文章目录
使用SQL命令管理数据库：
1.数据库管理命令：
查看数据库：MariaDB [(none)]> show databases;
查看当前库：MariaDB [(none)]> select database();
查看当前用户：MariaDB [(none)]> select user();
创建数据库：create database if not exists 数据库名;
切换数据库：use 数据库名；
删除数据库：慎用！！！ drop database if exists 数据库名;
命令帮助：help 命令
在数据库调用OS命令：system 命令
MariaDB [mysql]> system date
2020年 06月 19日 星期五 14:43:55 CST
MariaDB [mysql]> system free -m
total used free shared buff/cache available
Mem: 1819 212 1454 9 152 1460
Swap: 2047 0 2047
2.数据表结构管理：
创建表：create table(...);
查看表：show tables;
查询指定数据库的表：show tables from 数据库名;
MariaDB [db1]> show tables from mysql;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| column_stats |
| columns_priv |
| course |
| db |
| event |
| func |
| general_log |
| global_priv |
| gtid_slave_pos |
| help_category |
查看建表语句：show create table 数据库名.表名;
MariaDB [db1]> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` char(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)
表结构：desc 数据库名.表名;
删除表：drop table 数据库名.表名;
3.数据库SQL语言分类：
（1）DDL数据库定义语言：create、alter、drop，rename,truncate；管理基础数据库，例如：库，表
（2）DML数据操作语言：select、insert、delete、update,针对数据库里的表，记录
（3）DCL数据控制语言：grant、revoke、commit、rollback；用户授权，权限回收，数据提交回滚等
面试题： drop truncate delete区别？
3.1 create table 表名();
数据类型：数值类型：int double 日期类型：date time 字符串类型：char() varchar()
约束：not null、unique 、primary key、foreign key(eg:foreign key(列名) references 表名(列名))、限制列中的值的范围check（单个列值限制/多个列值得限制）、设置自动增长auto_increment 、默认值(default 值) 、选择表的存储引擎和字符集 ENGINE=InnoDB DEFAULT CHARSET=utf8
#check单个列值的限制
CREATE TABLE Persons(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
City varchar(255),
CHECK (Id_P>0))；
#需要命名 check约束，以及check多个列值的限制
CREATE TABLE Persons(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
City varchar(255),
CONSTRAINT 约束名 CHECK (Id_P>0 AND City='Sandnes'));
注：有时候因为字符集的问题，添加不进去中文值，解决：
1.alter table score convert to character set utf8;
2.创建表时后面跟上ENGINE=InnoDB DEFAULT CHARSET=utf8: create table score (id int(10) primary key not null unique auto_increment,stu_id int(10) not null,c_name varchar(20),grade int(10)) innodb default charset=utf-8;
3.2 alter有关操作
3.2.1 添加/删除列：alter table 表名 add/drop 列名 类型 (default 值);
alter table 表名 add 列名 类型；
alter table 表名 drop 列名；
3.2.2 修改列的数据类型：alter table 表名 alter column 列名 类型;
alter table 表名 modify 列名 类型;
3.2.3 修改表名：alter table 旧表名 rename to 新表名；
或者：rename table 旧表名 to 新表名;
3.2.4 修改列名：alter table 表名 rename column 旧列名 to 新列名;
或者 alter table 表名 change 旧列名 新列名 列类型；
3.3 select 限定条件有哪些？
条件查询：where 满足条件
匹配：列名 like '' (%任意多个字符，_任意一个字符)【mysql中汉字是3个字节】
分组查询:group by 列名 having 条件
排序查询;order by 列(asc|desc)
聚合函数：count() sum() min() max() avg()
limit offset_start,row_count (仅限mysql)
limit第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)
查询第n到m行：select * from table limit n-1,m-n+1;
#检索记录行 6-15
eg:SELECT * FROM table LIMIT 5,10;
3.4 insert的使用：
插入一行记录：insert into score values(20190001,‘张三’);
给某个属性插入值：insert into score(name) values(‘张华’),(“李四”),(“王五”),(“王明”),(“王刚”);
3.5 update的使用：
update 表名 set 列名=值 (where 列名=值);
MariaDB [db1]> update t1 set sex='女';
Query OK, 3 rows affected (0.001 sec)
Rows matched: 3 Changed: 3 Warnings: 0
MariaDB [db1]> update t1 set name='lisi',sex='男' where name='a2';
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0
3.6 delete/drop/truncate的使用：
删除部分数据和全部数据：delete from 表名 (where 列名=值);
删除结构和数据：drop table 表名;
快速删除表中全部数据，保留表结构：truncate table 表名;
4.单表查询例题：
CREATE TABLE emp (
empno int(4) NOT NULL,
ename varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
job varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
mgr int(4) NULL DEFAULT NULL,
hiredate date NOT NULL,
sai int(255) NOT NULL,
comm int(255) NULL DEFAULT NULL,
deptno int(2) NOT NULL,
PRIMARY KEY (empno) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO emp VALUES (1001, ‘甘宁’, ‘文员’, 1013, ‘2000-12-17’, 8000, NULL, 20);
INSERT INTO emp VALUES (1002, ‘黛绮丝’, ‘销售员’, 1006, ‘2001-02-20’, 16000, 3000, 30);
INSERT INTO emp VALUES (1003, ‘殷天正’, ‘销售员’, 1006, ‘2001-02-22’, 12500, 5000, 30);
INSERT INTO emp VALUES (1004, ‘刘备’, ‘经理’, 1009, ‘2001-04-02’, 29750, NULL, 20);
INSERT INTO emp VALUES (1005, ‘谢逊’, ‘销售员’, 1006, ‘2001-09-28’, 12500, 14000, 30);
INSERT INTO emp VALUES (1006, ‘关羽’, ‘经理’, 1009, ‘2001-05-01’, 28500, NULL, 30);
INSERT INTO emp VALUES (1007, ‘张飞’, ‘经理’, 1009, ‘2001-09-01’, 24500, NULL, 10);
INSERT INTO emp VALUES (1008, ‘诸葛亮’, ‘分析师’, 1004, ‘2007-04-19’, 30000, NULL, 20);
INSERT INTO emp VALUES (1009, ‘曾阿牛’, ‘董事长’, NULL, ‘2001-11-17’, 50000, NULL, 10);
INSERT INTO emp VALUES (1010, ‘韦一笑’, ‘销售员’, 1006, ‘2001-09-08’, 15000, 0, 30);
INSERT INTO emp VALUES (1011, ‘周泰’, ‘文员’, 1006, ‘2007-05-23’, 11000, NULL, 20);
INSERT INTO emp VALUES (1012, ‘程普’, ‘文员’, 1006, ‘2001-12-03’, 9500, NULL, 30);
INSERT INTO emp VALUES (1013, ‘庞统’, ‘分析师’, 1004, ‘2001-12-03’, 30000, NULL, 20);
INSERT INTO emp VALUES (1014, ‘黄盖’, ‘文员’, 1007, ‘2002-01-23’, 13000, NULL, 10);
INSERT INTO emp VALUES (1015, ‘张三’, ‘保洁员’, 1001, ‘2013-05-01’, 80000, 50000, 50);
MariaDB [db1]> desc emp;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empno | int(4) | NO | PRI | NULL | |
| ename | varchar(255) | YES | | NULL | |
| job | varchar(255) | YES | | NULL | |
| mgr | int(4) | YES | | NULL | |
| hiredate | date | NO | | NULL | |
| sai | int(255) | NO | | NULL | |
| comm | int(255) | YES | | NULL | |
| deptno | int(2) | NO | | NULL | |
+----------+--------------+------+-----+---------+-------+
8 rows in set (0.001 sec)
– 1. 查询出部门编号为30的所有员工
MariaDB [db1]> select * from emp where deptno=30;
+-------+-----------+-----------+------+------------+-------+-------+--------+
| empno | ename | job | mgr | hiredate | sai | comm | deptno |
+-------+-----------+-----------+------+------------+-------+-------+--------+
| 1002 | 黛绮丝 | 销售员 | 1006 | 2001-02-20 | 16000 | 3000 | 30 |
| 1003 | 殷天正 | 销售员 | 1006 | 2001-02-22 | 12500 | 5000 | 30 |
| 1005 | 谢逊 | 销售员 | 1006 | 2001-09-28 | 12500 | 14000 | 30 |
| 1006 | 关羽 | 经理 | 1009 | 2001-05-01 | 28500 | NULL | 30 |
| 1010 | 韦一笑 | 销售员 | 1006 | 2001-09-08 | 15000 | 0 | 30 |
| 1012 | 程普 | 文员 | 1006 | 2001-12-03 | 9500 | NULL | 30 |
+-------+-----------+-----------+------+------------+-------+-------+--------+
6 rows in set (0.001 sec)
– 2. 所有销售员的姓名、编号和部门编号。
MariaDB [db1]> select ename,empno,deptno from emp where job='销售员';
+-----------+-------+--------+
| ename | empno | deptno |
+-----------+-------+--------+
| 黛绮丝 | 1002 | 30 |
| 殷天正 | 1003 | 30 |
| 谢逊 | 1005 | 30 |
| 韦一笑 | 1010 | 30 |
+-----------+-------+--------+
4 rows in set (0.000 sec)
– 3. 找出奖金高于工资的员工。
MariaDB [db1]> select * from emp where comm>sai;
+-------+--------+-----------+------+------------+-------+-------+--------+
| empno | ename | job | mgr | hiredate | sai | comm | deptno |
+-------+--------+-----------+------+------------+-------+-------+--------+
| 1005 | 谢逊 | 销售员 | 1006 | 2001-09-28 | 12500 | 14000 | 30 |
+-------+--------+-----------+------+------------+-------+-------+--------+
1 row in set (0.001 sec)
– 4. 找出奖金高于工资60%的员工。
MariaDB [db1]> select * from emp where comm>sai*0.6;
+-------+--------+-----------+------+------------+-------+-------+--------+
| empno | ename | job | mgr | hiredate | sai | comm | deptno |
+-------+--------+-----------+------+------------+-------+-------+--------+
| 1005 | 谢逊 | 销售员 | 1006 | 2001-09-28 | 12500 | 14000 | 30 |
| 1015 | 张三 | 保洁员 | 1001 | 2013-05-01 | 80000 | 50000 | 50 |
+-------+--------+-----------+------+------------+-------+-------+--------+
2 rows in set (0.000 sec)
– 5. 找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。
MariaDB [db1]> select * from emp where (deptno=10 and job='经理') or (deptno=20 and job='销售员');
+-------+--------+--------+------+------------+-------+------+--------+
| empno | ename | job | mgr | hiredate | sai | comm | deptno |
+-------+--------+--------+------+------------+-------+------+--------+
| 1007 | 张飞 | 经理 | 1009 | 2001-09-01 | 24500 | NULL | 10 |
+-------+--------+--------+------+------------+-------+------+--------+
1 row in set (0.000 sec)
– 6. 找出部门编号为10中所有经理，部门编号为20中所有销售员，还有即不是经理又不是销售员但其工资大或等于20000的所有员工详细资料。
MariaDB [db1]> select * from emp where (deptno=10 and job='经理') or (deptno=20 and job='销售员') or (job not in ('经理','销售员') and sai>=20000);
+-------+-----------+-----------+------+------------+-------+-------+--------+
| empno | ename | job | mgr | hiredate | sai | comm | deptno |
+-------+-----------+-----------+------+------------+-------+-------+--------+
| 1007 | 张飞 | 经理 | 1009 | 2001-09-01 | 24500 | NULL | 10 |
| 1008 | 诸葛亮 | 分析师 | 1004 | 2007-04-19 | 30000 | NULL | 20 |
| 1009 | 曾阿牛 | 董事长 | NULL | 2001-11-17 | 50000 | NULL | 10 |
| 1013 | 庞统 | 分析师 | 1004 | 2001-12-03 | 30000 | NULL | 20 |
| 1015 | 张三 | 保洁员 | 1001 | 2013-05-01 | 80000 | 50000 | 50 |
+-------+-----------+-----------+------+------------+-------+-------+--------+
5 rows in set (0.000 sec)
– 7. 无奖金或奖金低于1000的员工。
MariaDB [db1]> select * from emp where comm is null or comm<1000;
+-------+-----------+-----------+------+------------+-------+------+--------+
| empno | ename | job | mgr | hiredate | sai | comm | deptno |
+-------+-----------+-----------+------+------------+-------+------+--------+
| 1001 | 甘宁 | 文员 | 1013 | 2000-12-17 | 8000 | NULL | 20 |
| 1004 | 刘备 | 经理 | 1009 | 2001-04-02 | 29750 | NULL | 20 |
| 1006 | 关羽 | 经理 | 1009 | 2001-05-01 | 28500 | NULL | 30 |
| 1007 | 张飞 | 经理 | 1009 | 2001-09-01 | 24500 | NULL | 10 |
| 1008 | 诸葛亮 | 分析师 | 1004 | 2007-04-19 | 30000 | NULL | 20 |
| 1009 | 曾阿牛 | 董事长 | NULL | 2001-11-17 | 50000 | NULL | 10 |
| 1010 | 韦一笑 | 销售员 | 1006 | 2001-09-08 | 15000 | 0 | 30 |
| 1011 | 周泰 | 文员 | 1006 | 2007-05-23 | 11000 | NULL | 20 |
| 1012 | 程普 | 文员 | 1006 | 2001-12-03 | 9500 | NULL | 30 |
| 1013 | 庞统 | 分析师 | 1004 | 2001-12-03 | 30000 | NULL | 20 |
| 1014 | 黄盖 | 文员 | 1007 | 2002-01-23 | 13000 | NULL | 10 |
+-------+-----------+-----------+------+------------+-------+------+--------+
11 rows in set (0.000 sec)
– 8. 查询名字由三个字组成的员工。
– 注意：一个汉字占三个字节
MariaDB [db1]> select * from emp where ename like '___';
+-------+-----------+-----------+------+------------+-------+------+--------+
| empno | ename | job | mgr | hiredate | sai | comm | deptno |
+-------+-----------+-----------+------+------------+-------+------+--------+
| 1002 | 黛绮丝 | 销售员 | 1006 | 2001-02-20 | 16000 | 3000 | 30 |
| 1003 | 殷天正 | 销售员 | 1006 | 2001-02-22 | 12500 | 5000 | 30 |
| 1008 | 诸葛亮 | 分析师 | 1004 | 2007-04-19 | 30000 | NULL | 20 |
| 1009 | 曾阿牛 | 董事长 | NULL | 2001-11-17 | 50000 | NULL | 10 |
| 1010 | 韦一笑 | 销售员 | 1006 | 2001-09-08 | 15000 | 0 | 30 |
+-------+-----------+-----------+------+------------+-------+------+--------+
5 rows in set (0.000 sec)
MariaDB [db1]> select * from emp where length(ename)=9;
+-------+-----------+-----------+------+------------+-------+------+--------+
| empno | ename | job | mgr | hiredate | sai | comm | deptno |
+-------+-----------+-----------+------+------------+-------+------+--------+
| 1002 | 黛绮丝 | 销售员 | 1006 | 2001-02-20 | 16000 | 3000 | 30 |
| 1003 | 殷天正 | 销售员 | 1006 | 2001-02-22 | 12500 | 5000 | 30 |
| 1008 | 诸葛亮 | 分析师 | 1004 | 2007-04-19 | 30000 | NULL | 20 |
| 1009 | 曾阿牛 | 董事长 | NULL | 2001-11-17 | 50000 | NULL | 10 |
| 1010 | 韦一笑 | 销售员 | 1006 | 2001-09-08 | 15000 | 0 | 30 |
+-------+-----------+-----------+------+------------+-------+------+--------+
5 rows in set (0.000 sec)
– 9.查询2000年入职的员工。
MariaDB [db1]> select * from emp where year(hiredate)=2000;
+-------+--------+--------+------+------------+------+------+--------+
| empno | ename | job | mgr | hiredate | sai | comm | deptno |
+-------+--------+--------+------+------------+------+------+--------+
| 1001 | 甘宁 | 文员 | 1013 | 2000-12-17 | 8000 | NULL | 20 |
+-------+--------+--------+------+------------+------+------+--------+
1 row in set (0.000 sec)
– 10. 查询所有员工详细信息，用编号升序排序
MariaDB [db1]> select * from emp order by empno;
+-------+-----------+-----------+------+------------+-------+-------+--------+
| empno | ename | job | mgr | hiredate | sai | comm | deptno |
+-------+-----------+-----------+------+------------+-------+-------+--------+
| 1001 | 甘宁 | 文员 | 1013 | 2000-12-17 | 8000 | NULL | 20 |
| 1002 | 黛绮丝 | 销售员 | 1006 | 2001-02-20 | 16000 | 3000 | 30 |
| 1003 | 殷天正 | 销售员 | 1006 | 2001-02-22 | 12500 | 5000 | 30 |
| 1004 | 刘备 | 经理 | 1009 | 2001-04-02 | 29750 | NULL | 20 |
| 1005 | 谢逊 | 销售员 | 1006 | 2001-09-28 | 12500 | 14000 | 30 |
| 1006 | 关羽 | 经理 | 1009 | 2001-05-01 | 28500 | NULL | 30 |
| 1007 | 张飞 | 经理 | 1009 | 2001-09-01 | 24500 | NULL | 10 |
| 1008 | 诸葛亮 | 分析师 | 1004 | 2007-04-19 | 30000 | NULL | 20 |
| 1009 | 曾阿牛 | 董事长 | NULL | 2001-11-17 | 50000 | NULL | 10 |
| 1010 | 韦一笑 | 销售员 | 1006 | 2001-09-08 | 15000 | 0 | 30 |
| 1011 | 周泰 | 文员 | 1006 | 2007-05-23 | 11000 | NULL | 20 |
| 1012 | 程普 | 文员 | 1006 | 2001-12-03 | 9500 | NULL | 30 |
| 1013 | 庞统 | 分析师 | 1004 | 2001-12-03 | 30000 | NULL | 20 |
| 1014 | 黄盖 | 文员 | 1007 | 2002-01-23 | 13000 | NULL | 10 |
| 1015 | 张三 | 保洁员 | 1001 | 2013-05-01 | 80000 | 50000 | 50 |
+-------+-----------+-----------+------+------------+-------+-------+--------+
15 rows in set (0.000 sec)
– 11. 查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序
MariaDB [db1]> select * from emp order by sai desc,hiredate;
+-------+-----------+-----------+------+------------+-------+-------+--------+
| empno | ename | job | mgr | hiredate | sai | comm | deptno |
+-------+-----------+-----------+------+------------+-------+-------+--------+
| 1015 | 张三 | 保洁员 | 1001 | 2013-05-01 | 80000 | 50000 | 50 |
| 1009 | 曾阿牛 | 董事长 | NULL | 2001-11-17 | 50000 | NULL | 10 |
| 1013 | 庞统 | 分析师 | 1004 | 2001-12-03 | 30000 | NULL | 20 |
| 1008 | 诸葛亮 | 分析师 | 1004 | 2007-04-19 | 30000 | NULL | 20 |
| 1004 | 刘备 | 经理 | 1009 | 2001-04-02 | 29750 | NULL | 20 |
| 1006 | 关羽 | 经理 | 1009 | 2001-05-01 | 28500 | NULL | 30 |
| 1007 | 张飞 | 经理 | 1009 | 2001-09-01 | 24500 | NULL | 10 |
| 1002 | 黛绮丝 | 销售员 | 1006 | 2001-02-20 | 16000 | 3000 | 30 |
| 1010 | 韦一笑 | 销售员 | 1006 | 2001-09-08 | 15000 | 0 | 30 |
| 1014 | 黄盖 | 文员 | 1007 | 2002-01-23 | 13000 | NULL | 10 |
| 1003 | 殷天正 | 销售员 | 1006 | 2001-02-22 | 12500 | 5000 | 30 |
| 1005 | 谢逊 | 销售员 | 1006 | 2001-09-28 | 12500 | 14000 | 30 |
| 1011 | 周泰 | 文员 | 1006 | 2007-05-23 | 11000 | NULL | 20 |
| 1012 | 程普 | 文员 | 1006 | 2001-12-03 | 9500 | NULL | 30 |
| 1001 | 甘宁 | 文员 | 1013 | 2000-12-17 | 8000 | NULL | 20 |
+-------+-----------+-----------+------+------------+-------+-------+--------+
15 rows in set (0.000 sec)
– 12.查询每个部门的平均工资
MariaDB [db1]> select deptno,avg(sai)from emp group by deptno;
+--------+------------+
| deptno | avg(sai) |
+--------+------------+
| 10 | 29166.6667 |
| 20 | 21750.0000 |
| 30 | 15666.6667 |
| 50 | 80000.0000 |
+--------+------------+
4 rows in set (0.045 sec)
– 13.查询每个部门的雇员数量
MariaDB [db1]> select deptno,count(*) from emp group by deptno;
+--------+----------+
| deptno | count(*) |
+--------+----------+
| 10 | 3 |
| 20 | 5 |
| 30 | 6 |
| 50 | 1 |
+--------+----------+
4 rows in set (0.000 sec)
– 14.查询每种工作的最高工资、最低工资、人数
MariaDB [db1]> select job,max(sai),min(sai),count(*) from emp group by job;
+-----------+----------+----------+----------+
| job | max(sai) | min(sai) | count(*) |
+-----------+----------+----------+----------+
| 保洁员 | 80000 | 80000 | 1 |
| 分析师 | 30000 | 30000 | 2 |
| 文员 | 13000 | 8000 | 4 |
| 经理 | 29750 | 24500 | 3 |
| 董事长 | 50000 | 50000 | 1 |
| 销售员 | 16000 | 12500 | 4 |
+-----------+----------+----------+----------+
6 rows in set (0.101 sec)
–15.查询工资由高到低，如果工资相同按入职时间先后顺序，打印top4
MariaDB [mysql]> select * from emp order by sai desc,hiredate desc limit 4;
+-------+-----------+-----------+------+------------+-------+-------+--------+
| empno | ename | job | mgr | hiredate | sai | comm | deptno |
+-------+-----------+-----------+------+------------+-------+-------+--------+
| 1015 | 张三 | 保洁员 | 1001 | 2013-05-01 | 80000 | 50000 | 50 |
| 1009 | 曾阿牛 | 董事长 | NULL | 2001-11-17 | 50000 | NULL | 10 |
| 1008 | 诸葛亮 | 分析师 | 1004 | 2007-04-19 | 30000 | NULL | 20 |
| 1013 | 庞统 | 分析师 | 1004 | 2001-12-03 | 30000 | NULL | 20 |
+-------+-----------+-----------+------+------------+-------+-------+--------+
4 rows in set (0.000 sec)
–16.按职工号排序，输出第3-8条记录
MariaDB [mysql]> select * from emp order by empno limit 2,6;
+-------+-----------+-----------+------+------------+-------+-------+--------+
| empno | ename | job | mgr | hiredate | sai | comm | deptno |
+-------+-----------+-----------+------+------------+-------+-------+--------+
| 1003 | 殷天正 | 销售员 | 1006 | 2001-02-22 | 12500 | 5000 | 30 |
| 1004 | 刘备 | 经理 | 1009 | 2001-04-02 | 29750 | NULL | 20 |
| 1005 | 谢逊 | 销售员 | 1006 | 2001-09-28 | 12500 | 14000 | 30 |
| 1006 | 关羽 | 经理 | 1009 | 2001-05-01 | 28500 | NULL | 30 |
| 1007 | 张飞 | 经理 | 1009 | 2001-09-01 | 24500 | NULL | 10 |
| 1008 | 诸葛亮 | 分析师 | 1004 | 2007-04-19 | 30000 | NULL | 20 |
+-------+-----------+-----------+------+------------+-------+-------+--------+
6 rows in set (0.000 sec)
5.多表查询与子查询：
## 5.1 子查询：某些情况下，当进行查询时，需要的条件是另外一个select语句的结果，这时候就需要子查询。
关键字：in 、not in、=、<>等
5.2 多表查询：select … from 表名1 连接类型 表名2 on 连接条件 where 过滤条件
连接类型：内连接、外连接、交叉连接、自连接
5.2.1.交叉连接：笛卡尔积
select * from t1_name cross t2_name;
select * from t1_name,t2_name;
5.2.2.内连接：等值连接、非等值连接、自然连接。
select * from t1_name t1,t2_name t2 where t1.deptno=t2.deptno;
select * from t1_name t1 inner join t2_name t2 on t1.deptno=t2.deptno;
#但是内连接只能查询出匹配的行
5.2.3.外连接：左外连接、右外连接、全连接
左外连接：select ... from t1_name left [outer]join t2_name on 连接条件;
右外连接：select ... from t1_name right [outer] join t2_name on 连接条件;
全连接：select ... from t1_name full [outer] join t2_name on 连接条件;
#不仅可以列出与连接条件相匹配的记录，还列出左外连接、右外连接或全连接中所有符合where过滤条件的数据行。
5.2.4.自连接：使用的是同一个表【难点理解】
5.2.5 多表查询列题：
多表的查询，这个测试中涉及到两张表，分别是emp表，dept 表，（emp表是员工信息表，dept是部门信息表）
CREATE TABLE dept (
deptno int NOT NULL ,
dname char(9) NOT NULL ,
loc char(6) NOT NULL
);
INSERT INTO dept VALUES (10, ‘教研部’, ‘北京’);
INSERT INTO dept VALUES (20, ‘学工部’, ‘上海’);
INSERT INTO dept VALUES (30, ‘销售部’, ‘广州’);
INSERT INTO dept VALUES (40, ‘财务部’, ‘武汉’);
1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
MariaDB [mysql]> select t1.*,dname,loc from (select deptno,count(*) from emp group by deptno having count(*)>=1) t1 left join dept on t1.deptno=dept.deptno;
+--------+----------+-----------+--------+
| deptno | count(*) | dname | loc |
+--------+----------+-----------+--------+
| 10 | 3 | 教研部 | 北京 |
| 20 | 5 | 学工部 | 上海 |
| 30 | 6 | 销售部 | 广州 |
| 50 | 1 | NULL | NULL |
+--------+----------+-----------+--------+
4 rows in set (0.001 sec)
2. 列出所有员工的姓名及其直接上级的姓名。【自连接】
MariaDB [mysql]> select e.ename,p.ename from emp e left join emp p on e.mgr=p.empno;
+-----------+-----------+
| ename | ename |
+-----------+-----------+
| 甘宁 | 庞统 |
| 黛绮丝 | 关羽 |
| 殷天正 | 关羽 |
| 刘备 | 曾阿牛 |
| 谢逊 | 关羽 |
| 关羽 | 曾阿牛 |
| 张飞 | 曾阿牛 |
| 诸葛亮 | 刘备 |
| 曾阿牛 | NULL |
| 韦一笑 | 关羽 |
| 周泰 | 关羽 |
| 程普 | 关羽 |
| 庞统 | 刘备 |
| 黄盖 | 张飞 |
| 张三 | 甘宁 |
+-----------+-----------+
15 rows in set (0.001 sec)
3.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
MariaDB [mysql]> select emp.*,dept.dname from emp right join dept on emp.deptno=dept.deptno;
+-------+-----------+-----------+------+------------+-------+-------+--------+-----------+
| empno | ename | job | mgr | hiredate | sai | comm | deptno | dname |
+-------+-----------+-----------+------+------------+-------+-------+--------+-----------+
| 1001 | 甘宁 | 文员 | 1013 | 2000-12-17 | 8000 | NULL | 20 | 学工部 |
| 1002 | 黛绮丝 | 销售员 | 1006 | 2001-02-20 | 16000 | 3000 | 30 | 销售部 |
| 1003 | 殷天正 | 销售员 | 1006 | 2001-02-22 | 12500 | 5000 | 30 | 销售部 |
| 1004 | 刘备 | 经理 | 1009 | 2001-04-02 | 29750 | NULL | 20 | 学工部 |
| 1005 | 谢逊 | 销售员 | 1006 | 2001-09-28 | 12500 | 14000 | 30 | 销售部 |
| 1006 | 关羽 | 经理 | 1009 | 2001-05-01 | 28500 | NULL | 30 | 销售部 |
| 1007 | 张飞 | 经理 | 1009 | 2001-09-01 | 24500 | NULL | 10 | 教研部 |
| 1008 | 诸葛亮 | 分析师 | 1004 | 2007-04-19 | 30000 | NULL | 20 | 学工部 |
| 1009 | 曾阿牛 | 董事长 | NULL | 2001-11-17 | 50000 | NULL | 10 | 教研部 |
| 1010 | 韦一笑 | 销售员 | 1006 | 2001-09-08 | 15000 | 0 | 30 | 销售部 |
| 1011 | 周泰 | 文员 | 1006 | 2007-05-23 | 11000 | NULL | 20 | 学工部 |
| 1012 | 程普 | 文员 | 1006 | 2001-12-03 | 9500 | NULL | 30 | 销售部 |
| 1013 | 庞统 | 分析师 | 1004 | 2001-12-03 | 30000 | NULL | 20 | 学工部 |
| 1014 | 黄盖 | 文员 | 1007 | 2002-01-23 | 13000 | NULL | 10 | 教研部 |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 财务部 |
+-------+-----------+-----------+------+------------+-------+-------+--------+-----------+
15 rows in set (0.000 sec)
4.列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
MariaDB [mysql]> select t.deptno,t.ename,dept.dname from (select e.deptno,e.ename from emp e,emp p where e.mgr=p.empno and e.hiredate<p.hiredate) t,dept where t.deptno=dept.deptno;
+--------+-----------+-----------+
| deptno | ename | dname |
+--------+-----------+-----------+
| 20 | 甘宁 | 学工部 |
| 30 | 黛绮丝 | 销售部 |
| 30 | 殷天正 | 销售部 |
| 20 | 刘备 | 学工部 |
| 30 | 关羽 | 销售部 |
| 10 | 张飞 | 教研部 |
+--------+-----------+-----------+
6 rows in set (0.000 sec)
5.返回拥有员工的部门名、部门号
MariaDB [mysql]> select dept.deptno,dname from (select deptno from emp group by deptno having count(*)>=1) t,dept where t.deptno=dept.deptno;
+--------+-----------+
| deptno | dname |
+--------+-----------+
| 10 | 教研部 |
| 20 | 学工部 |
| 30 | 销售部 |
+--------+-----------+
3 rows in set (0.001 sec)
146
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
