python之数据库-记录操作

记录操作

  1. 查询数据

    1. 单表查询

      • select

        1. 可以查一个，多个，*所有

          # select * from 表名;
# select 字段名 from 表名;
# select 字段名,字段名,字段名 from 表名;

        2. 调用函数：now() use() database() concat() concat_ws()

          # select user();
# select database();
# select now();

        3. 可以进行四则运算

          # select emp_name,salary\*12 from 表;

        4. 可以去重

          # select distinct 字段 from 表;

        5. 可以进行条件判断case when语句

          # select(
#     case
#     when emp_name = 'alex' then
#         concat(emp_name,'BIGSB')
#     when emp_name = 'jingliyang' then
#         emp_name
#     else
#         concat(emp_name,'sb')
#     end
#     ) as new_name
# from employee;

        6. 使用concat()函数对查询结果进行修改

          # select concat(字段,'字符串2',字段) from 表
# select concat(emp_name,' : ',salary) as info from employee;
# select concat(emp_name,' : ',salary) info from employee;
# select concat_ws('分隔符',字符串,字段1,字段2) info from employee;
# select concat_ws('|','信息',emp_name,salary) info from employee;

      • where 筛选行

        格式：select 字段 from 表名 where 条件

        1. 范围查询

          # > < >= <= = !=/<>
# between a and b
# in (1,2,3,4)   n选1

        2. 模糊查询

          • like

            # % 一个百分号代表任意长度的任意字符
	# 'a%'	以a开头
    # '%ing'	以ing结尾
    # '%a%'	含有a的
# _ 一个下划线代表一个任意字符
	# 'a_'	a后跟一个字符
    # '_a'	a前有一个字符
    # '_a_'	a前后各有一个字符

          • regexp

            # '^a'	以a开头
# '\d+'	数字

          • is is not

            # is null
# is not null

          • 逻辑运算

            # and
# or
# not

      • group by

        ​ 根据某个重复率比较高的字段进行分组，结果去重，一旦分组就不能对某一条数据进行操作了，永远都是考虑这组。

        ​ 单独使用group by分组，只能查询分组的字段，需要借助其他函数获得组内的其他相关信息。

        1. 与group_concat()函数一起

          select post,group_concat(emp_name) from employee group by post:	# 按岗位分组，并查看组内成员名
select post,group_concat(emp_name) as emp_members from employee group by post;	# 按岗位分组，查肯组内成员，并重命名emp_members

        2. 与聚合函数一起

          聚合函数聚合的是组的内容，若没有分组，则默认一组

          # count() 统计
select post,count(*) from employee group by post;
# max()	最大值
select post,max(salary) from employee group by post;
# min()	最小的
select post,min(salsry) from employee group by post;
# avg() 平均值
select post,avg(salary) from employee group by post;
# sum() 求和
select post,sum(salary) from employee group by poat;

      • having 过滤

        对分组后的数据进行过滤，

        # 查询各岗位内包含的员工个数小于2名的岗位名、岗位内包含的员工名字、个数
select post,group_conct(emp_name),count(*) from employee group by post having count(*)<2;
# 查询各岗位平均薪资大于10000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary)>10000;

        与where不同点：

        #！！！执行优先级从高到低：where > group by > having 
#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

      • order by 查询排序

        order by salary 默认升序（asc)；降序（desc）

        # 单列排序
select * from employee order by salary;	# 升序排序
select * from employee order by salary desc:	# 降序
# 多列排序
select * from employee order by age,salary desc;	# 按年龄升序排序，美年龄段按salary降序排序

      • limit 限制查询次数

        limit m,n 从m+1开始，每次显示n条记录

        select * from employee limit 0,5;	# 每次显示5条，第一条从1开始；
select * from employee limit 5;	# 默认初始值为m=0

      • 使用正则表达式查询

        select * from employee where emp_name regexp '^a';	# 查询以a开头的所有名字

      总结：

      1. 单表查询语法

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

      2. 关键字执行优先级

        from	# 先找到表
where	# 拿着where指定的约束条件，去文件/表中取出一条条记录
group by	# 将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
select	# 执行select（去重）
having	# 将分组结果按条件进行having过滤
order by	# 将结果按条件排序
limit	# 限制结果的显示条数

    2. 多表查询

      数据准备：

      #建表
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
('dema','male',18,200),
('jianji','female',48,201),
('debang','male',38,201),
('airuiliya','female',28,202),
('yasuo','male',18,200),
('lakesi','female',18,204)
;

      • 连表查询

        1. 交叉连接

          不适用任何匹配条件，生成笛卡尔积

          mysql> select * from employee,department;
