Mysql基础

一.概念

1.DB：数据库（database），存储数据的“仓库”，保存了一系列有组织的数据。

2.DBMS：数据库管理系统（Database Management System）。数据库是通过DBMS创建和操作的容器

3.SQL：结构化查询语言（Structure Query Language）。专门用来与数据库通信的语言。

二、常用sql语句

（1）基础篇

1.通配符查询： %（匹配多个字符） _匹配单个字符

select * from temp where name like '%a%'; #名称中含a的数据

select * from temp where name like '__a%'; #名称中第三个字符含a的数据

2.ESCAPE关键字指定转义符或\：转义字符

select * from temp where name like '_a_' escape 'a'; #名称中第二个为_的数据

3.安全等于 <=> 可以判断null值，也等价于=

（2）函数篇

字符函数：

1.length 获取参数值的字节个数

2.concat 拼接字符串

3.upper、lower 字母大小写

4.substr、substring 截取字符，索引从1开始，一个参数时表示从该下标往后截取，两个参数表示从该下标截取指定长度

5.instr 两个参数，返回子串参数2在参数1中第一次出现的索引，没有则返回0

6.trim 截取空格

例：select trim('a' from 'aaaaaaaahehcnaiosc ksin aaaaaaaaa');结果为hehcnaiosc ksin 。

7.lpad(参1,数字,参3) 用参3实现左填充指定长度，长度不够就进行截取；

8.rpad(参1,数字,参3) 用参3实现有填充指定长度，长度不够就进行截取；

9.replace(参1,参2,参3)在参1串中将参2替换为参3

数学函数：

1.round() 四舍五入；一个参数时，返回整数，两个参数时，保留指定小数四舍五入

2.ceil() 向上取整

3.floor() 向下取整

4.truncate() 截断

5.mod(a,b) 求余

6.rand()随机取数，返回0-1之间小数

日期函数：

1.now() 返回当前系统日期+时间

2.curdate() 返回系统当前日期，不包含时间

3.curtime() 返回系统当前时间，不包含日期

4.year(a) month(a)/monthname(a) day(a) hour(a) minute(a) second(a) 获取指定部分

5.str_to_date(str,format) 将字符串通过指定格式转化为日期

6.date_format(date,format) 将日期转化为字符串

7.datediff(largerdate,smallerdate) 计算largerdate与smallerdate相差的天数

 

 

其他函数：

1.version()

2.database()

3.user()

4.password(str) 返回str的密码形式

5.md5(str) 返回该字符串的md5加密形式

流程控制函数：

1.if(exp,res1,res2)

2.case 要判断的字段或者表达式

when 常量1 then 要显示的值1或者语句1

when 常量2 then 要显示的值2或者语句2

.....

else 要显示的值n或者语句n;

end

3.case

when 条件1 then 要显示的值1或者语句1

when 条件2 then 要显示的值2或者语句2

.....

else 要显示的值n或者语句n;

end

分组函数：

sum()、avg()、max()、min()、count()

1.sum()和avg()一般处理数值型，max()、min()、count()可以处理任何类型

2.以上分组函数都忽略null值

3.可以和distinct搭配实现去重的运算

4.count()性能问题

MYISAM存储引擎下，count(*)效率高

INNODB存储引擎下，count(*)和count(1)效率差不多，比count(字段)要高一些

分组查询：

select 分组函数，列(要求出现在group by之后)

from 表

【where 条件】

group by 分组的列

【order by 子句】

注意：查询列表必须特殊，要求是分组函数和group by后出现的字段

特点：1.分组查询中的筛选条件分为两种：

数据源 位置 关键字

分组前筛选 原始表 group by子句前 where

分组后筛选 分组后的结果集 group by子句后 having

①分组函数做条件时一定是放在having子句中

②能用分组函数前筛选的，有限考虑使用分组前筛选

2. 支持多个字段查询

（3）多表连接

语法：

sql92语法:了解(等值连接、非等值连接、)

sql99语法

select 查询列表

from 表1 别名

【连接类型】 join 表2别名 on 连接条件

【连接类型】 join 表3别名 on 连接条件

....

【where 筛选条件】

【group by 分组】

【having 筛选条件】

【order by排序列表】

分类：

内连接：inner

外连接：

左外连接：left 【outer】

右外连接：right 【outer】

全外连接：full 【outer】

交叉连接：cross:笛卡尔乘积

（4）分页查询

语法：

select 查询列表

from 表1 别名

【连接类型】 join 表2别名 on 连接条件

【连接类型】 join 表3别名 on 连接条件

....

【where 筛选条件】

【group by 分组】

【having 筛选条件】

【order by排序列表】

limit 【offset,】 size;

注：offset为显示条目的起始索引,从0开始，size表示查询的数目

特点：

①limit语句放在查询语句最后

