MySQL数据库深度讲解

mySQL深度讲解

1.数据库设计 和 SQL语句
2.数据库高级开发 视图 函数 存储过程 事务和锁 索引
3.数据库管理 DBA 管理MySQL安全 备份 还原 复制 日志管理

Oracle 和 MySQL 比较

MySQL 免费 开源 中小企业
Linux Apache MySQL PHP LAMP

第一章 数据库设计

1.1 软件开发周期
需求分析阶段、概要设计阶段、详细设计阶段、
代码编写阶段、软件测试阶段、安装部署阶段

ER模型 Entity RelationShip
实体 学生 专业 学院 课程介绍

确认实体和属性、画ER图、由ER模型转换的关系模型

物理设计

第一范式 属性不可再分
第二范式 每个表只描述一件事情
第三范式 表中不要有其他表种已经存在的信息

彻底搞定MySQL乱码问题

预备知识
字符 Character a b c d
字符集合 CharSet 一组字符 ASCII 扩展ASCII GB2312 BIG5 Unicode字符集合
字符编码
字符编码和字符集合定义了字符集
字符序 Collation 定义了字符集种字符的排序规则
字符集合可以由多种编码方式，多个字符集
一个字符集可以有多个排序规则，多个字符序
以_ci结尾大小写不敏感
以_cs结尾大小写敏感
以_bin结尾按编码值比较
存储时使用的字符集和查看的时候使用的字符集不一致就出现了乱码。

常见的字符集

ASCII字符集
扩展ASCII字符集 latin1 8位二进制 包括ASCII字符集种的全部字符
GB2312 BIG5 GBK 16位二进制
Unicode字符集 全球语言 16位二进制
UTF8 一个英文字符 1个字节，一个中文字符3个字节

MySQL支持的字符集
show character set;

设置服务器默认字符集
在my.ini文件中输入一下内容，重启服务，一定要注意数据库默认字符集是utf8，不然 设置变量为中文时会报错，只要表的默认字符集是utf8插入中文就不会报错
[mysql]
default-character-set=utf8
[mysqld]
character-set-server=utf8
创建数据库不指定字符集则使用服务器默认字符集
创建表不指定字符集默认使用数据库的默认字符集
创建列不指定字符集默认使用表的默认字符集

查看数据库服务器默认字符集
show variables like 'character_set_%';

更改表的默认字符集对现有记录不影响，只有更改列的字符集才会立刻生效。

字符集的兼容性，范围大的字符集兼容范围小的字符集，例如utf8字符集兼容latin1字符集

指定客户端使用的字符集
set names utf8;

数据库存储引擎

MySQL常用的存储引擎为MyISAM、InnoDB、MEMORY、MERGE，其中InnoDB提供事务安全表，其他存储引擎都是非事务安全表。
MYISAM是MySQL默认的存储引擎，不支持事务，也不支持主键、外键，但其访问速度快，对事务完整性没有要求。如果表存的只是历史记录，很少对表进行增删改，只是查询，就适合用这种存储引擎。

查看数据库默认存储引擎
show variables like 'table_type';

配置文件设置mysql 默认存储引擎
default-storage-engine=InnoDB

更改表的存储引擎
alter table 'test' ENGINE=InnoDB;

mySQL命令帮助

？ 查询命令
？ contents
？ Account Management
? create user
? data definition
? create table

使用show命令查看数据库、表
show databases;
show tables;
show table status;
show create table test;

第三章 mySQL 语言结构

SQL语言分类

结构化查询语言 Structured Query Language

数据定义语言
DDL （Data Definition Language） 定义数据库中对象 表、视图、用户、函数、触发器

数据控制语言
授权用户访问、拒绝用户房屋内 撤销授予的权限
grant、deny、revoke
DCL （Data Control Language)

 create user 'wang'@'localhost' identified by 'a1!';
 grant select on db.student to 'wang'@'localhost';
 revoke select on db.student from 'wang'@'localhost';

SQL语句中的常量和变量

select ‘cruder’
select true,false
select 100>200

