MySql操作

壹如年少遲夏歸

于 2022-11-26 15:28:30 发布

阅读量98

点赞数

分类专栏：数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_32444571/article/details/128052386

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

SQL

Structured Query LanguageSQL是结构化查询语言，是一种用来操作RDBMS的数据库语言，当前关系型数据库都支持使用SQL语言进行操作,也就是说可以通过 SQL 操作 oracle,sql server,mysql,sqlite 等等所有的关系型的数据库
分类
- DQL：数据查询语言，用于对数据进行查询，如select
- DML：数据操作语言，对数据进行增加、修改、删除，如insert、udpate、delete
- TPL：事务处理语言，对事务进行处理，包括begin transaction、commit、rollback
- DCL：数据控制语言，进行授权与权限回收，如grant、revoke
- DDL：数据定义语言，进行数据库、表的管理等，如create、drop
- CCL：指针控制语言，通过控制指针完成表的操作，如declare cursor

MySQL

服务端安装

# E: Sub-process /usr/bin/dpkg returned an error code (1)解决办法
cd /var/lib/dpkg/
sudo mv info/ info_bak          # 现将info文件夹更名
sudo mkdir info                 # 再新建一个新的info文件夹
sudo apt-get update             # 更新
sudo apt-get -f install         # 修复
sudo mv info/* info_bak/        # 执行完上一步操作后会在新的info文件夹下生成一些文件，现将这些文件全部移到info_bak文件夹下
sudo rm -rf info                # 把自己新建的info文件夹删掉
sudo mv info_bak info           # 把以前的info文件夹重新改回名

#Failed to start mysql.service: Unit mysql.service is masked.
systemctl unmask mysql.service

linux安装
- sudo apt-get install mysql-server
启动服务
- sudo service mysql start
查看进程是否存在mysql服务
- ps ajx|grep mysql
停止服务
- sudo service mysql stop
重启服务
- sudo service mysql restart

配置

目录为/etc/mysql/mysql.cnf

主要配置

bind-address  	# 表示服务器绑定的ip，默认为127.0.0.1

port			# 表示端口，默认为3306

datadir			# 表示数据库目录，默认为/var/lib/mysql

general_log_file# 表示普通日志，默认为/var/log/mysql/mysql.log

log_error		# 表示错误日志，默认为/var/log/mysql/error.log

命令行客户端
- 安装
  - sudo apt-get install mysql-client
- 查看文档
  - mysql --help
- 最基本的连接
  - 查看账户密码 sudo cat /etc/mysql/debian.cnf
  - mysql -u root -p
- 退出
  - ctrl + d quit exit

卸载

sudo apt purge mysql-server

# 删除MySQL数据库文件和日志
ls /etc/mysql
sudo ls/var/lib/mysql
sudo rm -r /etc/mysql /var/lib/mysql

# 如果你已经启用了MySQL的日志记录，请确保你也删除了日志文件。
sudo rm -r /var/log/mysql

# 移除不需要的包
sudo apt autoremove

# 卸载以往版本
sudo apt-get remove --purge mysql-\*
sudo apt-get autoremove --purge mysql-server
sudo apt-get remove mysql-common
# 清楚数据 缓存
dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P
# MySQL的剩余依赖项 
dpkg --list|grep mysql
# 继续删除剩余依赖项
sudo apt-get autoremove --purge mysql-apt-config
# 更新软件源库
sudo apt-get update

数据完整性

一个数据库就是一个完整的业务单元，可以包含多张表，数据被存储在表中
在表中为了更加准确的存储数据，保证数据的正确有效，可以在创建表的时候，为表添加一些强制性的验证，包括数据字段的类型、约束

数据类型

使用数据类型的原则是：够用就行，尽量使用取值范围小的，而不用大的，这样可以更多的节省存储空间
常用数据类型
- 整数 int, bit
- 小数 decimal
- 字符串 vachar char
- 日期类型 date time datetime
- 枚举类型 enum
注意
- decimal 表示浮点数；如decimal(5, 2) 表示共存 5位数，小数占 2位
- char 表示固定长度的字符串；如char(3)，如果填充ab会补一个空格 'ab ’
- varchar 表示变长字符串；如varchar(3)，如果填充ab就会存储 ‘ab’
- 字符串text 表示存储大文本，当字符大于4000时使用
- 对于图片、音频、视频等文件，不存储在数据库中，而是上传到服务器上，然后在表中存储这个文件的保存路径

约束

主键primary key：物理上存储的顺序
非空not null：此字段不允许填写空值
惟一unique：此字段的值不允许重复
默认default：当不填写此值时会使用默认值，如果填写时以填写为准
外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常
说明：虽然外键约束可以保证数据的有效性，但是在进行数据的crud（增加、修改、删除、查询）时，都会降低数据库的性能，所以不推荐使用，那么数据的有效性怎么保证呢？答：可以在逻辑层进行控制

数值类型(常用)

类型	字节大小	有符号范围(Signed)	无符号范围(Unsigned)
TINYINT	1	-128 ~ 127	0 ~ 255
SMALLINT	2	-32768 ~ 32767	0 ~ 65535
MEDIUMINT	3	-8388608 ~ 8388607	0 ~ 16777215
INT/INTEGER	4	-2147483648 ~2147483647	0 ~ 4294967295
BIGINT	8	-9223372036854775808 ~ 9223372036854775807	0 ~ 18446744073709551615

字符串

类型	字节大小	示例
CHAR	0-255	类型:char(3) 输入 ‘ab’, 实际存储为’ab ‘, 输入’abcd’ 实际存储为 ‘abc’
VARCHAR	0-255	类型:varchar(3) 输 ‘ab’,实际存储为’ab’, 输入’abcd’,实际存储为’abc’
TEXT	0-65535	大文本

日期时间类型

类型	字节大小	示例
DATE	4	‘2020-01-01’
TIME	3	‘12:29:59’
DATETIME	8	‘2020-01-01 12:29:59’
YEAR	1	‘2017’
TIMESTAMP	4	‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-01 00:00:01’ UTC

数据库如果开启root远程访问
- root权限登录
  - mysql -u root -p;
- 选择mysql
  - use mysql;
  - select host, user, authentication_string, plugin from user;
- 指定root，修改root账号密码，修改host值
  - update user set password=123456 where user='root' and host='%'; 老语句
  - ALTER USER 'root'@'localhost' identified with mysql_native_password BY '123456';
- 刷新mysql的系统权限相关表
  - flush privileges;
- 退出
  - quit;
- 重启mysql
  - service mysql restart

数据库操作

查看所有数据库
- show databases;
使用数据库
- use 数据库名;
查看当前使用数据库
- select database();
创建数据库
- create database 数据库名称 charset=utf8;
删除数据库
- drop database 数据库名;
清屏
- system clear;
查看版本
- show version();
查看时间
- show now();

数据表操作

查看所有表
- show tables;
查看表结构
- desc 表名;

创建表 auto_increment 表示自动增长

CREATE TABLE table_name(
	column1 datatype contrai,
    column2 datatype,
    ...
    columnN datatype,
    PRIMARY KEY(one or more columns)
);

/*创建班级*/
create table classes(
	id int unsigned auto_increment primary key not null,
    name varchar(10)
);
/*创建学生表*/
create table students(
	id int unsigned primary key auto_increment not null,
    name varchar(20) default "",
    age tinyint unsigned default 0,
    height decimal(5, 2),
    gender enum("男", "女", "人妖", "保密"),
    cls_id int unsigned default 0
);

