浅谈SQL
在工作中,使用SQl语句操作数据库,大体是为了实现对数据的增删改查。所以,在这里先简单的陈列一些SQl语法吧!
下面介绍SQL语句(ps:打字太麻烦,直接上图)
DDl:数据库定义语言,用来定义数据库、表、列等。
DML:数据操作语言,用来操作数据库表中的记录(添加、删除、更新)
DQL:数据查询语言,用来查询记录(数据)
DCL:数据控制语言,用来定义访问权限和安全级别
SQl实战:
DDL部分:
创建下列四张表格:
- 学生表:student(学号、学生姓名、出生年月、性别)
- 成绩表:score(学号、课程号、成绩)
- 课程表:course(课程号、课程名称、教师号)
- 教师表:teacher(教师工号、教师姓名)
SQl实现:
创建数据库:CREATE DATABASE testsql;
创建学生表: `CREATE TABLE student(
学号 VARCHAR(50) PRIMARY KEY,
学生姓名 VARCHAR(50),
出生年月 VARCHAR(50),
性别 INT
);
创建成绩表:
CREATE TABLE score(
学号 VARCHAR(50) ,
课程表 VARCHAR(50),
成绩 VARCHAR(20),
primary key (学号,课程号)
);此处用来联合主键
创建课程表:
CREATE TABLE course(
课程号 VARCHAR(50),
课程名称 VARCHAR(50),
教师号 VARCHAR(50),
PRIMARY KEY(课程号,教师号)
);
创建教师表:
CREATE TABLE teacher(
教师号 VARCHAR(50) PRIMARY KEY,
教师姓名 VARCHAR(50)
);
向表中插入数据:
学生表: - INSERT INTO student(学号,学生姓名,出生年月,性 别)VALUES(
‘0001’,‘猴子’,‘1998-01-01’,1 );
成绩表: - INSERT INTO scores(学号,课程号,成绩) VALUES(
‘0001’,‘0001’,‘80’
);
课程表: - INSERT INTO course(课程号,课程名称,教师号) VALUES(
‘0001’,‘语文’,‘0001’
);
教师表: - INSERT INTO teacher(教师号) VALUES(
‘0003’
);
- 简单查询:
- 查询姓猴的学生的名单。SELECT * FROM student WHERE 学生姓名 LIKE ‘猴%’;(模糊查询)
- 查询"孟"老师的个数
SELECT COUNT(教师号) FROM teacher WHERE 教师姓名 LIKE ‘%孟%’;
查询课程编号为"0002"的总成绩
SELECT SUM(成绩) FROM scores WHERE 课程号=‘0002’;
查询选择了课程的学生的人数
SELECT COUNT(DISTINCT 学号) AS 学生人数 FROM scores;
查询各科成绩的最高分和最低分
SELECT 课程号,MAX(成绩)AS 最高分,MIN(成绩) AS 最低分 FROM scores GROUP BY 课程号;
查询每门课程被选修的学生人数
SELECT 课程号, COUNT(学号)AS 学生数 FROM scores GROUP BY 课程号;
查询男女生人数
SELECT 性别, COUNT()FROM student GROUP BY 性别;
分组条件的查询
查询平均成绩大于60的学生的学号和平均成绩
SELECT 学号,AVG(成绩) FROM scores GROUP BY 学号 HAVING AVG(成绩)>60;
查询至少查询了两门课程的学生的学号
SELECT 学号,COUNT(课程号) AS 选修课程数目 FROM scores GROUP BY 学号 HAVING COUNT(课程号)>2;
查询同名同姓学生名单并统计人数
SELECT 学生姓名 ,COUNT() as人数 FROM student GROUP BY 学生姓名 HAVING COUNT(*)>=2;
检索课程编号为“0004”且分数小于60的学生学号,结果按分数降序排列
SELECT 学号 FROM scores WHERE 课程号=‘0003’ AND 成绩<60 ORDER BY 成绩 DESC;
复杂查询
查询所有课程成绩小于60分学生的学号姓名
SELECT 学号,学生姓名 FROM student WHERE 学号 IN (
SELECT 学号 FROM scores WHERE 成绩 < 60
);
查询没有学完所有课程的学生的学号、姓名
SELECT 学号,学生姓名 FROM student WHERE 学号 IN (
SELECT 学号 FROM score GROUP BY 学号 HAVING COUNT(课程号)<(SELECT COUNT(课程号) FROM course)
);
查询1990年出生的学生的名单
SELECT 学号,学生姓名 FROM student WHERE YEAR(出生年月)=1998;
联结
查询所有学生的学号、姓名、选课数、总成绩
SELECT a.学号,a.学生姓名,COUNT(b.课程号) AS 选课数,SUM(b.成绩)
AS 总成绩 FROM student AS a LEFT JOIN scores AS b ON a.学号 = b.学号
GROUP BY a.学号;
查询平均成绩大于85的所有学生的学号、姓名、和平均成绩。
SELECT a.学号,a.学生姓名, AVG(b.成绩) AS 平均成绩
FROM student AS a LEFT JOIN scores AS b ON a.学号 = b.学号 HAVING AVG(b.成绩)>85;
使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩,分别统计:各分数段人数,课程号和课程名称
SELECT a.课程号,b.课程名称,
SUM(CASE WHEN 成绩 BETWEEN 85 AND 100
THEN 1 ELSE 0 END) AS ‘[100-85]’,
SUM(CASE WHEN 成绩 >=70 AND 成绩<85
THEN 1 ELSE 0 END) AS ‘[85-70]’,
SUM(CASE WHEN 成绩>=60 AND 成绩<70
THEN 1 ELSE 0 END) AS ‘[70-60]’,
SUM(CASE WHEN 成绩<60 THEN 1 ELSE 0 END) AS ‘[<60]’
FROM scores AS a RIGHT JOIN course AS b
ON a.课程号=b.课程号
GROUP BY a.课程号,b.课程名称;
查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名
SELECT a.学号,a.学生姓名 FROM student AS a INNER JOIN scores AS b ON a.学号=b.学号
WHERE b.课程号=‘0003’ AND b.成绩>80 ;