-- 创建student表：
DROP TABLE IF EXISTS student;
CREATE TABLE student(
sno INT PRIMARY KEY,
sname VARCHAR(20) NOT NULL,
sex CHAR(2) DEFAULT '男',
age INT NOT NULL DEFAULT 20,
addr VARCHAR(20)
);
-- auto_increment：表示自增长
-- primary key：表示主键（能唯一的表示表中的每一行）
-- 修改表格
-- 1）添加列
ALTER TABLE student ADD birthday INT;
-- 2）修改列的类型
ALTER TABLE student MODIFY birthday datetime;
-- 3）修改列名
ALTER TABLE student CHANGE birthday birth datetime;
ALTER TABLE student CHANGE birth bth VARCHAR(20);
-- 4）删除列
ALTER TABLE student DROP COLUMN bth;
-- 5）修改表名
ALTER TABLE student RENAME TO stu2;
RENAME TABLE stu2 TO stu;
-- 可以同时修改多个表的名字
CREATE TABLE t1(tname INT);
RENAME TABLE stu TO stu1,t1 TO t2;
-- 删除表
drop table t2;
-- 插入数据
INSERT INTO student(sno,sname,sex,age,addr)VALUES(101,'贾宝玉','男',21,'荣国府');
-- 如果给所有的列都添加数据，可以省略列名【不推荐使用】
-- 这时，值的顺序必须与表结构一致
INSERT INTO student
VALUES (102, '林黛玉', '女', 20, '荣国府');
INSERT INTO student(sname,age,sno)
VALUES('贾链',30,103);
-- 有默认值的列，或可以为null的列，或自增长的列，可以不给值
-- 给定的值必须符合数据类型和约束的要求
INSERT INTO student(sno,sname,age)
VALUES(104,'贾蔷',26);
INSERT INTO student(sno, sname,age)
VALUES(105,'贾珍',25);
INSERT INTO student(sno, sname,age)
VALUES(106,'贾蓉',18);
-- 修改数据
-- 106的性别改成女
UPDATE student SET sex='女'
WHERE sno=106;
-- 103,104地址改为杭州
UPDATE student SET addr='杭州'
WHERE sno=103 OR sno=104;
-- 将贾珍的年龄增加2岁，地址改为北京
UPDATE student SET age=age+2, addr='北京'
WHERE sname='贾珍';
-- age年龄都加1
UPDATE student SET age=age+1;
-- 删除数据
DELETE FROM student WHERE sname='贾蓉';
DELETE FROM student WHERE sno>110;
=============================================
-- 创建sc表
DROP TABLE IF EXISTS `sc`;
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
`scid` int(11) NOT NULL auto_increment,
`sno` int(11) DEFAULT NULL,
`cno` int(11) DEFAULT NULL,
`score` DOUBLE DEFAULT NULL,
PRIMARY KEY (`scid`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
INSERT INTO `sc` VALUES ('1', '101', '11', '86');
INSERT INTO `sc` VALUES ('2', '101', '12', '45');
INSERT INTO `sc` VALUES ('3', '102', '11', '72');
INSERT INTO `sc` VALUES ('4', '102', '12', '68');
INSERT INTO `sc` VALUES ('5', '103', '11', '26');
INSERT INTO `sc` VALUES ('6', '103', '12', '70');
INSERT INTO `sc` VALUES ('7', '104', '11', '61');
INSERT INTO `sc` VALUES ('8', '104', '12', '81');
INSERT INTO `sc` VALUES ('9', '104', '13', '69');
INSERT INTO `sc` VALUES ('10', '101', '12', '65');
INSERT INTO `sc` VALUES ('11', '103', '11', '36');
INSERT INTO `sc` VALUES ('12', '103', '11', '60');
分组查询：group by
-- select后面只能有分组列、聚合函数
-- 统计每个学生的总成绩、平均成绩
SELECT sno, SUM(score),AVG(score)
FROM sc
GROUP BY sno;
-- 统计每门课程的总成绩、平均成绩、考试次数
SELECT cno, SUM(score), AVG(score), COUNT(*)
FROM sc
GROUP BY cno;
-- 统计男生、女生的平均年龄、人数
SELECT sex, AVG(age), COUNT(*)
FROM student
GROUP BY sex;
-- 根据多列分组
-- 统计每个学生每门课程的平均成绩、考试次数
SELECT sno, cno, AVG(score), COUNT(*)
FROM sc
GROUP BY sno, cno
ORDER BY AVG(score);
-- 如果对组有筛选条件：having
-- 注意：having必须跟在group by后面
-- where：是对行的筛选
-- having：是对组的筛选
-- 统计每个学生及格了的成绩的总成绩、.....
-- 结果只显示平均成绩高于75分的
SELECT sno, SUM(score),AVG(score),MAX(score),MIN(score)
FROM sc
WHERE score>=60
GROUP BY sno
HAVING AVG(score)>=75;
-- 统计有地址的学生的男、女生平均年龄、人数
-- 结果只显示平均年龄高于20的
SELECT sex,AVG(age),COUNT(*)
FROM student
WHERE addr IS NOT NULL
GROUP BY sex
HAVING AVG(age)>20
ORDER BY sno DESC;
/*
select 列名
from 表名
where 行的条件
group by 分组列
having 组的条件
order by 排序列
*/
-- 统计每个地区的学生人数和平均年龄，只显示人数1个人以上的
SELECT addr, COUNT(*), AVG(age)
FROM student
GROUP BY addr
HAVING COUNT(*)>1;
-- 统计每门课程60分以上的记录的平均成绩、最高成绩、最低成绩
SELECT cno, AVG(score),MAX(score), MIN(score)
FROM sc
WHERE score>=60
GROUP BY cno;
-- 统计每门课程的最高成绩、考试次数，只显示考试次数多于3的课程
SELECT cno, MAX(score), COUNT(*)
FROM sc
GROUP BY cno
HAVING COUNT(*)>3;
-- 统计有补考的学生学号
-- count(*) 考试次数, count(distinct cno) 选修课程数
SELECT sno
FROM sc
GROUP BY sno
HAVING COUNT(*)>COUNT(DISTINCT cno);
-- 统计没有补考的学生学号
SELECT sno
FROM sc
GROUP BY sno
HAVING COUNT(*)=COUNT(DISTINCT cno);
统计函数
-- group_concat()
-- 语法： group_concat(列名 [order by 列名 separator '符号'] )
-- 作用：把一些字符串归为一个分组。
SELECT sex, AVG(age),COUNT(*), GROUP_CONCAT(sname ORDER BY sname SEPARATOR '-')
FROM student
GROUP BY sex;
-- with rollup
-- 如果group by子句里只有一个数据列，加上with rollup关键字的效果是将在查询结果的最后一行将自动添加一条总数统计记录。
-- 如果按多列分组，将会进行阶段性总和（相当于“小计”），最后再为全体记录统计一个最终的总和（相当于“总计”）。
SELECT sno,cno, AVG(score), COUNT(*)
FROM sc
GROUP BY sno,cno
WITH ROLLUP;
多表连接查询
-- 查看学生信息：学号、姓名、性别、年龄、课程号、成绩
-- 语法1：
SELECT student.sno, sname, sex, age, cno, score
FROM student
INNER JOIN sc ON student.sno=sc.sno;
-- 语法2：
SELECT student.sno, sname, sex, age, cno, score
FROM student,sc
WHERE student.sno=sc.sno;
-- 查看男生信息：学号、姓名、性别、年龄、课程号、成绩
-- 语法1：
SELECT student.sno, sname, sex, age, cno, score
FROM student
INNER JOIN sc ON student.sno=sc.sno
WHERE sex='男';
-- 语法2：
SELECT student.sno, sname, sex, age, cno, score
FROM student,sc
WHERE student.sno=sc.sno AND sex='男';
-- 查看学生信息：学号、课程号、课程名、成绩
SELECT sno, c.cno, cname, score
FROM sc
INNER JOIN course c ON sc.cno=c.cno;
SELECT * FROM sc;
-- 查看学生不及格的信息：学号、课程号、课程名、成绩
SELECT sno, c.cno, cname, score
FROM sc
INNER JOIN course c ON sc.cno=c.cno
WHERE score<60;
三表联合查询
-- 学生的信息：学号、姓名、性别、年龄、课程名、成绩
-- 语法1：
SELECT stu.sno, sname, sex, age, cname, score
FROM stu
INNER JOIN sc ON stu.sno=sc.sno
INNER JOIN course c ON sc.cno=c.cno;
-- 语法2：
SELECT stu.sno, sname, sex, age, cname, score
FROM stu, sc, course c
WHERE stu.sno=sc.sno AND sc.cno=c.cno;
============================================
create table t1(
id1 int,
name1 char(10)
);
create table t2(
id2 int,
name2 char(10)
);
insert into t1
select 11, 'aa' union
select 12, 'bb' union
select 13, 'cc' ;
select * from t1;
select * from t2;
insert into t2
select 11, 'a1' union
select 12, 'a2' union
select 12, 'a3' union
select 14, 'a4' union
select 15, 'a5' ;
内连接
只显示有匹配的记录
SELECT t1.*, t2.*
FROM t1
INNER JOIN t2 ON id1=id2;
外连接
1.左外连接
左表的数据全显示，如果右边没有匹配记录，则显示null
SELECT t1.*, t2.*
FROM t1
LEFT JOIN t2 ON id1=id2;
2.右外连接
右表的数据全显示，如果左边没有匹配记录，则显示null
SELECT t1.*, t2.*
FROM t1
RIGHT JOIN t2 ON id1=id2;
3.完整外连接
两表的数据全显示，如果没有匹配记录，则显示null【不支持】
SELECT t1.*, t2.*
FROM t1
FULL JOIN t2 ON id1=id2;
交叉连接
左表的每一条记录跟右表的每一条记录都匹配一次（笛卡尔积）
SELECT t1.*, t2.*
FROM t1
CROSS JOIN t2 ;
子查询
1. 用关系运算符连接的子查询
-- 注意：要比较的列和子查询select后面的列 数据类型、意义要一致
-- 子查询最多只能返回一行
-- 查询年龄比林黛玉大的学生信息
SELECT *
FROM student
WHERE age>(SELECT age
FROM student
WHERE sname='林黛玉');
-- 查询跟林黛玉同地区的人
SELECT *
FROM student
WHERE addr=(SELECT addr FROM student WHERE sname='林黛玉');
-- 查询林黛玉的所有考试成绩
SELECT *
FROM sc
WHERE sno=(SELECT sno
FROM student
WHERE sname='林黛玉');
2. 用in连接的子查询
--注意：要比较的列和子查询select后面的列 数据类型、意义要一致
-- 子查询可以返回多行
--查询所有男生的考试成绩
SELECT *
FROM sc
WHERE sno IN (SELECT sno
FROM student
WHERE sex='男');
-- any， all
-- 用>, <，>=, <=连接的子查询，如果子查询有多个，可以使用Any或all
-- >all：大于最大的， <all：小于最小的
-- >any： 大于最小的， <any：小于最大的
-- 查询成绩比102的所有成绩都高的信息
-- 方法1
SELECT *
FROM sc
WHERE score>ALL(SELECT score FROM sc WHERE sno=102); -- 68 72
-- 方法2：效率高，推荐使用
SELECT *
FROM sc
WHERE score>(SELECT MAX(score) FROM sc WHERE sno=102);
3. exists 连接的子查询
-- 表示是否存在
-- 如果存在成绩高于80分的，就显示所有的成绩
-- 方法1：
SELECT *
FROM sc
WHERE EXISTS (SELECT * FROM sc WHERE score>80);
-- 方法2：
SELECT *
FROM sc
WHERE (SELECT MAX(score) FROM sc)>80;
-- 如果不存在成绩高于90分的，就显示所有的成绩
SELECT *
FROM sc
WHERE NOT EXISTS (SELECT * FROM sc WHERE score>90);
多列子查询
-- 查询与林黛玉性别和地区都相同的学生
SELECT *
FROM student
WHERE (sex, addr)=(SELECT sex,addr FROM student WHERE sname='林黛玉');
-- 子查询的位置可以在select, from, where
-- 查询学生的信息：学号、姓名、年龄、平均成绩
SELECT sno, sname, age, (SELECT AVG(score) FROM sc WHERE sno=s1.sno) 平均成绩
FROM student s1;
-- 查询成绩高于全班平均成绩的成绩信息
SELECT *
FROM sc
WHERE score>(SELECT AVG(score) FROM sc);
-- 查询成绩高于自己平均成绩的成绩信息
SELECT sc.*
FROM sc
INNER JOIN (SELECT sno, AVG(score) avgscore
FROM sc
GROUP BY sno) s1
ON sc.sno=s1.sno
WHERE score>avgscore;
-- 查询结果的合并
-- union all：取结果的并集， 不去除重复
SELECT sno,sname, sex, age,addr
FROM student
WHERE age>25
UNION ALL
SELECT sno,sname, sex, age,addr
FROM student
WHERE addr='北京';
-- union ：取结果的并集， 去除重复
SELECT sno,sname, sex, age,addr
FROM student
WHERE age>25
UNION
SELECT sno,sname, sex, age,addr
FROM student
WHERE addr='北京';
====================================================
create table stu2
select * from stu;
create table sc2
select * from sc;
select * from stu2;
select * from sc2;
alter table sc2
add sname2 varchar(20);
--多表联合修改
update stu2,sc2 set sname2=sname
where stu2.sno=sc2.sno;
--多表联合删除
delete stu2 from stu2,sc2
where stu2.sno=sc2.sno;
delete sc2 from stu2, sc2
where stu2.sno=sc2.sno and sex='女';
浙公网安备 33010602011771号