修改表-添加字段
- alter table 表名 add 列名类型;
- alter table students add brithday datetime;
修改表-添加字段重命名
- alter table 表名 change 原名新名类型及约束;
- alter table students change brithday birth datatime not null;
修改表-添加字段
- alter table 表名 modify 列名类型及约束；
- alter table students modify birth date not null;
修改表-删除字段
- alter table 表名 drop 列名；
- alter table students drop birthday;
删除表
- drop table 表名；
- drop table students;
查看表的创建语句
- show create table 表名；
- show create table classes;

增删改查

查询

/*查询所有*/
select * from 表名；

/*查询指定列*/
select col1, col2, ... from 表名;

增加

格式:INSERT [INTO] tb**name [(col**name,…)] {VALUES | VALUE} ({expr | DEFAULT},…),(…),…

说明：主键列是自动增长，但是在全列插入时需要占位，通常使用0或者 default 或者 null 来占位，插入成功后以实际数据为准

/*全列插入：值的顺序与表中字段的顺序对应*/
insert into 表名 values(...);

/*部分插入：值的顺序与给出的列顺序对应*/
insert into 表名(col1, ...) values(val1,...);

/*一次可以向表中插入一行数据*/
insert into 表名 values(...),(...)...;
insert into classes values(0,'python1'),(0,'python2');
insert into students(name) values('杨康'),('杨过'),('小龙女');

修改

格式: UPDATE tbname SET col1={expr1|DEFAULT} [,col2={expr2|default}]…[where 条件判断]

