SQL(struct query language) 结构化查询语言
1)mysql语句规范
- 关键字与函数名称全部大写
- 数据库名称，表名称，字段名称全部小写
- SQL语句必须以分号结尾
2)mysql语句
修改分隔符 DELIMITER char(修改后的分隔符)
创建数据库：
CREATE {DATABASE | SCHEMA} [db_name]
[DEFAULT] CHARACTER SET [=] characterset_name
修改数据库
ALTER {DATABASE | SCHEMA} [db_name]
[DEFAULT] CHARACTER SET [=] characterset_name
删除数据库
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
3)数据类型
整型：TINYINT 一个字节
SMALLINT 两个字节
MEDIUMINT 三个字节
INT 四个字节
BIGINT 八个字节
浮点型：
FLOAT[(M,D)] M是数字总位数，D是小数点后面的位数， 如果M和D被省略，根据硬件允许的限制来保存值。单精度浮点数精确到大约小数点后七位
DOUBLE[(M,D)]
日期：
YEAR
TIME
DATE
DATETIME
TIMESTAMP
字符型：
mysql varchar(50) 不管中文 还是英文 都是存50个的，但是一个表中所有varchar字段的总长度跟编码有关，如果是utf-8， 那么大概65535/3，如果是gbk，那么大概65535/2.
CHAR(M) M个字节，0<=M<=255
VARCHAR(M) L+1个字节，其中L<=M,且0<=M<=65535
TINYTEXT L+1个字节，其中L<2^8
TEXT L+2个字节，其中L<2^16
MEDIUMTEXT L+3个字节，其中L<2^24
LONGTEXT L+4个字节，其中L<2^32
ENUM('value1','value2',....) 一或2个字节，取决于枚举值的个数（最多65535个值）
SET('value1','vaiue2',.....) 1,2,3,4或者8个字节，取决于set成员的数目（最多64个成员）
4）数据表
数据表（或称表）是数据库最重要的组成部分之一，是其他对象的基础
插入记录：
INSERT[INFO] tab1_name[(col_name,...)]VALUES(VAL,...)
查找记录：
SELECT exper,...FROM tab1_name
5）空值与非空
NULL,字段值可以为空
NOT NULL , 字段值禁止为空
6）AUTO_INCREMENT
自动编号，且必须与主键组合使用
默认情况下，起始值为1，每次的增量为1
7）PRIMARY KEY（一张数据表只能有一个主键）
主键约束
每张数据表只能存在一个主键
主键保证记录的唯一性
主键自动为NOT NULL
8）UNIQUE KEY
唯一约束
唯一约束可以保证记录的唯一性
唯一约束的字段可以为空值（null）？？？？
每张数据表可以存在多个唯一约束
9）DEFAULT
默认值（默认约束）
当插入记录时，如果没有明确为字段负值，则自动赋予默认值
10）FOREIGN KEY
外键约束
保持数据一致性，完整性
实现一对一或一对多关系
要求：
1.父表和子表必须使用相同的存储引擎，而且禁止使用临时表。
2.数据表的存储引擎只能为InnoDB.
3.外键和参照列必须具有相似的数据类型。其中数字的长度或是否
有符号位必须相同，而字符的长度则可以不同
4.外键列和参照列必须创建索引，如果外键列不存在索引的话，MySQL将
自动创建索引。
外键约束的参照操作
1.CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行
2.SET NULL : 从父表删除或更新行，并设置子表中的外键列为null。
如果使用该选项，必须保证子表列没有指定not null
3.RESTRICT: 拒绝父表的删除或更新操作
4.NO ACTION:标准SQL 关键字，在MySQL中与RESTRICT 相同
11）约束
1.约束保证数据的完整性和一致性
2.约束分为表级约束和列级约束（根据操作数目的多少来划分）
对一个数据列建立的约束，称为列级约束，
对多个数据列建立的约束，称为表级约束
列级约束既可以在列定义时声明，也可以在列定义后声明
表级约束只能在列定义后声明。
3.约束类型包括：
NOT NULL(非空约束)
PRIMARY KEY(主键约束)
UNIQUE KEY(唯一约束)
DEFAULT(默认约束)
FOREIGN KEY(外键约束)
12）修改数据表
1：添加单列
ALTER TABLE tbl_name ADD[COLUMN]col_name column_definition
[FIRST|AFTER col_name]
2：添加多列
ALTER TABLE tbl_name ADD[COLUMN] (col_name column_definition,.....)
3:删除列
ALTER TABLE tbl_name DROP[COLUMN] col_name
4:添加主键约束
ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]]
PRIMARY KEY[index_type](index_col_name,...)
5.添加唯一约束
ALTER TABLE tbl_nameADD[CONSTRAINT[symbol]]
UNIQUE [INDEX|KEY][index_name][index_type]
(index_col_name,....)
6:添加外键约束
ALTERTABLE tbl_name ADD[CONSTRAINT[symbol]]
FOREIGN KEY [index_name](index_col_name,...)
reference_definition
7.添加/删除默认约束
ALTER TABLE tbl_name ALTER[column] col_name
{SET DEFAULT literal|DROP DEFAULT}
8.删除主键约束
ALTER TABLE tbl_name DROP PRIMARY KEY
9.删除唯一约束
ALTER TABLE tbl_name DROP {INDEX|KEY} index_name
10.删除外键约束
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol
11.修改列定义
ALTER TABLE tbl_name MODIFY[COLUMN]col_name
column_definition [FIRST|AFTER col_name]
12.修改列名称
ALTER TABLE tbl_name CHANGE[COLUMN] old_col_name
new_col_name column_definition [FIRST|AFTER col_name]
13.数据表更名
方法1：
ALTER TABLE tbl_name RENAME[TO|AS] new_tbl_name
方法2：
ALTER TABLE tbl_name TO new_tbl_name
[,tbl_name2 TO new_tbl_name2]...
13)操作数据表中的记录
1.插入记录
方法1：
INSERT [INTO] tbl_name[(col_name,...)] {VALUES|VALUE}
({expr|DEFAULT},...),(...),...
方法2：
INSERT [INTO] tbl_name SET col_name ={expr|DEFAULT},...
说明：与第一种方式的区别在于，此方法可以使用子查询（SubQuery）
方法3：
INSERT [INTO] tbl_name [(col_name,...)]SELECT...
说明：此方法可以将查询结果插入到指定数据表。
2.更新记录
1.单表更新：
UPDATE[LOW_PRIORITY][IGNORE]table_reference SET
col_name1={expr1|DEFAULT}[,col_name2={expr2|DEFAULT}]...
[WHERE where_condition]
2.多表更新：
UPDATE table_references SET col_name1={expr1|DEFAULT}
[,col_name2={expr2|DEFAULT}]...[WHERE where_condition]
3.创建数据表的同时将查询结果写入到数据表CREATE...SELECT
CREATE TABLE [IF NOT EXISTS] tbl_name
[(create_definition),...] select_statement
3.删除记录
单表删除：
DELETE FROM tbl_name [WHERE where_condition]
多表删除：
DELETE tbl_name[.*][,tbl_name[.*]]...
FROM table_references
[WHERE where_condition]
4.查找记录
SELECT select_expr [,select_expr...]
[
FROM table_references
[WHERE where_condition]
[GROUP BY{col_name|position}[ASC|DESC],...]
[HAVING where_condition]
[ORDER BY{col_name|expr|position}[ASC|DESC],...]
[LIMIT{[offset,]row_count OFFSET offset}]
]
5.查询表达式：select_expr
每一个表达式表示想要的一列，必须有至少一个。
多个列之间以英文逗号隔开
星号（*）表示所有列。tbl_name.*可以表示命名表的所有列
查询表达式可以使用[AS] alias_name 为其赋予别名
别名可用于GROUP BY,ORDE BY 或HAVING子句。
6.条件表达式 WHERE
对记录进行过滤，如果没有指定WHERE子句，则显示所有记录
在WHERE表达式中，可以使用MySQL支持的函数或用算符
7. 查询结果分组 GROUP BY
[GROUP BY{col_name|position}[ASC|DESC],...]
其中ASC是升序，DESC是降序
8.分组条件HAVING
[HAVING where_condition]
9.对查询结果进行排序
[ORDER BY{col_name|expr|position} [ASC|DESC],...]
10.限制查询结果返回的数量
[LIMIT{[offset,]row_count|row_count OFFSET offset}]
注意：偏移值offset是从0开始计算
14）子查询
子查询（Subquery）是指出现在其他SQL语句内的SELECT子句。
例如：
SELECT*FROM t1 WHERE col1 = (SELECT col2 FROM t2);
其中SELECT*FROM t1,称为Quter Query/Outer Statement
SELECT col2 FROM t2,称为SubQuery
子查询指嵌套在查询内部，且必须始终出现在圆括号内。
子查询可以包含多个关键字或条件如 DISTINCT. GROUP BY. ORDER BY.LIMIT.函数等等
子查询的外层查询可以是：SELECT INSERT UPDATE SET或DO。
1.子查询返回值
子查询可以返回标量，一行，一列或子查询
2.使用比较运算符的子查询
= > < >= <= <> != <=>
语法结构
operand comparison_operator subquery
3.用ANY SOME 或ALL修饰的比较运算符
operandcomparison_operator ANY(subquery)
operand comparison_operator SOME(subquery)
operand comparison_operator ALL (subquery)
| 项目 | ANY | SOME | ALL |
|---|---|---|---|
| > >= | 最小值 | 最小值 | 最大值 |
| < <= | 最大值 | 最大值 | 最小值 |
| = | 任意值 | 任意值 | |
| <> != | 任意值 |
4.使用[NOT]IN 的子查询
语法结构
operand comparison_operator[NOT]IN(subquery)
=ANY运算符与IN等效
!=ALL或<>ALL运符与NOT IN等效
5.使用[NOT] EXISTS的子查询
如果子查询返回任何行，EXISTS将返回TRUE;否则为FALSE.
15)连接
MySQL在SELECT语句，多表更新，多表删除语句中支持JOIN操作。
语法结构：
table_reference
{[INNER|CROSS] JOIN |{LEFT | RIGHT}[OUTER] JOIN}
table_reference
ON condition_expr
数据表参照：
table_reference
tbl_name[[AS]alias]|table_subquery[AS] alias
数据表可以使用tbl_name AS alias_name
或tbl_name alias_name 赋予别名。
table_subquery可以作为子查询使用在FROM子句中，
这样的子查询必须为其赋予别名。
1.连接类型：
INNER JOIN 内连接
仅显示符合连接条件的记录
两表中的共有部分
在mysql中，JOIN CROSS JOIN和 INNER JOIN是等价的
LEFT[OUTER] JOIN 左外连接
显示左表的全部记录及右表符合连接条件的记录
说明：
A LEFT JOIN B join_condition
数据表B的结果集依赖数据表A
数据表A的结果集根据左连接条件依赖所有数据表（B除外）
左外连接条件决定如何检索数据表B（在没有指定WHERE条件的情况下）。
如果数据表A的某条记录符合WHERE条件，但是在数据表B不存在符合连接条件的记录，将生成一个所有列为空的额外的B行
RIGHT [OUTER] JOIN 右外连接
显示右表的全部记录及左表符合连接条件的记录
如果使用内连接查找的记录在连接数据表中不存在，并且在WHERE
子句中尝试以下操作：col_name IS NULL 时，如果col_name被定
义为 NOT NULL ,MySQL将在找到符合连接条件的记录后停止搜索
更多行。
2.连接条件：
使用ON关键字来设定连接条件，也可以使用WHERE来代替。
通常使用ON关键字来设定连接条件
使用WHERE关键字进行结果集记录的过滤
16）无限级分类表设计
1）自身连接
同一个数据表对其自身进行连接。
17)MySQL中的函数
根据功能来分：
1.字符函数
CONCAT() 字符连接
CONCAT_WS() 使用指定的分隔符进行字符连接
FORMAT() 数字格式化
LOWER() 转换成小写字母
UPPER() 转换成大写字母
LEFT() 获取左侧字符
RIGHT() 获取右侧字符
LENGTH() 获取字符串长度
LTRIM() 删除前导空格
RTRIM() 删除后续空格
TRIM() 删除前导和后续空格 还可以删除特定的字符
SUBSTRING() 字符串截取 %代表任意个字符，_代表任意一个字符
[NOT]LIKE 模式匹配
REPLACE() 字符串替换
2.数值运算符与函数
CEIL() 进一取整
DIV 整数除法
FLOOR() 舍一取整
MOD 取余数（取模）
POWER() 幂运算
ROUND() 四舍五入
TRUNCATE() 数字截取
3.比较运算符与函数
[NOT]BETWEEN...AND... [不]在范围之内
[NOT]IN() [不]在列出值范围内
IS[NOT] NULL [不]为空
4.日期时间函数
NOW() 当前日期和时间
CURDATE() 当前日期
CURTIME() 当前时间
DATE_ADD() 日期变化
DATEDIFF() 日期差值
DATE_FROMAT() 日期格式化
5.信息函数
CONNECTION_ID() 连接ID
DATEBASE() 当前数据库
LAST_INSERT_ID() 最后插入记录的ID号
USER() 当前用户
VERSION() 版本信息
6.聚合函数
AVG() 平均值
COUNT() 计数
MAX() 最大值
MIN() 最小值
SUM() 求和
7.加密函数
MD5() 信息摘要算法
PASSWORD() 密码算法
18）自定义函数
用户自定义函数（user-defined function,UDF）是一种
对mysql扩展的途径，其用法与内置函数相同
自定义函数的两个必要条件：
（1）参数
（2）返回值
函数可以返回任意类型的值，同样可以接收这些类型的参数
1.创建自定义函数;
CREATE FUNCTION function_name
RETURNS
{STRING|INTEGER|REAL|DECIMAL}
routine_body
2.关于函数体
1.函数体由合法的SQL语句组成
2.函数体可以是简单的SELECT或INSERT 语句
3.函数体如果为复合结构则使用BEGIN...END语句
4.复合结构可以包含声明，循环，控制结构
19）存储过程
存储过程是SQL语句和控制语句的预编译集合，以一个名称存储
并作为一个单元处理
存储过程的优点：
1.增强SQL语句的功能和灵活性
2.实现较快的执行速度
3.减少网络流量
创建存储过程：
CREATE
[DEFINER = {user|CURRENT_USER}]
PROCEDURE sp_name([proc_parameter[,...]])
[characteristic...]routinr_body
proc_parameter:
[IN|OUT|INOUT] param_name type
IN,表示该参数的值必须在调用存储过程时指定 ，在存储过程中这个值不能被返回
OUT, 表示该参数的值可以被存储过程改变，并且可以反回
INOUT，表示该参数的调用时指定，并且可以被改变和返回
特性：
COMMENT:注释
CONTAINS SQL:包含SQL语句，但不包含读或写数据的语句
NO SQL:不包含SQL 语句
READ SQL DATA:包含读数据的语句
MODIFIES SAL DATA：包含写数据的语句
SAL SECURITY{DEFINER|INVOKER} 指明谁有权限来执行
过程体：
1.过程体由合法的SQL语句组成
2.过程体可以是'任意'的SQL 语句
3.过程体如果为复合结构则使用BEGIN...END语句
4.复合结构可以包含声明，循环，控制结构
创建存储过程
CREEATE PROCEDURE sp_name() SELECT VERSION();
调用存储过程
CALL sp_name([parameter[,...]])
CAL sp_name[()]
创建一个带有IN参数的存储过程：
DELIMITER //
CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = p_id;
END
//
DELIMITER ;
修改存储过程：
ALTER PROCEDURE sp_name[characteristic...]
COMMENT 'string'
|{CONTAINS SQL |NO SQL |READ SQL DATA | MODIFIES SQL DATA}
| SQL SECURITY{DEFINER|INVOKER}
删除存储过程：（只能修改简单的特性，并不能修改过程体，如果要修改过程体，只能先将存储过程删除，重新来创建）
DROP PROCEDURE [IF EXISTS] sp_name
创建带有IN和OUT类型参数的存储过程
DELIMITER //
CREATE PROCEDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED, OUT userNums INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = p_id;
SELECT count(id) FROM users INTO userNums;
END
//
DELIMITER ;
使用： CALL removeUserById(27,@nums);其中@nums是定义的用户变量，只对客户端有效
创建带有多个OUT类型参数的存储过程
CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED,OUT deleteUser SMALLINT UNSIGNED,OUT userCounts SMALLINT UNSIGNED)
BEGIN
DLETE FROM users WHERE age = p_age;
SELECT ROW_COUNT() INTO deleteUsers;
SELECT COUNT(id) FROM users INTO userCounts;
END
存储过程和自定义函数的区别：
存储过程实现的功能要复杂一些，而函数的针对性更强
存储过程可以返回多个值，函数只能有一个返回值
存储过程一般独立来执行，而函数可以作为其他SQL语句的组成部分来出现
MySQL的存储引擎
MySQL可以将数据以不同的技术存储在文件（内存）中，这种技术就称为存储引擎。
每一种存储引擎使用不同的存储机制，索引技巧，锁定水平，最终提供广泛且不同的功能。
MySQL所支持的主要存储引擎：MyISAM InnoDB Memory CSV Archive
并发控制： 当多个连接对记录进行修改时保证数据的一致性和完整性。
锁：
1.共享锁（读锁）：在同一时间段内，多个用户可以读取同一资源，读取过程中数据不会发生任何变化
2.排他锁（写锁）：在任何时候只能有一个用户写入资源，当进行锁时会阻塞其他的读锁操作
锁颗粒：
表锁，是一种开销小的锁策略
行锁，是一种开销最大的锁策略
事务处理： 事务用于保证数据库的完整性。
事务的特性：
原子性（Atomicity）
一致性（Consistency）
隔离性（Isolation）
持久性（Durability）
外键：是保证数据一致性的策略。
索引: 是对数据表中一列或多列的值进行排序的一种结构。
各种存储引擎的特点
| 特点 | MyISAM | InnoDB | Memory | Archive |
|---|---|---|---|---|
| 存储限制 | 256TB | 64TB | 有 | 无 |
| 事务安全 | - | 支持 | - | - |
| 支持索引 | 支持 | 支持 | 支持 | - |
| 锁颗粒 | 表锁 | 行锁 | 表锁 | 行锁 |
| 数据压缩 | 支持 | - | - | 支持 |
| 支持外键 | - | 支持 | - | - |
修改存储引擎的方法：
1.修改MySQL的配置文件
default-storage-engine = engine
2.通过修改数据表命令实现
ALTER TABLE table_name ENGINE[=] engine_name
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
