简单的对SQL操作语句的总结（复习总结DB）

文章适合期末复习一下关于DB的操作

简单的罗列一下目前用到的MySQL语句和一点基本概念，其中数据查询涉及到的最多，用得少的语句没有写，后面学练习JDBC的时候涉及到再补充，有哪里不足欢迎补充和纠正

MySQL不区分大小写，要注意命名，关键字可以大写也可以小写，文章里我都用小写（偷个懒）

数据定义： 定义、删除修改DB的对象（表、识图等

1、 基本表的定义和删除

（关于约束我放在后面）

--定义：可以单表也可以多表
create table Stu(
	Sno int(7),
	Sname char(10)
)
create table SC(
	Sno int(7),
	Cno char(6),
	Grade int (4)
)

--删除表Stu
drop table table_name

table_name 你的表名
col_name 你的列名

2、表结构的修改

（如果是对数据库操作，建议不要轻易去动表结构，表炸了就要哭）

--添加一列，列名和数据类型自己定
alter table table_name
add newcol_name int(5) 

--删除一列
alter table tabke_name drop column col_name  

3、实现完整性（主码、外码、约束）

约束都可以在建表时设置，也可以后面需要时添加

• 实体完整性（主键/码）

--primary key(主键：设为主键的列取值不能为空且不能重复)
--可以在建表的时候加，也可以后面加
--设Stu中的Sno为主键
create table Stu(
	Sno int(7) primary key,
	Sname char(10)
)
--或者
alter table table_name 
--PRIMARY_KEY_NAME为主键的名字
add constraint PRIMARY_KEY_NAME primary key(Sno)	

• 引用完整性（外键/码）
  主码Sno引用Stu的外码

alter table table_name 
--FOREIGN_KEY_NAME为外键的名字
add constraint FOREIGN_KEY_NAME foreign key(Sno) references Stu(Sno)	

• 用户定义完整性

--default约束,default_value为自己要设定的默认值
alter table table_name 
add constraint DF_NAME default default_value for(col_name)

--check：取值范围,xxx为逻辑表达式
alter table table_name 
add constraint DF_NAME default default_value check(xxx)

--unique：取值唯一
--unique的取值允许一个null，一个表可以有多个unique约束
--多个列可以共用一个unique约束，多个列不能有重复值
alter table table_name 
add constraint DF_NAME default default_value for(col_name)

数据查询：查询表中的数据

1、单表查询

(Sno为列名，Stu为表名)

--查询表中的列
select Sno from Stu
--* 查询所有的列
select * from Stu  
--可计算查询
select Sage-10,Sno from Stu
--增加一列常量
select Sno，'我是常量列',Sage from Stu
--列别名(有无as都可以，可以试试)
select Sno 我是别名, Sname as 我也是别名 from Stu

distinct（去掉重复行）

select后，目标列前

select distinct col_name from table_name

查询满足条件的元组（where）

--比较
select col1,col2 from table_name where 条件表达式
--确定范围(取col1在10~20之间的元组)
--（取10~20之外的用not between 10 and 20,也可以用where col1<10 or col1>20）
select col1,col2 from table_name where col1 between 10 and 20
--确定集合（col in('xx','xx','xx')）(也有not in)
select col1,col2 from table_name where col1 in('xx','xx','xx')
--字符串匹配
-- _ 匹配任意一个字符
-- % 匹配0个或多个字符
-- [] 匹配[]里面的任意一个字符
-- [^] 不匹配里面的任意一个字符
select Sname from Stu where Sname like '张%'
--多重条件查询（and连接）
select col_name where 条件1 and 条件2

对查询结果排序（order by desc表示降序 asc升序）

select col_name from table_name order by col_name desc/asc

聚合函数（count，sum，avg，max，min）

注意：聚合函数不能在where字句里面

select count(*/col_name) 学生人数 from Stu
select avg(*/col_name) 学生人数 from Stu

对查询结果进行分组统计（group by）

