【黑马程序员】MySQL学习记录二（SQL）案例

案例1（数据类型、DDL、DML、DQL）

根据需求创建表（设计合理的数据类型、长度）

1、设计一张员工信息表，要求如下

 create table emp(

        id INT COMMENT '编号',

        workno VARCHAR(10) COMMENT '员工工号',

        name VARCHAR(10) COMMENT '员工姓名',

        gender CHAR(1) COMMENT '性别',

        age TINYINT UNSIGNED COMMENT '年龄',

        idcard CHAR(18) COMMENT '身份证号',

        entrydate DATE COMMENT '入职时间'

) COMMENT '员工表';

 2、为emp表增加一个新的字段“昵称”为nickname，类型为VARCHAR(20)

alter table emp add nickname VARCHAR(20) COMMENT '昵称';

 3、将emp表的nickname字段修改为"用户名"username，类型为varchar(30)

alter table emp change nickname username VARCHAR(30) COMMENT '用户名';

 4、将emp表的字段username删除

alter table emp drop username;

 5、将emp表的表名修改为employee

alter table emp rename to employee;

 

6、为employee表插入一条数据：id为1，workno为1，name为test，gender为男，age为10，idcard为123456789012345678，entrydate为2000-01-01

insert into employee (id, workno, name, gender, age, idcard, entrydate) values (1, '1', 'test', '男', 10, '123456789012345678', '2000-01-01');

7、为employee表批量插入三条数据，数值随便

insert into employee values 
(2, '2', 'test1', '男', 11, '123456789012345679', '2001-01-01'),
(3, '3', 'test2', '女', 12, '123456789012345670', '2002-01-01'),
(4, '4', 'test3', '女', 13, '123456789012345671', '2003-01-01');

8、修改id为1的数据：将name修改为testname，gender修改为女

update employee set name = 'testname',gender = '女' where id = 1;

9、将所有员工入职日期修改为2008-01-01

update employee set entrydate = '2008-01-01';

 10、删除gender为女的员工

delete from employee where gender = '女';

11、为employee表批量插入三条数据，数值随便。查询指定字段name，workno，age并返回。

insert into employee values

(3, '3', 'test2', '男', 11, '123456789012345679', '2001-01-01'),
(4, '4', 'test3', '女', 12, '123456789012345670', '2002-01-01'),
(5, '5', 'test4', '男', 11, '123456789012345679', '2001-01-01'),
(6, '6', 'test5', '女', 12, '123456789012345670', '2002-01-01'),
(7, '7', 'test6', '女', 13, '123456789012345671', '2003-01-01');

select name,workno,age from employee; 

12、查询所有员工的idcard，起别名为'身份证'

select idcard as '身份证' from employee;

13、根据性别分组，统计男性员工和女性员工的数量

select gender as '性别' ,count(*) as '数量' from employee group by gender;

 案例2（DQL）

按照需求完成如下DQL语句编写

select * from emp where gender = '女' and age in(20,21,22,23); 

select * from emp where gender = '男' and (age between 20 and 40) and name like '___'; 

select count(*) from  emp where age < 60 group by gender;

select name,age from emp where age <= 35 order by age asc, entrydate desc;

select  * from emp where gender = '男' and age between 20 and 40 order by age asc, entrydate asc limit 5;