mysql学习大纲

作者: 黄海佳 | 来源:发表于2018-08-22 23:04 被阅读55次

一、基础知识

1 MySQL数据库概要

2 简单MySQL环境

3 数据的存储和获取

MySQL存储引擎类型

InnoDB 事务型数据库的首选引擎，支持ACID事务，支持行级锁定, MySQL 5.5 起成为默认数据库引擎
MyISAM MySQL 5.0 之前的默认数据库引擎，最为常用。拥有较高的插入，查询速度，但不支持事务
Memory 所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在 MySQL 重新启动时丢失

如何选择存储引擎

除非需要用到某些InnoDB不具备的特性，并且没有其他办法可以替代，否则都应该优先选择InnoDB引擎

如何设置数据表的存储引擎

alter table table_name engine=innodb; (修改表引擎）
show variables like '%storage_engine%'; (查看当前默认的存储引擎)
mysql> show create table table_name; (查看某个表使用的存储引擎)

MySQL 数据类型

（1）数值类型

1 整数类型： int tinyint bigint

2 浮点型：float 和double 不推荐使用浮点型

3 高精度型： decimal （numeric）如：amount DECIMAL(6,2)表示amount列最多存储6位数字，小数位数为2位。

（2）字符类型

1 varchar和char 变长字符和定长字符

2 text和blob 存储文本大数据类型和存储二进制大数据类型（实际很少使用，太浪费资源）

3 varbinary和binary 存储二进制字符

4 enum和set 集合类型，enum可枚举65536个元素而set最多64个

（3）日期和时间类型

DateTime（8个字节） TimeStamp（4）Date（3） Year（1）Time (1)
实际开发中选择TimeStamp作为时间类型的字段，也可以用int

MySQL运算符

1 算数运算符： + - * / %

2 比较运算符： == <==>安全的等于， <>和!= 不等于，<= , >= ,> ,<,
IS NULL ,IS NOT NULL , IN, NOT IN, LIKE(通配符匹配)， REGEXP(正则表达式匹配)

3 逻辑运算符的求值所得结果均为1 (TRUE)、0( FALSE)，这类运算符有逻辑非（NOT或者!）、逻辑与（AND或者&&）、逻辑或（OR或者||）、逻辑异或（XOR）。

4 位操作运算符参与运算符的操作数，按二进制位进行运算。包括位与（&）、位或（|）、位非（~）、位异或（^）、左移（<<）、右移(>>)6种。

数据库操作

创建

CREATE DATABASE db_name;

查看

SHOW DATABASES;
SHOW CREATE DATABASE db_name;

选择

USE db_name;
select database();

删除

DROP DATABASE db_name；

数据表操作

创建数据表视图、索引、同义词、聚簇等如：

CREATE TABLE/VIEW/INDEX/SYN/CLUSTER

create table 表名 (

字段名1 类型 (宽度) 约束条件,

字段名2 类型(宽度) 约束条件,

字段名3 类型(宽度) 约束条件,

.......

);

查看表结构

查看某库有多少个表： SHOW TABLES;
查看表结构:DESC db_name;
查看表的所有字段内容：SELECT * FROM db_name;
查看某些字段SELECT id, port FROM host;

修改表结构

修改一列类型
alter table tab_name modify 列名 类型 [完整性约束条件]［first｜after 字段名］;
alter table users2 modify age tinyint default 20;
alter table users2 modify age int after id;

重命名表结构

修改列名
alter table tab_name change [column] 列名 新列名

类型 [完整性约束条件]［first｜after 字段名］;
alter table users2 change age Age int default 28 first;

修改表名
rename table 表名 to 新表名;

修该表所用的字符集

alter table student character set utf8;

删除表

alter table tab_name drop [column] 列名;

添加主键，删除主键

alter table tab_name add primary key(字段名称,...)
ALTER TABLE person ADD PRIMARY KEY (id);

alter table users drop primary key;

MySQL语句操作

插入记录

INSERT INTO table_name ( field1, field2,...fieldN )

VALUES

( value1, value2,...valueN );

修改记录

UPDATE table_name SET field1=new-value1, field2=new-value2

删除记录

DELETE FROM table_name [WHERE Clause]

[WHERE Clause]

约束

约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。

约束类型：    
primary key  主键
foreign key   外键
unique         唯一
not null        非空
auto_increment  自增 
default       默认值

主键约束

主键约束主键列的数据类型不限，但此列必须是唯一并且非空。当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

创建主键约束

create table temp(
    id int primary key,
   name varchar(20)
);
alter table temp add primary key(id,name);

删除主键约束

alter table temp drop primary key;

外键约束 foreign key

外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系

-- 主表

create table classes(

id int,

name varchar(20),

number int,

primary key(name,number)

);

副表

create table student(

id int auto_increment primary key,

name varchar(20),

classes_name varchar(20),

classes_number int,

/表级别联合外键/

foreign key(classes_name, classes_number) references classes(name, number)

);

删除外键约束

alter table student drop foreign key student_id;

增加外键约束

alter table student add foreign key(classes_name, classes_number) 
references classes(name, number);

唯一约束unique

唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。

创建唯一约束

create table temp(

    id int not null ,
    name varchar(20),
    password varchar(10),
    unique(name,password)

);

alter table temp add unique (name, password);

修改唯一约束

alter table temp modify name varchar(25) unique;

删除约束

alter table temp drop index name;

非空约束 not null 与默认值 default

增加非空约束

alter table temp modify sex varchar(2) not null;

取消非空约束

alter table temp modify sex varchar(2) null;

取消非空约束，增加默认值

alter table temp modify sex varchar(2) default 'abc' null;

5 函数

数学函数（ABS FLOOR RAND PI TRUNCATE ROUND SQRT )

