第一次javaweb（MySQL）基础笔记

数据库的定义

现在的存储方式

• java程序存储数据（变量，数组，对象，集合），数据保存在内存中，属于临时存储。

• 文件（File）存储数据，保存在硬盘上，属于长久状态存储

  缺点

• 没有数据类型的区分

• 存储数量级较小

• 没有访问安全限制

• 没有备份，恢复机制

什么是数据库

数据库是按照数据结构来组织、存储和管理数据的仓库，是一个长期存储在计算机内的有组织，可以共享，统一管理的数据集合。

网状数据库	以节点形式存储和访问
层次结构数据库	定向有序的树状结构进行存储和访问
关系结构数据库（MySql）	以表格（Table存储，多表间建立关系，通过分类，合并，连接，选取等运算实现访问）
非关系型数据库	多数使用哈希表，表中以键值的方式实现特定的键和一个指针指向的特定数据

数据库管理系统

指一种操作和管理数据库的大型软件，用于建立，使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性，用户通过数据库管理系统访问数据库中的数据。

常见的数据库管理系统：Oracle，DB2，SQl Server

SQL语言

SQL结构查询语言，用于存储数据，更新，查询和管理关系数据库系统的程序性语言。
通常执行对数据库的“增删改查”C（Creat）,R(Read),U(Update),D(Delete).

命令指示符对数据库的操作

创建自定义数据库	mysql>CREATE DATABASE mydb1;//创建数据库
	mysql>CREATE DATABASE mydb2 CHARACTER SET gbk;//创建数据库并设置编号
	mysql>CREATE DATABASE IF NOT EXISTS mydb3;//如果数据库mydb3不存在则创建
查看数据库创建信息	SHOW CREATE DATABASE mydb2 ;
修改数据库	ALTER DATABASE mydb2 CHARACTER SET gbk;
删除数据库	DROP DATABASE mydb1;
查看当前数据库	SELECT DATABASE(); //先使用数据库再查看
使用数据库	USE mydb1;

数据查询

关系结构数据库是由表（table）进行数据存储，由行和列组成。

进行查询操作时显示出来的是一张虚拟表，并不会改变原表的值。

基本查询

语法：SELECT 列名 FROM 表名

• 当查询所有列时： select 所有的列名 from 表名
  注意：也可以使用 * 号代替所有的列但是 星号需要转换成所有的列，效率低，可读性差，所以优先使用列名查询

对列中的数据进行运算（列的数据类型应该是数值列，否则不能进行运算）

算数运算符	描述
+	两列相加
-	两列相减
*	两列相乘
/	两列相除

注：%是占位符不是模运算符

列的别名
在我们查询时为了使我们清楚了解查询的是什么，我们可以建立它的别名

语法： as 别名

select employee_id as '员工编码',salary as'年薪' from t_employees;

查询结果去重
通常我们创建的数据中有许多的重复，如果不想让其重复出现可以添加“DISTINCT”

语法：distinct 列名

select distinct manager_id from t_employees;

排序查询

语法：select 列名 from 表名 ORDER BY 排序列 [排序规则]（方括号可以不加）

排序规则	描述
ASC	对前面的排序列做升序排序
DESC	对前面的排序列做降序排序

**单列排序**

select EMPLOYEE_ID,salary from t_employees order by salary asc;
select EMPLOYEE_ID,salary from t_employees order by salary desc;

**多列排序**
在前方排序列中有相同的数据后，后面的排序列才能有用并按照后面的排序规则排序

select EMPLOYEE_ID,salary from t_employees order by salary desc,EMPLOYEE_ID desc;

条件查询

语法：select 列名 from 表名 where 条件
where 条件：在查询结果中，筛选符合条件的结果，条件为布尔表达式，只有布尔表达式的值为真才执行。

等值判断（=）

在 sql语言中等值判断是（=），Java是（==）

select EMPLOYEE_ID,FIRST_NAME,salary
from t_employees
where salary = 11000;

逻辑判断（and ,or,not）