一般都是包含“每个”关键字

这里用到的是where筛选，也可以用having在分组后筛选，where是在分组前，即group by之前。这里要注意的是where字句是不能使用聚合函数的，having则可以

- - 例如：统计每个系（Dept）的女生（Sex='女'）的人数
-- 先把女生筛选（where）出来，随后将其按系别分组，最后再统计（group by）
-- where Sex='女'  group by Dept count(*) 整理一下得到结果
select count(*) 女生人数 from Stu
where Sex='女' group by Dept
-- having
select count(*) 女生人数 from Stu
group by Dept having Sex='女'

2、多表连接查询

内连接（join on）

过程类似于双重循环遍历，将满足条件的两行拼接在一起，然后放在结果表中

-- 将表一和表二内连接，连接条件是表一的某一列等于表二的同名列
select Name from table_1 join table_2 on table_1.Name=table_2.Name
-- 为了方便代码编写（为了偷懒），可以为表起别名 
-- 起表别名之后，代码中的其他地方的表名也要相应更改，否则会报错
select Name from table_1 T1 join table_2 T2 on T1.Name=T2.Name
-- 每多一个表来参与连接，就多加一个join on字句，连接的条件是两表有相同语义的列
select Name from table_1 T1 join table_2 T2 on T1.Name=T2.Name
join table_3 T3 join table_4 T4 on T3.xx=T4.xx

自连接

（后面的子查询会比这个容易想到一些，这个看看就好了）

自连接基础是起别名，将同一张表当成两张表

-- 查询和“xx”同一个系（Sdept）学习的同学的学号（Sno）和名字（Sname）
select S2.Sno,S2.Sname from Stu S1 join Stu S2
on S1.Sdept=S2.Sdept where S1.name='xx' and S2.name!='xx'

外连接（左、右连接）

左连接是在建立两表的连接时，如果左表的列不满足on后的条件，可以原样输出，右表不满足则用null填充；右连接则反之

select Name from table_1 T1 left join table_2 T2 on T1.Name=T2.Name
select Name from table_1 T1 right join table_2 T2 on T1.Name=T2.Name

3、使用top(limit)限制查询结果（最好和order by一起用）

top在select、distinct之后，列名之前（MySQL用limit，这里我只展示limit的用法）

-- 先排序，然后从第一条记录开始，展示五条记录
select * from table_name order by col_name limit 0,5

4、子查询（用in连接或用比较运算符（’>’ ‘=’ '<'等）连接）

比较运算符连接

--查询选修了课程号（Cno,放在SC这张表里）为“123”且成绩（Grade,也放在SC中）大于平均成绩的学生的学号和成绩
--（如果要查名字就要多表连接一下，这里就不写了，只是了解一下基础用法）

--分析
-- 这里有两个筛选条件：1、Cno='123'  2、Grade>avg(Grade) 多个条件用and连接
/*
where Cno='123' and Grade > (
-- 这里是求平均成绩
	select avg(Grade) from SC where Cno='123'
)
*/
-- 然后再查询
select Sno,Grade from SC
where Cno='123' and Grade > (
	select avg(Grade) from SC where Cno='123'
)

上面的自连接提到的例子也可以用子查询来实现，个人认为子查询还是比较好用的（条理清晰）

-- 查询和“xx”同一个系（Sdept）学习的同学的学号（Sno）和名字（Sname）
-- 先找出xx所在的系，再找这个系的所有同学
select Sno,Sname from Stu 
where Sdept in(
	select Sdept from Stu where Sname='xx'
)

数据操纵：增、删、改表中的数据

1、插入（insert into）

-- 列名和values的值一一对应（虽然有时候可以省掉列名，但是不建议，所以就不演示了）
-- 可以单条插入，也可以多条插入
insert into table_1 (col_1,col_2) values (xx,xx)
insert into table_1 (col_1,col_2) values (xx,xx)
insert into table_1 (col_1,col_2) values (xx,xx)

