完成将server和client端的mysql配置默认字符集为utf8mb4、掌握如何获取SQL命令的帮助、给testdb.host表中添加多条数据、根据表扩展出几个语句，完成总结DDL, DML

大海绵啤酒肚

于 2023-06-19 17:06:14 发布

阅读量633

点赞数 2

文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/weixin_50471413/article/details/131271905

版权

一、完成将server和client端的mysql配置默认字符集为utf8mb4;

1、查看支持所有字符集

SHOW CHARACTER SET;
SHOW CHARSET;

2、查看支持所有排序规则

SHOW COLLATION;
#注意
utf8_general_ci不区分大小写
utf8_bin 区分大小写

3、查看当前使用的排序规则


[root@Rocky8 ~]#mysql -uroot -p
Enter password: 

mysql> show variables like 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8mb4_0900_ai_ci |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)

4、设置服务器默认的字符集

[root@Rocky8 ~]#vim /etc/my.cnf  /*添加以下内容*/

  5 [mysqld]
  6 character-set-server=utf8mb4

5、设置mysql客户端默认的字符集

[root@Rocky8 ~]# vim /etc/my.cnf
#针对mysql客户端
[mysql]
default-character-set=utf8mb4

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

实例：字符集和相关文件

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 |
| utf8mb3  | UTF-8 Unicode                   | utf8mb3_general_ci  |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

[root@Rocky8 ~]#ll /usr/share/mysql/charsets/
total 240
-rw-r--r-- 1 root root  5961 Dec 16 23:34 armscii8.xml
-rw-r--r-- 1 root root  5947 Dec 16 23:34 ascii.xml
-rw-r--r-- 1 root root  8676 Dec 16 23:34 cp1250.xml
-rw-r--r-- 1 root root  8762 Dec 16 23:34 cp1251.xml
-rw-r--r-- 1 root root  6010 Dec 16 23:34 cp1256.xml
-rw-r--r-- 1 root root  9343 Dec 16 23:34 cp1257.xml
-rw-r--r-- 1 root root  5947 Dec 16 23:34 cp850.xml
-rw-r--r-- 1 root root  5963 Dec 16 23:34 cp852.xml
-rw-r--r-- 1 root root  6054 Dec 16 23:34 cp866.xml
-rw-r--r-- 1 root root  6970 Dec 16 23:34 dec8.xml
-rw-r--r-- 1 root root  5957 Dec 16 23:34 geostd8.xml
-rw-r--r-- 1 root root  6169 Dec 16 23:34 greek.xml
-rw-r--r-- 1 root root  5952 Dec 16 23:34 hebrew.xml
-rw-r--r-- 1 root root  5943 Dec 16 23:34 hp8.xml
-rw-r--r-- 1 root root 19475 Dec 16 23:34 Index.xml
-rw-r--r-- 1 root root  5970 Dec 16 23:34 keybcs2.xml
-rw-r--r-- 1 root root  5951 Dec 16 23:34 koi8r.xml
-rw-r--r-- 1 root root  6973 Dec 16 23:34 koi8u.xml
-rw-r--r-- 1 root root 10251 Dec 16 23:34 latin1.xml
-rw-r--r-- 1 root root  7673 Dec 16 23:34 latin2.xml
-rw-r--r-- 1 root root  5950 Dec 16 23:34 latin5.xml
-rw-r--r-- 1 root root  7879 Dec 16 23:34 latin7.xml
-rw-r--r-- 1 root root  8488 Dec 16 23:34 macce.xml
-rw-r--r-- 1 root root  8499 Dec 16 23:34 macroman.xml
-rw-r--r-- 1 root root  1749 Dec 16 23:34 README
-rw-r--r-- 1 root root  6971 Dec 16 23:34 swe7.xml

6、查看当前字符集的使用情况

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     | utf8mb3                    |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

二、掌握如何获取SQL命令的帮助，基于帮助完成添加testdb库，字符集utf8, 排序集合utf8_bin

1、获取SQL帮助

mysql> help create database;
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_option] ...

create_option: [DEFAULT] {
    CHARACTER SET [=] charset_name  //字符集
  | COLLATE [=] collation_name   //排列集合
  | ENCRYPTION [=] {'Y' | 'N'}
}

CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.

URL: https://dev.mysql.com/doc/refman/8.0/en/create-database.html

2、创建testdb数据库并修改字符集

