首页 > > 网络编程 > Mysql >

Mysql

2018-06-18 00:57:01来源：未知阅读 ()

Day01

1.什么是数据库
简单来讲,数据库就是存储在硬盘上的一个文件而已,
只不过这个文件他有一定的规则,还有人专门为这个文件
开发了一种语言(SQL),通过这种语言可以通过管理软件
来快速的从数据库中查出数据。
数据库（DB）
2.什么是数据库管理软件
管理软件就是用来管理数据库的一些操作,这种管理操作
通过定义的（SQL）语言来操作的。市面上这种数据库管理
系统有很多,常见的有下面几个
MySql 甲骨文
Oracle 甲骨文
DB2 IBM
Sybase 赛尔斯 PowerDesigner 建模软件
SQL Server 微软
3.MySql安装
a,绿色安装
解压,按照
http://blog.csdn.net/symoriaty/article/details/78277615
b,exe安装
无脑下一步,按照按照文档一步步的实现即可
4.数据库的启动和关闭
数据库服务器的启动和关闭不是MySql的语法,他是操作系统的语法,操作系统
提供了这样的命令

net start 服务名 -- 启动该服务
net stop 服务名 -- 关闭该服务
5.数据库的登录/退出/修改当前登录用户的密码
MySql数据库管理软件中最高用户用户名为 root
a,登录
mysql -u用户名 -p密码密码明文登录

mysql -u用户名 -p 回车在下面密文输入
b,退出
\q
c,修改密码(登录状态下)
set password=password('新密码');
6.MySql数据库中常用的操作命令
a,查看当前用户下管理了哪些数据库
show databases;
b,切换数据库
use 数据库名字;
c,查看当前数据库下面有哪些表
show tables;
d,查看数据库的状态（数据库的客户端服务器以及连接的编码格式）
status;
如果不是统一的UTF8 就修改 my.ini 配置文件修改他的默认
编码格式全部为 utf8

在[client]下追加：
default-character-set=utf8
在[mysqld]下追加：
character-set-server=utf8
在[mysql]下追加：
default-character-set=utf8
修改完毕后，使用如下命令之一重启mysql服务：

7.MySql快速入门
建议:
SQL语言建议所有的关键字大写。
a,创建数据库
create database 数据库名字;
创建一个 java1711数据库
create database java1711;
b,删除数据库
drop database 数据库名字;
drop database java1711;
c,创建表
//创建表之前一定要切换到该数据库下面 use 数据库名字;
create table 表名(
列名列类型,
列名列类型
...
);
name varchar(5),
type varchar(3),
price double
);
d,删除表
drop table 表名;
drop table t_hero;
e,查看表结构
desc 表名;
f,向表中插入数据
insert into 表名(列名1,列名2,,,) values(对应的列值1,对应的列值2,,);
//添加一个英雄
insert into t_hero(name,type,price) values('披甲龙龟','坦克',3150);
//添加一个英雄
insert into t_hero(name,type,price) values('无极剑圣','刺客',450);

insert into t_hero(name,type,price) values('abcdef','刺客',450);
g,查看该表的所有数据
select * from 表名;
select * from t_hero;

45道练习题
https://www.cnblogs.com/aqxss/p/6563625.html

Day02

/*
1.什么是SQL语言
SQL语言是一种通用的数据库操作语言,这个语言
有多个版本,每个版本都有不同的标准。
实现SQL语言标准的好处是降低使用者的学习成本。
目前的标准有SQL89 SQL92 SQL99
这些标准都是由各大的数据库厂商他们统一制定的,但是
越是大的数据库厂商越不遵守标准。所以SQL语言在所有的
数据库中是有一定的兼容性问题。
2.SQL语言的分类
https://www.cnblogs.com/fjfzhkb/archive/2007/10/18/929108.html
DDL -- 数据定义语言 ***
CREATE
DROP
DML -- 数据操纵语言 ***
INSERT
UPDATE
DELETE
DCL -- 数据控制语言了解 DBA
GRANT 授权
REVOKE 撤销
TCL -- 事务控制语言 ****
COMMIT
ROLLBACK
DQL -- 数据查询语言 ******
SELECT
*/

