数据库05——记录相关操作

数据库05-- 记录相关操作

1. 介绍

MySQL数据操作：DML

在MYSQL管理软件中，可以通过SQL语句中的DML语言来实现数据的操作，包括

1）使用insert实现数据的插入

2）update实现数据的更新

3）使用delete实现数据的删除

4）使用select查询数据以及

2. 插入数据INSERT

1. 插入完整数据（顺序插入）
    语法一：
    INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);

    语法二：
    INSERT INTO 表名 VALUES (值1,值2,值3…值n);

2. 指定字段插入数据
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);

3. 插入多条记录
    语法：
    INSERT INTO 表名 VALUES
        (值1,值2,值3…值n),
        (值1,值2,值3…值n),
        (值1,值2,值3…值n);

3. 更新数据UPDATE

语法：
    UPDATE 表名 SET
        字段1=值1,
        字段2=值2,
        WHERE CONDITION;

示例：
    UPDATE mysql.user SET password=password(‘123’) 
        where user=’root’ and host=’localhost’;
    补充：没有切换库（use 数据库名）就需要mysql.user指定库去操作

4. 删除数据DELETE

语法：
    DELETE FROM 表名 
        WHERE CONITION;

示例：
    DELETE FROM mysql.user 
        WHERE password=’’;

5. 查询数据SELECT

前期表准备

create table emp(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male',
age int(3) unsigned not null default 0,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2)
);

insert into emp(name,sex,age,hire_date,post,salary)  values
('alex','male',78,'20150302','teacher',1000000.31),
('wupeiqi','male',81,'20130305','teacher',8300),
('yuanhao','male',73,'20140701','teacher',3500),
('liwenzhou','male',28,'20121101','teacher',2100),
('jingliyang','female',18,'20110211','teacher',9000),
('jinxin','male',18,'19000301','teacher',30000),
('成龙','male',48,'20101111','teacher',10000),
('歪歪','female',48,'20150311','sale',3000.13),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35),
('丁丁','female',18,'20110312','sale',1000.37),
('星星','female',18,'20160513','sale',3000.29),
('格格','female',28,'20170127','sale',4000.33),
('张野','male',28,'20160311','operation',10000.13), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000),
('程咬银','female',18,'20130311','operation',19000),
('程咬铜','male',18,'20150411','operation',18000),
('程咬铁','female',18,'20140512','operation',17000);

# 当表字段特别多 展示的时候错乱 可以使用\G分行展示
select * from emp\G;

5.1. 单表查询的语句

select 字段1,字段2...from 表名 
					where 条件
    				group by field
        			having 筛选
            		order by field
                	limit 限制条数

5.2. 关键字的执行优先级（重点）

重点：关键字的执行优先级
from
where
group by
having
select
distinct
order by
limit

1.找到表：from

2.拿着where制定的约束条件，去文件/表中取出一条条记录

3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组

4.将分组的结果进行having过滤

5.执行select

6.去重

7.将结果按条件排序：order by

8.限制结果的显示条数

5.3. 简单查询

# 简单查询
select id,name,sex,age,hire_date,post,post_comment,salary
from emp;

select * from emp;
select name,sex,salary;

# 避免重复distinct
select distinct post from emp;


模糊查询like % 在前不走索引
not in 不走索引

5.4. where约束条件

# 作用：是对整体数据的一个筛选操作
# 1. 查询id大于等3小于等于6的数据；
select * from emp where id<=3 and id <=6;
select * from emp where id between 3 and 6;

# 2.查询薪资20000或者18000或者17000的数据
select * from emp where salary=20000 or salary=18000 or salary=17000;
select * from emp where salary in(20000,18000,17000);

# 3.查询员工姓名中包含字母o的员工的姓名和薪资
"""
模糊查询
	like
		% 匹配任意多个字符
		_ 匹配任意单个字符
"""
select name,salary from emp where name like '%o%';

# 4.查询员工姓名室友四个字符组成的 姓名和薪资 char_length
select name,salary from emp where name like'____';
select name,salary from emp where char_length(name)=4;