select EMPLOYEE_ID,FIRST_NAME,salary
from t_employees
where salary = 11000 and COMMISSION_PCT=0.3;
#查询符合两个条件的数据


select EMPLOYEE_ID,FIRST_NAME,salary
from t_employees
where salary = 11000 or COMMISSION_PCT=0.3;
#查询满足两个条件中一个的数据

select EMPLOYEE_ID,FIRST_NAME,salary
from t_employees
where not salary = 11000;
#查询不满足条件的数据

不等值判断（>,<,>=,<=,!=,<>）

需注意的是 != 和 <> 都表示不等于的意思

select EMPLOYEE_ID,FIRST_NAME,salary
from t_employees
where  salary != 11000;


select EMPLOYEE_ID,FIRST_NAME,salary
from t_employees
where  salary <> 11000;
#!= 和 <> 都是不等于

区间判断（between…and…）

区间前面的数值一定要比后面的小

select EMPLOYEE_ID,FIRST_NAME,salary
from t_employees
where  salary between 6000 and 10000;

NULL值判断

* IS NULL
	列名 IS NULL
* IS NOT NULL
	列名 IS NOT NULL

判断是否为空值时不能使用“=”号，需要使用 IS NULL或者 IS NOT NULL

SELECT EMPLOYEE_ID,FIRST_NAME,SALARY
FROM t_employees
WHERE MANAGER_ID IS NOT NULL;

枚举查询

列名 IN（值1，值2，值3）

需要注意的是枚举查询中的枚举的值只要满足其中一个就可以，相当于各个值之间有个“or”。

SELECT EMPLOYEE_ID,FIRST_NAME,SALARY
FROM t_employees
where DEPARTMENT_ID in(70,80,90);

模糊查询

*  LIKE _(单个任意字符)
* 列名 LIKE '张_'（这个下划线是可以增加个数的有几个下划线就代表几个任意字符）
*  LIKE%（任意个字符）
* 列名 LIKE '张%'

需注意的是模糊查询只能和LIKE关键字结合使用

SELECT EMPLOYEE_ID,FIRST_NAME,SALARY
FROM t_employees
WHERE FIRST_NAME LIKE 'L__';


SELECT EMPLOYEE_ID,FIRST_NAME,SALARY
FROM t_employees
WHERE FIRST_NAME LIKE 'L%';

分支结构查询

CASE
	WHEN 条件1 THEN 结果1
	WHEN 条件2 THEN 结果2
	WHEN 条件3 THEN 结果3
	ELSE 结果4
END AS '别名'
FROM 表名

AS ‘别名’是因为查询出来的虚拟表的列名会变成代码块，加了别名这样可以简单并且好理解

SELECT EMPLOYEE_ID,FIRST_NAME,SALARY,
CASE
		WHEN SALARY >10000 THEN 'A'
		WHEN SALARY >6000 AND SALARY <=8000 THEN 'B'
		WHEN SALARY >4000 AND SALARY <=6000 THEN 'C'
		ELSE 'D'
END AS '薪资分阶'
FROM t_employees;

时间查询

SELECT 时间函数（参数列表）；
SELECT 时间函数（）；

执行时间查询会生成一张虚拟表（一行一列）；

时间函数	描述
SYSDATE()	查询系统当前的时间（包括日期和时间）
CURDATE()	查询当前系统日期
CURTIME()	查询当前时间（只有时间）
WEEK(DATE)	查询日期是该年的第几周
YEAR(DATE)	查询日期中的年份
HOUR(TIME)	查询时间中的小时部分
MINUTE(TIME)	查询时间中的分钟部分
DATEDIFF(DATE1,DATE2)	查询两个日期之间的天数间隔
ADDDATE(DATE,N)	在日期的基础上加上天数

需注意DATEDIFF 中前面的日期要小于后面的日期

#当前系统时间
SELECT SYSDATE();
#当前系统日期
SELECT CURDATE();
#当前系统时间
SELECT CURTIME();
#获取当前年份的第几周
SELECT WEEK(SYSDATE());
#获取当前指定日期的年份
SELECT YEAR('2020-4-1');
#获取小时值
SELECT HOUR(CURTIME());
#获取分钟值
SELECT MINUTE(CURTIME());
#指定日期相隔的天数
SELECT DATEDIFF('2020-7-9','2020-3-8');
#指定日期再加上天数
SELECT ADDDATE('2020-3-9',6);

