MySQL的基础知识
MySQL的入门教程
https://www.w3cschool.cn/mysql/mysql-tutorial.html
MySQL的 安装、管理
https://www.w3cschool.cn/mysql/mysql-install.html
https://www.w3cschool.cn/mysql/mysql-administration.html
MySQL的连接
https://www.w3cschool.cn/mysql/mysql-connection.html
MySQL的数据类型
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。
数值类型
MySQL支持所有标准SQL数值数据类型。
这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。
关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。
BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。
作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。
类型
大小
范围（有符号）
范围（无符号）
用途
TINYINT
1 字节
(-128，127)
(0，255)
小整数值
SMALLINT
2 字节
(-32 768，32 767)
(0，65 535)
大整数值
MEDIUMINT
3 字节
(-8 388 608，8 388 607)
(0，16 777 215)
大整数值
INT或INTEGER
4 字节
(-2 147 483 648，2 147 483 647)
(0，4 294 967 295)
大整数值
BIGINT
8 字节
(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)
(0，18 446 744 073 709 551 615)
极大整数值
FLOAT
4 字节
(-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)
0，(1.175 494 351 E-38，3.402 823 466 E+38)
单精度
浮点数值
DOUBLE
8 字节
(1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
双精度
浮点数值
DECIMAL
对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2
依赖于M和D的值
依赖于M和D的值
小数值
日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性，将在后面描述。
类型
大小
(字节)
范围
格式
用途
DATE
3
1000-01-01/9999-12-31
YYYY-MM-DD
日期值
TIME
3
'-838:59:59'/'838:59:59'
HH:MM:SS
时间值或持续时间
YEAR
1
1901/2155
YYYY
年份值
DATETIME
8
1000-01-01 00:00:00/9999-12-31 23:59:59
YYYY-MM-DD HH:MM:SS
混合日期和时间值
TIMESTAMP
8
1970-01-01 00:00:00/2037 年某时
YYYYMMDD HHMMSS
混合日期和时间值，时间戳
字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
类型
大小
用途
CHAR
0-255字节
定长字符串
VARCHAR
0-65535 字节
变长字符串
TINYBLOB
0-255字节
不超过 255 个字符的二进制字符串
TINYTEXT
0-255字节
短文本字符串
BLOB
0-65 535字节
二进制形式的长文本数据
TEXT
0-65 535字节
长文本数据
MEDIUMBLOB
0-16 777 215字节
二进制形式的中等长度文本数据
MEDIUMTEXT
0-16 777 215字节
中等长度文本数据
LOGNGBLOB
0-4 294 967 295字节
二进制形式的极大文本数据
LONGTEXT
0-4 294 967 295字节
极大文本数据
CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。
有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。
MySQL的常用命令
管理MySQL的命令
以下列出了使用Mysql数据库过程中常用的命令：
-
**use 数据库名 :**选择要操作的Mysql数据库，使用该命令后所有Mysql命令都只针对该数据库。
-
show databases: 列出 MySQL 数据库管理系统的数据库列表。
-
show tables: 显示指定数据库的所有表，使用该命令前需要使用 use命令来选择要操作的数据库。
-
show columns from 数据表 or desc 数据表 : 显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息。
-
create database testdb charset "utf8": 创建一个叫testdb的数据库，且让其支持中文
-
drop database testdb: 删除数据库
-
**show index from 数据表:**显示数据表的详细索引信息，包括PRIMARY KEY（主键）
库操作命令
1、创建数据库
create database <数据库名>；
1create database students; #创建一个名为students数据库 2 3mysql> create database students; 4Query OK, 1 row affected (0.00 sec)
2、显示所有的数据库
show databases；
1mysql> show databases; 2+--------------------+ 3| Database | 4+--------------------+ 5| information_schema | 6| classdb | 7| mysql | 8| performance_schema | 9| students | 10| sys | 11+--------------------+ 126 rows in set (0.00 sec)
3、删除数据库
drop database <数据库名>；
1mysql> drop database students; 2Query OK, 0 rows affected (0.06 sec) 3 4mysql> show databases; 5+--------------------+ 6| Database | 7+--------------------+ 8| information_schema | 9| classdb | 10| mysql | 11| performance_schema | 12| sys | 13+--------------------+ 145 rows in set (0.00 sec)
4、连接数据库
use <数据库名>；
1mysql> use classdb; 2Reading table information for completion of table and column names 3You can turn off this feature to get a quicker startup with -A 4 5Database changed
5、查看当前使用的数据库
select database();
1mysql> select database(); 2+------------+ 3| database() | 4+------------+ 5| classdb | 6+------------+ 71 row in set (0.00 sec)
6、当前数据库包含的表信息：
show tables;
1mysql> show tables; 2+-------------------+ 3| Tables_in_classdb | 4+-------------------+ 5| a | 6| address | 7| addresses | 8| b | 9| class | 10| customer | 11| students | 12| user | 13+-------------------+ 148 rows in set (0.01 sec)
表操作命令
1.创建数据表
CREATE TABLE table_name (column_name column_type);
1mysql> create table student1 ( 2 -> id int not null auto_increment, #AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1 3 -> name char(32) not null, 4 -> age int not null, 5 -> date date, 6 -> address char(32), 7 -> primary key (id)); #PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。 8Query OK, 0 rows affected (0.04 sec) 9 #创建一个student1表 （id，name，age，date，address）
2.获取表结构
desc 数据表名； show columns from 数据表名；
1mysql> desc student1; 2+---------+----------+------+-----+---------+----------------+ 3| Field | Type | Null | Key | Default | Extra | 4+---------+----------+------+-----+---------+----------------+ 5| id | int(11) | NO | PRI | NULL | auto_increment | 6| name | char(32) | NO | | NULL | | 7| age | int(11) | NO | | NULL | | 8| date | date | YES | | NULL | | 9| address | char(32) | YES | | NULL | | 10+---------+----------+------+-----+---------+----------------+ 115 rows in set (0.01 sec) 12 13mysql> show columns from student1; 14+---------+----------+------+-----+---------+----------------+ 15| Field | Type | Null | Key | Default | Extra | 16+---------+----------+------+-----+---------+----------------+ 17| id | int(11) | NO | PRI | NULL | auto_increment | 18| name | char(32) | NO | | NULL | | 19| age | int(11) | NO | | NULL | | 20| date | date | YES | | NULL | | 21| address | char(32) | YES | | NULL | | 22+---------+----------+------+-----+---------+----------------+ 235 rows in set (0.00 sec)
3.删除表
drop table 数据表表名；
表数据操作命令
1.表插入数据
INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );
1mysql> insert into student1(name,age,date,address)values("jack",25,"2018-09-01","china") 2 -> ; 3Query OK, 1 row affected (0.04 sec) 4 5mysql> insert into student1(name,age,date,address)values("rain",24,"2018-09-01","english"); 6Query OK, 1 row affected (0.04 sec) 7 8mysql> insert into student1(name,age,date,address)values("lucy",22,"2018-09-02","USA"); 9Query OK, 1 row affected (0.01 sec)
2.删除表数据
DELETE FROM table_name [``WHERE Clause]
1mysql> delete from student1 where id=5; 2Query OK, 1 row affected (0.03 sec) 3 4mysql> select * from student1; 5+----+-------+-----+------------+---------+ 6| id | name | age | date | address | 7+----+-------+-----+------------+---------+ 8| 1 | jack | 25 | 2018-09-01 | china | 9| 2 | rain | 24 | 2018-09-01 | english | 10| 3 | lucy | 22 | 2018-09-02 | USA | 11| 4 | lili | 25 | 2018-09-03 | USA | 12| 6 | lisi | 26 | 2018-09-08 | China | 13| 7 | maliu | 21 | 2018-09-09 | China | 14+----+-------+-----+------------+---------+ 156 rows in set (0.00 sec)
3.查询表数据
SELECT column_name,column_name FROM table_name [ WHERE Clause] [OFFSET M ][LIMIT N]
-
查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
-
SELECT 命令可以读取一条或者多条记录。
-
你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
-
你可以使用 WHERE 语句来包含任何条件。
-
你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
-
你可以使用 LIMIT 属性来设定返回的记录数。
mysql> select * from student1; +----+------+-----+------------+---------+ | id | name | age | date | address | +----+------+-----+------------+---------+ | 1 | jack | 25 | 2018-09-01 | china | | 2 | rain | 24 | 2018-09-01 | english | | 3 | lucy | 22 | 2018-09-02 | USA | +----+------+-----+------------+---------+ 3 rows in set (0.01 sec)
mysql> mysql> select * from student1 limit 3 offset 2; +----+---------+-----+------------+---------+ | id | name | age | date | address | +----+---------+-----+------------+---------+ | 3 | lucy | 22 | 2018-09-02 | USA | | 4 | lili | 25 | 2018-09-03 | USA | | 5 | zhansan | 25 | 2018-09-08 | China | +----+---------+-----+------------+---------+ 3 rows in set (0.00 sec)
mysql> select * from student1 limit 3 ,1; +----+------+-----+------------+---------+ | id | name | age | date | address | +----+------+-----+------------+---------+ | 4 | lili | 25 | 2018-09-03 | USA | +----+------+-----+------------+---------+ 1 row in set (0.00 sec)
MySQL where 子句
SELECT field1, field2,...fieldN FROM table_name1, table_name2...[ WHERE condition1 [ AND [ OR ]] condition2.....
以下为操作符列表，可用于 WHERE 子句中。
下表中实例假定 A为10 B为20
操作符
描述
实例
=
等号，检测两个值是否相等，如果相等返回true
(A = B) 返回false。
<>, !=
不等于，检测两个值是否相等，如果不相等返回true
(A != B) 返回 true。
>
大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true
(A > B) 返回false。
<
小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true
(A < B) 返回 true。
>=
大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true
(A >= B) 返回false。
<=
小于等于号，检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true
(A <= B) 返回 true。
1mysql> select * from student1 where id >2; 2+----+---------+-----+------------+---------+ 3| id | name | age | date | address | 4+----+---------+-----+------------+---------+ 5| 3 | lucy | 22 | 2018-09-02 | USA | 6| 4 | lili | 25 | 2018-09-03 | USA | 7| 5 | zhansan | 25 | 2018-09-08 | China | 8| 6 | lisi | 26 | 2018-09-08 | China | 9| 7 | maliu | 21 | 2018-09-09 | China | 10+----+---------+-----+------------+---------+ 115 rows in set (0.03 sec) 12 13mysql> select * from student1 where address="usa"; 14+----+------+-----+------------+---------+ 15| id | name | age | date | address | 16+----+------+-----+------------+---------+ 17| 3 | lucy | 22 | 2018-09-02 | USA | 18| 4 | lili | 25 | 2018-09-03 | USA | 19+----+------+-----+------------+---------+ 202 rows in set (0.00 sec)
MySQL LIKE 子句
SELECT field1, field2,...fieldN table_name1, table_name2... WHERE field1 LIKE condition1 [ AND [ OR ]] filed2 = 'somevalue'
1mysql> select * from student1 where name like "%u"; 2+----+-------+-----+------------+---------+ 3| id | name | age | date | address | 4+----+-------+-----+------------+---------+ 5| 7 | maliu | 22 | 2018-09-09 | China | 6| 8 | maliu | 22 | 2018-09-09 | China | 7| 9 | maliu | 22 | 2018-09-09 | China | 8| 10 | maliu | 22 | 2018-09-09 | China | 9+----+-------+-----+------------+---------+ 104 rows in set (0.02 sec) 11 12mysql> select * from student1 where name like "l%"; 13+----+------+-----+------------+---------+ 14| id | name | age | date | address | 15+----+------+-----+------------+---------+ 16| 3 | lucy | 22 | 2018-09-02 | USA | 17| 4 | lili | 25 | 2018-09-03 | USA | 18| 6 | lisi | 22 | 2018-09-08 | China | 19+----+------+-----+------------+---------+ 203 rows in set (0.00 sec)
MySQL 排序
1SELECT field1, field2,...fieldN table_name1, table_name2... 2ORDER BY field1, [field2...] [ASC [DESC]] 3使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。 4 5mysql> select * from student1 order by age desc; 6+----+-------+-----+------------+---------+ 7| id | name | age | date | address | 8+----+-------+-----+------------+---------+ 9| 1 | jack | 25 | 2018-09-01 | China | 10| 4 | lili | 25 | 2018-09-03 | USA | 11| 2 | rain | 24 | 2018-09-01 | English | 12| 3 | lucy | 22 | 2018-09-02 | USA | 13| 6 | lisi | 22 | 2018-09-08 | China | 14| 7 | maliu | 22 | 2018-09-09 | China | 15| 8 | maliu | 22 | 2018-09-09 | China | 16| 9 | maliu | 22 | 2018-09-09 | China | 17| 10 | maliu | 22 | 2018-09-09 | China | 18+----+-------+-----+------------+---------+ 199 rows in set (0.03 sec) 20 21mysql> select * from student1 order by age asc; 22+----+-------+-----+------------+---------+ 23| id | name | age | date | address | 24+----+-------+-----+------------+---------+ 25| 3 | lucy | 22 | 2018-09-02 | USA | 26| 6 | lisi | 22 | 2018-09-08 | China | 27| 7 | maliu | 22 | 2018-09-09 | China | 28| 8 | maliu | 22 | 2018-09-09 | China | 29| 9 | maliu | 22 | 2018-09-09 | China | 30| 10 | maliu | 22 | 2018-09-09 | China | 31| 2 | rain | 24 | 2018-09-01 | English | 32| 1 | jack | 25 | 2018-09-01 | China | 33| 4 | lili | 25 | 2018-09-03 | USA | 34+----+-------+-----+------------+---------+ 359 rows in set (0.00 sec)
MySQL GROUP BY 语句
1SELECT column_name, function(column_name) 2FROM table_name 3WHERE column_name operator value 4GROUP BY column_name; 5 6mysql> SELECT * FROM employee_tbl; 7+----+--------+---------------------+--------+ 8| id | name | date | singin | 9+----+--------+---------------------+--------+ 10| 1 | 小明 | 2016-04-22 15:25:33 | 1 | 11| 2 | 小王 | 2016-04-20 15:25:47 | 3 | 12| 3 | 小丽 | 2016-04-19 15:26:02 | 2 | 13| 4 | 小王 | 2016-04-07 15:26:14 | 4 | 14| 5 | 小明 | 2016-04-11 15:26:40 | 4 | 15| 6 | 小明 | 2016-04-04 15:26:54 | 2 | 16+----+--------+---------------------+--------+ 17 18接下来我们使用 GROUP BY 语句 将数据表按名字进行分组，并统计每个人有多少条记录： 19mysql> SELECT name, COUNT(*) FROM employee_tbl GROUP BY name; 20+--------+----------+ 21| name | COUNT(*) | 22+--------+----------+ 23| 小丽 | 1 | 24| 小明 | 3 | 25| 小王 | 2 | 26+--------+----------+ 273 rows in set (0.01 sec) 28 29使用 WITH ROLLUP 30mysql> SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP; 31+--------+--------------+ 32| name | singin_count | 33+--------+--------------+ 34| 小丽 | 2 | 35| 小明 | 7 | 36| 小王 | 7 | 37| NULL | 16 | 38+--------+--------------+ 394 rows in set (0.00 sec) 40其中记录 NULL 表示所有人的登录次数。<br> 41我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法： 42mysql> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
4.修改表数据
MySQL UPDATE 查询
UPDATE table_name SET field1=new-value1, field2=new-value2 [ WHERE Clause]
1mysql> update student1 set age=22 where id >4; 2Query OK, 5 rows affected (0.02 sec) 3Rows matched: 5 Changed: 5 Warnings: 0 4 5mysql> select * from student1; 6+----+-------+-----+------------+---------+ 7| id | name | age | date | address | 8+----+-------+-----+------------+---------+ 9| 1 | jack | 25 | 2018-09-01 | china | 10| 2 | rain | 24 | 2018-09-01 | english | 11| 3 | lucy | 22 | 2018-09-02 | USA | 12| 4 | lili | 25 | 2018-09-03 | USA | 13| 6 | lisi | 22 | 2018-09-08 | China | 14| 7 | maliu | 22 | 2018-09-09 | China | 15| 8 | maliu | 22 | 2018-09-09 | China | 16| 9 | maliu | 22 | 2018-09-09 | China | 17| 10 | maliu | 22 | 2018-09-09 | China | 18+----+-------+-----+------------+---------+ 199 rows in set (0.01 sec) 20 21mysql> update student1 set address ="China" where id =1; 22Query OK, 1 row affected (0.00 sec) 23Rows matched: 1 Changed: 1 Warnings: 0 24 25mysql> select * from student1 ; 26+----+-------+-----+------------+---------+ 27| id | name | age | date | address | 28+----+-------+-----+------------+---------+ 29| 1 | jack | 25 | 2018-09-01 | China | 30| 2 | rain | 24 | 2018-09-01 | english | 31| 3 | lucy | 22 | 2018-09-02 | USA | 32| 4 | lili | 25 | 2018-09-03 | USA | 33| 6 | lisi | 22 | 2018-09-08 | China | 34| 7 | maliu | 22 | 2018-09-09 | China | 35| 8 | maliu | 22 | 2018-09-09 | China | 36| 9 | maliu | 22 | 2018-09-09 | China | 37| 10 | maliu | 22 | 2018-09-09 | China | 38+----+-------+-----+------------+---------+ 399 rows in set (0.00 sec) 40 41mysql> update student1 set address ="English" where id =2; 42Query OK, 1 row affected (0.00 sec) 43Rows matched: 1 Changed: 1 Warnings: 0 44 45mysql> select * from student1 ; 46+----+-------+-----+------------+---------+ 47| id | name | age | date | address | 48+----+-------+-----+------------+---------+ 49| 1 | jack | 25 | 2018-09-01 | China | 50| 2 | rain | 24 | 2018-09-01 | English | 51| 3 | lucy | 22 | 2018-09-02 | USA | 52| 4 | lili | 25 | 2018-09-03 | USA | 53| 6 | lisi | 22 | 2018-09-08 | China | 54| 7 | maliu | 22 | 2018-09-09 | China | 55| 8 | maliu | 22 | 2018-09-09 | China | 56| 9 | maliu | 22 | 2018-09-09 | China | 57| 10 | maliu | 22 | 2018-09-09 | China | 58+----+-------+-----+------------+---------+ 599 rows in set (0.00 sec)
MySQL ALTER命令
我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令。
删除，添加或修改表字段
1alter table student1 drop date; #从student1表删除date 字段 2alter table student1 add phone int(11) not null; ＃添加phone字段
修改字段类型及名称
如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。
例如，把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:
mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);
使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。尝试如下实例：
1mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT; 2 3mysql> ALTER TABLE testalter_tbl CHANGE j j INT;
ALTER TABLE 对 Null 值和默认值的影响
当你修改字段时，你可以指定是否包含只或者是否设置默认值。
以下实例，指定字段 j 为 NOT NULL 且默认值为100 。
1mysql> ALTER TABLE testalter_tbl 2 -> MODIFY j BIGINT NOT NULL DEFAULT 100;
修改表名
mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;
主键
外键，一个特殊的索引，用于关键2个表，只能是指定内容
1mysql> create table class( 2 -> id int not null primary key, 3 -> name char(16)); 4Query OK, 0 rows affected (0.02 sec) 5 6 7CREATE TABLE `student2` ( 8 `id` int(11) NOT NULL, 9 `name` char(16) NOT NULL, 10 `class_id` int(11) NOT NULL, 11 PRIMARY KEY (`id`), 12 KEY `fk_class_key` (`class_id`), 13 CONSTRAINT `fk_class_key` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`) 14) 15 16 17此时如果class 表中不存在id 1,student表也插入不了，这就叫外键约束 18mysql> insert into student2(id,name,class_id) values(1,'alex', 1); 19ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`testdb`.`student2`, CONSTRAINT `fk_class_key` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`)) 20 21 22 23mysql> insert into class(id,name) values(1,"linux"); 24Query OK, 1 row affected (0.01 sec) 25 26mysql> insert into student2(id,name,class_id) values(1,'alex', 1); 27Query OK, 1 row affected (0.00 sec) 28 29 30＃如果有student表中跟这个class表有关联的数据，你是不能删除class表中与其关联的纪录的 31mysql> delete from class where id =1; 32ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`testdb`.`student2`, CONSTRAINT `fk_class_key` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))
MySQL NULL 值处理
我们已经知道MySQL使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。
为了处理这种情况，MySQL提供了三大运算符:
IS NULL: 当列的值是NULL,此运算符返回true。
IS NOT NULL: 当列的值不为NULL, 运算符返回true。
<=>: 比较操作符（不同于=运算符），当比较的的两个值为NULL时返回true。
关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。
在MySQL中，NULL值与任何其它值的比较（即使是NULL）永远返回false，即 NULL = NULL 返回false 。
MySQL中处理NULL使用IS NULL和IS NOT NULL运算符。
Mysql 连接（left join, right join, inner join ,full join）
我们已经学会了如果在一张表中读取数据，这是相对简单的，但是在真正的应用中经常需要从多个数据表中读取数据。
本章节我们将向大家介绍如何使用 MySQL 的 JOIN 在两个或多个表中查询数据。
你可以在SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。
JOIN 按照功能大致分为如下三类：
-
INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
-
**LEFT JOIN（左连接）：**获取左表所有记录，即使右表没有对应匹配的记录。
-
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
A B
- - 1 3 2 4 3 5 4 6
Inner join
1select * from a INNER JOIN b on a.a = b.b; 2select a.*,b.* from a,b where a.a = b.b; 3 4a | b 5--+-- 63 | 3 74 | 4
Left join
1select * from a LEFT JOIN b on a.a = b.b; 2 3a | b 4--+----- 51 | null 62 | null 73 | 3 84 | 4
Right join
1select * from a RIGHT JOIN b on a.a = b.b; 2 3a | b 4-----+---- 53 | 3 64 | 4 7null | 5 8null | 6
Full join
1select * from a FULL JOIN b on a.a = b.b; 2 3 a | b 4-----+----- 5 1 | null 6 2 | null 7 3 | 3 8 4 | 4 9null | 6 10null | 5 11 12 13mysql 并不直接支持full join,but 总是难不到我们 14 15 16select * from a left join b on a.a = b.b UNION select * from a right join b on a.a = b.b; 17+------+------+ 18| a | b | 19+------+------+ 20| 3 | 3 | 21| 4 | 4 | 22| 1 | NULL | 23| 2 | NULL | 24| NULL | 5 | 25| NULL | 6 | 26+------+------+ 276 rows in set (0.00 sec)
事务
MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
- 在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
- 事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行
- 事务用来管理insert,update,delete语句
一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）
- 1、**事务的原子性：**一组事务，要么成功；要么撤回。
- 2、稳定性 ： 有非法数据（外键约束之类），事务撤回。
- 3、**隔离性：**事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
- 4、**可靠性：**软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。
在Mysql控制台使用事务来操作
1mysql> begin； ＃开始一个事务 2 3mysql> insert into a (a) values(555); 4 5mysql>rollback; 回滚 ， 这样数据是不会写入的
当然如果上面的数据没问题，就输入commit提交命令就行；
索引
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。
索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。
创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。
普通索引
创建索引
这是最基本的索引，它没有任何限制。它有以下几种创建方式：
CREATE INDEX indexName ON mytable(username(length));
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
修改表结构
ALTER mytable ADD INDEX [indexName] ON (username(length))
创建表的时候直接指定
1CREATE TABLE mytable( 2 3ID INT NOT NULL, 4 5username VARCHAR(16) NOT NULL, 6 7INDEX [indexName] (username(length)) 8 9);
删除索引的语法
DROP INDEX [indexName] ON mytable;
唯一索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
创建索引
1创建索引 2CREATE UNIQUE INDEX indexName ON mytable(username(length)) 3 4 5修改表结构 6ALTER mytable ADD UNIQUE [indexName] ON (username(length)) 7 8 9创建表的时候直接指定 10CREATE TABLE mytable( 11 12ID INT NOT NULL, 13 14username VARCHAR(16) NOT NULL, 15 16UNIQUE [indexName] (username(length)) 17 18);
使用ALTER 命令添加和删除索引
1有四种方式来添加数据表的索引： 2ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。 3ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。 4ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。 5ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。 6 7 8以下实例为在表中添加索引。 9mysql> ALTER TABLE testalter_tbl ADD INDEX (c); 10你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引: 11mysql> ALTER TABLE testalter_tbl DROP INDEX (c);
使用 ALTER 命令添加和删除主键
1主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下： 2mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL; 3mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i); 4 5你也可以使用 ALTER 命令删除主键： 6mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY; 7删除指定时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。
显示索引信息
mysql> SHOW INDEX FROM table_name\G
更多mysql知识，请看
http://www.cnblogs.com/wupeiqi/articles/5713323.html
http://www.yiibai.com/mysql/basic-mysql.html
http://www.cnblogs.com/alex3714/articles/5950372.html
python操作mysql
Python操作MySQL主要使用两种方式：
- 原生模块 pymsql
- ORM框架 SQLAchemy
pymysql
pymsql是Python中操作MySQL的模块，其使用方法和MySQLdb几乎相同。
下载安装
pip3 install pymysql
使用操作
1.执行mysql
1# !/usr/bin/env python 2# -*- coding:utf-8 -*- 3import pymysql 4 5# 创建连接 6conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='classdb') 7 8# 创建游标 9cursor = conn.cursor() 10 11# 执行SQL，并返回收影响行数 12effect_row = cursor.execute("update hosts set host = '1.1.1.2'") 13 14# 执行SQL，并返回受影响行数 15# effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,)) 16 17# 执行SQL，并返回受影响行数 18# effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)]) 19 20# 提交，不然无法保存新建或者修改的数据 21conn.commit() 22 23# 关闭游标 24cursor.close() 25 26# 关闭连接 27conn.close()
2、获取新创建数据自增ID
1#!/usr/bin/env python 2# -*- coding:utf-8 -*- 3import pymysql 4 5conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1') 6cursor = conn.cursor() 7cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)]) 8conn.commit() 9cursor.close() 10conn.close() 11 12# 获取最新自增ID 13new_id = cursor.lastrowid
3、获取查询数据
1#!/usr/bin/env python 2# -*- coding:utf-8 -*- 3import pymysql 4 5conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1') 6cursor = conn.cursor() 7cursor.execute("select * from hosts") 8 9# 获取第一行数据 10row_1 = cursor.fetchone() 11 12# 获取前n行数据 13# row_2 = cursor.fetchmany(3) 14# 获取所有数据 15# row_3 = cursor.fetchall() 16 17conn.commit() 18cursor.close() 19conn.close()
注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：
- cursor.scroll(1,mode='relative') # 相对当前位置移动
- cursor.scroll(2,mode='absolute') # 相对绝对位置移动
4、fetch数据类型
关于默认获取的数据是元祖类型，如果想要或者字典类型的数据，即：
1#!/usr/bin/env python 2# -*- coding:utf-8 -*- 3import pymysql 4 5conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1') 6 7# 游标设置为字典类型 8cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) 9r = cursor.execute("call p1()") 10 11result = cursor.fetchone() 12 13conn.commit() 14cursor.close() 15conn.close()