MySql

MySql

Sql语句分类

DDL(Data Definition Language):数据定义语言，用来定义数据库对象：库、表、列等。功能：创建、删除、修改库和表结构。

查看所有数据库:`SHOW DATABASES;

使用数据库:`USE 数据库名;`

删除数据库:`DROP DATABASE 数据库名;

use test；切换数据库

创建表

查看当前数据库中所有表:`SHOW TABLES;

查看表结构:`DESC 表名;

删除表:`DROP table 表名;

修改表:

修改表之添加列:`ALTER TABLE 表名 add (列名 列类型，...，列名 列类型);

修改表之修改列类型:`ALTER TABLE 表名 MODIFY 列名 列的新类型;

修改表之列名称列类型一起修改:`ALTER TABLE 表名 CHANGE 原列名 新列名 列名类型;

修改表之删除列:`ALTER TABLE 表名 DROP 列名;

修改表之修改表名:`ALTER TABLE 表名 RENAME TO 新表名

DML(Data Manipulation Language):数据操作语言，用来定义数据库记录:增、删、改表记录。

插入数据(一次插入就是插入一行)

insert into 表名 (列名1，列名2，列名3) values (列值1，列值2，列值3)

说明:

在数据库中所有的字符串类型，必须使用单引号。

1. (列名1，列名2，列名3)可省略，表示按照表中的顺序插入。但不建议采取这种写法，因为降低了程序的可读性。

修改记录

修改某列的全部值:`update 表名 set 列名1=列值1(，列名2=列值2);`

修改(某行或者多行记录的)列的指定值:`update 表名 set 列名1=列值1 where 列名2=列值2 or 列名3=列值3;

运算符:`=、!=、<>、<、>、>=、<=、between...and、in(…)、is null、not、or、and`，其中in(...)的用法表示集合。例如:`update 表名 set 列名1=列值1 where 列名2=列值2 or 列名2=列值22`用in(...)写成`update 表名 set 列名1=列值1 where 列名2 in(列值2，列值3)

删除数据(删除整行)

`delete from 表名 (where 条件);`不加where条件时会删除表中所有的记录

DCL(Data Control Language):数据控制语言，用来定义访问权限和安全级别。

创建用户

用户只能在指定ip地址上登录mysql:`create user 用户名@IP地址 identified by ‘密码’；

用户可以在任意ip地址上登录:`create user 用户名@‘％’ identified by ‘密码’；

给用户授权

语法:`grant 权限1，…，权限n on 数据库.* to 用户名@IP地址；

其中权限1、2、n可以直接用all关键字代替

撤销授权

语法:`revoke 权限1，…，权限n on 数据库.* from 用户名@ ip地址；

查看权限

查看指定用户的权限:`show grants for 用户名@ip地址；

删除用户

drop user 用户名@ip地址；

DQL(Data Query Language):数据查询语言，用来查询记录。

SQL数据中的属性类型

TINYINT:1字节，小整数值。

SMALLINT:2字节，大整数值。

MEDIUMINT:3字节，大整数值。

FLOAT:单精度浮点数值。

CHAR:字符型，固定长度字符串类型:char(255)

VARCHAR:可变长度字符串类型

TEXT(CLOB):mysql独有的数据类型，字符串类型。

YEAR:年份值，格式为:YYYY

DATA:日期类型，格式为:yyyy-MM-dd。

TIME:时间类型，格式为:hh:mm:ss。

TIMESTAMP:时间戳类型，格式为上面二者的综合。

DATETIME:混合日期和时间值，格式为:YYYYMMDD HHMMSS.

MySQL常见的建表约束

MySQL约束类型

NOT NULL非空约束

PRIMARY KEY主键约束（主关键字），自带非空、唯一、索引

FOREIGN KEY外键约束（外关键字）

DEFAULT默认值（缺省值）

数据查询语言

单表查询

查询所有列:`select * from 表名;`其中＊表示查询所有列，而不是所有行的意思

查询指定列:`select 列1，列2，列n from 表名；

完全重复的记录只显示一次:在查询的列之前添加distinct

列运算

数量类型的列可以做加、减、乘、除:`SELECT sal*5 from 表名；

字符串累类型可以做连续运算(需要用到concat()函数):`select concat(列名1，列名2) from 表名;`其中列名的类型要为字符串。

条件控制

条件查询。在后面添加where指定条件:`select * from 表名 where 列名＝指定值;

