MySQL必知必会学习笔记

MySQL必知必会学习笔记

systemctl start docker
docker start ff7
docker exec -it ff7 /usr/bin/mysql -uroot -p123456

查看数据库信息

​ 查看所有数据库：

show databases;

​ 使用某个数据库

use test;

​ 查看数据库内的所有表：

show tables;

​ 显示表内的所有列：

show columns from 表名;
可以简化为：
describe 表名;

​ 显示广泛的服务器状态信息：

show status;

​ 显示创建特定数据库

show create database;

​ 显示创建特定表

show create table;

​ 显示授予用户（所有用户或授权用户）的安全权限

show grants;

​ 显示服务器错误

show errors;

​ 显示服务器警告

show warnings;

​ 使用 help show 来显示允许的show语句

MySQL数据类型

如果数值是计算（求和、平均等）中使用的数值，则应该存储在数值数据类型列中。如果作为字符串（可能只包含数字）使用，则应该保存在串数据类型列中。

字符串：

字符串必须扩在引号内。

数据类型	说明
CHAR	1～255个字符的定长串。它的长度必须在创建时指定，否则MySQL假定为R(1)
ENUM	接受最多64 K个串组成的一个预定义集合的某个串
LONGTEXT	与TEXT相同，但最大长度为4 GB
MEDIUMTEXT	与TEXT相同，但最大长度为16 K
SET	接受最多64个串组成的一个预定义集合的零个或多个串
TEXT	最大长度为64 K的变长文本
TINYTEXT	与TEXT相同，但最大长度为255字节
VARCHAR	长度可变，最多不超过255字节。如果在创建时指定为VARCHAR(n)，则可存储0到n个字符的变长串（其中n≤255）

数值：

数值不支持以0开头，比如以0开头的电话、邮编不能以数值类型存储。

所有数值类型（除bit和boolean）都可以有符号或无符号，默认为有符号，使用 unsigned 指定无符号。

数据类型	说 明
BIT	位字段，1～64位。（在MySQL 5之前，BIT在功能上等价于TINYINT
BIGINT	整数值，支持-9223372036854775808～9223372036854775807（如果是UNSIGNED，为0～18446744073709551615）的数
BOOLEAN（BOOL）	布尔标志，或者为0或者为1，主要用于开/关（on/off）标志
DECIMAL（DEC）	精度可变的浮点值
DOUBLE	双精度浮点值
FLOAT	单精度浮点值
INT（INTEGER）	整数值，支持-2147483648～2147483647（如果是UNSIGNED，为0～4294967295）的数
MEDIUMINT	整数值，支持-8388608～8388607（如果是UNSIGNED，为0～16777215）的数
REAL	4字节的浮点值
SMALLINT	整数值，支持-32768～32767（如果是UNSIGNED，为0～65535）的数
TINYINT	整数值，支持-128～127（如果为UNSIGNED，为0～255）的数

日期和时间

数据类型	说 明
DATE	表示1000-01-01～9999-12-31的日期，格式为YYYY-MM-DD
DATETIME	DATE和TIME的组合
TIMESTAMP	功能和DATETIME相同（但范围较小）
TIME	格式为HH:MM:SS
YEAR	用2位数字表示，范围是70（1970年）～69（2069年），用4位数字表示，范围是1901年～2155年

二进制数据

二进制数据类型可存储任何数据（甚至包括二进制信息），如图像、多媒体、字处理文档等。

数据类型	说 明
BLOB	Blob最大长度为64 KB
MEDIUMBLOB	Blob最大长度为16 MB
LONGBLOB	Blob最大长度为4 GB
TINYBLOB	Blob最大长度为255字节

创建和操作表

创建表：

create table customers
(
	cust_id		 int		not NULL	auto_increment,
	cust_name	 char(50)	not NULL,
	cust_address char(50)	NULL,
	cust_city	 char(50)	NULL,
	cust_state	 char(5)	NULL,
	cust_zip	 char(10)	NULL,
	cust_country char(50)	NULL,
	cust_contact char(50)	NULL,
	cust_email	 char(255)	NULL,
	primary key(cust_id)
) engine=innodb;

create table orderitems
(
	order_num	int		 NOT NULL,
	order_item	int		 NOT NULL,
	prod_id		char(10) NOT NULL,
	quantity	int		 NOT NULL	default 1,
	item_price	decimal(8, 2)	NOT NULL,
	primary key(order_num, order_item)
) engine=innodb;

如果想不存在时才创建，可以在表名后加上 if not exists 。

如果允许NULL，列名后可以不加NULL，默认允许空值。

每个表只能有一个自增列，并且必须被索引。如果手动指定了自增列的值，则后续开始从手动指定的值开始自增。

可以使用 last_insert_id() 获取最后一个自增的值。

select last_insert_id() ....

可以给列指定默认值，但mysql不允许函数作为默认值，只支持常量。

许多数据库开发人员设定列使用默认值而不是NULL，特别是对用于计算或数据分组的列更是如此。

引擎类型

