MySQL
MySQL的分类
# 关系型数据库
MySQL、SqlServer、oracle、MariaDB、db2、access、
# 非关系型数据库
MongoDB、Redis、memcache
'''
特点：
关系型：拥有固定的表结构，表与表之间可以建立关系
非关系型：没有固定的表结构，以k:v键值对的形式存储数据
'''
下载与安装
#下载
去挂网下载：https://www.mysql.com/ >>>>>> DOWNLOAD >>>> https://dev.mysql.com/downloads/installer/
# 下载下来之后，是一个zip的压缩包，去解压文件
# mysql目录介绍：
# bin
mysql.exe # 这个是mysql自带的客户端
mysqld.exe # 这个是mysql的服务端
# data
'''它是mysql的存储数据的地方'''
my-defalut.ini # 这个是mysql的配置文件
README # 是说明文件
# 如何使用mysql
1. 先启动服务端，要去到bin路径下找到mysqld.exe启动文件
2. 启动客户端(新建一个cmd窗口出来)，链接服务端(服务端的cmd窗口不能关闭)
3. ctrl + c是停止服务的
4. 把mysqld服务端所在的路径加入到环境变量中
5. 制作系统服务
# 目的：就是可以随时关闭mysql的服务端，达到开启自启动，自关闭
1. 先关闭已经启动的服务端
2. 在打开cmd窗口：
mysqld --install （Install/Remove of the Service Denied!） # 没有权限
'''使用管理员方式启动cmd'''
# 第一次安装成功之后，服务并没有启动，需要手动启动一次
3. 启动服务的方式:
1. 鼠标点击启动按钮
2. net start mysql # （管理员权限）
4. 停止服务
1. 鼠标点击停止
2. net stop mysql # （管理员权限）
5. 如何卸载服务
1. 先关闭服务
2. mysqld --remove
# 如何退出客户端: exit
'''
打开服务的方式：
1. 在底部任务栏右键选择任务管理器 >>>>> 服务
2. 此电脑右键 >>> 管理 >>> 服务和应用程序 >>> 服务
3. win + r >>> 输入：services.msc >>> 回车
'''
修改密码
1. 修改管理员密码
mysqladmin -u用户名 -p旧密码 password 新密码
# mysqladmin -u root -p password 123
2. set password=PASSWORD('密码')
'''管理员密码改完之后，一定要使用管理员账号登录：'''
mysql -u root -p密码
# 直接输入mysql登录是游客模式
# 以后都用管理员模式登录，不要使用游客模式，游客模式只能查看，权限很低
'''忘记密码了怎么办？'''
1. 先停止服务端
2. 用'跳过授权表'的形式再次启动服务端
mysqld --skip-grant-tables
'跳过授权表' >>>: 客户端在登录mysql的时候，只需要输入用户名即可，不在验证密码
3. 用管理形式登录成功之后，进行修改密码
# 下面的命令先了解，你看不懂
update mysql.user set password=password('123') where Host='localhost' and User='root';
4. 退出客户端，终止服务端
5. 不要在使用跳过授权表了，正常启动服务
MySQL的基本操作
统一字符
1. 修改配置文件
[mysqld]
default-character-set=utf8mb4 #utf8mb4:表情字符
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
#2. 重启服务
#3. 查看修改结果：
# show variables like '%char%'
# 配置文件： /etc/my.cnf
# sql语句:
# select user(); 查看当前用户
#\c:取消sql语句
# 查看当前在哪个库下
select database();
库（文件夹）
增：create database db1 charset utf8mb4(字符编码类型);
查：show databases;
show create dabatabse db1; # 查看具体库的信息
改：alter database db1 charset latin1;
删除: drop database db1;
表 （文件）
# 注：要先切换到库（文件夹下面）：use db1; 或者使用绝对路：db1.ti
增：create table t1(id int,name varchar(16)); # int为数字类型 varchar为字符类型16）为字段长度
查：show tables;
desc t1;
改：alter table t1 rename tt1; # 重命名表名
删：drop table t1;
记录（文件中的内容）
增：insert t1 values(1,'egon1'),(2,'egon2'),(3,'egon3'); #不在当前库用绝对路径
查：select * from t1; # 查看此表的所以内容
改：update t1 set name='sb' where id=2; # where：定位
删：delete from t1 where id=1;
清空表
delete from t1; #如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。
truncate table t1;数据量大，删除速度比上一条快，且直接从零开始，
创建远程连接用户
grant all privileges on *.* to 'gjl'@'%' identified by '123';
字符编码与配置文件
\s 查看MySQL相关信息
--------------
mysql Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL)
Connection id: 27
Current database:
Current user: root@172.17.0.1
SSL: Cipher in use is DHE-RSA-AES128-GCM-SHA256
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.41 MySQL Community Server (GPL)
Protocol version: 10
Connection: 127.0.0.1 via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
TCP port: 3308
Binary data as: Hexadecimal
Uptime: 2 days 20 hours 59 min 34 sec
Threads: 1 Questions: 87 Slow queries: 0 Opens: 118 Flush tables: 1 Open tables: 110 Queries per second avg: 0.000
--------------
# 修改字符编码
将my-default.ini备份一份，文件名修改为my.ini
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
MySQL存储引擎
# 存储引擎
数据库针对数据采取的多种存取方式
# 查看常见的存储引擎方式
show engines;
# 存储引擎
01）InnoDB(5.5之后默认的)
支持事务,其设计目标主要面向联机事务处理(OLTP)的应用。其
特点是行锁设计、支持外键,并支持类似 Oracle 的非锁定读,即默认读取操作不会产生锁。
从 MySQL 5.5.8 版本开始是默认的存储引擎。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包括很多更新和删除操作，那么InnoDB存储引擎是比较合适的。InnoDB除了有效的降低由删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似计费系统或者财务系统等对数据准确要求性比较高的系统都是合适的选择。
InnoDB 存储引擎将数据放在一个逻辑的表空间中,这个表空间就像黑盒一样由 InnoDB 存储引擎自身来管理。从 MySQL 4.1(包括 4.1)版本开始,可以将每个 InnoDB 存储引擎的 表单独存放到一个独立的 ibd 文件中。此外,InnoDB 存储引擎支持将裸设备(row disk)用 于建立其表空间。
InnoDB 通过使用多版本并发控制(MVCC)来获得高并发性,并且实现了 SQL 标准 的 4 种隔离级别,默认为 REPEATABLE 级别,同时使用一种称为 netx-key locking 的策略来 避免幻读(phantom)现象的产生。除此之外,InnoDB 存储引擎还提供了插入缓冲(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead) 等高性能和高可用的功能。
对于表中数据的存储,InnoDB 存储引擎采用了聚集(clustered)的方式,每张表都是按 主键的顺序进行存储的,如果没有显式地在表定义时指定主键,InnoDB 存储引擎会为每一 行生成一个 6 字节的 ROWID,并以此作为主键。
InnoDB 存储引擎是 MySQL 数据库最为常用的一种引擎,Facebook、Google、Yahoo 等 公司的成功应用已经证明了 InnoDB 存储引擎具备高可用性、高性能以及高可扩展性。对其 底层实现的掌握和理解也需要时间和技术的积累。如果想深入了解 InnoDB 存储引擎的工作 原理、实现和应用,可以参考《MySQL 技术内幕:InnoDB 存储引擎》一书。
02）MyISAM
只是读取和插入，不做修改和删除使用这个，MyISAM不支持事务、表锁设计、支持全文索引,主要面向一些 OLAP 数 据库应用,在 MySQL 5.5.8 版本之前是默认的存储引擎(除 Windows 版本外)。数据库系统 与文件系统一个很大的不同在于对事务的支持,MyISAM 存储引擎是不支持事务的。究其根 本,这也并不难理解。用户在所有的应用中是否都需要事务呢?在数据仓库中,如果没有 ETL 这些操作,只是简单地通过报表查询还需要事务的支持吗?此外,MyISAM 存储引擎的 另一个与众不同的地方是,它的缓冲池只缓存(cache)索引文件,而不缓存数据文件,这与 大多数的数据库都不相同。
03）MEMORY 支持hash索引，使用redis替换
正如其名,Memory 存储引擎中的数据都存放在内存中,数据库重 启或发生崩溃,表中的数据都将消失。它非常适合于存储 OLTP 数据库应用中临时数据的临时表,也可以作为 OLAP 数据库应用中数据仓库的维度表，可以提供极快的访问。Memory的缺陷是对表的大小有限制，虽然数据库因为异常终止的话数据可以正常恢复，但是一旦数据库关闭，存储在内存中的数据都会丢失。
Memory 存储引擎默认使用哈希 索引,而不是通常熟悉的 B+ 树索引。
04）BLACKHOLE
黑洞存储引擎，可以应用于主备复制中的分发主库。
05）NDB 存储引擎
2003 年,MySQL AB 公司从 Sony Ericsson 公司收购了 NDB 存储引擎。 NDB 存储引擎是一个集群存储引擎,类似于 Oracle 的 RAC 集群,不过与 Oracle RAC 的 share everything 结构不同的是,其结构是 share nothing 的集群架构,因此能提供更高级别的 高可用性。NDB 存储引擎的特点是数据全部放在内存中(从 5.1 版本开始,可以将非索引数 据放在磁盘上),因此主键查找(primary key lookups)的速度极快,并且能够在线添加 NDB 数据存储节点(data node)以便线性地提高数据库性能。由此可见,NDB 存储引擎是高可用、 高性能、高可扩展性的数据库集群系统,其面向的也是 OLTP 的数据库应用类型。
06）Infobright 存储引擎
第三方的存储引擎。其特点是存储是按照列而非行的,因此非常 适合 OLAP 的数据库应用。其官方网站是 http://www.infobright.org/,上面有不少成功的数据 仓库案例可供分析。
07）NTSE 存储引擎
网易公司开发的面向其内部使用的存储引擎。目前的版本不支持事务, 但提供压缩、行级缓存等特性,不久的将来会实现面向内存的事务支持。
08）ARCHIVE
09）FEDERATED
10）EXAMPLE
11）MERGE
12）NDBCLUSTER
13）CSV
#还可以使用第三方存储引擎:
01）MySQL当中插件式的存储引擎类型
02）MySQL的两个分支
03）perconaDB
04）mariaDB
# 建表时指定存储引擎
create databases test;
use test;
create table t1(id int) engine = innodb;
create table t2(id int) engine = myisam;
create table t3(id int) engine = memory;
create table t4(id int) engine = balckhole;
'''
1.innodf两个文件
.frm 表结构
.ibd 表数据(表索引)
2.myisam三个文件
.frm 表结构
.MYD 表数据
.MYI 表索引
3.memory一个文件
.frm 表结构
4.balckhole一个文件
.frm 表结构
'''
# 查看正在使用的存储引擎
show variables like 'storage_engine%';
或者
SELECT @@default_storage_engine;
# 查看innodb的表有哪些，表information_schema.tables
select table_schema,table_name,engine from information_schema.tables where engine='innodb';
# 查看myisam的表有哪些，表information_schema.tables
select table_schema,table_name,engine from information_schema.tables where engine='myisam';
# 查看表的存储引擎
SHOW CREATE TABLE db1.t1\G
或者
SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1' AND TABLE_SCHEMA = 'db1'\G
数据类型
整型
# 整型的类型(默认带一个字节的负号)：tinyint(1个字节)、smallint(2个字节)、int(4个字节)、bigint(8个字节)
# unsigned：设置带不带符号
# Zerofill：类型括号加数字是显示宽度，如果存的数据大于这个数字就是展示这个数字本身，如果小于这个数据就用0来填充
========================================
tinyint[(m)] [unsigned] [zerofill]
小整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-128 ～ 127
无符号：
0 ～ 255
========================================
int[(m)][unsigned][zerofill]
整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-2147483648 ～ 2147483647
无符号：
0 ～ 4294967295
========================================
bigint[(m)][unsigned][zerofill]
大整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-9223372036854775808 ～ 9223372036854775807
无符号：
0 ～ 18446744073709551615
=========有符号和无符号tinyint==========
#tinyint默认为有符号
MariaDB [db1]> create table t1(x tinyint); #默认为有符号，即数字前有正负号
MariaDB [db1]> desc t1;
MariaDB [db1]> insert into t1 values
-> (-129),
-> (-128),
-> (127),
-> (128);
MariaDB [db1]> select * from t1;
+------+
| x |
+------+
| -128 | #-129存成了-128
| -128 | #有符号，最小值为-128
| 127 | #有符号，最大值127
| 127 | #128存成了127
+------+
========================================
#设置无符号tinyint
MariaDB [db1]> create table t2(x tinyint unsigned);
MariaDB [db1]> insert into t2 values
-> (-1),
-> (0),
-> (255),
-> (256);
MariaDB [db1]> select * from t2;
+------+
| x |
+------+
| 0 | -1存成了0
| 0 | #无符号，最小值为0
| 255 | #无符号，最大值为255
| 255 | #256存成了255
+------+
============有符号和无符号int=============
#int默认为有符号
MariaDB [db1]> create table t3(x int); #默认为有符号整数
MariaDB [db1]> insert into t3 values
-> (-2147483649),
-> (-2147483648),
-> (2147483647),
-> (2147483648);
MariaDB [db1]> select * from t3;
+-------------+
| x |
+-------------+
| -2147483648 | #-2147483649存成了-2147483648
| -2147483648 | #有符号，最小值为-2147483648
| 2147483647 | #有符号，最大值为2147483647
| 2147483647 | #2147483648存成了2147483647
+-------------+
#设置无符号int
MariaDB [db1]> create table t4(x int unsigned);
MariaDB [db1]> insert into t4 values
-> (-1),
-> (0),
-> (4294967295),
-> (4294967296);
MariaDB [db1]> select * from t4;
+------------+
| x |
+------------+
| 0 | #-1存成了0
| 0 | #无符号，最小值为0
| 4294967295 | #无符号，最大值为4294967295
| 4294967295 | #4294967296存成了4294967295
+------------+
==============有符号和无符号bigint=============
MariaDB [db1]> create table t6(x bigint);
MariaDB [db1]> insert into t5 values
-> (-9223372036854775809),
-> (-9223372036854775808),
-> (9223372036854775807),
-> (9223372036854775808);
MariaDB [db1]> select * from t5;
+----------------------+
| x |
+----------------------+
| -9223372036854775808 |
| -9223372036854775808 |
| 9223372036854775807 |
| 9223372036854775807 |
+----------------------+
MariaDB [db1]> create table t6(x bigint unsigned);
MariaDB [db1]> insert into t6 values
-> (-1),
-> (0),
-> (18446744073709551615),
-> (18446744073709551616);
MariaDB [db1]> select * from t6;
+----------------------+
| x |
+----------------------+
| 0 |
| 0 |
| 18446744073709551615 |
| 18446744073709551615 |
+----------------------+
======用zerofill测试整数类型的显示宽度=============
MariaDB [db1]> create table t7(x int(3) zerofill);
MariaDB [db1]> insert into t7 values
-> (1),
-> (11),
-> (111),
-> (1111);
MariaDB [db1]> select * from t7;
+------+
| x |
+------+
| 001 |
| 011 |
| 111 |
| 1111 | #超过宽度限制仍然可以存
+------+
浮点型
# 浮点型的类型：float、double、decimal
# 建表
'''总共255位，小数占30位'''
create table t1(id float(255,30));
create table t2(id double(255,30));
create table t3(id decimal(65,30));
'''总共65位，小数占30位'''
# 查看表的详细信息
mysql> desc t1;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id | float(255,30) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> desc t2;
+-------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| id | double(255,30) | YES | | NULL | |
+-------+----------------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> desc t3;
+-------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| id | decimal(65,30) | YES | | NULL | |
+-------+----------------+------+-----+---------+-------+
# 插入数据
mysql> insert into t1 values(1.1111111111111111111);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 values(1.1111111111111111111);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t3 values(1.1111111111111111111);
Query OK, 1 row affected (0.01 sec)
# 查看数据
mysql> select * from t1;
+----------------------------------+
| id |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select * from t2;
+----------------------------------+
| id |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select * from t3;
+----------------------------------+
| id |
+----------------------------------+
| 1.111111111111111111100000000000 |
+----------------------------------+
1 row in set (0.00 sec)
# 结论(精确度不一样)
decimal ---> double ----> float
字符串
# char、varchar
char：定长，超出4位报错(mysql 5.6之前不报错)，不足4位空格填补
varchar：可变长，超出与不足都是有几位存几位
# 建表
mysql> create table t4(name char(4));
Query OK, 0 rows affected (0.03 sec)
mysql> create table t5(name varchar(4));
Query OK, 0 rows affected (0.01 sec)
# 插入数据
mysql> insert into t4 values('xxxxx');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into t4 values('x');
Query OK, 1 row affected (0.02 sec)
mysql> insert into t5 values('xxxxx');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into t5 values('x');
Query OK, 1 row affected (0.00 sec)
# 查看数据(char插入不足的数据需要用函数来查看长度 char_length )
mysql> select char_length(name) from t4;
+-------------------+
| char_length(name) |
+-------------------+
| 4 |
| 4 |
+-------------------+
2 rows in set (0.01 sec)
mysql> select * from t4;
+------+
| name |
+------+
| xxxx |
| x |
+------+
2 rows in set (0.00 sec)
'''
set global sql_mode='strict_trans_tables,pad_char_to_full_length'; # 临时修改
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 写入配置文件[一定要写在mysqld下面]
strict_trans_tables：严格模式，mysql5.6之前定义char长度超过不会报错，设置了之后就会报错
pad_char_to_full_length:我们在定义表某一个字段为 char 类型的时候,会往右填充空格来满足你指定的字符长度, 例如指定长度为5, 存大于5字符报错, 小于5就以空格填充到5个字符存储,但在查询时, 查出的结果会自动删除尾部的空格,设置PAD_CHAR_TO_FULL_LENGTH可以让其不删除空格显示
'''
mysql> select char_length(name) from t5;
+-------------------+
| char_length(name) |
+-------------------+
| 4 |
| 4 |
+-------------------+
2 rows in set (0.01 sec)
mysql> select char_length(name) from t5;
+-------------------+
| char_length(name) |
+-------------------+
| 4 |
| 1 |
+-------------------+
2 rows in set (0.00 sec)
日期
# 日期的类型：datatime、time、year、data
# 建表
mysql> create table t6 (id int,
-> reg_time date,
-> login_time datetime,
-> logout_time time,
-> birth_day year
-> );
Query OK, 0 rows affected (0.02 sec)
# 插数据
mysql> insert into t6 values(1, '2023-04-04','2023-04-04 11:11:11', '11:11:11', 1995);
Query OK, 1 row affected (0.01 sec)
# 查数据
mysql> select * from t6;
+------+------------+---------------------+-------------+-----------+
| id | reg_time | login_time | logout_time | birth_day |
+------+------------+---------------------+-------------+-----------+
| 1 | 2023-04-04 | 2023-04-04 11:11:11 | 11:11:11 | 1995 |
+------+------------+---------------------+-------------+-----------+
1 row in set (0.00 sec)
枚举与集合
# 枚举(enum())：多选一
# 建表
mysql> create table t7(id int, gender enum('male','female','other'));
Query OK, 0 rows affected (0.03 sec)
# 插入数据
mysql> desc t7;
+--------+-------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| gender | enum('male','female','other') | YES | | NULL | |
+--------+-------------------------------+------+-----+---------+-------+
'''如果插入的数据不是enum里的会报错，多插入也会报错'''
mysql> insert into t7 values(1,'男');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
mysql> insert into t7 values(2,'male, female');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
mysql> insert into t7 values(1,'male');
Query OK, 1 row affected (0.01 sec)
# 集合(set())：多选多
# 建表
mysql> create table t8(id int, hobby set('music','ball','rap'));
Query OK, 0 rows affected (0.03 sec)
# 插入数据
mysql> desc t8;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| hobby | set('music','ball','rap') | YES | | NULL | |
+-------+---------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
'''插入的数据不在set里会报错'''
mysql> insert into t8 values(1,'eat');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1
# 单插入与多插入
mysql> insert into t8 values(1,'rap');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t8 values(2,'rap,music');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t8;
+------+-----------+
| id | hobby |
+------+-----------+
| 1 | rap |
| 2 | music,rap |
+------+-----------+
2 rows in set (0.00 sec)
创建表的完整语法
create table 表名(
字段名 字段类型(数字) 约束条件,
字段名 字段类型(数字) 约束条件,
字段名 字段类型(数字) 约束条件
);
1.字段名和字段类型是必须的
2.数字和约束条件是可选等等
3.约束条件可以写多个 空格隔开
4.最后一行结尾不能加逗号
约束条件
无符号与零填充
# 无符号 unsigned
# 建表
mysql> create table t1(id int unsigned);
Query OK, 0 rows affected (0.04 sec)
# 插数据
mysql> insert into t1 values(-2),(4);
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 1
mysql> select * from t1;
+------+
| id |
+------+
| 0 |
| 4 |
+------+
4 rows in set (0.00 sec)
# 插入的-2变成了0，表示不支持符号
# 零填充 zerofill
# 建表
mysql> create table t2(id int(4) zerofill);
Query OK, 0 rows affected (0.04 sec)
# 插数据
mysql> insert into t2 values(8);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t2;
+------+
| id |
+------+
| 0008 |
+------+
2 rows in set (0.00 sec)
# int(展示的长度)，如果数据展示的长度不足int括号里的，将会用0来填充
非空
# MySQL中NULL相当于python中的None(空)
# 建表
mysql> create table t1(id int, name varchar(10));
Query OK, 0 rows affected (0.02 sec)
# 插入数据
mysql> create table t1(id int, name varchar(10));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1 values(1,'jack');
Query OK, 1 row affected (0.01 sec)
# 指定字段插入数据
mysql> insert into t1(id) values(2);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | jack |
| 2 | NULL |
+------+------+
2 rows in set (0.00 sec)
'''所有字段不加约束条件，默认情况下可以为空'''
# 加约束建表
mysql> create table t2(id int, name varchar(10) not null);
Query OK, 0 rows affected (0.02 sec)
mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
# 插数据
mysql> insert into t2(id) values(1);
ERROR 1364 (HY000): Field 'name' doesn't have a default value
######################################################
mysql> insert into t2 values(1,'');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t2;
+------+------+
| id | name |
+------+------+
| 1 | |
+------+------+
1 row in set (0.00 sec)
默认值
# 默认值default
'''设置字段有默认值后，则无论子段是null还是not null，都可以插入空，插入空默认填入default指定的默认值，默认值应该是字段声明的数据类型'''
# 建表
mysql> create table t3(id int default 8, name varchar(10) default 'gjl');
Query OK, 0 rows affected (0.04 sec)
# 插入数据
mysql> desc t3;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | 8 | |
| name | varchar(10) | YES | | gjl | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
# 只给name字段插入数据
mysql> insert into t3(name) values('jason');
Query OK, 1 row affected (0.01 sec)
# 只给id字段插入数据
mysql> insert into t3(id) values(1);
Query OK, 1 row affected (0.01 sec)
# 查看数据
mysql> select * from t3;
+------+-------+
| id | name |
+------+-------+
| 8 | jason |
| 1 | gjl |
+------+-------+
2 rows in set (0.00 sec)
唯一值
# 设置某种字段的值不能重复
##########################################单列唯一#######################################################
# 建表
mysql> create table t1(id int unique, name varchar(10));
Query OK, 0 rows affected (0.02 sec)
# 插数据
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert into t1 values(1,'xxx');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | xxx |
+------+------+
1 row in set (0.00 sec)
# 尝试插入id是1的数据
mysql> insert into t1 values(1,'www');
ERROR 1062 (23000): Duplicate entry '1' for key 'id'
##########################################联合唯一#######################################################
mysql> create table t3(id int, ip varchar(32), port int, unique(ip,port));
Query OK, 0 rows affected (0.05 sec)
mysql> desc t3;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| ip | varchar(32) | YES | MUL | NULL | |
| port | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> insert into t3 values(1,'127.0.0.1',80),(2,'127.0.0.2',80),(3,'127.0.0.1',81);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t3;
+------+-----------+------+
| id | ip | port |
+------+-----------+------+
| 1 | 127.0.0.1 | 80 |
| 2 | 127.0.0.2 | 80 |
| 3 | 127.0.0.1 | 81 |
+------+-----------+------+
3 rows in set (0.00 sec)
mysql> insert into t3 values(4,'127.0.0.1',80);
ERROR 1062 (23000): Duplicate entry '127.0.0.1-80' for key 'ip'
'''当unique()中的多个值，只有一个相同时不会报错，当多个值同时相同插入数据就会报错'''
主键
# primary key(非空且唯一)
'''
1.主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引组织表，一张表中必须有且只有一个主键。
2.建表时没有指定主键，innodb存储引擎会自上而下的扫描字段直到找一个不为空且唯一的字段作为主键，如果没这种字段，那么innodb存储引擎会创建一个8个字节的隐藏字段然后将这个字段作为主键。
3.约束效果等同于：not null + unique
4.加速查询
'''
# 约束效果: not null + unique (非空且唯一)
mysql> create table t1(id int primary key, name varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql> insert into t1(name) values('gjl');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
'''id字段没有给值，默认不能是空'''
mysql> insert into t1 values(1,'gjl');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 values(1,'test');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
'''id字段的值重复'''
自增
# auto_increment(自增)：约束字段为自动增长，被约束的字段必须同时被key约束，一张表中只能出现一次
mysql> create table t2(id int primary key auto_increment, name varchar(16), sex enum('male','female') default 'male');
Query OK, 0 rows affected (0.04 sec)
mysql> desc t2;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(16) | YES | | NULL | |
| sex | enum('male','female') | YES | | male | |
+-------+-----------------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)
# 当不指定id字段时，则自动增长
mysql> insert into t2(name) values('jason'),('tom');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t2;
+----+-------+------+
| id | name | sex |
+----+-------+------+
| 1 | jason | male |
| 2 | tom | male |
+----+-------+------+
2 rows in set (0.00 sec)
'''当使用delete删除记录之后，再没有指定id插入数据时，会接着上一次删除前的位置增长'''
mysql> delete from t2;
Query OK, 2 rows affected (0.01 sec)
mysql> select * from t2;
Empty set (0.00 sec)
mysql> insert into t2(name) values('jack'),('tonk');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t2;
+----+------+------+
| id | name | sex |
+----+------+------+
| 3 | jack | male |
| 4 | tonk | male |
+----+------+------+
2 rows in set (0.00 sec)
'''这时候就需要用truncate来删除数据'''
mysql> truncate t2;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from t2;
Empty set (0.00 sec)
mysql> insert into t2(name) values('gjl'),('dzy');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t2;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | gjl | male |
| 2 | dzy | male |
+----+------+------+
2 rows in set (0.00 sec)
补充的SQL语句
# 添加字段
alter table t1 add gender varchar(16) after id;
# after：字段添加在哪个子段后面
# first：添加在第一个
# 删除字段
alter table t1 drop gender;
# 修改字段
alter table t1 modify name char(16); # 只能修改字段的数据类型, 不能改字段名
alter table t1 change name name1 char(16); # change可以改字段名
表与表之间的关系
多对一：单向的foreign key
create table class(
id int primary key auto_increment,
name varchar(20),
room int
);
create table student(
id int primary key auto_increment,
name varchar(16),
age int,
gender enum("male","female"),
class_id int,
foreign key(class_id) references class(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
# 注：以上面案例student表中的class_id与class表中的id建立了关系，就要先建立class表
多对多：建立中间表，双向的foreign key
create table book(
id int primary key auto_increment,
name varchar(20)
);
create table author(
id int primary key auto_increment,
name varchar(16)
);
create table book2author(
id int primary key auto_increment,
book_id int,
author_id int,
foreign key(book_id) references book(id) on delete cascade on update cascade,
foreign key(author_id) references author(id) on delete cascade on update cascade
);
一对一：foreign key+unique
create table customer(
id int primary key auto_increment,
name varchar(20),
tel int
);
create table student(
id int primary key auto_increment,
class varchar(10),
customer_id int unique,
foreign key(customer_id) references customer(id) on update cascade on delete cascade
);
关键字
数据准备
mysql> create table emp(
-> id int primary key auto_increment,
-> name varchar(20) not null,
-> sex enum('male','female') not null default 'male',
-> age smallint(3) unsigned not null default 28,
-> hire_date date not null,
-> post varchar(50),
-> post_comment varchar(100),
-> salary double(15,2),
-> office int,
-> depart_id int
-> );
Query OK, 0 rows affected (0.16 sec)
mysql> insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
-> ('tom','male',78,'20150302','teacher',1000000.31,401,1),#以下是教学部
-> ('kevin','male',81,'20130305','teacher',8300,401,1),
-> ('tony','male',73,'20140701','teacher',3500,401,1),
-> ('owen','male',28,'20121101','teacher',2100,401,1),
-> ('jack','female',18,'20110211','teacher',9000,401,1),
-> ('jenny','male',18,'19000301','teacher',30000,401,1),
-> ('sank','male',48,'20101111','teacher',10000,401,1),
-> ('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
-> ('呵呵','female',38,'20101101','sale',2000.35,402,2),
-> ('西西','female',18,'20110312','sale',1000.37,402,2),
-> ('乐乐','female',18,'20160513','sale',3000.29,402,2),
-> ('拉拉','female',28,'20170127','sale',4000.33,402,2),
-> ('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
-> ('程咬金','male',18,'19970312','operation',20000,403,3),
-> ('程咬银','female',18,'20130311','operation',19000,403,3),
-> ('程咬铜','male',18,'20150411','operation',18000,403,3),
-> ('程咬铁','female',18,'20140512','operation',17000,403,3);
Query OK, 17 rows affected (0.02 sec)
Records: 17 Duplicates: 0 Warnings: 0
mysql> select * from emp;
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| 1 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 2 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 3 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 4 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 5 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 6 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 7 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 8 | 哈哈 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 9 | 呵呵 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 10 | 西西 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 11 | 乐乐 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 12 | 拉拉 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 13 | 僧龙 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 14 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 15 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 16 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 17 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
17 rows in set (0.00 sec)
查询关键字之where筛选
# where 筛选
"""
模糊查询:没有明确的筛选条件
关键字:like
关键符号:
%:匹配任意个数任意字符
_:匹配单个个数任意字符
show variables like '%mode%';
"""
# 1.查询id大于等于3小于等于6的数据
mysql> select * from emp where id >=3 and id <=6;
mysql> select * from emp where id between 3 and 6;
+----+-------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| 3 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 4 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 5 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 6 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
+----+-------+--------+-----+------------+---------+--------------+----------+--------+-----------+
# 2.查询薪资是20000或者18000或者17000的数据
mysql> select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
mysql> select * from emp where salary in(20000,18000,17000);
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 14 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 16 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 17 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
mysql> # 3.查询员工姓名中包含o字母的员工姓名和薪资
mysql> select * from emp where name like '%o%';
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| 1 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 3 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 4 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
# 3.查询员工姓名中包含o字母的员工姓名和薪资
mysql> select * from emp where name like '%o%';
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| 1 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 3 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 4 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
# 4.查询员工姓名是由四个字符组成的员工姓名与其薪资
mysql> select * from emp where name like '____';
mysql> select * from emp where char_length(name)=4;
+----+------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| 3 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 4 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 5 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 7 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
+----+------+--------+-----+------------+---------+--------------+----------+--------+-----------+
# 5.查询id小于3或者大于6的数据
mysql> select * from emp where id < 3 or id >6;
mysql> select * from emp where id not between 3 and 6;
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| 1 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 2 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 7 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 8 | 哈哈 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 9 | 呵呵 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 10 | 西西 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 11 | 乐乐 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 12 | 拉拉 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 13 | 僧龙 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 14 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 15 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 16 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 17 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
# 6.查询薪资不在20000，18000，17000范围的数据
mysql> select * from emp where salary != 20000 and salary != 18000 and salary != 17000;
mysql> select * from emp where salary not in (20000,18000,17000);
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| 1 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 2 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 3 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 4 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 5 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 6 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 7 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 8 | 哈哈 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 9 | 呵呵 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 10 | 西西 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 11 | 乐乐 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 12 | 拉拉 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 13 | 僧龙 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 15 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
# 7.查询岗位描述为空的员工名与岗位名 针对null不能用等号，只能用is
mysql> select * from emp where post_comment is null;
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| 1 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 2 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 3 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 4 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 5 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 6 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 7 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 8 | 哈哈 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 9 | 呵呵 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 10 | 西西 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 11 | 乐乐 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 12 | 拉拉 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 13 | 僧龙 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 14 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 15 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 16 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 17 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
查询关键字之group by分组
# group by 分组
"""
按照某个指定的条件将单个单个的个体分成一个个整体
eg: 按照男女将人分组
按照肤色分组
按照年龄分组
"""
# 分组之后默认只能够直接过去到分组的依据 其他数据都不能直接获取
针对5.6需要自己设置sql_mode
set global sql_mode = 'only_full_group_by,STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';
# 聚合函数
聚合函数主要就是配合分组一起使用
max min sum count avg
# 1.按部门分组
# 分组后取出的是每个组的第一条数据
mysql> select post from emp group by post;
+-----------+
| post |
+-----------+
| operation |
| sale |
| teacher |
+-----------+
# 2.获取每个部门的最高工资
mysql> select post,max(salary) from emp group by post;
+-----------+-------------+
| post | max(salary) |
+-----------+-------------+
| operation | 20000.00 |
| sale | 4000.33 |
| teacher | 1000000.31 |
+-----------+-------------+
# 每个部门的最低工资
mysql> select post,min(salary) from emp group by post;
+-----------+-------------+
| post | min(salary) |
+-----------+-------------+
| operation | 10000.13 |
| sale | 1000.37 |
| teacher | 2100.00 |
+-----------+-------------+
# 每个部门的平均工资
mysql> select post, avg(salary) from emp group by post;
+-----------+---------------+
| post | avg(salary) |
+-----------+---------------+
| operation | 16800.026000 |
| sale | 2600.294000 |
| teacher | 151842.901429 |
+-----------+---------------+
# 每个部门的工资总和
mysql> select post, sum(salary) from emp group by post;
+-----------+-------------+
| post | sum(salary) |
+-----------+-------------+
| operation | 84000.13 |
| sale | 13001.47 |
| teacher | 1062900.31 |
+-----------+-------------+
# 每个部门的人数
mysql> select post, count(*) from emp group by post;
+-----------+----------+
| post | count(*) |
+-----------+----------+
| operation | 5 |
| sale | 5 |
| teacher | 7 |
+-----------+----------+
# group_concat 分组之后使用,按照岗位分组，并查看组内成员名
mysql> select post,group_concat(name) from emp group by post;
+-----------+------------------------------------------------+
| post | group_concat(name) |
+-----------+------------------------------------------------+
| operation | 程咬铁,程咬铜,程咬银,程咬金,僧龙 |
| sale | 拉拉,乐乐,西西,呵呵,哈哈 |
| teacher | sank,jenny,jack,owen,tony,kevin,tom |
+-----------+------------------------------------------------+
# as 起别名
mysql> select post,group_concat(name) as name from emp group by post;
+-----------+------------------------------------------------+
| post | name |
+-----------+------------------------------------------------+
| operation | 程咬铁,程咬铜,程咬银,程咬金,僧龙 |
| sale | 拉拉,乐乐,西西,呵呵,哈哈 |
| teacher | sank,jenny,jack,owen,tony,kevin,tom |
+-----------+------------------------------------------------+
# 分组之后 显示名字和性别
mysql> select post,group_concat(name,'|',sex) from emp group by post;
+-----------+-----------------------------------------------------------------------------+
| post | group_concat(name,'|',sex) |
+-----------+-----------------------------------------------------------------------------+
| operation | 程咬铁|female,程咬铜|male,程咬银|female,程咬金|male,僧龙|male |
| sale | 拉拉|female,乐乐|female,西西|female,呵呵|female,哈哈|female |
| teacher | sank|male,jenny|male,jack|female,owen|male,tony|male,kevin|male,tom|male |
+-----------+-----------------------------------------------------------------------------+
# 修改连接符
mysql> select post,group_concat(distinct name separator ' ') from emp group by post;
+-----------+------------------------------------------------+
| post | group_concat(distinct name separator ' ') |
+-----------+------------------------------------------------+
| operation | 程咬铁 程咬铜 程咬银 程咬金 僧龙 |
| sale | 拉拉 乐乐 西西 呵呵 哈哈 |
| teacher | sank jenny jack owen tony kevin tom |
+-----------+------------------------------------------------+
# concat 不分组使用
mysql> select concat(name,'|',sex) from emp;
+----------------------+
| concat(name,'|',sex) |
+----------------------+
| tom|male |
| kevin|male |
| tony|male |
| owen|male |
| jack|female |
| jenny|male |
| sank|male |
| 哈哈|female |
| 呵呵|female |
| 西西|female |
| 乐乐|female |
| 拉拉|female |
| 僧龙|male |
| 程咬金|male |
| 程咬银|female |
| 程咬铜|male |
| 程咬铁|female |
+----------------------+
# concat_ws()
mysql> select post,concat_ws('|', name, age, post) from emp ;
+-----------+---------------------------------+
| post | concat_ws('|', name, age, post) |
+-----------+---------------------------------+
| teacher | tom|78|teacher |
| teacher | kevin|81|teacher |
| teacher | tony|73|teacher |
| teacher | owen|28|teacher |
| teacher | jack|18|teacher |
| teacher | jenny|18|teacher |
| teacher | sank|48|teacher |
| sale | 哈哈|48|sale |
| sale | 呵呵|38|sale |
| sale | 西西|18|sale |
| sale | 乐乐|18|sale |
| sale | 拉拉|28|sale |
| operation | 僧龙|28|operation |
| operation | 程咬金|18|operation |
| operation | 程咬银|18|operation |
| operation | 程咬铜|18|operation |
| operation | 程咬铁|18|operation |
+-----------+---------------------------------+
关键字之having过滤
"""
where与having都是筛选功能 但是有区别
where在分组之前对数据进行筛选
having在分组之后对数据进行筛选
关键字where group by 同时出现的情况下，group by必须在where之后
where 先对整张表进行一次筛选，group by再对筛选过后的表进行分组
"""
# 统计各部门年龄在30岁以上的员工平均薪资,并且保留平均薪资大于10000的部门.
mysql> select post,avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;
+---------+---------------+
| post | avg(salary) |
+---------+---------------+
| teacher | 255450.077500 |
+---------+---------------+
关键字之order by排序
# asc 升序（默认）
# desc 降序
# 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
mysql> select post,avg(salary) from emp where age >20 group by post having avg(salary) > 4000 order by avg(salary);
+-----------+---------------+
| post | avg(salary) |
+-----------+---------------+
| operation | 10000.130000 |
| teacher | 204780.062000 |
+-----------+---------------+
关键字之limit分页
# 单数字，从0开始展示3条
mysql> select * from emp limit 3;
+----+-------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-------+------+-----+------------+---------+--------------+------------+--------+-----------+
| 1 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 2 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 3 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
+----+-------+------+-----+------------+---------+--------------+------------+--------+-----------+
# 从第0开始，即先查询出第一条，然后包含这一条在内往后查5条
mysql> select * from emp limit 0,5;
+----+-------+--------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-------+--------+-----+------------+---------+--------------+------------+--------+-----------+
| 1 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 2 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 3 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 4 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 5 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
+----+-------+--------+-----+------------+---------+--------------+------------+--------+-----------+
# 从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
mysql> select * from emp limit 5,5;
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| 6 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 7 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 8 | 哈哈 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 9 | 呵呵 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 10 | 西西 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
作业
1. 查询岗位名以及岗位包含的所有员工名字
# select post,group_concat(name) as name from emp group by post;
2. 查询岗位名以及各岗位内包含的员工个数
# select post,count(*) as name from emp group by post;
3. 查询公司内男员工和女员工的个数
# select sex,count(*) as name from emp group by sex;
4. 查询岗位名以及各岗位的平均薪资
# select post,avg(salary) from emp group by post;
5. 查询岗位名以及各岗位的最高薪资
# select post,max(salary) from emp group by post;
6. 查询岗位名以及各岗位的最低薪资
# select post,min(salary) from emp group by post;
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
# select sex,avg(salary) from emp group by sex;
多表查询
多表查询思路：合并两张表，然后去所有可能性，加上where过滤想要的字段
## 重点：外连接语法
select 字段列表
FROM 表1 inner|left|right join 表2
on 表1.字段 = 表2.字段;
内连接查询（inner join）：只保留两表有关系的记录
#找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果
#department没有204这个部门，因而employee表中关于204这条员工信息没有匹配出来
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id;
+----+-----------+------+--------+--------------+
| id | name | age | sex | name |
+----+-----------+------+--------+--------------+
| 1 | egon | 18 | male | 技术 |
| 2 | alex | 48 | female | 人力资源 |
| 3 | wupeiqi | 38 | male | 人力资源 |
| 4 | yuanhao | 28 | female | 销售 |
| 5 | liwenzhou | 18 | male | 技术 |
+----+-----------+------+--------+--------------+
#上述sql等同于
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;
左连接(left inner)：在内链接的基础上增加左表的无效数据
#以左表为准，即找出所有员工信息，当然包括没有部门的员工
#本质就是：在内连接的基础上增加左边有右边没有的结果
mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;
+----+------------+--------------+
| id | name | depart_name |
+----+------------+--------------+
| 1 | egon | 技术 |
| 5 | liwenzhou | 技术 |
| 2 | alex | 人力资源 |
| 3 | wupeiqi | 人力资源 |
| 4 | yuanhao | 销售 |
| 6 | jingliyang | NULL |
+----+------------+--------------+
右链接（right inner）：在内链接的基础上增加右表的无效数据
#以右表为准，即找出所有部门信息，包括没有员工的部门
#本质就是：在内连接的基础上增加右边有左边没有的结果
mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;
+------+-----------+--------------+
| id | name | depart_name |
+------+-----------+--------------+
| 1 | egon | 技术 |
| 2 | alex | 人力资源 |
| 3 | wupeiqi | 人力资源 |
| 4 | yuanhao | 销售 |
| 5 | liwenzhou | 技术 |
| NULL | NULL | 运营 |
+------+-----------+--------------+
全外连接：显示左右两个表全部记录
全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
#注意：mysql不支持全外连接 full JOIN
#强调：mysql可以使用此种方式间接实现全外连接
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id
;
#查看结果
+------+------------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+------------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 6 | jingliyang | female | 18 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
+------+------------+--------+------+--------+------+--------------+
#注意 union与union all的区别：union会去掉相同的纪录
作业
/*
数据导入：
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 50624
Source Host : localhost
Source Database : sqlexam
Target Server Type : MySQL
Target Server Version : 50624
File Encoding : utf-8
Date: 10/21/2016 06:46:46 AM
*/
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`caption` varchar(32) NOT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;
-- ----------------------------
-- Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`cname` varchar(32) NOT NULL,
`teacher_id` int(11) NOT NULL,
PRIMARY KEY (`cid`),
KEY `fk_course_teacher` (`teacher_id`),
CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;
-- ----------------------------
-- Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
`num` int(11) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_score_student` (`student_id`),
KEY `fk_score_course` (`course_id`),
CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `score`
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
COMMIT;
-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`gender` char(1) NOT NULL,
`class_id` int(11) NOT NULL,
`sname` varchar(32) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_class` (`class_id`),
CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `student`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
COMMIT;
-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`tname` varchar(32) NOT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
1、查询所有的课程的名称以及对应的任课老师姓名
select course.cname,teacher.tname from course inner join teacher on course.teacher_id = teacher.tid;
2、查询平均成绩大于八十分的同学的姓名和平均成绩
select student.sname, avg(num) from student inner join score on student.sid = score.student_id group by sname having avg(num) >80;
3、查询没有报李平老师课的学生姓名
SELECT sname FROM student WHERE sid not in (SELECT DISTINCT student_id FROM score WHERE course_id IN (SELECT cid FROM course WHERE teacher_id =(SELECT tid FROM teacher WHERE tname='李平老师')))
4、查询挂科超过两门(包括两门)的学生姓名和班级
select class.caption,student.sname from class inner join student on class.cid = student.class_id WHERE student.sid in (SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count(course_id) >= 2)
**子查询 **
#1：子查询是将一个查询语句嵌套在另一个查询语句中。
#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3：子查询中可以包含：in、not in、any、all、exists 和 not exists等关键字
#4：还可以包含比较运算符：= 、 !=、> 、<等
带IN关键字的子查询
#查询平均年龄在25岁以上的部门名
select id,name from department
where id in
(select dep_id from employee group by dep_id having avg(age) > 25);
#查看技术部员工姓名
select name from employee
where dep_id in
(select id from department where name='技术');
#查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from employee);
# 注：in相当于=,与=的区别：=就是一个数，in就是等于这个范围里面的。
# 取反就是：not in
带ALL关键字的子查询
# all同any类似，只不过all表示的是所有，any表示任一
查询出那些薪资比所有部门的平均薪资都高的员工=》薪资在所有部门平均线以上的狗币资本家
select * from employee where salary > all (select avg(salary) from employee group by depart_id);
查询出那些薪资比所有部门的平均薪资都低的员工=》薪资在所有部门平均线以下的无产阶级劳苦大众
select * from employee where salary < all (select avg(salary) from employee group by depart_id);
# all: >all就是比最大的值要大，<all就是比最小值还小
查询出那些薪资比任意一个部门的平均薪资低的员工=》薪资在任一部门平均线以下的员工select * from employee where salary < any ( select avg(salary) from employee group by depart_id);
查询出那些薪资比任意一个部门的平均薪资高的员工=》薪资在任一部门平均线以上的员工
select * from employee where salary > any (select avg(salary) from employee group by depart_id);
# any：>any：比最小值大，<any：比最大值小
Python操作MySQL
# pymysql 模块
pip3 install pymysql
import pymysql
# 链接MySQL
conn = pymysql.connect(
user='root',
password='123',
port=3306,
host='127.0.0.1',
database='test',
charset='utf8mb4',
autocommit=True # 增、删、改需要二次确认
)
# 产生游标对象
# cursor = conn.cursor() # 括号内不填写参数 数据是元组
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# {'id': 1, 'name': 'tom', 'sex': 'male', 'age': 78, 'hire_date': datetime.date(2015, 3, 2), 'post': 'teacher', 'post_comment': None, 'salary': 1000000.31, 'office': 401, 'depart_id': 1}
# 编写SQL语句
sql = 'select * from emp;'
# 发送SQL语句给服务端
affect_rows = cursor.execute(sql) # execute执行也有返回值，结果是这个SQL语句执行影响的行数
# 获取SQL语句执行之后的结果
res = cursor.fetchall()
pymysql补充
1.获取数据
fetchall() 获取所有的结果
fetchone() 获取结果集的第一个数据
fetchmany() 获取指定数量的结果集
ps:注意三者都有类似于文件光标移动的特性
cursor.scroll(1,'relative') # 基于当前位置往后移动
cursor.scroll(0,'absolute') # 基于数据的开头往后移动
2.增删改查
autocommit=True # 针对增 删 改 自动确认(直接配置)
conn.commit() # 针对 增 删 改 需要二次确认(代码确认)
视图
视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，可以将该结果集当做表来使用。
使用视图我们可以把查询过程中的临时表摘出来，用视图去实现，这样以后再想操作该临时表的数据时就无需重写复杂的sql了，直接去视图中查找即可，但视图有明显地效率问题，并且视图是存放在数据库中的，如果我们程序中使用的sql过分依赖数据库中的视图，即强耦合，那就意味着扩展sql极为不便，因此并不推荐使用
创建视图
#语法：CREATE VIEW 视图名称 AS SQL语句
create view teacher_view as select tid from teacher where tname='李平老师';
#于是查询李平老师教授的课程名的sql可以改写为
mysql> select cname from course where teacher_id = (select tid from teacher_view);
+--------+
| cname |
+--------+
| 物理 |
| 美术 |
+--------+
2 rows in set (0.00 sec)
#！！！注意注意注意：
#1. 使用视图以后就无需每次都重写子查询的sql，但是这么效率并不高，还不如我们写子查询的效率高
#2. 而且有一个致命的问题：视图是存放到数据库里的，如果我们程序中的sql过分依赖于数据库中存放的视图，那么意味着，一旦sql需要修改且涉及到视图的部分，则必须去数据库中进行修改，而通常在公司中数据库有专门的DBA负责，你要想完成修改，必须付出大量的沟通成本DBA可能才会帮你完成修改，极其地不方便
使用视图
#修改视图，原始表也跟着改
mysql> select * from course;
+-----+--------+------------+
| cid | cname | teacher_id |
+-----+--------+------------+
| 1 | 生物 | 1 |
| 2 | 物理 | 2 |
| 3 | 体育 | 3 |
| 4 | 美术 | 2 |
+-----+--------+------------+
4 rows in set (0.00 sec)
mysql> create view course_view as select * from course; #创建表course的视图
Query OK, 0 rows affected (0.52 sec)
mysql> select * from course_view;
+-----+--------+------------+
| cid | cname | teacher_id |
+-----+--------+------------+
| 1 | 生物 | 1 |
| 2 | 物理 | 2 |
| 3 | 体育 | 3 |
| 4 | 美术 | 2 |
+-----+--------+------------+
4 rows in set (0.00 sec)
mysql> update course_view set cname='xxx'; #更新视图中的数据
Query OK, 4 rows affected (0.04 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> insert into course_view values(5,'yyy',2); #往视图中插入数据
Query OK, 1 row affected (0.03 sec)
mysql> select * from course; #发现原始表的记录也跟着修改了
+-----+-------+------------+
| cid | cname | teacher_id |
+-----+-------+------------+
| 1 | xxx | 1 |
| 2 | xxx | 2 |
| 3 | xxx | 3 |
| 4 | xxx | 2 |
| 5 | yyy | 2 |
+-----+-------+------------+
5 rows in set (0.00 sec)
修改视图
语法：ALTER VIEW 视图名称 AS SQL语句
mysql> alter view teacher_view as select * from course where cid>3;
Query OK, 0 rows affected (0.04 sec)
mysql> select * from teacher_view;
+-----+-------+------------+
| cid | cname | teacher_id |
+-----+-------+------------+
| 4 | xxx | 2 |
| 5 | yyy | 2 |
+-----+-------+------------+
2 rows in set (0.00 sec)
删除视图
语法：DROP VIEW 视图名称
DROP VIEW teacher_view
触发器
使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询
# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
创建触发器
#准备表
CREATE TABLE cmd (
id INT PRIMARY KEY auto_increment,
USER CHAR (32),
priv CHAR (10),
cmd CHAR (64),
sub_time datetime, #提交时间
success enum ('yes', 'no') #0代表执行失败
);
CREATE TABLE errlog (
id INT PRIMARY KEY auto_increment,
err_cmd CHAR (64),
err_time datetime
);
#创建触发器
delimiter // # sql语句都是以;结尾,创建触发器带来不便，因此会更改符号
CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW
BEGIN
IF NEW.success = 'no' THEN #等值判断只有一个等号
INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #必须加分号
END IF ; #必须加分号
END//
delimiter ; # 创建完之后一定要改回来
#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
USER,
priv,
cmd,
sub_time,
success
)
VALUES
('egon','0755','ls -l /etc',NOW(),'yes'),
('egon','0755','cat /etc/passwd',NOW(),'no'),
('egon','0755','useradd xxx',NOW(),'no'),
('egon','0755','ps aux',NOW(),'yes');
#查询错误日志，发现有两条
mysql> select * from errlog;
+----+-----------------+---------------------+
| id | err_cmd | err_time |
+----+-----------------+---------------------+
| 1 | cat /etc/passwd | 2017-09-14 22:18:48 |
| 2 | useradd xxx | 2017-09-14 22:18:48 |
+----+-----------------+---------------------+
2 rows in set (0.00 sec)
插入后触发触发器
# 删除触发器
drop trigger tri_after_insert_cmd;
事务
1.1什么是事务
# 事务就相当于存放sql的容器，事务中的sql要么全部执行成功，要么全不执行。
1.2为何要用事务
# 为了保证数据的安全、一致性。
# 经典的银行转账案列
A账户给B账户10元，数据库操作需要两步，第一步A账户减10元，第二步B账户加10元，如果没有事务且两步中间出错，就会导致A账户减10元，但是B账户没有变化，如果不能保证两步操作统一，转账业务也无法展开。
事务的特性
2.1四大特性
# 1.原子性
事务作为一个整体，要么全部执行成功，要么谁都别执行成功
# 2.一致性
事务执行前的状态和执行后的状态保持一致。列如：A有80，B有20，A给B转20，A应该减20，B应该加20而不是加30，执行之前两人一共有100执行之后一共还是100。
# 3.隔离性
多个事务同时执行时，一个事务的执行不应影响其他事务执行
# 4.持久性
一个事务一旦提交，修改的数据应该永久保存在数据库中
2.2案列
# A账户向B账户转账：
1、从A账号中把余额读出来（500）。
2、对A账号做减法操作（500-100）。
3、把结果写回A账号中（400）。
4、从B账号中把余额读出来（500）。
5、对B账号做加法操作（500+100）。
6、把结果写回B账号中（600）。
# 1.原子性
保证1-6所有过程要么都执行，要么都不执行。一旦在执行某一步骤的过程中发生问题，就需要执行回滚操作。 假如执行到第五步的时候，B账户突然不可用（比如被注销），那么之前的所有操作都应该回滚到执行事务之前的状态。
# 2.一致性
在转账之前，A和B的账户中共有500+500=1000元钱。在转账之后，A和B的账户中共有400+600=1000元。也就是说，数据的状态在执行该事务操作之后从一个状态改变到了另外一个状态。同时一致性还能保证账户余额不会变成负数等。
# 3.隔离性
在A向B转账的整个过程中，只要事务还没有提交（commit），查询A账户和B账户的时候，两个账户里面的钱的数量都不会有变化。
如果在A给B转账的同时，有另外一个事务执行了C给B转账的操作并提交了，虽然A给B转账的事务里看不到最新修改的数据，但是当两个事务都提交完的时候，B账户里面的钱应该是A转给B的钱加上C转给B的钱再加上自己原有的钱。
# 4.持久性
一旦转账成功（事务提交），两个账户的里面的钱就会真的发生变化（会把数据写入数据库做持久化保存）！
# 强调：原子性与隔离性
一致性与原子性是密切相关的,原子性的破坏可能导致数据库的不一致，数据的一致性问题并不都和原子性有关。
比如刚刚的例子，在第五步的时候，对B账户做加法时只加了50元。那么该过程可以符合原子性，但是数据的一致性就出现了问题。
MySQL事务的3种运行模式
3.1自动提交事务（隐式开启、隐式提交）
# 这是MySQL默认的事务模式
MySQL默认每条sql开启事务，并且会在本条sql执行完毕后自动commit提交
3.2隐式事务（隐式开启、显示提交）
#既然mysql默认是为每条sql都开启了事务并且在该sql运行完毕后会自动提交,那么我只需要将自动提交关闭即可变成“隐式开启、显式提交”
#1.临时关闭
set autocommit =0;
show variables like 'autocommit'; -- 查看
#2.永久关闭
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
autocommit=0
3.3显示事务（显示开启、显示提交）
#手动开启的事务里默认不会自动提交,所以我们可以将要执行的sql语句放在我们自己手动开启的事务里，如此便是显式开启、显式提交
start transaction;
update test.t1 set id=33 where name = "jack";
commit;
# 注：这种方式在当你使用commit或者rollback后，事务就结束了，再次进入事务状态需要再次start transaction
事务保存点
savepoint和虚拟机中的快照类似，用于事务中，每设置一个savepoint就是一个保存点，当事务结束时会自动删除定义的所有保存点，在事务没有结束前可以回退到任意保存点
1、设置保存点savepoint 保存点名字
2、回滚到某个保存点，该保存点之后的操作无效，rollback 某个保存点名
3、取消全部事务，删除所有保存点rollback
# 注意：rollback和commit都会结束掉事务，这之后无法再回退到某个保存点
总结
START TRANSACTION（或 BEGIN）：显式开始一个新事务 #开启事务
SAVEPOINT：分配事务过程中的一个位置，以供将来引用 #临时存档
COMMIT：永久记录当前事务所做的更改 #提交
ROLLBACK：取消当前事务所做的更改 #回滚
ROLLBACK TO SAVEPOINT：取消在 savepoint 之后执行的更改 #回到存档点
RELEASE SAVEPOINT：删除 savepoint 标识符 #删除临时存档
SET AUTOCOMMIT：为当前连接禁用或启用默认 autocommit 模式
索引
1.1什么是索引
1.索引就是存储引擎的一种数据结构，或者说是数据的组织方式，又称之为key，是存储引擎用于快速找到记录的一种数据结构。
2.为数据建立索引就好比为书建立目录，或者说是为字典创建音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。
1.2为何要用索引
一般的应用系统读写比例在10：1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的、也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。
索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
1.3如何正确看待索引
# 先说结论：索引不是越多越好，并且egon建议大家最好在提前创建好索引，而不是等火烧眉毛
# 详解如下索引是应用程序设计和开发的一个重要方面。若索引太多，应用程序的性能可能会受到影响。而索引太少，对查询性能又会产生影响，要找到一个平衡点，这对应用程序的性能至关重要。一些开发人员总是在事后才想起添加索引----我一直认为，这源于一种错误的开发模式。如果知道数据的使用，从一开始就应该在需要处添加索引。开发人员往往对数据库的使用停留在应用的层面，比如编写SQL语句、存储过程之类，他们甚至可能不知道索引的存在，或认为事后让相关DBA加上即可。DBA往往不够了解业务的数据流，而添加索引需要通过监控大量的SQL语句进而从中找到问题，这个步骤所需的时间肯定是远大于初始添加索引所需的时间，并且可能会遗漏一部分的索引。当然索引也并不是越多越好，我曾经遇到过这样一个问题：某台MySQL服务器iostat显示磁盘使用率一直处于100%，经过分析后发现是由于开发人员添加了太多的索引，在删除一些不必要的索引之后，磁盘使用率马上下降为20%。可见索引的添加也是非常有技术含量的。
理解索引的储备知识
1）机械磁盘 一次IO的时间
机械磁盘一次io的时间 = 寻道时间 + 旋转延迟 + 传输时间
# 寻道时间道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下
# 旋转延迟旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；
# 传输时间传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计所以访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右1234567891011122）这9ms对于人来说可能非常短，但对于计算机来可是非常长的一段时间，长到什么程度呢？一台500 -MIPS（Million Instructions Per Second）的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行约450万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。
2）磁盘的预读
# 考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化：当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助.
3）索引原理精髓提炼
索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等
!!!!!!!!!!!!!!!!!!!!!!!!!!!!
本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
!!!!!!!!!!!!!!!!!!!!!!!!!!!!
数据库也是一样，但显然要复杂的多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段......这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的。而数据库实现比较复杂，一方面数据是保存在磁盘上的，另外一方面为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。
索引分类
索引模型分为很多种类
#B+树索引（等值查询与范围查询都快）
二叉树->平衡二叉树->B树->B+树
#HASH索引（等值查询快，范围查询慢）
将数据打散再去查询
#FULLTEXT：全文索引（只可以用在MyISAM引擎）
通过关键字的匹配来进行查询，类似于like的模糊匹配
like + %在文本比较少时是合适的
但是对于大量的文本数据检索会非常的慢
全文索引在大量的数据面前能比like快得多，但是准确度很低
百度在搜索文章的时候使用的就是全文索引，但更有可能是ES
不同的存储引擎支持的索引类型也不一样
- InnoDB存储引擎
支持事务，支持行级别锁定，支持 B-tree（默认）、Full-text 等索引，不支持 Hash 索引；
- MyISAM存储引擎
不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
- Memory存储引擎
不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
索引的数据结构
4.1创建索引的两大步骤
为某个字段创建索引，即以某个字段值为基础构建索引结构，那么如何构建呢？分为两大步：
-
1、提取每行记录中主键字段的值，以该值当作key，至于key对的value是什么？每种索引结构各不相同
-
2、然后以key值为基础构建索引结构
innodb存储引擎默认的索引结构为B+树，而B+树是由二叉树、平衡二叉树、B树再到B+树一路演变过来的
4.2二叉查找树
以key值的大小为基础构建二叉树
二叉查找树的特点就是任何节点的左节点的key值都小于当前节点的key值，右边节点的key值都大于当前节点的key值。
顶端的节点我们称之为根节点，没有子节点的节点我们称之为叶节点。
利用二叉树索引查找流程：
1、将根节点作为当前节点，把12与当前节点的键值10比较，12大于10，接下来我们把当前节点>的右子节点作为当前节点。2、继续把12和当前节点的键值13比较，发现12小于13，把当前节点的左子节点作为当前节点。
3、把12和当前节点的键值12对比，12等于12，满足条件，我们从当前节点中取出data，即id=1>2,name=xm。
总结：
利用二叉树我们只需要3次就可以查到匹配的数据，如果在表中一条条的查找需要6次。
# 查找次数就是树的高度
4.3平衡二叉树
这个时候二叉树变成了一个链表，这样查找数据相当于全表扫描，为了解决这个问题，我们需要保证二叉树一直保持平衡，就需要用到平衡二叉树。
平衡二叉树又称AVL树，在满足二叉树特性的基础上，要求每个节点的左右子树的高度不能超过1。
平衡二叉树和非平衡二叉树的对比：
由平衡二叉树的构造我们可以发现第一张图中的二叉树其实就是一棵平衡二叉树。平衡二叉树保证了树的构造是平衡的，当我们插入或删除数据导致不满足平衡二叉树不平衡时，平衡二叉树会进行调整树上的节点来保持平衡。具体的调整方式这里就不介绍了。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。
4.4 B树
直接用平衡二叉树这种数据结构构建索引会有什么问题？
如果我们单纯用平衡二叉树这种数据结构作为索引的数据结构，即每个磁盘块只放一个节点，每个节点中只存放一组键值对，此时如果数据量过大，二叉树的节点则会非常多，树的高度也随即变高，我们查找数据的也会进行很多次磁盘IO，查找数据的效率也会变得极低！
如果我们能够在平衡二叉的树的基础上，把更多的节点放入一个磁盘块中，那么平衡二叉树的弊端也就解决了。即构建一个单节点可以存储多个键值对的平衡树，这就是B树。
从上图可以看出，B树相对于平衡二叉树，每个节点存储了更多的key值和数据，并且每个节点拥有更多的子节点，子节点的个数一般称之为阶，上图的B树为3阶的B树，高度很低。基于这个特性，B树查找数据读取磁盘的次数将会很少，查找效率会比平衡二叉树高很多。
案列：
# 假如我们要查找id=28的用户信息
1.先找到跟节点也就是页1，判断28在key值17和35之间，我们根据页1的指针p2找到页3。
2.将28和页3中的key值相比较，28在26和30之间，根据页3中的指针p2找到页8。
3.将28和页8中的key值比较，发现有匹配的key值28
缺点：
B树只擅长做等值查询，而对于范围查询（范围查询的本质就是n次等值查询），或者说排序操作，B树也帮不了我们
select * from user where id=3; -- 擅长
select * from user where id>3; -- 不擅长
4.5 B+树
B+树就是B树的进一步优化
B+树的四大特点
1.非叶子节点只放key值，只有叶子节点才存放key以及对应的value----> 非叶子节点能存放key的个数变多，衍生出的指针越多，树会变得更矮更胖，io效率进一步提升
2.叶子节点彼此之间有双向链表--->范围查询速度快
3.叶子节点内的key值是单向链表，叶子节点与叶子节点之间是双向链表，即全都排好序了----> 排序也贼快
4.一个页、一个磁盘块、一个节点固定大小16k，可以存放的数据量很多
按照每个节点存放1000数据来算，3层的b+树可以组织多少条数据：1000 * 1000 * 1000
不同：
1.B+树非叶子节点不存数据，仅存key，而B树的非叶子节点不光存key也会存数据。意义在于：树的一个节点就是一个页，innodb存储引擎默认一页为16K，所以在页大小固定的情况下，能往一个页中放更多的几点，相对应的子节点就会更大，那么树必然更矮更胖，如此一来查找数据进行的磁盘IO数会再次减少，查找效率也会更快。
2.上图B+树存储数量：3*3*3 key值数量树高度的次方
3.因为B+树索引的所有数据均存储在叶子节点leaf node，而且数据是按照顺序排列的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而B树因为数据分散在各个节点，要实现这一点是很不容易的。
4.B+树中各个页之间也是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。其实上面的B树我们也可以对各个节点加上链表。其实这些不是它们之前的区别，是因为在mysql的innodb存储擎中，索引就是这样存储的。也就是说上图中的B+树索引就是innodb中B+树索引真正的实现方式，准确的说应该是聚集索引（聚集索引和非聚集索引下面会讲到）。通过上图可以看到，在innodb中，我们通过数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。
聚集索引与非聚集索引
1、聚集索引（又称聚簇索引、主键索引，一张表必须有且只有一个）：聚集索引叶子节点key所对应的value值是整行完整内容
2、非聚集索引（又称非聚簇索引、辅助索引，一张表可以创建多个辅助索引）：辅助索引叶子节点key所对应的value值是该所对应的主键字段值
覆盖索引与回表操作
覆盖索引
# 在命中索引的基础上，只在本索引树的叶子节点就找到了我们想要的数据
回表操作
# 在命中辅助索引的基础上，在辅助索引的叶子节点并没有找到想要的数据，需要拿着对应的主键字段值去聚集索引里再找一下
索引管理
create table t1(
id int,
class_name varchar(10),
name varchar(16),
age int
);
# 注：索引最好是创建表的时候指定
# 创建主键索引
alter table student add primary key t1(id);
# 删除主键索引
alter table student drop primary key;
# 创建唯一索引
alter table country add unique key t1(class_name);
# 删除唯一索引
alter table t1 drop index t1;
# 创建普通索引
create table t1(
id int primary key auto_increment,
class_name varchar(10) unique,
name varchar(16),
age int
);
create index xxx on t1(name);
# 删除普通索引
drop index xxx on t1;
如何正确使用索引
# 以什么字段的值为基础构建索引
最好是不为空、唯一、占用空间小的字段
# 针对全表查询语句如何优化？
应用场景：用户想要浏览所有商品信息
优化方案：分页查找，用户每次看，现从数据拿
8.1 针对等值查询
以重复度低的字段为基础创建索引加速效果明显
create index xxx on s1(id);
select count(id) from s1 where id = 33;
以重复度高字段为基础创建索引加速效果不明显
create index yyy on s1(name);
select count(id) from s1 where name="egon";
以占用空间大字段为基础创建索引加速效果不明显
# 总结：给重复度低、占用空间小的字段值为基础构建索引
8.2 范围查询
> 号 、<号
# 如果查找范围很大，相当于全表扫描，查询速度很慢
# 如果查找范围很小，查询速度还是很快
!= 不等于
# 不等于的范围也还是很大，查询速度慢
between ...and...
# 范围大查询慢，范围小查询快
like
# like指定的是明确的值，速度还是很快。
# 如果%在末尾，速度也快（有明确的开头，缩小了范围）
# 如果%在开头，速度慢（相当于以任意字符开头，范围很大）
8.3运算
select count(id) from s1 where id*12 = 10000; (速度慢)
# 把所有的id查找出来再进行运算，速度很慢
select count(id) from s1 where id = 10000/12; (速度快)
# id的值是一个明确的数字，速度很快
8.4索引下推技术
and/or
#1、and与or的逻辑
条件1 and 条件2:所有条件都成立才算成立，但凡要有一个条件不成立则最终结果不成立
条件1 or 条件2:只要有一个条件成立则最终结果就成立
#2、and的工作原理
条件：
a = 10 and b = 'xxx' and c > 3 and d =4
索引：
制作联合索引(d,a,b,c)
工作原理:
对于连续多个and：mysql会按照联合索引，从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围)，加速查询，即按照d—>a->b->c的顺序
#3、or的工作原理
条件：
a = 10 or b = 'xxx' or c > 3 or d =4
索引：
制作联合索引(d,a,b,c)
工作原理:
对于连续多个or：mysql会按照条件的顺序，从左到右依次判断，即a->b->c->d
8.5最左前缀匹配
# 创建联合索引时，将范围比较小的字段放在前面，查询速度会很快。
浙公网安备 33010602011771号