/*
DDL 数据定义语言

主要作用是可以
a,创建数据库
b,修改数据库修改数据库的编码一般别动
c,删除数据库
e,创建表
f,修改表
g,删除表
*/

-- 创建数据库
-- create database [IF NOT EXISTS] 数据库名字
-- 创建数据库时候指定数据库的编码
-- CREATE DATABASE 数据库名字 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
-- 当Java1711_temp数据库不存在的时候才创建
CREATE DATABASE IF NOT EXISTS Java1711_temp;

-- 删除数据库
-- drop database [IF EXISTS] 数据库名字
DROP DATABASE Java1711_temp;
-- 当Java1711_temp数据库存在的时候才删除
DROP DATABASE IF EXISTS Java1711_temp;
-- ALTER DATABASE 数据库名字 DEFAULT CHARACTER SET 编码 COLLATE 编码_general_ci

-- 创建表
/*
create table 表名(
列名列类型,
列名列类型,
列名列类型定义最后一个列的时候不需要编写 ,
);
*/
CREATE TABLE student(
student_sno VARCHAR(20),
student_name VARCHAR(20)
);
-- 在建表之前一定要选择好建在哪个数据库下面
USE java1711;

-- 如果操作的时候有关键字使用 `` 将关键字包起来
-- 了解一般只要不刻意的去向关键字去撞,很难遇到。
-- 建议表名前面加上 t_
-- 属性名前面加上表名_属性名
C`student`
-- 删除表
-- drop table 表名
`java1711`-- 修改表
-- 修改表名
-- alter table 原表名 rename to 新表名;
ALTER TABLE t_student RENAME TO student;
-- 添加列
-- alter table 表名 add 列名列类型;
ALTER TABLE student ADD age INT;
ALTER TABLE student ADD className VARCHAR(30);
ALTER TABLE student ADD student_phone VARCHAR(20);
ALTER TABLE student ADD student_sex VARCHAR(20);
DESC student;
-- 删除表列
-- alter table 表名 drop 列名;
ALTER TABLE student DROP className;
-- 修改表列类型
-- alter table 表名 modify 列名列类型;
ALTER TABLE student MODIFY age VARCHAR(20);
-- 修改列名
-- alter table 表名 change 旧列名新列名列类型;
ALTER TABLE student CHANGE age student_age INT;
-- 修改字段位置
-- ALTER TABLE 表名 MODIFY 字段名1 数据类型 FIRST ｜ AFTER 字段名2;
-- FIRST 第一个
-- AFTER 在什么之后
ALTER TABLE student MODIFY student_age INT AFTER student_name;

/*
DML
DML的作用主要是操纵数据库里面的记录信息。
a,插入数据
b,修改数据
c,删除数据
*/
-- 插入数据
-- 给指定的列插入指定的数据没有插的列以 null值保存
-- insert into 表名(列名,,,,) values(列值,,,,);
INSERT INTO student(student_sno,student_name,student_age,student_phone) VALUES('10001','德玛',22,'111');
SELECT * FROM student;
-- 给所有列插入数据
-- insert into 表名 values(列值1,,,,,);
-- 推荐使用给指定列插入指定的数据可读性强;
DESC student;
INSERT INTO student VALUES('10001','德玛',22,'11','111','男');
INSERT INTO student VALUES('10001','德玛',22,'11','111','女');
-- 修改数据
-- update 表名 set 列名=列值,列名=列值,,, 修改表中所有的记录
UPDATE student SET student_name='德邦',student_age=28;
-- update 表名 set 列名=列值,列名=列值 where 条件修改表中满足条件的记录
UPDATE student SET student_name='剑圣' WHERE student_sex='男';
-- 删除数据
-- delete from 表名删除该表所有记录
DELETE FROM student;
-- delete from 表名 where 条件删除该表满足条件的记录
SELECT * FROM student;
DELETE FROM student WHERE student_sex='男';