set @name = ‘teenway’
select @ name

mysql系统变量

全局系统变量 针对所有人默认设置
会话系统变量 针对当前用户生效

show global variables
show session variables

set @@session.sql_select_limit=2

运算符和表达式

select 1+1,1-1,3*4,4/2
select 17%5

select 10=10,10<10,10<>10
select ‘a’=‘A’,‘a’<‘b’
select ‘a’=‘A’,‘a’<‘b’

系统内置函数

数学函数
abs(x)
ceil(x)
floor(x)
rand()
sign()
pi()
truncate()
round()
pow()

字符串函数
char_length(s)
length(s)
concat(s1,s2)
ltrim(s)
rtrim(s)
trim(s)

日期和时间函数

加密和解密函数
password(str)
MD5(str)
ENCODE(str,pswd_str)
DECODE(crypt_str,pswd_str)

其他函数
FORMAT(x,n)

分割线---------------------------------------

数据库高级开发

课程介绍

数据完整性、函数、视图、存储过程、触发器、索引、事务和锁

数据完整性
数据的完整性指数据的可靠性和准确性，

实体完整性
PRIMARY KEY（主键约束）
UNIQUE（唯一值约束）

域完整性
DEFAULT 默认值约束
CHECK 检查约束
FOREIGN KEY 外键约束

引用完整性
FOREIGN KEY 外键约束

第二章 创建存储函数

-- 创建存储函数
create function getGrade(mark int)
returns varchar(20)
BEGIN
return (case FLOOR(mark/10) when 5 then '不及格' when 6 then '及格' when 7 then '中等' when 8 then '良好' else '优秀' end);
END

-- 删除存储函数
drop function getGrade

-- 创建随机生成姓名的函数
create function createName()
  RETURNS varchar(3)
BEGIN
  DECLARE LN VARCHAR(300);
  DECLARE MN VARCHAR(300);
  DECLARE FN VARCHAR(300);
  DECLARE LN_N INT;
  DECLARE MN_N INT;
  DECLARE FN_N INT;
   SET LN = '赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮卞齐康伍余元卜顾孟平黄和穆萧尹段';
  SET MN = '听见雨滴落在青青草地听见远方下课钟声响起可是没有听见声音认真呼唤姓名爱上时候还不懂感情离别了才觉得刻骨铭心为什么没有发现遇见了是生命最好事情也许当时忙着微笑和哭泣忙着追逐天空中流星人理所当然忘记是谁风里雨里一直默默守护在原地原来是最想留住幸运原来和爱情曾经靠得那么近那为对抗世界决定那陪淋雨一幕幕都是一尘不染真心与相遇好幸运可已失去为泪流满面权利但愿在看不到天际张开了双翼遇见注定她会有多幸运青春是段跌跌撞撞旅行拥有着后知后觉美丽来不及感谢是给勇气让能做回自己也许当时忙着微笑和哭泣忙着追逐天空中流星人理所当然忘记是谁风里雨里一直默默守护在原地原来是最想留住幸运原来和爱情曾经靠得那么近那为对抗世界决定那陪淋雨一幕幕都是一尘不染真心与相遇好幸运可已失去为泪流满面权利但愿在看不到天际张开了双翼遇见注定她会有多幸运';
  SET FN = '如果骄傲没被现实大海冷冷拍下又怎会懂得要多努力才走得到远方如果梦想不曾坠落悬崖千钧一发又怎会晓得执着人拥有隐形翅膀把眼泪种在心上会开出勇敢花可以在疲惫时光闭上眼睛闻到一种芬芳就像好好睡了一夜直到天亮又能边走着边哼着歌用轻快步伐沮丧时总会明显感到孤独重量多渴望懂得人给些温暖借个肩膀高兴一路上我们默契那么长穿过风又绕了弯心还连着像往常一样最初梦想紧握在手上最想要去地方怎么能在半路就返航最初梦想绝对会到达实现了真渴望才能够算到过了天堂如果骄傲没被现实大海冷冷拍下又怎会懂得要多努力才走得到远方如果梦想不曾坠落悬崖千钧一发又怎会晓得执着人拥有隐形翅膀把眼泪种在心上会开出勇敢花可以在疲惫时光闭上眼睛闻到一种芬芳就像好好睡了一夜直到天亮又能边走着边哼着歌用轻快步伐沮丧时总会明显感到孤独重量多渴望懂得人给些温暖借个肩膀高兴一路上我们默契那么长穿过风又绕了弯心还连着像往常一样最初梦想紧握在手上最想要去地方怎么能在半路就返航最初梦想绝对会到达实现了真渴望才能够算到过了天堂最初梦想绝对会到达实现了真渴望才能够算到过了天堂';
  SET LN_N = CHAR_LENGTH(LN);
  SET MN_N = CHAR_LENGTH(MN);
  SET FN_N = CHAR_LENGTH(FN);
  RETURN CONCAT(substring(LN,CEIL(RAND()*LN_N),1),substring(MN,CEIL(RAND()*LN_N),1),substring(FN,CEIL(RAND()*FN_N),1));

