初始mysql

写在前面

-- 1. 在实际的开发过程中，不加where条件是非常危险的行为，尤其是在update以及delete的时候.
-- 2. 尽量不要设置级联删除以及级联更新
-- 3. 不要随便删除数据库，以及表以及任何记录
-- 4. 尽量不要使用IDE可视化工具，尽量写原生SQL语句 ^_^

引入

设现在你已经是某大型互联网公司的高级程序员，让你写一个火车票购票系统，来hold住十一期间全国的购票需求，你怎么写？

由于在同一时段抢票的人数太多，所以你的程序不可能写在一台机器上，应该是多台机器一起分担用户的购票请求。

那么问题就来了，票务信息的数据存在哪里？存在文件里么？

如果存储在文件里，那么存储在哪一台机器上呢？是每台机器上都存储一份么？

首先，如果其中一台机器上卖出的票另外两台机器是感知不到的，

其次，是如果我们将数据和程序放在同一个机器上，如果程序和数据有一个出了问题都会导致整个服务不可用

最后，是操作文件，修改文件对python代码来说是一件很麻烦的事

基于上面这些问题，单纯的将数据存储在和程序同一台机器上的文件中是非常不明智的。

综上：所以便有了数据库，数据库很好的解决了这个问题

数据库的优势

.程序稳定性 ：这样任意一台服务所在的机器崩溃了都不会影响数据和另外的服务。
.数据一致性 ：所有的数据都存储在一起，所有的程序操作的数据都是统一的，就不会出现数据不一致的现象
.并发 ：数据库可以良好的支持并发，所有的程序操作数据库都是通过网络，而数据库本身支持并发的网络操作，不需要我们自己写socket
.效率 ：使用数据库对数据进行增删改查的效率要高出我们自己处理文件很多

什么是数据（Data）

描述事物的符号记录称为数据，描述事物的符号既可以是数字，也可以是文字、图片，图像、声音、语言等，数据由多种表现形式，它们都可以经过数字化后存入计算机

在计算机中描述一个事物，就需要抽取这一事物的典型特征，组成一条记录，就相当于文件里的一行内容，如：

1, 小满, 女, 3, 召唤师峡谷, 抢人头

单纯的一条记录并没有任何意义，如果我们按逗号作为分隔，依次定义各个字段的意思，相当于定义表的标题

id,name,sex,age,addr,hobby # 字段/列名
1,小满,女,3,召唤师峡谷,抢人头 # 数据

什么是数据库（DataBase，简称DB）

数据库即存放数据的仓库，只不过这个仓库是在计算机存储设备上，而且数据是按一定的格式存放的

过去人们将数据存放在文件柜里，现在数据量庞大，已经不再适用

数据库是长期存放在计算机内、有组织、可共享的数据集合。

数据库中的数据按一定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种 用户共享

什么是数据库管理系统（DataBase Management System 简称DBMS）

在了解了Data与DB的概念后，如何科学地组织和存储数据，如何高效获取和维护数据成了关键

这就用到了一个系统软件---数据库管理系统

如MySQL、Oracle、SQLite、Access、MS SQL Server

mysql主要用于大型门户，例如搜狗、新浪等，它主要的优势就是开放源代码，因为开放源代码这个数据库是免费的，他现在是甲骨文公司的产品。
oracle主要用于银行、铁路、飞机场等。该数据库功能强大，软件费用高。也是甲骨文公司的产品。
sql server是微软公司的产品，主要应用于大中型企业，如联想、方正等。

数据库管理员 DBA（Database Administrator）

数据库服务器、数据管理系统、数据库、表与记录的关系（重点）

记录：1, 小满, 女, 3, 召唤师峡谷, 抢人头（多个字段的信息组成一条记录，即文件中的一行内容）

表：userinfo,studentinfo,courseinfo（即文件）

数据库：db（即文件夹）

数据库管理系统：如mysql（是一个软件）

数据库服务器：一台计算机（对内存要求比较高）

总结：
数据库服务器-：运行数据库管理软件
数据库管理软件：管理-数据库
数据库：即文件夹，用来组织文件
表：即文件，用来存放多行内容/多条记录

初始MySQL

管理数据的工具有很多种，不止mysql一个。关于分类其实可以从各个纬度来进行划分，但是我们最常使用的分类还是根据他们存取数据的特点来划分的，主要分为关系型和非关系型。

可以简单的理解为，关系型数据库需要有表结构，非关系型数据库是key-value存储的，没有表结构

关系型：如sqllite，db2，oracle，access，sql server，MySQL，注意：sql语句通用
非关系型：mongodb，redis，memcache

各种服务器的端口

MySQL：3306
Redis： 6379
MongoDB：27017
Django： 8000
flask： 5000
fastapi： 3000

mysql

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。

MySQL是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。

mysql的下载

点击downloads

然后进入MySQL Community (GPL) Downloads »

安装和配置环境

安装

下载后是一个压缩包，解压到一个文件夹里面（路径不要带有中文)，比如磁盘根目录。

然后再下面新建一个data文件夹，即工作目录(主要用于存放mysql数据库以及数据的)。

配置环境变量

将解压后的文件夹的bin目录，添加到系统环境变量里面的path即可

创建配置文件

即刚解压的文件夹下创建一个.ini结尾的配置文件，比如mysql.ini

[mysqld]
; 设置3306端口
port=3306
; 设置mysql的安装目录  这里记得切换成你自己的
basedir="C:\WinApps\MySQL"
; 设置mysql数据库的数据的存放目录，就是前面手动创建的data目录  
; 这里记得切换成你自己的
datadir="C:\WinApps\MySQL\data"
; 允许最大连接数
max_connections=200
; 允许连接失败的次数。
max_connect_errors=10
; 服务端使用的字符集默认为utf8mb4
character-set-server=utf8mb4
; 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
; 默认使用“mysql_native_password”插件认证, mysql_native_password
default_authentication_plugin=mysql_native_password
[mysql]
; 设置mysql网络通信的默认字符集
default-character-set=utf8mb4
[client]
; 设置mysql客户端连接服务端时默认使用的端口
port=3306
; 设置mysql客户端的默认字符集
default-character-set=utf8mb4

初始化数据库

进入cmd使用下面的命令初始化数据库即可

初始化后记得最后的这串密码，先不要关闭窗口，后续可以修改密码

mysqld --initialize --console

注册到系统服务

使用管理员打开cmd终端，使用下面的命令将mysql注册到系统服务，如果成功了则会提示Service successfully installed.

mysqld --install

卸载系统服务

字面意思理解，一样使用管理员cmd运行下面的命令

此方法别乱用

mysqld --remove

启动MySQL

方式1：直接再服务上面，找到MySQL，然后右键启动。（不推荐）

方式2：通过终端，然后使用命令去启动（推荐）

net start MySQL

关闭MySQL

net stop MySQL

需要使用管理员模式操作

登录和退出

登录

mysql -uroot -p

注意：mysql与linux一样，在安装成功以后默认就存在了一个上帝一般的用户，叫root。

mysql -h 127.0.0.1 -P 3306 -uroot -p

退出

exit

修改用户名和密码

前提是已经登录进数据库了

'root' ：就是要修改密码的用户名

'localhost' ：表示允许用户在什么地址下可以使用密码登陆到数据库服务器，localhost表示本地登陆

'123456' ：就是新的密码了，注意，不要设置空密码！以后公司里面的密码一定要非常难记的才最好。

alter user 'root'@'localhost' identified by '123456';

跳过授权表重置密码

①关闭MySQL服务

net stop MySQL

②跳过授权表

先停止mysql服务，然后新建一个cmd窗口输入，输入 mysql -uroot -p 不输入密码直接进入

mysqld --skip-grant-tables

③无密码登入

输入密码的位置直接enter

mysql -uroot -p

④修改当前用户密码

alter user 'root'@'localhost' identified by '1314521';

⑤刷新权限数据

flush privileges;

⑥重新进入MySQL

关闭当前服务，以正常方式登入数据库

修改 配置文件 mysql.ini

在服务块内添加以下配置

[mysql]
# 不需要每次输入验证码即可登陆
user="root"
password=你的密码
default-character-set=utf8

MySQL的注释

--使用  --就算单行注释

/*
这样就算多行注释
*/

简单概述数据库

库（Database）
- 库是指在数据库管理系统中用于存储和组织数据的容器。
- 它可以视为一个文件夹，用于存放相关的数据表。 数据库中可以包含多个库，每个库可以包含多个表。

表（Table）
- 表是数据库中的一个基本组成单位，用于存储和展示数据。
- 表由行（记录）和列（字段）组成，每一行表示一个记录，每一列表示一个字段。
- 表可以看作是一个二维数据结构，类似于电子表格。

记录（Record）
- 记录也称作行，是表中的一个数据项或实体。
- 每一行都代表了一个完整的数据记录，其中包含了各个字段的具体数值或信息。
- 例如，在一个学生信息表中，每一行代表一个学生的具体信息。

表头（Header）
- 表头是表中的第一行，用于描述每个列字段的含义或名称。
- 表头通常包含了列的标签或标题，通过表头可以了解每个列字段所代表的意义，方便数据的理解和查询。

表单（Form）
- 表单是一种用来收集和展示数据的界面形式，常见于数据输入和显示的场景中。
- 表单通常包含了各种输入字段（例如文本框、下拉列表等）和相关的操作按钮，用户可以通过表单进行数据的录入、编辑和提交等操作。
- 表单的数据可以被存储到相应的表中，用于后续的数据处理和分析。

小结

库：相当于我们的文件夹
表：相当于我们的文件
记录：相当于我们一行行的数据
表头：表格的第一行字段
表单：表头对应的每一条数据

SQL语言的分类

数据定义语言：简称【DDL】(Data Definition Language)，用来定义数据库对象：数据库，表，列等。
关键字：create，alter，drop等

数据操作语言：简称【DML】(Data Manipulation Language)，用来对数据库中表的记录进行更新。关键
字：insert，delete，update等

数据控制语言：简称【DCL】(Data Control Language)，用来定义数据库的访问权限和安全级别，及创建
用户；关键字：grant等

数据查询语言：简称【DQL】(Data Query Language)，用来查询数据库中表的记录。关键字：select，
from，where等

mysql的工作流程

MySQL架构总共四层，在上图中以虚线作为划分。
　　首先，最上层的服务并不是MySQL独有的，大多数给予网络的客户端/服务器的工具或者服务都有类似的架构。比如：连接处理、授权认证、安全等。
　　第二层的架构包括大多数的MySQL的核心服务。包括：查询解析、分析、优化、缓存以及所有的内置函数（例如：日期、时间、数学和加密函数）。同时，所有的跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。

　　第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。服务器通过API和存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明化。存储引擎API包含十几个底层函数，用于执行“开始一个事务”等操作。但存储引擎一般不会去解析SQL（InnoDB会解析外键定义，因为其本身没有实现该功能），不同存储引擎之间也不会相互通信，而只是简单的响应上层的服务器请求。

　　第四层包含了文件系统，所有的表结构和数据以及用户操作的日志最终还是以文件的形式存储在硬盘上。

数据库的增删改查

若无特殊说明，案例中的data即代表数据库名或表名

定义多个字段的时候，最后一个字段后面不能加符号

--创建一个数据库data
--数据库名没有修改一说，要修改，干掉再来。
create database data;

--删除数据库(慎用)
drop database data;
--删除数据库的另外一种写法（先判断是否存在）不存在也不会报错
drop database if exists data; 

--查看所有的存储引擎
show engines;

/*
	在MySQL中存在主要的四个引擎
	Innodb
		是MySQL5.5版本之后的默认存储引擎，支持事务/行锁/外键 （即原子性操作）
	myisam
		是MySQL5.5版本之前的默认存储引擎速度比innodb快（全局索引），但是数据安全较弱
	memory内存引擎
		数据全部存放在内存中存储速度快，但是断电数据丢失
	blackhole黑洞引擎
		无论存什么都会立刻消失
*/

--查看数据库服务器中的所有的数据库:
show databases;

--查看某个数据库的定义的信息 data即数据库名
show create database data;

--查看建表语句
show create table data;

--终端查看，可以使用\G让结果显示效果更好一些
show create table data \G;

