SQL基础
1. SQL分类
SQL语句主要分为以下三类
DDL： 数据定义语言,用于定义不同的数据段、数据库、表、列、索引等数据库对象。常用的关键字包含create、alter、drop、truncate等
DML： 数据操纵语句,用于新增、删除、更新和查询数据库里的记录。常用的关键字包含insert、delete、update和select 等。
DCL： 数据控制语言，用户定义数据库、表、字段、用户的访问权限和安全级别等。常用的关键字包含grant、revoke等。
2 DDL语言
2.1 数据库相关
2.1.1 创建数据库
MySQL创建数据库的常用语法如下：
/** 创建数据库 **/ CREATE DATABASE dbname DEFAULT CHARACTER SET character_name;
例如：创建一个 testdb 数据库
/** 创建testdb数据库 **/ CREATE DATABASE testdb;
注： 字符集通常可以不指定，使用默认的字符集,但前提是全局字符集设置是合理的。
2.1.2 查看数据库
查看当前实例有哪些数据库，可以用如下命令：
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | testdb | +--------------------+ 5 rows in set (0.00 sec)
可以发现，除了刚创建的testdb库,还存在其他几个系统库，这几个系统库的功能如下：
- information_schema 存储的是本实例的数据库元数据信息，例如包含数据库信息、表信息、列信息、权限信息、字符集信息、分区信息等。
- mysql 存储的是系统的用户权限信息、慢查询日志表、general日志表等信息。
- performance_schema 存储的是数据库实例的性能参数，MySQL5.7默认是开启的。
- sys 是基于performance_schema库中相关表的视图信息，目的是更好的查看performance_schema库表间的相关信息，提高阅读性。
另外 查看数据库建库脚本可以用如下脚本：
mysql> show create database testdb; +----------+-----------------------------------------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------------------------------------+ | testdb | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ | +----------+-----------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
2.1.3 选择数据库
当需要使用具体数据库时，需要选择对应的库，可以用如下脚本
mysql> use testdb; Database changed
2.1.4 修改数据库
/** 修改数据库字符集 **/
mysql> alter database testdb DEFAULT CHARACTER SET utf8; Query OK, 1 row affected, 1 warning (0.00 sec)
/** 修改后查看如下 **/ mysql> show create database testdb; +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | testdb | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+-----------------------------------------------------------------+ 1 row in set (0.00 sec)
如果想查看支持的字符集，可以使用如下脚本：
mysql> show character set; +----------+---------------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+---------------------------------+---------------------+--------+ | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 | | ascii | US ASCII | ascii_general_ci | 1 | | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | | binary | Binary pseudo charset | binary | 1 | | cp1250 | Windows Central European | cp1250_general_ci | 1 | | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 | | cp1256 | Windows Arabic | cp1256_general_ci | 1 | | cp1257 | Windows Baltic | cp1257_general_ci | 1 | | cp850 | DOS West European | cp850_general_ci | 1 | | cp852 | DOS Central European | cp852_general_ci | 1 | | cp866 | DOS Russian | cp866_general_ci | 1 | | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 | | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 | | euckr | EUC-KR Korean | euckr_korean_ci | 2 | | gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 | | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 | | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 | | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 | | greek | ISO 8859-7 Greek | greek_general_ci | 1 | | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 | | hp8 | HP West European | hp8_english_ci | 1 | | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 | | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 | | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 | | latin1 | cp1252 West European | latin1_swedish_ci | 1 | | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 | | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 | | macce | Mac Central European | macce_general_ci | 1 | | macroman | Mac West European | macroman_general_ci | 1 | | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 | | swe7 | 7bit Swedish | swe7_swedish_ci | 1 | | tis620 | TIS620 Thai | tis620_thai_ci | 1 | | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 | | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 | | utf16 | UTF-16 Unicode | utf16_general_ci | 4 | | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 | | utf32 | UTF-32 Unicode | utf32_general_ci | 4 | | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 | +----------+---------------------------------+---------------------+--------+ 41 rows in set (0.00 sec)
建议字符集配置在配置文件中，初始化启动后即全局生效，整库的字符集统一。
2.1.5 删除数据库
删除数据库的语法很简单，例如：
mysql> drop database testdb; Query OK, 0 rows affected (0.00 sec)
注： 在确定可以删除数据库的情况下再去执行删除操作，否则恢复较麻烦。
2.2 表相关
2.2.1创建表
由于上一步将库删除了，本次创建表前先创建数据库testdb，再选择使用testdb库，然后在testdb库下创建students表,示例如下：
mysql> create database testdb; Query OK, 1 row affected (0.00 sec) mysql> use testdb; Database changed mysql> create table students ( id int not null auto_increment primary key, -> stu_name varchar(10) not null comment '姓名', -> age tinyint comment '年龄', -> class_no varchar(10) comment '班级', -> key idx_stu_name(stu_name) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec)
2.2.2 查看表结构
查看表结构的方式有多种方式，在此主要介绍2种，示例如下：
/** 查看字段名及字段类型等 **/ mysql> desc students; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | stu_name | varchar(10) | NO | MUL | NULL | | | age | tinyint(4) | YES | | NULL | | | class_no | varchar(10) | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) /** 查看建表语句 **/ mysql> show create table students\G *************************** 1. row *************************** Table: students Create Table: CREATE TABLE `students` ( `id` int(11) NOT NULL AUTO_INCREMENT, `stu_name` varchar(10) NOT NULL COMMENT '姓名', `age` tinyint(4) DEFAULT NULL COMMENT '年龄', `class_no` varchar(10) DEFAULT NULL COMMENT '班级', PRIMARY KEY (`id`), KEY `idx_stu_name` (`stu_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
desc的方式比较方便查看字段信息，但是看不到备注及索引等其他信息，show的方式可以查看完整的建表语句。
2.2.3 修改表
MySQL修改表的语法与其他数据库有点差异。
2.2.3.1 修改字段类型
语法如下：
ALTER TABLE table_name MODIFY [COLUMN] column_define [FIRST|AFTER column_name]
其中MODIFY后面的COLUMN 关键字是可选性，FIRST和AFTER column_name 分表代表放在表的第一个字段 或置于某个字段之后。
2.2.3.2 新增字段
语法如下：
ALTER TABLE table_name ADD column_define [FIRST|AFTER column_name]
2.2.3.3 修改字段名
ALTER TABLE table_name change column_name_old column_name_new column_define [FIRST|AFTER column_name]
修改字段名可以用change处理，另外change也可以修改字段类型等。
2.2.3.4 删除字段
ALTER TABLE table_name DROP [COLUMN] column_name;
2.2.4 删除表
删除表结构的语法类似于删除库
DROP TABLE table_name;
2.2.5 添加索引
索引的介绍会在后面详细介绍，此处简单列一下创建索引的实例
ALTER TABLE table_name ADD KEY key_name(column_names);
例如：
/** students表的class_no 添加索引 **/ mysql> alter table students add key idx_class_no(class_no); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
2.2.6 修改表名
修改表名的语法为
ALTER TABLE table_name_old RENAME TO table_name_new;
例如：
/** 将students表名变更为 student **/ mysql> alter table students rename to student; Query OK, 0 rows affected (0.01 sec)
MySQL中如果需要修改数据库名可以通过修改表名的方式处理，步骤为新建新的库名，再将表通过修改表名的方式迁移至新的库上即可，整个过程比较快。
3 DML语句
3.1 新增记录
往一张表里新增记录的基本语法如下：
INSERT INTO table_name(column_name_list) values(values_list1),(values_list2);
例如：
/** 逐条新增记录 **/ mysql> insert into student(stu_name,age,class_no) values('张三1',20,'201901'); Query OK, 1 row affected (0.00 sec) mysql> insert into student(stu_name,age,class_no) values('李四',19,'201901'); Query OK, 1 row affected (0.00 sec)
一次性插入多条记录：
mysql> insert into student(stu_name,age,class_no) values('王五',19,'201901'),('Lisa',21,'201902'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0
ps: 建议用批量插入的方式，以此来减少网络开销及数据库IO操作次数，这是数据库优化的一个技能点。
不列出 字段名时，插入时需要将所有字段赋值：
mysql> insert into student values(5,'陈六',21,'201902'); Query OK, 1 row affected (0.00 sec)
MySQL 的自增列可以不对其赋值，也可以如例子中指定为其赋值。如果既想不列出表的所有字段，又想自增列自动递增，则可以用null或0 作为占位处理，例如：
/** 查询当前所有记录 **/ mysql> select * from student; +----+----------+------+----------+ | id | stu_name | age | class_no | +----+----------+------+----------+ | 1 | 张三1 | 20 | 201901 | | 2 | 李四 | 19 | 201901 | | 3 | 王五 | 19 | 201901 | | 4 | Lisa | 21 | 201902 | | 5 | 陈六 | 21 | 201902 | +----+----------+------+----------+ 5 rows in set (0.00 sec) /** 使用null占位，让自增列自动自增 **/ mysql> insert into student values(null,'李白',21,'201901'); Query OK, 1 row affected (0.00 sec) /** 使用 0 占位，让自增列自动自增 **/ mysql> insert into student values(0,'李白1',23,'201902'); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +----+----------+------+----------+ | id | stu_name | age | class_no | +----+----------+------+----------+ | 1 | 张三1 | 20 | 201901 | | 2 | 李四 | 19 | 201901 | | 3 | 王五 | 19 | 201901 | | 4 | Lisa | 21 | 201902 | | 5 | 陈六 | 21 | 201902 | | 6 | 李白 | 21 | 201901 | | 7 | 李白1 | 23 | 201902 | +----+----------+------+----------+ 7 rows in set (0.00 sec)
有时,新增一条记录时,如果该条记录的主键或唯一索引不冲突则新增，如果存在，则修改对应内容，例如
/** 新增一条记录，如果不存在id=7的记录，则新增，如果存在则将名称变更为杜甫 **/
mysql> insert into student values(7,'李白1',23,'201902') on duplicate key update stu_name = '杜甫'; Query OK, 2 rows affected (0.00 sec) mysql> select * from student; +----+----------+------+----------+ | id | stu_name | age | class_no | +----+----------+------+----------+ | 1 | 张三1 | 20 | 201901 | | 2 | 李四 | 19 | 201901 | | 3 | 王五 | 19 | 201901 | | 4 | Lisa | 21 | 201902 | | 5 | 陈六 | 21 | 201902 | | 6 | 李白 | 21 | 201901 | | 7 | 杜甫 | 23 | 201902 | +----+----------+------+----------+ 7 rows in set (0.00 sec)
常见问题：字段数与value值个数不一致，例如：
/** 字段数与value值的个数不匹配 **/ sql> insert into student values('王维',23,'201902'); ERROR 1136 (21S01): Column count doesn't match value count at row 1
3.2 修改记录
先创建一张class表，后面也将用到
/** 创建表 **/ mysql> create table class(class_no varchar(10) not null primary key ,class_name varchar(10),location varchar(50)); Query OK, 0 rows affected (0.01 sec) /** 插入2条记录 **/ mysql> insert into class values('201901','2019级01班','博学北楼A401'),('201902','2019级02班','博学北楼A402'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0
3.2.1 修改单表的字段内容
/** 将王五同学的年龄变为20 **/ mysql> update student set age =20 where stu_name='王五'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
3.2.2 同时修改多张表
/** 同时将201902班的学生年龄+1，且班级location变更为博学北楼B401 **/ mysql> update student s,class c -> set s.age=s.age+1 ,c.location ='博学北楼B401' -> where s.class_no =c.class_no and c.class_no = '201902'; Query OK, 4 rows affected (0.00 sec) Rows matched: 4 Changed: 4 Warnings: 0
其他的关联相关内容较多，将在后续单独推出。
3.3 删除记录
删除记录也可以删除单表记录和同时删除多表记录
3.3.1 删除单表记录
/** 将Lisa同学除名 ** / mysql> delete from student where stu_name='Lisa'; Query OK, 1 row affected (0.00 sec)
3.3.2 同时删除多张表的记录
/** 同时删除201901班的学生及班级信息 **/ mysql> delete s,c from student s ,class c where s.class_no=c.class_no and c.class_no='201901'; Query OK, 5 rows affected (0.00 sec) ## 当然 此处也可以删除其中一个表的内容
3.4 查询记录
3.4.1 单表查询
/** 查询所有字段 **/ mysql> select * from student; +----+----------+------+----------+ | id | stu_name | age | class_no | +----+----------+------+----------+ | 5 | 陈六 | 22 | 201902 | | 7 | 杜甫 | 24 | 201902 | +----+----------+------+----------+ 2 rows in set (0.00 sec) /** 查询其中需要的字段 **/ mysql> select stu_name from student; +----------+ | stu_name | +----------+ | 杜甫 | | 陈六 | +----------+ 2 rows in set (0.00 sec) /** 按条件筛选对应记录的对应字段 ** / mysql> select stu_name,class_no from student where age < 23; +----------+----------+ | stu_name | class_no | +----------+----------+ | 陈六 | 201902 | +----------+----------+ 1 row in set (0.00 sec)
3.4.2 多表查询
多表查询即需要进行关联查询，本节不展开
mysql> select stu_name,c.class_name,c.location from student s, class c where s.class_no=c.class_no ; +----------+--------------+------------------+ | stu_name | class_name | location | +----------+--------------+------------------+ | 陈六 | 2019级02班 | 博学北楼B401 | | 杜甫 | 2019级02班 | 博学北楼B401 | +----------+--------------+------------------+ 2 rows in set (0.00 sec)
4. DCL语言
4.1 创建用户
/** 创建用户test1,且所有主机都可以访问 **/ create user test1@'%' identified by 'test1@123';
/** 创建用户test2,只能ip为192.168.24.128的主机能访问 **/
create user test2@'192.168.24.128' identified by 'test2@123';
/** 创建用户test3,只能ip为192.168.25.0网段的主机能访问 **/
create user test3@'192.168.25.%' identified by 'test3@123';
4.2 给用户授权
MySQL 的权限比较多,可以针对不同的用户设置相应的权限，生产环境建议按照最小化授权的原则赋权
/** 授予对testdb库的DML权限给test1 **/ mysql> grant select ,insert ,update,delete on testdb.* to test1@'%'; Query OK, 0 rows affected (0.00 sec) /** 授权所有权限给test2,且包含授权权限 **/ mysql> grant all on *.* to test2@'192.168.24.128' with grant option; Query OK, 0 rows affected (0.00 sec) /** 授权所有权限给test3,但不包含授权权限 **/ mysql> grant all on *.* to test3@'192.168.25.%'; Query OK, 0 rows affected (0.01 sec)
/** 刷新权限，此处纯属介绍此功能，只是在update表操作时才必须执行，其他情况一般不用执行即可生效 ** /
mysql> flush privileges;
注：
a） MySQL8.0之前可以通过grant的方式同时创建用户并授权，MySQL 8.0之后必须分开来处理，也建议创建用户及授权分开来处理。
例如
mysql> GRANT SELECT ON *.* TO t@'172.%' IDENTIFIED BY 'tt@123'; Query OK, 0 rows affected, 1 warning (0.01 sec)
b） 绑定的主机为%是 数据库所在的主机在不带ip的情况下是不能访问的,本机访问需开通localhost的权限，具体可参考历史博文 MySQL连接小结
4.3 用户及权限查看
/** 查看创建用户脚本 **/ mysql> show create user test2@'192.168.24.128'; +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | CREATE USER for test2@192.168.24.128 | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | CREATE USER 'test2'@'192.168.24.128' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$tv>#%npc?9~\9rlqFJoUYA11a8SAEcAFJ72fchoHYce1tJpSxaQ8zXa4' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) /** 查看授权信息 **/ mysql> show grants for test2@'192.168.24.128'; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for test2@192.168.24.128 | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `test2`@`192.168.24.128` WITH GRANT OPTION | | GRANT APPLICATION_PASSWORD_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO `test2`@`192.168.24.128` WITH GRANT OPTION | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
注： 本结果为MySQL 8.0的查看情况，MySQL 8.0之前的版本会有所差异，具体情况请自己测试。
4.4 回收权限
回收权限使用revoke关键字来处理，例如：
/** 回收test1用户的删除权限 **/ mysql> revoke delete on testdb.* from test1@'%'; Query OK, 0 rows affected (0.01 sec)
4.5 修改用户密码
/** 修改用户密码 **/ mysql> alter user test2@'192.168.24.128' identified by 'Test2@456'; Query OK, 0 rows affected (0.01 sec)
4.6 删除用户
删除操作 依旧由DROP来完成，例如：
/** 删除用户 test2@192.168.24.128 **/ mysql> drop user test2@192.168.24.128; Query OK, 0 rows affected (0.00 sec) /** 查看结果 ** / mysql> select user ,host from mysql.user; +------------------+--------------+ | user | host | +------------------+--------------+ | test1 | % | | test3 | 192.168.25.% | | mysql.infoschema | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +------------------+--------------+ 6 rows in set (0.00 sec)
注： 新增、修改、删除用户均可以用DML方式操作，操作后必须使用flush privileges进行刷新权限,此处不做介绍了。
因此主要的常用的DDL DML、DCL的基础操作就介绍了，高级操作（如关联、索引类、存储过程、函数等）后续将推出。
桃李不言，下自成蹊。
耿小厨已开通个人微信公众号，想进一步沟通或想了解其他文章的同学可以关注我
