Mysql
规范:
关键字与函数名称全部大写
数据库名称、表名称、字段名称全部小写
SQL语句必须以分号结尾
操作数据库:
Mysql的登录:mysql -h host -u user -p
数据库默认端口号:3306
显示当前服务器版本:SELECT VERSION();
显示当前时间:SELECT NOW();
显示当前用户:SELECT USER();
显示错误:SHOW WARNINGS;
创建数据库:CREATE {DATEBASE | SCHEME} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name;
删除数据库:DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;
修改数据库:ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name;
数据类型与数据表的操作
数据类型是指列、存储过程参数、表达式和局部变量的数据特征,它决定了数据的存储格式,代表了不同的信息类型。
整型:
TINYINT:1个字节,有符号值:-128127,无符号值0255
SMALLINT:2个字节,有符号值:-3276832767,无符号值:065535
MEDIUMINT:3个字节,有符号值:-83886088388607,无符号值:016777215
INT:4个字节,有符号值:-21474836482147483647,无符号值:04294967295
BIGINT:8个字节
浮点型:
FLOAT[(M,D)]:M是数字总位数,D是小数后面的位数,如果M和D被省略,根据硬件允许的限制来保存值。单精度浮点数精确到大约7位小数位。
DOUBLE[(M,D)]
日期时间型
YEAR
TIME
DATE
DATETIME
TIMESTAMP:1970-1-1 0:0:0 ~ 2037
字符型
CHAR(M)
VARCHAR(M)
TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT
ENUM('value1','value2',...):枚举值,最多65535个值
SET('value1','value2',...):集合,最多64个成员
数据表是数据库中最重要的组成成员之一,是其他对象的基础。
1.打开数据库:USE db_name;
2.创建数据表:CREATE TABLE [IF NOT EXISTS] table_name (column_name data_type,...);
CREATE TABLE IF NOT EXISTS t1(
username VARCHAR(20) NOT NULL,
age TINYINT UNSIGNED,
salary FLOAT(8,2) UNSIGNED
);
3.查看数据库列表:SHOW TABLE [FROM db_name] [LINK 'pattern' | WHERE expr];
4.查看数据表的结构:SHOW COLUMNS FROM tbl_name;
5.插入记录:INSERT [INTO] tbl_name [(col_name,...)] VALUES(val,...);
6.记录查找:SELECT expr,... FROM tbl_name;
7:自动编号:AUTO_INCREMENT,且必须与主键组合使用,默认情况下,起始值为1,每次增量为1
主键约束:PRIMARY KEY,每张数据表只能存在一个主键,主键保证记录的唯一性,主键自动为NOT NULL。
唯一约束:UNIQUE KEY,唯一约束可以保证记录的唯一性,唯一约束的字段可以为空值NULL,每张数据表可以存在多个唯一约束。
默认约束:当插入记录时,如果没有明确为字段赋值,则自动赋予默认值。
CREATE TABLE tb6(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL UNIQUE KEY,
sex ENUM('1','2','3') DEFAULT '3'
);
约束
约束保证数据的完整性和一致性
约束分为表级约束和列级约束
约束类型包括:非空约束、主键约束、唯一约束、默认约束、外键约束
外键约束的条件
父表和子表必须使用相同的存储引擎,而且禁止使用临时表
数据表的存储引擎只能为InnoDB
外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有富豪为必须相同,而字符的长度则可以不同
外键列和参照列必须创建索引,如果外键列不存在索引的话,MySQL将自动创建参照列的索引
外键约束的参照操作:
CASCADE:从父表删除或者更新且自动删除或者更新子表中匹配的行
SET NULL:从父表删除或更新行,并设置子表中的外键列为NULL,如果使用了该选项,必须保证子表列没有指定NOT NULL
RESTRICT:拒绝对父表的删除或更新操作
NO ACTION:标准SQL的关键字,在MySQL中与RESTRICT相同
8.修改数据表
添加单列:ALTER TABLE tbl_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;
9.添加主键约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type (index_col_name,...);
插入
INSERT [INTO] ... VALUES(...);
INSERT [INTO] ... SET col_name = value1,...;
INSERT [INTO] tbl_name [(col_name,...)] SELECT ...;
示例:
INSERT INTO test (username) SEELCT username FROM USERS WHERE age >= 30;
更新
单表更新:
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1 | DEFAULT} [,col_name2={expr1 | DEFAULT}] ... [WHERE where_conditiion];
示例:
UPDATE users SET age = age +5;
UPDATE users SET age = age - id,sex = 0;
UPDATE users SET age = age + 10 WHERE id % 2 = 0;
删除记录
单表删除
DELETE FROM tbl_name [WHERE where_condition];
示例:
DELETE FROM users WHERE id = 6;
注意:删除后再插入数据,id为当前最大id+!而不是去补充被删除的id。
别名
AS:SELECT userid AS uid FROM users;
分组
GROUP BY:GROUP BY {col_name | position} [ASC | DESC],...
ACS:升序
DESC:降序
分组条件
HAVING:HAVING where_condition,HAVING中若出现字段名,则必须出现在SELECT中
排序
ORDER BY:[ORDER BY {col_name | expr | position} [ASC | DESC],...]
限制查询结果返回的数量
[LIMIT {[offser,] row_count OFFSET offset}]
示例:
SELECT * FROM users LIMIT 2
子查询(Subquery)是指出现在其他SQL语句内的SELECT子句。
例如:SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);
其中SELECT * FROM t1称为Outer Query / Outer Statement,SELECE col2 FROM t2 称为Subquery。
子查询的外层可以是:SELECT、INSERT、UPDATE、SET或DO。
子查询可以包含多个关键字或条件,如DISTINCT、GROUP BY、ORDER BY、LIMIT、函数等。
示例:
SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price >= (SELECT ROUND(AVG(goods_price),2) FROM tbd_goods);
使用 [NOT] IN 的子查询
=ANY 运算符与IN等效
!=ALL或者 <>ALL运算符与NOT IN等效
使用 [NOT] EXISTS 的子查询
如果子查询返回任何行,EXISTS将返回TRUE,否则为FALSE。
用ANY、SOME、ALL修饰的比较运算符
\
ANY
SOME
ALL
> 、>=
最小值
最小值
最大值
< 、<=
最大值
最大值
最小值
=
任意值
任意值
<> 、!=
任意值
示例:
SELECT * FROM tdb_goods WHERE goods_price > ALL(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超极本');
连接类型
在MySQL中,JOIN、CROSS JOIN 和 INNER JOIN是等价的。
使用 ON 关键字来设定连接条件,也可以使用 WHERE 来代替,通常情况下使用 ON 关键字来设定连接条件,使用 WHERE 关键字进行结果集记录的过滤。
INNER JOIN:内连接,仅显示符合连接条件的记录
UPDATE tdb_goods INNER JOIN tbd_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;
SELECT goods_id,goods_name,cate_name FROM tdb_goods
INNER JOIN tdb_goods_cates ON
tdb_goods.cate_id = tdb_goods_cates.cate_id;
LEFT [OUTER] JOIN:左外连接,显示左表的全部记录和右表中符合条件的记录,如果右表中不存在,则显示为NULL。
RIGHT [OUTER] JOIN:右外连接,显示右表中的全部记录,和左表中符合条件的记录,如果左表中不存在,则显示为NULL。
多表连接
SELECT goods_id,goods_name,cate_name,brand_name,goods_price
FROM tdb_goosd AS g
INNER JOIN tdb_goods_cates AS c
ON g.cate_id = c.cate_id
INNER JOIN tdb_goods_brands AS b
ON g.brand_id = b.brand_id;
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将在找到符合连接条件的记录后停止搜索更多的行。
CREATE...SELECT:创建数据表的同时将查询结果写入到数据表,CREATE TABLE [IF NOT EXISTS] [(create_detinition,...)] select_statement;
示例:
CREATE TABLE tdb_goods_brands(
brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
brand_name VARCHAR(40) NOT NULL
)
SELECT brand_name FROM tdb_goods GROUP BY brand_name;
UPDATE tdb_goods INNER JOIN tdb_goods_brands
ON tdb_goods.brand_name = tdb_goods_brands.brand_name
SET tdb_goods.brand_name = tdb_goods_brands.brand_id;
ALTER TABLE tdb_goods
CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;
无限级分类表设计
示例:
CREATE TABLE tdb_goods_types(
type_id SMALLINT UNSIGNED PRIMARY AUTO_INCREMENT,
type_name VARCHAR(20) NOT NULL,
parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
);
相关查询:通过自身连接实现即同一个数据表对其自身进行连接。进行自身连接时,一定要取别名。MySQL无法实现递归查询。
//查询父类型
SELECT s.type_id,s.type_name,p.type_name
FROM tdb_goods_types AS s
LEFT JOIN tdb_goods_types AS p
ON s.parent_id = p.type_id;
//查询子类型
SELECT p.type_id,p.type_name,s.type_name
FROM tdb_goods_types AS p
LEFT JOIN tdb_goods_types AS s
ON s.parent_id = p.type_id;
//查询父类以及子类的无重复目录
SELECT p.type_id,p.type_name,s.type_name
FROM tdb_goods_types AS p
LEFT JOIN tdb_goods_types AS s
ON s.parent_id = p.type_id
GROUP BY p.type_name
ORDER BY p.type_id;
//查询父类以及子类的数目
SELECT p.type_id,p.type_name,COUNT(s.type_name) AS child_count
FROM tdb_goods_types AS p
LEFT JOIN tdb_goods_types AS s
ON s.parent_id = p.type_id
GROUP BY p.type_name
ORDER BY p.type_id;
多表删除
//模拟单表的多表删除,删除重复记录中id较大的记录
DELETE t1 FROM tdb_goods AS t1
LEFT JOIN (
SELECT goods_id,goods_name
FROM tdb_goods
GROUP BY goods_name
HAVING count(goods_name) >= 2
) AS t2
ON t1.goods_name = t2.goods_name
WHERE t1.goods_id > t2.goods_id;
运算符
字符函数
CONCAT():字符连接
SELECT CONCAT('HELOO','WORLD');
SELECT CONCAT('HELLO','-','WORLD');
SELECT CONCAT(firstname,lastname) AS fullname FROM test; //用于姓名的连接
CONCAT_WS():使用指定的分隔符进行字符连接
SELECT COMCAT_WS('-','A','B','C'); //结果为A-B-C
SELECT CONCAT_WS('-','HELLO','WORLD');
FORMAT():数字格式化
SELECT FORMAT(12345.345,2); //结果为12,345.35,四舍五入
LOWER():转换成小写字母
UPPER():转换成大写字母
LEFT():获取左侧字符
SELECT LEFT('MySQL',2); //结果为My
SELECT LOWER(LEFT('MySQL',2)); //结果为my
RIGHT():获取右侧字符
LENGTH():获取字符串长度
LTRIM():删除前导空格
RTRIM():删除后续空格
TRIM():删除前导和后续空格或者指定字符
SELECT TRIM(LEADING '?' FROM '??MySQL???'); //结果为MySQL???
SELECT TRIM(TRAILING '?' FROM '??MySQL???'); //结果为??MySQL
SELECT TRIM(BOTH '?' FROM '??MySQL???'); //结果为MySQL
SUBSTRING():字符串截取
SELECT SUNSTRING('MySQL',1,2); //结果为My,MySQL中字符串位数从1开始
SELECT SUNSTRING('MySQL',3); //结果为SQL
SELECT SUNSTRING('MySQL',-1); //结果为L,即从右开始倒数操作
[NOT] LIKE:模式匹配
SELECT 'MySQL' LIKE 'M%';
//查询字符串中包含%的字符串,如下,ESCAPE即跳过1后面的%的解析
SELECT * FROM test WHERE name LIKE '%1%%' ESCAPE '1';
REPLACE():字符串替换
SELECT REPLACE('??My??SQL???','?',''); //结果为MySQL
数值运算函数
CEIL():进一取整
DIV:整数除法
FLOOR():舍一取整
MOD:取余数(取模)
POWER():幂运算
ROUND():四舍五入
TRUNCATE():数字截取
SELECT CEIL(3.01); //结果为4
SELECT FLOOR(3.99); //结果为3
SELECT 3 DIV4; //结果为0
SELECT 5 MOD 3; //结果为2
SELECT POWER(3,3); //结果为27
SELECT ROUND(3.1415,3); //结果为3.142
SELECT TRUNCATE(1234.567,2); //结果为1234.56,没有四舍五入操作
比较运算符函数
[NOT] BETWEEN ... AND ...:[不]在范围之内
[NOT] IN():[不]在列出值范围内
IS [NOT] NULL:[不]为空
SELECT 15 BETWEEN 1 AND 22; //结果为1,即为true
SELECT 10 IN(5,10,15,20); //结果为1,即表示10在所列数字序列之内
SELECT 11 IN(5,10,15,20); //结果为0,即表示11不在所列数字序列之内
SELECT NULL IS NULL; //结果为1
SELECT '' IS NULL; //结果为0
SELECT 0 IS NULL; //结果为0
日期时间函数
NOW():当前日期和时间
CURDATE():当前日期
CURTIME():当前时间
DATE_ADD():日期变化
DATEDIFF():日期差值
DATE_FORMAT():日期格式化
SELECT NOW(); //返回当前日期时间
SELECT DATE_ADD('2013-2-11',INTERVAL 365 DAY); //后一年
SELECT DATE_ADD('2013-2-11',INTERVAL -365 DAY); //前一年
SELECT DATE_ADD('2013-2-11',INTERVAL 1 YEAR); //YEAR WEEK DAY
SELECT DATEDIFF('2013-1-1','2011-2-1'); //计算两个日期之间的天数差值
SELECT DATE_FORMAT('2013-1-2','%m/%d/%Y'); //结果为01/02/2013
信息函数
CONNECTION_ID():连接ID
DATABASE():当前数据库
LAST_INSERT_ID():最后插入记录的ID
VERSION():版本信息
聚合函数
AVG():求平均值
COUNT():计数
MAX():最大值
MIN():最小值
SUM():求和
SELECT AVG(goods_price) FROM tbd_goods;
SELECT COUNT(goods_id) FROM tdb_goods;
SELECT MAX(goods_price) FROM tdb_goods;
SELECT MIN(goods_price) FROM tdb_goods;
SELECT SUM(goods_price) FROM tdb_goods;
加密函数
MD5():信息摘要算法
PASSWORD():密码算法
自定义函数的操作
用户自定义函数(user-defined function,UDF)是一种对MySQL扩展的途径,其用法与内置函数相同。
自定义函数的两个必要条件:
参数
返回值
函数可以返回任意类型的值,同样可以接收这些类型的参数,但是函数的参数和返回值之间没有必要的联系,MySQL规定自定义函数的参数数量不能超过1024个。
创建自定义函数:
CREATE FUNCTION function_name
RETURNS
{STRING | INTEGER | REAL | DECIMAL}
routine_body
函数体由合法的SQL语句构成;
函数体可以是简单的SELECT或者INSERT语句;
函数体如果为复合结构则使用BEGIN...END语句;
复合结构可以包含声明、循环、控制结构;
创建示例:
//创建一个转换了日期时间显示格式的不带参数的自定义函数
CREATE FUNCTION f1() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H时:%i分:%s秒');
//计算两个数的平均值
CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
RETURNS FLOAT(10,2) UNSIGNED
RETURN (num1 + num2)/2;
/* 此时需要自定义设置命令结束符为'//',屏蔽';' */
CREATR FUNCTION f3(username VARCHAR(20)) RETURNS INT UNSIGNED
BEGIN
INSERT test(username) VALUES(username);
RETURN LAST_INSET_ID();
END//
//删除已经存在的函数
DROP FUNCTION function_name;
存储过程
SQL命令 -> MySQL引擎分析 -> 语法正确的可识别命令 -> 执行返回结果 -> 结果返回给客户端
存储过程是SQL语句和控制语句的预编译集合,以一个名称存储并作为一个单元处理。
存储过程的优点:
增强SQL语句的功能和灵活性
实现较快的执行速度
减少网络流量
创建存储过程
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:注释
CONTAIN SQL:包含SQL语句,但是不包含读或者写数据的语句
NO SQL:不包含SQL语句
READS SQL DATA:包含读数据的语句
MODIFIES SQL DATA:包含写数据的语句
SQL SECURITY {DEFINER | INVOKER}:指明谁有权限来执行
过程体
过程题由合法的SQL语句构成
过程题可以是任意SQL语句(对于数据的增删改查以及多表的连接)
过程体如果为复合结构则使用BEGIN...END语句
复合语句可以包含声明、控制、循环语句
//创建一个获取版本的存储过程
CREATE PROCEDURE sp1() SELECT VERSION();
/* 此时需要自定义设置命令结束符为'//',屏蔽';' */
CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = p_id;
END//
/* 此时需要自定义设置命令结束符为'//',屏蔽';' */
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//
//
//调用存储过程
CALL sp_name[()]
CALL sp_name([parameter[,...]])
MySQL存储引擎
MySQL将数据以不同的技术存储在文件(内存)中,这种技术称之为存储引擎。每一种存储引擎使用不同的存储机制、索引技巧、锁定水平,最终提供广泛且不同的功能。
并发控制:当多个连接记录进行修改时保证数据的一致性和完整性。处理并发控制使用锁系统来解决。
共享锁(读锁):在同一时间段内,多个用户可以读取同一资源,读取过程中数据不会发送任何变化。
排他锁(写锁):在任何时候只能有一个用户来写入资源,当进行写锁时会阻塞其他的读锁或者写锁操作。
锁颗粒
表锁:是一种开销最小的锁策略
行锁:是一种开销最大的锁策略
事务:事务用于保证数据库的完整性。
事务的特性
原子性
一致性
隔离性
持久性
外键:保证数据一致性的策略。
索引:对数据表中的一列或多列的值进行排序的一种结构。索引是进行记录快速定位的一种方法。索引分为普通索引、唯一索引、全文索引。
支持的存储引擎
MyISAM
InooDB
Memory
CSV:不支持索引
Archive
BlackHole:黑洞引擎,写入的数据都会消失,一般用于做数据复制的中继
特点
MyISAM
InooDB
Memory
Archive
存储限制
256TB
64TB
有
无
事务安全
-
支持
-
-
支持索引
支持
支持
支持
-
锁颗粒
表锁
行锁
表锁
行锁
数据压缩
支持
-
-
支持
支持外键
-
支持
-
-
修改数据表的存储引擎
在Mysql的配置文件中,默认存储引擎设置:default-storage-engine=INNODB
通过创建数据表命令实现,CREATE TABLE table_name(...) ENGINE = engine_name;
修改已创建的表的存储引擎:ALTER TABLE table_name ENGINE = engine_name;