--查看正在使用的数据库
select database();

--查看数据库下面的所有表
show tables;

--查看表的结构和元数据信息，包括列名、数据类型、约束等。它的作用类似于SHOW COLUMNS FROM 表名语句
describe data;
--简写
desc data;

--切换数据库 data
use data;

--查询命令 user即表名, 工作的时候不要用* 效率比较低，平时练习的时候可以用
select * from user;
select name, age user;

--创建数据表的时候要指定引擎以及编码，现在新版本的已经是innodb引擎了，可以不用只当引擎，不过建议指定编码
--t1即表名
--10表示10个字符，如果超过了10个字符，也就只取10个字符
create table t1(id int, name char(10)) engine=innodb default charset=utf8;
create table t1(id int, name char(10)) engine=innodb default charset utf8;

--空值 null表示为空 not null表示不能为空
create tabel data(id int null, name char(10) not null);

/*
	创建一个自增的主键: 不能为空，不能重复
	
	auto_increment：表示自增
	primary key：表示约束 （不能重复 且不能为空） 加速查找 速度会更快
	
	需要注意的是：
	一个表里面只能有一个自增列auto_increment也只能有一个主键 primary key
	auto_increment 必须和 primary key 一起使用, 也需要结合int一起使用
	约定俗成：auto_increment 都会绑定成primary key
*/
create table data(id int not null auto_increment primary key);

-- 修改主键的值
alter table data auto_increment=20;

-- 修改表头
alter table player modify column name varchar(150);

-- 重命名
ALTER TABLE player CHANGE name nick_name VARCHAR(255);

-- 新增字段
alter table player add column login_time datetime;

-- 删除字段
alter table player drop column login_time;
INSERT INTO player(nick_name, level, exp) VALUES ('jack', 10, 100);

-- 设置编码
alter table player convert to character set utf8;

-- 插入多个数据
INSERT INTO player(nick_name) VALUES ('eva'),('jack'), ('小满');

-- 设定默认值
alter table player modify level int default 1;


-- 修改数据
update player set level=100 where id=45; 

-- 修改一列的数据
update player set exp=100;

-- 修改多列的数据，多个字段中间用逗号隔开
update player set level=50, exp=200;

-- 如果要修改默认的数据为NULL，不需要添加引号
update teacher set school=null,course=null where username='老夫子';

-- 修改多列数据指定条件
UPDATE teacher SET school='召唤师峡谷', course='javascript' WHERE username='小满';

-- 删除一列数据
alter table student drop column score;

-- 添加一列数据到指定位置
alter table student add column score decimal(5, 2) default null after course;

--清空表（清空内容 表还在）  
--如果这个表之前是有自增的，那么插入数据还会按照之前的继续往下自增
delete table from data;

--清空表（清空内容 表还在）
--如果这个表之前是有自增的，重置自增，从1开始
truncate table data;

--删除表
drop table data;

--根据条件去删除
--语法 delete from 表名 筛选条件;
delete from data where name='eva';

/*	补充：
	truncate table 速度也会比 delete table的速度快很多，数据量量越大会越明显。
*/

--插入数据可以简写，也可以指名道姓
insert into data(id, name) values(3, '小满');
--插入数据，按表头顺序来
insert into data values(3, '小满');

--更改数据
--语法updata 表名 set 需要更改的值 where 筛选条件;
updata data set name='阿珂' where id=88;

将A表的数据插入B表

-- 先创建一个空的A表
create table d (
    id int primary key auto_increment,
    name varchar(12),
    level int(4))
default char set  utf8;

-- 然后从B表（player）往A表里面插入数据
-- B表里面有209条数据，只插入前10条
insert into d (name, level) select name, level from player order by level desc limit 10;

-- 展示最终数据
mysql> select * from d;
+----+--------------+-------+
| id | name         | level |
+----+--------------+-------+
|  1 | 阿碧         |   100 |
|  2 | 姬小满       |   100 |
|  3 | 独孤求败     |   100 |
|  4 | 佟湘玉       |    99 |
|  5 | 风清扬       |    99 |
|  6 | 娜可露露     |    99 |
|  7 | 米莱狄       |    99 |
|  8 | 沈梦溪       |    98 |
|  9 | 钱德勒       |    98 |
| 10 | 陆展博       |    98 |
+----+--------------+-------+
10 rows in set (0.01 sec)

外键

外键也称之为外键约束: foreign key

外键: 外面的键, 一张表的一个字段(非主键)指向另外一个表的主键, 那么该字段就称之为外键.

外键所在的表称之为子表(附表); 外键所指向的主键所在的表称之为父表(主表)

-- 约束 null not null UNIQUE
-- 外键约束 一个表的外键，必须是另外一个表的主键

增加外键

一对一

-- user表
create table user (
    id int primary key auto_increment,
    name varchar(20) not null
)default char set utf8;

insert into user (name) values ('小满');
insert into user (name) values ('大乔');
insert into user (name) values ('兰陵王');
insert into user (name) values ('阿珂');
insert into user (name) values ('庄周');
insert into user (name) values ('小乔');
insert into user (name) values ('海月');
insert into user (name) values ('夏侯淳');


-- admin表
create table admin (
    id int primary key auto_increment,
    name varchar(20) not null ,
    password varchar(20) not null ,
    user_id int not null ,

    unique (user_id),
    constraint user_fk foreign key (user_id) references user(id)
) default char set utf8;

insert into admin (name, password, user_id) values ('小满', 112233, 1);
insert into admin (name, password, user_id) values ('大乔', 112233, 2);
insert into admin (name, password, user_id) values ('兰陵王', 112233, 3);

-- 此时的两张表
mysql> select * from user;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 小满      |
|  2 | 大乔      |
|  3 | 兰陵王    |
|  4 | 阿珂      |
|  5 | 庄周      |
|  6 | 小乔      |
|  7 | 海月      |
|  8 | 夏侯淳    |
+----+-----------+
8 rows in set (0.00 sec)

mysql> select * from admin;
+----+-----------+----------+---------+
| id | name      | password | user_id |
+----+-----------+----------+---------+
|  1 | 小满      | 112233   |       1 |
|  2 | 大乔      | 112233   |       2 |
|  3 | 兰陵王    | 112233   |       3 |
+----+-----------+----------+---------+
3 rows in set (0.00 sec)

