关系型数据库管理系统之MySql学习总结

数据库介绍

数据库（Database，DB）
概述：存储数据的处所，用户可对数据进行增删改查等操作，用一定方式存储数据，可用户共享，即一个数据库多张表的结构。
数据库管理系统（Database Management System，DBMS）
概述：操作和管理数据库的软件，用于建立，使用，维护数据库，对数据库进行统一管理，以确保数据库的安全性和完整性，用户通过数据库管理系统访问数据库中表内的数据信息。
数据库和数据库管理系统的关系：

有关一些数据库介绍：
MySql：主流，开源免费小型数据库，已经被oracle公司收购，mysql6.x版本开始收费。
Oracle：主流，收费的大型数据库，oracle公司的产品，oracle收购了sun公司和mysql。
DB2：IBM公司收费的大型关系数据库，经常应用于银行系统中。
SQLServer：主流，microsoft公司收费的中型数据库，c#，.net等语言常使用。
Sybase：以淡出历史，免费的专业数据建模的工具powerdesigner。
SQLite：，免费的轻量级嵌入式小型数据库，应用于手机端。

MySql介绍

什么是sql：
1、数据库不识别其他语言，但要与它交互，这时要用到数据库认识的sql语句
2、结构化查询语言structured query language简称sql，是关系型数据库管理系统都要
遵循的规范，不同数据库都支持sql语句，但都有特有内容。
数据库定义语言（Data Definition Language） DDl：用于操作数据库，表，列等。
数据库控制语言（Data Control Language） DCL：定义数据库访问权限，用户。
数据库操作语言（Data Manipulation Language） DML：对表中数据进行增删改。
数据库查询语言（Data Query Language） DQL：对表中数据进行查询，统计数量。

创建数据库语法：
1、create database 数据库名;
2、create database 数据库名 character ‘UTF-8’;（设置字符集）;
**使用数据库就要切换：use 数据库名;
查看所有的数据库： show databases;
查看指定数据库： show create database 数据库名;
查看当前使用的数据库，为null 代表没有切换数据库：select database();
删除数据库： drop database 数据库名;
创建表（多个字段用逗号隔开，字段后写字段类型，约束）
create table 表名 (字段名1 类型(长度) [约束] , 字段名2 类型(长度) [约束])
查看当前数据库中所有表： show tables;
查看表结构： desc 表名；
查看建表语句： show create table 表名；
删除表： drop table 表名；
修改表结构
给指定表添加列： alter table 表名 add 列名 类型 约束；
对表中字段的类型和约束修改： alter table 表名 modify 列名 类型 约束;
对表的字段名类型和约束修改： alter table 表名 change 旧列名 新列名 类型 约束;
删除表中的指定一列： alter table 表名 drop 列名；

添加数据

整表添加数据： insert into 表名 values (值1,值2);
指定列添加数据：insert into 表名 (列名1,列名2) values (值1,值2);
多条数据插入： insert into 表名 (列名1,列名2) values (值1,值2),(值1,值2);**

添加时需要注意：

1. 列的数量要和值的数量一致；
2. 列的类型要和值的类型一致；
3. 值的顺序要和列的顺序一致；
4. 值的长度不能超过列规定的长度；
5. 除了数值型(int，duble)的列，其他类型的列，插入数据时，要加上单引号。
6. 允许为null的列，可以添加null，也可以对该列不做添加；
   自增列：auto_increment
   要求：该列的类型必须是int ，执行时自动产生自赠数，如果对自增列添加时，可以对该列不做插入值，或者插入0或null；
   删除指定数据： delete from 表名 where 条件；
   整表删除： truncate table 表名；
   两个删除的区别：
   delete from table 表名： 速度慢，记日志，可以恢复 自增列不清空；
   truncate table 表名： 速度快，不记日志，不可恢复 自增列清空;
   修改数据语法：update 表名 set 列名 = 值 where 条件；

导入数据

批量数据导入，表存在的方式进行导入：
create table 新表名 ( 列名1,列名2)
select 原表名,原表列 from 原表名；
原表中的数据导入到一个新建表中，用于数据初始化的时候，只执行一次：
create table 新表名 select 原表名 , 原表列 from 原表；

查询数据

查看当前表中所有数据： select * from 表名;
指定列查询： select 列名1,列名2 from 表名;
条件查询： select * from 表名 where 条件;
指定列重命名： select 列名 as 别名 , 列名 as 别名 from 表名;
指定列去重复数据： select distinct 列名,列名 from 表名;
条件查询中需要的比较运算符：< > = != <>
[] 可加可不加，* 代表全部；
区间查询：between and ；
注：包含两头，通常用于 数值 和 日期；
in (set) 集合；
is null 判断是否为空；
is not null 不为空；
like 配合 % ；% 能有多个字符； 只能有一个字符；
例： select * from 表名 where 列名 like ‘%张%’;（查看带张字的字符）
limit 0,0 固定行，从第几行开始到第几行结束；
添加常量列：select 列名 , ‘值’ as 列名 from 表名;
排序 order by语法：select *(列) from 表名 where 条件 order by 排序列;
asc 升序， desc 降序，默认升序；
根据对各列对结果进行排序：
select *(列) from 表名 where 条件
order by 列1[asc/desc] , 列2[asc/desc];