+----+-----------+--------+------+--------+------+--------------+
| id | name      | sex    | age  | dep_id | id   | name         |
+----+-----------+--------+------+--------+------+--------------+
|  1 | dema      | male   |   18 |    200 |  200 | 技术         |
|  1 | dema      | male   |   18 |    200 |  201 | 人力资源     |
|  1 | dema      | male   |   18 |    200 |  202 | 销售         |
|  1 | dema      | male   |   18 |    200 |  203 | 运营         |
|  2 | jianji    | female |   48 |    201 |  200 | 技术         |
|  2 | jianji    | female |   48 |    201 |  201 | 人力资源     |
|  2 | jianji    | female |   48 |    201 |  202 | 销售         |
|  2 | jianji    | female |   48 |    201 |  203 | 运营         |
|  3 | debang    | male   |   38 |    201 |  200 | 技术         |
|  3 | debang    | male   |   38 |    201 |  201 | 人力资源     |
|  3 | debang    | male   |   38 |    201 |  202 | 销售         |
|  3 | debang    | male   |   38 |    201 |  203 | 运营         |
|  4 | airuiliya | female |   28 |    202 |  200 | 技术         |
|  4 | airuiliya | female |   28 |    202 |  201 | 人力资源     |
|  4 | airuiliya | female |   28 |    202 |  202 | 销售         |
|  4 | airuiliya | female |   28 |    202 |  203 | 运营         |
|  5 | yasuo     | male   |   18 |    200 |  200 | 技术         |
|  5 | yasuo     | male   |   18 |    200 |  201 | 人力资源     |
|  5 | yasuo     | male   |   18 |    200 |  202 | 销售         |
|  5 | yasuo     | male   |   18 |    200 |  203 | 运营         |
|  6 | lakesi    | female |   18 |    204 |  200 | 技术         |
|  6 | lakesi    | female |   18 |    204 |  201 | 人力资源     |
|  6 | lakesi    | female |   18 |    204 |  202 | 销售         |
|  6 | lakesi    | female |   18 |    204 |  203 | 运营         |
+----+-----------+--------+------+--------+------+--------------+
24 rows in set (0.00 sec)

        2. 内连接查询 inner join ... on

          只连接匹配的行

          # 语法：
select * from 表1 inner join 表2 on 条件

          select * from employee inner join department d on dep_id=d.id;

mysql> select * from employee inner join department d on dep_id=d.id;
+----+-----------+--------+------+--------+------+--------------+
| id | name      | sex    | age  | dep_id | id   | name         |
+----+-----------+--------+------+--------+------+--------------+
|  1 | dema      | male   |   18 |    200 |  200 | 技术         |
|  2 | jianji    | female |   48 |    201 |  201 | 人力资源     |
|  3 | debang    | male   |   38 |    201 |  201 | 人力资源     |
|  4 | airuiliya | female |   28 |    202 |  202 | 销售         |
|  5 | yasuo     | male   |   18 |    200 |  200 | 技术         |
+----+-----------+--------+------+--------+------+--------------+
5 rows in set (0.00 sec)

        3. 外连接查询

          • 左连接 left join ... on

            优先显示左表全部信息，本质是在内连接的基础上增加左边有右边没有的结果。

            # 语法
select * from 表1 left join 表2 on 条件;

            select * from employee left join department d on dep_id=d.id;

mysql> select * from employee left join department d on dep_id=d.id;
+----+-----------+--------+------+--------+------+--------------+
| id | name      | sex    | age  | dep_id | id   | name         |
+----+-----------+--------+------+--------+------+--------------+
|  1 | dema      | male   |   18 |    200 |  200 | 技术         |
|  5 | yasuo     | male   |   18 |    200 |  200 | 技术         |
|  2 | jianji    | female |   48 |    201 |  201 | 人力资源     |
|  3 | debang    | male   |   38 |    201 |  201 | 人力资源     |
|  4 | airuiliya | female |   28 |    202 |  202 | 销售         |
|  6 | lakesi    | female |   18 |    204 | NULL | NULL         |
+----+-----------+--------+------+--------+------+--------------+
6 rows in set (0.00 sec)

          • 右连接 right join ... on

            优先显示右表全部记录，本质就是在内连接的基础上增加右边有左边没有的内容。

            # 语法
select * from 表1 right join 表2 on 条件;

            select * from employee right join department d on dep_id=d.id;

