Mysql总结

一、简介

1. 数据库DataBase
2. 数据库管理系统DataBaseManageSystem
3. 操作关系型数据库的编程语言SQL

二、SQL

1.单行注释用-- （即两个横杠一个空格）或#（Mysql中独有），

                     多行注释用/*  */

2.SQL语句即可单行书写又可多行书写，最后以；结尾。

三、SQL的分类

DDL 数据定义语言，定义数据库、列、表等。

DML 数据操作语言，对表中的数据进行增删改。

DQL 数据查询语言，查询数据库中表的记录（数据）

DCL 数据控制语言，定义数据库的访问权限和安全级别及创建用户。

四、DDL

（1）数据库

查看：show databases;

创建：create database [if not exists] 数据库名；

使用：use 名；

删除：drop database[if exists] 数据库名；

查看当前使用的数据库：select database（）；

(2) 表

查看表结构：desc 名；

查看当前数据库的所有表的名称：show tables；

创建表：create table[if not exists]名(

字段 类型，

字段 类型，

字段 类型）；

注：数据类型包括数值类型，字符串类型，日期类型。

        数值类型又包括int,doule

        字符串类型：char(定长字符串），varchar(变长字符串）[单引号，双引号都可]

        日期类型：date(年月日）

删除：drop table[if exists]名；

*修改表结构：

        改表名：alter table M rename to N;

        添加一列: alter table M add 字段 类型；

        改数据类型：alter table M modify 列名 新类型；

        改列名及数据类型：alter table M change 列名 新列名 新类型；

        删除列：alter table M drop 列名；

五、DML

（1）增：insert into M (...) values (...);

（2）删：delete from M where ...;

（3）改：update M set 字段=？，... where ...;

六、DQL

（1）简单查询

select * from M;

select distinct L from M[as]m;(去重）

注：as用于给表、列取别名，也可以省去。

（2）条件查询（where）

like 占位符-->模糊查询 下划线_代表一个字符，%代表多个任意字符。

并且用and，或者用or，在...之间用between...and，不等于用！=或<>。

SQL语句中日期可以比较。

（3）排序查询（order by）

asc 升序（默认）

desc 降序

（4）分组查询（group by...having...）

聚合函数：将一列作为整体，纵向计算。

聚合函数包括：count（列的数目）、sum（总和）、max、min、avg

注：NULL不参与聚合函数的计算。

select 聚合函数（列名） from M ;

分组后的查询字段是聚合函数和分组字段。

（5）分页查询（limit）

select * from M limit 查询条数；/select * from M limit 起始索引，查询条数；

起始索引从0开始。

起始索引=（页数-1）*查询条数。

七、约束

（1）非空约束 not null

(2) 唯一约束 unique

（3）主键约束 primary key （非空且唯一）

一个表中只有一个主键。

（4）默认约束 default

（5） 外键约束 foreign key

alter table M constraint[FM] foreign key(列键名）reference 主表（列名）;

（6）自增长约束 auto_increment（当列的类型为数字，且唯一）

insert into table2（字段1，字段2）select 字段1，字段2 from table1;

将一张表的数据导入到另一张表中去。

八、正则表达式（DQL)regexp匹配

1.'^'匹配输入字符串的开始位置

2.'$'匹配输入字符串的结束位置

3.'、'匹配除了“\n”外的任何单个字符

4.[...]匹配内所包含的任意一个字符

5.[^...]匹配未包含的任意字符

6.p1|p2|p3匹配p1或p2或p3

7.'*'出现0次或多次

8.'+'出现1次或多次

9.'?'出现0次或1次

10.a{m}匹配m个a        恰好才匹配成功

11.a{m,}匹配m个a或者更多个a

12.a{m,n}匹配m到n个a        包括m和n

九、数据库设计

数据库设计即建立数据库中表的结构和表与表之间的关联关系的过程。

（1）表的关系

1.一对一：在任意一方加外键，且外键唯一unique

2.一对多：在多的一方加外键，指向一的一方的主建

3.多对多：借助中间表

（2）多表联合查询

1.连接查询：

        交叉连接查询--》笛卡尔积即两张表的乘积

select * fromA,B;

        内连接查询：inner ==》多张表的交集

select * fromA,B where ...;-- 隐式内连接

select * from A [inner]join B on ...;--显式内连接

        外连接查询：outer==》包括左外连接查询（left outer join)，右外连接查询(right outer join)，满外连接(union)

注：满外连接：左外 union 右外

即两个查询结果上下拼接。（去重）

2.子查询（包含select嵌套的查询）

返回的数据类型：单行单列（值）、多行单列、多行多列（表）

关键字：All，Some（=Any），In，Exists

子关联查询：表自身进行关联查询，必须给表起别名

select * from A a1,A a2 where...;

十、Mysql函数

（1）聚合函数

由count、sum、max、min、avg【以前学过】

group_concat（[distinct]字段名[order by排序字段asc/desc] [separator ‘分隔符’])

separtor是一个字符串值，默认为，。

eg:select group_concat(name) from student;

（2）数学函数

ABS(x)：返回x的绝对值

ceil（x）：返回大于或等于x的最小整数

floor（x）：返回小于或等于x的最大整数

greatest：取列表最大值

least：取列表最小值

max（...）

min（...）

mod（x，y）取模x除以y的余数

power（x，y）取x的y次方

PI（）

rand（...）返回0到1的随机数

round（x）小数四舍五入

round（x，y）返回指定位数的小数

（3）字符串函数

char_length(...)返回字符串中字符的个数

length（...)返回字节数

concat（...)字符串合并

concat_ws（分隔符，...)指定分隔符进行字符串合并