字符串查询

SELECT 字符串函数（参数列表）

字符串函数	描述
CONCAT(str1,str2,str3…)	讲多个字符串连接
INSERT(str,pos,len,newstr)	将str中指定的pos位置开始len长度的内容替换成newstr
LOWER(str)	将字符串的内容变成小写
UPPER(str)	将字符串的内容变成大写
SUBSTRING(str,num,len)	截取str中从num开始的位置len长度内容出来

需注意的是MySQL中字符串下标位置是从1开始，而不是java中从0开始
，字符串连接也可以将表中各个列中的字符串连接

#多个字符串拼接
SELECT CONCAT('MY','S','QL');
SELECT CONCAT(FIRST_NAME,LAST_NAME) FROM t_employees;#MYSQL

#字符串替换
SELECT INSERT('这是一个数据库',3,2,'MYSQL');#这是一个MYSQL数据库
#字符串转小写
SELECT LOWER('MYSQL');#mysql
#字符串转大写
SELECT UPPER('mysql');#MYSQL
#字符串截取
SELECT SUBSTRING('JAVAMYSQLPRACLE',5,5);#MYSQL

聚合函数

SELECT 聚合函数（列名）FROM 表名

聚合函数是对一列的多条数据进行运算，返回统计后的一行结果。

聚合函数	描述
SUM()	求单列中所有行数据的总和
AVG()	求单列所有行数据平均
MAX()	求单列所有行最大值
MIN()	求单列所有行最小值
COUNT()	求对某列名中的属性在该列中出现的行数之和

需注意聚合函数对值为NULL 的行数是不会计算在内的

SELECT SUM(SALARY) FROM t_employees;
SELECT AVG(SALARY) FROM t_employees;
SELECT MAX(SALARY) FROM t_employees;
SELECT MIN(SALARY) FROM t_employees;
SELECT COUNT(EMPLOYEE_ID)FROM t_employees;

分组查询

SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组依据（列）

分组是以某列中的数据分组

查询单个列的分组依据

SELECT DEPARTMENT_ID,COUNT(EMPLOYEE_ID)
FROM t_employees
GROUP BY DEPARTMENT_ID;

查询多个列的分组依据

后面的列是要前面的列为依据再进行分组，即先对前面的列进行分组，再对后面的列分组

SELECT DEPARTMENT_ID,JOB_ID,COUNT(EMPLOYEE_ID)
FROM t_employees
GROUP BY DEPARTMENT_ID,JOB_ID;

常见问题

SELECT DEPARTMENT_ID,COUNT(*),FIRST_NAME
FROM t_employees
GROUP BY DEPARTMENT_ID;

在该代码中只显示出来了DEPARTMENT_ID，FIRST_NAME虽然显出出来了结果但并不完整。所以分组查询只是显示出来分组依据的结果或者聚合函数列，不会出现其它的列

分组过滤查询

	SELECT 列名 FROM 表名  WHERE 条件 GROUP BY 查询依据 HAVING 过滤规则

HAVING 过滤规则是对分组后的数据进行过滤

SELECT DEPARTMENT_ID ,MAX(SALARY)
FROM t_employees
GROUP BY DEPARTMENT_ID
HAVING DEPARTMENT_ID IN(60,70,90);

最后显示的是60，70，90三个部门编号和对应的最高工资

限定查询

SELECT 列名 FROM 表名 LIMIT 起始行，查询行数

与前面字符串不同的是行数下标是从0开始的，0表示第一行，第二个参数是你想要查询的行数

SELECT * FROM t_employees LIMIT 0,5;#表示从第1行开始查询了5行
SELECT * FROM t_employees LIMIT 5,5;#表示从第六行开始查询了5行
SELECT * FROM t_employees LIMIT 10,5;#表示从第11行开始查询了5行

查询总结

