说明在mysql语句中，sql语句总共分四种
a、DDL数据定义语句=>数据定义语言，用来定义数据库对象，数据库，表， 列等；常用的ddl语句有（CREATE【创建】,DROP【删除】,ALTER【修改表结构】）
b、DML数据操作语句=>数据操作语言，用来对数据库中表的数据增删改；常用的dml语句有（INSERT【添加】,UPDATE【修改数据】,DELETE【删除】）
c、DQL数据查询语句=>数据查询语言，用来查询数据库中表的记录；常用的dql语句有(SELECT【查询】)
d、DCL数据控制语句=>数据控制语言，用来定义数据库中访问权限和安全级别，及创建用户；用于管理数据库和用户的. 常用的有(commit, savepoint, rollback, grant, revoke）
1、mysql的命令行登录（注意：在登录后的mysql命令行里面，结束符用;或者用\g来结束）
//第一种登录方式
mysql -hlocalhost -uroot -p -P3306 => mysql -uroot -p
//再输入自己的密码就可以执行登录（注意在windows下面，可以把mysql添加到环境变量中）-P表示端口号默认是3306,-h表示地址,-D表示登录的同时登录到指定的数据库注意区别开prompt的D
//第二种登录方式
mysql -uroot -p123456
//123456表示密码
2、mysql的退出方式
//第一种方式
exit
//第二种方式
quit
//第三种方式
ctrl+c
3、修改mysql的命令提示符
--第一种在登录的时候进行修改,只对当次的连接有效,注意在使用的时候要用双引号
mysql -uroot -p --prompt "test>"
--在修改命令提示符的时候\h表示主机名\u表示当前用户\d表示当前打开的数据库\D表示当前的日期时间
mysql -uroot -p --prompt "\h\u\d\D>"
--mysql 在显示时设置对齐的小技巧，以下设置完后，那么显示就整齐了
cmd>mysql -uroot -p --default-character-set=latin1
mysql>set names gbk;
4、mysql 获取基本信息以及规范
--获取版本号，在未登录之前进行查看
mysql -V
msyql --version
--进入数据库之后查看版本号(注意在语句结束后要用；或者\g来结尾而\c来表示不执行当前命令)
SELECT VERSION();
--查看当前用户
SELECT UESR();
--获取当前数据库
SELECT DATABASE();
--获取当前时间
SELECT NOW();
--注意数据库和表名称不要用mysql的内部名称进行命名，如果一定要使用，那么要用``将其括起来
SELECT @@SQL_MODE;
--查看mysql的模式，是否是严格模式
SHOW ENGINES\G；
--查看mysql存储引擎
SHOW VARIABLES LIKE 'storage_engine';
--查看默认的存储引擎
SHOW TABLE STATUS LIKE 'test'\G;
--查看表的状态 如果查看全部的则是SHOW TABLE STATUS\G;
CHECK TABLE test
--检查表的内容test表示表名并且这边的表名是不加引号的
REPAIR TABLE test
--修复表test表示表名并且这边的表名是不加引号的
SHOW PROCESSLIST
--查看当前有多少个客户端口连接到该数据库，该指令可以帮助我们查看当前有多少个客户端连接到mysqldb上，有助于我们了解当前的mysql运行和使用状况
5、数据库的相关操作
创建数据库
不同的校验规则，会对查询时字母是否区分大小写和排序有影响, 举例:
(1) 默认的 utf8_general_ci 不区分大小写
(2) 还有有个 utf8_bin 这个是区分大小写.
--创建数据库
CREATE DATABASE `test`;
--为了阻止重名的提示,表示如果不存在test的情况下新建test,也就是把错误信息放到警告中区
CREATE DATABASE IF NOT EXISTS `test`;
--查看警告,注意没有括号
SHOW WARNINGS;
--在创建数据库的时候指定编码方式
CREATE DATABASE IF NOT EXISTS `test` DEFAULT CHARACTER SET 'UTF8'('utf8mb4');
--在创建数据库的同时，指定编码方式和较验规则
CREATE DATABASE IF NOT EXISTS `db` DEFAULT CHARACTER SET `utf8` DEFAULT COLLATE `utf8_general_ci`;
--查看指定数据库的编码方式
SHOW CREATE DATABASE `test`;
--修改指定数据库的编码方式alter表示改变的意思
ALTER DATABASE `test` DEFAULT CHARACTER SET 'UTF8'('utf8mb4');
--查看数据库
SHOW DATABASES;
--打开指定数据库
USE test;
--获得当前已经打开的数据库
SELECT DATABASE();
--删除指定的数据库
注意在打印SHOW CREATE DATABASE `dbname`时会出现
表示当mysql版本大小4.01.00的时候会执行后面语句
比如：当执行/*!50500 SHOW DATABASES */时表示当mysql 的版本大于5.05.00的时候，会执行里面语句；
数据库的备份与恢复
数据库备份的基本语法(在未进入mysql即cmd环境下)：cmd>mysqldump -uroot -p 数据库名>d:/备份路径/备份名.bak
如果只想备份其他的几张表，语法是：cmd>mysqldump -uroot -p 数据库名 表名1 表名2 >d:/备份路径/备份名.bak
数据库恢复的步骤
如果库中有中文字符的情况，最好设置 set names gbk;
- 创建一个新的空库（mysql 环境下）
- use 新的数据库
- SOURCE d:/备份路径/备份名.bak
同时备份多个数据库 mysqldump -uroot -p -B 数据库名1 数据库名2 数据库名3 >d:/路径/文件名.bak
那么同时恢复多个数据库 SOURCE d:/备份路径/备份名.bak 即可（注意设置set names gbk）
6、数据表的相关操作
数据表中的类型主要有,整数型,浮点型,定点型,字符串型,日期类型
整数类型:
注意：在类型后面加数字，只是规定其显示的宽度（即零填充的宽度），而不会改变其存储的容量。BOOL类型指定后被打印出来显示是TINYINT(1)，但是会影响零填充的情况。
数值型bit的使用，bit字段在显示时按ascii码对应的字符显示，但是查询的时候仍然可以用数值查询 占用的字节是1-64个字节。
浮点型：
注意：对精度要求比较高的情况下，可以用DECIMAL进行存储如求和后，尾部会有一长串的小数位。以上的几个数据类型M表示数据的总长度（包含小数位），D表示小数所占用的长度，decimal 如果不指定长度的情况下，默认保留10位数，decimal最大数可以保留到65位
举例：
float(4,2) 表示的范围是-99.99-99.99
float(4,2) unsigned 表示的范围是 0-99.99
decimal(4,2) 表示的范围是-99.99-99.99
decimal(4,2) unsigned 表示的范围是 0-99.99
注意当小数点超过设置的小数点的位数，那么系统会进行四舍五入，如果四舍五入后超出范围，那么就会报越界的错误
字符型：
注意：CHAR(3),即使存储一个字节，那么所占容量是3个字节，VARCHAR(3)，存储一个字节，所占容量是length+1个字节。但是CHAR的检索速度要比VARCHAR来得快。CHAR在存储的时候尾部会用空格来填充，在检索的时候会把尾部的空格去掉，VARCHAR则不进行填充，尾部的空格也不会去掉。如果需要的字段是定长的，如电话号，md5等可以使用char，否则可以使用varchar。char在存放空格的时候容易丢失，而varchar则不容易丢失。
VARCHAR M 的范围是
如果表的字符集是utf8那么M的范围是（65535-3）/3=21844；
如果表的字符集是gbk那么M的范围是（65535-3）/2=32766；
如果表的字符集是latin1那么M的范围是（65535-3）/1=65532；
其中有两个字符是记录varchar数据的大小，一个字符是记录是否为Null
TEXT相关的列不能有默认值default，可以不指定大小并且TEXT在检索的时候是不存在大小写转换，并且效率要低于CHAR与VARCHAR。
在创建一张表的时候，所有的字段总和不能超过65535，但是如果超过了，那么可以用TEXT来替代
ENUM类型对应的value值所对应的下标是从1开始的，也可以用下标进行插入值，如：INSERT test (sex) VALUES (1)进行插入。
ENUM相当于单选，而SET相当于多选，并且可以用数字来替代
CREATE TABLE IF NOT EXISTS test_enum(
a ENUM('男','女','保密')
);
INSERT test_enum (a) VALUES ('男');
INSERT test_enum (a) VALUES (3);
INSERT test_enum (a) VALUES ('女');
SELECT * FROM test_enum;
--输出
+------+
| a |
+------+
| 男 |
| 保密 |
| 女 |
+------+
SET类型的在设定的值里面，当插入值后，表会根据原始的值的顺序对插入的值进行排序，可以插入一个值，也可以插入多个值，但前提是值要在预设的值里面，否则mysql会报错，并且注意只用一个引号。
CREATE TABLE IF NOT EXISTS test_set(
a SET('A','B','C','D','E','F')
)ENGINE=INNODB CHARSET=UTF8;
INSERT test_set (a) VALUES ('A,B');
INSERT test_set(a) VALUES('C,F,D');
INSERT test_set(a) VALUES('B,A,F');
SELECT * FROM test_set;
+-------+
| a |
+-------+
| A,B |
| C,D,F |
| A,B,F |
+-------+
如果查询在集合set中的字段用find_in_set如下
SELECT * FROM `test_set` WHERE FIND_IN_SET('A',a),表示在表test_set表中查询字段a中有A选项的记录，返回的是指定值在字段的位置
日期型:
注意:常用的有DATE,DATETIME,TIMESTAMP,date的格式是“2011-11-11”，datatime和timestamp的格式是"2011-11-11 12:12:00"的模式TIME类型的第一个数字表示天数,当212:59:59表示60:50:50;
timestamp要慎重使用，因为在insert（没有指定值的时候，自动取系统时间）和update（在更新该字段或者其他字段时）的时候timestamp字段会自动更新
对于图片音频和视频通常不会直接存放在数据库中，而是记录该文件的路径，然后通过路径去读取该文件
表格的创建
//创建表的基本语法
CREATE TABLE [IF NOT EXISTS] tbl_name(
字段名称 字段类型 [完整性约束条件],
字段名称 字段类型 [完整性约束条件],
...
)ENGINE=存储引擎 CHARSET=编码方式 COLLATE=utf8_general_ci;
//举例(以下例子不包括约束性条件):
CREATE TABLE IF NOT EXISTS user(
id INT,
username VARCHAR(20),
password CHAR(32),
email VARCHAR(50),
age TINYINT,
card CHAR(18),
tel CHAR(11),
salary FLOAT(8,2),
married TINYINT(1),
addr VARCHAR(100),
sex ENUM('1','2','0')
)ENGINE=INNODB CHARSET=UTF8;
表格的基本操作
//查看数据库的表
//如果只是查看当前的表,前提是当前数据库处于打开的状态下
SHOW TABLES;
//如果查看别的数据库的表,可以是当前无打开的数据库
SHOW TABLES FROM (database_name)
//也可以是
SHOW TABLES IN (database_name)
//查看表的详细信息
SHOW CREATE TABLE table_name
//查看表的结构
DESC table_name
//删除数据表
DROP TABLE IF EXISTS table_name
复制表
--复制一个表结构 CREATE TABLE `新表名` like `旧表名`; --进行数据的复制 INSERT INTO `新表名` SELECT * FROM `旧表名`; （注意：这里没有value或者是values）
7、完整性的约束条件
A、UNSIGNED 无符号，没有负数，从0开始（一般放在not null 前面）
B、ZEROFILL 零填充，当显示长度不够的时候可以使用前补0的效果填充至指定长度，添加上这个属性后就相当于默认UNSIGNED这个属性了，填充的顺序是从左边开始填充
C、NOT NULL 非空约束，也就是插入值的时候这个字段必需给值
D、PRIMARY KEY 主键，标识记录的唯一性，值不能重复，一个表只能有一个主键，并且自动禁止为空,实现表里字段的快速定位
E、UNIQUE KEY 唯一性，一个表中可以有多个字段是唯一索引，值不能重复，NULL除外(如果设置成unique,not null在使用效果上相当于Primary key)
F、AUTO_INCREMENT 自增长，只能用于数值列，而且配合索引使用,且一个表只有一个自增长的类型
G、FOREIGN KEY 代表外键约束
H、COMMENT 表示添加注释
I、 DEFAULT 默认值
测试主键
--测试主键
CREATE TABLE IF NOT EXISTS test_pri(
id INT UNSIGNED PRIMARY KEY,
--还可以使用以下两种方法指定
--id INT UNSIGNED KEY,
--PRIMARY KEY(id),
username VARCHAR(20)
)ENGINE=INNODB CHARSET=UTF8;
INSERT test_pri (id,username) VALUES (1,'first');
INSERT test_pri (username) VALUES (2,'second');
DESC test_pri;
+----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| username | varchar(20) | YES | | NULL | |
+----------+------------------+------+-----+---------+-------+
复合主键表示用两个字段共同组成一个主键,那么就可以根据这两个字段进行对数据的定位,同时唯一性也就变成两个字段不能同时相同
CREATE TABLE IF NOT EXISTS test_pri(
id INT UNSIGNED,
uid INT UNSIGNED,
name VARCHAR(20),
--定义复合组件只能用这种方式定义,也可以是整型与字符串的组合,比如uid是VARCHAR类型的
PRIMARY KEY(id,uid)
)ENGINE=INNODB CHARSET=UTF8;
INSERT test_pri (id,uid,name) VALUES (1,2,'first');
DESC test_pri;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| uid | int(10) unsigned | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
测试自增长
CREATE TABLE IF NOT EXISTS test_inc(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
)ENGINE=INNODB CHARSET=UTF8;
--以下三种方式添加都可以实现主键的自增长
INSERT test_inc (id,name) VALUES (DEFAULT,'first');
INSERT test_inc (id,name) VALUES (NULL,'second');
INSERT test_inc (name) VALUES ('third');
DESC test_inc;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+
SHOW CREATE TABLE test_inc;
--输出如下表格
+----------+-------------------------------------------------+
| Table | Create Table |
+----------+-------------------------------------------------+
| test_inc | CREATE TABLE `test_inc` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
+----------+-------------------------------------------------+
测试唯一索引
CREATE TABLE IF NOT EXISTS test_unq(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
--在定义唯一索引的时候可以省略KEY
name VARCHAR(20) UNIQUE KEY NOT NULL,
--UNIQUE KEY(name)
phone VARCHAR(11) UNIQUE NOT NULL
)ENGINE=INNODB CHARSET=UTF8;
DESC test_unq;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | UNI | NULL | |
| phone | varchar(11) | NO | UNI | NULL | |
+-------+------------------+------+-----+---------+----------------+
测试建立一个相对完整的用户表
CREATE TABLE IF NOT EXISTS user(
`id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '用户编号',
`username` VARCHAR(20) NOT NULL COMMENT '用户名',
`password` VARCHAR(60) NOT NULL COMMENT '用户密码',
`email` VARCHAR(50) NOT NULL UNIQUE KEY COMMENT '用户邮箱',
`age` TINYINT UNSIGNED NOT NULL COMMENT '用户年龄',
`tel` VARCHAR(20) NOT NULL UNIQUE KEY COMMENT '用户手机',
`addr` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '用户地址',
`sex` ENUM('0','1','2') NOT NULL DEFAULT '0' COMMENT '性别，1代表男，2代表女，0代表保密',
`card` VARCHAR(18) NOT NULL UNIQUE KEY COMMENT '用户身份证',
`married` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否已婚，0未婚1已婚',
`salary` FLOAT(6,2) NOT NULL DEFAULT 0 COMMENT '薪水'
)ENGINE=INNODB CHARSET=UTF8;
8、表结构的相关操作（字段的增删改）
增加和删除字段
--添加字段,如果没有FIRST或者AFTER，那么默认是追加到表尾
ALTER TABLE tab_name ADD 字段名 字段属性[完整性约束条件][FIRST|AFTER 字段名称]
--添加多个字段的时候
ALTER TABLE tab_name ADD 字段名 字段属性[完整性约束条件][FIRST|AFTER 字段名称],ADD 字段名 字段属性[完整性约束条件][FIRST|AFTER 字段名称];
--例子，注意age是在username之前添加
ALTER TABLE user ADD age TINYINT UNSIGNED NOT NULL AFTER id,ADD username VARCHAR(20) NOT NULL AFTER age;
--删除字段
ALTER TABLE tab_name DROP 字段名
--删除多个字段
ALTER TABLE tab_name DROP 字段名,DROP 字段名;
--例子
ALTER TABLE user DROP username,DROP age;
修改字段的属性类型(注意不包含索引的修改,索引在这里面修改是无效的)
--修改字段的字段类型,字段属性
ALTER TABLE tab_name MODIFY 字段名 字段类型[完整性约束性条件][FIRST|AFTER 字段名称]
--同时修改多个字段的字段类型，字段属性
ALTER TABLE tab_name MODIFY 字段名 字段类型[完整性约束性条件][FIRST|AFTER 字段名称],MODIFY 字段名 字段类型[完整性约束性条件][FIRST|AFTER 字段名称]
--修改字段的名称,字段类型,字段属性
ALTER TABLE tab_name CHANGE 字段名 新字段名[完整性约束性条件][FIRST|AFTER 字段名称]
--同时修改多个字段的名称,字段类型,字段属性
ALTER TABLE tab_name CHANGE 字段名 新字段名[完整性约束性条件][FIRST|AFTER 字段名称],CHANGE 字段名 新字段名[完整性约束性条件][FIRST|AFTER 字段名称]
添加和删除主键
--添加主键
ALTER TABLE tab_name ADD PRIMARY KEY 字段名
--如果是添加复合主键，那么按如下书写
ALTER TABLE tab_name ADD PRIMARY KEY (字段名1,字段名2)
--删除主键，如果主键上有AUTO_INCREMENT这个属性，那么，要先删除AUTO_INCREMENT这个属性
ALTER TABLE tab_name DROP PRIMARY KEY
添加和删除唯一索引
--添加唯一索引，以下两种写法都可以
ALTER TABLE tab_name ADD UNIQUE KEY 索引名(如uni_name) (字段名)
ALTER TABLE tab_name ADD UNIQUE INDEX 索引名(如uni_name) (字段名)
--例子(两种效果是一样的)
ALTER TABLE test ADD UNIQUE INDEX uni_name (name);
ALTER TABLE test ADD UNIQUE KEY uni_name (name);
--也可以同时添加多个唯一索引
ALTER TABLE tab_name ADD UNIQUE KEY 索引名(如uni_name) (字段名),ADD UNIQUE KEY 索引名(如uni_name) (字段名)
--例子
ALTER TABLE test ADD UNIQUE KEY uni_name (name),ADD UNIQUE INDEX uni_card(card)
--删除唯一索引，注意后面添加的是索引名，通常没有特别指定的，那么索引名就是字段名，可以能过SHOW CREATE TABLE tab_name 来查看
ALTER TABLE tab_name DROP INDEX 索引名
--删除多个唯一索引
ALTER TABLE tab_name DROP INDEX 索引名，DROP INDEX 索引名
-- 还有一种建立唯一索引的方法
CREATE UNIQUR INDEX 索引名 ON 表名（字段名）
修改数据表名称
--第一种方法，这里面的TO或者AS 是可以省略的
ALTER TABLE tab_name RENAME [TO|AS] new_tab_name;
--举例
ALTER TABLE test RENAME nametest;
--第二种方法，这里面的TO是不能省略的
RENAME TABLE tab_name TO new_tab_name;
--举例，这里只有TO
RENAME TABLE nametest TO test;
重置自增长的序号
--重置AUTO_INCREMENT
ALTER TABLE tab_name AUTO_INCREMENT=1;
索引
在Mysql中常见的索引有四种：主键索引，唯一索引，普通索引（建立如下例子），全文索引（主要用于检索某个字段的关键字）
建立索引会让表的检索速度加快，但是会稍微损耗一些增删改的性能，不过这种损耗可以忽略不计
-- 建立普通索引 一般索引名是ind_字段名 一张表可以有多个普通索引
CREATE INDEX `INDEXNAME` ON `TABLENAME`(`字段名`)
--方法二
ALTER TABLE `表名` ADD INDEX `索引名` (`字段`)
索引的查询
——方法一 SHOW INDEX FROM '表名' \G 表示换行显示 ——方法二 SHOW INDEXES FROM `表名`
9、mysql 的存储引擎
mysql 是由SQL 接口，解析器，优化器，缓存和存储引擎组成的。
--通过SHOW ENGINES\G表出所有的引擎
--引擎名称
Engine: InnoDB
--是否支持
Support: YES
--描述
Comment: Supports transactions, row-level locking, foreign keys and encryption for tables
--是否支持事务
Transactions: YES
--是否支持分布式交易处理的XA规范
XA: YES
--是否支持保存点，事务性回滚的时候回滚到所保存的点
Savepoints: YES
MEMORY存储引擎：特点：1、数据保存在内存中，读写效率高。2、磁盘中只产生一个.frm的文件，只保存表结构。3、关闭mysql服务，数据会产生丢失。
CSV存储引擎：特点：1、所有的列不支持空值，所以都必需加上NOT NULL。2、CSV引擎不持索引也不支持分区。3、.csv文件是保存数据的原文件。4、.csm是保存元信息的文件保存表的状态及存储的数据量。
ARCHIVE存储引擎：特点：1、适合对于不经常使用，又删除不了的数据做归档处理。2、.frm文件是结构文件 .arz是数据文件。3、插入效率很高而且占用的空间小，只支持select和insert而不支持delete 和update语句。
MYISAM存储引擎：特点：1、默认的MYISAM会在磁盘中产生三个文件.frm文件是表结构文件，.myd是表示数据文件，.myi是表示索引文件。2、单表支持的最大的数据量的记录是2的64次方条记录。3、每个表可以建立64个索引，复合索引最多可以含16个字段，索引值最大长度是1000B。
InnoDB存储引擎：特点：1、设计遵循ACID原则，支持事务，具有从服务崩溃中恢复的能力，最大限度的保护用户的数据。2、支持行级锁，可以提升多用户并发时的读写性能。3、支持外键，保证数据的一致性和完整性。4、InnoDB拥有自己独立的缓冲池，常用数据和索引都在缓存中。5、对于INSERT、UPDATE、DELETE操作INNODB会使用一种change buffering的机制来自动优化，还可以提供一致性的读，并且还能够缓存变更的数据，减少磁盘I/O,提高性能。
(1) MyISAM的存储引擎的介绍
- 执行速度快.
- 不支持事务，不支持外键
- 会产生碎片，需要定时进行optimize table 表名[举例]
- 如果我们的表不需要事务，则考虑使用MyISAM
(2) InnoDB存储引擎
- 并发性好.
- 支持事务，支持外键
- 如果我们希望使用事务，则应该选择InnoDB.
(3) Memory 存储引擎
- 数据在内存中
- 执行速度快.
- 当重启mysql后，数据丢失了/表结构还在.
- memory 表，可以优化项目，可以存放经常查询或者变化的数据，而且这些数据不需要入库,比如(用户的状态[离线，在线，发呆]) .
