MySql核心技术

学习目标

提示：mysql从基础到熟悉了解，在到使用的过程复习

一、为什么要学习数据库

数据库的好处

1. 持久化数据到本地；
2. 可以实现结构化查询，方便于管理。

二、数据库的相关概念

1. DB：数据库，保存一组有组织的数据的容器；
2. DBMS：数据库管理系统，又称为数据库软件，用于管理DB中的数据；
3. SQL：结构化查询语言，用于和DBMS进行通信。

三、数据库存储数据的特点

1. 将数据存储到表中，将表在放入到库中；
2. 一个数据库中可以有多张表，每张表都有一个唯一的名字，用于标识自己；
3. 表由列组成，也就是字段，所有的表都由一个或者是多个列组成，每一列类似于java中的属性；
4. 表中的数据都是按行存储，每一行相当于java中的对象。

四、初始MySql

1、MySql产品的介绍

1. MySql的背景：前身属于瑞典的一家公司，Mysql AB，08年被sun公司收购，09年sun被oracle收购；
2. MySql的优点：①开源、免费、成本低②性能高，移植性也好③体积小，方便安装

2、MySql产品的安装

属于c/s架构的软件，一般来讲安装服务端就好，目前有企业版和社区版

3、MySql服务的启动和停止

1. 方式一：计算机–>右键管理–>服务
2. 方式二：通过管理员身份运行cmd.exe
   net start 服务名(启动服务)
   net stop 服务名(停止服务)

4、MySql服务的登录和退出

1. 方式一：通过mysql自带的客户端，只限于root用户
2. 方式二：通过windows的客户端
   登录：mysql 【-h主机名 -p端口号】（注：本机此处可省略）-u用户名 -p密码
   退出：exit 或者ctrl+c

5、MySql常见命令和语法规范

常见命令

1. 查看当前所有的数据库：show databases;；
2. 打开指定的库：use 库名；
3. 查看当前库的所有表：show tables;；
4. 查看其它库的所有表：show tables from 库名；
5. 创建表：

create table 表名(
		列名 	列类型
		列名 	列类型
		....
);

1. 查看表结构：desc 表名；
2. 查看服务器的版本：
   方式一：登录mysql服务端 mysql version();
   方式二：没有登录到mysql服务端mysql --version或者mysql --v

语法规范

1. 不区分大小写，但是建议关键字大写，表名，列名小写；
2. 每条命令最好用;结束；
3. 每条命令根据需要，可以进行缩进或者换行；
4. 注释：单行注释：#或者–注释文字
   多行注释：/* 注释文字 */

6、Sql的语言分类

1. DQL(Data Query Language)：数据查询语言，select
2. DML(Data Manipulate Language)：数据操作语言，insert，update，delete
3. DDL(Data Define Language)：数据定义语言，create，drop，alter
4. TCL(Transaction Control Language)：事务控制语言，commit，rollback

7、Sql的常见命令

1. show databases; 查看所有的数据库；
2. use 库名，打开指定库；
3. show tables; 显示库中所有的表；
4. show tables from 库名; 显示指定库中所有的表；

   create table 表名(
   	字段名 字段类型，
   	字段名 字段类型
   );
   

创建表；

5. desc 表名; 查看指定表的机构；
6. select * from 表名;显示表中的所有数据。

五、DQL语言的学习

1、基础查询

语法：select 要查询的内容 FROM 表名;
特点：通过select查询完的结果，是一个虚拟的表格，不是真实的存在；要查询的东西可以是常量值、表达式、字段、函数。

2、条件查询

根据条件过滤原始表中的数据，查询自己想要的结果。
语法：

select	
		要查询的字段|表达式|常量值|函数
from 
		表名
where 
		条件;

分类：

1. 条件表达式：条件运算符为> < >= <= = != <>；
2. 逻辑表达式：逻辑运算符为

	and(&&)：两个条件如果成立，结果为true，否则为false；
	or(||)：两个条件只要有一个成立，结果为true，否则为false；
	not(!)：如果条件成立，则not后为false，否则为true。

3. 模糊查询：示例：name like '%王%';

3、排序查询

语法：

select	
		要查询的东西
from
		表
where 
		条件
order by 排序字段|表达式|函数|别名|
asc|desc

4、常见函数

1. 单行函数
   ①字符函数