mysql> CREATE DATABASE IF NOT EXISTS testdb;  //创建testdb数据库
Query OK, 1 row affected (0.00 sec)

mysql> show databases;  //查看所有数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
| workpress          |
+--------------------+
6 rows in set (0.00 sec)

mysql> ALTER DATABASE testdb character set utf8;   //修改utf8字符集
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> ALTER DATABASE testdb collate='utf8_bin';  //修改排列集合
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 utf8mb3 COLLATE utf8mb3_bin */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

手动创建数据库帮助

CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME'
CHARACTER SET 'character set name'
COLLATE 'collate name';

（1）手动创建数据库并设置字符集

mysql> create database zabbix character set utf8 collate utf8_bin;
或mysql> create database test charset=utf8;  //此处简写
Query OK, 1 row affected (0.000 sec)

[root@centos8 ~]#cat /mysql/data/testdb/db.opt
default-character-set=utf8
default-collation=utf8_bin

三、总结mysql常见的数据类型

1、整数型

tinyint(m) 1个字节 范围(-128~127)
smallint(m) 2个字节 范围(-32768~32767)
mediumint(m) 3个字节 范围(-8388608~8388607)
int(m) 4个字节 范围(-2147483648~2147483647)
bigint(m) 8个字节 范围(+-9.22*10的18次方)
上述数据类型，如果加修饰符unsigned后，则最大值翻倍
如：tinyint unsigned的取值范围为(0~255)
int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，规定了MySQL的一些
交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，Int(1)和Int(20)是
相同的
BOOL，BOOLEAN：布尔型，是TINYINT(1)的同义词。zero值被视为假，非zero值视为真

2、浮点型(float和double)，近似值

float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位, 注意: 小数点不占用总个数
double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位, 注意: 小数点不占用总个数
设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以
实际为准，即6位

3、定点数

在数据库中存放的是精确值,存为十进制
格式 decimal(m,d) 表示 最多 m 位数字,其中 d 个小数,小数点不算在长度内
比如: DECIMAL(6,2) 总共能存6位数字，末尾2位是小数，字段最大值 9999.99 （小数点不算在长度内）
参数m<65 是总个数，d<30且 d<m 是小数位
MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。
例如: decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：其中，小数点前的9个数字用4个
字节，小数点后的9个数字用4个字节，小数点本身占1个字节
浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储。double占用
8个字节
因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal，例如存储
财务数据。但在数据量比较大的时候，可以考虑使用bigint代替decimal

4、字符串(char,varchar,text)

char(n) 固定长度，最多255个字符,注意不是字节
varchar(n) 可变长度，最多65535个字符
tinytext 可变长度，最多255个字符
text 可变长度，最多65535个字符
mediumtext 可变长度，最多2的24次方-1个字符
longtext 可变长度，最多2的32次方-1个字符
BINARY(M) 固定长度，可存二进制或字符，长度为0-M字节
VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节
内建类型：ENUM枚举, SET集合

char和varchar的比较：

在这里插入图片描述

1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉，所以char类型存储的字符
串末尾不能有空格，varchar不限于此
2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1
个字节（n< n>255)，所以varchar(4),存入3个字符将占用4个字节
3.char类型的字符串检索速度要比varchar类型的快

varchar 和 text：

1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n< n>255)，text
是实际字符数+2个字节。
2.text类型不能有默认值
3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text

面试题: VARCHAR(50) 能存放几个 UTF8 编码的汉字？

存放的汉字个数与版本相关。
mysql 4.0以下版本，varchar(50) 指的是 50 字节，如果存放 UTF8 格式编码的汉字时（每个汉字3字
节），只能存放16 个。
mysql 5.0以上版本，varchar(50) 指的是 50 字符，无论存放的是数字、字母还是 UTF8 编码的汉字，
都可以存放 50 个。

5、二进制数据BLOB

BLOB和text存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而Blob以二进制方式存储，
不分大小写
BLOB存储的数据只能整体读出
TEXT可以指定字符集，BLOB不用指定字符集

6、日期时间类型

date 日期 '2008-12-2'
time 时间 '12:25:36'
datetime 日期时间 '2008-12-2 22:06:44'
timestamp 自动存储记录修改时间
YEAR(2), YEAR(4)：年份
timestamp 此字段里的时间数据会随其他字段修改的时候自动刷新，这个数据类型的字段可以存放这
条记录最后被修改的时间

7、修饰符