MySQL有多个用于具体管理和处理数据的内部引擎，执行创建表、处理数据请求等操作。

可以不省略 engine = 语句，会使用默认引擎

查看默认引擎：

show variables like '%storage_engine%';
或
show engines;

更改默认引擎：

（临时）

set default_storage_engine=引擎名

（永久）

在mysql配置文件（linux下为/etc/my.cnf），在mysqld后面增加default-storage-engine=INNODB即可。

更改表的引擎：

alter table 表名 engine=引擎名;

引擎可以混用，但要考虑另一个引擎是否也有需要的功能。

引擎比较：

功能	MylSAM	MEMORY	InnoDB	Archive
存储限制	256TB	RAM	64TB	None
支持事务	No	No	Yes	No
支持全文索引	Yes	No	No	No
支持树索引	Yes	Yes	Yes	No
支持哈希索引	No	Yes	No	No
支持数据缓存	No	N/A	Yes	No
支持外键	No	No	Yes	No

• 如果要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制，InnoDB 是一个很好的选择。
• 如果数据表主要用来插入和查询记录，则 MyISAM 引擎提供较高的处理效率。
• 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的 MEMORY 引擎中，MySQL 中使用该引擎作为临时表，存放查询的中间结果。
• 如果只有 INSERT 和 SELECT 操作，可以选择Archive 引擎，Archive 存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive 存储引擎非常适合存储归档数据，如记录日志信息可以使用 Archive 引擎。

引擎的具体信息参考https://dev.mysql.com/doc/refman/8.0/en/storage-engines.html

更新表

• 更新表时，最好做一个完整的备份（模式和数据的备份），因为更改不能撤销。

使用 alter table 来更新表，但尽量设计表时多考虑，以便不对表进行大的改动。

添加与删除列：

alter table vendors add vend_phone char(20);

alter table vendors drop column vend_phone;

定义外键：

alter table orderitems 
add constraint fk_orderitems_orders
foreign key(order_num) references orders (order_num);

对单个表进行多个更改，可以使用单条alter table语句，每个更改用逗号分隔。

重命名表：

rename talbe 旧名 to 新名 [, 旧名 to 新名];

复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：

• 用新的列布局创建一个新表；
• 使用INSERT SELECT语句从 旧表复制数据到新表。如果有必要，可使用转换函数和计算字段；
• 检验包含所需数据的新表；
• 重命名旧表（如果确定，可以删除它）；
• 用旧表原来的名字重命名新表；
• 根据需要，重新创建触发器、存储过程、索引和外键。

删除表

删除整个表而不是内容：

drop table 表名;

没有确认，也不能撤销。

检索数据

简单检索：

select 列名 from 表名;

select 列名, 列名, 列名 from 表名;

一般，除非你确实需要表中的每个列，否则最好别使用*通配符。虽然使用通配符可能会使你自己省事，不用明确列出所需列，但检索不需要的列通常会降低检索和应用程序的性能。

select * from 表名;

去掉重复数据：

select distinct 列名 from 表名;

返回指定行：

select 列名 from 表名 limit [开始行数, ]返回的行数;
如：
select prod_name from products limit 1,5;
返回从第二行(下标从0开始)开始往下数五个
行数不够时能只返回能返回的行数

另一种语法
select prod_name from products limit 5 offset 1;
从行1开始取5行，更直观

限定列名或表名

select 表名.列名 from 数据库名.表名;
select products.prod_name from test.products;

插入数据（insert）

---

更改语句的优先级

数据库的更新操作可能很耗时（特别是有很多索引需要更新时），并可能降低等待处理的select语句的性能。在请求较多时，可以降低语句的优先级：

insert low_priority into

update low_priority

delete low_priority

replace low_priority

---

插入可以用几种方式使用：

• 插入完整的行；

• 插入行的一部分；

• 插入多行；

• 插入某些查询的结果。

可针对每个表或每个用户，利用MySQL的安全机制禁止使用INSERT语句。

简单示例：

insert into customers 
values(NULL, 
	'Pep E. LaPew', 
	'100 Main Street', 
	'Los Angeles', 
	'CA', 
	'90046', 
	'USA', 
	NULL, 
	NULL);

插入一行新值，没有值就指定NULL（如果允许NULL），各个列必须以在表的定义中的次序填充。

第一列也是NULL，因为设置了自增，设定的NULL会被mysql忽略。

如果不想依赖表定义的次序：

insert into customers(cust_name, 
	cust_address, 
	cust_city, 
	cust_state, 
	cust_zip, 
	cust_country, 
	cust_contact, 
	cust_email) 
values('Pep E. LaPew', 
	'100 Main Street', 
	'Los Angeles', 
	'CA', 
	'90046', 
	'USA', 
	NULL, 
	NULL);

自增的cust_id列和值可以省略不写了。

先明确给出要插入的列名，顺序随意，再用values给出对应值。

可以忽略一些列，省略列必须至少满足以下条件之一：

• 允许NULL（无值或空值）
• 有默认值