②要显示的页数page，每页条目size：limit (page-1)*size,size;

（5）子查询

1.标量子查询（单行单列）

2.列子查询（单列多行）

（6）联合查询：将多条查询语句的结果合并成一个结果

语法：

查询语句1

union

查询语句2

union

查询语句3

....

应用：查询结果来自多张表，且多个表没有直接连接关系，但查询信息一致

特点：

1.各个查询语句查询的列数相同且对应

2.union关键字默认是去重的，使用union all包含重复项

三、DML语言

（1）插入语句：insert

语法：1.insert into 表名(column,...) values(value,...) ,(value,...) --可插入多行

2.insert into 表名 set column=value,...

（2）修改语句：update

语法：update 表名 set column=newValue,column=newValue,... where 筛选条件

（3）删除语句：

语句：1.delete from 表名

多表删除时，delete a,b from a [连接类型] join b where 筛选条件

--a,b选择哪个表，删除那个表的数据

2.truncate table 表名; --相当于清空整张表数据，效率比delete from table高

比较：1.delete可加条件，truncate不能加条件

2.truncate比delete效率高一点

3.truncate清空再插入，增长列从1开始；delete清空再插入，增长列继续递增

4.truncate删除没有返回值，delete删除有返回值

5.truncate不能回滚，delete可以回滚

四、DDL语言：数据定义语言

（一）、库的管理

1.库的创建：create database [if not exists] 库名;

2.库的修改(修改库的字符集)：alter database 库名 character set gbk;

3.库的删除：drop database [if exists] 库名;

（二）、表的管理

1.表的创建：create table [if not exists] 表名 (列名 类型[长度 , 约束],

列名 类型[长度 , 约束],

....);

2. 表的修改：alter table 表名 add|drop|modify|change column 类名 [数据类型 约束];

①修改列名：alter table 表名 change column 列名 新列名 数据类型;

②修改列类型或者约束：alter table 表名 modify column 类名 数据类型;

③新增列：alter table 表名 add column 新列名 数据类型 [first] or [after column];

④删除列：alter table 表名 drop column 列名;

⑤修改表名：alter table 表名 rename to 新表名;

3.表的删除：drop table [if exists] 表名;

4.表的复制：①.仅复制表的结构：create table 复制表名 like 表名;

②复制表的结构+数据：create table 复制表名 select [列名,...] from 表名 [条件]

注：跨库的表可以通过[库名.表名]形式获取

（三）数据类型

1.整型：

 

特点：①默认字段为无符号，加上unsigned表示无符号

②插入数值超出范围，报out of range异常，并插入临界值

③不设置长度，会有默认长度，长度为显示长度，搭配zerofill会填充长度

2.小数

 

特点：①M:整数部分+小数部分 ，D:小数部分 ，如果超过范围，则插入临界值

②M和D可以省略;Decimal,M默认为10,D默认为0;float和double,根据插入的数值来决定精度

③定点型的精确度较高

3.字符型

 

特点：char长度固定，效率高；varchar长度可变，效率较慢

（四）、约束：六大约束

1.not null:非空约束

2.default:默认约束，保证该字段有默认值

3.primary key:主键约束，保证字段唯一性且非空,支持组合，但不推荐

4.unique:唯一约束，可以为空，支持组合，但不推荐

5.check:检查约束(mysql不支持),check(条件)

6.foreign key:外键约束(mysql不支持)，保证字段值来自另外一张表的某列值，字段类型必须一致或兼容:refrences 外表(列名，必须为key，一般为主键或唯一)

注：①列级约束：在字段后加上约束，外键约束没有效果(?)

②表级约束:[constraint 外键名] primary key(列名)

[constraint 约束名] unique(列名)

[constraint 约束名] check(列名条件)

[constraint 约束名] primary key(列名) references 外表名(列名)

不支持非空和默认约束

③修改表时添加约束：alter table 表名 modify column 字段名 字段类型 新约束；

alter table 表名 add [constraint 约束名] 约束类型 【外键引用】

④修改表时删除约束：

删除非空：alter table 表名 modify column 列名(数据类型) null

删除默认：alter table 表名 modify column 列名(数据类型)

删除主键：alter table 表名 drop primary key;

删除唯一：alter table 表名 drop index 索引名

删除外键：alter table 表名 drop foreign key 外键名

④标识列：auto_imcrement

create table 表名(列名 int|float key auto_increment)

alter table 表名 modify column int|float key auto_increment

五、TCL语言（Transaction Control Language）

（一）事务:事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句是相互依赖的。

（二）存储引擎：在mysql中用不同的技术存储在文件（或内存）中，show engines来查看mysql支持的存储引擎，常用的存储引擎有innodb，myisam，memory等，innodb支持事务，myisam和memory不支持。

（三）事务的ACID属性：

