【MySQL】MySQL基础
一、基本语法
【MySQL目录结构】
●bin目录，存储可执行文件
●data目录，存储数据文件
●docs，文档
●include目录，存储包含的头文件
●lib目录，存储库文件
●share，错误信息和字符集文件
【MySQL的配置选项】
●修改编码方式
[mysql]
default-character-set=utf8
[mysqld]
character-set-server=utf8
【启动服务】
●启动MySQL服务
net start mysql
【MySQL退出】
●mysql>exit;
●mysql>quit;
●mysql>\q;
【修改MySQL提示符】
●连接客户端时通过参数指定
●shell>mysql -uroot -proot --promot 提示符
●连接上客户端后，通过promot命令修改
●mysql>prompt 提示符
参数 描述
\n 完整的日期
\d 当前数据库
\h 服务器名称
\u 当前用户
【MySQL常用命令】
#显示当前服务器版本
SELECT VERSION();
#显示当前日期时间
SELECT NOW();
#显示当前用户
SELECT USER();
【MySQL语句的规范】
●关键字与函数名称全部大写
●数据库名称、表名称、字段名称全部小写
●SQL语句必须以分号结尾
二、数据库的创建、修改和删除
【创建数据库】
●CREARE {DATABASE | SCHEMA } [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] character_name
{}中为必选项
[]中为可选项
【查看当前服务器下的数据库列表】
●SHOW {DATABASE | SCHEMAS} [LIKE 'pattern' | WHERE expr]
【修改数据库】
●ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name
【删除数据库】
●DROP {DATABASE | SCHEMA } [IF EXISTS ] db_name
三、数据类型
【MySQL目录结构】
●bin目录，存储可执行文件
●data目录，存储数据文件
●docs，文档
●include目录，存储包含的头文件
●lib目录，存储库文件
●share，错误信息和字符集文件
【MySQL的配置选项】
●修改编码方式
[mysql]
default-character-set=utf8
[mysqld]
character-set-server=utf8
【启动服务】
●启动MySQL服务
net start mysql
【MySQL退出】
●mysql>exit;
●mysql>quit;
●mysql>\q;
【修改MySQL提示符】
●连接客户端时通过参数指定
●shell>mysql -uroot -proot --promot 提示符
●连接上客户端后，通过promot命令修改
●mysql>prompt 提示符
参数 描述
\n 完整的日期
\d 当前数据库
\h 服务器名称
\u 当前用户
【MySQL常用命令】
#显示当前服务器版本
SELECT VERSION();
#显示当前日期时间
SELECT NOW();
#显示当前用户
SELECT USER();
【MySQL语句的规范】
●关键字与函数名称全部大写
●数据库名称、表名称、字段名称全部小写
●SQL语句必须以分号结尾
【创建数据库】
●CREARE {DATABASE | SCHEMA } [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] character_name
{}中为必选项
[]中为可选项
【查看当前服务器下的数据库列表】
●SHOW {DATABASE | SCHEMAS} [LIKE 'pattern' | WHERE expr]
【修改数据库】
●ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name
【删除数据库】
●DROP {DATABASE | SCHEMA } [IF EXISTS ] db_name
----------------------------------------------------------------------------------------------------------
【创建数据表】
●CREATE TABLE [IF NOT EXISTS] table_name (column_name data_type,...)
【查看数据表列表】
●SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr]
【查看数据表结构】
●SHOW COLUMNS FROM tbl_name
【插入记录】
●INSERT [INTO] tbl_name [(col_name,...)] VALUES(val,...)
【查找记录】
●SELECT expr,... FROM tbl_name
【空值与非空】
●NULL,字段值可以为空
●NOT NULL，字段值禁止为空
【AUTO_INCREMENT】
●自动编号，且必须与主键组合使用
● 默认情况下，起始值为1，每次的增量为1
【PRIMARY KEY】
●主键约束
●每张数据表只能存在一个主键
●主键保证记录的唯一性
●主键自动为NOT NULL
【UNIQUE KEY】
●唯一约束
●唯一约束可以保证记录的唯一性
●唯一约束的字段可以为空值(NULL)
●每张数据表可以存在多个唯一约束
例如：
mysql>CREATE TABLE tb5
->(
->id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
->username VARCHAR(30) NOT NULL UNIQUE KEY,
->age TINYINT UNSIGNED)
->;
【DEFAULT】
●默认值
●当插入记录时，如果没有明确为字段赋值，则自动赋予默认值
例如：
mysql>CREATE TABLE tb6
->(
->id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
->usename VARCHAR(30) NOT NULL UNIQUE KEY,
->sex ENUM('1','2','3') DEFAULT '3'
);
【约束】
●约束保证数据的完整性和唯一性
●约束分为表级约束和列级约束
●约束类型包括：
NOT NULL（非空约束）
PRIMARY KEY(主键约束)
UNIQUE KEY(唯一约束)
DEFAULT(默认约束)
FOREIGN KEY(外键约束)
【FOREIGN KEY】
●外键约束
●保证数据一致性，完整性
●实现一对一和一对多关系
【外键约束的要求】
●父表和子表必须使用相同的存储引擎，而且禁止使用临时表
●数据表的存储引擎只能为InnoDB
●外键列和参照列必须具有相似的数据类型，切中数字的长度或是否有符号位必须相同；而只读的长度可以不同
●外键列和参照列必须创建索引，如果外键列不存在索引的话，MySQL将自动创建索引
例如：
父表
mysql>CREATE TABLE provinces(
->id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
->pname VARCHAR(20) NOT NULL
->PRIMARY KEY ('id');
子表
mysql>CREATE TABLE users(
->id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
->username VARCHAR(10) NOT NULL,
->pid SMALLINT UNSIGNED,
->FOREIGN KEY (pid) REFERENCES province (id)
->;
【外键约束的参照操作】
●CASCADE: 从父表删除或更新且自动删除或则更新子表中匹配的行
●SET NULL: 从父表删除或更新行，并设置子表中的外键列为NULL，如果使用该选项，必须保证子表列没有指定NOT NULL
●RESTRICT:拒绝对父表的删除或更新操作
●NOT ACTION:标准SQL关键字，在MySQL中与RESTRICT相同
【表级约束和列级约束】
●对一个数据列建立的约束，称为列级约束
●对多个数据列建立的约束，称为表级约束
●列级约束可以在列定义时声明，也可以在列定义后声明
●表级约束只能在列定义后声明
【修改数据表】
添加单列
●ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST|ALTER col_name ]
添加多列
●ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition,...)
删除列
●ALTER TABLE tbl_name DROP [COLUMN] col_name
例如：
mysql>ALTER TABLE users ADD truename VARCHAR(30) NOT NULL FIRST;
mysql>ALTER TABLE users DROP truename;
mysql>ALTER TABLE users DROP username,DROP age;
添加主键约束
●ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
例如：
msyql>ALTER TABLE users ADD id SMALLINT UNSIGNED;
添加唯一约束
●ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...)
添加外键约束
●ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name) references_definiton
添加、删除默认约束
●ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
mysql>ALTER TABLE users2 ADD age TINYINT UNSIGNED NOT NULL;
mysql>ALTER TABLE users2 ALTER age SET DEFAULT 15;
删除主键约束
●ALTER TABLE tbl_name DROP PRIMARY KEY
删除唯一约束
●ALTER TABLE tbl_name DROP [INDEX|KEY] index_name
删除外键约束
●ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol
mysql>ALTER TABLE user2 DROP FOREIGN KEY users_ibfk_1;
修改表定义
●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 [FIRST|ALTER col_name]
数据表更名
●方法1：ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name
●方法2：RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2] ...
【INSERT】
插入记录
●方法一：INSERT [INTO] tbl_name [(col_name,...)] {VALUES|VALUE} ({expr|DEFAULT},...),(...),...
●方法二：INSERT [INTO] tbl_name SET col_name={expr|DEFAULT},...
说明：与第一种的区别是，此方法可以使用子查询。
●方法三：INSERT [INTO] tbl_name [(col_name,...)] SELECT ...
说明：此方法可以将查询结果插入到指定数据表
【UPDATE】
●更新记录（单表更新）
●UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr|DEFAULT} [,col_name2={expr2|DEFAULT}] .... [WHERE where_condition]
【DELETE】
●删除记录（单表删除）
●DELETE FROM tbl_name [WHERE where_condition]
【SELECT】
●查找记录
●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 | row_count OFFSET offset}]
]
【select_expr】
查询表达式
●每一个表达式表示想要的一列，必须有至少一个
●多个列之间以英文逗号分隔
●星号表示所有列，tbl_name.*可以表示命名表的所有列
●查询表达式可以使用 [AS] alias_name为其赋予别名
●别名可用于GROUP BY,ORDER BY或HAVING子句
【WHERE】
●条件表达式
●对记录进行过滤，如果没有指定WHERE子句，则显示所有记录
●在WHERE表达式中，可以使用MySQL支持的函数或运算符
【GROUP BY 】
●查询结果分组
●[GROUP BY {col_name|position} [ASC| DESC],...]
【HAVING】
●分组条件
● [HAVING where_condition]
【ORDER BY】
●对查询结果进行排序
●[ORDER BY {col_name| expr|position} [ASC|DESC],...]
【LIMIT】
●限制查询结果返回的数量
●[LIMIT {[offset,] row_count | row_count OFFSET offset}]
【子查询】
子查询指嵌套在查询内部，且必须始终出现在圆括号内；
子查询可以包含多个关键字或条件，如DISTINCT、GROUP BY、ORDER BY、LIMIT、函数等。
子查询的外层查询可以是SELECT,INSERT,UPDATE,SET或DO.
【子查询返回值】
子查询可以返回标量、一行、一列或子查询。
【使用比较运算符的子查询】
使用比较运算符的子查询：=,>,<,>=,<=,<>,!=,<=>
语法结构： operand comparison_operator subquery
【用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运算符与NOT IN 等效。
【使用[NOT] EXISTS的子查询】
如果子查询返回任何行，EXISTS将返回TRUE；否则返回FALSE.
【INSERT...SELECT】
将查询结果写入数据表
INSERT [INTO] tbl_name [(col_name,...)] SELECT ....
【多表更新】
UPDATE table_references
SET col_name1={expr|DEFAULT}
[,col_name2={expr2|DEFAULT}]...
[WHERE where_condition]
【语法结构】
table_reference
{[INNER| CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN}
table_reference
ON conditonal_expr
【连接类型】
INNER JOIN : 内连接
在MySQL中，JOIN,CROSS JOIN和INNER JOIN是等价的。
LEFT [OUTER] JOIN： 左连接
RIGHT [OUTER] JOIN: 右连接
mysql>UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate
->SET goods_cate = cate_id;
【CREATE ...SELECT】
创建数据表同时将查询结果写入到数据表
CREATE TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
select_statement
【连接】
MySQL在SELECT语句、多表更新、多表删除语句中支持JOIN操作。
【数据表参照】
table_reference
tbl_name [[AS] alias] | 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_name IS NULL时，如果col_name被定义为NOT NULL,MySQL将在找到符合连接的条件的记录后停止搜索更多的行。
【自身连接】
同一个数据表对其自身进行连接。
【多表删除】
DELETE tbl_name[.*] [,tbl_name[.*]]...
FROM table_references
[WHERE where_condition]
【字符函数】
函数名称 描述
CONCAT() 字符连接
CONCAT_WS() 使用指定的分隔符进行字符连接
FORMAT() 数字格式化
LOWER() 转换成小写字母
UPPER() 转换成大写字母
LEFT() 获取左侧字符
RIGHT() 获取右侧字符
LENGTH() 获取字符串长度
LTRIM() 删除前导空格
RTRIM() 删除后续空格
TRIM() 删除前导和后续空格
SUBSTRING() 字符串截取
[NOT] LIKE 模式匹配
REPLACE() 字符串替换
例如：
mysql>SELECT CONCAT('imooc','MySQL')
mysql>SELECT CONCAT_WS('imooc','-','MySQL')
mysql>SELECT CONCAT_WS('|','A','B','C');
mysql>SELECT FORMAT(12560.75,2);
mysql>SELECT FORMAT(12560.75,1);
mysql>SELECT LOWER('MySQL');
mysql>SELECT UPPER('MySQL');
mysql>SELECT LEFT('MySQL',2);
mysql>SELECT LENGTH(TRIM( 'MySQL' ));
mysql>SELECT TRIM(LEADING '?' FROM '??MySQL???');
mysql>SELECT TRIM(BOTH '?' FROM '??MySQL???');
mysql>SELECT TRIM(BOTH '?' FROM '??My??SQL???');
mysql>SELECT REPLACE('??My??SQL???','?','');
mysql>SELECT SUBSTRING('MySQL',1,2);
mysql>SELECT SUBSTRING('MySQL',3);
mysql>SELECT SUBSTRING('MySQL',-1);
mysql>SELECT * FROM test WHERE first_name LIKE '%1%%' ESCAPE '1';
【数值运算符与函数】
名称 描述
CEIL() 进一取整
DIV 整数除法
FLOOR() 舍一取整
MOD 取余数
POWER() 幂运算
ROUND() 四舍五入
TRUNCATE() 数字截取
【比较运算符与函数】
名称 描述
[NOT] BETWEEN...AND.... [不]在范围之内
[NOT] IN() [不]在列出值范围内
IS [NOT] NULL [不]为空
【日期时间函数】
名称 名称
NOW() 当前日期和时间
CURDATE() 当前日期
CURTIME() 当前时间
DATE_ADD() 日期变化
DATEDIFF() 日期差值
DATE_FORMAT() 日期格式化
mysql>SELECT DATE_ADD('2014-3-12',INTERVAL 365 DAY);
mysql>SELECT DATEDIFF('2013-3-12','2014-3-12');
mysql>SELECT DATE_FORMAT('2014-3-2','%m/%d/%Y');
【信息函数】
名称 描述
CONNECTION_ID() 连接ID
DATABASE() 当前数据库
LAST_INSERT_ID() 最后插入记录的ID号
USER() 当前用户
VERSION() 版本信息
【聚合函数】
名称 描述
AVG() 平均值
COUNT() 计数
MAX() 最大值
MIN() 最小值
SUM() 求和
【加密函数】
名称 描述
MD5() 信息摘要算法
PASSWORD() 密码算法
【自定义函数】
自定义函数的两个必要条件：参数和返回值
函数可以返回任意类型的值，同样可以接收这些类型的参数
●创建自定义函数
CREATE FUNCTION function_name
RETURNS
{STRING|INTEGER|REAL|DECIMAL}
routine_body
【关于函数体】
●函数体由合法的SQL语句构成；
●函数体可以是简单的SELECT或INSERT语句；
●函数体如果为复合结构则使用BEGIN...END语句；
●复合结构可以包含声明，循环，控制结构；
mysql>CREATE FUNCTION f1() RETURNS VARCHAR(30)
mysql>RETURN DATE_FORMAT(NOW(),'%Y%m%d %H:%i:%s');
mysql>CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
mysql>RETURNS FLOAT(10,2) UNSIGNED
mysql>RETURN (num1+num2)/2;
mysql>DELIMITER //
mysql>CREATE FUNCTION adduser(username VARCHAR(20))
mysql>RETURNS INT UNSIGNED
mysql>BEGIN
mysql>INSERT test(username) VALUES(username);
mysql>RETURN LAST_INSERT_ID();
mysql>END
mysql>//
【删除函数】
DROP FUNCTION [IF EXISTS] function_name
【创建存储过程】
CREATE
[DEFINER = {user| CURRENT_USER}]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic...] routine_body
proc_parameter:
[IN|OUT|INOUT] param_name type
【参数】
●IN: 表示该参数的值必须在调用存储过程时指定
●OUT: 表示该参数的值可以被存储过程改变，并且可以返回
●INOUT: 表示该参数可以在调用时指定，并且可以被改变和返回
【特性】
COMMENT 'string'
| { CONTAINS SQL | NO SQL|READS SQL DATA | MODIFIES SQL DATA}
| SQL SECURITY {DEFINER | INVOKER}
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[()]
mysql>DELIMITER //
mysql>CREATE PROCEDURE removeUserById(IN id INT UNSIGNED)
mysql>BEGIN
mysql>DELETE FROM users WHERE id = id;
mysql>END
mysql>//
【修改存储过程】
ALTER PROCEDURE sp_name [characteristic ..]
COMMENT 'string'
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
| SQL SECURITY {DEFINER | INVOKER}
【删除存储过程】
DROP PROCEDURE [IF EXISTS ] sp_name
mysql>DELIMITER //
mysql>CREATE PROCEDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)
mysql>BEGIN
mysql>DELETE FROM users WHERE id = p_id
mysql>SELECT count(id) FROM users INTO userNums;
mysql>END
mysql>//
mysql>CALL removeUserAndRerurnUserNums;
mysql>SELECT @nums;
mysql>DELIMITER //
mysql>CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED,OUT deleteUsers SMALLINT UNSIGNED,OUT userCounts SMALLINT UNSIGNED)
mysql>BEGIN
mysql>DELETE FROM users WHERE age=p_age;
mysql>SELECT ROW_COUNT() INTO deleteUsers;
mysql>SELECT COUNT(id) FROM users INTO userCounts;
mysql>END
mysql>//
【存储过程与自定义函数的区别】
●存储过程实现的功能要复杂一些，而函数的针对性较强；
●存储过程可以返回多个值；函数只能有一个返回值；
●存储过程一般独立的来执行；而函数可以作为其他SQL语句的组成部分来出现；
【存储引擎】
MySQL可以将数据以不同的技术存储在文件中，这种技术成为存储引擎。
【MySQL支持的存储引擎】
MyISAM
InnoDB
Memory
CSV
Archive
【关于锁】
锁
●共享锁（读锁）
在同一时间段内，多个用户可以读取同一个资源，读取过程中数据不会发生任何变化；
●排他锁（写锁）
在任何时候只能有一个用户写入资源，当进行写锁时会阻塞其他的读锁或者写锁操作
锁颗粒
●表锁：是一种开销最小的锁策略
●行锁：是一种开销最大的锁策略
【编辑数据表的默认存储引擎】
●方式一：修改MySQL配置文件
default storage-engine=INNODB
●方式二：使用命令修改
ALTER TABLE table_name ENGINE [=] engine_name;
【修改存储引擎的方法】
●通过创建数据表命令实现
CREATE TABLE table_name(
...
) ENGINE = engine;