field（字符串，aaa，bbb，ccc）返回字符串在列表中第一次出现的位置

trim（...）去除字符串两端的空格

mid（字符串，开始截取的位置，取几个字符）

position（'abc' in 'helloabcworld')取字符串a在字符串中的位置

replace（字符串，小字符串1，小字符串2）字符串的替换

reverse（...）字符串的反转

right（hello，3）返回字符串的后几个字符

strcmp（...，...)字符串的比较

ucase（...）将小写转大写

lcase（...）将大写转小写

（4）日期函数

unix_timestamp() 获取时间戳（毫秒值）

unix_timestamp('日期')将一个日期字符串转成毫秒值

from_unixtime(毫秒，'格式'）将毫秒值转成指定格式的日期

curdate()当前的年月日

curtime（）当前的时分秒

current_timestamp()=now（）当前的年月日时分秒

date（'年月日时分秒'）从日期字符串中获取年月日

datediff（A,B)获取日期之间的差值

timediff（A,B)获取时间的差值

date_format('日期字符串'，'格式')日期格式化

str_to_date(字符串，格式）将字符串转为日期

date_sub/add('A日期',interval 2 {days、months、years});将日期进行减法/加法

extract（hour/day/month/year from'日期')从日期中获取年月日时

last_day('日期')给定日期的最后一天

makedate（'年份',天数）获取指定年份和天数的日期

year/month/day/hour/minute/quarter（'日期')从日期中获取年月日时分秒季度

简述          monthname月份名称（英文）

dayname星期几

dayofmonth本月的第几天

dayofweek返回数字：1 -》星期日

dayofyear本年的第几天

week=weekofyear本年的第几周,返回数字，范围在[0,53]

weekday星期几 0-》星期一

（5）控制流函数

if（表达式，值1，值2）

ifnull（v1,v2）如果v1是null，就返回v2

isnull（v1）是null就返回1

nullif（v1,v2）一样就返回null，不一样就返回v1

case...when--

        select case 表达式

        when条件    then结果

        else...end;

(6)窗口函数=开窗函数

window function（expr）over(partition by字段 order by字段）

1.序号函数

row_number()/rank()/dense_rank() over(partition by...order by...)

连续序号/有并列（1.1.3）/1.1.2

2.开窗聚合函数{与聚合函数不同的是其会保留原始的数据}

sum/count/max/min/avg() over(partition by...order by...)

也可区间计算order by后加row between unbounde/3 preceding and current row[1 following].

3.分布函数（算比例）

cume-dist（）分组内小于等于当前rank值的行数/分组内总行数

percent_rank()         （rank-1）/（rows-1）rank为RANK（）函数产生的序号

4.前后函数

返回位于当前行的前n行/后n行的expr值

lag/lead（expr，n）

5.头尾函数

first_value(expr)/last_value(expr)

返回第一个或最后一个的expr值。

注：如果不加order by，会出现错误。

当前行及以上的最后一个==》本行

6.其他函数

nth_value(expr,n)显示expr的第n个值

ntile(n)将分组中的有效数据分成n组

十一、Mysql的视图

（1）视图是一个虚拟表，数据库只存放视图的定义，没有视图的数据，数据在原来的表中。

（2）作用：简化代码，安全

（3）        1.创建视图：create [or replace] view 名 as select_statement;

2.查看表和视图：show full tables;        select * from 视图名；

3.修改视图：alter view 名 as select_statement;

4.重命名视图：rename table 视图名 to 新视图名；

5.删除视图：drop view [if exists] 名；        只是将视图的定义删除了。

十二、Mysql的存储过程

（1）存储过程是一组SQL语句集，类似于Java中的方法。

存储过程就是SQL语言的封装与重用。

（2)存储过程的格式：

delimiter 自定义结束符（$$、\\)

create procedure 存储名

begin sql语句

end 结束符

delimiter ;

调用存储过程：call 存储名；

（3）定义变量

1.局部变量：在begin和end之间有效

声明变量 declare 变量名 类型[default默认值]；

给变量赋值，用select...into。

select  列名 into 变量名 from 表 where...;

查看赋完值的变量：select 变量名；

2.用户变量：@var_name不需要提前声明，使用即声明。

3.系统变量

i.全局变量：在整个数据库中有效，由系统提供。

@@global.var_name

查看全局变量：show global  variables；

查看某一个全局变量：select @@global.名；

