SQL语言

SQL语言

SQL语言规范

• 不区分大小写
• sql语句可单行或者多行，默认;结尾
• 关键词不能跨行写或者多行写
• 子句位于独立行。

注释

#单行注释，
-- 注释内容
#多行注释
/*注释内容*/

数据库对象和命名

对象：数据库，表，索引，视图，用户，存储过程，函数，触发器，事件调度器
命名：

• 必须以子母开头，后续可以包括字母数字和三个特殊字符（#_$）
• 不能使用mysql的关键字

SQL语句分类

• DDL：数据定义语言
  CREATE,DROP,ALTER
• DML:数据操纵语言
  INSERT,DELETE,UPDATE
• DQL：数据查询语言
  SELECT
• DCL：数据控制语言
  GRANT,REVOKE

字符集和排序

字符集可以代表使用的范围，早期字符集为latin1，后来从8开始字符集改为了utf8mb4

查看支持的所有字符集

SHOW CHARACTER SET;

查看当前使用的字符集

#show variables like 'character%'

mysql> show variables like 'character%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8                           |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)

设置默认字符集

#服务器默认字符集
[ 14:05:24 root@CentOS7zhangbo ~]#vim /etc/my.cnf
[mysqld]
character-set-server=utf8mb4

#客户端默认字符集
[mysql]
default-character-set=utf8mb4

#所有客户端
[client]
default-character-set=utf8mb4

查看使用的排序规则

show variables like 'collation%';

管理数据库

创建数据库

格式：

• CREATE DATABASE|SCHEMA ‘DB_NAME’ CHARACTER SET ‘character is name’ COLLATE ‘collate name’

例子
创建一个数据库名称为zb1

#创建数据库
mysql> CREATE DATABASE zb1;

#查看数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| ZB123              |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zb1                |
+--------------------+
6 rows in set (0.00 sec)

创建一个指定字符集的数据库db2为latin7

