事业编计算机岗——常用sql语句

SQL语句

school 数据库中有三张表
student（Sno，sname，ssex，sage，sdept）
course（Cno，cname，cpno，ccredit）
sc(Sno,Cno,gade）

1、创建表

 Create table XXX
(属性1 数据类型（长度），
属性2 数据类型（长度）,
……
Primary key (xxx));
【双主键用逗号在括号内隔开】
例
CREATE TABLE student
(sno char(6),
sname varchar（8），
ssex char(2),
sage smallint,
sdept varchar(15),
primary key(sno));

2、插入语句

 插入单条
INSERT INTO items(name,city,price,number,picture)
VALUES(‘耐克运动鞋’,‘广州’,500,1000,‘003.jpg’);
 插入多条
INSERT INTO
表名
VALUES
([列值],[列值])),
([列值],[列值])),
([列值],[列值]));
INSERT INTO
items(name,city,price,number,picture)
VALUES
(‘耐克运动鞋’,‘广州’,500,1000,‘003.jpg’),
(‘耐克运动鞋2’,‘广州2’,500,1000,‘002.jpg’);

3、 修改


UPDATE XXX
SET column1 = value1, column2 = value2, …
WHERE condition;
 在表中添加删除列
添加
ALTER TABLE table_name
ADD column_name datatype
删除
ALTER TABLE table_name
DROP COLUMN column_name

4、删除

 DELETE
FROM ＜表名＞[ WHERE ＜条件＞];
删除学号为200215128的学生记录（删除满足条件的元组）。
DELETE FROM Student WHERE Sno =‘200215128’
删除所有的学生选课记录（删除所有元组）
DELETE FROM sc
删除计算机科学系所有学生的选课记录（带子查询的删除语句）
DELETE
FROM sc
WHERE sno in ( SELECT sno
FROM student
WHERE sdept =’ CS ')
 删除表employees，departments
DROP TABLE IF EXISTS employees, departments;
 删除触发器
DROP TRIGGER IF EXISTS XXX，XXX;
 删除约束
DROP CONSTRAINT IF EXISTS XXX,XXX;
 删除整个数据库
DROP DATABASE XXX
 重建并使用数据库
CREATE DATABASE xxx;
USE XXX;

5、 条件

 Where
删除所有JSJ系的男生
delete from Student where Sdept= =‘JSJ’ and Ssex='男";
查询姓名中第2个字为“明”字的学生学号、性别。
Select sno，ssex from student where sname like‘明%’
【%：替代1或n个字符，:替代1个字符】

 In
删除"数据库原理”的课的选课纪录
delete from SC where Cno in (select Cno fromCourse where Cname=数据库原理");
【in为满足括号内条件的意思，如果括号内有多个条件，条件与条件之间是或的关系】
修改李文庆的1001课程的成绩为93分
Update SC set grade = ‘93’
Where cno = ‘1001’ and sno in （select sno where sname = ‘李文庆’）；

6、 排序

 Order by
ORDER BY 关键字默认按照升序对记录进行排序
SELECT column1, column2, …
FROM table_name
ORDER BY column1, column2, … ASC|DESC;
Asc:升序
Desc：降序
查询年龄在19至21岁之间的女生的学号,姓名,年龄,按年龄从大到小排列。
Select sno，sname，sage from student where ssex = ‘女’ and sage between 19 and 21
Order by sage desc；
 Group by
按值分组，值相等的为一组
查询JSJ、SX、 WL系的年龄大于25岁的学生学号,姓名，结果按系排列
Select sno ，cno from student where sdept in （jsj，sx，wl）and sage>25 group
By sdept
【聚焦函数
Count（） 统计元组个数
Count（列名） 统计一列中值的个数
Sum（列名） 计算一列中值的总和（要求数值型）
AVG （列名） 计算一列中值的平均值（要求数值型）
Max（列名） 求一列中最大值
Min（列名） 求一列中最小值
】
计算每一门课的总分、平均分，最高分、最低分，按平均分由高到低排列
SELECT Cno, SUM(grade),avg(grade),max(grade),min(grade)
from sc
GROUP BY Cno ORDER BY avg(grade) DESC
 Having
查询平均分大于80分的学生学号及平均分
select Sno , avg(grade)
from sc GROUP BY Sno HAVING avg(grade) >‘80’
统计选修课程超过 2 门的学生学号
SELECT Sno
from sc GROUP BY Sno HAVING count() >‘2’
统计有10位成绩大于85分以上的课程号。
SELECT Cno FROM sc
WHERE grade>‘85’ GROUP BY Cno HAVING count(*)=‘10’
【having在分组后过滤，也就是写在group by之后，可以包含聚合函数
Where 在分组前过滤，写在group by前，不能包含聚合函数
】

7、 连接

 从相关的所有表中查，通过 where 表名.列名=表名.列名的方式将两个表连接起来
查询 JSJ 系的学生选修的课程号
select cno from student,sc where student.sno=sc.sno and sdept=’JSJ’
查询数据库原理不及格的学生学号及成绩
select sno,grade from sc ,course where sc.cno=course.cno and cname=’数据库原理’ and grade<60;
查询选修“数据库原理”课且成绩 80 以上的学生姓名
select sname from student , sc , course where student.sno=sc.sno and sc.cno = course.cno and grade>80 and cname=’数据库原理’
查询平均分不及格的学生的学号，姓名,平均分。
select sno,sname , avg(grade)from sc , student where student.sno=sc.sno group by student.sno having avg(grade) <60
查询女学生平均分高于 75 分的学生姓名。
Select sname from sc,student where student.sno=sc.sno and Ssex=’女’ Group by student.sno having avg(grade)>75
 Join
查询男学生学号、姓名、课程号、成绩。(一门课程也没有选修的男学生也要列出，不能 遗漏)
select student.sno,sname,cno,grade from student left join sc ON student.sno=sc.sno and ssex=’男’
【INNER JOIN：如果表中有至少一个匹配，则返回行
LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
FULL JOIN：只要其中一个表中存在匹配，则返回行】

8、 嵌套

 两张表分开查，一般有带in，带运算符，带all/any，带exists几种
查询平均分不及格的学生人数
select count(*) from student where sno in ( select sno from sc group by sno having avg(grade)<60 )
查询平均分最高的学生学号及平均分
select sno,avg(grade) from sc group by sno having avg(grade) >=all ( select avg(grade) from sc group by sno )
查询没有选修 1001，1002 课程的学生姓名。
Select sname from student where not exists (Select * from course where cno in (‘1001’,’1002’) and Not exists ( select * from sc where sno=student.sno and cno=course.cno ) )
查询平均分前三名的学生学号
select top 3 sno from sc group by sno order by avg(grade) desc
 Union
UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。
查询 1001 号课程大于 90 分的学生学号、姓名及平均分大于 85 分的学生学号、姓名
select student.sno,sname from student,sc where cno=’1001’ and grade>90 union select sno,sname from student where sno in ( select sno from sc group by sno having avg(grade)>85 )