mysql> select * from employee right join department d on dep_id=d.id;
+------+-----------+--------+------+--------+------+--------------+
| id   | name      | sex    | age  | dep_id | id   | name         |
+------+-----------+--------+------+--------+------+--------------+
|    1 | dema      | male   |   18 |    200 |  200 | 技术         |
|    2 | jianji    | female |   48 |    201 |  201 | 人力资源     |
|    3 | debang    | male   |   38 |    201 |  201 | 人力资源     |
|    4 | airuiliya | female |   28 |    202 |  202 | 销售         |
|    5 | yasuo     | male   |   18 |    200 |  200 | 技术         |
| NULL | NULL      | NULL   | NULL |   NULL |  203 | 运营         |
+------+-----------+--------+------+--------+------+--------------+
6 rows in set (0.00 sec)

          • 全外连接

            显示左右两个表全部内容，在内连接基础上增加左边有右边没有的和右边有左边没有的结果，使用union间接实现全外连接。

            # 语法
select * from 表1 left join 表2 on 条件
union
select * from 表1 right join 表2 on 条件;

            select * from employee left join department d on dep_id=d.id
union
select * from employee right join department d on dep_id=d.id;

mysql> select * from employee left join department d on dep_id=d.id
    -> union
    -> select * from employee right join department d on dep_id=d.id;
+------+-----------+--------+------+--------+------+--------------+
| id   | name      | sex    | age  | dep_id | id   | name         |
+------+-----------+--------+------+--------+------+--------------+
|    1 | dema      | male   |   18 |    200 |  200 | 技术         |
|    5 | yasuo     | male   |   18 |    200 |  200 | 技术         |
|    2 | jianji    | female |   48 |    201 |  201 | 人力资源     |
|    3 | debang    | male   |   38 |    201 |  201 | 人力资源     |
|    4 | airuiliya | female |   28 |    202 |  202 | 销售         |
|    6 | lakesi    | female |   18 |    204 | NULL | NULL         |
| NULL | NULL      | NULL   | NULL |   NULL |  203 | 运营         |
+------+-----------+--------+------+--------+------+--------------+
7 rows in set (0.00 sec)

          • 小练习

            # 以内连接的方式查询employee和department表，并且employee表中的age字段必须大于25，即找出年龄大于25岁的员工以及员工所在部门
select e.name,d.name from employee e inner join department d on e.dep_id = d.id where age>25;

mysql> select e.name,d.name from employee e inner join department d on e.dep_id = d.id where age>25;
+-----------+--------------+
| name      | name         |
+-----------+--------------+
| jianji    | 人力资源     |
| debang    | 人力资源     |
| airuiliya | 销售         |
+-----------+--------------+
3 rows in set (0.00 sec)

# 以内连接的方式查询employee和department表，并且以age字段的升序方式显示
select * from employee e inner join department d on e.dep_id=d.id order by age;

mysql> select * from employee e inner join department d on e.dep_id=d.id order by age;
+----+-----------+--------+------+--------+------+--------------+
| id | name      | sex    | age  | dep_id | id   | name         |
+----+-----------+--------+------+--------+------+--------------+
|  1 | dema      | male   |   18 |    200 |  200 | 技术         |
|  5 | yasuo     | male   |   18 |    200 |  200 | 技术         |
|  4 | airuiliya | female |   28 |    202 |  202 | 销售         |
|  3 | debang    | male   |   38 |    201 |  201 | 人力资源     |
|  2 | jianji    | female |   48 |    201 |  201 | 人力资源     |
+----+-----------+--------+------+--------+------+--------------+
5 rows in set (0.00 sec)

      • 子查询

        子查询包含以下要点：

        子查询是将一个查询语句嵌套在另一个查询语句中；

        内层查询语句的查询结果，可以为外层查询语句提供条件；

        子查询中可以包含：in、not in、any、all、exists、和not exists等关键字；

        还可以包含比较预算符：=、！=、>、<等

        1. 带in关键字的子查询

          # 查询平均年龄在25岁以上的部门
select dep_id from employee group by dep_id having avg(age)>25;	# 先找出平均年龄在25岁以上的部门id
select name from department where id in (select dep_id from employee group by dep_id having avg(age)>25);	# 通过上面找出的id在department表中找对应的部门名字

mysql> select name from department where id in (select dep_id from employee group by dep_id having avg(age)>25);
+--------------+
| name         |
+--------------+
| 人力资源     |
| 销售         |
+--------------+
2 rows in set (0.00 sec)

