MySql-Learning

### MySQL 学习笔记知识点详解 #### 一、MySQL 存储结构 MySQL 的存储结构主要包括以下几个层次： 1. **数据库**: 数据库是用于组织、存储和管理数据的集合。在 MySQL 中，可以通过创建不同的数据库来区分不同业务或项目的数据。 2. **表**: 表是数据库中的基本单元，它由行和列组成。每个表都有自己的结构定义，包括字段名称、数据类型等。 3. **数据 SQL 语句**: SQL (Structured Query Language) 是一种用于管理关系型数据库的标准语言。它用于执行诸如查询、更新和管理数据库的操作。 #### 二、数据库的增删改查 1. **添加一个数据库**: - 命令格式: `CREATE DATABASE 数据库名称 DEFAULT CHARACTER SET utf8;` - 示例: 创建一个名为 `testdb` 的数据库，字符集为 UTF-8。 ```sql CREATE DATABASE testdb DEFAULT CHARACTER SET utf8; ``` 2. **更改一个数据库**: - 命令格式: `ALTER DATABASE 数据库名称 DEFAULT CHARACTER SET gbk;` - 示例: 将 `testdb` 的字符集更改为 GBK。 ```sql ALTER DATABASE testdb DEFAULT CHARACTER SET gbk; ``` 3. **查询数据库**: - 查看所有数据库: `SHOW DATABASES;` - 查看某个数据库的创建信息: `SHOW CREATE DATABASE 数据库名称;` - 示例: 查看所有数据库。 ```sql SHOW DATABASES; ``` 4. **删除一个数据库**: - 命令格式: `DROP DATABASE 数据库名称;` - 示例: 删除 `testdb` 数据库。 ```sql DROP DATABASE testdb; ``` #### 三、表的增删改查 1. **选定一个数据库**: - 命令格式: `USE 数据库名称;` - 示例: 使用 `testdb` 数据库。 ```sql USE testdb; ``` 2. **添加一个表**: - 命令格式: `CREATE TABLE 表名 (字段1 数据类型, 字段2 数据类型, ...);` - 示例: 在 `testdb` 数据库中创建一个名为 `users` 的表，包含 `id` 和 `name` 两个字段。 ```sql CREATE TABLE users (id INT, name VARCHAR(50)); ``` 3. **删除一个表**: - 命令格式: `DROP TABLE 表名;` - 示例: 删除 `users` 表。 ```sql DROP TABLE users; ``` 4. **改变一个表**: - **字段的增删改**: - **增加一个字段**: - 命令格式: `ALTER TABLE 表名 ADD COLUMN 字段名 字段类型;` - 示例: 向 `users` 表添加一个 `age` 字段。 ```sql ALTER TABLE users ADD COLUMN age INT; ``` - **删除一个字段**: - 命令格式: `ALTER TABLE 表名 DROP COLUMN 字段名;` - 示例: 从 `users` 表删除 `age` 字段。 ```sql ALTER TABLE users DROP COLUMN age; ``` - **更改字段的默认字符集**: - 命令格式: `ALTER TABLE 表名 MODIFY COLUMN 字段名 新的字段类型;` - 示例: 修改 `users` 表中的 `name` 字段为 `VARCHAR(100)` 并且设置为 UTF-8 字符集。 ```sql ALTER TABLE users MODIFY COLUMN name VARCHAR(100) CHARACTER SET utf8; ``` - **修改字段的名称**: - 命令格式: `ALTER TABLE 表名 CHANGE COLUMN 旧字段名 新字段名 新字段类型;` - 示例: 将 `users` 表中的 `name` 字段改为 `username`。 ```sql ALTER TABLE users CHANGE COLUMN name username VARCHAR(50); ``` - **修改表的名称**: - 命令格式: `ALTER TABLE 表名 RENAME TO 新表名;` - 示例: 将 `users` 表重命名为 `users_new`。 ```sql ALTER TABLE users RENAME TO users_new; ``` - **查询表**: - 命令格式: `SHOW TABLES;` - 示例: 显示当前数据库的所有表。 ```sql SHOW TABLES; ``` - **表内容的增删改查**: - **增加数据**: - 命令格式: `INSERT INTO 表名 (字段1, 字段2, ...) VALUES (值1, 值2, ...);` - 示例: 向 `users` 表插入一条记录。 ```sql INSERT INTO users (id, name) VALUES (1, 'John Doe'); ``` - **修改数据**: - 命令格式: `UPDATE 表名 SET 字段1 = 值1, 字段2 = 值2, ... WHERE 条件;` - 示例: 更新 `users` 表中 `id` 为 1 的记录的 `name` 为 'Jane Doe'。 ```sql UPDATE users SET name = 'Jane Doe' WHERE id = 1; ``` - **删除数据**: - 命令格式: `DELETE FROM 表名 WHERE 条件;` - 示例: 从 `users` 表中删除 `id` 为 1 的记录。 ```sql DELETE FROM users WHERE id = 1; ``` - **查询数据**: - **查询所有字段**: - 命令格式: `SELECT * FROM 表名 WHERE 条件;` - 示例: 查询 `users` 表中所有记录。 ```sql SELECT * FROM users; ``` - **查询部分字段**: - 命令格式: `SELECT 字段1, 字段2, ... FROM 表名 WHERE 条件;` - 示例: 查询 `users` 表中 `name` 和 `age` 字段。 ```sql SELECT name, age FROM users; ``` - **指定别名**: - 命令格式: `SELECT 字段 AS 别名 FROM 表名;` - 示例: 查询 `users` 表中 `name` 字段，并命名为 `username`。 ```sql SELECT name AS username FROM users; ``` - **合并列**: - 命令格式: `SELECT (字段 + 字段2) FROM 表名;` - 示例: 查询 `users` 表中 `age` 和 `age * 2` 的结果。 ```sql SELECT age, age * 2 AS double_age FROM users; ``` - **去重**: - 命令格式: `SELECT DISTINCT 字段 FROM 表名;` - 示例: 查询 `users` 表中 `age` 字段的所有不同值。 ```sql SELECT DISTINCT age FROM users; ``` - **按条件查询**: - **逻辑条件**: - 命令格式: `SELECT * FROM 表名 WHERE 条件1 AND 条件2;` - 示例: 查询 `users` 表中 `age` 大于 20 且 `name` 为 'John Doe' 的记录。 ```sql SELECT * FROM users WHERE age > 20 AND name = 'John Doe'; ``` - **比较条件**: - 命令格式: `SELECT * FROM 表名 WHERE 字段1 比较运算符 字段2;` - 示例: 查询 `users` 表中 `age` 大于 30 的记录。 ```sql SELECT * FROM users WHERE age > 30; ``` - **判断空值**: - `IS NULL`: 判断字段是否为空值。 - 示例: 查询 `users` 表中 `address` 为空值的记录。 ```sql SELECT * FROM users WHERE address IS NULL; ``` - `IS NOT NULL`: 判断字段是否非空。 - 示例: 查询 `users` 表中 `address` 不为空值的记录。 ```sql SELECT * FROM users WHERE address IS NOT NULL; ``` - `''`: 判断字段是否为空字符串。 - 示例: 查询 `users` 表中 `address` 为空字符串的记录。 ```sql SELECT * FROM users WHERE address = ''; ``` - **模糊查询**: - `%`: 替换任意多个字符。 - 示例: 查询 `users` 表中 `name` 以 'J' 开头的记录。 ```sql SELECT * FROM users WHERE name LIKE 'J%'; ``` - `_`: 替换单个字符。 - 示例: 查询 `users` 表中 `name` 第二个字符为 'o' 的记录。 ```sql SELECT * FROM users WHERE name LIKE '_o%'; ``` - **分页查询**: - 命令格式: `SELECT * FROM 表名 LIMIT 起始行, 查询行数;` - 示例: 查询 `users` 表中第 10 行开始的 5 条记录。 ```sql SELECT * FROM users LIMIT 9, 5; ``` - **排序**: - 命令格式: `SELECT * FROM 表名 ORDER BY 字段 ASC/DESC;` - 示例: 查询 `users` 表中所有记录，并按 `age` 升序排列。 ```sql SELECT * FROM users ORDER BY age ASC; ``` - **分组查询**: - 命令格式: `SELECT 字段, COUNT(*) FROM 表名 GROUP BY 字段;` - 示例: 查询 `users` 表中按 `gender` 分组的男女数量。 ```sql SELECT gender, COUNT(*) FROM users GROUP BY gender; ``` - **分组后筛选**: - 命令格式: `SELECT * FROM 表名 GROUP BY 字段 HAVING 条件;` - 示例: 查询 `users` 表中 `age` 大于 20 的男女数量。 ```sql SELECT gender, COUNT(*) FROM users GROUP BY gender HAVING COUNT(*) > 20; ``` #### 四、SQL 语句分类 1. **数据定义语言 (DDL)**: - 主要用于定义数据库结构，如创建、删除和修改数据库或表。 - `CREATE`: 创建数据库或表。 - `DROP`: 删除数据库或表。 - `ALTER`: 修改数据库或表的结构。 2. **数据操纵语言 (DML)**: - 主要用于操作数据库中的数据，如插入、删除和更新数据。 - `INSERT`: 插入数据到表中。 - `DELETE`: 从表中删除数据。 - `UPDATE`: 更新表中的数据。 - `TRUNCATE`: 清空表中的所有数据，但保留表结构。 3. **数据库查询语言 (DQL)**: - 主要用于检索数据。 - `SELECT`: 从数据库中选择数据。 - `SHOW`: 显示有关数据库的信息，如显示所有数据库。 #### 五、数据约束 - **默认值 (DEFAULT)**: - 允许字段为空。 - 当用户对使用默认值的字段不插入值时，自动使用默认值。 - **非空 (NOT NULL)**: - 必须有值，不能为空。 - **唯一 (UNIQUE)**: - 对字段的值不能重复。 - 可以出现 `NULL` 值，但一般与 `NOT NULL` 结合使用，即主键。 - **主键 (PRIMARY KEY)**: - 相对于非空约束与唯一约束的集合。 - 通常情况下，每张表都会设置一个主键的字段，用于标记每条记录的唯一性。 - 建议不要选择包含业务含义的字段作为主键，而应给每张表独立设计一个非业务含义的 ID 字段。 - **自增长 (AUTO_INCREMENT)**: - ID 值可以不用赋值，自己递增。 - 通常与主键一起使用，以便自动为每条新记录分配一个唯一的 ID。