ABS(x) 返回x的绝对值

CEIL(x) 返回大于x的最小整数值

FLOOR(x) 返回小于x的最大整数值

MOD(x,y) 返回x/y的模（余数）

SQRT(x) 返回一个数的平方根

ROUND(x,y) 返回参数x的四舍五入的有y位小数的值

TRUNCATE(x,y) 返回数字x截短为y位小数的结果

PI() 返回pi的值（圆周率）

RAND()返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。

字符串函数( INSERT UPPER UCASE LEFT RTRIM SUBSTRING REVERSE FIELD)

INSERT(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果

LOWER(str) 返回将字符串str中所有字符改变为小写后的结果

UPPER(str) 返回将字符串str中所有字符转变为大写后的结果

LEFT(str,x)返回字符串str中最左边的x个字符

RIGHT(str,x) 返回字符串str中最右边的x个字符

TRIM(str)去除字符串首部和尾部的所有空格

REVERSE(str) 返回颠倒字符串str的结果

FIELD(filed_name,str1,str2,str3,str4)可以用来对SQL中查询结果集进行指定顺序排序
select * from 表名 order by field(id,1,4,2,3) desc;

日期和时间函数(CURDATE CURTIME NOW DATEDIFF ADDDATE SUBDATE)

CURDATE()或CURRENT_DATE() 返回当前的日期

CURTIME()或CURRENT_TIME() 返回当前的时间

NOW() 返回当前的日期和时间

DATE_FORMAT(date,fmt) 依照指定的fmt格式格式化日期date值

DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7)

DAYOFMONTH(date) 返回date是一个月的第几天(1~31)

DAYOFYEAR(date) 返回date是一年的第几天(1~366)

DAYNAME(date) 返回date的星期名，如：

SELECT DAYNAME(CURRENT_DATE);

HOUR(time) 返回time的小时值(0~23)

MINUTE(time) 返回time的分钟值(0~59)

MONTH(date) 返回date的月份值(1~12)

MONTHNAME(date) 返回date的月份名，如：

SELECT MONTHNAME(CURRENT_DATE);

FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式，格式化UNIX时间戳ts

DATE_FORMAT(create_time, '%m-%d-%Y')

条件判断函数

IF(test,t,f) 如果test是真，返回t；否则返回f

