从0基础学习MySQL数据库(二)

MySQL数据库技术

六、DCL(数据控制语言)操作管理用户、授权

DBA数据管理员

1.管理用户

1）添加用户

​ 语法：create user 用户名@‘主机名IP地址’ identified by’密码’;

-- 创建用户，用户只能在指定IP 地址上登录
create user magic@'192.168.1.113' identified by'';
--用户可以在任意IP地址下登录
create user magic@'%' identified by'';

2）查询用户，mysql下的数据表

select * from user;

select host,user from user;

3）删除用户

drop user 用户名@‘主机名IP地址’

4）修改用户密码

5.7以后密码password字段改为authentication_string

①update user set password = password(‘新密码’) where user= ‘用户名’;

②set password for用户名@‘主机名IP地址’ = password(‘新密码’)

5）刷新权限指令

flush privileges;

6）忘记密码

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sNxks4H1-1608272878707)(C:\Users\24582\AppData\Roaming\Typora\typora-user-images\image-20201215150408764.png)]

2.授权管理

此时，用户可以连接mysql数据库，但是只能使用很少的权限，因此需要其他用户给其权限

1）查询权限

show grants for 用户名@‘主机名IP地址’

2）授予权限

grant 权限列表 on 数据库名.表名 to 用户名@‘主机名IP地址’

–赋多个权限给用户

eg：grant select,update,insert create on mydb1.student to magic@’%’;

–给magic用户赋予所有权限，在任意数据库任意表上进行各种操作，权限相当于root用户

grant all on*.* to’用户名’@‘主机名‘；

3）撤销权限

revoke 权限列表 on 数据库名.表名 from 用户名@‘主机名‘；

–撤销用户所有的权限

revoke all on 数据库名.* from 用户名@‘主机名‘；

七、图形化管理数据库的工具：SQLYog客户端管理工具

1.概念：

SQLyog是一个快速而简洁饿的图形化管理MYSQL数据库的工具，它能够在任何地点有效地管理你的数据库，由业界著名的Webyog公司出品

2.安装：

自行百度安装即可

八、DQL查询语句

1.简单查询：

查询不会对数据库的数据进行修改，没有影响，只是一种显示数据的方式

语法：

-- select 字段列表 from 表名列表;
-- select 字段列表 from 表名列表 where 条件;
desc student;-- 查询student表结构

select sname,age from student; -- 查询指定字段，名字年龄

select * from student; -- 查询表的所有列

-- 查询中出现重复，则删除重复 distinct
select distinct address from student; -- 去掉重复信息

-- 起别名 as 也可以省略，表上也可以使用
select name as 姓名,age AS 年龄 from student;

-- 计算列 加减乘除
-- eg：所有年龄加5
select age+5 from student;

2.条件查询：

> < >= <= = != <>
and 或 &&:  SQL中建议使用前者,
or  或 ||
not 或 !

between... and...:在一定范围内
in(多个值): 表示多个数据,使用逗号分隔
like: 模糊查询
		点位符:_:单个任意字符
			  %:多个任意字符
is null:空值

-- 查询年龄大于19的
SELECT * FROM student WHERE age>=19;

-- 查询年龄大于18，小于20的
SELECT * FROM student WHERE age>18 AND age<20;

-- 查询年龄一定范围内
SELECT * FROM student WHERE age  BETWEEN 18 AND 20;

-- 满足其中一个就可以
SELECT * FROM student WHERE age IN(18,19);

-- 查询姓名中带o的同学
SELECT * FROM student WHERE sname LIKE '%o%';

3.排序查询：

语法：
	order by 排序字段1 排序方式1... -- 多个字段进行排序
	asc：升序
	desc：降序
	
-- 查询记录时，按照年龄升序输出
SELECT * FROM student ORDER BY age ASC;

-- 查询记录时，按照年龄降序输出
SELECT * FROM student ORDER BY age DESC;

-- 按照年龄升序排序，若年龄相同，按照id降序排列
SELECT * FROM student ORDER BY age ASC,id DESC;

4.聚合函数：

将一列数据作为一个整体，进行纵向计算

count():计算个数
max():计算最大值
min():计算最小值
sum():计算和
avg():计算平均分


-- 查询学生总数，null不会被统计
SELECT COUNT(id) FROM student;

SELECT COUNT(*) FROM student;-- 表示只有一列数据有一个不为null值，就算一行记录（常用*）

-- 查询计算年龄最大值、最小值
SELECT MAX(age) FROM student;
SELECT MIN(age) FROM student;

-- 查询年龄总和
SELECT SUM(age) FROM student;

--查询年龄的平均年龄
SELECT AVG(age) FROM student;

