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 ADD FOREIGN KEY() REFERENCES ()

(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 [HAVING ] ]

[ ORDER BY [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`

等价于

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`

 

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

等价于

select `sno`, `sname`, `sage` from `student` where `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 [ASC|DESC] [,  [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 [( [， ...])]  VALUES（ [，， ] ...）

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

 

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

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

 

2、插入自查询结果

INSERT INTO [ ( [，] ...) ] 子查询；

 

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 = [，= ...]   [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语句却不能执行的，全部更改为可执行的语句，以实际为主。