-- 尝试往admin表里面插入一个不在外键约束id的值
mysql> insert into admin (name, password, user_id) values ('上官婉儿', 112233, 33);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`learn_fk`.`admin`, CONSTRAINT `user_fk` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`))

多对多

-- userinfo表
CREATE TABLE userinfo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(10),
    gender CHAR(1)
) DEFAULT CHARSET=utf8;

INSERT INTO userinfo (name, gender) VALUES ('小满', '女');
INSERT INTO userinfo (name, gender) VALUES ('大乔', '女');
INSERT INTO userinfo (name, gender) VALUES ('兰陵王', '男');
INSERT INTO userinfo (name, gender) VALUES ('阿珂', '女');
INSERT INTO userinfo (name, gender) VALUES ('庄周', '男');
INSERT INTO userinfo (name, gender) VALUES ('小乔', '女');
INSERT INTO userinfo (name, gender) VALUES ('海月', '女');
INSERT INTO userinfo (name, gender) VALUES ('夏侯淳', '男');

-- host表
CREATE TABLE host(
    id INT PRIMARY KEY AUTO_INCREMENT,
    hostname VARCHAR(64)
) DEFAULT CHARSET=utf8;

INSERT INTO host (hostname) VALUES('c1');
INSERT INTO host (hostname) VALUES('c2');
INSERT INTO host (hostname) VALUES('c3');
INSERT INTO host (hostname) VALUES('c4');
INSERT INTO host (hostname) VALUES('c5');

-- user_host表
CREATE TABLE user_host (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user INT NOT NULL,
    host_id INT NOT NULL,
    UNIQUE uq_user_host (user, host_id),
    FOREIGN KEY (user) REFERENCES userinfo(id),
    FOREIGN KEY (host_id) REFERENCES host(id)
) DEFAULT CHARSET=utf8;

INSERT INTO user_host(user, host_id) VALUES(1, 1);
INSERT INTO user_host(user, host_id) VALUES(1, 2);
INSERT INTO user_host(user, host_id) VALUES(1, 3);
INSERT INTO user_host(user, host_id) VALUES(2, 2);
INSERT INTO user_host(user, host_id) VALUES(2, 3);
INSERT INTO user_host(user, host_id) VALUES(2, 4);
INSERT INTO user_host(user, host_id) VALUES(3, 1);
INSERT INTO user_host(user, host_id) VALUES(4, 2);
INSERT INTO user_host(user, host_id) VALUES(4, 3);

常用语句

where

where语句用来提取那些满足标准的记录，可以结合delete、select以及update一起使用。

表的结构为

mysql> desc player;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| name  | varchar(100)  | YES  |     | NULL    |       |
| sex   | varchar(10)   | YES  |     | NULL    |       |
| email | varchar(100)  | YES  |     | NULL    |       |
| level | int(11)       | YES  |     | 1       |       |
| exp   | int(11)       | YES  |     | NULL    |       |
| gold  | decimal(10,2) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

等级等于1的玩家

mysql> select * from player where level=1;
+------+-----------+------+---------------------+-------+------+-------+
| id   | name      | sex  | email               | level | exp  | gold  |
+------+-----------+------+---------------------+-------+------+-------+
|   17 | 吕秀才    |  男   | lvxiucai@gmail.com  |     1 |    2 |  3.00 |
|  131 | 弈星      | 男   | yixing@geekhour.net |     1 |   61 | 90.00 |
+------+-----------+------+---------------------+-------+------+-------+
2 rows in set (0.00 sec)

等级大于1并且小于5的玩家

mysql> select * from player where level > 1 and level < 5;
+------+-----------+------+-----------------------+-------+------+-------+
| id   | name      | sex  | email                 | level | exp  | gold  |
+------+-----------+------+-----------------------+-------+------+-------+
|    1 | 张三      | 男   | zhangsan@gmail.com    |     3 |   19 | 20.00 |
|    2 | 赵四儿    | 男   | zhaosier@geekhour.net |     4 |   22 | 26.00 |
+------+-----------+------+-----------------------+-------+------+-------+
2 rows in set (0.00 sec)

等级大于等于1且小于等于5的玩家

-- 方法1
select * from player where level >= 1 and level <= 5;

-- 方法2
select * from player where level between 1 and 5;

-- 方法3 
-- between 1 and 5的意思就是包括1和5
mysql> select * from player where level between 1 and 5;
+------+-----------+------+-----------------------+-------+------+-------+
| id   | name      | sex  | email                 | level | exp  | gold  |
+------+-----------+------+-----------------------+-------+------+-------+
|    1 | 张三      | 男   | zhangsan@gmail.com    |     3 |   19 | 20.00 |
|    2 | 赵四儿    | 男   | zhaosier@geekhour.net |     4 |   22 | 26.00 |
|   17 | 吕秀才    | 男   | lvxiucai@gmail.com    |     1 |    2 |  3.00 |
|   37 | 慕容复    | 男   | murongfu@gmail.com    |     5 |   49 | 38.00 |
|  131 | 弈星      | 男   | yixing@geekhour.net   |     1 |   61 | 90.00 |
+------+-----------+------+-----------------------+-------+------+-------+
5 rows in set (0.00 sec)

优先级顺序

not > and > or

等级大于1小于5 或 经验大于1小于5的玩家

mysql> select * from player where level > 1 and level < 5 or exp > 1 and exp < 5;
+------+-----------+------+-------------------------+-------+------+-------+
| id   | name      | sex  | email                   | level | exp  | gold  |
+------+-----------+------+-------------------------+-------+------+-------+
|    1 | 张三      | 男   | zhangsan@gmail.com      |     3 |   19 | 20.00 |
|    2 | 赵四儿    | 男   | zhaosier@geekhour.net   |     4 |   22 | 26.00 |
|   17 | 吕秀才    | 男   | lvxiucai@gmail.com      |     1 |    2 |  3.00 |
|   19 | 佟湘玉    | 女   | tongxiangyu@163.com     |    99 |    4 | 10.00 |
|   50 | 高启强    | 男   | gaoqiqiang@geekhour.net |    88 |    3 | 30.00 |
|  136 | 女娲      | 女   | nvwa@qq.com             |    89 |    2 | 86.00 |
+------+-----------+------+-------------------------+-------+------+-------+
6 rows in set (0.00 sec)

等级为1 3 5的玩家

mysql> select * from player where level in (1, 3, 5);
+------+-----------+------+---------------------+-------+------+-------+
| id   | name      | sex  | email               | level | exp  | gold  |
+------+-----------+------+---------------------+-------+------+-------+
|    1 | 张三      | 男   | zhangsan@gmail.com  |     3 |   19 | 20.00 |
|   17 | 吕秀才    | 男   | lvxiucai@gmail.com  |     1 |    2 |  3.00 |
|   37 | 慕容复    | 男   | murongfu@gmail.com  |     5 |   49 | 38.00 |
|  131 | 弈星      | 男   | yixing@geekhour.net |     1 |   61 | 90.00 |
+------+-----------+------+---------------------+-------+------+-------+
4 rows in set (0.00 sec)

not取反

查找等级不在10和100之间的玩家

not可以加在一个任何语句前面

mysql> select * from player where level not between 10 and 100;
+------+-----------+------+-----------------------+-------+------+-------+
| id   | name      | sex  | email                 | level | exp  | gold  |
+------+-----------+------+-----------------------+-------+------+-------+
|    1 | 张三      | 男   | zhangsan@gmail.com    |     3 |   19 | 20.00 |
|    2 | 赵四儿    | 男   | zhaosier@geekhour.net |     4 |   22 | 26.00 |
|   11 | 胡一菲    | 女   | huyifei@163.com       |     6 |   53 | 38.00 |
|   17 | 吕秀才    | 男   | lvxiucai@gmail.com    |     1 |    2 |  3.00 |
|   31 | 周伯通    | 男   | zhoubotong@163.com    |     8 |   45 | 70.00 |
|   37 | 慕容复    | 男   | murongfu@gmail.com    |     5 |   49 | 38.00 |
|   49 | 安欣      | 男   | anxin@gmail.com       |     8 |   43 | 33.00 |
|   87 | 小鱼儿    | 男   | xiaoyuer@163.com      |     6 |   55 |  4.00 |
|  131 | 弈星      | 男   | yixing@geekhour.net   |     1 |   61 | 90.00 |
+------+-----------+------+-----------------------+-------+------+-------+
9 rows in set (0.00 sec)

like 模糊查询

%表示任意个字符

_下划线 表示任意一个字符

名字姓王玩家

-- 即名字中第一个字是王，后面可以是任意字符的玩家
mysql> select * from player where name like "王%";
+------+-----------+------+---------------------------+-------+------+-------+
| id   | name      | sex  | email                     | level | exp  | gold  |
+------+-----------+------+---------------------------+-------+------+-------+
|    3 | 王五      | 女   | wangwu@163.com            |    64 |   38 | 15.00 |
|    7 | 王小二    | 男   | wangxiaoer@163.com        |    70 |   30 | 54.00 |
|   33 | 王重阳    | 男   | wangchongyang@gmail.com   |    38 |   63 | 38.00 |
|   54 | 王语嫣    | 女   | wangyuyan@geekhour.net    |    71 |   97 | 85.00 |
|   82 | 王小蒙    | 女   | wangxiaomeng@geekhour.net |    95 |   77 | 44.00 |
|  169 | 王昭君    | 女   | wangzhaojun@gmail.com     |    96 |   48 | 11.00 |
+------+-----------+------+---------------------------+-------+------+-------+
6 rows in set (0.00 sec)

名字中包括姓王的玩家

-- 即只要名字有王就行，前面后面可以有任意多个字符
mysql> select * from player where name like "%王%";
+------+-----------+------+---------------------------+-------+------+-------+
| id   | name      | sex  | email                     | level | exp  | gold  |
+------+-----------+------+---------------------------+-------+------+-------+
|    3 | 王五      | 女   | wangwu@163.com            |    64 |   38 | 15.00 |
|    7 | 王小二    | 男   | wangxiaoer@163.com        |    70 |   30 | 54.00 |
|   33 | 王重阳    | 男   | wangchongyang@gmail.com   |    38 |   63 | 38.00 |
|   54 | 王语嫣    | 女   | wangyuyan@geekhour.net    |    71 |   97 | 85.00 |
|   82 | 王小蒙    | 女   | wangxiaomeng@geekhour.net |    95 |   77 | 44.00 |
|  160 | 牛魔王    | 男   | niumowang@qq.com          |    11 |   16 | 46.00 |
|  168 | 兰陵王    | 男   | lanlingwang@qq.com        |    64 |   22 | 12.00 |
|  169 | 王昭君    | 女   | wangzhaojun@gmail.com     |    96 |   48 | 11.00 |
+------+-----------+------+---------------------------+-------+------+-------+
8 rows in set (0.00 sec)

名字姓王且只有两个字的玩家

-- 使用了 _ 匹配任意一个字符
mysql> select * from player where name like "王_";
+------+--------+------+----------------+-------+------+-------+
| id   | name   | sex  | email          | level | exp  | gold  |
+------+--------+------+----------------+-------+------+-------+
|    3 | 王五   | 女   | wangwu@163.com |    64 |   38 | 15.00 |
+------+--------+------+----------------+-------+------+-------+
1 row in set (0.00 sec)

正则表达式regexp

注意，MySQL中的正则匹配的结果并不是很准确，所以不是很推荐。

选项	说明	例子	匹配值示例
^	匹配文本的开始字符	‘^b’ 匹配以字母 b 开头的字符串	book、big、banana、bike
$	匹配文本的结束字符	‘st$’ 匹配以 st 结尾的字符串	test、resist、persist
.	匹配任何单个字符	‘b.t’ 匹配任何 b 和 t 之间有一个字符	bit、bat、but、bite
*	匹配前面的字符 0 次或多次	‘f*n’ 匹配字符 n 前面有任意个字符 f	fn、fan、faan、abcn
+	匹配前面的字符 1 次或多次	‘ba+’ 匹配以 b 开头，后面至少紧跟一个 a	ba、bay、bare、battle
?	匹配前面的字符 0 次或1次	‘sa?’ 匹配0个或1个a字符	sa、s
字符串	匹配包含指定字符的文本	‘fa’ 匹配包含‘fa’的文本	fan、afa、faad
[字符集合]	匹配字符集合中的任何一个字符	‘[xz]’ 匹配 x 或者 z	dizzy、zebra、x-ray、extra
[^]	匹配不在括号中的任何字符	‘[^abc]’ 匹配任何不包含 a、b 或 c 的字符串	desk、fox、f8ke
字符串	匹配前面的字符串至少 n 次	‘b{2}’ 匹配 2 个或更多的 b	bbb、bbbb、bbbbbbb
字符串	匹配前面的字符串至少 n 次， 至多 m 次	‘b{2,4}’ 匹配最少 2 个，最多 4 个 b	bbb、bbbb

名字中包括姓王的玩家

mysql> select * from player where name regexp '王';
+------+-----------+------+---------------------------+-------+------+-------+
| id   | name      | sex  | email                     | level | exp  | gold  |
+------+-----------+------+---------------------------+-------+------+-------+
|    3 | 王五      | 女   | wangwu@163.com            |    64 |   38 | 15.00 |
|    7 | 王小二    | 男   | wangxiaoer@163.com        |    70 |   30 | 54.00 |
|   33 | 王重阳    | 男   | wangchongyang@gmail.com   |    38 |   63 | 38.00 |
|   54 | 王语嫣    | 女   | wangyuyan@geekhour.net    |    71 |   97 | 85.00 |
|   82 | 王小蒙    | 女   | wangxiaomeng@geekhour.net |    95 |   77 | 44.00 |
|  160 | 牛魔王    | 男   | niumowang@qq.com          |    11 |   16 | 46.00 |
|  168 | 兰陵王    | 男   | lanlingwang@qq.com        |    64 |   22 | 12.00 |
|  169 | 王昭君    | 女   | wangzhaojun@gmail.com     |    96 |   48 | 11.00 |
+------+-----------+------+---------------------------+-------+------+-------+
8 rows in set (0.00 sec)

王字结尾的玩家

mysql> select * from player where name regexp "王$";
+------+-----------+------+--------------------+-------+------+-------+
| id   | name      | sex  | email              | level | exp  | gold  |
+------+-----------+------+--------------------+-------+------+-------+
|  160 | 牛魔王    | 男   | niumowang@qq.com   |    11 |   16 | 46.00 |
|  168 | 兰陵王    | 男   | lanlingwang@qq.com |    64 |   22 | 12.00 |
+------+-----------+------+--------------------+-------+------+-------+
2 rows in set (0.00 sec)

姓王的玩家

mysql> select * from player where name regexp "^王";
+------+-----------+------+---------------------------+-------+------+-------+
| id   | name      | sex  | email                     | level | exp  | gold  |
+------+-----------+------+---------------------------+-------+------+-------+
|    3 | 王五      | 女   | wangwu@163.com            |    64 |   38 | 15.00 |
|    7 | 王小二    | 男   | wangxiaoer@163.com        |    70 |   30 | 54.00 |
|   33 | 王重阳    | 男   | wangchongyang@gmail.com   |    38 |   63 | 38.00 |
|   54 | 王语嫣    | 女   | wangyuyan@geekhour.net    |    71 |   97 | 85.00 |
|   82 | 王小蒙    | 女   | wangxiaomeng@geekhour.net |    95 |   77 | 44.00 |
|  169 | 王昭君    | 女   | wangzhaojun@gmail.com     |    96 |   48 | 11.00 |
+------+-----------+------+---------------------------+-------+------+-------+
6 rows in set (0.00 sec)

名字包含王或者满的王家

mysql> select * from player where name regexp "王|满";
+------+-----------+------+---------------------------+-------+------+-------+
| id   | name      | sex  | email                     | level | exp  | gold  |
+------+-----------+------+---------------------------+-------+------+-------+
|    3 | 王五      | 女   | wangwu@163.com            |    64 |   38 | 15.00 |
|    7 | 王小二    | 男   | wangxiaoer@163.com        |    70 |   30 | 54.00 |
|   33 | 王重阳    | 男   | wangchongyang@gmail.com   |    38 |   63 | 38.00 |
|   54 | 王语嫣    | 女   | wangyuyan@geekhour.net    |    71 |   97 | 85.00 |
|   82 | 王小蒙    | 女   | wangxiaomeng@geekhour.net |    95 |   77 | 44.00 |
|   94 | 姬小满    | 女   | jixiaoman@geekhour.net    |    27 |   23 | 70.00 |
|  160 | 牛魔王    | 男   | niumowang@qq.com          |    11 |   16 | 46.00 |
|  168 | 兰陵王    | 男   | lanlingwang@qq.com        |    64 |   22 | 12.00 |
|  169 | 王昭君    | 女   | wangzhaojun@gmail.com     |    96 |   48 | 11.00 |
+------+-----------+------+---------------------------+-------+------+-------+
9 rows in set (0.03 sec)

邮件地址以zhangsan开头的玩家

-- 方法1
select * from player where email like "zhangsan%";

-- 方法2
mysql> select * from player where email regexp "^zhangsan";
+------+-----------+------+---------------------------+-------+------+-------+
| id   | name      | sex  | email                     | level | exp  | gold  |
+------+-----------+------+---------------------------+-------+------+-------+
|    1 | 张三      | 男   | zhangsan@gmail.com        |     3 |   19 | 20.00 |
|   46 | 张三丰    | 男   | zhangsanfeng@geekhour.net |    13 |   79 | 12.00 |
+------+-----------+------+---------------------------+-------+------+-------+
2 rows in set (0.00 sec)

邮件地址以a/b/c开头的玩家

-- 方法1
select * from player where email regexp "^[a-c]";

-- 方法2
mysql> select * from player where email regexp "^[abc]";
+------+--------------+------+--------------------------+-------+------+--------+
| id   | name         | sex  | email                    | level | exp  | gold   |
+------+--------------+------+--------------------------+-------+------+--------+
|   14 | 陈美嘉       | 女   | chenmeijia@geekhour.net  |    95 |   71 |  66.00 |
|   20 | 白展堂       | 男   | baizhantang@qq.com       |    89 |   85 |  13.00 |
|   49 | 安欣         | 男   | anxin@gmail.com          |     8 |   43 |  33.00 |
|   55 | 阿朱         | 女   | azhu@163.com             |    79 |   81 |  65.00 |
|   56 | 阿紫         | 女   | azi@qq.com               |    91 |   60 |  56.00 |
|   57 | 阿碧         | 女   | abi@gmail.com            |   100 |    9 |  92.00 |
|   88 | 包青天       | 男   | baoqingtian@qq.com       |    28 |   60 |   4.00 |
|  104 | 艾琳         | 女   | ailin@qq.com             |    86 |   73 |  22.00 |
|  108 | 阿古朵       | 女   | aguduo@qq.com            |    78 |   88 |   3.00 |
|  120 | 嫦娥         | 女   | change@163.com           |    12 |   16 |  74.00 |
|  139 | 百里玄策     | 男   | bailixuance@163.com      |    13 |   60 |  43.00 |
|  140 | 百里守约     | 男   | bailishouyue@qq.com      |    96 |   34 |  16.00 |
|  150 | 蔡文姬       | 女   | caiwenji@geekhour.net    |    64 |   82 |  70.00 |
|  153 | 成吉思汗     | 男   | chengjisihan@gmail.com   |    42 |   65 | 100.00 |
|  165 | 不知火舞     | 女   | buzhihuowu@gmail.com     |    18 |   65 |  33.00 |
|  174 | 程咬金       | 男   | chengyaojin@geekhour.net |    79 |   98 |   1.00 |
|  175 | 安琪拉       | 女   | anqila@163.com           |    92 |   77 |  82.00 |
|  186 | 曹操         | 男   | caocao@geekhour.net      |    70 |   15 |  27.00 |
|  192 | 白起         | 男   | baiqi@qq.com             |    63 |   29 |  76.00 |
|  193 | 扁鹊         | 男   | bianque@gmail.com        |    82 |   99 |  59.00 |
|  196 | 阿轲         | 女   | ake@geekhour.net         |    60 |   61 |  62.00 |
+------+--------------+------+--------------------------+-------+------+--------+
21 rows in set (0.00 sec)

邮件地址以net结尾的玩家

-- 方法1
select * from player where email like "%net";

-- 方法2
mysql> select * from player where email regexp "net$";
+------+--------------+------+----------------------------+-------+------+--------+
| id   | name         | sex  | email                      | level | exp  | gold   |
+------+--------------+------+----------------------------+-------+------+--------+
|    2 | 赵四儿       | 男   | zhaosier@geekhour.net      |     4 |   22 |  26.00 |
|    6 | 马大帅       | 男   | madashuai@geekhour.net     |    87 |   98 |  98.00 |
|   10 | 陆展博       | 男   | luzhanbo@geekhour.net      |    98 |   51 |  62.00 |
|   14 | 陈美嘉       | 女   | chenmeijia@geekhour.net    |    95 |   71 |  66.00 |
|   18 | 李大嘴       | 男   | lidazui@geekhour.net       |    91 |   28 |  40.00 |
|   22 | 郭芙蓉       | 女   | guofurong@geekhour.net     |    95 |   80 |  33.00 |
|   26 | 小龙女       | 女   | xiaolongnv@geekhour.net    |    75 |    7 |  48.00 |
|   30 | 黄药师       | 男   | huangyaoshi@geekhour.net   |    21 |   93 |  84.00 |
|   34 | 黄老邪       | 男   | huanglaoxie@geekhour.net   |    68 |   16 |  92.00 |
|   38 | 慕容博       | 男   | murongbo@geekhour.net      |    75 |   19 |  45.00 |
|   42 | 赵敏         | 女   | zhaomin@geekhour.net       |    32 |   12 |  54.00 |
|   46 | 张三丰       | 男   | zhangsanfeng@geekhour.net  |    13 |   79 |  12.00 |
|   50 | 高启强       | 男   | gaoqiqiang@geekhour.net    |    88 |    3 |  30.00 |
|   54 | 王语嫣       | 女   | wangyuyan@geekhour.net     |    71 |   97 |  85.00 |
|   58 | 乔峰         | 男   | qiaofeng@geekhour.net      |    54 |   31 |  35.00 |
|  194 | 孙膑         | 男   | sunbin@geekhour.net        |    42 |   30 |  12.00 |
|  196 | 阿轲         | 女   | ake@geekhour.net           |    60 |   61 |  62.00 |
|  200 | 孙尚香       | 女   | sunshangxiang@geekhour.net |    97 |   95 |  98.00 |
|  205 | 小乔         | 女   | xiaoqiao@geekhour.net      |    83 |   60 |  59.00 |
|  209 | 东方不败     |      | dongfangbubai@geekhour.net |    95 |   95 |   2.00 |
+------+--------------+------+----------------------------+-------+------+--------+
51 rows in set (0.00 sec)
-- 结果太长 省略部分结果

空值 null

不能使用等号判断，因为null值与其它任何值都不相等，包括null本身

-- 错误的演示
mysql> select * from player where email = null;
Empty set (0.00 sec)

-- 正确的演示
mysql> select * from player where email is null;
+------+-----------+------+-------+-------+------+-------+
| id   | name      | sex  | email | level | exp  | gold  |
+------+-----------+------+-------+-------+------+-------+
|   12 | 吕子乔    | 男   | NULL  |    36 |  100 | 46.00 |
|   13 | 吕小布    | 男   | NULL  |    81 |   88 | 25.00 |
+------+-----------+------+-------+-------+------+-------+
2 rows in set (0.03 sec)

-- 另外一种方法
-- 不推荐
mysql> select * from player where email <=> null;
+------+-----------+------+-------+-------+------+-------+
| id   | name      | sex  | email | level | exp  | gold  |
+------+-----------+------+-------+-------+------+-------+
|   12 | 吕子乔    | 男   | NULL  |    36 |  100 | 46.00 |
|   13 | 吕小布    | 男   | NULL  |    81 |   88 | 25.00 |
+------+-----------+------+-------+-------+------+-------+
2 rows in set (0.00 sec)

null和空值的区别

null表示没有值，而空值则表示填写了一个空的字符串

mysql> select * from player where email is null or email = '';
+------+-----------+------+-------+-------+------+-------+
| id   | name      | sex  | email | level | exp  | gold  |
+------+-----------+------+-------+-------+------+-------+
|   12 | 吕子乔    | 男   | NULL  |    36 |  100 | 46.00 |
|   13 | 吕小布    | 男   | NULL  |    81 |   88 | 25.00 |
|  190 | 吕布      | 男   |       |    77 |   43 | 31.00 |
+------+-----------+------+-------+-------+------+-------+
3 rows in set (0.00 sec)

-- 这里的吕布就是一个空值，并不是null

排序 order by

如果不指定，默认是升序排序

降序desc 升序 asc

将玩家等级升序排列

mysql> select * from player order by level;
+------+--------------+------+----------------------------+-------+------+--------+
| id   | name         | sex  | email                      | level | exp  | gold   |
+------+--------------+------+----------------------------+-------+------+--------+
|   17 | 吕秀才       | 男   | lvxiucai@gmail.com         |     1 |    2 |   3.00 |
|  131 | 弈星         | 男   | yixing@geekhour.net        |     1 |   61 |  90.00 |
|    1 | 张三         | 男   | zhangsan@gmail.com         |     3 |   19 |  20.00 |
|    2 | 赵四儿       | 男   | zhaosier@geekhour.net      |     4 |   22 |  26.00 |
|   37 | 慕容复       | 男   | murongfu@gmail.com         |     5 |   49 |  38.00 |
|   11 | 胡一菲       | 女   | huyifei@163.com            |     6 |   53 |  38.00 |
|   19 | 佟湘玉       | 女   | tongxiangyu@163.com        |    99 |    4 |  10.00 |
|   64 | 风清扬       | 男   | fengqingyang@qq.com        |    99 |   80 |  81.00 |
|  129 | 米莱狄       | 女   | milaidi@qq.com             |    99 |   93 |  31.00 |
|  164 | 娜可露露     | 女   | nakelulu@qq.com            |    99 |   16 |  33.00 |
|   57 | 阿碧         | 女   | abi@gmail.com              |   100 |    9 |  92.00 |
|  208 | 独孤求败     | 男   | duguqiubai@gmail.com       |   100 |  100 |   1.00 |
+------+--------------+------+----------------------------+-------+------+--------+
209 rows in set (0.00 sec)

-- 结果太长，只截取一部分

将玩家等级降序排列

加上一个desc即可

mysql> select * from player order by level;
+------+--------------+------+----------------------------+-------+------+--------+
| id   | name         | sex  | email                      | level | exp  | gold   |
+------+--------------+------+----------------------------+-------+------+--------+
|   17 | 吕秀才       | 男   | lvxiucai@gmail.com         |     1 |    2 |   3.00 |
|  131 | 弈星         | 男   | yixing@geekhour.net        |     1 |   61 |  90.00 |
|    1 | 张三         | 男   | zhangsan@gmail.com         |     3 |   19 |  20.00 |
|    2 | 赵四儿       | 男   | zhaosier@geekhour.net      |     4 |   22 |  26.00 |
|   37 | 慕容复       | 男   | murongfu@gmail.com         |     5 |   49 |  38.00 |
|   11 | 胡一菲       | 女   | huyifei@163.com            |     6 |   53 |  38.00 |
|   37 | 慕容复       | 男   | murongfu@gmail.com         |     5 |   49 |  38.00 |
|    2 | 赵四儿       | 男   | zhaosier@geekhour.net      |     4 |   22 |  26.00 |
|    1 | 张三         | 男   | zhangsan@gmail.com         |     3 |   19 |  20.00 |
|   17 | 吕秀才       | 男   | lvxiucai@gmail.com         |     1 |    2 |   3.00 |
|  131 | 弈星         | 男   | yixing@geekhour.net        |     1 |   61 |  90.00 |
+------+--------------+------+----------------------------+-------+------+--------+
209 rows in set (0.00 sec)

-- 结果太长，只截取一部分

等级降序经验升序（多列排序）

加上列名和规则即可

-- 先按照等级降序去排列，如果等级相同则按照经验升序去排列
-- 方法1
select * from player order by level desc, exp;

-- 方法2
mysql> select * from player order by level desc, exp asc;
+------+--------------+------+----------------------------+-------+------+--------+
| id   | name         | sex  | email                      | level | exp  | gold   |
+------+--------------+------+----------------------------+-------+------+--------+
|   57 | 阿碧         | 女   | abi@gmail.com              |   100 |    9 |  92.00 |
|  208 | 独孤求败     | 男   | duguqiubai@gmail.com       |   100 |  100 |   1.00 |
|   19 | 佟湘玉       | 女   | tongxiangyu@163.com        |    99 |    4 |  10.00 |
|  164 | 娜可露露     | 女   | nakelulu@qq.com            |    99 |   16 |  33.00 |
|   64 | 风清扬       | 男   | fengqingyang@qq.com        |    99 |   80 |  81.00 |
|  129 | 米莱狄       | 女   | milaidi@qq.com             |    99 |   93 |  31.00 |
|  123 | 沈梦溪       | 女   | shenmengxi@geekhour.net    |    98 |   47 |  29.00 |
|   11 | 胡一菲       | 女   | huyifei@163.com            |     6 |   53 |  38.00 |
|   87 | 小鱼儿       | 男   | xiaoyuer@163.com           |     6 |   55 |   4.00 |
|   37 | 慕容复       | 男   | murongfu@gmail.com         |     5 |   49 |  38.00 |
|    2 | 赵四儿       | 男   | zhaosier@geekhour.net      |     4 |   22 |  26.00 |
|    1 | 张三         | 男   | zhangsan@gmail.com         |     3 |   19 |  20.00 |
|   17 | 吕秀才       | 男   | lvxiucai@gmail.com         |     1 |    2 |   3.00 |
|  131 | 弈星         | 男   | yixing@geekhour.net        |     1 |   61 |  90.00 |
+------+--------------+------+----------------------------+-------+------+--------+
209 rows in set (0.00 sec)

-- 结果太长，只截取一部分

通过列的序号来排序

-- 再次看一下表的结构 
mysql> describe player;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| name  | varchar(100)  | YES  |     | NULL    |       |
| sex   | varchar(10)   | YES  |     | NULL    |       |
| email | varchar(100)  | YES  |     | NULL    |       |
| level | int(11)       | YES  |     | 1       |       |
| exp   | int(11)       | YES  |     | NULL    |       |
| gold  | decimal(10,2) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
7 rows in set (0.01 sec)

-- 通过序号去降序排列  5 就是这表的第5列 level
mysql> select * from player order by 5 desc;
+------+--------------+------+----------------------------+-------+------+--------+
| id   | name         | sex  | email                      | level | exp  | gold   |
+------+--------------+------+----------------------------+-------+------+--------+
|   57 | 阿碧         | 女   | abi@gmail.com              |   100 |    9 |  92.00 |
|  208 | 独孤求败     | 男   | duguqiubai@gmail.com       |   100 |  100 |   1.00 |
|   19 | 佟湘玉       | 女   | tongxiangyu@163.com        |    99 |    4 |  10.00 |
|   64 | 风清扬       | 男   | fengqingyang@qq.com        |    99 |   80 |  81.00 |
|  129 | 米莱狄       | 女   | milaidi@qq.com             |    99 |   93 |  31.00 |
|  164 | 娜可露露     | 女   | nakelulu@qq.com            |    99 |   16 |  33.00 |
|   10 | 陆展博       | 男   | luzhanbo@geekhour.net      |    98 |   51 |  62.00 |
|   73 | 钱德勒       | 男   | qiandelmo@gmail.com        |    98 |   91 |   1.00 |
|  123 | 沈梦溪       | 女   | shenmengxi@geekhour.net    |    98 |   47 |  29.00 |
|   11 | 胡一菲       | 女   | huyifei@163.com            |     6 |   53 |  38.00 |
|   87 | 小鱼儿       | 男   | xiaoyuer@163.com           |     6 |   55 |   4.00 |
|   37 | 慕容复       | 男   | murongfu@gmail.com         |     5 |   49 |  38.00 |
|    2 | 赵四儿       | 男   | zhaosier@geekhour.net      |     4 |   22 |  26.00 |
|    1 | 张三         | 男   | zhangsan@gmail.com         |     3 |   19 |  20.00 |
|   17 | 吕秀才       | 男   | lvxiucai@gmail.com         |     1 |    2 |   3.00 |
|  131 | 弈星         | 男   | yixing@geekhour.net        |     1 |   61 |  90.00 |
+------+--------------+------+----------------------------+-------+------+--------+
209 rows in set (0.00 sec)

-- 结果太长，只截取一部分

聚合函数

也可以同分组GROUP BY 子句一起使用，下面会讲到。

需要注意 的是，where条件后面不能加聚合条件的结果，不然会报错。

总数count

用于计算某列中非NULL值的数量。

mysql> select count(*) from player;
+----------+
| count(*) |
+----------+
|      209 |
+----------+
1 row in set (0.00 sec)

最大值 max

用于找到某列中的最大值。

mysql> select max(level) from player;
+------------+
| max(level) |
+------------+
|        100 |
+------------+
1 row in set (0.03 sec)

最小值 min

用于找到某列中的最小值。

mysql> select min(gold) from player;
+-----------+
| min(gold) |
+-----------+
|      1.00 |
+-----------+
1 row in set (0.00 sec)

平均值 avg

用于计算某列中所有数值的平均值。

mysql> select avg(gold) from player;
+-----------+
| avg(gold) |
+-----------+
| 47.564593 |
+-----------+
1 row in set (0.03 sec)

/*
	In [1]: 9941 / 209
    Out[1]: 47.56459330143541
*/

求和 sum

用于计算某列中所有数值的总和。

mysql> select sum(gold) from player;
+-----------+
| sum(gold) |
+-----------+
|   9941.00 |
+-----------+
1 row in set (0.00 sec)

分组 group by

后面可以跟上一个或者多个列名，表示按照这些列来分组，然后前面的select语句中，就可以使用刚刚我们学过的聚合函数对这些列进行计算了

-- 统计男女比例的玩家各有多少名
mysql> select sex, count(*) from player group by sex;
+------+----------+
| sex  | count(*) |
+------+----------+
| NULL |        3 |
|      |        1 |
| 女   |       65 |
| 男   |      140 |
+------+----------+
4 rows in set (0.00 sec)

-- 统计每个等级的玩家有多少名
mysql> select level, count(level) from player group by level;
+-------+--------------+
| level | count(level) |
+-------+--------------+
|     1 |            2 |
|     3 |            1 |
|     4 |            1 |
|     5 |            1 |
|    99 |            4 |
|   100 |            2 |
+-------+--------------+
89 rows in set (0.00 sec)

-- 结果太长，只截取一部分

having 筛选分组后的数据

主要对分组后的结果进行过滤

-- 对于上面的结果，数量大于4的结果有哪些
mysql> select level, count(level) from player group by level having count(level) > 4;
+-------+--------------+
| level | count(level) |
+-------+--------------+
|    13 |            9 |
|    54 |            5 |
|    88 |            5 |
|    95 |            5 |
|    96 |            7 |
+-------+--------------+
5 rows in set (0.00 sec)

-- 也可以用as 起一个别名
mysql> select level, count(level) as '数量' from player group by level having count(level) > 4;
+-------+--------+
| level | 数量   |
+-------+--------+
|    13 |      9 |
|    54 |      5 |
|    88 |      5 |
|    95 |      5 |
|    96 |      7 |
+-------+--------+
5 rows in set (0.00 sec)

-- 对刚刚的结果进行降序排列
mysql> select level, count(level) as '数量' from player group by level having count(level) > 4 order by count(level) desc;
+-------+--------+
| level | 数量   |
+-------+--------+
|    13 |      9 |
|    96 |      7 |
|    54 |      5 |
|    95 |      5 |
|    88 |      5 |
+-------+--------+
5 rows in set (0.00 sec)

limit限制数量

小练习

统计每个玩家姓氏的数量，并将结果按照数量来降序排列，只显示结果大于等于5的姓氏，并且只显示前三位玩家。

substr函数，截取字符串的一部分，第一个参数是要截取的字符串，第二个参数是开始位置，第三个位置要截取的长度。
substr(name, 1, 1) 就表示截取name字段的第一个字符（姓氏）

limit也可以设置一个偏移量 比如 limit 3, 3 表示第四名到第6名  第一个三表示从第四名开始，第二个三表示返回的数量

mysql> select substr(name, 1, 1), count(substr(name, 1, 1)) from player group by substr(name, 1, 1)
    -> having count(substr(name, 1, 1)) >= 5
    -> order by count(substr(name, 1, 1)) desc
    -> limit 3;
+--------------------+---------------------------+
| substr(name, 1, 1) | count(substr(name, 1, 1)) |
+--------------------+---------------------------+
| 李                 |                        11 |
| 张                 |                         8 |
| 王                 |                         6 |
+--------------------+---------------------------+
3 rows in set (0.00 sec)

mysql> select substr(name, 1, 1) as '姓氏', count(substr(name, 1, 1)) as '数量'
    -> from player group by substr(name, 1, 1)
    -> order by count(substr(name, 1, 1)) desc limit 3, 3;
+--------+--------+
| 姓氏   | 数量   |
+--------+--------+
| 阿     |      5 |
| 小     |      5 |
| 赵     |      4 |
+--------+--------+
3 rows in set (0.00 sec)

distinct 去重

-- 获取玩家的所有性别
mysql> select distinct sex from player;
+------+
| sex  |
+------+
| 男   |
| 女   |
| NULL |
|      |
+------+
4 rows in set (0.00 sec)

union 合并查询结果集（并集）

-- 等级1到3
mysql> select * from player where level between 1 and 3;
+------+-----------+------+---------------------+-------+------+-------+
| id   | name      | sex  | email               | level | exp  | gold  |
+------+-----------+------+---------------------+-------+------+-------+
|    1 | 张三      | 男   | zhangsan@gmail.com  |     3 |   19 | 20.00 |
|   17 | 吕秀才    | 男   | lvxiucai@gmail.com  |     1 |    2 |  3.00 |
|  131 | 弈星      | 男   | yixing@geekhour.net |     1 |   61 | 90.00 |
+------+-----------+------+---------------------+-------+------+-------+
3 rows in set (0.03 sec)

-- 经验1到3
mysql> select * from player where exp between 1 and 3;
+------+-----------+------+-------------------------+-------+------+-------+
| id   | name      | sex  | email                   | level | exp  | gold  |
+------+-----------+------+-------------------------+-------+------+-------+
|   17 | 吕秀才    | 男   | lvxiucai@gmail.com      |     1 |    2 |  3.00 |
|   50 | 高启强    | 男   | gaoqiqiang@geekhour.net |    88 |    3 | 30.00 |
|  136 | 女娲      | 女   | nvwa@qq.com             |    89 |    2 | 86.00 |
|  145 | 大乔      | 女   | daqiao@gmail.com        |    87 |    1 | 82.00 |
+------+-----------+------+-------------------------+-------+------+-------+
4 rows in set (0.00 sec)

-- 使用union合并起来即可
-- 需要注意的是union会去重，比如等级为1到3并且经验为1到3的玩家 吕秀才 ，所以合并的时候去掉了一条
-- 所有等级为1到3 以及 所有等级为1 到3的玩家
mysql> select * from player where level between 1 and 3
    -> union
    -> select * from player where exp between 1 and 3;
+------+-----------+------+-------------------------+-------+------+-------+
| id   | name      | sex  | email                   | level | exp  | gold  |
+------+-----------+------+-------------------------+-------+------+-------+
|    1 | 张三      | 男   | zhangsan@gmail.com      |     3 |   19 | 20.00 |
|   17 | 吕秀才    | 男   | lvxiucai@gmail.com      |     1 |    2 |  3.00 |
|  131 | 弈星      | 男   | yixing@geekhour.net     |     1 |   61 | 90.00 |
|   50 | 高启强    | 男   | gaoqiqiang@geekhour.net |    88 |    3 | 30.00 |
|  136 | 女娲      | 女   | nvwa@qq.com             |    89 |    2 | 86.00 |
|  145 | 大乔      | 女   | daqiao@gmail.com        |    87 |    1 | 82.00 |
+------+-----------+------+-------------------------+-------+------+-------+
6 rows in set (0.00 sec)

union all 不去重

注意等级为1-3且经验为1到3的吕秀才

mysql> select * from player where level between 1 and 3
    -> union all
    -> select * from player where exp between 1 and 3;
+------+-----------+------+-------------------------+-------+------+-------+
| id   | name      | sex  | email                   | level | exp  | gold  |
+------+-----------+------+-------------------------+-------+------+-------+
|    1 | 张三      | 男   | zhangsan@gmail.com      |     3 |   19 | 20.00 |
|   17 | 吕秀才    | 男   | lvxiucai@gmail.com      |     1 |    2 |  3.00 |
|  131 | 弈星      | 男   | yixing@geekhour.net     |     1 |   61 | 90.00 |
|   17 | 吕秀才    | 男   | lvxiucai@gmail.com      |     1 |    2 |  3.00 |
|   50 | 高启强    | 男   | gaoqiqiang@geekhour.net |    88 |    3 | 30.00 |
|  136 | 女娲      | 女   | nvwa@qq.com             |    89 |    2 | 86.00 |
|  145 | 大乔      | 女   | daqiao@gmail.com        |    87 |    1 | 82.00 |
+------+-----------+------+-------------------------+-------+------+-------+
7 rows in set (0.00 sec)

union和or的区别

union和or有一些类似，只不过union是合并两个条件的，or是合并两个查询结果的

uni instrsect 合并结果集（交集）

待补充

except 合并结果集（差集）

待补充

子查询

一个查询结果作为另外一个查询的条件

子查询不仅可以用在where语句中，还可以用在select update delete insert等语句中

-- 正常查看玩家的平均等级
mysql> select avg(level) from player;
+------------+
| avg(level) |
+------------+
|    55.0813 |
+------------+
1 row in set (0.00 sec)

-- 查询所有等级大于平均等级的玩家
mysql> select * from player where level > (select avg(level) from player);
+------+--------------+------+----------------------------+-------+------+--------+
| id   | name         | sex  | email                      | level | exp  | gold   |
+------+--------------+------+----------------------------+-------+------+--------+
|    3 | 王五         | 女   | wangwu@163.com             |    64 |   38 |  15.00 |
|    5 | 范德彪       | 男   | fandebiao@gmail.com        |    95 |   89 |  44.00 |
|    6 | 马大帅       | 男   | madashuai@geekhour.net     |    87 |   98 |  98.00 |
|    7 | 王小二       | 男   | wangxiaoer@163.com         |    70 |   30 |  54.00 |
|   10 | 陆展博       | 男   | luzhanbo@geekhour.net      |    98 |   51 |  62.00 |
|   13 | 吕小布       | 男   | NULL                       |    81 |   88 |  25.00 |
|   14 | 陈美嘉       | 女   | chenmeijia@geekhour.net    |    95 |   71 |  66.00 |
|  202 | 妲己         | 女   | daji@163.com               |    96 |   55 |  56.00 |
|  203 | 墨子         | 男   | mozi@qq.com                |    70 |  100 |  66.00 |
|  205 | 小乔         | 女   | xiaoqiao@geekhour.net      |    83 |   60 |  59.00 |
|  206 | 廉颇         | 男   | lianpo@163.com             |    84 |   90 |  73.00 |
|  208 | 独孤求败     | 男   | duguqiubai@gmail.com       |   100 |  100 |   1.00 |
|  209 | 东方不败     |      | dongfangbubai@geekhour.net |    95 |   95 |   2.00 |
+------+--------------+------+----------------------------+-------+------+--------+
104 rows in set (0.04 sec)

-- 结果太长，只截取一部分

-- 计算所有玩家等级和平均等级之间的差值
mysql> select name, level, (select avg(level) from player) as '平均等级',
    -> round(level - (select avg(level)  from player) , 2)
    -> as '差值' from player;
+--------------+-------+--------------+--------+
| name         | level | 平均等级     | 差值   |
+--------------+-------+--------------+--------+
| 张三         |     3 |      55.0813 | -52.08 |
| 赵四儿       |     4 |      55.0813 | -51.08 |
| 王五         |    64 |      55.0813 |   8.92 |
| 刘能         |    30 |      55.0813 | -25.08 |
| 小乔         |    83 |      55.0813 |  27.92 |
| 廉颇         |    84 |      55.0813 |  28.92 |
| 李白         |    53 |      55.0813 |  -2.08 |
| 独孤求败     |   100 |      55.0813 |  44.92 |
| 东方不败     |    95 |      55.0813 |  39.92 |
+--------------+-------+--------------+--------+
209 rows in set (0.00 sec)

-- 结果太长，只截取一部分

使用子查询将结果保存到一个新的表里面

-- 将等级小于10的玩家保存到一个新的表里
mysql> create table new_player select * from player where level < 5;
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0

-- 展示新表的结果
mysql> select * from new_player;
+------+-----------+------+-----------------------+-------+------+-------+
| id   | name      | sex  | email                 | level | exp  | gold  |
+------+-----------+------+-----------------------+-------+------+-------+
|    1 | 张三      | 男   | zhangsan@gmail.com    |     3 |   19 | 20.00 |
|    2 | 赵四儿    | 男   | zhaosier@geekhour.net |     4 |   22 | 26.00 |
|   17 | 吕秀才    | 男   | lvxiucai@gmail.com    |     1 |    2 |  3.00 |
|  131 | 弈星      | 男   | yixing@geekhour.net   |     1 |   61 | 90.00 |
+------+-----------+------+-----------------------+-------+------+-------+
4 rows in set (0.00 sec)

使用insert语句结合子查询

-- 把等级为10到12（含）的玩家数据插入到新的表里面
mysql> insert into new_player select * from player where level between 10 and 12;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

-- 展示新表的结果
mysql> select * from new_player;
+------+-----------+------+-----------------------+-------+------+-------+
| id   | name      | sex  | email                 | level | exp  | gold  |
+------+-----------+------+-----------------------+-------+------+-------+
|    1 | 张三      | 男   | zhangsan@gmail.com    |     3 |   19 | 20.00 |
|    2 | 赵四儿    | 男   | zhaosier@geekhour.net |     4 |   22 | 26.00 |
|   17 | 吕秀才    | 男   | lvxiucai@gmail.com    |     1 |    2 |  3.00 |
|  131 | 弈星      | 男   | yixing@geekhour.net   |     1 |   61 | 90.00 |
|   91 | 余则成    | 男   | yuzecheng@163.com     |    10 |   53 | 29.00 |
|   97 | 海月      | 女   | haiyue@gmail.com      |    12 |   77 | 74.00 |
|  120 | 嫦娥      | 女   | change@163.com        |    12 |   16 | 74.00 |
|  160 | 牛魔王    | 男   | niumowang@qq.com      |    11 |   16 | 46.00 |
|   91 | 余则成    | 男   | yuzecheng@163.com     |    10 |   53 | 29.00 |
|   97 | 海月      | 女   | haiyue@gmail.com      |    12 |   77 | 74.00 |
|  120 | 嫦娥      | 女   | change@163.com        |    12 |   16 | 74.00 |
|  160 | 牛魔王    | 男   | niumowang@qq.com      |    11 |   16 | 46.00 |
+------+-----------+------+-----------------------+-------+------+-------+
12 rows in set (0.00 sec)

exists 反馈查询结果

返回值只有0和1两种，0表示没有找到，1表示找到了

-- 查询是否有等级大于100的玩家
-- 0表示没有
mysql> select exists(select * from player where level > 100);
+------------------------------------------------+
| exists(select * from player where level > 100) |
+------------------------------------------------+
|                                              0 |
+------------------------------------------------+
1 row in set (0.03 sec)

-- 如果将查询条件改成存在的 比如10
mysql> select exists(select * from player where level > 10);
+-----------------------------------------------+
| exists(select * from player where level > 10) |
+-----------------------------------------------+
|                                             1 |
+-----------------------------------------------+
1 row in set (0.00 sec)

表关联

表关联用来查询多个表之间的数据，关联的表之间必须有相同的字段

一般情况下使用表的主键和外键进行关联

表连接的本质就是笛卡尔积加上条件的过滤

-- 班级表
create table class (
    cid int primary key auto_increment,
    caption varchar(32)
) default char set utf8;

insert into class (caption) values ('三年二班'), ('一年三班'), ('三年一班');

-- 学生表
create table student (
    sid int primary key auto_increment,
    sname varchar(32),
    gender varchar(12),
    class_id int not null ,
    constraint class_student_fk foreign key (class_id)  references class(cid)
) default char set utf8;

insert into student(sname, gender, class_id) values ('小满', '女', 1);
insert into student(sname, gender, class_id) values ('大乔', '女', 1);
insert into student(sname, gender, class_id) values ('庄周', '男', 2);

-- 教师表
create table teacher (
    tid int primary key auto_increment,
    tname varchar(32)
) default char set utf8;

insert into teacher (tname) values ('阿珂');
insert into teacher (tname) values ('海月');
insert into teacher (tname) values ('上官婉儿');

-- 课程表
create table course (
    cid int primary key auto_increment,
    cname varchar(32),
    teacher_id int not null,
    constraint teacher_course_fk foreign key (teacher_id) references teacher(tid)
) default char set utf8;

insert into course (cname, teacher_id) values ('抢人头', 1);
insert into course (cname, teacher_id) values ('抢野怪', 1);
insert into course (cname, teacher_id) values ('摸鱼', 2);

-- 成绩表
create table score (
    sid int primary key auto_increment,
    student_id int not null ,
    corse_id int not null ,
    number int(4),
    constraint score_student_fk foreign key (student_id)  references student(sid),
    constraint score_course_fk foreign key (corse_id)  references course(cid)
)default char set utf8;

insert into score (student_id, corse_id, number) values (1, 1, 60);
insert into score (student_id, corse_id, number) values (1, 2, 59);
insert into score (student_id, corse_id, number) values (2, 2, 100);

-- 支持多表查询，只需要这些表有相同的字段即可
SELECT
    student.sname as 学生姓名,
    class.caption as 班级,
    teacher.tname as 老师,
    course.cname as 项目,
    score.number as 成绩
FROM student
LEFT JOIN score ON student.sid = score.student_id
LEFT JOIN class ON student.class_id = class.cid
LEFT JOIN course ON student.class_id = course.cid
LEFT JOIN teacher ON teacher.tid = course.teacher_id where score.number > 59;

+--------------+--------------+--------+-----------+--------+
| 学生姓名     | 班级         | 老师   | 项目      | 成绩   |
+--------------+--------------+--------+-----------+--------+
| 小满         | 三年二班     | 阿珂   | 抢人头    |     60 |
| 大乔         | 三年二班     | 阿珂   | 抢人头    |    100 |
+--------------+--------------+--------+-----------+--------+
2 rows in set (0.00 sec)

笛卡尔积

在关系数据库中，笛卡尔积是指两个表之间的所有可能组合的行。当你执行没有任何条件限制的查询时，将得到这两个表的笛卡尔积。

比如A表有47条数据，B条也有47条数据，如果直接写select * from A, B; 那么结果就是47*47=2209条数据

装备表的表结构如下

mysql> desc equip;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id        | int(11)      | YES  |     | NULL    |       |
| name      | varchar(100) | YES  |     | NULL    |       |
| player_id | int(11)      | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

-- 装备表的详细数据
mysql> select * from equip;
+------+-----------------+-----------+
| id   | name            | player_id |
+------+-----------------+-----------+
|    1 | 青龙偃月刀      |       177 |
|    2 | 丈八蛇矛        |       157 |
|    3 | 七星宝刀        |       186 |
|    4 | 长剑            |      NULL |
|    5 | 铁盾            |      NULL |
|    6 | 大师之剑        |        76 |
|    7 | 金箍棒          |       161 |
|    8 | 方天画戟        |       190 |
|    9 | 赤兔马          |       190 |
+------+-----------------+-----------+
9 rows in set (0.00 sec)

inner join内连接

返回表中都有的数据， 如果是null值，那一行值就隐藏掉

使用inner关键字来指定关联的表，然后是on关键字和两个表中关联的字段，最后可以加上where关键字和查询条件

-- 将玩家表和装备表进行关联
mysql> select player.id, player.name, sex,email,level,exp,gold, equip.name as equip
    -> from player inner join equip on player.id = equip.player_id;
+------+-----------+------+---------------------+-------+------+-------+-----------------+
| id   | name      | sex  | email               | level | exp  | gold  | equip           |
+------+-----------+------+---------------------+-------+------+-------+-----------------+
|   76 | 林克      | 男   | linke@qq.com        |    48 |   12 | 11.00 | 大师之剑        |
|  157 | 张飞      | 男   | zhangfei@gmail.com  |    76 |   36 | 80.00 | 丈八蛇矛        |
|  161 | 孙悟空    | 男   | sunwukong@gmail.com |    74 |   32 | 23.00 | 金箍棒          |
|  177 | 关羽      | 男   | guanyu@gmail.com    |    19 |   60 | 36.00 | 青龙偃月刀      |
|  186 | 曹操      | 男   | caocao@geekhour.net |    70 |   15 | 27.00 | 七星宝刀        |
|  190 | 吕布      | 男   |                     |    77 |   43 | 31.00 | 方天画戟        |
|  190 | 吕布      | 男   |                     |    77 |   43 | 31.00 | 赤兔马          |
+------+-----------+------+---------------------+-------+------+-------+-----------------+
7 rows in set (0.00 sec)

-- 方法2 使用where
mysql> select player.id, player.name, sex,email,level,exp,gold, equip.name as equip
    -> from player inner join equip where player.id = equip.player_id;
+------+-----------+------+---------------------+-------+------+-------+-----------------+
| id   | name      | sex  | email               | level | exp  | gold  | equip           |
+------+-----------+------+---------------------+-------+------+-------+-----------------+
|   76 | 林克      | 男   | linke@qq.com        |    48 |   12 | 11.00 | 大师之剑        |
|  157 | 张飞      | 男   | zhangfei@gmail.com  |    76 |   36 | 80.00 | 丈八蛇矛        |
|  161 | 孙悟空    | 男   | sunwukong@gmail.com |    74 |   32 | 23.00 | 金箍棒          |
|  177 | 关羽      | 男   | guanyu@gmail.com    |    19 |   60 | 36.00 | 青龙偃月刀      |
|  186 | 曹操      | 男   | caocao@geekhour.net |    70 |   15 | 27.00 | 七星宝刀        |
|  190 | 吕布      | 男   |                     |    77 |   43 | 31.00 | 方天画戟        |
|  190 | 吕布      | 男   |                     |    77 |   43 | 31.00 | 赤兔马          |
+------+-----------+------+---------------------+-------+------+-------+-----------------+
7 rows in set (0.00 sec)

-- 方法3 使用where并指定别名
mysql> select p.id, p.name, sex,email,level,exp,gold, e.name as equip
    -> from player as p inner join equip as e where p.id = e.player_id;
+------+-----------+------+---------------------+-------+------+-------+-----------------+
| id   | name      | sex  | email               | level | exp  | gold  | equip           |
+------+-----------+------+---------------------+-------+------+-------+-----------------+
|   76 | 林克      | 男   | linke@qq.com        |    48 |   12 | 11.00 | 大师之剑        |
|  157 | 张飞      | 男   | zhangfei@gmail.com  |    76 |   36 | 80.00 | 丈八蛇矛        |
|  161 | 孙悟空    | 男   | sunwukong@gmail.com |    74 |   32 | 23.00 | 金箍棒          |
|  177 | 关羽      | 男   | guanyu@gmail.com    |    19 |   60 | 36.00 | 青龙偃月刀      |
|  186 | 曹操      | 男   | caocao@geekhour.net |    70 |   15 | 27.00 | 七星宝刀        |
|  190 | 吕布      | 男   |                     |    77 |   43 | 31.00 | 方天画戟        |
|  190 | 吕布      | 男   |                     |    77 |   43 | 31.00 | 赤兔马          |
+------+-----------+------+---------------------+-------+------+-------+-----------------+
7 rows in set (0.00 sec)

left join左连接

返回左表中都有的数据和右表匹配的数据，右表中没有的数据用null填充

左表会全部显示

-- 将上面的内连接换成左连接
-- 只取10个
mysql> select player.id, player.name, sex,email,level,exp,gold, equip.name as equip
    -> from player left join equip on player.id = equip.player_id limit 10;
+------+-----------+------+-----------------------+-------+------+-------+-----------------+
| id   | name      | sex  | email                 | level | exp  | gold  | equip           |
+------+-----------+------+-----------------------+-------+------+-------+-----------------+
|  177 | 关羽      | 男   | guanyu@gmail.com      |    19 |   60 | 36.00 | 青龙偃月刀      |
|  157 | 张飞      | 男   | zhangfei@gmail.com    |    76 |   36 | 80.00 | 丈八蛇矛        |
|  186 | 曹操      | 男   | caocao@geekhour.net   |    70 |   15 | 27.00 | 七星宝刀        |
|   76 | 林克      | 男   | linke@qq.com          |    48 |   12 | 11.00 | 大师之剑        |
|  161 | 孙悟空    | 男   | sunwukong@gmail.com   |    74 |   32 | 23.00 | 金箍棒          |
|  190 | 吕布      | 男   |                       |    77 |   43 | 31.00 | 方天画戟        |
|  190 | 吕布      | 男   |                       |    77 |   43 | 31.00 | 赤兔马          |
|    1 | 张三      | 男   | zhangsan@gmail.com    |     3 |   19 | 20.00 | NULL            |
|    2 | 赵四儿    | 男   | zhaosier@geekhour.net |     4 |   22 | 26.00 | NULL            |
|    3 | 王五      | 女   | wangwu@163.com        |    64 |   38 | 15.00 | NULL            |
+------+-----------+------+-----------------------+-------+------+-------+-----------------+
10 rows in set (0.00 sec)

right join右连接

返回右表中所有的数据，以及和左表匹配的数据，左表中没有的数据用null填充

右表会全部显示

-- 将上面的结果换成右连接
mysql> select player.id, player.name, sex,email,level,exp,gold, equip.name as equip
    -> from player right join equip on player.id = equip.player_id;
+------+-----------+------+---------------------+-------+------+-------+-----------------+
| id   | name      | sex  | email               | level | exp  | gold  | equip           |
+------+-----------+------+---------------------+-------+------+-------+-----------------+
|   76 | 林克      | 男   | linke@qq.com        |    48 |   12 | 11.00 | 大师之剑        |
|  157 | 张飞      | 男   | zhangfei@gmail.com  |    76 |   36 | 80.00 | 丈八蛇矛        |
|  161 | 孙悟空    | 男   | sunwukong@gmail.com |    74 |   32 | 23.00 | 金箍棒          |
|  177 | 关羽      | 男   | guanyu@gmail.com    |    19 |   60 | 36.00 | 青龙偃月刀      |
|  186 | 曹操      | 男   | caocao@geekhour.net |    70 |   15 | 27.00 | 七星宝刀        |
|  190 | 吕布      | 男   |                     |    77 |   43 | 31.00 | 方天画戟        |
|  190 | 吕布      | 男   |                     |    77 |   43 | 31.00 | 赤兔马          |
| NULL | NULL      | NULL | NULL                |  NULL | NULL |  NULL | 长剑            |
| NULL | NULL      | NULL | NULL                |  NULL | NULL |  NULL | 铁盾            |
+------+-----------+------+---------------------+-------+------+-------+-----------------+
9 rows in set (0.00 sec)

结合where语句一起使用

需要指定具体表的字段 表名.字段

mysql> select player.id, player.name, sex,email,level,exp,gold, equip.name as equip
    -> from player inner join equip on player.id = equip.player_id where player.name='林克';
+------+--------+------+--------------+-------+------+-------+--------------+
| id   | name   | sex  | email        | level | exp  | gold  | equip        |
+------+--------+------+--------------+-------+------+-------+--------------+
|   76 | 林克   | 男   | linke@qq.com |    48 |   12 | 11.00 | 大师之剑     |
+------+--------+------+--------------+-------+------+-------+--------------+
1 row in set (0.00 sec)

索引

索引是一种用来提高查询效率的数据结构，可以帮助我们快速定位到我们想要的数据，如果没有使用索引，就只能从头开始遍历所有的数据，直到找到满足条件的数据为止。

当数据少的时候当然没有什么问题，如果查询数据量很大的表，查询效率会直线下降，索引就是为了解决这个问题而产生的。

create index 创建索引

可以在建表的时候指定索引，也可以在建表之后添加一个索引

-- 创建索引的语法
CREATE [UNIQUE, FULLTEXT, SPATIAL] INDEX index_name
	ON tbl_name (index_col_name, ...)
/*
	CREATE 		创建索引的关键字
	INDEX  		前面可以加上可选索引的类型
	UNIQUE 		表示唯一索引
	FULLTEXT 	表示全文索引
	SPATIAL		表示空间索引
	inde_name	索引的名称
	ON			关键字后面后面跟上表名，表示要在哪张表上创建索引
				最后是用括号括起来的，一个或者多个字段名，这些字段名就是我们要多哪些字段来创建索引
				一般情况下会对表的主键或者经常查询的字段创建索引（即where后面的查询条件字段），以此来提高查询的效率
*/

-- 给area这张表的province字段创建一个索引
mysql> create index province_index on area(province);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 创建方式2，修改表结构的时候创建
-- area				表的名称
-- province_index   索引的名称
mysql> alter table area add index province_index (province);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

show index 查询索引

-- tbl_name 就就是表的名称
show index from tbl_name 

-- 查询刚刚创建的索引
show index from area;

drop index 删除索引

-- index_name 就是索引的名称 
-- tbl_name就是表的名称
drop index index_name on tbl_name 

-- 删除索引
mysql> drop index province_index on area;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

视图

视图是一种虚拟存在的表，它本身并不包含数据，而是作为一个查询语句，保存在数据字典中。

当我们查询视图的时候，它会根据查询语句的定义，来动态生成数据

而且如果表中的数据发生了变化，视图中的结果也会发生变化，这是视图的特性

create view 创建视图

-- 创建一个玩家表中，等级排行榜前10的视图
-- create view 创建关键字
-- top10	   视图名称
-- 然后后面跟上一个as以及查询语句就可以了

mysql> create view top10
    -> as
    -> select * from player order by level desc limit 10;
Query OK, 0 rows affected (0.03 sec)

查询视图

mysql> select * from top10;
+------+--------------+------+-------------------------+-------+------+-------+
| id   | name         | sex  | email                   | level | exp  | gold  |
+------+--------------+------+-------------------------+-------+------+-------+
|   57 | 阿碧         | 女   | abi@gmail.com           |   100 |    9 | 92.00 |
|  208 | 独孤求败     | 男   | duguqiubai@gmail.com    |   100 |  100 |  1.00 |
|   19 | 佟湘玉       | 女   | tongxiangyu@163.com     |    99 |    4 | 10.00 |
|  164 | 娜可露露     | 女   | nakelulu@qq.com         |    99 |   16 | 33.00 |
|   64 | 风清扬       | 男   | fengqingyang@qq.com     |    99 |   80 | 81.00 |
|  129 | 米莱狄       | 女   | milaidi@qq.com          |    99 |   93 | 31.00 |
|   73 | 钱德勒       | 男   | qiandelmo@gmail.com     |    98 |   91 |  1.00 |
|  154 | 钟馗         | 男   | zhongkui@geekhour.net   |    98 |   76 | 28.00 |
|   10 | 陆展博       | 男   | luzhanbo@geekhour.net   |    98 |   51 | 62.00 |
|  123 | 沈梦溪       | 女   | shenmengxi@geekhour.net |    98 |   47 | 29.00 |
+------+--------------+------+-------------------------+-------+------+-------+
10 rows in set (0.03 sec)

-- 变更数据
mysql> update player set level=100 where name='姬小满';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 视图的数据也会变动
mysql> select * from top10;
+------+--------------+------+-------------------------+-------+------+-------+
| id   | name         | sex  | email                   | level | exp  | gold  |
+------+--------------+------+-------------------------+-------+------+-------+
|   57 | 阿碧         | 女   | abi@gmail.com           |   100 |    9 | 92.00 |
|   94 | 姬小满       | 女   | jixiaoman@geekhour.net  |   100 |   23 | 70.00 |
|  208 | 独孤求败     | 男   | duguqiubai@gmail.com    |   100 |  100 |  1.00 |
|   19 | 佟湘玉       | 女   | tongxiangyu@163.com     |    99 |    4 | 10.00 |
|   64 | 风清扬       | 男   | fengqingyang@qq.com     |    99 |   80 | 81.00 |
|  164 | 娜可露露     | 女   | nakelulu@qq.com         |    99 |   16 | 33.00 |
|  129 | 米莱狄       | 女   | milaidi@qq.com          |    99 |   93 | 31.00 |
|  123 | 沈梦溪       | 女   | shenmengxi@geekhour.net |    98 |   47 | 29.00 |
|   73 | 钱德勒       | 男   | qiandelmo@gmail.com     |    98 |   91 |  1.00 |
|   10 | 陆展博       | 男   | luzhanbo@geekhour.net   |    98 |   51 | 62.00 |
+------+--------------+------+-------------------------+-------+------+-------+
10 rows in set (0.00 sec)

alter view 修改视图

-- 将刚刚的视图修改成从小到大排列
mysql> alter view top10
    -> as
    -> select * from player order by level limit 10;
Query OK, 0 rows affected (0.01 sec)

-- 查看最新的结果
mysql> select * from top10;
+------+-----------+------+-----------------------+-------+------+-------+
| id   | name      | sex  | email                 | level | exp  | gold  |
+------+-----------+------+-----------------------+-------+------+-------+
|  131 | 弈星      | 男   | yixing@geekhour.net   |     1 |   61 | 90.00 |
|   17 | 吕秀才    | 男   | lvxiucai@gmail.com    |     1 |    2 |  3.00 |
|    1 | 张三      | 男   | zhangsan@gmail.com    |     3 |   19 | 20.00 |
|    2 | 赵四儿    | 男   | zhaosier@geekhour.net |     4 |   22 | 26.00 |
|   37 | 慕容复    | 男   | murongfu@gmail.com    |     5 |   49 | 38.00 |
|   87 | 小鱼儿    | 男   | xiaoyuer@163.com      |     6 |   55 |  4.00 |
|   11 | 胡一菲    | 女   | huyifei@163.com       |     6 |   53 | 38.00 |
|   31 | 周伯通    | 男   | zhoubotong@163.com    |     8 |   45 | 70.00 |
|   49 | 安欣      | 男   | anxin@gmail.com       |     8 |   43 | 33.00 |
|   91 | 余则成    | 男   | yuzecheng@163.com     |    10 |   53 | 29.00 |
+------+-----------+------+-----------------------+-------+------+-------+
10 rows in set (0.03 sec)

drop view 删除视图

-- top10就是视图的名称
drop view top10;

mysql> drop view top10;
Query OK, 0 rows affected (0.00 sec)

数据库导入导出

导入命令

-- 语法为：mysql -u root -p 数据库名称 数据.sql 
-- 然后输入密码进行导入

mysql -u root -p db1 company.sql 
-- 要导入的数据库必须存在如果导入遇到错误可以参考下面的解决方案
/*
	错误提示：ERROR 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'
    是因为linux下MySQL版本不兼容导致的
    我们需要做的是打开我们导出的的sql文件

    把文件中的所有的utf8mb4_0900_ai_ci替换为utf8_general_ci
    以及utf8mb4替换为utf8

最后再重新导入成功。
*/

导出命令

-- 导出数据库的命令
-- mysqldump -u root -p 数据库名称 表名称 > game.sql 
-- 如果省略表的名称，就会导出数据库的所有数据
-- eg:  在当前MySQL的根目录下输入，也可以进入到其它目录操作，比如桌面
mysqldump -u root -p game > game.sql

-- mysqldump -u root -d 数据库名称 表名称 > game.sql -p
-- 加上了-d就是代表不含数据的，需要自己往里面插入数据，就是没有insert into 单独的一个表而已

关于SQL的优化方案

1. 查询语句中不要使用select *
2. 尽量减少子查询，使用关联查询替代
3. 减少使用IN或者NOT IN，使用exists、not exists或者关联查询语句替代。
4. or的查询尽量用union或者union all代替。
5. 应该尽量避免在where子句中使用！=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。

pymysql模块

获取数据

1. 安装pymysql：首先，你需要在你的Python环境中安装pymysql库。你可以使用pip命令来安装它：

   pip install pymysql
   

2. 导入pymysql库：在你的Python代码中，导入pymysql库以便使用它：

   import pymysql
   

3. 建立数据库连接：使用pymysql库的connect()函数来建立与MySQL数据库的连接。你需要提供MySQL数据库的主机名、用户名、密码和数据库名称。

   connection = pymysql.connect(host='hostname', user='username', password='password', database='database_name')
   

4. 创建游标对象：使用连接对象的cursor()方法创建一个游标对象，它用于执行SQL查询和获取结果。

   cursor = connection.cursor()
   

5. 执行SQL查询：使用游标对象的execute()方法执行SQL查询。你可以将SQL查询作为字符串传递给execute()方法。

   sql_query = "SELECT * FROM table_name"
   cursor.execute(sql_query)
   

6. 获取查询结果：使用游标对象的fetchall()方法获取执行SQL查询的结果。它返回一个包含查询结果的元组列表。

   results = cursor.fetchall()
   for row in results:
       # 处理每一行的数据
       print(row)
   

7. 提交更改和关闭连接：如果你对数据库进行了修改（如插入、更新或删除操作），需要使用连接对象的commit()方法提交更改。最后，使用连接对象的close()方法关闭数据库连接。

   connection.commit()
   connection.close()
   

插入数据到数据库

要使用pymysql插入数据到MySQL数据库，你可以按照以下步骤进行操作：

1. 建立数据库连接：使用pymysql库的connect()函数建立与MySQL数据库的连接。你需要提供MySQL数据库的主机名、用户名、密码和数据库名称。

   import pymysql
   
   connection = pymysql.connect(host='hostname', user='username', password='password', database='database_name')
   

2. 创建游标对象：使用连接对象的cursor()方法创建一个游标对象，它用于执行SQL查询和获取结果。

   cursor = connection.cursor()
   

3. 编写插入语句：使用SQL的INSERT INTO语句编写要插入的数据。你可以使用参数化查询来安全地插入数据，避免SQL注入攻击。

   insert_query = "INSERT INTO table_name (column1, column2, column3) VALUES (%s, %s, %s)"
   

   这里的column1、column2和column3是你要插入数据的列名。

4. 执行插入操作：使用游标对象的execute()方法执行插入语句。将插入语句和要插入的数据作为参数传递给execute()方法。

   data = ('value1', 'value2', 'value3')
   cursor.execute(insert_query, data)
   

   这里的value1、value2和value3是你要插入的具体值。

5. 提交更改和关闭连接：如果你对数据库进行了修改（如插入、更新或删除操作），需要使用连接对象的commit()方法提交更改。最后，使用连接对象的close()方法关闭数据库连接。

   connection.commit()
   connection.close()
   

lastrowid 获取上一次的自增id

sql = 'insert into student(sname, gender, class_id) values (%s, %s, %s)'
cursor.execute(sql, ('小满', '女', 2))
conn.commit()
print(cursor.lastrowid)  # 获取上一次的主键自增id 结果是4

mysql> select * from student;
+-----+--------+--------+----------+
| sid | sname  | gender | class_id |
+-----+--------+--------+----------+
|   1 | 小满   | 女     |        1 |
|   2 | 大乔   | 女     |        1 |
|   3 | 庄周   | 男     |        2 |
|   4 | 小满   | 女     |        2 |
+-----+--------+--------+----------+
4 rows in set (0.01 sec)
-- 这里的sid 4就是刚刚的自增id 可以通过pymysql的lastrowid获取

基于数据库的登录注册

-- MySQL表的创建
create table user (
    id int auto_increment primary key not null ,
    username varchar(32) not null unique ,
    password int(6) not null
) default char set utf8;

# settings.py
USER = 'root'
PASSWORD = 'xxx'
DATABASE = 'xxx'
HOST = 'localhost'

mysql注入版（不要这要写）

import pymysql
from pymysql.cursors import DictCursor
from settings import *

conn = pymysql.connect(
    db=DATABASE,
    user=USER,
    password=PASSWORD,
    host=HOST,
    charset='utf8',
    cursorclass=DictCursor
)

cs = conn.cursor()


def input_data():
    username = input("请输入用户名：").strip()
    password = input("请输入密码：").strip()
    return username, int(password)


def register():
    username, password = input_data()
    sql = 'insert into user (username, password) values (%s, %s);'
    cs.execute(sql, (username, password))
    print(f"用户[{username}]注册成功。")


def login():
    username, password = input_data()
    # 不要拼在一起
    sql = "select * from user where username='%s and password='%s'" % (username, password)
    print(sql)
    cs.execute(sql)
    result = cs.fetchone()
    if result:
        print(f"[{username}]登录成功")
    else:
        print(f"用户名或者密码错误，登录失败。")



def main():
    func_list = [('注册', register), ('登录', login)]
    for index, item in enumerate(func_list):
        print(f"{index}-{item[0]}")
    try:
        choice = input('输入选项：').strip()
    except Exception as e:
        print(e)
    else:
        func = func_list[int(choice)][1]
        func()
    finally:
        conn.commit()
        cs.close()
        conn.close()


if __name__ == '__main__':
    main()

"""
0-注册
1-登录
输入选项：1
请输入用户名：wertyuiknbvghbhjnbnm' or 1=1 --
请输入密码：1
select * from user where username='wertyuiknbvghbhjnbnm' or 1=1 -- and password='1'
[wertyuiknbvghbhjnbnm' or 1=1 --]登录成功
"""

正常的版本

import pymysql
from pymysql.cursors import DictCursor
from settings import *

conn = pymysql.connect(
    db=DATABASE,
    user=USER,
    password=PASSWORD,
    host=HOST,
    charset='utf8',
    cursorclass=DictCursor
)

cs = conn.cursor()


def input_data():
    username = input("请输入用户名：").strip()
    password = input("请输入密码：").strip()
    return username, int(password)


def register():
    username, password = input_data()
    sql = 'insert into user (username, password) values (%s, %s);'
    cs.execute(sql, (username, password))
    print(f"用户[{username}]注册成功。")


def login():
    username, password = input_data()
    # 不要自己拼，让pymysql内部帮我们拼
    sql = 'select * from user where username=%s and password=%s;'
    cs.execute(sql, (username, password))
    result = cs.fetchone()
    if result:
        print(f"[{username}]登录成功")
    else:
        print(f"用户名或者密码错误，登录失败。")


def main():
    func_list = [('注册', register), ('登录', login)]
    for index, item in enumerate(func_list):
        print(f"{index}-{item[0]}")
    try:
        choice = input('输入选项：').strip()
    except Exception as e:
        print(e)
    else:
        func = func_list[int(choice)][1]
        func()
    finally:
        conn.commit()
        cs.close()
        conn.close()


if __name__ == '__main__':
    main()
 
"""
0-注册
1-登录
输入选项：1
请输入用户名：jjdlskdskdklsdklsdks' or 1=1 --
请输入密码：1
select * from user where username=%s and password=%s;
用户名或者密码错误，登录失败。


0-注册
1-登录
输入选项：1
请输入用户名：eva
请输入密码：112233
select * from user where username=%s and password=%s;
[eva]登录成功
"""

豆瓣电影top250写入MySQL

# common.py的内容
import time
import requests
from lxml import etree
from fake_useragent import UserAgent

data_list = []
urls = [
    "https://movie.douban.com/top250?start={}&filter=".format(n * 25)
    for n in range(10)
]

get_inq = lambda x: x[0] if x else '/'
maps = lambda x: x[0].strip().replace('xa0', '')


def fetch_one_page(url):
    try:
        headers = {"User-Agent": UserAgent().random}
        response = requests.get(url, headers=headers)
    except requests.exceptions.RequestException as e:
        print(f"获取网址{url}时出错。")
        return
    else:
        html = etree.HTML(response.text)
        tags = html.xpath('//div[@class="item"]')
        for tag in tags:
            rank = tag.xpath('./div[1]/em/text()')[0]
            title = tag.xpath('.//a/span[1]/text()')[0]
            author = tag.xpath('.//p[1]/text()')
            inq = tag.xpath('.//span[@class="inq"]/text()')
            score = tag.xpath('.//span[@class="rating_num"]/text()')[0]
            score_person = tag.xpath('.//div[@class="star"]/span[last()]/text()')[0]
            data = [int(rank), title, score_person, score, get_inq(inq), maps(author)]
            data_list.append(data)

    time.sleep(1)

# settings.py模块的内容
USER = 'root'
PASSWORD = 'xxxx'
HOST = 'localhost'
DB = 'spider'
PORT = 3306

# 主模块
import pymysql
import common
import settings
from concurrent.futures import ThreadPoolExecutor

conn = pymysql.connect(
    port=settings.PORT,
    db=settings.DB,
    host=settings.HOST,
    user=settings.USER,
    password=settings.PASSWORD,
    charset='utf8'
)

cs = conn.cursor()

if __name__ == '__main__':
    with ThreadPoolExecutor(10) as pool:
        for url in common.urls:
            pool.submit(common.fetch_one_page, url)

    pool.shutdown()
    common.data_list.sort(key=lambda x: x[0])
    sql = 'INSERT INTO douban_top250 (title, score_person, score, inq, author) values (%s, %s, %s, %s, %s);'
    for data in common.data_list:
        cs.execute(sql, data[1:])
    
    # 一次性插入多条数据
    # cs.executemany(sql, common.data_list)
    else:
        conn.commit()
        cs.close()
        conn.close()