Python进阶_mysql_查询、事物（4）

Stella981
• 阅读 849

在进行查询之前，我们要先建好关系表，并往数据表中插入些数据。为查询操作做好准备。

五张关系表的创建：
#创建并进入数据库：
mysql> CREATE DATABASE `info`;
Query OK, 1 row affected (0.00 sec)
mysql> USE `info`;
Database changed

#创建学院表：
CREATE TABLE `department`(
`id` INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL
);

 #创建学生表：
CREATE TABLE `student`(
`s_id` INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
`dept_id` INT,
FOREIGN KEY(`dept_id`) REFERENCES `department` (`id`)
);


`age` INT,
`sex` CHAR(1),
FOREIGN KEY(`s_id`) REFERENCES `student` (`s_id`)
);

#创建课程表：
CREATE TABLE `course`(
`id` INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL
);

#创csou建中间表：
CREATE TABLE `select`(
`s_id` INT,
`c_id` INT,
PRIMARY KEY (`s_id`,`c_id`),
FOREIGN KEY (`s_id`) REFERENCES `student`(`s_id`),
FOREIGN KEY (`c_id`) REFERENCES ins`course`(`id`)
);showcreate;


#查看当前存在的表：
mysql> SHOW TABLES;
往数据表中添加数据
#往学院表中添加数据：
mysql> INSERT INTO `department`(`name`)
    -> VALUES('外国语'),('艺术'),('计算机'),('化工');

#往学生表中添加数据：
mysql> INSERT INTO `student`(`name`,`dept_id`)
    -> VALUES('小明',1),('小红',3),('小花',3),('小新',4),('张三',2),('刘三',3);

#往学生详细信息表中添加数据：
mysql> INSERT INTO stu_details
    -> VALUES(1,18,'男'),(4,20,'女'),(3,16,'女'),(2,19,'男');

#往课程表中添加数据：
mysql> INSERT INTO `course`(`name`)
    -> VALUES ('心理学'),('佛学'),('近代史'), ('音乐鉴赏');

#查看中间表的结构：    (select是关键字，要加反引号)
mysql> DESC `select`;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| s_id  | int(11) | NO   | PRI | NULL    |       |
| c_id  | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.03 sec)
#往中间表中添加数据
mysql> INSERT INTO `select`
    -> VALUES(1,2),(1,4),(2,1),(2,4),(4,1),(4,2),(4,4);

查询

查询所有记录
SELECT * FROM  tb_name; 

    SELECT * FROM  `department`;
    SELECT * FROM  `student`;
    SELECT * FROM  `stu_details`;
    SELECT * FROM  `course`;
    SELECT * FROM  `select`;

查询选中列记录

SELECT col_name1,col_name2 FROM tb_name; 

查询指定条件下的记录

SELECT col_name FROM  tb_name  WHERE 条件

查询后为列取别名

SELECT  col_name  AS  new_name  FROM  tab_name

模糊查询

SELECT 字段 FROM 表 WHERE 某字段 Like 条件

#查询所有记录：
mysql> SELECT * FROM `student`;

#查询选中列记录
mysql> SELECT name,dept_id FROM student;

#查询指定条件下的记录
mysql> SELECT * FROM student WHERE `name`='小红';

#查询后为列取别名
SELECT name AS `姓名` ,dept_id AS 学院id  FROM student WHERE s_id>=2;

SELECT name ,dept_id   FROM student WHERE s_id>=2;

SELECT name `姓名` ,dept_id 学院id  FROM student WHERE s_id>=2;

#模糊查询  *
mysql> select * from student where name like '小%';
mysql> select * from student where name like '小_';
mysql> select * from student where name like '%小%';

% 多个字（任意个）*
_ 一个字


#逻辑运算符  or  and 
# or
select * from student where name like '小%' or name like '张%';

# and
select * from student where name like '小%' and s_id >2;

# 判断为null，不用 =   ，只能用 is    is not
select * from student where dept_id is null;

select * from student where dept_id is not null;
排序ORDER BY selectsuss

ASC升序(默认) DESC降序

