首页 > 数据库 >MySQL数据库笔记(二)

MySQL数据库笔记(二)

时间：2023-08-11 23:31:46浏览次数：34  
标签：-- 数据库 笔记 约束 user MySQL table tb select

聚集函数

聚集函数：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;

MySQL数据库笔记(二)_DQL分组查询

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';

约束概述

约束概述 : 对表中的数据（记录）进行限定，保证数据的正确性、有效性、完整性。

MySQL数据库笔记(二)_多表设计_02

主键约束

-- 概述
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);

-- 思考 : 可不可以不通过添加外键约束 完成数据有效性的控制

MySQL数据库笔记(二)_约束_03

多表设计之多对多

-- 现实场景
	一个学生可以选择多门课程,一门课程可以被多个学生所选择.
	一个角色可以有多个权限,一个权限也可以属于多个角色.
	
-- 问题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);


MySQL数据库笔记(二)_DQL分组查询_04

多表设计之一对一

-- 现实场景
	一个公司对应一个注册地址.同时一个注册地址只能被一个公司注册
	
-- 设计原则
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);

MySQL数据库笔记(二)_多表设计_05

标签：--,数据库,笔记,约束,user,MySQL,table,tb,select
From： https://blog.51cto.com/u_16213911/7054115

相关文章

  • [刷题笔记] Luogu P1725 琪露诺
    ProblemDescription若当前在\(pos\)位置，每次可以在\([pos+l,pos+r]\)区间内任选一个点跳。每跳到一个地方就可以获得这个地方的值，最后跳到位置\(pos\geqn\)即为结束，求如何跳才能使结束的时候获得的值最大？Analysis我们发现所有操作都是在一条链上完成的，显然若再次跳到这个位......
  • 「学习笔记」圆方树
    圆方树最初是处理「仙人掌图」（每条边在不超过一个简单环中的无向图）的一种工具，不过发掘它的更多性质，有时我们可以在一般无向图上使用它。个人觉得，圆方树是一个很好的工具。圆方树的题目更多的侧重于想，而不是怎么建圆方树。前置知识——点双连通分量点双连通分量：不存在割点的图。......
  • 与点对有关的CDQ分治（菜鸟笔记）
    参考文章   首先要说明的是CDQ是一种思想，并且扩展范围很广。   这里主要说的是与点对有关的CDQ。参考文章说了与CDQ主要解决的三大类问题。第一类就是解决和点对有关的问题。主要是给定一个长度为n的序列，然后找出其中满足题意的点对\((i,j)\)。   CDQ的......
  • 【后端面经-数据库】Redis详解——Redis基本概念和特点
    目录1.Redis基本概念2.Redis特点2.1优点2.2缺点3.Redis的应用场景面试模拟参考资料声明：Redis的相关知识是面试的一大热门知识点，同时也是一个庞大的体系，所涉及的知识点非常多，如果用一篇文章罗列，往往会陷入知识海洋中无法感知其全貌，因此，这段时间我会试着拆分Redis的相关章节，辅......
  • 开源数据库Mysql_DBA运维实战 （DML/DQL语句）
    DML/DQLDMLINSERT  实现数据的 插入        实例：DELETE  实现数据的 删除        实例：UPDATE  实现数据的 更新         实例1：         实例2：     实例3：DQL DML/DQL编辑 DML语句数据库操纵语言： 插入数据INSERT、删......
  • 【后端面经-数据库】Redis详解——Redis基本概念和特点
    (【后端面经-数据库】Redis详解——Redis基本概念和特点)声明：Redis的相关知识是面试的一大热门知识点，同时也是一个庞大的体系，所涉及的知识点非常多，如果用一篇文章罗列，往往会陷入知识海洋中无法感知其全貌，因此，这段时间我会试着拆分Redis的相关章节，辅以思维导图的形式介绍Redis的相......
  • JS原型链污染学习笔记
    1.JS原型和继承机制1>原型及其搜索机制NodeJS原型机制，比较官方的定义：我们创建的每个函数都有一个prototype（原型）属性，这个属性是一个指针，指向一个对象，而这个对象的用途是包含可以由特定类型的所有实例共享的属性和方法设计原型的初衷无非是对于每个实例对象，其拥有的共同......
  • 三核电脑如何装数据库
    三核电脑如何装数据库问题现象：目前市场上出现了三核CPU（AMD的处理器）的电脑，导致装msde2000的数据库怎么都装不上，好不容易装上了SQL2000的数据库，但SP4的补丁怎么都打不上，或者是装了SQL2005数据库服务怎么都启动不了，那么就会导致软件无法使用，那面对这种情况我们该如何操作？问题分析......
  • MySQL运行错误：‘mysql‘不是内部或外部命令，也不是可运行程序，Windows添加环境变量
    1、Windows电脑安装MySQL5.7，打开cmd出现“'mysql'不是内部或外部命令，也不是可运行的程序或批处理文件。”2、出现以上情况基本断定为没有安装mysql或者是安装了mysql没有配置环境变量，下面进行配置环境变量找到mysql的安装目录，可以windows+r然后输入：services.msc3、找到my......
  • openGauss学习笔记-37 openGauss 高级数据管理-事务
    openGauss学习笔记-37openGauss高级数据管理-事务事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位。openGauss数据库支持的事务控制命令有启动、设置、提交、回滚事务。openGauss数据库支持的事务隔离级别有读已提交和可重复读。READ......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99