/*
DQL
原本属于 DML语法的，因为用的比较多，所以
给他单独的分理出一个语法，主要就是查询

-- SQL中的运算符
常用的运算符

比较运算符
> < >= <= <> !=
逻辑运算符
and or not

between and 在什么范围之内
is null 是不是空空不能用等于号比较
is not null | not is null 是不是不是空
in()
-- SQL 数据类型
整数：int
字符串： varchar char
小数：double
钱有关的： decimal
日期：喜欢使用字符串，喜欢使用long毫秒值
blob：二进制 4GB

*/
-- 基本查询
/*
select 列名1，列名2，列名3
from 要查的表
-- 查询表中的所有列
select * 不建议使用
from 要查的表
*/

SELECT * FROM dept; -- 4
SELECT * FROM emp; -- 15
SELECT * FROM salgrade; -- 5
-- 查询所有员工的姓名和职位
SELECT ename ,job
FROM emp;
-- 查询员工的所有信息
SELECT *
FROM emp;
-- 条件查询
-- 查询员工编号为1005的员工姓名和工资及提成
SELECT ename ,sal , comm
FROM emp
WHERE empno=1005;

-- 查询工资大于等于30000的员工信息
SELECT *
FROM emp
WHERE sal>=30000;

-- 查询部门编号不是20的员工信息，
select *
from emp
where deptno!=20;

SELECT *
FROM emp
WHERE deptno<>20;

-- 查询工资在2000~30000范围内的员工信息
select *
from emp
where sal>=20000 and sal<=30000;

select *
from emp
where sal between 20000 and 30000;

-- 查询员工编号为 1003 1008 1002 的员工详细信息
select *
from emp
where empno=1002 or empno=1008 or empno=1003;

select *
from emp
where empno not in(1002,1003,1008);

-- 显示没有提成的
select *
from emp
where comm is null;

-- 查询公司里面所有职位
-- DISTINCT 去除重复的行如果存在多列
-- 重复的条件是多个列都一样能去除，所以
-- 这个关键字一般用在单列查询的时候出现的概率比较大

select distinct job
from emp

-- 查询一个员工的年薪和他的姓名
-- sal*12 不友好，我们可以重新给该列重新起别名
-- 起别名 as 别名 as 可以省略
select ename '姓名' , sal*12 as '年薪'
from emp

-- 查询每个员工的姓名和他的每月实际收入
-- ifnull （可能出现空值的列名，替换的值）
select ename ,sal+ ifnull(comm,0)'实际收入'
from emp;

-- 模拟查询
-- _ 通用的匹配任意字符
-- % 通用的匹配任意多个字符
-- like 关键字

select * from emp;
-- 寻找出姓名中都带子的原工信息
select *
from emp
where ename like '%子%';

-- 查询出名字中第二个字是花的员工信息
select *
from emp
where ename like '_花%';

SELECT *
FROM emp;
WHERE ename LIKE '花%';

SELECT ename,job , sal
from emp
where ename like '花%'

-- 排序 order by
-- desc 降序默认升序 asc
select *
from emp
order by sal DEsc,empno DESC;

-- 组函数聚合函数 sum avg max min count
-- 公司一个月工资的开销
select sum(sal)
from emp;

SELECT avg(sal)
FROM emp;

SELECT max(sal)
FROM emp;

SELECT min(sal)
FROM emp;

SELECT count(*)
FROM emp;

-- 查看20部门所有工资的总和
/*
关键字： group by
where 分组前过滤
having 分组后过滤
具体使用哪一种过滤方式，看过滤条件是在分组前
产生的还是分组后产生的

如果使用分组关键字，那么select后面只能
跟上分组的列和组函数，不能出现其他的列
*/

select deptno,sum(sal)
from emp
group by deptno
having sum(sal)>90000;

