MySQL笔记

慕课网课程与MySQL的零距离接触的笔记，尚未整理，错误较多

如何创建数据表
数据表操作
PRIMARY KEY（主键约束）
UNIQUE KEY（唯一约束）
DEFAULT（默认约束）
NOT NULL（非空约束）
记录插入
查找记录

1.约束保证数据的完整性和一致性。
2.约束分为表级约束和列级约束。
3.约束类型包括：
NOT NULL（非空约束）PRIMARY KEY（主键约束）UNIQUE KEY（唯一约束）DEFAULT（默认约束）FOREIGN KEY（外键约束）

 

外键约束的要求

1.父表和子表必须使用相同的存储引擎，而且禁止使用临时表。
2.数据表的存储引擎只能为InnoDB。
3.外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。
4.外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将自动创建索引。

MySQL配置文件
default-storage-engine=INNODB

外键约束的参照操作
1.CASCADE：从父表删除或更新且自动删除或更新子表中匹配的行
2.SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL
3.RESTRICT：拒绝对父表的删除或更新操作。
4.NOACTION：标准SQL的关键字，在MySQL中与RESTRICT相同

 

表级约束与列级约束
对一个数据列建立的约束，称为列级约束.对多个数据列建立的约束，称为表级约束.
列级约束既可以在列定义时声明，也可以在列定义后声明.
表级约束只能在列定义后声明.

 

修改数据表
添加单列
ALTER TABLE tl_name ADD[COLUMN]col_name
column_definition[FIRST |AFTER col_name]

添加多列
ALTER TABLE tbl_name ADD[COLUMN]
（col_name column_definition..…）

删除列
ALTER TABLE tbl_name DROP[COLUMN]col_name

 

添加主键约束
ALTER TABLE tbl_name ADD[CONSTRAINT[symbol]]
PRIMARY KEY[index type]（index_col_name..…）

添加唯一约束
ALTER TABLE tbl_name ADD[CONSTRAINT[symbol]]
UNIQUE INDEXIKEY][index_name][index_type]
（index_col_name..…）

添加外键约束
ALTER TABLE tbl_name ADD[CONSTRAINT[symbol]]
FOREIGN KEY[index name]（index_col_name...）
reference_definition

添加/删除默认约束
ALTER TABLE tbl_name ALTER[COLUMN]col_name
{SET DEFAULT literal |DROP DEFAULT}

删除外键约束
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol

修改列定义
ALTER TABLE tbl_name MODIFY[COLUMN]col_name
column_definition[FIRST|AFTER col_name]

 

修改列名称
ALTER TABLE tbl_name CHANGE[COLUMN]old_col_name
new_col_name column_definition[FIRSTIAFTER col_name]

数据表更名
方法1
ALTER TABLE tbI_name RENAME[TOIAS]new_tbl_name

方法2
RENAME TABLE tbl_name TO new_tbl_name
[，tbl_name2 TO new_tbl_name2]...

约束
按功能划为：NOT NULL，PRIMARY KEY，UNIQUE KEY，DEFAULT，FOREIGN KEY按数据列的数目划为：表级约束，列级约束修改数据表
针对字段的操作：添加/删除字段、修改列定义，修改列名称等针对约束的操作：添加/删除各种约束
针对数据表的操作：数据表更名（两种方式）

NOT NULL（非空约束）功能PRIMARY KEY（主键约束）UNIQUE KEY（唯一约束）DEFAULT（默认约束）FOREIGN KEY（外键约束）数据列的数目
表级约束列级约束
针对字段的操作：添加/删除字段、修改列定义，修改列名称等修改数据表针对约末的操作：添加/删除各种约束针对数据表的操作：数据表更名（两种方式）

 

 

四，操作数据表中的记录

INSERT [INTO]tbl name[（col_name.…）]{VALUES IVALUE}（{expr| DEFAULT}.….）....…

