零基础MySQL8.0（二）命名规则、sql分类和部分示例

上一篇，零基础MySQL8.0（一） 简介、数据类型、数据库设计
后更：摆了，太麻烦了，还是直接记sql吧。

参考

SQL语句的四大分类
Mysql数据库基础知识整理
MySQL官方文档

介绍

  先说一下一些sql命令的格式规则

1. 以英文分号;结尾
2. |作为间隔符，
3. []表示可选参数，[ a | b ] 表示 a 或 b 或都不要
4. {}表示必选项，{ a | b } 表示 a 或 b
5. …表重复，[ a | b ] … 表示可以重复添加参数 a 或 b 或都不要
6. 注释包括/**/和–（两个英文横杠）
7. 支持一些通配符，% 表0到多位任意， _ 表单个任意
8. db.*表示db的所有表，table.*表示表的所有列/属性/字段
9. '英文单引号是作字符串之用，而如果要把数据库名和表名括起来，需用`英文反引号，也就是 esc 键下面那个键
   10..是用来访问库表列的，形如 db_name.table_name.column_name

  其次是分类，网上关于SQL的分类，有4种的有5种的，按5种说是：数据定义语言(DDL)、数据查询语言(DQL)、数据操纵语言(DML)、数据控制语言(DCL)、事务控制语言（TCL）,但是MySQL官方文档只看到了DDL和DML在比较大的分类，而另外的都是细分，比如DQL其实就是select查询，官方是放在DML之下了，其他有的还细分的比较深，这里先按4种（也就是查询也在DML里）的情况来讨论。

• 数据定义语言（Data Definition Language）
  • 操作逻辑结构（表、视图、索引等）
• 数据操作语言（Data Manipulation Language）
  • 操作数据
• 数据控制语言（Data Control Language）
  • 操作数据库完整性和安全性等（权限控制）。如create user，grant等
• 事务控制语言 （Transaction Control Language）
  • 管理数据库中的事务。例如：START TRANSACTION、COMMIT、ROLLBACK等。

常用SQL

如下sql会写出命令格式、简单使用例子、复杂使用例子，部分是用ai生成的，尤其是复杂例子。

基本

1. mysql -uuser_name -p password; -- 登录数据库
2. show databases;  -- 查看所有数据库
3. show tables;
4. use db_name; -- 切换到名为db_name的数据库
5. help create user; -- 查看"create user"命令的帮助

DDL

包括数据库/表/索引/视图的增删改

1. 数据库

