数据库 - 基础2-CSDN博客

本文链接：https://blog.csdn.net/qq_40893824/article/details/106441068

思路来源
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 新建连接

左键单击上图红框后：

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)		可参见：文章链接 1.2 小节
	decimal(size,d) 精确数值，字符串存储的 double		decimal(5, 2)： -999.99 ~ 999.99 具体参见：文章链接
字符串	有size	char(size) 定长	0 ~ 255 字符
	有size	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)
);

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

学生成绩表

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

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

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;

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 &lt;date_sub(#{opTime,jdbcType=TIMESTAMP},interval -1 day) 
	and createTime &gt;= 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://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;

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

嵌套循环
排序合并，常用于等价连接
索引连接

查询学生学号、姓名，他们选修了 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 手动

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');

插入多元组

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