MYSQL学习
2017-01-12 本文已影响0人
JUNjianshuZHU
distinct
select distinct col from table;
and
select col from table
where col1= 'value1' and col2= 'value2';
or
select * from table_name
where column1_name='exist1_value' or column2_name='exist2_value';
xor
select col
from table
where col1 xor col2
round
ROUND(7253.86, 0) -> 7254
ROUND(7253.86, 1) -> 7253.9
ROUND(7253.86,-3) -> 7000
ROUND(population/1000000,1)
case
CASE
WHEN condition1 THEN value1
WHEN condition2 THEN value2
ELSE def_value END
PROMPT
PROMPT \u@\h \d>
修改mysql提示符
创建数据库
CREATE DATABASE [IF NOT EXISTS] db_name
CHARACTER SET [=] charset_name;
修改数据库
ALTER DATABASE db_name
CHARACTER SET = charset_name;
删除数据库
DROP DATABASE db_name
Paste_Image.png
查看当前数据库
SELECT DATABASE();
创建表格
CREATE TABLE table_name(
username VARCHAR(20),
age TINYINT UNSIGNED,
salary FLOAT(8,2) UNSIGNED,
);
查看表格
SHOW TABLES [FROM db_name];
查看当前表的数据结构
SHOW COLUMNS FROM tb_name;
或者DESC COL
NULL，字段值可以为空
NOT NULL，字段值禁止为空，不赋值会报错
自动编号 AUTO_INCREMENT，必须与主键配合使用
创建一个带主键的表
CREATE TABLE table_name(
id SMALLINT UNSIGNED PRIMARY KEY
);
每张表只能存在一个主键,唯一性
1、UNIQUE KEY 一张数据表可以有多个，而PRIMARY KEY一张数据表只能有一个
2、唯一约束的字段可以为空，主键约束的字段不能为空
3、唯一约束若多个字段为空，看起来似乎不能保证唯一性，但存储的只有一个‘空’，所以还是保证了字段的唯一性
Paste_Image.png
数据类型 整型 （tinyint smallint mediumint int bigint）
浮点型 （FLOAT{[M,D]},DOUBLE{[M,D]}）M:总位数,D:小数点后位数
日期 （YEAR DATE TIME TIMESTEMP）
字符型 （CHAR VARCHAR TEXT）
DOS 界面连接数据库 mysql -u用户名 -p密码 -hHOST
显示数据库 SHOW DATABASES;
显示表结构 SHOW COLUMNS FROM table;
显示所有表 SHOW TABLES;
进入数据库 USE 数据库；
创建数据库 CREATE DATABASE 库名；
创建表 CREATE TABLE 表名（字段及信息）；
插入数据 INSERT INTO table []VALUES()；[]为空说明插入全部的值
查询数据 SELECT 字段 FROM table WHERE 条件
自增自动编号 auto_increment
主键约束 primary key（有auto_increment的一定是主键，主键不一定有auto_increment）
唯一约束 unique key
默认约束 default 当没有赋值时则自动添加默认值
创建外键约束
CREATE TABLE table1_name(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT，
pname VARCHAR(20) NOT NULL,
);
CREATE TABLE table2_name(
id SMALLINT UNSIGNED PRIMARY KEY，
username VARCHAR(20) NOT NULL,
pid SMALLINT UNSIGNED //pid和表table1_name中id字段类型要完全一样
FOREIGN KEY (pid) REFERENCES table1_name(id)
);
查看表是否有索引
SHOW INDEX FROM table_name
添加列字段
ALTER TABLE table1_name
ADD column1_name [字段约束] [FIRST添加到所有列字段前/AFTER column2_name添加到列字段之后]
添加多个列字段
ALTER TABLE table1_name
ADD COLUMN col [字段约束] [FIRST添加到所有列字段前/AFTER col添加到列字段之后],
ADD COLUMN col [字段约束] [FIRST添加到所有列字段前/AFTER col添加到列字段之后];
exp
mysql> ALTER TABLE transaction
-> ADD COLUMN t_way VARCHAR(255) NOT NULL,
-> ADD COLUMN t_time DATETIME NOT NULL;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
删除列字段
ALTER TABLE table1_name
DROP column1_name,DROP column2_name,......
列字段添加约束(可以是主键、唯一、外键约束)
ALTER TABLE table1_name
ADD CONSTRAINT (column1_name)
删除主键约束
删除主键约束
ALTER TABLE tb_name DROP PRIMARY KEY;
删除唯一约束 先通过 SHOW INDEXES FROM db_name来查找索引名
ALTER TABLE tb_name DROP {INDEX|KEY} index_name ;
删除外键约束 先通过 SHOW CREATE TABLE tb_name来查约束名。
ALTER TABLE tb_name DROP FOREIGN KEY fk_symbol;
修改列字段约束(只修改约束)
ALTER TABLE table1_name
MODIFY column1_name CONSTRAINT
修改列字段约束(同时修改列字段名称和约束)
ALTER TABLE table1_name
CHANGE column1_name new_column1_name CONSTRAINT
修改表名称
ALTER TABLE table1_name
RENAME new_table1_name
2017/3/14
插入记录
INSERT [into] table_name [(column_name)]
VALUES (value1,value2)，(value3,value4)......;
exp:
mysql> INSERT merchant (id,m_name)
-> VALUES (9999,'HUANYOU');
Query OK, 1 row affected
1.对于自动编号的字段，插入“NULL”或“DEFAULT”系统将自动依次递增编号；
2.对于有默认约束的字段，可以插入“DEFAULT”表示使用默认值；
3.列值可传入数值、表达式或函数，如密码可以用md5()函数进行加密（如md5('123')）；
4.可同时插入多条记录，多条记录括号间用逗号“,”隔开
插入记录的其他方法
insert tb_name set column_name={expr|default}
可以进行子查询，但是只能插入一条记录
insert table_name [(colname...)] select.....
把查询到的结果插入到指定数据表中
更新记录
UPDATE tb_name
SET col1_name='new1_value',col2_name='new2_value'.......
[where col3_name='exist_value'];
exp:
mysql> UPDATE merchant
-> SET create_time = '2017-1-1'
-> WHERE id=9999;
删除记录
DELETE FROM tb_name
WHERE col_name='exist_value'
exp:
mysql> DELETE FROM merchant
-> WHERE id=9999;
Query OK, 1 row affected
1.不添加WHERE则删除全部记录
2.删除单条记录后再插入，插入的记录中id编号将从最大值往上加，而不是填补删除的。
查询记录
SELECT col_name FROM tb_name;
57b5d7af0001df1b12800720.jpg
578f84f7000184e312800720.jpg
WHERE语句
SELECT col_name FROM tb_name
Where col_name = 'value';
GROUP BY语句
SELECT col_name FROM tb_name
GROUP BY (col_name/value)
exp:
SELECT*FROM users GROUP BY sex;
SELECT*FROM users GROUP BY 1;(这里的一表示查询的第一个字段，这里查询所有，第一个字段就是id，就就是会按照字段进行分组）
2017/3/15
GROUP BY语句的HAVING用法
SELECT col_name FROM tb_name
GROUP BY (col_name/value) [HAVING where_condition]
*HAVING 分组条件*
1.没有 HAVING 时，GROUP BY 是对全体记录进行分组并显示结果。
2.有 HAVING 时，对全体记录分组后只把符合HAVING条件的记录显示出来。
3.分组用的是HAVING要求HAVING后的条件要么是聚合函数，要么字段在前面出现
举例：SELECT username,age FROM users GROUP BY age HAVING count(id)>=2;
order by
SELECT * FROM tb_name
ORDER BY col1_name asc/desc;
限制查询结果返回数量
SELECT * FROM tb_name
LIMIT number[,number];
exp:
SELECT * FROM users LIMIT 2;
从第一条开始,返回两条【SELECT语句从0开始编号】
SELECT * FROM users LIMIT 2,3 ;
从第三条开始【第一个为0】，返回三条
SQL子查询
指出现在【其他SQL语句内】的SELECT子句
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
其中，SELECT * FROM t1 ...称为Outer Query[外查询](或者Outer Statement)
SELECT column1 FROM t2 称为Sub Query[子查询]
1.子查询指嵌套在【查询内部】，且必须始终出现在【圆括号内】。
2.子查询可以包含多个关键字或者条件，如DISTINCT,GROUP BY,ORDER BY,LIMIT,函数等
3.子查询的外层查询可以是：【SELECT,INSERT,UPDATE,SET或DO】
4.子查询可以返回值：标量、一行、一列或者子查询
SQL子查询应用
查询高于平均单价的商品有哪些？
方法一：
SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;
************************************************
SELECT goods_id,goods_name,goods_price
FROM tdb_goods
WHERE goods_price >5391.30;
方法二【SQL子查询】：
SELECT goods_id,goods_name,goods_price
FROM tdb_goods
WHERE goods_price > (
SELECT ROUND(AVG(goods_price),2) FROM tdb_goods
);
1.当子查询返回多个结果时，使用ANY/SOME/ALL中的一个确定最终结构
2.ANY和SOME用法相同
3.用法如下：
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>=
ANY (SELECT goods_price FROM tdb_goods WHERE goods_cate='超级本');
查询比价格最低的超级本 价格高的商品
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>=
ALL (SELECT goods_price FROM tdb_goods WHERE goods_cate='超级本');
查询比所有超级本 价格都高的商品
计算记录长度
LENGTH('Hello') -> 5
提取指定长度记录
LEFT('Hello world', 4) -> 'Hell'
2017/3/16
将一张表的数据插入到另一张表
INSERT tb_name ［（col1_name）］
SELECT col2_name FROM tb2_name
建立内连接，参照某一表更新另一表中的记录
UPDATE tb1_name
INNER JOIN tb2_name
ON tb1.col1_name=tb2.col1_name
SET tb1.col2_name=tb2.col2_name
多表更新
方法一:
1.先创建表
2.将记录写入该表
3.用内连接参照该表将原表更新
方法二:
1.创建表并将另一表的数据写入
exp:
CREATE TABLE tdb_goods_brands(
brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
brand_name VARCHAR(40) NOT NULL
)
SELECT brand_name FROM tdb_goods GOURP BY brand_name;
2.用内连接参照该表将原表更新
3.用CHANGE修改参照表列字段名称和类型，目的是瘦身数据表
4.小技巧：两个表建立连接时 若有名称相同的字段 需要用AS起别名
exp：
UPDATE tdb_goods AS g
INNER JOIN tdb_goods_brands AS b
ON g.brand_name = b.brand_name
SET g.brand_name = b.brand_id
2017/3/17
内连接
在MySQL中JOIN,INNER JOIN,CROSS JOIN是等价的交集 仅显示A、B两表符合连接条件的记录。不符合连接条件的记录不显示。
SELECT tb1.col1,tb2.col1
FROM tb1
INNER JOIN tb2
ON tdb1.col2=tdb2.col2
exp:
SELECT goods_id,goods_name,cate_name
FROM tdb_goods
INNER JOIN tdb_goods_cates
ON tdb_goods.cate_id=tdb_goods_cates.cate_id
\G;
左外连接和右外连接
1.LEFT JOIN：显示左表全部和左右符合连接条件的记录
2.RIGHT JOIN：显示左右符合连接条件的记录和右表全部记录
3.若某字段只存在某一表，则另一表的里字段返回null
多表连接
SELECT tb1.col1,tb2.col1,tb3.col1
FROM tb1
INNER JOIN tb2 ON tdb1.col2=tdb2.col2
INNER JOIN tb3 ON tdb1.col3=tdb3.col2
exp:
SELECT goods_id,goods_name,cate_name,brand_name,goods_price
FROM tdb_goods AS g
INNER JOIN tdb_goods_cates AS t ON g.cate_id=t.cate_id
INNER JOIN tdb_goods_brands AS b ON g.brand_id=b.brand_id
\G;
无限级分类表设计
一张表包含了父类和子类，如何查询父类下有多少子类，子类对应的父类是什么？
mysql> SELECT * FROM tdb_goods_types;
+---------+-----------------+-----------+
| type_id | type_name | parent_id |
+---------+-----------------+-----------+
| 1 | 家用电器 | 0 |
| 2 | 电脑、办公 | 0 |
| 3 | 大家电 | 1 |
| 4 | 生活电器 | 1 |
| 5 | 平板电视 | 3 |
| 6 | 空调 | 3 |
| 7 | 电风扇 | 4 |
| 8 | 饮水机 | 4 |
| 9 | 电脑整机 | 2 |
| 10 | 电脑配件 | 2 |
| 11 | 笔记本 | 9 |
| 12 | 超级本 | 9 |
| 13 | 游戏本 | 9 |
| 14 | CPU | 10 |
| 15 | 主机 | 10 |
+---------+-----------------+-----------+
15 rows in set (0.03 sec)
查询子类对应的父类：
SELECT s.type_id,s.type_name,p.type_name
FROM tdb_goods_types AS s
LEFT JOIN tdb_goods_types AS p
ON s.parent_id=p.type_id;
查询父类下有多少个子类
SELECT p.type_id,p.type_name,COUNT(s.type_name) AS child_name
FROM tdb_goods_types AS p
LEFT JOIN tdb_goods_types AS s
ON p.type_id=s.parent_id
GROUP BY p.type_name
ORDER BY p.type_id;
多表连接删除重复项
DELETE t1 FROM tdb_goods AS t1
LEFT JOIN (
SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING COUN
T(goods_name)>1) AS t2
ON t1.goods_name=t2.goods_name
WHERE t1.goods_id > t2.goods_id;
MySQL中的函数分类
2017/3/20
字符函数
CONCAT 字符连接
mysql> SELECT CONCAT('a','b');
+-----------------+
| CONCAT('a','b') |
+-----------------+
| ab |
+-----------------+
CONCAT_WS 使用指定的分隔符进行字符连接
mysql> SELECT CONCAT_WS('|','a','b');
+------------------------+
| CONCAT_WS('|','a','b') |
+------------------------+
| a|b |
+------------------------+
FORMAT 数字格式化
mysql> SELECT FORMAT(123.45,1);
+------------------+
| FORMAT(123.45,1) |
+------------------+
| 123.5 |
+------------------+
LOWER/UPPER 转换成小写字母/大写字母
mysql> SELECT LOWER('ABC');
+--------------+
| LOWER('ABC') |
+--------------+
| abc |
+--------------+
mysql> SELECT UPPER('abc');
+--------------+
| UPPER('abc') |
+--------------+
| ABC |
+--------------+
LEFT/RIGHT 获取左侧字符/右侧字符
mysql> SELECT LEFT('abcde',3);
+-----------------+
| LEFT('abcde',3) |
+-----------------+
| abc |
+-----------------+
mysql> SELECT RIGHT('abcde',3);
+------------------+
| RIGHT('abcde',3) |
+------------------+
| cde |
+------------------+
LEGTH 获取字符串长度
mysql> SELECT LENGTH('abcde');
+-----------------+
| LENGTH('abcde') |
+-----------------+
| 5 |
+-----------------+
LTRIM/RTRIM 删除前导空格/后续空格
mysql> SELECT LTRIM(' abcde ');
+----------------------+
| LTRIM(' abcde ') |
+----------------------+
| abcde |
+----------------------+
mysql> SELECT RTRIM(' abcde ');
+----------------------+
| RTRIM(' abcde ') |
+----------------------+
| abcde |
+----------------------+
TRIM 同时删除前后两边的空格
小技巧：删除指定的前导和后续的字符,但不能删除中间的字符
mysql> SELECT TRIM(LEADING'?' FROM '??MYSQL????');
+-------------------------------------+
| TRIM(LEADING'?' FROM '??MYSQL????') |
+-------------------------------------+
| MYSQL???? |
+-------------------------------------+
mysql> SELECT TRIM(TRAILING '?' FROM '??MYSQL????');
+---------------------------------------+
| TRIM(TRAILING '?' FROM '??MYSQL????') |
+---------------------------------------+
| ??MYSQL |
+---------------------------------------+
mysql> SELECT TRIM(BOTH '?' FROM '??MY???SQL????');
+--------------------------------------+
| TRIM(BOTH '?' FROM '??MY???SQL????') |
+--------------------------------------+
| MY???SQL |
+--------------------------------------+
REPLACE 替换指定字符
mysql> SELECT REPLACE('??MY???SQL????','?','-');//'?'表示要替换的字符，'-'表示替换成的字符
+-----------------------------------+
| REPLACE('??MY???SQL????','?','-') |
+-----------------------------------+
| --MY---SQL---- |
+-----------------------------------+
SUBSTRING 截取字符串
mysql> SELECT SUBSTRING('ABCDEF',3,2);//3表示从第3个字符开始截取，2表示截取2个字符
+-------------------------+
| SUBSTRING('ABCDEF',3,2) |
+-------------------------+
| CD |
+-------------------------+
[NOT]LIKE 模糊匹配
(%)：代表任意个字符，0个或多个
(_)：代表任意一个字符，只有一个
小技巧：带有%的模糊查找：
mysql> SELECT * FROM test
-> WHERE first_name LIKE '%1%%' ESCAPE'1';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| tom% | 123 |
+------------+-----------+
数值运算符
SELECT CEIL(3.01) --->4 //有n.xx 都是n+1 进一取整 向上取整
SELECT FLOOR(3.99) --->3 //取n.xx 都是n 舍一取整 向下取整
SELECT 3 DIV 4 ---> 0 //整数除法
SELECT 3/4 --->0.75 //除法
SELECT 21 MOD 2 --->1 //取余数(取模)整数,小数都可以
SELECT 21 % 2 --->1 //取余数(取模)整数,小数都可以
SELECT POWER(3,4) --->81 //3的4次方 幂运算
SELECT ROUND(3.1415926,4) --->3.142 //四舍五入
SELECT TRUNCATE(123.89,1) --->123.8 //截取小数点后的位置
SELECT TRUNCATE(123.89,0) --->123 //截取小数点后的位置,0位为整数部分
SELECT TRUNCATE(123.89,-1) --->120 //截取-1,从个位起去掉后面的数值替换为0
2017/3/21
比较运算符
SELECT 15 BETWEEN 1 AND 20 ---> 1 //15 在1到20之间 ,返回值是1
SELECT 15 NOT BETWEEN 1 AND 20 --->0 // 15在1到20之间,条件不成立 返回值是0
SELECT 10 IN(5,10,15) ---> 1 //值是否在给定的数值中,如果在返回1,不在返回0 条件成立为1不成立为0
SELECT 10 NOT IN(5,10,15) ---> 0 //数值是否不在给定的数值中,如果不在返回1,在返回0 条件成立为1不成立为0
SELECT NULL IS NULL --->1// 空是空 条件成立返回1,不成立返回0
SELECT '' IS NULL ---> 0 // 除了NULL空其它都是非空 返回都是1
SELECT col1 FROM tb WHERE col2 IS NOT NULL; // 查找表中的非空值
日期时间函数
SELECT NOW(); --->2017-03-21 09:15:34
SELECT CURDATE(); --->2017-03-21
SELECT CURTIME(); --->09:16:36
SELECT DATE_ADD('2016-6-6',INTERVAL 1 YEAR); --->2017-06-06
SELECT DATE_ADD('2016-6-6',INTERVAL -1 YEAR); --->2015-06-06
//后面的单位可以是WEEK,MONTH,YEAR
SELECT DATEDIFF('2016-6-6','2015-6-6');--->366
SELECT DATE_FORMAT('2016-6-6','%m/%d/%Y');--->06/06/2016
SELECT DATE_FORMAT('2016-6-6','%M/%D/%Y');--->June/6th/2016
信息函数
聚合函数
加密函数
SELECT MD5('ying chao');--->6bf3c53e9f52b5d08dda3db79f173317
SELECT PASSWORD('ying chao');--->*B550EC7159D944939E18AED502C75119FACB32FC
小技巧：修改MYSQL登录密码：
SET PASSWORD=PASSWORD('123456');
2017/3/29
创建不带参数的自定义函数
原始查询时间
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2017-03-29 08:48:43 |
+---------------------+
中文显示时间：
mysql> SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日 %h小时%i分%s秒');
+---------------------------------------------------------+
| DATE_FORMAT(NOW(),'%Y年%m月%d日 %h时%i分%s秒') |
+---------------------------------------------------------+
| 2017年03月29日 08时50分58秒 |
+---------------------------------------------------------+
将中文显示封装成自定义函数：
mysql> CREATE FUNCTION f1()
-> RETURNS VARCHAR(30)
-> RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %h时%i分%s秒');
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT f1();
+-----------------------------------+
| f1() |
+-----------------------------------+
| 2017年03月29日 08时55分08秒 |
+-----------------------------------+
创建带参数的自定义函数
创建一个求平均值的函数
mysql> CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
-> RETURNS FLOAT(10,2) UNSIGNED
-> RETURN (num1+num2)/2;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT f2 (15,20);
+------------+
| f2 (15,20) |
+------------+
| 17.50 |
+------------+
创建具有复合结构函数体的自定义函数
1.修改分隔符：DELEMITER 分隔符。当编写函数体内容的时候，需要使用 DELIMITER 将分隔符先修改为别的，否则编写语句的时候写到 ';' 的时候会直接执行，导致函数编写失败
用法：DELIMITER // --->将命令分隔符;改为//
2.当函数体内需要执行的是多条语句时，要使用BEGIN...END语句
mysql> CREATE FUNCTION adduser(username VARCHAR(20))
-> RETURNS INT UNSIGNED
-> BEGIN
-> INSERT test(first_name) VALUES(username);
-> RETURN LAST_INSERT_ID();
-> END
-> //
mysql> SELECT adduser ('jj');--->创建了jj的值
+----------------+
| adduser ('jj') |
+----------------+
| 7 |
+----------------+
2017/3/31
MYSQL存储过程
2017/4/5
创建带有IN类型参数的存储过程
mysql> SELECT * FROM test; --->查询原始表数据
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
| 1 | A | B |
| 2 | C | D |
| 3 | tom% | 123 |
| 4 | NULL | 11 |
| 5 | ee | ff |
| 6 | 88 | 99 |
| 7 | jj | NULL |
| 8 | ee | NULL |
+----+------------+-----------+
mysql> DELIMITER //
mysql> CREATE PROCEDURE rubi(IN p_id INT UNSIGNED)
-> BEGIN
-> DELETE FROM test WHERE id =p_id;
-> END
-> //
mysql> DELIMITER ;
mysql> CALL rubi (4);
Query OK, 1 row affected (0.06 sec)
mysql> SELECT * FROM test;--->查询修改后的表数据
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
| 1 | A | B |
| 2 | C | D |
| 3 | tom% | 123 |
| 5 | ee | ff |
| 6 | 88 | 99 |
| 7 | jj | NULL |
| 8 | ee | NULL |
+----+------------+-----------+
创建带有IN和OUT类型参数的存储过程
mysql> DELIMITER //
mysql> CREATE PROCEDURE rubiarn(IN p_id INT UNSIGNED,OUT namenum INT UNSIGNED)
-> BEGIN
-> DELETE FROM test WHERE id =p_id;
-> SELECT COUNT(id) FROM test INTO namenum;--->INTO 含义就是将 SELECT 语句结果的表达式返回到namenum变量中
-> END
-> //
mysql> DELIMITER ;
mysql> CALL rubiarn (6,@num);
mysql> SELECT @num;
+------+
| @num |
+------+
| 6 |
+------+
2017/4/11
MySQL支持的存储引擎
并发控制
当多个连接对记录进行修改时保证数据的一致性和完整性。系统使用锁系统来解决这个并发控制，这种锁分为：
锁的力度（也叫锁的颗粒）
表锁:一张表只有一个锁
行锁：一行一个锁
索引=书的目录
1.CSV存储引擎不支持索引
2.BlackHole:黑洞引擎，写入的数据都会消失，一般用做数据复制的中继。
3.使用最广泛的两种存储引擎：MyISAM/InnoDB
4.MyISAM：适用于事务的处理不多的情况。
5.InnoDB：适用于事务处理比较多，需要有外键支持的情况。
设置存储引擎
(1)通过修改MySQL配置文件实现
- default-storage-engine = engine
(2)通过创建数据表命令来实现
mysql> CREATE TABLE tp1(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT
-> )ENGINE=MYISAM;
Query OK, 0 rows affected (0.04 sec)
mysql> SHOW CREATE TABLE tP1;
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------+
| tP1 | CREATE TABLE `tp1` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------+
(3)通过修改数据表命令来实现
mysql> ALTER TABLE tp1
-> ENGINE=InnoDB;
Query OK, 0 rows affected (0.34 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE tP1;
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------+
| tP1 | CREATE TABLE `tp1` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------+
导入数据，要把文件类型设置为utf8，命名时写成.sql
SELECT * FROM movies WHERE name BETWEEN 'A' AND 'J';
This statement filters the result set to only include movies with names that begin with letters "A" up to but not including "J".
SELECT * FROM movies WHERE year BETWEEN 1990 AND 2000;
In this statement, the BETWEEN operator is being used to filter the result set to only include movies with years between 1990 up to and including 2000.
The UNION clause allows us to utilize information from multiple tables in our queries.
The UNION ALL clause allows us to utilize information from multiple tables in our queries, including duplicate values.
INTERSECT is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement.
EXCEPT returns distinct rows from the first SELECTstatement that aren’t output by the second SELECT statement
MySQL数据类型及长度限制
what's VIEW
CREATE VIEW
UPDATE and DEL VIEW
使用BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的
UNION 和 UNION ALL
GROUP BY 配合 WITH ROLLUP 进行全局统计
MySQL 正则表达式
MYSQL常用优化总结
优化语句很多，需要注意的也很多，针对平时的情况总结一下几点：
1、有索引但未被用到的情况（不建议）
(1) Like的参数以通配符开头时
尽量避免Like的参数以通配符开头，否则数据库引擎会放弃使用索引而进行全表扫描。
以通配符开头的sql语句，例如：select * from t_credit_detail where Flistid like '%0'\G
image
这是全表扫描，没有使用到索引，不建议使用。
不以通配符开头的sql语句，例如：select * from t_credit_detail where Flistid like '2%'\G
image
很明显，这使用到了索引，是有范围的查找了，比以通配符开头的sql语句效率提高不少。
(2) where条件不符合最左前缀原则时
例子已在最左前缀匹配原则的内容中有举例。
(3) 使用！= 或 <> 操作符时
尽量避免使用！= 或 <>操作符，否则数据库引擎会放弃使用索引而进行全表扫描。使用>或<会比较高效。
select * from t_credit_detail where Flistid != '2000000608201108010831508721'\G
image
(4) 索引列参与计算
应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
select * from t_credit_detail where Flistid +1 > '2000000608201108010831508722'\G
image
(5) 对字段进行null值判断
应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： 低效：select * from t_credit_detail where Flistid is null ;
可以在Flistid上设置默认值0，确保表中Flistid列没有null值，然后这样查询： 高效：select * from t_credit_detail where Flistid =0;
(6) 使用or来连接条件
应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如： 低效：select * from t_credit_detail where Flistid = '2000000608201108010831508721' or Flistid = '10000200001';
可以用下面这样的查询代替上面的 or 查询： 高效：select from t_credit_detail where Flistid = '2000000608201108010831508721' union all select from t_credit_detail where Flistid = '10000200001';
image
2、避免select *
在解析的过程中，会将'*' 依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。
所以，应该养成一个需要什么就取什么的好习惯。
3、order by 语句优化
任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。
方法：
1.重写order by语句以使用索引；
2.为所使用的列建立另外一个索引
3.绝对避免在order by子句中使用表达式。
4、GROUP BY语句优化
提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉
低效:
SELECT JOB , AVG(SAL)
FROM EMP
GROUP by JOB
HAVING JOB = ‘PRESIDENT'
OR JOB = ‘MANAGER'
高效:
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB = ‘PRESIDENT'
OR JOB = ‘MANAGER'
GROUP by JOB
5、用 exists 代替 in
很多时候用 exists 代替 in 是一个好的选择： select num from a where num in(select num from b) 用下面的语句替换： select num from a where exists(select 1 from b where num=a.num)
6、使用 varchar/nvarchar 代替 char/nchar
尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
7、能用DISTINCT的就不用GROUP BY
SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID
可改为：
SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10
8、能用UNION ALL就不要用UNION
UNION ALL不执行SELECT DISTINCT函数，这样就会减少很多不必要的资源。
9、在Join表的时候使用相当类型的例，并将其索引
如果应用程序有很多JOIN 查询，你应该确认两个表中Join的字段是被建过索引的。这样，MySQL内部会启动为你优化Join的SQL语句的机制。
而且，这些被用来Join的字段，应该是相同的类型的。例如：如果你要把 DECIMAL 字段和一个 INT 字段Join在一起，MySQL就无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行。（两个表的字符集有可能不一样）
查看SQL语句运行时间
- 查一下profile是不是打开了，默认是不打开的。
mysql> show profiles; - 开启profile，然后测试
mysql> set profiling=1; - 正常输入SQL语句
- 查看SQL语句运行时间
show profiles; - 查看第n条语句运行时间
show profile for query 1;