**适用所有类型的修饰符：**
NULL 数据列可包含NULL值，默认值
NOT NULL 数据列不允许包含NULL值，相当于网站注册表中的 * 为必填选项
DEFAULT 默认值
PRIMARY KEY 主键，所有记录中此字段的值不能重复，且不能为NULL
UNIQUE KEY 唯一键，所有记录中此字段的值不能重复，但可以为NULL
CHARACTER SET name 指定一个字符集

**适用数值型的修饰符：**
AUTO_INCREMENT 自动递增，适用于整数类型, 必须作用于某个 key 的字段,比如primary key
UNSIGNED 无符号

范例：关于AUTO_INCREMENT

mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.00 sec)

# auto_increment_offset 定义初始值
# auto_increment_increment 定义步进

范例：

mysql> use testdb;
Database changed
mysql> create table t1(id int unsigned auto_increment primary key) auto_increment = 4294967294;
Query OK, 0 rows affected (0.02 sec)

mysql> desc t1;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int unsigned | NO   | PRI | NULL    | auto_increment |
+-------+--------------+------+-----+---------+----------------+
1 row in set (0.00 sec)

mysql> show table status from testdb like "t1" \G;
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 4294967294
    Create_time: 2023-06-15 23:17:34
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb3_bin
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

mysql> insert into t1 values(null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------------+
| id         |
+------------+
| 4294967294 |
+------------+
1 row in set (0.00 sec)

mysql> insert into t1 values(null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------------+
| id         |
+------------+
| 4294967294 |
| 4294967295 |
+------------+
2 rows in set (0.00 sec)

四、创建一个主机表host,放在testdb中，要求字段

1) 主键自增id 无符号, tinyint.
2) hostname可变字符长度256，可为空
3）ip 可变字符长度256，可为空。
4）账号，可变字符长度256，可为空。
5）密码，可变字符长度256，可为空。 6）创建时间，时间类型，非空。
7）更新时间，时间类型，默认当前时间。
8）区域，只能在华南，华北，华东，三个区域之一。
9）端口，无符号整数，可为空。
10）外网地址，可变字符长度256，可为空。
11）内网地址，可变字符长度256，可为空。

mysql> create table host(id tinyint unsigned auto_increment primary key,hostname VARCHAR(256) , ip VARCHAR(256),admin VARCHAR(256),password VARCHAR(256),date timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,area  ENUM('华南','华北','华东') NOT NULL ,port int unsigned ,outNET VARCHAR(256) , inNET VARCHAR(256) );
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| host             |
| t1               |
+------------------+
2 rows in set (0.00 sec)

mysql> desc host;
+----------+----------------------------------+------+-----+-------------------+-------------------+
| Field    | Type                             | Null | Key | Default           | Extra             |
+----------+----------------------------------+------+-----+-------------------+-------------------+
| id       | tinyint unsigned                 | NO   | PRI | NULL              | auto_increment    |
| hostname | varchar(256)                     | YES  |     | NULL              |                   |
| ip       | varchar(256)                     | YES  |     | NULL              |                   |
| admin    | varchar(256)                     | YES  |     | NULL              |                   |
| password | varchar(256)                     | YES  |     | NULL              |                   |
| date     | timestamp                        | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| area     | enum('华南','华北','华东')       | NO   |     | NULL              |                   |
| port     | int unsigned                     | YES  |     | NULL              |                   |
| outNET   | varchar(256)                     | YES  |     | NULL              |                   |
| inNET    | varchar(256)                     | YES  |     | NULL              |                   |
+----------+----------------------------------+------+-----+-------------------+-------------------+
10 rows in set (0.00 sec)

五、给testdb.host表中添加多条数据。

mysql> insert host(hostname,ip,admin,password,area) values('hang','192.168.188.10','root',123456,'华北' );
Query OK, 1 row affected (0.00 sec)

mysql> insert host(hostname,ip,admin,password,area,port,outNET,inNET) values('hang','192.168.188.10','root',123456,'华北' ,1521,'192.168.188.10','192.168.188.10');
Query OK, 1 row affected (0.01 sec)

mysql> insert host(hostname,ip,admin,password,area,port,outNET,inNET) values('zhang','192.168.188.11','root',123456,'华北' ,1521,'192.168.188.2','192.168.188.254');
Query OK, 1 row affected (0.00 sec)

