编辑代码

CREATE DATABASE school;
use school;
-- CREATE TABLE students (
--     id INT PRIMARY KEY comment '编号',
--     name VARCHAR(50) comment '姓名',
--     age INT comment '年龄',
--     gender VARCHAR(1) comment '性别'
-- );

-- INSERT INTO students(id,name,age,gender) 
-- VALUES (1,'小明',20,'男'),
--         (2,'小红',54,'女'),
--         (3,'小刚',23,'男');

-- alter table students add score INT;

-- alter table students change gerden gender varchar(1);

-- UPDATE students
-- SET score = 98
-- WHERE id = 1;

-- UPDATE students
-- SET score = 45
-- WHERE id = 2;

-- UPDATE students
-- SET score = 67
-- WHERE id = 3;

-- INSERT INTO students(id,name,age,gender,score) 
-- VALUES (4,'小赵',50,'男',78),
--         (5,'小光',24,'女',99);

-- UPDATE students SET name='小青', gender='女' WHERE id=4;

-- ALTER TABLE students add enterSchool DATE;
-- ALTER TABLE students add class varchar(10);

-- UPDATE students SET enterSchool = '2023-09-01';
-- UPDATE students SET class = '1班' WHERE id = 1;
-- UPDATE students SET class = '2班' WHERE id = 2;
-- UPDATE students SET class = '2班' WHERE id = 3;
-- UPDATE students SET class = '3班' WHERE id = 4;
-- UPDATE students SET class = '4班' WHERE id = 5;
-- select class AS '班级' from students;

create table score(
    id int comment 'ID',
    name varchar(20) comment '姓名',
    math int comment '数学',
    english int comment '英语',
    chinese int comment '语文'
);

insert into score(id,name,math,english,chinese)
VALUES (1,'小明',89,43,54),
(2,'小刚',54,66,87),
(3,'小红',89,33,98);

select name,(case when math>=80 then '优秀' when math>=60 then '及格' else '不及格' end) '数学',
(case when english>=80 then '优秀' when english>=60 then '及格' else '不及格' end) '英语',
(case when chinese>=80 then '优秀' when chinese>=60 then '及格' else '不及格' end) '语文'
from score;



-- CREATE DATABASE COMPUMENT;
-- use COMPUMENT;
-- CREATE TABLE emp(
--     id INT PRIMARY KEY COMMENT '编号',
--     workno VARCHAR(10) COMMENT '工号',
--     name VARCHAR(10) COMMENT '姓名',
--     gender CHAR(10) COMMENT '性别',
--     age tinyint unsigned COMMENT '年龄',
--     idcard CHAR(18) COMMENT '身份证号',
--     workaddress VARCHAR(50) COMMENT '工作地址',
--     entrydate DATE COMMENT '入职时间'
-- );

-- INSERT INTO emp (id,workno,name,gender,age,idcard,workaddress,entrydate)
-- VALUES (1,'1','柳岩','女',20,'123456789782738471','北京','2000-01-01'),
-- (2,'2','张无忌','男',45,'24234345362342312X','上海','2000-04-01'),
-- (3,'3','韦一名','男',40,'4673646736735','北京','2005-06-30'),
-- (4,'4','赵敏','女',25,'73365734323422','上海','2020-12-11'),
-- (5,'5','小昭','女',77,'34525666573673','上海','2023-03-21'),
-- (6,'6','逍遥','男',56,'4525633653345','北京','2012-04-04'),
-- (7,'7','范增','男',23,'3663673677367','北京','2013-06-05'),
-- (8,'8','黛绮丝','女',82,'87476836368463','上海','2012-08-21'),
-- (9,'9','范冰冰','女',13,'73735367337784566','上海','2003-02-12'),
-- (10,'10','陈友谅','男',53,'12312132145123414','上海','2005-09-03'),
-- (11,'11','张士诚','男',23,'3673767363X','北京','2006-10-06'),
-- (12,'12','常遇春','男',64,'7573667336573','北京','2007-11-12'),
-- (13,'13','张三丰','男',188,'3636567368','上海','2010-05-11'),
-- (14,'14','灭绝师太','女',77,'567783463686563','西安','2022-06-23'),
-- (15,'15','胡青牛','男',35,'3464683667837','上海','2021-07-26'),
-- (16,'16','周芷若','女',18,null,'上海','2015-02-08');


-- 1、查询指定字段name，workno，age返回
-- select name,workno,age from emp;

-- 2、查询所有字段返回
-- select id,workno,name,gender,age,idcard,workaddress,entrydate from emp;

-- 3、查询所有员工的工作地址，起别名
-- select workaddress AS '工作地址' from emp;

