JavaWeb入门 day01----mysql数据库

不积跬步无以至千里，不积小流无以成江海

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

1.mysql的下载
官方网站:https://www.mysql.com/
下载地址：https://dev.mysql.com/downloads/mysql/

2对数据库的操作

1.查看数据库   show databases;

2.创建数据库   
create database mydbl;
同时设置编码
create database mydb2 character set gbk;

如果不存在mydb2再创建
create database if not exists mydb2;

3.查看数据库创建信息（返回的是一个表）
show create database mydb2;

4.修改数据库
alter database mydb2 character set utf8;

5.删除数据库
drop database mydb1;

6.查看当前所使用的数据库
select database();

7.使用数据库
use mydb2;

3.对数据库中表的操作

#1查询语句
select 列名 from 表名;
#1.1查询部分列 （该表大于3列）
select 列名1，列名2，列名3 from 表名；
#1.2.1查询所有列(使用*的方式)
select * from 表名;
#1.2.2查询所有列
select 列名1，列名2，列名3，列名4，列名5 from 表名;
注意：生产环境下，优先使用列名查询，*的方式需转换成全列名，效率低，可读性差

#1.3对列进行运算（+ - * /）
select salary*12 from employees;

#1.4列的别名 as
select salary*12 as “年薪” from employees;

#1.5去重 distinct
select distinct manager_id from employees;

#1.6.1单列排序查询 asc （默认） 升序 desc 降序
select 列名 from 表名 order by 排序列 【排序规则】;
#按照员工工资进行降序排序
select employee_id,salary from employees order by salary desc;

#1.6.2多列排序查询
#先按工资排序（降序），工资相同再按员工id进行排序（升序）
select employee_id,salary from employees order by salary desc，id asc;

#1.7条件查询
#语法: select 列名 from 表名 where 条件

#1.7.1等值判断 =(注：与java不同，mysql等值判断使用=)
select employee_id,first_name,salary from employees where salary=11000;

#1.7.2逻辑判断 and or not
select employee_id,first_name,salary from employees where salary=11000 and commission_pct=0.30;
select employee_id,first_name,salary from employees where salary=11000 or commission_pct=0.30;
select employee_id,first_name,salary from employees where not salary=11000;

#1.7.3不等值判断（> < >= <= != <>）
select employee_id,first_name,salary from employees where salary>=6000 and salary<=10000;

#1.7.4区间判断 between and(注:在区间判断中，小值在前，大值在后，否则不能得到正确的结果)
select employee_id,first_name,salary from employees where salary between 6000 and 10000;

#1.7.5空值判断 is null / is not null
#语法 列名 is null ; 列名 is not null
select employee_id,first_name,manager_id from employees where manager_id is null;
select employee_id,first_name,manager_id from employees where manager_id is not null;

#1.7.6枚举查询 IN
select employee_id,first_name,salary department_id from employees where department_id=70 or department_id=80 or department_id=90;
select employee_id,first_name,salary department_id from employees where department_id in (70,80,90);
注:in的查询效率比较低，可以通过多条件拼接

#1.7.7模糊查询 like
#语法： like_(单个任意字符) 列名 like “张_” 或 列名 like “张%”
select employee_id,first_name,salary from employees where first_name like “张%”；

#1.7.8分支结构查询
语法：
case
when 条件1 then 结果1
when 条件2 then 结果2
when 条件3 then 结果3
else 结果
end
经验：类似java中的switch
select employee_id,first_name,salary
case
when salary>10000 then ‘A’
when salary<=10000 and salary>8000 then ‘B’
when salary<=8000 and salary>7000 then ‘C’
end as ‘薪资级别’
from employees;

#1.8时间查询
#1.8.1当前系统时间 yyyy-MM-dd hh:mm:ss
select sysdate();

#1.8.2当前系统日期 yyyy-MM-dd
select curdate();

#1.8.3当前系统时间hh:mm:ss
select curtime();

#1.8.4获取指定日期为一年中的第几个周 week(Date date)
select week(sysdate());

#1.8.5获取指定日期的年份
select year(‘2020-10-10’);

#1.8.6获取小时值
select hour(curtime());

#1.8.7获取分钟值
select minute(curtime());

#1.8.8指定日期之间相隔的天数
select datediff(‘2020-10-10’,‘2019-10-10’);

#1.8.9计算Date日期加上n天后的日期
select adddate(‘2020-10-10’,5);

