Java第十九天：mysql（二）

0.昨天查询继续

#select，
#在开发的时候不允许这样写的，会将咱们数据库person表里面所有的数据显示出来，会拖慢咱们数据库的效率
mysql > select * from person;

#按照字段查询
msyql > select id, name from person;
mysql > select name, id from person;

#按照字段查询，还可以给字段起一个别名
mysql > select id as "编号" , name as "姓名" , info as "信息"  from person;

#还可以使用最基本的条件进行查询
# > <   >=     <=      !=    =
mysql > select * from person where id >= 19;


#还可以使用逻辑运算符
mysql > select * from person where age > 12 and salary > 10000;
mysql > select * from person where age > 12 or salary > 10000;


#排序  order by
mysql > select * from person order by age asc; #升序
mysql > select * from person order by age desc； #降序

#按照年龄升序排，年龄相同的再按照salary降序进行排
mysql > select * from person order by age asc, salary desc;


#分页查询【重点】limit
mysql > select * from person limit 0,5;

#0  1 2 3 4  5  6 7 8 9 10 11 12 13 14 
#0-4    limit 0, 5;
#5-9    limit 5,5;
#10-14  limit 10,5;
#pagecount 页码数   length 表示一页显示多少条数据
#limit (pagecount - 1)*length, length;
#第二页   3
#limit 3,3;
#第三页
#limit 6,3;


#内置函数（尽量不要用，因为会影响运行的效率）
mysql > select max(age) from person;
mysql > select min(age) from person;
mysql > select avg(age) from person;
mysql > select sum(age) from person;
#统计
mysql > select count(*) from person;--后期有用


#模糊查询   _只是占位符而已
mysql > select * from person where name like "骚_";
mysql > select * from person where name like "_骚_";
mysql > select * from person where name like "骚%"；
mysql >  select * from person where name like "%骚%";


#分组 group by
#按照性别分组
mysql > select * from person group by sex;
#按照性别进行分组，统计
mysql > select sex as "性别"， count(*) from person group by sex;

#按照性别分组，找出人数大于5的那一个组
mysql > select sex, count(*)--查什么
from person --从哪找
group by sex--以sex 分组
having count(*) > 5;--分组之后的条件不能再使用where 使用having

#找出年龄大于20的，按照性别分组，找出人数大于3的组
mysql > select sex, count(*)
from person
where age > 20
group by sex
having count(*) > 3;

where       order by  group by  having 

1.数据的约束(重点)

1.1默认值

#用户在插入数据的时候没有给当前字段一个数据的话，当前字段会按照给的默认值进行处理


mysql > create table person1 (
id int,
name varchar(30),
country char(20) default "PRC");

1.2非空

mysql > create table person2 (
id int not null,
name varchar(30),
info text
);

mysql> insert into person2(name, info) values("随便", "太随便了");
ERROR 1364 (HY000): Field 'id' doesn't have a default value

1.3唯一

#要求你字段数据在当前数据表中不能重复
#unique
mysql > create table person3(
id int unique,
name varchar(30) not null,
info text not null
);
mysql> insert into person3(id, name, info) values(1, "浪博", "心浪微博");
Query OK, 1 row affected (0.01 sec)

mysql> insert into person3(id, name, info) values(1, "浪博1", "心浪微博1");
ERROR 1062 (23000): Duplicate entry '1' for key 'id'

1.4主键[重点]

#唯一和非空的一种组合方式
#primary key
#主键是该行数据唯一的索引
#一般工作的时候主键都是id 并且是唯一的，  会把和业务无关的字段设置为主键
#性别，年龄，地址，工资 等都不能设置为主键
mysql > create table person4 (
id int primary key,
name varchar(30) not null,
info text not null
);

1.5自增长

#插入数据的时候，有写字段可以自动增加
#auto_increment
#要想使用自增，字段类型必须是整型的数据，另外一个必须是key键 一般是主键
mysql > create table person5 (
id int primary key auto_increment,
name varchar(30) not null,
info text not null
);
注意事项主键是不能重复  自增 1以下的都不行，最好是让他自己增加

1.6外键约束（开发中不常用）

