sql语句记录

用于操作的表： student(sno, sname, ssex, sage, sdept) sc(sno, cno, grade) course(cno, cname, cpno, credits) 1、创建表(CREATE) CREATE TABLE 表名(列名 数据类型 [列级完整性约束条件] [，列名 数据类型 [列级完整性约束条件] ]... [，表级完整性约束

用于操作的表：

student(sno, sname, ssex, sage, sdept)

sc(sno, cno, grade)

course(cno, cname, cpno, credits)

1、创建表(CREATE)

CREATE TABLE <表名>(<列名> <数据类型> [列级完整性约束条件]

[，<列名> <数据类型> [列级完整性约束条件] ]...

[，<表级完整性约束条件> ] )；

CREATE TABLE student(`sno` CHAR(5) NOT NULL UNIQUE, `sname` CHAR(8) NOT NULL UNIQUE, `ssex` INT, `sdept` CHAR(20), PRIMARY KEY(`sno`));

CREATE TABLE course(`cno` CHAR(4) NOT NULL, `cname` CHAR(10) NOT NULL, `cpno` CHAR(4), `credits` INT, PRIMARY KEY(`cno`));

CREATE TABLE sc(`sno` CHAR(5), `cno` CHAR(4), `grade` INT, PRIMARY KEY(`sno`, `cno`), FOREIGN KEY(`sno`) REFERENCES student(`sno`)，FOREIGN KEY(`cno`) REFERENCES course(`cno`), CHECK(`grade` BETWEEN 0 AND 100));

2、基本表的修改（ALTER）

ALTEER TALBE <表名> [ADD <新列名> <数据类型> [完整性约束] ]

[DROP <完整性约束名>]

[MODIFY <列名> <数据类型>];

(1)增加列

ALTER TABLE `student` ADD `address` CHAR(30)

不论基本表中原来是否已有数据，新增加的列不能定义为“NOT NULL”

(2)补充定义主码

ALTER TABLE <表名> ADD PRIMARY KEY(<列名>)

(3)撤销定义主码

ALTER TABLE <表名> DROP PRIMARY KEY;

(4)补充定义外码

ALTER TALBE <表名1> ADD FOREIGN KEY(<列名>) REFERENCES <表名2>(<列名>)

(5)撤销外码定义

ALTER TABLE <表名> DROP <外码名>

(6)修改属性数据类型以及约束条件

ALTER TABLE `student` MODIFY `sage` SMALLINT;

ALTER TABLE `student` DROP UNIQUE(`sname`);

3、删除表（DROP）

DROP TABLE <表名>；

DROP TABLE `student`;

4、SQL数据查询（SELECT）

SELECT [ALL | DISTINCT] <目标列表达式> [， <目标列表达式> ]…

FROM <表名> [，<表名> ]…

[ WHERE <条件表达式> ]

[ GROUP BY <列名1> [HAVING <条件表达式>] ]

[ ORDER BY <列名2> [ASC | DESC] ];

(1)查询指定列

select `sno`, `sname`, `sage`, `ssex`, `sdept` from `student`;

(2)查询全部列

select * from `student`;

(3)查询经过计算的列

select `sname`, 2013-`sage` from `student;

select ISLOWER(`sno`) LSNO, `sname`, 'YEAR OF BIRTH' BRITH, 2007-`sage` BIRTHDAY from `student`;

(字段或者表达式后面跟随的字段就是新的列名，譬如BRITH和BIRTHDAY， 前面的是表达式或者值)

select `sno` from `sc`;

(4)消除取值相同的元组

select DISTINCT `sno` from `sc`;

默认是ALL，指定DISTINCT则会消除重复行(消除取值相同的元组，重复的元组只显示一行)

(5)查询满足条件的元组

查询条件 谓 词

比较 =, >, <, >=, <=, !=, <>, NOT+上述比较运算符

确定范围 BETWEEN AND, NOT BETWEEN AND

确定集合 IN, NOT IN

字符匹配 LIKE, NOT LIKE

空值　 IS NULL, IS NOT NULL

多重条件（逻辑谓词） AND, OR

1、比较大小

select `sno`, `sname` from `student` where `sdept` = '计算机系'

select `sname`, `sage` from `student` where `sage` <21;

等价于

select `sname`, `sage` from `student` where NOT `sage` >= 21;

2、确定范围

select `sno`, `sname`, `sage` from `student` where `sage` BETWEEN '19' AND'21';

等价于

select `sno`, `sname`, `sage` from `student` where `sage` >= '19' AND `sage` <= '21'

select `sno`, `sname`, `sage` from `student` where `sage` NOT BETWEEN '19' AND '21';

等价于

select `sno`, `sname`, `sage` from `student` where `sage` <'19' OR `sage` > '21';

3、确定集合查询

select `sno`, `sname`, `ssex` from `student` where `sdept` IN('计算机系','数学系');

等价于

select `sno`, `sname`, `ssex` from `student` where `sdept`= '计算机系' OR `sdept` = '数学系';

select `sno`, `sname`, `ssex` from `student` where `sdept` NOT IN('计算机系','数学系');

等价于

select `sno`, `sname`, `ssex` from `student` where `sdept`!= '计算机系' AND`sdept` != '数学系';

4、字符匹配的查询

[NOT] LIKE '匹配串' [ESCAPE '<换码字符> ' ] 注: 换码字符后要跟一个空格，例如 ESCAPE '\ ' 实践得出的。

%(百分号) 代表任意长度(长度可以为0)的字符串。例如, a%b表示以a开头，以b结尾的任意长度的字符串，如acb, adertb, abdedb等

_(下划线) 表示任意单个字符。例如， a_b表示以a开头，以b结尾的长度为3的任意字符串，如adb, agb等

select * from student where `sname` like '李%';

select * from student where `sname` NOT LIKE '李%';

select * from `student` where `sname` like '__小%';

注：一个汉字要占两个字符，所以匹配串“小”前面需要两个“_”

假设有个字段值为DB_Design,

select `cno`, `credits` from `course` where `cname` LIKE 'DB\_Design` ESCAPE '\ ';

5、涉及空值的查询

空值（NULL）在数据库中具有特殊的含义，它表示不确定的值。判断某个值是否为NULL，不能使用普通的比较运算，

而只能使用专门判断NULL的子句来完成

select `sno`, `cno` from `sc` where `grade` IS [NOT] NULL;

6、多重条件的查询：

AND 优先级高于 OR， 但用户可以用括号改变优先级

(6) 对查询结果进行排序

ORDER BY <列名1> [ASC|DESC] [, <列名n> [ASC|DESC] ]......

默认是升序排序，即ASC

select * from `student` ORDER BY `sdept` DESC, `sage`, `sno`;

(7)使用集函数

COUNT([DISTINCT|ALL] *) 统计表中元组的个数

COUNT([DISTINCT|ALL] <列名>) 统计一列中值的个数

SUM 计算一列值的总和（此列必须是数值类型）

AVG 计算一列值的平均值（此列必须是数值类型）

MAX 求一列中的最大值

MIN 求一列中的最小值

查询全体学生的总人数

select COUNT(*) from student;

查询选修了课程的学生人数

select COUNT(DISTINCT `sno`) from `sc`;

计算选修3号课程的学生平均成绩

select AVG(`grade`) from `sc` where `cno` = '3';

查询选修3号课程的学生的最高成绩

select MAX(`grade`) from `sc` where `cno` = '3';

(8)对查询分组

GROUP BY 子句将查询结果表按其某一列或多列值分组，值相等的为一组。

HAVING短语只用在组里面使用，通常跟着GROUP BY 一起使用。

查询各个课程号及相应的选课人数

select `cno`, COUNT(`sno`) CNTSNO from `sc` GROUP BY `cno`;

查询选修了2门以上课程的学生学号

select `sno` from `sc` GROUP BY `sno` HAVING COUNT(*) > 2;

注：where子句作用在表，having短语作用于组，所以一般都是跟着GROUP BY

(9)连接查询

1、不同表之间连接查询

查询每个学生及其选修课程的情况

select `student`.*, `sc`.* from student, sc where student.sno = sc.sno

2、自身连接查询

select A.cno, A.cname, B.cpno from course A, course B where A.cpno = B.cno;

3、外连接查询

标准SQL使用*，有的数据库使用+。(貌似实践不靠谱，用的是 RIGHT(LEFT) [OUTER] JOIN)

外连接分外左外连接和又外连接。

SELECT student.*, sc.* FROM student RIGHT JOIN sc ON student.sno = sc.sno

注：RIGHT JOIN 是右外连接，LEFT JOIN 是左外连接，必须用ON来做判定，不用where，JOIN哪边，哪边的数据就是全的，另一边没有的数据会用NULL来表示

(10) 嵌套查询

将一个查询块嵌套在另一个查询块的WHERE子句或HAVING 短语的条件中的查询称为嵌套查询。

如

select `sname` from `student` where `sno` IN(select SNO from sc where `cno` = '2');

(11) 带有EXISTS谓词的子查询

带有EXISTS只返回逻辑“TRUE”和"FALSE"

select * from student where EXISTS(select * from sc where `sno` = student.sno and cno = '3')

5、SQL插入数据（INSERT）

1、向表中插入单个元组

insert into <表名> [(<属性1> [，<属性2> ...])] VALUES（<常量1> [，， <常量2>] ...）

注：into后面没有出现的属性列，其新元组在这些列上取空值。如果into后面一个属性都没出现，那么就是默认全部属性

insert into student VALUES('S8804', '刘红英', '女', '20', '数学系')

insert into sc(`sno`, `cno`) VALUES('S8804', '2'); 其余未加入字段为空值

2、插入自查询结果

INSERT INTO <表名> [ (<属性1> [，<属性2>] ...) ] 子查询；

insert into history-student select * from student;

insert into TG(`sno`, `tgrade`) select `sno`, SUM(`grade`) from sc group by sno;

6、SQL修改数据（UPDATE）

UPDATE <表名> SET <列名1>=<表达式1> [，<列名n>=<表达式n> ...] [WHERE <条件>]

update `sc` set grade = 0 where '数学系' = (select `sdept` from `student` where `student`.`sno` = sc.sno) ;

7、SQL删除数据（DELETE）

DELETE FROM <表名> [WHERE <条件>]

delete删除的是数据，不是关于表的定义和属性

delete from `student` where `sno` = 'S8203'

delete from `student`; 删除这个表数据

注：增加、删除、修改操作只能针对一个表操作，查询可以针对多张表。

注：以上语句全部经由SQLyog执行验证通过，标准SQL语句却不能执行的，全部更改为可执行的语句，以实际为主。