# 查看技术部员工姓名
select id from department where name='技术';
select name from employee where dep_id in (select id from department where name='技术');

mysql> select name from employee where dep_id in (select id from department where name='技术');
+-------+
| name  |
+-------+
| dema  |
| yasuo |
+-------+
2 rows in set (0.00 sec)

# 查看不足1人的部门名（子查询得到的是有人的部门id）
select dep_id from employee;
select name from department where id not in (select dep_id from employee);

mysql> select name from department where id not in (select dep_id from employee);
+--------+
| name   |
+--------+
| 运营   |
+--------+
1 row in set (0.00 sec)

# 查询平均年龄在25岁以上的部门名以及平均年龄的值
	# 先查部门的平均年龄大于25的部门id，平均年龄
	select dep_id,avg(age) from employee group by dep_id having avg(age)>25;
	# 查出结果后连表
	select name,avg_age from department 
	inner join (select dep_id,avg(age) avg_age from employee group by dep_id having avg(age)>25) t
	on department.id=t.dep_id;
mysql> select name,avg_age from department
    -> inner join (select dep_id,avg(age) avg_age from employee group by dep_id having avg(age)>25) t
    -> on department.id=t.dep_id;
+--------------+---------+
| name         | avg_age |
+--------------+---------+
| 人力资源     | 43.0000 |
| 销售         | 28.0000 |
+--------------+---------+
2 rows in set (0.00 sec)

        2. 带比较运算符的子查询

          # 查询大于所有人平均年龄的员工与年龄
	# 先查询出所有人的平均年龄
	select avg(age) from employee;
	# 根据查出的平均年龄查找
	select name,age from employee where age>(select avg(age) from employee);

mysql> select name,age from employee where age>(select avg(age) from employee);
+--------+------+
| name   | age  |
+--------+------+
| jianji |   48 |
| debang |   38 |
+--------+------+
2 rows in set (0.00 sec)

# 查询大于部门内平均年龄的员工名、年龄
	# 先找出每个部门的平均年龄
	select dep_id,avg(age) from employee group by dep_id;
	# 根据平均年龄查找
	select name,age from employee e inner join(select dep_id,avg(age) avg_age from employee group by dep_id) t
	on e.dep_id=t.dep_id
	where e.age>t.avg_age;

        3. 带exists关键字的子查询

          EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
          而是返回一个真假值。True或False
          当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询。

          # department表中dep_id=203,True
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=200);
+----+------------+--------+------+--------+
| 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 |
+----+------------+--------+------+--------+

          练习：查询每个部门最新入职的那位员工

          # 表与数据准备
#创建表
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 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, 
depart_id int
);
#插入记录
#三个部门：教学，销售，运营
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('lala','male',18,'20170301','teacher',7300.33,401,1), #以下是教学部
('dama','male',78,'20150302','teacher',1000000.31,401,1),
('dabang','male',81,'20130305','teacher',8300,401,1),
('yasuo','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('lakesi','female',18,'20110211','teacher',9000,401,1),
('xiaoxiao','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),

('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),

('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;

          # 先找到每个部门最晚的入职日期
select post,max(hire_date) from emp group by post;
# 通过入职日期与原表进行连接
select name,hire_date from emp inner join (select max(hire_date) max_date from emp group by post) t
on emp.hire_date=t.max_date;

mysql> select name,hire_date from emp inner join (select max(hire_date) max_date from emp group by post) t
    -> on emp.hire_date=t.max_date;
+--------+------------+
| name   | hire_date  |
+--------+------------+
| lala   | 2017-03-01 |
| 格格   | 2017-01-27 |
| 张野   | 2016-03-11 |
+--------+------------+
3 rows in set (0.00 sec)

    3. insert 插入数据

      # 插入完整数据（顺序插入）
insert into 表名（字段1，字段2，字段3...） values(值1，值2，值3...);
insert into 表名 values(值1，值2，值3...);
# 指定字段插入
insert into 表名（字段1，字段2...） values(值1，值2...);
# 插入多条记录
insert into 表名 values(值1，值2...),
					  (值1，值2...),
					  (值1，值2...);
#插入查询结果
insert into 表名（字段1，字段2...） select （字段1，字段2...） from 表名1 where...

    4. update 更新数据

      update 表名 set 字段1=值1，字段2=值2 where 条件;

    5. delete 删除数据

      delete from 表名 where 条件;
posted @ 2019-08-06 21:02  小小蚂蚁mm  阅读(125)  评论(0)  编辑  收藏  举报