-- 查询出各个职位的人数
select job,count(*)
from emp
group by job

-- 查询部门员工工资大于25000的人数
select job, count(*)
from emp
where sal>25000
group by job
having count(*)>=2;

/*创建部门表*/
CREATE TABLE dept(
deptno INT PRIMARY KEY,
dname VARCHAR(50),
loc VARCHAR(50)
);

/*创建雇员表*/
CREATE TABLE emp(
empno INT PRIMARY KEY,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
COMM DECIMAL(7,2),
deptno INT,
CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno)
);

/*创建工资等级表*/
CREATE TABLE salgrade(
grade INT PRIMARY KEY,
losal INT,
hisal INT
);

/*插入dept表数据*/
INSERT INTO dept VALUES (10, '教研部', '北京');
INSERT INTO dept VALUES (20, '学工部', '上海');
INSERT INTO dept VALUES (30, '销售部', '广州');
INSERT INTO dept VALUES (40, '财务部', '武汉');

/*插入emp表数据*/
INSERT INTO emp VALUES (1009, '猴哥', '董事长', NULL, '2001-11-17', 50000, NULL, 10);
INSERT INTO emp VALUES (1004, '剑圣', '经理', 1009, '2001-04-02', 29750, NULL, 20);
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 (1013, '螳螂', '分析师', 1004, '2001-12-03', 30000, 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 (1005, '老鼠', '销售员', 1006, '2001-09-28', 12500, 14000, 30);
INSERT INTO emp VALUES (1010, '奥巴马', '销售员', 1006, '2001-09-08', 15000, 0, 30);
INSERT INTO emp VALUES (1012, '风女', '文员', 1006, '2001-12-03', 9500, NULL, 30);
INSERT INTO emp VALUES (1014, '奶妈', '文员', 1007, '2002-01-23', 13000, NULL, 10);
INSERT INTO emp VALUES (1011, '牛头', '文员', 1008, '2007-05-23', 11000, NULL, 20);
INSERT INTO emp VALUES (1001, '锤石', '文员', 1013, '2000-12-17', 8000, NULL, 20);
INSERT INTO emp VALUES (1015, '花千骨', '服务员', 1001, '2001-12-17', 80000, NULL, 50);

/*插入salgrade表数据*/
INSERT INTO salgrade VALUES (1, 7000, 12000);
INSERT INTO salgrade VALUES (2, 12010, 14000);
INSERT INTO salgrade VALUES (3, 14010, 20000);
INSERT INTO salgrade VALUES (4, 20010, 30000);
INSERT INTO salgrade VALUES (5, 30010, 99990);

Day03

/*
DAY03
回顾:
DDL create drop
DML insert update delete
DQL select
a,基本查询
b,条件查询 where
c,排序 order by
d,分组 group by
e,分组后的过滤 having
f,字段的控制 distinct 别名...
*/
-- 分页查询
/*
在实际的Web开发中有可能一页的数据太多,不能够
在一页展示,如果展示也可能造成性能的丢失,所以为
了提高用户的体验,我们可以数据分页的显示在web
页面上。实际开发中分页分为2种一种为
真分页数据库采用了分页查询
假分页数据库没有采用分页查询,而是在服务端对数据
进行处理。

limit 注意这个不是SQL标准,只能在mysql中使用。
limit 参数1,参数2
1-- 从第几条开始从0开始
2-- 改页的条数
*/
USE java1711;

-- 每页4条记录第一页
SELECT *
FROM emp
LIMIT 0,4;

-- 每页4条记录第二页
SELECT *
FROM emp
LIMIT 4,4;

-- 当前页-1 * 每页的条数

-- 每页4条记录第三页
SELECT *
FROM emp
LIMIT 8,4;

-- 每页4条记录第四页
SELECT *
FROM emp
LIMIT 12,4;

SELECT *
FROM emp
WHERE deptno='30'

SELECT *
FROM emp
WHERE comm>sal;

/*4. 找出奖金高于工资60%的员工。*/
SELECT *
FROM emp
WHERE comm>sal*0.6;
/*5. 找出部门编号为10中所有经理，和部门编号为20中所有
销售员的详细资料。*/
SELECT *
FROM emp
WHERE deptno=10 AND job='经理' OR deptno=20 AND job='销售员';
/*6. 找出部门编号为10中所有经理，部门编号为20中所有销
售员，还有即不是经理又不是销售员但其工资大或等于
20000的所有员工详细资料。*/
SELECT *
FROM emp
WHERE deptno=10 AND job='经理' OR
deptno=20 AND job='销售员' OR
job!='经理' AND job!='销售员' AND sal>=20000;
/*7. 有奖金的工种。*/
SELECT *
FROM emp
WHERE comm IS NOT NULL;
/*8. 无奖金或奖金低于10000的员工。*/
SELECT ename
FROM emp
WHERE comm IS NULL OR comm<10000;
/*9. 查询名字由三个字组成的员工。*/
SELECT * FROM emp
WHERE ename LIKE '___';
/*10.查询2000年入职的员工。*/
SELECT ename
FROM emp
WHERE hiredate LIKE '2000%';

/*11. 查询所有员工详细信息，用编号升序排序*/
SELECT *
FROM emp
ORDER BY empno ASC;
/*12. 查询所有员工详细信息，用工资降序排序，如果工资
相同使用入职日期升序排序*/
SELECT *
FROM emp
ORDER BY sal DESC ,hiredate ASC;
/*13. 查询每个部门的平均工资*/
SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno;
/*14. 求出每个部门的雇员数量。*/
SELECT DEPTNO, COUNT(EMPNO)FROM emp GROUP BY deptno;
/*15. 查询每种工作的最高工资、最低工资、人数*/
SELECT job, MAX(sal),MIN(sal),COUNT(*)
FROM emp
GROUP BY job
/*16. 显示非销售人员工作名称以及从事同一工作雇员的月
工资的总和，并且要满足从事同一工作的雇员的月工资合
计大于50000，输出结果按月工资的合计升序排列*/
SELECT job '工种',SUM(sal) '月工资'
FROM emp
WHERE job!='销售员'
GROUP BY job
HAVING SUM(sal)>50000
ORDER BY SUM(sal) ASC;
/*
select
from
where
group by
having
order by
limit
*/

/*
数据库的约束
和Java中的泛型有些类似,目的是为了让程序报错。
为了数据库中记录的数据有意义。保证数据的完整性。

对于MySql来说有下面常见的约束
a,主键约束
单列 PK
联合了解
b,唯一约束
c,非空约束
d,外键约束
e,检查约束 MySql不支持
*/
CREATE DATABASE java1711_1;
USE java1711_1;
DROP TABLE t_student;
CREATE TABLE t_student(
sno INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
cardID VARCHAR(18) UNIQUE
);
INSERT INTO t_student(NAME,cardID) VALUES('张三',NULL);
INSERT INTO t_student(NAME,cardID) VALUES('李四','123');
INSERT INTO t_student(NAME,cardID) VALUES('王五','456');
SELECT * FROM t_student;
DROP TABLE t_user;
CREATE TABLE t_user(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键并且自增长
username VARCHAR(30) BINARY UNIQUE NOT NULL, -- 唯一非空
PASSWORD VARCHAR(30) NOT NULL -- 非空
);
/*
BINARY
MySql 默认情况下在Windows系统下字段是不区分大小写
在Linux 会区分大小写。
*/
INSERT INTO t_user(username,PASSWORD) VALUES('root','123456');
INSERT INTO t_user(username,PASSWORD) VALUES('system','6666');
INSERT INTO t_user(username,PASSWORD) VALUES('Root','123456');
SELECT * FROM t_user;

/*
外键必须要先有班级才能有学生
学生表
班级表
*/
DROP TABLE t_student;
CREATE TABLE t_student(
sno INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30) NOT NULL,
age INT,
address VARCHAR(50),
c_id INT,
CONSTRAINT t_666 FOREIGN KEY (c_id)
REFERENCES t_class(c_id)
);
DROP TABLE t_class;
CREATE TABLE t_class(
c_id INT PRIMARY KEY,
c_name VARCHAR(30)
);
INSERT INTO t_class(c_id,c_name) VALUES(1,'Java1711');
INSERT INTO t_class(c_id,c_name) VALUES(2,'Java1710');
INSERT INTO t_class(c_id,c_name) VALUES(3,'Java1712');

