test
创表表的增删改数据库约束、表的设计合并查询、子查询、联表查询、聚合查询创表
设计一张图书表，包含以下字段：图书名称，图书作者、图书价格、图书分类create table books(name varchar(20) comment"图书名称",author varchar(10) comment"图书作者",price float comment"图书价格",classify varchar(10) comment"图书分类");
设计一张老师表，包含以下字段：姓名、年龄、身高、体重、性别、学历、生日、身份证号
create table teachers(name varchar(10) comment "姓名",age int(3) comment "年龄",hight float comment "身高",weight float comment "体重",gender varchar(1) comment "性别",education varchar(10) comment "学历",birthday datetime comment "生日",id varchar(20) comment "身份证号");
设计一张商品表，包含以下字段：商品名称、商品价格、商品库存、商品描述
create table commodity(name varchar(20) comment "商品名称",price float comment "商品价格",inventory int comment "商品库存",description text comment "商品描述");
表的增删改
在图书表中新增一条记录：Java核心技术、作者“Cay S. Horstman”，价格56.43，分类为“计算机技术”insert into books values("Java核心技术","Cay S. Horstman",53.43,"计算机技术");
修改“Java核心技术”的图书信息，将价格修改为61
update books set price = 61 where name = "java核心技术";
删除商品表中，价格大于60，或者是库存小于200的记录
delete from commodity where price > 60 or inventory < 200;
修改所有库存大于30的商品记录，将价格增加50块
update commodity set price = price+50 where price > 30;
在以上创建的商品表中插入一条数据：名称为“学生书包”、价格18.91、库存101、描述为空
insert into commodity values ("学生书包",18.91,101,null);
查询用户user表中，满足以下条件的用户数据：
ID在1至200或300至500，且账号accout列不为空
充值金额amount在1000以上。
create table user(id int,account varchar(20),amount int);insert into user values(1,"523",20),(250,"600",2000),(400,null,6000),(420,"900",5321),(500,"150",4444),(200,null,60);select * from user where ((id between 1 and 200) or (id between 300 and 500)) and account is not null and amount > 1000;
查询book图书表中，作者author列不为空，或者满足条件：价格price在50元以上且出版日期publish_date在之后的图书信息
create table book(name varchar(20),author varchar(20),price int,publish_data datetime);insert into book values("计算机基础","阿星",60,".3.4"),("操作系统","阿琦",20,".4.4"),("数据库","阿朵",84,".12.10"),("java","阿明",18,".8.9"),("c语言",null,60,".3.4"),("c语言",null,15,".3.8");select * from book where author is not null and price > 50 and publish_date > "";
查询article文章表中，文章标题title为空，或者满足发表日期create_date在1月1日之后
create table aritcle(title varchar(20),create_date datetime);insert into aritcle values(null,"-03-04"),("计算机","-04-04"),(null,"-12-10"),("数据库","-08-09"),(null,"-03-04");select * from aritcle where title is null and create_date > ".1.1";
查询article文章表中，发表日期create_date在1月1日上午10点30分至11月10日下午4点2分的文章
insert into aritcle values(null,".1.1 10:53:11"),(null,".5,6 15:23:55");select * from aritcle where create_date between ".1.1 10:30" and ".11.10 4:2";
student学生表中，字段有姓名name，年龄age，要求查询姓张，并且年龄在18到25岁之间的学生
create table stu(name varchar(20),age int);insert into stu values("张雷锋",20),("樊亦星",21),("张大炮",50),("琦琦",23),("张晓丽",5);select * from stu where name like "张%" and age between 18 and 25;
数据库约束、表的设计
学校食堂管理系统，包含食堂表，食堂仓口表，仓口收费记录表create batadase school_canteen;use school_canteen;create table canteen(c_id int primary key;c_name varchar(20) unique);create table hatch(h_id int primary key,h_name varchar(20),foreign key (h_id) references canteen(c_id));create table charge(c_id int primary key,c_name varchar，c_price int,foreign key (c_price) references hatch(id));
车辆违章系统，包含用户表，车辆表，违章信息表。违章信息表中包含用户和车辆的违章信息
create database car_violation;use car_violation;drop table if exists user;create table user(u_id varchar(20) primary key comment "身份证号",u_name varchar(20) comment "姓名");drop table if exists car;create table car(c_id varchar(20) primary key comment "车辆编号");create table violation(v_userid varchar(20) comment "车主身份证号",foreign key (v_userid) references user(u_id),v_carid varchar(20) comment "车辆编号",foreign key (v_carid) references car(c_id));
学校宿舍管理系统，要求包含宿舍信息，学生信息，每日的宿舍查房记录。
create database school_dormitory;use school_dormitory;drop table if exists dormitory;create table dormitory(d_id int primary key comment "宿舍号",d_college varchar(20) comment "所属学院");drop table if exists student;create table student(s_id varchar(20) primary key comment "学号",s_name varchar(20) comment "学生姓名",s_dormitory int comment "学生宿舍号",foreign key (s_dormitory) references dormitory(d_id));drop table if exists inspect;create table inspect(i_date datetime comment "日期",i_dor int comment "宿舍号",i_level varchar(5) comment "优良中差",foreign key (i_dor) references dormitory(d_id));
考勤系统，包含员工表，考勤记录表
create database record;use record;drop table if exists staff;create table staff(s_id varchar(20) primary key comment "员工编号",s_name varchar(20) comment "员工姓名");drop table if exists attend;create table attend(a_date datetime comment "考勤日期",a_staffid varchar(20) comment "员工编号",foreign key (a_staffid) references staff(s_id),a_time float(3,1) comment "出勤时间");
合并查询、子查询、联表查询、聚合查询
有一张员工表emp，字段：姓名name，性别sex，部门depart，工资salary。查询以下数据：(1)查询男女员工的平均工资
(2)查询各部门的总薪水
(3)查询总薪水排名第二的部门
(4)查询姓名重复的员工信息
(5)查询各部门薪水大于10000的男性员工的平均薪水
有一张员工表emp，字段：姓名name，性别sex，部门depart，工资salaryuse test;drop table if exists emp;create table emp(name varchar(10) comment "姓名",sex varchar(1) comment "性别",depart varchar(20) comment "部门",salary int comment "工资");insert into emp values("阿星","男","计算机",56),("阿奇","女","计算机",66),("阿甜","女","网络",49),("阿纳","男","能源",89),("阿赫","男","会计",46),("阿辰","女","能源",55),("阿彪","男","网络",66),("阿妹","女","计算机",99),("阿星","男","能源",78);1、查询男女员工的平均工资select sex,avg(salary) from emp group by sex;2、查询各部门的总薪水select depart,sum(salary) from emp group by depart;3、查询总薪水排名第二的部门select depart,sum(salary) from emp group by depart order by sum(salary) desc limit 1,1;4、查询姓名重复的员工信息select * from emp where name in(select name from emp group by name having count(name) > 1);5、查询各部门薪水大于55的男性员工的平均薪水select depart,avg(salary) from emp where salary > 55 and sex = "男" group by depart ;
现在有员工表、部门表和薪资表。部门表depart的字段有depart_id， name；员工表 staff 的字段有 staff_id， name， age， depart_id；薪资表salary 的字段有 salary_id，staff_id，salary，month。
（问题a）：求每个部门’-09’月份的部门薪水总额
（问题b）：求每个部门的部门人数，要求输出部门名称和人数
（问题c）：求公司每个部门的月支出薪资数，要求输出月份和本月薪资总数
现在有员工表、部门表和薪资表。部门表depart的字段有depart_id， name；员工表 staff 的字段有 staff_id， name， age， depart_id；薪资表salary 的字段有 salary_id，staff_id，salary，month。create database stafftest;use stafftest;drop table if exists depart;create table depart(depart_id int primary key,name varchar(20));insert into depart values(1,"计算机"),(2,"电控"),(3,"会计"),(4,"能源");drop table if exists staff;create table staff(staff_id int primary key,name varchar(20),age int,depart_id int,foreign key (depart_id) references depart(depart_id));insert into staff values(1,"阿星",18,1),(2,"阿明",44,1),(3,"阿珍",5,1),(4,"阿甜",46,2),(5,"阿奶",75,2),(6,"阿彪",26,3),(7,"阿忠",21,3),(8,"阿赫",33,4),(9,"阿阳",22,4);drop table if exists salary;create table salary(salary_id int,staff_id int,foreign key (staff_id) references staff(staff_id),salary int,month datetime);insert into salary values(1,1,56,".9.1"),(2,2,88,".9.1"),(3,3,16,".9.1"),(4,4,78,".9.1"),(5,5,44,".9.1"),(6,6,48,".9.1"),(7,7,75,".9.1"),(8,8,68,".9.1"),(9,9,79,".9.1");（问题a）：求每个部门'-09'月份的部门薪水总额select depart.name,sum(salary.salary)from staff,salary,departwhere staff.staff_id = salary.staff_id and depart.depart_id = staff.depart_idgroup by staff.depart_id;（问题b）：求每个部门的部门人数，要求输出部门名称和人数select depart.name,count(staff_id)from depart,staffwhere depart.depart_id = staff.depart_idgroup by depart.name;（问题c）：求公司每个部门的月支出薪资数，要求输出月份和本月薪资总数select month,sum(salary)from salary,staffwhere salary.staff_id = staff.staff_idgroup by depart_id;
有两个表分别如下：
表A(varchar(32) name， int grade)
数据：zhangshan 80， lisi 60， wangwu 84
表B(varchar(32) name， int age)
数据：zhangshan 26， lisi 24， wangwu 26， wutian 26
写SQL语句得到如下查询结果：
drop table if exists a;create table a(name varchar(32),grade int);insert into a values("zhangshan",80),("lisi",60),("wangwu",84);drop table if exists b;create table b(name varchar(32),age int);insert into b values("zhangshan",26),("lisi",24),("wangwu",26),("wutian",26);select a.name,grade,agefrom a right join bon a.name = b.name;
编写一个SQL查询，获取Employee表中第二高的薪水（Salary）
±----±------+
| Id | Salary|
±----±------+
| 1 | 100 |
±----±------+
| 2 | 200 |
±----±------+
| 3 | 300 |
±----±------+
例如上述Employee表，SQL查询应该返回200作为第二高的薪水。如果不存在第二高的薪水，那么查询应该返回null。
±--------------------+
| SecondHighestSalary |
±--------------------+
| 200 |
±--------------------+
drop table if exists salary;create table salary(id int,salary int);insert into salary values(1,100),(2,200),(3,300);select salary as SecondHighestSalaryfrom salaryorder by salary desclimit 1,1;
使用UNION语句查询
已知T1和T2的字段定义完全相同，T1有5条不同数据，T2有5条不同数据，其中T1有2条数据存在表T2中，使用UNION语句查询这些数据，要求重复数据不出现
select * from t1 union select * from t2;
