一、完成将server和client端的mysql配置默认字符集为utf8mb4;
1、查看支持所有字符集
SHOW CHARACTER SET;
SHOW CHARSET;
2、查看支持所有排序规则
SHOW COLLATION;
#注意
utf8_general_ci不区分大小写
utf8_bin 区分大小写
3、查看当前使用的排序规则
[root ~]#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 ~]#vim /etc/my.cnf /*添加以下内容*/
5 [mysqld]
6 character-set-server=utf8mb4
5、设置mysql客户端默认的字符集
[root ~]# 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 ~]#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 ~]#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端配置