#查询学生的选修表（中间表）
mysql> SELECT * FROM `select`;

#按学生学号升序输出
mysql> SELECT * FROM `select` ORDER BY `s_id`;
select s_id 学生,c_id 课程 from `select` order by s_id asc;

#按课程id降序输出：
mysql> SELECT * FROM `select` ORDER BY `c_id` DESC;
限制显示数据的数量LIMIT
# 分页。

#按学生学号升序输出的前4条数据
mysql> SELECT * FROM `select` ORDER BY `s_id` LIMIT 4;    # （0，4）  从 索引为0， 往后面拿4条



#指定的返回的数据的位置和数量
mysql> SELECT * FROM `select` ORDER BY `s_id` LIMIT 4,4;    # （4，4）  从 索引为4， 往后面拿4条
分组查询GROUP BY
## 一般跟聚合函数 


#对学生表中学院栏进行分组，并统计学院的学生人数：
mysql> SELECT dept_id AS 学院id,count(dept_id) AS 学生个数 FROM student GROUP BY `dept_id` ;
+----------+--------------+
| 学院id   | 学生个数     |
+----------+--------------+
|        1 |            1 |
|        3 |            2 |
|        4 |            1 |
+----------+--------------+
3 rows in set (0.00 sec)

select * from student order by dept_id;
see

## 不能这么写
# select s_id , age ,sex from stu_details group by sex

# group by 只能跟聚合函数一起用， 一般用 做统计
select sex , MAX(age) from stu_details group by sex



# 装B
select dept_id,group_concat(s_id,name separator ',') from student group by `dept_id`;
      3 |                  2小红,3小花,6刘三  

select dept_id,group_concat(s_id,'  ',name separator ',') from student group by `dept_id`;



HAVING分组条件   
HAVING 后的字段必须是SELECT后出现过的
mysql> SELECT dept_id AS 学院id,count(dept_id) AS 学生个数 FROM student GROUP BY `dept_id` HAVING  学生个数=1;
+----------+--------------+
| 学院id   | 学生个数     |
+----------+--------------+
|        1 |            1 |
|        4 |            1 |
+----------+--------------+
2 rows in set (0.01 sec)

区别：
where  带着条件 去表里面，删选内容， （table）

having ：select 出来了结果，在进行一次删选。

子查询

出现在其他SQL语句内的SELECT字句。

1)嵌套在查询内部2)必须始终出现在圆括号内3)可以包含多个关键字或条件

### 把别人的结果，当成数据，直接拿过来用。


# 求出学生的平均年龄
SELECT AVG(`age`) FROM `stu_details`;

#查找出大于平均年龄的数据
mysql> SELECT * FROM `stu_details` WHERE `age`>18.25;

#将平均数的SQL语句作为子查询放入上一条语句中
mysql> SELECT * FROM `stu_details` WHERE `age`>(SELECT AVG(`age`) FROM `stu_details`);



##需求： 要查找，计算机和外国语 的学生

select id from department where name in ('计算机','外国语');



select * from student where dept_id in (select id from department where name in ('计算机','外国语') );


# in ， not in
select * from student where dept_id not in (1,3);
select * from student where dept_id in (1,3);

联表查询

内连接[INNER| CROSS] JOIN

无条件内连接：无条件内连接，又名交叉连接/笛卡尔连接第一张表种的每一项会和另一张表的每一项依次组合

有条件内连接：在无条件的内连接基础上，加上一个ON子句当连接的时候，筛选出那些有实际意义的记录行来进行拼接

在写条件时注意两张表的列名是否一样，如果时一样的则要在前面加上表名，tb_name.colname这种形式存在

