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 regexp '1000' ord