修改全局变量的值：set global 名=...;/set @@global.名=...;

ii.会话变量：由系统提供，当前会话连接有效。会话变量没改过即为全局变量。

@@session.var_name

查看：show session variables;

查看某个：select @@session.名；

修改：set session 名=...;/set @@session.名=...;

（4）.存储过程传参

1.in

delimiter$$

create procedure 名(in 参数名->name 类型）

begin 

select* from emp where emp.name=name;

end$$

delimiter;

call 名（参数）；

2.out

delimiter$$

create procedure 名（out 参数名 类型)

begin 

select 列 into 参数名 from...;(给参数赋值）

end$$

delimiter;

call 名（用户变量）；参数会出来，需要有变量接收。

select 用户变量；

3.inout可传入可修改可传出

（5）流程控制--判断

1.if 条件 then 结果

else if 条件 then 结果

else 结果

end if;

2.case 是另一个条件判断语句，类似于Java中的switch。

i.case 变量名

when when_value then 结果

when when_value then 结果

else 结果

end case;

ii.

case

when 条件 then 结果

when条件 then 结果

else 结果

end case;

（6）流程控制--循环

分类：while、repeat、loop

leave 类似于break（跳出），

iterate类似于continue（继续）：即跳出本次循环的剩余代码，进入下一次循环。

i.while

while 循环条件 do 循环体；

end while；

用leave/iterate 必须加标签：if...then leave/iterate 标签；

ii.repeat循环（先执行后判断）

repeat 循环体

until 条件表达式

end repeat;

iii.loop循环（先执行后判断）

loop 循环体

if 条件表达式 then leave;

end if;

end loop;

(7)游标的使用--cursor

声明：declare 名 cursor for select 语句；

打开游标/关闭游标：open/ close 游标名；

取值游标：fetch 游标名 into 变量名，...；将值给了游标。

（8）句柄--异常处理

顺序：变量声明，游标声明，句柄声明

declare continue/exit handler for 原因 statement;

(9)存储函数

主要有返回值

create function 名

return type

begin

end 结束符

十三、Mysql的触发器--一种特殊的存储过程，是对代码的封装与复用。

触发器无需调用，可自动触发，但只有执行insert，update，delete时才可。

触发器只支持行级触发，即对每一行进行增删改。

（1）创建：

create trigger 触发器名 before/after 增删改 on 表名 for each row执行语句；

（2）查看触发器：show triggers；

（3）删除：drop trigger 名；

十四、Mysql的索引

索引是存储引擎用来快速查找记录的一种数据结构。

（1）从实现方式来分：Hash索引、B+Tree索引

从功能来分：单列、组合、全文、空间

（2）单列索引

1.普通索引：

创建表时：index 索引名（列名）

创建表后：create index 索引名 on 表名（列名）；

修改表结构：alter table 名 add index 索引名 （列名）；

查看表中所有索引：show index from 表名；

删除索引：drop index 名 on 表名；/alter table 表名 drop index 名；

2.唯一索引（允许为空）

创建表时：unique 索引名（列名）

创建表后：create  unique index 索引名 on 表名（列名）；

修改表结构：alter table 名 add unique 索引名 （列名）；

3.主键索引：只要列为主键列，自动添加索引。

（3）组合索引（复合索引）--》遵循最左原则

create index 名1 on 名2（列名1，列名2）；

（4）全文索引

（5）空间索引

（6）索引的原理

1.Hash：缺点：不能进行范围查找

2.二叉树：包括根节点，左子树，右子树

3.平衡二叉树：支持范围查找

4.BTree树：B+Tree树双向链表

MyISAM 存储引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。

InnoDB 存储引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据。

十五、Mysql的存储引擎（支持事务和外键）

（1）查看：show engines；

查看某个表用了哪个引擎：show create table 表名；

修改数据库引擎：

alter table 表名 engine=...；

十六、Mysql的事务（transaction）--由存储引擎实现

（1）事务的操作：

开始事务：begin/start transaction；

回滚事务：rollback（失败）；

提交事务：commit；

用set来改变事务的自动提及模式：set autocommit=0；--禁止自动提交

set autocommit=1；--开启自动提交

（2）事务的特性：原子性（一损俱损），一致性，隔离性，持久性

Mysql的默认隔离级别是可重复读（repeatable read）

十七、Mysql的锁机制

（1）MyISAM表锁

加读锁：lock table 名 read；只可读，不可操作其他表。

加写锁：lock table 名 write；表可写可读，别的表什么也不可操作。

解锁：unlock tables；

（2）InnoDB行锁（既支持表锁又支持行锁）

共享锁：select * from M where...lock in share mode;（别的只可读不可写）

排他锁：select * from M where ... for update;(可自动加）

十八、Mysql日志

（1）错误日志

show variables like 'log_error%';

（2）二进制日志

包括了查询之外的其他操作，DDL,DML

show variables like 'log_bin';

（3)查询日志

开启：set global general_log=1;

（4）慢查询日志(记录比较耽误时间的查询）

set global slow_query_log=1;