一、SQL概述
1.SQL语言主要分为三类：
数据定义（DDL）：create，drop，alter
数据操纵（DML）：insert，update，delete，select
数据控制（DCL）：grant，revoke
2.SQL对数据库三级模式结构的支持:
·视图(view)：对应于外模式。
是从基本表或其他视图中导出的虚表，不存储。
·基本表：对应于模式。
特点：
一个基本关系 <---> 一个基本表；
多个基本表 ---> 一个存储文件；
·存储文件：逻辑结构 <---> 内模式，物理结构任意。
基本表和视图都是关系，都用SQL语言进行操作。
二、数据定义
1、定义概述
表 | 视图 | 索引 | |
创建 | CREATE TABLE | CREATE VIEW | CREATE INDEX |
删除 | DROP TABLE | DROP VIEW | DROP INDEX |
修改 | ALTER TABLE |
#没有修改索引、视图的操作，只能删除重建。
2.定义、删除与修改基本表
2.1 定义基本表
一般格式：
CREATE TABLE <表名> (
<列名> <数据类型> [列级完整性约束]，
<列名> <数据类型> [列级完整性约束]，
...
表级完整性约束条件>
) ;
<表名>：基本表的名字，
<列名>：基本表所含的属性(列)的名字，一个或多个。
列级完整性约束>：规定列要满足的一些约束条件(仅涉及一个属性，如：NOT NULL、UNIQUE、单属性主码、外码等)
表级完整性约束条件>：规定表要满足的一些约束条件(涉及一个或多个属性，如：单属性或组合的主码、外码等)
<数据类型> ：指明属性的数据类型及长度。
;为语句结束标志。
例1：创建基本表
Student（Sid，Sname，Ssex，Sage）
Course（Cid，Cname，Cteacher）
SC（Sid，Cid，Grade）
Sid,Cid分别为Student，Course的外键。
修改Sid为unsigned tinyint型
建表结果：
大意忘记更改字符集：
alter table Student character set utf8;
插入一些数据：
insert into Student
(Sid,Sname,Ssex,Sage)
values
(1,'zhangsan','M',18),
(2,'wangwu','M',17),
(3,'xiaohua','W',19),
(4,'dasan','M',18);
CREATE TABLE Course (
Cid TINYINT(4) UNSIGNED PRIMARY KEY,
Cname VARCHAR(20),
Cteacher VARCHAR(20)
)charset=utf8;
insert into Course
(Cid,Cname,Cteacher)
values
(001,'math','laoli'),
(002,'english','Merry');
2.2 修改基本表
一般格式：
ALTER TABLE
[ ADD
[ DROP <COLUMN列名> ]
[ MODIFY <COLUMN列名> <数据类型> [ [NOT] NULL]
[ DROP [CONSTRAINT]
<表名>: 指定要修改的基本表；
ADD子句: 用于增加新列和新的完整性约束；
DROP COLUMN子句: 用于删除指定列 ;
MODIFY COLUMN
DROP [CONSTRAINT]
例2.1：向Student表增加Scity。
alter table Student add Scity varchar(20);
向Scity中插入数据：
update Student set Scity='武汉' where Sid=1；
例2.2：将年龄数据类型改为smallint,并默认为0。
alter table Student modify Sage smallint not null default '0';
insert into Student (Sid,Sname,Ssex,Scity) values (5,'sansan','W','西安');
删除基本表：
DROP TABLE
#字段改名
#alter table Student change Sage Sage1 int(4);
#modify和change都可以修改列的定义，不同的是change 后面要写两次列名，不方便但是可惜修改列名。
2.3 建立与删除索引
索引是加快查询速度的有效手段。
一般格式：
CREATE [UNIQUE] [CLUSTER] INDEX
<表名>：指定要建索引的基本表。
<列名>：指定要建索引的列名。索引可以建在一列或多列上，各列名之间用逗号分隔。
ASC（升序）和DESC（降序）两种，缺省值为ASC。
UNIQUE：表明每一个索引值只对应唯一的数据记录。
CLUSTER：建立聚簇索引：表中的记录按索引项的顺序存放。一个基本表最多只能建立一个聚簇索引。
聚簇索引列数据的更新会导致表中记录的物理移动，代价较大，因此经常更新的列不宜建聚簇索引。
例3:为Student建立索引。
create index S_NO on Student(Sid desc);
删除索引：
DROP INDEX <索引名> ON <表名>
查看索引：
SHOW INDEX FROM <表名>
1)索引一经建立，就由DBMS使用和维护它,不需用户干预;
2)如果数据增删改频繁，系统会花费许多时间来维护索引, 这时，可以删除一些不必要的索引。
三、查询
1 查询概述
MySQLSQL用SELECT语句进行查询
一般格式为：
SELECT
FROM
[WHERE <条件表达式>]
[GROUP BY <列名列表1> [HAVING <条件表达式>] ]
[ORDER BY <列名列表2> [ASC|DESC]]
2 单表查询
2.1 列查询
1）查询指定列
eg：查询全体学生的学号与姓名。---> select Sid，Sname from Student；
select * from Student；
3）查询计算列
eg：查询所有学生的出生年份
SELECT Sname,YEAR(CURDATE())-Sage as year FROM Student;
eg:查全体学生的姓名、出生年份和性别，要求用小写字母表示性别。
select Sname,'Birth in',YEAR(CURDATE())-Sage as year,LOWER(Ssex) from Student;
select Sname as name,'Birth in' as 'Year of Birth',YEAR(CURDATE())-Sage as year,LOWER(Ssex) sex from Student;
Birth's Year)
结果：
2.2 行查询
1）消除重复行
eg：select DISTINCT
2)查询满足条件的行
通过WHERE语句来实现
条件
比较大小
确定范围 BETWEEN AND,NOT
确定集合 IN, NOT
字符匹配 LIKE, NOT
空值判断 IS NULL,IS NOT
组合条件
a.比较大小
eg:查询全部的男生。--->select * from Student where Ssex='M';
eg:查询年纪在19岁 以下的学生--->select * from Student where Sage<19;
b.确定范围
eg：查询年龄在19到23的学生的姓名、性别。
NOT
c.确定集合
eg：查找地址在上海和西安的学生。
select * from Student where Scity in ('西安','上海');
d.字符匹配
%：代表任意字符串。如a%b：a开头b结尾，长度>=2
_：代表任意一个字符。如a_b：a开头b结尾, 长度为3
eg:查看地址带有‘安’字的学生信息。
select * from Student where Scity like '%安%';
e.空值判断
eg:找出地址为空的学生姓名。
is null;
f.组合条件查询
用NOT、AND、OR和括号连接多个简单查询条件，形成一个复杂查询条件。
eg：查询地址在西安，性别男性的学生。
select * from Student where Scity='西安' and Ssex='M';
2.3 使用函数
COUNT(*) 统计元组个数
COUNT(<列名>) 统计列值的个数(不统计null)
SUM(<列名>) 计算数值型列的总和
AVG(<列名>) 计算数值型列的平均值
MAX/MIN(<列名>) 求列的最大值/最小值
DISTINCT，则在计算时要取消重复值。eg：COUNT([DISTINCT]
eg：查询学生的总人数--->select count(*) from Student;
查询不同的城市数--->select count(distinct Scity) from Student;
此外，函数还有字符串函数，日期时间函数，流程函数等，不再一一尝试。
2.4 对查询结果进行分组
用GROUP BY子句按列值相等的原则对查询结果进行分组。
eg：学生中男生女生的人数
select Ssex,count(*) from Student group by Ssex;
2.5 使用HAVING筛选分组
WHERE与HAVING的区别：作用对象不同。
WHERE作用于表，选择元组，不能直接包含集函数。
HAVING作用于组，选择组，常包含集函数。
eg: select Ssex,count(*) from Student group by Ssex having count(*)>2;
2.6 对查询的结果排序
用ORDER BY指定按某些列升序(ASC)或降序(DESC) ，其中ASC为缺省值。
eg：将id号进行从大到小排序。
select * from Student order by Sid desc;
(只有男生)select * from Student where Ssex='M' order by Sid desc;
3. 连接查询
1）等值查询
select Sname,Sno,Course.Cid,Cname,Cteacher from Student,Course where Student.Cid=Course.Cid;
两种特殊的连接：广义笛卡儿积、自然连接。
2)自身连接
准备课表zCourse 每节课都有先行课，找出每门课的先行课的先行课。
select C1.Cno,C1.Cname,C2.Cpno from zCourse as C1,zCourse as C2 where C1.Cpno=C2.Cno
表别名的作用：
1）若FROM后有相同的表,则要取不同的别名,否则无法表达目标列和连接条件 。
2）表名较长时为了缩短目标列和连接条件, 可取较短的别名。
3）AS可以省略（与目标列别名相同）
3）外连接
左连接：
select Sid,Sname,Sno,S.Cid,Cname from Student S left join Course C on S.Cid=C.Cid group by Sid;
右连接：
select Sid,Sname,Sno,C.Cid,Cname from Student S right join Course C on C.Cid=S.Cid;
#理解左连接查询方式(以两表id相等作为on的条件)：
先将左表数据查出，然后根据on后面的条件，将右表中凡是id与左表id相等的记录都查出来，与匹配的左表记录依次排成一行或多行，若无匹配的记录，则显示null。（右连接也同样）
4)复合连接
多个条件的连接
查询学生（Student）的学号，姓名，课程名（Course）及成绩（CS）。
SELECT
FROM
WHERE
AND
#含n张表的连接查询至少应有n-1个连接条件，否则在某些位置将蜕化为笛卡尔积。
4.嵌套查询
1）带有IN运算符的子查询
分步实现：
①确定“刘晨”所在系
SELECT Sdept FROM Student
WHERE Sname='刘晨'
②查找所有IS系或MA系的学生。
SELECT Sno, Sname, Sdept FROM Student
WHERE Sdept IN ('IS', 'MA' )
用嵌套查询实现：
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept IN ( SELECT Sdept
FROM Student
WHERE Sname=‘刘晨’ )
2)带有比较运算的子查询：父查询与子查询之间用比较运算符连接。
eg：查询“刘晨”所在系的每个学生比“刘晨”大多少岁。
SELECT Sname, Sage-( SELECT Sage
FROM Student
WHERE Sname = '刘晨' )
FROM Student
WHERE ( SELECT Sdept FROM Student
WHERE Sname= '刘晨' ) = Sdept
3)带ANY或ALL的子查询：子查询结果包含多个单列元组时使用比较运算符＋ANY或ALL：
eg：查询其他系中比IS系某个学生年龄小的学生名单
SELECT * FROM Student
WHERE Sdept <> 'IS' AND Sage<ANY(
SELECTSage FROM Student
WHERE Sdept='IS' )
ORDER BY Sage DESC
4)带EXISTS的子查询：EXISTS:存在量词，用来对子查询进行检查，若结果非空则返回 true，否则返回 false
eg：查询与“刘晨” 同系的学生：
SELECT Sno, Sname, Sdept FROM Student AS s
EXISTS
SELECT * FROM Student AS t
WHERE t.Sname='刘晨' AND t.Sdept=s.Sdept)
说明：a) 子查询的目标列用*，给出列名无意义。
相关子查询。
需多次执行子查询
