【MySQL学习|黑马笔记|Day2】SQL|DML、DGL、DCL，函数，约束

（接上篇）

今天看的是MySQL的12～36集，笔记包含SQL语法中的DML,DGL,DCL以及函数，约束相关知识

3）DML

1.添加INSERT

• 给指定字段添加数据

  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,..);

注意：

• 插入数据时，指定的字段顺序需要与值的顺序一一对应
• 字符串和日期型数据应该包含在引号中
• 插入的数据大小，应该在字段的规定范围内

2.修改UPDATE

• 修改数据

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

注意：修改语句的条件可以有也可以没有，若没有则会修改整张表的所有数据

3.删除DELETE

• 删除数据

  DELETE FROM 表名 [WHERE 条件];

注意：删除语句的条件可以有也可以没有，若没有则会删除整张表的所有数据

​ DELETE语句不能删除某一个字段的值（可以使用update）

4）DGL

1.基本查询

• 查询多个字段

  SELECT 字段1,字段2,字段3...FROM 表名;

  SELSECT * FROM 表名; （*表示返回所有字段）

• 设置别名

  SELECT 字段名1[AS 别名1],字段2[AS 别名2]...FROM表名;

• 去除重复记录

  SELECT DISTINCT 字段列表 FROM 表名;

2.条件查询

• 语法

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

• 条件

eg

查询没有身份证号的 select * from tb_user where idcard is nulll;

查询有身份证号的 select * from tb_user where idcard is not nulll;

查询年龄等于18或20或40的 select * from tb_user where age = 18 or age = 20 or age = 40;，select * from tb_user where age in (18,20,40);

查询姓名为两个字的 select * from tb_user where name like '___';

查询身份证为结尾为x的 select * from tb_user where idcard like '%X';

3.聚合函数

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

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

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

eg

-- 1.统计员工数量
select count(*) from tb_user;
-- 2.统计平均年龄
select avg(age) from tb_user;
-- 3.统计西安地区员工年龄之和
select sum(age) from tb_user where workaddress = '西安';

4.分组查询

• 语法

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

where VS having

• 执行时机不同：where是分组前进行过滤，补码组where条件，不参与分组；having是分组之后对结果进行过滤
• 判断条件不同：where不能对聚合函数进行判断，而having可以

-- 1.根据性别分组 统计各自数量
select gender,count(*) from tb_user group by gender;
-- 2.根据性别分组 统计各自平均年龄
select gender,avg(age) from tb_user group by gender;
-- 3.查询年龄小于45的，根据工作地址分组，获取员工数量大于3的工作地址
select workaddress,count(*) from tb_user where age < 45 group by workaddress having count(*) >= 3;

注意

• 执行顺序：where > 聚合函数 > having
• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义

5.排序查询

• 语法

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

• 排序方式

  ASC：升序

  DESC：降序

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段排序

eg：

-- 1.根据年龄排序
select * from tb_user order by age asc;
-- 2.根据年龄升序排序，年龄相同再按照id升序排序
select * from tb_user order by age asc,id asc;

6.分页查询

• 语法

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

注意

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

eg

-- 1.查询第一页 每页显示10条
select * from tb_user limit 10;
-- 2.查询第二页 每页显示10条
select * from tb_user limit 10,10;

7.编写顺序和执行顺序

5）DCL

管理数据库用户、控制数据库的访问权限

1.用户管理

• 查询用户

  USE mysql;
  SELECT * FROM user;
  

• 创建用户

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

• 修改用户密码

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

• 删除用户

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

注意

• 主机名可以使用%通配
• 这类SQL开发人员操作的比较少，主要是DBA（数据库管理员）使用

2.权限控制

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

• 查询权限

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

• 授予权限

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

• 撤销权限

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

注意

• 多个权限之间，使用逗号分隔

• 授权时，数据库名和表名可以使用*进行通配，代表所有

  grant all on *.* to 'r'@'%';授予所有数据库和所有表的权限

三.函数

1）字符串函数

MySQL中内置的字符串函数

函数	功能
CONCAT(S1,S2,S3,…)	字符串拼接
LOWER(str)	将str转换为小写
UPPER(str)	将str转换为大写
LPAD(str,n,pad)	左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
RPAD(str,n,pad)	右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
TRIM(str)	去掉字符串头尾的空格
SUBSTRING(str,strat,len)	返回字符串str从start起len个长度的字符串

