系统数据库
• information_schema(虚拟库) ○ 用户表信息、列信息、权限信息、字符信息等 • performance_schema ○ 主要存储数据库服务器的性能参数 • mysql(授权库) ○ 主要存储系统用户的权限信息 • sys(优化库) ○ 主要存储数据库服务器的性能参数 • bgx(业务库) ○ 主要存放业务所需要的库和表
一、数据库的连接方式
使用mysql -u root -p可以连接数据库, 但这只是本地连接数据库的方式, 在生产很多情况下都是连接网络中某一个主机上的数据库
-P //指定连接远程数据库端口 -h //指定连接远程数据库地址 -u //指定连接远程数据库账户 -p //指定连接远程数据库密码
[root@liza ~]# mysql -h192.168.112.160 -p3306 -uroot -p mysql: [Warning] Using a password on the command line interface can be insecure. Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.7.32 MySQL Community Server (GPL) 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. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
二、数据库的基本操作
1、查看数据库版本
mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.32 | +-----------+ 1 row in set (0.00 sec)
2、创建数据库
mysql> create database bgx_edu default character set utf8; 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)
4、删除数据库
mysql> drop database bgx_edu; Query OK, 0 rows affected (0.01 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、查询某个库的表
mysql> use bgx_edu; Database changed mysql> show tables; +-------------------+ | Tables_in_bgx_edu | +-------------------+ | t1 | +-------------------+ 1 row in set (0.00 sec)
6、查看某张表的建表语句
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)
三、数据库增删查改
在MySQL管理软件中, 可以通过SQL语句中的DML语言来实现数据的操作, 包括如下:
- INSERT数据插入
- UPDATE数据更新
- DELETE数据删除
1、准备操作环境数据表
创建数据文件
mysql> create database bgx default character set utf8; Query OK, 1 row affected (0.00 sec) mysql> use bgx; Database changed mysql> create table t1 ( -> id int, -> name varchar(10), -> sex enum('man','woman'), -> age int -> ); Query OK, 0 rows affected (0.00 sec)
查看表字段
mysql> desc t1; +-------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | | sex | enum('man','woman') | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+---------------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)
2、插入数据INSERT语句
方法一：插入完整数据, 顺序插入: 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)
方法二：插入完整数据, 推荐方式: INSERT INTO 表名 VALUES (值1,值2,值n);
mysql> insert into t1 values("2","bgx2","woman","10"); Query OK, 1 row affected (0.00 sec)
方法三：指定字段插入：INSERT INTO 表名(字段2,字段3…) VALUES (值 2,值3…);
mysql> insert into t1 values -> ("3","bgx4","man","18"), -> ("4","bgx5","man","18"), -> ("5","bgx6","woman","26"); 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 | woman | 10 | | NULL | bgx3 | man | 20 | | 3 | bgx4 | man | 18 | | 4 | bgx5 | man | 18 | | 5 | bgx6 | woman | 26 | +------+------+-------+------+ 6 rows in set (0.00 sec)
3、更新数据UPDATE语句
语法: 更新 表 设置 字段1=值1, 字段2=值2, WHERE 条件;
- 查看需要修改的表的字段 desc
- 查询对用的字段 select
- 更新对应的表字段 update
- 添加对应的where条件,精准修改
示例1: 将t1表中, name字段等于bgx1的改为update_bgx
mysql> update t1 set name="update_bgx" where name="bgx"; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from t1; +------+------------+-------+------+ | id | name | sex | age | +------+------------+-------+------+ | 1 | update_bgx | man | 18 | | 2 | bgx2 | woman | 10 | | NULL | bgx3 | man | 20 | | 3 | bgx4 | man | 18 | | 4 | bgx5 | man | 18 | | 5 | bgx6 | woman | 26 | +------+------------+-------+------+ 6 rows in set (0.00 sec)
示例2: 修改密码示例, 查看表字段内容
mysql> select user,host,authentication_string from mysql.user; +---------------+-----------+-------------------------------------------+ | user | host | authentication_string | +---------------+-----------+-------------------------------------------+ | root | % | *5F2035C0E578A25296968F8A3F33B8D9EFE1C8F0 | | 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: 0 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, 1 row affected (0.00 sec) mysql> select * from t1; +------+------+-------+------+ | id | name | sex | age | +------+------+-------+------+ | 2 | bgx2 | woman | 10 | | NULL | bgx3 | man | 20 | | 3 | bgx4 | man | 18 | | 4 | bgx5 | man | 18 | | 5 | bgx6 | woman | 26 | +------+------+-------+------+ 5 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)
四、数据库查询语句
单表查询
在学习查询前, 需要定义好对应数据进行查询
编号 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','woman') 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 -> ); Query OK, 0 rows affected (0.00 sec)
插入数据
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','woman','20180202','instructor','teach',7200,501,100), ('bgx','man','20180202','hr','hrcc',600,502,101), ('harry','man','20180202','hr', NULL,6000,502,101), ('trf','woman','20180206','sale','salecc',20000,503,102), ('test','woman','20180205','sale','salecc',2200,503,102), ('dog','man','20180205','sale', NULL,2200,503,102), ('alex','man','20180205','sale','',2200,503,102); Query OK, 10 rows affected (0.00 sec) Records: 10 Duplicates: 0 Warnings: 0
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','woman') | 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)
查询所有数据
mysql> select * from t2; +----+-------+-------+------------+------------+--------+----------+--------+--------+ | id | name | sex | time | post | job | salary | office | dep_id | +----+-------+-------+------------+------------+--------+----------+--------+--------+ | 11 | jack | man | 2018-02-02 | instructor | teach | 5000.00 | 501 | 100 | | 12 | tom | man | 2018-02-03 | instructor | teach | 5500.00 | 501 | 100 | | 13 | robin | man | 2018-02-02 | instructor | teach | 8000.00 | 501 | 100 | | 14 | alice | woman | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 | | 15 | bgx | man | 2018-02-02 | hr | hrcc | 600.00 | 502 | 101 | | 16 | harry | man | 2018-02-02 | hr | NULL | 6000.00 | 502 | 101 | | 17 | trf | woman | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 | | 18 | test | woman | 2018-02-05 | sale | salecc | 2200.00 | 503 | 102 | | 19 | dog | man | 2018-02-05 | sale | NULL | 2200.00 | 503 | 102 | | 20 | alex | man | 2018-02-05 | sale | | 2200.00 | 503 | 102 | +----+-------+-------+------------+------------+--------+----------+--------+--------+ 10 rows in set (0.00 sec)
指定字段查询
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)
避免重复查询字段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)
通过四则运算查询, 计算每个人的年薪（14个月）
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) 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.00 sec)
定义显示格式 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 模糊查询
单条件查询
mysql> select name,post from t2 where post='hr'; +-------+------+ | name | post | +-------+------+ | bgx | hr | | harry | hr | +-------+------+ 2 rows in set (0.00 sec)
多条件查询
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)
查找薪资范围在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)
查找部门为Null
没有部门的员工 mysql> select name,job from t2 where job is null; +-------+------+ | name | job | +-------+------+ | harry | NULL | | dog | NULL | +-------+------+ 2 rows in set (0.01 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)
集合查询
mysql> select name,salary from t2 where salary=4000 OR salary=5000 OR salary=8000; +-------+---------+ | name | salary | +-------+---------+ | jack | 5000.00 | | robin | 8000.00 | +-------+---------+ 2 rows in set (0.00 sec) 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.00 sec)
模糊查询like, 通配符%
mysql> select * from t2 where name like 'al%'; +----+-------+-------+------------+------------+-------+---------+--------+--------+ | id | name | sex | time | post | job | salary | office | dep_id | +----+-------+-------+------------+------------+-------+---------+--------+--------+ | 14 | alice | woman | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 | | 20 | 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 | +----+------+-----+------------+------+------+---------+--------+--------+ | 20 | alex | man | 2018-02-05 | sale | | 2200.00 | 503 | 102 | +----+------+-----+------------+------+------+---------+--------+--------+ 1 row in set (0.00 sec)
3、查询排序
- 单列排序
- 多列排序
按单列排序, 按薪水从低到高排序, 默认ASC
mysql> select * from t2 ORDER BY salary ASC -> ; +----+-------+-------+------------+------------+--------+----------+--------+--------+ | id | name | sex | time | post | job | salary | office | dep_id | +----+-------+-------+------------+------------+--------+----------+--------+--------+ | 15 | bgx | man | 2018-02-02 | hr | hrcc | 600.00 | 502 | 101 | | 18 | test | woman | 2018-02-05 | sale | salecc | 2200.00 | 503 | 102 | | 19 | dog | man | 2018-02-05 | sale | NULL | 2200.00 | 503 | 102 | | 20 | alex | man | 2018-02-05 | sale | | 2200.00 | 503 | 102 | | 11 | jack | man | 2018-02-02 | instructor | teach | 5000.00 | 501 | 100 | | 12 | tom | man | 2018-02-03 | instructor | teach | 5500.00 | 501 | 100 | | 16 | harry | man | 2018-02-02 | hr | NULL | 6000.00 | 502 | 101 | | 14 | alice | woman | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 | | 13 | robin | man | 2018-02-02 | instructor | teach | 8000.00 | 501 | 100 | | 17 | trf | woman | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 | +----+-------+-------+------------+------------+--------+----------+--------+--------+ 10 rows in set (0.00 sec)
按单列排序, 薪水从低往高排序, DESC倒序
mysql> select * from t2 ORDER BY salary DESC; +----+-------+-------+------------+------------+--------+----------+--------+--------+ | id | name | sex | time | post | job | salary | office | dep_id | +----+-------+-------+------------+------------+--------+----------+--------+--------+ | 17 | trf | woman | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 | | 13 | robin | man | 2018-02-02 | instructor | teach | 8000.00 | 501 | 100 | | 14 | alice | woman | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 | | 16 | harry | man | 2018-02-02 | hr | NULL | 6000.00 | 502 | 101 | | 12 | tom | man | 2018-02-03 | instructor | teach | 5500.00 | 501 | 100 | | 11 | jack | man | 2018-02-02 | instructor | teach | 5000.00 | 501 | 100 | | 18 | test | woman | 2018-02-05 | sale | salecc | 2200.00 | 503 | 102 | | 19 | dog | man | 2018-02-05 | sale | NULL | 2200.00 | 503 | 102 | | 20 | alex | man | 2018-02-05 | sale | | 2200.00 | 503 | 102 | | 15 | bgx | man | 2018-02-02 | hr | hrcc | 600.00 | 502 | 101 | +----+-------+-------+------------+------------+--------+----------+--------+--------+ 10 rows in set (0.00 sec)
多列排序, 先按入职时间，再按薪水排序
mysql> select * from t2 ORDER BY time DESC,salary ASC; +----+-------+-------+------------+------------+--------+----------+--------+--------+ | id | name | sex | time | post | job | salary | office | dep_id | +----+-------+-------+------------+------------+--------+----------+--------+--------+ | 17 | trf | woman | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 | | 18 | test | woman | 2018-02-05 | sale | salecc | 2200.00 | 503 | 102 | | 19 | dog | man | 2018-02-05 | sale | NULL | 2200.00 | 503 | 102 | | 20 | alex | man | 2018-02-05 | sale | | 2200.00 | 503 | 102 | | 12 | tom | man | 2018-02-03 | instructor | teach | 5500.00 | 501 | 100 | | 15 | bgx | man | 2018-02-02 | hr | hrcc | 600.00 | 502 | 101 | | 11 | jack | man | 2018-02-02 | instructor | teach | 5000.00 | 501 | 100 | | 16 | harry | man | 2018-02-02 | hr | NULL | 6000.00 | 502 | 101 | | 14 | alice | woman | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 | | 13 | robin | man | 2018-02-02 | instructor | teach | 8000.00 | 501 | 100 | +----+-------+-------+------------+------------+--------+----------+--------+--------+ 10 rows in set (0.00 sec)
多列排序, 先按职位， 再按薪水排序
mysql> select * from t2 ORDER BY post,salary DESC; +----+-------+-------+------------+------------+--------+----------+--------+--------+ | id | name | sex | time | post | job | salary | office | dep_id | +----+-------+-------+------------+------------+--------+----------+--------+--------+ | 16 | harry | man | 2018-02-02 | hr | NULL | 6000.00 | 502 | 101 | | 15 | bgx | man | 2018-02-02 | hr | hrcc | 600.00 | 502 | 101 | | 13 | robin | man | 2018-02-02 | instructor | teach | 8000.00 | 501 | 100 | | 14 | alice | woman | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 | | 12 | tom | man | 2018-02-03 | instructor | teach | 5500.00 | 501 | 100 | | 11 | jack | man | 2018-02-02 | instructor | teach | 5000.00 | 501 | 100 | | 17 | trf | woman | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 | | 18 | test | woman | 2018-02-05 | sale | salecc | 2200.00 | 503 | 102 | | 19 | dog | man | 2018-02-05 | sale | NULL | 2200.00 | 503 | 102 | | 20 | 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 | +----+-------+-------+------------+------------+--------+----------+--------+--------+ | 17 | trf | woman | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 | | 13 | robin | man | 2018-02-02 | instructor | teach | 8000.00 | 501 | 100 | | 14 | alice | woman | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 | | 16 | harry | man | 2018-02-02 | hr | NULL | 6000.00 | 502 | 101 | | 12 | 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; +----+-------+-------+------------+------------+--------+---------+--------+--------+ | id | name | sex | time | post | job | salary | office | dep_id | +----+-------+-------+------------+------------+--------+---------+--------+--------+ | 16 | harry | man | 2018-02-02 | hr | NULL | 6000.00 | 502 | 101 | | 12 | tom | man | 2018-02-03 | instructor | teach | 5500.00 | 501 | 100 | | 11 | jack | man | 2018-02-02 | instructor | teach | 5000.00 | 501 | 100 | | 20 | alex | man | 2018-02-05 | sale | | 2200.00 | 503 | 102 | | 18 | test | woman | 2018-02-05 | sale | salecc | 2200.00 | 503 | 102 | +----+-------+-------+------------+------------+--------+---------+--------+--------+ 5 rows in set (0.00 sec)
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 | +----+------+-------+------------+------+--------+----------+--------+--------+ | 17 | trf | woman | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 | +----+------+-------+------------+------+--------+----------+--------+--------+ 1 row in set (0.00 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)
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、使用正则表达式查询
对字符串匹配方式
- WHERE name = ‘trf’;
- WHERE name LIKE ‘ha%’;
- WHERE name REGEXP ‘gx$’;
mysql> select * from t2 where name REGEXP '^ali'; +----+-------+-------+------------+------------+-------+---------+--------+--------+ | id | name | sex | time | post | job | salary | office | dep_id | +----+-------+-------+------------+------------+-------+---------+--------+--------+ | 14 | alice | woman | 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 | +----+------+-----+------------+------+------+--------+--------+--------+ | 15 | bgx | man | 2018-02-02 | hr | hrcc | 600.00 | 502 | 101 | +----+------+-----+------------+------+------+--------+--------+--------+ 1 row in set (0.00 sec)
多表查询
- 多表连接查询
- 复合条件连接查询
- 子查询
- 准备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); Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0 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) -> ); Query OK, 0 rows affected (0.01 sec)
为表2插入数据
mysql> insert into t4 values -> (200,'hr'), -> (201,'it'), -> (202,'xs'), -> (203,'cw'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from t4; +--------+-----------+ | dep_id | dept_name | +--------+-----------+ | 200 | hr | | 201 | it | | 202 | xs | | 203 | cw | +--------+-----------+ 4 rows in set (0.00 sec)
1、交叉连接, 不使用任何匹配条件
mysql> 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.01 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、符合条件连接查询
以内连接的方式查询 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)
子查询
- 子查询是将一个查询语句嵌套在另一个查询语句中。
- 内层查询语句的查询结果，可以为外层查询语句提供查询条件。
- 子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字还可以包含比较运算符：=、 !=、> 、<等
1、带 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)
2、代表运算符子查询
查询年龄大于等于 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.00 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)
