mysql命令
mysql简介和sql定义
- mysql是一个轻量级关系型数据库管理系统，具有体积小，速度快，开源的优点
- sql是一种结构化查询语言(Structured Query Language)，专门用来管理和处理关系型数据库的标准化编程语言，mysql实现了SQL标准，使得用户能够使用SQL语言来与MySQL数据库进行交互和操作数据
sql语言分类
- 数据定义语言DDL(data definition language)，定义和管理数据库和数据表
- 数据操作语言DML(data manipulation language)，操作数据库数据(增删改)
- 数据查询语言DQL(data query language)，查看数据库数据(查)
- 数据控制语言DCL(data control language)，数据库权限管理
- 事务控制语言TCL(transaction control language)，数据库事务管理
连接数据库及数据库常用指令
这里的命令在sql可视化工具中可以直接操作，只有在linux或cmd中才会用到
-
连接数据库常用选项
-u用户名，-p密码 如果省略会在命令行隐藏式输入密码
-P端口 不加默认3306，-h服务器地址 不加默认本地ip
-D指定默认数据库 不加默认不指定，-D参数可以省略直接加数据库名
# 连接本地mysql mysql -uroot -p # 连接远程mysql服务器，端口为3333，指定数据库为yilko mysql -h192.168.xx.xx -Dyilko -P3333 -uroot -p # 不加-D指定数据库,直接写数据库名也可以 mysql -uroot -p yilko -
查看及选择数据库
想知道自己在用哪个数据库，可以用database()函数，在下面mysql函数-系统信息函数可见
show databases; use db_nme; -
查看表以及表结构
show tables; desc tb_name; -
刷新数据库(属于dcl范畴)及退出mysql
flush privileges; exit
数据类型
创建表时，表字段需要指定类型，先了解数据库常用数据类型，才能在ddl创建表中运用
数值类型
| 类型 | 说明 | 有符号范围 | 无符号范围 | 存储占用 |
|---|---|---|---|---|
| tinyint | 很小的数据 | -27到27-1 | 0到2^8-1(255) | 1字节 |
| smallint | 较小的数据 | -215到215-1 | 0到2^16-1(65535) | 2字节 |
| mediumint | 中等数据 | -223到223-1 | 0到2^24-1(1677w) | 3字节 |
| int | 标准整数 | -231到231-1 | 0到2^32-1(42.94e) | 4字节 |
| bigint | 较大整数 | -263到263-1 | 0到2^64-1(20位) | 8字节 |
| float | 单精度浮点数 | 约7个有效数字 | (整数+小数) | 4字节 |
| double | 双精度浮点数 | 约15个有效数字 | (整数+小数) | 8字节 |
| decimal | 字符串形式浮点数 | - | - | - |
decimal根据不同的赋值有不同的存储大小，基本格式为decimal(m,d)
m指最大位数(小数点左边整数和右边小数加起来)，m的范围是1-65
d指小数点右边位数(不能大于m)，d的范围是0-30
如果d被省略，默认为0，m被省略，默认为10
当某些场景需要高精度计算时不使用double，而是使用decimal类型
字符串类型
这里只提及字符长度，没有提及存储占用，是因为不同的编码方式，字节和字符的转换不同
想知道存储占用，可以先知道长度，编码方式，然后参考注意事项部分得出占用大小
固定长度的字符串，分配固定的空间，速度快
可变长度的字符串，根据实际长度动态分配空间，速度慢
enum类型属于一次性定义，不支持直接修改枚举值，要修改涉及到数据迁移，所以enum用的很少
| 类型 | 说明 | 最大长度 |
|---|---|---|
| char | 固定长度字符串 | 255个字符 |
| varchar | 可变(长度)字符串 | 65535个字符 |
| tinytext | 可变字符串，存储很短文本 | 255个字符 |
| text | 可变字符串，存储较大文本 | 65535个字符 |
| longtext | 可变字符串，存储庞大文本，约4g | 2^32-1个字符 |
| binary | 固定长度二进制 | 255个字符 |
| varbinary | 可变(长度)二进制 | 65535个字符 |
| longblob | 可变二进制，存储庞大二进制数据，约4g | 2^32-1个字符 |
| enum | 枚举类型，从预定义集合中选一个值，存储效率高 | - |
| set | 集合类型，从预定义集合中选择多个值 | - |
日期时间类型
| 类型 | 说明 | 取值范围 | 存储占用 |
|---|---|---|---|
| date | 日期格式yyyy-mm-dd | 1000-01-01到9999-12-31 | 3字节 |
| time | 时间格式hh:mm:ss | -838:59:59到838:59:59 | 3字节 |
| datetime | 格式yyyy-mm-dd hh:mm:ss | date+time(24小时)的范围 | 8字节 |
| timestamp | 时间戳 | 1970-01-01 00:00:01到现在 | 7字节 |
| year | 年份 | 1901-2155 | 1字节 |
time、datetime、timestamp注意事项：
精度：默认精确到秒级，如需精确到毫秒级使用time(3)，微秒级使用time(6)，代表小数精度
时区：timestamp受服务器时区影响，date、time、datetime不受时区影响
null类型
代表该字段的数据缺失或未定义的状态，不是一个具体的值
与0、空字符串、空数组都不是一个意思
判断null不能使用表达式或运算符，而是用is [not] null判断该字段是否为空
null参与计算需要使用coalesce空处理函数把null改为指定默认值
分组函数计算时自动忽略null的那行
操作数据库(ddl)
对表的结构进行增删改，常见的有create、drop、alter、truncate指令
ddl的语句在sql可视化工具中是可以直接增删改，只有在linux或cmd中才会用到ddl命令
创建数据库
# 数据库存在会报错
create database yilko;
# 数据库存在不会报错也不会创建，沿用已存在的数据库
create dateabase if not exists yilko;
查看建库语句
show create database yilko;
创建表
表的创建涉及到列属性的定义，列属性中也存在约束，索引，引擎等相关知识
下面表格只简单描述列属性有哪些，简单的说明等(只提及约束、索引、引擎在表创建时添加)
更深入(如何使用，修改及删除等)的约束，索引，引擎查看下面的章节
列级约束写在字段里面，跟整列相关。表级约束是写在字段外表格内的，跟整个表相关
| 列属性 | 说明 |
|---|---|
| 数据类型 | 决定该列存储的类型，如int，varchar |
| 长度/精度 | 声明存储最大长度或精度，如varchar(255)，time(3) |
| 主键约束(表级) | 记录该行记录的唯一标识，相当于身份证号，不允许重复且不能为null |
| 非空约束(列级) | 决定该字段能否存储null值，默认为null，使用not null可约束该字段不能为空 |
| 唯一约束(列级) | 字段的值在表中唯一，但可以有多个null |
| 外键约束(表级) | 建立表的关联关系，子表中的字段引用父表中的主键字段 |
| 自增(列级) | 适用于整数类型字段，插入新行自动增加1，常用于主键，可定义起始值和步长 |
| 默认值(列级) | 插入时未指定值，使用默认值填充 |
| 无符号(列级) | 适用于整数类型字段，只能存储0或正整数的数值，增加整数型的取值范围 |
| 检查约束(表级) | mysql8.0后支持，适用于各种字段，在检查约束里写一个判断条件 |
| 索引(表级) | 缩小扫描范围，提高查询效率，可理解为一本书的目录 |
| 注释(列/表级) | 字段添加备注 |
| 存储引擎(表级) | 默认innodb引擎，可以指定别的引擎，或者指定表级或列级引擎 |
基本格式：
create table [if not exists] '表名'(
'字段名1' '数据类型'('长度/精度') ['列属性1'] ['列属性2'] ['列级约束'] ['注释'],
'字段名2' '数据类型'('长度/精度') ['列属性1'] ['列属性2'] ['列级约束'] ['注释'],
...,
['表级约束'],
['索引'],
...
)['存储引擎'] ['字符集'] ['注释']
如果没有指定引擎和字符集，那么默认使用InnoDB引擎和utf8mb3字符集
举例使用上面所有列属性，数据类型列举大部分常用的
# 创建一个班级表用于了解外键的连接
CREATE TABLE class_table(
# 列级主键约束不用加字段，默认指定当前列字段，而下面表级主键约束需要指定列字段
# 注意建立外键的字段类型要一致，否则报incompatible错误
`id` SMALLINT AUTO_INCREMENT PRIMARY KEY COMMENT '班级id'
);
CREATE TABLE my_table(
# 常用数值类型举例，包含自增、无符号列属性
`id` INT AUTO_INCREMENT COMMENT '用户id',
`age` TINYINT UNSIGNED COMMENT '用户年龄(0-255)',
`class_id` SMALLINT COMMENT '用户班级id 外键',
`balance` DOUBLE COMMENT '用户余额',
# 常用字符串类型举例，包含默认值、非空约束、唯一约束列属性
`sex` CHAR(1) DEFAULT 0 COMMENT '用户性别 0未知 1男 2女',
`name` VARCHAR(20) NOT NULL COMMENT '用户名字',
`email` VARCHAR(50) UNIQUE COMMENT '用户邮箱',
`self_introduce` TEXT COMMENT '自我介绍',
`self_photo` LONGBLOB COMMENT '用户自拍照片',
# 常用时间类型举例
`birthday` DATE COMMENT '生日',
`create_time` DATETIME COMMENT '创建时间',
`school_year` YEAR COMMENT '入学年份',
# 主键外键索引注释
PRIMARY KEY(id),
FOREIGN KEY(class_id) REFERENCES class_table(id),
INDEX idx_name(`name`)
# 自定义存储引擎和字符集及表注释
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '个人信息表';
查看建表语句
show create table my_table;
改变表结构(改名和增删改字段)
下面所写的例子都是基于上面创建的表结构
-
修改表名
alter table 旧表名 rename as 新表名; alter table class_table rename as my_class_table; -
添加字段
alter table 表名 add 字段名 字段属性(长度) [列属性] [约束] [注释]; alter table my_class_table add person_sum tinyint; alter table my_class_table add name varchar(10) not null default '1班' comment '班级名字'; -
修改字段属性
alter table 表名 modify 字段名 字段属性; alter table my_class_table modify person_sum tinyint unsigned not null comment '班级总人数'; -
修改字段名和属性
只想改名字不改变属性，需要把旧字段的属性原封不动搬到修改语句，否则属性改变
实际上字段属性可能很复杂，很难完全照搬，所以只改名字的情况下，尽量不改名
alter table 表名 change 旧字段名 新字段名 字段属性; alter table my_class_table change name class_name varchar(5); -
删除表字段
alter table 表名 drop 字段名; alter table my_class_table drop person_sum;
清空表
删除表全部数据(清空表)的情况下，truncate会比delete好
-
delete语句一行一行记录删，删除效率低，不会清空自增约束的数目
数据还在真实存储空间，支持回滚(数据可恢复)，属于DML语句
-
truncate一整张表清空，删除效率高，会把自增约束重置为1
属于物理删除，不支持回滚，属于DDL语句
truncate 表名;
truncate my_table;
删除表
清空表和删除表都没有选择my_class_table是因为存在外键，清空或删除会报错
这里主要是了解如何删除和清空表，在下面约束章节中会提到如何解决外键的问题
drop table [if exists] 表名;
drop table my_table;
操作数据表(dml)
对表的数据进行增删改，常见的有insert、update、delete指令
dml语句用的频率相对来说会高一些，需要熟悉掌握
下面使用到的dml语句都是来自于上面ddl创建的两个表
插入行
插入有两种方式：
-
插入指定字段，字段顺序可以不跟table一样，但是字段和值要一一对应
没有指定的使用默认值或赋予null，但遇到没有默认值且非空情况，需要指定该字段赋值
insert into 表名(字段1,字段3...) values (值1,值3...); # 插入错误情况，name字段非空，需要指定 insert into my_table(age,school_year) values(18,2022); # 正常插入 insert into my_table(age,school_year,name) values(18,2022,'bueatiful girl'); -
省略字段只写值，但必须所有字段值都写上
不需要值的那个字段也需要写null，拥有自增的主键也需要写上
values的个数必须和字段的个数对上，对不上就报错
insert into 表名 values(值1,值2,值3...); insert into my_table values(2,22,null,100,1,'good girl','aaa@qq.com','自我介绍完毕',null,null,'2024-4-10 18:00:00',null);
可以一次插入多条记录，每条记录使用逗号隔开即可，上面两种方式都可
-
指定字段一次插入多条
有弊有利，利是字段少了看起来比起省略字段的要简洁
弊是固定死了字段，如果第二条记录跟第一条记录不同字段，就没办法同时插入
insert into my_table(age,school_year,name) values(19,2022,'bueatiful girl'),(33,2018,'green girl'); -
省略字段一次插入多条
insert into my_table values(3,22,null,100,1,'sexy girl',null,'自我介绍完毕',null,null,'2024-4-10 18:00:00',null),(7,25,null,100,1,'nice girl',null,'自我介绍完毕',null,null,'2024-4-10 18:00:00',null); -
上面两个方式看需求使用，插入多条时都是同一字段，就用第一种，否则就用第二种
更新行
update语句一定一定一定要有where限制条件！否则整张表数据全部更新！！！
update 表名 set 字段1=值1,字段2=值2... where 条件;
# 更新一条记录
update my_table set sex=2,email='qwe@qq.com' where id=2;
# 更新多条记录(大于小于或者in的方式都可以)
update my_table set balance=1000 where age>20;
# 更新整张表(很少情况会用到，谨慎使用)
update my_table set self_introduce=null;
删除行
delete语句一定一定一定要有where限制条件！否则整张表数据全部删除！！！
delete from 表名 where 条件;
# 删除一条记录
delete from my_table where id=4;
# 删除多条记录
delete from my_table where age in (18,19,20);
# 删除全部记录(不用delete语句，效率低，并且删除全部数据需谨慎)
truncate my_table;
数据库导入导出(备份)
下面所有的dql语句和mysql函数都会用到老杜的sql文件，这里先说如何对sql文件导入及导出
两种方式导入sql文件
-
第一种是未登录mysql情况下，在cmd命令行或linux命令行界面直接导入
这里导入本机数据库，如果是远程数据库需要再加-h指定ip
如果sql文件中已经有指定数据库，可以不用在命令行中指定数据库
该命令在cmd和linux下都可以使用(只是sql文件路径有一点差别)
mysql -uroot -p yilko < e:\xxx\bjpowernode.sql -
第二种是登录mysql，在mysql里面使用命令导入sql文件
mysql -uroot -p yilko source e:xxx\bjpowernode.sql
导出数据
-
导出整个库
基本格式：mysqldump -hxxx -uxxx -p 数据库 > 导出路径+sql文件命名
mysqldump -uroot -p yilko > e:\xxx\yilko.sql -
导出某个表
基本格式：mysqldump -hxxx -uxxx -p 数据库 数据表 > 导出路径+sql文件命名
mysqldump -uroot -p yilko emp > e:\xxx\emp.sql
mysql函数
在了解dql语句前也需要先了解一些mysql常用的函数，在查询中可能会用到，先打个底了解下
以下只是提及到常用的，实际上mysql提供的函数有很多，没有提及到的可以去官方文档查
分组函数(聚合函数)
-
分组函数定义：
需要分组(group by)后才能使用的函数，总共有五个(count,sum,avg,max,min)
根据某个字段group by后，原本一张表散乱的数据被分类为多组
而分组函数可以对每个组进行操作，从而计算每个组中count,sum,avg,max,min的数据
# 根据部门分组，找出每个部门中工资最高的人 select deptno,max(sal) from emp group by deptno; -
分组函数一般都要配合group by使用，这里只是简单讲解
-
详细使用请参考下面dql章节的分组查询
字符串函数
-
字符串拼接
基本格式：concat(字段或字符串1,字段或字符串2…)
# 字段与字段拼接 select ename,concat(ename,job) new_str from emp; # 字段与字符串拼接 select ename,concat(ename,'congrats') new_str from emp; -
字符串长度
基本格式：length(字段)
经过测试字符串里面左右中的空格都会计算在内
select job,length(job) len from emp; -
字符串截取
基本格式：substr(字段,下标,长度)
长度如果省略代表取到最后
下标不是从0开始，而是从1开始
下标正数代表从左往右数，下标负数代表从右往左数(与python切片类似)
mysql提供left()，right()从左右边截取，但是substr都可实现
# 从左边第二个开始截取，取两个长度 select job,substr(job,2,2) from emp; # 如果截取长度超过原字段，超出部分忽略 select job,substr(job,3,5) from emp; # 从右边第三个开始截取，截取两个长度 select job,substr(job,-3,2) from emp; # 从左边第两个开始截取，一直截取到最后 select job,substr(job,2) from emp; -
字符串大小写
基本格式：lower(字段)
select lower(job) my_job from emp;基本格式：upper(字段)
select upper(job) my_job from emp; -
字符串去除左右空格
基本格式：trim(字段)
只能去掉左右空格，并不能去掉中间空格
select trim(concat(ename,'- ')) new_str from emp; # 与上面语句展示对比效果 select concat(ename,'- ') new_str from emp; # 展示不能去掉中间空格 select loc,trim(loc) from dept; -
字符串替换
基本格式：replace(被替换字段,被替换的字符串,新替换的字符串)
如果字段中没有符合条件，则被跳过不替换
select ename,replace(ename,'S','--') rep from emp; -
字符串反转
基本格式：reverse(字段)
select ename,reverse(ename) from emp; -
字符串小练习
把emp表中ename改为只有首字母大写，其他小写
mysql中没有提供首字母大写，其他小写的的函数
因此需要自己截取，其他部分改小写，再拼接
select ename,concat(substr(ename,1,1),lower(substr(ename,2))) name from emp;
日期时间函数
-
获取当前系统日期和时间(datetime类型)
select now(); -
获取当前系统日期(date类型)
select curdate(); -
提取年月日时分秒
接收一个date或datetime类型的参数，可以返回年、月、日、时、分、秒
如果接收date类型，但是查询时分秒，默认为0时0分0秒，查出来的结果都是0
select year(now()); select month(now()); select day(now()); select hour(now()); select minute(now()); select second(now()); # 传date类型，获取秒，返回0 select second(curdate()); -
计算时间差
基本格式：timestampdiff(间隔类型,前一个日期,后一个日期)
间隔类型：year,month,day,hour,minute,second,week,quarter(季度)
前一个日期必须是小的，后一个日期必须是大的，否则得出的结果会是负数
如果日期是date类型，但需要计算时分秒的间隔，会默认把date默认为0时0分0秒计算
# emp表，计算每个员工入职年份距离现在有多少年 select ename,hiredate,timestampdiff(year,hiredate,now()) year from emp; # emp表，计算每个员工入职年份距离现在是多少秒 # hiredate字段没有时间，默认以0时0分0秒来算 select ename,hiredate,timestampdiff(second,hiredate,now()) second from emp; -
字符串转date类型
mysql具有默认日期格式，使用该格式，字符串类型的时间会自动转成时间类型
而无需使用函数对字符串和日期进行转换
mysql默认日期格式：
date类型：‘%Y-%m-%d’
datetime类型：‘%Y-%m-%d %H:%i:%s’
# 单独创建一个表讲解字符串转date，date转字符串这两个函数 CREATE TABLE teacher( id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '老师id', `name` VARCHAR(20) NOT NULL COMMENT '老师名字', birthday DATE COMMENT '老师生日', create_time DATETIME COMMENT '创建时间' );字符串转date场景，在插入记录时体现
使用默认日期格式：
写的时候是字符串，插入的时候自动把字符串转为date类型
insert into teacher values(1,'xcvb','2000-1-1','2024-04-19 12:00:00');使用自定义格式：
基本格式：str_to_date(‘字符串日期’,‘日期格式’)
实际上很少用该函数，因为自定义日期格式插入记录后，
实际上mysql存储的还是默认格式的日期，等同白搞，还不如就使用默认格式来的方便
INSERT INTO teacher VALUES(2,'95511',STR_TO_DATE('2000/1/1','%Y/%m/%d'),STR_TO_DATE('2024_04_19_14_58_00','%Y_%m_%d_%H_%i_%s')); -
date类型转字符串
与上面同理，使用默认日期格式，mysql可以自动把时间类型的数据自动转为字符串类型
date转字符串场景在查询数据时体现
teacher表中的birthday和create_time字段从日期类型转为字符串类型展示
使用默认日期格式：
# 查找teacher表 select name,create_time from teacher;使用自定义日期格式：
无论插入的记录是否默认日期格式，最终mysql存储都是默认格式，所以mysql查询到的时间都是默认格式，而如果不想展示是默认格式，就需要用到date_format()函数
基本格式：date_format(字段,‘日期格式’)
# 查找teacher表，把create_time字段日期改为自定义格式 select name,date_format(create_time,'%Y_%m_%d_%H_%i_%s') my_time from teacher;
控制流程函数
-
if函数
当需要基于某个字段的布尔结果(筛选条件)来动态展示数据时就会用到if函数，例如数据筛选
简单理解为不是想展示该字段数据，而是根据筛选条件分为符合、不符合两种结果
基本格式：if(字段限制条件,满足条件输出的内容,不满足条件输出的内容)
# emp表，sal字段大于等于1000视为高工资，小于1000为低工资 select ename,sal,if(sal>=1000,'high','low') sal_level from emp; # emp表，comm字段为空或为0，显示无奖金，其他显示奖金数目 select ename,comm,if(comm is null or comm=0,'无奖金',comm) has_comm from emp; -
case when函数
有两种格式：
case ‘字段’ when ‘字段值(不能判断空)’ then ‘结果’ when…then…else ‘结果’ end
case when ‘布尔表达式’ then ‘结果’ when…then…else ‘结果’ end
第一种适用于对某个字段进行枚举判断，第二种适用于范围判断(跟if类似但分支更多)
没有else不符合条件的字段默认显示为null
case when涉及很长语句，本质上还是一个字段，展示一列数据(只是多了筛选过程)
# emp表，deptno枚举10，20，30，分别对应会计，研究，销售 # 可以自己尝试一下去掉30，那么30部门对应的就会显示null SELECT ename,deptno, CASE deptno WHEN 10 THEN '会计' WHEN 20 THEN '研究' WHEN 30 THEN '销售' END dept_name FROM emp; # emp表，hiredate在1980及之前为元老，1981是开荒员工，1982是老油条，1982以后是新人 # 如果不加else字段，那么1982年以后的都显示null SELECT ename,hiredate, CASE WHEN hiredate < '1980-12-31' THEN '元老' WHEN hiredate BETWEEN '1981-1-1' AND '1981-12-31' THEN '开荒员工' WHEN hiredate BETWEEN '1982-1-1' AND '1982-12-31' THEN '老油条' ELSE '新人' END people_level FROM emp;
逻辑函数
-
空处理函数
空字段进行数学运算(加减乘除取余)时都为空
使用空处理函数可把空字段赋予某个值进行运算，大多数情况是把空字段当成0
空处理函数有两种，第一种为ifnull，第二种为coalesce
ifnull只能在mysql中使用，coalesce在其他数据库中是通用的空处理函数
基本格式：coalsece(expression1, expression2, …, expressionN)
空字段当成0参与计算
# 计算emp表每个人的工资+奖金总数 select ename,sal+coalesce(comm,0) sum_sal from emp;空字段赋值其他字段(一个一个查找不为空的字段)
字段1为空找字段2，字段2为空找字段3，以此类推，最后找个默认值兜底
老杜的表没有该函数的使用场景，自己创建一个场景
假设有一个学生表，表中有固话，爸爸手机号码，妈妈手机号码
现需要找到某个学生的家属联系方式
联系方式优先找妈妈，妈妈找不到找爸爸，爸爸找不到找固话，固话找不到返回null
# 创建表 CREATE TABLE student( id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '学生id', `name` VARCHAR(20) NOT NULL COMMENT '学生名字', dad_contact VARCHAR(11) COMMENT '父亲电话', mom_contact VARCHAR(11) COMMENT '母亲电话', landline VARCHAR(8) COMMENT '固定电话' ); # 插入数据 INSERT INTO student VALUES(1,'where','12311112222','12333334444','84608888'), (2,'tyuio','12355556666','12377778888',NULL), (3,'where','12399998888',NULL,'84621234'), (4,'ltsz',NULL,'12312341234','84601111'), (5,'grlt',NULL,NULL,'84662222'), (6,'gofy',NULL,'12345671111',NULL), (7,'dfgh','12312346789',NULL,NULL), (8,'hjkl',NULL,NULL,NULL); # 使用coalesce查询上面场景结果 SELECT `name`,COALESCE(mom_contact,dad_contact,landline,NULL) contact FROM student;
数学函数
-
四舍五入
基本格式：round(值，取几位小数)
第二个参数为0代表取整
第二个参数为负数，代表往整数位取整(-1代表个位，-2代表十位以此类推)
select round(12.45,0); # 12 select round(12.45,1); # 12.5 select round(12.45,-1); # 10 select round(12.45,-2); # 0 -
向上取整
select ceiling(10.1); -
向下取整
select floor(10.9); -
生成[0,1)区间随机浮点数
基本格式：rand()
无参数，但有很多个小数点，一般需要配合round指定位数
select rand(); select round(rand(),2); -
数学函数小练习
emp表comm字段加上一个1-100以内的随机整数
rand()的范围是[0-1)，所以乘100就变成[0-100)
地板除可以保证rand()*100最大为99，再加1可以实现[1-100]区间
select ename,comm,coalesce(comm,0)+floor(rand()*100)+1 add_comm from emp;emp表comm字段加上一个1-100以内的随机两位小数
rand()的范围是[0-1)，所以乘100就变成[0-100)
但是round()会四舍五入，rand()>0.995时，rand()*100变成99.5，再四舍五入就变100
如果再加1，最终范围就会变成[1-101]，不符合要求
所以可以得出rand()*99就可以保证最大为98.x，再四舍五入最大为99，再加1符合范围要求
select ename,comm,coalesce(comm,0)+round(rand()*99+1,2) add_comm from emp;
系统信息函数
-
查看当前mysql数据库版本号
select version(); -
查看当前使用哪个数据库
在cmd时可能不知道自己选择了哪个数据库，可以用该函数查看
select database(); -
查看当前用户信息
select user();
查询数据(dql)
使用mysql，用的最多的是查询语句，ddl和dml这些都相对少，因此dql是学mysql中最重要的
下面使用到的sql语句都是来自上面数据库导入导出章节的sql文件
select基本格式
select [all | distinct] [字段 | * | 字面值]
from [database_name].table_name [as 别名]
[(left | right | inner) join table_name2 [as 别名] on 表连接条件]
[where 条件(可多个)]
[group by 字段(可多个) having 条件(可多个)]
[order by 字段(可多个) asc | desc]
[limit 偏移量,返回行数]
-
all代表查所有，实际查询可省略all，默认查所有，如果不查所有则需要加distinct去重
-
查询的内容可以是字段，字面值或者*(代所有字段)，同时字段支持算术运算
-
当没有指定库或者指定A库但要用B库的表时，需要表名前面加库名
-
表和字段都可以加别名，可以是as+空格+别名，也可以省略as直接空格+别名
-
表连接有三种，其中inner join可省略inner，表连接需要给表起别名(要识别字段是哪个表)
-
where可以筛选多个条件，筛选方式有多种，下面会讲解
-
group by按照字段分组，分组后筛选使用having
-
order by按照字段排序，支持多个字段排序
-
limit限制返回条数，一般用于分页，如果limit后只有一个数字代表返回多少行
-
执行顺序：
-
查表(from)和连表(join)
-
过滤(where)
-
分组(group by)
-
分组后过滤(having)
-
查询结果(select)
-
排序(order by)
-
限制行数(limit)
-
去重查询(distinct )
-
distinct有去重的功能，支持多个字段联合去重(多个作为整体，全部字段一样才认为重复)
-
可以与count()函数结合使用计算去重后的总数
# 查找emp表有哪些职位 select distinct job from emp; # 查找emp表部门有哪些职位 select distinct job,deptno from emp; # 统计emp表有多少个职位 select count(distinct job) job_nums from emp;
字段算术运算与字面值
-
加减乘除取余
字段可以使用数学表达式参与运算(加减乘除取余)
如果字段为空，进行数学运算也是为空，使用空处理函数把null当成0参与计算
# 计算员工的年薪，包括工资和奖金，sal每个月一次，comm一年一次 select ename,sal*12+coalesce(comm,0) year_sal from emp; # 计算每个员工日薪，月工资/21.75为日薪，并保留两位小数 select ename,round(sal/21.75,2) day_sal from emp; # 计算工资最高和工资最低的相差多少 select max(sal)-min(sal) diff_value from emp; # 计算员工的编号是是单数还是双数，单数显示单数，双数显示双数 select empno,if(empno%2=0,'双数','单数') '单双数',ename from emp; -
字面值
字面值定义：直接写入的、表示固定数据值，不随运行时动态变化，是硬编码
select后面除了写字段，还可以跟字面值，查询的数据返回几条，就返回几个字面值
字面值可以是数字，字符串，字符串形式的日期格式，布尔值，null值
布尔值默认会转为01，false代表0，true代表1
# 数字 select 1 as num from emp where deptno=10; # 字符串 select 'peppa pig' as '粉色吹风机' from emp; select '中文也ok' as chinese from emp where comm is null; select '2000-1-1' as str_date from emp group by job; # 布尔值 select false as bool from emp where mgr is not null; select true as bool from emp where empno%2=0; # null值 select null as '空值' from emp where length(ename)>5;
库名
-
查询时没有指定库名(use xxx)，查询的表需要加库名
-
指定A库，但使用B库的表时，查询需要加库名
# 登录mysql，查看所选数据库 select database(); # 没有选库的情况下查询emp表(提示没选数据库) select * from emp; # 选了yilko库的情况下查询mysql库的user表 use yilko; # 会查yilko的user表，但yilko库没有，会报错 select * from user; # 不查yilko库，去查mysql库，可以找到，不报错 select * from mysql.user;
起别名(alias)
-
可以给表或字段起别名
-
别名可省略as，空格+名字即可
-
别名可以是中文，最好加单引号(实际试过不加也可以)
# 给emp表起名为e，查询ename，job，deptno字段都加上表别名前缀 select e.ename,e.job,e.deptno from emp e; # 给emp的mgr字段起别名叫领导 select mgr '领导' from emp; # 统计总人数，并起别名为nums，使用as起别名 select count(*) as nums from emp;
连表查询(join)
-
交叉连接(cross join)
也就是笛卡尔积，两张表连接查询没有条件限制情况下出现，查询结果为两张表条数乘积
只要是表连接就一定会出现笛卡尔积现象，增加条件过滤后(内/外连接)也无法避免
条件过滤是出现笛卡尔积后才过滤，并不是在笛卡尔积之前过滤
因此展示的结果实际是笛卡尔积后的结果再过滤来展示的有效数据
无效数据并不是没有，只是不展示而已
交叉连接实际上用的不多，更多的是内连接和外连接，但是要知道交叉连接的
# 查看emp表和salgrade表总条数 select count(*) from emp; select count(*) from salgrade; # 交叉连接 select * from emp join salgrade;左表 右表 a A b B c C d D a对ABCD，b对ABCD，c对ABCD，d对ABCD一共是4*4=16条记录
-
内连接(inner join)
可以省略inner，一般写sql语句只写join
内连接是在交叉连接基础上，筛选左表和右表同时都有的数据进行展示
简单理解就是两个圆取交集
# 取emp表和dept表都是deptno相等的数据，不相等的数据被过滤不展示 select * from emp e join dept d on e.deptno=d.deptno; # 取emp表和salgrade表薪资范围在对应区间的数据，不在区间的被过滤不展示 select * from emp e join salgrade s on e.sal between s.losal and s.hisal;左表 右表 a null b B c C d D null E b对B，c对C，d对D，在笛卡尔积中只有3条是两表交集
-
外连接(outer join)
-
左外连接(left outer join)
可以省略outer，一般写sql语句只写left join
左外连接是在交叉连接基础上，展示左表全部数据+筛选左表和右表同时都有的数据
以左表为基准，右表与左表一一匹配，匹配上的正常显示，匹配不上用null填充
简单理解就是左边的圆全部显示
# dept做左表，emp做右表，dept的全部数据都会展示出来 # dept有一个部门40还没有人，但是也会显示出来，但是使用null填充 select * from dept d left join emp e on d.deptno=e.deptno;左表 右表 a null b B c C d D null E a对null，b对B，c对C，d对D，在笛卡尔积中只有3条是两表交集+1条左表数据
左外连接还有一种情况，可能有场景只需要右表没有的数据
也就是左边的圆，不要交集那部分，只要无交集部分
# dept做左表，emp做右表 # 左表有数据但右表没有，对应的连接字段右边为null，使用null筛选可达到目的 select * from dept d left join emp e on d.deptno=e.deptno where e.deptno is null;左表 右表 a null b B c C d D null E a对null，在笛卡尔积中只有1条左表数据是右表为null的
-
右外连接(right outer join)
可以省略outer，一般写sql语句只写right join
跟左外连接差不多，只是反过来，是以右表为基准，这里不再重复描述
# emp做左表，dept做右表 select * from emp e right join dept d on e.deptno=d.deptno;左表 右表 a null b B c C d D null E b对B，c对C，d对D，null对E在笛卡尔积中只有3条是两表交集+1条右表数据
右外连接也有场景可能只需要左表没有的数据
同样是使用null筛选达到目的，但是使用左表的字段
# emp做左表，dept做右表 select * from emp e right join dept d on e.deptno=d.deptno where e.deptno is null;左表 右表 a null b B c C d D null E null对E，在笛卡尔积中只有1条右表数据是左表为null的
-
全连接(full outer join)
mysql不支持全连接，但可以通过union实现，左连接 union 右连接
左表有右表没有的会展示，左表没有右表有的都会展示，就是两个连接的联合
并且union会把完全相同的行去除(去重)
因为两个表联合实际上交集部分是有两次记录，存在重复的
全连接可以简单理解为两个圆的并集
# 左连接联合右连接 select * from dept d left join emp e on d.deptno=e.deptno union select * from dept d right join emp e on d.deptno=e.deptno;左表 右表 a null b B c C d D null E a对null，b对B，c对C，d对D，null对E在笛卡尔积中只有3条是两表交集+2条非交集数据
全连接也有场景可能只需要非交集那部分
select * from dept d left join emp e on d.deptno=e.deptno where e.deptno is null union select * from dept d right join emp e on d.deptno=e.deptno where d.deptno is null;左表 右表 a null b B c C d D null E a对null，null对E，在笛卡尔积中只有2条是两表非交集的部分
-
-
自连接(self join)
同一张表自己进行连接，左表和右表都是自己
在以下场景中可能会用到自连接：
-
字段中存在层级关系
如领导和员工、菜单层级、文件目录结构等
# 查找emp表每个员工对应的领导名字，展示员工编号，员工名字，领导名字 # 因为king没有领导，也得输出他的信息，需要用到左连接 select a.empno,a.ename '员工',b.ename '领导' from emp a left join emp b on a.mgr=b.empno; -
比较表自身行(与下面查找字段重复项类似，都可归为一类)
如某行A字段值为B，查找还有哪些行的A字段也是B
可能首先会想到用where A=B，但这种情况基于知道值为B才能用
如果不知道A字段的值，就无法用where，可以用子查询或自连接实现
# 查找与allen相同职位的员工，显示名字+职业 # 通过job自连接，就是对job的笛卡尔积，如果同职位有5个，总共有5*5=25条记录 # 如果过滤a表的allen，就要展示b表的名字，反之过滤b表就要展示a表名字 # 如果过滤a表还展示a表，就全是allen名字 # 同时需要过滤自身allen select a.ename,a.job from emp a join emp b on a.job=b.job where b.ename='allen' and a.ename!='allen'; # 子查询也能实现，相对来说比自连接好理解一些 # 先找到allen的职位 select job from emp where ename='allen'; # 把子查询当成一个值去查其他人的职位，并排除自己 select ename,job from emp where job=(select job from emp where ename='allen') and ename!='allen'; -
查找字段重复项
如查找A字段中相同的值都有哪些行
与上面最大的差别是一个是针对行相同的值，这个是针对表相同的值
也与distinct有不同，是去掉重复，而这个是查找重复
这个其实很好理解，查找重复项从数学角度来看就是
A跟其他人比工资，B跟其他人比工资，以此类推
每个人都跟所有人比一下，遇到一样的就记录，本质上就是表自身的笛卡尔积
# 查找具有相同工资的人，显示名字和工资 select a.ename,a.sal from emp a join emp b on a.sal=b.sal and a.empno!=b.empno;
-
-
三表查询
三表或以上其实和两个表查询是一样的
可以理解为A跟B组合后，生成临时表t，临时表t再和C组合
# 查看每个人的部门名称和薪资水平，显示名字，部门编号+名字，薪资+薪资水平 select e.ename,e.deptno,d.dname,e.sal,s.grade from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and s.hisal; # 还可以在上面基础上对grade排序，二级排序等 select e.ename,e.deptno,d.dname,e.sal,s.grade from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and s.hisal order by grade desc,deptno,ename; -
union联合
union合并要求查询的列数相同，列名相同，列的数据类型相同
union是把两个表相加，因此要求列完全一致
union合并时会默认去重，如果不需要去重可以使用 union all
在实际中很少使用到union，知道使用条件，什么场景用即可
# 上面连表查询有一个全连接，就用到union字段 # 因为三个表没有相同的字段，没有办法展示union使用 # 这里只展示基本格式 select field1,field2... from table1 union [all] select field1,field2... from talbe2; -
表连接时的一些注意点或小知识
-
表连接时建议加表名，有以下两个原因：
第一不加表名时查字段会从两个表一起查，加了表名只查一个表，会提高查询效率
第二两个表有同个名字的字段，查询时用到该字段，mysql因不知道使用哪个而报错
# emp表和dept表同时有deptno字段，不加表名连表查就会报错 select * from emp join deptno on deptno=deptno; -
在sql当中，表和表之间连接次数越多，效率越低，因为笛卡尔积。根据实际需求可以用数据冗余换取执行速度(不分表)
-
union和join有本质区别，union是对表做加法，把两个表的数据加起来展示，要求列数列名列类型一模一样，总共有m+n条记录。而join是对表做乘法，进行笛卡尔积，总共有m*n条记录。从效率上来说，union比join高，但前提是支持使用union
-
多表查询时，可以内连接、外连接、自连接混合使用，但实际使用过程中需要知道哪个是主表，哪个表的数据是一定要展示的。
-
条件查询(where过滤)
-
逻辑运算符
and或者&&，逻辑与，两者为真才为真
or或者||，逻辑或，只要一个真就是真
not或者!，逻辑非，条件为假，结果返回真
如果同时存在多个逻辑运算符，and的优先级比or高，如果想or部分先执行需要加小括号
如果多个逻辑运算符下不知道运行顺序，把自己想要先执行的部分加小括号即可
# 查找职业为clerk同时部门为20的人 select ename,job,deptno from emp where job='clerk' and deptno=20; # 查找工资大于2000或者奖金不为0和空的人 # 第一种写法利用空处理函数，把空当成0，然后筛选非0条件 select ename,sal,comm from emp where sal>2000 or coalesce(comm,0)!=0; # 第二种是判断非空和判断非0，但两者是并列关系要用and select ename,sal,comm from emp where sal>2000 or (comm is not null and comm!=0); -
比较运算符(单词)
is [not] null，判断某个字段是否为空
[not] in，判断某个字段的值是否在集合内(可以是字符串或者数字)
between A and B，判断某个字段是否在b和c之间(左右都是闭区间且左小右大)
like，模糊查询，%匹配0或多个字符，_匹配任意一个字符，匹配字符本身需要\转义
# 查找哪些人的奖金不为空 select ename,comm from emp where comm is not null; # 查找哪些人的领导为空 select * from emp where mgr is null; # 查找哪些人的职业是clerk或manager select ename,job from emp where job in ('clerk','manager'); # 查找哪些人的工资在1000-1500范围内 select ename,sal from emp where sal between 1000 and 1500; # 查询哪些人的名字是四个字母的 select ename from emp where ename like '____'; # 查询哪些人的名字是j开头，s结尾的 select ename from emp where ename like 'j%s'; # 查询哪些人的名字第二个字母是l，中间或最后包含e的 select ename from emp where ename like '_l%e%'; # 查询哪些人的名字是a开头或n结尾的(多个条件时需要写明 字段 like 'xxx') select ename from emp where ename like 'a%' or ename like '%n'; # 查询哪些人的名字是n结尾并且长度是5的 select ename from emp where ename like '%n' and length(ename)=5; -
比较运算符(符号)
大于、小于、等于、大于等于、小于等于、不等于
# 查询入职年份大于1981年，并且工资小于2000，名字长度等于4的人 select * from emp where hiredate>'1981-1-1' and sal<2000 and length(ename)=4; # 查找年份的另一种写法 select * from emp where year(hiredate)>1980 and sal<2000 and length(ename)=4; # 查找工资大于等于2000，名字长度小于等于4位，入职年份不为12月的人 select * from emp where sal>=2000 and length(ename)<=4 and month(hiredate)!=12;
分组查询(group by&having)
-
分组查询基本格式：
select 分组字段,分组函数(返回一个值的子查询) from 表 group by 分组字段 having 条件;
-
分组查询注意点：
-
使用分组函数时，一般需要先分组(group by)再使用
分为一组就返回一个数据，两组返回两个，以此类推
# 根据deptno分组，每一组统计工资最高的 # 分为三组，返回三个数据 select deptno,max(sal) from emp group by deptno; -
特殊情况：不分组的时候使用分组函数，会默认把整张表当一组(可省略group by)
此时只有一组，只会返回一个数据
# 不加group by，查询表中最高工资 # 一张表作为一组，只返回一个数据 select max(sal) from emp; -
分组函数支持组合使用
使用分组函数后，每一组只有一个值，每个分组函数互不影响(一对一不影响，一对多不行)
分组后使用count(字段)需要注意，遇到null不统计，需要coalesce改为非空再统计
# 根据deptno分组后，统计工资最高，工资最低，平均工资，工资总和，总条数 select deptno,max(sal),min(sal),avg(sal),sum(sal),count(sal) from emp group by deptno; -
分组函数支持统计不同字段
都是针对分组后的数据进行聚合，都是一组返回一个值，每个字段其实互不影响
# 根据deptno分组后，统计平均薪资，最近入职日期 select deptno,avg(sal),max(hiredate) from emp group by deptno; -
分组字段支持多个
简单理解为AB两个字段都相同时作为一组，联合分组
实际场景中也会遇到，比如：查询每个xxx下的每个xxx
# 查询每个部门下都有哪些职位 select deptno,job from emp group by deptno,job; -
分组字段与查询字段
select 分组字段,分组函数(子查询)[可多个] from 表 group by 分组字段;
分组后每一组只有一行数据，因此要求查询字段(select后面跟的字段)只能是单个值
如果分组后返回一行数据，但是查某个字段时返回多行，mysql就会报错
查询字段返回单个值的场景有：
-
分组函数(最常见)
# 单字段分组 # 根据deptno分组后，统计平均薪资，最近入职日期 # 分组函数会把每一组的数据都聚合成单个值，因此根据四个组输出四个不同的数据 select deptno,avg(sal),max(hiredate) from emp group by deptno; # 联合分组 # 根据部门分组，查询每个部门最近入职的人的时间，并且显示部门名字 # 第一步先根据deptno连表查，第二步联合deptno和dname分组查最近入职时间 select d.deptno,d.dname,max(e.hiredate) lastest_date from emp e join dept d on e.deptno=d.deptno group by d.deptno,d.dname; -
字面值
# 根据deptno分组后，实际分为四组 # 查询的字面值本身就是返回单个值，因此跟着四组输出四次共四行 select deptno,'xdnhx' as '兄弟你好香' from emp group by deptno; -
子查询(返回单个值)
# 查询每个职位最近入职日期的人的工资 # job分组之后的值会传给子查询，有多少组子查询就执行几次 # 子查询根据外部查询的值过滤条件(比如第一次过滤clerk，第二次是salesman) # 过滤后根据日期倒叙取第一个，即该职位最近入职时间，然后查它的工资 select job,(select sal from emp b where b.job=a.job order by hiredate desc limit 1) max_date_sal from emp a group by job;
分组字段(group by后面跟的字段)并不一定要写在查询字段那里
但是分组后查询的字段一定要是单个值
# 上面的字面值例子去掉查询deptno # 可以去掉查询deptno分组字段，但是还是四组 # 字面值字段根据四组输出四次共四行 select 'xdnhx' as '兄弟你好香' from emp group by deptno; # 一个表作为一组 # 也可以不需要分组字段，但查询字段要单个值 # 因为只有一组，所以就只有一行 select count(*),max(hiredate),avg(sal),sum(comm),min(deptno) from emp; -
-
分组函数自动忽略null，但不同情况需不同处理方式
如果使用sum,avg,max,min等，碰到null不需要空函数处理，会降低执行效率
如果使用count时，碰到null需要加空函数，否则该条记录被忽略不统计在内
# 分组计算comm总工资 select sum(comm),comm from emp group by comm; # 分组计算comm条数 # 错误做法，因为统计到comm为null的字段条数显示0，因为null被忽略 select count(comm),comm from emp group by comm; # 正确做法，改为非null状态，记录到空的条数为10条 select count(coalesce(comm,0)) count,comm from emp group by comm; -
分组函数不能用在where子句中
从执行顺序讲，分组函数要在语句执行分组后才能使用(group by)，而where的执行顺序在group by字段之前，所以where执行时还没分组，不能使用分组函数，强行使用会报错
而select执行在group by后面，所以可以使用
但实际上确实会有这种场景，处理方案是把分组函数放到子查询
# 计算哪些人员的工资高于平均工资 # 错误做法 select ename,sal from emp where sal > avg(sal); # 正确做法 select ename,sal from emp where sal > (select avg(sal) from emp); -
where和having的使用
where和having都有过滤的功能，但where用于分组前过滤，而having用于分组后过滤
having需要配合group by一起使用，不能单独使用
having的查询效率比较低，确认所需字段必须使用分组后的数据，才用having
如果所需字段在分组前就能过滤的，优先使用where
# 统计部门中超过5人的部门有哪些 # 题目中的5人是分组后的数据才过滤，使用having select deptno,count(*) from emp group by deptno having count(*) >= 5; # 统计哪些职位的最低工资大于1500，并且统计这些职位对应的人数 # 这个题目并不能where过滤，题目要求职位每个人最低工资都要大于1500 # 如果where筛选，假如A职位有两个人分别是1000和2000工资 # 按题目是要求A职业不符合，但是会被where过滤出来，因此where不适用 # 应该先按职业分组求每个组最小值 select job,min(sal) min_sal from emp group by job; # 因为涉及到分组后的数据过滤，所以用having select job,min(sal) min_sal from emp group by job having min(sal)>1500; # 最后加上每个职位人数 select job,min(sal) min_sal,count(*) nums from emp group by job having min(sal)>1500; -
分组函数不支持嵌套使用，想实现该效果只能通过子查询(mysql不支持，oracle支持)
某些场景需要用到嵌套分组函数，比如基于分组后的结果，需要再一次分组
例如一共x个部门，算出了每个部门平均工资，但是还需要知道哪个部门平均工资最低
# 计算平均工资最低的部门(展示部门+工资) # 第一步先得出每个部门的平均工资 select avg(sal) avg_sal from emp group by deptno; # 第二步取平均工资最低 select min(t.avg_sal) min_avg_sal from (select avg(sal) avg_sal from emp group by deptno) t; # 第三步获取工资最低的部门及工资 select deptno,avg(sal) min_avg_sal from emp group by deptno having avg(sal)=(select min(t.avg_sal) min_avg_sal from (select avg(sal) avg_sal from emp group by deptno) t); -
count(1),count(*),count(字段)三者区别与比较
mysql官方文档中有解释到，存储引擎使用innodb的情况下
count(*)和count(1)没有性能差异，因此不存在说count(1)比count(*)快的说法
count(*)和count(1)都会计算包含null在内的行数
而count(字段)会忽略null行，除非主键字段
从效率上来说count(*)=count(1)>count(主键)>count(非主键列)
-
排序查询(order by)
-
对结果集排序，不指定排序默认使用asc升序
-
asc(ascend)代表升序，desc(descend)代表降序
-
排序支持使用字段或数字来指定列
-
字符串排序默认使用一个个字母对比方式，如果想根据长度排序，可以使用length(字段)
-
支持多个字段排序，使用英文逗号分割，第二个字段用于二次排序(最多排16个字段)
当第一个字段的数据相同时，比较第二个字段，以此类推
多个字段排序时，每个字段都要指定升降序，只写一个降序只对最后一个字段生效
# emp表工资升序排序 select * from emp order by sal; select * from emp order by sal asc; # emp表入职时间降序排序 select * from emp order by hiredate desc; # 指定第2列降序排序 select * from emp order by 2 desc; # 比较职位，职位相同情况下比较工资，工资相同按照入职时间,降序排列 select * from emp order by job desc,sal desc,hiredate desc; # ename根据长度降序排序，长度相等情况下再用默认的一个个字母对比方式排序(升序) select * from emp order by length(ename) desc,ename;
分页查询(limit)
-
基本格式 limit 下标,长度
-
下标从0开始，代表第一条数据
-
如果只有一个数字，代表取的长度，忽略下标。等同于 limit 0,长度
-
如果限制条数大于实际有的条数，多的忽略不会报错
-
通用公式：limit (page_num -1)*page_size,page_size
-
page_num代表第几页，page_size代表每页多少条
-
按照公式，假如想知道第20页的数据(5条一页算)，可以很快算出应该展示limit 95,5的数据
# 查看第1-5条数据(第一页) # 两种写法结果一样，但一般从第一条开始拿数据都用第一种省略0下标方式 select * from emp limit 5; select * from emp limit 0,5; # 查看第6-10条数据(第二页) select * from emp limit 5,5; # 查看11-15条数据(第三页) # 因为表实际只有14条，这里最多就显示4条，限制15不会报错 select * from emp limit 10,5;
子查询
-
子查询从本质上来说就是select语句嵌套select语句，可以多层嵌套
-
子查询在实际场景的复杂查询中用的不少，需要看懂并理解
-
子查询的结果可以返回一个值、一行记录、一列记录、一个表等
-
子查询效率比表连接查询低，实际中优先连表查，不行再考虑子查询
-
从出现的位置上来分类，常用的大概分为三种
-
where后面出现子查询(出现最多)
where接的子查询可以是一个值或者是一列记录(一组值)
# emp表，统计哪些人高于平均工资 # 第一步，计算平均工资，返回一个值 select avg(sal) from emp; # 第二步，计算哪些人大于平均工资 select * from emp where sal>(select avg(sal) from emp);# emp表，统计与职位为clerk相同领导的其他员工的姓名、职位、领导 # 第一步，查找职位为clerk的领导列表，返回一列(多个值) select distinct mgr from emp where job='clerk'; # 第二步，查找除职位clerk外，哪些职位的领导也在这些选项中 select ename,job,mgr from emp where job!='clerk' and mgr in (select distinct mgr from emp where job='clerk'); -
from后面出现子查询(join临时表)
from接的子查询可以是一行记录或者是一个表
其实返回一行和一个表，本质上都是当成临时表进行连接查询
# 查找哪些员工的工资在1档 # 第一步，找到1档的记录(返回一行记录) select * from salgrade where grade=1; # 第二步，与第一步的行记录结合筛选哪些人的工资是1档 select e.ename,e.sal,t.grade from emp e join (select * from salgrade where grade=1) t on e.sal between t.losal and t.hisal; # 别的思路，先连表查，再筛选1档工资(不需要用到子查询) select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal where s.grade=1;# 展示部门编号、部门名字、每个部门员工最近的入职日期(涉及emp和dept表) # 第一步，查找每个部门最近的入职日期(返回一张临时表) select deptno,max(hiredate) from emp group by deptno; # 第二步，查找部门名字和编号，与第一步结合成一张表 select d.deptno,d.dname,t.lastest_date, from dept d join (select deptno,max(hiredate) lastest_date from emp group by deptno) t on t.deptno=d.deptno; # 别的思路，先连表查再筛选最近的入职日期(不需要用到子查询) select d.deptno,d.dname,max(e.hiredate) lastest_date from emp e join dept d on e.deptno=d.deptno group by d.deptno,d.dname; -
select后面出现子查询
select接的子查询只能是一个值(sql规范要求)
如果返回多行多列，就会出现表格嵌套表格情况
# 查找每个职位最近入职的人的工资，展示职位、入职时间、工资 # job分组之后的值会传给子查询，有多少组子查询就执行几次 # 子查询根据外部查询的值过滤条件(比如第一次过滤clerk，第二次是salesman) # 过滤后根据日期倒叙取第一个，即该职位最近入职时间，然后查它的工资 select job,max(hiredate) lastest_date, (select sal from emp b where b.job=a.job order by hiredate limit 1) lastest_sal from emp a group by job;
-
权限管理(dcl)
对数据库账户以及访问权限的控制，常见的有create、drop、alter、grant、revoke指令
查看用户
-
用户存放在mysql库user表，直接查表即可
user表中，host字段代表记录哪个主机或ip地址可以连接mysql
如果为%代表该用户可以用任何主机连接，如果为localhost代表只能服务器本地连接
# 查看当前用户 select user(); # 查看所有用户 select user,host from mysql.user;
创建用户
-
使用create user语句，不建议在mysql库user表直接插入记录，需要手动配置其他东西
-
基本格式：create user 用户名[@主机名] identified by 密码;
-
用户名可省略主机名，密码需要单引号括起来(纯数字也要)
-
创建用户需要已登录用户拥有CREATE USER权限，否则报错
-
新创建的用户只有usage权限，该权限仅能登录连接数据库，增删改查无权限
# 不设置密码(默认空密码，直接回车登录即可) create user yilko; # 设置密码 create user yilco identified by '123456'; # 查看新用户权限(只有usage权限) show grants for yilko;
删除用户
-
基本格式：drop user 用户名
-
删除用户需要已登录用户拥有CREATE USER权限，否则报错
-
如果删除的用户不存在会报错
drop user yilko;
修改用户
-
修改用户名
基本格式：rename 旧用户名 to 新用户名
rename user yilco to yilko; -
修改用户密码
基本格式：
第一种：set password for 用户名=‘密码’
第二种：alter user 用户名 identified by ‘密码’
哪种都可以看个人喜好，但我个人偏向第二种因为和创建用户很像，比较好记
# set passowrd方式 set password for yilko='qwe123'; # alter方式 alter user yilko identified by 'qwer1234';如果是给当前用户修改密码，上面命令还可以进一步简化
set passwrd可以省略用户名
但alter不可以省略，必须指定用户名，可以用user()代替当前用户名
# set passwrd当前用户修改密码 set password='112233'; # alter方式当前用户修改密码 alter user user() identified by 'qqwwee';
授权用户
-
权限一般分为以下几类
-
系统权限：
涉及到mysql服务器级别的操作，允许执行特定的管理功能
但是因为也不是很常用，这里不列举，详细了解可查资料或问ai
-
数据库/表操作权限：
库的操作例如create、alter、drop
表的操作例如insert、delete、update、select
索引的操作例如index，允许创建和删除索引
-
全局权限：all [privileges]，授予除grant option之外所有简单权限
-
管理权限(grant option)：与其他权限独立开，它可以赋予用户可以将权限授权给其他人。(类似于成员和管理员区别，被授予管理权限就是管理员，否则就是普通成员)
-
-
授权
基本格式：grant 权限列表 on 库名.表名 to 用户名
如果不指定库名和表名使用*.*所有库所有表，但并不建议使用
如果再配上all，会把默认核心数据库的权限也授予
此时该用户就拥有高级系统管理权限，差不多有了等同root的权限
防止用户权限过大，一般指定数据库授予权限(database.*)，排除默认数据库的授权
# 给yilko用户授予yilko库所有表的增删改查权限和索引权限 grant insert,delete,update,select,index on yilko.* to yilko; # 给yilko用户授予yilko库emp表增删改表的权限 grant create,alter,drop on yilko.emp to yilko; # 给yilko用户授予yilko库所有权限 grant all on yilko.* to yilko;授权时是需要已知该用户权限的情况下(show grants for 用户名)进行调整的
因为grants只会一直往上叠加权限，而不会出现权限覆盖的情况
假设A用户A库已经有all权限，此时再给他授权select权限是无作用的
因为本来all权限已经包含select权限，也不会把select权限覆盖all权限
但如果真的有这个需求(从all权限改为只有select权限)，应该是先撤回权限再授予select权限
# 授予yilko库所有表select权限 grant select on yilko.* to yilko; # 授予yilko库所有表update和delete权限，授权更新 grant update,delete on yilko.* to yilko; # 授予yilko库所有表所有权限，授权更新 grant all on yilko.* to yilko; # 授予yilko库的所有表insert权限，授权不更新，因为all已经包含select权限 grant insert on yilko.* to yilko;
撤销授权用户
-
基本格式：revoke 权限列表 on 库名.表名 from 用户名
-
权限修改一般根据实际情况
如果权限复杂就全部撤回，然后重新授权
如果不复杂根据已有权限进行grant或者revoke
# 撤回yilko库所有表的所有权限 revoke all on yilko.* from yilko; # 撤回yilko库所有表的增删改权限，只留select权限(假设原本有增删改查四个权限) revoke insert,delete,update on yilko.* from yilko;
查看用户授予的权限
-
查看单个用户权限
-
基本格式：show grants for 用户名@主机
# 不指定主机，默认所有主机都显示 show grants for root; # 指定主机 show grants for root@localhost; # 查看当前用户权限 show grants; -
查看所有用户权限
需要自己查看mysql库的user表(基本权限和全局权限)、db表(用户在某个库上的权限)、table_priv表(用户在某个表上的权限)，如果想直观展示，需要这三个表进行连表查询。
刷新权限
-
修改用户权限后，理论上mysql会更新权限设置
但刷新权限可以确保权限缓存和权限实际设置保持同步
-
如果不是通过命令修改权限而是直接在权限表改权限，该情况一定要刷新权限
-
删除用户后再创建同名用户，也需要刷新权限确保权限被正确处理
flush privileges;
事务(tcl)
常见的指令有commit、rollback、start transaction
事务的介绍
- 定义：一个完整的业务逻辑，是最小的工作单元，不可再拆分，要么同时成功，要么同时失败
- 本质：多条DML语句同时成功或者同时失败
事务的使用场景
- 为什么需要事务：某些场景需要多条dml语句作为一个整体被执行，需要保证数据的一致性
- 举例银行转钱。A给B转钱，从操作上看A执行update扣钱，B执行update加钱。这是一个整体的动作。如果某条dml不成功这个场景就视为失败，回滚重来。两个dml都成功，该场景才成功。
- 涉及数据变化(dml的增删改)才会有事务的需求，事务就是为了保证数据的安全
事务的执行过程
- innodb引擎提供一组用来记录事务性活动的日志文件，事务执行时，每一条DML语句都会记录到事务性活动的日志文件中
- 提交事务：清空事务性活动的日志文件，将数据持久化到数据库表中。提交事务标志着事务以成功结束
- 回滚事务：将之前DML操作全部撤销，清空事务性活动的日志文件，提交事务标志着事务以失败结束
事务的使用
-
mysql默认使用innodb引擎，该引擎支持事务
-
默认情况下mysql支持自动提交事务，每执行一条dml语句就会提交一次事务，当需要事务是多条是需要用start transaction说明开启事务，此时会关闭自动提交事务
-
事务格式：start transaction; 各种dml语句; commit/rollback;
# 伪代码，实际没有表可操作 start transaction; update accout set money=money-1000 where id='A'; update accout set money=money+1000 where id='B'; commit;事务使用起来很简单，多个dml语句用start transaction和commit包起来即可
但实际上事务都是配合开发语言来使用
当检查到两个语句都修改成功，不抛出异常，正常commit提交，事务执行成功
当检查到两个语句任意一个不成功，抛出异常，捕捉异常处写rollback，事务执行失败回滚
# 以pyhton举个例子 # 连接数据库... try: # 开始事务 cursor.execute("START TRANSACTION;") # 减少用户A的账户余额 sql_A = "UPDATE accounts SET balance=balance-1000 WHERE user='A'" cursor.execute(sql_update_A) if cursor.rowcount == 0: # 检查是否有行受到影响，可以根据业务逻辑调整 raise ValueError("User A's balance update failed.") # 增加用户B的账户余额 sql_B = "UPDATE accounts SET balance=balance+1000 WHERE user='B'" cursor.execute(sql_update_B) if cursor.rowcount == 0: raise ValueError("User B's balance update failed.") # 提交事务 connection.commit() print("Transaction committed successfully.") except Error as e: # 如果有错误发生，回滚事务 connection.rollback() print(f"Transaction failed. Error: {e}") finally: # 关闭游标和连接 cursor.close()
事务的四个特性(ACID)
这四个特性是事务中的核心概念，确保了数据可靠性和一致性
- 原子性(Atomicity)：保证事务内的操作是不可分割的最小工作单元
- 一致性(Consistency)：事务执行前后，数据库的状态保持合法，确保数据的正确性有效性
- 隔离性(Isolation)：并发环境中，多个事务之间互不干扰。提供了四种隔离级别
- 持久性(Durability)：事务提交后永久性保存在数据库，系统故障也不会丢失
事务的四种隔离级别
-
读未提交(Read Uncommitted)：
最宽松的隔离级别
事务A可以读到事务B还未提交的数据，但会出现读到不对的数据，也叫脏读
-
读已提交(Read Committed)：
oracle的默认级别
事务A只能读取事务B已提交的数据，但会出现每次读的数据都不同，也叫不可重复读
-
可重复读(Repeatable Read)：
mysql的默认级别
事务A只能读取事务B已提交的数据并且多次读取数据的结果一致
但事务B的操作改了，A还是读B旧的数据，会出现幻读
-
序列化(Serializable)：
最高的隔离级别，可以处理脏读、不可重复读、幻读的问题。但并发的性能最低
事务只能排队进行(同步)，不能并发，只能执行完事务A，才能再进行事务B
# 查看mysql的隔离级别 select @@global.transaction_isolation; # 设置mysql的隔离级别 set global transaction isolation level 隔离英文;
约束
约束一般在创建表定义字段的时候已经写好。但也支持创建表后对约束添加或修改。创建表后再改约束已经会有数据，可能会导致添加约束失败，一般需要先处理数据。最好先备份数据再操作
常见的八种约束
- 八种约束在ddl的创建表章节中有做解释说明，这里不再详细叙述
- 主键约束(primary key)、非空约束(not null)、唯一约束(unique)，外键约束(foreign key)
- 自增长约束(auto_increment)，默认约束(default)，无符号约束(unsigned)，检查约束(check)
- 检查约束需要mysql从8.0版本才开始支持
列级约束和表级约束
-
在列属性后面直接加的为列级约束，不在列属性加而是另起一行的为表级约束
-
列级约束常见于主键、非空、唯一、自增长、默认、无符号约束
-
表级约束常见于外键、检查、联合约束(联合主键或联合唯一)
create table teacher( # 下面四个列属性都跟了约束，为列级约束 id int auto_increment primary key comment '教师id', name varchar(20) not null comment '教师名字', id_card char(18) unique comment '身份证号码', class_id int comment '班级id', sex tinyint UNSIGNED default 0 COMMENT '用户性别 0未知 1男 2女', # 不跟列属性写一起的就是表级约束 # 其中外键的class表没有创建，只是示例 foreign key (class_id) references class(id), check (length(id_card)=18) ) COMMENT '教师信息表';
创建表时定义约束
- 上面已有例子说明，这里不再重复叙述
创建表后添加约束
-
测试表准备
# 创建一个表用来增删改约束，下面的增删改约束都用到这个表 create table restraint( id int,name varchar(20),sex tinyint,age tinyint,id_card char(18),frg_id int ) comment '约束测试表'; # 演示添加外键的表 create table restraint_frg(id int primary key) comment '外键约束测试表'; -
添加主键约束
alter table 表名 add primary key(字段); alter table restraint add primary key(id); -
添加非空约束
# 其实就是把允许nll改为不允许null，该命令与修改字段属性一样，要把原有属性都写上 alter table 表名 modify 字段 原字段属性 [not] null; alter table restraint modify name varchar(20) not null; -
添加唯一约束
alter table 表名 add unique(字段); alter table restraint add unique(id_card); -
添加外键约束
alter table 表名 add foreign key(字段) references 外键表(外键表字段); alter table restraint add foreign key(frg_id) references restraint_frg(id); -
添加自增长约束
不支持添加自增长，需要在创建表时定义好
-
添加默认约束
alter table 表名 alter 字段 default 默认值; alter table restraint alter sex set default 0; -
添加无符号约束
# 与添加非空约束一样，都是与修改字段属性一样，要把原有属性都写上 alter table 表名 modify 字段 原字段属性 unsigned; alter table restraint modify id int unsigned; -
添加检查约束
# 约束名是可以随意起，但最好见名知义，条件就是一个返回布尔值的表达式 alter table 表名 add constraint 约束名 check(条件); alter table restraint add constraint check_id_card check(length(id_card)=18);
创建表后删除约束
-
删除主键约束
alter table 表名 drop primary key; alter table restraint drop primary key; -
删除非空约束
# 其实就是把不允许nll改为允许null，该命令与修改字段属性一样，要把原有属性都写上 alter table 表名 modify 字段 原字段属性 null; alter table restraint modify name varchar(20) null; -
删除唯一约束
# 约束名通过show create table来获取 alter table 表名 drop constraint 约束名; show create table restraint; alter table restraint drop constraint id_card; -
删除外键约束
# 外键名通过show create table来获取 alter table 表名 drop foreign key 外键名; show create table restraint; alter table restraint drop foreign key `restraint_ibfk_1`; # 删除外键后，还需要删除字段的索引 alter table 表名 drop index 字段 alter table restraint drop index fgn_id; -
删除自增长约束
# 该命令与修改字段属性一样，要把原有属性都写上,写少自增即可 alter table 表名 modify 字段 原字段属性(少自增长); # 假设id自增长，现在改没 alter table restraint modify id int unsigned; -
删除默认约束
# 其实就是把有默认值改为默认null，该命令与修改字段属性一样，要把原有属性都写上 alter table 表名 modify 字段 原字段属性 default null; alter table restraint modify sex tinyint default null; -
删除无符号约束
# 该命令与修改字段属性一样，要把原有属性都写上,写少无符号即可 alter table 表名 modify 字段 原字段属性(少无符号); alter table restraint modify id int; -
删除检查约束
# 约束名通过show create table来获取 alter table 表名 drop check 约束名; show create table restraint; alter table restraint drop check `check_id_card`;
创建表后修改约束
- 通常是删除约束再添加约束来实现修改效果
创建表后查看约束
# 其实就是查看表结构，以下两种方式都可以
# 但如果要看检查约束使用show create table，desc无法看到
desc 表名
show create table 表名
约束补充说明
-
联合约束
-
联合主键约束
两个或多列组合起来作为主键，两个列值完全一样才是主键，单独一列可重复可为null
# 创建时添加联合主键约束 create table union_test( id int,name varchar(20),primary key(id,name) ); # 添加和删除联合主键约束与单个主键约束一样，只是字段多了 alter table 表名 add primary key(字段1,字段2...); alter table 表名 drop primary key; -
联合唯一约束
两个或多列组合起来作为唯一，两个列值完全一样才是唯一，单独一列可重复
# 创建时添加联合唯一约束 create table union_test1( name varchar(20),email varchar(255),unique(name,email) ); # 添加和产出联合唯一约束与单个唯一约束一样，只是字段多了 alter table 表名 add unique(字段1,字段2...); alter table 表名 drop constraint 约束名;
-
-
外键约束
- 外键指子表中的字段引用父表中的主键字段
- 成立条件：子表不是主键字段，父表要主键字段，这两个字段的数据类型要一致
- 有外键约束时，父子表的创建、插入和删除都有顺序要求。创建和插入都是先父后子，删除是先子后父
- 外键在开发中很少使用
-
每一张表都应该有主键，没有主键，表无效
-
一张表中主键约束只能有一个，大于一个会报错
-
mysql中某个字段同时添加not null和unique约束会自动变为主键字段(oracle不会)
-
主键建议使用int,bigint,char类型，主键值一般是数字、定长的，不建议用varchar类型
-
不建议使用复合主键，建议使用单一主键，主键意义在于唯一性，联合起来就不是唯一了
索引
索引介绍
索引是数据库中用于提高数据检索速度的数据结构，通过创建指向表中数据的指针或引用，可以更快地定位到所需的数据行，从而大幅提高查询效率。可以简单理解为一本书的目录
索引分类
- 按照字段特性分类，可以分为四类索引
- 主键索引：设置为主键的字段默认添加索引
- 唯一索引：设置为unique的字段默认添加索引
- 普通索引：没有唯一性的要求，是最基本的索引类型，用于快速定位特定数据
- 全文索引：用于全文搜索，针对大文本字段的模糊匹配
- 按照字段个数分类，可以分为两类索引
- 单列索引：索引只包含一个字段
- 复合索引：一个索引中包含多个字段
索引添加时机
- 数据量很大的字段
- 属于高频查询的字段，经常在select语句或where子句出现
- 属于高频连表查询的字段，经常在连表时用该字段连接
- 频繁更新(增删改)的字段，不建议添加索引(字段修改后索引需要重新排序)
- 数据量少的字段，不建议添加索引，全表扫描会比索引扫描还快
- 不建议随意添加索引(不是越多越好)，索引本身也需要维护，添加太多会影响mysql的性能
索引使用
-
创建索引
-
创建表时添加索引
基本格式：index 索引名(字段)
create table index_test( id int primary key, name varchar(20), index name_index(name) ) comment '索引测试表'; -
创建表后添加索引
create index 索引名 on 表名(字段); # 给emp表的员工名字添加索引 create index name_index on emp(ename); # 给emp表的员工名字和部门编号添加复合索引 create index composite_index on emp(ename,deptno);
-
-
删除索引
# 索引名可以查看索引来获取 drop index 索引名 on 表名; drop index name_index on emp; -
查看索引
-
查看表中哪个字段添加了索引
show indexes from 表名; show indexes from emp; -
查看sql语句是否有使用索引
当key字段显示为null时说明没有使用索引
当type字段显示为all代表全表扫描，没有使用索引
除了all以外其他的如index、range、ref、const、system都有利用索引优化查询效率
explain select语句 explain select empno from emp; explain select * from emp;
-
索引失效
以下举例的几种场景都不是必然导致索引失效，只是有可能失效，具体还得explain分析
-
索引字段参与计算(加减乘除取余)
# 已经给emp表sal添加索引 # key返回null，没有使用索引 explain select ename from emp where sal*12 > 10000; -
索引字段使用了函数
# 已经emp表给hiredate字段添加索引 # key返回null，没有使用索引 explain select * from emp where year(hiredate) > 1987; -
索引字段使用like模糊查询并且以%开头
# 已经给emp表name字段添加索引 # key返回null，没有使用索引 explain select * from emp where ename like '%a'; -
索引字段使用or连接
当or连接左右两边的字段都有添加索引，才会走索引
如果任意一边没有索引，都会出现索引失效
# mgr没有添加索引，sal已添加索引 # key返回null，没有使用索引 explain select * from emp where mgr > 7800 or sal > 2000; -
索引字段为复合索引
多个字段联合添加一个索引，只有最左字段才会走索引，其他字段不走索引
存储过程
什么是存储过程
- 一组预编译的sql语句集合，可以实现复杂的业务逻辑和代码复用
- 可以简单理解为业务需求从编程语言上实现转移到以sql语句实现
存储过程的作用
- 提高性能：存储过程只编译一次，之后调用不用再编译，比每次执行sql语句效率高
- 减少网络流量：sql语句存在数据库服务器，客户端只需发请求不用发sql语句，减少传输量
- 代码重用：可以被多个服务共享使用，减少重复编码
- 处理复杂逻辑：支持流程控制语句、循环语句
存储过程的缺点
- 业务需求使用编程语言实现要比用存储过程简单
- 复杂的业务逻辑需要深厚的sql知识和存储过程语法(下面例子没有讲语法)
- 存储过程调试困难，没有图形化工具调试，通常需要依赖日志
- 需要额外时间学习，相当于又多学了一门编程语言
存储过程基本格式
delimiter //
create procedure 函数名([in|out|inout] 参数名 参数类型(长度))
begin
... # sql语句和逻辑
end //
delimiter ;
-
delimiter把语句结束标志从;改为//，存储过程内部会有多个sql语句，都会带有;
改变结束标志才能使整个存储过程执行，否则遇到第一个;就会执行
存储过程执行完后需要把结束标志改为;使其他语句正常执行(遇到;作为结束标志)
-
in代表入参，out代表输出，inout代表既是入参也是输出
-
起名的参数名不能和sql的字段名字一致，会出问题
存储过程的使用
-
创建存储过程
# emp表查询工资大于2000的人名字、部门名字、工资 delimiter // create procedure s_dept_name(in gt_sal int,out ename varchar(255),out dname varchar(255)) begin select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno where sal > gt_sal limit 1; end // delimiter ; -
修改存储过程
mysql8.x开始才支持修改存储过程，其他版本需要先删除存储过程再创建
基本格式：create or replace procedure 存储过程名字(参数1,参数2) begin … end
可以改变参数输入输出的类型、个数
也可以改变存储过程的内容
delimiter // create or replace procedure s_dept_name(in gt_sal int) begin select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno where sal > gt_sal; end // delimiter ; -
查询存储过程
-
两次show获取存储过程的内容
# 获取指定库的存储过程(不指定库会有很多条)，可以获取存储过程的名字 show procedure status where db='yilko'; # 通过存储过程名字获取它的内容 show create procedure s_dept_name; -
查询information_schema库的routines表
routine_schema代表数据库名字、routine_name代表存储过程名字
routine_definition代表存储过程的内容
若不知道存储过程名字，可以通过查库再查名字，可以得到内容
两种方式第一种可能简单一些
select routine_definition from information_schema.routines where routine_name='s_dept_name';
-
-
调用存储过程
基本格式：call 存储过程名字(参数)
类似python函数调用即可
call s_dept_name(2000); -
删除存储过程
基本格式：drop procedure if exists 存储过程名字;
drop procedure [if exists] s_dept_name;
视图
什么是视图
- 视图是一个虚拟表，不直接存储数据
- 视图基于一个或多个表(其他视图)构建
- 视图中的行和列实际上是来自表的查询结果
视图的作用
- 简化sql查询，某个sql语句经常被查询但很复杂，视图可以达到简化查询的作用
- 隐藏表中的部分列数据，限制敏感数据访问，仅暴露所需信息，提高数据安全性
- 可以复用相同的查询逻辑，减少代码冗余
视图的缺点
- 视图上不能直接创建索引
- 复杂的视图查询影响性能
视图的使用
-
创建视图
基本格式：create view 视图名 as dql语句;
# 三表查询，输出员工编号、名字、工资水平、部门名字 create view name_dept_sal as (select e.empno,e.ename,s.grade,d.dname from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and s.hisal order by grade desc,dname,ename); -
修改视图
-
修改视图的dql语句
基本格式：alter view 视图名 as dql语句
# 多加一列薪资 alter view name_dept_sal as (select e.empno,e.ename,e.sal,s.grade,d.dname from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and s.hisal order by grade desc,dname,ename); -
修改视图dql语句涉及表的数据
视图内修改数据会影响到原表的内容，修改前最好先了解视图的dql语句并且做好备份
视图更多的是体现查询的结果，如果要改表的数据，最好是直接操作表而不是视图
视图修改数据与普通表修改一致，都是用insert、update、delete实现
-
-
查询视图
-
查看视图的dql语句
show create view 视图名
show create view name_dept_sal; -
查看视图列信息
desc 视图名
desc name_dept_sal; -
查询视图数据
把视图当成一个表来查询即可，select * from 视图名
select * from name_dept_sal; -
查找已创建视图名
出现忘记视图名的情况，通过information_schema库的views表查找，需要知道数据库名字
table_name=视图名，view_definition=视图的dql语句，table_schema=数据库
select table_name from information_schema.views where table_schema='yilko';
-
-
删除视图
基本格式：drop view 视图名;
drop view name_dept_sal;
引擎
引擎介绍
- 存储引擎是mysql表存储数据的一种方式,是数据库中的关键部分
- mysql默认引擎为InnoDB
常见的引擎
- 常见的有三种引擎
- myisam：不支持事务，支持全文索引，但读取速度快，占用资源少
- InnoDB：重量级存储引擎，支持事务，支持数据库崩溃后自动恢复机制，不支持全文索引
- memory：数据存储在内存中，查询极快，但数据非持久化，服务器重启后数据丢失
引擎使用
-
使用InnoDB引擎
create table engine_test1(id int primary key); create table engine_test2(id int primary key) engine=InnoDb; -
使用memory引擎
create table engine_test3(id int primary key) engine=memory; -
使用myisam引擎
create table engine_test4(id int primary key) engine=myisam; -
查看当前数据库支持的引擎(有九种引擎)
show engines; -
查看当前表使用的引擎
show create table 表名;
sql注入
什么是sql注入
输入内容时故意输入sql代码，篡改或查询数据库的数据，从而达到攻击的目的
sql注入怎么产生
- 在代码层面，sql通过命令和变量拼接方式组合
# 以emp表举例，查询emp表中deptno为10且名字为king的人
# 以python的pymysql为例子(代码不完整，sql连接和查询语句部分没有放出来)
mu = MysqlUtil("localhost", "root", "***", 3306, database="yilko");
deptno = "10 or 1=1";
s_sql = f"select * from emp where deptno={deptno} and ename='king'";
print(mu.dql(s_sql));
- 正常应该输出部门编号为10且名字为king的人
- 而现在因为注入绝对成立的sql代码，只会执行前面的代码，后面的被忽略
- 整条sql语句就等同于select ename from emp where deptno=10;
如何防止sql注入
- 不要使用命令和变量拼接方式组合
- 使用%s作为占位符，将参数以元组形式传入
deptno = "10 or 1=1";
s_sql = "select ename from emp where deptno=%s and ename='king'";
print(mu.dql(s_sql, (deptno)));
为什么占位符可以防止注入
- 占位符会对特殊字符进行转义，确保在sql上失去特殊意义
- 例如单引号或sql有特殊意义的单词，都会被转义从而失去注入能力
设计三范式
数据库表设计的依据，可以避免表中数据冗余，避免空间的浪费
第一范式
任何一张表中必须要有主键，每一个字段都是原子性不可再分
举例：一般是主键都是id，字段如联系方式(邮箱+手机)，联系方式字段可以再次拆分邮箱字段和手机字段
第二范式
建立第一范式基础上，所有非主键字段完全依赖主键，不要产生部分依赖
部分依赖的定义：复合主键(A+B)，某个字段依赖A，某个字段依赖B，称为部分依赖
第三范式
第三范式：建立第二范式基础上，所有非主键字段直接依赖主键，不要产生传递依赖
传递依赖定义：某字段A依赖某字段B，某字段B又依赖主键
举例：如学生教师都写到一张表，学生学号是主键，但是班级编号和班级名称是依赖关系然后又依赖学生学号
一对一设计
- 一对一，加外键再加唯一性约束
- 正常来说一张表就搞定，但是如果一张表中的字段太多，可以根据实际情况对字段进行分类，拆为两张表
一对多设计
- 一对多，两张表，多的表加外键
- 一对多的关系时使用两张表来设计，1、学生表(加班级编号做外键) 2、班级表
多对多设计
- 多对多，三张表，关系表两个外键
- 多对多的关系时使用三张表来设计，1、学生表 2、教师表 3、学生教师关系表(对应学生和老师的外键)
知识点和注意事项
-
字符串使用单引号’'，双引号""在mysql中可用，但oracle不支持
-
字符串比较大小：如果字符串里全部是数字，则直接比较数字里面的内容。如果是数字字母混合，则先判断字符串总长度，如果长度相等则比较同位置字符，按照ascii码排序
-
表字段的数据类型长度需要注意：
对于可变长度类型来说，如varchar(66)，假如说插入的记录长度超过66也不会报错，因为实际长度是255，只要小于这个数都没问题
对于固定长度来说，如char(2)，假如说插入超过两位数就会报错，因为固定长度2位就不可变
但是声明为2不是固定2位数，而是最大两位数，也就是说可以存1位数或2位数
声明的类型针对可变长度字符串或者数值类型都是无作用的，因为实际是根据定义的类型最大范围来限制的
但是varchar不能不限制长度，如果只写varchar但不写长度，创建表时会报错
-
数据类型章节中涉及到字节和字符的概念，不同的编码方式对应的不同
ASCII码：1个字符=1个字节，其中包含(大小写英文，0-9数字，英文特殊符号)
utf-8：ASCII码部分为1字符=1字节，中文大多数1字符=2或3字节，极少数占用4字节
gbk：ASCII码部分为1字符=1字节，中文1字符=2字节(固定2字节，不存在大于2的情况)
-
tinytext和varchar选择
有索引的
varchar字段通常提供更好的查询性能。tinytext更适合存储那些长度不确定但总体较短，且不需要频繁查询或索引的文本内容
990
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