SELECT * FROM t_class;
INSERT INTO t_student(NAME,age,address,c_id) VALUES('张三',10,'合肥',4);

SELECT * FROM t_student;

Day04

/*
回顾:
a,什么是数据库
b,数据库的作用
c,常用的数据库 Oracle MySql
1.关系型数据库表的形式来进行存储的
2.非关系型数据库
MyBatis Hibernate 封装对象 DAO
d,SQL
通用性的操作数据库的一种语言,所有的
数据库都是根据这个SQL标准来进行实现。
但是不同的实现软件（DBMS）会在原有的
基础上添加了一些新的功能,这些新的
功能是不通用的,只专属于某一个数据库管理
软件,这也造成了SQL的兼容性问题。
e,SQL的分类
DDL
创建删除修改库表
create drop alter
DML
添加删除修改表中的数据
insert delete update
DCL
对用户权限的管理 DBA
授权和收回权限
DQL *****
select 查询
1.基本查询
select 列名1,列名2,,
from 表名
2.条件查询
过滤分组前过滤
where
3.模糊查询
like _ %
4.分组查询
group by
分组后 select 查询的列
只能是分组的列和组函数
不能出现其他列。

select job,count(*)
from emp
group by job;
5.分组后过滤
having
6.排序
order by
asc 默认的升序
desc 降序
7.分页
limit 起始条,每页的条数
起始条=每页的条数*(当前页-1)
TCL JDBC 再进行学习
事务
提交回滚
f,约束
1.主键约束
单独的列（常用）还可以是几个列联合（了解）
primary key
2.非空约束
not null
该列不能为空
3.唯一约束
unique
4.检查约束
MySql不支持
5.外键约束
开发的时候不添加外键开发完毕
再添加.

如果2张表之间有外键约束,添加数据
不是很方便。

笔记:
1.表与表之间的关系
a,一对一
b,一对多多对一常用
c,多对多常用
b,c如何进行表的设计
b,一对多
如果存在一对多的关系就存在谁是主表
谁有从表。一般情况下数据量越小就会
把他设计成主表。
多对多
2.DCL 权限的控制了解
a,创建用户
* CREATE USER 用户名@IP地址 IDENTIFIED BY '密码';
> 用户只能在指定的IP地址上登录
* CREATE USER 用户名@'%' IDENTIFIED BY '密码';
> 用户可以在任意IP地址上登录
b,给用户授权
* GRANT 权限1, … , 权限n ON 数据库.* TO 用户名@IP地址
> 权限、用户、数据库
> 给用户分派在指定的数据库上的指定的权限
> 例如；GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb1.* TO user1@localhost;
* 给user1用户分派在mydb1数据库上的create、alter、drop、insert、update、delete、select权限
* GRANT ALL ON 数据库.* TO 用户名@IP地址;
> 给用户分派指定数据库上的所有权限

c.撤销授权
* REVOKE 权限1, … , 权限n ON 数据库.* FROM 用户名@IP地址;
> 撤消指定用户在指定数据库上的指定权限
> 例如；REVOKE CREATE,ALTER,DROP ON mydb1.* FROM user1@localhost;
* 撤消user1用户在mydb1数据库上的create、alter、drop权限
d,查看权限
* SHOW GRANTS FOR 用户名@IP地址
> 查看指定用户的权限
e,删除用户
* DROP USER 用户名@IP地址
*/