update 表名 set col1=val1, col2=val2...where 条件;
update students set gender=0,hometown='北京' where id=5;

删除

DELETE FROM tbname [where 条件判断]

delete from 表名 where 条件;
delete from students where id=5;

逻辑删除，本质就是修改操作

update students set isdelete=1 where id=1;

备份

命令行运行mysqldump 命令

mysqldump -uroot -p 数据库名 > python.sql;

还原

创建数据库

# 命令行
mysql -u root -p testDB < /home/bak.sql

数据库设计

关系型数据库建议在E-R模型的基础上，我们需要根据产品经理的设计策划，抽取出来模型与关系，制定出表结构，这是项目开始的第一步
在开发中有很多设计数据库的软件，常用的如power designer，db desinger等，这些软件可以直观的看到实体及实体间的关系
设计数据库，可能是由专门的数据库设计人员完成，也可能是由开发组成员完成，一般是项目经理带领组员来完成
现阶段不需要独立完成数据库设计，但是要注意积累一些这方面的经验

三范式

经过研究和对使用中问题的总结，对于设计数据库提出了一些规范，这些规范被称为范式(Normal Form)
目前有迹可寻的共有8种范式，一般需要遵守3范式即可
◆ 第一范式（1NF）：强调的是列的原子性，即列不能够再分成其他几列

考虑这样一个表：【联系人】（姓名，性别，电话）如果在实际场景中，一个联系人有家庭电话和公司电话，那么这种表结构设计就没有达到 1NF。要符合 1NF 我们只需把列（电话）拆分，即：【联系人】（姓名，性别，家庭电话，公司电话）。1NF 很好辨别，但是 2NF 和 3NF 就容易搞混淆。
◆ 第二范式（2NF）：首先是 1NF，另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。

考虑一个订单明细表：【OrderDetail】（OrderID，ProductID，UnitPrice，Discount，Quantity，ProductName）。因为我们知道在一个订单中可以订购多种产品，所以单单一个 OrderID 是不足以成为主键的，主键应该是（OrderID，ProductID）。显而易见 Discount（折扣），Quantity（数量）完全依赖（取决）于主键（OderID，ProductID），而 UnitPrice，ProductName 只依赖于 ProductID。所以 OrderDetail 表不符合 2NF。不符合 2NF 的设计容易产生冗余数据。

可以把【OrderDetail】表拆分为【OrderDetail】（OrderID，ProductID，Discount，Quantity）和【Product】（ProductID，UnitPrice，ProductName）来消除原订单表中UnitPrice，ProductName多次重复的情况。
◆ 第三范式（3NF）：首先是 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

考虑一个订单表【Order】（OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity）主键是（OrderID）。其中 OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity 等非主键列都完全依赖于主键（OrderID），所以符合 2NF。不过问题是 CustomerName，CustomerAddr，CustomerCity 直接依赖的是 CustomerID（非主键列），而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合 3NF。通过拆分【Order】为【Order】（OrderID，OrderDate，CustomerID）和【Customer】（CustomerID，CustomerName，CustomerAddr，CustomerCity）从而达到 3NF。 *第二范式（2NF）和第三范式（3NF）的概念很容易混淆，区分它们的关键点在于，2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分；3NF：非主键列是直接依赖于主键，还是直接依赖于非主键列。

1NF

id	姓名	系名	系主任	课名	分数
201701	小明	中文系	大明	高数	60
201701	小明	中文系	大明	英语	99
201701	小明	中文系	大明	编程	99
201702	小华	计算机系	大华	高数	80
201702	小华	计算机系	大华	英语	55
201702	小华	计算机系	大华	编程	99

2NF

id 课名分数
201701 高数 60
201701 英语 99
201701 编程 99
201702 高数 80
201702 英语 55
201702 编程 99

id 姓名系名系主任
201701 小明中文系大明
201702 小华计算机系大华
3NF

id 课名分数
201701 高数 60
201701 英语 99
201701 编程 99
201702 高数 80
201702 英语 55
201702 编程 99

id 姓名系名
201701 小明中文系
201702 小华计算机系
- 添加系名系主任
系名系主任
中文系大明
计算机系大华

id	课名	分数
201701	高数	60
201701	英语	99
201701	编程	99
201702	高数	80
201702	英语	55
201702	编程	99

id	姓名	系名	系主任
201701	小明	中文系	大明
201702	小华	计算机系	大华

id	课名	分数
201701	高数	60
201701	英语	99
201701	编程	99
201702	高数	80
201702	英语	55
201702	编程	99

id	姓名	系名
201701	小明	中文系
201702	小华	计算机系

