Mysql学习笔记

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;
posted @ 2017-03-12 16:58  永无乡seven  阅读(180)  评论(0)  编辑  收藏  举报