#创建数据库
mysql> create database zb2 character set latin7;
Query OK, 1 row affected (0.00 sec)
#查看创建的时候使用的编码
mysql> show create database zb2;
+----------+---------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                   |
+----------+---------------------------------------------------------------------------------------------------+
| zb2      | CREATE DATABASE `zb2` /*!40100 DEFAULT CHARACTER SET latin7 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改数据库

格式

• ALTER DATABASE DB_NAME character set 字符集
  只能修改字符集

范例：
修改zb2的字符集为utf8mb4

mysql> ALTER DATABASE zb2 character set utf8mb4;
Query OK, 1 row affected (0.00 sec)

#修改zb2后的显示
mysql> show create database zb2
    -> ;
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                               |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| zb2      | CREATE DATABASE `zb2` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

删除数据库

格式

• DROP DATABASE|SCHEMA ‘db_name’

实例
删除数据库

mysql> DROP database zb2;
Query OK, 0 rows affected (0.01 sec)

查看数据库列表

格式

• SHOW DATABASES;

范例

#查看数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zb1                |
+--------------------+
5 rows in set (0.00 sec)

查看数据库创建的时候的格式

mysql> show create database zb1;
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                               |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| zb1      | CREATE DATABASE `zb1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

数据类型

mysql支持的数据类型
选择数据类型三大原则

• 1、更小的通常更好，尽量使用能够存储数据的最小是数据类型
• 2、简单越好，越简单越快
• 3、尽量避免为NULL，因为空列更难优化。

整数型

tinyint(m) 1个字节范围：-128-127
smallint(m) 2个字节范围：-32768-32767
mediumint(m) 3个字节范围：-8388608-8388607
int(m) 4个字节范围：-2147483648-2147483647
bigint(m) 8个字节范围：2^64

可以使用修饰符unsigned后，取得正数，比如tintint unsigned取值范围为（0-255）

浮点型

float(m,d):单精度浮点数8位精度，m总个数，d小数位
double(m,d)：双精度浮点数16位精度，m总数，d小数位

字符串

char(n) 固定长度，最多4个字节，255个字符
varchar(n) 可变的长度，最多65535个字符
tinttext 可变长度，最多255个字符
text 可变长度，最多65535个字符

char和varchar的区别

1、char指定的固定字符，不管有没有信息都占4个字节，varchar是变化的，会随着字符长度而变化
2、char和varchar如果输入字符都大于定于类型都会被切割，只显示定义的字节数
3、char的字符串检索速度比varchar要快的多

时间日期时间类型

date ：2009-12-12
time：12:12:12
datatime：‘2009-12-12 12:12:12’
timestamp：自动记录存储的最后一次时间，当其他字段更改会更新时间。

修饰符

所有类型的修饰符
NULL ：表示可以为空
NOT NULL：表示不能为空
DEFAULT：默认值
PRIMARY KEY ：主键，不能重复，不能为空，一个表只有一个
UNIQUE KEY：唯一键，不能重复，可以为空
character set name：指定字符集

适用数值型的修饰符
AUTO_INCREMENT ：自动递增
UNSIGEND：无符号

auto_increment = 数值 定义初始值
auto_increment_increment 定义步进

DDL语句

表的语句：create，intsert，alter，delete，

创建表

格式

• CREATE TABLE 表名

实例
创建一个学生表

(root@localhost) [test]> create table student(
    -> id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> name VARCHAR(10) NOT NULL,
    -> age tinyint UNSIGNED,
    -> gender ENUM('M','F') default 'M',
    -> birthday date
    -> );

查看表结构
desc 表明
(root@localhost) [test]> desc student;
+----------+---------------+------+-----+---------+----------------+
| Field    | Type          | Null | Key | Default | Extra          |
+----------+---------------+------+-----+---------+----------------+
| id       | int unsigned  | NO   | PRI | NULL    | auto_increment |
| name     | varchar(10)   | NO   |     | NULL    |                |
| age      | int unsigned  | YES  |     | NULL    |                |
| gender   | enum('M','F') | YES  |     | M       |                |
| birthday | date          | YES  |     | NULL    |                |
+----------+---------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)


写入一条数据
(root@localhost) [test]> insert into student values(NULL,'zhang',22,'M','1998-12-12');
(root@localhost) [test]> select * from student;
+----+-------+------+--------+------------+
| id | name  | age  | gender | birthday   |
+----+-------+------+--------+------------+
|  1 | zhang |   22 | M      | 1998-12-12 |
|  2 | zhang |   22 | M      | 1998-12-12 |
+----+-------+------+--------+------------+
2 rows in set (0.00 sec)

通过查询先存表的表结构创建，但是不复制数据

• create table name1 like name2

#复制另一个表的表结构
(root@localhost) [test]> create table teacher like student;

#查看两个表的表结构
(root@localhost) [test]> desc teacher;
+----------+---------------+------+-----+---------+----------------+
| Field    | Type          | Null | Key | Default | Extra          |
+----------+---------------+------+-----+---------+----------------+
| id       | int unsigned  | NO   | PRI | NULL    | auto_increment |
| name     | varchar(10)   | NO   |     | NULL    |                |
| age      | int unsigned  | YES  |     | NULL    |                |
| gender   | enum('M','F') | YES  |     | M       |                |
| birthday | date          | YES  |     | NULL    |                |
+----------+---------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

(root@localhost) [test]> desc student;
+----------+---------------+------+-----+---------+----------------+
| Field    | Type          | Null | Key | Default | Extra          |
+----------+---------------+------+-----+---------+----------------+
| id       | int unsigned  | NO   | PRI | NULL    | auto_increment |
| name     | varchar(10)   | NO   |     | NULL    |                |
| age      | int unsigned  | YES  |     | NULL    |                |
| gender   | enum('M','F') | YES  |     | M       |                |
| birthday | date          | YES  |     | NULL    |                |
+----------+---------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

复制表的结构和数据都给复制一份

• create table name select * from name2

#复制表和数据
(root@localhost) [test]> create table teacher select * from student;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0


查看表的数据
(root@localhost) [test]> select * from student;
+----+-------+------+--------+------------+
| id | name  | age  | gender | birthday   |
+----+-------+------+--------+------------+
|  1 | zhang |   22 | M      | 1998-12-12 |
|  2 | zhang |   22 | M      | 1998-12-12 |
+----+-------+------+--------+------------+
2 rows in set (0.00 sec)

(root@localhost) [test]> select * from teacher;
+----+-------+------+--------+------------+
| id | name  | age  | gender | birthday   |
+----+-------+------+--------+------------+
|  1 | zhang |   22 | M      | 1998-12-12 |
|  2 | zhang |   22 | M      | 1998-12-12 |
+----+-------+------+--------+------------+
2 rows in set (0.00 sec)

表查看

查看表：
格式

• show tables

范例：

(root@localhost) [test]> show tables
    -> ;
+----------------+
| Tables_in_test |
+----------------+
| student        |
| teacher        |
+----------------+
2 rows in set (0.00 sec)

查看表的创建命令：

• show create table tb_name

(root@localhost) [test]> show create table teacher;

查看表结构

• desc 表明

(root@localhost) [test]> desc teacher;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| id       | int unsigned  | NO   |     | 0       |       |
| name     | varchar(10)   | NO   |     | NULL    |       |
| age      | int unsigned  | YES  |     | NULL    |       |
| gender   | enum('M','F') | YES  |     | M       |       |
| birthday | date          | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

查看表的状态：

• show table status like ‘表名’

(root@localhost) [test]> show table status like 'student';

查看支持的engine类型

• show engines

(root@localhost) [test]> show engines;

修改和删除表

修改表

ALTER TABLE '表名' ADD|DROP|CHANGE|MODIFY 

#添加字段：ADD
#删除字段：DROP
#修改字段：alter（默认），change（字段名），modify(字段属性)

删除表

DROP TABLE 表名

范例

#更改表名
ALTER TABLE student RENAME STUDENT;
#在name后面加一个字段
ALTER TABLE STUDENT ADD phone varchar(11) AFTER name;
#更改为phone的属性为int
ALTER TABLE STUDENT MODIFY phone int;

DML语句

DML:INSERT,DELETE,UPDATE

INSERT语句

能够插入数据，一行或者多行
格式

INSERT INTO 表名（字段名） values (valu1，value2),(valu21,value22)

实例

#添加学生表数据
(root@localhost) [test]> insert into student values(NULL,'li',24,'F','1996-1-1');

Query OK, 1 row affected (0.00 sec)

把表2的数据批量导入到表1的表内
insert 表1 select * from 表2
表2的字段的数据类型和表1相同，且字段名可以不同，表2的字符数据范围小于等于表1

#把学生表导入到老师的表内，需要字段类型相同，但是名称可以不通过
(root@localhost) [test]> insert into teacher select * from student;

UPDATE语句

能够更新数据
格式

UPDATE 表名 set 更改内容 注意是否加限制条件where

实例

#更改一条数据
(root@localhost) [test]> update teacher set age=26 where name='li';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

(root@localhost) [test]> select * from teacher;
+----+-------+------+--------+------------+
| id | name  | age  | gender | birthday   |
+----+-------+------+--------+------------+
|  1 | zhang |   22 | M      | 1998-12-12 |
|  2 | zhang |   22 | M      | 1998-12-12 |
|  1 | zhang |   22 | M      | 1998-12-12 |
|  2 | zhang |   22 | M      | 1998-12-12 |
|  3 | li    |   26 | F      | 1996-01-01 |
+----+-------+------+--------+------------+
5 rows in set (0.00 sec)

• 注意可以加上safe-update避免全部更新。

可以写进配置文件中

vim /etc/my.cnf
[mysql]
safe-update

DELETE语句

删除表中的数据，不会缩减数据文件的大小
语法：

DELETE FROM 表名 where 条件

• 注意：一定有限制条件

可以清空表，保留表结构使用

TRUNCATE TABLE 表名

缩减表的大小

OPTIMIZE TABLE 表名

实例

删除一条数据
(root@localhost) [test]> delete from teacher where id=3;
Query OK, 0 rows affected (0.00 sec)

清空表

#清空表数据，且缩减文件
(root@localhost) [test]> truncate table teacher;

DQL语句

单表操作

字段别名

#把*改为别名
(root@localhost) [test]> select id as  学生ID,name as 学生姓名,age as 年龄,gender as 性别,birthday 生日 from student;
+----------+--------------+--------+--------+------------+
| 学生ID   | 学生姓名     | 年龄   | 性别   | 生日       |
+----------+--------------+--------+--------+------------+
|        1 | zhang        |     22 | M      | 1998-12-12 |
|        2 | zhang        |     22 | M      | 1998-12-12 |
|        3 | li           |     24 | F      | 1996-01-01 |
+----------+--------------+--------+--------+------------+
3 rows in set (0.00 sec)

常见的查询：

#id小于3的
select * from student where id<3;
#性别为M的
select * from student where gender='M';
#性别为空的
select * from student where gender IS NULL;
#性别非空
select * from student where gender IS NOT NULL;
#id大于等于2且小于4
select * from student where id >=2 and id<=4;
#id小于2或者大于4
select * from student where id >=2 or id<=4;
#姓名以t开头的
select * from student where name LIKE 't%';
#匹配正则包含lo
select * from student where name RLIKE '.*[o].*';
#检查classid为1，3，5的
select * from student where classid in (1,3,5);
#不是1，3，5的
select * from student where classid not in (1,3,5)