-- 创建在指定IP地址登录的用户
CREATE USER tj01@192.168.3.33 IDENTIFIED BY '123';
CREATE USER tj02@localhost IDENTIFIED BY '123';
-- 创建在任意IP地址都可以登录的用户
CREATE USER tj03@'%' IDENTIFIED BY '123';
-- 默认初创的用户是没用任何权限的
-- 我们需要对用户进行授权操作

-- 给用户进行授权操作
GRANT SELECT,INSERT ON java1711.* TO tj03@'%';
SELECT * FROM dept;
-- 撤销用户的权限
REVOKE INSERT ON java1711.* FROM tj03@'%';
COMMIT;
-- 查看用户的权限
SHOW GRANTS FOR tj03@'%';
-- 删除用户
DROP USER tj01@192.168.3.33;
-- https://www.cnblogs.com/pengyin/p/6413936.html

/*
数据库的备份和还原
a,什么是数据库的备份
数据转换为 .sql文件的过程
mysqldump -u username -p dbname table1 table2 ...-> BackupName.sql
mysqldump -u root -p123 java1711 person > D:\backup.sql
mysqldump -u用户名 -p密码数据库>保存SQL的路径
b,什么是数据库的还原
.sql文件转换为数据的过程
mysql -u用户名 -p密码数据库<保存的SQL的路径
数据库的备份和还原不是针对数据库而是
针对数据库里面的数据。
https://www.cnblogs.com/kissdodog/p/4174421.html
*/

