MySQL
客户端命令
mysql访问数据库
| 选项 | 说明 |
|---|---|
| -u, --user=name | 指定登录用户名 |
| -p, --password | 指定登录密码(注意是小写p),一定要放到最后面 |
| -h, --host=name | 指定数据库的主机地址，常与-P共同使用 |
| -P, --port=xxx | 指定数据库的端口号(大写P) |
| -S, --socket=name | 指定socket文件 |
| -e, --execute=name | 使用非交互式操作(在shell终端执行sql语句) |
- 使用案例
[root@mysql ~]# mysql -uroot -p1
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
[root@mysql ~]# mysql -e 'show databases;' -uroot -p1
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
mysql默认库介绍：
information_schema
对象信息数据库，提供对数据库元数据的访问 ，有关MySQL服务器的信息，例如数据库或表的名称，列的数据类型或访问权限等
在INFORMATION_SCHEMA中，有数个只读表，它们实际上是视图，而不是基本表，因此你将无法看到与之相关的任何文件
视图，是一个虚表，即视图所对应的数据不进行实际存储，数据库中只存储视图的定义，在对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基本表
mysql
mysql数据库是系统数据库。它包含存储MySQL服务器运行时所需的信息的表。比如权限表、对象信息表、日志系统表、时区系统表、优化器系统表、杂项系统表等
不可以删除,也不要轻易修改
performance_schema
MySQL5.5开始新增一个数据库，主要用于收集数据库服务器性能
库里表的存储引擎均PERFORMANCE_SCHEMA，而用户不能创建存储引擎为PERFORMANCE_SCHEMA的表
sys
- mysql5.7增加了sys 系统数据库，通过这个库可以快速的了解系统的元数据信息；
- sys库方便DBA发现数据库的很多信息，解决性能瓶颈
- 这个库是通过视图的形式把information_schema 和performance_schema结合起来，查询出更加令人容易理解的数据
mysqladmin管理数据库
常用选项
| 选项 | 描述 |
|---|---|
| -h, --host=name | 指定连接数据库主机 |
| -p, --password | 指定数据库密码 |
| -P, --port=# | 指定数据库端口 |
| -S, --socket=name | 指定数据库socket文件 |
| -u, --user=name | 指定连接数据库用户 |
常用命令
| 命令 | 描述 |
|---|---|
| password [new-password] | 更改密码 |
| reload | 刷新授权表 |
| shutdown | 停止mysql服务 |
| status | 简短查看数据库状态信息 |
| start-slave | 启动slave |
| stop-slave | 停止slave |
| variables | 打印可用变量 |
| version | 查看当前mysql数据库的版本信息 |
用法
更改密码
[root@mysql ~]# mysqladmin password '123' -p
Enter password:
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
刷新授权表
[root@mysql ~]# mysqladmin reload -p
Enter password:
停止mysql
[root@mysql ~]# mysqladmin shutdown -p
Enter password:
查看状态
[root@mysql ~]# mysqladmin status -p
Enter password:
Uptime: 65 Threads: 1 Questions: 2 Slow queries: 0 Opens: 105 Flush tables: 1 Open tables: 98 Queries per second avg: 0.030
打印可用变量
[root@mysql ~]# mysqladmin variables -p
Enter password:
查看mysql版本
[root@mysql ~]# mysqladmin version -p123
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqladmin Ver 8.42 Distrib 5.7.31, for linux-glibc2.12 on x86_64
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Server version 5.7.31
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 5 min 7 sec
Threads: 1 Questions: 6 Slow queries: 0 Opens: 106 Flush tables: 1 Open tables: 99 Queries per second avg: 0.019
SQL介绍
概述
SQL 是 Structure Query Language(结构化查询语言)的缩写,它是使用关系模型的数据库应
用语言,由 IBM 在 20 世纪 70 年代开发出来,作为 IBM 关系数据库原型 System R 的原型关
系语言,实现了关系数据库中的信息检索。
20 世纪 80 年代初,美国国家标准局(ANSI)开始着手制定 SQL 标准,最早的 ANSI 标准于
1986 年完成,就被叫作 SQL-86。标准的出台使 SQL 作为标准关系数据库语言的地位得到了
加强。SQL 标准目前已几经修改更趋完善。
正是由于 SQL 语言的标准化,所以大多数关系型数据库系统都支持 SQL 语言,它已经发展
成为多种平台进行交互操作的底层会话语言。
分类
-
DDL(Data Definition Languages)语句:
数据定义语言,这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create、drop、alter、rename、truncate。
-
DML(Data Manipulation Language)语句:
数据操纵语句,用于添加、删除、更新和查询数据库记录,并检查数据完整性,常用的语句关键字主要包括 insert、delete、update等。
-
DCL(Data Control Language)语句:
数据控制语句,用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 grant、revoke 等。
-
DQL(Data Query Language)语句:
数据查询语句，用于从一个或多个表中检索信息。主要的语句关键字包括 select
逻辑结构
-
MySQL，是MySQL数据库管理软件（DBMS）
-
一个MySQL DBMS可以同时存放多个数据库，理论上一个项目就对应一个数据库。如博客项目blog数据库
-
一个数据库中含有多个数据表，用于存放数据，理论上一个功能对应于一个数据表，如博客系统中的用户管理功能，就需要一个user数据表
-
一个数据表可以拆分为多个字段（列），每个字段就是一个属性
-
一个数据表除了字段以外，还有很多行，每一行都是一条完整的数据（记录）
基础SQL语句
操作库（文件夹）
一·增create
#创建数据库
mysql> create database db1;
#创建时指定编码格式（默认字符集）
mysql> create database db2 default charset utf8;
#如果存在不报错(if not exists)
mysql> create database if not exists db1 default charset utf8;
编码格式
- latin1 ：仅有256个传统字符
- gbk：gb2312中国大陆开发包含简体中文的编码格式，big5中国台湾开发包含繁体中文的编码格式，后合并升级为gbk
- utf8：通用编码，支持多国语言，5.6版本后升级为utf8mb4
二·查show
#查看所有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| mysql |
| performance_schema |
| sys |
+--------------------+
#查看指定数据库创建过程（编码格式）
mysql> show create database db1;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
三·删drop
mysql> drop database db2;
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
四·改alter（一般改编码格式）
mysql> alter database db1 default charset gbk;
操作表（文件）
一·增create
语法：
mysql> create table 数据表名称(
字段1 字段类型 [字段约束],
字段2 字段类型 [字段约束],
...
);
#创建表前，需要先进入数据库
mysql> use db1;
Database changed
mysql> create table t1(id int,name char);
字段类型(下面有详细介绍)
- tinyint：微整型，范围-128 ~ 127，无符号型，则表示0 ~ 255
- int：整形
- char：固定长度的字段
- varchar：可变长度的字段，早期最大值为256，现在理论最大值65535，1个GBK格式的字符占用2个字节，1个UTF8格式的字符占用3个字节
- text：文本类型，varchar存储不了的字符串信息，都能用text文本处理
- date：年-月-日
- unsigned：无符号型，只有0和正数，如年龄
- enum：枚举类型，只能从给定的值中选择，如性别
mysql> create table t2(
-> id int,
-> title varchar(50),
-> author varchar(20),
-> content text
-> );
Query OK, 0 rows affected (0.00 sec)
二·查show
#查询当前数据库的所有数据表
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
| t2 |
+---------------+
#查询数据表的创建过程（编码格式，字段信息）
mysql> show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+--------------------------------------------------------------------------------------------------------------------+
#显示表的字段信息
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
三·改alter
#添加字段
语法：
mysql> alter table 表名 add 新字段名 字段类型 first|after 其他字段;
######选项说明：
first：把新添加字段放在第一位
after 字段名称：把新添加字段放在指定字段的后面
mysql> alter table t1 add time date after name;
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(1) | YES | | NULL | |
| time | date | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
#修改字段名和字段类型
mysql> alter table t1 change name name varchar(30);
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
| time | date | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
#仅修改字段类型
mysql> alter table t1 modify id int(20);
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(20) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
| time | date | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
#删除字段
mysql> alter table t1 drop time;
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(20) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
#修改数据表引擎（MyISAM或InnoDB）
mysql> alter table t1 engine=myisam;
mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(20) DEFAULT NULL,
`name` varchar(30) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+------------------------------------------------------------------------------------------------------------------------+
#修改数据表的编码格式
mysql> alter table t1 default charset=utf8;
mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(20) DEFAULT NULL,
`name` varchar(30) CHARACTER SET gbk DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------+
#重命名表名
mysql> rename table t1 to db1_t1;
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| db1_t1 |
| t2 |
+---------------+
#将表移动到其他数据库
mysql> rename table db1.db1_t1 to db2.db2_t1;
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t2 |
+---------------+
mysql> use db2;
Database changed
mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| db2_t1 |
+---------------+
四·删drop
#删除表
mysql> use db1;
Database changed
mysql> drop table t2;
mysql> show tables;
Empty set (0.00 sec)
操作记录（行）
在SQL中，数据外，其他类型的值都要引号引起来，否则会报错
#准备一个数据表
mysql> use db1;
mysql> create table msg(
id int,
username varchar(20),
age tinyint unsigned,
gender enum('男','女','保密'),
address varchar(255)
) engine=innodb default charset=utf8;
mysql> desc msg;
+----------+----------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| username | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('男','女','保密') | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
+----------+----------------------------+------+-----+---------+-------+
一·增insert
mysql> insert into msg values(1,'张三',18,'男','北京');
mysql> insert into msg values(2,'李四',18,'女','上海');
mysql> insert into msg(id,username) values(3,'王五');
二·查select
语法：
select 字段 from 表名 [where 查询条件]；
#查询所有记录
mysql> select * from msg;
+------+----------+------+--------+---------+
| id | username | age | gender | address |
+------+----------+------+--------+---------+
| 1 | 张三 | 18 | 男 | 北京 |
| 2 | 李四 | 18 | 女 | 上海 |
| 3 | 王五 | NULL | NULL | NULL |
+------+----------+------+--------+---------+
#仅查询表中的id和username
mysql> select id,username from msg;
+------+----------+
| id | username |
+------+----------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
+------+----------+
#查询id=2的人员信息
mysql> select * from msg where id=2;
+------+----------+------+--------+---------+
| id | username | age | gender | address |
+------+----------+------+--------+---------+
| 2 | 李四 | 18 | 女 | 上海 |
+------+----------+------+--------+---------+
#去重查询
mysql> select distinct age from msg;
+------+
| age |
+------+
| 18 |
| NULL |
+------+
三·改update
语法：
update 表名 set 字段1=更新后的值,字段2=更新后的值,... where 条件;
##如果不指定条件，则会修改表中所有记录##
mysql> update msg set gender='保密',age=20,address='广东';
mysql> select * from msg;
+------+----------+------+--------+---------+
| id | username | age | gender | address |
+------+----------+------+--------+---------+
| 1 | 张三 | 20 | 保密 | 广东 |
| 2 | 李四 | 20 | 保密 | 广东 |
| 3 | 王五 | 20 | 保密 | 广东 |
+------+----------+------+--------+---------+
mysql> update msg set gender='男',age=18,address='北京' where username='张三';
mysql> update msg set gender='女',age=18,address='上海' where username='李四';
mysql> select * from msg;
+------+----------+------+--------+---------+
| id | username | age | gender | address |
+------+----------+------+--------+---------+
| 1 | 张三 | 18 | 男 | 北京 |
| 2 | 李四 | 18 | 女 | 上海 |
| 3 | 王五 | 20 | 保密 | 广东 |
+------+----------+------+--------+---------+
四·删除
语法：
delete from 表名 [where 条件];
#删除某条记录
mysql> delete from msg where id=3;
mysql> select * from msg;
+------+----------+------+--------+---------+
| id | username | age | gender | address |
+------+----------+------+--------+---------+
| 1 | 张三 | 18 | 男 | 北京 |
| 2 | 李四 | 18 | 女 | 上海 |
+------+----------+------+--------+---------+
#删除所有记录（清空）
mysql> delete from msg ;
mysql> select * from msg;
Empty set (0.00 sec)
#清空
mysql> truncate msg;
mysql> select * from msg;
Empty set (0.00 sec)
delete from&truncate清空数据
- delete：删除数据记录
- 数据操作语言（DML）
- 在事务控制里，DML语句要么成功，要么失败
- 删除大量记录速度慢，只删除数据不回收高水位线
- 可以带条件删除
- truncate：删除所有数据记录
- 数据定义语言（DDL）
- 不在事务控制里，DDL语句执行前会提交前面所有未提交的事务
- 清里大量数据速度快，回收高水位线（high water mark）
- 不能带条件删除
- 回收高水位线
- id int not null auto_increment primary key,
- uto_increment自动增长
- not null 非空约束
- primary key主键约束（非空且唯一）
#准备表 create table tb1( id int not null auto_increment, username varchar(20), age tinyint unsigned, gender enum('男','女','保密'), address varchar(255), primary key(id) ) engine=innodb default charset=utf8; insert into tb1 values (null,'张三',24,'男','广东'); insert into tb1 values (null,'张三',24,'男','广东'); insert into tb1 values (null,'李四',22,'女','上海'); mysql> select * from tb1; +----+----------+------+--------+---------+ | id | username | age | gender | address | +----+----------+------+--------+---------+ | 1 | 张三 | 24 | 男 | 广东 | | 2 | 张三 | 24 | 男 | 广东 | | 3 | 李四 | 22 | 女 | 上海 | +----+----------+------+--------+---------+ #delete from清空后，不回收高水位线，id从4开始 mysql> delete from tb1; mysql> insert into tb1 values (null,'小五',18,'女','湖南省'); mysql> select * from tb1; +----+----------+------+--------+-----------+ | id | username | age | gender | address | +----+----------+------+--------+-----------+ | 4 | 小五 | 18 | 女 | 湖南省 | +----+----------+------+--------+-----------+ #truncate清空后，回收高水位线，id又从1开始 mysql> truncate tb1; mysql> insert into tb1 values (null,'小五',18,'女','湖南省'); mysql> select * from tb1; +----+----------+------+--------+-----------+ | id | username | age | gender | address | +----+----------+------+--------+-----------+ | 1 | 小五 | 18 | 女 | 湖南省 | +----+----------+------+--------+-----------+
数据类型
整数类型
| 类型 | 大小（字节） | 范围（有符号） | 范围（无符号） | 备注 |
|---|---|---|---|---|
| tinyint | 1 | （-128,127） | （0,255） | |
| smallint | 2 | （-32768,32767） | （0,65535） | |
| mediumint | 3 | （-8388608,8388607） | （0,16777215） | 1600万 |
| int | 4 | （-231，231-1) | （0,232-1) | 21亿 |
| bigint | 8 | （-263,263-1) | (0,264-1) | 1800亿亿 |
整数类型的选择主要取决于数值的范围，满足条件情况下越小越好，
- 案例：age tinyint unsigned,
小数类型
| 类型 | 大小（字节） | 精确度 | 备注 |
|---|---|---|---|
| float | 4 | 小数点后7位，近似值 | 单精度，浮点型 |
| double | 8 | 小数点后15位，近似值 | 双精度，浮点型 |
| decimal | 变长 | 精确值 | 高精度，定点型 |
float类型会随着数值增大，精度减小
float（M,D）其中M表示存储的有效位数（整数部分+小数部分）；D表示小数部分的位数
float（7,4），如果插入数值超过7位或者超过4位小数，系统会存入近似值。假如想插入100.00009，系统会存入100.0001
- 案例：salary decimal(11,2),
字符串类型
| 类型 | 大小 | 描述 | 用途举例 |
|---|---|---|---|
| char | （0,255） | 定长存储 长度不足用空格填充，读取时删除空格 | 加密密码char（32） 手机号char（11） |
| varchar | （0,65535） | 变长存储 建议最多存储255个字符 | 名称varchar（30） |
| text | （0,65535） | 长文本数据 varchar不能存的，text可以 | 文本内容 |
#案例：存储用户及其密码
mysql> use db1;
mysql> create table user_pass(
id tinyint unsigned not null auto_increment,
username varchar(10),
password char(32),
primary key(id)
) engine=innodb default charset=utf8;
mysql> insert into user_pass values(null,'admin',md5('g666'));
mysql> select * from user_pass;
+----+----------+----------------------------------+
| id | username | password |
+----+----------+----------------------------------+
| 1 | admin | b2b73cd92e1cde3349e263985f1a0aeb |
+----+----------+----------------------------------+
时间类型
| 类型 | 范围 | 格式 | 备注 |
|---|---|---|---|
| date | 1000-01-01/9999-12-31 | YYYY-MM-DD | 年月日 |
| time | -838:59:59/838:59:59 | HH:MM:SS | 时分秒 |
| year | 1901/2155 | YYYY | 年 |
| datetime | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | |
| timestamp | 1970-01-01 00:00:01/2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 时间戳 |
无效的时间，会被系统转换成’00:00:00’
其他类型
| 类型 | 描述 | 用途举例 |
|---|---|---|
| blob | 二进制的大型数据（字节串） | 图片，音视频 |
| enum | 枚举类型，多选一 | 性别gender enum(‘男’,‘女’,‘保密’) |
| set | 集合类型，多选多 | 兴趣爱好hobby set(‘吃饭’,‘睡觉’,‘打游戏’) |
SQL查询语句
语法
select */字段列表 from 表名 where 子句 group by 子句 having 子句 order by 子句 limit 子句;
① where子句② group by子句③ having子句④ order by子句⑤ limit子句
五子句的顺序是固定的，不能颠倒。
五子句
where子句
- 用于筛选数据
| 符号 | 说明 |
|---|---|
| % | 匹配0个或任意多个字符 |
| _(下划线) | 匹配单个字符 |
| like | 模糊匹配 |
| = | 等于,精确匹配 |
| > | 大于 |
| < | 小于 |
| >= | 大于等于 |
| <= | 小于等于 |
| !=和<> | 不等于 |
| ! 和 not | 逻辑非 |
| || 和 or | 逻辑或 |
| && 和 and | 逻辑与 |
| between…and… | 两者之间 |
| in (…) | 在… |
| not in (…) | 不在 |
- 准备数据
create table tb_student(
id mediumint not null auto_increment,
name varchar(20),
age tinyint unsigned default 0,
gender enum('男','女'),
address varchar(255),
primary key(id)
) engine=innodb default charset=utf8;
insert into tb_student values (null,'刘备',33,'男','湖北省武汉市');
insert into tb_student values (null,'貂蝉',18,'女','湖南省长沙市');
insert into tb_student values (null,'关羽',32,'男','湖北省荆州市');
insert into tb_student values (null,'大乔',20,'女','河南省漯河市');
insert into tb_student values (null,'赵云',25,'男','江苏省南京市');
insert into tb_student values (null,'小乔',18,'女','湖北省荆州市');
insert into tb_student(id,name,age,gender) values (null,'曹操',36,'男');
mysql> desc tb_student;
+---------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+----------------+
| id | mediumint(9) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | 0 | |
| gender | enum('男','女') | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
+---------+---------------------+------+-----+---------+----------------+
mysql> select * from tb_student;
+----+--------+------+--------+-----------------+
| id | name | age | gender | address |
+----+--------+------+--------+-----------------+
| 1 | 刘备 | 33 | 男 | 湖北省武汉市 |
| 2 | 貂蝉 | 18 | 女 | 湖南省长沙市 |
| 3 | 关羽 | 32 | 男 | 湖北省荆州市 |
| 4 | 大乔 | 20 | 女 | 河南省漯河市 |
| 5 | 赵云 | 25 | 男 | 江苏省南京市 |
| 6 | 小乔 | 18 | 女 | 湖北省荆州市 |
+----+--------+------+--------+-----------------+
- 使用方法
#查询姓赵的学生信息
mysql> select * from tb_student where name like'赵%';
+----+--------+------+--------+--------------------+
| id | name | age | gender | address |
+----+--------+------+--------+--------------------+
| 5 | 赵云 | 25 | 男 | 江苏省南京市 |
+----+--------+------+--------+--------------------+
#取班级中年龄大于30岁的男学生信息
mysql> select * from tb_student where gender='男' && age>30;
+----+--------+------+--------+--------------------+
| id | name | age | gender | address |
+----+--------+------+--------+--------------------+
| 1 | 刘备 | 33 | 男 | 湖北省武汉市 |
| 3 | 关羽 | 32 | 男 | 湖北省荆州市 |
+----+--------+------+--------+--------------------+
#获取年龄在18周岁~25周岁之间的同学信息
mysql> select * from tb_student where age between 18 and 25;
+----+--------+------+--------+--------------------+
| id | name | age | gender | address |
+----+--------+------+--------+--------------------+
| 2 | 貂蝉 | 18 | 女 | 湖南省长沙市 |
| 4 | 大乔 | 20 | 女 | 河南省漯河市 |
| 5 | 赵云 | 25 | 男 | 江苏省南京市 |
| 6 | 小乔 | 18 | 女 | 湖北省荆州市 |
+----+--------+------+--------+--------------------+
mysql> select * from tb_student where age>=18 and age<=25;
+----+--------+------+--------+--------------------+
| id | name | age | gender | address |
+----+--------+------+--------+--------------------+
| 2 | 貂蝉 | 18 | 女 | 湖南省长沙市 |
| 4 | 大乔 | 20 | 女 | 河南省漯河市 |
| 5 | 赵云 | 25 | 男 | 江苏省南京市 |
| 6 | 小乔 | 18 | 女 | 湖北省荆州市 |
+----+--------+------+--------+--------------------+
#获取id为1,3,5的学生信息
mysql> select * from tb_student where id in (1,3,5);
+----+--------+------+--------+--------------------+
| id | name | age | gender | address |
+----+--------+------+--------+--------------------+
| 1 | 刘备 | 33 | 男 | 湖北省武汉市 |
| 3 | 关羽 | 32 | 男 | 湖北省荆州市 |
| 5 | 赵云 | 25 | 男 | 江苏省南京市 |
+----+--------+------+--------+--------------------+
#查询地址为空的学生信息
mysql> select * from tb_student where address is NULL;
+----+--------+------+--------+---------+
| id | name | age | gender | address |
+----+--------+------+--------+---------+
| 7 | 曹操 | 36 | 男 | NULL |
+----+--------+------+--------+---------+
group by子句
- 使用group by子句分组的目的是为了便于统计，常与统计函数一起使用
| 常见统计函数 | 说明 |
|---|---|
| max | 求最大值 |
| min | 求最小值 |
| sum | 求和 |
| avg | 求平均值 |
| count | 求总行数 |
- 统计函数使用方法
#统计tb_student表中共有多少条记录
mysql> select count(*) from tb_student;
+----------+
| count(*) |
+----------+
| 7 |
+----------+
#统计tb_student表中年龄平均值
mysql> select avg(age) from tb_student;
+----------+
| avg(age) |
+----------+
| 26.0000 |
+----------+
- group by使用方法
语法：
select 分组字段，统计函数 from 表名 group by 分组字段；
#分别统计表中男性，女性的总数
mysql> select gender,count(*) from tb_student group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男 | 4 |
| 女 | 3 |
+--------+----------+
#分别统计表中男性，女性中年龄最大值
mysql> select gender,max(age) from tb_student group by gender;
+--------+----------+
| gender | max(age) |
+--------+----------+
| 男 | 36 |
| 女 | 20 |
+--------+----------+
2 rows in set (0.00 sec)
having子句
-
having与where类似，根据条件对数据进行过滤筛选
-
where针对表中的列发挥作用，查询数据
-
having针对分组后的结果发挥作用，筛选数据
-
数据准备
create table tb1(
id mediumint not null auto_increment,
name varchar(20),
age tinyint unsigned default 0,
gender enum('男','女'),
subject enum('ui','java','yunwei','python'),
primary key(id)
) engine=innodb default charset=utf8;
insert into tb1 values
(null,'悟空',255,'男','ui'),
(null,'八戒',250,'男','python'),
(null,'唐僧',30,'男','yunwei'),
(null,'沙僧',150,'男','java'),
(null,'小白龙',100,'男','yunwei'),
(null,'白骨精',28,'女','ui'),
(null,'兔子精',22,'女','yunwei'),
(null,'狮子精',33,'男','yunwei');
mysql> select * from tb1;
+----+-----------+------+--------+---------+
| id | name | age | gender | subject |
+----+-----------+------+--------+---------+
| 1 | 悟空 | 255 | 男 | ui |
| 2 | 八戒 | 250 | 男 | python |
| 3 | 唐僧 | 30 | 男 | yunwei |
| 4 | 沙僧 | 150 | 男 | java |
| 5 | 小白龙 | 100 | 男 | yunwei |
| 6 | 白骨精 | 28 | 女 | ui |
| 7 | 兔子精 | 22 | 女 | yunwei |
| 8 | 狮子精 | 33 | 男 | yunwei |
+----+-----------+------+--------+---------+
- 使用方法
#求每个学科中，学科人数大于3人的学科信息
###先统计每个学科各有多少人，再筛选学科人数大于3的学科
mysql> select subject,count(*) from tb1 group by subject ;
+---------+----------+
| subject | count(*) |
+---------+----------+
| ui | 2 |
| java | 1 |
| yunwei | 4 |
| python | 1 |
+---------+----------+
mysql> select subject,count(*) from tb1 group by subject having count(*)>3 ;
+---------+----------+
| subject | count(*) |
+---------+----------+
| yunwei | 4 |
+---------+----------+
order by子句
-
用于排序
-
使用方法
语法：
select * from 表名 ... order by 字段名称 [asc/desc];
#将tb_student表按年龄大小排序
mysql> select * from tb_student order by age;
+----+--------+------+--------+--------------------+
| id | name | age | gender | address |
+----+--------+------+--------+--------------------+
| 2 | 貂蝉 | 18 | 女 | 湖南省长沙市 |
| 6 | 小乔 | 18 | 女 | 湖北省荆州市 |
| 4 | 大乔 | 20 | 女 | 河南省漯河市 |
| 5 | 赵云 | 25 | 男 | 江苏省南京市 |
| 3 | 关羽 | 32 | 男 | 湖北省荆州市 |
| 1 | 刘备 | 33 | 男 | 湖北省武汉市 |
| 7 | 曹操 | 36 | 男 | NULL |
+----+--------+------+--------+--------------------+
7 rows in set (0.00 sec)
#将tb_student表按年龄大小降序排序
mysql> select * from tb_student order by age desc;
+----+--------+------+--------+--------------------+
| id | name | age | gender | address |
+----+--------+------+--------+--------------------+
| 7 | 曹操 | 36 | 男 | NULL |
| 1 | 刘备 | 33 | 男 | 湖北省武汉市 |
| 3 | 关羽 | 32 | 男 | 湖北省荆州市 |
| 5 | 赵云 | 25 | 男 | 江苏省南京市 |
| 4 | 大乔 | 20 | 女 | 河南省漯河市 |
| 2 | 貂蝉 | 18 | 女 | 湖南省长沙市 |
| 6 | 小乔 | 18 | 女 | 湖北省荆州市 |
+----+--------+------+--------+--------------------+
#将tb_student表按年龄大小降序排序，如果年龄相同，按id排序
mysql> select * from tb_student order by age,id desc;
+----+--------+------+--------+--------------------+
| id | name | age | gender | address |
+----+--------+------+--------+--------------------+
| 6 | 小乔 | 18 | 女 | 湖北省荆州市 |
| 2 | 貂蝉 | 18 | 女 | 湖南省长沙市 |
| 4 | 大乔 | 20 | 女 | 河南省漯河市 |
| 5 | 赵云 | 25 | 男 | 江苏省南京市 |
| 3 | 关羽 | 32 | 男 | 湖北省荆州市 |
| 1 | 刘备 | 33 | 男 | 湖北省武汉市 |
| 7 | 曹操 | 36 | 男 | NULL |
+----+--------+------+--------+--------------------+
limit子句
-
限制数量；在开发项目中，主要应用于数据分页
-
使用方法
select * from 表名 ... limit n; 查询满足条件的n条数据
select * from 表名 ... limit m,n; 从m+1开始查，查到第m+n条记录，共查记录n条
#查前五条记录
mysql> select * from tb_student limit 5;
+----+--------+------+--------+--------------------+
| id | name | age | gender | address |
+----+--------+------+--------+--------------------+
| 1 | 刘备 | 33 | 男 | 湖北省武汉市 |
| 2 | 貂蝉 | 18 | 女 | 湖南省长沙市 |
| 3 | 关羽 | 32 | 男 | 湖北省荆州市 |
| 4 | 大乔 | 20 | 女 | 河南省漯河市 |
| 5 | 赵云 | 25 | 男 | 江苏省南京市 |
+----+--------+------+--------+--------------------+
#数据分页，三条记录三条记录的查
mysql> select * from tb_student limit 0,3;
+----+--------+------+--------+--------------------+
| id | name | age | gender | address |
+----+--------+------+--------+--------------------+
| 1 | 刘备 | 33 | 男 | 湖北省武汉市 |
| 2 | 貂蝉 | 18 | 女 | 湖南省长沙市 |
| 3 | 关羽 | 32 | 男 | 湖北省荆州市 |
+----+--------+------+--------+--------------------+
mysql> select * from tb_student limit 3,3;
+----+--------+------+--------+--------------------+
| id | name | age | gender | address |
+----+--------+------+--------+--------------------+
| 4 | 大乔 | 20 | 女 | 河南省漯河市 |
| 5 | 赵云 | 25 | 男 | 江苏省南京市 |
| 6 | 小乔 | 18 | 女 | 湖北省荆州市 |
+----+--------+------+--------+--------------------+
mysql> select * from tb_student limit 6,3;
+----+--------+------+--------+---------+
| id | name | age | gender | address |
+----+--------+------+--------+---------+
| 7 | 曹操 | 36 | 男 | NULL |
+----+--------+------+--------+---------+
多表查询
准备数据
create table department(
id int,
name varchar(20)
);
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');
mysql> select * from department;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
insert into employee(name,sex,age,dep_id) values
('张三','male',18,200),
('李四','female',48,201),
('王二','male',38,201),
('麻子','female',28,202),
('小明','male',18,200),
('小红','female',18,204);
mysql> select * from employee;
+----+--------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+--------+--------+------+--------+
| 1 | 张三 | male | 18 | 200 |
| 2 | 李四 | female | 48 | 201 |
| 3 | 王二 | male | 38 | 201 |
| 4 | 麻子 | female | 28 | 202 |
| 5 | 小明 | male | 18 | 200 |
| 6 | 小红 | female | 18 | 204 |
+----+--------+--------+------+--------+
create table intern(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
insert into intern(name,sex,age,dep_id) values
('嚣张','male',28,202),
('王一宝','female',22,201);
mysql> select * from intern;
+----+-----------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+-----------+--------+------+--------+
| 1 | 嚣张 | male | 28 | 202 |
| 2 | 王一宝 | female | 22 | 201 |
+----+-----------+--------+------+--------+
UNION联合查询
- UNION联合查询，把多个表中的数据联合在一起进行显示
- 必须是结构完全一致的记录集合才可以使用UNION
mysql> select * from intern union select * from employee;
+----+-----------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+-----------+--------+------+--------+
| 1 | 嚣张 | male | 28 | 202 |
| 2 | 王一宝 | female | 22 | 201 |
| 1 | 张三 | male | 18 | 200 |
| 2 | 李四 | female | 48 | 201 |
| 3 | 王二 | male | 38 | 201 |
| 4 | 麻子 | female | 28 | 202 |
| 5 | 小明 | male | 18 | 200 |
| 6 | 小红 | female | 18 | 204 |
+----+-----------+--------+------+--------+
交叉查询
字段总数 = 数据表1的字段 + 数据表2的字段
总记录数 = 数据表1的记录数 * 数据表2的记录数 => (笛卡尔积)
- 使用方法
语法：
select */字段列表 from 表1,表2;
select */字段列表 from 表1 cross join 表2;
mysql> select * from department,employee;
+------+--------------+----+--------+--------+------+--------+
| id | name | id | name | sex | age | dep_id |
+------+--------------+----+--------+--------+------+--------+
| 200 | 技术 | 1 | 张三 | male | 18 | 200 |
| 201 | 人力资源 | 1 | 张三 | male | 18 | 200 |
| 202 | 销售 | 1 | 张三 | male | 18 | 200 |
| 203 | 运营 | 1 | 张三 | male | 18 | 200 |
| 200 | 技术 | 2 | 李四 | female | 48 | 201 |
| 201 | 人力资源 | 2 | 李四 | female | 48 | 201 |
| 202 | 销售 | 2 | 李四 | female | 48 | 201 |
| 203 | 运营 | 2 | 李四 | female | 48 | 201 |
| 200 | 技术 | 3 | 王二 | male | 38 | 201 |
| 201 | 人力资源 | 3 | 王二 | male | 38 | 201 |
| 202 | 销售 | 3 | 王二 | male | 38 | 201 |
| 203 | 运营 | 3 | 王二 | male | 38 | 201 |
| 200 | 技术 | 4 | 麻子 | female | 28 | 202 |
| 201 | 人力资源 | 4 | 麻子 | female | 28 | 202 |
| 202 | 销售 | 4 | 麻子 | female | 28 | 202 |
| 203 | 运营 | 4 | 麻子 | female | 28 | 202 |
| 200 | 技术 | 5 | 小明 | male | 18 | 200 |
| 201 | 人力资源 | 5 | 小明 | male | 18 | 200 |
| 202 | 销售 | 5 | 小明 | male | 18 | 200 |
| 203 | 运营 | 5 | 小明 | male | 18 | 200 |
| 200 | 技术 | 6 | 小红 | female | 18 | 204 |
| 201 | 人力资源 | 6 | 小红 | female | 18 | 204 |
| 202 | 销售 | 6 | 小红 | female | 18 | 204 |
| 203 | 运营 | 6 | 小红 | female | 18 | 204 |
+------+--------------+----+--------+--------+------+--------+
内连接
-
把两个表连接，然后拿表1中的每一条记录与表2中的每一条记录进行匹配，如果有与之对应的结果，则显示。反之，则忽略这条记录。
-
使用方法
语法：
select 表1.字段列表,表2.字段列表 from 表1 inner join 表2 on 连接条件
mysql> select * from employee inner join department on employee.dep_id=department.id;
+----+--------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+--------+--------+------+--------+------+--------------+
| 1 | 张三 | male | 18 | 200 | 200 | 技术 |
| 2 | 李四 | female | 48 | 201 | 201 | 人力资源 |
| 3 | 王二 | male | 38 | 201 | 201 | 人力资源 |
| 4 | 麻子 | female | 28 | 202 | 202 | 销售 |
| 5 | 小明 | male | 18 | 200 | 200 | 技术 |
+----+--------+--------+------+--------+------+--------------+
mysql> select employee.*,department.name from employee inner join department on employee.dep_id=deparrtment.id;
+----+--------+--------+------+--------+--------------+
| id | name | sex | age | dep_id | name |
+----+--------+--------+------+--------+--------------+
| 1 | 张三 | male | 18 | 200 | 技术 |
| 2 | 李四 | female | 48 | 201 | 人力资源 |
| 3 | 王二 | male | 38 | 201 | 人力资源 |
| 4 | 麻子 | female | 28 | 202 | 销售 |
| 5 | 小明 | male | 18 | 200 | 技术 |
+----+--------+--------+------+--------+--------------+
mysql> select employee.name,employee.age,department.name from employee inner join department on emplooyee.
+--------+------+--------------+
| name | age | name |
+--------+------+--------------+
| 张三 | 18 | 技术 |
| 李四 | 48 | 人力资源 |
| 王二 | 38 | 人力资源 |
| 麻子 | 28 | 销售 |
| 小明 | 18 | 技术 |
+--------+------+--------------+
外连接
- 左外连接查询：把左表中的每一条数据都保留，右表匹配到结果就显示，匹配不到就NULL
select 表1.字段列表,表2.字段列表 from 表1 left join 表2 on 连接条件;
- 右外连接查询：把右表中的每一条数据都保留，左表匹配到结果就显示，匹配不到就NULL
select 表1.字段列表,表2.字段列表 from 表1 right join 表2 on 连接条件;
- 使用方法
mysql> select * from employee left join department on employee.dep_id=department.id;
+----+--------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+--------+--------+------+--------+------+--------------+
| 1 | 张三 | male | 18 | 200 | 200 | 技术 |
| 5 | 小明 | male | 18 | 200 | 200 | 技术 |
| 2 | 李四 | female | 48 | 201 | 201 | 人力资源 |
| 3 | 王二 | male | 38 | 201 | 201 | 人力资源 |
| 4 | 麻子 | female | 28 | 202 | 202 | 销售 |
| 6 | 小红 | female | 18 | 204 | NULL | NULL |
+----+--------+--------+------+--------+------+--------------+
mysql> select employee.*,department.name from employee left join department on employee.dep_id=department.id;
+----+--------+--------+------+--------+--------------+
| id | name | sex | age | dep_id | name |
+----+--------+--------+------+--------+--------------+
| 1 | 张三 | male | 18 | 200 | 技术 |
| 5 | 小明 | male | 18 | 200 | 技术 |
| 2 | 李四 | female | 48 | 201 | 人力资源 |
| 3 | 王二 | male | 38 | 201 | 人力资源 |
| 4 | 麻子 | female | 28 | 202 | 销售 |
| 6 | 小红 | female | 18 | 204 | NULL |
+----+--------+--------+------+--------+--------------+
mysql> select employee.name,employee.age,department.name from employee right join department on employee.dep_id=department.id;
+--------+------+--------------+
| name | age | name |
+--------+------+--------------+
| 张三 | 18 | 技术 |
| 李四 | 48 | 人力资源 |
| 王二 | 38 | 人力资源 |
| 麻子 | 28 | 销售 |
| 小明 | 18 | 技术 |
| NULL | NULL | 运营 |
+--------+------+--------------+
别名机制：简化内外连接
mysql> select * from employee right join department on employee.dep_id=department.id;
+------+--------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+--------+--------+------+--------+------+--------------+
| 1 | 张三 | male | 18 | 200 | 200 | 技术 |
| 2 | 李四 | female | 48 | 201 | 201 | 人力资源 |
| 3 | 王二 | male | 38 | 201 | 201 | 人力资源 |
| 4 | 麻子 | female | 28 | 202 | 202 | 销售 |
| 5 | 小明 | male | 18 | 200 | 200 | 技术 |
| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
+------+--------+--------+------+--------+------+--------------+
mysql> select * from employee e right join department d on e.dep_id=d.id;
+------+--------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+--------+--------+------+--------+------+--------------+
| 1 | 张三 | male | 18 | 200 | 200 | 技术 |
| 2 | 李四 | female | 48 | 201 | 201 | 人力资源 |
| 3 | 王二 | male | 38 | 201 | 201 | 人力资源 |
| 4 | 麻子 | female | 28 | 202 | 202 | 销售 |
| 5 | 小明 | male | 18 | 200 | 200 | 技术 |
| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
+------+--------+--------+------+--------+------+--------------+
mysql> select e.name,e.sex,d.id from employee e right join department d on e.dep_id=d.id;
+--------+--------+------+
| name | sex | id |
+--------+--------+------+
| 张三 | male | 200 |
| 李四 | female | 201 |
| 王二 | male | 201 |
| 麻子 | female | 202 |
| 小明 | male | 200 |
| NULL | NULL | 203 |
+--------+--------+------+
子查询
介绍
- 子查询是将一个查询语句嵌套在另一个查询语句中
- 内层查询语句的查询结果，可以为外层查询语句提供查询条件
- 子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
- 还可以包含比较运算符：= 、 !=、> 、<等
含in关键字
#查询平均年龄在25岁以上的部门名
mysql> select dep_id,avg(age) from employee group by dep_id;
+--------+----------+
| dep_id | avg(age) |
+--------+----------+
| 200 | 18.0000 |
| 201 | 43.0000 |
| 202 | 28.0000 |
| 204 | 18.0000 |
+--------+----------+
mysql> select dep_id,avg(age) from employee group by dep_id having avg(age)>25;
+--------+----------+
| dep_id | avg(age) |
+--------+----------+
| 201 | 43.0000 |
| 202 | 28.0000 |
+--------+----------+
mysql> select * from department where id in(select dep_id,avg(age) from employee group by dep_id haviing avg(age)>25);
ERROR 1241 (21000): Operand should contain 1 column(s)
报错：ERROR 1241 (21000): Operand should contain 1 column(s)
原因：子查询中的select后面只能跟一个字段
解决：如下
mysql> select * from department where id in(select dep_id from employee group by dep_id having avg(age)>25);
+------+--------------+
| id | name |
+------+--------------+
| 201 | 人力资源 |
| 202 | 销售 |
+------+--------------+
#查看暂无职员的部门名
mysql> select id from department;
+------+
| id |
+------+
| 200 |
| 201 |
| 202 |
| 203 |
+------+
mysql> select * from employee where dep_id not in (select id from department);
+----+--------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+--------+--------+------+--------+
| 6 | 小红 | female | 18 | 204 |
+----+--------+--------+------+--------+
含exists关键字
- 子查询条件为真，执行父查询
- 子查询条件为假，不执行父查询
#department表中存在dept_id=200，条件为真
mysql> select * from employee where exists (select id from department where id=200);
+----+--------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+--------+--------+------+--------+
| 1 | 张三 | male | 18 | 200 |
| 2 | 李四 | female | 48 | 201 |
| 3 | 王二 | male | 38 | 201 |
| 4 | 麻子 | female | 28 | 202 |
| 5 | 小明 | male | 18 | 200 |
| 6 | 小红 | female | 18 | 204 |
+----+--------+--------+------+--------+
#department表中存在dept_id=205，条件为假
mysql> select * from employee where exists (select id from department where id=205);
Empty set (0.00 sec)
含比较运算符
#查询大于所有人平均年龄的员工名与年龄
mysql> select avg(age) from employee;
+----------+
| avg(age) |
+----------+
| 28.0000 |
+----------+
mysql> select name,age from employee where age > (select avg(age) from employee);
+--------+------+
| name | age |
+--------+------+
| 李四 | 48 |
| 王二 | 38 |
+--------+------+
#查询大于部门内平均年龄的员工名、年龄
#查询各部门平均年龄
mysql> select dep_id,avg(age) from employee group by dep_id;
+--------+----------+
| dep_id | avg(age) |
+--------+----------+
| 200 | 18.0000 |
| 201 | 43.0000 |
| 202 | 28.0000 |
| 204 | 18.0000 |
+--------+----------+
#用于分组后，上述子句出现两个字段，不能直接嵌套，需要连表
mysql> select * from employee t1 cross join (select dep_id,avg(age) from employee group by dep_id) t2;
+----+--------+--------+------+--------+--------+----------+
| id | name | sex | age | dep_id | dep_id | avg(age) |
+----+--------+--------+------+--------+--------+----------+
| 1 | 张三 | male | 18 | 200 | 200 | 18.0000 |
| 2 | 李四 | female | 48 | 201 | 200 | 18.0000 |
| 3 | 王二 | male | 38 | 201 | 200 | 18.0000 |
| 4 | 麻子 | female | 28 | 202 | 200 | 18.0000 |
| 5 | 小明 | male | 18 | 200 | 200 | 18.0000 |
| 6 | 小红 | female | 18 | 204 | 200 | 18.0000 |
| 1 | 张三 | male | 18 | 200 | 201 | 43.0000 |
| 2 | 李四 | female | 48 | 201 | 201 | 43.0000 |
| 3 | 王二 | male | 38 | 201 | 201 | 43.0000 |
| 4 | 麻子 | female | 28 | 202 | 201 | 43.0000 |
| 5 | 小明 | male | 18 | 200 | 201 | 43.0000 |
| 6 | 小红 | female | 18 | 204 | 201 | 43.0000 |
| 1 | 张三 | male | 18 | 200 | 202 | 28.0000 |
| 2 | 李四 | female | 48 | 201 | 202 | 28.0000 |
| 3 | 王二 | male | 38 | 201 | 202 | 28.0000 |
| 4 | 麻子 | female | 28 | 202 | 202 | 28.0000 |
| 5 | 小明 | male | 18 | 200 | 202 | 28.0000 |
| 6 | 小红 | female | 18 | 204 | 202 | 28.0000 |
| 1 | 张三 | male | 18 | 200 | 204 | 18.0000 |
| 2 | 李四 | female | 48 | 201 | 204 | 18.0000 |
| 3 | 王二 | male | 38 | 201 | 204 | 18.0000 |
| 4 | 麻子 | female | 28 | 202 | 204 | 18.0000 |
| 5 | 小明 | male | 18 | 200 | 204 | 18.0000 |
| 6 | 小红 | female | 18 | 204 | 204 | 18.0000 |
+----+--------+--------+------+--------+--------+----------+
#内连接
mysql> select * from employee t1 inner join (select dep_id,avg(age) from employee group by dep_id) t2 on t1.dep_id=t2.dep_id;
+----+--------+--------+------+--------+--------+----------+
| id | name | sex | age | dep_id | dep_id | avg(age) |
+----+--------+--------+------+--------+--------+----------+
| 1 | 张三 | male | 18 | 200 | 200 | 18.0000 |
| 2 | 李四 | female | 48 | 201 | 201 | 43.0000 |
| 3 | 王二 | male | 38 | 201 | 201 | 43.0000 |
| 4 | 麻子 | female | 28 | 202 | 202 | 28.0000 |
| 5 | 小明 | male | 18 | 200 | 200 | 18.0000 |
| 6 | 小红 | female | 18 | 204 | 204 | 18.0000 |
+----+--------+--------+------+--------+--------+----------+
#增加条件（大于部门内平均年龄）
mysql> select * from employee t1 inner join (select dep_id,avg(age) from employee group by dep_id) t2 on t1.dep_id=t2.dep_id where t1.age > t2.avg(age) ;
ERROR 1630 (42000): FUNCTION t2.avg does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
报错：ERROR 1630 (42000): FUNCTION t2.avg does not exist. Check the ‘Function Name Parsing and Resolution’ section in the Reference Manual
原因：最后的条件where t1.age > t2.avg(age)中 avg(age)没有被定义
解决：如下
mysql> select * from employee t1 inner join (select dep_id,avg(age) avg from employee group by dep_idd) t2 on t1.dep_id=t2.dep_id where t1.age > t2.avg;
+----+--------+--------+------+--------+--------+---------+
| id | name | sex | age | dep_id | dep_id | avg |
+----+--------+--------+------+--------+--------+---------+
| 2 | 李四 | female | 48 | 201 | 201 | 43.0000 |
+----+--------+--------+------+--------+--------+---------+
用户&权限管理
用户管理
创建用户
语法：
create user '用户名'@'被允许连接的主机名或主机的IP地址' identified by '用户密码';
创建用户张三，并指定密码为1
mysql> create user '张三'@'locahost' identified by '1';
设置root账号可以被某一网段连接（%表任意）
mysql> create user 'root'@'192.168.226.%' identified by '123';
设置root账号可以被所有人连接
mysql> create user 'root'@'%' identified by '123';
查看mysql用户（用户名和主机名，有一个不一致，都是不同用户）
mysql> select user,host from mysql.user;
+---------------+---------------+
| user | host |
+---------------+---------------+
| root | % |
| root | 192.168.226.% |
| 张三 | locahost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+---------------+
删除用户
- 使用drop user命令删除
mysql> drop user '张三'@'locahost';
- 删除mysql.user表中相关记录
mysql> delete from mysql.user where user='张三' and host='locahost';
mysql> flush privileges;
修改用户
- 使用rename user命令修改
mysql> rename user 'root'@'192.168.226.%' to 'root'@'172.1.1.%';
- 使用update命令修改
mysql> update mysql.user set user='root',host='172.1.1.%' where user='root' and host='192.168.226.%';
mysql> flush privileges;
权限管理
官方权限表https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html
授权
语法：
grant 权限 on *.* to '用户名'@'主机名/ip地址' [identified by '密码'];
说明：
1.添加identified by '密码'，表示创建账号的同时，进行授权
但在MySQL8.0中grant命令添加新特性,建用户和授权分开了，grant不再支持自动创建用户了，也不支持改密码，授权之前，必须要提前创建用户
2.权限作用范围
*.* 针对所有数据库里的所有表
db1.* 针对db1数据库里的所有表
db1.tb1 针对db1数据库里的表tb1
3.常用权限
ALL: SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
ALL : 以上所有权限，一般是普通管理员拥有的
with grant option：超级管理员才具备的，给别的用户授权的功能
- 案例
给'root'@'%'账号分配db1数据库的查询权限
mysql> grant select on db1.* to 'root'@'%';
给'root'@'%'账号分配db1数据库中employee表的age字段的修改权限
mysql> grant update(age) on db1.employee to 'root'@'%';
给'root'@'192.168.226.%'账号所有权限，并且可以下发权限
mysql> grant all on *.* to 'root'@'192.168.226.%' with grant option;
查询权限
查询当前用户的权限
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
查询其他用户权限
mysql> show grants for 'root'@'%';
+------------------------------------------------------+
| Grants for root@% |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'%' |
| GRANT SELECT ON `db1`.* TO 'root'@'%' |
| GRANT UPDATE (age) ON `db1`.`employee` TO 'root'@'%' |
+------------------------------------------------------+
回收权限
语法：
revoke 权限 on 库.表 from '用户名'@'主机名/ip地址';
mysql> revoke update(age) on db1.employee from 'root'@'%';
mysql> show grants for 'root'@'%';
+---------------------------------------+
| Grants for root@% |
+---------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'%' |
| GRANT SELECT ON `db1`.* TO 'root'@'%' |
+---------------------------------------+
mysql> flush privileges;
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
