MySQL使用汇总

寻梦之旅yan

已于 2024-02-03 20:42:14 修改

阅读量1k

点赞数 29

分类专栏： MySQL 文章标签： mysql 数据库

于 2024-01-16 23:56:35 首次发布

本文链接：https://blog.csdn.net/shuiquliu1987/article/details/135619175

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

安装部署

MySQL8.0文档：link
MySQL下载：link
MySQL8.0 tar.gz包安装推荐：link

注意：以下所有SQL均在MySQL 5.7.30版本执行通过。

用户

-- 创建用户
create user 's_user'@'localhost' identified by 'Yu_345678';
-- 授权
grant all privileges on *.* to 's_user' identified by 'Yu_345678';
-- 刷新
flush privileges;
-- 查看数据库中创建的用户
select user from mysql.user;
-- 删除创建的用户
drop user 's_user'@'localhost';

问题

创建用户报错：ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
查看密码策略，根据密码策略创建用户。

SHOW VARIABLES LIKE 'validate_password%';

密码策略如下：长度8位、要求要有数字、特殊符号等，参考上面创建用户。
在这里插入图片描述

数据库

-- 展示当前用户下的所有数据库
show databases;
-- 创建数据库
create database test;
-- 进入数据库
use test;
-- 删除数据库
drop database test;

表

MySQL的表创建在数据库中，表在数据库中创建有两种引用数据库名称的方式：

-- 方式一：以test数据库为例
create table test.tb_001 (c0 int);
-- 方式二
use test;
create tb_001(c0 int);
-- 可以查看当前数据库下所有的表
use test;
show tables;
-- 查看表结构
show create table test.tb_001;
show create table test.tb_001\G
-- 删除表
drop table test.tb_001;

MySQL支持各种分区表，比如RANGE分区表、LIST分区表、HASH分区表、KEY分区表、二级分区表等。

RANGE分区表

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
);
-- 或者加MAXVALUE
CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

LIST分区表

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

HASH分区表

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

KEY分区表

-- 主键分区表
CREATE TABLE k1 (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2;
-- 唯一键分区表
CREATE TABLE k1 (
    id INT NOT NULL,
    name VARCHAR(20),
    UNIQUE KEY (id)
)
PARTITION BY KEY()
PARTITIONS 2;

二级分区表（Subpartitioning）

-- range & hash
CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) )
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );
-- 或者下面方式创建二级分区表
CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s2,
            SUBPARTITION s3
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s4,
            SUBPARTITION s5
        )
    );

支持的类型

数据类型	说明
TINYINT	有符号数据范围[-128, 127]，无符号数据范围[0， 255]
SMALLINT	有符号数据范围[-32768, 32767]，无符号数据范围[0， 65535]
MEDIUMINT	有符号数据范围[-8388608, 8388607]，无符号数据范围[0， 16777215]
INT	有符号数据范围[-2147483648, 2147483647]，无符号数据范围[0， 4294967295]
BIGINT	有符号数据范围[-2^63, 2^63 - 1]，无符号数据范围[0， 2^64 - 1]
CHAR(M)	M长度范围[0, 255] ，比如char(0)，char(255)，char(256)会报错
VARCHAR(M)	创建表的SQL为create table t1(id varchar(21841),c1 varchar(2));，M长度范围[0, 21844]，这里的charset是utf8，长度范围是可创建表的范围；创建表的SQL为create table t1(id varchar(21841),c1 varchar(2));时，范围最大值为21841，所以创建表时需要注意字段和字符集对表中字段类型为varchar(M)时M长度的影响。
FLOAT	范围为-3.402823466E+38到-1.175494351E-38、0以及1.175494351E-38到3.402823466E+38
DOUBLE	范围为-1.7976931348623157E+308到-2.2250738585072014E-308、0以及2.2250738585072014E-308到1.7976931348623157E+308
DECIMAL(M,D)	M的范围是[1,65]，D的范围[0, 30]，且D不能大于M
BIT(M)	M的范围是[1，64]
TIME	范围是[-838:59:59, 838:59:59]
DATE	范围是[1000-01-01, 9999-12-31]
DATETIME	范围是[1000-01-01 00:00:00, 9999-12-31 23:59:59]
TIMESTAMP	范围是[1970-01-01 00:00:01 UTC, 2038-01-19 03:14:07 UTC]
YEAR	范围是[1901, 2155]以及0000
BINARY(M)	M的长度范围是[0, 255] ，比如binary(0)，binary(255)，binary(256)会报错
VARBINARY(M)	charset为utf8，创建表的SQL为create table t1(id varbinary(65532));时，M的长度范围是[0, 65532] ，varbinary(65533)会报错。
TINYBLOB	最大长度为 255 字节
BLOB	最大长度为 65,535 字节（64 KB）
MEDIUMBLOB	最大长度为 16,777,215 字节（16 MB）
LONGBLOB	最大长度为 4,294,967,295 字节（4 GB）
TINYTEXT	最大长度为255个字符
TEXT	最大长度为65,535个字符
MEDIUMTEXT	最大长度为16,777,215个字符
LONGTEXT	最大长度为4,294,967,295个字符
ENUM	可以存储最多65535个不同的值
SET	可以存储最多64个不同的值
JSON	可以存储最大长度为65535个字节的数据，JSON类型支持有效的 JSON 数据，包括对象（Object）、数组（Array）、字符串（String）、数字（Number）、布尔值（Boolean）和 NULL。