/*
多表查询
a,合并结果集
作用是将2次查询的结果进行合并
合并结果集有2种方式,一种是合并
之后去除重复的数据,还有一种是
保留所有的数据,合并结果集的要求
是合并的列个数必须一样。
UNION 合并去除重复的数据
UNION ALL 保留所有的数据
b,多表查询
在 from 后面跟上多个表名以,号隔开
查询的结果是2张表记录的乘积,这个乘积
被一个叫笛卡尔的人发现了,所以了又称之为
笛卡尔积。
*/
-- 合并结果集
DROP TABLE t_stu1;
CREATE TABLE t_stu1(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30)
);
INSERT INTO t_stu1(NAME) VALUES('张三');
INSERT INTO t_stu1(NAME) VALUES('李四');
INSERT INTO t_stu1(NAME) VALUES('王五');
DROP TABLE t_stu2;
CREATE TABLE t_stu2(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
address VARCHAR(40)
);
INSERT INTO t_stu2(NAME,address) VALUES('王五','合肥');
INSERT INTO t_stu2(NAME,address) VALUES('赵六','安庆');
INSERT INTO t_stu2(NAME,address) VALUES('钱七','芜湖');
INSERT INTO t_stu2(NAME,address) VALUES('孙八','马鞍山');

SELECT * FROM t_stu1
UNION ALL
SELECT NAME,address FROM t_stu2;

/*
多表查询
内连接
标准
非标准 ,隔开
外连接
左外连接
左边的数据一个都不能少
右外连接
右边的数据一个都不能少
全外连接
两边的数据一个都不能少
MySql不支持

*/
-- 非标准内连接简单
SELECT *
FROM emp,dept
WHERE emp.deptno=dept.deptno; -- 内连接
-- 标准的
SELECT *
FROM emp INNER JOIN dept
ON emp.deptno=dept.deptno;

-- 查询出所有员工的姓名职位部门名称
SELECT ename,job,dname
FROM emp e,dept d
WHERE e.deptno=d.deptno;
/*
内连接去笛卡尔积的特点是只可以显示
满足条件的数据,不满足条件不会显示。
*/
-- 左外连接左边的表数据一个都不会少
SELECT ename,job,IFNULL(dname,'无部门') '部门'
FROM emp e LEFT JOIN dept d
ON e.deptno=d.deptno
UNION
SELECT ename,job,IFNULL(dname,'无部门') '部门'
FROM emp e RIGHT JOIN dept d
ON e.deptno=d.deptno;

