mysql 即学a又学b_Mysql学习笔记

最新推荐文章于 2022-08-10 16:03:51 发布

毕小荷

最新推荐文章于 2022-08-10 16:03:51 发布

阅读量263

点赞数

文章标签： mysql 即学a又学b

本文链接：https://blog.csdn.net/weixin_30118919/article/details/113912693

版权

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：不支持索引