{CREATE | DROP } {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
	[option] -- 字符集，编码之类

/* 创建/删除数据库db_test */
{create| drop } database db_test;

-- 修改数据库就算了，没什么好改的，msyql不支持数据库改名，可以改的只有字符集、编码之类

2. 建表
   表的命令格式太麻烦，直接放示例了

/* 建表命令 */
CREATE TABLE IF NOT EXISTS `customer` (
  `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID', -- 整型，自增，主键
  `name` VARCHAR(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '姓名', -- 字符串，长度最大为50B，非空
  `age` INT(3) UNSIGNED ZEROFILL COMMENT '年龄', -- 整数，3位，不足3位时在前面补0
  `email` VARCHAR(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '电子邮箱', --默认为NULL
  `phone` VARCHAR(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '电话号码',
  `address` TEXT COLLATE utf8mb4_unicode_ci COMMENT '地址', -- 文本类型的列，可以存储大量字符数据
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', -- 时间戳类型的列，在插入数据时默认为当前时间
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', -- 时间戳类型，在更新数据时设置为当前时间
  PRIMARY KEY (`id`), -- 将id列设置为主键
  UNIQUE KEY `idx_customer_email` (`email`), -- 将email列设置为唯一键，保证每个值都是唯一的
  KEY `idx_customer_age` (`age`,`name`), -- 创建组合索引，包括age和name两列
  FULLTEXT KEY `idx_customer_name` (`name`) -- 全文索引，用于全文搜索
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='客户信息表'; -- 设置存储引擎为InnoDB，并且指定默认字符集为utf8mb4，并指定排序规则为utf8mb4_unicode_ci

3. 改表

   emm，懒得写了，去我另一篇博客看看吧。MYSQL改表记录

4. 删表

DROP [TEMPORARY] TABLE [IF EXISTS] -- TEMPORARY是临时表，一般不用
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

drop table `db_name`.`table_name`;

6. 索引 见上 create key/index
7. 视图 create view，作用就是复用临时表

DML

例如表数据的增删改查，具体看下一篇吧 零基础MySQL8.0（三）DML以及常用SQL记录

DCL

1. 创建用户 CREATE USER

/* 创建用户命令格式 */
CREATE USER [IF NOT EXISTS]
	user [auth_option] [, user [auth_option]] ... -- 可以同时创建多个用户
	DEFAULT ROLE role [, role ] ... -- 角色：一类权限的集合
	[REQUIRE {NONE | tls_option [[AND] tls_option] ...}] -- 用户认证要求（可选）
	[WITH resource_option [resource_option] ...] -- 用户资源选项（可选）
	[password_option | lock_option] ... -- 用户密码或锁定选项（可选）
	[COMMENT 'comment_string' | ATTRIBUTE 'json_object'] -- 用户注释或属性（可选）

/* 创建一个叫xiaoming的用户，主机在本地，密码为123456 */
CREATE USER 'xiaoming'@'localhost' IDENTIFIED BY '123456';

/* 创建一个user1用户，在192.168.1网段登录使用，密码为password123 */
CREATE USER IF NOT EXISTS 
	'user1'@'192.168.1.%' IDENTIFIED BY 'password123' 
	DEFAULT ROLE 'role1', 'role2' -- 指定用户的默认角色为'role1'和'role2'
	REQUIRE TLS SSL -- 要求通过TLS SSL进行连接
	WITH MAX_QUERIES_PER_HOUR 100 -- 设置每小时最大查询数为100
	PASSWORD EXPIRE INTERVAL 180 DAY -- 设置密码180天后过期
	COMMENT 'with full privileges'; -- 给用户添加注释为'with full privileges'

2. 用户授权 GRANT
   上面创建的用户，是没有操作权限的，需在这里进行赋予。
   另外，grant授权有些要求，用户a操作grant给用户b授予c权限，那么必须存在b这个用户，且a必须有c权限。

GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ... -- 授权的权限类型（可选）
    ON [object_type] priv_level -- 授予什么对象的权限，这个对象可以是库、表、列、函数等等
    TO user_or_role [, user_or_role] ... -- 权限授予给谁，用户/角色
    [WITH GRANT OPTION] -- 授予GRANT权限的选项（可选）
    [AS user -- 以指定用户的身份授予权限
        [WITH ROLE DEFAULT -- 使用指定用户的默认角色
          | NONE -- 不使用任何角色
          | ALL -- 使用所有角色
          | ALL EXCEPT role [, role ] ... -- 使用除指定角色之外的所有角色
          | role [, role ] ... -- 使用指定角色
        ]
    ]

/* 给小明授予数据库db1的所有表的所有权限 */
GRANT ALL ON db1.* TO 'xiaoming'@'localhost';

/* 给用户user1，user2授予角色role1，role2的权限 */
GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';

/* 给角色role3授予数据库world的所有表的select权限 */
GRANT SELECT ON world.* TO 'role3';

/* 给用户xiaoming授予数据库tmp_db的表user的name和age这两列的select权限 */
GRANT SELECT(name, age) ON tmp_db.user TO 'xiaoming'@'localhost';

/*  -- 授予SELECT、INSERT和UPDATE权限在mydatabase数据库中的所有表 */
GRANT SELECT, INSERT (column1, column2), UPDATE
	ON mydatabase.* 
	TO 'user1'@'localhost', 'user2'@'%'
	WITH GRANT OPTION -- 允许用户将授权转授给其他用户
	AS 'admin' -- 因为使用了AS关键字，接下来的WITH ROLE子句将被解释为'admin'用户的角色设置
	WITH ROLE DEFAULT -- 使用'admin'用户的默认角色

3. 收回权限 REVOKE
   这个很好理解，因为这就是grant的相反，就不放复杂示例了。

REVOKE [IF EXISTS]
    priv_type [(column_list)] [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user_or_role [, user_or_role] ...
    [IGNORE UNKNOWN USER]

revoke update on db_test.user from 'xiaoming'