#1.9字符串查询
#1.9.1多个字符串做拼接
select concat(‘My’,‘SQL’,‘语言’);
select concat(first_name,last_name) as ‘姓名’ from employees;

#1.9.2字符串替换 insert(str,pos,len,newStr)
select insert(‘这是一个数据库’,‘3’,‘2’,‘MySQL’);

#1.9.3字符串转小写
select lower(‘MYSQL’);

#1.9.4字符串转大写
select upper(‘mysql’);

#1.9.5指定内容截取 substring(str,pos,len)
select substriing(‘JavaWeb’,2,2);

#1.10聚合函数(会自动忽略null值)
语法： select 聚合函数（列名） from 表名

#1.10.1求和函数 sum()
select sum(salary) from employees;

#1.10.2求平均值
select avg(salary) from employees;

#1.10.3求单列最大值
select max(salary) from employees;

#1.10.4求单列最小值
select min(salary) from employees;

#1.10.5求总行数
select count(employee_id) from employees;

#1.11分组查询 group by 列名 【分组，必须在where之后生效】

#1.11.1查询各部门的总人数
select department_id ,count(employee_id) from emloyees group by deparement_id;
#1.11.1.1查询各部门的平均工资
select department_id ,avg(salary) from employees group by department_id;
#1.11.1.2查询各个部门、各个岗位的总人数
select department_id,job_id count(employee_id) from employees group by department_id,job_id;

#1.12分组过滤查询
语法;select 列名 from 表名 where 条件 group by 分组列 having 过滤规则
注意:过滤规则定义在分组后
#1.12.1统计60,70,90号部门的最高工资
select department_id ,max(salary) from employees group by department_id having department_id in(60,70,90);

#1.13限定查询
语法：select 列名 from 表名 limit 起始行,查询行数

#1.13.1查询前5条数据
select * from employees limit 0,5;
#查询下一个5条数据
select * from employees limit 5,5;
#再查询下一个5条数据
select * from employees limit 10,5;
注：起始行从0开始，代表了第一行，第二个参数代表的是从指定的行开始查询几行

#limit的典型应用
#分页查询，一页显示10条，一共查询3页
selec * from employees limit 0,5;
select * from employees limit 5,5;
select * from employees limit 10,5;
经验：在分页应用场景中，起始行是变化的，但是一页显示的条数是不变的。

#总结
#SQL语句编写顺序
select 列名 from 表名 where 条件 group by 分组列 having 过滤条件 order by 排序列[排序规则] limit 起始行,总条数
#SQL语句执行顺序
1.from 指定数据的来源表
2.where 对查询数据进行第一次过滤
3.group by 分组
4.having 对分组后的数据进行第二次过滤
5.select 查询各字段的值
6.order by 排序
7.limit限定查询结果

#2.1子查询（作为条件判断）
语法: select 列名 from 表名 where 条件 （子查询结果）
#2.1.1查询工资大于Bruce的工资的员工信息
select * from employees where salary>(select salary from employees where first_name=‘Bruce’);
注:将子查询"一行一列"的结果作为外部查询的条件，做第二次查询
子查询得到一行一列的结果才能作为外部查询的等值判断条件或不等值条件判断、

#2.2子查询(作为枚举查询条件)
语法：select 列名 from 表名 where 列名 in（子查询结果）
#查询与名为King同一部门的员工信息
select * from employees where department_id in(select department_id from employees where last_name=‘King’);
注：将子查询“多行一列”的结果作为外部查询的枚举查询条件，做第二次查询

#2.3子查询 all any
#2.3.1查询大于部门号为60的员工工资的员工信息
select * from employees where salary>all(select salary from employees where department_id=60);
#2.3.2查询
select * from employees where salary>any(select salary from employees where department_id=60);
注:当子查询结果集形式为多行单列时可以使用any 或all关键字

#2.4子查询(作为一张表)
语法：select 列名 from （子查询的结果集）where 条件
#2.4.1查询员工表中工资排名前5名的员工信息
select * from (select * from employees order by salary desc) as temp limit 0,5;
注:将子查询“多行多列”的结果作为外部查询的一张表，做第二次查询
子查询作为临时表，为其赋予一个临时表名

#3.合并查询（了解）
语法:
select * from 表名1 union select * from 表名2
select * from 表名1 union all select * from 表名2
注意:列数必须相同，列的数据类型可以不同

