基本sql语句练习（学生表）-蒲公英云

基本sql语句练习

1.学生表的基本操作

老老实实把下面的打完，相信我。
在这里插入图片描述

-- 创建表
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;