2、更新（update）

分为有条件更新和无条件更新（其实就是带where字句和不带的区别）

-- xxx写你要更新的内容，如果需要有条件的更新就加上where
update table_name set xxx where xxx
-- 将Stu中的成绩低于60的全设为60(但凡有这个，也不至于挂科了。。。)
update Stu set Grade=60  where Grade < 60

3、删除（delete）

和更新类似的用法（有条件、无条件）

-- 简单写一下语法，不演示了，不会轻易删表的
delete from table_name (where xxx)

其他的语句（用得少的）

top：就是返回前几个值（top n）
exists：返回真或假 不返回具体的记录（where exists()）

case：用法分为简单case表达式和搜索case表达式

-- 简单case表达式
-- 当满足不同的when时，返回对应的then的内容
select col_name1,col_name2,
case col_name3
	when 'xx' then 'xxx'
	when 'xx' then 'xxx'		
	when 'xx' then 'xxx'
	(..)
	end,col_name4 from table_name 
-- 搜索case表达式
-- 简单的例子：查询课程号为‘123’的同学的学号和成绩，并将成绩在90以上的显示为A，80~90为B，以此类推。。。
select Sno,Grade
case
	when Grade >=90 then 'A' 
	when Grade between 80 and 89 then 'B'
	end from SC where Cno='123'

懒得动手建表的小伙伴看这里

最后再附上我们老师上课老是拿来做实验的那几组数据，省去我们造数据库的时间，感谢老师！！不过建议可以自己试试去建，这样才知道要注意什么地方

-- 建空表
CREATE TABLE Student(
		 	Sno  CHAR(5) NOT NULL UNIQUE, 
        	Sname  CHAR(20) UNIQUE,          
         	Ssex  CHAR(2) ,
         	Sage  INT,
         	Sdept  CHAR(15));
CREATE TABLE SC(
            Sno CHAR(5) ,
            Cno CHAR(3) , 
            Grade   int,
            Primary key (Sno, Cno));
CREATE TABLE course(
            Cno CHAR(3) , 
            Cname CHAR(20),
            Cpno CHAR(3),
            Ccredit int,
            Primary key (Cno))

-- 插入数据
insert  into  student (Sno,Sname,Ssex,Sage,Sdept  )  values('95001','李勇','男',20,'CS');
insert  into  student (Sno,Sname,Ssex,Sage,Sdept  )  values('95002','刘晨','女',19,'IS');
insert  into  student (Sno,Sname,Ssex,Sage,Sdept  )  values('95003','王敏','女',18,'MA');
insert  into  student (Sno,Sname,Ssex,Sage,Sdept  )  values('95004','张立','男',19,'IS');

insert  into  sc (Sno,Cno,Grade  )  values('95001','1',92);
insert  into  sc (Sno,Cno,Grade  )  values('95001','2',85);
insert  into  sc (Sno,Cno,Grade  )  values('95001','3',88);
insert  into  sc (Sno,Cno,Grade  )  values('95002','2',90);
insert  into  sc (Sno,Cno,Grade  )  values('95002','3',80);

insert  into  course  values('1','数据库',5,4);
insert  into  course(Cno,Cname,Ccredit)  values('2','数学', 4);
insert  into  course  values('3','信息系统',1,4);
insert  into  course  values('4','操作系统',6,3);
insert  into  course  values('5','数据结构',7,4);

原以为一个白天加晚上就可以搞定，结果整理笔记加上码这篇文章也花了一天加一个上午的时间,还是有点工作量的。这块整理完准备去看看JDBC了，争取早点把Java全栈这一大块都串起来。

朋友们，一定要继续坚持，继续加油啊！！！

对了，这里还有一篇关于数据库的集合运算的文章，我觉得写得很好，放个链接在这啦

数据库的传统集合运算（并、差、交、笛卡尔积）和专门的关系运算（选择、投影、连接、除运算）