IFNULL(arg1,arg2) 如果arg1不是空，返回arg1，否则返回arg2

CASE WHEN[test1] THEN [result1]...ELSE [default] END如果testN是真，则返回resultN，否则返回default
--------------------------------------------------------------------
SELECT
    card_no,
    case 
       when card.status=1 then '已入库'
             when card.status=2 then '已发放'
             when card.status=3 then '注销'
             when card.status=4 then '申请注销' 
       when card.status=5 then '注销申请失败' 
  end as a
-------------------------------------------------------------------
CASE [test] WHEN[val1] THEN [result]...ELSE [default]END 如果test和valN相等，则返回resultN，否则返回default

CASE函数的格式有些复杂，通常如下所示：

CASE [expression to be evaluated]

WHEN [val 1] THEN [result 1]

WHEN [val 2] THEN [result 2]

WHEN [val 3] THEN [result 3]

CASE函数还有另外一种句法，有时使用起来非常方便，如下：

CASE

WHEN [conditional test 1] THEN [result 1]

WHEN [conditional test 2] THEN [result 2]

ELSE [default result]

END

系统信息函数

DATABASE() 返回当前数据库名

BENCHMARK(count,expr) 将表达式expr重复运行count次

CONNECTION_ID() 返回当前客户的连接ID

FOUND_ROWS() 返回最后一个SELECT查询进行检索的总行数

USER()或SYSTEM_USER() 返回当前登陆用户名

VERSION() 返回MySQL服务器的版本

示例：

SELECT DATABASE(),VERSION(),USER();

SELECTBENCHMARK(9999999,LOG(RAND()*PI()));

该例中,MySQL计算LOG(RAND()*PI())表达式9999999次。

加密函数(PASSWORD MD5)

AES_ENCRYPT(str,key) 返回用密钥key对字符串str利用高级加密标准算法加密后的结果，
调用AES_ENCRYPT的结果是一个二进制字符串，以BLOB类型存储

AES_DECRYPT(str,key) 返回用密钥key对字符串str利用高级加密标准算法解密后的结果

DECODE(str,key) 使用key作为密钥解密加密字符串str

ENCRYPT(str,salt) 使用UNIXcrypt()函数，用关键词salt(一个可以惟一确定口令的字符串，就像钥匙一样)加密字符串str

ENCODE(str,key) 使用key作为密钥加密字符串str，调用ENCODE()的结果是一个二进制字符串，它以BLOB类型存储

MD5() 计算字符串str的MD5校验和

PASSWORD(str) 返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。

SHA() 计算字符串str的安全散列算法(SHA)校验和

示例：

SELECT ENCRYPT('root','salt');

SELECT ENCODE('xufeng','key');

SELECT DECODE(ENCODE('xufeng','key'),'key');#加解密放在一起

SELECT AES_ENCRYPT('root','key');

SELECT AES_DECRYPT(AES_ENCRYPT('root','key'),'key');

SELECT MD5('123456');

SELECT SHA('123456');

其他函数

1、格式化函数：FORMAT(x,n)将数字x进行四舍五入保留n位小数

2、不同进制数字转换：

ASCII(s)返回字符串s的第一个字符的ASCII码

BIN(x)返回x的二进制编码

3、IP地址与数字相互转换的函数：

INET_ATON(IP)将IP转换为数字

INET_NTOA(n)将n转换为IP

4、改变字段数据类型的函数：CAST(x AS type)、CONVERT(x,type)两个函数将x变成type类型，
仅仅是改变输出，没有改变表中的字段类型

5、改变字符集的函数CONVERT(s USING cs)函数将字符串s的字符集变成cs。

6 数据查询

基本查询语句

带IN关键字的查询
WHERE s_id IN (101, 102);
WHERE s_id NOT IN (101, 102);

带BETWEEN AND 的范围查询
SELECT f_name, f_price FROM fruits WHERE f_price BETWEEN 2.00 AND 10.20;
SELECT f_name, f_price FROM fruits WHERE f_price NOT BETWEEN 2.00 AND 10.20;