5.分组查询

-- 按照性别查询，按照男女分别多少人？
语法：
group by -- 用于对查询的结果进行分组
having -- 子句，用于限制分组显示结果，对结果再次筛选
SELECT sex,COUNT(*) FROM student GROUP BY sex;

-- 按照性别查询，按照男女分别多少人，且平均年龄？
SELECT sex,COUNT(*),AVG(age) FROM student GROUP BY sex;

-- 按照性别查询，按照男女分别多少人，且平均年龄，年龄低于18的不参加分组？
SELECT sex,COUNT(*),AVG(age) FROM student WHERE age>18 GROUP BY sex;

--where和having区别？
	①where在分组之前进行限定，如果不满足条件，则不参与分组
	having在分组之后进行限定，如果不满足条件，不会被查询出来
	②where后不可以跟聚合函数
	having后可以

九、数据表的约束

1.概念：

是对表中的数据进行限定，从而保证数据的正确性、有效性、完整性

一个表如果添加了约束条件则不正确数据不能添加进去

2.分类：

主键约束：primary key

非空约束：not null

唯一约束：unique

外键约束：foreign key

3.主键约束：primary key

-- 用来唯一标识数据库中的每一条记录，当定义了主键约束后，该列不但不能重复而且不能为null

--创建表时，添加主键约束
CREATE TABLE stu(
	id INT PRIMARY KEY,-- 定义列时指定主键
	sname VARCHAR(50)
)

CREATE TABLE stu(
	id INT,
	sname VARCHAR(50),
    PRIMARY KEY(id) --定义完所有列后添加主键
)
-- 删除主键的方法
ALTER TABLE stu DROP PRIMARY KEY;

--创建完成后，添加主键
ALTER TABLE stu ADD PRIMARY KEY(id);

-- 注意：
主键的含义是非空且唯一
一张表只能有一个字段为主键
主键就是表中记录的唯一标识

--插入重复的值
INSERT INTO stu(id,sname) VALUES(1,'tom');
INSERT INTO stu(id,sname) VALUES(1,'tom');

错误代码： 1062
Duplicate entry '1' for key 'PRIMARY'

--插入空值
INSERT INTO stu(id,sname) VALUES(NULL,'tom');

错误代码： 1048
Column 'id' cannot be null

自动增长

关键字：auto_increment

在创建表时，添加主键约束并且完成主键的增长

CREATE TABLE stu3(
	id INT PRIMARY KEY AUTO_INCREMENT,-- 给id添加主键约束，设置为自动增长
	sname VARCHAR(50)
)

-- 删除自动增长
ALTER TABLE stu3 MODIFY id INT;

-- 创建表之后，再添加自动增长
ALTER TABLE stu3 MODIFY id INT AUTO_INCREMENT;

-- 使用
INSERT INTO stu3(sname) VALUES ('tom');
INSERT INTO stu3(id,sname) VALUES(NULL,'rose');

-- 修改主键自增从200开始
ALTER TABLE stu3  AUTO_INCREMENT=200;

delete：删除删除所有的记录之后，自增长没有影响，从200--201
truncate：删除之后，自增长从0开始

4.非空约束：not null

概念

数据表的某一列不能为null，如果在列上定义了not null，插入数据时，必须为该列提供数据，为空就没意义了

格式

-- 创建表时添加约束
create table stu(
	id int,
    sname varchar(20) not null -- 创建了非空约束
)

-- 创建表结束后，添加非空约束
alter table 表名 modify sname varchar(20) not null;

-- 删除字段的非空约束
alter table 表名 modify sname varchar(20);

-- 案例
INSERT INTO stu3 VALUES(1,NULL);
-- 错误代码： 1048
Column 'sname' cannot be null

5.默认值：为某字段设置默认值

语法

create table stu(
	id int primary key,
    sname varchar(20) not null,
    sex varchar(10) default 'man' -- 默认为男
)

INSERT INTO stu2(id,sname,sex)VALUES(1,'zjd','woman');
INSERT INTO stu2(id,sname,sex)VALUES(2,'txw',NULL);
INSERT INTO stu2(id,sname,sex)VALUES(3,'txb',DEFAULT);

6.唯一约束：unique

概念

数据表中某一列不可出现重复的值，必须保证唯一性，当某一列数据定义了唯一约束之后，不能重复

语法

-- 创建表示添加唯一约束
CREATE TABLE stu(
	id INT PRIMARY KEY,
	sname VARCHAR(20) UNIQUE
)

-- 创建之后添加唯一约束
alter table 表名 modify 字段名 字段类型 unique
ALTER TABLE stu MODIFY sname VARCHAR(20) UNIQUE;

