系统数据库
• information_schema(虚拟库)
○ 用户表信息、列信息、权限信息、字符信息等
• performance_schema
○ 主要存储数据库服务器的性能参数
• mysql(授权库)
○ 主要存储系统用户的权限信息
• sys(优化库)
○ 主要存储数据库服务器的性能参数
• bgx(业务库)
○ 主要存放业务所需要的库和表1.数据库连接方式
使用mysql -u root -p可以连接数据库, 但这只是本地连接数据库的方式, 在生产很多情况下都是连接网络中某一个主机上的数据库
-P //指定连接远程数据库端口
-h //指定连接远程数据库地址
-u //指定连接远程数据库账户
-p //指定连接远程数据库密码
[root@sql ~]# mysql -h192.168.56.11 -P3306 -uroot -p
Enter password: 2.数据库基本操作
1.查看数据库版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.22 |
+-----------+
1 row in set (0.01 sec)2.创建数据库DDL
mysql> create database Bgx_edu;
Query OK, 1 row affected (0.00 sec)
//以分号结尾数据库名称严格区分大小写
数据库名称必须是唯一
数据库名称不允许使用数字
数据库名称不能使用关键字命名create select
3.查看当前的库内容
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| Bgx_edu |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
//执行命令不区分大小写
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| Bgx_edu |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)4.删除数据库
mysql> drop database Bgx_edu;
Query OK, 0 rows affected (0.07 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
//删除库下的表
mysql> drop table Bgx_edu.t1;5.查询某个库的表
//use进入对应库
mysql> use Bgx_edu;
Database changed
//列出当前库下面的表
mysql> show tables;
Empty set (0.00 sec)
//查询某个库下的表结构
mysql> desc mysql.slow_log;
+----------------+---------------------+------+-----+----------------------+--------------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+----------------------+--------------------------------+
| user_host | mediumtext | NO | | NULL | |
| query_time | time(6) | NO | | NULL | |
| lock_time | time(6) | NO | | NULL | |
| rows_sent | int(11) | NO | | NULL | |
| rows_examined | int(11) | NO | | NULL | |
| db | varchar(512) | NO | | NULL | |
| last_insert_id | int(11) | NO | | NULL | |
| insert_id | int(11) | NO | | NULL | |
| server_id | int(10) unsigned | NO | | NULL | |
| sql_text | mediumblob | NO | | NULL | |
| thread_id | bigint(21) unsigned | NO | | NULL | |
+----------------+---------------------+------+-----+----------------------+--------------------------------+
12 rows in set (0.00 sec)
//查看某张表的建表语句
mysql> show create table mysql.slow_log\G
*************************** 1. row ***************************
Table: slow_log
Create Table: CREATE TABLE `slow_log` (
`start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
`user_host` mediumtext NOT NULL,
`query_time` time(6) NOT NULL,
`lock_time` time(6) NOT NULL,
`rows_sent` int(11) NOT NULL,
`rows_examined` int(11) NOT NULL,
`db` varchar(512) NOT NULL,
`last_insert_id` int(11) NOT NULL,
`insert_id` int(11) NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`sql_text` mediumblob NOT NULL,
`thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.00 sec)3.数据库增删查改
在MySQL管理软件中, 可以通过SQL语句中的DML语言来实现数据的操作, 包括如下:
INSERT数据插入
UPDATE数据更新
DELETE数据删除
1.准备操作环境数据表
#创建数据文件
mysql> create database bgx;
mysql> use bgx;
mysql> create table t1(id int,
name varchar(10),
sex enum('man','gril'),
age int);
#查看表字段
mysql> desc t1;
+-------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| sex | enum('man','gril') | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+--------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)2.插入数据INSERT语句
#1.插入完整数据, 顺序插入: INSERT INTO 表名(字段1,字段2,字段n) VALUES (值1,值2,值 n);
mysql> insert into t1(id,name,sex,age) values ("1","bgx","man","18");
Query OK, 1 row affected (0.01 sec)
#1.插入完整数据, 推荐方式 INSERT INTO 表名 VALUES (值1,值2,值n);
mysql> insert into t1 values("2","bgx2","gril","10");
Query OK, 1 row affected (0.01 sec)
#2.指定字段插入, INSERT INTO 表名(字段2,字段3…) VALUES (值 2,值3…);
mysql> insert into t1(name,sex,age) values ("bgx","man","20");
Query OK, 1 row affected (0.00 sec)
#3.插入多条记录, INSERT INTO 表名 VALUES (值1,值2,值n),(值1,值2,值n);
mysql> insert into t1 values
("3","bgx3","man","18"),
("4","bgx4","man","18"),
("5","bgx5","man","18");
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+------+------+------+------+
| id | name | sex | age |
+------+------+------+------+
| 1 | bgx | man | 18 |
| 2 | bgx2 | gril | 10 |
| NULL | bgx | man | 20 |
| 3 | bgx3 | man | 18 |
| 4 | bgx4 | man | 18 |
| 5 | bgx5 | man | 18 |
+------+------+------+------+
6 rows in set (0.00 sec)3.更新数据UPDATE语句
语法: 更新 表 设置 字段1=值1, 字段2=值2, WHERE 条件;
1.查看需要修改的表的字段 desc
2.查询对用的字段 select
3.更新对应的表字段 update
4.添加对应的where条件,精准修改
示例1: 将t1表中, name字段等于bgx1的改为update_bgx
mysql> update t1 set name="update_bgx" where name="bgx1";
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from t1;
+------+------------+------+------+
| id | name | sex | age |
+------+------------+------+------+
| 1 | update_bgx | man | 18 |
| 2 | bgx2 | gril | 10 |
| NULL | update_bgx | man | 20 |
| 3 | bgx3 | man | 18 |
| 4 | bgx4 | man | 18 |
| 5 | bgx5 | man | 18 |
+------+------------+------+------+
6 rows in set (0.00 sec)示例2: 修改密码示例, 查看表字段内容
mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | *4927A5B79C852EA2CE585A7679C5C26DF683F18C |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
更新字段
mysql> update mysql.user set
authentication_string=password("Bgx123.com")
where user='root' and host='localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)4.删除数据DELETE
语法: DELETE FROM 表名 WHERE CONITION;
//删除字段包含update_bgx
mysql> delete from t1 where name="update_bgx";
Query OK, 2 rows affected (0.01 sec)
mysql> select * from t1;
+------+------+------+------+
| id | name | sex | age |
+------+------+------+------+
| 2 | bgx2 | gril | 10 |
| 3 | bgx3 | man | 18 |
| 4 | bgx4 | man | 18 |
| 5 | bgx5 | man | 18 |
+------+------+------+------+
4 rows in set (0.00 sec)
//清空表数据
mysql> truncate t1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
Empty set (0.00 sec)4.数据库查询语句
4.1单表查询
在学习查询前, 需要定义好对应数据进行查询
编号 id int
姓名 name varchar(30)
性别 sex enum
日期 time date
职位 post varchar(50)
描述 job varchar(100)
薪水 salary double(15,2)
部门编号 dep_id int
创建表
mysql> CREATE TABLE bgx.t2(
id int primary key AUTO_INCREMENT not null,
name varchar(30) not null,
sex enum('man','gril') default 'man' not null,
time date not null,
post varchar(50) not null,
job varchar(100),
salary double(15,2) not null,
office int,
dep_id int );插入数据
mysql> insert into bgx.t2(name,sex,time,post,job,salary,office,
dep_id) values
('jack','man','20180202','instructor','teach',5000,501,100),
('tom','man','20180203','instructor','teach',5500,501,100),
('robin','man','20180202','instructor','teach',8000,501,100),
('alice','gril','20180202','instructor','teach',7200,501,100),
('bgx','man','20180202','hr','hrcc',600,502,101),
('harry','man','20180202','hr', NULL,6000,502,101),
('trf','gril','20180206','sale','salecc',20000,503,102),
('test','gril','20180205','sale','salecc',2200,503,102),
('dog','man','20180205','sale', NULL,2200,503,102),
('alex','man','20180205','sale','',2200,503,102);1.简单查询
//查看表字段与表信息
mysql> desc t2;
+--------+--------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| sex | enum('man','gril') | NO | | man | |
| time | date | NO | | NULL | |
| post | varchar(50) | NO | | NULL | |
| job | varchar(100) | YES | | NULL | |
| salary | double(15,2) | NO | | NULL | |
| office | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+--------+--------------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)1.查询所有数据
mysql> select * from t2;
+----+-------+------+------------+------------+--------+----------+--------+--------+
| id | name | sex | time | post | job | salary | office | dep_id |
+----+-------+------+------------+------------+--------+----------+--------+--------+
| 1 | jack | man | 2018-02-02 | instructor | teach | 5000.00 | 501 | 100 |
| 2 | tom | man | 2018-02-03 | instructor | teach | 5500.00 | 501 | 100 |
| 3 | robin | man | 2018-02-02 | instructor | teach | 8000.00 | 501 | 100 |
| 4 | alice | gril | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 |
| 5 | bgx | man | 2018-02-02 | hr | hrcc | 600.00 | 502 | 101 |
| 6 | harry | man | 2018-02-02 | hr | NULL | 6000.00 | 502 | 101 |
| 7 | trf | gril | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 |
| 8 | test | gril | 2018-02-05 | sale | salecc | 2200.00 | 503 | 102 |
| 9 | dog | man | 2018-02-05 | sale | NULL | 2200.00 | 503 | 102 |
| 10 | alex | man | 2018-02-05 | sale | | 2200.00 | 503 | 102 |
+----+-------+------+------------+------------+--------+----------+--------+--------+
10 rows in set (0.00 sec)2.指定字段查询
mysql> select name,salary,dep_id from t2;
+-------+----------+--------+
| name | salary | dep_id |
+-------+----------+--------+
| jack | 5000.00 | 100 |
| tom | 5500.00 | 100 |
| robin | 8000.00 | 100 |
| alice | 7200.00 | 100 |
| bgx | 600.00 | 101 |
| harry | 6000.00 | 101 |
| trf | 20000.00 | 102 |
| test | 2200.00 | 102 |
| dog | 2200.00 | 102 |
| alex | 2200.00 | 102 |
+-------+----------+--------+
10 rows in set (0.00 sec)3.避免重复查询字段distinct
mysql> select post from t2;
+------------+
| post |
+------------+
| instructor |
| instructor |
| instructor |
| instructor |
| hr |
| hr |
| sale |
| sale |
| sale |
| sale |
+------------+
10 rows in set (0.00 sec)
mysql> select distinct post from t2;
+------------+
| post |
+------------+
| instructor |
| hr |
| sale |
+------------+
3 rows in set (0.00 sec)4.通过四则运算查询, 计算每个人的年薪
mysql> select name,salary,salary*14 from t2;
+-------+----------+-----------+
| name | salary | salary*14 |
+-------+----------+-----------+
| jack | 5000.00 | 70000.00 |
| tom | 5500.00 | 77000.00 |
| robin | 8000.00 | 112000.00 |
| alice | 7200.00 | 100800.00 |
| bgx | 600.00 | 8400.00 |
| harry | 6000.00 | 84000.00 |
| trf | 20000.00 | 280000.00 |
| test | 2200.00 | 30800.00 |
| dog | 2200.00 | 30800.00 |
| alex | 2200.00 | 30800.00 |
+-------+----------+-----------+
10 rows in set (0.00 sec)
#计算年薪并定义输出字段信息别名, AS可去掉
mysql> select name,salary,salary*14 AS Annual_salary from t2;
+-------+----------+---------------+
| name | salary | Annual_salary |
+-------+----------+---------------+
| jack | 5000.00 | 70000.00 |
| tom | 5500.00 | 77000.00 |
| robin | 8000.00 | 112000.00 |
| alice | 7200.00 | 100800.00 |
| bgx | 600.00 | 8400.00 |
| harry | 6000.00 | 84000.00 |
| trf | 20000.00 | 280000.00 |
| test | 2200.00 | 30800.00 |
| dog | 2200.00 | 30800.00 |
| alex | 2200.00 | 30800.00 |
+-------+----------+---------------+
10 rows in set (0.01 sec)5.定义显示格式 CONCAT() 函数用于连接字符串
mysql> select concat(name,'annual salary:',salary*14) from t2;
+-----------------------------------------+
| concat(name,'annual salary:',salary*14) |
+-----------------------------------------+
| jackannual salary:70000.00 |
| tomannual salary:77000.00 |
| robinannual salary:112000.00 |
| aliceannual salary:100800.00 |
| bgxannual salary:8400.00 |
| harryannual salary:84000.00 |
| trfannual salary:280000.00 |
| testannual salary:30800.00 |
| dogannual salary:30800.00 |
| alexannual salary:30800.00 |
+-----------------------------------------+
10 rows in set (0.00 sec)2.单条件查询
单条件查询
多条件查询
关键字 BETWEEN AND
关键字 IS NULL
关键字 IN 集合查询
关键字 LIKE 模糊查询
1.单条件查询
mysql> select name,post from t2 where post='hr';
+-------+------+
| name | post |
+-------+------+
| bgx | hr |
| harry | hr |
+-------+------+
2 rows in set (0.00 sec)2.多条件查询
mysql> select name,post,salary from t2 where post='hr' and salary >5000;
+-------+------+---------+
| name | post | salary |
+-------+------+---------+
| harry | hr | 6000.00 |
+-------+------+---------+
1 row in set (0.00 sec)3.查找薪资范围在8000-2000,使用BETWEEN区间
mysql> select name,salary from t2 where salary between 8000 and 20000;
+-------+----------+
| name | salary |
+-------+----------+
| robin | 8000.00 |
| trf | 20000.00 |
+-------+----------+
2 rows in set (0.00 sec)4.查找部门为Null, 没有部门的员工
mysql> select name,job from t2 where job is null;
+-------+------+
| name | job |
+-------+------+
| harry | NULL |
| dog | NULL |
+-------+------+
2 rows in set (0.00 sec)
#查找有部门的员工
mysql> select name,job from t2 where job is not null;
+-------+--------+
| name | job |
+-------+--------+
| jack | teach |
| tom | teach |
| robin | teach |
| alice | teach |
| bgx | hrcc |
| trf | salecc |
| test | salecc |
| alex | |
+-------+--------+
8 rows in set (0.00 sec)
#查看部门为空的员工
mysql> select name,job from t2 where job='';
+------+------+
| name | job |
+------+------+
| alex | |
+------+------+
1 row in set (0.00 sec)5.集合查询
mysql> select name,salary from t2 where salary=4000 OR salary=5000 OR salary=8000;
mysql> select name,salary from t2 where salary in(4000,5000,8000);
+-------+---------+
| name | salary |
+-------+---------+
| jack | 5000.00 |
| robin | 8000.00 |
+-------+---------+
2 rows in set (0.01 sec)6.模糊查询like, 通配符%
mysql> select * from t2 where name like 'al%';
+----+-------+------+------------+------------+-------+---------+--------+--------+
| id | name | sex | time | post | job | salary | office | dep_id |
+----+-------+------+------------+------------+-------+---------+--------+--------+
| 4 | alice | gril | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 |
| 10 | alex | man | 2018-02-05 | sale | | 2200.00 | 503 | 102 |
+----+-------+------+------------+------------+-------+---------+--------+--------+
2 rows in set (0.00 sec)
#通配符__
mysql> select * from t2 where name like 'al__';
+----+------+-----+------------+------+------+---------+--------+--------+
| id | name | sex | time | post | job | salary | office | dep_id |
+----+------+-----+------------+------+------+---------+--------+--------+
| 10 | alex | man | 2018-02-05 | sale | | 2200.00 | 503 | 102 |
+----+------+-----+------------+------+------+---------+--------+--------+
1 row in set (0.00 sec)3.查询排序
单列排序
多列排序
#1.按单列排序, 按薪水从低到高排序, 默认ASC
mysql> select * from t2 ORDER BY salary ASC;
+----+-------+------+------------+------------+--------+----------+--------+--------+
| id | name | sex | time | post | job | salary | office | dep_id |
+----+-------+------+------------+------------+--------+----------+--------+--------+
| 5 | bgx | man | 2018-02-02 | hr | hrcc | 600.00 | 502 | 101 |
| 8 | test | gril | 2018-02-05 | sale | salecc | 2200.00 | 503 | 102 |
| 9 | dog | man | 2018-02-05 | sale | NULL | 2200.00 | 503 | 102 |
| 10 | alex | man | 2018-02-05 | sale | | 2200.00 | 503 | 102 |
| 1 | jack | man | 2018-02-02 | instructor | teach | 5000.00 | 501 | 100 |
| 2 | tom | man | 2018-02-03 | instructor | teach | 5500.00 | 501 | 100 |
| 6 | harry | man | 2018-02-02 | hr | NULL | 6000.00 | 502 | 101 |
| 4 | alice | gril | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 |
| 3 | robin | man | 2018-02-02 | instructor | teach | 8000.00 | 501 | 100 |
| 7 | trf | gril | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 |
+----+-------+------+------------+------------+--------+----------+--------+--------+
10 rows in set (0.00 sec)
#1.按单列排序, 薪水从低往高排序, DESC倒序
mysql> select * from t2 ORDER BY salary DESC;
+----+-------+------+------------+------------+--------+----------+--------+--------+
| id | name | sex | time | post | job | salary | office | dep_id |
+----+-------+------+------------+------------+--------+----------+--------+--------+
| 7 | trf | gril | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 |
| 3 | robin | man | 2018-02-02 | instructor | teach | 8000.00 | 501 | 100 |
| 4 | alice | gril | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 |
| 6 | harry | man | 2018-02-02 | hr | NULL | 6000.00 | 502 | 101 |
| 2 | tom | man | 2018-02-03 | instructor | teach | 5500.00 | 501 | 100 |
| 1 | jack | man | 2018-02-02 | instructor | teach | 5000.00 | 501 | 100 |
| 8 | test | gril | 2018-02-05 | sale | salecc | 2200.00 | 503 | 102 |
| 9 | dog | man | 2018-02-05 | sale | NULL | 2200.00 | 503 | 102 |
| 10 | alex | man | 2018-02-05 | sale | | 2200.00 | 503 | 102 |
| 5 | bgx | man | 2018-02-02 | hr | hrcc | 600.00 | 502 | 101 |
+----+-------+------+------------+------------+--------+----------+--------+--------+
10 rows in set (0.00 sec)
#2.多列排序, 先按入职时间，再按薪水排序
mysql> select * from t2 ORDER BY time DESC, salary ASC;
+----+-------+------+------------+------------+--------+----------+--------+--------+
| id | name | sex | time | post | job | salary | office | dep_id |
+----+-------+------+------------+------------+--------+----------+--------+--------+
| 7 | trf | gril | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 |
| 8 | test | gril | 2018-02-05 | sale | salecc | 2200.00 | 503 | 102 |
| 9 | dog | man | 2018-02-05 | sale | NULL | 2200.00 | 503 | 102 |
| 10 | alex | man | 2018-02-05 | sale | | 2200.00 | 503 | 102 |
| 2 | tom | man | 2018-02-03 | instructor | teach | 5500.00 | 501 | 100 |
| 5 | bgx | man | 2018-02-02 | hr | hrcc | 600.00 | 502 | 101 |
| 1 | jack | man | 2018-02-02 | instructor | teach | 5000.00 | 501 | 100 |
| 6 | harry | man | 2018-02-02 | hr | NULL | 6000.00 | 502 | 101 |
| 4 | alice | gril | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 |
| 3 | robin | man | 2018-02-02 | instructor | teach | 8000.00 | 501 | 100 |
+----+-------+------+------------+------------+--------+----------+--------+--------+
10 rows in set (0.00 sec)
#2.多列排序, 先按职位， 再按薪水排序
mysql> select * from t2 ORDER BY post, salary DESC;
+----+-------+------+------------+------------+--------+----------+--------+--------+
| id | name | sex | time | post | job | salary | office | dep_id |
+----+-------+------+------------+------------+--------+----------+--------+--------+
| 6 | harry | man | 2018-02-02 | hr | NULL | 6000.00 | 502 | 101 |
| 5 | bgx | man | 2018-02-02 | hr | hrcc | 600.00 | 502 | 101 |
| 3 | robin | man | 2018-02-02 | instructor | teach | 8000.00 | 501 | 100 |
| 4 | alice | gril | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 |
| 2 | tom | man | 2018-02-03 | instructor | teach | 5500.00 | 501 | 100 |
| 1 | jack | man | 2018-02-02 | instructor | teach | 5000.00 | 501 | 100 |
| 7 | trf | gril | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 |
| 8 | test | gril | 2018-02-05 | sale | salecc | 2200.00 | 503 | 102 |
| 9 | dog | man | 2018-02-05 | sale | NULL | 2200.00 | 503 | 102 |
| 10 | alex | man | 2018-02-05 | sale | | 2200.00 | 503 | 102 |
+----+-------+------+------------+------------+--------+----------+--------+--------+
10 rows in set (0.00 sec)4.限制查询的记录数
#查询薪资最高前5名同事, 默认初始位置为0
mysql> select * from t2 ORDER BY salary DESC limit 5;
+----+-------+------+------------+------------+--------+----------+--------+--------+
| id | name | sex | time | post | job | salary | office | dep_id |
+----+-------+------+------------+------------+--------+----------+--------+--------+
| 7 | trf | gril | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 |
| 3 | robin | man | 2018-02-02 | instructor | teach | 8000.00 | 501 | 100 |
| 4 | alice | gril | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 |
| 6 | harry | man | 2018-02-02 | hr | NULL | 6000.00 | 502 | 101 |
| 2 | tom | man | 2018-02-03 | instructor | teach | 5500.00 | 501 | 100 |
+----+-------+------+------------+------------+--------+----------+--------+--------+
5 rows in set (0.00 sec)
#从第4条开始, 并显示5条数据
mysql> select * from t2 ORDER BY salary DESC limit 3,5;5.使用集合函数查询
#统计当前表总共多少条数据
mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
#统计dep_id为101有多少条数据
mysql> select count(*) from t2 where dep_id=101;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
#薪水最高
mysql> select MAX(salary) from t2;
+-------------+
| MAX(salary) |
+-------------+
| 20000.00 |
+-------------+
1 row in set (0.00 sec)
#薪水最低
mysql> select min(salary) from t2;
+-------------+
| min(salary) |
+-------------+
| 600.00 |
+-------------+
1 row in set (0.00 sec)
#平均薪水
mysql> select avg(salary) from t2;
+-------------+
| avg(salary) |
+-------------+
| 5890.000000 |
+-------------+
1 row in set (0.00 sec)
#总共发放多少薪水
mysql> select sum(salary) from t2;
+-------------+
| sum(salary) |
+-------------+
| 58900.00 |
+-------------+
1 row in set (0.00 sec)
#hr部门发放多少薪水
mysql> select sum(salary) from t2 where post='hr';
+-------------+
| sum(salary) |
+-------------+
| 6600.00 |
+-------------+
1 row in set (0.00 sec)
#哪个部门哪个人薪水最高
mysql> select * from t2 where salary=(select max(salary) from t2);
+----+------+------+------------+------+--------+----------+--------+--------+
| id | name | sex | time | post | job | salary | office | dep_id |
+----+------+------+------------+------+--------+----------+--------+--------+
| 7 | trf | gril | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 |
+----+------+------+------------+------+--------+----------+--------+--------+
1 row in set (0.01 sec)6.分组查询
# GROUP BY 和 GROUP_CONCAT()函数一起使用
mysql> select post,GROUP_CONCAT(name) from t2 GROUP BY post;
+------------+----------------------+
| post | GROUP_CONCAT(name) |
+------------+----------------------+
| hr | bgx,harry |
| instructor | jack,tom,robin,alice |
| sale | trf,test,dog,alex |
+------------+----------------------+
3 rows in set (0.00 sec)
mysql> select post,GROUP_CONCAT(name) AS Group_Post from t2 GROUP BY post;
+------------+----------------------+
| post | Group_Post |
+------------+----------------------+
| hr | bgx,harry |
| instructor | jack,tom,robin,alice |
| sale | trf,test,dog,alex |
+------------+----------------------+
3 rows in set (0.00 sec)
#GROUP BY 和集合函数一起使用
mysql> select post,sum(salary) from t2 GROUP BY post;
+------------+-------------+
| post | sum(salary) |
+------------+-------------+
| hr | 6600.00 |
| instructor | 25700.00 |
| sale | 26600.00 |
+------------+-------------+
3 rows in set (0.00 sec)7.使用正则表达式查询
mysql> select * from t2 where name REGEXP ‘^ali’;
+----+-------+------+------------+------------+-------+---------+--------+--------+
| id | name | sex | time | post | job | salary | office | dep_id |
+----+-------+------+------------+------------+-------+---------+--------+--------+
| 4 | alice | gril | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 |
+----+-------+------+------------+------------+-------+---------+--------+--------+
1 row in set (0.00 sec)
mysql> select * from t2 where name REGEXP 'gx$';
+----+------+-----+------------+------+------+--------+--------+--------+
| id | name | sex | time | post | job | salary | office | dep_id |
+----+------+-----+------------+------+------+--------+--------+--------+
| 5 | bgx | man | 2018-02-02 | hr | hrcc | 600.00 | 502 | 101 |
+----+------+-----+------------+------+------+--------+--------+--------+
1 row in set (0.00 sec)对字符串匹配方式
WHERE name = ‘trf’;
WHERE name LIKE ‘ha%’;
WHERE name REGEXP ‘gx$’;
4.2多表查询
多表连接查询
复合条件连接查询
子查询
准备2张数据表
#准备表1
mysql> create table bgx.t3(
id int auto_increment primary key not null,
name varchar(50),
age int,
dep_id int
);
#为表1插入数据
mysql> insert into t3(name,age,dep_id) values
('bgx',18,200),
('tom',26,201),
('jack',30,201),
('alice',24,202),
('robin',40,'200'),
('natasha',28,204);
mysql> select * from t3;
+----+---------+------+--------+
| id | name | age | dep_id |
+----+---------+------+--------+
| 1 | bgx | 18 | 200 |
| 2 | tom | 26 | 201 |
| 3 | jack | 30 | 201 |
| 4 | alice | 24 | 202 |
| 5 | robin | 40 | 200 |
| 6 | natasha | 28 | 204 |
+----+---------+------+--------+
6 rows in set (0.00 sec)
#准备表2
mysql> create table t4(
dep_id int,
dept_name varchar(100)
);
#为表2插入数据
mysql> insert into t4 values
(200,'hr'),
(201,'it'),
(202,'xs'),
(203,'cw');
mysql> select * from t4;
+--------+-----------+
| dep_id | dept_name |
+--------+-----------+
| 200 | hr |
| 201 | it |
| 202 | xs |
| 203 | cw |
+--------+-----------+
4 rows in set (0.00 sec)1.交叉连接, 不使用任何匹配条件
ysql> select t3.name,t3.age,t3.dep_id,t4.dept_name from t3,t4;
+---------+------+--------+-----------+
| name | age | dep_id | dept_name |
+---------+------+--------+-----------+
| bgx | 18 | 200 | hr |
| bgx | 18 | 200 | it |
| bgx | 18 | 200 | xs |
| bgx | 18 | 200 | cw |
| tom | 26 | 201 | hr |
| tom | 26 | 201 | it |
| tom | 26 | 201 | xs |
| tom | 26 | 201 | cw |
| jack | 30 | 201 | hr |
| jack | 30 | 201 | it |
| jack | 30 | 201 | xs |
| jack | 30 | 201 | cw |
| alice | 24 | 202 | hr |
| alice | 24 | 202 | it |
| alice | 24 | 202 | xs |
| alice | 24 | 202 | cw |
| robin | 40 | 200 | hr |
| robin | 40 | 200 | it |
| robin | 40 | 200 | xs |
| robin | 40 | 200 | cw |
| natasha | 28 | 204 | hr |
| natasha | 28 | 204 | it |
| natasha | 28 | 204 | xs |
| natasha | 28 | 204 | cw |
+---------+------+--------+-----------+
24 rows in set (0.00 sec)2.内连接, 只连接匹配的行
#只找出有部门的员工, (部门表中没有natasha所在的部门)
mysql> select t3.id,t3.name,t3.age,t4.dep_id,t4.dept_name from t3,t4
where t3.dep_id=t4.dep_id;
+----+-------+------+--------+-----------+
| id | name | age | dep_id | dept_name |
+----+-------+------+--------+-----------+
| 1 | bgx | 18 | 200 | hr |
| 2 | tom | 26 | 201 | it |
| 3 | jack | 30 | 201 | it |
| 4 | alice | 24 | 202 | xs |
| 5 | robin | 40 | 200 | hr |
+----+-------+------+--------+-----------+
5 rows in set (0.00 sec)3.外连接
SELECT 字段列表 FROM 表1 LEFT|RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;
#左连接
mysql> select id,name,t4.dept_name from t3 left join t4 on t3.dep_id = t4.dep_id;
+----+---------+-----------+
| id | name | dept_name |
+----+---------+-----------+
| 1 | bgx | hr |
| 5 | robin | hr |
| 2 | tom | it |
| 3 | jack | it |
| 4 | alice | xs |
| 6 | natasha | NULL |
+----+---------+-----------+
6 rows in set (0.00 sec)
#右连接
mysql> select id,name,t4.dept_name from t3 right join t4 on t3.dep_id = t4.dep_id;
+------+-------+-----------+
| id | name | dept_name |
+------+-------+-----------+
| 1 | bgx | hr |
| 2 | tom | it |
| 3 | jack | it |
| 4 | alice | xs |
| 5 | robin | hr |
| NULL | NULL | cw |
+------+-------+-----------+
6 rows in set (0.00 sec)4.符合条件连接查询
#1.以内连接的方式查询 t3和t4表, 找出公司所有部门中年龄大于25岁的员工
mysql> select t3.id,t3.name,t3.age,t4.dept_name
from t3,t4
where t3.dep_id = t4.dep_id
and age >25;
+----+-------+------+-----------+
| id | name | age | dept_name |
+----+-------+------+-----------+
| 5 | robin | 40 | hr |
| 2 | tom | 26 | it |
| 3 | jack | 30 | it |
+----+-------+------+-----------+
3 rows in set (0.01 sec)
#以内连接的方式查询 t3和t4表，并且以age字段降序显示
mysql> select t3.id,t3.name,t3.age,t4.dept_name
-> from t3,t4
-> where t3.dep_id = t4.dep_id
-> ORDER BY age DESC;
+----+-------+------+-----------+
| id | name | age | dept_name |
+----+-------+------+-----------+
| 5 | robin | 40 | hr |
| 3 | jack | 30 | it |
| 2 | tom | 26 | it |
| 4 | alice | 24 | xs |
| 1 | bgx | 18 | hr |
+----+-------+------+-----------+
5 rows in set (0.00 sec)4.子查询
子查询是将一个查询语句嵌套在另一个查询语句中。
内层查询语句的查询结果，可以为外层查询语句提供查询条件。
子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS 等关键字 还可以包含比较运算符：= 、 !=、> 、<等
#带 IN 关键字的子查询 查询t3表，但dept_id必须在t4表中出现过
mysql> select * from t3 where dep_id IN (select dep_id from t4);
+----+-------+------+--------+
| id | name | age | dep_id |
+----+-------+------+--------+
| 1 | bgx | 18 | 200 |
| 2 | tom | 26 | 201 |
| 3 | jack | 30 | 201 |
| 4 | alice | 24 | 202 |
| 5 | robin | 40 | 200 |
+----+-------+------+--------+
5 rows in set (0.00 sec)
#代表运算符子查询, 查询年龄大于等于 25 岁员工所在部门（查询老龄化的部门）
mysql> select dep_id,dept_name from t4
where dep_id IN
(select DISTINCT dep_id from t3 where age >=25);
+--------+-----------+
| dep_id | dept_name |
+--------+-----------+
| 201 | it |
| 200 | hr |
+--------+-----------+
2 rows in set (0.01 sec)
#子查询 EXISTS 关字键字表示存在。在使用 EXISTS 关键字时，内层查询语句不返回查询的记录，而是返回一个真假值。
#Ture 或 False，当返回 Ture 时，外层查询语句将进行查询；当返回值为 False 时，外层查询语 句不进行查询
#t4 表中存在 dep_id=203，Ture
mysql> select * from t3
where EXISTS (select * from t4 where dep_id=203);
+----+---------+------+--------+
| id | name | age | dep_id |
+----+---------+------+--------+
| 1 | bgx | 18 | 200 |
| 2 | tom | 26 | 201 |
| 3 | jack | 30 | 201 |
| 4 | alice | 24 | 202 |
| 5 | robin | 40 | 200 |
| 6 | natasha | 28 | 204 |
+----+---------+------+--------+
6 rows in set (0.00 sec)
mysql> select * from t3 where EXISTS (select * from t4 where dep_id=300);
Empty set (0.00 sec)
文档更新时间: 2018-10-29 14:59 作者：李延召
