目录
创建数据库
创建数据库、表时，为了规避关键字，可以使用反引号(`)解决。
查询、删除数据库
SHOW DATABASES显示当前数据库服务器下所有数据库
SHOW CREATE DATABASE db_name显示数据库创建的信息
DROP DATABASE [IF EXISTS] db_name数据库删除语句（慎用！！）
备份恢复数据库
备份（在dos执行）
mysqldump -u root -p -B db_name >d:\\backup.sql此处文件保存的位置、名字可以自定义
如果只想备份数据库中的某张表，则执行如下语句：
mysqldump -u root -p db_name table_name>d:\\backup.sql注意：一定要把-B删掉，否则系统会认为table_name是另一个数据库，会报错。
恢复（在MySQL命令行中执行）
source d:\\backup.sql也可以将备份文件打开，将其中的sql语句直接运行
创建表
CREATE TABLE table_name
(
field1 datatype（数据类型后面可以指定长度以及有无符号）,
field2 datatype,
field3 datatype
)character set 字符集 collate 校对规则 engine 存储引擎
field:指定列名 datatype：指定列类型（字段类型） character set:如不指定则为所在数据库字符集 collate:如不指定则为所在数据库校对规则 engine:引擎注意：创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。
列类型
数值类型
在能够满足需求的情况下，尽量选择占用空间小的类型；
未指定unsigned的情况下，默认是有符号的；
bit型显示的数据为二进制，但查询时仍然可按照十进制查询。
整型
用得较多的为int[4个字节]。
bit型在用于二进制数据时十分节省空间。
小数类型
用得较多的为double[双精度 8个字节]，decimal[M,D]（大小不确定）
可以支持更加精确的小数位，M是位数（精度）的总数，D是小数点（标度）后面的位数。
也可以存非常大的整数（超过bigint）。
如果D为0：值没有小数点或分数部分。
M最大65，被省略时默认是10；
D最大30，被省略时默认是0。
文本（字符串）、二进制类型
文本类型
常用（注意大小是否固定）
char
varchar
编码不同，大小不同（1-3个字节用于记录大小）
gbk编码两个字节表示一个字符。size：（65535-3）/2=32766
utf8编码表示中文字符一般为3个字节。size：（65535-3）/3=21844
mediumtext
下图中没有，大小为0-2^24
字符串使用细节
1.括号内的size（假设为6）是字符长度，字符占用字节数由所用编码决定，不管是中文还是字母都最多存放6个。
2.char（6）是定长，即使你插入的数据只有一个字符，也会占用6个字符的空间；而varchar（6）是变长，会按照实际占用空间分配（注意：varchar还会用1-3个字节来记录存放内容的长度）
3.如果数据是定长用char好。查询速度：char>varchar
4.存放文本时可以使用text类型，注意不能有size
二进制数据类型
用的不多
时间日期类型
常用（注意下方图片中的时间日期格式）
datetime
timestamp（自动更新）
修改表
修改列名
ALTER TABLE 表名 CHANGE 原字段名 新字段名 字段类型 约束条件CRUD语句
Insert语句
如果希望指定某个值为默认值，可以在创建表时设定。
Update语句
Delete语句
Select语句
单表
每个 column name 都可以用 as 别名，各个 as 用逗号隔开。
where子句中 不能使用别名,不能使用聚合函数！！！
between...and是闭区间
like使用示例：
select * from table_name
where `name` like '韩%'
上面这一行也可以中的%表示韩以后匹配任意个字符
若使用_则表示匹配任意一个字符函数
统计函数
count函数
两者差在是否排除null
在想要统计的列名前加上distinct可以去重
sum函数
返回满足where条件的行的和，一般在数值列使用
注意：sum仅对数值起作用，否则会报错；对多列分别求和时，“，”符号不能少
avg函数
返回满足where条件的一列平均值
round函数
返回按照指定小数位数进行四舍五入运算的结果，除数值外，也可对日期进行四舍五入运算
select round(number,num_digits)如果 digits 大于 0，则四舍五入到指定的小数位。
如果 digits 等于 0，则四舍五入到最接近的整数。
如果 digits 小于 0，则在小数点左侧进行四舍五入。
如果round函数只有参数number,等同于digits 等于 0。
举例
round(3.1415926,2)=3.14;
round(3.1415926,3)=3.142;
select round(193,-2)from dual; 200
select round(193,-1)from dual;190
select round(193,-3)from dual;0max/min函数
返回满足where条件的最大/最小值
group、having函数
having即添加查询后显示时的筛选条件
当group by 与聚合函数配合使用时，功能为分组后计算
当group by 与having配合使用时，功能为分组后过滤
当group by 与聚合函数，非聚合字段同时使用时，非聚合字段的取值是第一个匹配到的字段内容，即id小的条目对应的字段内容。
字符串函数
练习：以首字母小写的方式显示所有员工emp表的姓名
数学函数
rand（）返回一个随机浮点值（范围在0-1.0）之间。若已指定一个整数参数seed，则将会产生重复的一个随机值。
时间日期相关函数
记住日期作为字符串要用单引号括起来！
细节说明
在实际开发中经常使用int来保存一个时间戳，随后使用from_unixtime()进行转换。
加密和系统函数
user()回复格式：用户名@ip地址
流程控制函数
用if语句判断是否为空要用is，而不是用等号；判断不为空则用is not
-- 简单Case函数
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
CASE sex WHEN ‘1’ THEN ‘男’ WHEN ‘2’ THEN ‘女’ ELSE ‘其他’ END
-- 搜索Case函数
CASE WHEN [expr] THEN [result1]…ELSE [default] END
CASE WHEN sex = ‘1’ THEN ‘男’ WHEN sex = ‘2’ THEN ‘女’ ELSE ‘其他’ END搜索case 可以写判断式
分页查询（limit）
基本语法：select...limit start,rows
表示从start+1行开始取，取出rows行，start从0开始计算
重点看看公式！
注意几种语句的顺序！很重要，顺序错了就会报错
多表
多表查询的条件不能少于表的个数-1，否则会出现笛卡尔集
在默认情况下：当两个表查询时，规则：
1.从第一张表中，取出一行和第二张表的每一行进行组合，返回结果【含有两张表的所有列】
2.一共返回的记录数第一张表行数*第二张表的行数
3.这样多表查询默认处理返回的结果，称为笛卡尔集
4.要在where中写出必要的过滤条件：
自连接
指在同一张表的连接查询（即将同一张表看作两张表）
自连接的特点1.把同一张表当做两张表使用 2.需要给表取别名：表名 表别名 3.列名不明确，可以指定列的别名：列名 as 列的别名
SELECT worker.ename AS'职员名'，boss.ename AS'上级名'
FROM emp worker,emp boss
WHERE worker.mgr boss.empno;
SELECT FROM emp;子查询
嵌入在其它sql语句中的select语句，也叫嵌套查询
单行子查询
指只返回一行数据的子查询语句
SELECT *
FROM emp
WHERE deptno = (
SELECT deptno
FROM emp
WHERE ename ='SMITH'
)
-- 查询和fakenews在同一天录入的数据
SELECT *
from mes
where date(send_time) = date(
(SELECT send_time
FROM mes
where content ='fakenews')
)AND id>1 -- 也可以用HAVING id>1多行子查询（in）
指返回多行数据的子查询，使用关键字in
SELECT ename,job,sal,deptno
FROM emp
WHERE job IN (
SELECT DISTINCT job
FROM emp
WHERE deptno = 10
) AND deptno != 10 --（不等号也可以用“<>”表示）
--查询整个公司其他部门和十号部门工作相同的人员（不包括10号部门本身）子查询可以当作一张临时表(temp)来使用：
SELECT
goods_id,
ecs_goods. cat_id,--此处注意要明确是哪一张表的cat_id，否则两张表都有会报错
goods_name,
shop_price
FROM(
SELECT cat_id,MAX( shop_price ) AS max_price
FROM ecs_goods
GROUP BY cat_id
) temp,ecs_goods
WHERE
temp.cat_id = ecs_goods.cat_id
AND temp.max_price = ecs_goods.shop_priceall和any
比如要找出公司其他部门比某一个部门所有人工资都高的员工，则可以用all；要找出比某一个部门任意一个人工资高的员工，就可以使用any。
当然，比所有的高即比最高的高，此时也可使用max；比任意的高即比最低的高，此时也可使用min。
多列子查询
表.*表示将该表的所有列都显示出来，可以简化 SQL 语句。
合并查询
在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union all，union
1.union all用于取得两个结果集的并集。当使用该操作符时，不会取消重复行
2.union 会自动去重
表复制及去重
自我复制数据（蠕虫复制）
有时，为了对某个SQL语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据。
INSERT into my_tab01
SELECT * FROM my_tab01去重通过创建临时表，使用 distinct 关键字，再通过表复制完成，最终使用 drop 删除临时表即可
表的外连接（join on）
当只有join时，后面可以用on也可以用where
1.左外连接（左侧的表完全显示）
我们先执行join： stu join class on classid = class.id (join的详细语法介绍过)
再执行left join：stu left join class on classid = class.id 后变为：
由于左边的表stu中的数据在join后，第三行没有在新表出现，left join会将没在新表出现的数据和NULL作拼接，并作为新的行加进来 
如果将stu和class顺序换一下，执行 class left join stu on classid = class.id 结果会是什么呢？
这个时候，左边的表是class，它的数据在新表中都存在，所以和用join是一样的！！
通过这个例子，再回过头去看上面的话，应该就可以理解。
left join 左右两边的表是不能交换的，交换的话，意思会变；join左右两边的表可以交换，意思不变
2.右外连接（右侧的表完全显示）
它们的常用语句结构：
select .. from 表1 left (right) join 表2 on 条件约束
用于确保数据库的数据满足特定的商业规则
包括：not null,unique,primary,foreign key 和 check 五种。
primary key（主键）
细节说明
-
primary key:不能重复而且不能为null.
-
一张表最多只能有一个主键，但可以是复合主键
当定义为复合主键时，只有两个字段都相同才算作重复（所以上图的添加时有效的）
-
主键的指定方式有两种 （1）直接在字段名后指定：字段名primary key （2）在表定义最后写primary key（列名）
-
使用desc表名，可以看到primary key的情况。
-
在实际开发中，每个表往往都会有主键。
not null（非空）
如果在列上定义了not null,那么当插入数据时，必须为列提供数据。 字段名 字段类型 not null
unique（唯一）
当定义了唯一约束后，该列值是不能重复的。 字段名 字段类型 unique
细节说明
1.如果没有指定not null,则unique字段可以有多个null；unique not null 使用效果类似primary key 2.一张表可以有多个unique字段
foreign key（外键）
用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主 键约束或是unique约束。当定义外键约束后，要求外键列数据必须在主表的主 键列存在或是为null
细节说明
-
外键指向的表的字段，要求是primary key或者是unique
-
表的类型是innodb,这样的表才支持外键
-
外键字段的类型要和主键字段的类型一致（长度可以不同）
-
外键字段的值，必须在主键字段中出现过，或者为null[前提是外键字段允许为null]
-
一旦建立主外键的关系，数据不能随意删除了，当主外键有相关数据时会删除失败
check
用于强制行数据必须满足的条件
在MySQL中实现check的功能，一般是在程序中控制或者通过触发器完成
enum（枚举）
自增长
细节说明
-
一般来说自增长是和 primary key 配合使用的
-
自增长也可以单独使用[但是需要配合一个unique]
-
自增长修饰的字段为整数型的(虽然小数也可以但是非常非常少这样使用)
-
自增长默认从1开始,你也可以通过如下命令修改 :
alter table 表名 auto_increment = 新的开始值; -
如果你添加数据时,给自增长字段(列)指定的有值，则以指定的值为准。一般来说，如果指定了自增长，就按照自增长的规则来添加数据。
索引
因为全表扫描导致没有索引会慢，使用索引会形成一个索引的数据结构（比如二叉树，B+树）
主键索引
主键自动地为主索引
添加主键索引
alter table 表名 add primary key （字段）
删除主键索引
alter table 表名 drop primary key唯一索引(UNIQUE)
普通索引(INDEX)
全文索引（FULLTEXT）[适用于MylSAM]
一般开发中，不使用MySQL自带的全文索引，开发中考虑使用全文搜索 Solr 和 ElasticSearch
索引的代价：
-
磁盘占用
-
对 dml(update delete insert) 语句的效率影响
查询索引
show index from 表名
show keys from 表名添加索引
添加唯一索引
create unique index id_index on 表名 （字段）
添加普通索引
方式一
create index id_index on 表名（字段）
方式二
alter table 表名 add index id_index （字段）
如何选择
1.如果某列的值是不会重复的，则优先考虑使用unique索引，否则使用普通索引删除索引
drop index 索引名称 on 表名创建索引的规则
-
较频繁的作为查询条件的字段应该创建索引
-
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件（比如性别）
-
更新较为频繁的字段不适合创建索引
-
不会出现在where子句中的字段不该创建索引
事务
事务用于保证数据的一致性，它由一组相关的dml语句组成，该组dml语句要么全部成功，要么全部失败。（比如转账）
-
如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
-
如果开始一个事务，你没有创建保存点。你可以执行rollback,默认就是回退到你事务开始的状态。
-
你也可以在这个事务中（还没有提交时），创建多个保存点。比如：savepoint aaa;执行dml,savepoint bbb;
-
你可以在事务没有提交前，选择回退到哪个保存点
-
mysql的事务机制需要innodb的存储引擎，myisam不支持。
-
开始一个事务start transaction,也可以写作set autocommit=off;
隔离级别
1.多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。（通俗解释） 2.如果不考虑隔离性，可能会引发如下问题：
脏读(dirty read)
当一个事务读取另一个事务尚未提交的修改时，产生脏读
不可重复读(nonrepeatable read)
同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此事发生不可重复读
幻读(phantom read)
同一查询在同一事务中多次进行，由于其他提交事务所作的插入操作，每次返回不同的结果集，此时发生幻读
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