插入记录
INSERT INTO]tbl_name SET col name={expr I DEFAULT）..…
说明：与第一种方式的区别在于，此方法可以使用子查询（SubQuery）

插入记录
INSERT INTO]tbl_name[（col_name...）]SELECT..
说明：此方法可以将查询结果插入到指定数据表。

INSERT [ INTO] tbl_name [(col_name…)]{ VALUES IVALUE}
({ expr I DEFAULT).….)..)..…
INSERT INTO] tbl_name SET col name=(expr I DEFAULT],.…
INSERT INTO] tbl_name [(col_name..)] SELECT..…

更新记录（单表更新）

UPDATE[LOW_PRIORITY]IGNORE]table_reference SET col_name1={expr1lDEFAULT}L，col_name2={expr2lDEFAULT].…
[WHERE where_condition]

单表更新
UPDATE[LOW_PRIORITY]IGNORE]table_reference SET
col_name1={exprlDEFAULT]L col_name2={expr2IDEFAULT].…
[WHERE where_condition]

单表删除
DELETE FROM tbl_name[WHERE where_condition]

查找记录
SELECT select_expr [,select_expr.…]

[

FROM table_references
[WHERE where_condition]
[GROUP BY{col name I position}[ASCI DESC].…]
[HAVING where_condition]
[ORDER BY（col_name l expr I position}[ASCI DESC]，.…]
[LIMIT{[offset，]row_count |row_count OFFSET offset}]

]

SELECT select_expr L select expr.…J FROM table_references
[ WHERE where_condition]
[ GROUP BY { col_name I position)[ ASC I DESC].…1
[ HAVING where_condition]
[ ORDER BY{ col name I expr I position}[ ASC I DESC]..…]
[ LIMIT([ offset,] row_count Irow_count OFFSET offset)]

select_expr查询表达式
每一个表达式表示想要的一列，必须有至少一个。
多个列之间以英文逗号分隔。
星号（*）表示所有列。tbl_name.*可以表示命名表的所有列。
查询表达式可以使用[AS]alias_name为其赋予别名。
别名可用于GROUP BY，ORDRE BY或HAVING子句。

WHERE条件表达式
对记录进行过滤，如果没有指定WHERE子句，则显示所有记录。在WHERE表达式中，可以使用MySQL支持的函数或运算符。

GROUP BY
查询结果分组
[GROUP BY{col_name I position}[ASC I DESC].…]

HAVING
分组条件
[HAVING where_condition]

ORDER BY
对查询结果进行排序
[ORDER BY{col_name I expr I position}[ASCI DESC].…]

LIMIT限制查询结果返回的数量
[LIMIT{[offset，]row_count I row_count OFFSET offset）]

 

五，子查询

用ANY、SOME或ALL修饰的比较运算符
operand comparison_operator ANY（subquery）
operand comparison_operator SOME（subquery）
operand comparison_operator ALL（subquery）

使用[NOT]IN的子查询
语法结构
operand comparison_operator[NOT]IN（subquery）
=ANY 运算符与IN等效。
！=ALL或<>ALL运符与NOTIN等效。

使用[NOT]EXISTS的子查询

如果子查询返回任何行，EXISTS将返回TRUE；否则为FALSE。

 

INSERT...SELECT
将查询结果写入数据表
INSERT[INTO]tbl_name[（col_name..….）]
SELECT.…

多表更新
UPDATE table_references
SET col_name1={expr1|DEFAULT}
Lcol_name2={expr2IDEFAULT}]..
[WHERE where_condition]

语法结构
table_reference
[INNERI CROSS]JOIN I（LEFTIRIGHT][OUTER]JOIN}
table_reference
ON conditional_expr

连接类型
INNERJOIN，内连接
在MySQL中，JOIN，CROSS JOIN和INNER JOIN是等价的。
LEFT[OUTER]JOIN，左外连接RIGHT[OUTER]JOIN，右外连接