#无条件内连接：
mysql> SELECT * FROM `student` INNER JOIN `department`;
+------+--------+------+----+-----------+
| s_id | name   | dept_id | id | name      |
+------+--------+------+----+-----------+
|    1 | 小明   |    1 |  1 | 外国语    |
|    2 | 小红   |    3 |  1 | 外国语    |
|    3 | 小花   |    3 |  1 | 外国语    |
|    4 | 小新   |    4 |  1 | 外国语    |
|    1 | 小明   |    1 |  2 | 艺术      |
|    2 | 小红   |    3 |  2 | 艺术      |
|    3 | 小花   |    3 |  2 | 艺术      |
|    4 | 小新   |    4 |  2 | 艺术      |
|    1 | 小明   |    1 |  3 | 计算机    |
|    2 | 小红   |    3 |  3 | 计算机    |
|    3 | 小花   |    3 |  3 | 计算机    |
|    4 | 小新   |    4 |  3 | 计算机    |
|    1 | 小明   |    1 |  4 | 化工      |
|    2 | 小红   |    3 |  4 | 化工      |
|    3 | 小花   |    3 |  4 | 化工      |
|    4 | 小新   |    4 |  4 | 化工      |
+------+--------+------+----+-----------+
16 rows in set (0.04 sec)
#有条件内连接：
mysql> SELECT * FROM `student` INNER JOIN `department`
    -> ON dept_id=id;
+------+--------+------+----+-----------+
| s_id | name   | dept_id | id | name      |
+------+--------+------+----+-----------+
|    1 | 小明   |    1 |  1 | 外国语    |
|    2 | 小红   |    3 |  3 | 计算机    |
|    3 | 小花   |    3 |  3 | 计算机    |
|    4 | 小新   |    4 |  4 | 化工      |
+------+--------+------+----+-----------+
4 rows in set (0.03 sec)

#需求： 查询出 学生姓名，和对应学院名
mysql> SELECT s.name 姓名, d.name 学院 FROM `student` s
    -> INNER JOIN `department` d
    -> ON dept_id=id;
外连接{ LEFT| RIGHT } [OUTER] JOIN

{ LEFT| RIGHT } [OUTER] JOIN

左外连接： （以左表为基准） in两张表做连接的时候，在连接条件不匹配的时候留下左表中的数据，而右表中的数据以NULL填充

右外连接： （以右表为基准）对两张表做连接的时候，在连接条件不匹配的时候留下右表中的数据，而左表中的数据以NULL填充

#往学生表中添加数据，只添加名字
mysql> INSERT INTO student(name)
    -> VALUES('xixi');
Query OK, 1 row affected (0.11 sec)
#查看所有学生表数据
mysql> SELECT * FROM  student;
+------+--------+------+
| s_id | name   | dept_id |
+------+--------+------+
|    1 | 小明   |    1 |
|    2 | 小红   |    3 |
|    3 | 小花   |    3 |
|    4 | 小新   |    4 |
|    5 | xixi   | NULL |
+------+--------+------+
5 rows in set (0.00 sec)
#使用内连接加条件只能看到有分配好学院的学生的信息；
mysql> SELECT * FROM  student INNER JOIN departmentjoin
    -> ON dept_id=id;
+------+--------+------+----+-----------+
| s_id | name   | dept_id | id | name      |
+------+--------+------+----+-----------+
|    1 | 小明   |    1 |  1 | 外国语    |
|    2 | 小红   |    3 |  3 | 计算机    |
|    3 | 小花   |    3 |  3 | 计算机    |
|    4 | 小新   |    4 |  4 | 化工      |
+------+--------+------+----+-----------+
4 rows in set (0.02 sec)

#使用左连接把学生的数据全取出来，该学生没有学院信息的用NULL填充
mysql> SELECT * FROM  student LEFT JOIN department
    -> ON dept_id=id;
+------+--------+------+------+-----------+
| s_id | name   | dept_id | id   | name      |
+------+--------+------+------+-----------+
|    1 | 小明   |    1 |    1 | 外国语    |
|    2 | 小红   |    3 |    3 | 计算机    |
|    3 | 小花   |    3 |    3 | 计算机    |
|    4 | 小新   |    4 |    4 | 化工      |
|    5 | xixi   | NULL | NULL | NULL      |
+------+--------+------+------+-----------+
5 rows in set (0.00 sec)



#使用右外连接把目前还没有学生的学院的数据也显示出来
mysql> SELECT * FROM  student RIGHT JOIN department
    -> ON dept_id=id;
