2021-09-06

#创建db_test数据库
create DATABASE db_test CHARACTER set utf8;

use db_test;
#在test数据库中创建teacher表
create table teacher(
    number int PRIMARY key auto_increment,
    tname VARCHAR(30),
    sex VARCHAR(4),
    depno int,
    salary float,
    address VARCHAR(100)
)
INSERT into teacher values
    (201,'刘芸','女',604,4500,'河北省沧州'),
    (202,'周星驰','男',601,1600,'山东省济南'),
    (203,'秦牧之','男',604,2800,'北京市昌平'),
    (204,'周润发','男',602,3500,'河北省沧州'),
    (205,'林双','女',605,1200,'河北省保定'),
    (206,'张帆','女',602,3000,'北京市昌平'),
    (207,'陈友谅','男',604,2800,'山西省大同'),
    (208,'张国荣','男',601,2500,'山东省烟台');
    
#1.    向表中添加记录，字段对应值分别为（209，毛不易，男，604，4000，黑龙江齐齐哈尔）
INSERT into teacher values(209,'毛不易','男',604,4000,'黑龙江齐齐哈尔');

#2.    查询表中所有记录
SELECT * from teacher;

#3.    删除表中Number是201并且sex是女的教师信息
DELETE from teacher where number=201 and sex='女';

#4.    查询表中Number字段的值是202，205或207教师的所有记录
SELECT * from teacher where number in(202,205,207);

#5.    修改表中Number值是202教师的姓名为“马云”
update teacher set tname='马云' where number=202;

#6.    查询成绩最高的前三条信息
select * from teacher ORDER BY salary desc limit 3;

#7.    查询姓名中包含张的信息
select * from teacher where tname like'%张%';

#8.    查询男女生的个数信息
select count(*),sex from teacher GROUP BY sex;

#9.    查询薪资在1800到3600之间的信息(使用多种方式实现)
select * from teacher where salary>1800 and salary<3600;

select * from teacher where salary between 1800 and 3600;