SQL基础与进阶：从基本操作到复杂查询-优快云博客

本文链接：https://blog.youkuaiyun.com/zhangpinghao/article/details/8184524

一直对sql语句使用都不是很熟悉，最近去个笔试发现自己很菜，sql语句都不会写，现学现卖，总结下。

一：对基本表的操作

1.建立基本表

create table person(
id int not null auto_increment,
name varchar(40) not null,
city varchar(20),
salary int,
primary key(id)
)engine=innodb charset=gb2312;

2.修改基本表

alter table students

2.1 add sentrancedate datetime;增加新列，后面还可以加上完整性约束

2.2 alter column sage smallint; 修改列，可以修改列的数据类型

2.3 drop column sage; 删除列，或者 drop constraint un_sname删除对某列的唯一性约束（该条在mysql测试失败）

3.删除表

drop table students;

4.建立索引，建立索引时为了减少查询操作的时间，但是如果数据增删改查频繁，系统就会话费许多时间来维护索引。这时可以删除一些不必要的所以。我的理解是建立索引就会对索引所对应的列排序，此时查找某个索引值的话，就可以用二分查找了，所以查找更快乐，但是插入数据后，为了保持数据的有序性会导致时间开销变大。具体原理还有待学习。

create unique index rep_scno on reports(sno asc,cno desc);为reports表按sno升序和cno号降序建唯一索引，加了unique就表示是唯一的，这样能保证sno，cno是唯一的不会出现重复。单个的重复是可以的，也就是说能保证sno，cno都相同的数据不会有多行。如果只是对sno建立唯一索引，那么就能保证每行的sno号是唯一的，但这样是不对的，每个学生不会只能有一个成绩，而是每个学生每门课程只有一个成绩。另外：sql中asc可以省略，因为默认是asc升序

5.删除索引

drop index reports.rep_scno;

在mysql上不管用。。。

删除索引和删除唯一性的约束在mysql里好像都不管用，不知道为什么。。。还有问题是，在建立表时，constraint un_sno unqque(sno)，是给sno建立一个取值唯一的约束 un_sno，但为什么建立的是索引呢。也就是说mysql里，取值唯一性约束必然是索引，但是索引却可以不是取值唯一性的。

二：表的数据查询

1.简单查询-对象是一个表

1.1 比较运算符 {=,<,>} select sname,sage from students where sage>=18

1.2 谓词条件{between and ,in,like} select sname from students where sage in{20,21};

对于like，当然包括等于的功能，另外关键是它还可以使用两种通配符进行匹配，灵活度大大增大，类似于正则表达式。%表示任意长度，_表示一个字符，\在mysql里默认就是转义字符，mysql中不识别书中说的escape符号。

1.3 对结果排序

select sno,grade from reports where cno='c03' order by sno,grade desc;对查询结果按sno升序排序（不加东西默认是asc）按grade降序排序。

1.4 集函数{count(*)，count(列名)，sum，avg，max，min}

select count(distinct sno) from reports;查询选修了课程的学生人数，distinct是去除里面重复的sno，这样每个sno只会加一遍。

1.5 分组，分组的目的是细化集函数的作用对象，否则将作用于整个查询结果。

select cno,count(sno) cntsno from reports group by cno;查询各个课程号相应的选课人数。此时集函数count会分别作用于每个cno组。

如果想对这些组进行筛选，则可以使用having 关键字。

select sno from reports group by sno having count(cno)>=3;会选出那些选修课程数大于等于3的学号。

having 与where的区别是，where 作用于基本表或视图，而having作用于组。

2.涉及到多个表的查询

2.1使用表的连接，把多个表连接为一个表，当成一个表来处理，任何涉及到多个表的情况都可以用这种方法来解决

表的连接有，左连接，右连接，全（外）连接，内连接，交叉连接（又叫笛卡尔连接）

左连接：左表是全的，右表中没有对应项的话的会补充null，...left join...on...用法是SELECT *from A left join B on A.id = B.id ，后面一定要有on，这是左连接的定义决定的，那是连接的规则，条件。

右连接：连接右表是全的，左表中没有对应项的话的会补充null...我觉得右连接完全可以用左连接替代，只要把两个表的前后位置换一下不就可以。

全连接：数据库书上讲的是，返回左右两个表的全部行，没有匹配的都加null，...full join ...on....。好，这是标准的sql语法，但是mysql没有实现全连接，使用...full join ...on....这个会返回错误，但是只是使用full join的话是可以的，不过此时结果和交叉连接一样。

内连接：返回左右两个表中都有的匹配项，语法：...inner join ...on...可以缩写为...join ...on ...因为默认就是内连接。后面如果不加on的话，结果适合交叉连接的结果一样。

交叉连接：应该是最牛逼的连接，最全的连接，对两个表的各个行做笛卡尔乘积，返回的行数是两个表的行数的乘积。语法：...cross join...

nner join 内连接等价于下面的sql:
SELECT A.name, B.address
FROM A, B
WHERE A.id = B.A_id

下面总结下在mysql中的特别之处，(inner) join 不加on等价于cross join ，full join 等价于 cross join ，【,】逗号连接符也等价与cross join。但是逗号连接符不能加on，可以加where。join 连接符可以加on也可以加where，当然on和where是等价的。

所以：在mysql里的连接其实只有三个关键字：左连接...left join...on...，右连接：...right join...on...，全外连接和交叉连接：...join...，内连接（其实就是加上条件的笛卡尔交叉连接，所以我说三种，虽然写了4种）...join...on...。mysql里没有实现全外连接，它的full join是假的。

2.2 使用嵌套，上面说了使用连接已经可以解决所有的多个表的查询问题，使用嵌套只是对于多表查询的另外一种方案。

2.2.1 带谓词in的关键字查询

select sno ,sname,sdept from students where sdept in (select sdept from students where sname ='张宇');

2.2.2 使用带有比较运算符的嵌套查询，使用范围有限，只适用于内层查询只返回单值的情况。可以用下面any，all代替。

2.2.3 带谓词 any,all的嵌套查询，与比较符同时使用。可以使用集函数代替，而且一般来说使用集函数的方法效率更高。

SELECT sname ,sage,sdept from students where sdept<='自动化' and sage<=all (select sage from students where sdept='自动化') ORDER by sage desc;

2.2.4 带谓词 exists的嵌套查询，效率也比较高，处理能力也很强，很多要求都可以用这个来做。查询的处理过程是，首先去外层查询表中的第一个原则，根据它与内层子查询相关的属性比较，得到true还是false还判断是否把这个元组放入结果表。

select sname ,sdept from students where exists(SELECT * from reports where sno=students.sno and cno='c01');

三：表的数据更新

3.1 插入数据

3.1.1 插入指定元组 insert into students value('s05','张晓晓','男',21,'物理系',null);

3.1.1 插入其他表（这个表可以使我们通过selcet 语句获取的），这时候要求目的表和原表的列格式一一匹配。