DML(SQL的增删改语句) 、DQL(SQL的查询语句)、DCL(数据库控制语句)

1.DML(增删改)

Data Manipulation Language ,对数据库中表的数据记录进行增删改的操作

 1.添加数据(insert)

2.修改数据

update employee set workAddress = '天津' where id = 16;

3.删除数据

2.DQL(查询)

1.DQL-语法

2.基本查询

其中设置别名时 as是可以省略的.

insert into employee(id, workNumber, name,  gender,age, idcard, workAddress, entrydate)
values(1,'1','刘燕','男',20,'123456789123456789','北京','2001-01-01'),
       (2,'2','王燕','女',21,'123456789123456789','上海','2002-01-01'),
       (3,'3','张燕','男',22,'123456789123456789','天津','2003-01-01'),
       (4,'4','李燕','男',23,'123456789123456789','广州','2004-01-01'),
       (5,'5','董燕','女',24,'123456789123456789','重庆','2005-01-01'),
       (6,'6','吕燕','男',25,'123456789123456789','南京','2006-01-01'),
       (7,'7','赵燕','女',26,'123456789123456789','杭州','2007-01-01'),
       (8,'8','任燕','女',27,'123456789123456789','海口','2008-01-01'),
       (9,'9','吴燕','男',28,'123456789123456789','深圳','2009-01-01'),
       (10,'10','苏燕','男',30,'123456789123456789','西安','2010-01-01'),
       (11,'11','连燕','女',40,'123456789123456789','太原','2011-01-01'),
       (12,'12','尹燕','女',50,'123456789123456789','呼和浩特','2012-01-01'),
       (13,'13','南燕','男',35,'123456789123456789','哈尔滨','2013-01-01'),
       (14,'14','郭燕','女',34,'123456789123456789','拉萨','2014-01-01'),
       (15,'15','商燕','男',33,'123456789123456789','大连','2015-01-01'),
       (16,'16','薛燕','女',32,null,'青岛','2016-01-01');



-- 基本查询
-- 1.查询指定字段: name  worknumber age 并返回
select name ,workNumber,age from employee;

-- 2.查询所有字段
select * from employee;
-- 3.查询所有工作地址
select workAddress  as '工作地址'from employee;

select workAddress  '工作地址'from employee;

-- 4.查询公司员工上班地址(不能重复)
select distinct workAddress '工作地址' from employee;

update employee set workAddress = '天津' where id = 16;
select distinct workAddress '工作地址' from employee;

3.条件查询

-- 条件查询
-- 1.查询年龄等于 50 的员工
select * from employee where age = 50;

-- 2.查询年龄小于 30 的员工
select * from employee where age < 30;

-- 3.查询没有身份证号的员工
select * from employee where idcard is null;

-- 4.查询有身份证号的员工信息
select * from employee where idcard is not null;

-- 5.查询年龄不等于88 的员工信息
select * from employee where age != 30;
select * from employee where age <> 30;


-- 6.查询年龄在25岁(包含) 到 38岁(包含)之间的员工信息
select * from employee where age >= 25 && age <= 38;
select * from employee where age >= 25 and age <= 38;
select * from employee where age between 25 and  38;

-- 7.查询性别为女且年龄小于38的员工信息
select * from employee where age < 38 && gender = '女';
select * from employee where age < 38 and gender = '女';

-- 8.查询年龄为30或40或50的员工
select * from employee where age = 30 || age = 40 || age = 50;
select * from employee where age = 30 or age = 40 or age = 50;
select * from employee where age in (30,40,50);

-- 9.查询姓名为两个字的员工信息
select * from employee where name like '__';

-- 10.查询身份证号最后一位是x
select * from employee where idcard like '%X';
select * from employee where idcard like '_________________X';

4.聚合函数

-- 聚合函数
-- 1.统计该企业员工总数
select count(*) from employee;
select count(16) from employee;

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

-- 3.统计天津地区员工的年龄之和
select sum(age) from employee where workAddress = '天津';

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

-- 5.统计该企业员工的最大年龄
select max(age) from employee;

所有null值不参与聚合函数的运算

5.分组查询

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

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

-- 3.查询年龄小于45 的员工,并根据工作地址分组,获取员工数量大于等于2的工作地址
select workAddress as '工作地址',  count(*) as '数量'from employee where age < 45 group by workAddress having count(*) >= 2;

6.排序查询

-- 排序查询
-- 1.根据年龄对公司的员工进行升序排序
select * from employee order by age;
select * from employee order by age asc;

-- 2.根据年龄降序排序
select * from employee order by age desc;

-- 3.根据入职时间 ,对员工进行降序排序
select * from employee order by entrydate desc;

-- 4.根据年龄升序,年龄相同按照入职时间降序
select * from employee order by age asc, entrydate desc;

7.分页查询

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

select * from employee limit 10;

-- 2.查询第2页员工数据,每页展示10条记录----> (页码 -1)*页展示记录数
select * from employee limit 10 ,10;

8.执行顺序

3.DCL(控制)

1.管理用户

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

-- 创建用户 heima ,可以在任意主机访问该数据库 , 密码123456
create user 'heima'@'%' identified by '123456';

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

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

2.权限控制

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

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

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