SQL语句编写顺序

	SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 查询依据
HAVING 过滤规则 ORDER BY 排序列（asc|desc）LIMIT 起始行，总行数

SQL语句执行顺序

1.FROM :  指定数据来源表
2.WHERE：对查询数据做第一次过滤
3.GROUP BY：分组
4.HAVING：对分组后的数据做第二次过滤
5.SELECT：查询各字段的值
6.ORDER BY：排序
7.LIMIT：限定查询结果

子查询

单行单列

SELECT 列名 FROM 表名 WHERE 子查询结果

• 将子查询结果单行单列作为外部查询的条件，再做第二次查询
• 子查询得到的一行一列的结果作为外部查询等值或者不等值的判断条件

SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'bruce';#结果出来是6000
#查询工资大于6000
SELECT * FROM t_employees WHERE SALARY > 6000;
#整合
SELECT * FROM t_employees WHERE SALARY >( SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'bruce');

子查询（枚举查询，多行单列）

SELECT 列名 FROM 表名 WHERE 列名 IN （子查询结果）

将子查询的结果多行单列作为外部查询的枚举查询条件，做第二次查询

#子查询（单列多行）
SELECT * FROM t_employees WHERE LAST_NAME='KING';#得到的部门编号有80，90
SELECT * FROM t_employees WHERE DEPARTMENT_ID IN(80,90);#查询部门编号为80，90的信息
#整合
SELECT * FROM t_employees WHERE DEPARTMENT_ID IN(SELECT DEPARTMENT_ID FROM t_employees WHERE LAST_NAME='KING');#因为需要得到的值是编号所以不能再用星号

子查询（ALL,ANY关键字使用，多行单列）

ALL 关键字表示满足子查询结果中的所有条件，ANY关键字表示满足子查询结果的部分条件也可以理解位满足其中一个条件就可以。

#查询工资高于60部门的员工信息
SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID = '60';
#整合
SELECT * FROM t_employees WHERE SALARY >ALL (SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID = '60');#即大于部门60中最高工资
SELECT * FROM t_employees WHERE SALARY >ANY (SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID = '60');#即大于部门60中最低工资即可

子查询（多行多列即一张表）

SELECT 列名 FROM （子查询结果集）WHERE 条件；

子查询结果是一张表时需要在其后面给该表定义个名字（AS 表名）

#子查询（多行多列即一张表）
#查询工资前五位的工资，编号和名字先做一张工资降序表再从里面找出前五个
SELECT EMPLOYEE_ID,SALARY,FIRST_NAME FROM t_employees ORDER BY SALARY DESC;
SELECT * FROM  (SELECT EMPLOYEE_ID,SALARY,FIRST_NAME FROM t_employees ORDER BY SALARY DESC)AS temp
LIMIT 0,5;

• 将子查询“多行多列”的结果作为外部查询的一张表，做第二次查询
• 注意：子查询是临时表时，需要为其赋一个临时表名

合并查询

SELECT * FROM 表名 UNION SELECT * FROM 表名
SELECT * FROM 表名 UNION ALL SELECT * FROM 表名

UNION 合并两表会去除重复的记录，合并结果的两张表列数必须相同，数据类型可以不同

SELECT * FROM t_departments UNION SELECT * FROM t_jobs 

UNION ALL合并两表会不去除重复的记录，合并结果的两张表列数必须相同，数据类型可以不同

SELECT * FROM t1 UNION  ALL SELECT * FROM t2 ;

表连接查询

SELECT 列名 FROM 表名1 连接方式 ON 表名2

内连接查询（INNER JOIN ON）
结果是将两张表的数据合并，但是要ON后面的属性对应

#通用，查询所有员工的信息（不包括没有部门的）
SELECT * FROM t_employees
INNER JOIN t_jobs
ON t_employees.JOB_ID = t_jobs.JOB_ID;

#SQL标准
SELECT * FROM t_employees,t_jobs WHERE t_employees.JOB_ID=t_jobs.JOB_ID;

三表连接查询

