Mysql个人学习总结

版本5.6.46
学习笔记，总结学习过程中的知识点，不定时更新。
如有错误，请指出。
例子引用如下
https://blog.csdn.net/qq_45075198/article/details/100775251

sql数据定义功能

DATE 日期型

数据库管理

库管理命令

show databases; //显示所有库
select user() //显示连接用户
user 库名; // 选择数据库
select databases(); //显示当前所在的库

create database 库名;　　　　　　//创建库
show tables; //显示当前库的所有表
drop databases　库名; //删除库

create datebase|schema [if not exists] 库名  //创建库
show databases  库名				//显示所有库
use 库名									//使用库
drop database 库名					/删除库    

表管理命令

表的操作包括创建新表，修改表，删除表

create table 表名  [null | not null ] [primary key] [unique] 
default 默认值				//创建数据库
drop table 表名;			//删除表
alter table 表名 add 字段 类型;		//为表增加字段
alter table 表名 modify 字段 类型;	//修改字段
alter table 表名 drop 字段 类型;		//删除字段

show tables;	//查看当前库中所有表
show create table 表名;
desc 表名;		//查看表结构

例如以下代码：
学生表
学号，姓名，性别，出生日期，专业号，班级

create table student(
	sno varchar(10) NOT NULL,
	sname varchar(20) NOT NULL,
	ssex char(2) NULL DEFAULT '男',
	sbirth DATE NULL,
	zno varchar(4) null,
	sclass varchar(10) null,
	PRIMARY KEY(sno)
);

数据操纵功能

INSERT 插入数据

insert into 表名 valuse (值) 
 //若未指定列，则值的排列顺序必须和定义表时的列名排列顺序一致
 insert into 表名(列名,列名....)  values (值)
 //指定列
 insert into 表名 (列名,列名,列名) SELECT 列名,列名,列名 from 表名;
 //插入多行记录，可以从另一个表中抽取数据插入

UPDATE 修改数据

update 表名 set 列名='表达式' [列名='表达式'] [where 列名='表达式'];
//set句子要给出修改的列及其修改后的值 
// where子句指定待修改的记录应当满足的条件，where省略时，则修改表中所有记录
update 表名 set 列名='表达式'

DELETE 删除数据

delete from 表名 [where 列名='表达式' ]
//表名是要删除数据的表
// where子句， 待删除的记录应当满足的条件，where省略时，删除表中所有记录
delete from 表名 
// 删除表中所有记录 表为空表，但其定义仍存在于数据字典中

sql数据查询功能

单表查询

SELECT [ ALL | DISTINCT ] <目标列表达式> [ AS <别名> ]
FROM <表名或视图名> [ AS <别名> ]
[ WHERE<条件表达式> ]
[ GROUP BY<分组依据列> ]
[ HAVING <条件表达式> ]
[ ORDER BY <排序依据列> [ ASC | DESC ] ]
[ LIMIT N,M ]

（1） [ ALL | DISTINCT ] ：指定在结果集中是否显示重复行。ALL表示显示，默认ALL；DISTINCT表示不显示

（2）<目标列表达式> [ AS <新列名> ] ：指定为结果集选定的列。如果该处为 * ,则表示输出所有列

（3）<表名或视图名> [ AS <别名> ] ：指定从其中检索数据的表或试图

（4）[ WHERE<条件表达式> ] ：指定数据检索的条件

（5）[ GROUP BY<分组依据列> ] ：分组列，实现对数据的分组查询
（6）[ HAVING <条件表达式> ] ：用于分组后的筛选条件

（7）[ ORDER BY <排序依据列> [ ASC | DESC ] ] ：排序列，对结果集按<排序依据列>指定的列的值排序。其中ASC表示升序序列，DESC表示结果降序排列。

（8）[ LIMIT N,M ] ：表示从查询结果集中输出。 从第N+1行开始，查询M列。

整个SELECT语句的含义是：根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组，再按SELECT子句中的目标列表达式，选出元组中的属性值，形成结果表。
如果有GROUP子句，则将结果按<分组依据列>的值进行分组，该属性列的值相等的元组为一个组，每个组产生结果表中的一条记录。通常会在分组中使用 集函数 。如果GROUP子句待HAVING短语，则只有满足指定条件的组才予以输出。如果有ORDER子句，则还需根据<排序依据列>的值升序或降序排列。

select * from student;
select sname,sno sclass from student ; //列顺序可与基表中不同

//<目标列>不仅可以是属性列，也可以将查询出的属性列经过一定的计算后列出结果
select year(now())-year(sbirth) from student ;  //year()是输出年份的函数，now()是输出当前日期的函数

select sname  as 学生姓名 from student ;//as后是别名

select sno from sc ;
select distinct sno from sc;// 如有重复的行，可用distinct消除重复

常用查询条件

查询条件	谓词
比较（比较运算符）	=,>,>=,<,<=,<>(!=),not
确定范围	BETWEEN…AND ,NOT BETWEEN…AND
确定集合	in ,not in
字符匹配	like,not like 模糊查询
空值	is null, is not null
多重条件	and ，or

like : %代表任意长度，_代表单个字符
逻辑运算符and ,or用来联结多个查询条件，同时出现于where中，and优先级高于or，但可以用括号改变优先级。

select sname from student where sclass='商务1401';//查询商务1401全体学生名单

select sname from student where sbirth < '1998-01-01';//查询1995年以前出生的学生姓名

select distinct sno from sc where grade<60 //查询成绩不及格的学生的学号