函数	功能
CONCAT(str1,str2,…,strn)	将str1，str2…连接成一个完成的字符串
INSERT(str,x,y,instr)	将字符串str从x开始，y个字符串长度的子串替换为字符串instr
LOWER(str)	将str中的所有字符串转换为小写
UPPER(str)	将str中的所有字符串转换为大写
LEFT(str,x)	返回字符串最左边的x个字符
RIGHT(str,x)	返回字符串最右边的x个字符
LPAD(str,n,pad)	使用字符串pad对字符串str最左边进行填充，知道长度为n个字符长度
RPAD(str,n.pad)	使用字符串pad对字符串str最右边进行填充，知道长度为n个字符长度
LTRIM(str)	去掉左边的空格
RTRIM(str)	去掉右边的空格
TRIM(str)	去掉字符串行头和行尾的空格
REPLACE(str,a,b)	使用字符串b替换掉字符串str中所有的a
REPEAT(str,x)	返回字符串重复x次的结果
STRCMP(str1,str2)	比较字符串str1和str2
SUBSTRING(str,x,y)	返回字符串str中从x为止起y个字符串长度的字符串
LENGTH(str)	获取str字符串的字节个数

②数学函数

函数	功能
ROUND(2.5)	四舍五入
RAND()	0-1之间的随机数
FLOOR(3.6)	向下取整
CEIL(3.6)	向上取整
MOD(10,3)	取余
TRUNCATE(1,10)	截断
SQRT(5)	取平方根
ABS(-2.36)	取绝对值

③日期函数

函数	功能
NOW()	获取系统的当前日期+时间
CURDATE()	获取当前系统日期
CURTIME()	获取当前系统时间
STR_TO_DATE(‘2017-01-06 10:20:30’,’%Y-%m-%d %H:%i:%s’)	将字符转换成日期
DATE_FORMAT(NOW(),’%Y-%m-%d %H:%i:%s’)	将日期转换成字符

④流程控制函数

函数	功能
select IF(str,str1,str2)	如果str成立，那么返回str1,否则返回str2
case	case 函数可以实现两种功能，第一种类似于java中的swith 语句（做等值判断），第二种类似于java中的多重if语句（做区间判断）

case示例一：

case 要判断的字段或语句
	when 常量1 then 要显示的值1或语句1;
	when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n；
end

case示例二：

case
	when expr1 then 要显示的值1或语句1;
	when expr2 then 要显示的值2或语句2;
...
else 要显示的值2或语句2;
end

⑤其他函数

函数	功能
version()	当前数据库版本
DATABASE()	当前库
USER()	当前连接用户

2. 分组函数

函数	功能
SUM()	求和
MAX()	求最大值
MIN()	求最小值
AVG()	求平均值
COUNT()	计数

特点：①以上的五个函数都忽略null值，count()除外；②sum和avg一般用于处理数值型的字段，max、min、count可以处理任何数据类型；③都可以搭配DISTINCT来使用，用于同级去重后的结果；④count的参数可支持字段、常量，一般放1即可、*，建议使用count(*)。

5、分组查询

语法：

select	要查询的内容
	from	表名
	where 条件
group by 要分组的字段;

特点：①可以按照单个字段进行分组；②和分组函数一同查询的字段最好是分组后的字段；③分组的筛选：

分组前后	针对的表	位置	关键字
分组前筛选	原始表	group by的前面	where
分组后筛选	分组后的结果集	group by的后面	having

④可以按照多个字段进行分组，字段之间用逗号隔开；⑤可支持排序；⑥having后可支持别名。

6、多表连接查询

使用原因：
笛卡尔乘积：如果没有用连接条件或者是连接条件无效则会出现，所以必须使用连接条件查询。

1. 传统模式下的连接：等值连接、非等值连接
   ①等值连接的结果等于多个表的交集；
   ②n表连接至少需要n-1个连接条件；
   ③多个表不分主次，没有顺序要求；
   ④可为表起别名，提高sql的可读性和性能。
2. sql99语法：通过join关键字实现连接
   含义：1999年推出的sql语法
   支持：等值连接、非等值连接(内连接)、外连接、交叉连接
   语法：

select	字段
from 表1
inner|left|right|cross| join 表2 on 连接条件,
inner|left|right|cross| join 表3 on 连接条件,
....
where 筛选条件
group by 分组字段
having 分组后的筛选条件
order by  排序的字段或者表达式;

3. 自连接
   案例：查询城市和省份的名称

 sql99语法：
		select	a.name,b.name
		from 表1 as a
		left join 表1 as b
		on a.上级ID=b.ID
 sql92语法：
		select a.name,b.name
		from 表1 as a,表2 as b
		where a.上级ID=b.ID

7、子查询

含义：一条select又嵌套了另一条完整的select语句，其中被嵌套的select语句被称为子查询或者是内查询，在外面的查询语句外称为主查询或者是外查询。
特点：①子查询都放在小括号外边；②子查询可以放在from后面、select后面、where后面、having后面，但是一般都是放在条件的右侧；③子查询优于主查询执行，主查询使用了子查询的执行结果；④子查询根据查询结果的行数不同定义了以下两类：