系名	系主任
中文系	大明
计算机系	大华

E-R模型

E表示entry，实体，设计实体就像定义一个类一样，指定从哪些方面描述对象，一个实体转换为数据库中的一个表
R表示relationship，关系，关系描述两个实体之间的对应规则，关系的类型包括包括一对一、一对多、多对多
关系也是一种数据，需要通过一个字段存储在表中

逻辑删除

对于重要数据，并不希望物理删除，一旦删除，数据无法找回
删除方案：设置isDelete的列，类型为bit，表示逻辑删除，默认值为0
对于非重要数据，可以进行物理删除
数据的重要性，要根据实际开发决定

MySQL查询

查询

创建数据库、数据表

-- 创建数据库
create database python_test_1 charset=utf8;

-- 使用数据库
use python_test_1;

-- students表
create table students(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default '',
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum('男','女','中性','保密') default '保密',
    cls_id int unsigned default 0,
    is_delete bit default 0
);

-- classes表
create table classes (
    id int unsigned auto_increment primary key not null,
    name varchar(30) not null
);

插入数据

-- 向students表中插入数据
insert into students values
(0,'小明',18,180.00,2,1,0),
(0,'小月月',18,180.00,2,2,1),
(0,'彭于晏',29,185.00,1,1,0),
(0,'刘德华',59,175.00,1,2,1),
(0,'黄蓉',38,160.00,2,1,0),
(0,'凤姐',28,150.00,4,2,1),
(0,'王祖贤',18,172.00,2,1,1),
(0,'周杰伦',36,NULL,1,1,0),
(0,'程坤',27,181.00,1,2,0),
(0,'刘亦菲',25,166.00,2,2,0),
(0,'金星',33,162.00,3,3,1),
(0,'静香',12,180.00,2,4,0),
(0,'郭靖',12,170.00,1,4,0),
(0,'周杰',34,176.00,2,5,0);

-- 向classes表中插入数据
insert into classes values (0, "python_01期"), (0, "python_02期");

查询字段

select * from 表名;
select 列1,列2,... from 表名;

使用 as 给字段起别名

select id as 标号, name as 名字, gender as 性别 from students;

去重
```
select distinct 列1,... from 表名;
```

条件
- 使用where子句对表中的数据筛选(支持多种运算符)，结果为true的行会出现在结果集中
  - 比较运算符
  - 逻辑运算符
  - 模糊查询
  - 范围查询
  - 空判断
```
select * from 表名 where 条件;
```
- 比较运算符
  - 等于: =
  - 大于: >
  - 大于等于: >=
  - 小于: <
  - 小于等于: <=
  - 不等于: != 或 <>
- 逻辑运算符
- and
- or
- nots
- 模糊查询
- like
- %表示任意多个任意字符
- _表示一个任意字符
```
select * from student where name like '黄%'or name like "_靖"
```
- 范围查询
  - in表示在一个非连续的范围内
  - between … and …表示在一个连续的范围内
```
-- 查询编号是1或3或8的学生
select * from student where id in(1,3,8);

-- 查询编号为3至8的学生
select * from student where id between 3 and 8 and gender='男';
```
- 空判断
  - 注意：null与’'是不同的
  - 判空is null
```
-- 查询没有填写身高的学生
select * from students where height is null;
```
- 优先级
  - 优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符
排序
- 说明
  - 将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推
  - 默认按照列值从小到大排列（asc）
  - asc从小到大排列，即升序
  - desc从大到小排序，即降序
```
select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]

-- 查询未删除男生信息，按学号降序
select * from student where gender='男' and is_delete=0 order by id desc;
```

聚合函数

总数
- count(*)表示计算总行数，括号中写星与列名，结果是相同的
- 聚合函数不能在 where 中使用
```
-- 查询学生总数
select count(*) from students;
```

最大值、最小值

max(列)表示求此列的最大值、min(列)

-- 查询女生的编号最大值
select max(id) from students where gender='女';

求和

sum(列)表示求此列的和

-- 查询男生的总年龄
select sum(age) from students where gender=1;

平均值

avg(列)表示求此列的平均值

-- 查询未删除女生的编号平均值
select avg(id) from students where is_delete=0 and gender=2;

分组

group by

将查询结果按照1个或多个字段进行分组，字段值相同的为一组
group by可用于单个字段分组，也可用于多个字段分组

select gender from student group by gender;

-- MySQL有any_value(field)函数，它主要的作用就是抑制ONLY_FULL_GROUP_BY值被拒绝。
select any_value(name),cate_name,max(price),min(price),avg(price),count(*) as 数量 from goods group by cate_name