带LIKE的字符匹配查询
'%' 匹配任意长度的字符,甚至包括零字符
SELECT f_name FROM fruits WHERE f_name LIKE 'b%y';

查询空值
SELECT * FROM customers WHERE c_email IS NULL;
SELECT * FROM customers WHERE c_email IS NOT NULL;

带AND OR的多条件查询

查询结果不重复
SELECT DISTINCT 字段名 FROM 表名
查询fruits表中s_id字段的值,返回s_id字段且不得重复
SELECT DISTINCT s_id FROM fruits;

使用LIMIT限制查询记录数量
LIMIT [位置偏移量,] 行数
显示fruits表查询结果的前4行
SELECT * FROM fruits LIMIT 4;

分组查询 group by

当group by单独使用时，只显示出每组的第一条记录，所以group by单独使用时的实际意义不大

group by + group_concat()

(1) group_concat(字段名)可以作为一个输出字段来使用， concat：合并，连接
(2) 表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合
select sex,group_concat(name) from employee group by sex;

group by + 集合函数

(1) 通过group_concat()的启发，我们既然可以统计出每个分组的某字段的值的集合，那么我们也可以通过集合函数来对这个"值的集合"做一些操作
分别统计性别为男/女的人年龄平均值
select sex,avg(age) from employee group by sex;
select sex,count(sex) from employee group by sex;

group by + having

having 条件表达式：用来分组查询后指定一些条件来输出查询结果
(2) having作用和where一样，但having只能用于group by
select sex,count(sex) from employee group by sex having count(sex)>2;

group by + with rollup

(1) with rollup的作用是：在最后新增一行，来记录当前列里所有记录的总和
select sex,group_concat(age) from employee group by sex with rollup;

单表查询

聚合函数查询

COUNT()函数
SUM()函数
AVG()函数
MAX()函数
MIN()函数

连接查询

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。

子查询

合并查询结果

定义表和字段的别名

使用正则表达式查询

实践：使用聚合函数sum对学生成绩进行汇总

二、核心技术

7 索引

MySQL索引概述与分类

创建索引

删除索引

8 存储过程

创建存储过程和存储函数

流程控制语句

调用存储过程和存储函数

查看存储过程和函数

修改存储过程和函数

删除存储过程和函数

捕获存储过程中的错误

实践：使用存储过程实现用户注册

9 触发器

创建MySQL触发器

查看触发器

应用触发器

删除触发器

10 视图

视图的概念与作用

创建视图

查看视图

修改视图

更新视图

删除视图

实践：使用MySQL视图查询学生信息表

11 事务

(1) GRANT：授权。
(2) ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。回滚
(3) COMMIT [WORK]：提交

MySQL事物概述（原子性一致性孤立性持久性）

MySQL事物的创建与存在周期

MySQL行为

事物和性能

MySQL伪事物

12 系统管理

MySQL系统管理概述

数据目录的位置

数据目录的结构

MySQL服务器如何提供对数据的访问

MySQL数据库在文件系统里如何表示

MySQL数据表如何在文件系统里表示

SQL语句如何映射为数据表文件操作

操作系统对数据库和数据表命名的限制

数据目录的结构对系统性能的影响

MySQL状态文件和日志文件

一、高级技术

13 数据库备份与还原

数据备份

数据还原

数据库迁移

表的导出和导入

实践：导出XML文件

14 MySQL性能优化

优化简介

优化查询

优化数据库结构

查询高速缓存

优化多表查询

优化表设计

15 MySQL数据库安全技术

MySQL的基本安全和保护策略

用户和权限管理

MySQL数据库安全技术常见问题

16 MySQL日常管理

连接故障恢复

日志文件管理

MySQL服务器镜像配置

MySQL服务器优化配置

优化MySQL服务器

运行多个MySQL服务器

统计表数据量

use information_schema;
SELECT TABLE_NAME,TABLE_ROWS FROM TABLES WHERE TABLE_SCHEMA='ycxc2_o2o' ORDER BY TABLE_ROWS DESC;