模糊查询：当你想查询所有姓张的记录。用到关键字like。

select * from 表名 where 列名 like ‘张_’;

(_代表匹配任意一个字符，％代表匹配0～n个任意字符)

排序(所谓升序和降序都是从上往下排列

升序:`select * form 表名 order by 列名 (ASC );()里面的内容为缺省值；

降序:`select * from 表名 order by 列名 DESC;

使用多列作为排序条件: 当第一列排序条件相同时，根据第二列排序条件排序(当第二列依旧相同时可视情况根据第三例条件排序)

意思是当列名1的值相同时按照列名2的值降序排。

聚合函数

count:`select count(列名) from 表名；纪录行数。

max:`select max(列名) from 表名；列中最大值

min:`select min(列名) from 表名；列中最小值

sum: select sum(列名) from 表名；

avg:select avg(列名) from 表名;

分组查询

语法

select 分组列，聚合函数 from 表名 where 条件 group by 分组列；

select 分组列，聚合函数 from 表名 where 条件 group by 分组列 having 聚合函数或列名(条件)；

LIMIT子句(mysql中独有的语法)

select * from 表名 limit 4，3；

如果一个参数 说明从开始查找三条记录

如果两个参数 说明从第三行起（不算），向后查三条记录

多表查询

内连接

使用INNER JOIN或者直接使用JOIN 进行连接。

在连接条件中使用**等于号（=）运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。

SELECT * from teacher t JOIN course c on t.id = c.t_id

SELECT * from teacher t inner JOIN course c on t.id = c.t_id

外连接（常用）

左连接（左外连接）

LEFT JOIN

SELECT * from teacher t LEFT JOIN course c on t.id = c.t_id

右连接（右外连接）

right JOIN

SELECT * from teacher t right JOIN course c on t.id = c.t_id

全连接，mysql不支持，oracle支持

子查询

where 型子查询

如果是 where 列 =（内层 sql） 则内层 sql 返回的必须是单行单列，单个值。

如果是 where 列 in（内层 sql） 则内层 sql 返回的必须是单列，可以多行。

from 型子查询

查询结果集在结构上可以当成表看，那就可以当成临时表对他进行再次查询：

常用函数

COUNT(col) 统计查询结果的行数

MIN(col) 查询指定列的最小值

MAX(col) 查询指定列的最大值

SUM(col) 求和，返回指定列的总和

AVG(col) 求平均值，返回指定列数据的平均值

数值型函数

CEILING(x) 返回大于x的最小整数值，向上取整

FLOOR(x) 返回小于x的最大整数值，向下取整

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

字符串函数

LENGTH(s) 计算字符串长度函数，返回字符串的字节长度

LOWER(str) 将字符串中的字母转换为小写

UPPER(str) 将字符串中的字母转换为大写

TRIM(str) 删除字符串左右两侧的空格

SUBSTRING 截取字符串，返回从指定位置开始的指定长度的字符换

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

日期和时间函数

- CURDATE 和 CURRENT_DATE 两个函数作用相同，返回当前系统的日期值- CURTIME 和 CURRENT_TIME 两个函数作用相同，返回当前系统的时间值- NOW 和 SYSDATE 两个函数作用相同，返回当前系统的日期和时间值- UNIX_TIMESTAMP 获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数- FROM_UNIXTIME 将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数- MONTH 获取指定日期中的月份- MONTHNAME 获取指定日期中的月份英文名称- DAYNAME 获取指定曰期对应的星期几的英文名称- DAYOFWEEK 获取指定日期对应的一周的索引位置值- WEEK 获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53- DAYOFYEAR 获取指定曰期是一年中的第几天，返回值范围是1~366- DAYOFMONTH 获取指定日期是一个月中是第几天，返回值范围是1~31- YEAR 获取年份，返回值范围是 1970〜2069- DATE_ADD 和 ADDDATE 两个函数功能相同，都是向日期添加指定的时间间隔- DATE_SUB 和 SUBDATE 两个函数功能相同，都是向日期减去指定的时间间隔- ADDTIME 时间加法运算，在原始时间上添加指定的时间- SUBTIME 时间减法运算，在原始时间上减去指定的时间- DATEDIFF 获取两个日期之间间隔，返回参数 1 减去参数 2 的值- DATE_FORMAT 格式化指定的日期，根据参数返回指定格式的值

流程控制函数

CASE WHEN[test1] THEN [result1]...ELSE [default] END 如果testN是真，则返回resultN，否则返回default

CASE [test] WHEN[val1] THEN [result]...ELSE [default]END 如果test和valN相等，则返回resultN，否则返回default

CASE 列名

加密函数

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

Mysql 视图

创建视图

显示视图

show create view 视图名;

删除视图

drop view 视图名;

简介

视图其相当于从原来的数据表中获取部分数据，然后新建一个只可创建、查询和删除的新表来存放这些数据（一般情况下），可以理解成把想要的数据部分截图下来保存，供以后查询用，此时视图只是为了满足某些数据查询而建立的对象。

MySQL事务

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

四大特征

原子性（Atomicity）

一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性（Consistency）

在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。(比如：A向B转账，不可能A扣了钱，B却没有收到)

隔离性（Isolation）

数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。（比u人：A正在从一张银行卡里面取钱，在A取钱的过程中，B不能向这张银行卡打钱）

读未提交：read uncommitted

事物A和事物B，事物A未提交的数据，事物B可以读取到

这里读取到的数据叫做“脏数据”，叫脏读

这种隔离级别最低，这种级别一般是在理论上存在，数据库隔离级别一般都高于该级别

读已提交：read committed

能读到别的事物已经提交的数据。

A事务在本次事务中，对自己操作过的数据，进行了多次读取发现数据不一致，不可重复读。

简单点说就是不能让我好好的重复读，一个事务里读出来的数据都不一样，让不让人干活了。

针对的语句update和delete，会导致不可重复读

可重复读：repeatable read

A事务在本次事务中对未操作的数据进行多次查询，发现第一次没有，第二次出现了就像幻觉一样。或者第一次有而第二次没有。针对delete和insert。

串行化：serializable

事务A和事务B，事务A在操作数据库时，事务B只能排队等待

这种隔离级别很少使用，吞吐量太低，用户体验差

这种级别可以避免“幻像读”，每一次读取的都是数据库中真实存在数据，事务A与事务B串行，而不并发。

别的地方一用这个数据就不能修改删除，直到别的地方提交

持久性（Durability）

事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务提交、回滚

实现的原理简单介绍

mysql每执行一条语句记录一条日志，

start transaction，先记个日志，真正执行执行。

UPDATE user set balance = balance - 200 where id = 1，先记个日志，真正执行。

如果此时断电了，当然不能继续执行了，过了一会来电了，启动mysql会检查日志，发现有个事务没有执行完毕，没有commit，就会安装反向的操作把他回滚了。

UPDATE user set balance = balance + 200 where id = 2，先记个日志，真正执行。

如commit，记个记录，执行，结束了，日志就能删除了。如果rollback，就会按照日志反向操作，回滚。

数据库设计

三范式

设计只是一种思想一种理念，我们按照规范的设计方式设计数据库对我们来说有好处，但绝对不是说一定要严格遵守，三范式能极大的减少数据冗余

第一范式：要求有主键，并且要求每一个字段原子性不可再分

第二范式：要求所有非主键字段完全依赖主键，不能产生部分依赖

第三范式：所有非主键字段和主键字段之间不能产生传递依赖

常见表关系

一对一

一个表和另一张表存在的关系是一对一，此种设计不常用，应为此种关系经常会将多张表合并为一张表。

一对多（第三范式的例子）

分两张表存储，在多的一方添加外键， 

这个外键字段引用一的一方中的主键字段

多对多（第二范式的例子）

分三张表存储，在学生表中存储学生信息，在课程表中存储课程信息，

在成绩表中存储学生和课程的关系信息

MySQL 索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

普通索引

创建索引

CREATE INDEX indexName ON mytable(username(length));

create index myDeptIndex on detail(dept_id);

修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

创建表的时候直接指定

删除索引的语法

DROP INDEX [indexName] ON mytable;

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

CREATE UNIQUE INDEX indexName ON mytable(username(length))

修改表结构

ALTER table mytable ADD UNIQUE [indexName] (username(length))

ALTER 命令添加和删除索引

添加数据表的索引：

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list) 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

ALTER TABLE tbl_name ADD INDEX index_name (column_list)添加普通索引，索引值可出现多次。

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)该语句指定了索引为 FULLTEXT ，用于全文索引。

添加： ALTER TABLE testalter_tbl ADD INDEX (c);

删除：ALTER TABLE testalter_tbl DROP INDEX c;

ALTER 命令添加和删除主键（主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL））

添加：ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;

删除： ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);