2021-10-10 SQL

一、操作数据库

1. 创建数据库

create database if not exists student

2. 删除数据库

drop database if exists student

3. 使用数据库

use student

4. 查看数据库

show databases

二、操作表

1. 建表

create table student
(
    sno int(10),
    sname varchar(30),
    sex char(1),
    age int(10),
    enterdate date,
    primary key(sno)
)ENGINE=INNODB DEFAULT CHARSET=utf8

2. 修改表

2.1 修改表名

alter table student rename as student1

2.2 添加字段

alter table student add classid int(10)

2.3 修改字段的约束

alter table student modify sex int(1)

2.4 重命名字段

alter table teacher change age age1

3. 删除表

3.1 删除字段

alter table student drop age1

3.2 删除表

alter table if exists teacher

三、操作表中的数据

1. DML语言

1.1 添加

insert into student(sno,sname) values (1,'xk')

主键会自增，可以省略

如果不些表的字段， 会一一按序匹配所有

数据与字段要一一对应

1.2 修改

update student set name='李'

1.2.1 where 子句运算符

1. = 等于
2. <>或！= 不等于
3. between and 在某范围内
4. and 与
5. or 或

1.3 删除

1.3.1 方式一

delete from student (where sno=1)

1.3.2 方式二

truncate table 'student'

1.3.3 两种方式的区别

相同点：
都能删除数据，都不会删除表的结构

不同点：
truncate会重新设置自动增量
truncate不会影响事务

1.3.4 delete删除问题

重启数据库：
如果用的是Innodb，自动增量会从1开始。因为数据存在内存中，内存断电即失
如果用的是myisam，继续从上一个增量开始。因为数据存在文件中

2. DQL查询语言

2.1 查询所有

select * from student

2.2 查询指定字段

select sname from student

2.3 查询不重复的指定字段

select distinct sname from student

2.4 一些特殊查询

2.4.1 查系统版本

select version()

2.4.2 计算表达式

select 100*3-1 as 计算结果

2.4.3 查询一些变量

查询自增步长

select @@auto_increment_increment

2.5 模糊查询

2.5.1 语法

%代表任意个字符（0个也算）

_代表一个字符

2.5.2 SQL语句

查询姓刘的人

select sno,sname from student where sname like '刘%'

2.6 联表查询

2.6.1 两表查询

2.6.1.1 要求：

输出考了试学生的studentNo，studentName，subjectNo，studentResult

2.6.1.2 思路：

1. 分析需求，分析查询字段来自哪些表
    发现，studentName没有在成绩表里，其他三个都在成绩表里能查到

2. 确定使用哪种连接查询

3. 确定交叉点，即两张表哪个数据相同
   studentNo

4. 确定判断的条件

2.6.1.3 SQL语句：

select s.studentNo,s.studentName,SubjectNo,studentResult
from student as s inner join result as r
on s.studentNo=r.studentNo

2.6.1.4 此时如果用left join或right join

left join：比inner join 多查出那些没有参加考试的人
           因为是以左表为基准，左表有的满足studentNo的，查询结果都会有

right join：结果就没有那些没有参加考试的人，因为是以右表成绩表为基准

2.6.1.5 缺考的人：

用left join 然后加上条件：where studentResult is null

2.6.2 三表查询

2.6.2.1 要求：

查出参加了考试的同学的分数、学号、学生姓名、科目名称

2.6.2.2 思路

1. 分析字段来自哪些表
   分数来自成绩表，学号和学生姓名来自学生表，科目名称来自科目表

2. 确定使用哪种查询

3. 确定交叉点
   交叉点：成绩表和学生：学号，科目表和成绩表：科目编号

4. 确定判断的条件

2.6.2.3 SQL语句

select studuentResult,s.studentNo,studentName,subjectName
from student s right join result r 
on r.studentNo=s.studentNo
inner join subject sub
on r.subjectNo=sub.subjectNo

2.7 自连接

2.7.1 核心

一张表拆成两张一样的表即可

2.7.2 练习

查询父类对应的子类信息

select c1.catoryName,c2.catoryname
from c2.parentid=c1.id

2.8 分页和排序

2.8.1 排序

升序 asc

降序 desc

select * from student order by sno asc

2.8.2 分页

2.8.2.1 为什么要分页

缓解数据库压力，给人更好的体验

2.8.2.2 语法

limit 起始 页面大小
limit 0,5 //第一条数据-第五条
limit 1,5 //第二条-第六条

2.8.2.3 网页应用

显示第N页

limit (N-1)*pagesize,pagesize//pagesize:页面大小

2.9 子查询（比联表快）

在where语句中嵌套一个子查询

2.9.1

查询数据结构的所有考试结果，包括学号、科目编号、成绩，按成绩降序

方式一：联表查询

select sub.studentNo,sub.subjectNo,studentResult
from result r inner join subject sub
on r.subjectNo=sub.subjectNo
where subjectName='数据结构'
order by subjectResult desc

方式二：子查询

select studentNo,subjectNo,studentResult
from result where subjectNo=(select subjectNo from subject where subjectName='数据结构')

即先查到科目名称为数据结构的科目编号，然后拿着该编号，去成绩表中，查出科目编号等于该编号的那些行