mysql> select * from host;
+----+----------+----------------+-------+----------+---------------------+--------+------+----------------+-----------------+
| id | hostname | ip             | admin | password | date                | area   | port | outNET         | inNET           |
+----+----------+----------------+-------+----------+---------------------+--------+------+----------------+-----------------+
|  1 | zhao     | 192.168.188.81 | NULL  | NULL     | 2023-06-16 00:35:01 | 华南   | NULL | NULL           | NULL            |
|  2 | hang     | 192.168.188.10 | NULL  | NULL     | 2023-06-19 14:22:22 | 华北   | NULL | NULL           | NULL            |
|  3 | hang     | 192.168.188.10 | root  | 123456   | 2023-06-19 14:25:30 | 华北   | NULL | NULL           | NULL            |
|  4 | hang     | 192.168.188.10 | root  | 123456   | 2023-06-19 14:27:12 | 华北   | 1521 | 192.168.188.10 | 192.168.188.10  |
|  5 | zhang    | 192.168.188.11 | root  | 123456   | 2023-06-19 14:28:25 | 华北   | 1521 | 192.168.188.2  | 192.168.188.254 |
+----+----------+----------------+-------+----------+---------------------+--------+------+----------------+-----------------+
5 rows in set (0.00 sec)

六、根据表扩展出几个语句，完成总结DDL, DML的用法，并配上示例。

1、DDL

DDL: Data Defination Language 数据定义语言
CREATE，DROP，ALTER
创建表帮助：HELP CREATE TABLE

1、创建表：

mysql> create table work(id tinyint unsigned auto_increment primary key,name char(4) NOT NULL,gender enum('F','M') NOT NULL ,age tinyint UNSIGNED NOT NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> desc work
    -> ;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | tinyint unsigned | NO   | PRI | NULL    | auto_increment |
| name   | char(4)          | NO   |     | NULL    |                |
| gender | enum('F','M')    | NO   |     | NULL    |                |
| age    | tinyint unsigned | NO   |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

2、DROP表



帮助：
DROP TABLE [IF EXISTS] 'tbl_name';

mysql> show tables
    -> ;
+------------------+
| Tables_in_testdb |
+------------------+
| host             |
| t1               |
| work             |
+------------------+
3 rows in set (0.01 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| host             |
| work             |
+------------------+
2 rows in set (0.00 sec)

3、修改表ALTER



帮助：
#修改表名
ALTER TABLE students RENAME s1;
#添加字段
ALTER TABLE s1 ADD phone varchar(11) AFTER name;
#修改字段类型
ALTER TABLE s1 MODIFY phone int;
#修改字段名称和类型
ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);
#删除字段
ALTER TABLE s1 DROP COLUMN mobile;
#修改字符集
ALTER TABLE s1 character set utf8;
#修改数据类型和字符集
ALTER TABLE s1 change name name varchar(20) character set utf8;
#添加字段
ALTER TABLE students ADD gender ENUM('m','f');
alter table student modify is_del bool default false;
#修改字段名和类型
ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY; 
#删除字段
ALTER TABLE students DROP age;
#查看表结构
DESC students;
#新建表无主键，添加和删除主键
CREATE TABLE t1 SELECT * FROM students;
3.6 DML 语句
DML: INSERT, DELETE, UPDATE
3.6.1 INSERT 语句
功能：一次插入一行或多行数据
语法
简化写法：
范例: 全值插入
ALTER TABLE t1 add primary key (stuid);
ALTER TABLE t1 drop primary key ;
#添加外键
ALTER TABLE students add foreign key(TeacherID) references teachers(tid);
#删除外键
SHOW CREATE TABLE students #查看外键名
ALTER TABLE students drop foreign key <外键名>;

实例：
mysql> ALTER TABLE host ADD car ENUM('aodi','bmw');
Query OK, 0 rows affected (0.01 sec)

mysql> select id,hostname,car from host;
+----+----------+------+
| id | hostname | car  |
+----+----------+------+
|  1 | zhao     | NULL |
|  2 | hang     | NULL |
|  3 | hang     | NULL |
|  4 | hang     | NULL |
|  5 | zhang    | NULL |
+----+----------+------+
5 rows in set (0.00 sec)

2、DML

DML: Data Manipulation Language 数据操纵语言
INSERT，DELETE，UPDATE
软件开发：CRUD

1、INSERT插入

1、INSERT
mysql> insert student values(0,'ding',18,default);
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+------+------+--------+
| id | name | age  | gender |
+----+------+------+--------+
|  1 | dang |   18 | M      |
+----+------+------+--------+
1 row in set (0.00 sec)

#部分列插入
mysql> insert student(name,age)values('zhang',20);
Query OK, 1 row affected (0.01 sec)
mysql> insert student(id,name,age)values(default,'li',19);
Query OK, 1 row affected (0.00 sec)
mysql> insert student(id,name,gender)values(null,'zhao','F');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+-------+------+--------+
| id | name  | age  | gender |
+----+-------+------+--------+
|  1 | wang  |   18 | M      |
|  2 | zhang |   20 | M      |
|  3 | li    |   19 | M      |
|  4 | zhao  | NULL | F      |
+----+-------+------+--------+
4 rows in set (0.00 sec)

2、UPDATE 语句

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

注意：一定要有限制条件，否则将修改所有行的指定字段

**可利用MySQL选项避免此错误：**

mysql -U | --safe-updates| --i-am-a-dummy
[root@centos8 ~]#vim /etc/my.cnf
[mysql]
safe-updates

mysql> updata student set age = 19 where id = 4 ;  //必须加where限制条件
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;

|  3 | HANG | M      |   19 |

3、DELETE

mysql> delete from student where age >=23 ;  //删除age大于23的内容
Query OK, 4 rows affected (0.00 sec)
mysql> select * from student;

缩减表的大小


mysql> OPTIMIZE TABLE student;
+----------------+----------+----------+-------------------------------------------------------------------+
| Table          | Op       | Msg_type | Msg_text                                                          |
+----------------+----------+----------+-------------------------------------------------------------------+
| zabbix.student | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| zabbix.student | optimize | status   | OK                                                                |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.03 sec)