#三表连接
SELECT EMPLOYEE_ID,t_employees.DEPARTMENT_ID,t_departments.LOCATION_ID FROM t_employees
INNER JOIN t_departments
ON t_departments.DEPARTMENT_ID=t_departments.DEPARTMENT_ID
INNER JOIN t_locations
ON t_departments.LOCATION_ID=t_locations.LOCATION_ID;#其中t_employees和t_departments有相同department_id,t_departments 和t_locations有一样的location_id,需要指定是哪张表的属性否则会报错

左外连接（LEFT JOIN ON）

以左表为主表向右匹配，匹配到，返回值，匹配不到，返回NULL

#左表连接
SELECT EMPLOYEE_ID,salary,DEPARTMENT_NAME FROM t_employees
LEFT JOIN t_departments
ON t_employees.DEPARTMENT_ID=t_departments.DEPARTMENT_ID;

右外连接（RIGHT JOIN ON）

以右表为主表向左匹配，匹配到，返回值，匹配不到，返回NULL

SELECT EMPLOYEE_ID,salary,DEPARTMENT_NAME FROM t_employees
RIGHT JOIN t_departments
ON t_employees.DEPARTMENT_ID=t_departments.DEPARTMENT_ID;

DML操作

新增（INSERT）

INSERT INTO 表名 （列名1，列名2，列名3....）VALUES (值1，值2，值3...)

