学习目标
MySQL与SQL基础
目录
学习目标
一、MySQL
1 关系表的特定条件：
- 存储单元的数据必须是原始的，每个单元只存储一条数据
- 每行数据是唯一的
- 列名是唯一的
- 主键保证：实体完整性原则，不能为空
- 外键保证：引用完整性原则，不能为空
2 数据库的规范化
- 第一范式(1NF):每个属性只包含一个值，
- 第二范式(2NF):完全依赖主键（而非部分依赖）
- 第三范式(3NF):不存在传递依赖关系
3 操作命令：
库
CREATE/SHOW/DROP DATABASE IF NOT EXISTS xxx;
USE xxx;
表
CREATE/DESC/DROP TABLE xxx;
表内
插入:
INSERT [LOW_PRIORITY|DELAYED|HIGH PRIORITY][IGNORE]
[INTO] 数据表名[(字段名1, ……)]
VALUES({值|DEFAULT},……)，(……),……
[ON DUPLICATE KEY UPDATE 字段名=表达式,……];
修改:
UPDATE [LOW_PRIORITY][IGNORE] 数据表名
SET 字段1=值1[,字段2=值2……]
[WHERE 条件表达式]
[ORDER BY……]
[LIMIT行数];
删除:
DELETE [LOW_PRIORITY][QUICK][IGNORE] FROM 数据表名
[WHERE 条件表达式]
[ORDER BY……]
[LIMIT行数]
TRUNCATE [TABLE] 数据库名.数据表名;
查询:
SELECT [DISTINCT] <字段列表>
FROM <数据表>
[<连接类型> JOIN <数据表> ON <连接条件>]
[WHERE <查询条件>] #支持关系条件、逻辑条件、BETWEEN AND、LIKE、IS NULL查询，不支持统计函数，查询前执行。
[GROUP BY <字段列表>]
[HAVING <条件表达式>] #HAVING可以有统计函数，查询完成后执行
[ORDER BY 字段1[ASC|DESC],字段2[ASC|DESC],……]
[LIMIT [<offset,>]<限制行数>];
使用统计函数查询：
SELECT 字段名 COUNT[SUM|AVG|MAX|MIN|GROUP_CONCAT](字段名) AS COUNT_NUM
内连接查询：
SELECT 字段1,字段2……
FROM 数据表1 INNER JOIN 数据表2 [INNER JOIN 数据表3……]
ON 数据表1.列名 条件运算符 数据表2.列名;
或者
SELECT 字段1,字段2,字段3……
FROM 数据表1,数据表2,数据表3
WHERE 数据表1.id = 数据表2.id AND 数据表2.id = 数据表3.id
外连接查询：
SELECT 字段名1，字段名2……
FROM 数据表1 LEFT|RIGHT [OUTER] JOIN 数据表2
ON 连接条件
WHERE ;
子查询(IN)：外层查询条件是内层查询的结果
SELECT *
FROM books
WHERE bookid [IN|EXISTS|ANY] (SELECT DISTINCT bookid FROM borrow)
合并查询结果:UNION去重, UNION ALL不去重.
SELECT 语句1
UNION|UNION ALL
SELECT 语句2
UNION|UNION ALL……
SELECT 语句n;
视图
创建视图
CREATE [OR REPLACE] VIEW 视图名[db_name.视图名] [(字段名1,……)]
AS SELECT 语句
[WITH [CASCADED|LOCAL] CHECK OPTION];
查看视图
DESCRIBE 视图名;
SHOW TABLE STATUS LIKE 视图名;
SHOW CREATE VIEW 视图名;
SELECT * FROM information_schema.view;
修改视图
ALTER VIEW 视图名[(字段名1,……)]
AS SELECT语句
[WITH [CASCADED|LOCAL] CHECK OPTION];
更新视图(不可更新的情况)
聚合函数
DISTINCT关键字
GROUP BY子句
ORDER BY子句
HAVING 子句
UNION运算符
位于选择列表中的子查询
FROM子句中包含多个表
SELECT语句中引用了不可更新视图
WHERE子句中的子查询，引用FROM子句中的表
更新视图：
插入数据：INSERT INTO 视图名 VALUES('','',……);
修改数据：UPDATE 视图名 SET ……;
删除数据：DELETE FROM 视图名 WHERE ;
删除视图
DROP VIEW [IF EXISTS] 视图名,……
索引
索引分类：普通索引/唯一性索引和主键索引/全文索引/空间索引 || 单列索引/组合索引
创建索引(创建表时）：
CREATE TABLE 表名(……
字段名 数据类型 [完整性约束条件],
……
PRIMARY KEY(字段名,……[ASC|DESC]) #主键索引
|INDEX|KEY [索引名] (字段名[长度],……[ASC|DESC]) #普通索引
|[UNIQUE|FULLTEXT|SPATITAL][INDEX|KEY] [索引名](字段名[长度],……[ASC|DESC]) #唯一索引|全文索引|空间索引)
创建索引（表已存在时之一）：
ALTER TABLE 表名
ADD PRIMARY KEY(字段名,……[ASC|DESC]) #主键索引
|ADD INDEX [索引名] (字段名[长度],……[ASC|DESC]) #普通索引
|ADD [UNIQUE|FULLTEXT|SPATITAL][INDEX|KEY] [索引名](字段名[长度],……[ASC|DESC]) #唯一索引|全文索引|空间索引)
创建索引（表已存在时之二）：
CREATE [UNIQUE|FULLTEXT|SPATITAL] INDEX [索引名] ON (字段名[长度],……[ASC|DESC]) #普通索引
|ADD [UNIQUE|FULLTEXT|SPATITAL][INDEX|KEY] [索引名](字段名[长度],……[ASC|DESC]) #唯一索引|全文索引|空间索引)
查看索引：
SHOW INDEX FROM 表名;
删除索引：
ALTER TABLE 表名
|DROP PRIMARY KEY
|DROP INDEX 索引名
数据完整性约束
主键约束(不允许值为NULL)
单字段-定义时：字段名 数据类型 PRIMARY KEY [默认值];
单字段-定义后：[CONSTRAINT 约束名] PRIMARY KEY (字段名);
多字段：[CONSTRAINT 约束名] PRIMARY KEY (字段名1，字段名2……);
唯一约束
字段名 数据类型 UNIQUE;
[CONSTRAINT 约束名] UNIQUE (字段名);
非空约束
字段名 数据类型 not null;
默认约束
字段名 数据类型 DEFAULT 默认值;
字段值自动增加
字段名 数据类型 AUTO_INCREMENT;
外键约束(只有InnoDB支持外键)
[CONSTRAINT 外键名] FOREIGN KEY (字段名 [,字段名2，……]) REFERENCE 主键名(主键列1 [,主键列2,……]) ENGINE=InnoDB;
二、SQL
1 注释
- 行部分注释：#
- 单整行注释：-- ,注意：--后有空格
- 多行注释：/*……*/
2 SQL变量
系统变量：
查询：SHOW [GLOBAL|SESSION|LOCAL] VARIABLES [LIKE '字符串'];
设置：
① SET [GLOBAL|SESSION|LOCAL] 系统变量名 = 表达式|DEFAULT ;
② @@[GLOBAL|SESSION|LOCAL].变量名 = 表达式|DEFAULT;
用户变量：
定义：SET @用户变量名1=表达式1，……;
局部变量：
定义：DELARE 局部变量名1,……类型 [DEFAULT值];
3 SQL流程控制语句
1. BEGIN……END
BEGIN
{
语句顺序
}
END;
2.DELIMITER,如：## $$等
3.IF
IF 条件表达式1 THEN 语句顺序1
[ELSEIF 条件表达式2 THEN 语句顺序2]……
[ELSE 语句顺序e]
END IF;
4.CASE
CASE
WHEN 条件1 THEN 语句顺序1
……
[ELSE 语句顺序e]
END CASE;
5.WHILE
WHILE 条件表达式 DO
程序段
END WHILE
6.REPEAT
REPEAT
程序段
UNTIL 条件表达式
END REPEAT
7.LOOP
[语句标号：] LOOP
程序段
END LOOP [语句标号]
8.LEAVE
LEAVE 语句标号
4 存储过程和存储函数
创建存储过程：
CREATE PROCEDURE 存储过程名([参数1],……)[特性] #参数格式：[IN|OUT|INOUT] 参数名 类型
存储过程体
[特性]包含：
|LANGUAGE SQL
|[NOT] DETERMINISTIC
|{CONSTRAINT SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY {DEFINER|INVOKER}
|COMMENT 'string'
调用存储过程：
CALL 存储过程名([参数1,……])
创建存储函数：
CREATE FUNCTION 存储函数名([参数1,……])[特性]#参数格式：参数名 类型
RETURN 类型
存储函数体
调用存储函数：
SELECT 存储函数名();
查看存储过程和存储函数：
查看状态：SHOW PROCEDURE|FUNCTION STATUS [LIKE 存储过程/函数名]
查看定义：SHOW CREATE PROCEDURE|FUNCTION 存储过程/函数名
从information——schema.Routines查看信息：
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME = 存储过程/函数名
修改存储过程和函数：
ALTER PROCEDURE|FUNCTION 存储过程/函数名 [特性]
删除存储过程和函数：
DROP PROCEDURE|FUNCTION [IF EXISTS] [数据库名.]存储函数/过程名
存储函数和存储过程的区别：
1. 存储函数没有输出参数，因为存储函数本身就是输出参数。
2. 存储函数不能使用CALL语句调用。
3. 存储函数必须包含一条RETURN语句，而存储过程则不允许包含。
触发器
创建触发器：
CREATE TRIGGER 触发器名 触发时间 触发事件
ON 表名 FOR EACH ROW 触发器动作
查看触发器：
1. SHOW方法：SHOW TRIGGERS
2. Trigger表中查看： SELECT * FROM information_schema.triggers [WHERE 查询条件]
删除触发器：
DROP TRIGGER [IF EXISTS] [数据库名.]触发器名
5 事务
创建事务(包括：初始化、创建、SELECT检查)
创建事务（新表）：
CREATE TABLE 表名
(field definations)
TYPE = INNODB/BDB;
创建事务（已存在的表）：
ALTER TABLE table_name TYPE = INNODB/BDB
初始化事务
START TRANSACTION;
提交事务
COMMIT;
撤销事务
ROLLBACK;
举例：
START TRANSACTION;
INSERT INTO readers
VALUES
('S7010','张三','男')
SELECT *
FROM readers
WHERE readername = '张三';
事务行为
自动提交：SET AUTOCOMMIT = 0 #关闭：0，开启：1
事务的孤立级
SERIALIZABLE(序列化)
REPEATABLE READ(可重读)
READ COMMITTED(提交后读)
REA UNCOMMITTED(未提交读)
修改事务的孤立级 SELECT @@transaction_isolation;
6 数据安全
添加用户
1. CREATE USER 用户名1 [IDENTIFIED BY [PASSWORD] '密码字符串1'][,用户名2 [IDENTIFIED BY [PASSWORD] '密码字符串1']]
2. GRANT 权限类型 ON 数据库名.表名 TO 用户名1 [IDENTIFIED BY [PASSWORD] '密码字符串1'][,用户名2 [IDENTIFIED BY [PASSWORD] '密码字符串1']]
3. INSERT INTO mysql.user(Host, User, authentication_string, ssl_cipher, x509_issuer, x509subject) VALUES ('主机名', '用户名', PASSWORD('密码字符串'),'','','')
FLUSH PRIVILEGES;
删除用户
1. DROP USER 用户名1 [,用户名2]#用户名形式：USER@HOST
2. DELETE FROM mysql.user WHERE Host='主机名' AND User='用户名';
FLUSH PRIVILEGES;
权限查看
SHOW GRANT FOR '用户名'@'主机名'
权限授权
GRANT 权限列表 [列列表] ON 数据库名.表名
TO 用户名1 [IDENTIFIED BY [PASSWORD] '密码1']
[,用户名2 [IDENTIFIED BY [PASSWORD] '密码2']]
[WITH 选项1 [,选项2]]
权限收回
REVOKE 权限列表 [列列表] ON 数据库名.表名
FROM 用户名1 [,用户名2]
数据备份
mysqldump -u 用户名 -p[密码] --databases 数据库1 [数据库2 数据库3] > 备份文件名.sql
数据还原
mysql -u 用户名 -p[密码] 数据库名 < 备份文件名.sql
备份错误日志
mysqladmin -u 用户名 -p[密码] flushlogs
注：文章内容由本人阅读学习《MySQL数据库基础|实例教程》与诸多博客所记录，如有侵权请及时联系处理。
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
