MySql基础教程 笔记
为了从0了解数据库，拼了。
第一章 MySql的概要
第一节 数据库概要
1.1.1 数据库是什么
数据库表示『具有某种规则的数据集合』。现在一般提到的数据库必须拥有对数据的管理功能才能成为数据库，更像一种操作系统。
1.1.2 关系数据库是什么
现在使用最为广泛的数据库是关系数据库。其中一条数据称为记录(record)，各个项目称为列(column)。
我们把收集这些数据的表格称为表(table)。一个数据库可以有多个表。
管理关系数据库的系统称为RDBMS（Relational Database Management System, 关系数据库管理系统）。
MySql是RDSMS的一种。
1.13数据库的特征
原子性，隔离性，持久性，一致性。
1.2MySql是什么样的数据库
1.2.1Mysql是开源软件
主流的数据库
Oracle 世界上最常用的商用RDBMS
Access 微软公司Office系列的RDNMS
Microsoft SQL Server 微软公司商用RDBMS
PostgreSQL 开源的RDBMS，在日本受欢迎
MySql 世界上最常用的RDBMS
1.2.2 MySql的历史
MySql是米卡埃尔维德纽斯(MIchael Widenius)在1995年开发的RDBMS。经过几次转手，现在归属Oracle公司。
1.2.3MySql的两个版本
分为商业版和社区版，商业版提供更好的技术支持服务（废话）
1.2.4MySql的特征
执行速度快
开放源代码
支持在多种操作系统运行
支持多种编程语言
拥有免费和付费两个版本
1.3.1SQL的概要
在操作数据库的时候，作为用户的我们会向数据库发出命令（command），并指定需要处理的内容。
表示这种命令的语句就是查寻（query）。例如，创建表时使用的查寻是"CREATE TABLE ..."，
编写查寻需要遵守SQL（Structrued Query Language）的规则。SQL直译过来就是结构化查寻语言。
SQL是数据库的窗口，充当用户和数据库交互的媒介。
1.3.2 首相熟悉SELECT命令
SQL中SELECT是用于选择的命令，它也是使用SQL中使用最为频繁的关键字。无论使用SELECT命令，都不必担心会损坏或更改数据，
所以刚开始入门的时候，这个命令随便用。
第2章 MySql的环境配置
主要讲了windows下NAMP软件的安装，调试，以及简单的MySql配置。
第3章 MySql监视器
通过终端 mysql -u root -proot进入默认的mysq系统。
MySql命令结束用\g或;
windows下可以通过chcp 65001用于修改命令提示符的字符编码。
可以通过 status或者SHOW VARIABLES LIKE 'char%'
来显示器服务端或客户端的编码集。
mysql Ver 14.14 Distrib 5.7.28, for Linux (x86_64) using EditLine wrapper Connection id: 25 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.28 MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 15 hours 27 min 11 sec
+--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.01 sec)
退出mysql用exit 或者quit
winodws下cmd窗口可以通过F7查看历史命令
通过--选择名=登录MySql
mysql --user=root --password=root
还可以指定命令输入字符集启动MySql监视器
mysql -u root -proot --default-character-set=gbk
修改MySql密码
SET PASSWORD FOR root@localhost=PASSWORD('1234')
作业:
创建用户:
CREATE USER test IDENTIFIED BY '1234';
授权操作
GRANT ALL ON *.* TO test;
删除用户
DROP USER test
第4章 创建数据库
4.1创建数据库
CREATE DATABASE db1
注意返回的信息
Query OK, 1 row affected (0.00 sec)
Query OK表示成功，修改了一行，花费了0.00秒.
4.2确认创建的数据库
SHOW DATABASES
+--------------------+ | Database | +--------------------+ | information_schema | | db1 | | homework | | houduan | | mysql | | new3 | | performance_schema | | sys | | test | | work | +--------------------+ 10 rows in set (0.00 sec)
最后一行信息表示显示了几条在框内。
4.3 指定使用的数据库
use db1
use 不是SQL语句，所以在之后可以不加;，也可以用过\u db1来切换数据库。
显示正在使用的数据库
SELECT DATABASE();
也可以在登录的时候指定数据库
mysql db1 -uroot -p
指定db1数据库登录.
4.4创建表tb1
数据库中构成表的项目称为字段（field），构成记录的各项目数据称为列，列中保存的数据的种类称为数据类型。
创建表
CREATE TABLE 表名 (列名1 字段1, 列名2 字段2,......)
CREATE TABLE tb1 (empid VARCHAR(10),name VARCHAR(10),age INT);
4.5显示所有表
SHOW TABLES;
mysql> SHOW TABLES; +---------------+ | Tables_in_db1 | +---------------+ | tb1 | +---------------+ 1 row in set (0.00 sec)
在建表的时候时候如果遇到编码问题，可以在建表的时候最后添加 CHARSET=utf8
CREATE TABLE tb1 (empid VARCHAR(10),name VARCHAR(10),age INT) CHARSET=utf8;
访问其他数据库
SELECT * FROM db2.table
这样可以在使用db1的情况下查寻db2下表的数据。
4.6确定表的列结构
mysql> SHOW COLUMNS FROM tb1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | empid | varchar(10) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> DESCRIBE tb1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | empid | varchar(10) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> DESC tb1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | empid | varchar(10) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql>
可以通过
DESC 表名;
DESCRIBE 表名;
SHOW COLUMNS FRON 表名;
显示都是一样的。
SHOW是MySql特有的语句，其他RDBMS没有这个命令。
4.7向表中插入数据
INSERT INTO 表名 VALUES(data1,data2,data3......)
mysql> INSERT INTO tb1 VALUE('A101','佐藤',40);
Query OK, 1 row affected (0.00 sec)
插入数据字符串需要""或''包裹，数字不需要。
继续插入
mysql> INSERT INTO tb1 VALUES('A102','高教',28);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO tb1 VALUES('A103','中川',20);
Query OK, 1 row affected (0.00 sec)
指定列名插入记录
INSERT INTO 表名 (列名1,列名2,...) VALUES(数据1,数据2,)
mysql> INSERT INTO tb1 (age,name,empid) VALUES(23,'渡边','A104'); Query OK, 1 row affected (0.01 sec)
一次性插入多条数据
INSERT INTO 表名 (列名1,列名2,...) VALUES(数据1,数据2,),(数据1,数据2,),(数据1,数据2,)...
mysql> INSERT INTO tb1 (empid,name,age) VALUES('A104','渡边',23),('A105','西哲',35);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
4.8显示数据
SELECT 列名1,列名2 FROM 表名;
mysql> SELECT empid,name FROM tb1; +-------+--------+ | empid | name | +-------+--------+ | A101 | 佐藤 | | A102 | 高教 | | A103 | 中川 | | A104 | 渡边 | | A104 | 渡边 | | A105 | 西哲 | +-------+--------+ 6 rows in set (0.00 sec) mysql> SELECT * FROM tb1; +-------+--------+------+ | empid | name | age | +-------+--------+------+ | A101 | 佐藤 | 40 | | A102 | 高教 | 28 | | A103 | 中川 | 20 | | A104 | 渡边 | 23 | | A104 | 渡边 | 23 | | A105 | 西哲 | 35 | +-------+--------+------+ 6 rows in set (0.00 sec) mysql>
SELECT还可以做另外的一些功能
mysql> SELECT '星星'; +--------+ | 星星 | +--------+ | 星星 | +--------+ 1 row in set (0.00 sec) mysql> SELECT (2+3)*5; +---------+ | (2+3)*5 | +---------+ | 25 | +---------+ 1 row in set (0.00 sec) mysql>
4.9准备复制表tb1
CREATE TABLE 表名 SELECT * FROM 被复制的表名
mysql> CREATE TABLE tb1A SELECT * FROM tb1; Query OK, 6 rows affected (0.03 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> CREATE TABLE tb1B SELECT * FROM tb1A; Query OK, 6 rows affected (0.04 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> SHOW TABLES; +---------------+ | Tables_in_db1 | +---------------+ | tb1 | | tb1A | | tb1B | +---------------+ 3 rows in set (0.00 sec)
作业
使用一行命令显示两次tb1的所有数据
mysql> SELECT * FROM tb1;SELECT * FROM tb1; +-------+--------+------+ | empid | name | age | +-------+--------+------+ | A101 | 佐藤 | 40 | | A102 | 高教 | 28 | | A103 | 中川 | 20 | | A104 | 渡边 | 23 | | A104 | 渡边 | 23 | | A105 | 西哲 | 35 | +-------+--------+------+ 6 rows in set (0.00 sec) +-------+--------+------+ | empid | name | age | +-------+--------+------+ | A101 | 佐藤 | 40 | | A102 | 高教 | 28 | | A103 | 中川 | 20 | | A104 | 渡边 | 23 | | A104 | 渡边 | 23 | | A105 | 西哲 | 35 | +-------+--------+------+ 6 rows in set (0.00 sec) mysql>
显示数据库中user信息
mysql> SELECT host,user FROM mysql.user; +-----------+---------------+ | host | user | +-----------+---------------+ | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-----------+---------------+ 3 rows in set (0.00 sec) mysql>
第五章 数据类型和数据输入
MySQL的基本类型就是数字，字符串与日期。
其中在本书中用到的数字类型为整数INT与小数DOUBLE。
输入数据的时候可以在数字前面加上+-，也可以通过指数法输入6.02E23表示6.02乘以10的23次方。
mysql> INSERT INTO tb1A (age) values(1E4); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM tb1A; +-------+--------+-------+ | empid | name | age | +-------+--------+-------+ | A101 | 佐藤 | 40 | | A102 | 高教 | 28 | | A103 | 中川 | 20 | | A104 | 渡边 | 23 | | A104 | 渡边 | 23 | | A105 | 西哲 | 35 | | NULL | NULL | 10000 |
字符串类型有
CHAR,VARCHAR,TEXT,LONGTEXT
输入字符串的时候，如果在单引号的内容里面输入单引号，需要对单引号转义\'
mysql> INSERT INTO tb1B (name) VALUES('\'西泽\'');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT name FROM tb1B;
+----------+
| name |
+----------+
| 佐藤 |
| 高教 |
| 中川 |
| 渡边 |
| 渡边 |
| 西哲 |
| '西泽' |
+----------+
7 rows in set (0.00 sec)
mysql>
日期与时间类型
时间类型有DATA, DATATIME, TIME, YEAR
练习
db1:root>INSERT INTO t_date (a) VALUES('2018-5-3');
Query OK, 1 row affected (0.00 sec)
db1:root>SELECT * FROM t_date;
+------------+
| a |
+------------+
| 2018-05-03 |
+------------+
1 row in set (0.00 sec)
db1:root>
PROMPT 作为提示的文本 \d设置数据库名， \h设置主机名, \u设置用户名， \D变成时间
PROMPT不带参数，返回默认的提示。
题目1
db1:root>CREATE TABLE t_time(col_time DATETIME);
Query OK, 0 rows affected (0.04 sec)
db1:root>DESCRIBE t_time;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| col_time | datetime | YES | | NULL | |
+----------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)
db1:root>INSERT INTO t_time(col_time) VALUES('2018-11-17-20-35-15');
Query OK, 1 row affected (0.01 sec)
db1:root>SElECT * FROM t_time;
+---------------------+
| col_time |
+---------------------+
| 2018-11-17 20:35:15 |
+---------------------+
1 row in set (0.00 sec)
第6章 修改表
修改表的列结构
通过ALTER TABLE ... MODIFY ...(修改)
ALTER TABLE ... ADD ... (增加)
ALTER TABLE ... CHANGE ... (修改)
ALTER TABLE ... DROP ...(删除)
修改列的数据类型。
ALTER TABLE 表名 MODIFY 列名 字段
db1:localhost>ALTER TABLE tb1C MODIFY name VARCHAR(100); Query OK, 6 rows affected (0.12 sec) Records: 6 Duplicates: 0 Warnings: 0 db1:localhost>DESCRIBE tb1C; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | empid | varchar(10) | YES | | NULL | | | name | varchar(100) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) db1:localhost>
修改表中列数据类型，原则上列中存在数据，不应该再修改数据类型了。
添加列
ALTER TABLE 表名 ADD 列名 数据类型;
db1:localhost>ALTER TABLE tb1C ADD birth DATETIME; Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 db1:localhost>SHOW COLUMNS FROM tb1C; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | empid | varchar(10) | YES | | NULL | | | name | varchar(100) | YES | | NULL | | | age | int(11) | YES | | NULL | | | birth | datetime | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) db1:localhost>
修改列的位置
把列添加到最前面
最后面添加参数FIRST
db1:localhost>ALTER TABLE tb1D ADD birth DATETIME FIRST; Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 db1:localhost>DESCRIBE tb1D; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | birth | datetime | YES | | NULL | | | empid | varchar(10) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) db1:localhost>
修改列的顺序
再需要修改的列名，字段类型后面添加FIRST,也可以使用AFTER 列名，在执行列的后面。
db1:localhost>DESCRIBE tb1C; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | empid | varchar(10) | YES | | NULL | | | name | varchar(100) | YES | | NULL | | | age | int(11) | YES | | NULL | | | birth | datetime | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) db1:localhost>ALTER TABLE tb1C MODIFY birth DATETIME FIRST; Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0 db1:localhost>DESCRIBE tb1C; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | birth | datetime | YES | | NULL | | | empid | varchar(10) | YES | | NULL | | | name | varchar(100) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) db1:localhost>
修改列名和数据类型
ALTER TABLE 表名 CHANGE 改前列名 改后列名 改后数据类型
db1:localhost>ALTER TABLE tb1C CHANGE biRTh birthdat date; Query OK, 8 rows affected (0.10 sec) Records: 8 Duplicates: 0 Warnings: 0
MODIFY 后面一个 列名 需要修改的数据类型
CHANGE 改前列名 改后列名 改后数据类型
删除列
ALTER TABLE 表名 DROP 列名;
db1:localhost>DESC tb1C; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | birthday | date | YES | | NULL | | | empid | varchar(10) | YES | | NULL | | | name | varchar(100) | YES | | NULL | | | age | int(11) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) db1:localhost>ALTER TABLE tb1C DROP birthday; Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 db1:localhost>DESC tb1C; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | empid | varchar(10) | YES | | NULL | | | name | varchar(100) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) db1:localhost>
db1:localhost>INSERT INTO tb1F (name) VALUES ('12345677890');
ERROR 1406 (22001): Data too long for column 'name' at row 1
db1:localhost>INSERT INTO tb1F (name) VALUES ('1234567');
Query OK, 1 row affected (0.00 sec)
db1:localhost>DESC tb1F; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | empid | varchar(10) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) db1:localhost>
书中介绍，当超过VARCHAR（）长度限制，会忽悠后面超出部分字符，但我实际使用中，我的版本出现了报错提示。
设置主键
主键:
没有重复的值
不允许输入空值
创建主键
db1:localhost>CREATE TABLE t_pk (a INT PRIMARY KEY,b VARCHAR(10)); Query OK, 0 rows affected (0.04 sec) db1:localhost>
PRIMARY KEY中间记得空格
db1:localhost>INSERT INTO t_pk values ('1','阿');
Query OK, 1 row affected (0.01 sec)
db1:localhost>SELECT * FROM t_pk;
+---+------+
| a | b |
+---+------+
| 1 | 阿 |
+---+------+
1 row in set (0.00 sec)
db1:localhost>INSERT INTO t_pk values ('1','b');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
db1:localhost>INSERT INTO t_pk values (NULL,'b');
ERROR 1048 (23000): Column 'a' cannot be null
db1:localhost>INSERT INTO t_pk values ('2');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
db1:localhost>INSERT INTO t_pk (a) values ('2');
Query OK, 1 row affected (0.01 sec)
db1:localhost>SELECT * FROM t_pk;
+---+------+
| a | b |
+---+------+
| 1 | 阿 |
| 2 | NULL |
+---+------+
2 rows in set (0.00 sec)
db1:localhost>
上面测试了主键唯一与不能为空
设置唯一键
唯一键不允许列中有重复值，但允许有空值。
db1:localhost>CREATE TABLE t_uniq(a int UNIQUE, b VARCHAR(10)); Query OK, 0 rows affected (0.04 sec) db1:localhost>INSERT INTO t_uniq VALUES(NULL,'ok'); Query OK, 1 row affected (0.01 sec) db1:localhost>INSERT INTO t_uniq VALUES(NULL,'ok'); Query OK, 1 row affected (0.01 sec) db1:localhost>INSERT INTO t_uniq VALUES(12,'ok'); Query OK, 1 row affected (0.01 sec) db1:localhost>INSERT INTO t_uniq VALUES(12,'ok'); ERROR 1062 (23000): Duplicate entry '12' for key 'a' db1:localhost>SELECT * FROM t_uniq; +------+------+ | a | b | +------+------+ | NULL | ok | | NULL | ok | | 12 | ok | +------+------+ 3 rows in set (0.00 sec)
空值重复不算重复。
PRIMATY KEY, UNIQUE 都写在字段类型后面。
使列具有自动连续编号功能
具有自动连续编号功能的列必须为INT TINYINT SMALLINT等整数类型。
加上关键字AUTO_INCREMENT
可以与主键配合PRIMARY KEY
db1:localhost>CREATE TABLE t_series (a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10)); Query OK, 0 rows affected (0.03 sec) db1:localhost>DESC t_series; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | a | int(11) | NO | PRI | NULL | auto_increment | | b | varchar(10) | YES | | NULL | | +-------+-------------+--
插入数据
db1:localhost>INSERT INTO t_series (b) VALUES ('wo');
Query OK, 1 row affected (0.01 sec)
db1:localhost>INSERT INTO t_series (b) VALUES ('ni');
Query OK, 1 row affected (0.01 sec)
db1:localhost>INSERT INTO t_series (b) VALUES ('ta');
Query OK, 1 row affected (0.01 sec)
db1:localhost>INSERT INTO t_series VALUES (0,'ta1');
Query OK, 1 row affected (0.01 sec)
db1:localhost>INSERT INTO t_series VALUES (NULL,'ta2');
Query OK, 1 row affected (0.01 sec)
db1:localhost>SELECT * FROM t_series;
+---+------+
| a | b |
+---+------+
| 1 | wo |
| 2 | ni |
| 3 | ta |
| 4 | ta1 |
| 5 | ta2 |
+---+------+
5 rows in set (0.00 sec)
db1:localhost>
对于自动增长的键，可以传入NULL或者0，改键的数据会自动增长。
自动增长的数字从1开始。
db1:localhost>INSERT INTO t_series values (100,'100'); Query OK, 1 row affected (0.01 sec) db1:localhost>INSERT INTO t_series values (0,'KANKAN'); Query OK, 1 row affected (0.01 sec) db1:localhost>SELECT * FROM t_series; +-----+--------+ | a | b | +-----+--------+ | 1 | wo | | 2 | ni | | 3 | ta | | 4 | ta1 | | 5 | ta2 | | 100 | 100 | | 101 | KANKAN | +-----+--------+ 7 rows in set (0.00 sec) db1:localhost>
当手工输入一个数字以后，将从这个大的数字开始自动计算。
db1:localhost>DELETE FROM t_series; Query OK, 7 rows affected (0.00 sec) db1:localhost>INSERT INTO t_series VALUES(0, '忠'); Query OK, 1 row affected (0.01 sec) db1:localhost>SELECT * FROM t_series; +-----+------+ | a | b | +-----+------+ | 102 | 忠 | +-----+------+ 1 row in set (0.00 sec)
当删除表中的所有数据，再次插入数据，还是从没删除的数字接着开始。
db1:localhost>ALTER TABLE t_series AUTO_INCREMENT=1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
db1:localhost>INSERT INTO t_series VALUES(0,'1')
-> ;
Query OK, 1 row affected (0.01 sec)
db1:localhost>SELECT * FROM t_series;
+-----+------+
| a | b |
+-----+------+
| 102 | 忠 |
| 103 | 1 |
+-----+------+
2 rows in set (0.00 sec)
db1:localhost>DELETE FROM t_series;
Query OK, 2 rows affected (0.00 sec)
db1:localhost>ALTER TABLE t_series AUTO_INCREMENT=1;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
db1:localhost>INSERT INTO t_series VALUES(0,'new');
Query OK, 1 row affected (0.00 sec)
db1:localhost>SELECT * FROM t_series;
+---+------+
| a | b |
+---+------+
| 1 | new |
+---+------+
1 row in set (0.00 sec)
设置列的默认值
CREATE TABLE 表名 (列名 数据类型 DEFAULT 默认值);
db1:root>ALTER TABLE tb1G MODIFY NAME VARCHAR(10) DEFAULT '未输入姓名'; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 db1:root>DESC tb1G; +-------+-------------+------+-----+-----------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+-----------------+-------+ | empid | varchar(10) | YES | | NULL | | | NAME | varchar(10) | YES | | 未输入姓名 | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+-----------------+-------+ 3 rows in set (0.00 sec) db1:root>
通过修改字段的形式添加默认值。
插入数据
db1:root>INSERT INTO tb1G (empid,age) VALUES('N999',39);
Query OK, 1 row affected (0.00 sec)
db1:root>SELECT * FROM tb1G;
+-------+-----------------+------+
| empid | NAME | age |
+-------+-----------------+------+
| A101 | 佐藤 | 40 |
| A102 | 高教 | 28 |
| A103 | 中川 | 20 |
| A104 | 渡边 | 23 |
| A104 | 渡边 | 23 |
| A105 | 西哲 | 35 |
| NULL | NULL | 2000 |
| N999 | 未输入姓名 | 39 |
+-------+-----------------+------+
8 rows in set (0.00 sec)
db1:root>
创建索引
当查找的表中的数据时，如果数据量过于庞大，查找要花费很多时间，在这种情况下，最好在表上创建索引。
CREATE INDEX 索引名 ON 表名 （列名）
db1:root>CREATE INDEX my_ind ON tb1G(empid); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0
显示索引
db1:root>SHOW INDEX FROM tb1G;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tb1G | 1 | my_ind | 1 | empid | A | 7 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
db1:root>SHOW INDEX FROM tb1G\G
*************************** 1. row ***************************
Table: tb1G
Non_unique: 1
Key_name: my_ind
Seq_in_index: 1
Column_name: empid
Collation: A
Cardinality: 7
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
db1:root>
我尝试在一个表中，两个字段创建索引显示会如何。
db1:root>SHOW INDEX FROM tb1G\G
*************************** 1. row ***************************
Table: tb1G
Non_unique: 1
Key_name: my_ind
Seq_in_index: 1
Column_name: empid
Collation: A
Cardinality: 7
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: tb1G
Non_unique: 1
Key_name: my_name
Seq_in_index: 1
Column_name: NAME
Collation: A
Cardinality: 7
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)
db1:root>
看来一个表中能够对多个字段创建索引。
显示的时候，可以通过\G来显示，效果非常好。每一列显示一块。
删除索引
DROP INDEX 索引名 ON 表名;
db1:root>DROP INDEX my_ind ON tb1G; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 db1:root>
索引并不代表一定会缩短查找时间。通常情况下，重复值比较的情况下，最好不要创建索引。
另外当对创建的索引的表进行更新时，也需要对已经存在的索引信息进行维护。所以在使用索引的情况下，检索速度可能会很快，但于此同时，更新速度就会变慢。
练习题
db1:root>CREATE TABLE t_initial_serial(id INT AUTO_INCREMENT PRIMARY KEY,ctime DATETIME) AUTO_INCREMENT=100; Query OK, 0 rows affected (0.05 sec) db1:root>INSERT INTO t_initial_serial (ctime) VALUES(NOW()); Query OK, 1 row affected (0.00 sec) db1:root>SELECT * FROM t_initial_serial; +-----+---------------------+ | id | ctime | +-----+---------------------+ | 100 | 2020-03-23 01:55:31 | +-----+---------------------+ 1 row in set (0.00 sec) db1:root>
AUTO_INCREMENT的初始值应该放在字段类型赋值的外面，就时小括号的外面
第七章 赋值、删除表和记录
复制表的列结构和记录
CREATE TABLE 新表名 SELECT * FROM 旧表名l;
Database changed mysql> CREATE TABLE tb1_bk SELECT * FROM tb1; Query OK, 7 rows affected (0.04 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM tb1_bk; +-------+--------+------+ | empid | name | age | +-------+--------+------+ | A101 | 佐藤 | 40 | | A102 | 高教 | 28 | | A103 | 中川 | 20 | | A104 | 渡边 | 23 | | A104 | 渡边 | 23 | | A105 | 西哲 | 35 | | NULL | NULL | 2000 | +-------+--------+------+ 7 rows in set (0.00 sec) mysql>
这种赋值情况可能存在不能复制表元的索引等情况，所以后续应该通过DESC查看表结构。
仅复制表的列结构
CREATE TABLE 新表名 LIKE 老表名
mysql> CREATE TABLE tb1_bkc LIKE tb1; Query OK, 0 rows affected (0.06 sec) mysql> SELECT * FROM tb1_bkc; Empty set (0.00 sec) mysql> DESCRIBE tb1_bkc; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | empid | varchar(10) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
复制其他表的记录
INSERT INTO 表名 SELECT * FROM 元表名
db1:root>SELECT * FROM tb1_bkc; Empty set (0.00 sec) db1:root>INSERT INTO tb1_bkc SELECT * FROM tb1; Query OK, 7 rows affected (0.01 sec) Records: 7 Duplicates: 0 Warnings: 0 db1:root>SELECT * FROM tb1_bkc; +-------+--------+------+ | empid | name | age | +-------+--------+------+ | A101 | 佐藤 | 40 | | A102 | 高教 | 28 | | A103 | 中川 | 20 | | A104 | 渡边 | 23 | | A104 | 渡边 | 23 | | A105 | 西哲 | 35 | | NULL | NULL | 2000 | +-------+--------+------+ 7 rows in set (0.00 sec) db1:root>
选择某一列进行复制
db1:root>INSERT INTO tb1_bkc (name) SELECT empid FROM tb1; Query OK, 7 rows affected (0.02 sec) Records: 7 Duplicates: 0 Warnings: 0 db1:root>SELECT * FROM tb1_bkc; +-------+--------+------+ | empid | name | age | +-------+--------+------+ | A101 | 佐藤 | 40 | | A102 | 高教 | 28 | | A103 | 中川 | 20 | | A104 | 渡边 | 23 | | A104 | 渡边 | 23 | | A105 | 西哲 | 35 | | NULL | NULL | 2000 | | NULL | A101 | NULL | | NULL | A102 | NULL | | NULL | A103 | NULL | | NULL | A104 | NULL | | NULL | A104 | NULL | | NULL | A105 | NULL | | NULL | NULL | NULL | +-------+--------+------+ 14 rows in set (0.00 sec) db1:root>
删除表、数据库、记录
删除表
DROP TABLE 表名
db1:root>SHOW TABLES; +------------------+ | Tables_in_db1 | +------------------+ | t_date | | t_initial_serial | | t_pk | | t_series | | t_time | | t_uniq | | tb1 | | tb1A | | tb1B | | tb1C | | tb1D | | tb1E | | tb1F | | tb1G | | tb1_bk | | tb1_bkc | | test | +------------------+ 17 rows in set (0.00 sec) db1:root>DROP TABLE tb1A; Query OK, 0 rows affected (0.02 sec) db1:root>SHOW TABLES; +------------------+ | Tables_in_db1 | +------------------+ | t_date | | t_initial_serial | | t_pk | | t_series | | t_time | | t_uniq | | tb1 | | tb1B | | tb1C | | tb1D | | tb1E | | tb1F | | tb1G | | tb1_bk | | tb1_bkc | | test | +------------------+ 16 rows in set (0.00 sec)
DROP TABLE IF EXISTS 这样删除能避免报错
包括创建表或者数据的时候，可以这么写 CREATE DATABASE IF NOT EXISTS
CREATE TABLE IF NOT EXISTS
非常好用的语句。
DROP DATABASE 数据库名称
删除数据库
DELECT FROM 表名;删除所有表内的数据
一般后面加WHERE 使用，要不然很危险，删除表数据的时候，是一行一行删除的。
db1:root>DELETE FROM tb1_bk; Query OK, 7 rows affected (0.01 sec) db1:root>SELECT * FROM tb1_bk; Empty set (0.00 sec) db1:root>
使用mysqladmin -u -p
CREATE 数据库名字
DROP 数据库名字
不要加DATABASE
删除数据库
mysql 数据库 -uxxx -p xxx-e "命令执行语句"
练习题
mysql> CREATE TABLE t_name (a VARCHAR(10)); Query OK, 0 rows affected (0.04 sec) mysql> INSERt INTO t_name SELECT name FROM tb1; Query OK, 7 rows affected (0.01 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM t_name; +--------+ | a | +--------+ | 佐藤 | | 高教 | | 中川 | | 渡边 | | 渡边 | | 西哲 | | NULL | +--------+ 7 rows in set (0.00 sec) mysql>
第8章 使用各种条件进行提取
mysql> DESC tb; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | empid | varchar(10) | YES | | NULL | | | sales | int(11) | YES | | NULL | | | month | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> SELECT empid,sales FROM tb; +-------+-------+ | empid | sales | +-------+-------+ | a103 | 101 | | a102 | 54 | | a104 | 181 | | a101 | 184 | | a103 | 17 | | a101 | 300 | | a102 | 205 | | a104 | 93 | | a103 | 12 | | a107 | 87 | +-------+-------+ 10 rows in set (0.00 sec) mysql> SELECT empid,sales,sales FROM tb; +-------+-------+-------+ | empid | sales | sales | +-------+-------+-------+ | a103 | 101 | 101 | | a102 | 54 | 54 | | a104 | 181 | 181 | | a101 | 184 | 184 | | a103 | 17 | 17 | | a101 | 300 | 300 | | a102 | 205 | 205 | | a104 | 93 | 93 | | a103 | 12 | 12 | | a107 | 87 | 87 | +-------+-------+-------+ 10 rows in set (0.00 sec)
显示指定列的数据，同一个列只要列名出现多次，可以多次显示。
使用别名进行显示
mysql> SELECT empid AS 员工号,sales AS 销售额 FROM tb; +-----------+-----------+ | 员工号 | 销售额 | +-----------+-----------+ | a103 | 101 | | a102 | 54 | | a104 | 181 | | a101 | 184 | | a103 | 17 | | a101 | 300 | | a102 | 205 | | a104 | 93 | | a103 | 12 | | a107 | 87 | +-----------+-----------+ 10 rows in set (0.00 sec) mysql>
计算列值或处理字符串之后显示列
算数远算符 +,-,*,/,DIV，地板除,%或MOD 取余数。
mysql> SELECT sales*10000 AS 销售额 FROM tb; +-----------+ | 销售额 | +-----------+ | 1010000 | | 540000 | | 1810000 | | 1840000 | | 170000 | | 3000000 | | 2050000 | | 930000 | | 120000 | | 870000 | +-----------+ 10 rows in set (0.00 sec)
使用函数进行计算
AVG平均 SUM求和 COUNT统计次数
mysql> SELECT AVG (sales) FROM tb; +-------------+ | AVG (sales) | +-------------+ | 123.4000 | +-------------+ 1 row in set (0.01 sec) mysql> SELECT SUM(sales) FROM tb; +------------+ | SUM(sales) | +------------+ | 1234 | +------------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(sales) FROM tb; +--------------+ | COUNT(sales) | +--------------+ | 10 | +--------------+ 1 row in set (0.00 sec) mysql>
用于显示各种信息的函数
mysql> SELECT PI();
+----------+
| PI() |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.28 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| db1 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT USER();
+----------------+
| USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT CHARSET('折耳根就');
+-------------------------+
| CHARSET('折耳根就') |
+-------------------------+
| utf8 |
+-------------------------+
1 row in set (0.00 sec)
mysql>
链接字符串
concat
db1:root>SELECT CONCAT(empid,name,'先生') FROM tb1; +-----------------------------+ | CONCAT(empid,name,'先生') | +-----------------------------+ | A101佐藤先生 | | A102高教先生 | | A103中川先生 | | A104渡边先生 | | A104渡边先生 | | A105西哲先生 | | NULL | +-----------------------------+ 7 rows in set (0.00 sec) db1:root>
字符串操作中常用的函数。
db1:root>SELECT LEFT(empid,2) FROM tb1; +---------------+ | LEFT(empid,2) | +---------------+ | A1 | | A1 | | A1 | | A1 | | A1 | | A1 | | NULL | +---------------+ 7 rows in set (0.00 sec) db1:root>SELECT RIGHT(empid,2) FROM tb1; +----------------+ | RIGHT(empid,2) | +----------------+ | 01 | | 02 | | 03 | | 04 | | 04 | | 05 | | NULL | +----------------+ 7 rows in set (0.00 sec) db1:root>
左起或者右起取值。
db1:root>SELECT SUBSTRING(empid,2,3) FROM tb1; +----------------------+ | SUBSTRING(empid,2,3) | +----------------------+ | 101 | | 102 | | 103 | | 104 | | 104 | | 105 | | NULL | +----------------------+ 7 rows in set (0.00 sec)
SUBSTRING(列名,起始位置，连续显示数量)
起始位置为1开始
SELECT REPEAT('!',age) FROM tb1;
REPEAT第一个参数为重复出现的图标，第二个为字段参数。
db1:root>SELECT REVERSE(name)
-> FROM tb1;
+---------------+
| REVERSE(name) |
+---------------+
| 藤佐 |
| 教高 |
| 川中 |
| 边渡 |
| 边渡 |
| 哲西 |
| NULL |
+---------------+
7 rows in set (0.00 sec)
REVERES(列名),反转字符串
日期和时间函数
db1:root>CREATE TABLE t_now(a INT AUTO_INCREMENT PRIMARY KEY,b DATETIME); Query OK, 0 rows affected (0.03 sec) db1:root>INSERT INTO t_now (b) VALUES(NOW()),(NOW()),(NOW()),(NOW()),(NOW()); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 db1:root>SELECT * FROM t_now; +---+---------------------+ | a | b | +---+---------------------+ | 1 | 2020-03-23 19:15:03 | | 2 | 2020-03-23 19:15:03 | | 3 | 2020-03-23 19:15:03 | | 4 | 2020-03-23 19:15:03 | | 5 | 2020-03-23 19:15:03 | +---+---------------------+ 5 rows in set (0.00 sec) db1:root>
限制条件进行显示
限制记录数进行显示
LIMIT 显示的数量
db1:root>SELECT * FROM tb LIMIT 3; +-------+-------+-------+ | empid | sales | month | +-------+-------+-------+ | a103 | 101 | 4 | | a102 | 54 | 5 | | a104 | 181 | 4 | +-------+-------+-------+ 3 rows in set (0.00 sec) db1:root>
使用WHERE提取记录
db1:root>SELECT * FROM tb WHERE sales>=100; +-------+-------+-------+ | empid | sales | month | +-------+-------+-------+ | a103 | 101 | 4 | | a104 | 181 | 4 | | a101 | 184 | 4 | | a101 | 300 | 5 | | a102 | 205 | 6 | +-------+-------+-------+ 5 rows in set (0.00 sec)
常用的比较运算符
=, >, >=, <, <=, !=,<>, IN, NOT IN, BETWENN AND, NOT BETWEEN AND
db1:root>SELECT * FROM tb WHERE sales<50; +-------+-------+-------+ | empid | sales | month | +-------+-------+-------+ | a103 | 17 | 5 | | a103 | 12 | 6 | +-------+-------+-------+ 2 rows in set (0.00 sec)
不在4月份的显示
db1:root>SELECT * FROM tb WHERE month !=4; +-------+-------+-------+ | empid | sales | month | +-------+-------+-------+ | a102 | 54 | 5 | | a103 | 17 | 5 | | a101 | 300 | 5 | | a102 | 205 | 6 | | a104 | 93 | 5 | | a103 | 12 | 6 | | a107 | 87 | 6 | +-------+-------+-------+ 7 rows in set (0.00 sec) db1:root>SELECT * FROM tb WHERE month <>4; +-------+-------+-------+ | empid | sales | month | +-------+-------+-------+ | a102 | 54 | 5 | | a103 | 17 | 5 | | a101 | 300 | 5 | | a102 | 205 | 6 | | a104 | 93 | 5 | | a103 | 12 | 6 | | a107 | 87 | 6 | +-------+-------+-------+ 7 rows in set (0.00 sec) db1:root>
一些简单的示例
db1:root>SELECT * FROM tb WHERE sales BETWEEN 50 AND 100; +-------+-------+-------+ | empid | sales | month | +-------+-------+-------+ | a102 | 54 | 5 | | a104 | 93 | 5 | | a107 | 87 | 6 | +-------+-------+-------+ 3 rows in set (0.00 sec) db1:root>SELECT * FROM tb WHERE sales NOT BETWEEN 50 AND 100; +-------+-------+-------+ | empid | sales | month | +-------+-------+-------+ | a103 | 101 | 4 | | a104 | 181 | 4 | | a101 | 184 | 4 | | a103 | 17 | 5 | | a101 | 300 | 5 | | a102 | 205 | 6 | | a103 | 12 | 6 | +-------+-------+-------+ 7 rows in set (0.00 sec) db1:root>SELECT * FROM tb WHERE month in (5,6); +-------+-------+-------+ | empid | sales | month | +-------+-------+-------+ | a102 | 54 | 5 | | a103 | 17 | 5 | | a101 | 300 | 5 | | a102 | 205 | 6 | | a104 | 93 | 5 | | a103 | 12 | 6 | | a107 | 87 | 6 | +-------+-------+-------+ 7 rows in set (0.00 sec)
使用字符串作为条件
模糊查找使用LIKE来实现，搭配%以及_来使用，%表示任意字符,_表示一个字符。
db1:root>SELECT name FROM tb1 WHERE name LIKE "%川%"; +--------+ | name | +--------+ | 中川 | +--------+ 1 row in set (0.00 sec) db1:root>
同样可以使用NOT LIKE来表示不包含这个字符的选项
使用NULL作为条件
db1:root>SELECT * FROM tb1H WHERE age IS NUll; +-------+-----------------+------+ | empid | name | age | +-------+-----------------+------+ | NULL | 我是各名字 | NULL | +-------+-----------------+------+ 1 row in set (0.00 sec) db1:root>SELECT * FROM tb1H WHERE age = NUll; Empty set (0.00 sec) db1:root>
当做NULL进行查寻的时候需要用IS 和 IS NOT 进行匹配，用=与<>不能进行匹配。
当遇到多个记录显示的时候，可以用过DISTINCT来去重
db1:root>SELECT empid FROM tb; +-------+ | empid | +-------+ | a103 | | a102 | | a104 | | a101 | | a103 | | a101 | | a102 | | a104 | | a103 | | a107 | +-------+ 10 rows in set (0.00 sec) db1:root>SELECT DISTINCT empid FROM tb; +-------+ | empid | +-------+ | a103 | | a102 | | a104 | | a101 | | a107 | +-------+ 5 rows in set (0.00 sec) db1:root>
指定多个条件进行选择
使用AND与OR进行多个条件的选择
db1:root>SELECT * FROM tb WHERE empid='A101' AND month=4; +-------+-------+-------+ | empid | sales | month | +-------+-------+-------+ | a101 | 184 | 4 | +-------+-------+-------+ 1 row in set (0.00 sec) db1:root>
db1:root>SELECT * FROM tb WHERE sales<50 OR sales>200; +-------+-------+-------+ | empid | sales | month | +-------+-------+-------+ | a103 | 17 | 5 | | a101 | 300 | 5 | | a102 | 205 | 6 | | a103 | 12 | 6 | +-------+-------+-------+ 4 rows in set (0.00 sec) db1:root>
AND 与 OR 一起执行并列的时候，先执行AND后执行OR，如果想先执行OR可以用()包裹.
使用CASE WHEN
CASE
WHEN 条件1 THEN 显示的值
WHEN 条件2 THEN 显示的值
...
ELSE 不满足条件显示的值
END
db1:root>SELECT empid,sales, CASE WHEN sales>=100 THEN '高' WHEN sales>=50 THEN '中等' ELSE '低' END AS 等级 FROM tb; +-------+-------+--------+ | empid | sales | 等级 | +-------+-------+--------+ | a103 | 101 | 高 | | a102 | 54 | 中等 | | a104 | 181 | 高 | | a101 | 184 | 高 | | a103 | 17 | 低 | | a101 | 300 | 高 | | a102 | 205 | 高 | | a104 | 93 | 中等 | | a103 | 12 | 低 | | a107 | 87 | 中等 | +-------+-------+--------+ 10 rows in set (0.00 sec) db1:root>
用起来还是非常不错的，直接帮你等级排好了。
再手写一遍
CASE
WHEN XXX THEN UUU
ELSE ZZZ
END
排序
ORDER BY
DESC倒序 从大到小 ASC从小到大默认ASC
db1:root>SELECT * FROM tb ORDER BY sales; +-------+-------+-------+ | empid | sales | month | +-------+-------+-------+ | a103 | 12 | 6 | | a103 | 17 | 5 | | a102 | 54 | 5 | | a107 | 87 | 6 | | a104 | 93 | 5 | | a103 | 101 | 4 | | a104 | 181 | 4 | | a101 | 184 | 4 | | a102 | 205 | 6 | | a101 | 300 | 5 | +-------+-------+-------+ 10 rows in set (0.00 sec) db1:root>
倒序，显示前5条
db1:root>SELECT * FROM tb ORDER BY sales DESC LIMIT 5; +-------+-------+-------+ | empid | sales | month | +-------+-------+-------+ | a101 | 300 | 5 | | a102 | 205 | 6 | | a101 | 184 | 4 | | a104 | 181 | 4 | | a103 | 101 | 4 | +-------+-------+-------+ 5 rows in set (0.00 sec) db1:root>
指定记录的显示范围
db1:root>SELECT * FROM tb ORDER BY sales DESC LIMIT 2 OFFSET 4; +-------+-------+-------+ | empid | sales | month | +-------+-------+-------+ | a103 | 101 | 4 | | a104 | 93 | 5 | +-------+-------+-------+ 2 rows in set (0.00 sec) db1:root>SELECT * FROM tb ORDER BY sales DESC LIMIT 4,2; +-------+-------+-------+ | empid | sales | month | +-------+-------+-------+ | a103 | 101 | 4 | | a104 | 93 | 5 | +-------+-------+-------+ 2 rows in set (0.00 sec)
通过LIMIT 显示条数 OFFSET 定位，也可以指定通过LIMIT N,M N为定位点，M为显示的条数
定位点是1的话显示从第二个数据开始，这个是索引一样，从0开始
分组显示
GROUP BY
只是分组显示没有任何意义，需要配合一些统计的函数进行操作。
db1:root>SELECT * FROM tb GROUP BY empid; ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db1.tb.sales' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by db1:root>SELECT SUM(sales) FROM tb GROUP BY empid; +------------+ | SUM(sales) | +------------+ | 484 | | 259 | | 130 | | 274 | | 87 | +------------+ 5 rows in set (0.00 sec) db1:root>SELECT empid, SUM(sales) FROM tb GROUP BY empid; +-------+------------+ | empid | SUM(sales) | +-------+------------+ | a101 | 484 | | a102 | 259 | | a103 | 130 | | a104 | 274 | | a107 | 87 | +-------+------------+ 5 rows in set (0.00 sec) db1:root>
我这里测试当不填写统计函数的时候，GROUP BY不能运行。
db1:root>SELECT COUNT(*) AS 个数 FROM tb GROUP BY empid; +--------+ | 个数 | +--------+ | 2 | | 2 | | 3 | | 2 | | 1 | +--------+ 5 rows in set (0.00 sec) db1:root>
db1:root>SELECT empid,AVG(sales) as 平均值 FROM tb GROUP BY empid; +-------+-----------+ | empid | 平均值 | +-------+-----------+ | a101 | 242.0000 | | a102 | 129.5000 | | a103 | 43.3333 | | a104 | 137.0000 | | a107 | 87.0000 | +-------+-----------+ 5 rows in set (0.00 sec) db1:root>
当GROUP BY 分组的时候，如果显示的内容里面有多个数据，报错的情况下，可以用GROUP_CONCAT函数
mysql> SELECT empid,sales FROM tb GROUP BY empid; ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db1.tb.sales' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> SELECT * FROM tb; +-------+-------+-------+ | empid | sales | month | +-------+-------+-------+ | a103 | 101 | 4 | | a102 | 54 | 5 | | a104 | 181 | 4 | | a101 | 184 | 4 | | a103 | 17 | 5 | | a101 | 300 | 5 | | a102 | 205 | 6 | | a104 | 93 | 5 | | a103 | 12 | 6 | | a107 | 87 | 6 | +-------+-------+-------+ 10 rows in set (0.00 sec) mysql>
从错误可以看出来，通过empid分组，empid组里面包含了很多sales数据，这个时候，如果直接写字段名会报错，当然你可以通过设置MySQL的选择显示
但更好的方式通过GROUP_CONCAT来显示
mysql> SELECT empid,GROUP_CONCAT(sales) FROM tb GROUP BY empid; +-------+---------------------+ | empid | GROUP_CONCAT(sales) | +-------+---------------------+ | a101 | 184,300 | | a102 | 54,205 | | a103 | 101,17,12 | | a104 | 181,93 | | a107 | 87 | +-------+---------------------+ 5 rows in set (0.00 sec) mysql>
mysql> help GROUP_CONCAT
Name: 'GROUP_CONCAT'
Description:
Syntax:
GROUP_CONCAT(expr)
This function returns a string result with the concatenated non-NULL
values from a group. It returns NULL if there are no non-NULL values.
The full syntax is as follows:
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
URL: https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html
Examples:
mysql> SELECT student_name,
GROUP_CONCAT(test_score)
FROM student
GROUP BY student_name;
mysql>
设置条件分组显示
用HAVING设置的条件适用于分组的结果值
db1:root>SELECT empid,SUM(sales) AS 总和 FROM tb GROUP BY empid HAVING 总和>200; +-------+--------+ | empid | 总和 | +-------+--------+ | a101 | 484 | | a102 | 259 | | a104 | 274 | +-------+--------+ 3 rows in set (0.00 sec) db1:root>
提取后再进行分组
db1:root>SELECT empid,AVG(sales) FROM tb WHERE sales>=50 GROUP BY empid; +-------+------------+ | empid | AVG(sales) | +-------+------------+ | a101 | 242.0000 | | a102 | 129.5000 | | a103 | 101.0000 | | a104 | 137.0000 | | a107 | 87.0000 | +-------+------------+ 5 rows in set (0.00 sec) db1:root>
分组后排序
db1:root>SELECT empid,AVG(sales) AS avg
-> FROM tb GROUP BY empid
-> ORDER BY avg DESC;
+-------+----------+
| empid | avg |
+-------+----------+
| a101 | 242.0000 |
| a104 | 137.0000 |
| a102 | 129.5000 |
| a107 | 87.0000 |
| a103 | 43.3333 |
+-------+----------+
5 rows in set (0.00 sec)
db1:root>
按照ID统计平均值后进行排序
分组方法总结
一个是提取记录后分组，用了WHERE都原始数据进行过滤
二是分组后提取记录，可以用HAVING对分组后的记录进行筛选
db1:root>SELECT empid, AVG(sales) as avg
-> FROM tb WHERE sales>=50 GROUP BY empid
-> ORDER BY avg DESC;
+-------+----------+
| empid | avg |
+-------+----------+
| a101 | 242.0000 |
| a104 | 137.0000 |
| a102 | 129.5000 |
| a103 | 101.0000 |
| a107 | 87.0000 |
+-------+----------+
5 rows in set (0.01 sec)
db1:root>
练习题
db1:root>SELECT CONCAT('合计',SUM(SALES),'万元') AS 销售额 FROM tb;
+------------------+
| 销售额 |
+------------------+
| 合计1234万元 |
+------------------+
1 row in set (0.00 sec)
db1:root>SELECT empid,AVG(sales) FROM tb WHERE sales >=50 GROUP BY empid HAVING AVG(sales) >=120 ORDER BY AVG(sales) DESC; +-------+------------+ | empid | AVG(sales) | +-------+------------+ | a101 | 242.0000 | | a104 | 137.0000 | | a102 | 129.5000 | +-------+------------+ 3 rows in set (0.00 sec) db1:root>
SELECT ~ FROM ~ WHERE~ GROUP BY~ HAVING~ ORDER BY~
实际执行为
FROM WHERE GROUPBY HAVING SELECT ORDER BY
文字解释就是GROUP BY 分组之前会先执行WHERE ，而HAVING执行的对象是GROUP BY分组后的结果。另外，可以看到ORDER BY重新排列了SELECT结果.
编辑数据
瞬间更新列中所有的记录
UPDATE 表名 SET 列名=设置的值
一般结合WHERE使用，单用的话要小心，会把所有的列数据修改了。
修改表数据是一个表格内容一个表格内容修改的，先选择好列，再通过WHERE 选择行
db1:root>ALTER TABLE tb ADD remark VARCHAR(100); Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 db1:root>DESCRIBE tb; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | empid | varchar(10) | YES | | NULL | | | sales | int(11) | YES | | NULL | | | month | int(11) | YES | | NULL | | | remark | varchar(100) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) db1:root>UPDATE tb SET remark='无特殊记录'; Query OK, 10 rows affected (0.00 sec) Rows matched: 10 Changed: 10 Warnings: 0 db1:root>SELECT * FROM tb; +-------+-------+-------+-----------------+ | empid | sales | month | remark | +-------+-------+-------+-----------------+ | a103 | 101 | 4 | 无特殊记录 | | a102 | 54 | 5 | 无特殊记录 | | a104 | 181 | 4 | 无特殊记录 | | a101 | 184 | 4 | 无特殊记录 | | a103 | 17 | 5 | 无特殊记录 | | a101 | 300 | 5 | 无特殊记录 | | a102 | 205 | 6 | 无特殊记录 | | a104 | 93 | 5 | 无特殊记录 | | a103 | 12 | 6 | 无特殊记录 | | a107 | 87 | 6 | 无特殊记录 | +-------+-------+-------+-----------------+ 10 rows in set (0.00 sec) db1:root>
为了防止初学者的数据库管理员出现自杀行为，可以再登录的时候添加--safe-uptates这样再修改或者删除数据的时候，如果不加WHERE条件筛选将无法执行。
[sidian@VM_0_17_centos ~]$ mysql -uroot --safe-updates -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 104 Server version: 5.7.28 MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> \u db1 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A mysql> DELETE FROM tb; ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. mysql> UPDATE tb SET empid=1; ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. mysql>
修改特定数据的值
通过添加WHERE
mysql> UPDATE tb SET remark='优秀' WHERE sales>=100; Query OK, 5 rows affected (0.00 sec) Rows matched: 5 Changed: 5 Warnings: 0 mysql> SELECT * FROM tb; +-------+-------+-------+-----------------+ | empid | sales | month | remark | +-------+-------+-------+-----------------+ | a103 | 101 | 4 | 优秀 | | a102 | 54 | 5 | 无特殊记录 | | a104 | 181 | 4 | 优秀 | | a101 | 184 | 4 | 优秀 | | a103 | 17 | 5 | 无特殊记录 | | a101 | 300 | 5 | 优秀 | | a102 | 205 | 6 | 优秀 | | a104 | 93 | 5 | 无特殊记录 | | a103 | 12 | 6 | 无特殊记录 | | a107 | 87 | 6 | 无特殊记录 | +-------+-------+-------+-----------------+ 10 rows in set (0.00 sec) mysql>
通过LIMIT对限定条数进行修改
mysql> UPDATE tb SET remark='加油'
-> ORDER BY sales LIMIT 3;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> SELECT * FROM tb;
+-------+-------+-------+-----------------+
| empid | sales | month | remark |
+-------+-------+-------+-----------------+
| a103 | 101 | 4 | 优秀 |
| a102 | 54 | 5 | 加油 |
| a104 | 181 | 4 | 优秀 |
| a101 | 184 | 4 | 优秀 |
| a103 | 17 | 5 | 加油 |
| a101 | 300 | 5 | 优秀 |
| a102 | 205 | 6 | 优秀 |
| a104 | 93 | 5 | 无特殊记录 |
| a103 | 12 | 6 | 加油 |
| a107 | 87 | 6 | 无特殊记录 |
+-------+-------+-------+-----------------+
10 rows in set (0.00 sec)
mysql>
直接使用了ORDER BY 命令对数据进行排序，然后通过LIMIT 限制修改的输出条数
复制符合条件的记录
mysql> CREATE TABLE tb_A101 SELECT * FROM tb WHERE empid='a101'; Query OK, 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM tb_A101; +-------+-------+-------+ | empid | sales | month | +-------+-------+-------+ | a101 | 184 | 4 | | a101 | 300 | 5 | +-------+-------+-------+ 2 rows in set (0.00 sec) mysql>
mysql> CREATE TABLE tb_2to5 SELECT * FROM tb ORDER BY sales DESC LIMIT 4 OFFSET 2; Query OK, 4 rows affected (0.03 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM tb_2to5; +-------+-------+-------+ | empid | sales | month | +-------+-------+-------+ | a101 | 184 | 4 | | a104 | 181 | 4 | | a103 | 101 | 4 | | a104 | 93 | 5 | +-------+-------+-------+ 4 rows in set (0.00 sec)
删除符合条件的记录
mysql> SELECT * FROM tb1; +-------+--------+------+ | empid | name | age | +-------+--------+------+ | A101 | 佐藤 | 40 | | A102 | 高教 | 28 | | A103 | 中川 | 20 | | A104 | 渡边 | 23 | | A104 | 渡边 | 23 | | A105 | 西哲 | 35 | | NULL | NULL | 2000 | +-------+--------+------+ 7 rows in set (0.00 sec) mysql> DELETE FROM tb1 WHERE age<30; Query OK, 4 rows affected (0.00 sec) mysql> SELECT * FROM tb1; +-------+--------+------+ | empid | name | age | +-------+--------+------+ | A101 | 佐藤 | 40 | | A105 | 西哲 | 35 | | NULL | NULL | 2000 | +-------+--------+------+ 3 rows in set (0.00 sec) mysql>
mysql> DELETE FROM tb_copy ORDER BY sales DESC LIMIT 4; Query OK, 4 rows affected (0.01 sec) mysql> SELECT * FROM tb_copy; +-------+-------+-------+ | empid | sales | month | +-------+-------+-------+ | a103 | 101 | 4 | | a102 | 54 | 5 | | a103 | 17 | 5 | | a104 | 93 | 5 | | a103 | 12 | 6 | | a107 | 87 | 6 | +-------+-------+-------+ 6 rows in set (0.00 sec) mysql>
练习题
mysql> SELECT * FROM t_stock; +-----------+------+------------+ | a | b | c | +-----------+------+------------+ | 东分店 | 200 | 2011-08-08 | | 西分店 | 500 | 2017-06-15 | | 南分店 | 100 | 2010-02-23 | | 北分店 | 400 | 2019-08-08 | +-----------+------+------------+ 4 rows in set (0.00 sec) mysql> DESCRIBE t_stock; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | a | varchar(10) | YES | | NULL | | | b | int(11) | YES | | NULL | | | c | date | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql>
mysql> CREATE TABLE t_stock_new SELECT * FROM t_stock WHERE NOW()-INTERVAL 5 YEAR > c; Query OK, 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM t_stock_new; +-----------+------+------------+ | a | b | c | +-----------+------+------------+ | 东分店 | 200 | 2011-08-08 | | 南分店 | 100 | 2010-02-23 | +-----------+------+------------+ 2 rows in set (0.00 sec)
MYSQL里面的时间是可以加减的，同过INTERVAL X YEAR 进行+-操作
并且时间的话，可以直接用><符号进行比较。
使用多个表
显示多个表的记录
显示多条提取结果
mysql> SELECT * FROM tb1 UNION SELECT * FROM tb2; +-------+--------+------+ | empid | name | age | +-------+--------+------+ | A101 | 佐藤 | 40 | | A102 | 高教 | 28 | | A103 | 中川 | 20 | | A104 | 渡边 | 23 | | A105 | 西哲 | 35 | | A106 | 中村 | 26 | | A107 | 田中 | 24 | | A108 | 铃木 | 23 | | A109 | 村井 | 25 | | A110 | 吉田 | 27 | +-------+--------+------+ 10 rows in set (0.00 sec) mysql> SELECT * FROM tb1 UNION SELECT * FROM tb3; ERROR 1222 (21000): The used SELECT statements have a different number of columns mysql> SELECT * FROM tb3; +-------+--------------+ | empid | region | +-------+--------------+ | A101 | 东京都 | | A102 | 埼玉县 | | A103 | 神奈川县 | | A104 | 北海道 | | A105 | 静冈县 | +-------+--------------+ 5 rows in set (0.00 sec) mysql>
对于字段相同的数据，筛选出来的数据可以通过UNION连接显示。
按条件合并多条提取结果进行显示
mysql> SELECT empid FROM tb WHERE sales >200 UNION SELECT empid
-> FROM tb1 WHERE age >=35 ;
+-------+
| empid |
+-------+
| a101 |
| a102 |
| A105 |
+-------+
3 rows in set (0.00 sec)
mysql>
UNION对与两个合并数据的重复数据，自动去重了。
合并显示多条提取结果（允许重复）
mysql> (SELECT empid FROM tb WHERE sales>200) UNION ALL (SELECT empid FROM tb1 WHERE age>=35); +-------+ | empid | +-------+ | a101 | | a102 | | A101 | | A105 | +-------+ 4 rows in set (0.00 sec) mysql>
连接多个表并显示（内连接）
使用JOIN INNER JOIN 也可以
mysql> SELECT * FROM tb INNER JOIN tb1 ON tb.empid=tb1.empid WHERE age>30; +-------+-------+-------+-------+--------+------+ | empid | sales | month | empid | name | age | +-------+-------+-------+-------+--------+------+ | a101 | 184 | 4 | A101 | 佐藤 | 40 | | a101 | 300 | 5 | A101 | 佐藤 | 40 | +-------+-------+-------+-------+--------+------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM tb INNER JOIN tb1 ON tb.empid=tb1.empid WHERE sales>130; +-------+-------+-------+-------+--------+------+ | empid | sales | month | empid | name | age | +-------+-------+-------+-------+--------+------+ | a101 | 184 | 4 | A101 | 佐藤 | 40 | | a101 | 300 | 5 | A101 | 佐藤 | 40 | | a102 | 205 | 6 | A102 | 高教 | 28 | | a104 | 181 | 4 | A104 | 渡边 | 23 | +-------+-------+-------+-------+--------+------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM tb JOIN tb1 ON tb.empid=tb1.empid WHERE sales>130; +-------+-------+-------+-------+--------+------+ | empid | sales | month | empid | name | age | +-------+-------+-------+-------+--------+------+ | a101 | 184 | 4 | A101 | 佐藤 | 40 | | a101 | 300 | 5 | A101 | 佐藤 | 40 | | a102 | 205 | 6 | A102 | 高教 | 28 | | a104 | 181 | 4 | A104 | 渡边 | 23 | +-------+-------+-------+-------+--------+------+ 4 rows in set (0.00 sec) mysql>
通过测试，将两个表合并了以后，后面的WHERE 条件，两个表的条件都可以使用了。
记录的情况是，ON条件下，两个表都满足的情况。
选择列进行显示
在进行内联显示，我的理解，由于两个表格通过INNER JOIN 以后，SELECT是两个表格用了一个
所有在添加显示的列时，除了用*，其他用列名的方式必须通过表名.列名的方式告诉MySql
给表添加别名
表名 AS 别名
mysql> SELECT x.empid,y.name,x.sales FROM tb AS x INNER JOIN tb1 AS y ON x.empid=y.empid; +-------+--------+-------+ | empid | name | sales | +-------+--------+-------+ | a103 | 中川 | 101 | | a102 | 高教 | 54 | | a104 | 渡边 | 181 | | a101 | 佐藤 | 184 | | a103 | 中川 | 17 | | a101 | 佐藤 | 300 | | a102 | 高教 | 205 | | a104 | 渡边 | 93 | | a103 | 中川 | 12 | +-------+--------+-------+ 9 rows in set (0.00 sec) mysql>
使用USING代替ON 表1.字段=表2.字段，当字段的名字相等时
mysql> SELECT * FROM tb INNER JOIN tb1 USING(empid); +-------+-------+-------+--------+------+ | empid | sales | month | name | age | +-------+-------+-------+--------+------+ | a103 | 101 | 4 | 中川 | 20 | | a102 | 54 | 5 | 高教 | 28 | | a104 | 181 | 4 | 渡边 | 23 | | a101 | 184 | 4 | 佐藤 | 40 | | a103 | 17 | 5 | 中川 | 20 | | a101 | 300 | 5 | 佐藤 | 40 | | a102 | 205 | 6 | 高教 | 28 | | a104 | 93 | 5 | 渡边 | 23 | | a103 | 12 | 6 | 中川 | 20 | +-------+-------+-------+--------+------+ 9 rows in set (0.00 sec) mysql>
mysql> SELECT * FROM tb INNER JOIN tb1 ON tb.empid=tb1.empid; +-------+-------+-------+-------+--------+------+ | empid | sales | month | empid | name | age | +-------+-------+-------+-------+--------+------+ | a103 | 101 | 4 | A103 | 中川 | 20 | | a102 | 54 | 5 | A102 | 高教 | 28 | | a104 | 181 | 4 | A104 | 渡边 | 23 | | a101 | 184 | 4 | A101 | 佐藤 | 40 | | a103 | 17 | 5 | A103 | 中川 | 20 | | a101 | 300 | 5 | A101 | 佐藤 | 40 | | a102 | 205 | 6 | A102 | 高教 | 28 | | a104 | 93 | 5 | A104 | 渡边 | 23 | | a103 | 12 | 6 | A103 | 中川 | 20 | +-------+-------+-------+-------+--------+------+ 9 rows in set (0.00 sec) mysql>
区别还是有一点，通过*输出，一个empid只显示一列，一个显示两列
还有一种更加简单的写法，直接FROM的时候，来至两张表，通过WHERE 设置条件，显示返回结果
mysql> SELECT * FROM tb,tb1 WHERE tb.empid=tb1.empid; +-------+-------+-------+-------+--------+------+ | empid | sales | month | empid | name | age | +-------+-------+-------+-------+--------+------+ | a103 | 101 | 4 | A103 | 中川 | 20 | | a102 | 54 | 5 | A102 | 高教 | 28 | | a104 | 181 | 4 | A104 | 渡边 | 23 | | a101 | 184 | 4 | A101 | 佐藤 | 40 | | a103 | 17 | 5 | A103 | 中川 | 20 | | a101 | 300 | 5 | A101 | 佐藤 | 40 | | a102 | 205 | 6 | A102 | 高教 | 28 | | a104 | 93 | 5 | A104 | 渡边 | 23 | | a103 | 12 | 6 | A103 | 中川 | 20 | +-------+-------+-------+-------+--------+------+ 9 rows in set (0.01 sec)
通过WHERE设置条件从连接表中提取记录
可以通过WHERE 表名.列名的方式进行筛选，但经过本人测试，只要两个表的字段名不是重复的，直接写列名就可以了
mysql> SELECT empid AS 员工号,name AS 姓名, sales AS 销售额 FROM tb INNER JOIN tb1 USING(empid)WHERE sales>=100; +-----------+--------+-----------+ | 员工号 | 姓名 | 销售额 | +-----------+--------+-----------+ | a101 | 佐藤 | 184 | | a101 | 佐藤 | 300 | | a102 | 高教 | 205 | | a103 | 中川 | 101 | | a104 | 渡边 | 181 | +-----------+--------+-----------+ 5 rows in set (0.00 sec) mysql>
查寻语句中，各个例我都没有加表名的限制，因为使用的几个列名都时唯一的，就算那个empid因为使用了USING也变成了单一列名，所以不用加表格名.列名来限制。
提取多个表中的记录
提取多个表中的记录，可以添加多个INNER JOIN ON或USING
mysql> SELECT empid,tb.sales,tb1.name,tb3.region
-> FROM tb INNER JOIN tb1 USING(empid)
-> INNER JOIN tb3 USING(empid);
+-------+-------+--------+--------------+
| empid | sales | name | region |
+-------+-------+--------+--------------+
| a103 | 101 | 中川 | 神奈川县 |
| a102 | 54 | 高教 | 埼玉县 |
| a104 | 181 | 渡边 | 北海道 |
| a101 | 184 | 佐藤 | 东京都 |
| a103 | 17 | 中川 | 神奈川县 |
| a101 | 300 | 佐藤 | 东京都 |
| a102 | 205 | 高教 | 埼玉县 |
| a104 | 93 | 渡边 | 北海道 |
| a103 | 12 | 中川 | 神奈川县 |
+-------+-------+--------+--------------+
9 rows in set (0.00 sec)
mysql>
INNER JOIN 后面一定要跟着ON或USING 条件（USING为函数）。
使用多个表的所有记录（外连接）
外连接是即使与连接键不匹配，外连接也会提取另一个表中的所有记录
LEFT JOIN RIGHT JOIN 分别为左外联 与右外联
使用左外连接
SELECT empid,name FROM tb LEFT JOIN tb1 USING(empid);
a101 佐藤 a101 佐藤 a102 高教 a102 高教 a103 中川 a103 中川 a103 中川 a104 渡边 a104 渡边 a107
使用右外连显示
mysql> SELECT tb.empid,name FROM tb RIGHT JOIN tb1 USING(empid); +-------+--------+ | empid | name | +-------+--------+ | a103 | 中川 | | a102 | 高教 | | a104 | 渡边 | | a101 | 佐藤 | | a103 | 中川 | | a101 | 佐藤 | | a102 | 高教 | | a104 | 渡边 | | a103 | 中川 | | NULL | 西哲 | +-------+--------+ 10 rows in set (0.00 sec)
左外连或右外连，我个人的理解，当偏向那一边的表时候，当显示的内容中有偏向表的列名，读取该列的行数据的时候，需要读取
另外一张表格内的字段信息，但由于该行信息并非条件满足字段，另一张表无法提供有效信息，在内连的时候，该行信息是不显示的，因为只显示满足ON条件
的信息，当外联的时候，另一张无法提供的信息，用NULL填写。
简单的理解，可以认为当你外联哪张表时候，外联对应的表显示的字段信息一行不会少，行信息缺少的信息用NULL填充。
当使用外联的时候，设置的条件USING(xxx)，默认的输出行数双方表中数量较多的那张表的行数
内联的时候，取的是双方都满足ON 条件下的数据，可能会缺少行信息。
外联的时候，就不存在这个，看来以后要多用外联，少用内联。
mysql> SELECT * FROM tb1; +-------+--------+------+ | empid | name | age | +-------+--------+------+ | A101 | 佐藤 | 40 | | A102 | 高教 | 28 | | A103 | 中川 | 20 | | A104 | 渡边 | 23 | | A105 | 西哲 | 35 | +-------+--------+------+ 5 rows in set (0.00 sec) mysql> SELECT * FROM tb; +-------+-------+-------+ | empid | sales | month | +-------+-------+-------+ | a103 | 101 | 4 | | a102 | 54 | 5 | | a104 | 181 | 4 | | a101 | 184 | 4 | | a103 | 17 | 5 | | a101 | 300 | 5 | | a102 | 205 | 6 | | a104 | 93 | 5 | | a103 | 12 | 6 | | a107 | 87 | 6 | +-------+-------+-------+ 10 rows in set (0.00 sec) mysql> SELECT tb.empid,tb1.name FROM tb LEFT JOIN tb1 USING(empid); +-------+--------+ | empid | name | +-------+--------+ | a101 | 佐藤 | | a101 | 佐藤 | | a102 | 高教 | | a102 | 高教 | | a103 | 中川 | | a103 | 中川 | | a103 | 中川 | | a104 | 渡边 | | a104 | 渡边 | | a107 | NULL | +-------+--------+ 10 rows in set (0.00 sec) mysql> SELECT tb.empid,tb1.name FROM tb RIGHT JOIN tb1 USING(empid); +-------+--------+ | empid | name | +-------+--------+ | a103 | 中川 | | a102 | 高教 | | a104 | 渡边 | | a101 | 佐藤 | | a103 | 中川 | | a101 | 佐藤 | | a102 | 高教 | | a104 | 渡边 | | a103 | 中川 | | NULL | 西哲 | +-------+--------+ 10 rows in set (0.00 sec) mysql>
避免混合使用做外连接和右外连接。
在使用中LEFT JOIN 与LEFT OUTER JOIN 效果一样，包括RIGHT JOIN。
插入话题：
笛卡尔积：CROSS JOIN
要理解各种JOIN首先要理解笛卡尔积。笛卡尔积就是将A表的每一条记录与B表的每一条记录强行拼在一起。所以，如果A表有n条记录，B表有m条记录，笛卡尔积产生的结果就会产生n*m条记录。下面的例子，t_blog有10条记录，t_type有5条记录，所有他们俩的笛卡尔积有50条记录。有五种产生笛卡尔积的方式如下。
MySql对于多变的JOIN在目前只支持一种算法---Nested-Loop Join（NLJ）。
NLJ的原理非常简单，就是内外两层循环，对于外循环中的每条记录，都要在内循环中做一次检索。
所以对两个数据量比较大的表进行JOIN以后生成的新表将非常大。
自连接
在自连接的时候，书中演示了JOIN之后添加ON或者USING也就是不添加任何显示输出的信息。
mysql> SELECT * FROM tb1 AS a JOIN tb1 AS b; +-------+--------+------+-------+--------+------+ | empid | name | age | empid | name | age | +-------+--------+------+-------+--------+------+ | A101 | 佐藤 | 40 | A101 | 佐藤 | 40 | | A102 | 高教 | 28 | A101 | 佐藤 | 40 | | A103 | 中川 | 20 | A101 | 佐藤 | 40 | | A104 | 渡边 | 23 | A101 | 佐藤 | 40 | | A105 | 西哲 | 35 | A101 | 佐藤 | 40 | | A101 | 佐藤 | 40 | A102 | 高教 | 28 | | A102 | 高教 | 28 | A102 | 高教 | 28 | | A103 | 中川 | 20 | A102 | 高教 | 28 | | A104 | 渡边 | 23 | A102 | 高教 | 28 | | A105 | 西哲 | 35 | A102 | 高教 | 28 | | A101 | 佐藤 | 40 | A103 | 中川 | 20 | | A102 | 高教 | 28 | A103 | 中川 | 20 | | A103 | 中川 | 20 | A103 | 中川 | 20 | | A104 | 渡边 | 23 | A103 | 中川 | 20 | | A105 | 西哲 | 35 | A103 | 中川 | 20 | | A101 | 佐藤 | 40 | A104 | 渡边 | 23 | | A102 | 高教 | 28 | A104 | 渡边 | 23 | | A103 | 中川 | 20 | A104 | 渡边 | 23 | | A104 | 渡边 | 23 | A104 | 渡边 | 23 | | A105 | 西哲 | 35 | A104 | 渡边 | 23 | | A101 | 佐藤 | 40 | A105 | 西哲 | 35 | | A102 | 高教 | 28 | A105 | 西哲 | 35 | | A103 | 中川 | 20 | A105 | 西哲 | 35 | | A104 | 渡边 | 23 | A105 | 西哲 | 35 | | A105 | 西哲 | 35 | A105 | 西哲 | 35 | +-------+--------+------+-------+--------+------+
排序的技巧 其一
书中通过两个表之间产生的笛卡尔积的表，通过分组与大小的比较后进行数量统计，统计各个年龄人的年龄排名
mysql> SELECT a.empid,COUNT(*) FROM tb1 AS a JOIN tb1 AS b WHERE a.age>=b.age GROUP BY a.empid; +-------+----------+ | empid | COUNT(*) | +-------+----------+ | A101 | 5 | | A102 | 3 | | A103 | 1 | | A104 | 2 | | A105 | 4 | +-------+----------+ 5 rows in set (0.00 sec)
起始这个理解，我个人感觉你可以当做两张独立的表来理解是一种不错的选择，还有就是用Python的双重for循环来理解也可以
从SELECT记录中SELECT（子查询）
显示最大值
mysql> SELECT * FROM tb ORDER BY sales DESC LIMIT 1; +-------+-------+-------+ | empid | sales | month | +-------+-------+-------+ | a101 | 300 | 5 | +-------+-------+-------+ 1 row in set (0.00 sec) mysql> SELECT * FROM tb WHERE sales IN (SELECT MAX(sales) FROM tb); +-------+-------+-------+ | empid | sales | month | +-------+-------+-------+ | a101 | 300 | 5 | +-------+-------+-------+ 1 row in set (0.00 sec) mysql> SELECT * FROM tb WHERE sales = (SELECT MAX(sales) FROM tb); +-------+-------+-------+ | empid | sales | month | +-------+-------+-------+ | a101 | 300 | 5 | +-------+-------+-------+
GROUP BY 函数
MAX、AVR、SUM等聚合函数也称为"GROUP BY 函数"，这类函数用于处理分组后的值。但是，在没有"GROUP BY ...的情况下，这类函数会将整个表作为一个组进行处理。
提取大于等于平均值的记录
mysql> SELECT * FROM tb1 WHERE age>=(SELECT AVG(age) FROM tb1); +-------+--------+------+ | empid | name | age | +-------+--------+------+ | A101 | 佐藤 | 40 | | A105 | 西哲 | 35 | +-------+--------+------+ 2 rows in set (0.00 sec) mysql>
使用IN（返回列的子查询）
mysql> SELECT name FROM tb1 WHERE empid IN (SELECT empid FROM tb WHERE sales>=200); +--------+ | name | +--------+ | 佐藤 | | 高教 | +--------+ 2 rows in set (0.00 sec) mysql>
使用EXISTS，仅以存在的记录为对象
这个使用比较抽象，我以前也没碰到过，使用EXISTS
mysql> SELECT * FROM tb1 WHERE EXISTS (SELECT * FROM tb WHERE tb.empid=tb1.empid); +-------+--------+------+ | empid | name | age | +-------+--------+------+ | A101 | 佐藤 | 40 | | A102 | 高教 | 28 | | A103 | 中川 | 20 | | A104 | 渡边 | 23 | +-------+--------+------+ 4 rows in set (0.00 sec)
起始我觉的这个逻辑不是很好理解，语句显示的字面意思就是存在tb1的empid与tb的empid相当的数据，最后显示出来
WHERE EXISTS 后面的条件是两个不同表两个字段满足的条件，更像一个多条件满足的集合。
有EXISTS 同样也有NOT EXISTS
mysql> SELECT * FROM tb1 WHERE NOT EXISTS (SELECT * FROM tb WHERE tb.empid=tb1.empid); +-------+--------+------+ | empid | name | age | +-------+--------+------+ | A105 | 西哲 | 35 | +-------+--------+------+ 1 row in set (0.00 sec)
书中最后一个案例，通过新建一张表，创建字段的方式，创建排名表
mysql> CREATE TABLE tb_rank LIKE tb; Query OK, 0 rows affected (0.04 sec) mysql> ALTER TABLE tb_rank ADD c_rank INT AUTO_INCREMENT PRIMARY KEY; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC tb_rank; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | empid | varchar(10) | YES | | NULL | | | sales | int(11) | YES | | NULL | | | month | int(11) | YES | | NULL | | | c_rank | int(11) | NO | PRI | NULL | auto_increment | +--------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> INSERT INTO tb_rank(empid,sales,month) SELECT * FROM tb ORDER BY SALES DESC; Query OK, 10 rows affected (0.01 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM tb_rank; +-------+-------+-------+--------+ | empid | sales | month | c_rank | +-------+-------+-------+--------+ | a101 | 300 | 5 | 1 | | a102 | 205 | 6 | 2 | | a101 | 184 | 4 | 3 | | a104 | 181 | 4 | 4 | | a103 | 101 | 4 | 5 | | a104 | 93 | 5 | 6 | | a107 | 87 | 6 | 7 | | a102 | 54 | 5 | 8 | | a103 | 17 | 5 | 9 | | a103 | 12 | 6 | 10 | +-------+-------+-------+--------+ 10 rows in set (0.00 sec) mysql>
练习题
这次习题比较简单，就不上了。
第一题，MySQL5.7安全问题会报错。
第11章 熟练使用视图
将SELECT的结果像表一样保留下来的虚表就是视图。视图不是表。因此，视图中并没有保存记录或者列中的记录。
也就是说，视图时一种逆袭，用于查寻记录。
跟新视图的记录，基表也会更新。视图只有在MySQL5或者更高的版本中使用。
创建视图
mysql> CREATE VIEW v1 AS SELECT name,age FROM tb1; Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM v1; +--------+------+ | name | age | +--------+------+ | 佐藤 | 40 | | 高教 | 28 | | 中川 | 20 | | 渡边 | 23 | | 西哲 | 35 | +--------+------+ 5 rows in set (0.00 sec) mysql>
通过视图更新列
mysql> UPDATE v1 SET name='主任佐藤' WHERE name='佐藤'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM v1; +--------------+------+ | name | age | +--------------+------+ | 主任佐藤 | 40 | | 高教 | 28 | | 中川 | 20 | | 渡边 | 23 | | 西哲 | 35 | +--------------+------+ 5 rows in set (0.00 sec) mysql> SELECT * FROM tb1; +-------+--------------+------+ | empid | name | age | +-------+--------------+------+ | A101 | 主任佐藤 | 40 | | A102 | 高教 | 28 | | A103 | 中川 | 20 | | A104 | 渡边 | 23 | | A105 | 西哲 | 35 | +-------+--------------+------+ 5 rows in set (0.00 sec) mysql>
视图的内容更新操作跟表操作基本一样，更新视图的信息，基础表信息也一样发生了改变。
设置条件创建视图
这里通过JOIN多表内联创建视图
mysql> CREATE VIEW v2 AS SELECT tb.empid,tb1.name,tb.sales FROM tb JOIN tb1 USING(empid) WHERE tb.sales>=100; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM v2; +-------+--------------+-------+ | empid | name | sales | +-------+--------------+-------+ | a101 | 主任佐藤 | 184 | | a101 | 主任佐藤 | 300 | | a102 | 高教 | 205 | | a103 | 中川 | 101 | | a104 | 渡边 | 181 | +-------+--------------+-------+ 5 rows in set (0.00 sec) mysql>
当更新基表时，视图会发生什么变化
mysql> UPDATE tb set sales=777 WHERE empid='A102'; Query OK, 2 rows affected (0.01 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> SELECT * FROM v2; +-------+--------------+-------+ | empid | name | sales | +-------+--------------+-------+ | a101 | 主任佐藤 | 184 | | a101 | 主任佐藤 | 300 | | a102 | 高教 | 777 | | a102 | 高教 | 777 | | a103 | 中川 | 101 | | a104 | 渡边 | 181 | +-------+--------------+-------+ 6 rows in set (0.00 sec) mysql>
这里更加说明了视图就时显示的信息，所以的显示信息，都时根据条件实时显示最新的基表信息。
确认视图
SHOW TABLES 查看视图，最后显示的就时视图
DESC 查看视图结构
mysql> DESCRIBE v1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | name | varchar(10) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> SHOW COLUMNS FROM v1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | name | varchar(10) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec)
查看建表语句。
SHOW CREATE VIEW 视图名字
基本跟表操作一模一样
11.4限制通过视图写入
视图的可见部分大多数时基表中的一部分内容。对视图执行INSERT操作，就意味这只能向表的其中一部分内容中插入数据。
用一句话概括就时对视图执行INSERT操作是有限制的。列如在使用UNION、JOIN、子查询的视图中，不能执行INSERT和UPDATE。
如果创造的视图只是从一个表中提取了列，那么执行INSERT和UPDATE是没有任何问题的。
mysql> INSERT INTO v1 VALUES('临时工',18);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM v1;
+--------------+------+
| name | age |
+--------------+------+
| 主任佐藤 | 40 |
| 高教 | 28 |
| 中川 | 20 |
| 渡边 | 23 |
| 西哲 | 35 |
| 临时工 | 18 |
+--------------+------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM tb1;
+-------+--------------+------+
| empid | name | age |
+-------+--------------+------+
| A101 | 主任佐藤 | 40 |
| A102 | 高教 | 28 |
| A103 | 中川 | 20 |
| A104 | 渡边 | 23 |
| A105 | 西哲 | 35 |
| NULL | 临时工 | 18 |
+-------+--------------+------+
6 rows in set (0.00 sec)
mysql>
从一个基础表中提取的视图进行INSERT操作没有问题。
设置了条件的基表中会发生什么变化。
mysql> CREATE VIEW v3 AS SELECT empid,sales FROM tb WHERE sales >=100; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM v3; +-------+-------+ | empid | sales | +-------+-------+ | a103 | 101 | | a102 | 777 | | a104 | 181 | | a101 | 184 | | a101 | 300 | | a102 | 777 | +-------+-------+ 6 rows in set (0.00 sec) mysql>
mysql> SELECT * FROM v3
-> ;
+-------+-------+
| empid | sales |
+-------+-------+
| a103 | 101 |
| a102 | 777 |
| a104 | 181 |
| a101 | 184 |
| a101 | 300 |
| a102 | 777 |
+-------+-------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM tb;
+--------------+-------+-------+
| empid | sales | month |
+--------------+-------+-------+
| a103 | 101 | 4 |
| a102 | 777 | 5 |
| a104 | 181 | 4 |
| a101 | 184 | 4 |
| a103 | 17 | 5 |
| a101 | 300 | 5 |
| a102 | 777 | 6 |
| a104 | 93 | 5 |
| a103 | 12 | 6 |
| a107 | 87 | 6 |
| 恶意插入 | 50 | NULL |
+--------------+-------+-------+
11 rows in set (0.01 sec)
mysql>
从反馈的结果来看，数据还是被插入了基表，但由于视图的条件显示，在视图上无法显示。
当与视图不匹配时报错
在创建视图的时候，最后面添加 WITH CHECK OPTION
mysql> CREATE VIEW v4 AS SELECT empid,sales FROM tb WHERE saleS>100 WITH CHECK OPTION;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO v4 VALUES('false',50);
ERROR 1369 (HY000): CHECK OPTION failed 'db1.v4'
mysql>
最后我自己实例插入JOIN的表格，直接报错了。
mysql> INSERT INTO v2 VALUES('999','打杂的',888);
ERROR 1394 (HY000): Can not insert into join view 'db1.v2' without fields list
替换视图
当创建一个视图时，已经存在重名的视图，要替换原来的视图，新建信的视图
CREATE OR REPLACE VIEW 命令。
mysql> CREATE OR REPLACE VIEW v1 AS SELECT NOW(); Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM v1; +---------------------+ | NOW() | +---------------------+ | 2020-03-26 21:25:34 | +---------------------+ 1 row in set (0.00 sec) mysql>
修改视图结构
ALTER VIEW xxx AS 从基表需要的内容
跟创建视图就第一个单次不一样，一个时CREATE ，一个时ALTER
mysql> ALTER VIEW v1 AS SELECT empid,sales FROM tb; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM v1; +--------------+-------+ | empid | sales | +--------------+-------+ | a103 | 101 | | a102 | 777 | | a104 | 181 | | a101 | 184 | | a103 | 17 | | a101 | 300 | | a102 | 777 | | a104 | 93 | | a103 | 12 | | a107 | 87 | | 恶意插入 | 50 | +--------------+-------+ 11 rows in set (0.00 sec) mysql>
删除视图
DROP VIEW xxx
如果视图不存在会报错
用DROP VIEW IF EXISTS xxx
复制可分为同步模式和异步模式。
复制的内容为二进制的日志
MySQL的复制是异步的，在MySQL5.5以及更高的版本可以实现一部分过程的同步。
以前的MySQL异步像UDP操作，不管从库是否收到数据，主库扔出去就不管事了。
但半同步的模式下，可以做到从库的响应，根据从库的响应来判断，判断从库是否同步成功。
还有就是无损半同步，原理差不多，也是等待从库的响应收到（从库完成了跟新，主库才更新）
练习题
mysql> CREATE VIEW v_sales AS SELECT empid,AVG(sales) FROM tb WHERE sales>=50 GROUP BY empid ORDER BY AVG(sales) DESC HAVING AVG(sales)>=120; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'HAVING AVG(sales)>=120' at line 1 mysql> CREATE VIEW v_sales AS SELECT empid,AVG(sales) FROM tb WHERE sales>=50 GROUP BY empid HAVING AVG(sales)>=120 ORDER BY AVG(sales) DESC; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM v_sales; +-------+------------+ | empid | AVG(sales) | +-------+------------+ | a102 | 777.0000 | | a101 | 242.0000 | | a104 | 137.0000 | +-------+------------+ 3 rows in set (0.00 sec) mysql>
这道题目，我前面犯下了一个错误，就是先ORDER BY 再HAVING，其实现在想想ORDER BY 应该在最后面，因为只有最后的数据确认了，才能ORDER BY。
第12章 数量使用存储功能。
存储过程后就可以记录一系列操作并批量运行它们了，如果需要每次多次执行相同的命令，就可以实现把这个定义为存储过程。
感觉有点像Python里面的函数。
什么是存储过程。
将多个SQL语句组合成一个只需要使用命令"CALL xx"就能执行的集合，该集合称为存储过程。
创建存储过程
使用命令 CREATE PROCEDURE 存储名字()
BEGIN
SQL语句1;
SQL语句2;
END
由于在写命令的会有;在碰到这个分隔符的时候，都会先执行分隔符之前的部分，所以需要在创建存储过程前修改分隔符
命令delimiter ,通常可以设置为delimiter //
mysql> delimiter //
mysql> CREATE PROCEDURE pr1()
-> BEGIN
-> SELECT * FROM tb1;
-> SELECT * FROM tb;
-> END
->
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> CALL pr1;
+-------+--------------+------+
| empid | name | age |
+-------+--------------+------+
| A101 | 主任佐藤 | 40 |
| A102 | 高教 | 28 |
| A103 | 中川 | 20 |
| A104 | 渡边 | 23 |
| A105 | 西哲 | 35 |
| NULL | 临时工 | 18 |
+-------+--------------+------+
6 rows in set (0.01 sec)
+--------------+-------+-------+
| empid | sales | month |
+--------------+-------+-------+
| a103 | 101 | 4 |
| a102 | 777 | 5 |
| a104 | 181 | 4 |
| a101 | 184 | 4 |
| a103 | 17 | 5 |
| a101 | 300 | 5 |
| a102 | 777 | 6 |
| a104 | 93 | 5 |
| a103 | 12 | 6 |
| a107 | 87 | 6 |
| 恶意插入 | 50 | NULL |
+--------------+-------+-------+
11 rows in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
上面的代码把创建与使用存储过程都介绍使用了。
创建只显示大于等于执行值的记录的存储过程。
CREATE PROCEDURE 存储过程名(变量名, 数据类型)
mysql> CREATE PROCEDURE pr2(d INT)
-> BEGIN
-> SELECT * FROM tb WHERE sales>=d;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> CALL pr2(200)//
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| a102 | 777 | 5 |
| a101 | 300 | 5 |
| a102 | 777 | 6 |
+-------+-------+-------+
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
书中讲了存储过程中的IN，OUT, INOUT
查了一些资料，写一些代码让自己了解下，顺便熟悉一下存储过程。
根据我查了资料与实际使用来看，IN的时候，你如果传入的是变量，会使用这个变量，但存储过程中，不能修改这个变量。
OUT的时候，存储变量不能读取这个参数，但存储过程中，可以修改这个变量。
INOUT，理解了上面两个意义，就可以更加容易理解这个了，这个就是可读可写的意思。
先写一个IN的
mysql> CREATE PROCEDURE intest(IN i INT) BEGIN SELECT i; SET i:=100; SELECT i; END// Query OK, 0 rows affected (0.00 sec) mysql> SET @i=1// Query OK, 0 rows affected (0.00 sec) mysql> SELECT @i// +------+ | @i | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> CALL intest(@i)// +------+ | i | +------+ | 1 | +------+ 1 row in set (0.00 sec) +------+ | i | +------+ | 100 | +------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> SELECT @i// +------+ | @i | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql>
IN 是属于默认的，向存储过程中传输变量，存储过程能够使用该变量，但不能修改该变量。
mysql> CREATE PROCEDURE outtest(OUT o INT) BEGIN SELECT o; SET o=99; SELECT o; END// Query OK, 0 rows affected (0.00 sec) mysql> SET @my_o :=1// Query OK, 0 rows affected (0.00 sec) mysql> SELECT @my_o// +-------+ | @my_o | +-------+ | 1 | +-------+ 1 row in set (0.00 sec) mysql> CALL outtest(@my_o)// +------+ | o | +------+ | NULL | +------+ 1 row in set (0.00 sec) +------+ | o | +------+ | 99 | +------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> SELECT @my_o// +-------+ | @my_o | +-------+ | 99 | +-------+ 1 row in set (0.00 sec) mysql>
从OUT的存储过程可以看出来，传进去的变量，存储过程不会读取，只会通过存储过程设定该变量。
mysql> CREATE PROCEDURE inouttest(INOUT io INT) BEGIN SELECT io; SET io :=000; SELECT io; END// Query OK, 0 rows affected (0.00 sec) mysql> SET @myio :=88// Query OK, 0 rows affected (0.00 sec) mysql> CALL inouttest(@myio)// +------+ | io | +------+ | 88 | +------+ 1 row in set (0.00 sec) +------+ | io | +------+ | 0 | +------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> SELECT @myio// +-------+ | @myio | +-------+ | 0 | +-------+ 1 row in set (0.00 sec) mysql>
INOUT，在存储过程里面既可以读取参数，也可以修改参数。我自己感觉一般IN与INOUT应该用的比较多。
显示与删除存储过程
mysql> SHOW CREATE PROCEDURE intest\G
*************************** 1. row ***************************
Procedure: intest
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `intest`(IN i INT)
BEGIN SELECT i; SET i:=100; SELECT i; END
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
mysql> DROP PROCEDURE intest//
Query OK, 0 rows affected (0.00 sec)
存储函数
使用存储函数，就真的跟Python的自定义函数一样了
首相要设置一下参数log_bin_trust_function_creators
mysql> SET GLOBAL log_bin_trust_function_creators := 1; Query OK, 0 rows affected (0.00 sec) mysql> SHOW VARIABLES LIKE "log_bin_trust_%"; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin_trust_function_creators | ON | +---------------------------------+-------+ 1 row in set (0.00 sec)
mysql> CREATE FUNCTION fu1(height INT) RETURNS DOUBLE
-> BEGIN
-> DECLARE res DOUBLE;
-> SET res :=(height*height*22/10000);
-> RETURN res;
-> END//
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT fu1(176)//
+----------+
| fu1(176) |
+----------+
| 68.1472 |
+----------+
1 row in set (0.00 sec)
DECLARE是在MySQL函数内部定义局部变量用的。变量的设定需要通过set xx :=xxx来设定
@xx 属于定义全局变量。
调用函数使用 SELECT func()
mysql> CREATE FUNCTION fu2() RETURNS DOUBLE BEGIN DECLARE r DOUBLE; SELECT AVG(sales) INTO r FROM tb; RETURN r; END// Query OK, 0 rows affected (0.00 sec) mysql> SELECT fu2()// +---------------+ | fu2() | +---------------+ | 234.454545454 | +---------------+ 1 row in set (0.00 sec) mysql>
书中第二个例子就使用了局部变量的声明。
显示与删除跟PROCEDURE是一样的
SHOW CREATE FUNCTION
DROP FUNCTION
触发器
触发器(trigger)是一种对表执行某操作后会促发执行其他命令的机制。
触发器被触发的时间有BEFORE与AFTER
列值有OLD.列名与NEW.列名
创建触发器的格式
CREATE TRIGGER 触发器名 BEFORE(或者AFTER) DELETE、INSERT、UPDATE
ON 表名 FOR EACH ROW
BEGIN
使用前或者使用后的列的处理，也就是真正执行的触发语句
END
mysql> CREATE TRIGGER tr1 BEFORE DELETE ON tb1 FOR EACH ROW
-> BEGIN
-> INSERT INTO tb1_from VALUES(OLD.empid,OLD.name,OLD.age);
-> END//
Query OK, 0 rows affected (0.01 sec)
通过SHOW TRIGGERS 可以查看触发器数量
DROP TRIGGER 删除触发器
作业
CREATE FUNCTION f_sales ( t INT ) RETURNS INT BEGIN
DECLARE
res INT;
SELECT
SUM( sales ) INTO res
FROM
tb
WHERE
MONTH = t;
RETURN res;
END //
第13章 数量使用事务
InnoDB是唯一一个支持事务的存储引擎
修改存储引擎
ALTER TABLE ENGINE=MyISAM;
我们将多个操作作为单个逻辑工作单元处理的功能称为事务(transaction).
将事务之后的处理结果反映到数据库中的操作称为提交(commit)，
不反应到数据库中而是恢复到原来状态的操作称为回滚(rollback)
在数据库的世界例，除非开启了事务处理，否则数据修改之后是无法恢复原状的。
使用事务
首先要确保你的表数据引擎是InnoDB，然后开启事务
mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec)
第一次测试可以用，后面不能用了
将AUTOCOMMIT关闭就可以了
mysql> SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@AUTOCOMMIT; +--------------+ | @@AUTOCOMMIT | +--------------+ | 0 | +--------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM tb; Empty set (0.00 sec) mysql> INSERT INTO tb(SELECT * FROM tb_copy); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM tb; +-------+-------+-------+ | empid | sales | month | +-------+-------+-------+ | a103 | 101 | 4 | | a102 | 54 | 5 | | a103 | 17 | 5 | | a104 | 93 | 5 | | a103 | 12 | 6 | | a107 | 87 | 6 | +-------+-------+-------+ 6 rows in set (0.00 sec) mysql> ROLLBACK; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM tb; +-------+-------+-------+ | empid | sales | month | +-------+-------+-------+ | a103 | 101 | 4 | | a102 | 54 | 5 | | a103 | 17 | 5 | | a104 | 93 | 5 | | a103 | 12 | 6 | | a107 | 87 | 6 | +-------+-------+-------+ 6 rows in set (0.00 sec) mysql> DELETE FROM tb; Query OK, 6 rows affected (0.00 sec) mysql> SELECT * FROM tb; Empty set (0.00 sec) mysql> ROLLBACK; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM tb; +-------+-------+-------+ | empid | sales | month | +-------+-------+-------+ | a103 | 101 | 4 | | a102 | 54 | 5 | | a103 | 17 | 5 | | a104 | 93 | 5 | | a103 | 12 | 6 | | a107 | 87 | 6 | +-------+-------+-------+ 6 rows in set (0.00 sec)
通过 SELECT @@AUTOCOMMIT查看信息
mysql> SELECT @@AUTOCOMMIT; +--------------+ | @@AUTOCOMMIT | +--------------+ | 0 | +--------------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE '%AUTOCOM%' ; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | OFF | +---------------+-------+ 1 row in set (0.00 sec) mysql>
通过SELECT @@查看系统变量。
事务对DROP以及ALTER TABLE是无效的
习题比较简单，告诉我们在没有提交事务的情况，修改内容会作废的。
第14章 使用文件进行交互
首先对my.cnf进行修改，添加secure_file_priv=''
重复MySQL，进入终端后，运行
mysql> SELECT @@GLOBAL .secure_file_priv; +----------------------------+ | @@GLOBAL .secure_file_priv | +----------------------------+ | | +----------------------------+ 1 row in set (0.00 sec) mysql>
出现空返回的情况，表示可以任何路径导入或者导出文件。如果指定为NULL，则不能导入或者导出问题件。
执行
LOAD DATA INFILE '文件名' INTO TABLE 表名 选项
选项有
FIELDS TERMINATED BY 分隔符 （默认是'\t':Tab）
LINES TERMINATED BY 换行符 （默认是'\n':换行）
IGNORE 最开始跳过的行 LINES （默认是0）
如果将一个文件写入数据库，可以执行
LOAD DATA INFILE '~/t.csv' INTO TABLE tb1 FIELDS TERMINATED BY ','
将数据导出文本文件
SELECT * INTO OUTFILE '文件名' 选择 FROM 表名
具体示例
SELECT * INTO OUTFILE '~/cc.csv' FIFLDS TERMINATED BY ',' FROM tb1;
从文件中读取并执行SQL命令
这个真的还是比较实用的
SOURCE 文件名
[sidian@VM_0_17_centos ~]$ more sql.txt use db1 SELECT * FROM tb; SELECT * FROM tb1; [sidian@VM_0_17_centos ~]$
mysql> SOURCE ~/sql.txt Database changed +-------+-------+-------+ | empid | sales | month | +-------+-------+-------+ | a103 | 101 | 4 | | a102 | 54 | 5 | | a103 | 17 | 5 | | a104 | 93 | 5 | | a103 | 12 | 6 | | a107 | 87 | 6 | +-------+-------+-------+ 6 rows in set (0.00 sec) Empty set (0.00 sec)
这样的话，以后可以再TXT文档调试好语句，然后在sql客户端运行。
通过命令提示符执行编写再文件中的SQL命令
通过mysql -rroot -p -e "执行的语句"
这个执行有点不好，就是把密码直接暴露再命令行模式下了。
将SQL的执行结果保存到文件中
可以通过>的重定向按钮
mysql -uroot -p >mylog.txt
进入终端后sql终端后，你的命令输入没有任何的屏幕反应，因为读输出到mysql.txt中去了
还有一种更加方便的是，通过前面学的-e输出的信息，直接导入到log里面
mysql -uroot -p -e"SOUREC ~/sql.txt" > log.txt
[sidian@VM_0_17_centos ~]$ rm mylog.txt [sidian@VM_0_17_centos ~]$ mysql -uroot -p -e"SOURCE ~/sql.txt" >mylog.txt Enter password: [sidian@VM_0_17_centos ~]$ cat mylog.txt empid sales month a103 101 4 a102 54 5 a103 17 5 a104 93 5 a103 12 6 a107 87 6 [sidian@VM_0_17_centos ~]$
还有一种方法通过 <导入执行命令,然后通过>导出信息
[sidian@VM_0_17_centos ~]$ mysql -uroot -p < sql.txt >t_log.txt Enter password: [sidian@VM_0_17_centos ~]$ cat t_log.txt empid sales month a103 101 4 a102 54 5 a103 17 5 a104 93 5 a103 12 6 a107 87 6 [sidian@VM_0_17_centos ~]$
最后一种方式就是sql命令行中输入tee 然后文件名，会再你运行的目录下新建改文件，并写入内容
通过输入tee "文件名"会在运行的目录下新建一个文件。
然后执行命令，会将输出输入到该文件。经过本人多次测试，只能输入最近的一次命令，不能进行追加输入，通过? 查寻找不到相关设置信息
notee关闭输出模式。
备份和恢复数据库
mysqldump将导出数据库中所有的建表语句以及插入语句，然后重定向到输出文件
mysqldump -uroot -p 数据库名 > 输出文件名
[sidian@VM_0_17_centos ~]$ mysqldump -uroot -p db1>db1_out_txt Enter password: [sidian@VM_0_17_centos ~]$ more db1_out_txt -- MySQL dump 10.13 Distrib 5.7.28, for Linux (x86_64) -- -- Host: localhost Database: db1 -- ------------------------------------------------------ -- Server version 5.7.28 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOT
如果失败了，可以在命令后面加上 --default-charset-set=utf8之列的字符编码选项
恢复转储文件
通过<输入命令，转储数据库
[sidian@VM_0_17_centos ~]$ mysqladmin -uroot -p CREATE db2 Enter password: [sidian@VM_0_17_centos ~]$ mysql db2 -uroot -p <db1_out_txt Enter password: [sidian@VM_0_17_centos ~]$ mysql db2 -uroot -p Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 22 Server version: 5.7.28 MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SHOW TABLES; +------------------+ | Tables_in_db2 | +------------------+ | t_date | | t_initial_serial | | t_name | | t_now |
首相通过mysqladmin创建数据库，然后通过<写入表与数据
如果在导入或者导出的时候，碰到字符集问题
可以在命令的最后面都+上 --default-charset-set='字符集'
锁表
LOCK TABLES 表名 锁类型
READ 只读锁，所有客户端只允许执行select。只读锁
READ LOCAL 对于InnoDB以外的存储引擎，加锁的客户仅能执行SELECT。本地只读锁
WRITE 没有加锁的客户端不能进行任何操作，拥有锁的客户端可以执行操作。
UNLOCK TABLES 解锁当前的所有表。
练习题
...
