聚集函数
聚集函数:SQL提供的方法
统计函数
count(字段):统计表中记录的个数.
语法:
select count(*) from 表名;
练习:
-- 统计exam中有多少个学生:
select count(name) from tb_exam;
select count(id) from tb_exam;
select count(*) from tb_exam; -- 根据任意字段进行统计
-- 统计数学成绩大于80分学生个数:
select count(math) from tb_exam where math > 80;
-- 统计总成绩大于220分学生个数:
select count(name) from tb_exam where (chinese + math + english) > 220;
求和函数
sum() :求和
语法:
select sum(列名) from 表名;
练习:
-- 统计一个班级的数学总成绩:
select sum(math) from tb_exam;
-- 统计一个班级数学,英语,语文各科总成绩:
select sum(math),sum(english),sum(chinese) from tb_exam;
-- 统计一个班级语文,数学,英语的成绩总和:
select sum(math) + sum(english) + sum(chinese) from tb_exam;
select sum(math + english + chinese) from tb_exam;
-- 统计一个班级的语文成绩的平均分:
-- 语文平均分 = 语文的总分 / 总人数
-- 语文的总分 : sum(chinese)
-- 总人数 : count(chinese)
//错误写法
select sum(chinese) from tb_exam / select count(chinese) from tb_exam;
-- 正确写法
select sum(chinese) / count(chinese) as 语文平均分 from tb_exam;
平均值函数
avg():求平均值
语法:
select avg(列名) from 表名;
练习:
-- 求一个班级的语文成绩的平均分:
select avg(chinese) from tb_exam;
-- 求一个班级总分的平均分:
select avg(chinese + math + english) from tb_exam;
DQL 分组查询
-- 准备工作 :
create table tb_orders(
id int,
product varchar(20),
price double
);
-- 添加数据
insert into tb_orders values (1,'tv',800);
insert into tb_orders values (2,'wash',1200);
insert into tb_orders values (3,'ice',2300);
insert into tb_orders values (4,'tv',800);
insert into tb_orders values (5,'wash',1200);
语法:
select * from 表 where 条件 group by 列名;
-- 注意事项:
1. where 后面的条件不能使用聚集函数.
2. having 分组后的条件过滤. [having后面跟聚集函数]
案例:
-- 对订单表中的商品归类后,显示每类商品的总价格:
select product,sum(price) from tb_orders group by product;
-- 对订单表中的商品归类后,显示每类商品的数量:
select product,count(*) from tb_orders group by product;
-- 查询购买了几类商品,显示的是总价格大于2000的商品.
-- 错误写法 : group by 要放在 where 的后面
select product,sum(price) from tb_orders group by product where sum(price) > 2000;
-- 错误写法 : where 中不可以写聚集函数
select product,sum(price) from tb_orders where sum(price) > 2000 group by product;
-- 正确的写法
select product,sum(price) from tb_orders group by product having sum(price) > 2000;
DQL 分页查询
语法:
-- 语法
select * from 表名 limit begin , pageSize;
-- begin : 开始索引
-- pageSize : 查询记录个数
begin = (currentPage-1) * pageSize;
-- 实现对tb_orders的分页查询(每页2行数据)
-- 第一页
select * from tb_orders limit 0 , 2;
-- 第二页
select * from tb_orders limit 2 , 2;
-- 第三页
select * from tb_orders limit 4 , 2;
DCL 账户管理
注意 : 账户管理、授权管理只能在root账户下。
语法:
-- 查询账户
use mysql;
-- User:账户名
-- Host:允许账户登录mysql服务器的主机地址
select User,Host from user;
-- 添加账户
create user '账户名'@'主机名' identified by '密码';
-- 删除账户
drop user '账户名'@'主机名';
-- 修改账户
set password for '账户名'@'主机名' = password('新密码');
案例:
-- 添加账户
-- zhangsan账户只能在当前主机登录mysql服务
create user 'zhangsan'@'localhost' identified by '123456';
-- zhangsan账户只能在所有主机登录mysql服务
create user 'zhangsan'@'%' identified by 'zhangsan';
-- 删除账户
drop user 'zhangsan'@'%';
drop user 'zhangsan'@'localhost';
-- 修改账户
set password for 'zhangsan'@'localhost' = password('zhangsan');
-- 注意事项 :
账户管理必须在root权限下完成
DCL 权限管理:
mysql账户可以拥有不同的权限
语法:
-- 查看授权
show grants for '账户名'@'主机名';
-- 添加授权
grant 权限1 , 权限2 ,... on 数据库名.表名 to '账户名'@'主机名';
-- 撤销授权
revoke 权限1 , 权限2 , ... on 数据库名.表名 from '账户名'@'主机名';
案例:
-- 查看zhangsan授权
show grants for 'zhangsan'@'localhost';
-- 添加授权
grant select on mydb1.tb_exam to 'zhangsan'@'localhost';
-- 撤销权限
revoke select on mydb1.tb_exam from 'zhangsan'@'localhost';
-- 授予所有权限
grant all PRIVILEGES on *.* to 'zhangsan'@'localhost';
约束概述
约束概述 : 对表中的数据(记录)进行限定,保证数据的正确性、有效性、完整性。
主键约束
-- 概述
1 . 非空和唯一两个功能
2 . 一张表只能有一个列作为主键
3 . 主键一般用于表中数据的唯一标识
-- 语法
-- 建表时添加主键约束
create table 表名(
字段名1 字段类型(长度) primary key ,
字段名2 字段类型(长度)
...
);
-- 删除主键约束
alter table 表名 drop primary key;
-- 建表后添加主键约束
alter table 表名 modify 字段名 字段类型(长度) primary key;
代码:
class user{
private int id;
private String username;
private double money;
}
-- 创建表时添加主键约束
create table tb_user(
id int primary key,
username varchar(30),
money double
);
-- 删除主键约束
alter table tb_user drop primary key;
-- 创建表后添加主键约束 (DML)
alter table tb_user modify id int primary key;
自增约束:
-- 概述
1 . 如果主键是一个整数类型,那么就可以将其设置为自增约束;
2 . 值可以自动增长。
-- 语法
-- 创建表时添加自增约束
create table 表名(
字段1 字段类型(长度) primary key auto_increment,
字段2 字段类型(长度),
...
);
-- 删除自增约束
alter table 表名 modify 字段1 字段类型(长度);
-- 创建表后添加自增约束
alter table 表名 modify 字段1 字段类型(长度) auto_increment;
代码:
-- 创建表时添加自增约束 给id属性增加primary key auto_increment
create table tb_user(
id int primary key auto_increment,
username varchar(30),
money double
);
-- 删除自增约束
alter table tb_user modify id int;
-- 创建表后添加自增约束
alter table tb_user modify id int auto_increment;
-- 验证自增效果
insert into tb_user values (1,'zhangsan',10000);
-- 正确写法
insert into tb_user values (null,'lisi',15000);
-- 错误写法
insert into tb_user values ('wangwu',20000);
唯一约束
-- 概述
使用唯一约束的字段的值不可重复
-- 语法
-- 创建表时添加唯一约束
create table 表名(
字段1 字段类型(长度) unique,
字段2 字段类型(长度),
...
);
-- 删除唯一约束
alter table 表名 drop index 字段名;
-- 创建表后添加唯一约束
alter table 表名 modify 字段名 字段类型(长度) unique;
代码:
-- 创建表时添加唯一约束,给name属性增加唯一约束
create table tb_user(
id int primary key auto_increment,
username varchar(30) unique,
money double
);
-- 删除唯一约束
alter table tb_user drop index username;
-- 创建表后添加唯一约束
alter table tb_user modify username varchar(30) unique;
-- 验证unique约束
insert into tb_user values (null,'zhangsan',15000);
非空约束
-- 概述
使用唯一约束的字段的值不可为null
-- 语法
-- 创建表时添加非空约束
create table 表名(
字段1 字段类型(长度) not null,
字段2 字段类型,
...
);
-- 删除非空约束
alter table 表名 modify 字段 字段类型(长度);
-- 创建表后添加非空约束
alter table 表名 modify 字段 字段类型(长度) not null;
代码
-- 创建表 给username加非空约束
create table tb_user(
id int primary key auto_increment,
username varchar(30) not null,
money double
);
-- 删除非空约束
alter table tb_user modify username varchar(30);
-- 创建表后添加非空约束
alter table tb_user modify username varchar(30) not null;
-- 验证非空约束
insert into tb_user values (null,'zhangsan',15000);
insert into tb_user values (null,null,25000);
默认值约束
-- 概念
给指定字段一个默认值
-- 语法
-- 创建表时添加默认值约束
create table 表名(
字段1 字段类型(长度) default 默认值,
字段2 字段类型(长度),
...
);
-- 删除默认值约束
alter table 表名 modify 字段 字段类型(长度);
-- 创建表后添加默认值约束
alter table 表名 modify 字段 字段类型(长度) default 默认值;
代码:
-- 创建表时添加默认值约束, 给money加默认值约束
create table tb_user(
id int primary key auto_increment,
username varchar(30),
money double default 6000
);
-- 删除默认值约束
alter table 表名 modify money double;
-- 创建表后添加默认值约束
alter table 表名 modify money double default 5000;
-- 验证默认值约束
-- 如果money=null,不使用默认值
insert into tb_user values(null,'zhangsan',null);
-- 添加记录时,不对money赋值,就可以使用default值 -- 指定赋值的字段名
insert into tb_user(id,username) values(null,'lisi');
检查约束
-- 概述
1 . 对字段的值的范围进行检查;
2 . mysql不支持检查约束。
-- 语法
create table tb_student (
id int primary key auto_increment,
name varchar(30),
gender char(1) check ('男' or '女')
);
-- 代替
create table tb_student(
id int primary key auto_increment,
name varchar(30),
gender enum ('男','女')
);
数据库备份和还原
-- 命令语法
--备份
mysqldump -uroot -p 数据库名称 > 路径地址\文件名.sql
mysqldump -uroot -p mydb1 > 路径地址\文件名.sql
--还原
mysql -uroot -p 数据库名称 < 路径地址\文件名.sql
mysql -uroot -p mydb1 < 路径地址\文件名.sql
多表设计
后面用的可视化工具,SQLyog,大家可以下载一个适合自己的
1. 一对一 :
公司对注册地址,丈夫对妻子
2. 一对多 :
用户对订单,部门对员工
3. 多对多 :
老师对学生,选课系统
多表设计之一对多
-- 现实场景
一个部门有多个员工,一个员工只能属于某一个部门.
一个用户产生多个订单,一个订单只能属于某一个用户.
-- 一对多
表A中的一条记录确定表B中的一条记录
表B中的一条记录确定表A中的多条记录
-- 问题1
如何体现已知部门下有哪些员工,如何体现已知员工属于哪个部门?
-- 解决方案
需要在多的一方添加一个字段指向一的一方的主键
-- 问题2
1. 假设一个公司只有固定的三个部门:市场部,研发部,人事部.有一个小伙子来入职,说自己是安保部的,请问怎么办?
2. 如果要将市场部撤销,那么市场部中的员工怎么办?
-- 解决方案
在多的一方添加外键约束一的一方
-- 语法
alter table 多的一方表 add foreign key(外键名) references 一的一方表(主键名)
案例实现:
-- 创建部门表 tb_dept
create table tb_dept(
did int primary key auto_increment,
name varchar(30)
);
-- 创建员工表 tb_employee
create table tb_employee(
eid int primary key auto_increment,
name varchar(30)
);
-- 添加数据
-- 添加外键约束
alter table 多的一方表 add foreign key(外键名) references 一的一方表(主键名);
alter table tb_employee add foreign key(eno) references tb_dept(did);
-- 思考 : 可不可以不通过添加外键约束 完成数据有效性的控制
多表设计之多对多
-- 现实场景
一个学生可以选择多门课程,一门课程可以被多个学生所选择.
一个角色可以有多个权限,一个权限也可以属于多个角色.
-- 问题1
如何设计表实现多对多关系?
-- 设计原则
创建一个中间表,中间表中需要有两个字段.分别作为外键指向多对多双方的主键.
-- 问题2
1. 存在学生学习了不存在的课程
2. 不存在的学生学习了存在的课程
3. 不存在的学生学习了不存在的课程
-- 添加外键约束
代码:
-- 创建学生表
CREATE TABLE tb_student(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(30)
);
-- 创建课程表
CREATE TABLE tb_course(
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(30)`tb_student`
);
-- 如何表现2张表的多对多关系 : 创建中间表 中间表的列分别绑定2张表的主键
CREATE TABLE tb_student_course(
scid INT PRIMARY KEY AUTO_INCREMENT,
sno INT,
cno INT
);
-- 问题1 : 存在的学生学习了不存在的课程 王五 html(4)
-- 问题2 : 不存在的学生学习了存在的课程 赵六 javame(3)
-- 问题3 : 不存在的学生学习了不存在的课程 赵六 html(4)
-- 解决方案 : 在中间表中添加外键 让sno指向student表中的sid,让cno指向course表中的cid
-- alter table 多的一方表 add foreign key(外键名) references 一的一方表(主键名);
ALTER TABLE tb_student_course ADD FOREIGN KEY(sno) REFERENCES tb_student(sid);
ALTER TABLE tb_student_course ADD FOREIGN KEY(cno) REFERENCES tb_course(cid);
多表设计之一对一
-- 现实场景
一个公司对应一个注册地址.同时一个注册地址只能被一个公司注册
-- 设计原则
1. 唯一外键对应
在一张表中建一个字段,使用unique约束,并作为外键指向另一张表的主键
2. 主键对应
将一张表中的主键作为外键约束指向另一张表的主键。
-- 新建公司表
CREATE TABLE tb_company(
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(30)
);
-- 新建地址表
CREATE TABLE tb_address(
aid INT PRIMARY KEY AUTO_INCREMENT,
aname VARCHAR(100)
);
-- 添加外键约束
ALTER TABLE tb_address ADD FOREIGN KEY(aid) REFERENCES tb_company(cid);