内连接

什么时候用：保证多张表有关联关系，查询的数据是两张表或多张表都存在相关数据；
两种语法：
1， select 列1,列2 from 表1 inner join 表2 on表1.列 = 表2.列;
2， select 列名，列名 from 表1,表2 where 表1 . 列 = 表2 . 列;

左外连接/右外连接

1， 表和表之间的关系
2，查询的是左表中所有的数据以及右表中的和左表相关的数据，如果右表中没有左表相关数据显示为null； 有顺序
语法：
select 列,列 from 左表 left join 右表 on 左表 . 列 = 右表 . 列；
右外连接和左外连接相反;： 关键字： right join on；

子查询

把一个查询结果嵌套到另一个查询结果中，子查询的列只能有一个;
子查询配合 in(set) =;
union 和 union all
作用：用于合并结果集；
要求：两个或多个结果集的列数量一致；
在使用union和union all 重命名时，必须写在第一个sql中
特点：1、不论表是否一致；
2、不论结果集的列的类型是否一致；
3、只要列的数量一致就会合并；
区别：union 会把结果集一样的数据合并；
union all 不会合并结果集；

约束

约束目的：防止不符合规范的数据进入数据库，保证数据的完整性，正确性，有效性；

not null 非空约束： 要求该列不能为null值；
unique 唯一约束： 要求该列可以为null，但不能有重复值；
default 默认值约束
primary key：主键约束：要求该列不能为空，不能重复；
foreign key：外键约束：要求引用性，完整性，用于表和表之间；
主键：primary key； 作为表中数据的唯一标识，整张表主键列不能有重复值和null；
外键约束语法：constraint 外键名 foreign key (列) references 主表 (列)；
注意：使用外键时为了保证表和表之间的引用性，完整性
（学生表 引用 班级表）被引用的表 班级表 ，主表，引用表 学生表 从表；
注：在建立外键约束时，必须保证，创建表时，先创建主表，在创建从表
，插入数据时，先插入主表，再插入从表，删除表中数据时，先删从表，再删主表，
注：存在外键约束的表中不能使用 truncate 如果从表中的数据，没有引用到主表中删除数据，那么该条数据可以删除，从表也称：子表，副表；
三大范式
第一大范式：确保每一列的原子性，保证每一列不可再分；
第二大范式：保证每条数据描述的都是同一件事；
第三大范式：确保非主键列和主键列有直接关系，不能存在间接传递；

函数

聚合函数

使用语法： select 聚合函数 from 表名;
1， count () 计数；
2， sum () 求和；
3， avg () 求平均值；
4， max () 最大值；
5， min () 最小值；
count()只记录不为null的值，括号中可以写 * 列名 常量；常量速度快，如果列是主键速度和常量一样其他列速度慢(*)速度没有(常量)快但比 (普通列)快;

分组函数

group by 分组函数 语法：
select 被分组的列, 聚合函数 from 表名 where 条件
group by 被分组的列;
having过滤条件：如果对分组结果进行筛选，只能使用having
having需要在分组之后执行使用，
where 只能在分组之前对数据进行筛选，在where 里面不能使用别名 group by可以；

数学函数

mysql执行函数时前面必须加select
1， abs (x) 返回x的绝对值；
2， ceil (x) 向上取整；
3， floor (x) 向下取整；
4， rand () 获取0-1的随机数；
5， rand (x) 获取固定随机数；
6， sign (x) 获取该数和0的比较，x为负数返回-1，整数返回1,0返回.；
7， pi () 圆周率；
8， truncate (x,y) 从x数直接截取到y，，截取字符串；
9， round () 四舍五入；
10， pow () 获取x的y次方；
11， trim () 去除字符串两端的空格，注意：中间空格不去除

字符串函数

1，length (‘ ‘) 获取字符串长度，一个中文占3个字符串长度；
2， char_length (‘ ’) 返回字符串的字符串数；
3， concat (‘ ‘) 拼接字符串
4， concat_ws (‘ ’) 拼接字符串，第一个值为分割符；
5， insert (str,pos,len,str2) 把str2从str，pos的位置，开始替换；
6， upper (str) 小写转大写；
7， lower (str) 大写转小写；
8， left (‘ ’) 从字符串左边截取；
9， right (‘ ’) 从右边截取；
10， lpad (str,len,pad) 使用pad在str左边填充；
11， rpad () 右填充；

