MySQL学习笔记：总结

概述

数据库（DB），一种专门存储信息和维护信息的容器。

数据库管理系统（DBMS）管理数据库的软件。

数据字典：存放的各种对象的定义信息和其他一些辅助管理信息，包括名字、结构、位置、类型等。

数据库的类型：

• 网状型数据库
• 层次型数据库
• 关系型数据库

mysql的体系结构：

1. 连接层
2. sql层
3. 存储引擎层

字符集：

utf8

客户端字符集、连接层字符集、数据库字符集、结果集字符集、服务器字符集

数据库管理

创建数据库：create database [if not existe] databasename

删除数据库：drop database databasename

E-R模型由实体、属性和关系三个基本要素构成。

实体之间的关系：一对一，一对多，多对多

五个约束类型：

• NOT NULL：非空约束
• UNIQUE：唯一性约束
• PRIMARY KEY：主键约束
• FOREIGN KEY：外键约束
• CHECK：检查性约束

CHECK约束可以使用ENUM类型、SET类型或者触发器

数据类型

• 整数类型：int
• 小数类型：float，double，decimal
• 日期和时间类型：date，datetime，timestamp，time，year
• 字符串类型：char，varchar，enum，set

创建表：create table 表名

快速建表（复制表）：create table newtablename like 源表、create table newtablename as select * from 源表

删除表：drop table tablename

修改表：

• ALTER TABLE 表名 ADD 列名 数据类型和长度 列属性 //添加列名
• ALTER TABLE 表名 MODIFY 列名 新数据类型和长度  新列属性  //修改列名属性
• ALTER TABLE 表名 CHANGE 列名 新列名 数据类型和长度 //修改列名名称
• ALTER TABLE 表名 DROP COLUMN 列名  //删除列名

修改约束：

• ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型（字段名） //添加约束
• ALTER TABLE 表名 DROP PRIMARY KEY //删除主键约束
• ALTER TABLE 表名 DROP FOREIGN KEY 约束名 //删除外键约束
• ALTER TABLE 表名 DROP INDEX 索引名 //删除唯一性约束

数据表管理

插入数据：INSERT INTO table （字段名，……）VALUES（值1，……）

批量插入：

• INSERT INTO 表名[(字段列表)] VALUES (值列表1),(值列表2),…(值列表n); 
• INSERT INTO 表名[(列名1[,列名2，…，列名n])] 子查询 ；

修改数据：UPDATE   table SET    字段名=新值，…… where 限定条件

删除数据：DELETE FROM  表名 WHERE 限定条件

截断表：TRUNCATE TABLE table; 

TRUNCATE和DELETE区别

• TRUNCATE是DDL，只能删除表中所有记录，释放存储空间，使用ROLLBACK不可以回滚。
• DELETE是DML，可以删除指定记录，不释放存储空间，使用ROLLBACK可以回滚。

事务：由一个或多个SQL语句所组成的操作序列，要么全部执行成功，要么全部执行失败。

事务两条关键语句：commit（提交）、rollback（回滚）

简单查询

五类结构化查询语言：

• 数据查询语言（DQL）
• 数据操作语言（DML）
• 事务处理语言（TPL）
• 数据控制语言（DCL）
• 数据定义语言（DDL）

order by 子句：排列，最后执行，ASC：升序，DESC：降序

分页关键字：limit [start,]length;

start表示从第几行记录开始输出，length表示输出的记录行数。

函数

数值函数

• CEIL(x)、CEILING(x)：返回大于或者等于x的最小整数值；
• FLOOR(x)：返回小于或者等于x的最大整数值;
• ROUND(x,y):返回保留小数点后面y位，四舍五入的整数；
• TRUNCATE(x,y):返回被舍弃的小数点后y位的数字x；
• RAND()：每次产生不同的随机数；

字符串函数

