什么是数据库？
数据库是按照数据结构组织、存储和管理数据库的仓库。
每个数据库都有一个或多个不同的API用于创建、访问、管理、搜索和复制保存的数据。
我们使用关系型数据库管理系统来存储和管理大量的数据。所谓关系型数据库，是建立在关系模型上的数据库，借助集合代数等数学概念和方法来处理数据库中的数据。
RDBMS（关系型数据库管理系统）的特点：
- 数据以表格的形式出现
- 每行为各种记录名称
- 每列记录名称所对应的数据域
- 许多的行和列组成一张表单
- 若干的表单组成database
RDBMS术语
- 数据库：数据库是一些关联表的集合。
- 数据表：表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
- 列：一列（数据元素）包含了相同的数据。
- 行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
- 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
- 主键：主键是唯一的。一个数据中只能包含一个主键，可以使用主键来查询数据。
- 外键：外键用于关联两个表。
- 复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
- 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
- 参照完整性：参照完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。
管理MySQL的命令
- use database_name;//选中要操作的数据库
- show databases;//列出Mysql数据库管理系统的数据库列表
- show tables;//显示指定数据库的所有表，使用该命令前需要使用use命令来选中要操作的数据库。
- show columns from table_name;//显示表的属性，属性类型，主键信息。是否为NULL，默认值等其他信息。
- show index from table_name;//显示数据表的详细索引信息，包括主键。
- show table status from database;//显示数据库中所有表信息。
- show table status from database like 'runnoob%';//# 表名以runoob开头的表的信息
- show table status from database like 'runnoob%'\G;#加上\G，查询结果按列打印
MySqL数据库操作
- creat database database_name;
- drop database database_name;
MySQL数据类型
数值、日期/时间、字符串（字符）类型。
- TINYINT 1byte -128~127 0~255 小整数
- SMALLINT 2byte -32768~32767 0~65535 大整数
- MEDIUMINT 3 字节 (-8 388 608，8 388 607) (0，16 777 215) 大整数值
- INT(INTEGER) 4byte -2147483648~2147483647 0~4294967295 大整数
- BIGINT 8 字节 (-9,223,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的值 小数值
时间/时间类型
- DATE 3byte 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
- TIME 3byte HH:MM:SS 时间值或持续时间
- YEAR 1byte 1901/2155 YYYY 年份值
- DATETIME 8byte 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
- TIMESTAMP 4byte 1970-01-01 00:00:00/2038(结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07) YYYYMMDD HHMMSS 混合日期和时间值，时间戳
字符串类型
- CHAR(定长字符串) VARCHAR(变长字符串) TINYBLOB(不超过255个字符的二进制字符串) TINYTEXT(短文本字符串)等等！
数据类型的属性
- NULL 数据列可包含NULL值
- NOT NULL 数据列不允许包含NULL值
- DEFAULT 默认值
- PRIMARY KEY 主键
- AUTO_INCREMENT 自动递增，适用于整数类型
- UNSIGNED 无符号
- CHARACTERSET 指定一个字符集
mysql表操作
- 创建 creat table table_name(column_name column_type);
- 实例：
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
`runoob_id` INT UNSIGNED AUTO_INCREMENT,
`runoob_title` VARCHAR(100) NOT NULL,
`runoob_author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `runoob_id` ) #设置主键
)ENGINE=InnoDB DEFAULT CHARSET=utf8; #ENGINE:设置存储引擎，CHARSET：设置编码
- 删除数据表 drop table table_name;
- 删除表内数据，是使用delete. delete from table_name where T_name="zhangsan";
- 清除表内数据，保留表结构，用truncate. truncate table table_name;
- 插入数据
insert into table_name (field1, field2,...fieldN)
VALUES
(value1, value2,... valueN) #如果数据是字符型，必须使用单引号或者双引号，如："value"。
- 实例：
INSERT INTO table_name (field1, field2,...fieldN)
VALUES
(valueA1,valueA2,...valueAN),(valueB1,valueB2,...valueBN),(valueC1,valueC2,...valueCN)......;
- 查询数据 select
select column_name,colunm_name
from table_name
[where clause]
[limt n] [offset m]
- 注：查询语句中可以使用一个或者多个表，表之间使用逗号（，）分割，并使用where语句来设定查询条件。
- SELECT命令可以读取一条或者多条记录。
- 可以使用星号（*）来替代其他字段，SELECT语句会返回表的所有字段数据
- 你可以使用 WHERE 语句来包含任何条件。
- 你可以使用 LIMIT 属性来设定返回的记录数。
- 你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
- 实例：
select * from table; #返回表中所有记录
MySQL where 子句，有条件地从表中选取数据，可将where字句添加到select语句中
- 语法：
select field1, field2, ... fieldN from table_name1, table_name2...
[where condition1 [AND [OR]] condition2...
- 注：
- 可以查询多个表，表间用,分割，并使用where语句来设定查询条件
- 使用AND或OR指定一个或多个条件
- where子句也可以运用在delete或update命令
- WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。
- 实例：
select * from table_name where BINARY cloumn='runoob.com';
# BINARY用于区分大小写
UPDATE 更新
- 语法：
update table_name SET field1=new-value1,field2 = new-value2
[where Clause]
- 注：
你可以同时更新一个或多个字段。
你可以在 WHERE 子句中指定任何条件。
你可以在一个单独表中同时更新数据。
- 实例：
update runoob_tbl set runoob_title='学习 C++' where runood_id=3;
select * from runoob_tbl where runoob_id=3;
return :
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
| 3 | 学习 C++ | RUNOOB.COM | 2016-05-06 |
Delete 语句
- delete from table_name [where Clause]
- delete 语句用于删除表中的数据, 基本用法为:
- delete from 表名称 where 删除条件;
- 以下是在表 students 中的实例:
删除 id 为 3 的行: delete from students where id=3;
删除所有年龄小于 21 岁的数据: delete from students where age<20;
删除表中的所有数据: delete from students;
- delete, drop, truncate都有删除表的作用，区别在于：
- delete 和 truncate 仅仅删除表数据，drop 连表数据和表结构一起删除，打个比方，delete 是单杀，truncate 是团灭，drop 是把电脑摔了。
- delete 是 DML 语句，操作完以后如果没有不想提交事务还可以回滚，truncate 和 drop 是 DDL 语句，操作完马上生效，不能回滚，打个比方，delete 是发微信说分手，后悔还可以撤回，truncate 和 drop 是直接扇耳光说滚，不能反悔。
- 执行的速度上，drop>truncate>delete，打个比方，drop 是神舟火箭，truncate 是和谐号动车，delete 是自行车。
LIKE 子句
- 使用百分号%字符来表示任意字符，类似于UNIX或者正则表达式中的星号*。
- 如果没有使用百分号%，LIKE子句与等号=的效果是一样的。
- 实例：
select * from runoob_tbl where runoob_author like '%COM';
return:
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 3 | 学习 Java | RUNOOB.COM | 2015-05-01 |
| 4 | 学习 Python | RUNOOB.COM | 2016-03-06 |
+-----------+---------------+---------------+-----------------+
- like 匹配/模糊匹配，会与% 和_结合使用
'%a' //以a结尾的数据
'a%' //以a开头的数据
'%a%' //含有a的数据
'_a_' //三位且中间字母是a的
'_a' //两位且结尾字母是a的
'a_' //两位且开头字母是a的
查询以 java 字段开头的信息。
SELECT * FROM position WHERE name LIKE 'java%';
查询包含 java 字段的信息。
SELECT * FROM position WHERE name LIKE '%java%';
查询以 java 字段结尾的信息。
SELECT * FROM position WHERE name LIKE '%java';
- 在where like 的条件查询中，SQL提供了四种匹配方式
%：表示任意 0 个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。
_：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。
[]：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。
[^] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。
查询内容包含通配符时,由于通配符的缘故，导致我们查询特殊字符 “%”、“_”、“[” 的语句无法正常实现，而把特殊字符用 “[ ]” 括起便可正常查询。
MySQL UNION操作符
- 描述：
- 用于连接两个以上的select语句的结果组合到一个结果集合中。多个select语句会删除重复的数据。
- 语法：
SELECT expression1, expression2, ... expression_n //要检索的列
FROM tables //要检索的数据表
[WHERE conditions] //可选，检索条件
UNION [ALL | DISTINCT] //DISTINCT：可选，删除结果中重复的数据。 ALL: 可选，返回所有结果集，包含重复数据。
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
- 实例：
select * from websites;
+----+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+---------------------------+-------+---------+
select * from apps;
+----+------------+-------------------------+---------+
| id | app_name | url | country |
+----+------------+-------------------------+---------+
| 1 | QQ APP | http://im.qq.com/ | CN |
| 2 | 微博 APP | http://weibo.com/ | CN |
| 3 | 淘宝 APP | https://www.taobao.com/ | CN |
+----+------------+-------------------------+---------+
3 rows in set (0.00 sec)
select country from websites
union
select country from apps
order by country;
+--------+
|country |
+--------+
|CN |
|IND |
|USA |
+--------+
select country from websites
union ALL
select country from apps
order by country;
注：将显示所有国家(重复)
select country, name from websites
where country='CN'
UNION ALL
SELECT country, app_name from apps
where country='CN'
order by country;
+-------+------+
|country|name |
+-------+------+
|CN |淘宝 |
|... |... |
+-------+------+
排序 order by
- select * from table_name order by column_name ASC;//升序
- select * from table_name order by column_name DESC;//降序
- 注：
如果字符集采用的是 gbk(汉字编码字符集)，直接在查询语句后边添加 ORDER BY：
SELECT *
FROM runoob_tbl
ORDER BY runoob_title;
如果字符集采用的是 utf8(万国码)，需要先对字段进行转码然后排序：
SELECT *
FROM runoob_tbl
ORDER BY CONVERT(runoob_title using gbk);
Group by 根据一个或多个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
- with rollup 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）
Mysql 连接的使用，作用从多张数据表中读取数据 JOIN
- 当然，可以在select\update和delete语句中使用mysql的join来联合多表查询
- 分类
- inner join(内连接，或等值连接)：获取两个表中字段匹配关系的记录。
- left join(左连接)：获取左表所有记录，即使右表没有对应匹配的记录。
- right join(右连接)：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
- 实例：
use RUNOOB;
select * from tcount_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鸟教程 | 10 |
| RUNOOB.COM | 20 |
| Google | 22 |
+---------------+--------------+
3 rows in set (0.01 sec)
select * from runoob_tbl;
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 1 | 学习 PHP | 菜鸟教程 | 2017-04-12 |
| 2 | 学习 MySQL | 菜鸟教程 | 2017-04-12 |
| 3 | 学习 Java | RUNOOB.COM | 2015-05-01 |
| 4 | 学习 Python | RUNOOB.COM | 2016-03-06 |
| 5 | 学习 C | FK | 2017-04-05 |
+-----------+---------------+---------------+-----------------+
5 rows in set (0.01 sec)
//使用inner join 来连接这两种表中所有的runoob_auther字段对应在tcount_tbl表对应的runoob_count字段：
select a.runoob_id, a.runoob_author, b.runoob_count from runoob_tbl a inner join tcount_tbl b ON a.runoob_author = b.runoob_auther;
+-------------+-----------------+----------------+
| a.runoob_id | a.runoob_author | b.runoob_count |
+-------------+-----------------+----------------+
| 1 | 菜鸟教程 | 10 |
| 2 | 菜鸟教程 | 10 |
| 3 | RUNOOB.COM | 20 |
| 4 | RUNOOB.COM | 20 |
+-------------+-----------------+----------------+
4 rows in set (0.00 sec)
//以上生气了语句等价于：
select a.runoob_id, a.runoob_author, b.runoob_count from runoob_tbl a, tcount_tbl b where a.runoob_author = b.runoob_author;
+-------------+-----------------+----------------+
| a.runoob_id | a.runoob_author | b.runoob_count |
+-------------+-----------------+----------------+
| 1 | 菜鸟教程 | 10 |
| 2 | 菜鸟教程 | 10 |
| 3 | RUNOOB.COM | 20 |
| 4 | RUNOOB.COM | 20 |
+-------------+-----------------+----------------+
inner join : 取两个表的交集
Mysql left join
- 与join不同。left join 会读取左边数据表的全部数据，即使右边表无对应数据。
- 实例：(以runoob_tbl为左表，tcount_tbl为右表)
select a.runoob_id, a.runoob_author, b.runoob_count from runoob_tbl a left join tcount_tbl b on a.runoob_author = b.runoob_author;
+-------------+-----------------+----------------+
| a.runoob_id | a.runoob_author | b.runoob_count |
+-------------+-----------------+----------------+
| 1 | 菜鸟教程 | 10 |
| 2 | 菜鸟教程 | 10 |
| 3 | RUNOOB.COM | 20 |
| 4 | RUNOOB.COM | 20 |
| 5 | FK | NULL |
+-------------+-----------------+----------------+
- 以上实例使用了left join，该语句会读取左边的数据表 runoob_tbl的所有选取的字段，即便右侧表tcount_tbl中没有对应的runoob_author字段。
mysql right join会读取右侧数据表的全部数据，即便左侧表无对应数据。
- 实例：
a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
+-------------+-----------------+----------------+
| a.runoob_id | a.runoob_author | b.runoob_count |
+-------------+-----------------+----------------+
| 1 | 菜鸟教程 | 10 |
| 2 | 菜鸟教程 | 10 |
| 3 | RUNOOB.COM | 20 |
| 4 | RUNOOB.COM | 20 |
| NULL | NULL | 22 |
+-------------+-----------------+----------------+
- 以上实例中使用了 RIGHT JOIN，该语句会读取右边的数据表 tcount_tbl 的所有选取的字段数据，即便在左侧表 runoob_tbl 中没有对应的runoob_author 字段值。
mysql null
- 当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 运算符。
select * , columnName1+ifnull(columnName2,0) from tableName;
//columnName1，columnName2 为 int 型，当 columnName2 中，有值为 null 时，columnName1+columnName2=null，
ifnull(columnName2,0) 把 columnName2 中 null 值转为 0。⚠️
mysql 正则表达式
- mysql可以通过LIKE...%来进行模糊匹配，mysql同样支持其他正则表达式的匹配，mysql中使用REGEXP操作符进行正则表达式匹配。
模式 | 描述
^ | 匹配输入字符串的开始位置。如果设置了RegExp对象的Multiline属性，^也匹配'\n'或'\r'之后的位置。
$ | 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。
. | 匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用象 '[.\n]' 的模式。
[...] | 字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。
[^...} | 负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。
p1|p2|p3 | 匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。
* | 匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}.
+ | 匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。
{n} | n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。
{n,m} | m和n均为非负整数，其中你<=m.最少匹配n次且最多匹配m次。
- 实例：
//查找name字段中以‘st’为开头的所有数据
select name from person_tbl where name REGEXP '^st';
//查找name字段以‘OK’为结尾的所有数据
select name from person_tbl where name REGEXP 'ok$';
//查找name字段中包含'mar'字符串的所有数据：
SELECT name FROM person_tbl WHERE name REGEXP 'mar';
//查找name字段中以元音字符开头或以‘ok’字符结尾的所有数据：
select name from person_tbl where name REGEXP '^[aeiou]|ok$';
mysql 事务
- 主要用于处理操作量大、复杂度高的数据。比如说，在人员管理系统中，删除一个人，你需要删除人员的基本资料，也需要删除和该人员有关的信息，如信箱、文章等，如此这些数据库操作语句就构成一个事务！
- 只有使用innodb数据引擎的数据库或表才支持事务！
- 事务处理可以用来维护数据库的完整性，保证成批的sql语句要么全部执行，要么不执行。
- 事务用来管理insert、update、delete语句。
- 事务要满足4个条件（ACID）：原子性（Atomicity，或称为不可分割性）、一致性（consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
- 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
- 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据可以自发性地完成预定的工作。
- 隔离性:数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
- 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。
- 事务控制语句：
BEGIN 或 START TRANSACTION 显式地开启一个事务；
COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier 把事务回滚到标记点；
SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
- 事务处理主要有两种方法：
- 1.用begin（开始一个事务）, rollback（事务回滚）, commit（事务确认）来实现
- 2.直接用set来改变mysql的自动提交模式：
- set autocommit=0 禁止自动提交
- set autocommit=1 开启自动提交
- 实例：(事务测试)
use RUNOOB;
_> database changed
create table runoob_transaction_test(id int(5)) engine=innodb;
_> Query OK, 0 rows affected (0.04sec)
select * from runoob_trancaction_test;
_> Empty set
begin #开始事务
insert into runoob_transaction_test value(5);
insert into runoob_transaction_test value(6);
commit #提交事务
select * from runoob_trancaction_test;
_>
+------+
| id |
+------+
| 5 |
| 6 |
+------+
begin; #开始事务
insert into runoob_transaction_test values(7);
rollback; #回滚
select * from runoob_transaction_test; #因为回滚所以数据没有插入
_>
+------+
| id |
+------+
| 5 |
| 6 |
+------+
# 使用保留点savepoint
# savepoint 是在数据库事务处理中实现“子事务”（subtransaction），也称为嵌套事务的方法。
# 事务可以回滚到 savepoint 而不影响 savepoint 创建前的变化, 不需要放弃整个事务。
savepoint savepoint_name;//声明一个savepoint
rollback to savepoint; //回滚到savepoint
# 删除 savepoint
# 保留点再事务处理完成（执行一条rollback或commit）后自动释放。
release savepoint savepoint_name;//删除指定保留点
mysql alter命令
- 当我们需要修改数据表名或者数据表字段时，需要使用alter命令
- 首先创建一张表：
create table testalter_tbl
->(
->i INT,
->c CHAR(1)
->);
show columns from testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i | int(11) | YES | | NULL | |
| c | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
删除，添加或修改字段
# 删除，添加或修改字段
alter table testalter_tbl drop i;
# 当数据表中剩余一个字段则无法使用drop来删除字段
# 使用add子句向数据表中添加列，如下实例：
alter tbale testalter_tbl add i INT;
show columns from testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c | char(1) | YES | | NULL | |
| i | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
- 如果你需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。尝试以下 ALTER TABLE 语句, 在执行成功后，使用 SHOW COLUMNS 查看表结构的变化：
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;
- FIRST 和 AFTER 关键字可用于 ADD 与 MODIFY 子句，所以如果你想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。
修改字段类型及名称
- 若需要修改字段类型及名称，你可以在alter命令中使用modify或change子句。
- 实例：
# 把字段 c 的类型从 CHAR(1) 改为 CHAR(10)
alter table testalter_tbl modify c char(10);
# 使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型
alter table testalter_tbl change i j BIGINT;
alter table testalter_tbl change j j INT;
alter table对NULL值和默认值的影响
- 当你修改字段时，你可以指定是否包含值或是否设置默认值
- 实例：
# 段 j 为 NOT NULL 且默认值为100 ,你不设置默认值，mysql会设置该字段为NULL。
alter table testalter_table modify j BIGINT NOT NULL DEFAULT 100;
# 修改字段默认值
alter table testalter_tbl alter i set defult 1000;
show columns from testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c | char(1) | YES | | NULL | |
| i | int(11) | YES | | 1000 | |
+-------+---------+------+-----+---------+-------+
# 也可以使用alter命令及drop子句来删除字段的默认值
alter table testalter_tbl alter i drop defult;
show columns from testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c | char(1) | YES | | NULL | |
| i | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
# 修改数据表类型，可以使用alter命令及type子句来完成。
changing a table Type:
ALTER TABLE testalter_tbl ENGINE = MYISAM;
mysql> SHOW TABLE STATUS LIKE 'testalter_tbl'\G
_>
*************************** 1. row ****************
Name: testalter_tbl
Type: MyISAM
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 25769803775
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2007-06-03 08:04:36
Update_time: 2007-06-03 08:04:36
Check_time: NULL
Create_options:
Comment:
修改表名
- 若需要修改数据表名称，可以在alter table语句中使用rename子句来完成。
- 实例：
alter table testalter_tbl rename to alter_tbl;
- ALTER 命令还可以用来创建及删除MySQL数据表的索引，该功能我们会在接下来的章节中介绍。
mysql 索引
- mysql索引的建立对于mysql的高效运行很重要，索引可以提高mysql的检索速度。
- 索引分单列索引和组合索引
- 单列索引：即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
- 组合索引：即一个索引包含多个列。
- 创建索引时，需要确保该索引是应用在sql查询语句的条件（一般作为where子句的条件）
- 实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
- 优缺点：
- 优点：大大提升查询速度
- 缺点：降低了更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，mysql不仅要保存数据，还要保存一下索引文件。若过多使用，会造成滥用。
- 建立索引会占用磁盘空间的索引文件。
普通索引
- 最基本的索引，没有任何限制，创建方式：
- creat index indexname on mytable(length);
- 如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
修改表结构（添加索引）
- alter table tablename add index indexname(columnname);
创建表的时候直接指定
create table mytable(
id INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexname] (username(length))
);
删除索引的语法
- drop index [indexname] on mytable;
唯一索引
- 与普通索引类似，不同的是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它一下几种创建方式：
创建索引
- create unique index indexname on mytable(username(length));
修改表结构
- alter table mytable add unique indexname;
创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
);
使用alter命令添加和删除索引
- ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
- ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
- ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
- ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。
使用alter命令添加和删除主键
- 主键只能作用在一个列上，添加主键索引时，需要确保主键默认不为空（NOT NULL）
- 实例：
alter table testalter_tbl modify i INT NOT NULL;
alter table testalter_tbl add primary key (i);
- 可以使用 ALTER 命令删除主键：
- ALTER TABLE testalter_tbl DROP PRIMARY KEY;
- 删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。
- 显示索引信息
- show index from table_name; \G
mysql 临时表
- MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。
- 如果使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然你也可以手动销毁。
- 实例：（以下展示了使用Mysql临时表的简单实例）
CREATE TEMPORARY TABLE SalesSummary ( //关键字temporary
-> product_name VARCHAR(50) NOT NULL
-> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
-> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
-> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
INSERT INTO SalesSummary
-> (product_name, total_sales, avg_unit_price, total_units_sold)
-> VALUES
-> ('cucumber', 100.25, 90, 2);
select * from SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber | 100.25 | 90.00 | 2 |
+--------------+-------------+----------------+------------------+
- 当你使用show tables命令显示数据表时，你将无法看到SalesSummary表。
- 如果你退出当前MySQL会话，再使用 SELECT命令来读取原先创建的临时表数据，那你会发现数据库中没有该表的存在，因为在你退出时该临时表已经被销毁了。
- 删除临时表
- drop table SalesSummary;
MySQL 复制表
- 若需要完全复制MySQL的数据表，包括表的结构，索引，默认值等，仅仅使用create table ... select命令，时无法实现的。
- 复制表的步骤：
- 使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。
- 复制以下命令显示的SQL语句，修改数据表名，并执行SQL语句，通过以上命令 将完全的复制数据表结构。
- 如果你想复制表的内容，你就可以使用 INSERT INTO ... SELECT 语句来实现。
- 实例：
# 1.获取数据表的完整结构
show create table runoob_tbl \G;
_>
*************************** 1. row ***************************
Table: runoob_tbl
Create Table: CREATE TABLE `runoob_tbl` (
`runoob_id` int(11) NOT NULL auto_increment,
`runoob_title` varchar(100) NOT NULL default '',
`runoob_author` varchar(40) NOT NULL default '',
`submission_date` date default NULL,
PRIMARY KEY (`runoob_id`),
UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)
) ENGINE=InnoDB
1 row in set (0.00 sec)
ERROR:
No query specified
# 2.修改sql语句的数据表名，并执行sql语句。
create table 'clone_tbl'(
-> `runoob_id` int(11) NOT NULL auto_increment,
-> `runoob_title` varchar(100) NOT NULL default '',
-> `runoob_author` varchar(40) NOT NULL default '',
-> `submission_date` date default NULL,
-> PRIMARY KEY (`runoob_id`),
-> UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (1.80 sec)
# 3.若要copy数据，可以使用insert into ... select语句来实现。
INSERT INTO clone_tbl (runoob_id,
-> runoob_title,
-> runoob_author,
-> submission_date)
-> SELECT runoob_id,runoob_title,
-> runoob_author,submission_date
-> FROM runoob_tbl;
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0
其他复制表的操作
CREATE TABLE targetTable LIKE sourceTable;
INSERT INTO targetTable SELECT * FROM sourceTable;
来给大家区分下mysql复制表的两种方式。
- 第一、只复制表结构到新表
- create table 新表 select * from 旧表 where 1=2
- 或者
- create table 新表 like 旧表
- 第二、复制表结构及数据到新表
- create table新表 select * from 旧表
Mysql 元数据
- 数据库和数据表的信息
- 查询结果信息
- 获取服务器元数据
- 详细介绍
MySql 序列使用
- MySQL 序列是一组整数：1, 2, 3, ...，由于一张数据表只能有一个字段自增主键， 如果你想实现其他字段也实现自动增加，就可以使用MySQL序列来实现。
- 使用 auto_increment
- 实例：
CREATE TABLE insect
-> (
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (id),
-> name VARCHAR(30) NOT NULL, # type of insect
-> date DATE NOT NULL, # date collected
-> origin VARCHAR(30) NOT NULL # where collected
);
Query OK, 0 rows affected (0.02 sec)
- 获取auto_increment值
- 在MySQL的客户端中你可以使用 SQL中的LAST_INSERT_ID( ) 函数来获取最后的插入表中的自增列的值。
重置序列
- 如果你删除了数据表中的多条记录，并希望对剩下数据的AUTO_INCREMENT列进行重新排列，那么你可以通过删除自增的列，然后重新添加来实现。 不过该操作要非常小心，如果在删除的同时又有新记录添加，有可能会出现数据混乱。
MySQL 处理重复数据
防止表中出现重复数据
- 可以在数据表中设置指定的字段为primary key（主键）或者unique（唯一）索引来保证数据的唯一性。
- 实例：
# 下表无索引及主键，所以该表允许出现多条重复记录
CREATE TABLE person_tbl
(
first_name CHAR(20),
last_name CHAR(20),
sex CHAR(10)
);
# 如果你想设置表中字段 first_name，last_name 数据不能重复，你可以设置双主键模式来设置数据的唯一性， 如果你设置了双主键，那么那个键的默认值不能为 NULL，可设置为 NOT NULL。
create table person_tbl(
first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
sex CHAR(10),
primary key (last_name, first_name)
);
# 如果我们设置了唯一索引，那么在插入重复数据时，SQL 语句将无法执行成功,并抛出错。
# INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE 会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。
# 以下实例使用了 INSERT IGNORE INTO，执行后不会出错，也不会向数据表中插入重复数据：
INSERT IGNORE INTO person_tbl (last_name, first_name)
-> VALUES( 'Jay', 'Thomas');
Query OK, 1 row affected (0.00 sec)
INSERT IGNORE INTO person_tbl (last_name, first_name)
-> VALUES( 'Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)
# INSERT IGNORE INTO 当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。 而 REPLACE INTO 如果存在 primary 或 unique 相同的记录，则先删除掉。再插入新记录。
# 添加unique索引确保数据唯一性；
CREATE TABLE person_tbl
(
first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
sex CHAR(10),
UNIQUE (last_name, first_name)
);
统计重复数据
SELECT COUNT(*) as repetitions, last_name, first_name
-> FROM person_tbl
-> GROUP BY last_name, first_name
-> HAVING repetitions > 1;
过滤重复数据
- 如果你需要读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。
mysql> SELECT DISTINCT last_name, first_name
-> FROM person_tbl;
- 你也可以使用 GROUP BY 来读取数据表中不重复的数据：
mysql> SELECT last_name, first_name
-> FROM person_tbl
-> GROUP BY (last_name, first_name);
删除重复数据
create table tmp select lat_name, first_name, sex from person_tbl group by (last_name, first_name, sex);
drop table person_tbl;
alter table tmp rename to person
- 或者
# 当然你也可以在数据表中添加 INDEX（索引） 和 PRIMAY KEY（主键）这种简单的方法来删除表中的重复记录。方法如下：
mysql> ALTER IGNORE TABLE person_tbl
-> ADD PRIMARY KEY (last_name, first_name);
MySQL 及SQL注入
- 通过网页获取用户输入的数据并将其插入一个MySQL数据库，那么就有可能发生SQL注入安全的问题。
- 所谓SQL注入，就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。
- 我们永远不要信任用户的输入，我们必须认定用户输入的数据都是不安全的，我们都需要对用户输入的数据进行过滤处理。 SQL注入
MySQL 导出数据
- 使用select ... into outfile语句导出数据
select * from runoob_tbl into OUTFILE 'tmp/runob.txt';
# 可以通过命令选项来设置数据输出的指定格式。
# 导出为csv格式
select * from passwd into outfile '/tmp/runoob.txt'
-> fields terminated by ',' enclosed by ""
-> lines terminated by '\r\n';
# 生成一个文件，各值用逗号隔开。这种格式可以被许多程序使用。
SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
-> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
-> LINES TERMINATED BY '\n'
-> FROM test_table;
- 导出sql格式的数据
# 导出数据表
$ mysqldump -u root -p RUNOOB runoob_tbl > dump.txt
password ******
# 导出整个数据库的数据
$ mysqldump -u root -p RUNOOB > database_dump.txt
password ******
# 备份所有数据库
$ mysqldump -u root -p --all-databases > database_dump.txt
password ******
mysql 导入数据
- 学习资源
- mysql命令
- source命令
- 使用load data导入数据
- 使用mysqlimport导入数据
MySQL 函数
- mysql内置了许多函数
- 查询使用方法及用途
MySQL 运算法
- 算术运算符
- 比较运算符
- 逻辑运算符
- 位运算符
- 学习地址
MySQL教程学习告一段落！日后在实战中加强运用和实践！
学习源Runoob,本文为学习笔记！