-- 4、查询所有员工的工作地址，起别名(不重复)
-- select distinct workaddress AS '工作地址' from emp;

-- 条件查询
-- 1、查询年龄等于82的员工
-- select * from emp
-- where age = 82;

-- 2、查询年龄小于20的员工信息
-- select * from emp
-- where age<20;

-- 3、查询年龄小于等于20的员工信息
-- select * from emp
-- where age<=20;

-- 4、查询没有身份证号的员工
-- select * from emp
-- where idcard is null;

-- 5、查询有身份证号的员工
-- select * from emp
-- where idcard is not null;

-- 6、查询查询年龄不等于82的员工
-- select * from emp
-- where age!=82;

-- 7、查询年龄在15岁(包含)到20岁(包含)之间的员工
-- select * from emp
-- where age>=15 and age <=20

-- select * from emp
-- where age between 15 and 20;

-- 8、查询性别为女且年龄小于25岁的员工
-- select * from emp
-- where gender='女'and age<25


-- 9、查询年龄等于18或20或40的；员工
-- select * from emp
-- where age=18 or age=20 or age=40;

-- select * from emp
-- where age in(18,20,40);

-- 10、查询姓名为两个字的员工信息
-- select * from emp
-- where name like '__';

-- 11、查询身份证号最后一位是X的员工信息
-- select * from emp
-- where idcard like '%X';

-- 聚合函数
-- 1、统计该企业员工的数量
-- select count(id) from emp;

-- 2、统计该企业员工的平均年龄
-- select avg(age) from emp;

-- 3、统计该企业员工的最大年龄
-- select max(age) from emp;

-- 4、统计该企业员工的最小年龄
-- select min(age) from emp;

-- 5、统计北京地区员工的年龄之和
-- select * from emp
-- where workaddress='北京';
-- select sum(age) from emp
-- where workaddress='北京';


-- 分组查询
-- 1、根据性别分组，统计男员工和女员工的数量
-- select gender,count(*) from emp
-- group by gender;

-- 2、根据性别分组，统计男员工和女员工的平均年龄
-- select gender,avg(age) from emp
-- group by gender;

-- 3、查询年龄小于45的员工，并根据工作地址分组，获取数量大于等于3的工作地址
-- select workaddress,count(*) address_count from emp
-- where age<45
-- group by workaddress
-- having address_count>=3;

-- 排序查询
-- 1、根据年龄对公司的员工进行升序排序
-- select name,age from emp
-- order by age;

-- select name,age from emp
-- order by age desc;

-- 2、根据入职时间，对员工进行降序排序
-- select name '姓名',entrydate '入职时间' from emp
-- order by entrydate desc;

-- 3、根据年龄对公司的员工进行升序排序，年龄相同，再按入职时间降序排序
-- select name,age,entrydate from emp
-- order by age asc,entrydate desc;

-- 分页查询
-- 1、查询第1页员工数据，每页展示10条记录
-- select * from emp
-- limit 0,10;

-- 2、查询第2页员工数据，每页展示10条记录
-- select * from emp
-- limit 10,10;

-- -- 1、查询年龄为20，21，22，23的女性员工信息
-- select * from emp
-- where age in (20,21,22,23) and gender='女';
-- -- 2、查询性别为男，并且年龄在20~40岁(含)以内的姓名为三个字的员工
-- select * from emp
-- where gender='男' and (age between 20 and 40 ) and name like '___';
-- -- 3、统计员工表中，年龄小于60岁的，男性员工和女性员工的人数
-- select gender,count(*) from emp
-- where age<60
-- group by gender;
-- -- 4、查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序
-- select name,age from emp
-- where age<=35
-- order by age asc,entrydate desc;
-- -- 5、查询性别为男，且年龄在20-40岁(含)以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序
-- select * from emp
-- where gender='男' and (age>=20 and age<=40)
-- order by age,entrydate
-- limit 5;

-- 查询年龄大于15的员工的姓名、年龄，并根据年龄进行升序排序
-- select name,age from emp
-- where age>15
-- order by age;

-- use mysql;
-- select * from user;

-- 创建用户itcast ，只能够在当前主机localhost访问，密码123456
-- create user 'itcast'@'localhost' identified by '123456';

-- 修改用户 itcast的访问密码为1234
-- alter user 'itcast'@'localhost' identified with mysql_native_password by '1234';

-- 删除用户
-- drop user 'itcast'@'localhost';

-- -- 查询权限
-- show grants for 'root'@'%';

-- -- 授予权限
-- grant all on itcast.* to 'root'@'%';

-- -- 撤销权限
-- revoke all on itcast.* from 'root'@'%';