基本sql语句练习（学生表）

深碍√TFBOYSˉ_ 2021-11-16 14:12 627阅读 0赞

### 基本sql语句练习 ###

#### 1.学生表的基本操作 ####

老老实实把下面的打完，相信我。  
![在这里插入图片描述][201907281553555.jpg]

-- 创建表
    CREATE TABLE t_stu(
    	-- 列名 类型 主键 自动增长 注释
    	stu_id BIGINT PRIMARY KEY auto_increment COMMENT '学生id',
    	-- 不允许为空约束
      stu_name VARCHAR(50) not null DEFAULT '马云' COMMENT '学生姓名',
    	-- 学号 唯一性约束 不能为空
    	stu_no VARCHAR(30) UNIQUE not null COMMENT '学号',
    	-- 年龄
    	stu_age int COMMENT '年龄',
    	-- 生日
    	stu_birth TIMESTAMP DEFAULT NOW() COMMENT '生日'
    )ENGINE=INNODB auto_increment=1000 charset=utf8 COMMENT '学生表';
    
    -- 插入数据 有默认值得不插入
    INSERT into t_stu(stu_no,stu_age) VALUES('20190101',25);
    -- 插入生日
    INSERT into t_stu(stu_no,stu_age,stu_birth) VALUES('20190102',25,'2000-10-10');
    -- 查询
    select * from t_stu;
    -- 插入多条数据
    INSERT into t_stu(stu_name,stu_no,stu_age) 
    						values('x1','20190108',25),('x3','20190104',26),('x6','20190107',20);
    
    -- 如果每列都插入值，可以省略列名的编写
    INSERT into t_stu value(2000,'x8','20190105',25,'2000-11-11');
    
    INSERT into t_stu(stu_no,stu_age) VALUES('20190110',30);
    
    
    -- 字符串作为主键
    CREATE table stu2(
    	stu_no VARCHAR(30) not null,
    	stu_name varchar(30) not null,
    	-- 设置主键
    	PRIMARY key(stu_no)
    );
    
    INSERT into stu2 values('1','a'),('2','b');
    SELECT stu_no,stu_name from stu2;
    -- 联合主键
    CREATE table stu3(
    	stu_no VARCHAR(30) not null,
    	stu_name varchar(30) not null,
    	-- 设置主键
    	PRIMARY key(stu_no,stu_name)
    );
    
    
    -- 主外键
    -- 创建课程表
    CREATE table course(
    	course_id int auto_increment PRIMARY key,
    	course_name VARCHAR(30) not null UNIQUE,
    	-- 记录的时间
    	update_time TIMESTAMP DEFAULT NOW()
    );
    
    -- 实现选课 学生和课程之间是一个多对多得关系
    -- 创建关系表
     CREATE table  stu_course(
    	stu_id BIGINT,
    	cour_id int,
    	-- 主键
    	PRIMARY key(stu_id,cour_id),
    	-- 外键
    	CONSTRAINT FOREIGN key(stu_id) REFERENCES t_stu(stu_id),
    	constraint foreign key(cour_id) REFERENCES  course(course_id)
    ); 
    
    -- 查询学生表的信息
    select * from t_stu;
    -- 查询课程表
    select * from course;
    INSERT into course(course_id,course_name) 
    	values(200,'java'),(300,'html'),(400,'js')
    -- x1 选择类 java 和js课程 x3 选择了html 和js的课程
    insert INTO stu_course(stu_id,cour_id)
    	values(1002,200),(1002,400),(1003,300),(1003,400)
    select * from stu_course
    -- 获取完整的信息需要3张出现在一个查询中
    select * from t_stu,stu_course,course WHERE t_stu.stu_id = stu_course.stu_id AND
    stu_course.cour_id = course.course_id
    
    -- 查询x1选择的所有课程的名称
    select stu_name,course_name from t_stu,stu_course,course WHERE t_stu.stu_id = stu_course.stu_id AND
    stu_course.cour_id = course.course_id and t_stu.stu_name = 'x1'
    
    -- 使用子查询的方式
    -- 获取 x1 的id
    select stu_id from t_stu where stu_name = 'x1'
    -- 根据x1的id查询课程的id
    select cour_id from stu_course where stu_id = 
    	(select stu_id from t_stu where stu_name = 'x1')
    -- 利用上面课程的id查询课程名称
    select course_name from course where course_id in (
    	select cour_id from stu_course where stu_id = 
    	(select stu_id from t_stu where stu_name = 'x1')
    )
    
    -- 使用连接查询,stu_course sc ,course c
    select s.*,sc.*,c.* from t_stu s
    					INNER JOIN stu_course sc ON s.stu_id = sc.stu_id
    					INNER JOIN course c on sc.cour_id =  c.course_id
    					where s.stu_name = 'x1';
    
    -- 别名查询
    SELECT s.stu_id id, s.stu_name as name,s.stu_no  as '学号' from t_stu s;
    -- 连接查询
    SELECT * from t_stu;
    select * from stu_course;
    -- 使用学生表左连接课程和学生的关系表 查询的数据以关键字left join 左边的表为准
    -- 如果没有数据与之关联，那么现实的null
    SELECT * from t_stu s LEFT JOIN stu_course sc ON s.stu_id = sc.stu_id
    
    SELECT * from stu_course sc LEFT JOIN t_stu s ON s.stu_id = sc.stu_id
    
    
    -- 右连接
    SELECT * from t_stu s right JOIN stu_course sc ON s.stu_id = sc.stu_id
    
    SELECT * from stu_course sc right JOIN t_stu s ON s.stu_id = sc.stu_id
    
    
    -- 修改语句
    select * from t_stu;
    -- 将年龄大于20并且小于26的人的年龄修改成25
    update t_stu set stu_age = 25 where stu_age >= 20 and stu_age < 26
    -- 删除 
    DELETE from t_stu where stu_no = '20190110';
    
    select * from t_stu where 1=1  ;
    
    -- 统计记录条数
    select count(*) nums from t_stu where 1 = 1;
    -- 可以使用具体的列名
    select count(stu_id) nums from t_stu where 1 = 1;
    
    select count(stu_age) nums from t_stu where 1 = 1;
    
    -- 其他的聚合函数
    select count(*),MIN(stu_age),MAX(stu_age),SUM(stu_age),AVG(stu_age) from 
    t_stu where 1 = 1;
    -- 一旦使用了聚合函数，那么要么使用group by，不然是不能查询单列
    select stu_id,count(*),MIN(stu_age),MAX(stu_age),SUM(stu_age),AVG(stu_age) from 
    t_stu where 1 = 1;
    
    -- 分组
    select * from t_stu;
    select stu_id,count(*),MIN(stu_age),MAX(stu_age),SUM(stu_age),AVG(stu_age) from 
    t_stu where 1 = 1 GROUP BY stu_age;
    
    -- 分组之后过滤 id 需要大于1001才做分组 分组之前数据
    select * from t_stu;
    select stu_id,count(*),MIN(stu_age),MAX(stu_age),SUM(stu_age),AVG(stu_age) from 
    t_stu where 1 = 1 and stu_id > 1001 GROUP BY stu_age;
    
    -- 如果使用的having那么过滤的是分组之后的数据
    select * from t_stu;
    select stu_id,count(*),MIN(stu_age),MAX(stu_age),SUM(stu_age),AVG(stu_age) from 
    t_stu where 1 = 1  GROUP BY stu_age HAVING stu_id > 1001;
    
    
    select * from t_stu;
    select stu_id,count(*),MIN(stu_age),MAX(stu_age),SUM(stu_age),AVG(stu_age) from 
    t_stu where 1 = 1  GROUP BY stu_age HAVING count(*) > 1;
    
    
    -- 分组和排序
    select * from t_stu;
    select stu_id,count(*) nums,MIN(stu_age),MAX(stu_age),SUM(stu_age),AVG(stu_age) from 
    t_stu where 1 = 1 and stu_id > 1001 GROUP BY stu_age ORDER BY  nums ASC;
    
    
    -- select * from table where 条件 group by having ORDER BY asc/desc
    
    -- 查询选择了所有的课程的学生的姓名
    SELECT * from course;
    
    select * from stu_course;
    select * from t_stu;
    
    -- 查询总的课程数目
    select count(*) nums from course
    -- 按照学生的id分组统计课程和学生的关系表
    select stu_id ,count(*) from stu_course GROUP BY stu_id
    -- 过滤想要的id
    select stu_id ,count(*) from stu_course GROUP BY stu_id HAVING count(*) = (
    	select count(*) nums from course
    )
    -- 根据学生表查询学生的姓名
    select stu_name from t_stu where stu_id in (
    	select stu_id  from stu_course GROUP BY stu_id HAVING count(*) = (
    	select count(*) nums from course
    )
    )
    
    -- 连接查询
    select s.stu_id,s.stu_name,count(*) from t_stu s 
    			INNER JOIN  stu_course sc on s.stu_id = sc.stu_id
    			INNER JOIN course c on sc.cour_id = c.course_id
    			GROUP BY s.stu_id,s.stu_name
    			HAVING count(*) = (select count(*) nums from course)
    
    select *,CONCAT(stu_name,'-',stu_no) from  t_stu;
    update t_stu set stu_name=CONCAT(stu_name,"123") where stu_id=1001
    update t_stu set stu_name=CONCAT(stu_name,stu_no) where stu_id=1001
    
    -- 插入日期可以是符合日期格式的字符串，也可以使用系统函数获取当前的日期
    SELECT * from t_stu;
    -- 日期
    INSERT into t_stu values(2002,'x5','20190705',30,'2019-07-05'),(2003,'x7','20190706',22,CURRENT_DATE());
    -- 日期和时间 
    INSERT into t_stu values (2004,'x9','20190707',24,CURRENT_TIMESTAMP());
    -- 只有时间
    select CURRENT_TIME();
    
    -- 日期的抽取
    SELECT stu_birth, YEAR(stu_birth),month(stu_birth),day(stu_birth) from t_stu;
    
    -- 日期的增加和减少 
    select stu_birth,DATE_SUB(stu_birth,INTERVAL 30 day) from t_stu
    select stu_birth,DATE_add(stu_birth,INTERVAL 5 day) from t_stu
    select stu_birth,DATE_add(stu_birth,INTERVAL 5 HOUR) from t_stu
    
    -- 日期相差
    select stu_birth,CURRENT_DATE(),DATEDIFF(CURRENT_DATE(),stu_birth) from t_stu
    -- 有多少岁 当前年 - 出生年
    select stu_birth,CURRENT_DATE(),YEAR(stu_birth),year(CURRENT_DATE()),(year(CURRENT_DATE())-YEAR(stu_birth)) b from t_stu
    
    -- 日期中某部分的抽取
    select stu_birth,EXTRACT(YEAR FROM stu_birth),EXTRACT(month FROM stu_birth),EXTRACT(day FROM stu_birth),
     EXTRACT(HOUR FROM stu_birth),EXTRACT(MINUTE FROM stu_birth),EXTRACT(SECOND FROM stu_birth) from t_stu;
    
    
    -- 数字的处理
    select 3*5 a;
    -- ROUND(X,D) 函数 CEIL(X)函数 FLOOR(X)
    select FLOOR(3.65),FLOOR(3.64),CEIL(3.65),CEIL(3.64),ROUND(3.654,2),ROUND(3.656,2)
    select * from t_stu
    -- 截取数字，不做四舍五入
    select TRUNCATE(3.656,2) ,TRUNCATE(3.654,2)
    
    show TABLES
    select * from stu2;
    select * from stu3;
    -- 删除数据
    delete from stu2;-- 000ms
    TRUNCATE table stu2;-- 时间: 0.040ms
    
    -- 创建一张表并且复制数据
    CREATE table copy_stu3 as select * from stu3;
    select * from copy_stu3
    CREATE table copy_stu31 as select stu_no from stu3;
    select * from copy_stu31
    
    -- 如果只是copy结构不要数据
    CREATE table copy_stu32 as select * from stu3 where 1=2;
    select * from copy_stu32;
    select * from copy_stu31;
    
    -- 查询表结构
    desc copy_stu32
    
    select * from copy_stu3;
    select * from copy_stu32;
    
    update copy_stu3 set stu_name = null where stu_no=2
    -- 将copy_stu3的数据插入到copy_stu32 如果数据是null 这用马云替换
    INSERT into copy_stu32(stu_no,stu_name) SELECT stu_no,IFNULL(stu_name,'马云') from copy_stu3
    
    
    -- 查询的时候null的处理
    select * from copy_stu3 where stu_name = null;
    select * from copy_stu3 where stu_name  is null;
    
    select * from t_stu;
    
    select *,(
    	CASE
    		WHEN stu_age >=20  THEN '成年人'
    		WHEN stu_age >=10 and  stu_age < 20 THEN '未成年'
    		ELSE '小孩'
    	END
    )type from t_stu;
    
    
    -- 分页
    
    select * from t_stu;
    select count(*) from t_stu;
    -- 放入同一列
    select stu_id from t_stu UNION select count(*) from t_stu;
    -- 将2张毫无关系的表数据放在一张表中 增加不同的列 
    select stu_id,(select count(*) from t_stu) nums from t_stu  ;
    
    select 5*3
    -- LIMIT
    select * from t_stu;
    -- 第一次查询 n = 1表示 0 = (n -1) * 3
    select * from t_stu LIMIT 0,3; -- 0表示第一条数据 3表示 查询记录条数
    
    -- 第二次任然查询3条记录 n = 2
    select * from t_stu LIMIT 3,3; -- 第一个3 如何计算
    
    -- 第三次查询任然查询3条记录 n = 3 6 = (3 -1) * 3
    select * from t_stu LIMIT 6,3;-- 6是如何计算
    
    
    
    -- 每次查询4条记录 ，获取 第二次查询的记录 (2 -1)* 4
    select * from t_stu LIMIT 4,4;
    
    -- 如果是网页 1 2 3 4 页码等同于 第几次查询
    -- 每次查询2条，如和计算要查询多少次可以把记录查询完毕
    -- 总的记录数是9条
    -- 
    select count(*) from t_stu;
    select count(*),CEIL(count(*)/2) from t_stu;
    -- 如果在程序中计算 (9 + 2 -1 ) / 2 = 取整数 （总的记录条数 + 每次查询的记录数 - 1）/ 每次查询的记录数
    
    
    -- 每次查询3条 获取第二次查询的数据 limit 3,3
    select s.stu_id,s.stu_name,count(*) from t_stu s 
    			INNER JOIN  stu_course sc on s.stu_id = sc.stu_id
    			INNER JOIN course c on sc.cour_id = c.course_id
    			where s.stu_id > 1000 
    			GROUP BY s.stu_id,s.stu_name
    			HAVING count(*) >=1
    			limit 3,3
    
    SELECT t.stu_id,t.stu_name from
    (select s.stu_id,s.stu_name from t_stu s 
    			INNER JOIN  stu_course sc on s.stu_id = sc.stu_id
    			INNER JOIN course c on sc.cour_id = c.course_id
    			where s.stu_id > 1000  limit 3,3) as t
    
    			GROUP BY t.stu_id,t.stu_name
    			HAVING count(*) >=1