# 5.查询id小于3或者id大于6的数据
select * from emp where id not between 3 and 6;

# 6.查询薪资不在20000,18000,17000范围的数据
select * from emp where salary not in(20000,18000,17000);

# 7.查询岗位描述为空的员工姓名和岗位名 针对null不能= 要用null
select * from emp where post_comment is null;

5.5. group by 分组

# 分组实际应用场景	男女比例    部门平均薪资    等等        # 1.按照部门分组select * from emp group by post;'''分组之后 最小可操作单位应该是组，而不再是组内的单个数据	上述命令在你没有设置严格模式的时候是可以正常执行的 返回的是分组之后，每个组的第一条数据，但是这不符合分组的规范：分组之后不应该考虑单个数据，而应该以组为操作单位(分组之后没办法直接获取组内单个数据)	如果设置了严格模式，上述命令会报错	#设置sql_mole如下操作(我们可以去掉ONLY_FULL_GROUP_BY模式)：mysql> set global sql_mode='STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY';'''设置严格模式之后，分组默认只能拿到分组的依据，如按照post分组只能拿到postselect post from emp group by post;按照什么分组就只能拿到分组 其他字段不能直接获取，需要借助与一些方法（聚合函数）'''什么时候需要分组呢？？？	关键字		每个 平均 最高 最低		聚合函数		max 最大 		min 最小		avg 平均		count 统计个数		sum  总和				'''# 1.获取每个部门的最高薪资select post,max(salary) from emp group by post;select post as '部门',max(salary) as '最高薪资' from emp group by post;select post  '部门',max(salary)  '最高薪资' from emp group by post;（不推荐）# as可以给字段取别名 也可以直接省略不写 但是不推荐 因为省略的语意不明确 容易错乱# 2.获取每个部门的最低薪资select post,min(salary) from emp group by post;# 3.获取每个部门的平均薪资select post,avg(salary) from emp group by post;# 4.获取每个部门的薪资总和select post,sum(salary) from emp group by post;# 5.获取每个部门的人数select post,count(id) from emp group by post;# 6.查询分组之后的部门名称和每个部门下所有的员工姓名# group_concat不仅仅可以支持你获取分组后的其他字段值 还支持拼接操作select post,group_concat(name) from emp group by post;select post,group_concat(name, ':' ,salary) from emp group by post;select post,group_concat(name, '_DSB') from emp group by post;# concat不分组的时候用select concat('NAME:',name),concat('SAL:',salary) from emp;# 补充 as语法不单单可以给字段其别名 还可以给表起别名（临时有效）select t1.id,t1.name from emp as t1;select id,name from emp as t1;# 查询每个人的年薪 12薪水select name,salary*12 from emp;

分组注意事项

# 关键字where和group by同时出现的时候group by必须在where的后面where 先对整体数据进行过滤之后在分组操作where筛选条件不能使用聚合函数select id,nane,age from emp where max(salary) > 3000;select max(salary) from emp; # 不分组默认整体就是一组# 统计各部门年龄在30岁以上的 员工平均薪资1) select group_concat(name),avg(salary) from emp where age>=30 group by post;2) select group_concat(post,':',name,':',age),avg(salary) from emp where age>=30 group by post;3) select post,avg(salary) from emp where age>=30 group by post;

5.6. having分组之后的筛选条件

having的语法跟where是一致的**，只不过having是在分组以后进行的过滤操作**，即having是可以直接使用聚合函数

''''''# 统计各部门年龄在30岁以上的员工平均工资并且保留平均薪资大于10000的部门select post,avg(salary) from emp					where age>=30 					group by post        			having avg(salary)>10000        			;

5.7. distinct去重

一定注意 必须是完全一样的数据才可以去重

一定不要主键忽视了，有主键存在的情况下，是不可能去重的

'''ORM 对象关系映射 让不懂SQL语句的人也能勾非常牛逼的操作数据库表						类一条条的数据				 对象字段对应的值				 对象的属性你再写类 就意味着在创建表用类生成对象 就一位置在创建数据对象点属性 就是在获取数据字段对应的值目的就是减轻python程序员的压力 只需要会python面对对象的知识点就可以操作MySQL'''select distinct id,age from emp;  # id 是主键无法去重select distinct age from emp;

