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