group by + group_concat()

group_concat(字段名)可以作为一个输出字段来使用
表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合

select gender, group_concat(name) from student group by gender;

+--------+-----------------------------------------------------------+
| gender | group_concat(name)                                        |
+--------+-----------------------------------------------------------+
| 男     | 彭于晏,刘德华,周杰伦,程坤,郭靖                            |
| 女     | 小明,小月月,黄蓉,王祖贤,刘亦菲,静香,周杰                  |
| 中性   | 金星                                                      |
| 保密   | 凤姐                                                      |
+--------+-----------------------------------------------------------+

select gender, group_concat(id, name) from student group by gender;
+--------+---------------------------------------------------------------------+
| gender | group_concat(id, name)                                              |
+--------+---------------------------------------------------------------------+
| 男     | 3彭于晏,4刘德华,8周杰伦,9程坤,13郭靖                                |
| 女     | 1小明,2小月月,5黄蓉,7王祖贤,10刘亦菲,12静香,14周杰                  |
| 中性   | 11金星                                                              |
| 保密   | 6凤姐                                                               |
+--------+---------------------------------------------------------------------+

group by + 集合函数

通过group_concat()的启发，我们既然可以统计出每个分组的某字段的值的集合，那么我们也可以通过集合函数来对这个值的集合做一些操作

-- 分别统计性别为男/女的人年龄平均值
select gender, avg(age) from student group by gender;

+--------+----------+
| gender | avg(age) |
+--------+----------+
| 女     |  23.2857 |
| 男     |  32.6000 |
| 保密   |  28.0000 |
| 中性   |  33.0000 |
+--------+----------+

-- 分别统计性别为男/女的人的个数
select gender, count(*) from student group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 女     |        7 |
| 男     |        5 |
| 保密   |        1 |
| 中性   |        1 |
+--------+----------+

group by + having
- having 条件表达式：用来分组查询后指定一些条件来输出查询结果
- having作用和where一样，但having只能用于group by

select gender, count(*) from student group by gender having count(*) > 2;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 女     |        7 |
| 男     |        5 |
+--------+----------+

group by + with rollup
- with rollup的作用是：在最后新增一行，来记录当前列里所有记录的总和

select gender, count(*) from student group by gender with rollup;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男     |        5 |
| 女     |        7 |
| 中性   |        1 |
| 保密   |        1 |
| NULL   |       14 |
+--------+----------+

select gender, group_concat(age) from student group by gender with rollup;
+--------+-------------------------------------------+
| gender | group_concat(age)                         |
+--------+-------------------------------------------+
| 男     | 29,59,36,27,12                            |
| 女     | 18,18,38,18,25,12,34                      |
| 中性   | 33                                        |
| 保密   | 28                                        |
| NULL   | 29,59,36,27,12,18,18,38,18,25,12,34,33,28 |
+--------+-------------------------------------------+

获取部分行
```
select * from 表名 limit start,count

-- 查询前3行男生信息
select * from student where gender='男' limit 0,3;
```
- 分页
  - 已知：每页显示m条数据，当前显示第n页
  - 求总页数：此段逻辑后面会在python中实现
    - 查询总条数p1
    - 使用p1除以m得到p2
    - 如果整除则p2为总数页
    - 如果不整除则p2+1为总页数
  - 求第n页的数据
```
select * from students where is_delete=0 limit (n-1)*m,m
```
连接查询
- 内连接查询：查询的结果为两个表匹配到的数据
- 右连接查询：查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充
- 左连接查询：查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充

在这里插入图片描述

语法

select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列

使用内连接查询班级表与学生表

select * from student inner join classes on student.cls_id = classes.id;
+----+-----------+------+--------+--------+--------+----------------------+----+--------------+
| id | name      | age  | height | gender | cls_id | is_delete            | id | name         |
+----+-----------+------+--------+--------+--------+----------------------+----+--------------+
|  1 | 小明      |   18 | 180.00 | 女     |      1 | 0x00                 |  1 | python_01期  |
|  2 | 小月月    |   18 | 180.00 | 女     |      2 | 0x01                 |  2 | python_02期  |
|  3 | 彭于晏    |   29 | 185.00 | 男     |      1 | 0x00                 |  1 | python_01期  |
|  4 | 刘德华    |   59 | 175.00 | 男     |      2 | 0x01                 |  2 | python_02期  |
|  5 | 黄蓉      |   38 | 160.00 | 女     |      1 | 0x00                 |  1 | python_01期  |
|  6 | 凤姐      |   28 | 150.00 | 保密   |      2 | 0x01                 |  2 | python_02期  |
|  7 | 王祖贤    |   18 | 172.00 | 女     |      1 | 0x01                 |  1 | python_01期  |
|  8 | 周杰伦    |   36 |   NULL | 男     |      1 | 0x00                 |  1 | python_01期  |
|  9 | 程坤      |   27 | 181.00 | 男     |      2 | 0x00                 |  2 | python_02期  |
| 10 | 刘亦菲    |   25 | 166.00 | 女     |      2 | 0x00                 |  2 | python_02期  |
+----+-----------+------+--------+--------+--------+----------------------+----+--------------+