清除并缩减表的大小

mysql> TRUNCATE TABLE teacher;
Query OK, 0 rows affected (0.01 sec)

七、导入hellodb库，总结DQL, alias, where子句，gruop by, order by, limit, having使用示例。

1、导入hellodb库

[root@Rocky8 ~]#mysql -uroot -p < hellodb_innodb.sql 
Enter password: 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
| workpress          |
+--------------------+
7 rows in set (0.00 sec)

mysql> use hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.01 sec)

2、总结DQL

1、单表查询

mysql> select * from students where name = 'ma chao';
+-------+---------+-----+--------+---------+-----------+
| StuID | Name    | Age | Gender | ClassID | TeacherID |
+-------+---------+-----+--------+---------+-----------+
|    23 | Ma Chao |  23 | M      |       4 |      NULL |
+-------+---------+-----+--------+---------+-----------+
1 row in set (0.00 sec)

mysql> select * from students where name like '%s%';
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu  |  22 | M      |       2 |         3 |
|     2 | Shi Potian   |  22 | M      |       1 |         7 |
|     6 | Shi Qing     |  46 | M      |       5 |      NULL |
|    10 | Yue Lingshan |  19 | F      |       3 |      NULL |
|    14 | Lu Wushuang  |  17 | F      |       3 |      NULL |
|    25 | Sun Dasheng  | 100 | M      |    NULL |      NULL |
+-------+--------------+-----+--------+---------+-----------+
6 rows in set (0.00 sec)

mysql> select stuid,name 姓名,gender 性别 from students;
+-------+---------------+--------+
| stuid | 姓名          | 性别   |
+-------+---------------+--------+
|     1 | Shi Zhongyu   | M      |
|     2 | Shi Potian    | M      |
|     3 | Xie Yanke     | M      |
|     4 | Ding Dian     | M      |
|     5 | Yu Yutong     | M      |
|     6 | Shi Qing      | M      |

mysql> select * from students where Age <=20  and gender='F';

mysql> select * from students where classid in (1,2,3);

mysql> select * from students where age limit 3,5;

mysql> select * from students where age between 20 and 30;

mysql> select * from students where name ='ma chao' and age=''or '1'='1';

按照年龄排序order by 关键字


mysql> select * from students order by age;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)

倒序排序


mysql> select * from students order by age desc;

八、基于hellodb 库, 总结子查询，关联查询，交叉连接，内连接，左连接，右连接，完全连接，自连接。

1、多表查询

1、子查询

mysql> update teachers set age = (select max(age) from students) where tid=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng | 100 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

2、联合查询 union字段

mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng | 100 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
mysql> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)