CREATE..…SELECT
创建数据表同时将查询结果写入到数据表
CREATE TABLE IF NOT EXISTS]tbl_name
[（create_definition..….）]
select_statement

连接
MySQL在SELECT语句、多表更新、多表删除语句中支持JOIN操作。

数据表参照
table_reference tbl_name[[AS]alias]I table_subquery[AS]alias数据表可以使用tbl_name AS alias_name或tbl_name alias_name赋予别名。
table_subquery可以作为子查询使用在FROM子句中，这样的子查询必须为其赋予别名。

连接条件
使用ON关键字来设定连接条件，也可以使用WHERE来代替。
通常使用ON关键字来设定连接条件，使用WHERE关键字进行结果集记录的过滤。

外连接
A LEFT JOIN B join_condition数据表B的结果集依赖数据表A。
数据表A的结果集根据左连接条件依赖所有数据表（B表除外）。
左外连接条件决定如何检索数据表B（在没有指定WHERE条件的情况下）。
如果数据表A的某条记录符合WHERE条件，但是在数据表B不存在符合连接条件的记录，将生成一个所有列为空的额外的B行。

如果使用内连接查找的记录在连接数据表中不存在，并且在WHERE子句中尝试以下操作：col_namd IS NULL时，如果col_name被定义为NOT NULL，MySQL将在找到符合连执着条件的记录后停止搜索更多的行。 

 

多表删除
DELETE tbl_name[.*][，tbl_name[.*]].…
FROM table_references
[WHERE where_condition]

MySQL数据库
1、字符函数
2、数值运算符与函数
3、比较运算符与函数
4、日期时间函数
5、信息函数
6、聚合函数
7、加密函数

字符函数
函数名称描述
CONCATO字符连接
CONCAT_WS（O使用指定的分隔符进行字符连接
FORMATO数字格式化
LOWERO转换成小写字母
UPPERO转换成大写字母
LEFTO获取左侧字符
RIGHTO获取右侧字符
名称|描述
LENGTH（）获取字符串长度
LTRIM0删除前导空格
RTRIM0删除后续空格
TRIM0删除前导和后续空格
SUBSTRING（）字符串截取
[NOT]LIKE模式匹配
REPLACEO字符串替换

数值运算符与函数
名称描述
CEIL（O进一取整
DIV整数除法
FLOORO舍一取整
MOD取余数（取模）
POWERO幂运算
ROUNDO四舍五入
TRUNCATE（）数字截取

