Loading... ## DCL数据控制语言(Data Control Language) 数据控制语言(DCL)用于管理数据与数据库对象的访问安全性，并管理用户权限。 #### 管理用户 查询用户： ```mysql USE mysql; SELECT * FROM user; ``` 创建用户： `CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';` 修改用户密码： `ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';` 删除用户： `DROP USER '用户名'@'主机名';` 例子： ```mysql -- 创建用户test，只能在当前主机localhost访问 create user 'test'@'localhost' identified by '123456'; -- 创建用户test，能在任意主机访问 create user 'test'@'%' identified by '123456'; create user 'test' identified by '123456'; -- 修改密码 alter user 'test'@'localhost' identified with mysql_native_password by '1234'; -- 删除用户 drop user 'test'@'localhost'; ``` ##### 注意事项 * 主机名可以使用 `%` 通配 #### 权限控制 常用权限： | 权限 | 说明 | | ------------------- | ------------------ | | ALL, ALL PRIVILEGES | 所有权限 | | SELECT | 查询数据 | | INSERT | 插入数据 | | UPDATE | 修改数据 | | DELETE | 删除数据 | | ALTER | 修改表 | | DROP | 删除数据库/表/视图 | | CREATE | 创建数据库/表 | 更多权限请看[权限一览表](http://www.bixiaguangnian.com/manual/mysqllearndoc/3369.html "权限一览表") 查询权限： `SHOW GRANTS FOR '用户名'@'主机名';` 授予权限： `GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';` 撤销权限： `REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';` ##### 注意事项 * 多个权限用逗号分隔 * 授权时，数据库名和表名可以用 `*` 进行通配，代表所有 ## DQL数据查询语言（DQL-Data Query Language）：用于查询数据库对象中所包含的数据，能够进行表单查询、连接查询、嵌套查询，以及集合查询等各种复杂程度不同的数据库查询。 语法： ```mysql SELECT 字段列表 FROM 表名字段 WHERE 条件列表 GROUP BY 分组字段列表 HAVING 分组后的条件列表 ORDER BY 排序字段列表 LIMIT 分页参数 ``` #### 基础查询 查询多个字段： `SELECT 字段1, 字段2, 字段3, ... FROM 表名;` `SELECT * FROM 表名;` 设置别名： `SELECT 字段1 [ AS 别名1 ], 字段2 [ AS 别名2 ], 字段3 [ AS 别名3 ], ... FROM 表名;` `SELECT 字段1 [ 别名1 ], 字段2 [ 别名2 ], 字段3 [ 别名3 ], ... FROM 表名;` 去除重复记录： `SELECT DISTINCT 字段列表 FROM 表名;` 转义： `SELECT * FROM 表名 WHERE name LIKE '/_张三' ESCAPE '/'` `/` 之后的\_不作为通配符 #### 条件查询 语法： `SELECT 字段列表 FROM 表名 WHERE 条件列表;` 条件： | 比较运算符 | 功能 | | ----------------- | ------------------------------------------- | | > | 大于 | | >= | 大于等于 | | < | 小于 | | <= | 小于等于 | | = | 等于 | | <> 或 != | 不等于 | | BETWEEN … AND … | 在某个范围内（含最小、最大值） | | IN(…) | 在in之后的列表中的值，多选一 | | LIKE 占位符 | 模糊匹配（\_匹配单个字符，%匹配任意个字符） | | IS NULL | 是NULL | | 逻辑运算符 | 功能 | | ---------- | ------------------------ | | AND 或 && | 并且（多个条件同时成立） | | OR | 或 | | NOT 或 ! | 非，不是 | 例子： ```mysql -- 年龄等于30 select * from employee where age = 30; -- 年龄小于30 select * from employee where age < 30; -- 小于等于 select * from employee where age <= 30; -- 没有身份证 select * from employee where idcard is null or idcard = ''; -- 有身份证 select * from employee where idcard; select * from employee where idcard is not null; -- 不等于 select * from employee where age != 30; -- 年龄在20到30之间 select * from employee where age between 20 and 30; select * from employee where age >= 20 and age <= 30; -- 下面语句不报错，但查不到任何信息 select * from employee where age between 30 and 20; -- 性别为女且年龄小于30 select * from employee where age < 30 and gender = '女'; -- 年龄等于25或30或35 select * from employee where age = 25 or age = 30 or age = 35; select * from employee where age in (25, 30, 35); -- 姓名为两个字 select * from employee where name like '__'; -- 身份证最后为X select * from employee where idcard like '%X'; ``` #### 聚合查询（聚合函数） 常见聚合函数： | 函数 | 功能 | | ----- | -------- | | count | 统计数量 | | max | 最大值 | | min | 最小值 | | avg | 平均值 | | sum | 求和 | 语法： `SELECT 聚合函数(字段列表) FROM 表名;` 例： `SELECT count(id) from employee where workaddress = "广东省";` #### 分组查询 语法： `SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后的过滤条件 ];` where 和 having 的区别： * 执行时机不同：where是分组之前进行过滤，不满足where条件不参与分组；having是分组后对结果进行过滤。 * 判断条件不同：where不能对聚合函数进行判断，而having可以。 例子： ```mysql -- 根据性别分组，统计男性和女性数量（只显示分组数量，不显示哪个是男哪个是女） select count(*) from employee group by gender; -- 根据性别分组，统计男性和女性数量 select gender, count(*) from employee group by gender; -- 根据性别分组，统计男性和女性的平均年龄 select gender, avg(age) from employee group by gender; -- 年龄小于45，并根据工作地址分组 select workaddress, count(*) from employee where age < 45 group by workaddress; -- 年龄小于45，并根据工作地址分组，获取员工数量大于等于3的工作地址 select workaddress, count(*) address_count from employee where age < 45 group by workaddress having address_count >= 3; ``` ##### 注意事项 * 执行顺序：where > 聚合函数 > having * 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义 #### 排序查询 语法： `SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;` 排序方式： * ASC: 升序（默认） * DESC: 降序 例子： ```mysql -- 根据年龄升序排序 SELECT * FROM employee ORDER BY age ASC; SELECT * FROM employee ORDER BY age; -- 两字段排序，根据年龄升序排序，入职时间降序排序(如果年龄相同那么就按这个) SELECT * FROM employee ORDER BY age ASC, entrydate DESC; ``` ##### 注意事项 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序 #### 分页查询 语法： `SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;` 例子： ```mysql -- 查询第一页数据，展示10条 SELECT * FROM employee LIMIT 0, 10; -- 查询第二页 SELECT * FROM employee LIMIT 10, 10; ``` ##### 注意事项 * 起始索引从0开始，起始索引 = （查询页码 - 1） \* 每页显示记录数 * 分页查询是数据库的方言，不同数据库有不同实现，MySQL是LIMIT * 如果查询的是第一页数据，起始索引可以省略，直接简写 LIMIT 10 #### DQL执行顺序 `FROM` -> `WHERE` -> `GROUP BY` -> `SELECT` -> `ORDER BY` -> `LIMIT` ## DML数据操作语言（DML-Data Manipulation Language）：对于DELETE,INSERT,UPDATE等语句，为了保证数据一致性，在写二进制日志之前还需要获取一把事务级锁，写完后释放。 #### 添加数据 指定字段： `INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);` 全部字段： `INSERT INTO 表名 VALUES (值1, 值2, ...);` 批量添加数据： `INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);` `INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);` ##### 注意事项 * 字符串和日期类型数据应该包含在引号中 * 插入的数据大小应该在字段的规定范围内 #### 更新和删除数据 修改数据： `UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [ WHERE 条件 ];` 例：`UPDATE emp SET name = 'Jack' WHERE id = 1;` 删除数据： `DELETE FROM 表名 [ WHERE 条件 ];` ## DDL数据定义语言（DDL）：对于ALTER,CREATE等语句直接修改表的数据结构。 数据定义语言 #### 数据库操作 查询所有数据库： `SHOW DATABASES;` 查询当前数据库： `SELECT DATABASE();` 创建数据库： `CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ DEFAULT CHARSET 字符集] [COLLATE 排序规则 ];` 删除数据库： `DROP DATABASE [ IF EXISTS ] 数据库名;` 使用数据库： `USE 数据库名;` ##### 注意事项 * `UTF8`字符集长度为3字节，有些符号占4字节，所以推荐用`utf8mb4`字符集 #### 表操作 查询当前数据库所有表： `SHOW TABLES;` 查询表结构： `DESC 表名;` 查询指定表的建表语句： `SHOW CREATE TABLE 表名;` 创建表： ```mysql CREATE TABLE 表名( 字段1 字段1类型 [COMMENT 字段1注释], 字段2 字段2类型 [COMMENT 字段2注释], 字段3 字段3类型 [COMMENT 字段3注释], ... 字段n 字段n类型 [COMMENT 字段n注释] )[ COMMENT 表注释 ]; ``` 所有的要用英文的格式 最后一个字段后面没有逗号 添加字段： `ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];` 例：`ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称';` 修改数据类型： `ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);` 修改字段名和字段类型： `ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];` 例：将emp表的nickname字段修改为username，类型为varchar(30) `ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '昵称';` 删除字段： `ALTER TABLE 表名 DROP 字段名;` 修改表名： `ALTER TABLE 表名 RENAME TO 新表名` 删除表： `DROP TABLE [IF EXISTS] 表名;` 删除表，并重新创建该表： `TRUNCATE TABLE 表名;` Last modification：September 20, 2024 © Allow specification reprint Like 如果觉得我的文章对你有用，请随意赞赏