END;

select createName();

在存储函数中使用变量和判断语句

-- 创建使用if的函数
create function fab(a int,b int)
  returns varchar(20)
  begin
  declare result varchar(20);
if a>b then set result = 'a>b';
else if a=b then set result = 'a=b';
else set result = 'a<b';
end if;
return result;
end;

select fab(22,25);

-- 创建使用if else的函数
create function fab1(a int,b int)
returns varchar(20)
begin
  declare result varchar(20);
  if a>b
    then set result = 'a>b';
  else if a=b
    then set result = 'a=b';
  else
    set result = 'a<b';
    end if;
  end if;
  return result;
end;

select fab1(25,25);

-- 创建使用case的函数
create function fab2(a int,b int)
  returns varchar(20)
begin
  declare result varchar(20);
  case when a>b then set result='a>b';
    when a=b then set result='a=b';
    else set result='a<b';
    end case;
  return result;
end;

select fab2(11,11);

 -- 创建使用while的函数
 -- 一个数除4余数
create function fb(sint int)
returns varchar(20)
begin
  declare result varchar(20);
  case sint%4 when 0 then set result = '0';
  when 1 then set result = '1';
  when 2 then set result = '2';
  else  set result = '3';
  end case;
return result;
end;

select fb(41)

-- 写一个函数将输入的字符串 重复n次 中间使用空格分开 

create function charn(ch varchar(20),n int)
  returns varchar(255)
begin
  declare result varchar(255);
  declare i INT;
  set result = '';
  set i = 1;
  while i<=n do
  set result = concat(result,ch,' ');
  set i=i+1;
  end while;
  return result;
end;

select charn('teenway',3)

-- 查看自定义函数
show create function charn

第三章 视图

就是保存在数据库中的查询语句，可以当作表来使用。

create view view_stu as
select s_name,s_sex from student;

select * from view_stu;

创建视图的目的
隐藏数据的复杂性
实现数据访问安全
grant select on db.view_stu to ‘zhang’@‘192.168.80.%’ identified by ‘123456’

通过查询视图可以创建新的视图

实现行列变换

第四章 存储过程

使用存储过程查询数据

-- 创建没有参数的存储过程
create procedure getmax()
begin
  select
    a.s_id
      ,a.s_name
      ,totalScore.total_score
  from
    student a
      left join
    (
      select s_id, sum(s_score) total_score from score group by s_id
    )totalScore on totalScore.s_id = a.s_id
  order by totalScore.total_score desc ;
end;

call getmax();

-- 创建带参数的存储过程
create procedure getmaxByCourse(in cid varchar(20))
begin
  select
    a.s_id
      ,a.s_name
      ,courseInfo.c_name
      ,courseScore.s_score
  from
    student a
      right join
    (
      select
             s_id,
             c_id,
             s_score
      from score
      where c_id = cid
    )courseScore on courseScore.s_id = a.s_id
   left join course courseInfo on courseInfo.c_id = courseScore.c_id
  order by courseScore.s_score desc ;