+------+--------+------+----+-----------+
| s_id | name   | dept_id | id | name      |
+------+--------+------+----+-----------+
|    1 | 小明   |    1 |  1 | 外国语     |
|    2 | 小红   |    3 |  3 | 计算机     |
|    3 | 小花   |    3 |  3 | 计算机     |
|    4 | 小新   |    4 |  4 | 化工       |
| NULL | NULL   | NULL |  2 | 艺术      |
+------+--------+------+----+-----------+
5 rows in set (0.00 sec)

mysql>s

# 需求：  作为班主任，我想看到，学生的 ( 姓名，选的课程名，所属学院 )
select  s.name, c.name,d.name  from  `student` s 
left join `select` se on se.s_id = s.s_id
left join course  c on se.c_id = c.id
left join department d on s.dept_id = d.id;

##1.
select s.name,d.name from student s left join department d on s.dept_id=d.id;

##2.学生选的课（名称）
select se.s_id ,c.name from `select` se   left join course c on se.c_id = c.id;


select s.name, d.name,  c.name from student s
left join  department d on s.dept_id = d.id
left join `select` se  on s.s_id = se.s_id
left join course c on se.c_id = c.id;



select  s.name ,GROUP_CONCAT(c.name SEPARATOR ',') 课程,d.name  from  `student` s 
left join `select` se on s.s_id = se.s_id
left join `course` c on se.c_id = c.id
left join `department` d on s.dept_id = d.id
group by s.name,d.name ;
+--------+-------------------------------+-----------+
| name   | 课程                          | name      |
+--------+-------------------------------+-----------+
| xixi   | NULL                          | NULL      |
| 小新   | 心理学,佛学,音乐鉴赏          | 化工      |
| 小明   | 佛学,音乐鉴赏                 | 外国语    |
| 小红   | 音乐鉴赏,心理学               | 计算机    |
| 小花   | NULL                          | 计算机    |
+--------+-------------------------------+-----------+


#需求：  作为宿管， 学生的 ( 姓名， 年龄，性别，所属学院)
select s.name, stu.age,stu.sex,d.name from student s
left join `stu_details` stu on s.s_id = stu.s_id
left join `department` d on s.dept_id = d.id;
+--------+------+------+-----------+
| name   | age  | sex  | name      |
+--------+------+------+-----------+
| 小明   |   18 | 男   | 外国语    |
| 小红   |   19 | 男   | 计算机    |
| 小花   |   16 | 女   | 计算机    |
| 小新   |   20 | 女   | 化工      |
| xixi   | NULL | NULL | NULL      |
+--------+------+------+-----------+

事务

事务： 是数据库运行中的一个逻辑工作单位。#原子性事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。​#一致性事务在完成时，必须使所有的数据都保持一致状态。​#隔离性由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。

为了保证数据库记录的更新从一个一致性状态变更为另一个一致性状态

例：
创建一张银行账户的表
mysql> CREATE TABLE `account`(
    -> `id` INT PRIMARY KEY AUTO_INCREMENT,
    -> `name` VARCHAR(20) NOT NULL,
    -> `balance` INT
    -> );
Query OK, 0 rows affected (0.52 sec)
添加两个用户及用户的存款的信息
mysql> INSERT INTO `account`(`name`,`balance`)
    ->  VALUES('张三',10000),
    ->        ('小明',2000)
    -> ;
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

假设现在用户小明在商店买了500元东西，现在要转账给商店，那么就需要从小明的账户上减去500，然后在商店的用户上加上500，但是如果在减500的过程中出现了系统故障，再重新启动后发现小明的钱扣了，但商店却没有收到，这时候就会出现数据变动不一致。对于这种数据的修改我们需要的就是要么同时修改成功，要么同时修改失败，所以这就需要用事务来进行出来。

START TRANSACTION：开始一个新的事务
COMMIT：提交当前事务，做出永久改变
ROLLBACK：回滚当前事务，放弃修改

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE `account`
    -> SET `balance`= `balance`-500
    -> WHERE `name` ='小明'
    -> ;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

使用ROLLBACK;使数据的修改不生效，回到事务前的状态：
mysql> ROLLBACK;
Query OK, 0 rows affected (0.06 sec)