mysql> select stuid id,name,age,gender from students union select * from teachers;
+----+---------------+-----+--------+
| id | name          | age | gender |
+----+---------------+-----+--------+
|  1 | Shi Zhongyu   |  22 | M      |
|  2 | Shi Potian    |  22 | M      |
|  3 | Xie Yanke     |  53 | M      |
|  4 | Ding Dian     |  32 | M      |
|  5 | Yu Yutong     |  26 | M      |
|  6 | Shi Qing      |  46 | M      |
|  7 | Xi Ren        |  19 | F      |
|  8 | Lin Daiyu     |  17 | F      |
|  9 | Ren Yingying  |  20 | F      |
| 10 | Yue Lingshan  |  19 | F      |
| 11 | Yuan Chengzhi |  23 | M      |
| 12 | Wen Qingqing  |  19 | F      |
| 13 | Tian Boguang  |  33 | M      |
| 14 | Lu Wushuang   |  17 | F      |
| 15 | Duan Yu       |  19 | M      |
| 16 | Xu Zhu        |  21 | M      |
| 17 | Lin Chong     |  25 | M      |
| 18 | Hua Rong      |  23 | M      |
| 19 | Xue Baochai   |  18 | F      |
| 20 | Diao Chan     |  19 | F      |
| 21 | Huang Yueying |  22 | F      |
| 22 | Xiao Qiao     |  20 | F      |
| 23 | Ma Chao       |  23 | M      |
| 24 | Xu Xian       |  27 | M      |
| 25 | Sun Dasheng   | 100 | M      |
|  1 | Song Jiang    |  45 | M      |
|  2 | Zhang Sanfeng | 100 | M      |
|  3 | Miejue Shitai |  77 | F      |
|  4 | Lin Chaoying  |  93 | F      |
+----+---------------+-----+--------+
29 rows in set (0.00 sec)

3、交叉连接

mysql> select count(*) from students cross join teachers;
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.01 sec)

4、内连接，重叠的项

mysql> select s.stuid,s.name,s.teacherid,t.tid,t.name from students s inner join teachers t on s.teacherid=t.tid;
+-------+-------------+-----------+-----+---------------+
| stuid | name        | teacherid | tid | name          |
+-------+-------------+-----------+-----+---------------+
|     1 | Shi Zhongyu |         3 |   3 | Miejue Shitai |
|     4 | Ding Dian   |         4 |   4 | Lin Chaoying  |
|     5 | Yu Yutong   |         1 |   1 | Song Jiang    |
+-------+-------------+-----------+-----+---------------+
3 rows in set (0.00 sec)

5、左连接

mysql> select * from students s inner join teachers t on s.teacherid=t.tid;
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name        | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |   4 | Lin Chaoying  |  93 | F      |
|     5 | Yu Yutong   |  26 | M      |       3 |         1 |   1 | Song Jiang    |  45 | M      |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
3 rows in set (0.00 sec)

mysql> select * from students s left outer teachers t on s.teacherid=t.tid;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'teachers t on s.teacherid=t.tid' at line 1
mysql> select * from students s left outer join teachers t on s.teacherid=t.tid;
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
| StuID | Name          | Age | Gender | ClassID | TeacherID | TID  | Name          | Age  | Gender |
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |    3 | Miejue Shitai |   77 | F      |
|     2 | Shi Potian    |  22 | M      |       1 |         7 | NULL | NULL          | NULL | NULL   |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 | NULL | NULL          | NULL | NULL   |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |    4 | Lin Chaoying  |   93 | F      |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |    1 | Song Jiang    |   45 | M      |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL | NULL | NULL          | NULL | NULL   |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL | NULL | NULL          | NULL | NULL   |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL | NULL | NULL          | NULL | NULL   |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL | NULL | NULL          | NULL | NULL   |
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
25 rows in set (0.00 sec)

6、右链接

mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng | 100 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

mysql> select * from students s right outer join teachers t on s.teacherid=t.tid;
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name        | Age  | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
|     5 | Yu Yutong   |   26 | M      |       3 |         1 |   1 | Song Jiang    |  45 | M      |
|  NULL | NULL        | NULL | NULL   |    NULL |      NULL |   2 | Zhang Sanfeng | 100 | M      |
|     1 | Shi Zhongyu |   22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
|     4 | Ding Dian   |   32 | M      |       4 |         4 |   4 | Lin Chaoying  |  93 | F      |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

7、完全外连接

