1.数据库简介
DataBase System = 数据库管理系统（DBMS，DataBase Management System) + 数据库(DataBase) + 管理员
SQL：数据库管理系统，用来管理数据的语言。结构化查询语言（SQL，Structured Query Language）
1.1 数据库的四种语法
数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。
1.1.1 数据查询语言DQL
数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块：
SELECT <字段名表>
FROM <表或视图名>
WHERE <查询条件>
1.1.2 数据操纵语言DML
数据操纵语言DML主要有三种形式：
1) 插入：INSERT
2) 更新：UPDATE
3) 删除：DELETE
1.1.3 数据定义语言DDL
数据定义语言DDL用来创建数据库中的各种对象—–表、视图、索引、同义词、聚簇等如：
| CREATE | TABLE | VIEW | INDEX | SYN | CLUSTER |
|---|---|---|---|---|---|
| 创建 | 表 | 视图 | 索引 | 同义词 | 簇 |
DDL操作是隐性提交的！不能rollback
1.1.4 数据控制语言DCL
数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。如：
1.1.4.1 GRANT：授权。
1.1.4.2 ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。
回滚—ROLLBACK
回滚命令使数据库状态回到上次最后提交的状态。
其格式为： SQL>ROLLBACK;
1.1.4.3 COMMIT [WORK]：提交。
在数据库的插入、删除和修改操作时，只有当事务在提交到数据库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看到所做的事情，别人只有在最后提交完成后才可以看到。提交数据有三种类型：显式提交、隐式提交及自动提交。下面分别说明这三种类型。
(1) 显式提交
用COMMIT命令直接完成的提交为显式提交。其格式为：
SQL>COMMIT；
(2) 隐式提交
用SQL命令间接完成的提交为隐式提交。这些命令是：
ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，
EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。
(3) 自动提交
若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，
系统将自动进行提交，这就是自动提交。其格式为：
SQL>SET AUTOCOMMIT ON；
2.数据库操作
2.1 创建数据库
2.1.1 基本语法
Create database db_name [数据库选项];
2.1.2 命令规则
数据库选项包括:
设定数据库字符集（character set utf8)和校对集(collate utf8_general_ci) ;
标识符（数据库名）命名规则：
大小写取决于当前操作系统。（认为是区分的）见名知意。推荐使用下划线方式。
标识符的字符：
使用任意字符，数字，符号，甚至是中文。但是一些特殊的组合，例如纯数字组合，特殊符号，包括mysql是内部关键字 应该使用标识符 限定符来包裹。
限定符： 反引号(“)。
中文可以：但是要求客户端编码 .
2.1.3 创建数据库结果
在mysql的数据目录，形成一个目录，目录名是数据库名。
如果是特殊字符(比如中文)，则使用编码的形式保存.
2.2 事务操作
2.2.1 事务操作
首先确定存储引擎是支持事物的比如innodb支持事物.
myisam引擎不支持事务, innodb和BDB引擎支持.
//查看当前的事物是否开启自动提交,所以才会执行完slq后自动提交
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)
//修改为off之后,可以试验手工提交数据
start transaction 简写 begin //开启事务
commit;//成功
rollback；//失败：
2.2.2 事务特点 ACID
- 1.原子性(Atomicity）:是指某几句sql的影响,要么都发生,要么都不发生.
- 2.一致性(Consistency):事务前后的数据,保持业务上的合理一致.
- 3.隔离性(Isolation）:在事务进行过程中, 其他事务,看不到此事务的任何效果. 持久性: 事务一旦发生,不能取消. 只能通过补偿性事务,来抵消效果.
- 4.持久性(Durability）。
2.2.3 隔离级别
-
- read uncommitted: 读未提交的事务内容,显然不符原子性, 称为”脏读”. 在业务中,没人这么用.
-
- read commited: 在一个事务进行过程中, 读不到另一个进行事务的操作,但是,可以读到另一个结束事务的操作影响.
-
- repeatable read: 可重复读,即在一个事务过程中,所有信息都来自事务开始那一瞬间的信息,不受其他已提交事务的影响. (大多数的系统,用此隔离级别)
-
- serializeable 串行化, 所有的事务,必须编号,按顺序一个一个来执行,也就取消了冲突的可能.这样隔离级别最高,但事务相互等待的等待长. 在实用,也不是很多.
| 隔离级别 | 读数据一致性 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|---|
| 未提交读 | （Read uncommitted） | 最低级别，只能保证不读取物理上损坏的数据 | 是 | 是 |
| 已提交读 | （Read committed） | 语句级 | 否 | 是 |
| 可重复读 | （Repeatable read） | 事务级 | 否 | 否 |
| 可序列化 | （Serializable） | 最高级别，事务级 | 否 | 否 |
//设置隔离级别
set session transaction isolation level [read uncommitted | read committed | repeatable read |serializable]2.2 操作数据库
2.2.1 基本语法
//创建数据库
Create database db_name [数据库选项];
//查看数据库
Show databases;
//查看数据库创建语句
Show create database db_name;
//数据库删除
Drop database db_name;
//修改数据库信息(数据库属性的修改)
Alter database db_name [修改指令]
//切换数据库
use database_new2.2.2 注意
数据库迁移
将数据库内容全部导出，新建一个数据库，将内容导入，删除旧数据库。
创建一个新数据库，将旧数据库内的表，都移动（重命名）到新数据库内，删除旧数据库
\G 格式化输出
删除数据库 删除一个数据库时，同时删除该数据库相关的目录及其目录内容
//格式化输出命令结果
Show databases\G;3 表操作(数据定义语言DDL)
3.1 基本语法
//创建表
create table tbl_name (列结构[列选项])[表选项];
列选项包括:
[是否为空] [Default 默认值] [是否为自动增长] [是否为主索引或唯一索引] [comment 注释] [引用定义]
表选项:
表引擎：engine|type=引擎;表字符集与校对集 charset set=字符集 collate=校对集; 注释 comment=‘注释’
eg:
mysql> create table test_table(
-> name varchar(20),
-> score int
-> );
Query OK, 0 rows affected (0.03 sec)
//查看表
show tables;
//模糊查看表
show tables like 'exam_%';
//查看表的创建信息
show create table tbl_name;
//\G可以格式化输出
show create table tbl_name\G;
//查看表结构
describe tbl_name;
//查看表结构(简写)
desc tbl_name;
//删除表
drop table [if exists] tbl_name;
也可以删除多个,用逗号分隔开.
//修改表名称
Rename table old_tbl_name to new_tbl_name;
//修改表名称(多个)
Rename table old_tbl_name1 to new_tbl_name1,old_tbl_name2 to new_tbl_name2;
//修改表内容(列定义)
alter table tbl_name [add|drop|change|modify]
//修改表内容-新增一列
alter tablename add newcolumn varchar(10);
//修改表内容-删除一列
alter tablename drop onecolumn;
//修改表内容-修改列属性
alter tablename modify newcolumn varchar(20):
//修改表内容-修改列名称
alter tablename change old_column_name new_column_name varchar(30);
3.2 其它基本语法
- Null | not null 可以规定当前列，是否可以为null。
- DEFAULT 默认值,如果sql不书写该列才用默认值.
- 主键 PRIMARY KEY
- 自动增长 Auto_increment
- 修改表名称支持跨数据库
3.3 列数据类型
3.3.1 整型
3.3.1.1 整型分类
| 类型 | 字节 | 最小值（有符号/无符号） | 最大值（有符号/无符号） |
|---|---|---|---|
| TINYINT | 1 | -128/0 | 127/255 |
| SMALLINT | 2 | -32768/0 | 32767/65535 |
| MEDIUMINT | 3 | -8388608/0 | 8388607/16777215 |
| INT/INTEGE | 4 | -2147483648/0 | 2147483647/4294967295 |
| BIGINT | 8 | -9223372036854775808/0 | 9223372036854775807/18446744073709551615 |
3.3.1.2 详解
- 是否有符号
默认不写就是有符号, 如果要符号见下列SQL. - 定义显示宽度
前导零填充达到目的。称之为 zerofill.(不影响数的范围,宽度大的不影响，不会截取) - 创建时的括号内int(10)的数值意义 创建列字段时的宽度意义为显示宽度,当数值过小用到zerofill时的补充0的后的总体宽度,如果数值过大,则不截取.和存储的数据大小没关系.默认不写会自动填写11.
- boolean
布尔bool类型，但是就是tinyint(1)的别名
//整型带符号
column_name int unsigned;
//前导零填充,补充零的个数最终和括号中的10一致,默认不写会自动填写11
coluemn_name int(10) zerofill;3.3.2 小数
3.3.2.1 小数分类
| 类型 | 存储空间（字节） | 最小值（理论） | 最大值（理论） |
|---|---|---|---|
| FLOAT | 4 | -3.402823466E+38 | 3.402823466E+38 |
| DOUBLE | 8 | -1.7976931348623157E+308 | 1.7976931348623157E+308 |
| DECIMAL(M,D) | 变长，大致是每9个数字，采用4个字节存储。整数和分数分开计算 | M，最大是65D，最大是30,默认是10，2,-（65个9） | （+65个9） |
3.3.2.2 详解
- float单精度 默认精度(不是位数)为6位左右。
- double双精度 默认精度(不是位数)为16位左右。
- DECIMAL(M,D) M表示所有的数值位数（不包括小数点，和符号），D,表示允许的小数位数。默认为(10,0)
- 可以zerofill；可以无符号；可以使用科学计数法(写法为: 1.234E3)
3.3.3 日期时间
3.3.3.1 日期时间分类
| 类型 | 显示格式 | 取值 | 存储空间 | 零值 |
|---|---|---|---|---|
| DATETIME | YYYY-MM-DD HH:MM:SS | ‘1000-01-01 00:00:00’到’9999-12-31 23:59:59’ | 8 | 0000-00-00 00:00:00 |
| TIMESTAMP | YYYY-MM-DD HH:MM:SS | ‘1970-01-01 00:00:00’到2038-01-19 03:14:07 | 4 | 0000-00-00 00:00:00 |
| DATE | YYYY-MM-DD | ‘1000-01-01’到’9999-12-31 | 3 | 0000-00-00 |
| TIME | HH:MM:SS | -838:59:59’到’838:59:59’ | 3 | 00:00:00 |
| YEAR | YYYY | 1901到2155 | 1 | 0000 |
3.3.3.2 详解
- timestamp 存储是整型,但是显示时都是时间格式;可用 t.
timestamp_column+0 来显示时间戳. - date 支持二位的年份70-69 1970 - 2069,70-99 19xx,0-69 20xx年(不支持使用).
- time 可以表示1)一天中的时间;2)表示时间间隔，在表示间隔时. D HH:MM:SS ; D天。
- year 范围是1901-2155；不是1000-9999。
- timestampe容易移植
3.3.4 字符串
3.3.4.1 字符串分类
| 类型 | 最大长度 | 备注 |
|---|---|---|
| char | 255 | Char(M),M字符数,不是字节数 |
| varchar | 65535()，但需要1-2个保存信息，同时由于记录的限制，因此最大为65532 | 编码不同字符数不同：Gbk<=32767;Utf8<=21845 |
| tinyText，text，mediumText，longtext | L + n。L为最大长度;2^8+1, 2^16+2, 2^24+3, 2^32+4 | 定义时，通常不用指定长度，可以自己计算。 |
| enum | 1、2。枚举选项量（65535） | 内部存储是整型表示。字段值只能是某一个 |
| Set | 1、2、3、4、8。元素数量：64 | 单个是00001,00010,00100,01000,10000这样方式.然后转换为和集保存. |
| Binary,varbinary,blob | Binary(char),varbinary(varchar),blob(text) 做类比 | 二进制数据（字节而非字符） |
3.3.4.2 字符串注意
| 数据值 | Char(5) | Varchar(5) | 说明 |
|---|---|---|---|
| ” | 5个字符(latin占1个字节,gbk2个字节,utf8占3个字节) | 0字符+1个字节(latin占1个字节,gbk2个字节,utf8占3个字节) | varchar需要一个字节保存字符串总长度; |
| ‘abc’ | 5 | 4字符+1字节(latin占1个字节,gbk2个字节,utf8占3个字节) | |
| ‘abcdf’ | 5 | 6 | |
| ‘abcdefg’ | 报错 | 报错 |
- Char(M) 固定长度.表示允许的字符串长度.
- Varchar(M) 可变长度.M在varchar中的表示，是允许的最大长度.
- varchar(M)在保存字符串时，同时保存该字符串的长度，小于255采用一个字节保存，否则采用二个字节保存
- varchar(M),最大65535,如果是gbk,只能保存理论32767(65535/2=32767余1)字符,如果是utf8只能保存理论21845(65535/3)字符.
- varchar的真实长度
如果类型数据超过255个字符时,则最大长度变为65535-2=65533,这两个字段来保存字符串的长度.
整条数据会有一个字节来保存记录中的null值: 数据的整条记录需要1个字节来保存null记录(保存所有列的null记录).除非所有列都不为null才能忽略不计.不使用该字节.
也就是varchar的的最终长度为65535(整列所有字段属性都不能为空)/65534包含任一列字段可为空)-2(超过255的时候要占个字节) 除于 2(gbk)/或3(utf8)= (65533/65534 -2)除于 2/或3 = 65531/65532 除于2/或3.
- text text的长度是65535(字符),还有 Tinytext longtext,不占用 一行数据的 65535的长度限制.
- enum ENUM(“one”, “two”, “three”) ,实际存储的值为1,2,3. 最多65535
- set
set1set(‘ABC’,’1111’,’2222’,’XXX’) DEFAULT NULL, ABC的值1,1111的值2,2222的值4,XXX的值8. 如果多个用逗号隔开,存储的为值对应的数值的和.
3.4. 存储引擎
3.4.1 存储引擎介绍
3.4.2 MyISAM和InnoDB对比
3.4.2.1
| 对比项 | MyISAM | InnoDB |
|---|---|---|
| 表结构 | db.frm | db.frm |
| 数据和索引 | 单独保存db.MYD,db.MYI | 数据保存在ibdata1 |
| 事务 | 不支持 | 支持索引 |
| 外键 | 不支持 | 支持外键 |
| 锁机制 | 表锁 | 行锁 |
| 全文检索 | 支持 | 5.5后支持 |
| 数据可否压缩 | 可压缩,体积小 | 不可压缩,体积大 |
| 查询count(*) | 保存有,读出即可 | 扫描整个表计算 |
| AUTO_INCREMENT类型的字段 | 该字段可以和其他字段联合索引 | 必须有且只有该字段的索引 |
MyISAM 不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求.
InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事 务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引
MEMORY 存储引擎使用存在内存中的内容来创建表。 每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦服务关闭，表中的数据就会丢失掉.(Memory 存储，比如我们数据变化频繁，不需要入库，同时又频繁的查询和修改，我们考虑使用memory, 速度极快. )
3.4.2.2 存储数据格式
InnoDB:
数据保存在: ibdata1,
表名目录下: sys_role2.frm,sys_role2.idb
MyISAM:
表名: user.frm
数据: user.MYD
索引: user.MYI
索引中记录了磁盘的位置. 如果将数据frm和myd和myi复制到新的地方.数据都存在,但是索引需要重建,因为磁盘位置已经不一样了.
分区的时候MyISAM如下:
table1.frm,table1.par,table#P#t0.MYD,table#P#t0.MYI,table#P#t1.MYD,table#P#t1.MYI,table#P#t2.MYD,table#P#t2.MYI,
3.4.2.3 索引原理区别
(聚簇索引,b-tree索引)-将在mysql优化中总结.
3.4.2.4 如何选择存储引擎
myisam 存储: 如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎.,比如 bbs 中的 发帖表，回复表.
INNODB 存储: 对事务要求高，保存的数据都是重要数据，我们建议使用INNODB,比如订单表，账号表.
3.4.3 存储引擎配置
- my.ini中额皮质默认
default-storage-engine=INNODB- 创建sql时指定
//1.创建表是指定
CREATE TABLE `order_info` (
`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`order_no` VARCHAR(64) DEFAULT NULL COMMENT '流水号'
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='订单表'
//2.修改表的存储引擎
ALTER TABLE `order_info` ENGINE=MYISAM;
3.5 字符集&校对规则
3.5.1 构成部分：
字符的集合，展示
字符的编码，保存和处理
Show character set;
mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)
mysql> show character set 常见的字符集&编码：
Ascii字符集，GB2312字符集，gbk字符集，latin1字符集，unicode字符集等
3.5.2工作原理
注意: 如果通过客户端操作服务器，那么客户端与服务器之间进行数据通信，要保证编码一致。可以将互相发送的数据，转换成 目标可以接收的编码。
//1.客户端
通过mysql的配置(my.ini)：
Character_set_client 客户端发送数据编码
Character_set_results客户端接收数据的编码
通过 指令 show variables like 'character_set_%';
设置变量：
Set 变量名=值
Set character_set_client = gbk;
告知服务器，客户端发送的数据是gbk编码
执行 没有返回数据的语句没有问题了。
//2.服务端
如果需要从服务器返回数据，还需要设置服务器发送给客户端的编码
Set character_set_results = gbk;
服务器在发送数据时，才能转成客户端认识的编码
统一的操作可以用 set names gbk可以完成。（简单项目通用的做法）
//3.连接层
其实还有一个有影响：连接层编码。
Set character_set_connection = gbk;
Set names 可以设置上面的三个。
典型的情况，setnames即可。如果情况复杂，需要分开设置。
设置字符集类型的地方:
对字段，表，数据库，服务器进行字符集的设置，还可以设定连接字符集（客户端与服务器端交互时）
转换的过程
Client->connection->[服务器内部编码]->result
3.5.3 校对规则
校对规则: 当前字符集内，字符之间的比较关系, 默认都是_ci.
不同字符集有不同的校对规则，命名约定：以其相关的字符集名开始，通常包括一个语言名，并且以_ci（大小写不敏感）、_cs（大小写敏感）或_bin（二元）结束.
order by的时候会体现出来.
mysql> show collation like 'utf8%';
+--------------------------+---------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
| utf8_bin | utf8 | 83 | | Yes | 1 |
| utf8_unicode_ci | utf8 | 192 | | Yes | 8 |
| utf8_icelandic_ci | utf8 | 193 | | Yes | 8 |
| utf8_latvian_ci | utf8 | 194 | | Yes | 8 |
| utf8_romanian_ci | utf8 | 195 | | Yes | 8 |
| utf8_slovenian_ci | utf8 | 196 | | Yes | 8 |
| utf8_polish_ci | utf8 | 197 | | Yes | 8 |
| utf8_estonian_ci | utf8 | 198 | | Yes | 8 |
| utf8_spanish_ci | utf8 | 199 | | Yes | 8 |
| utf8_swedish_ci | utf8 | 200 | | Yes | 8 |
| utf8_turkish_ci | utf8 | 201 | | Yes | 8 |
| utf8_czech_ci | utf8 | 202 | | Yes | 8 |
| utf8_danish_ci | utf8 | 203 | | Yes | 8 |
| utf8_lithuanian_ci | utf8 | 204 | | Yes | 8 |
| utf8_slovak_ci | utf8 | 205 | | Yes | 8 |
| utf8_spanish2_ci | utf8 | 206 | | Yes | 8 |
| utf8_roman_ci | utf8 | 207 | | Yes | 8 |
| utf8_persian_ci | utf8 | 208 | | Yes | 8 |
| utf8_esperanto_ci | utf8 | 209 | | Yes | 8 |
| utf8_hungarian_ci | utf8 | 210 | | Yes | 8 |
| utf8_sinhala_ci | utf8 | 211 | | Yes | 8 |
| utf8_german2_ci | utf8 | 212 | | Yes | 8 |
| utf8_croatian_ci | utf8 | 213 | | Yes | 8 |
| utf8_unicode_520_ci | utf8 | 214 | | Yes | 8 |
| utf8_vietnamese_ci | utf8 | 215 | | Yes | 8 |
| utf8_general_mysql500_ci | utf8 | 223 | | Yes | 1 |
| utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 |
| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 |
| utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 |
| utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 |
| utf8mb4_latvian_ci | utf8mb4 | 226 | | Yes | 8 |
| utf8mb4_romanian_ci | utf8mb4 | 227 | | Yes | 8 |
| utf8mb4_slovenian_ci | utf8mb4 | 228 | | Yes | 8 |
| utf8mb4_polish_ci | utf8mb4 | 229 | | Yes | 8 |
| utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 8 |
| utf8mb4_spanish_ci | utf8mb4 | 231 | | Yes | 8 |
| utf8mb4_swedish_ci | utf8mb4 | 232 | | Yes | 8 |
| utf8mb4_turkish_ci | utf8mb4 | 233 | | Yes | 8 |
| utf8mb4_czech_ci | utf8mb4 | 234 | | Yes | 8 |
| utf8mb4_danish_ci | utf8mb4 | 235 | | Yes | 8 |
| utf8mb4_lithuanian_ci | utf8mb4 | 236 | | Yes | 8 |
| utf8mb4_slovak_ci | utf8mb4 | 237 | | Yes | 8 |
| utf8mb4_spanish2_ci | utf8mb4 | 238 | | Yes | 8 |
| utf8mb4_roman_ci | utf8mb4 | 239 | | Yes | 8 |
| utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 8 |
| utf8mb4_esperanto_ci | utf8mb4 | 241 | | Yes | 8 |
| utf8mb4_hungarian_ci | utf8mb4 | 242 | | Yes | 8 |
| utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | 8 |
| utf8mb4_german2_ci | utf8mb4 | 244 | | Yes | 8 |
| utf8mb4_croatian_ci | utf8mb4 | 245 | | Yes | 8 |
| utf8mb4_unicode_520_ci | utf8mb4 | 246 | | Yes | 8 |
| utf8mb4_vietnamese_ci | utf8mb4 | 247 | | Yes | 8 |
+--------------------------+---------+-----+---------+----------+---------+
53 rows in set (0.00 sec)
mysql> | 校对规则 | 全称 | 说明 |
|---|---|---|
| _ci | case insensitive | 大小写不敏感,默认 |
| _cs | case sensitive | 大小写敏感 |
| _bin | binary | 二元 |
3.6.实体关系
3.6.1 实体关系分类
- 1:1
- 1:N
- M:N
3.6.2 外键
3.6.2.1 定义
如果一个实体的（people)的某个字段(people:country_id)，指向（引用）另个实体(country)的主键（country:id)，
就称 people实体的country_id是外键。 [人属于某个唯一国家]
被指向的实体，称之为 主实体（主表），也叫父实体（父表）。country
负责指向的实体，称之为 从实体（从表），也叫子实体（子表）。people
3.6.2.2 作用
保证数据的完整性。用于约束处于关系内的实体。
增加子表记录时，是否有与之对应的父表记录。
在删除或者更新主表记录时，从表应该如何处理相关的记录。
3.6.2.3 定义一个外键：
在从表上，增加一个外键字段，指向主表的主键。
使用关键字 foreign key
Foreign Key (外键字段） references 主表名 (关联字段) [主表记录删除时的动作] [主表记录更新时的动作]
//使用举例,table_people从表;table_contry主表
create table_people(
privty id int private key auto_increment,
foreign_key_name foreign key (contry_id ) references table_contry(id)
)
3.6.2.4 外键操作
- 类型:
On update
On delete 选项:
Cascade: 关联操作，如果主表被更新或删除，那么从表也会执行相应的操作。
Set null: 设置为null，表示从表不指向任何主表记录。
Restrict：拒绝主表的相关操作。修改外键
一般为先删除再新增.
//可以同时书写修改和删除时的操作
create table_people(
privty id int private key auto_increment,
foreign_key_name foreign key (contry_id ) references table_contry(id) on update set null on delete retrict
)
//修改
alter table table_people drop foreign key;
alter table table_people add foreign key(contry_id) references table_contry(id) on update restrict on delete cascade;3.7.视图
//创建视图
Create view view_name AS select_statement;
CREATE
/*[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]*/
VIEW `bobshutetest`.`viewa`
AS
(SELECT * FROM test_table t WHERE t.id <10);
//删除视图
Drop view [if exists] view_name;
//修改视图
修改是先删除然后新建
//查看视图的创建语句
show create view viewname;
//查看当前所有的视图
select * from information_schema.VIEWS;- 视图的执行算法：
undefined：未定义(默认的)，这不是一种实际使用的算法，是一种推卸责任的算法—-告诉系统，视图没有定义算法，你看着办。
temptable：临时表算法；系统应该先执行视图的select语句，后执行外部查询的语句。
merge：合并算法；系统应该先将视图对应的select语句与外部查询视图的select语句进行合并，然后执行(效率高)，系统默认值。
//创建视图时指定算法
create algorithm = 指定算法 view view_name as select ...3.8.触发器
//触发器必须有名字，最多64个字符，可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象.
CREATE TRIGGER <触发器名称>
//触发器有执行的时间设置：可以设置为事件发生前或后。
{ BEFORE | AFTER }
//同样也能设定触发的事件：它们可以在执行insert、update或delete的过程中触发。
{ INSERT | UPDATE | DELETE }
//触发器是属于某一个表的:当在这个表上执行插入、 更新或删除操作的时候就导致触发器的激活. //我们不能给同一张表的同一个事件安排两个触发器。
ON <表名称>
//触发器的执行间隔：FOR EACH ROW子句通知触发器 每隔一行执行一次动作，而不是对整个表执行一次。
FOR EACH ROW
//触发器包含所要触发的SQL语句：这里的语句可以是任何合法的语句,包括复合语句，但是这里的语句受的限制和函数的一样,如果是修改前后的数据通过new和old来区分
<触发器SQL语句>
//创建触发器
CREATE TRIGGER trigger_name
trigger_time
trigger_event
ON tbl_name
FOR EACH ROW
trigger_stmt(可以用new或old)
trigger_name：标识触发器名称，用户自行指定；
trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；
tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句。对数据处理可以通过new或old来区分修改前后的数据
Old:
监听事件所在表上的数据，在事件发生之前时的数据。旧的数据。
New:
监听表上，事件发生之后，新处理完毕的数据。
由此可见，可以建立6种触发器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。
另外有一个限制是不能同时在一个表上建立2个相同类型的触发器，因此在一个表上最多建立6个触发器。
//删除触发器
Drop trigger trigger_name;
//查看当前触发器sql
Show create trigger trigger_name;
//查看当前数据库中的触发器
SHOW TRIGGERS [FROM schema_name];
//通过sqlyog自动生成的语法,可以添加多长的触发器,首先修改定义sql结束符$$,使用完之后再修改回去;
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER `bobshutetest`.`triggera` BEFORE/AFTER INSERT/UPDATE/DELETE
ON `bobshutetest`.`<Table Name>`
FOR EACH ROW BEGIN
END$$
DELIMITER ;
举例
DELIMITER $$
create trigger tg4
after update on o
for each row
begin
update g set num = num+old.much-new.much where id = old/new.gid;
end$$
DELIMITER ;
3.9 分区
语法如下
create table table1 (
id int,
name char(10)
)engine myisam charset utf8
partition by range(id) (
partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than MAXVALUE
);
分区后结果会发现如果mysiam类型,则会有多个myd和myi,此时文件分布如下:
table1.frm,table1.par,table#P#t0.MYD,table#P#t0.MYI,table#P#t1.MYD,table#P#t1.MYI,table#P#t2.MYD,table#P#t2.MYI,
create table table2 (
id int,
type char(10)
)engine myisam charset utf8
partition by list(type) (
partition type1 values in ('typea'),
partition type2 values in ('typeb'),
partition type3 values in ('typec','typed')
);
注意这种情况如果值是typea,typeb,typec,typed之外的值,插入会报错.
当然还可以根据时间来分区
注意: 查询时需要带上分区的列才更快.
4 表数据操作(DQL,DML)
4.1 基本语法
//插入
insert into table_name (字段列表) value(值列表)
//查询
slect * from table_name;
//删除
delete from table_name 条件
//修改
Update 表名 set 字段=新值, … 条件。
4.2 插入数据
插入多条数据
1.基本语法
insert into table_name (字段列表) value(值列表);//没用写到的列用默认值,如果不能为空则报错
2.插入多条数据
insert into tableA (columnA,columnB) values
('columnAvalue1', 'columnBvalue1'),
('columnAvalue2', 'columnBvalue2');
3.插入部分字段(set的方式),注意:插入式不能用别名
INSERT INTO test_table
SET NAME = 'name1',
score_new = 100 ;
4.插入失败(主键冲突时)可以改成执行修改
INSERT INTO test_table
(`id`, `name`, `score_new`)
VALUES
(10, 'name', 100)
ON DUPLICATE KEY
UPDATE name = 'newname',
score_new = 100 ;
5.insert into select 查询结果直接插入,查询的结果的个数和类型与插入保持一致
insert into tableA
(column1, column2)
select column1,column2
from tableB;
6.replace,如果主键冲突则修改替换,替换结果是全部字段替换(也就是说如果有的列没写,那就是这些列为空值了)
REPLACE INTO test_table
(id,NAME,score_new)
VALUES
(7, 'name7', 30);
7.load file 见后面load file;
4.3 修改数据
//修改基础语法
Update 表名 set 字段=新值, … 条件。
//修改多条数据
UPDATE
tablea JOIN tableb
ON tablea.public_column = tableb.public_column
SET tablea.column1 = 'value1',
talbeb.column2 = 'valueb'
WHERE tablea.column2 = '1' ;
//插入失败(主键冲突时)可以改成执行修改
INSERT INTO test_table
(`id`, `name`, `score_new`)
VALUES
(10, 'name', 100)
ON DUPLICATE KEY
UPDATE name = 'newname',
score_new = 100 ;4.4 删除数据
//删除全表数据
DELETE FROM test_table;
//删除第一条
DELETE FROM test_table LIMIT 1;
//删除排序后的第一条,如果要排序删除,必须limit,否则排序无效
DELETE FROM test_table ORDER BY id DESC LIMIT 1;
//删除多表中的数据
delete from tabalea,tableb using tablea join tableb on tablea.public_column = tableb.public_column where xxx
//删除整张表数据,删除表并重建
Truncate table ;- Truncate和delete的区别
| 比较项 | Truncate | delete |
|---|---|---|
| 操作方式 | 删除表，新建表 | 逐删除表中的数据 |
| 是否返回删除记录数 | 不返回 | 返回 |
| 主键增长方式 | 重建增长 | 从上次的位置继续 |
4.5 查询数据
4.5.1 基本查询详解
4.5.1.1 基础
select [查询选项] [查询表达式(字段表达式)] [from 子句] [where 子句] [group by 子句] [having 子句] [order by 子句] [limit 子句]
[as] 别名,
dual 虚表
where 数据过滤，理解上，数据安装交叉连接连接完成后，再做数据过滤。
having
using Using 要求，负责连接的两个实体之间的字段名称，一致。
on 在连接时，就对数据进行判断。
Order by Order by 字段 升序|降序(asc|desc) 默认 升序，asc
limit Limit offset(偏移量,默认0,可以忽略)，row_count(查询显示记录数),从offset开始查询后面row_count行
distinct 去重
Union 结果去重,需要上线语句列数及类型一致
Union all 结果不去重. 如果需要排序order by,则各子句需要limit,否则各自排序忽略. 或查出来之后在最后加order by 即可.
select tableA,tableB using(public_field)4.5.1.2 运算符
关系运算符
= > < >= <= !=
like _ % \% \_ (查%或_用\转义)
Is null is not null
Between and
In|not in (集合元素)
<=> 功能与 =一致，特别的功能在于 可以比较null值。
select null<=>null,1<=>null 返回 1 ,0
Interval(值, 元素1， 元素2， 元素N);
依次判断值，与元素之间的大小关系，如果值，小于元素1，则返回0；如果值小于元素2 则返回1，依次类推。
SELECT INTERVAL(5,10,3,15,1); ##//0,大于5的坐标,从0开始坐标
SELECT INTERVAL(5,2,3,15,1); ##//2
SELECT INTERVAL(5,2,3,2,6); ##//3逻辑运算符
And && Or || Not ! Xor
非：not !，非null 为null。
与：and &&，有0就是0，都是非零为1，存在null与非零则为null。
或：or || , null||null=null null||1=1 null||0=null
异或： xor ,有null，就是null
优先级最好通过()来区分
4.5.2 聚合分组查询
groupby ( with rollup )
Sum() Avg() Max(); Min(); Count() Group_concat()[分组结果通过,连接]
where先执行，group by 后执行
//根据dep,pos统计平均值
mysql> select dep,pos,avg(sal) from employee group by dep,pos;
+------+------+-----------+
| dep | pos | avg(sal) |
+------+------+-----------+
| 01 | 01 | 1500.0000 |
| 01 | 02 | 1950.0000 |
| 02 | 01 | 1500.0000 |
| 02 | 02 | 2450.0000 |
| 03 | 01 | 2500.0000 |
| 03 | 02 | 2550.0000 |
+------+------+-----------+
6 rows in set (0.02 sec)
//with rollup 是根据dep,pos统计平均值后,再根据dep统计一次平均值
mysql> select dep,pos,avg(sal) from employee group by dep,pos with rollup;
+------+------+-----------+
| dep | pos | avg(sal) |
+------+------+-----------+
| 01 | 01 | 1500.0000 |
| 01 | 02 | 1950.0000 |
| 01 | NULL | 1725.0000 |
| 02 | 01 | 1500.0000 |
| 02 | 02 | 2450.0000 |
| 02 | NULL | 2133.3333 |
| 03 | 01 | 2500.0000 |
| 03 | 02 | 2550.0000 |
| 03 | NULL | 2533.3333 |
| NULL | NULL | 2090.0000 |
+------+------+-----------+
10 rows in set (0.00 sec) 4.5.3 Exists
4.5.3.1 Exists语法
Exists(subquery)
判断依据：
如果子查询的 可以返回数据，则认为 exists 表达式 返回真。
否者，返回假4.5.3.2 exist与in的区别
- exists： 先获得每一条 teacher_class的数据，然后获得id字段，去teacher表内查找对应值，找到，
说明符合条件。 - in：先获得所有的id的可能性。再在检索teacher_class数据时，判断当前的id是否在id集合内。
4.5.4 连接查询
- join = inner join ** On
- cross join ** on
- (left/right)outer join ** on
- Using
- 自然连接 笛卡尔
select tableA left join tableB ;
//外连接多次查询
SELECT
s.*,
si.*
FROM
tableA AS ta
LEFT JOIN tableB AS tb
ON ta.id = tb.class_id
LEFT JOIN tableC AS tc
ON ta.id = tci.id
WHERE ta.class_name = 'searchvalue' ;
4.6 备份还原数据
4.6.1 OUTFILE
- 导出数据
SELECT ... FROM TABLE_A where
INTO OUTFILE "/path/to/file"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' //修改每列的分隔符,行之间列的分隔符,和数据结束符
LINES TERMINATED BY '\n'; //修改行结束符号
也可以
SELECT INTO OUTFILE "/path/to/file"
.. FROM TABLE_A where
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' //修改每列的分隔符 ,行之间列的分隔符,和数据结束符
LINES TERMINATED BY '\n'; //修改行结束符号
select * into dumpfile path 导出二进制数据,格式同上 - 导入数据
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name [PARTITION (partition_name,...)]
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]
LOAD DATA INFILE “/path/to/file” INTO TABLE table_name;
注意：如果导出时用到了FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”’ LINES TERMINATED BY ‘\n’语句，那么LODA时也要加上同样的分隔限制语句。还要注意编码问题
//导出数据 执行的时候发生的提示
mysql> select * from test_table where id<10 into outfile 'd:/fileout/ourdata' ;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv opti
on so it cannot execute this statement
mysql>
//解决办法:
mysql> SHOW GLOBAL VARIABLES LIKE '%secure%';
+--------------------------+------------------------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------------------------+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\ |
+--------------------------+------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)
//所以说明只能导入到secure_file_priv目录下,这么操作执行完成
mysql> select * into outfile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/ourd
ata' from test_table where id<10;
Query OK, 2 rows affected (0.00 sec)
//这么写也能成功
mysql>select * from test_table where id<10 into outfile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/ourdata2' ;
//数据导出后如下(\N是空字段)
1,"a",444444444,"2",\N,0000000000000000001234,"2017-10-22 11:32:01",\N,\N
2,"b",00022,"44",\N,000011111.333333333334,\N,"2017-10-22 11:34:45","85:32:33"
//导出二进制数据(同导出普通数据)
select t.blob into dumpfile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/ourdata2' from table t where t.column='' limit 1;
//数据导入,删除备份的表后重新导入数据
mysql>load data infile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/ourdata4' into table test_table fields terminated by ',' enclosed by '"' lines terminated by '\n' ;
4.6.2 mysqldump
mysqldump导出数据
通过source恢复数据
//基本语法
mysqldump -u -p --no-create-info --tab=. --fields-terminated-by=, --fields-optionally-enclosed-by=\" db_name tb_name
//备份某一个完整的数据库
C:\Users\user>mysqldump -uroot -p bobshutetest >d:\\bobshutetest.sql
Enter password: ******
//备份某个数据库中的某个表
C:\Users\shubb>mysqldump -uroot -p bobshutetest test_table >d:\\test_table.sql
Enter password: ******
//备份某个数据库中的多个表
C:\Users\shubb>mysqldump -uroot -p bobshutetest test_table test_table2 test_table3 >d:\\test_table.sql
Enter password: ******
//恢复数据
mysql> use bobshutetest;
Database changed
mysql> source d:\\test_table.sql
Query OK, 0 rows affected (0.00 sec)4.6.3 mysiam存储引擎的方式复制备份
直接将 tbl_name.frm, Tbl_name.myd,Tbl_name.myi 三个文件，保存，备份即可。
恢复后注意的是索引需要重建(磁盘位置已经不一样)
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