end;

call getmaxByCourse('01');

-- 使用存储过程插入数据
create procedure insertStudent(in sn int)
begin
  declare i int;
  set i = 1;
  while i <= sn DO
  insert into
    student
  values(
          CONCAT(replace(replace(replace(convert(now(),char),'-',''),' ',''),':',''),LPAD(CEIL(RAND()*10000000000),10,'0')),
          createName(),
           concat('199',convert(ceil(rand()*9),char(1)),'-',
           LPAD(convert(ceil(rand()*12),char(2)),2,'0'),
           '-',
           LPAD(convert(ceil(rand()*28),char(2)),2,'0')),
          if(rand()*2>1,'男','女')
);
set i=i+1;
end while;
end;

call insertStudent(10);

-- 使用存储过程删除数据
create procedure deleteStudent(in s_id char(5))
begin
delete from score where s_id = s_id;
delete from student where s_id = s_id;
end

使用存储过程备份表
create procedure backupStudent(in tn varchar(20))
begin
  set @sql = CONCAT('
  create table ',tn,' (
s_id varchar(64) not null primary key,
s_name varchar(10),
s_birth date,
s_sex char(1)
)');
  PREPARE CT FROM @sql;
  execute CT;
  set @sql1 = CONCAT('
insert into ',tn,' (s_id,s_name,s_birth,s_sex)
select s_id,s_name,s_birth,s_sex from student');
  PREPARE CT1 FROM @sql1;
  execute CT1;
end;

call backupStudent('myStudent');

-- 查看和删除存储过程
show create procedure backupStudent;
drop procedure backupStudent;

第五章 触发器

增删改触发器，实现更为精细和更为复杂的数据控制能力。

安全性、审计、实现复杂的数据完整规则。

-- 数据准备
create table product
(
  product_id int primary key auto_increment,
  name varchar(20),
  num int,
  price decimal
);

create table order_info
(
  order_no int primary key auto_increment,
  product_id int,
  amount int
);

insert into product (name,num,price)
values
('墨水',100,5),
('宣纸',100,10),
('毛笔',100,2);

-- 创建触发器，新订单产生，减少商品库存
create TRIGGER tug
  AFTER insert on order_info
  for each ROW
begin
  update product set num=num-NEW.amount where product_id = NEW.product_id;
end;

insert into order_info (product_id,amount) values (1,20);

-- 创建触发器 当下单数量超过库存数量时报错
create TRIGGER tug1
  BEFORE insert on order_info
  for each row
begin
  declare m int;
  declare message varchar(20);
  select num into m from product where product.product_id = new.product_id;
  if m < new.amount
  then select A001 into message;
  else
    update product set num=num-NEW.amount where product_id = NEW.product_id;
  end if;
end;

insert into order_info (product_id,amount) values (1,10);

-- 创建触发器 周五不能下单
create TRIGGER weekendLimit
  before insert on order_info
  for each ROW
begin
  declare message varchar(10);
  if DAYNAME(now())='friday'
  then select E001 into message;
  else set message = 'insert success';
end if;
end;

-- 限制数据更改范围
-- 创建触发器控制价格涨幅不超过20%
create trigger limitPrice
  after update on product
  for each ROW
begin
  declare message varchar(20);
  if(NEW.price-OLD.price)*100/OLD.price>20
  then
    select A002 into message;
  else set message='更改成功';
  end if;
end;

update product set price = 21 where product_id =2;

-- 使用触发器实现数据准确性
create trigger limitSex
  before insert on student
  for each row
begin
  declare message varchar(10);
if NEW.s_sex='男' or NEW.s_sex='女'
then
set message = '插入成功';
else select A002 into message;
end if;
end;

insert into student values ('099103','徐志摩','1905-06-03','sss');

-- 正则表达式
select 'abc' regexp '^[abc]';   -- 1 匹配a/b/c开头的字符串
select 'abc' regexp 'c$';  -- 1 匹配c结尾的字符串
select 'abc' regexp '.a';  -- 0 匹配 ...a 只要a位于最后就行
select 'abc' regexp '.b';  -- 1
select 'abc' regexp '.c';  -- 1
select 'abc' regexp 'a.';  -- 1
select 'abc' regexp '[a-z]'; -- 1 只要字符串中有小写字母就行
select 'abc' regexp '[cxd]'; -- 1 只要字符串中有小写字母c/x/d就行
select 'x' regexp '[^abc]'; -- 1 匹配不包含a/b/c的字符
select 'a' regexp '[^abc]'; -- 0 匹配不包含a/b/c的字符
select 'abc' regexp '[^a]'; -- 1
select 'stab' regexp  '.ta*b'; -- 1 a* 匹配0个或多个a
select 'stb' regexp  '.ta*b'; -- 1
select 'color' regexp 'colou?r'; -- 1？ 匹配0次或1次
select 'colour' regexp 'colou?r'; -- 1？ 匹配0次或1次
select 'cloud' regexp 'colour+'; -- 0 ？ 匹配0次或1次
select 'cloud~' regexp 'cloud+'; -- 1 + 匹配1次或多次
select 'clouddd~' regexp 'cloud+'; -- 1 + 匹配1次或多次
select 'sub' regexp  'b'; -- 匹配包含b的字符串
select '010-226-3633' regexp '^010-[0-9]{3}-[0-9]{4}$';
select 'ttt@qq.com' regexp '.+@.+';
select '156336986542' regexp '[1][0-9]{10}';

-- 触发器审计数据库操作

-- 数据准备
create table audit
(
  username varchar(20),
  action varchar(20),
  s_id varchar(20),
  s_name varchar(20),
  oper_time timestamp
);

-- 创建触发器
create trigger insertAudit
  before insert on student
  for each row
begin
  insert into audit values
  (user(),'insert',new.s_id,new.s_name,now());
end;

insert into student values ('099103','徐志','1905-06-03','男');

select * from audit;

第6章 索引

create table stu
(
studentid INT,
sname varchar(10),
cardID varchar(20),
address varchar(20),
mobile char(11)
)engine=myisam

insert stu values (4,'张宇','1222223443423121','石家庄河北师大附中','13865623152'),
(5,'韩磊','1222223443423121','石家庄河北师大中','13865623152'),
(9,'韩红','1222223443423121','石家庄河北师大附中','13865623152'),
(7,'凯哥','1222223443423121','石家庄河北师大附中','13865623152');

explain select * from stu where studentid=9;

alter table stu add primary key(studentid);

alter table stu engine=innodb;

索引的分类

• 普通索引
• 唯一索引 允许有空值不允许有重复值
• 主键 不允许有空值和重复值
• 单列索引
• 组合索引
• 全文索引 char varchar text类型，mysql中只有MYISAM存储引擎支持全文索引

创建索引

创建表时创建索引
index sid_index(studentid)
unique index sid_index(studentid)
index sid_mobile_index(studentid,mobile)

-- 查看创建的索引
show index from stu;

-- 删除索引
drop index sid_index on stu;
alter table stu drop primary key;
alter table stu drop index sid_index;

验证索引查询速度

-- 插入200万条数据
call insertStudent(2000000);

-- 查询数据库索引大小
select concat(round(sum(index_length)/(1024*1024),2),'MB') AS 'Total Index Size' from information_schema.TABLES where table_schema like 'test';

-- 查询数据库数据大小
select concat(round(sum(data_length)/(1024*1024),2),'MB') AS 'Total Index Size' from information_schema.TABLES where table_schema like 'test';

select * from student where s_name like '柳%';

-- 给名字添加索引
alter table student add index sname_index(s_name);

应该给哪些列创建索引？
作为查询条件的列、作为连接条件的列、需要排序的列。
text、ntext、image数据类型定义的列不能索引。

精确查找的时候才会使用索引，范围查找会全表扫描。

第七章 事务和锁

事务的四个特性
原子性Atomic、一致性Consistency、隔离性Isolation、持久性Durability