-- 删除字段的唯一约束
ALTER TABLE stu DROP INDEX sname;

--案例
INSERT INTO stu(id,sname) VALUES(1,'tom');
INSERT INTO stu(id,sname) VALUES(2,'tom');
-- 错误代码： 1062
Duplicate entry 'tom' for key 'sname'

-- 注意：
当插入的数据都为null值，则可以重复
INSERT INTO stu(id,sname)VALUES(3,NULL);
INSERT INTO stu(id,sname) VALUES(4,NULL);
mysql数据库中，唯一约束限定的列的值可以有多个null值

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VPG40bjc-1608272878709)(C:\Users\24582\AppData\Roaming\Typora\typora-user-images\image-20201217145207964.png)]

扩展

如果一个字段设置了唯一约束，该字段与主键有什么区别？

主键：一张表最多只能有一个主键 不能为null 加自增长

唯一：一张表可以有多个唯一约束 可以有多个null 不可以加自增长

7.外键约束：foreign key

前言

单表存储数据时的缺点：

①数据重复、出现冗余的数据

②后期出现增删改时，存在问题

解决方法：

拆分表、拆成多个表

CREATE TABLE class(-- 主表
	cno INT PRIMARY KEY AUTO_INCREMENT, -- 班级编号
	cname VARCHAR(50), -- 班级名称
	address INT -- 班级对应教室号
)
CREATE TABLE student(-- 从表
	id INT PRIMARY KEY AUTO_INCREMENT, -- 学生编号
	sname VARCHAR(50), -- 姓名
	age INT, -- 年龄
	cno INT -- 班级
)
-- 添加两个班级
INSERT INTO class VALUES(1,'classone',101),(2,'classtwo',102);
-- 添加学生
INSERT INTO student(id,sname,age,cno) VALUES(1801,'tom',18,1);
INSERT INTO student(id,sname,age,cno) VALUES(1802,'Jack',19,1);
INSERT INTO student(id,sname,age,cno) VALUES(1803,'rose',18,1);

INSERT INTO student(id,sname,age,cno) VALUES(1804,'bib',19,2);
INSERT INTO student(id,sname,age,cno) VALUES(1805,'lily',18,2);
INSERT INTO student(id,sname,age,cno) VALUES(1806,'cir',19,2);

外键约束概念

用于定义主表和从表之间的关系，外键约束要定义在从表上，主表则必须具有主键约束，从而保证数据的正确性和有效性

主表：一方，用来约束别人的表

从表：多方，被别人约束的表

语法

create table 表名(
	列名 数据类型,
	……
	外键列
	constraint 外键名称 foreign key(外键列名称) references 主表名称(主表列名称)
)

CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 学生编号
	sname VARCHAR(50), -- 姓名
	age INT, -- 年龄
	cno INT, -- 班级
	CONSTRAINT student_fk FOREIGN KEY(cno) REFERENCES class(cno)
)

-- 删除外键
alter table 表名 drop foreign key 外键名称;
ALTER TABLE student DROP FOREIGN KEY student_fk;

-- 创建表之后添加外键
alter table 表名  add
constraint 外键名称 foreign key(外键列名称) references 主表名称(主表列名称)

ALTER TABLE student  ADD
CONSTRAINT student_fk FOREIGN KEY(cno) REFERENCES class(cno);

--案例：插入不存在的班级报错
错误代码： 1452
Cannot add or update a child row: a foreign key constraint fails (`mystudy`.`student`, CONSTRAINT `student_fk` FOREIGN KEY (`cno`) REFERENCES `class` (`cno`))

别人约束的表

语法

create table 表名(
	列名 数据类型,
	……
	外键列
	constraint 外键名称 foreign key(外键列名称) references 主表名称(主表列名称)
)

CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 学生编号
	sname VARCHAR(50), -- 姓名
	age INT, -- 年龄
	cno INT, -- 班级
	CONSTRAINT student_fk FOREIGN KEY(cno) REFERENCES class(cno)
)

-- 删除外键
alter table 表名 drop foreign key 外键名称;
ALTER TABLE student DROP FOREIGN KEY student_fk;

-- 创建表之后添加外键
alter table 表名  add
constraint 外键名称 foreign key(外键列名称) references 主表名称(主表列名称)

ALTER TABLE student  ADD
CONSTRAINT student_fk FOREIGN KEY(cno) REFERENCES class(cno);

--案例：插入不存在的班级报错
错误代码： 1452
Cannot add or update a child row: a foreign key constraint fails (`mystudy`.`student`, CONSTRAINT `student_fk` FOREIGN KEY (`cno`) REFERENCES `class` (`cno`))