1.原子性：事务是不可分割的一个工作单位，事物的执行要么全部成功，要么全部失败

2.一致性：事务使数据库从一个一致性状态变换为另一个一致性状态

3.隔离性：一个事务的执行不能被其他事务干扰，并发执行的各个事务不能相互干扰

4.持久性：事务一旦被提交，对数据库的影响是永久性的

（四）事务的隔离级别

1.数据问题

①脏读：对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还 没有被提交的字段之后, 若 T2 回 滚, T1读取的内容就是临时且无效的.

②不可重复度：对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段之后, T1再次读取同一个字段, 值就不同了

③幻读：对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行之后, 如果 T1 再次读取同一个表, 就会多出几行

2.隔离级别

 

注：oracle支持read commited，serializable，默认为read comminted；mysql支持四种隔离级别，默认为repeatable read

（五）相关sql语句

1.隐式事务：比如insert、update、delete

2.显性事务：

set autocommit=0;

start transaction;

sql语句...

commit;

rollback;

 

savepoint 断点

commit to 断点

rollback to 断点

3.设置隔离级别

set session|global transaction isolation level 隔离级别 [别名];

4.查看隔离级别

select @@tx_isolation;

六、视图

（一）好处

1.提高sql语句重用性，效率高

2.保护原始数据安全

（二）创建和增删改查

1.创建：

create view 视图名

as

查询语句；

2.增删改查与sql语句一致

注：某些视图不可更新：

包含以下关键字的sql语句：分组函数、distinct、group  by、having、union或者union all

常量视图

Select中包含子查询

join

from一个不能更新的视图

where子句的子查询引用了from子句中的表

3.更新：

①create or replace view 视图名 as 查询语句

②alter view 视图名 as 查询语句

4.删除

drop view 视图名

5.查看

①desc 视图名;

②show create view 视图名

七、存储过程

（一）创建

create procedure 存储过程名(in|out|inout 参数名 参数类型,....)

begin

存储过程体

end $

注意：①需要设置心得结束标记 delimiter $

②当存储过程体仅有一条sql语句时，可以省略begin end

（二）调用

call 储存过程名(实参列表)

八、函数

（一）创建

create function 函数名(参数名 参数类型) returns 数据类型

begin

函数体

end

（二）调用

select 函数名(实参列表)

九、变量

（一）全局变量

作用域：针对所有会话（连接）有效，但不能跨重启，除非修改配置文件

查看全局变量：show global variables like ’‘;

查看指定系统变量：select @@global.autocommit;

赋值：set @@global.autocommit=0 或者 set global autocommit=0;

（二）会话变量

作用域：当前会话有效

查看会话变量：show session variables like '';

查看指定会话变量：select @@autocommit;select @@session.tx_isolation; session可以省略

赋值：set @@session.tx_isolation='read-uncommit';或者set session tx_isolation='read-commited'

（三）用户变量

声明并初始化：

①set @变量名=值;

②set @变量名:=值;

③select @变量名:=值;

赋值：

方式一：用于简单的赋值

①set 变量名=值;

②set 变量名:=值;

③select 变量名:=值;

方式二：一般用于表的赋值

select 字段名or表达式 into 变量名 from table;

使用：select @变量名;

（四）局部变量名

声明：

declare 变量名 类型 【default 值】;

赋值：

方式一：

set 变量名=值;

set 变量名:=值;

select 变量名:=值;

方式二：

select 变量名or表达式 into 变量 from table

使用：

select 变量名

十、分支

（一）if函数

语法:if(条件，值1，值2)

特点:可以用在任何位置

（二）case语句

语法:

情况一：类似于switch

     case 表达式

     when 值1 then 结果1或语句1(如果是语句，需要加分号) 

     when 值2 then 结果2或语句2(如果是语句，需要加分号)

     ...

     else 结果n或语句n(如果是语句，需要加分号)

     end 【case】（如果是放在begin end中需要加上case，如果放在select后面不需要）

 

情况二：类似于多重if

     case 

     when 条件1 then 结果1或语句1(如果是语句，需要加分号) 

     when 条件2 then 结果2或语句2(如果是语句，需要加分号)

     ...

     else 结果n或语句n(如果是语句，需要加分号)

     end 【case】（如果是放在begin end中需要加上case，如果放在select后面不需要）

特点：可以放在任何地方

（三）if elseif语句

语法：

    if 情况1 then 语句1;

    elseif 情况2 then 语句2;

    ...

    else 语句n;

    end if;

特点：只能放在begin end中

（四）循环

语法：

【标签：】while 循环条件 do

循环体

end while【标签】;

特点：只能放在BEGIN END里面

    如果要搭配leave/iterate跳转语句，需要使用标签，否则可以不用标签

    leave类似于java中的break/continue语句，跳出所在循环！！！