MYSQL数据库(一)
centos7实现openvpn
yum -y install openvpn easy-rsa
#准备相关配置文件
cp /usr/share/doc/openvpn-2.4.12/sample/sample-config-files/server.conf /etc/openvpn/
cp -r /usr/share/easy-rsa/ /etc/openvpn/easy-rsa-server
cp /usr/share/doc/easy-rsa-3.0.8/vars.example /etc/openvpn/easy-rsa-server/3/vars
cd /etc/openvpn/easy-rsa-server/3/
#初始化PKI生成PKI相关目录和文件
./easyrsa init-pki
#创建CA机构
./easyrsa build-ca nopass
#创建服务端证书申请
./easyrsa gen-req server nopass
#签发服务端证书
./easyrsa sign server server
#创建 Diffie-Hellman 密钥
./easyrsa gen-dh
#准备客户端文件
cp -r /usr/share/easy-rsa/ /etc/openvpn/easy-rsa-client
cp /usr/share/doc/easy-rsa-3.0.8/vars.example /etc/openvpn/easy-rsa-client/3/vars
cd /etc/openvpn/easy-rsa-client/3/
#生成证书申请所需目录pki和文件
./easyrsa init-pki
#创建客户端证书申请
./easyrsa gen-req chenmeng nopass
#签发客户端证书
cd /etc/openvpn/easy-rsa-server/3/
./easyrsa import-req /etc/openvpn/easy-rsa-client/3/pki/reqs/chenmeng.req chenmeng
./easyrsa sign client chenmeng <<EOF
yes
EOF
#将CA和服务器证书相关文件复制到服务器相应的目录
mkdir /etc/openvpn/certs
cp /etc/openvpn/easy-rsa-server/3/pki/ca.crt /etc/openvpn/certs/
cp /etc/openvpn/easy-rsa-server/3/pki/issued/server.crt /etc/openvpn/certs/
cp /etc/openvpn/easy-rsa-server/3/pki/private/server.key /etc/openvpn/certs/
cp /etc/openvpn/easy-rsa-server/3/pki/dh.pem /etc/openvpn/certs/
find /etc/openvpn/ \( -name "chenmeng.key" -o -name "chenmeng.crt" -o -name "ca.crt" \) -exec cp {} /etc/openvpn/client/chenmeng/ \;
创建账户，日志文件
[root@centos7 ~]#getent passwd openvpn
openvpn:x:993:990:OpenVPN:/etc/openvpn:/sbin/nologin
[root@centos7 ~]#mkdir /var/log/openvpn
[root@centos7 ~]#chown openvpn.openvpn /var/log/openvpn
开启转发，添加防火墙
[root@centos7 ~]# echo net.ipv4.ip_forward = 1 >> /etc/sysctl.conf
[root@centos7 ~]# sysctl -p
[root@centos7 ~]# echo 'iptables -t nat -A POSTROUTING -s 10.8.0.0/24 -j MASQUERADE' >> /etc/rc.d/rc.local
[root@centos7 ~]# chmod +x /etc/rc.d/rc.local
[root@centos7 ~]# /etc/rc.d/rc.local
cat > /etc/openvpn/server.conf <<EOF
port 1194
proto tcp
dev tun
ca /etc/openvpn/certs/ca.crt
cert /etc/openvpn/certs/server.crt
key /etc/openvpn/certs/server.key # This file should be kept secret
dh /etc/openvpn/certs/dh.pem
server 10.8.0.0 255.255.255.0 #此处为网段
push "route 172.30.0.0 255.255.255.0" #此处为内网网段
keepalive 10 120
cipher AES-256-CBC
compress lz4-v2
push "compress lz4-v2"
max-clients 2048
user openvpn
group openvpn
status /var/log/openvpn/openvpn-status.log
log-append /var/log/openvpn/openvpn.log
verb 3
mute 20
EOF
systemctl enable --now openvpn@server.service #centos8上无service文件，需自建
MYSQL数据库
RDBMS 关系型数据库
关系Relational ：关系就是二维表，其中：表中的行、列次序并不重要
行row：表中的每一行，又称为一条记录record
列column：表中的每一列，称为属性，字段，域field
主键Primary key：PK ,一个或多个字段的组合, 用于惟一确定一个记录的字段，一张表只有一个主 键, 主键字段不能为空NULL
唯一键Unique key: 一个或多个字段的组合,用于惟一确定一个记录的字段,一张表可以有多个UK,而 且UK字段可以为NULL
域domain：属性的取值范围，如，性别只能是'男'和'女'两个值，人类的年龄只能0-150
数据库的三个范式
第一范式：1NF
无重复的列，每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有 多个值或者不能有重复的属性，确保每一列的原子性。除去同类型的字段，就是无重复的列 说明：第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据 库
第二范式：2NF
第二范式必须先满足第一范式，属性完全依赖于主键，要求表中的每个行必须可以被唯一地区分，通常 为表加上每行的唯一标识主键PK，非PK的字段需要与整个PK有直接相关性,即非PK的字段不能依赖于部 分主键
第三范式：3NF
满足第三范式必须先满足第二范式属性，非主键属性不依赖于其它非主键属性。第三范式要求一个数据 表中不包含已在其它表中已包含的非主关键字信息，非PK的字段间不能有从属关系
数据约束
约束：constraint，表中的数据在数据类型限定的基础上额外要遵守的限制 常见约束如下: 非空not null:此字段不允许填写空值.
主键primary key ：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行；必须提供 数据，即NOT NULL，一个表只能有一个
惟一键unique：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行；允许为 NULL，一个表可以存在多个
默认 default：当不填写字段对应的值会使用默认值﹐如果填写时以填写为准.
外键foreign key：一个表中的某字段可填入的数据取决于另一个表的主键或唯一键已有的数据
检查：字段值在一定范围内,如年龄在0到150之间
MYSQL数据库安装
1.yum安装
yum -y install mysql-server
systemctl enable --now mysqld
加固脚本，修改密码
mysql_secure_installation 执行脚本对数据库进行简单加固
修改密码：两种方式
alter user root@'localhost' identified by 'Cm@264539';
mysqladmin -uroot -p'Cm@264539' password 'Magedu@2021'
[root@localhost ~]# mysql_secure_installation
Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?
Press y|Y for Yes, any other key for No: y
There are three levels of password validation policy:
LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
Please set the password for root here.
New password:
Re-enter new password:
Estimated strength of the password: 25
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
... Failed! Error: Your password does not satisfy the current policy requirements
New password:
Re-enter new password:
Estimated strength of the password: 50
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
All done!
[root@localhost ~]# mysql -uroot -pcm123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
2.二进制安装
安装依赖包，解压缩
yum -y install libaio numactl-libs
tar -xvf mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
ln -s /usr/local/mysql-5.7.33-linux-glibc2.12-x86_64 /usr/local/mysql
添加mysql用户，修改权限
groupadd mysql
useradd -r -g mysql -s /sbin/nologin mysql
chown -R root:root /usr/local/mysql
修改PATH
echo "PATH=/usr/local/mysql/bin:$PATH" >/etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh
创建配置文件
cp /etc/my.cnf{,.bak}
cat >/etc/my.cnf <<EOF
[mysqld]
server-id=1
log-bin
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
EOF
初始化
mysqld --initialize --user=mysql --datadir=/data/mysql
awk '/password/{print $NF}' /data/mysql/mysql.log
生成空密码
mysqld --initialize-insecure --user=mysql --datadir=/data/mysql
创建启动脚本
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
service mysqld start
修改口令
#修改前面生成的随机密码为指定密码 mysqladmin -uroot -p'LufavlMka6,!' password magedu
#修改前面生成的空密码为指定密码 mysqladmin -uroot password magedu
一键安装二进制mysql
#!/bin/bash
rpm -q libaio || yum -y install libaio numactl-libs
[ -d /usr/local/mysql-5.7.33-linux-glibc2.12-x86_64 ] || ( tar -xvf mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ ;ln -s /usr/local/mysql-5.7.33-linux-glibc2.12-x86_64 /usr/local/mysql )
id mysql || (groupadd mysql;useradd -r -g mysql -s /sbin/nologin mysql)
chown -R root:root /usr/local/mysql
[ -f /etc/profile.d/mysql.sh ] || echo "PATH=/usr/local/mysql/bin:$PATH" >/etc/profile.d/mysql.sh
source /etc/profile.d/mysql.sh
#echo "PATH=/usr/local/mysql/bin:$PATH" >/etc/profile.d/mysql.sh
#. /etc/profile.d/mysql.sh
cp /etc/my.cnf{,.bak}
cat > /etc/my.cnf <<EOF
[mysqld]
server-id=1
log-bin
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
EOF
mysqld --initialize --user=mysql --datadir=/data/mysql
if [ -d /etc/init.d/mysqld ]
then
chkconfig --add mysqld
service mysqld start
else
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
service mysqld start
fi
PASSWD=`awk '/password/{print $NF}' /data/mysql/mysql.log`
mysqladmin -uroot -p"${PASSWD}" password magedu
二进制安装mariadb10.4
yum install -y libaio libaio-devel libedit ncurses-compat-libs -y
groupadd mysql
useradd -r -g mysql -s /sbin/nologin mysql
tar -xvf mariadb-10.4.0-linux-x86_64.tar.gz -C /usr/local/
ln -s /usr/local/mariadb-10.4.0-linux-x86_64 /usr/local/mysql
chown -R root:root /usr/local/mysql/
cd /usr/local/mysql
echo "PATH=/usr/local/mysql/bin:$PATH" > /etc/profile.d/mysqld.sh
. /etc/profile.d/mysqld.sh
cat > /etc/my.cnf <<EOF
[mysqld]
server-id=1
log-bin
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
EOF
. scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
systemctl daemon-reload
systemctl start mysqld
[root@localhost mysql]# mysql -uroot
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.4.0-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> exit
3.编译安装
安装依赖
yum -y install gcc gcc-c++ cmake bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel ncurses-devel gnutls-devel libxml2-devel openssl-devel libevent-devel libaio-devel perl-Data-Dumper
解压缩。创建用户
tar xvf mysql-5.6.51.tar.gz -C /usr/local/src
mkdir /data/mysql
useradd -r -s /sbin/nologin -d /data/mysql mysql
chown mysql.mysql /data/mysql
编译安装
cd mysql-5.6.51/
cmake . -DCMAKE_INSTALL_PREFIX=/apps/mysql -DMYSQL_DATADIR=/data/mysql/ -DSYSCONFDIR=/etc/ -DMYSQL_USER=mysql -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DWITHOUT_MROONGA_STORAGE_ENGINE=1 -DWITH_DEBUG=0 -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_ZLIB=system -DWITH_LIBWRAP=0 -DENABLED_LOCAL_INFILE=1 -DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci
make -j 4 && make install
提示：如果出错，执行rm -f CMakeCache.tx
修改PATH
echo 'PATH=/apps/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh
初始化
cd /apps/mysql/
scripts/mysql_install_db --datadir=/data/mysql/ --user=mysql
准备配置文件
cp -b /apps/mysql/support-files/my-default.cnf /etc/my.cnf
创建启动脚本
cp /apps/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
#service mysqld start
systemctl start mysqld
安全加固
mysql_secure_installation
sql语言
关系型数据库的常见组件
数据库：database
表：table，行：row 列：column
索引：index
视图：view
存储过程：procedure
存储函数：function
触发器：trigger
事件调度器：event scheduler，任务计划
用户：user
权限：privilege
SQL 语言规范
在数据库系统中，SQL 语句不区分大小写，建议用大写
SQL语句可单行或多行书写，默认以 " ; " 结尾
关键词不能跨多行或简写
用空格和TAB 缩进来提高语句的可读性
子句通常位于独立行，便于编辑，提高可读性
SQL语句分类
DDL: Data Defination Language 数据定义语言
CREATE，DROP，ALTER
DML: Data Manipulation Language 数据操纵语言
INSERT，DELETE，UPDATE
软件开发：CRUD
DQL：Data Query Language 数据查询语言
SELECT
DCL：Data Control Language 数据控制语言
GRANT，REVOKE
TCL：Transaction Control Language 事务控制语言
COMMIT，ROLLBACK，SAVEPOINT
数据库管理
查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
mysql> mysql> show create database db1;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
创建数据库
CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME'
CHARACTER SET 'character set name'
COLLATE 'collate name';
mysql> create database db1 character set utf8;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
修改数据库
ALTER DATABASE DB_NAME character set utf8;
删除数据库
DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';
mysql> drop database db1;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
DDL语句
DDL语句主要对表进行操作，包括查看，创建，修改和删除等
表创建
1 创建表
CREATE TABLE
获取帮助：
HELP CREATE TABLE
创建表的方法
(1) 直接创建
CREATE TABLE [IF NOT EXISTS] 'tbl_name' (col1 type1 修饰符, col2 type2 修饰符, ...)
\#字段信息
col type1
PRIMARY KEY(col1,...)
INDEX(col1, ...)
UNIQUE KEY(col1, ...)
\#表选项：
ENGINE [=] engine_name
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
注意：
Storage Engine是指表类型，也即在表创建时指明其使用的存储引擎
同一库中不同表可以使用不同的存储引擎
同一个库中表建议要使用同一种存储引擎类型
范例：创建表
CREATE TABLE student (
id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
age tinyint UNSIGNED,
\#height DECIMAL(5,2),
gender ENUM('M','F') default 'M'
)ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
\#id字段以10初始值
复合主键
CREATE TABLE employee (id int UNSIGNED NOT NULL ,name VARCHAR(20) NOT NULL,age
tinyint UNSIGNED,PRIMARY KEY(id,name));
通过查询现存表创建；新表会被直接插入查询而来的数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options]
[partition_options] select_statement
通过复制现存的表的表结构创建，但不复制数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE
old_tbl_name) }
列
1.创建表
mysql> create table tb1 ( id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(20) NOT NULL,age TINYINT UNSIGNED,GENDER ENUM("M","F") default 'm');
2.通过查询创建
mysql> create table tb2 select name,age from db1.tb1;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
通过复制表结构创建
mysql> create table tb3 like tb1;
Query OK, 0 rows affected (0.01 sec)
表查看
查看表：
SHOW TABLES [FROM db_name]
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| tb1 |
| tb2 |
| tb3 |
+---------------+
3 rows in set (0.00 sec)
查看表创建命令：
SHOW CREATE TABLE tbl_name
mysql> show create table tb1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb1 | CREATE TABLE `tb1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`age` tinyint(3) unsigned DEFAULT NULL,
`GENDER` enum('M','F') DEFAULT 'M',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
查看表结构：
DESC [db_name.]tb_name
SHOW COLUMNS FROM [db_name.]tb_name
mysql> desc tb1;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| GENDER | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec
mysql> show table status like 'tb1'\G
*************************** 1. row ***************************
Name: tb1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 1
Create_time: 2022-06-27 16:41:46
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
查看数据库内所有表信息
mysql> show table status from db1\G
查看支持的engine类型
SHOW ENGINES;
表修改和删除
修改表
ALTER TABLE 'tbl_name'
#字段：
#添加字段：add
ADD col1 data_type [FIRST|AFTER col_name]
#删除字段：drop
#修改字段：
alter（默认值）, change（字段名）, modify（字段属性）
查看修改表帮助
Help ALTER TABLE
删除表
DROP TABLE [IF EXISTS] 'tbl_name';
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| table1 |
| tb2 |
| tb3 |
+---------------+
3 rows in set (0.00 sec)
mysql> drop tables tb3;
Query OK, 0 rows affected (0.09 sec)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| table1 |
| tb2 |
+---------------+
2 rows in set (0.00 sec)
#修改表名
mysql> alter table table1 rename tb1;
Query OK, 0 rows affected (0.31 sec)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| tb1 |
| tb2 |
+---------------+
2 rows in set (0.00 sec
#添加字段
mysql> desc tb1;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| GENDER | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> alter table tb1 add phone varchar(20) after GENDER;
Query OK, 0 rows affected (0.31 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tb1;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| GENDER | enum('M','F') | YES | | M | |
| phone | varchar(20) | YES | | NULL | |
+--------+---------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
#修改字段类型
mysql> alter table tb1 modify phone int ;
Query OK, 0 rows affected (0.31 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tb1;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| GENDER | enum('M','F') | YES | | M | |
| phone | int(11) | YES | | NULL | |
+--------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
#修改字段名称和类型
ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);
mysql> alter table tb1 change column phone mobile char(11);
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tb1;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| GENDER | enum('M','F') | YES | | M | |
| mobile | char(11) | YES | | NULL | |
+--------+---------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
#删除字段
mysql> alter table tb1 drop column mobile;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tb1;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| GENDER | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
#新建表无主键，添加和删除主键
CREATE TABLE t1 SELECT * FROM students;
ALTER TABLE t1 add primary key (stuid);
ALTER TABLE t1 drop primary key ;
mysql> desc students;
+-----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| StuID | int(10) unsigned | NO | | 0 | |
| Name | varchar(50) | NO | | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
| Gender | enum('F','M') | NO | | NULL | |
| ClassID | tinyint(3) unsigned | YES | | NULL | |
| TeacherID | int(10) unsigned | YES | | NULL | |
+-----------+---------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> alter table students add primary key (stuid);
Query OK, 0 rows affected (0.43 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+-----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| StuID | int(10) unsigned | NO | PRI | 0 | |
| Name | varchar(50) | NO | | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
| Gender | enum('F','M') | NO | | NULL | |
| ClassID | tinyint(3) unsigned | YES | | NULL | |
| TeacherID | int(10) unsigned | YES | | NULL | |
+-----------+---------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> alter table students drop primary key;
Query OK, 25 rows affected (0.34 sec)
Records: 25 Duplicates: 0 Warnings: 0
mysql> desc students;
+-----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| StuID | int(10) unsigned | NO | | 0 | |
| Name | varchar(50) | NO | | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
| Gender | enum('F','M') | NO | | NULL | |
| ClassID | tinyint(3) unsigned | YES | | NULL | |
| TeacherID | int(10) unsigned | YES | | NULL | |
+-----------+---------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
#添加外键
ALTER TABLE students add foreign key(TeacherID) references teachers(tid);
#删除外键
SHOW CREATE TABLE students #查看外键名
ALTER TABLE students drop foreign key <外键名>;
DML语句
数据插入
mysql> insert into tb1 values(1,"zhangsan",20,default);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb1;
+----+----------+------+--------+
| id | name | age | GENDER |
+----+----------+------+--------+
| 1 | zhangsan | 20 | M |
+----+----------+------+--------+
1 row in set (0.00 sec)
连续插入
insert into table_name values(),(),();
注：插入数据需与字段匹配
也可按字段插入
insert 表(字段,字段，，)values();
数据修改
修改数据是一定要有限制，否则会修改整个表
可利用mysql 选项避免此错误：
mysql -U | --safe-updates| --i-am-a-dummy
[root@centos8 ~]#vim /etc/my.cnf
[mysql]
safe-updates
mysql> update tb1 set name="lisi" where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tb1;
+----+------+------+--------+
| id | name | age | GENDER |
+----+------+------+--------+
| 1 | lisi | 20 | M |
+----+------+------+--------+
1 row in set (0.00 sec)
数据删除
mysql> select * from tb1;
+----+----------+------+--------+
| id | name | age | GENDER |
+----+----------+------+--------+
| 1 | zhangsan | 20 | M |
+----+----------+------+--------+
1 row in set (0.00 sec)
mysql> delete from tb1 where id=1;
Query OK, 1 row affected (0.09 sec)
mysql> select * from tb1;
Empty set (0.00 sec)
数据删除是一定要有限制，否则会删除整个表
DQL语句
数据查询
1.单表查询
字段显示可以使用别名：
col1 AS alias1, col2 AS alias2, ...
WHERE子句：指明过滤条件以实现"选择"的功能：
过滤条件：布尔型表达式
算术操作符：+, -, *, /, %
比较操作符：=,<=>（相等或都为空）, <>, !=(非标准SQL), >, >=, <, <=
范例查询: BETWEEN min_num AND max_num
不连续的查询: IN (element1, element2, ...)
空查询: IS NULL, IS NOT NULL
DISTINCT 去除重复行，范例：SELECT DISTINCT gender FROM students;
模糊查询: LIKE 使用 % 表示任意长度的任意字符 _ 表示任意单个字符
逻辑操作符：NOT，AND，OR，XOR
GROUP BY：根据指定的条件把查询结果进行"分组"以用于做"聚合"运算
常见聚合函数： count(), sum(), max(), min(), avg(),注意:聚合函数不对null统计
HAVING: 对分组聚合运算后的结果指定过滤条件
一旦分组 group by ，select语句后只跟分组的字段，聚合函数
ORDER BY: 根据指定的字段对查询结果进行排序
升序：ASC
降序：DESC
LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制,跳过offset,显示row_count
行,offset默为值为0
对查询结果中的数据请求施加"锁"
FOR UPDATE: 写锁，独占或排它锁，只有一个读和写操作
LOCK IN SHARE MODE: 读锁，共享锁，同时多个读操作
字段别名：col1 AS alias1, col2 AS alias2, ...
mysql> select stuid ID,name as 姓名,age 年龄 from students order by age;
+----+---------------+--------+
| ID | 姓名 | 年龄 |
+----+---------------+--------+
| 8 | Lin Daiyu | 17 |
| 14 | Lu Wushuang | 17 |
| 19 | Xue Baochai | 18 |
| 12 | Wen Qingqing | 19 |
| 10 | Yue Lingshan | 19 |
| 7 | Xi Ren | 19 |
| 15 | Duan Yu | 19 |
| 20 | Diao Chan | 19 |
| 9 | Ren Yingying | 20 |
| 22 | Xiao Qiao | 20 |
| 16 | Xu Zhu | 21 |
| 1 | Shi Zhongyu | 22 |
| 21 | Huang Yueying | 22 |
| 2 | Shi Potian | 22 |
| 23 | Ma Chao | 23 |
| 18 | Hua Rong | 23 |
| 11 | Yuan Chengzhi | 23 |
| 17 | Lin Chong | 25 |
| 5 | Yu Yutong | 26 |
| 24 | Xu Xian | 27 |
| 4 | Ding Dian | 32 |
| 13 | Tian Boguang | 33 |
| 6 | Shi Qing | 46 |
| 3 | Xie Yanke | 53 |
| 25 | Sun Dasheng | 100 |
+----+---------------+--------+
25 rows in set (0.00 sec)
简单查询
比较操作，=,<=>（相等或都为空）, <>, !=(非标准SQL), >, >=, <, <=
mysql> select * from students where stuid=5;
+-------+-----------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-----------+-----+--------+---------+-----------+
| 5 | Yu Yutong | 26 | M | 3 | 1 |
+-------+-----------+-----+--------+---------+-----------+
1 row in set (0.01 sec)
范围查询：BETWEEN min_num AND max_num
mysql> select * from students where stuid between 2 and 4;
+-------+------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+------------+-----+--------+---------+-----------+
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
+-------+------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
不连续查询：in，not in
mysql> select * from students where stuid in (1,3,5);
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
去重查询
mysql> select distinct classid from students ;
+---------+
| classid |
+---------+
| 2 |
| 1 |
| 4 |
| 3 |
| 5 |
| 7 |
| 6 |
| NULL |
+---------+
8 rows in set (0.00 sec)
模糊查询: LIKE 使用 % 表示任意长度的任意字符 _ 表示任意单个字符
mysql> select * from students where name like "L%";
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
空查询： NULL，NOT NULL
mysql> select * from students where classid is NULL;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+-------------+-----+--------+---------+-----------+
2 rows in set (0.00 sec)
逻辑操作符：NOT，AND，OR，XOR
mysql> select * from students where gender="M" and classid is NULL;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+-------------+-----+--------+---------+-----------+
2 rows in set (0.00 sec)
mysql> select * from students where gender="F" or age < 18;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 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 |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | 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 |
+-------+---------------+-----+--------+---------+-----------+
10 rows in set (0.00 sec)
常见聚合函数： count(), sum(), max(), min(), avg(),注意:聚合函数不对null统计
mysql> select avg(age),max(age),count(*) from students where gender="M";
+----------+----------+----------+
| avg(age) | max(age) | count(*) |
+----------+----------+----------+
| 33.0000 | 100 | 15 |
+----------+----------+----------+
1 row in set (0.00 sec)
GROUP BY：根据指定的条件把查询结果进行"分组"以用于做"聚合"运算
mysql> select classid,avg(age),count(*) from students group by classid;
+---------+----------+----------+
| classid | avg(age) | count(*) |
+---------+----------+----------+
| NULL | 63.5000 | 2 |
| 1 | 20.5000 | 4 |
| 2 | 36.0000 | 3 |
| 3 | 20.2500 | 4 |
| 4 | 24.7500 | 4 |
| 5 | 46.0000 | 1 |
| 6 | 20.7500 | 4 |
| 7 | 19.6667 | 3 |
+---------+----------+----------+
8 rows in set (0.00 sec)
group_concat函数实现分组信息的集合
mysql> select gender,group_concat(name) from students group by gender;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| gender | group_concat(name) |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| F | Lin Daiyu,Yue Lingshan,Ren Yingying,Wen Qingqing,Xi Ren,Diao Chan,Huang Yueying,Xiao Qiao,Lu Wushuang,Xue Baochai |
| M | Hua Rong,Shi Zhongyu,Ma Chao,Xu Xian,Lin Chong,Xu Zhu,Duan Yu,Tian Boguang,Yuan Chengzhi,Shi Qing,Yu Yutong,Ding Dian,Xie Yanke,Shi Potian,Sun Dasheng |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
with rollup 分组后聚合函数统计后再做汇总
mysql> select gender,count(*) from students group by gender with rollup;
+--------+----------+
| gender | count(*) |
+--------+----------+
| F | 10 |
| M | 15 |
| NULL | 25 |
+--------+----------+
3 rows in set (0.00 sec)
HAVING: 对分组聚合运算后的结果指定过滤条件
mysql> select gender,avg(age),count(*) from students group by gender having gender="M";
+--------+----------+----------+
| gender | avg(age) | count(*) |
+--------+----------+----------+
| M | 33.0000 | 15 |
+--------+----------+----------+
1 row in set (0.00 sec)
注：一旦分组 group by ，select语句后只跟分组的字段，聚合函数
ORDER BY: 根据指定的字段对查询结果进行排序
升序：ASC
降序：DESC
mysql> select * from students order by age;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | 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)
LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制,跳过offset,显示row_count
行,offset默为值为0
2.多表查询
子查询：在SQL语句嵌套着查询语句，性能较差，基于某语句的查询结果再次进行的查询
用于比较表达式中的子查询；子查询仅能返回单个值
SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM teachers);
update students set Age=(SELECT avg(Age) FROM teachers) where stuid=25;
用于IN中的子查询：子查询应该单独查询并返回一个或多个值重新构成列表
SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);
用于FROM子句中的子查询
SELECT tb_alias.col1,... FROM (SELECT clause) AS tb_alias WHERE Clause;
范例：
SELECT s.ClassID,s.aage FROM (SELECT ClassID,avg(Age) AS aage FROM students
WHERE ClassID IS NOT NULL GROUP BY ClassID) AS s WHERE s.aage>30;
联合查询：UNION
联合查询 Union 实现的条件,多个表的字段数量相同,字段名和数据类型可以不同,但一般数据类型是相同
的
SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers;
内连接：
inner join 内连接取多个表的交集
mysql> select * from students s inner 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 |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
3 rows in set (0.00 sec)
mysql> select * from students s,teachers t where 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 |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
3 rows in set (0.00 sec)
外连接：
左外连接：FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
右外连接：FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
完全外连接: FROM tb1 FULL OUTER JOIN tb2 ON tb1.col=tb2.col 注意:MySQL 不支持此SQL语法
左连接: 以左表为主根据条件查询右表数据﹐如果根据条件查询右表数据不存在使用null值填充
右连接: 以右表为主根据条件查询左表数据﹐如果根据条件查询左表数据不存在使用null值填充
select * from students s left outer join teachers t on s.teacherid=t.tid
select * from students s right outer join teachers t on s.teacherid=t.tid
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 |
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | 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 |
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
25 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 |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
| 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 |
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 | M |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
后跟过滤条件
mysql> select * from students s left outer join teachers t on s.teacherid=t.tid where s.age>20;
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | 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 |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | NULL | NULL | NULL | NULL |
| 13 | Tian Boguang | 33 | M | 2 | 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 |
| 21 | Huang Yueying | 22 | F | 6 | 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 |
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
15 rows in set (0.00 sec)
练习
导入hellodb.sql生成数据库
1. 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄
mysql> select name,age from students where age>25 and gender="M";
+--------------+-----+
| name | age |
+--------------+-----+
| Xie Yanke | 53 |
| Ding Dian | 32 |
| Yu Yutong | 26 |
| Shi Qing | 46 |
| Tian Boguang | 33 |
| Xu Xian | 27 |
| Sun Dasheng | 100 |
+--------------+-----+
7 rows in set (0.00 sec)
2. 以ClassID为分组依据，显示每组的平均年龄
mysql> select classid,avg(age) from students group by classid;
+---------+----------+
| classid | avg(age) |
+---------+----------+
| NULL | 63.5000 |
| 1 | 20.5000 |
| 2 | 36.0000 |
| 3 | 20.2500 |
| 4 | 24.7500 |
| 5 | 46.0000 |
| 6 | 20.7500 |
| 7 | 19.6667 |
+---------+----------+
8 rows in set (0.00 sec)
3. 显示第2题中平均年龄大于30的分组及平均年龄
mysql> select classid,avg(age) from students group by classid having avg(age)>30;
+---------+----------+
| classid | avg(age) |
+---------+----------+
| NULL | 63.5000 |
| 2 | 36.0000 |
| 5 | 46.0000 |
+---------+----------+
3 rows in set (0.00 sec)
4. 显示以L开头的名字的同学的信息
mysql> select * from students where name like "L%";
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
5. 显示TeacherID非空的同学的相关信息
mysql> select * from students where teacherid is not NULL;
+-------+-------------+-----+--------+---------+-----------+
| 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 |
+-------+-------------+-----+--------+---------+-----------+
5 rows in set (0.00 sec)
6. 以年龄排序后，显示年龄最大的前10位同学的信息
mysql> select * from students order by age desc limit 10;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
+-------+--------------+-----+--------+---------+-----------+
10 rows in set (0.00 sec)
7. 查询年龄大于等于20岁，小于等于25岁的同学的信息
mysql> select * from students where age between 20 and 25;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
+-------+---------------+-----+--------+---------+-----------+
10 rows in set (0.00 sec)
mysql> select * from students where age >= 20 and age <= 25;
8. 以ClassID分组，显示每班的同学的人数
mysql> select classid,count(*) from students group by classid;
+---------+----------+
| classid | count(*) |
+---------+----------+
| NULL | 2 |
| 1 | 4 |
| 2 | 3 |
| 3 | 4 |
| 4 | 4 |
| 5 | 1 |
| 6 | 4 |
| 7 | 3 |
+---------+----------+
8 rows in set (0.00 sec)
9. 以Gender分组，显示其年龄之和
mysql> select gender,sum(age) from students group by gender;
+--------+----------+
| gender | sum(age) |
+--------+----------+
| F | 190 |
| M | 495 |
+--------+----------+
2 rows in set (0.00 sec)
10. 以ClassID分组，显示其平均年龄大于25的班级
mysql> select classid,avg(age) from students group by classid having avg(age)>25;
+---------+----------+
| classid | avg(age) |
+---------+----------+
| NULL | 63.5000 |
| 2 | 36.0000 |
| 5 | 46.0000 |
+---------+----------+
3 rows in set (0.00 sec)
11. 以Gender分组，显示各组中年龄大于25的学员的年龄之和
mysql> select tb1.gender,sum(tb1.age) from ( select * from students where age>25 ) as tb1 group by tb1.gender;
+--------+--------------+
| gender | sum(tb1.age) |
+--------+--------------+
| M | 317 |
+--------+--------------+
1 row in set (0.00 sec)
12. 显示前5位同学的姓名、课程及成绩
mysql> select name,course,score from students as st inner join scores as sc on st.stuid=sc.stuid inner join courses as co on sc.courseid=co.courseid where st.stuid<=5;
+-------------+----------------+-------+
| name | course | score |
+-------------+----------------+-------+
| Shi Zhongyu | Kuihua Baodian | 77 |
| Shi Zhongyu | Weituo Zhang | 93 |
| Shi Potian | Kuihua Baodian | 47 |
| Shi Potian | Daiyu Zanghua | 97 |
| Xie Yanke | Kuihua Baodian | 88 |
| Xie Yanke | Weituo Zhang | 75 |
| Ding Dian | Daiyu Zanghua | 71 |
| Ding Dian | Kuihua Baodian | 89 |
| Yu Yutong | Hamo Gong | 39 |
| Yu Yutong | Dagou Bangfa | 63 |
+-------------+----------------+-------+
10 rows in set (0.00 sec)
13. 显示其成绩高于80的同学的名称及课程
mysql> select name,course from students as st inner join scores as sc on st.stuid=sc.stuid inner join courses as co on sc.courseid=co.courseid where sc.score > 80;
+-------------+----------------+
| name | course |
+-------------+----------------+
| Shi Zhongyu | Weituo Zhang |
| Shi Potian | Daiyu Zanghua |
| Xie Yanke | Kuihua Baodian |
| Ding Dian | Kuihua Baodian |
| Shi Qing | Hamo Gong |
| Xi Ren | Hamo Gong |
| Xi Ren | Dagou Bangfa |
| Lin Daiyu | Jinshe Jianfa |
+-------------+----------------+
8 rows in set (0.00 sec)
14. 取每位同学各门课的平均成绩，显示成绩前三名的同学的姓名和平均成绩
mysql> select name,avg(sc.score) from students as st inner join scores as sc on st.stuid=sc.stuid inner join courses as co on sc.courseid=co.courseid group by st.name order by avg(sc.score) desc limit 3;
+-------------+---------------+
| name | avg(sc.score) |
+-------------+---------------+
| Shi Qing | 96.0000 |
| Shi Zhongyu | 85.0000 |
| Xi Ren | 84.5000 |
+-------------+---------------+
3 rows in set (0.00 sec)
15. 显示每门课程课程名称及学习了这门课的同学的个数
mysql> select course,count(st.stuid) from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.courseid group by course;
+----------------+-----------------+
| course | count(st.stuid) |
+----------------+-----------------+
| Dagou Bangfa | 2 |
| Daiyu Zanghua | 2 |
| Hamo Gong | 3 |
| Jinshe Jianfa | 1 |
| Kuihua Baodian | 4 |
| Taiji Quan | 1 |
| Weituo Zhang | 2 |
+----------------+-----------------+
7 rows in set (0.00 sec)
16. 显示其年龄大于平均年龄的同学的名字
mysql> select name from students where age>(select avg(age) from students);
+--------------+
| name |
+--------------+
| Xie Yanke |
| Ding Dian |
| Shi Qing |
| Tian Boguang |
| Sun Dasheng |
+--------------+
5 rows in set (0.00 sec)
17. 显示其学习的课程为第1、2，4或第7门课的同学的名字
mysql> select st.name,sc.courseid from students st inner join scores sc on st.stuid=sc.stuid where sc.courseid in (1,2,4) or sc.courseid=7;
+-------------+----------+
| name | courseid |
+-------------+----------+
| Shi Zhongyu | 2 |
| Shi Potian | 2 |
| Xie Yanke | 2 |
| Ding Dian | 2 |
| Yu Yutong | 1 |
| Yu Yutong | 7 |
| Shi Qing | 1 |
| Xi Ren | 1 |
| Xi Ren | 7 |
| Lin Daiyu | 4 |
+-------------+----------+
10 rows in set (0.00 sec)
18. 显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学
mysql> select * from students as stu inner join ( select st.classid,avg(age) as avage from students st inner join classes cl on st.classid=cl.classid group by st.classid having count(st.sttuid) >= 3 ) as cls on stu.classid=cls.classid where stu.age>cls.avage;
+-------+---------------+-----+--------+---------+-----------+---------+---------+
| StuID | Name | Age | Gender | ClassID | TeacherID | classid | avage |
+-------+---------------+-----+--------+---------+-----------+---------+---------+
| 2 | Shi Potian | 22 | M | 1 | 7 | 1 | 20.5000 |
| 3 | Xie Yanke | 53 | M | 2 | 16 | 2 | 36.0000 |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | 24.7500 |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 3 | 20.2500 |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | 6 | 20.7500 |
| 16 | Xu Zhu | 21 | M | 1 | NULL | 1 | 20.5000 |
| 17 | Lin Chong | 25 | M | 4 | NULL | 4 | 24.7500 |
| 18 | Hua Rong | 23 | M | 7 | NULL | 7 | 19.6667 |
| 21 | Huang Yueying | 22 | F | 6 | NULL | 6 | 20.7500 |
+-------+---------------+-----+--------+---------+-----------+---------+---------+
9 rows in set (0.00 sec)
19. 统计各班级中年龄大于全校同学平均年龄的同学
mysql> select classid,count(stuid) from students where age > ( select avg(age) from students ) group by classid ;
+---------+--------------+
| classid | count(stuid) |
+---------+--------------+
| NULL | 1 |
| 2 | 2 |
| 4 | 1 |
| 5 | 1 |
+---------+--------------+
4 rows in set (0.00 sec)
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· Manus爆火，是硬核还是营销？
· 一文读懂知识蒸馏
· 终于写完轮子一部分：tcp代理 了，记录一下