索引

MySQL支持的索引有：唯一索引、联合索引、全文索引、降序索引、函数索引和空间索引。
创建索引均有三种方式，一种是建表的时候顺便就创建，一种是使用ALTER TABLE创建，还有一种是使用CREATE INDEX创建，详细见下面的例子。

CREATE TABLE users (
    id INT,
    username VARCHAR(50),
    password VARCHAR(255),
    email VARCHAR(100),
    created_at TIMESTAMP,
    INDEX(id)
);
-- idx是索引名称，users是表名称，username是users表中的字段，参考users表
CREATE INDEX inx_001 ON users (username);
--
ALTER TABLE users ADD INDEX idx_002 (id);
-- 查询索引
SHOW INDEX FROM users;

-- 删除索引
-- ALTER TABLE方式
ALTER TABLE users DROP INDEX idx_002;
-- DROP INDEX方式
DROP INDEX idx_002 ON users;

约束

约束包括唯一约束、主键约束、外键约束、非空约束、默认约束、CHECK约束等。
创建索引均有三种方式，一种是建表的时候顺便就创建，一种是使用ALTER TABLE创建。

唯一约束

-- 随表创建唯一约束
CREATE TABLE users (
    id INT UNIQUE,
    username VARCHAR(50),
    password VARCHAR(255),
    email VARCHAR(100),
    created_at TIMESTAMP
);
ALTER TABLE users ADD UNIQUE uni_001 (username);

可以通过show create table tbname来查看创建的约束，如下示：

SHOW CREATE TABLE users;
或
SHOW CREATE TABLE users\G

如下图：
在这里插入图片描述

主键约束

-- 随表创建主键约束
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    password VARCHAR(255),
    email VARCHAR(100),
    created_at TIMESTAMP
);

触发器

-- 建表
CREATE TABLE tb_trigger_001 (id int, c1 varchar(20));
CREATE TABLE tb_trigger_002 (id int, c1 varchar(20));

delimiter &&

CREATE TRIGGER tri_001
AFTER INSERT ON tb_trigger_001
FOR EACH ROW
BEGIN
	INSERT INTO tb_trigger_02(id, c1) VALUES(NEW.id, NEW.c1);
END;
&&
delimiter ;

表空间

-- 创建表空间
CREATE TABLESPACE tps_001 
ADD DATAFILE 'tps_001.ibd' 
ENGINE=InnoDB
INITIAL_SIZE = 4M
AUTOEXTEND_SIZE = 4M
MAX_SIZE = 1024M;
-- 删除表空间
DROP TABLESPACE tps_001;

事务

事务的隔离级别

MySQL支持四种事务的隔离级别：分别是：
读未提交：允许读到未提交的数据
读已提交：只能读到已提交的内容
可重复读：同一个事务在相同查询条件下两次查询得到的数据结果一致
可串行化：事务进行串行化，但是牺牲了并发性能
事务的隔离级别可以保证事务的特性。
在MySQL数据库中查看事务的隔离级别：如下，MySQL的默认事务的隔离级别是可重复读

SELECT @@session.tx_isolation;

在这里插入图片描述

常用的查询

查询执行过/正在执行的SQL

查询正在执行的SQL

select id,user,db,command, time, state, info from information_schema.processlist where info is not null;

查询已经执行的SQL

select event_id,thread_id, sql_text from performance_schema.events_statements_history where sql_text is not null;

在/etc/my.cnf或my.ini中设置

log_output=FILE
general_log_file=/var/lib/mysql/general.log
general_log=1

重启MySQL，这里用

systemctl restart mysqld.service

就可以在general.log中查看执行过的SQL了

未完，待续…
参考：
【索引】https://blog.csdn.net/cybeyond_xuan/article/details/78876089

寻梦之旅yan

关注

29
点赞
踩
18

收藏

觉得还不错? 一键收藏
1
评论
MySQL使用汇总

创建约束和索引均有三种方式，一种是建表的时候顺便就创建，一种是使用ALTER TABLE创建，还有一种是使用CREATE INDEX创建，详细见下面的例子。MySQL支持各种分区表，比如RANGE分区表、LIST分区表、HASH分区表、KEY分区表、二级分区表等。MySQL支持的索引有：唯一索引、联合索引、全文索引、降序索引、函数索引和空间索引。约束包括唯一约束、主键约束、外键约束、非空约束、默认约束、CHECK约束等。密码策略如下：长度8位、要求要有数字、特殊符号等，参考上面创建用户。
复制链接

扫一扫