#员工表
mysql > create table employee(
id int primary key auto_increment,
empName varchar(30) not null,
depName varchar(30) not null,
regTime timestamp default current_timestamp
);

mysql> select * from employee;
+----+------------+---------+---------------------+
| id | empName    | depName | regTime             |
+----+------------+---------+---------------------+
|  1 | 骚磊       | 骚      | 2019-11-26 11:28:18 |
|  2 | 骚杰       | 骚      | 2019-11-26 11:28:34 |
|  3 | 王宝强     | 绿      | 2019-11-26 11:29:15 |
|  4 | 贾乃亮     | 绿      | 2019-11-26 11:29:25 |
|  5 | 陈羽凡     | 绿      | 2019-11-26 11:29:59 |
|  6 | 蔡徐坤     | 击      | 2019-11-26 11:30:36 |
|  7 | 蔡徐坤1    | 击      | 2019-11-26 11:31:11 |
+----+------------+---------+---------------------+

#分表操作
#上面这个表既有员工名字， 部门的名字
#员工表和部门表

mysql > drop table employee;
#部门表
mysql >  create table dept(
    id int primary key auto_increment,
    deptName varchar(20) not null
    );
#员工表
mysql > create table employee(
    id int primary key auto_increment,
    empName varchar(30) not null,
    deptId int not null
    );
 mysql> select * from dept;
+----+----------+
| id | deptName |
+----+----------+
|  1 | 骚       |
|  2 | 绿       |
|  3 | 击       |
+----+----------+

mysql> select * from employee;
+----+-----------+--------+
| id | empName   | deptId |
+----+-----------+--------+
|  1 | 骚磊      |      1 |
|  2 | 骚j杰     |      1 |
|  3 | 王宝强    |      2 |
|  4 | 蔡徐坤    |      3 |
|  5 | 贾乃亮    |      2 |
|  6 | 陈羽凡    |      2 |
+----+-----------+--------+
6 rows in set (0.00 sec)


#插入一个员工数据，部门不存在，照样可以插入， 不符合真实开发中的业务逻辑
#删除一个数据 ，照样可以删除

