思路来源
https://blog.csdn.net/bobo553443/article/details/81383194
上一节链接:https://blog.csdn.net/qq_40893824/article/details/106404794
下一节链接:https://blog.csdn.net/qq_40893824/article/details/106492174
目录
环境:Mysql workbench
0 控制台操作 mysql
找到 Command Line Client
双击进去
密码 123456
查询数据库用户:select user();
1 新建连接
![](https://i-blog.csdnimg.cn/blog_migrate/8ebce53372599bdb965a8d784950be8e.png)
![](https://i-blog.csdnimg.cn/blog_migrate/63d21f068056bcd7ec2ecdb3a6c239d3.png)
![](https://i-blog.csdnimg.cn/blog_migrate/14ab8b36fe92db13b73484f68fe4a8b9.png)
左键 单击上图红框后:
2 相关操作
2.1 数据类型
详情见:https://www.w3school.com.cn/sql/sql_datatypes.asp
不区分大小写
后面括号规定最大位数size
最大位数 也不能超过 其取值范围
数据类型 | 具体包含 | 取值范围 | |
整数 | 有符号 | tinyint(size) | -128 ~ 127 |
smallint(size) | -32768 ~ 32767 | ||
mediumint(size) | -8388608 ~ 8388607 | ||
int(size) | -2147483648 ~ 2147483647 | ||
bigint(size) | -9223372036854775808 ~ 9223372036854775807 | ||
无符号 | tinyint(size) unsigned | 0 ~ 255 | |
smallint(size) unsigned | 0 ~ 65535 | ||
mediumint(size) unsigned | 0 ~ 16777215 | ||
int(size) unsigned | 0 ~ 4294967295 | ||
bigint(size) unsigned | 0 ~ 16777215 | ||
浮点数 size ≥ d size:数字最大位数 d:小数点右侧最大位数 size - d:整数位数 | float(size,d) | 可参见: 文章链接 1.2 小节 | |
double(size,d) | |||
decimal(size,d) 精确数值,字符串存储的 double | decimal(5, 2): -999.99 ~ 999.99 具体参见:文章链接 | ||
字符串 | 有size | char(size) 定长 | 0 ~ 255 字符 |
varchar(size) 变长 | 0 ~ 255 字符 | ||
无 size 变长 | tinytext | 0 ~ 255 字符 | |
text | 0 ~ 65535 字符 | ||
mediumtext | 0 ~ 1677 7215 字符 | ||
longtext | 0 ~ 42 9496 7295 字符 | ||
blob 是二进制对象 BLOBs (Binary Large OBjects) | 0 ~ 65535 字符 | ||
mediumblob | 0 ~ 1677 7215 字符 | ||
longblob | 0 ~ 42 9496 7295 字符 | ||
枚举 enum('字符1', '字符2', ..., '字符n') 即:输入值只能是参数 n 个字符中的 1 个 | 1 个 | ||
枚举 set('字符1', '字符2', ..., '字符n') 即:输入值只能是参数 n 个字符中的 若干个 | 若干 个 | ||
日期 | date | YYYY-MM-DD 年- 月- 日 | '1000-01-01' ~ '9999-12-31' |
datetime | YYYY-MM-DD HH:MM:SS 年- 月- 日- 时- 分- 秒 | '1000-01-01 00:00:00' ~ '9999-12-31 23:59:59' | |
timestamp 时间戳 | YYYY-MM-DD HH:MM:SS 年- 月- 日- 时- 分- 秒 | '1970-01-01 00:00:01' UTC ~ '2038-01-09 03:14:07' UTC | |
time | HH:MM:SS 时- 分- 秒 | '-838:59:59' 到 '838:59:59' | |
year | 2 位 或 4 位格式的年 year(2)、year(4) | 2 位:70 ~ 69,表示从 1970 到 2069 70 ~ 99:1970 ~ 1999 0 ~ 69:2000 ~ 2069 4 位:1901 ~ 2155 |
timestamp,是 int - 有大小范围,最多21亿,所以 timestamp存的数被限制在1970 ~ 2038
它自动设置为当前的日期、时间,也就不能为 null
接受不同的格式:YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD、YYMMDD
2.1.2 完整性约束
完整性 | 代码 |
---|---|
实体完整性 | Cno char(5) primary key 或primary key(Sno,Cno) |
参照完整性 | foreign key(Sno) references Student(Sno) 可以参照别人,也可以参照自己 |
用户定义完整性 3个选项 可同时使用 | not null:非空 unique:唯一 check: Ssex CHAR(2) CHECK (Ssex IN ('男','女')) 或Grade SMALLINT CHECK (Grade>=0 AND Grade <=100) |
实体完整性:检查主码是否唯一、是否为空
参照完整性:
级联操作 cascades,把相关表同时更新(在创建表的时候用)
no action 拒绝操作
use qwe;
create table SC(
Sno char(5),
Cno char(5),
Grade smallint,
primary key(Sno,Cno),
foreign key(Sno) references Student(Sno)
on delete cascade /* 级联删除 */
on update cascade,/* 级联更新 */
foreign key(Cno) references Course(Cno)
on delete no action /* 不一致时,拒绝删除 */
on update cascade /* 级联更新 */
);
use sql1;
create table SC(
Sno char(5),
Cno char(5),
Grade smallint,
primary key(Sno,Cno),
constraint c3 foreign key(Sno) references Student(Sno),
constraint c4 foreign key(Cno) references Course(Cno)
);
2.1.2.1 建表时 约束
constraint 给约束起名
mysql constraint c1 not null
建表时不能给 not null 约束起名
auto_increment:主键是数值类型,自动增长,这样赋值的时候可以不用给主键复制,主键在前面的基础上自动+1
use qwe;
create table Student(
Sno char(5) primary key,
Sname varchar(20),
Ssex char(3) constraint c2 check(Ssex in('男','女')),
Sage smallint,
Sdept char(2)
);
2.1.2.2 建表后 增、删、改 约束
增 约束
加名字 使用以上所有约束 最好加上名字! | alter table 表名 add constraint 给约束起名 primary key(属性名1, 属性名2,…) ;alter table Student add constraint pk primary key(Sno,Ssex); |
---|---|
实体完整性 增加主键 | alter table 表名 add primary key(属性名1, 属性名2,...) ; alter table Student add primary key(Sno) ; |
参照完整性 加外键 | alter table Student add foreign key(Sdept) references SC(Cno); |
用户定义完整性 | ------------------ |
非空 not null | alter table Student modify column Sdept char(2) not null; 不能直接 alter table Student modify Sdept not null; |
唯一 unique | alter table Student add unique(Sdept); |
检查 check | alter table Student add check(Sage between 18 and 50); |
删 约束
主键删除后,其后下一个属性会 作为默认主键
删除主键 | alter table Student drop primary key ; |
---|---|
删除外键 | alter table Student drop foreign key 外键名; |
删除 check约束 | alter table Student drop check check名; |
改 约束
先删约束,再建约束
2.1.3代码
学生表
建 “学生”表Student(Sno,Sname,Ssex,Sage,Sdept)
学号长度为5,是主码,
姓名最多10个汉字,取值非空、唯一,
系编号长度为2
use sql1;
create table Student(
Sno char(5) primary key,
Sname varchar(20) not null unique,
Ssex char(2),
Sage smallint,
Sdept char(2)
);
![](https://i-blog.csdnimg.cn/blog_migrate/2d34f97af04d6ae1befe2e52a74de804.png)
create database sql1;
语句后面都有;
! 不能丢
或 create table sql1.Student
课程表
建立一个课程表Course,并标明主码和外码。 Course(Cno,Cname,Cpno,Ccredit),其中 Cno长度为5,Cname长度最大为10
use sql1;
create table Course(
Cno char(5) primary key,
Cname varchar(20),
Cpno char(5),
Ccredit smallint,
foreign key(Cpno) references Course(Cno)
);
![](https://i-blog.csdnimg.cn/blog_migrate/ae51d95ba5fdbd3db3f6d37a6f110770.png)
学生成绩表
use sql1;
create table SC(
Sno char(5),
Cno char(5),
Grade smallint,
primary key(Sno,Cno),
foreign key(Sno) references Student(Sno),
foreign key(Cno) references Course(Cno)
);
![](https://i-blog.csdnimg.cn/blog_migrate/c9bbc796d02e8911d62d201f47f74a8b.png)
2.2 语句执行
ctrl + enter 或手动,见下图
2.3 数据库
2.3.1 创建
create database sql1;
语句后面都有;
! 不能丢
或 create table sql1.Student
手动创建数据库
2.3.2 删除
drop database sql1;
手动删除
2.3.3 修改
修改字符集 charset:
alter database book character set utf8mb4;
2.4 表 - 增 删 查 改
2.4.1 增表 - 创建 create table
use sql1;
指定表的数据库,不能丢
use sql1;
create table Student(
Sno char(5) primary key,
Sname varchar(20) not null unique,
Ssex char(2),
Sage smallint,
Sdept char(2)
);
![](https://i-blog.csdnimg.cn/blog_migrate/2d34f97af04d6ae1befe2e52a74de804.png)
2.4.2 删表 drop table
drop table sc;
2.4.3 查表
见本节
2.4.4 改表 - 增(add) 删(drop) 改 属性( 列 )
复合处理,的话就直接加,
就可以了
alter table Student
modify column Scome char(5),
modify column Sage int,
add text1 char(5) ;
操作 | 格式 | 代码 | 结果 |
增新列 | alter table 表名 add 属性名 类型; 默认加在最后一列 | alter table Student add Scome datetime; | ![]() |
alter table 表名 add 属性名 类型 after 已有属性名; | alter table Student add Scome datetime after Sno; after Sno → first 则放在第一列 | ![]() | |
alter table 表名 add 属性名1 类型1 after 已有属性名, add 属性名2 类型2 after 已有属性名 ... ; | alter table Student add Scome datetime, add test char(5) after Sage; | ![]() | |
删除列 | alter table 表名 drop 属性名; | alter table Student drop Scome; | ![]() |
alter table 表名 drop 属性名1, drop 属性名2, ... ; | alter table Student drop Scome, drop text1; | ![]() | |
修改数据类型 | alter table 表名 modify column 属性名 新数据类型 ; | alter table Student modify column Scome char(5); | ![]() |
alter table 表名 modify column 属性名1 类型1, modify column 属性名2 类型2, … ; | alter table Student modify column Scome char(5), modify column Sage int, add text1 char(5) ; | ![]() | |
设置默认值: alter table 表名 alter 属性名 set default 默认值 ; | alter table Student alter Sage set default 15 ; | ![]() | |
删除默认值: alter table 表名 alter 属性名 drop default ; | alter table Student alter Sage drop default ; | ![]() |
https://www.yisu.com/zixun/315509.html
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;
alter table xx_log
CHANGE column xxID `deviceId` varchar(128) DEFAULT NULL COMMENT 'asdas';
主键自增
alter table xxx表名 modify id bigint(64) auto_increment;
2.5 索引
2.5.1 理解
索引就是 目录
目的要解决 查询
主键索引(PRIMARY),普通索引(INDEX),唯一索引(UNIQUE),组合索引
主键索引 = 聚簇索引(clustered index)
其余 = 非主键索引 = 二级索引(secondary index)
聚簇索引(innodb)的叶子节点就是数据节点。找到索引也就找到了数据
非聚簇索引(myisam)的叶子节点仍然是索引文件,只是这个索引文件中包含指向对应数据块的指针。找到索引也就找到了主键值,还要通过主键去查数据
没有主键,第一个非空唯一索引就是聚簇索引
覆盖索引
https://blog.csdn.net/cckevincyh/article/details/119655516
create table t1
(
a int primary key ,
b int,
c int,
d int,
e varchar(20)
)engine=InnoDB;
insert into t1 value (4, 3, 1, 1, 'd');
insert into t1 value (1, 1, 1, 1, 'a');
insert into t1 value (8, 8, 8, 8, 'h');
insert into t1 value (2, 2, 2, 2, 'b');
insert into t1 value (5, 2, 3, 5, 'e');
insert into t1 value (3, 3, 2, 2, 'c');
insert into t1 value (7, 4, 5, 5, 'g');
insert into t1 value (6, 6, 4, 4, 'f');
create index idx_t1_bcd on t1(b,c,d); -- 创建复合索引
explain select * from t1 where b = 1; -- 回表
explain select e from t1 where b = 1; -- 回表
explain select b from t1 where b = 1; -- 不用回表 Using index 覆盖索引
explain select b,c from t1 where b = 1; -- 不用回表 Using index 覆盖索引
explain select b,d from t1 where b = 1; -- 不用回表 Using index 覆盖索引
explain select b,c,d from t1 where b = 1; -- 不用回表 Using index 覆盖索引
explain select a,b,c,d from t1 where b = 1; -- 不用回表 Using index 覆盖索引
2.5.2 建立索引
create 唯一/聚簇 index 索引名 on 表名( 属性名 asc/desc );
unique:唯一
cluster:聚簇
asc:升序(不写也默认)
desc:降序
例子
create unique/cluster index name on Student (Sno desc);
2.5.3 删除索引
drop index 索引名 on 表名;
例子:
drop index name on Student;
索引 特点:
- 索引可随时建立
- 在使用频率高、常连接的表 建立索引
- 1 个表可有多个索引
- 用户不能使用索引,只能系统来用
- 提速,占据了空间,降低插入、删除、更新的速度
因为插入、删除、更新的同时,要修改索引
主键是特殊的索引
order by 属性3,该 属性3 需要添加对应索引,否则会很慢
有索引的列作为查询条件
查询频繁的列建立索引
索引失效
查询的结果集,超过了总数行数25%,优化器觉得就没有必要走索引了
相关情况:
同一语句,参数不同,可能出现走索引和不走索引 2两个情况
解决办法:
1 如果业务允许,可以使用limit控制
2 结合业务判断,有没有更好的方式。如果没有更好的改写方案
3 尽量不要在mysql存放这个数据了。放到redis里面。
3.索引本身失效,统计数据不真实
索引有自我维护的能力,对于表内容变化比较频繁的情况下,有可能会出现索引失效。
更改方案:
备份表数据,删除重建相关表
隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
索引建立的字段为varchar();
select * from stu where name = ‘111’;走索引
select * from stu where name = 111;不走索引
更改方法:
与研发协商,语句查询符合规范。
对 列字段 有任何的操作
对应索引会失效
查询条件使用函数在索引列上,或者对索引列进行运算,运算包括(+,-,*,/,! 等)
大于< 小于> ,not in , is not null 不走索引(辅助索引)
or或in 尽量改成union
not in 索引, 不走索引
not in 主键, 走索引
order by 索引, 不走索引
order by 主键, 走索引
is null 正常走索引
is not null,不走索引
not exists时,不走索引
or 两个条件都要添加索引,否则索引失效:
explain select * from t_user where id = 2 or username = 'Tom2';
like “%” 百分号在最前面不走
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE ‘31%’ 走索引
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE ‘%110’ 不走索引
更改方法:
%linux%类的搜索需求,可以使用elasticsearch + mongodb
专门做搜索服务的数据库产品
列使用了函数 或 计算操作,不走索引
https://blog.csdn.net/qq_40584358/article/details/114087346
https://www.jianshu.com/p/d212323e59a5
https://blog.csdn.net/weixin_30786631/article/details/113199736
https://cloud.tencent.com/developer/article/1541265
https://www.51cto.com/article/702691.html
BTREE索引、HASH索引
CREATE TABLE act_info(
id BIGINT NOT NULL AUTO_INCREMENT,
act_id VARCHAR(50) NOT NULL COMMENT "活动id",
act_name VARCHAR(50) NOT NULL COMMENT "活动名称",
act_date datetime NOT NULL,
PRIMARY KEY(id),
KEY idx_actid_name(act_id,act_name) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=UTF8 ROW_FORMAT=COMPACT COMMENT "活动记录表";
USING BTREE
通过B树使用主键,建立索引
https://cloud.tencent.com/developer/article/1770574
B-TREE索引使用场景
1 全值匹配的查询SQL,如 where act_id= ‘1111_act’
2 联合索引汇中匹配到最左前缀查询,如联合索引 KEY idx_actid_name(act_id,act_name) USING BTREE,只要条件中使用到了联合索引的第一列,就会用到该索引,但如果查询使用到的是联合索引的第二列act_name,该SQL则便无法使用到该联合索引(覆盖索引除外)
3 匹配模糊查询的前匹配,如where act_name like ‘11_act%’
4 匹配范围值的SQL查询,如where act_date > ‘9865123547215’(not in和<>无法使用索引)
5 覆盖索引的SQL查询,就是说select出来的字段都建立了索引
2.6 查询表
查询结果不会包含查询对应属性是 null 的情况
层次 | 解释 |
---|---|
select from where | 选择 |
(group by) / having | 再细选 |
order by | 按条件排序 |
distinct 去重,distinct 列表表达式
lower() 小写字母化
ase:正序,默认
desc:降序
格式
select 列表表达式 from 表名
where ...
group by ... having...
order by ...
小结
分类 | 举例 |
---|---|
列表表达式 | * 表名.* distinct 表名 count() 属性列表达式 |
聚集函数 | count sun avg max min |
where | where 属性名/any-all/常量 where 属性名 (not) between 常量/嵌套查询 and 常量/嵌套查询 |
2.6.1 单表查询
2.6.1.1 常见属性 去重
分类情况 | 代码 |
---|---|
最简单代码 | SELECT Sno,Sname FROM Student; |
查找全部属性列信息 | SELECT * FROM Student; |
查找的属性 可以是 表达式 | SELECT Sage+3 FROM Student; ![]() |
内容小写字母化 | SELECT Sname,'年龄',2020-Sage,lower(Sdept) FROM Student; lower() 是小写字母化 ![]() |
改列名 | SELECT Sname name,'的年龄' 年龄,Sage FROM Student; ![]() |
去重 本默认不去重 | SELECT distinct Sname FROM Student; |
2.6.1.2 比较 >、<
>、<、=、>=、<=、<>(更常用)、!=
- 查找 计算机科学系 全体学生的 名字
select Sname from Student
where Sdept='cs';
- 查找 年龄在20岁以下的学生的 姓名、年龄
select Sname,Sage from Student
where Sage<20;
- 查找 所以不及格同学的 学号
select distinct Sno from SC where Grade<60;
一个人可能多门科目不及格,所以要去重 distinct!
2.6.1.3 确定范围 betwen
between x and x
not between x and x
查找年龄在 [12,20] 的同学的 名字
SELECT Sname FROM Student
where Sage between 12 and 20;
2.6.1.4 确定集合 in
in / not in
查找 es 或 ms 或 is 系别的同学的 名字
select Sname from Student
where Sdept in ('es','ms','is');
或 逻辑运算符
select Sname,Ssex from Student
where Sdept='es' or Sdept='ms' or Sdept='is';
2.6.1.5 字符匹配 like
符号 | 例子 |
---|---|
% 任意长度字符串 | a%b表示 以a为开头、以b为结尾的字符串 |
_ 表示任意单个字符 | a_b 表示 以a为开头、以b为结尾的 长度是3 的字符串 |
\ 转义字符 | 字符串包括 % 、_ 用\ 转义一下 |
查询学号是 201215121 的学生详情
select * from Student
where Sno like '201212151';
等价于
select * from Student
where Sno='201212151';
查询所有 刘姓学生的姓名、学号、性别
select Sname, Sno, Ssex from Student
where Sname like '刘%';
查名字,名字三个字,前两个字是 欧阳
select Sname from Student
where Sname like '欧阳_';
查名字,其中名字 第 2 个字是 阳
select Sname from Student
where Sname like '_阳%';
查姓名、学号、性别,不姓刘
select Sname, Sno,Ssex from Student
where Sname not like '刘%';
查询 课程名是 db_ design 的课程号、学分
select Cno, Ccredit from Course
where Cname like 'db\_desgin';
查询课程详情,课程名以 db_ 开头,倒数第 3 个字符 是 i
select * from Course
where Cname like 'db\_%i__';
2.6.1.6 空值 is null
is null / is not null,is 不能用 =
代替
查询无成绩的学生学号、对应课程号
select Sno,Cno from SC
where Grade is null;
找 漏填性别 或 年龄的记录
select * from Student
where Ssex is null or Sage is null;
查 1 号课程不及格及其缺考学生
select Sno from SC
where Cno='1'and (Grade<60 or Grade is null);
2.6.1.7 逻辑运算符 and or not
and |
---|
or |
not |
括号可以改变优先级 |
查询学生姓名,计算机系、年龄小于20 |
select Sname from Student
where Sdept='cs' and Sage<20;
查找 es 或 ms 或 is 系别的同学的 名字
法1 确定范围
select Sname from Student
where Sdept in ('es','ms','is');
法2 逻辑运算
select Sname,Ssex from Student
where Sdept='es' or Sdept='ms' or Sdept='is';
2.6.1.8 排序 order by
order by 排序
ase:正序,默认
desc:降序
order by 属性1, 属性2
先属性 1 排序,此基础上按属性 2 再排序
查询学号、成绩,其中课程号是 3,结果按 成绩降序排列
select Sno,Grade from SC
where Cno='3'
order by Grade desc;
查询全体详情,按系号升序、同一系中按年龄降序
select * from Student
order by Sdept, Sage desc;
2.6.1.9 聚集函数 count
函数 | 解释 |
---|---|
count() | 统计元组个数 |
sum() | 求和 |
avg() 不是 age()! | 求平均 |
max() | 一列中最大值 |
min() | 一列中最小值 |
查询 学生总人数 |
select count(*) from Student;
查询 选修了课程的学生人数
select count(distinct Sno) from SC;
查询总学分数,其中学生学号是 201215012
select sum(Ccredit) from SC,Course
where Sno='201215012' and SC.Cno = Course.Cno;
查询 1 号课程学生平均成绩
select avg(Grade) from SC
where Cno='1';
查询 1 号课程最高分
select max(Grade) from SC
where Cno='1';
2.6.1.10 group by 分组 - having -
细化聚集函数的作用范围
没有 group by,聚集函数作用在全体,group by 分组后,就分别在组内 进行聚集
rollup 数据 小计 和 合计功能
具体参见:
https://www.cnblogs.com/weixiaotao/p/10935157.html
select Sno from Student group by Sname with rollup ;
求 各课程 相应选课人数
select Cno, count(Sno) from SC
group by Cno;
查询学生学号,学生是 选修了 3 门以上课程的
select Sno from SC
group by Sno
having count(*)>3;
查询学生学号、平均成绩,学生的平均成绩大于等于 90
select Sno, avg(Grade) from SC
group by Sno
having avg(Grade)>=90;
错误:
select Sno, avg(Grade) from SC
where avg(Grade)>=90
group by Sno;
连接又分组
select *
from applet_third_resources_choose atc,applet_third_resources_static ats
where atc.channl_name = ats.channel_name
group by atc.resource_name;
![](https://i-blog.csdnimg.cn/blog_migrate/a3475c363dc922d94deee633fb788b2c.png)
2.6.1.11 group_concat
https://www.sjkjc.com/mysql-ref/group_concat/
时间
https://blog.csdn.net/csdn_ds/article/details/71728164
NOW() = SYSDATE() 当前时间
WHERE TO_DAYS(createTime) = TO_DAYS(NOW())-1
TO_DAYS(NOW())-1 = 前一天
mysql> SELECT NOW(),SYSDATE();
+---------------------+---------------------+
| NOW() | SYSDATE() |
+---------------------+---------------------+
| 2017-04-01 19:36:52 | 2017-04-01 19:36:52 |
+---------------------+---------------------+
SELECT NOW(),CURDATE(),CURTIME()
NOW() = 2016-08-01 16:25:46
CURDATE() = 2016-08-01
CURTIME() = 16:25:46
-------------------------------------------------
今天是2016年08月01日
date_sub('2016-08-01',interval 1 day) 表示 2016-07-31
date_sub('2016-08-01',interval 0 day) 表示 2016-08-01
date_sub('2016-08-01',interval -1 day) 表示 2016-08-02
date_sub(curdate(),interval 1 day) 表示 2016-07-31
date_sub(curdate(),interval -1 day) 2016-08-02
date_sub(curdate(),interval 1 month) 表示 2016-07-01
date_sub(curdate(),interval -1 month) 表示 2016-09-01
date_sub(curdate(),interval 1 year) 表示 2015-08-01
date_sub(curdate(),interval -1 year) 表示 2017-08-01
当天时间:
<if test="opTime != null ">
AND createTime <date_sub(#{opTime,jdbcType=TIMESTAMP},interval -1 day)
and createTime >= date_sub(#{opTime,jdbcType=TIMESTAMP},interval 0 day)
</if>
大型项目不要用 date_sub(curdate(),interval xx day)
直接把计算的日期传进去
字符串 时间 转换
https://blog.51cto.com/YangPC/5482952
综合
https://blog.csdn.net/zhaomengszu/article/details/80784096
查 学生信息,姓氏-刘,计算机系 cs,按学号大小排列(默认就是升序)
select * from Student
where Sname like '刘%' and Sdept='cs'
order by Sno;
统计各系 男女人数,人数降序排列
select Sdept,Ssex,count(Sno) from Student
group by Sdept,Ssex
order by count(Sno) desc;
SELECT xxId,COUNT(xxId) as `count`,
substring_index(group_concat(createTime order by createTime desc),',',1) as lastModifiedTime
FROM security_interception_log_1
WHERE date(createTime) = date_sub(curdate(),interval 1 day)
GROUP BY tenantId;
解析:
SELECT xxId,COUNT(xxId) as `count`,
substring_index(group_concat(createTime order by createTime desc),',',1) as lastModifiedTime
xxId对应的一系列时间进行降序排序,取第一个时间,这个时间带有时分秒
date() 会去除时分秒
FROM security_interception_log_1
WHERE date(createTime) = date_sub(curdate(),interval 1 day)
GROUP BY tenantId;
![](https://i-blog.csdnimg.cn/blog_migrate/d7e11e8ef57edd99c0f502d03adaf431.png)
分组排序
https://www.cnblogs.com/qi-yuan-008/p/12788213.html
SELECT tId,COUNT(xxId),
substring_index(group_concat(cTime order by cTime desc),',',1) as cTime
FROM `xx_log`
GROUP BY tId;
2.6.2 连接查询 >、<
表1.列名 比较运算 表2.列名
连接字段必须可比,但名字不必相同
比较运算:
>、<、=、>=、<=、<>(更常用)、!=
分类 |
---|
等值、非等值连接 |
自身连接 |
外连接 |
多表连接 |
2.6.2.1 等值连接 =
等值连接 =
缺点:会有重复列(就是连接的列)
查询每个学生 及其选修课情况
select Student.*, SC.* from Student,SC
where Student.Sno=SC.Sno;
![](https://i-blog.csdnimg.cn/blog_migrate/c452a3ee8a9c05939785d2307e9604ba.png)
2.6.2.2 自然连接 =、外连接
自然连接,可以无重复列
等值连接
select Student.*, SC.* from Student,SC
where Student.Sno=SC.Sno;
或 外连接
select Student.*, Cno, Grade from Student
left outer join SC on (Student.Sno=SC.Sno);
![](https://i-blog.csdnimg.cn/blog_migrate/219d9343ba4bcddf7e0a7b817a270746.png)
嵌套循环
排序合并,常用于 等价连接
索引连接
查询学生学号、姓名,他们选修了 2 号课程、成绩大于 90
select Student.Sno,Sname from Student,SC
where Student.Sno=SC.Sno and Cno='2' and Grade>=90;
开头的select Student.Sno
不可以是select Sno
!
2.6.2.3 自身连接
一个表和自己连接
要给表 起别名进行区分,属性要有别名前缀
查询每门课的 直接先修课名称
select first.Cname, second.Cname
from Course first, Course second
where first.Cpno=second.Cno;
2.6.2.4 外连接 left join
内连接 VC 外连接 https://blog.csdn.net/ly294687451/article/details/88251761
select Student.Sno,Sname
from Student,SC;
上面这个代码就是内连接,等价于:
select Student.Sno,Sname
from Student inner join SC;
3个外连接 | 解释 |
---|---|
左外连接left join =left outer join | 以左表为参考,把左表复合条件的全选出来 不用考虑右表对应是值是否存在 |
右外连接right join =right outer join | 以右表为参考,把右表复合条件的全选出来 不用考虑左表对应是值是否存在 |
全外连接 mysql 暂不支持 union 可实现全外连接 | 以两表为参考,把两表复合条件的全选出来 不用考虑对方表对应是值是否存在 全外连接 = 左外连接 + 右外连接 + 去重 |
查询每个学生 及其选修课情况 |
法1 等值连接
select Student.*, SC.* from Student,SC
where Student.Sno=SC.Sno;
或 外连接
select Student.*, Cno, Grade
from Student left outer join SC on
(Student.Sno=SC.Sno);
全外连接
select * from Student where Sdept='cs'
union
select * from Student where Sage<=19;
2.6.3 多表查询
查每个学生学号、姓名、选的课程名、成绩
select Student.Sno,Sname,Cname,Grade
from Student,SC,Course
where Student.Sno=SC.Sno and SC.Cno=Course.Cno;
2.6.4 嵌套查询
外层查询 父查询
内层查询 子查询
order by 不能出现在嵌套块里(即 不能在子查询里),只能在最外层的查询
例子:
select Sname from Student
where Sno in
(select Sno from SC
where Cno='2');
2 大类 | 解释 |
---|---|
不相关查询 | 子查询条件,不依赖于父查询 |
相关查询 | 子查询条件,依赖于父查询 其过程是 父查询第 1 个元组传给内层查询,内层查好了就返回值给父查询 重复该过程,直至全部查询完毕为止 |
包括: | |
in | |
比较运算符 | |
any all | |
exists |
2.6.4.1 in
查询学生,他和 刘晨 在同一个系
1 刘晨在哪个系
select Sdept from Student where Sname='刘晨';
2 查这个系所有的学生
不相关查询
select Sno,Sname,Sdept from Student
where Sdept in
(select Sdept from Student where Sname='刘晨');
法2 自身连接查询
select s1.Sno,s1.Sname,s1.Sdept
from Student s1,Student s2
where s1.Sdept=s2.Sdept and s2.Sname='刘晨';
查询学生学号、姓名,他们选修了 ‘信息系统’ 这门课
1 先找 ‘信息系统’ 的课程号
select Cno from Course where Cname='信息系统';
2 在表 SC 中找 该课程号的学生学号
select Sno from SC
where Cno in
(select Cno from Course where Cname='信息系统');
3 在表 Student 中找 学生学号对应的学号、姓名
select Sno,Sname from Student where Sno in
(select Sno from SC where Cno in
(select Cno from Course where Cname='信息系统'));
法2 连接查询 - 思路简单
select Student.Sno,Sname from Student,SC,Course
where Course.Cname='信息系统' and
Course.Cno=SC.Cno and
Student.Sno=SC.Sno;
2.6.4.2 比较运算 >、<
查询集合是单值可以用比较运算
>、<、=、>=、<=、!=、<>
!=、<>
均表示 不等于,<>
适用更广泛
查询学生,他和 刘晨 在同一个系
select Sno,Sname,Sdept from Student
where Sdept = (select Sdept from Student where Sname='刘晨');
找 每个学生超过他选修课程平均成绩的课程号
select Sno,Cno from SC x
where Grade >= (select avg(Grade) from SC y where y.Sno=x.Sno);
2.6.4.3 any all exists
any | 任意一值 |
all | 全部值 |
exists | 是否为空,返回 true 或 false |
组合 | 解释 |
---|---|
>any | 大于 某个值,大于 min |
>all | 大于 所有值,大于 max |
<any | 小于 某个值,小于 max |
<all | 小于 所有值,小于 min |
>=any | 大于等于 某个值,大于等于 min |
>=all | 大于等于 所有值,大于等于 max |
=
in
<>、!=
not in
exists、not exists 与以下替换
in
比较运算
any all
查 非计算机系 比 计算机系任意一学生年龄小的 学生姓名、年龄
select Sname,Sage from Student
where Sdept <> 'cs' and Sage < any(select Sage from Student where Sdept='cs');
法2 聚集函数 max()
select Sname,Sage from Student
where Sdept <> 'cs' and Sage < (select max(Sage) from Student where Sdept='cs');
查 非计算机系 比 计算机系所有年龄都小 的学生姓名、年龄
select Sname,Sage from Student where
Sdept <> 'cs' and Sage < all(select Sage from Student where Sdept='cs');
法二 聚集函数 min()
select Sname,Sage from Student
where Sdept <> 'cs' and Sage < (select min(Sage) from Student where Sdept='cs');
查 所有选了 1 号课程的 学生姓名
select Sname from Student
where exists(select * from SC where Sno=Student.Sno and Cno='1');
法2 连接查询
select Sname from Student,SC
where Cno='1' and SC.Sno=Student.Sno;
查询 没选 1 号课程的 学生姓名
select Sname from Student
where not exists(select * from SC where Sno=Student.Sno and Cno='1');
查询学生,他和 刘晨 在同一个系
exist
select Sno,Sname,Sdept from Student s1
where exists(select * from Student s2
where s1.Sdept=s2.Sdept and s2.Sname='刘晨');
法2 不相关查询
select Sno,Sname,Sdept from Student
where Sdept in(select Sdept from Student where Sname='刘晨');
法3 连接查询
select s1.Sno,s1.Sname,s1.Sdept from Student s1,Student s2
where s1.Sdept=s2.Sdept and s2.Sname='刘晨';
查 选修了全部课程的学生姓名
过程解析:https://www.cnblogs.com/first-bloodlalala/p/11909296.html
not exist 不可以是 exists
select Sname from Student where not exists
(select * from Course where not exists
(select * from SC
where Sno=Student.Sno and Cno=Course.Cno));
逻辑蕴含转换 not exists
exists 和 not exists 实现逻辑蕴含
p→q 等价于 ﹁p∨q
查学生号码,他至少选修了 学生201215122 选修的全部课程
p:学生201215122 选修了课程 y
q:学生 x 选修了课程 y
查询就是 (∀y)p→q
翻译:所有课程 y,只要201215122选修了y,那x也选修了y
(∀y)p→q
= ﹁(∃y(﹁(p→q)))
= ﹁(∃y(﹁(﹁p∨q)))
= ﹁∃y(p∧﹁q))
即 不存在课程y,201215122选修了,但 x 没选
select distinct Sno from SC sx where not exists
(select * from SC sy
where sy.Sno='201215122' and not exists
(select * from SC sz
where sz.Sno=sx.Sno and sz.Cno=sy.Cno));
简写一哈 是
select distinct Sno from SC sx where not exists
(select * from SC sy
where sy.Sno='201215122' and not exists
(select * from SC
where Sno=sx.Sno and Cno=sy.Cno));
2.6.5 集合查询 union
3类 | |
---|---|
交 union 可实现全外连接 | union 自动去重 union all 不去重 |
并 intersect | mysql不支持! |
差 except | mysql不支持! |
集合操作的列数必须相等、数据类型也相等
查 年龄不超过19 的学生 和 计算机系的学生
select * from Student where Sdept='cs'
union
select * from Student where Sage<=19;
查 选修了 1 号课程 或 2 号课程的学生
select Sno from SC where Cno='1'
union
select Sno from SC where Cno='2';
查 年龄不超过19 的学生 和 计算机系的学生 的交集
mysql 不能用 intersect,用 and 代替!
select * from Student
where Sdept='cs' and Sage<=19;
查 选修了 1 号课程 和 2 号课程的学生
select Sno from SC
where Cno='1' and Sno in(select Sno from SC where Cno='2');
查 计算机系的学生 和 年龄不超过19 的学生 的差集
就是 计算机系 年龄不超过19 的的学生
select * from Student
where Sdept='cs' and Sage<=19;
2.7 更新 数据
2.7.1 插入数据 insert
插入数据会检查完整性约束
2.7.1.1 手动
![](https://i-blog.csdnimg.cn/blog_migrate/563470ff66a7ab6412f0ae39421ac213.png)
![](https://i-blog.csdnimg.cn/blog_migrate/137d28620d4208b4b17ee30f7e8640bc.png)
![](https://i-blog.csdnimg.cn/blog_migrate/addff78d93a941a299c3d293cfa82204.png)
2.7.1.2 代码插入 insert
2.7.2.2.1 插入元组
插入单元组
insert into 表名(属性1, 属性2,...)
value (值1, 值2, ...);
插入多元组
insert into 表名(属性1, 属性2,...)
values (值1, 值2, ...),(值1`, 值2`, ...);
1 其中的属性列可以不用全部写,未提到的属性列 为空 null
2 顺序可以不一致
3 非空属性必赋值
insert into 表名 value (值1, 值2, ...);
4 若无属性列,表示要插入的是完整的元组,且顺序和定义的一致
例子
insert into Student(Sno, Sname)
value ('123','123');
![](https://i-blog.csdnimg.cn/blog_migrate/80ca17a67cdbe96d376679a05c3155b5.png)
插入多元组
insert into Student(Sno, Sname)
values ('133','153'),('122','111');
2.7.2.2.2 插入子查询结果
可以一次插入多个元组
每个系,求学生平均年龄,并把结果存入数据库中
1 建表
create table save_avg_Grade(
Sdept varchar(15),
avg_grade smallint
);
2 插入数据
insert into save_avg_age(Sdept,avg_grade)
select Sdept,avg(Sage) from Student group by Sdept;
2.7.2 修改数据 update
格式
update 表名
set 要修改的目标1 , 要修改的目标2
where 选择条件;
会检查完整性约束
主键不能修改
修改 1 个元组
将 学生201215121 的年龄改为 22
update Student
set Sage=22
where Sno='201215121';
修改多个字段:
update Student
set Sage=22 , name='张三'
where Sno='201215121';
修改 多个元组
所有学生年龄加 1
首先
SET SQL_SAFE_UPDATES = 0;
然后
update Student set Sage=Sage+1;
修改 子查询的结果
将 计算机系cs学生 的成绩置0
update SC set Grade=0 where Sno in (
select Sno from Student where Sdept='cs'
);
2.7.3 删除数据 delete
格式
delete from 表名 where 条件;
删除 1 个元组
删除 学号是 201215128 的学生记录
delete from Student
where Sno='201215128';
删除读多个元组 or
delete from Student
where Sno='201215128' or Sno='21';
删除子查询结果
有时事先要
SET SQL_SAFE_UPDATES = 0;
再
delete from SC where Sno in(
select Sno from Student where Sdept='cs'
);
清空表
delete from 表名
该表就被清空了
2.8 自定义函数
创建表A,并且在表A中循环插入N条数据
- 自定义函数,插入31条数据
DELIMITER $$
create function a_Data()
returns int
DETERMINISTIC
begin
declare num int default 31;
declare i int default 0;
while i<num DO
INSERT INTO A(id, isenable, notes, rate, ratedescription, taxitemid, version)VALUES(UUID(), '1', NULL, NULL, NULL, NULL, NULL);
set i=i+1;
end while;
return i;
end $$
delimiter;
Select a_Data();
2.9 删除 DROP TRUNCATE DELETE
DROP 表名;
直接删除表test(包括数据、结构、索引、触发器、约束),释放空间,将test删除的一干二净
再想用只能新建表了
drop 数据库/表/索引
TRUNCATE 表名test; = TRUNCATE table 表名test;
删除表test里的内容,并释放空间,但不删除表的定义,表的结构还在。
DELETE
1、删除指定数据
删除表test中年龄等于30的且国家为US的数据
DELETE FROM test WHERE age=30 AND country='US';
2、删除整个表
仅删除表test内的所有内容,保留表的定义,不释放空间。
DELETE FROM test
DELETE * FROM test;
truncate 速度快 (释放数据页来删除数据,在事务日志中记录页的释放),是ddl,不能回滚
delete 写服务器log → 速度慢些 (每删除一行,在事务日志中为该行记录一项),是dml,可以回滚
truncate 会重置自增字段,delete 不可以
外键foreign key关联不能使用truncate
https://blog.csdn.net/Klhz555/article/details/107992154
SET FOREIGN_KEY_CHECKS=0; #禁用外键
TRUNCATE TABLE admininfo; #删除数据
SET FOREIGN_KEY_CHECKS=1; #启用外键
#添加数据
insert into admininfo values(0, 'andy', md5('123321'), 'images/user.png');
#条件事务
commit;
#查询数据
select * from admininfo;
TRUNCATE TABLE删除数据的表上 增加数据时,要用UPDATE STATISTICS 维护索引信息
https://blog.csdn.net/weter_drop/article/details/119000471
UPDATE STATISTICS table_or_indexed_view_name
表空间:
https://cloud.tencent.com/developer/news/298548
3 高效查询
https://blog.csdn.net/qq_40893824/article/details/128720113
上一节链接:https://blog.csdn.net/qq_40893824/article/details/106404794
下一节链接:https://blog.csdn.net/qq_40893824/article/details/106492174