select sname from student where sbirth>='1995-01-1' and sbirth<='1997-12-31';
select sname from student where sbirth between '1995-01-01' and '1997-12-31';
select sname from student where sbirth not between '1995-01-01' and '1997-12-31';
select sname from student where sbirth<='1995-01-1' or sbirth>='1997-12-31';
//查询在1995-01-01 到1997-12-31日出生的学生的姓名 between...and 和 <= ,>=

select sname from student where sclass in('信管1401','工商1301');
select sname from student where sclass not in('信管1401','工商1301');
//查询符合的元组；相对的谓词是not in，查找不属于指定集合的元组

select sname from student where sname like'_小%';
select sname from student where sname not like '小%';

空值（NULL）是没有值或值不确定，空值不可比较大小，不能用“=”对空值进行运算，应该用“is”。
注意，空值不是空格，空字符串 或0。
注意，is 不能用“=”代替

select sname,sno from student where grade is null;
select sname,sno from student where grade is not null;

用户可以用ORDER BY 子句指定按照一个或多个属性列的升序（ASC）或降序（DESC）排列查询结果。升序为默认值

select sno,grade from sc where cno='58130540' order by grade desc;
select * from student order by sclass,sbirth desc;//按班级升序排列，对同一班级的学生按年龄降序排列

集函数
（1）COUNT([ DISTINCT | ALL ] *)：统计元组个数
（2）COUNT(<列名>)：统计一列中值的个数
（3）SUM(<列名>)：计算一列值的总和（此列必须是数值型）
（4）AVG(<列名>)：计算一列值的平均值（此列必须是数值型）
（5）MAX(<列名>)：求一列值中的最大值
（6）MIN(<列名>)：求一列值中的最小值

select count(*) from student; //查询总人数
select count(distinct *) from student; //避免重复用distinct

select avg(grade) from sc where cno='58130540';
select max(grade) from sc where cno='58130540';
select max(grade),min(grade),sum(grade) from sc where cno='58130540';

group by 查询结果分组
GROUP子句可以将查询结果表的各行按一列或多列取值相等的原则进行分组。

select cno,count(sno) from sc group by cno;//查询各个课程号与相应的选课人数
select sno,count(cno) from sc group by sno having count(cno)>2;//查询选修了两门以上课程的学生的学号

WHERE 子句与HAVING短语的根本区别在于：作用对象不同。WHRER子句作用于基本表或视图中的记录，从中选择满足条件的元组；HAVING短语作用于分组查询后的结果，从中选择满足条件的结果。

连接查询

1.内连接
使用内连接时，如果两个表的相关字段满足连接条件，则从这两个表中提取数据并组合成新的记录。
当连接运算符为“=”时，称为等值连接。使用其他运算符为非等值连接。
执行连接操作的过程是：首先在表1中找到第一个元组；然后从头开始顺序扫描或按索引扫描表2，查找满足连接条件的元组， 每找到一个元组，就将表1中的第一个元组与该元组拼接起来，形成结果表中的一个元组。表2全部扫描完毕后，再到表1中找到第二个元组，然后再从头开始顺序扫描或按索引扫描表2，查找满足连接条件的元组，将表1中第二个元组与该元组拼接起来形成结果表的一个元组。重复此操作，直到表1全部元祖都处理完毕为止。

select * from student,sc where student.sno=sc.sno;
select * from student join sc on student.sno=sc.sno;
//关键词join连接表，关键词on描述连接条件。两张表中都有的列要加表名 表名.列名

select student.sno,sname,sex,sbirth,sclass cno,grade
from student,sc where student.sno=sc.sno; //没有重复列，自然连接
//第一种方式
select student.sno,sname,cno,grade from student,sc 
where student.sno=sc.sno and cno='58130540' and grade>=90;
//第二种方式
select student.sno,sname,cno,grade 
from student join sc on student.sno=sc.sno 
where  cno='58130540' and grade>=90;

//查询每个学生及其选修的课程名和成绩
select student.sno,sname,cname,grade from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno;
//第二种方式
select student.sno,sname,cname,grade from student
join sc on student.sno=sc.cno
join course on sc.cno=course.cno;

2.自连接
连接操作不仅可以在两个表之间进行，也可以是一个表与其自身进行连接，这种连接称为表的自身连接或自连接。

select distinct a.sname from student a,student b where a.sclass=b.sclass and a.sclass='商务1401';
//查询在同一个班学习的学生姓名
//为student表取两个别名a,b

3.外连接
将两个表完全连接在一起
LEFT OUT 左外连接 ，RIGHT OUT 右外连接

select student.sno,sname,ssex,sbirth,sclass,cno,grade
from student LEFT OUTER JOIN sc on student.sno = sc.sno;

嵌套查询

在sql语言中，一个SELECT…FROM…WHERE 语句称为一个查询块。将一个查询块嵌套在领一个查询块的WHERE 子句或HAVING短语的条件中的查询称为嵌套查询或子查询。

select sname from student sno in
(select sno from sc where cno='2');

在这个查询中，下层查询块(select sno from sc where cno=‘2’)是嵌套在上层查询块（select sname from student sno in）的WHERE条件中的。上层查询块称为外层查询或父查询或主查询，下层查询称为内层查询或子查询。
嵌套查询的求解方法是由里向外处理，每个子查询在其上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。
sql允许多层嵌套，即一个子查询中可以嵌套其他子查询。
注意：子查询的SELECT语句中不能使用ORDER BY子句，ORDER BY子句永远只能对最终查询结果排序。

1.带有IN谓词的子查询
带有IN谓词的子查询是指父查询与子查询之间用IN进行连接，判断某个属性列的值是否在子查询的结果中。

//查询与“郭爽”在同一个班学习的学生
select * from student where sclass in
(select sclass from student where sname='郭爽');