使用左连接查询班级表与学生表

select * from student as s left join classes as c on s.cls_id = c.id;
+----+-----------+------+--------+--------+--------+----------------------+------+--------------+
| id | name      | age  | height | gender | cls_id | is_delete            | id   | name         |
+----+-----------+------+--------+--------+--------+----------------------+------+--------------+
|  1 | 小明      |   18 | 180.00 | 女     |      1 | 0x00                 |    1 | python_01期  |
|  2 | 小月月    |   18 | 180.00 | 女     |      2 | 0x01                 |    2 | python_02期  |
|  3 | 彭于晏    |   29 | 185.00 | 男     |      1 | 0x00                 |    1 | python_01期  |
|  4 | 刘德华    |   59 | 175.00 | 男     |      2 | 0x01                 |    2 | python_02期  |
|  5 | 黄蓉      |   38 | 160.00 | 女     |      1 | 0x00                 |    1 | python_01期  |
|  6 | 凤姐      |   28 | 150.00 | 保密   |      2 | 0x01                 |    2 | python_02期  |
|  7 | 王祖贤    |   18 | 172.00 | 女     |      1 | 0x01                 |    1 | python_01期  |
|  8 | 周杰伦    |   36 |   NULL | 男     |      1 | 0x00                 |    1 | python_01期  |
|  9 | 程坤      |   27 | 181.00 | 男     |      2 | 0x00                 |    2 | python_02期  |
| 10 | 刘亦菲    |   25 | 166.00 | 女     |      2 | 0x00                 |    2 | python_02期  |
| 11 | 金星      |   33 | 162.00 | 中性   |      3 | 0x01                 | NULL | NULL         |
| 12 | 静香      |   12 | 180.00 | 女     |      4 | 0x00                 | NULL | NULL         |
| 13 | 郭靖      |   12 | 170.00 | 男     |      4 | 0x00                 | NULL | NULL         |
| 14 | 周杰      |   34 | 176.00 | 女     |      5 | 0x00                 | NULL | NULL         |
+----+-----------+------+--------+--------+--------+----------------------+------+--------------+

使用右连接查询班级表与学生表

select * from student as s right join classes as c on s.cls_id=c.id;
+------+-----------+------+--------+--------+--------+----------------------+----+--------------+
| id   | name      | age  | height | gender | cls_id | is_delete            | id | name         |
+------+-----------+------+--------+--------+--------+----------------------+----+--------------+
|    8 | 周杰伦    |   36 |   NULL | 男     |      1 | 0x00                 |  1 | python_01期  |
|    7 | 王祖贤    |   18 | 172.00 | 女     |      1 | 0x01                 |  1 | python_01期  |
|    5 | 黄蓉      |   38 | 160.00 | 女     |      1 | 0x00                 |  1 | python_01期  |
|    3 | 彭于晏    |   29 | 185.00 | 男     |      1 | 0x00                 |  1 | python_01期  |
|    1 | 小明      |   18 | 180.00 | 女     |      1 | 0x00                 |  1 | python_01期  |
|   10 | 刘亦菲    |   25 | 166.00 | 女     |      2 | 0x00                 |  2 | python_02期  |
|    9 | 程坤      |   27 | 181.00 | 男     |      2 | 0x00                 |  2 | python_02期  |
|    6 | 凤姐      |   28 | 150.00 | 保密   |      2 | 0x01                 |  2 | python_02期  |
|    4 | 刘德华    |   59 | 175.00 | 男     |      2 | 0x01                 |  2 | python_02期  |
|    2 | 小月月    |   18 | 180.00 | 女     |      2 | 0x01                 |  2 | python_02期  |
+------+-----------+------+--------+--------+--------+----------------------+----+--------------+