类别	特点	非法使用子查询的情况
单行子查询(标量子查询)	结果集只有一行，一般搭配单行操作符使用：> < >= <= != <>	子查询的结果为一组值；子查询的结果为空
多行子查询(列子查询)	结果集有多行，一般搭配多行操作符使用：any 、all、 in、not in
行子查询	结果集为多行多列
表子查询	结果集为多行多列

8、分页查询

应用场景：实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句
语法：

select	字段,表达式,...
from 	表
where 	条件
group	by	分组字段
having	条件
order	by	排序的字段
limit	起始的条目索引,条目数

特点：
①起始条目索引从0开始；
②limit子句放在查询语句的最后；
③公式：
select * from 表 limit (page-1)*sizeParPage,sizeParPage
注：page为要显示的页数
sizeParPage为每页显示的条数

9、联合查询

含义：union 联合、合并，将多次查询的结果合并成一个结果
语法：

select	字段|常量|函数|表达式|	
from表1	
where 条件 
union 
select	字段|常量|函数|表达式|	
from	表2	
where 条件

select	字段|常量|函数|表达式|	
from	表1	
where 条件
union  all	
select	字段|常量|函数|表达式|	
from	表2	where 条件

意义：①将一条比较复杂的查询语句拆分成多条语句②适用于查询多个表的时候，查询的列基本是一致的。

特点：①多条查询语句的查询列表必须是一致的；②多条查询语句的查询列表的类型几乎相同；③union去重，union all不去重。

六、DML语言的学习

1、插入

方式一：

语法：insert into 表名(字段名,...) values(值,...);
特点：

1. 要求值的类型和字段类型要匹配兼容；
2. 字段的个数和顺序不一定要跟原始表中的字段个数和顺序一致，但是必须保证字段值和顺序要一一对应；
3. 假如表中有可以为null的字段，可以通过两种方式插入null值①字段和值都可以省略②值以字符串null的形式插入；
4. 字段和值的个数必须是一致的；
5. 字段名可以省略，默认为所有的列。

方式二：

语法：insert into 表名 set 字段=值，...;

两种方式的区别：

1. 方式一一次可以插入多行数据，语法：insert into 表名(字段名,...) values(值,...),(值,...),(值,...)...;
2. 方式二支持子查询插入，语法：insert into 表名 查询语句。

2、修改

单表的修改

语法：update 表名 set 字段=值,... 【where 筛选条件】

多表的修改

语法：

	update	表1	别名
	left|right|inner|	表2	别名
	on	连接条件
	set	字段=值,...
	【where 筛选条件】

3、删除

方式一：

1. 删除单标的记录：delete from 表名 【where 筛选条件】【limit 条目数】；
2. 级联删除：
   语法：

	delete 别名1,别名2		
	from 表名1 别名1
	left|rigth|inner| join 表名2 别名2
	on 连接条件
	【where 筛选条件】

方式二：

关键字：truncate
语法：truncate table 表名

两种方式的区别：

1. truncate删除之后，如果再次插入，标识列从1开始，delete删除之后，如果再次插入，标识列从断点开始；
2. delete可以添加筛选条件，truncate不可以；
3. truncate效率比较高；
4. truncate没有返回值，delete可以返回受影响的行数；
5. truncate不可以回滚，delete可以回滚。

七、DDL语言的学习

1、库的管理

1.1、创建库

create database 【if not exists】库名 【character set 字符集名】

1.2、修改库

alter database 库名 character set 字符集名

1.3、删除库

drop database 【if exists】库名

2、表的管理

2.1、创建表

create table 【if not exists】 表名(
	字段1 字段类型 【约束】,
	字段1 字段类型 【约束】
	...
)

2.2、修改表

2.1.1、添加列

alter table 表名 add column 列名 类型 【first|after 字段名】

2.1.2、修改列的类型或约束

alter table 表名 modify column 列名 新类型【新约束】

2.1.3、修改列名

alter table 表名 change column 旧列名 新列名 类型;

2.1.4、删除列

alter table 表名 drop column 列名; 

2.1.5、修改表名

alter table 表名 rename 【to】 新表名;

2.3、删除表

drop table 【if exists】 表名;

2.3、复制表

2.3.1、复制表的结构

create table 表名 like 旧表;

2.3.2、复制表的结构和数据

create table 表名
select 查询列表 from 旧表【where 条件】

3、数据类型

3.1、数值型

1. 整形

整数类型	字节	范围
Tinyint	1	有符号：-128 ~ 127；无符号：0 ~ 255
Smallint	2	有符号：-32768 ~ 32767；无符号：0 ~ 65535
Mediumint	3	有符号：-8388608 ~ 8388607；无符号：0 ~ 65535
int、integer	4	有符号：-2147483648 ~ 2147483647；无符号：0 ~ 4294967295
Bigint	8	有符号：-9223372036854775808 ~ 9223372036854775807；无符号：0 ~ 9223372036854775807*2+1