比较运算符与函数
名称|描述
[NOT]BETWEEN..AND...[不]在范围之内
[NOT]INO[]在列出值范围内
IS[NOT]NULL[为空

日期时间函数
名称名称
NOW0当前日期和时间
CURDATEO当前日期
CURTIMEO当前时间
DATE_ADDO日期变化
DATEDIFF）日期差值
DATE_FORMATO日期格式化

信息函数
名称|描述
CONNECTION_IDO连接ID
DATEBASE（）当前数据库
LAST_INSERT_ID（O最后插入记录的ID号
USERO当前用户
VERSION）版本信息

加密函数
名称描述
MD50（）信息摘要算法
PASSWORD（）密码算法

 

七，自定义函数

自定义函数
用户自定义函数（user-defined function，UDF）是一种对MySQL扩展的途径，其用法与内置函数相同。

自定义函数的两个必要条件：
（1）参数
（2）返回值

函数可以返回任意类型的值，同样可以接收这些类型的参数

创建自定义函数
CREATE FUNCTION function_name
RETURNS
{STRINGIINTEGERIREALIDECIMAL}
routine_body

关于函数体
（1）函数体由合法的SQL语句构成；
（2）函数体可以是简单的SELECT或INSERT语句；
（3）函数体如果为复合结构则使用BEGIN...END语句；
（4）复合结构可以包含声明，循环，控制结构；

mysq1>CREATE FUNCTION f1（）RETURNS VARCHAR（30）
          ->RETURN DATE_FORMAT（NOW（），'%Y年%m月%d日H点：%i分：%s秒’）；

删除函数
DROP FUNCTION[IF EXISTS]function_name

自定义函数：简称UDF；是对MySQL扩展的一种途径创建自定义函数：CREATE FUNCTION....
自定义函数两个必要条件：参数：可以有零个或多个
返回值：只能有一个返回值
具有复合结构的函数体需要使用BEGIN...END来包含

八，存储过程

存储过程是SQL 语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理

存储过程的优点
增强SQL语句的功能和灵活性
实现较快的执行速度
减少网络流量

创建存储过程
CREATE
[DEFINER={user I CURRENT_USER}]
PROCEDURE sp_name（[proc_parameterL..…J]）
[characteristic..…] routine-body
proc_parameter：
[IN IOUTIINOUT]param_name type

参数
IN，表示该参数的值必须在调用存储过程时指定OUT，表示该参数的值可以被存储过程改变，并且可以返回INOUT，表示该参数的调用时指定，并且可以被改变和返回

特性
COMMENT：注释
CONTAINS SQL：包含SQL语句，但不包含读或写数据的语句NO SQL：不包含SQL语句
READS SQL DATA：包含读数据的语句MODIFIES SQL DATA：包含写数据的语句SQL SECURITY{DEFINER |INVOKER}指明谁有权限来执行

过程体
过程体由合法的SQL语句构成；过程体可以是任意SQL语句；过程体如果为复合结构则使用BEGIN..…END语句；复合结构可以包含声明，循环，控制结构；

调用存储过程
CALL sp_name（[parameter....]）
CALL sp_name[()]

修改存储过程
ALTER PROCEDURE sp_name[characteristic.…J
COMMENT'string'
I{CONTAINS SQL I NO SQL I READS SQL DATA I MODIFIES SQL DATA}
ISQL SECURITY{DEFINER IINVOKER}

存储过程与自定义函数的区别
存储过程实现的功能要复杂一些；而函数的针对性更强存储过程可以返回多个值；函数只能有一个返回值存储过程一般独立的来执行；而函数可以作为其他SQL语句的组成部分来出现。

课程回顾：
1、存储过程：是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。
2、参数：输入类型输出类型输入&&输出
3、创建：CREATE...PROCEDURE..…
4、注意事项：
（1）创建存储过程或者自定义函数时需要通过delimiter语句修改定界符。
（2）如果函数体或过程体有多个语句，需要包含在BEGIN..…END语句块中。
（3）存储过程通过call来调用。

 

九，存储引擎

存储引擎
MySQL可以将数据以不同的技术存储在文件（内存）中，这种技术就称为存储引擎。
每一种存储引擎使用不同的存储机制、索引技巧、锁定水平，最终提供广泛且不同的功能。

存储引擎
-MyISAM
-InnoDB
-Memory
-CSV
-Archive

并发控制
-当多个连接对记录进行修改时保证数据的一致性和完整性。

锁
-共享锁（读锁）：在同一时间段内，多个用户可以读取同一个资源，读取过程中数据不会发生任何变化。
-排他锁（写锁）：在任何时候只能有一个用户写入资源，当进行写锁时会阻塞其他的读锁或者写锁操作。

锁颗粒
-表锁，是一种开销最小的锁策略。

-行锁，是一种开销最大的锁策略。

事务
-事务用于保证数据库的完整性

事务的特性
-原子性（Atomicity）
-一致性（Consistency）
-隔离性（Isolation）
-持久性（Durability）

索引
-是对数据表中一列或多列的值进行排序的一种结构。

修改存储引擎的方法
通过创建数据表命令实现
-CREATE TABLE table_name（
.·
）ENGINE=engine；

-存储引擎
MyISAM：存储限制可达256TB，支持索引、表级锁定、数据压缩。
InnoDB：存储限制为64TB，支持事务和索引，锁颗粒为行锁。