select s.name, c.name from student as s inner join classes as c on s.cls_id=c.id;
+-----------+--------------+
| name      | name         |
+-----------+--------------+
| 小明      | python_01期  |
| 小月月    | python_02期  |
| 彭于晏    | python_01期  |
| 刘德华    | python_02期  |
| 黄蓉      | python_01期  |
| 凤姐      | python_02期  |
| 王祖贤    | python_01期  |
| 周杰伦    | python_01期  |
| 程坤      | python_02期  |
| 刘亦菲    | python_02期  |
+-----------+--------------+

自关联
- 设计省信息的表结构province
  - id
  - ptitle
- 设计市信息的表结构citys
  - id
  - ctitle
  - proid
- 存储的都是地区信息，而且每种信息的数据量有限，没必要增加一个新表，或者将来还要存储区、乡镇信息，都增加新表的开销太大
  - 定义表areas，结构如下
    - id
    - atitle
    - pid
- 说明
  - 因为省没有所属的省份，所以可以填写为null
  - 城市所属的省份pid，填写省所对应的编号id
  - 这就是自关联，表中的某一列，关联了这个表中的另外一列，但是它们的业务逻辑含义是不一样的，城市信息的pid引用的是省信息的id
  - 在这个表中，结构不变，可以添加区县、乡镇街道、村社区等信息
- ```
-- 查询省的名称为“山西省”的所有城市
select city.* from area as city
inner join area as province on city.pid=province.aid
where province.atitle='山西省';
```
- ```
-- 查询市的名称为“广州市”的所有区县
select dis.* from areas as dis
inner join areas as city on city.aid=dis.pid
where city.atitle='广州市';
```
子查询
- 子查询
  
  在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句
- 主查询和子查询的关系
  - 子查询是嵌入到主查询中
  - 子查询是辅助主查询的,要么充当条件,要么充当数据源
  - 子查询是可以独立存在的语句,是一条完整的 select 语句
- 子查询分类
  - 标量子查询: 子查询返回的结果是一个数据(一行一列)
  - 列子查询: 返回的结果是一列(一列多行)
  - 行子查询: 返回的结果是一行(一行多列)
- 标量子查询
  - 查询班级学生平均年龄
  - 查询大于平均年龄的学生
```
select * from student where age > (select avg(age) from student);
```
- 列级子查询
  - 查询还有学生在班的所有班级名字
  - 1. 找出学生表中所有的班级 id
    2. 找出班级表中对应的名字
```
select name from classes where id in (select cls_id from student);
```
- 行级子查询
  - 查找班级年龄最大,身高最高的学生
  - 行元素: 将多个字段合成一个行元素,在行级子查询中会使用到行元素
```
-- 并且
select * from student where (height, age)=(select max(height), max(age) from student)
-- 或者
select * from student where height=(select max(height)from student) or age=(select max(age)from student);
```
- 子查询中特定关键字使用
  - in 范围
    - 格式: 主查询 where 条件 in (列子查询)

SQL语句强化

求电脑产品的平均价格，并且保留两位小数
```
select round(avg(price),2) from goods;
```

查询所有价格大于平均价格的商品，并且按价格降序排序

select * from goods where price > (select avg(price)from goods) order by price desc;

查询类型cate_name为 ‘超极本’ 的商品名称、价格
```
select name,price from goods where cate_name="超级本";
```

显示商品的种类

select cate_name from goods group by cate_name;

select distinct cate_name from goods;

显示每种商品的平均价格

select cate_name, avg(price) from goods group by cate_name;

查询每种类型的商品中最贵、最便宜、平均价、数量

select cate_name,max(price),min(price),avg(price),count(*) from goods group by cate_name;

将分组结果写入到goods_cates 数据表

insert into goods_cates(name) select cate_name from goods group by cate_name;

通过goods_cates数据表来更新goods表

update goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name=c.id;

通过create…select来创建数据表并且同时写入记录,一步到位

-- 注意: 需要对brand_name 用as起别名，否则name字段就没有值
create table goods_brands (
    id int unsigned primary key auto_increment,
    name varchar(40) not null) select brand_name as name from goods group by brand_name;

eg: 查询每种类型中最贵的电脑信息

-- 按cate_name筛选每一种类型的信息组成新的视图 内联原来的表 加上on条件
select * from goods
inner join 
    (
        select
        cate_name, 
        max(price) as max_price,  -- 最贵的
        min(price) as min_price,  -- 最便宜的 
        avg(price) as avg_price, 
        count(*) from goods group by cate_name
    ) as goods_new_info 
on goods.cate_name=goods_new_info.cate_name and goods.price=goods_new_info.max_price;

修改表结构

-- 查看goods
desc goods;
-- 通过alter table语句修改表结构
alter table goods 
change cate_name cate_id int unsigned not null,
change brand_name brand_id int unsigned not null;