#### 2.创表多对多关系，sql的基本操作、分组统计、聚合 ####

drop table student; -- 删除表
    -- 学生表
    create table student(
    	stu_id int auto_increment PRIMARY key,
    	stu_name VARCHAR(25) COMMENT '学生姓名',
    	stu_no VARCHAR(25),
    	stu_birth TIMESTAMP
    );
    -- 插入数据
    insert into student(stu_name,stu_no,stu_birth) values('lisi','2222',NOW());
    insert into student(stu_name,stu_no,stu_birth) values('zhangsan','3333',NOW());
    insert into student(stu_name,stu_no,stu_birth) values('wangwu','5555','2015-06-07');
    insert into student(stu_name,stu_no,stu_birth) values('zhaoliu','6666','2015-06-07');
    select * from student;
    
    -- 创建课程表
    drop table if EXISTS course; -- 如果存在就删除课程表
    create table course(
    	cur_id int PRIMARY key,
    	cur_name VARCHAR(25),
    	create_time TIMESTAMP DEFAULT NOW() COMMENT '数据插入的时间'
    )
    -- 插入测试数据
    insert into course(cur_id,cur_name) values (100,'java');
    insert into course(cur_id,cur_name) values (200,'html');
    insert into course(cur_id,cur_name) values (300,'javascript');
    insert into course(cur_id,cur_name) values (400,'mysql');
    insert into course(cur_id,cur_name) values (500,'oracle');
    select * from course;
    
    -- 创建关系表
    create table stu_course(
    	stu_id int,
    	cur_id int,
    	PRIMARY key(stu_id,cur_id), -- 联合组件
    	CONSTRAINT FOREIGN key(stu_id) REFERENCES student(stu_id),
    	CONSTRAINT FOREIGN key(cur_id) REFERENCES course(cur_id)
    );
    
    -- 插入测试数据
    INSERT into stu_course(stu_id,cur_id) values(1,100);
    INSERT into stu_course(stu_id,cur_id) values(1,200); -- lisi选择了2门课
    INSERT into stu_course(stu_id,cur_id) values(2,100); -- 张三选择了1门课
    INSERT into stu_course(stu_id,cur_id) values(3,100);
    INSERT into stu_course(stu_id,cur_id) values(3,200);
    INSERT into stu_course(stu_id,cur_id) values(3,300);
    INSERT into stu_course(stu_id,cur_id) values(3,400); -- 王五选择了4门课
    
    select * from stu_course;
    
    -- 查询lisi选择的所有课程的名称
    
    
    -- 李四的id
    select s.stu_id from student s where s.stu_name = 'lisi';
    -- 课程的id
    select sc.cur_id from stu_course sc ;
    -- 李四所选的课程的id
    select sc.cur_id from stu_course sc where sc.stu_id = (select s.stu_id from student s where s.stu_name = 'lisi');
    -- 查询课程的名称
    select c.cur_name from course c where c.cur_id in (
    	select sc.cur_id from stu_course sc where sc.stu_id = (select s.stu_id from student s where s.stu_name = 'lisi')
    );
    -- 连接查询
    select s.*,sc.*,c.* from student s
    	INNER JOIN stu_course sc on s.stu_id = sc.stu_id
    	INNER join course c on sc.cur_id = c.cur_id
    where s.stu_name = 'lisi'
    
    
    select s.*,sc.* from student s
    	inner JOIN stu_course sc on s.stu_id = sc.stu_id
    -- 左连接
    select s.*,sc.* from student s
    	left JOIN stu_course sc on s.stu_id = sc.stu_id
    
    -- 右连接
    select c.*,sc.* from stu_course  sc
    	right JOIN course c on c.cur_id = sc.cur_id
    
    
    -- 在student表中那个添加age列
    select * from student;
    ALTER table student add COLUMN age int;
    
    -- 聚合函数 统计记录条数 求平均值 求最大值 求最小值
    -- 统计年龄小于25学生有多少个 一般情况下聚合函数不能和单独的某列一起查询
    select count(*) '小于25的学生个数' from student where age <= 25;
    -- 聚合函数 统计中记录数 平均值 最大值 最小值 总和
    select count(*),avg(age),max(age),min(age),sum(age) from student;
    -- 排序 升序
    select * from student WHERE stu_id >= 1 ORDER BY age asc;
    select * from student WHERE stu_id >= 1 ORDER BY age desc; -- 降序
    -- 排序可以多个字段做排序
    select * from student WHERE stu_id >= 1 ORDER BY age asc,stu_name desc;
    
    -- 分组
    SELECT count(*), age from student GROUP BY age;
    -- 分组之后过滤
    SELECT count(*), age from student GROUP BY age HAVING age >18;
    
    SELECT count(*), age from student GROUP BY age HAVING max(age) > 25;
    -- 也可以放入where条件
    SELECT count(*), age from student where stu_id >= 2 GROUP BY age HAVING age >=18 ;
    -- 还可以排序
    SELECT count(*), age from student where stu_id >= 2 GROUP BY age HAVING age >=18 ORDER BY age desc;
    
    
    -- 查询选择了所有的课程的学生的姓名和学号
    
    -- 总共有多少门课程
    SELECT count(*) from course;
    -- 从关系表查询每个学生到底选择了多少门课
    select * from stu_course;
    select stu_id,count(*) from stu_course GROUP BY stu_id;
    -- 获取选择了所有的课程的人的id
    select stu_id,count(*) from stu_course GROUP BY stu_id HAVING count(*) = (SELECT count(*) from course);
    
    -- 显示选择了所有课程的学生的姓名
    select * from student where stu_id in(
    select stu_id from stu_course GROUP BY stu_id HAVING count(*) = (SELECT count(*) from course)
    );
    
    select s.*,t.* from student s INNER JOIN 
    	( select stu_id,count(*) from stu_course GROUP BY stu_id HAVING count(*) = (SELECT count(*) from course) ) t
    	on s.stu_id = t.stu_id;

[201907281553555.jpg]: /images/20211116/4ae397c2a77c4e01913f56ed7daf05b8.png