Mysql 基础(二)

DML

Data Manipulation Language （数据操作语言），用于对数据库中的数据进行增删改操作。

添加数据 (INSERT)

• 给指定字段添加数据

  INSERT INTO 表名 (字段1, 字段2,..., 字段n) VALUES(值1, 值2, ..., 值n);

  值的顺序需与字段顺序对应

• 给全部字段添加数据

  INSERT INTO 表名 VALUES(值1, 值2, ..., 值n);

  值的顺序需与表中各字段顺序对应

• 批量添加数据

  INSERT INTO 表名 (字段1, 字段2,..., 字段n) VALUES(值1, 值2, ..., 值n),(值1, 值2, ..., 值n),(值1, 值2, ..., 值n);

  INSERT INTO 表名 VALUES(值1, 值2, ..., 值n),(值1, 值2, ..., 值n),(值1, 值2, ..., 值n);

修改数据 (UPDATE)

UPDATE 表名 SET 字段1 = 值1, 字段2 = 值2, ... [WHERE 条件];

如果没有添加条件语句默认修改整张表的该字段

删除数据 (DELETE)

DELETE FROM 表名 [WHERE 条件]

如果没有添加条件语句默认删除整张表的数据

DQL

Data Query Language (数据库查询语言)，用来查询表中的记录

语法

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 表名;

  AS 可省略

• 去除重复记录

  SELECT DISTINCT 字段列表 FROM 表名;

条件查询

• 语法

  SELECT 字段列表 FROM 表名 WHERE 条件列表;

• 条件

  比较运算符	功能
  >	大于
  >=	大于或等于
  <	小于
  =	等于
  <> 或 !=	不等于
  BETWEEN … AND …	在某个范围内（包括最大值和最小值）
  IN(…)	在列表中，多选一
  LIKE	模糊匹配（_匹配多个字符，%匹配任意字符）
  IS NULL	是 NULL

  逻辑运算符	功能
  AND 或 &&	并且（多个条件同时成立）
  OR 或 ||	或（多个条件中任意一个或多个成立）
  NOT 或 !	非，不是

聚合函数

将一列数据作为一个整体，进行纵向计算

• 语法

  SELECT 聚合函数(字段) FROM 表名;

• 常见聚合函数

  函数	功能
  count	统计数量
  min	最小值
  max	最大值
  sum	求和
  avg	平均值

分组查询

• 语法

  SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

• WHERE 与 HAVING 的区别

  • 执行时机不同：WHERE 在分组前进行过滤，不满足 WHERE 条件，不参与分组；HAVING 在分组后对结果进行过滤
  • 判断条件不同：WHERE 不能对聚合函数进行判断，而 HAVING 可以

• 分组后查询字段一般为分组字段和聚合函数，查询其它字段无任何意义

排序查询

• 语法

  SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2...;

  若为多字段排序，则优先按前面的字段排序，前面字段相同时按照后面字段排序

• 排序方式

  • ASC：升序（默认值）
  • DESC: 降序

分页查询

• 语法

  SELECT 字段列表 FEOM 表名 LIMIT 起始索引, 查询记录数;

  • 起始索引从0开始，起始索引 = (查询页面-1) * 每页记录数
  • 分页查询是数据库的方言，不同的数据库有不同的实现，Mysql 中使用 LIMIT
  • 如果查询第一页数据，起始索引可以省略，直接简写为 LIMIT 查询记录数

DQL 执行顺序

FROM –> WHERE –> GROUP BY –> SELECT –> ORDER BY –> LIMIT

DCL

Data Control Language ( 数据控制语言 ) ，用来管理数据库用户，控制数据库访问权限

用户管理

• 查询用户

  USE mysql;
  SELECT * FROM user;

• 创建用户

  CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

• 修改用户密码

  ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

• 删除用户

  DROP USER '用户名'@'主机名';

  主机名为 localhost 的用户只能在本机访问

  主机名为 % 的用户可在任意主机上访问数据库

权限控制

权限	说明
ALL , ALL PRIVILEGES	所有权限
SELECT	查询数据
INSERT	插入数据
UPDATE	修改数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库/表/视图
CREATE	创建数据库/表

• 查询权限

  SHOW GRANTS FOR '用户名'@'主机名';

• 授权

  GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

• 撤销权限

  REVOKE 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

  数据库名.* 表示该数据库的所有表 ， . 表示所有数据库的所有表

函数

• 函数是指一段可以直接被另一段程序调用的代码

字符串函数

函数	功能
CONCAT(S1,S2,…Sn)	字符串拼接，将S1，S2，…Sn 拼接成一个字符串
LOWER(str)	将字符串 str 全部转为小写
UPPER(str)	将字符串 str 全部转为大写
LPAD(str,n,pad)	左填充，用字符串 pad 对 str 的左边进行填充，达到 n 个字符长度
RPAD(str,n,pad)	右填充，用字符串 pad 对 str 的右边进行填充，达到 n 个字符长度
TRIM(str)	去掉字符串首尾的空白符
SUBSTRING(str,start,len)	返回字符串 str 从 start 位置起的 len 个长度的字符串, start 从1开始

示例

SELECT CONCAT('hello','mysql');

数值函数

函数	功能
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x,y)	返回 x mod y
RAND()	返回 0-1 的随机数
ROUND(x,y)	对 x 四舍五入，保留 y 位小数

日期函数

函数	功能
CURDATE()	返回当前日期. YYYY-MM-DD
CURTIME()	返回当前时间 HH:mm:ss
NOW()	返回当前日期时间 YYYY-MM-DD HH:mm:ss
YEAR(date)	获取指定 date 的年份 YYYY
MONTH(date)	获取指定 date 的月份 MM
DAY(date)	获取指定 date 的日期 DD
DATE_ADD(date, INTERVAL expr type)	返回一个日期/时间值加上一个时间间隔 expr 后的时间值
DAFEDIFF(date1, date2)	返回起始时间 date1 和结束时间 date2 之间的天数

SELECT DATE_ADD(NOW(), INTERVAL 70 DAY);# 获取70天后的时间
SELECT DATE_ADD(NOW(), INTERVAL 70 MONTH);# 获取70个月后的时间
SELECT DATE_ADD(NOW(), INTERVAL 70 YEAR);# 获取70年后的时间
SELECT('2022-3-24','2022-3-21');#3
SELECT('2022-3-21','2022-3-24');#-3

流程函数

函数	功能
IF(value, t, f)	当 value 为 true 时返回 true, 否则返回 false
IFNULL(value1, value2)	若 value1 不为空，返回 value1, 否则返回 value2
CASE WHEN [val1] THEN [res1] … ELSE [default] END	若 val1 为 true, 返回 val1， 否则返回 default
CASE [expr] WHEN [val1] THEN [res1] … ELSE [default] END	若表达式 expr 的值等于 val1，返回 res1，否则返回 default

IF 可嵌套

CASE 中可以包含多个 WHEN THEN

SELECT IF(score>80, '优秀', IF(score>60, '及格', '不及格'));
SELECT CASE city WHEN 'bj' THEN 'yxcs' WHEN 'sh' THEN 'yxcs' ELSE 'excs';#当前面不满足时匹配后面