5.8. ordr by排序

select * from emp order by salary;'''order by 默认是升序 asc 该asc可以省略desc是降序'''select * from emp order by age desc,salary asc;# 先按照age降序排序 如果碰到age相同的，再按照salary升序排序#  统计各部门年龄在10岁以上的员工平均工资并且保留平均薪资大于1000的部门，然后对平均工资降序排序select post,group_concat(name) as name,avg(salary) from emp 	where age >10     group by post    having avg(salary)>1000     order by avg(salary) desc;

5.9. limit限制展示条数

针对数据过多的数据，我们通常进行分页操作

select * from emp;  #　如果emp数据过多会卡死select * from emp limit 3;	# 只展示三条数据select * from emp limit 0,5;select * from emp limit 5,5;第一个参数是起始位置第二个参数是展示条数

5.10. 正则

select * from emp where name regexp '^j.*(n|y)$';

6. 多表查询

6.1. 介绍

准备表

#建表create table department(id int,name varchar(20) );create table employee(id int primary key auto_increment,name varchar(20),sex enum('male','female') not null default 'male',age int,dep_id int);#插入数据insert into department values(200,'技术'),(201,'人力资源'),(202,'销售'),(203,'运营');insert into employee(name,sex,age,dep_id) values('egon','male',18,200),('alex','female',48,201),('wupeiqi','male',38,201),('yuanhao','female',28,202),('liwenzhou','male',18,200),('jingliyang','female',18,204);#查看表结构和数据mysql> desc department;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || name | varchar(20) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+mysql> desc employee;+--------+-----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+-----------------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || name | varchar(20) | YES | | NULL | || sex | enum('male','female') | NO | | male | || age | int(11) | YES | | NULL | || dep_id | int(11) | YES | | NULL | |+--------+-----------------------+------+-----+---------+----------------+mysql> select * from department;+------+--------------+| id | name |+------+--------------+| 200 | 技术 || 201 | 人力资源 || 202 | 销售 || 203 | 运营 |+------+--------------+mysql> select * from employee;+----+------------+--------+------+--------+| id | name | sex | age | dep_id |+----+------------+--------+------+--------+| 1 | egon | male | 18 | 200 || 2 | alex | female | 48 | 201 || 3 | wupeiqi | male | 38 | 201 || 4 | yuanhao | female | 28 | 202 || 5 | liwenzhou | male | 18 | 200 || 6 | jingliyang | female | 18 | 204 |+----+------------+--------+------+--------+表department与employee

6.2. 多表连接查询

select * from department,employee;  # 结果 笛卡尔积select * from employee,department where employee.dep_id = department.id'''拼表操作方法inner join  内连接left join	左连接right join	右连接union		全连接'''select * from employee inner join department on employee.dep_id = department.id;# 只拼接两张表中共有的数据部分# left join 左连接select * from employee left join department on employee.dep_id = department.id;# 左表所有的数据都展示出来，没有对应的项就用NULL# right join  右链接select * from employee right join department on employee.dep_id = department.id;# 右表所有的数据都展示出来，没有对应的项就用NULL# union		全连接 左右两表所有的数据都展示出来select * from employee left join department on employee.dep_id = department.id unionselect * from employee right join department on employee.dep_id = department.id;

7. 子查询

'''子查询就是我们平时解决问题的思路	分步走解决问题		第一步		第二步将一个查询语句的结果当作另外一个查询语句的条件去用'''# 查询部门是技术或者人力资源的员工信息	1 先获取部门的id号    2 再去员工表里面筛选对应的员工    select id from department where name='技术' or name = '人力资源';    select name from employee where dep_id in (200,201);        select * from employee where dep_id in ( select id from department where name='技术' or name = '人力资源');    

总结

表的查询结果可以作为其他表的查询条件也可以通过其别名的方式把它作为一张虚拟表跟其他表关联'''多表查询就两种方式	先拼接表再查询	子查询 一步一步来'''