MySQL-1
-
- 一、MySQL介绍
- 二、MySQL与Navicat 安装
- 三、创建表
- 四、SQL语言
-
- 建立表
- DDL创建
- DML增删改
- DQL查
- 五、函数
- 六、连接
- 七、统计函数
- 八、子查询
-
- 子查询定义
- 子查询的位置
- 相关子查询
- 增强版修改和删除
- 九、其他
-
- 导入导出数据库
- union 和union all 区别
- 约束
- 视图
- 索引
一、MySQL介绍
MySQL 是时下最流行的关系型数据库管理系统，很多企业都使用MySQL，优点自行百度。
由于MySQL是数据库管理系统，所以我们在学习的时候需要安装一个服务器端(MySQL)和一个客户端(Navicat)。
二、MySQL与Navicat 安装
安装的版本如下，教程就不做记录了，需要安装包可留言。
三、创建表
MySQL里面可以创建很多的数据库，例如，创建一个企业OA系统的数据库
数据库里可以有很多张表(table)，数据是存储在表里，如：
| 部门编号 | 部门名称 | 部门地址 | 备注 |
|---|---|---|---|
| 1 | 开发部 | 大连高新园区 | 无 |
| 2 | 人事部 | 大连高新园区 | 无 |
表里的列也叫做字段，一行数据也叫做一条记录
- 在左侧空白处右键，新建数据库，自定义数据库名，字符集与排序规则可为空，字符集默认为UTF-8；
Java全栈大数据学习笔记NO.14（MySQL-1） - 右键点击表-新建表，这时就在创建字段了，每个字段代表该列存放的信息；
Java全栈大数据学习笔记NO.14（MySQL-1） - 名就：字段名
- 类型按需要选择，这里介绍一下字符串类型varchar和char
- char类型是固定长度字符串，按照长度来分配空间
- varchar类型是可变长度字符串，根据存储数据的长度来分配空间，但是不能超多指定的最大值
- 不是null选择之后在创建记录的时候就必须要设置值
- 钥匙：主键，任何一张表，都建议设一个主键，主键是唯一标识表里的每一条记录，我们可以指定某个列作为主键，该列的值不允许为null也不允许重复。如果表里没有哪些合适的列作为主键，可以加一个自动增长标识列作为主键。
四、SQL语言
任何数据库操作，都可以通过sql来实现，按sql语句功能的不同，可将sql进行分类
- DDL语言，实现创建，修改或删除数据库对象，叫做数据库定义语言
- DML语言，数据操作语言，做表里的数据做增删改的功能
- DQL语言，数据查询语言，对表里的数据做查询操作的功能
- DCL语言，数据控制语言，控制数据库用户权限的语言
- TCL语言，事务控制语言。
sql语言有两个版本，92版和99版，99是92的升级
在左侧数据库中右键点击查询-新建查询，在打开的查询编辑器中编写程序，注意保存
建立表
在代码介绍之前，先创建4个表，以便下面代码实现
表名：dept（部门编号，部门名称，部门所在地）
表名：emp（员工编号，员工名字，员工职务，所属上级编号，入职日期，工资，奖金，所在部门编号）
表名：job_grades（职务等级，最低工资，最高工资）
表名：jobs（职务编号，职务名称，最低工资，最高工资）
DDL创建
是针对数据库对象的创建、修改或者删除，关键字对应的是 create、alter、drop，这部分只需能看懂即可。
-- 创建表 dept
DROP TABLE IF EXISTS `dept`; -- 如果表dept存在就删除该表
CREATE TABLE `dept` ( -- 创建表dept的字段
`deptno` int(2) NOT NULL COMMENT '部门编号',
`dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
`loc` varchar(13) DEFAULT NULL COMMENT '部门地址',
PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 删除表dept中的 deptno 字段
ALTER TABLE dept DROP deptno
-- 在表 dept 后面追加 daa 字段
ALTER TABLE dept ADD (`daa` varchar(10) DEFAULT NULL COMMENT '部门xx');
DML增删改
在此之前首先声明，MySQL中的语言不区分大小写，可以将程序选中之后右键单独执行选中程序，添加注释的方法为ctrl+/
-
添加数据
语法：
INSERT INTO 表名(列名列表)VALUES(值列表) 可选取部分字段进行赋值，其他为空
INSERT INTO 表名 VALUES(值列表) 必须对全部字段进行赋值
INSERT INTO 表名 VALUES(值列表),VALUES(值列表),~~~ 赋值多个数据
INSERT INTO dept (id,dname,loc) VALUES(2,'综合部','沈阳'); INSERT INTO dept VALUES(3,'财务部','大连'); INSERT INTO dept VALUES(4,'业务部','大连'),(5,'人力部','大连'); -
删除数据
语法：
delete from 表名 删除整张表的数据
delete from 表名 where 条件 按条件删除
DELETE FROM dept; DELETE FROM dept WHERE loc = '大连' or id = 4; -
修改数据
语法：
update 表名 set 列名 = 新值，列名 = 新值… 修改整张表里所有记录相关字段的值
update 表名 set 列名 = 新值，列名 = 新值… where 条件 修改符合条件的所有记录的相关字段的值
UPDATE dept SET dname = '滕泰' WHERE loc = '大连'; UPDATE dept SET dname = '财务部',loc = '软件园' WHERE id = 3;
DQL查
- 查询数据：select 列名列表（或者是衍生列） from 表名
-- 查询全部列 SELECT * from emp; -- 1 查询每个员工的编号，姓名，工资，新工资 = 原始工资上浮25% -- FROM emp as e 代表在衍生列中可用 e 代表 emp SELECT e.empno,e.ename,e.sal*1.25 as newsal FROM emp as e; -
带条件的查询语句语法：select 列名列表（或者是衍生列） from 表名 where 条件
关系运算符：=，!=(<>),>,>=,<,<=
逻辑运算符：and 与，or 或，not 非
算术运算符：+，-，*，/
-- 2 查询在7900号部门员工的信息 SELECT * FROM emp as e WHERE e.empno=7900; -- 3 查询1985年以前入职的员工 SELECT * FROM emp WHERE hiredate < '1985-1-1'; -- 4 查询每个员工姓名，工资，奖金，实发工资（工资+奖金） SELECT e.ename,e.sal,e.comm,(e.sal+e.comm) as newsal FROM emp as e; - null值与任何数据做数学运算结果一定为null，null值与任何数据做比较，结果一定为false
- 去掉查询结果集合里的重复数据，用distinct关键字
- 特殊的关系运算符
- 空值验证使用 is 或者是 is not-
-- 5 查询奖金为空的员工的信息/查询没有部门的员工 SELECT * FROM emp WHERE comm IS NULL; SELECT * FROM emp WHERE deptno IS NULL; -- 6 查询奖金不为空的员工的信息 SELECT * FROM emp WHERE comm IS NOT NULL; - Between and 查询在某个范围内，只能做数字类型和日期类型的范围判断
-- 7 查询工资在3000到5000之间的员工信息（两种方法） SELECT * FROM emp WHERE sal>=3000 AND sal<=5000; SELECT * FROM emp WHERE sal BETWEEN 3000 AND 5000; -- 8 查询在85年到90年之间入职的员工信息（两种方法） SELECT * FROM emp WHERE hiredate>='1985-1-1' AND hiredate<='1990-12-31'; SELECT * FROM emp WHERE hiredate BETWEEN '1985-1-1' AND '1990-12-31'; -
In(值列表)：是逻辑或，判断的字段的值只要等于（）里的其中一个数据就ok
Not in (值列表)：是逻辑与，判断的字段的值不能等于（）里任何一个数据
-- 9 查询（7369，7521，7654）以外的所有员工的信息 SELECT * FROM emp WHERE empno=7369 OR empno=7521 OR empno=7654; SELECT * FROM emp WHERE empno not in (7369,7521,7654); -- 10 查询奖金不为300和500的员工的信息 SELECT * FROM emp WHERE comm is null or comm not in (300,500); -
模糊查询，like，只有字符串类型的字段可以做模糊查询
如果使用like做模糊查询，必须和通配符一起使用才能达到效果
%:代表任意长度的字符串
_:代表一个长度的任意字符
通过转移字符 \ 可将通配符转化成普通字符-- 11 查询ename是以S开头的所有员工信息 %：通配符，代表任意长度的字符串 SELECT * FROM emp WHERE ename like 's%'; -- 12 查询ename是以s结尾的所有员工的信息 _：代表一个长度的任意字符 SELECT * FROM emp WHERE ename like '%s'; -- 13 查询ename是以M开头，并且第三个字母是R的员工信息 SELECT * FROM emp WHERE ename like 'M_R%'; -- 14 查询ename中包含AL的员工信息 SELECT * FROM emp WHERE ename like '%AL%'; -- 15 查询工资超过5000，并且ename是以s结尾的员工 SELECT * FROM emp WHERE sal>2000 and ename like '%s';-- 16 查询名字中含有 _ % 的员工 SELECT * FROM emp WHERE ename like '%\_%';
- 空值验证使用 is 或者是 is not-
-
对查询结果进行排序：order by 排序列 asc(升序)/desc降序
Order by后面可以写：1）列名2）列的索引3）列的表名 4）表达式
排序也可以按多列来排序，按order by 后面的排序列的顺序，进行主次排序
-- 17 查询员工信息，按入职日期降序排列 升序ASC 降序DESC SELECT * FROM emp ORDER BY hiredate DESC; -- 18 查询员工信息，按工资升序，按编号降序 SELECT empno,ename,sal,hiredate as date FROM emp ORDER BY sal ASC,empno DESC; -
限制查询返回条数：limit
LIMIT n 查询的是前n条数据
LIMIT n,m 查询的是从第n+1条开始，返回m条数据
-- 19 查询员工表的前5条数据 SELECT * FROM emp LIMIT 5; -- 20 查询员工表第 6,7 条数据 SELECT * FROM emp LIMIT 5,2;
五、函数
和java里的方法差不多，可以实现某些特定的功能，传递参数，有返回值
数学函数：
- 向上取整，dual是一个虚拟库，可作练习用
- 向下取整
- 生产0-1之间的随机小数
- 四舍五入，第二个参数保留精度，也可以不设置精度，默认取整
- 截断数字，第二个参数是保留精度
字符串函数：
- 将多个字符串拼成一个字符串
- 获取字符串字符的个数
- LOWER 将字符串转换为小写，UPPER 将字符串转换为大写
-
返回第一个字符串在第二个字符串中的位置，如果没有则返回0
LEFT 在字符串左侧截取指定个数字符，RIGHT 在字符串右侧截取指定个数字符
- 从字符串的指定位置截取指定长度的字符串
- 替换字符串，第一个参数是原字符串，第二个参数是被替换的子串，第三个参数是替换后的新子串
日期函数：
- 获取当前系统时间
- 分别获取日期的年、月、日、时、分、秒部分
SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW()),HOUR(NOW()), MINUTE(NOW()),SECOND(NOW()) from dual; - 将日期格式化成指定格式的字符串，此处大小写有区别（Y%和y%）
- 两个日期相减，返回天数
- 在日期的指定部分加值（正负均可）
SELECT DATE_ADD('2021-10-20',INTERVAL -1 YEAR) from dual; SELECT DATE_ADD('2021-10-20',INTERVAL 1 MONTH) from dual; SELECT DATE_ADD('2021-10-20',INTERVAL 1 DAY) from dual; - 获取两个日期的指定时间差
通用函数：
- 空值替换处理，把空值替换成指定值
六、连接
MySQL是关系型数据库，各个表之间不是独立存在，它们之间可能会存在主外键关系，比如
部门表 ：
| 部门编号 | 部门名称 |
|---|---|
| 10 | 开发部 |
| 20 | 综合部 |
员工表：
| 员工编号 | 部门编号 |
|---|---|
| 7639 | 10 |
| 7896 | 30 |
当一个表的某个列的数据来源于另外一张表，那么该列可以设置成外键。一旦两张表建立了主外键关系，那么如果在外键表（从表）插入的值在主键表（主表）里不存在，系统要报主外键错误！
如果要删除主表的数据，如果该数据在从表里有关联数据，需要先删从表数据，再删主表数据。
多表连接查询分为内连接和外连接
-
内连接查询
内连接的原理，当两表按关联条件内连接查询时，符合关联条件的数据，出现在结果集里，没有关联上的数据全部过滤掉
-- 1. 查询员工编号，姓名，所在部门名称（emp和dept）92语法内连接查询语句 SELECT e.empno,e.ename,d.dname FROM emp e,dept d WHERE e.deptno = d.deptno; -- 2 查询员工编号，姓名，部门编号，部门名称，职务编号，职务名称（三表联查） SELECT e.empno,e.ename,e.deptno,d.dname,j.job_id,job_title FROM emp e,dept d,jobs j WHERE e.deptno=d.deptno and e.job=j.job_id;--99语法：select 多表的列名列表 from table1 INNER JOIN table2 on 关联条件 -- INNER JOIN table3 on 关联条件 -- JOIN table4 on 关联条件(INNER可省略) -- WHERE 其他条件-- 3 查询员工编号，姓名，所在部门编号，部门的名称 SELECT e.empno,e.ename,e.deptno,d.dname FROM emp e INNER JOIN dept d on e.deptno=d.deptno; -- 4 查询部门编号是10的员工编号，姓名，部门编号，部门名称，职务编号，职务名称（三表联查） SELECT e.empno,e.ename,d.deptno,d.dname,e.job,j.job_title FROM emp e JOIN dept d on e.deptno=d.deptno JOIN jobs j on e.job=j.job_id WHERE e.deptno=10; -
外连接查询
原理：符合条件的数据出现在结果集里，但是外连接可以指定其中某一张表中不符合关联条件的数据也出现在查询结果集里。
被指定的这张表暂时认为是主表，左外连接（left join）主表放在左侧，右外连接（right join）就将主表放在右侧。
-- 5 查询员工编号，姓名，部门编号，部门名称（外连接，没有部门的员工也返回） SELECT e.empno,e.ename,e.deptno,d.dname FROM emp e LEFT JOIN dept d on e.deptno=d.deptno; SELECT e.empno,e.ename,e.deptno,d.dname FROM dept d RIGHT JOIN emp e on e.deptno=d.deptno; -- 6 查询员工编号，姓名，部门编号，部门名称（外连接，没有员工的部门也返回） SELECT e.empno,e.ename,d.deptno,d.dname FROM dept d LEFT JOIN emp e on e.deptno=d.deptno;
七、统计函数
多行函数也叫做聚合函数也叫做统计函数，分组函数
max、min、avg、sum默认是统计括号里指定的列的非空数据的最大值、最小值、平均值还有和
count（列名）：统计该列的值有几条数据（不算空值），*就是所有数据的条数
group by：按该列内的值分组
having：在select之后执行条件，where先执行条件再进行select
-- 1 统计函数
SELECT MAX(sal) 最大值,MIN(sal) 最小值,AVG(sal) 平均值,SUM(sal),COUNT(*) 计数
FROM emp WHERE deptno=10;
-- 2 统计每个部门的平均工资
SELECT AVG(sal),deptno FROM emp GROUP BY deptno;
-- 3 统计每个部门不同职务的平均工资
SELECT AVG(sal),deptno,job FROM emp GROUP BY deptno,job;
-- 4 统计每个部门不同职务的平均工资
SELECT AVG(sal),deptno,job FROM emp GROUP BY deptno,job HAVING AVG(sal)<3000;
-- 5 统计不同职务的个数
SELECT COUNT(*),job from emp GROUP BY job;
-- 6 统计每个部门的平均工资，部门名称，职务名称
SELECT AVG(sal),d.dname,e.job
FROM emp e
JOIN dept d on e.deptno=d.deptno
JOIN jobs j on e.job=j.job_id
GROUP BY d.dname,e.job;
程序执行的先后顺序：
八、子查询
子查询定义
在一个查询语句里，嵌套另外一个完整的查询语句，并且用括号括起来，那么该查询语句是子查询，外面的查询语句叫主查询或者是父查询。
子查询的位置
任何表达式存在的位置都可以是子查询
- where后嵌套子查询：执行顺序是子查询先执行，执行后主查询执行。
-- 1 谁的工资比BLAKE高？ SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='BLAKE'); - select后的衍生列可以是子查询：执行顺序为父查询查询一条数据，子查询执行一次，父查询查到多少条数据，子查询执行多少次。
-- 2 查询员工编号，姓名，工资，员工表的工资总和 SELECT empno,ename,sal,(SELECT SUM(sal) FROM emp) total FROM emp; - from 后的表名可以是子查询：将子查询的结果当成一张表来使用，进行二次查询数据，执行顺序，先执行一次子查询，再执行父查询。
-- 3 查询每个部门的平均工资，部门编号，经理编号，经理姓名 SELECT temp.avg,temp.deptno,e.empno,e.ename FROM (SELECT AVG(sal) avg,deptno FROM emp GROUP BY deptno) temp JOIN emp e on temp.deptno=e.deptno WHERE e.job='MANAGER';
相关子查询
子查询内使用了父查询的数据，执行顺序是父查询先查出一条数据，然后子查询使用父查询查到的那条数据里的某个字段进行查询，子查询查询之后的结果被父查询再次使用，有可能做衍生列也有可能作为筛选条件。
-- 4 查询员工编号，姓名，部门编号，工资，本部门工资的总和(相关子查询)
SELECT e.empno,e.ename,e.deptno,e.sal,(SELECT SUM(sal) FROM emp m WHERE m.deptno=e.deptno) FROM emp e;
-- 5 查询所有工资超过本部门平均工资的员工的信息
SELECT * FROM emp e WHERE sal>(SELECT AVG(sal) FROM emp WHERE deptno=e.deptno);
增强版修改和删除
MySQL中不支持以子查询为条件更新或删除数据，但是可以通过符合连接条件的方式更新或删除数据。
-- 6 修改工资最高的员工的工资为一万
UPDATE emp JOIN (select max(sal) maxsal from emp)temp
on emp.sal=temp.maxsal SET emp.sal=10000;
-- 查看该员工信息
SELECT * FROM emp HAVING emp.sal=(SELECT MAX(sal) FROM emp);
-- 7 部门的所有员工工资降薪10% 并且该部门的名称改为后勤部
UPDATE emp e JOIN dept d on e.deptno=d.deptno
SET e.sal=e.sal*0.9,d.dname='后勤部'
WHERE e.deptno=10;
-- 查看修改后的信息
SELECT * FROM dept WHERE deptno=10;
SELECT * FROM emp WHERE deptno=10;
以连接条件做删除。注意，删除不能同时删除多表的数据
-- 8 开除工资最高的员工
DELETE emp FROM emp JOIN (SELECT MAX(sal) maxsal FROM emp)temp
on temp.maxsal=emp.sal;
九、其他
导入导出数据库
-
导出
在对应数据库上右键选择转储SQL文件，结构和数据即可得到一个.sql的文件
Java全栈大数据学习笔记NO.14（MySQL-1） -
导入
将.sql文件直接拖拽到查询中，选择复制到当前位置，所有的表都以程序方式存在，点击运行后各种表就会出现在当前数据库中。
Java全栈大数据学习笔记NO.14（MySQL-1）
union 和union all 区别
相同点： union和union all 都能实现合并查询结果集
不同点： union可以去掉两个结果集里的重复数据、结果按默认规则排序,
union all不去重、不排序
-- 两表合并
SELECT empno,ename from emp union all select deptno,dname from dept;
约束
数据库里有5种约束，目的是约束插入表里的数据的准确性。
- 主键约束 primary key
- 外键约束 foreign key
- 非空约束 not null 与 默认值 default
- 唯一键约束，设置唯一约束的列，里面的值不允许重复，但是允许有一个null值 unique
- 检查约束也叫check约束，为某一列写插入数据时的检查条件，符合条件，数据插入成功，否则失败
Java全栈大数据学习笔记NO.14（MySQL-1）
视图
视图就是保存起来的查询语句，作为数据库的对象使用，它是一张虚表，视图里的查询语句如果有衍生列，一定要起别名。
-- 每个部门的平均工资，工资的最大值，工资总和，部门编号，部门名称，部门地址
CREATE OR REPLACE VIEW v_deptinfo
AS
SELECT d.deptno,d.dname,d.loc,AVG(sal) avgsal,
COUNT(e.deptno) num,MAX(sal) maxsal,SUM(sal) sumsal
FROM dept d LEFT JOIN emp e on e.deptno=d.deptno
GROUP BY d.deptno;
-- 直接调用视图,视图是一张虚表，可以进行查询或者与其他表进行连接
SELECT * FROM v_deptinfo WHERE num>=2;
-- 删除视图
DROP VIEW v_deptinfo;
简单的单表视图是可以进行修改或者删除操作的，但是实际操作的是真实的表。如果是复杂的视图，比如多表查询，子查询的，分组操作，没有办法进行修改或删除。
索引
索引的作用是加快查询速度,但是以dml操作的速度为代价。
什么样的列适合建索引：
- 表的数据量大
- 该列频繁做条件搜索
- 该列的值重复率低
- 该列存储数据需要的空间较小（数值类型优于字符类型）
Create index 索引名 on table(列名)
Alter index 索引名 on table(列名)
Drop index 索引名
索引的类型：主键索引、外键索引、唯一键索引、普通索引、全文索引