外键(防止无效信息的插入)

查询所有商品的详细信息 (通过内连接)

select g.id,g.name,c.name,b.name,g.price from goods as g
inner join goods_cates as c on g.cate_id=c.id
inner join goods_brands as b on g.brand_id=b.id;

查询所有商品的详细信息 (通过左连接)

select g.id,g.name,c.name,b.name,g.price from goods as g
left join goods_cates as c on g.cate_id=c.id
left join goods_brands as b on g.brand_id=b.id;

如何防止无效信息的插入,就是可以在插入前判断类型或者品牌名称是否存在呢? 可以使用之前讲过的外键来解决
外键约束:对数据的有效性进行验证
关键字: foreign key,只有 innodb数据库引擎支持外键约束

对于已经存在的数据表如何更新外键约束

-- 给brand_id 添加外键约束成功
alter table goods add foreign key (brand_id) references goods_brands(id);
-- 给cate_id 添加外键失败
-- 会出现1452错误
-- 错误原因:已经添加了一个不存在的cate_id值12,因此需要先删除
alter table goods add foreign key (cate_id) references goods_cates(id);

在创建数据表的时候就设置外键约束

create table goods(
    id int primary key auto_increment not null,
    name varchar(40) default '',
    price decimal(5,2),
    cate_id int unsigned,
    brand_id int unsigned,
    is_show bit default 1,
    is_saleoff bit default 0,
    foreign key(cate_id) references goods_cates(id),
    foreign key(brand_id) references goods_brands(id)
);

取消外键约束

-- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称
show create table goods;
-- 获取名称之后就可以根据名称来删除外键约束
alter table goods drop foreign key 外键名称;

总结

SELECT select_expr [,select_expr,...] [      
      FROM tb_name
      [WHERE 条件判断]
      [GROUP BY {col_name | postion} [ASC | DESC], ...] 
      [HAVING WHERE 条件判断]
      [ORDER BY {col_name|expr|postion} [ASC | DESC], ...]
      [ LIMIT {[offset,]rowcount | row_count OFFSET offset}]
]

-- 完整的select语句
select distinct *
from 表名
where ....
group by ... having ...
order by ...
limit start,count

-- 执行顺序为
from 表名
on ...
where ....
group by ...
select distinct *
having ...
order by ...
limit start,count

python操作mysql

解决windows远程连接Ubuntu上MySql数据库

开放端口

默认开放端口就是3306 不需要做更改
下面几行命令 我没有用到，防火墙关闭状态也可以连接
#打开3306号端口：sudo ufw allow 3306
#关闭防火墙：sudo ufw disable
#开启防火墙：sudo ufw enable

允许其他IP访问

# 进入到 /etc/mysql/mysql.conf.d 目录    
# 一般mysql默认会安装在这个目录下。如果不是，可以使用sudo find / -name "mysql.cnf"来查找文件。
# 修改mysqld.cnf文件
# 修改权限
sudo chmod 644 /etc/mysql/mysql.conf.d/mysqld.cnf
把bind-address从127.0.0.0 注释掉

允许root用户在其他IP上登录

# 使用root用户登录mysql数据库：mysql -u root -p
# 进入到mysql数据库：use mysql;
# 查看user表的host和user数据
select host, user from user;    # 会发现root用户允许的host是localhost。
# 修改数据
update user set host='%' where user='root';    # 把user为root的host改为%，表示允许root用户在所有IP上登录。
# 重启mysql
sudo service mysql restart

connection对象
- 创建对象
```
conn = connect()
```
  - 参数host：连接的mysql主机，如果本机是’localhost’
  - 参数port：连接的mysql主机的端口，默认是3306
  - 参数database：数据库的名称
  - 参数user：连接的用户名
  - 参数password：连接的密码
  - 参数charset：通信采用的编码方式，推荐使用utf8
- 对象方法
  - close()关闭连接
  - commit()提交
  - cursor()返回Cursor对象，用于执行sql语句并获得结果
cursor对象
- 用于执行sql语句，使用频度最高的语句为select、insert、update、delete
- 获取Cursor对象：调用Connection对象的cursor()方法
```
cs1 = conn.cursor()
```
- 对象方法
  - close()关闭
  - execute(operation [, parameters ])执行语句，返回受影响的行数，主要用于执行insert、update、delete语句，也可以执行create、alter、drop等语句
  - fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组
  - fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回
- 对象属性
  - rowcount只读属性，表示最近一次execute()执行后受影响的行数
  - connection获得当前连接对象