自学MYSQL 例子2（基础篇）

例子数据

create table emp(
    id                     int                comment '编号',
    worknumber             varchar(10)        comment '工号',
    name                   varchar(10)        comment '姓名',
    gender                 char(1)            comment  '性别',
    age                    tinyint unsigned   comment  '年龄',
    idcard                 char(18)           comment '身份证号',
    workaddress            varchar(50)        comment '工作地址',
    entrydate              date               comment '入职时间'
) comment '员工表';
insert into emp (id, worknumber, name, gender, age, idcard,workaddress, entrydate)
values (1,'1','柳岩','女',20,'123456789012345678','北京','2000-01-01'),
       (2,'2','张无忌','男',18,'123456789012345670','北京','2005-09-01'),
       (3,'3','韦一笑','男',38,'123456789712345670','上海','2005-08-01'),
       (4,'4','赵敏','女',18,'123456757123845670','北京','2009-12-01'),
       (5,'5','小昭','女',16,'123456769012345678','上海','2007-07-01'),
	   (6,'6','杨道','男',28,'12345678931234567X','北京','2006-01-01'),
	   (7,'7','范瑶','男',40,'123456789212345670','北京','2005-05-01'),
	   (8,'8','黛绮丝','女',38,'123456157123645670','天津','2015-05-01'),
	   (9,'9','范凉凉','女',45,'123156789012345678','北京','2010-04-01'),
	   (10,'10','陈友谅','男',53,'123456789012345670','上海','2011-01-01'),
	   (11,'11','张士诚','男',55,'123567897123465670','江苏','2015-05-01'),
	   (12,'12','常遇春','男',32,'123446757152345670','北京','2004-02-01'),
	   (13,'13','张三丰','男',88,'123656789012345678','江苏','2020-11-01'),
       (14,'14','灭绝','女',65,'123456719012345670','西安','2019-05-01'),
       (15,'15','胡青牛','男',70,'12345674971234567X','西安','2018-04-01'),
       (16,'16','周芷若','女',18,null,'北京','2012-06-01');

条件查询例子：

-- 年龄等于30
select * from employee where age = 30;
-- 年龄小于30
select * from employee where age < 30;
-- 小于等于
select * from employee where age <= 30;
-- 没有身份证
select * from employee where idcard is null or idcard = '';
-- 有身份证
select * from employee where idcard;
select * from employee where idcard is not null;
-- 不等于
select * from employee where age != 30;
-- 年龄在20到30之间
select * from employee where age between 20 and 30;
select * from employee where age >= 20 and age <= 30;
-- 下面语句不报错，但查不到任何信息
select * from employee where age between 30 and 20;
-- 性别为女且年龄小于30
select * from employee where age < 30 and gender = '女';
-- 年龄等于25或30或35
select * from employee where age = 25 or age = 30 or age = 35;
select * from employee where age in (25, 30, 35);
-- 姓名为两个字
select * from employee where name like '__';
-- 身份证最后为X
select * from employee where idcard like '%X';

分组查询例子：

-- 根据性别分组，统计男性和女性数量（只显示分组数量，不显示哪个是男哪个是女）
select count(*) from employee group by gender;
-- 根据性别分组，统计男性和女性数量
select gender, count(*) from employee group by gender;
-- 根据性别分组，统计男性和女性的平均年龄
select gender, avg(age) from employee group by gender;
-- 年龄小于45，并根据工作地址分组
select workaddress, count(*) from employee where age < 45 group by workaddress;
-- 年龄小于45，并根据工作地址分组，获取员工数量大于等于3的工作地址
select workaddress, count(*) address_count from employee where age < 45 group by workaddress having address_count >= 3;

排序查询例子：

-- 根据年龄升序排序
SELECT * FROM employee ORDER BY age ASC;
SELECT * FROM employee ORDER BY age;
-- 两字段排序，根据年龄升序排序，入职时间降序排序
SELECT * FROM employee ORDER BY age ASC, entrydate DESC;

分页查询例子：

-- 查询第一页数据，展示10条
SELECT * FROM employee LIMIT 0, 10;
-- 查询第二页
SELECT * FROM employee LIMIT 10, 10;

管理用户例子：

-- 创建用户test，只能在当前主机localhost访问
create user 'test'@'localhost' identified by '123456';
-- 创建用户test，能在任意主机访问
create user 'test'@'%' identified by '123456';
create user 'test' identified by '123456';
-- 修改密码
alter user 'test'@'localhost' identified with mysql_native_password by '1234';
-- 删除用户
drop user 'test'@'localhost';

权限例子：

show grants for 'heima'@'%';
grant all on itcase.* to 'heima'@'%';
revoke all on itcase.* from 'heima'@'%';