SELECT 函数(参数);

eg

select concat('Hello','MySQL');select lower('ABC');select upper('abc');select lpad('111',5,'-');select trim('  hello mysql   ');

update tb_user set nickname = lpad(id,5,'0');

2）数值函数

函数	功能
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x,y)	返回x/y的模
RAND()	返回0~1内的随机数
ROUND(X,Y)	求参数x的四舍五入的值，保留y位小数

-- 生成一个六位的随机验证码
select lpad(round(rand()*1000000,0),6,0);

3）日期函数

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YEAR(date)	获取指定date的年份
MONTH(date)	获取指定date的月份
DAY(date)	获取指定date的日期
DATE_ADD(date,INTERVAL expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date 1,date 2)	返回起始时间date1和结束时间date2之间的天数

select curdate();
select curtime();
select now();select YEAR(now());
select MONTH(now());
select DAY(now());select date_add(now(),INTERVAL 70 DAY);select datediff('2025-11-27',now());

-- 查询所有员工的入职天数，倒序排序
select name,datediff(curdate(),entrydate) as 'entrydays' from tb_user order by entrydays desc;

4）流程控制函数

在SQL语句中实现条件筛选，从而提高语句的效率

函数	功能
IF(vakue,t,f)	如果value为true，则返回t，否则返回f
IFNULL(value1,value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN[val1]THEN[res1]…ELSE[default]END	如果val1为true，返回res1，…否则返回default
CASE[expr] WHEN[val1]THEN[res1]…ELSE[default]END	如果expr的值等于val1，返回res1，…否则返回default

-- if
select if(true,'OK','ERROR');-- ifnull
select ifnull('OK','Default');
select ifnull('','Default');
select ifnull(null,'Default');-- case
-- 查询emp表中姓名和工作地址，把北京上海改成一线城市，其他改成二线城市
selectname,(case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
from emp;

四.约束

1）概述

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据

目的：保证数据库中数据的正确、有效性、完整性

分类：

约束	描述	关键字
非空约束	限制该字段的数据不能为null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMART KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束	保证字段值满足某一个条件	CHECK
外键约束	用来让两张表的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束

2）案例

create table user(id int primary key auto_increment comment'主键',name varchar(10) not null unique comment'姓名',age int check(age > 0 && age <= 120) comment'年龄',status char(1) default '1' comment'状态',gender char(1) comment'性别'
) comment'用户表';-- 插入数据
insert into user(name,age,status,gender) values ('Tom1',19,'1','男'),('Tom2',22,'0','男');
insert into user(name,age,status,gender) values ('Tom3',29,'','男');-- 空
insert into user(name,age,status,gender) values ('Tom4',29,null,'男');-- <null>
insert into user(name,age,gender) values ('Tom5',29,'男');-- 没传status采用默认值

3）外键约束

• 添加外键

  1.在创建时添加

  create table 表名(字段名 数据类型,...[constraint] [外键名称] foreign key (外键字段名) references 主表(主表列名)
  );
  

  2.在创建后添加

  alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名);
  

• 删除外键

  alter table 表名 drop foreign key 外键名称;
  

4）外键删除更新行为

• 删除/更新行为

行为	说明
no action	当在父表中删除/更新对应记录时，首先要检查该记录是否有对应外键，若有则不允许删除/更新
restrict	当在父表中删除/更新对应记录时，首先要检查该记录是否有对应外键，若有则不允许删除/更新
cascade	当在父表中删除/更新对应记录时，首先要检查该记录是否有对应外键，若有则也删除/更新在子表的记录
set null	当在父表中删除/更新对应记录时，首先要检查该记录是否有对应外键，若有则设置子表中该外键值为null（要求外键允许取null）
set default	父表有变更时，子表将外键列设置为默认值（lnnodb不支持）

alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名(主表字段名) on update cascade on delete cascade;

（笔记内容主要基于黑马程序员的课程讲解，旨在加深理解和便于日后复习）

希望这篇笔记能对大家的学习有所帮助，有啥不对的地方欢迎大佬们在评论区