日期时间函数

1， current_date () 返回当前日期，不包含时间
2， curdate ()
3， current_time () 返回当前时间，不包含日期
4， curtime ()
5， now () 获取日期和时间
6， week () 获取一周；
7， month (now()) 获取月份；
8， year (now()) 获取年；
9， day (now()) 获取天
10， date_add (date,lnterval,exe,unit) 根据一个日期点和变量求另外一个日期；adddate()
11， datediff (exe1,exe2) 求两个日期的日期差，单位是天；
12， select truncate (datediff (now() , ‘日期2001-09-16’) / 365,0) 求年龄；
~~

条件判断函数

1， if (bool 表达式, true , false)
注意：如果bool表达式成立，返回true，否则false
2， 判断为空：ifnull (‘ ’ , ‘空’)
注意：如果第一个值为空，则显示第二个括号内容
3， case when e1 then v1
when e2 then v2
else vn end;
注意：case表示开始，end表示结束，如果e1 成立 返回 v1,如果e2 成立返回v2; 全部，不成立返回vn，有一个成立之后，后面的就不在执行；

系统信息函数

version () 获取版本号
user () 获取当前用户
connection_id () 服务器连接数
last_insert_id () 自增长的id标识

加密函数

password (‘123456’) 密码加密
md5 (‘str’) 字符串
支持解密的加密方式：encode (‘123456’ , ‘abcd’) 通常用于，数据传递
decode (encode (‘123456’ , ‘abcd’ , ’abcd’)) 解密

格式化函数

format (pi() , 4) 将pi() 进行格式化，将pi()保留到小数点后四位或者n位；

自定义函数

语法： create function 函数名 (参数名,参数类型 长度)
returns 返回类型长度
begin
declare 声明字段名 字段类型;
操作语句;
return 返回字段;
end;
调用自定义函数 select 函数名(参数)

视图

视图：视图本质上是一张虚拟表，创建完后存储到数据库服务器；
什么时候用：如果对一个查询结果需要进行多次使用，建议使用视图。
视图的优点：调用时比较简单，执行速度快；
创建视图语法：create view 视图名 as select 语句；

事务

事务的特性：原子性，一致性，隔离性，持久性。
原子性：确保工作单元内的所有操作都成功完成，否则事务将被终止在故障点，和以前的操作将回滚到以前的状态；
一致性：确保数据库正确地改变状态后，成功提交的事务；
隔离性：使事务操作彼此独立的和透明的；
持久性：事务完成后，它对系统的影响是永久性的修改即使出现致命的系统故障也将一直保持
一旦开启事务，要成功全成功，要失败全失败；
开启事务语法：1、start transaction;2、Begin
提交事务：commit
事务回滚：rollback

存储引擎

查看mysql都支持的存储引擎： show engines
必须是默认的存储引擎，
存储引擎简介：
mylsam存储引擎：不支持事务，外键，优势是访问速度快，对事务完整性没有要求或
以select，insert为主的应用基本可以用这个引擎创建表。
innodb存储引擎：默认存储引擎，提供了具有提交，回滚和崩溃恢复能力的事务安全。但是对比mylsam引擎，写的处理效率会差一些，并且占用更多的磁盘空间保留数据和索引，
innodb特点：支持自动增长列，支持外键约束。

存储过程

| 代表或者；@ 代表约定；
存储过程语法：
create procedure 存储过程名称 (参数)
begin
sql操作语句;
end;
调用存储过程： call 存储过程名称 ();
删除存储过程： drop procedure 存储过程名称;
mysql 存储过程共有三种参数类型： in out inout
语法：create procedure | function ( in | out | inout 参数名 类型 )
in 输入参数，在存储过程中修改该参数的值不能被返回为默认值。
out 输出参数，该值可在存储过程内部改变，可返回。
inout 输入输出参数，调用时指定，并可被改变和返回。
调用输出参数：call 存储过程名 (‘值’, @参数名)
select @参数名
注：into 变量名称，将结果保存到变量中
stuname 为输入参数，可以将参数传递到存储过程中
out 为输出参数，将计算的结果输出存储过程。

循环

注意：所有循环必须写在存储过程或者函数中,循环前声明变量；
在while里面声明变量使用declare
1, while循环语法：
声明变量： declare i int default 1 ;
while 条件 do
操作语句 ; 注意分号
set 变量 = 变量 + 1 ; 防止死循环，需要更新i的值 end while; 例： set i = i+1;
2, repeat 循环语法：
repeat
操作语句;
set 变量 = 变量+1;
until 变量 > 值 end repeat;
3, loop循环语法：
label : loop 注意冒号，label名可随便定义。
操作语句 ;
set i = i + 1 ;
if i > 10 then
leave label ;
end if ;
end loop ;

---