INSERT INTO t_jobs(JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
VALUES('JAVA_A','ADVANGE','2500','9000');

表名后的列名要和VALUES后面的值一一对应（个数，顺序，数据类型）

修改（UPDATE）

	UPDATE 表名 SET 列名 1= 新值 ，列名2=新值2...,WHERE 条件

要注意增加where条件，否则就会更新整张表

UPDATE t_employees SET SALARY='25000' WHERE EMPLOYEE_ID='100';

删除（DELETE）

DELETE FROM 表名 WHERE 条件

同时也要注意where条件，否则会删除整张表，一般删除条件所在的元组；

DELETE FROM t_employees
WHERE EMPLOYEE_ID='135';

清空整表数据（TRUNCAT）

TRUNCAT TABLE 表名

TRUNCAT 与 DELETE 的区别是DELETE 删除了表的数据，但是表的结构并为改变，而TRUNCAT 是将整张表销毁再按照该表的结构重新创建一个表

TRUNCATE TABLE t1;

数据表操作

数据表类型

MySQL数据类型大致分为三类：数值，日期/时间和字符串类型。

数值类型

日期类型

字符串类型

简单来讲，CHAR大小如果定义为20只输入一个字母a，系统会自动填满剩下的19大小空间，最后占用的还是20大小的空间，而VARCHAR你如果定义大小20，只输入一个字母a，它会自动缩减大小最后占用的空间只为1个，即a所占用的。

数据表的创建

CREATE TABLE 表名（
列名1 数据类型 【约束】，
列名2 数据类型 【约束】，
....

列名n 数据类型 【约束】#最后一个列名不用加逗号	



）【	charset=utf8】；#可根据指定表的字符编码集

数据表的操作

数据表的修改（ALTER）

ALTER TABLE 表名 操作；

修改表中的列（MODIFY）

ALTER TABLE `SUBJECT` MODIFY subjectNAME VARCHAR (10);
#修改了表中的数据类型

删除表中的列(DROP)

#删除表中的列
ALTER TABLE `SUBJECT` DROP gradeID;

修改列名（CHANGE）

ALTER TABLE `SUBJECT` CHANGE subjectHOURS classHOURS INT;

修改表名（RENAME）

ALTER TABLE `SUBJECT` RENAME `SUB`;

删除表

	DROP  TABLE 表名；

DROP TABLE `SUBJECT`;

约束

实体完整性约束

	表中的一行数据代表一个实体，实体完整性的作用即表示每一行数据不重复，实体唯一

主键约束

		列名 数据类型 PRIMARY KEY ，表示表中的一行数据，此列的数据不能重复，且不能为NULL

主键一般是编号，ID，因为主码表示的列名要能够判别数据的唯一。

唯一约束

	UNIQUE ，表示表中的一行数据，此列不能重复，可以为NULL

自动增长列

	AUTO_INCREMENT，自动增长，给主键数值列添加自动增长，从1开始，每次加1，不能单独使用，每次配合

由于自动加1，所以主键的内容需要是整型，且插入内容不用写主键编号，它会自动排好

CREATE TABLE `SUBJECT`(

subjectID INT PRIMARY KEY AUTO_INCREMENT,#Z主码
subjectNAME VARCHAR(20)	UNIQUE,
subjectHOURS INT


)CHARSET=utf8;#指定该表创建数据的字符集

域完整性约束

	限制列的单元格的数据完整性

非空约束

	NOT NULL，非空，此列必须有值

默认值约束

	DEFAULT 为列赋予默认值，当新增数据不指定值时，书写default，以指定的默认值填充

create table `subject`(
subjectid int primary key AUTO_INCREMENT,
subjectName varchar(20)unique not null,
subjectHours int DEFAULT 10
)charset=utf8;


insert into `subject`(subjectName,subjectHours)values('c++',default);

当你插入数据不知道取何值时，在插入公式中加入default，插入的数值为默认值即为10

引用完整性约束

	CONSTRAINT 引用名 FOREIGN KEY 列名 REFERENCES 被引用表名（列名）

FOREIGN KEY 引用外部表的某个列的值时，新增数据时结束此列的值必须是被引用表中存在的值。

create table speciality(
id int primary key,
specialityName varchar(20)not null
)charset=utf8;


insert into speciality(id,specialityName)values(1,'sql');
insert into speciality(id,specialityName)values(2,'my');
select * from speciality;


create table `subject`(
subjectid int primary key AUTO_INCREMENT,
subjectName varchar(20)unique not null,
subjectHours int DEFAULT 10,
specialityid int not null,
constraint fk_subject_specialityid foreign key (specialityid)references speciality(id)
)charset=utf8;

当两张表有关系时，需要先删除引用表，才能删除被引用表；

事务

模拟转账

#转账模拟
start transaction;
create table change_money(

id int primary key auto_increment,
money int not null

)charset=utf8;
insert into change_money(money)values(9000);
insert into change_money(money)values(8000);
select * from change_money;
update change_money set money=money-1000 where id =1;
update change_money set money=money-1000 where id =2;
#执行成功
commit;
#执行失败
rollback；

如果在该操作时账户1减钱成功，而账户2加钱操作有问题，则结果会显示账户1少了1000，而账户2并未加钱1000；

每条SQL语句都是一个独立的操作，一个操作执行完成会对数据库产生永久性的影响。

事务概念

事务是一个原子操作，是一个最小执行单位。可以由一个或者多个SQL语句组成
。在同一个事务中所有的	SQL语句都执行成功，则事务成功，
有一个SQL语句失败,则整个事务失败。

事务的边界

* 开始：连接到数据库，执行一条DML语句。上一个事务结束后， 又输入
一条DML语句，则是事务的开始

* 结束：
	1.提交
		a.显示提交：commit；
		b.隐式提交：一条创建，删除的语句，正常退出(客户端退出连接)
	2.回滚
		a.显示回滚：rollback;
		b.隐式回滚：非正常退出（断电...），执行了创建，删除语句,但是失败
		了，会为无效的语句执行回滚；

在上述情况中
如果在该操作时账户1减钱成功，而账户2加钱操作有问题，执行回滚语句，则账户1的账户会回到减钱成功前的状态，账户2 保持不变。

注意：执行事务后（start transaction），执行的语句属于当前事务，成功再执行commit，失败执行rollback；

权限管理

创建新用户：create user 用户名 indentified by 密码
授权：grant all on 数据库.表 to 用户名
撤销权限：revoke all on 数据库.表 from 用户名

密码必须是字符串

视图

创建的视图出来的表是会被保存的，原表数据改变视图也会改变。可以通过视图查询直接查询之前的数据。

视图的修改与删除
修改

	create or replace view 视图名 as 查询语句
	alter view 视图名 as 查询语句

删除

drop view 视图名