mysql> create table dept(
    -> id int primary key auto_increment,
    -> deptName varchar(20) not null
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> create table employee(
    -> id int primary key auto_increment,
    -> empName varchar(20) not null,
    -> deptId int not null,
    -> regTime timestamp default current_timestamp,
    #使用了外键约束，让两个表关联起来
    #fk_emp_dp  外键名字
    #foreign key 外键
    #dept(id) 参考一下这个键
    -> constraint fk_emp_dp foreign key(deptId) references dept(id)
    -> );
Query OK, 0 rows affected (0.03 sec)
#开始插入数据 部门表
mysql> insert into dept(deptName) values("骚");
Query OK, 1 row affected (0.01 sec)

mysql> insert into dept(deptName) values("绿");
Query OK, 1 row affected (0.01 sec)

mysql> insert into dept(deptName) values("鸡");
Query OK, 1 row affected (0.00 sec)

#开始插入数据  员工表
mysql> insert into employee(empName, deptId) values("骚磊", 1);
Query OK, 1 row affected (0.05 sec)

mysql> insert into employee(empName, deptId) values("骚杰", 1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into employee(empName, deptId) values("王宝强", 2);
Query OK, 1 row affected (0.03 sec)

mysql> insert into employee(empName, deptId) values("蔡徐坤", 3);
Query OK, 1 row affected (0.04 sec)

mysql> insert into employee(empName, deptId) values("贾乃亮", 2);
Query OK, 1 row affected (0.03 sec)

mysql> insert into employee(empName, deptId) values("陈羽凡", 2);
Query OK, 1 row affected (0.01 sec)

#插入的时候有外键的约束，在一个表中插入数据的时候，这个字段和另外一个表中的字段要有对应关系
mysql> insert into employee(empName, deptId) values("李云龙", 4);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`laowanmian`.`employee`, CONSTRAINT `fk_emp_dp` FOREIGN KEY (`deptId`) REF
ERENCES `dept` (`id`))

#删除
mysql> delete from dept where id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constrai
nt fails (`laowanmian`.`employee`, CONSTRAINT `fk_emp_dp` FOREIGN KEY (`deptId`)
 REFERENCES `dept` (`id`))
 
 #外键约束的特征：（部门表示主表， 员工表是从表）
 --1.先添加主表，再添加从表，如果添加时从表，直接报错
 --2.先删除从表， 再删除主表, 如果先删除主表，直接报错
 --3.先修改从表，再修改主表
 

1.7级联操作

自己演示：
--在使用外键的时候会出现问题
mysql> create table dept(
    -> id int primary key auto_increment,
    -> deptName varchar(20) not null
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> create table employee(
    -> id int primary key auto_increment,
    -> empName varchar(20) not null,
    -> deptId int not null,
    -> regTime timestamp default current_timestamp,
    #使用了外键约束，让两个表关联起来
    #fk_emp_dp  外键名字
    #foreign key 外键
    #dept(id) 参考一下这个键
    -> constraint fk_emp_dp foreign key(deptId) references dept(id)
    -> on delete cascade
    ->on update cascade
Query OK, 0 rows affected (0.03 sec)
    
    #on delete cascade 级联删除
    #on update cascade 级联修改
    

总结：
1.新建两个表 dept  (部门表)  employee（员工表）  没有加约束关系
2.随便增加 ，随便删除，随便更新  都不会报错但是不符合工作中业务逻辑
3.删除了上面建的两个表，然后有新建了dept  (部门表)  employee（员工表）加了外键约束
constraint fk_emp_dp foreign key(deptId) references dept(id)
4.删除主表（报错了） ， 先删除从表再删除主表没有报错（）
5.删除了上面建的两个表，然后有新建了dept  (部门表)  employee（员工表）加了外键约束和级联删除和更新
6.咋删除都不会报错了

2.联表查询

2.1内连接外连接

--员工表和部门表是有一定关系的，期望查到的数据带有对应部门的员工
--联表查询

-- 考虑的内容
--1. 需要查什么？
--2.从哪里查？
--3.查询的条件是什么？

--期望获得是员工的id 号，员工的名字，和部门的名字
--第一种写法
select employee.id, employee.empName,dept.deptName 
from employee, dept
--where很关键  一对多的关系
where employee.deptId = dept.id;

--第二种写法 可以给表名起别名
select e.id, e.empName, d.deptName
from employee e, dept d
where e.deptId = d.id;

--第三种写法 可以给字段起别名
select e.id as "编码", e.empName as "姓名", d.deptName as "部门"
from employee e, dept d
where e.deptId = d.id;

--可以加一些条件
select e.id as "编码", e.empName as "姓名", d.deptName as "部门"
from employee e, dept d
where e.deptId = d.id
order by e.id desc;



--内连接查询（重要）
-- inner join

#第四种写法
select e.id as "编号", e.empName as "姓名", d.deptName as "部门"
from employee e --查询数据的某一张表
inner join dept d --使用内连接查询，去连接另外一张表
where e.deptId = d.id;


--左外连接  以左边的表为主，意味着左边的额表的数据不能丢失
select e.id as "编号", e.empName as "姓名", d.deptName as "部门"
from dept d
left outer join employee e
on e.deptId = d.id;

--右外连接 以右边的表为主，意味着右边的数据不能丢
select e.id as "编号", e.empName as "姓名", d.deptName as "部门"
from dept d
right outer join employee e
on e.deptId = d.id;

一对一：用户表和详细信息表
一对多：一个老师对应多个学生（工作用的最大）
多对多：课程和学生的关系


内连接：只连接匹配的项
外连接：没有的话会补上null
（自己打开一个excel）

2.2一对多查询

mysql > create table student(
    id int primary key auto_increment, -- 学生ID
    name varchar(20) not null, -- 学生姓名
    age int not null, -- 学生年龄
    gender tinyint(1) not null -- 学生性别
);

mysql > create table score_record(
    id int primary key auto_increment, -- 成绩单Id
    stuId int not null, -- 当前成绩单对应的学生ID
    javaScore float(5, 2) not null, -- Java成绩
    cScore float(5, 2) not null, -- C语言成绩
    htmlScore float(5, 2) not null -- HTML成绩
);

mysql > insert into student(name, age, gender) values("骚磊", 16, 0);
mysql > insert into student(name, age, gender) values("骚杰", 66, 0);
mysql > insert into student(name, age, gender) values("骚很", 56, 0);
mysql > insert into student(name, age, gender) values("林妹妹", 15, 0);
mysql > insert into student(name, age, gender) values("宝哥哥", 18, 0);

mysql > insert into score_record(stuId, javaScore, cScore, htmlScore) values(1, 10.5, 20.5, 35.5);
mysql > insert into score_record(stuId, javaScore, cScore, htmlScore) values(1, 12.5, 22.5, 33.5);
mysql > insert into score_record(stuId, javaScore, cScore, htmlScore) values(1, 13.5, 21.5, 30.5);
mysql > insert into score_record(stuId, javaScore, cScore, htmlScore) values(2, 20.5, 12.5, 30.5);
mysql > insert into score_record(stuId, javaScore, cScore, htmlScore) values(2, 4.5, 51.5, 30.5);
mysql > insert into score_record(stuId, javaScore, cScore, htmlScore) values(2, 20.5, 60.5, 5.5);
mysql > insert into score_record(stuId, javaScore, cScore, htmlScore) values(3, 15.5, 21.5, 56.5);
mysql > insert into score_record(stuId, javaScore, cScore, htmlScore) values(3, 13.5, 20.5, 78.5);
mysql > insert into score_record(stuId, javaScore, cScore, htmlScore) values(4, 50.5, 15.5, 51.5);
mysql > insert into score_record(stuId, javaScore, cScore, htmlScore) values(4, 20.5, 25.5, 53.5);
mysql > insert into score_record(stuId, javaScore, cScore, htmlScore) values(5, 15.5, 76.5, 6.5);
mysql > insert into score_record(stuId, javaScore, cScore, htmlScore) values(5, 12.5, 32.5, 1.5);
mysql > insert into score_record(stuId, javaScore, cScore, htmlScore) values(5, 20.5, 3.5, 63.5);
mysql > insert into score_record(stuId, javaScore, cScore, htmlScore) values(2, 12.5, 5.5, 5.5);
mysql > insert into score_record(stuId, javaScore, cScore, htmlScore) values(3, 15.5, 6.5, 10.5);

2.3多对多查询

mysql > create table user(
    id int primary key auto_increment,
    name varchar(20) not null, 
    roleId int not null 
);

mysql > create table role(
    id int primary key auto_increment,
    roleName varchar(20) not null
);

mysql > create table privilege(
    id int primary key auto_increment,
    pName varchar(20) not null
);

mysql > create table role_to_privilege(
    id int primary key auto_increment,
    roleId int not null,
    pId int not null
);

mysql > insert into user(name, roleId) values("张三", 1);
mysql > insert into user(name, roleId) values("李四", 2); 

mysql > insert into role(roleName) values("管理员");
mysql > insert into role(roleName) values("普通用户");

mysql > insert into privilege(pName) values("删除用户"); -- 管理
mysql > insert into privilege(pName) values("添加用户"); -- 管理
mysql > insert into privilege(pName) values("修改用户"); -- 管理 用户
mysql > insert into privilege(pName) values("查看指定用户"); -- 管理 用户
mysql > insert into privilege(pName) values("查看所有用户"); -- 管理

mysql > insert into role_to_privilege(roleId, pId) values(1, 1);
mysql > insert into role_to_privilege(roleId, pId) values(1, 2);
mysql > insert into role_to_privilege(roleId, pId) values(1, 3);
mysql > insert into role_to_privilege(roleId, pId) values(1, 4);
mysql > insert into role_to_privilege(roleId, pId) values(1, 5);
mysql > insert into role_to_privilege(roleId, pId) values(2, 3);
mysql > insert into role_to_privilege(roleId, pId) values(2, 4);

希望大家关注我一波,防止以后迷路,有需要的可以加我Q讨论互相学习java ,学习路线探讨,经验分享与java Q:2415773436