特点：
①如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，使用UNSIGNED关键字；
②如果插入的数值超出了整型的范围，会报Out of range value异常；
③如果不设置长度，会有默认的长度，长度代表了显示的最大宽度，如果不够会用0在左边填充，但是必须搭配zerofill使用。

2. 浮点型

浮点数类型	字节	范围
float	4	土1.75494351E ~ 土3.402823466E+38
double	8	土2.2250738585072014E-308 ~ 土1.7976931348623157E+308

定点数类型	字节	范围
DEC(M,D)，DECIMAL(M,D)	M+2	最大取值范围与double相同，给定decimal的有效取值范围由M和D决定

定点数：decimal(M,D)
浮点数：float(M,D)，double(M,D)

特点：
①M代表整数部位外加小数部位的总长度，D代表小数部位的长度，如果超过报Out of range value异常；
②M和D都可以省略，但对于定点数，M默认为10，D默认为0；
③如果精度要求较高，则优先考虑使用定点数。

3.2、字符型

重点（较短的文本）：

字符串类型	最多字符数	存储要求	特点	空间的耗费	效率
char(M)	M	M为0 ~ 255之间的整数	固定长度的字符	比较耗费	高
varchar(M)	M	M为0 ~ 65535之间的整数	可变长度的字符	比较节省	低

较长的文本：text、bolb（较大的二进制）

其他：binary和varbinary用于保存较短的二进制
enum用户保存枚举
set用户保存集合

3.3、日期型

日期和时间类型	字节	最小值	最大值
date	4	1000-01-01	9999-12-31
datetime	8	1000-01-01 00:00:00	9999-12-31 23:59:59
timestamp	4	19700101080001	2038年的某个时刻
time	3	-838:59:59	838:59:59
year	1	1901	2155

4、常见的约束

4.1、常见的约束

1. NOT NULL：非空，保证该字段不能为空；
2. DEFAULT：默认，用户保证该字段有默认值，如性别；
3. PRIMARY KEY：主键，用于保证该字段的值具有唯一性，比如主键ID；
4. UNIQUE：唯一，用于保证该字段具有唯一性，可以为空没有 PRIMARY KEY严谨；
5. CHECK：检查约束【mysql中不支持】；
6. FOREIGN KEY：外键约束，用于限定两个表的关系，用于保证该字段的值必须来自主表的关联列
   注意：添加约束的时机①创建表时②修改表时

约束的添加分类：
①列级约束：以上六个约束都可以在列级约束中使用，但是外键没有效果；
②表级约束：除了非空、默认剩下的都是支持的。

主键和唯一的区别：

	保证唯一性	是否允许为空	一个表中可以有多少个	是否允许组合
主键	√	×	最多有一个	√，但是不推荐使用，并不是很稳定
唯一	√	×	可以有多个	√，不推荐

外键：

1. 要求在从表设置外键关系；
2. 从表的外键列的类型要求和主表的关联列的类型兼容，名称没有要求；
3. 主表的关联列必须是一个key(主键或者是唯一键)；
4. 插入数据时先插入主表的数据，在插入从表，删除数据时先删除从表，在删除主表

4.2、创建表时添加约束

create table 表名(
	字段名 字段类型 not null,#非空
	字段名 字段类型 primary key,#主键
	字段名 字段类型 unique,#唯一
	字段名 字段类型 default 值,#默认
	constraint 约束名 foreign key(字段名) references 主表（被引用列）
)

	位置	支持的约束类型	是否可以起约束名
列级约束	列的后面	语法都支持，但是外键没有效果	不可以
表级约束	所有列的下面	默认和非空不支持，其他支持	可以，主键没有效果

4.3、修改表时添加或者约束

4.3.1、非空

1. 添加非空：alter table 表名 modify column 字段名 字段类型 not null;
2. 删除非空：alter table 表名 modify column 字段名 字段类型;

4.3.2、默认

1. 添加默认：alter table 表名 modify column 字段名 字段类型 default 值;
2. 删除默认：alter table 表名 modify column 字段名 字段类型;

4.3.3、主键

1. 添加主键：alter table 表名 add【constraint 约束名】 primary key 字段名;
2. 删除主键：alter table 表名 drop primary key;

4.3.4、唯一

1. 添加唯一：alter table 表名 add【constraint 约束名】 unique(字段名);
2. 删除唯一：alter table 表名 drop index 索引名;

4.3.5、外键

1. 添加外键：alter table 表名 add【constraint 约束名】 foreign key(字段名) references 主表(被引用列);
2. 删除外键：alter table 表名 drop foreign key 约束名;

4.4、标识列(自增长列)

可以不需要手动插入值，系统提供默认的序列值

八、TCL语言的学习

九、视图的讲解

十、变量

十一、存储过程和函数

十二、流程控制结构