MySQL使用汇总

发布时间：2024年01月18日

MySQL使用汇总

安装部署

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;

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

文章来源:https://blog.csdn.net/shuiquliu1987/article/details/135619175
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
最新文章