说明在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'; --在创建数据库的同时，指定编码方式和较验规则 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'; --查看数据库 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 表，可以优化项目，可以存放经常查询或者变化的数据，而且这些数据不需要入库,比如(用户的状态[离线，在线，发呆]) .
495
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