#4.表连接查询
#4.1.1内连接查询 sql标准
select * from employees inner join jobs on employees.job_id=jobs.job_id;
#4.1.2内连接查询 mysql
select * from employees ,jobs where employees.job_id=jobs.job_id
注：在Mysql中，第二种方式，第二种方式也可以作为内连接查询，但是不符合sql标准
而第一种方式属于sql标准，与其他数据库通用。

#4.2左外连接查询
select e.employee_id,e.first_name,e.salary,d.department_name from employees e left join departments d on e.department_id=d.department_id;
employee为主表
注:左外连接，是以左表为主表，依次向右匹配，匹配到返回结果，匹配不到，则返回null值进行填充

#4.3右外连接查询
select e.employee_id,e.first_name,e.salary,d.department_name from employees e right join departments d on e.department_id=d.department_id;
departments为主表
注：右外连接，是以右表为主表，依次向左匹配，匹配到，返回结果，匹配不到，则返回null值填充

4.DML操作

#1新增insert
语法：insert into 表名 (列1，列2，列3,…) values (值1，值2，值3，…);
insert into employees (employee_id,first_name,salary)values(1,‘King’,7000);
注：表名后的列名要和values里的值一一对应(个数、顺序、类型)

#2.修改update
语法:update 表名 set 列1=值,列2=值，… where 条件;
update employees set salary=25000 where employee_id=100;
注：set后多个列名=值，绝大多数情况下都需要加where条件，否则就会整表更新

#3.删除 delete
语法: delete from 表名 where 条件
delete from employees where employee_id=1;
delete from employees where first_name=‘Peter’ and last_name=‘Lev’;

#4.清空整表数据 truncate
语法： truncate table 表名
truncate table coun;
注:与delete不加where删除整表数据不同，truncate是把表销毁，再按照原表的格式创建一张新表

5.数据表操作

#1数据表的创建
语法:
create table 表名(
列名 数据类型 [约束]；
列名 数据类型 [约束]；
…
列名 数据类型 [约束] #最后一列的末尾不加分号
)[charset=utf8] #可根据需要指定表的字符编码集
#1.1创建学生表
create table student(
student_id int,
student_name varchar(20),
student_age int
)charset=utf8;

#2.数据表的修改
语法：
alter table 表名 操作;
#2.1向现有的表中添加新的列
alter table student add class_id int;
#2.2修改表中的列
alter table student modify student_name varchar(10);
#2.3删除表中的列
alter table student drop class_id; #每次只能删除一列
#2.4修改列名
alter table student change student_age age int;
注：change和modify的区别 change修改列名，而modify修改列的类型或者约束
#2.5修改表名
alter table student rename stu;
#2.6删除表
drop table 表名
drop table stu;

6.约束

实体完整性约束

#1主键约束 primary key :唯一，标识表中的一行数据，此列的值不可以重复，且不能为null
create table student(
student_id int primary key,
student_name varchar(20),
student_age int
)charset=utf8;

#2.唯一约束 unique 唯一，标识表中的一行数据，不可重复，可以为null
create table student(
student_id int primary key,
student_name varchar(20) unique,
student_age int
)charset=utf8;

#3.自动增长列 auto_increment 给主键数列值添加自动增长，从1开始，每次加1，不能单独使用，和主键配合
create table student(
student_id int primary key auto_increment,
student_name varchar(20) unique,
student_age int
)charset=utf8;

域完整性约束 限制列的单元格的数据正确性

#1.非空约束 not null 非空，此列必须有值
create table student(
student_id int primary key auto_increment,
student_name varchar(20) unique not null,
student_age int
)charset=utf8;

#2.默认值约束
default 值 为列赋予默认值，当新增数据没有指定值的时候，书写default，以指定的默认值进行填充
create table student(
student_id int primary key auto_increment,
student_name varchar(20) unique not null,
student_age int default 20
)charset=utf8;
#3.引用约束
语法： constraint fk_表名_列名 foreign key (列名) references 被引用的表名 (列名)
注:fk_表名_列名 只是起的名字，建议这样起名字，比较规范
主表 :被引用表， 从表：引用表；记得一定要先删除引用表

案例:
创建表Grade：

create table Grade(
GrageId int primary key auto_increment,
GradeName varchar(20) unique not null
)charset=utf8;

创建表student