做一次正确的操作：
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE `account`
    -> SET `balance`=`balance`-500
    -> WHERE `name`='小明'
    -> ;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE `account`
    -> SET `balance`=`balance`+500
    -> WHERE `name`='张三'
    -> ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM `account`;

mysql> COMMIT;
Query OK, 0 rows affected (0.07 sec)

当COMMIT后，数据修改成功，ROLLBACK也没法回到之前了。

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

使用事务来处理是非常必要。

子查询

出现在其他SQL语句内的SELECT字句。

1)嵌套在查询内部2)必须始终出现在圆括号内3)可以包含多个关键字或条件

### 把别人的结果，当成数据，直接拿过来用。​​# 求出学生的平均年龄SELECT AVG(`age`) FROM `stu_details`;​#查找出大于平均年龄的数据mysql> SELECT * FROM `stu_details` WHERE `age`>18.25;​#将平均数的SQL语句作为子查询放入上一条语句中mysql> SELECT * FROM `stu_details` WHERE `age`>(SELECT AVG(`age`) FROM `stu_details`);​​​##需求： 要查找，计算机和外国语 的学生​select id from department where name in ('计算机','外国语');​​​select * from student where dept_id in (select id from department where name in ('计算机','外国语') );​​# in ， not inselect * from student where dept_id not in (1,3);select * from student where dept_id in (1,3);​​
selectmysqlmysql创建数据库python前端前端
点赞
收藏
评论区
推荐文章
blmius blmius
•
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时，报错：原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等，这样可以更容易地在不同的环境中使用MySQL。全局s
4244
0
0
helloworld_34035044 helloworld_34035044
•
2年前
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时，如何在新增行里自动增加id呢？能新增整数排序id吗？目前可以在新增行里自动增加id，但只能用uuid函数增加UUID编码，不能新增整数排序id。uuid函数说明：获取一个UUID，可以在填报表中用来创建数据ID语法：uuid()或uuid(sep)参数说明：sep布尔值，生成的uuid中是否包含分隔符''，缺省为
1206
0
0
待兔 待兔
•
5个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
1135
0
0
Jacquelyn38 Jacquelyn38
•
3年前
2020年前端实用代码段，为你的工作保驾护航
有空的时候，自己总结了几个代码段，在开发中也经常使用，谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
2445
0
0
Stella981 Stella981
•
3年前
Python3：sqlalchemy对mysql数据库操作，非sql语句
Python3：sqlalchemy对mysql数据库操作，非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
1179
0
0
Wesley13 Wesley13
•
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败，请联系管理员！');中国在东8区所以加8方法二：selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
1381
0
0
Wesley13 Wesley13
•
3年前
mysql数据库查询操作
\mysql数据库\知识要点：1\.单表查询2\.子查询3\.联表查询4\.事务在进行查询之前，我们要先建好关系表，并往数据表中插入些数据。为查询操作做好准备。\五张关系表的创建：\\\mysql创建并进入数据库：mysqlCREATEDATABASE\
847
0
0
Wesley13 Wesley13
•
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
1585
0
0
京东云开发者 京东云开发者
•
2年前
为什么mysql不推荐使用雪花ID作为主键
作者：毛辰飞背景在mysql中设计表的时候，mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一)，而是推荐连续自增的主键id，官方的推荐是auto_increment，那么为什么不建议采用uuid，使用uuid究
1088
0
0
Python进阶者 Python进阶者
•
11个月前
Excel中这日期老是出来00：00：00，怎么用Pandas把这个去除
大家好，我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下：这日期老是出来00：00：00，怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下：pd.toexcel之前把这
558
0
0
Stella981
Stella981
Lv1
接天莲叶无穷碧，映日荷花别样红。
文章
1.7w
粉丝
42
获赞
28
热门文章
OpenVPN下载、安装、配置及使用详解
GitHub神器，一个可以白嫖全网无损音乐的神器
OpenWrt 路由器过滤广告的N种方法
Python—执行系统命令的四种方法（os.system、os.popen、commands、subprocess）
SS端加密以及obfs混淆