mysql> select * from students s right outer join teachers t on s.teacherid=t.tid
    -> union
    -> select * from students s left outer join teachers t on s.teacherid=t.tid;
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
| StuID | Name          | Age  | Gender | ClassID | TeacherID | TID  | Name          | Age  | Gender |
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
|     5 | Yu Yutong     |   26 | M      |       3 |         1 |    1 | Song Jiang    |   45 | M      |
|  NULL | NULL          | NULL | NULL   |    NULL |      NULL |    2 | Zhang Sanfeng |  100 | M      |
|     1 | Shi Zhongyu   |   22 | M      |       2 |         3 |    3 | Miejue Shitai |   77 | F      |
|     4 | Ding Dian     |   32 | M      |       4 |         4 |    4 | Lin Chaoying  |   93 | F      |
|     2 | Shi Potian    |   22 | M      |       1 |         7 | NULL | NULL          | NULL | NULL   |
|     3 | Xie Yanke     |   53 | M      |       2 |        16 | NULL | NULL          | NULL | NULL   |
|     6 | Shi Qing      |   46 | M      |       5 |      NULL | NULL | NULL          | NULL | NULL   |
|     7 | Xi Ren        |   19 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
|     8 | Lin Daiyu     |   17 | F      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
|     9 | Ren Yingying  |   20 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    10 | Yue Lingshan  |   19 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
|    11 | Yuan Chengzhi |   23 | M      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    12 | Wen Qingqing  |   19 | F      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
|    13 | Tian Boguang  |   33 | M      |       2 |      NULL | NULL | NULL          | NULL | NULL   |
|    14 | Lu Wushuang   |   17 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
|    15 | Duan Yu       |   19 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
|    16 | Xu Zhu        |   21 | M      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
|    17 | Lin Chong     |   25 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
|    18 | Hua Rong      |   23 | M      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
|    19 | Xue Baochai   |   18 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    20 | Diao Chan     |   19 | F      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
|    21 | Huang Yueying |   22 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    22 | Xiao Qiao     |   20 | F      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
|    23 | Ma Chao       |   23 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
|    24 | Xu Xian       |   27 | M      |    NULL |      NULL | NULL | NULL          | NULL | NULL   |
|    25 | Sun Dasheng   |  100 | M      |    NULL |      NULL | NULL | NULL          | NULL | NULL   |
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
26 rows in set (0.00 sec)

8、自链接


MariaDB [hellodb]> select * from emp;
+------+----------+----------+
| id   | name     | leaderid |
+------+----------+----------+
|    1 | mage     |     NULL |
|    2 | zhangsir |        1 |
|    3 | wang     |        2 |
|    4 | zhang    |        3 |
+------+----------+----------+
4 rows in set (0.00 sec)
MariaDB [hellodb]> select   e.name,l.name   from emp as e inner join emp as l on
e.leaderid=l.id;
+----------+----------+
| name     | name     |
+----------+----------+
| zhangsir | mage     |
| wang     | zhangsir |
| zhang    | wang     |
+----------+----------+
3 rows in set (0.00 sec)
MariaDB [hellodb]> select   e.name,IFNULL(l.name,'无上级')   from emp as e left
join emp as l on e.leaderid=l.id;
+----------+----------+
| name     | name     |
+----------+----------+
| zhangsir | mage     |
| wang     | zhangsir |
| zhang    | wang     |
| mage     | NULL     |
+----------+----------+
4 rows in set (0.00 sec)
MariaDB [hellodb]> select e.name emp,IFNULL(l.name,'无上级') leader from emp as e 
left join emp as l on e.leaderid=l.id;
+----------+----------+
| emp      | leader   |
+----------+----------+
| zhangsir | mage     |
| wang     | zhangsir |
| zhang    | wang     |
| mage     | NULL     |
+----------+----------+
4 rows in set (0.000 sec)

范例：三表查询

mysql> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)


mysql> select * from courses;
+----------+----------------+
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        3 | Jinshe Jianfa  |
|        4 | Taiji Quan     |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
+----------+----------------+
7 rows in set (0.00 sec)

mysql> select * from scores;
+----+-------+----------+-------+
| ID | StuID | CourseID | Score |
+----+-------+----------+-------+
|  1 |     1 |        2 |    77 |
|  2 |     1 |        6 |    93 |
|  3 |     2 |        2 |    47 |
|  4 |     2 |        5 |    97 |
|  5 |     3 |        2 |    88 |
|  6 |     3 |        6 |    75 |
|  7 |     4 |        5 |    71 |
|  8 |     4 |        2 |    89 |
|  9 |     5 |        1 |    39 |
| 10 |     5 |        7 |    63 |
| 11 |     6 |        1 |    96 |
| 12 |     7 |        1 |    86 |
| 13 |     7 |        7 |    83 |
| 14 |     8 |        4 |    57 |
| 15 |     8 |        3 |    93 |
+----+-------+----------+-------+
15 rows in set (0.00 sec)