create table student(
student_id varchar(50) primary key ,
student_name varchar(50) not null,
sex char(2) default '男',
borndate date not null,
phone varchar(11)，
GradeId int not null ,
constraint fk_student_GradeId foreign key(GradeId) references Grade(GradeId)
)charset=utf8;

7.事务

#创建账户表
create table account(
id int,
money int
)charset=utf8;

#往账户中添加数据
insert into account(id,money)values(1,10000);
insert into account(id,money)values(2,1000);

#模拟转账 账户1给账户2赚钱1000
update account set money=money-1000 where id=1;
#出现断电、异常、出错。。。
update account set money=money+1000 where id=2;
上述代码在减钱操作后过程中出现了异常或加钱语句出错，会发现，减钱执行成功，而加钱失败！
注意:每条SQL语句都是一个独立的操作，一个操作的执行对于数据库是持久性的影响

由上面这个小案例，引入事务出现的必要性:
事务是一个原子操作，是一个最小的执行单元，可以由一个或多个SQL语句组成，在同一个事务中，所有的SQL语句都成功执行时，整个事务成功，有一个SQL语句执行失败，整个事务都执行失败。

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

事务的原理:

数据库会为每一个客户端都维护一个空间独立的缓存区（回滚段），一个事务中所有的增删改语句的执行结果都会缓存在回滚段中，只有当事务中的所有SQL语句均正常结束（commit），才会将回滚段中的数据同步到数据库，否则无论是因为哪种原因的失败，整个事务都将进行回滚（rollback）

事务的特性：（面试题）ACID

Atomicity（原子性）：表示一个事务内所有的操作是一个整体，要么全部成功，要么全部失败
Consistency(一致性):表示一个事务内有一个操作失败时，所有的更改的数据都必须回滚到更改前的状态
Isolation(隔离性):事务查看数据操作时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据
Durablity(持久性):持久性事务完成后，它对于系统的影响是永久的

#模拟转账 账户1给账户2赚钱1000
#开启事务
start transaction;
update account set money=money-1000 where id=1;
#出现断电、异常、出错。。。
update account set money=money+1000 where id=2;
#如果事务语句成功
commit;
#如果事务语句失败
rollback;

8.权限管理
1.创建用户

语法:
create user 用户名 identified by 密码
#创建一个zhangsan用户
create user ’ zhangsan’ identified by ‘123’;

2.授权

语法：grant all on 数据库表 to 用户名
#将companyDB下所有表的权限都赋给zhangsan
grant all on companyDB.* to ’ zhangsan’;

3.撤销权限(注:客户端重新连接才会生效)

语法： revoke all on 数据库.表名 from 用户名
#将companyDB下所有表的权限从zhangsan用户中撤销
revoke all on companyDB.* from ‘zhangsan’;

4.删除用户

drop user 用户名
#删除用户zhangsan
drop user ‘zhangsan’;

9.视图
1.概念

视图，虚拟表，从一个表或多个表查询出来的表，作用和真实表一样，包含一系列带有行和列的数据。视图中，用户可以使用select语句查询数据，也可以使用insert、update、delete修改记录，视图可以使用户操作方便，并保障数据库系统的安全。

2.特点：

优点：
简单化，数据所见即所得
安全性，用户只能查询或修改他们所能见到的数据
逻辑独立性，可以屏蔽真实表结构变化带来的影响
缺点：
性能相对比较差
修改不方便

3.视图的创建

语法：
create view 视图名 as 查询数据源表的语句

#创建视图
create view v_employees as select employee_id,first_name,last_name from employees;

4.视图的修改

方式一（不明确视图是否存在的时候）
create or replace view 视图名 as 查询语句；
#存在则更新，不存在就创建视图
create or replace view v_employees as select employee_id,first_name,last_name,job_id from employees;

方式二
alter view 视图名 as 查询语句
alter view v_employees as select employee_id,first_name,last_name,job_id,email from employees;

5删除视图

语法：
drop view 视图名
#删除视图v_employees
drop view v_employees;

注:删除视图并不会影响原表；

10.总结

1.数据库查询语言DQL（Data Query Language）:select 、where、order by、group by、having
2.数据定义语言DDL(Data Definition Language): create 、alter、 drop
3.数据操作语言DML（Data Manipulation Language）:insert、update、delete
4.事务处理语言TPL(Transaction Process Language):commit、rollback
5.数据控制语言DCL(Data Control Language):grant 、re voke