-- 查询出工资比狮子狗高的员工的姓名和薪资
SELECT *
FROM emp
WHERE sal>(SELECT sal
FROM emp
WHERE ename='狮子狗');

SELECT AVG(sal)
FROM emp;

/*
子查询可以写在2个地方
一个在 where 后面作为条件
一个在 from 后缀作为表
*/

SELECT ee.部门
FROM (SELECT ename,job,IFNULL(dname,'无部门') '部门'
FROM emp e LEFT JOIN dept d
ON e.deptno=d.deptno
UNION
SELECT ename,job,IFNULL(dname,'无部门') '部门'
FROM emp e RIGHT JOIN dept d
ON e.deptno=d.deptno) ee
WHERE ee.ename='菊花信'

Day05

-- 1.查出至少有一个员工的部门。显示部门编
-- 号、部门名称、部门位置、部门人数。
-- 列部门编号部门名称部门位置 dept
SELECT * FROM dept;

SELECT ee.deptno,d.dname,d.loc,ee.cnt
FROM (SELECT deptno,COUNT(*) cnt
FROM emp
GROUP BY deptno) ee LEFT JOIN dept d
ON ee.deptno=d.deptno;

SELECT *
FROM (SELECT COUNT(*),e.deptno
FROM emp e
GROUP BY e.deptno) ee LEFT JOIN
(SELECT d.deptno,dname,loc
FROM dept d) dd
ON ee.deptno=dd.deptno AND ee.deptno IS NOT NULL

-- 2.列出所有员工的姓名及其直接上级的姓名。
SELECT * FROM emp e,dept d WHERE e.deptno=d.deptno

SELECT e1.ename 员工姓名 ,IFNULL(e2.ename,'无') 上级领导
FROM emp e1 LEFT JOIN emp e2
ON e1.mgr=e2.empno

-- 3.列出受雇日期早于直接上级的所有员工的编号、
-- 姓名、部门名称。
SELECT e.empno, e.ename, d.dname
FROM emp e, emp ee, dept d
WHERE e.deptno=d.deptno AND e.mgr=ee.empno AND e.hiredate<ee.hiredate;
SELECT * FROM emp;
-- 4.列出部门名称和这些部门的员工信息，同时
-- 列出那些没有员工的部门。
SELECT *
FROM emp e1 LEFT JOIN dept d1
ON e1.deptno = d1.deptno
UNION
SELECT *
FROM emp e2 RIGHT JOIN dept d2
ON e2.deptno = d2.deptno;
-- 5.列出最低薪金大于15000的各种工作及从
-- 事此工作的员工人数。
SELECT job,COUNT(*) 'cnt'
FROM emp
GROUP BY job
HAVING MIN(sal)>15000
-- 6.列出在销售部工作的员工的姓名，假定不
-- 知道销售部的部门编号。
SELECT ename
FROM emp
WHERE deptno=(SELECT deptno FROM dept WHERE dname='销售部' )

-- 7.列出薪金高于公司平均薪金的所有员工信
-- 息，所在部门名称，上级领导，工资等级。
SELECT e.*,d.dname,m.ename,s.grade FROM emp e
LEFT JOIN dept d ON e.deptno=d.deptno
LEFT JOIN emp m ON e.mgr=m.empno
LEFT JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE e.sal>(SELECT AVG(sal)FROM emp)
-- 8.列出与狮子从事相同工作的所有员
-- 工及部门名称。
SELECT ename,job,dname
FROM emp,dept
WHERE emp.deptno=dept.deptno
AND job= (SELECT job
FROM emp
WHERE emp.ename='狮子狗');

-- 9.列出薪金高于在部门30工作的所有员工的薪
-- 金的员工姓名和薪金、部门名称。
SELECT e.ename,e.sal,IFNULL(dname,'无部门')
FROM dept RIGHT JOIN (SELECT *
FROM emp
WHERE sal>(SELECT MAX(sal)
FROM emp
WHERE deptno=30)) e
ON e.deptno=dept.deptno