• LENGTH(str):返回字符串str的长度；
• CONCAT(s1,s2,...)： 字符串连接；
• CONCAT_WS(x,s1,s2,…)：字符串连接, x是其它参数的分隔符;
• INSERT(s1,x,len,s2) ：返回字符串s1，s1中插入字符串s2；
• LOWER (str)|LCASE (str)：将字符串全部转换成小写字母；
• UPPER(str)|UCASE(str)：将字符串全部转换成大写字母；
• LEFT(s,n)：返回最左边指定长度的字符；
• RIGHT(s,n)：返回最右边指定长度的字符；
• LPAD(s1,len,s2)| RPAD(s1,len,s2) ：填充字符串函数；
• TRIM(s1 FROM s)|LTRIM(s）|RTRIM(s):删除空格函数；
• REPLACE（s，s1，s2）：字符串替换函数；
• SUBSTRING(s,n,len)：获取子串函数；
• LOCATE(str1,str)|POSITION(str1 IN str)|INSTR(str, str1)：匹配子串开始位置函数;
• REVERSE(s)：将字符串s反转；
• ELT(N,字符串1,字符串2,字符串3,…)：返回指定位置函数;

日期和时间函数

• CURDATE()和CURRENT_DATE() ：获取当前日期函数;
• NOW()：返回服务器的当前日期和时间；
• CURTIME(）：返回当前时间，只包含时分秒；
• TIMEDIFF(expr1, expr2)：返回两个日期相减相差的时间数；
• DATEDIFF(expr1, expr2)：返回两个日期相减相差的天数；
• DATE(date)、TIME(date)、YEAR（date）：选取日期时间的各个部分：
• EXTRACT(unit  FROM  date)：从日期中抽取出某个单独的部分或组合；
• DATE_FORMAT(date,format)：格式化日期；
• TIME_FORMATE(time,formate）：格式化时间；

流程控制函数

• CASE
• IF
• IFNULL
• NULLIF

其他函数

• Password(str)  :从原文密码str 计算并返回密码字符串，当参数为NULL 时返回NULL。
• Md5():为字符串算出一个MD5 128 比特检查和。

多表查询

连接类型：

按连接条件分：等值连接、非等值连接；

按其他连接方法分：外部连接（左外连接、右外连接）、内部连接（自身连接）

sql-199标准连接方式：交叉连接、自然连接、using子句、OUTER JOIN连接

高级查询

常用分组函数：

• MIN：最小值
• MAX：最大值
• SUM：总和
• AVG：平均值
• COUNT：返回满足条件的每组记录条数

GROUP BY子句：创建分组

Having子句：限制组

子查询：括号内的查询叫做子查询，也叫内部查询，先于主查询执行。

子查询可嵌套在：where子句、having子句、from子句

单行运算符：>、=、>=、<、<>、<=

多行运算符： IN、ANY、ALL

视图

视图是逻辑上来自一个或多个表的数据集合

视图分类：简单视图、复制视图

创建视图：CREATE VIEW view_name [(字段名称)]   AS 一条完整的SELECT语句
 [WITH [CASCADED | LOCAL] CHECK OPTION]

修改视图：CREATE OR REPLACE VIEW

删除视图：DROP VIEW view; 

索引

索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录。

其作用是提高对表中数据的查询速度

索引分类

• 普通索引
• 惟一性索引
• 全文索引
• 单列索引
• 多列索引
• 空间索引

创建索引有三种方式

• 创建表的时候创建索引
• 在已经存在的表上创建索引
• 使用ALTER TABLE语句来创建索引

用户和权限管理

创建新用户：create user 'username'@'localhost' identified by 'password';

删除账户：DROP USER user_name[, user_name] [,…];

修改用户名：RENAME USER old_user TO new_user

修改密码：ALTER USER userIDENTIFIED BY 'password';

授予权限：grant 权限1 ，…… on *.* to 'username'@localhot identified by 'password';

收回权限：revoke all privileges,grant option from 'username'@'hostname'[,'username'@'hostname'][,…n];

查看权限：SHOW GRANTS FOR 'username'@'hostname';