***合并三表并查询name、score、course***
mysql> select ss.name,sc.Score,co.Course from students  ss inner join scores sc on ss.StuID=sc.StuID inner join courses co on sc.CourseID=co.CourseID;
+-------------+-------+----------------+
| name        | Score | Course         |
+-------------+-------+----------------+
| Shi Zhongyu |    77 | Kuihua Baodian |
| Shi Zhongyu |    93 | Weituo Zhang   |
| Shi Potian  |    47 | Kuihua Baodian |
| Shi Potian  |    97 | Daiyu Zanghua  |
| Xie Yanke   |    88 | Kuihua Baodian |
| Xie Yanke   |    75 | Weituo Zhang   |
| Ding Dian   |    71 | Daiyu Zanghua  |
| Ding Dian   |    89 | Kuihua Baodian |
| Yu Yutong   |    39 | Hamo Gong      |
| Yu Yutong   |    63 | Dagou Bangfa   |
| Shi Qing    |    96 | Hamo Gong      |
| Xi Ren      |    86 | Hamo Gong      |
| Xi Ren      |    83 | Dagou Bangfa   |
| Lin Daiyu   |    57 | Taiji Quan     |
| Lin Daiyu   |    93 | Jinshe Jianfa  |
+-------------+-------+----------------+
15 rows in set (0.00 sec)

九、总结select语句处理顺序。


在 SQL 查询中，SELECT 语句处理的顺序如下：

FROM：指定要查询的表和视图。查询将从这些源中返回行。
WHERE：对数据进行过滤操作，取出符合条件的行。条件可以使用逻辑运算符和比较运算符组成。
GROUP BY：按照指定的列对结果进行分组操作。使用聚合函数（如 COUNT、SUM、AVG 等）可以在每个组内计算统计信息。
HAVING：过滤掉聚合结果集合中不符合条件的行。
SELECT：选择要返回的列。
ORDER BY：对结果进行排序，可以使用 ASC（升序）或 DESC（降序）。
LIMIT/OFFSET：限制返回的行数，可以使用 LIMIT 或 OFFSET 关键字来实现。

顺序：

FROM Clause --> WHERE Clause --> GROUP BY --> HAVING Clause -->SELECT --> ORDER 
BY --> LIMIT

十、搭建私人博客

[root@Rocky8 ~]#yum -y install php php-mysqlnd php-json //安装lamp环境
##下载workpress包
[root@Rocky8 ~]# wordpress-6.2.2-zh_CN.zip
[root@Rocky8 ~]#unzip wordpress-6.2.2-zh_CN.zip 
[root@Rocky8 ~]#cd wordpress/
[root@Rocky8 html]#mv /root/wordpress/* .

[root@Rocky8 html]#chown -R apache. /var/www/html/*

#设置dns
/etc/hosts
192.168.188.81 blog.dingbh.top

#创建数据库及服务账号
mysql> create database workpress;
Query OK, 1 row affected (0.00 sec)

mysql> show database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| workpress          |
+--------------------+
5 rows in set (0.01 sec)

mysql> create user workpress@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on workpress.* to workpress@'localhost';
Query OK, 0 rows affected (0.00 sec)

[root@Rocky8 html]#chown -R apache. /var/www/html/  //添加权限

随后进行web端配置

大海绵啤酒肚

关注

2
点赞
踩
3

收藏

觉得还不错? 一键收藏
打赏
0
评论
完成将server和client端的mysql配置默认字符集为utf8mb4、掌握如何获取SQL命令的帮助、给testdb.host表中添加多条数据、根据表扩展出几个语句，完成总结DDL, DML

一、完成将server和client端的mysql配置默认字符集为utf8mb4;1、查看支持所有字符集SHOW CHARACTER SET;SHOW CHARSET;2、查看支持所有排序规则SHOW COLLATION;#注意utf8_general_ci不区分大小写utf8_bin 区分大小写3、查看当前使用的排序规则[root@Rocky8 ~]#mysql -uroot -pEnter password: mysql> show variables like '
复制链接

扫一扫