Mysql 数据库操作 - 2023.9.16 -
DDL 数据定义语言 create 创建 alter 修改表(增加列 删除列等) dorp 删除 rename 重命名 truncate清空(有表结构)
DML 数据操作语言 insert 增加 delete 删除 update 修改数据(某一个具体的值) select 查询
DLC 数据控制语言 commit 提交 rollback 回滚 撤销 savepoint 保存点 grant设置权限 revoke回收权限
DQL 数据查询语言 select 查询
一. DDL: 数据库设计 // database 可以替换成 schema
❶. 对数据的操作 增 删 改 查
show databases; // 查询所有数据库
create database db01; // 创建数据库
create database if not exists db01; // 如不不存在则创建
use db01; // 使用数据库 db01
select database(); // 查看当前操作的数据库 函数的调用形式
drop database db01; // 删除数据库
drop database if exists db01; // 如果存在
show tables; // 查看表
desc tb_emp; // 查看某一个 表的 结构
show create table tb_emp; // 查看 表的 建表语句
❷. 对表的操作 增 删 改 查
①. 创建表 // 注意:最后一个字段后面 没有,逗号 []方括号内容 可写可不写
create table 表名(
字段1 字段类型 [约束] [comment '字段1注释'],
...
字段n 字段类型 [约束] [comment '字段n注释']
)
create table user2(
id int primary key auto_increment comment 'id,唯一标识',
username varchar(20) not null unique comment '用户名',
name varchar(10) not null comment '姓名',
age int comment '年龄',
gender char(1) default '男' comment '性别'
)
约束: 约束是作用于表中字段上的规则,用于限制存储在表中的数据 保证数据库中数据的正确性 有效性 完整性
约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。
not null 非空约束 限制该字段不能为null
unique 唯一约束 保证字段的所有数据都是唯一、不重复的
primary key 主键约束 主键是一行数据的唯一标识，要求非空且唯一
primary key auto_increment 从 1 自动增长
default 默认约束 保存数据时，如果未指定该字段值，则采用默认值 default '指定的默认值'
foreign key 外键约束 让两张表的数据建立连接，保证数据的一致性和完整性
②. 修改表结构 删除表
1. 添加字段 alter table 表名 add 字段名 类型(长度) [comment '注释'] [约束];
alter table tb_emp add qq varchar(10) comment 'QQ'; // 为表 tb_emp 添加字段qq varchar(11)
2. 修改字段类型 alter table 表名 modify 字段名 新数据类型(长度);
alter table tb_emp modify qq varchar(13) comment 'QQ' // 修改 表 tb_emp qq的字段类型 varchar(13)
3. 修改字段名和字段类型:alter table 表名 change 旧字段名 新字段名 类型(长度) [comment '注释'] [约束]
alter table tb_emp change qq qq_num varchar(13) comment 'QQ';
4. 删除字段 alter table 表名 drop column 字段名;
alter table tb_emp drop column qq_num;
5. 修改表名 rename table 表名 to 新表名;
rename table tb_emp to emp;
6. 删除表中 自动增长 auto_increment 表中 自动增长字段 只能有一个 必须是主键
alter table tb_emp modify id int;
7. 删除表中主键 alter table 表名 drop primary key; // 如果 表中有 自动增长属性 先要删除 自动增长属性
alter table tb_emp drop primary key; //
8. 给表的字段 增加主键 alter table tb_emp add primary key(字段1,字段2); // 主键可以有多个
alter table tb_emp add primary key (id); // 这里只添加了一个
9. 给表的主键 字段设置 自动增长 alter table 表名 modify 字段名 int auto_increment;
alter table tb_emp modify id int auto_increment; // 设置 自动增长
alter table emp01 auto_increment = 1; // 设置 自动增长 为 1 默认为1
二. DML: 数据操作语言
❶. 添加数据 insert into 表名(字段列表) values (字段值列表);
1. 指定字段添加数据 insert into 表名(字段1,字段2) values ('值1','值2');
insert into tb_emp(username,name,gender,create_time) values ('janice','小龙女',now(),now());
insert into tb_emp(id,username,name,gender,create_time) values (null,'janice','小龙女','2020-02-05',now());
2. 全部字段添加数据 insert into 表名 values('值1','值2');
3. 批量添加数据(指定字段) insert into 表名(字段1,字段2) values ('值1','值2'),('值1','值2');
4. 批量添加数据(全部字段) insert into 表名 values ('值1','值2'),('值1','值2');
注: 赋值的时候 字符串 和 时间 要加 单引号
如果给 自动增长的主键赋值的时候 null 即可
now() 函数 获取系统当前时间
❷. 修改数据 update 表名 set 字段名=字段值 [where 条件];
1. 修改表中的数据 单个字段 update 表名 set 字段1=值1, 字段2=值2 [where 条件];
update tb_emp set name='任盈盈' where id = 18;
2. 修改表中 某个字段的所有数据 update 表名 set 字段1=值1;
update tb_emp set qq = '113487606'; // 修改所有数据 不用加where 但是会有黄色的提示 点击确认修改Execute即可
❸. 删除数据 delete from 表名 [where 条件];
删除一行数据 delete from 表名 [where 条件];
delete from tb_emp where id = 17;
删除表中所有数据 delete from 表名; 会有黄色的提示
delete from tb_emp;
三. DQL: 查询语句 数据库查询
❶. 基本查询
select 字段列表
from 表名列表 基本查询
where 条件列表 条件查询
group by 分组字段列表 分组查询
having 分组后条件列表
order by 排序字段列表 排序查询
limit 分页查询 分页查询
select 1;
select 1 + 1;
select 3 * 2 from dual; // dual 是个 伪表 不存在的 只是为了结构完整
select * from employees; // * 代表 表中的所有的字段或列
①. 查询多个字段 select 字段1,字段2,字段3 from 表名;
select name,entrydate from tb_emp;
②. 查询所有字段(通配符) select * from 表名;
select * from tb_emp;
③. 别名 as 可加在 字段名 表名
加双引号 不要加单引号(虽然也好使,只不过sql语言不那么敏感) as alisa缩写 as可以省略
select 字段1 [as 别名1], 字段2[as 别名2] from 表名;
最好加双引号 代表一个整体 不要加单引号(虽然也好使,只不过sql语言不那么敏感) as是alisa缩写 as可以省略
select name as 姓名, entrydate as 入职日期 from tb_emp;
select student_id "stu" from user "u"
select student_id as stu from user as u
④. 去重 distinct 在字段前面加 表示 去重复 该字段
select distinct 字段列表 from 表名;
select distinct job from tb_emp;
select distinct sutdent_id from user // 去重复 sutdent_id 字段
select distinct sutdent_id, salary from user // 去重复 sutdent_id和salary 整体去重
⑤. 空值: null为空值 null参与计算 计算值为 null
ifnull(salary,0) 如果salary值不是空值,用salary,如果是空值用0计算
⑥. 着重号: ` ` 反引号 当出现字段跟系统关键字冲突时候 加 着重号
⑦. 查询常数
select '哈哈',123,student_id from user // '哈哈'和123为常数 的会给每一行都加一个 123
⑧. 显示表结构 describe 或 desc
describe user; // 查看 user 的表中 字段的 详细信息
desc user; // 作用同上
Type null Key Default Extra
⑨. 过滤 数据 where where关键字紧跟在from后面
select * from cust_user_info where parent_id = 41 // where 是条件
❷. 聚合函数: 将一列数据作为一个整体,进行纵向计算
select 聚合函数(字段列表) from 表名
count(*) 统计数量 不对 null 值 进行计算
count(cust_id) 计数
max() 最大值
min() 最小值
avg() 平均值
sum() 求和
select count(*) from tb_emp; // 结果跟上面都一样 推荐使用 count(*)
select count(*) from shop_order_addr; // 这个表一共多少行
select count(*) as addr from shop_order_addr; // as 换个名 addr
select count(*) from `shop_order_addr` where province_code > 60; // where 是条件 查询 province_code > 60 有多少
select AVG(cust_id) from shop_order_addr;
select min(entrydate) from tb_emp; // 查询 最早入职时间 查询 最小值
select max(entrydate) from tb_emp;
select avg(id) from tb_emp;
select sum(id) from tb_emp;
select count(id) from tb_emp;
select count(1) from tb_emp; // 1 和 a 都是常量 结果和 字段一样
select count("a") from tb_emp;
❸. 分组查询 group by 可以把有共性的字段整合起来
group by 在分组查询中, 主要查询一类是: 分组字段 另外一类是聚合函数
having 分组之后的查询条件
select 字段列表 from 表名[where 条件] group by 分组字段名 [having 分组后过滤条件];
select gender,count(*) from tb_emp group by gender; // 查询男 和女 分别有多少人
select job,count(*) from tb_emp where entrydate <= "2015-01-01" group by job; // 查询2015年前入职 各职位的人数
select job,count(*) from tb_emp where entrydate <= "2015-01-01" group by job having count(*) >= 2; // 大于等于2
select receiver, count(*) from shop_order_addr group by receiver;
// 根据 GROUP BY receiver 此字段属性 每个receiver属性 有多少个
第一列receiver 第二列COUNT计数
select receiver, mobile, count(*) from shop_order_addr group by receiver, mobile;
// 查询receiver和mobile两个字段 . 完全相同则计数 不相同单独计数
❹. 排序查询 order by
order by 排序 默认是 升序 asc 没有加规则就是 asc asc可以省略
asc 升序 默认值
desc 降序
select 字段列表 from 表名 [where 条件列表] [group by 分组字段] order by 字段1 排序方式, 字段2 排序方式;
select * from tb_emp order by entrydate desc; // 安装入职时间 进行降序
select * from tb_emp order by entrydate, update_time desc; // 按照入职时间 升序 如果相同 在安装 更新时间 降序
select * from cust_user_info order by parent_id;
select * from cust_user_info order by parent_id desc;
select * from cust_user_info where level = 10 order by parent_id desc;
select * from cust_user_info where level = 10 order by parent_id desc, lft; // 按parent_id 降序 在按lft 升序
select * from cust_user_info where level = 10 order by parent_id desc, lft desc;//按parent_id 降序在按lft降序
❺. 分页查询 limit
# select * from user limit 0,10; // 显示 前十条数据 每页 10条数据
# select * from cust_user_info where level = 10 order by parent_id desc, lft desc limit 0,10;
limit 10,10 // 从第11条数据开始 显示 10条 第二页
limit 20,10 // 第三页
limit 10 // 如果 位置偏移量是0的时候 可以简写 0可以省略
limit 位置偏移量,条目数
公式: limit(pageNo - 1) * pageSize,pageSize
limit 2 offset 31 // mysql8.0中的语句 加入了 offse关键字 offset后面跟偏移量 从第32条显示 2条数据
select * from cust_user_info where level = 10 order by parent_id desc, lft desc limit 2 offset 10;
select 字段列表 from 表名 limit 起始索引,查询记录数; // 起始索引从0开始 起始索引 = (页码 -1) * 每页展示记录数
select * from tb_emp limit 0,10;
select * from tb_emp limit 10; // 如果查询的是第一页数据,起始索引可以省略 简写成 limit 10;
❻. 条件判断 if(gender = 1, '男','女')
select gender,count(*) from emp group by gender;
select if(gender =1,'男','女') as 性别,count(*) from emp group by gender; // 在gender 加入了条件判断
❼. 流程控制 case 表达式 when 值1 then 结果1 when 值2 then 结果2 ... else ... end
select job,count(*) from tb_emp group by job;
select
(case job when 1 then '班主任' when 2 then '讲师' when 3 then '学工主管'
when 4 then '教研主管' else '未分配职位' end）as 职位,
count(*)
from tb_emp group by job;
四. 表结构设计
一对多的的关系: 父表是1 子表是多
部门表是父表 员工表是子表
一对多关系实现: 在数据库表中多的一方,添加字段,来关联一的一方的主键
❶. 外键约束
物理外键
创建表时指定
create table 表名(字段名 数据类型, [constraint] [外键名称] foreign key(外键字段名) references 主表(字段名));
建立完表后,添加外键
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(字段名);
逻辑外键 在业务逻辑中, 解决外键关联
❷. 连接查询
内连接: 相当于查询A B交集部分数据 ... 内连接无联系数据不显示
隐式内连接: select 字段列表 from 表1,表2 where 条件;
select * from emp,dept where dept.id = emp.dept_id;
select dept.name,emp.name from emp,dept where dept.id = emp.dept_id;
select d.name,e.name from emp as e ,dept as d where d.id = e.dept_id; // 使用别名后 不能以在用表.字段调用
显示内连接: select 字段列表 from 表1[inner] join 表2 on 连接条件; // inner 可以省略 要一直使用别名
select * from emp inner join dept on emp.dept_id = dept.id;
select emp.name,dept.name from emp inner join dept on emp.dept_id = dept.id;
外连接: 外连接一定显示主表 left左表 right右表
左外连接 以表1 为准 select 字段列表 from 表1 left [outer] join 表2 on 连接条件;
右外连接 以表2 为准 select 字段列表 from 表1 right [outer] join 表2 on 连接条件;
select * from emp left join dept d on d.id = emp.dept_id
❸. 子查询 sql 语句中的嵌套 select 语句 成为 嵌套查询 又称 子查询
子查询外部的语句可以是 insert update delete select 的任何一个 最常见的事select
标量子查询 select * from 表1 where 字段1 = (select 字段1 from 表2);
select id from dept where name = '教研部'; // 两个查询 合并成 一个查询
select name,dept_id from emp where dept_id =2; // ↓↓查询 在方东白 之后入职的
select name,dept_id from emp where dept_id =(select id from dept where name = '教研部';); // 查询教研部所有人
select name,entrydate from emp where entrydate >= (select entrydate from emp where name = '方东白');
列子查询 常用的操作符: = <> in not in
用于查询一个字段中 某些筛选数据
select * from 表1 where 字段0 in (select 字段0 from 表2 where 字段1 = '值1' or 字段1 = '值2');
select id from dept where name = '教研部' or name = '咨询部'; // 查询 教研部和咨询部所有的员工信息
select name,dept_id from emp where dept_id in (select id from dept where name = '教研部' or name = '咨询部');
行子查询 子查询返回的结果是一行(可以是多列) 常用的操作符: = <> in not in
用于查询 与某一个字段 的其他属性相同的 其他数据
select * from 表名 where (字段1,字段2) = (select 字段1,字段2 from 表名 where 字段0 = '字段0);
select dept_id,entrydate from emp where name = '韦一笑';
select * from emp where entrydate = (select entrydate from emp where name = '韦一笑')
and job = (select job from emp where name = '韦一笑'); // ↓查询与韦一笑在一个部门同时入职的员工
select * from emp where (entrydate,job) = (select entrydate,job from emp where name = '韦一笑');
表子查询 把查询的 信息 作为一张表使用 放到 from 后
select * from (select * from emp where entrydate > '2013-01-01') e, dept d where e.dept_id = d.id;
❹. 事物 就是 开启了 撤销模式 在开启事务后执行的代码并没有真正提交 只有commit后才是真正提交 可以回滚
开启事务: start transaciton; / begin;
提交事务: commit;
回滚事务: rollback;
五. 数据类型
❶. 数值类型
类型 大小(byte) 有符号(SIGNED)范围 无符号(unsigned)范围 描述 // ↓在类型后面使用 unsigned 使用无符号
tinyint 1 (-128，127) (0，255) 小整数值 tinyint unsigned .
smallint 2 (-32768，32767) (0，65535) 大整数值 smallint unsigned
mediumint 3 (-8388608，8388607) (0，16777215) 大整数值 ...
int 4 (-2147483648，2147483647) (0，4294967295) 大整数值 ...
bigint 8 (-2^63，2^63-1) (0，2^64-1) 极大整数值 ... // ↓2 表示小数位个数
float 4 (-3.40 E+38，3.40 E+38) 0 (1.17 E-38，3.40 E+38) 单精度浮点数值 float(5,2)：5表示整个数字长度
double 8 (-1.79 E+308，1.79 E+308)0 (2.22 E-308，1.79 E+308)双精度浮点数值 double(5,2)：5表示整个数字长度
decima l 在金融使用 小数值(精度更高) decimal(5,2)：5表示整个数字长度，2 表示小数位个数
❷. 字符串类型
类型 大小 描述
char 0-255 bytes 定长字符串
varchar 0-65535 bytes 变长字符串
tinyblob 0-255 bytes 不超过255个字符的二进制数据
tinytext 0-255 bytes 短文本字符串
blob 0-65535 bytes 二进制形式的长文本数据
text 0-65535 bytes 长文本数据
mediumblob 0-16777215 bytes 二进制形式的中等长度文本数据
mediumtext 0-16777215 bytes 中等长度文本数据
longblob 0-4294967295 bytes 二进制形式的极大文本数据
longtext 0-4294967295 bytes 极大文本数据
char(10): 最多只能存10个字符,不足10个字符,占用10个字符空间
varchar(10): 最多只能存10个字符,不足10个字符, 按照实际长度存储
phone char(11)
username varchar(20)
❸. 日期时间类型
类型 大小(byte) 范围 格式 描述
date 3 1000-01-01 至 9999-12-31 YYYY-MM-DD 日期值
time 3 -838:59:59 至 838:59:59 HH:MM:SS 时间值或持续时间
year 1 1901 至 2155 YYYY 年份值
datetime 8 1000-01-01 00:00:00 至 9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
timestamp 4 1970-01-01 00:00:01 至 2038-01-19 03:14:07 YYYY-MM-DD HH:MM:SS 混合日期和时间值，时间戳
birthday date
update_time datetime
六. 运算符
❶. 算术运算符: + - * / div也是除 %取模、余数 mod取模、余数 余数的符号跟被除数相同 跟除数没有关系
select 100 + '1' // 结果是 101 会把字符串 1 转换成数字 隐式转换
select 100 + 'a' // 结果是 100 a 不能转换是数字 a看做0处理
select 100 * 'A' // 结果是 0
select 100 div 0 // 结果是 null 除数不能是0
❷. 比较运算符:
返回结果是 1 为真
返回结果是 0 为假
返回结果是 null 为其他情况 只要有 null 值 参与比较 结果为 null，null 和 null 比较 结果也是null
> 大于
>= 大于等于
< 小于
<= 小于等于
!= 不等于 可以参与null运算
<> 不等于 可以参与null运算
= 等于 会把 等号 两边的 值做比较 如果相同 返回1 结果不同返回0
select 1 = 2, 1 = '1', 1 = 'a', 0 = 'a' ;
select update_user from cust_user_info WHERE update_user = NULL // 有null参与运算 不会返回任何结果
<=> 安全等于 和 = 用法一样 唯一区别可以进行 null 运算
select 1 <=> null, null <=> null;
select update_user from cust_user_info WHERE update_user <=> NULL;
is null 空 判断值 字符串或表达式是否为空
is not null 不为空 判断值 字符串或表达式是否不为空
isnull() 空 函数 判断值 字符串或表达式是否
select update_user from cust_user_info WHERE update_user is null; // 取 update_user 是null的情况
select update_user from cust_user_info WHERE update_user is not null;
select parent_id from cust_user_info where ISNULL(parent_id);
between ... and 在 ... 之间
select parent_id from cust_user_info where parent_id between 28 and 50; // 包括28 包括50 前面的数要小于后面的数
select parent_id from cust_user_info where parent_id not between 28 and 50; // 不包括28 不包括50 不在28-50之间
select parent_id from cust_user_info where parent_id < 50 or parent_id >28;
select parent_id from cust_user_info where parent_id >10 && parent_id <50;
select cust_id, parent_id from cust_user_info where parent_id = 10 or parent_id = 50; // 出现10或50的
select cust_id, parent_id from cust_user_info where parent_id = 10 or 50; // 查询所有 因为50不为零返回值是 1
in() 具体的 等于 某一直 或多选一
not ... in() 不等于 某一直
select * from tb_emp where job in (2);
select * from tb_emp where job in (2,3,4);
select * where parent_id in (10,20,50);
select * where parent_id in (10);
select * where parent_id not in (10);
like 模糊查询
_ 下划线 代表一个字符
% 百分号 不确定字数的字符 包括 0个
\ 反斜杠 转义字符 代表 本身的意思
escape 定义转义字符
select * from tb_emp where name like '美'; // 这样的结果是 是 美的 查询出来 并不是模糊查询 相当于 =
select * from tb_emp where name like '方芝美'; //
select * from tb_emp where name like '%美%'; // 模糊查询 带 美 字的就可以
select * from tb_emp where name not like '%美%'; // 不带美子字的
select * from tb_emp where name like '_美%'; // 第二个字是美的
select * from tb_emp where name like '_\_a%'; // 第二个字符是 下划线的
select * from tb_emp where name like '_$_a%' escape '$'; //把 $符 定义成 转义字符
select * from tb_emp where name like '__'; // 查询 两个字的员工
least 取 最小值 如果是字母 a 最小 z 最大
greatest 取 最大值
select least('g','b','c','v') from dual; // 输出 b
select greatest('c','s','y') from dual; // 输出 y
select greatest('2','3','1') from dual; // 输出 3
select least(lft,rgt) from cust_user_info; // 比较两个字段 取最小值的字段
regexp 正则表达式运算符
rlike 正则表达式运算符
select cust_name, cust_id from cust_user_info where cust_name regexp '^陈' // 以陈开头的
❸. 逻辑运算符
or || 或 两边有一个真就可以
and && 且 两边都是真 and和 or 可以一起使用 and优先级高于or a and b or c and d 先算and
not ! 非
xor 亦或 两边一个真一个假的时候 成立
select * from cust_user_info where parent_id = 50 xor lft < 20 // 这里相当于 or 了 这不常用了解就好