如果省略了不满足条件的列会报错。

一次插入多行：

insert into customers(cust_name, 
	cust_address, 
	cust_city, 
	cust_state, 
	cust_zip, 
	cust_country) 
values(
	'Pep E. LaPew',
	'100 Main Street',
	'Los Angeles',
	'CA',
	'90046',
	'USA'),
	(
	'M. Martian',
	'42 Galaxy Way',
	'New York',
	'NY',
	'11213',
	'USA');

这样比多个insert语句快。

插入返回的数据：

假如你想从另一表中合并客户列表到你的customers表。不需要每次读取一行，然后再将它用INSERT插入：

insert into customers(cust_id,
	cust_contact,
	cust_email,
	cust_name,
	cust_address,
	cust_city,
	cust_state,
	cust_zip,
	cust_country)
select cust_id,
	cust_contact,
	cust_email,
	cust_name,
	cust_address,
	cust_city,
	cust_state,
	cust_zip,
	cust_country
from custnew;

更新数据

注意update的where语句，不然会更新表中的所有行。

可以限制和控制update语句的使用。

update 表名 set 列名 = 新值 [, 列名 = 新值] where 条件

update customers set cust_email = 'elmer@fudd.com' where cust_id = 10005;

• update也可以使用select返回的子查询

如果用update更新多行，但更新某行时出现错误，则所有操作被回滚。如果即使发生错误也要继续更新，可以使用ignore：

update ignore 表名....

想用update来删除某个列的值，可以将它更新为NULL：

update customers set cust_email = NULL where cust_id = 10005;

删除数据

同样，注意where语句，不然会删除表中的所有行。

可以限制和控制delete语句的使用。

delete from 表名 where 条件

delete from customers where cust_id = 10006;

delete删除整行，update可以删除某个列。

delete不会删除表本身。但如果想从表中删除所有行，不要使用delete，使用 truncate ，速度更快（truncate实际上时删除原来的表并重新创建一个表，而delete是逐行删除数据）。

如果从一个表中删除大量数据，应该使用 OPTIMIZE TABLE 来回收所用的空间，优化性能。

更新和删除的建议：

• 除非确实想更新和删除每一行，否则不要使用不带where的update和delete
• 保证每个表都有主键，尽可能在where中使用它，可以指定各逐渐、多个值或值的范围
• 在使用update和delete使用where之前，应该先用select进行测试，保证过滤的是正确的记录
• 使用强制实施引用完整性的数据库，避免删除具有与其他表相关联的数据的行

排序数据

排序

select 列名[, 列名...] from 表名 order by 列名[, 列名...]

倒序

select ........ 列名 desc;
desc只对它前面的列名生效，如果希望多个降序，需要都指定desc

示例：找出列中的最高值

select prod_price from products order by prod_price desc limit 1;

过滤数据

select 列名[, 列名] from 表名 where 条件[ and 条件] [ order by];

算数运算符： = !=(或<>) > < >= <= BETWEEN IS NULL

select prod_name, prod_price from products where prod_price = 2.50

select prod_id, prod_price, prod_name from products where vend_id = 1003 and prod_price <=10;

select prod_name, prod_price from products where vend_id = 1002 or vend_id = 1003;

select prod_name, prod_price from products where (vend_id=1002 or vend_id=1003 ) and prod_price >= 10;

select prod_name, prod_price from products where prod_price between 5 and 10;

逻辑运算符：AND OR IN NOT

​ (AND优先级比OR高)

​ 示例：

查找所有id=1002或id=1003的值。IN语句的整个清单必须在圆括号中，比如换成1002，1004则不会包含1003

IN一般比OR执行的快

IN可以包含其他select语句

select prod_name, prod_price from products where vend_id in (1002, 1003) order by prod_name;

NOT用来否定它之后的所跟的任何条件

列出出1002和1003之外的所有供应商：

select prod_name, prod_price from products where vend_id not in (1002, 1003) order by prod_name;

通配符：使用 LIKE 操作符： % _

• 不要过度使用通配符，因为它更耗时间。如果其他操作符能达到同样的目的，应该使用其他操作符
• 尽量不要将通配符用在搜索模式的开头，那样搜索起来是最慢的

%可以匹配[0, ∞)个字符，但不匹配空格。比如使用 ‘%anvil’ 搜索时，不会匹配 ‘anvil ‘。一个简单的解决方法是在搜索模式最后加’%’，更好的办法是使用函数去掉首尾空格。

%不匹配NULL

select prod_id, prod_name from products where prod_name like 'jet%';

select prod_id, prod_name from products where prod_name like '%anvil%';

‘_’ 只匹配1个字符，0个和多个都不匹配

select prod_id, prod_name from products where prod_name like '_ ton anvil';

正则表达式： REGEXP： ‘.’ ‘|’ ‘[ ]’ ‘^’ ‘[-]’ ‘\\’ 字符类 多次匹配

REGEXP将后面所跟的东西作为正则表达式处理

select prod_name from products where prod_name rege