关于数据库的SQL相关知识
SQL的分类
1. DDL 数据定义语言
一、操作数据库:
1.查看所有数据库:show databases;
2.创建数据库:create database 数据库库名; (create database student;)
3.查看数据库创建数据的语句:show create database 数据库库名;(show create database student;)
4.删除数据库:drop database 数据库库名;(drop database student;)
5.使用数据库:use 数据库库名;(use student;)
二、操作表:
1.查看所有表:show tables;
2.创建表(student(id,name,age)):
create table student(
id int,name VARCHAR(20),age int
);
3.查看表结构:
(1).以 sql 格式返回:show create table 表名;(show create table stu;)
(2).以表格格式返回:desc 表名;(desc stu;)
4.删除表:drop table 表名;(drop table stu;)
5.复制表:create table 表名 like 想要复制的表的表名;(create table stu1 like stu;)
2. DML 数据操作语言
增删表中数据:
1.添加数据:insert into 表名 (列名1,列名2,…,列名n) values (值1,值2,…,值n);
例子:
`INSERT INTO student(id,name,age) VALUES
(1,'张三',25);`
注:若只定义表名,不定义列名,则默认给所有列赋值;除了数据类型,其他类型的值要用引号引起来
2.修改数据:update 表名 set 列名1=值1, 列名2=值2,…,列名n=值n where 条件;
注:若不加条件,则表中数据全部修改,需要特别注意才行。
3.删除数据:
- 删除表中的所有数据:delete from 表名 where 条件;(delete from student where id = 1;)
- 删除所有数据:truncate table 表名;(truncate table student;) --删除表,然后再创建一个一模一样的空表出
3. DQL 数据查询语言
一、基础查询数据:
- 查询所有列:select * from 表名;
- 指定列查询:select 列名1,列名2,…,列名n form 表名;(select name,age from student;)
- 查询时指定别名:select 列名1 as 别名1,列名2 别名2,…,列名n form 表名;(select name 名字,age as 年龄 from student;)(as 可以省略)
- 去除重复数据:select distinct 列名1,列名2,…,列名n from 表名;(select distinct age from student;)
二、排序:
- 单列排序:select *from 表名 order by 要排序的列名1 排序方式1;
- 组合排序:select *from 表名 order by 要排序的列名1 排序方式1,列名2 排序方式2;
注:排序方式:升序:ASC(默认);降序:DESC。
对多个字段进行排序(组合排序),如果第 1 个字段相等,则按第 2 个字段排序,依次类推。
三、聚合函数:
1.五个聚合函数:
max(列名) :求这一列的最大值
min(列名) :求这一列的最小值
avg(列名): 求这一列的平均值4 / 26
count(列名): 统计这一列有多少条记录
sum(列名) :对这一列求总和
注意:count函数对于 NULL 的记录不会统计,这时可以利用 IFNULL()函数,如果记录为 NULL,给个默认值,这样统计的数据就不会遗漏
如:select count(ifnull(id,0)) from student;
-- 查询年龄大于 20 的总数
select count(id) from student1 where age>20;
-- 查询数学成绩总分
select sum(math) 总分 from student1;
-- 查询数学成绩平均分
select avg(math) 平均分 from student1;
-- 查询数学成绩最高分
select max(math) 最高分 from student1;
-- 查询数学成绩最低分
select min(math) 最低分 from student1;
四、分组查询:
分组的目的就是为了统计,一般分组会跟聚合函数一起使用。
-- 按性别进行分组,求男生和女生数学的平均分
select sex, avg(math) 数学 from student1 group by sex;
-- 按性别进行分组,求男生和女生数学的平均分和人数,并且分数低于60分的人不参与分组。
select sex, avg(math) 数学,count(id) 人数 from student1 where math>60 group by sex;
-- 按性别进行分组,求男生和女生数学的平均分和人数,要求分数低于60分的人不参与分组,并且分组后人数要大于2人。
select sex, avg(math) 数学,count(id) 人数 from student1 where math>60 group by sex having 人数>2;
注意:
1.where在分组之前进行条件限制,而having在分组之后进行条件限制。
2.where后面不能跟聚合函数,而having可以。
五、分页查询:
使用limit关键字,作用是限制查询记录的条数。
语法:select * from student3 limit 开始的索引:每页查询的条数;
-- 查询学生表中数据,从第 1 条开始显示,显示 6 条。(第一个参数是0,可以省略写)
select * from student3 limit 0,6;--第一页
-- 查询学生表中数据,从第 7 条开始显示,显示 6 条。
select * from student3 limit 6,6;--第二页
-- 查询学生表中数据,从第 13 条开始显示,显示 6 条。(最后如果不够6条,有多少显示多少)
select * from student3 limit 12,6;--第三页
公式:开始的索引 = (当前页码-1)*每页要显示的条数;
六、条件查询:
不多说,直接上代码:
-- 查询表格中年龄大于20岁的信息。
select * from student where age>20;
--查询表格中年龄为18,20,21岁的信息。
select * from student where age in(18,20,21);
-- 查询姓名是3个字的人
SELECT * FROM student WHERE NAME LIKE '___';
--查询表格中名字包含“张”字的信息。
select * from student where name like "%张%";
七、约束:
作用:对表中的数据进行限制,保证数据的正确性、有效性和完整性。一个表如果添加了约束,不正确的数据将无法插入到表中。约束在创建表的时候添加比较合适。
- 主键约束:用来唯一标识数据库中的每一条记录,主键关键字: primary key。
--在创建表的时候给字段添加主键
create table stu2 (
id int primary key, -- id 为主键
name varchar(20),
age int
);-- 创建表学生表 stu2, 包含字段(id, name, age)将 id 做为主键
desc st5;
--在已有表中添加主键
alter table student modify name varchar(20) not null;-- 插入 NULL 的主键值, Column 'id' cannot be null,即添加了非空约束。
--删除主键
-- 错误的做法: alter table stu modify id int ;
ALTER TABLE stu DROP PRIMARY KEY; -- 删除了表中唯一的主键
--添加主键约束,并且完成主键自增长(auto_increment表示自动增长(字段类型必须是整数类型))
create table stu3(
id int primary key auto_increment, -- 给id添加主键约束并完成自动增长
name varchar(20)
);
--删除自动增长
ALTER TABLE stu MODIFY id INT;
--添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
注意:主键是给数据库和程序使用的,不是给最终的客户使用的。所以主键有没有含义没有关系,只要不重复,非空就行。
- 非空约束:某一列值不能为 null,关键字:not null。
--在创建表的时候给字段添加主键
create table stu4 (
id int,
name varchar(20) not null,--name不能为空和null
age int
);-- 创建表学生表 stu4, 包含字段(id, name, age)
--创建表后添加:
alter table stu4 modify name varchar(20) not null;-- 插入 NULL 的主键值,Column 'id' cannot be null,即添加了非空约束。
--删除非空约束
ALTER TABLE stu4 MODIFY name varchar(20);
- 唯一约束:表中值唯一,不能重复
--创建表时,添加唯一约束
create table stu5(
id int unique,---- 添加了唯一约束
name varchar(20)
);
--在创建表后,添加唯一约束
ALTER TABLE stu5 MODIFY id int UNIQUE;
--删除唯一约束
ALTER TABLE stu5 DROP INDEX id; -- 删除了唯一的索引
- 外键约束:从表与主表主键对应的那一列
主表: 一方,用来约束别人的表
从表: 多方,被别人约束的表
-- 创建从表 employee 并添加外键约束 emp_depid_fk
-- 从表
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int, -- 外键对应主表的主键
-- 创建外键约束
constraint emp_depid_fk foreign key (dep_id) references department(id)
)
-- 删除 employee 表的 emp_depid_fk 外键
alter table employee drop foreign key emp_depid_fk;
-- 在 employee 表存在的情况下添加外键
alter table employee add constraint emp_depid_fk
foreign key (dep_id) references department(id);
补充:
外键的级联:
- 级联操作: 是在修改和删除主表的主键时,同时更新或删除副表的外键值的操作,需要小心使用。
-- 删除 employee 表,重新创建 employee 表,添加级联更新和级联删除
drop table employee;
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int, -- 外键对应主表的主键
-- 创建外键约束
constraint emp_depid_fk foreign key (dep_id) references
department(id) on update cascade on delete cascade
)
八、多表关系
表与表之间的三种关系:
一对多:最常用的关系,如部门和员工(一个部门有多个员工,一个员工只能对应一个部门),班级和学生,客户和订单之间的关系。
实现方式:在多的一方建立外键,指向一的一方的主键。
多对多:学生选课表和学生表,一门课程可以有多个学生选择,一个学生也可以选择多门课程。
实现方式:多对多关系实现需要借助第三张中间表。中间表至少包含两个字段,这两个字段作为第三张表的外键,分别指向两张表的主键
一对一:相对使用比较少。如人与身份证之间的关系,一个人只有一个身份证,一个身份证只能对应一个人。 公民表和护照表类似。
实现方式:一对一关系实现,可以在任意一方添加唯一外键指向另一方的主键。
九、范式
分类:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。
三大范式:
-
第一范式(1NF):第一范式每一列不可再拆分(原子性)。
-
第二范式(2NF):在1NF的基础上,所有列完全依赖于主键列。
特点:- 一张表只描述一件事情。
- 表中的每一列都完全依赖于主键
-
第三范式(3NF):在2NF基础上,任何非主列不得传递依赖于主键(在2NF基础上消除传递依赖)。
补充:
- 函数依赖:A–>B,如果通过A属性(属性组)的值,可以确定唯一B属性的值。则称B依赖于A
例如:学号–>姓名。
2. 完全函数依赖:A–>B, 如果A是一个属性组,则B属性值得确定需要依赖于A属性组中所有的属性值。
例如:(学号,课程名称) --> 分数
3. 部分函数依赖:A–>B, 如果A是一个属性组,则B属性值得确定只需要依赖于A属性组中某一些值即可。
例如:(学号,课程名称) – > 姓名
4. 传递函数依赖:A–>B, B – >C . 如果通过A属性(属性组)的值,可以确定唯一B属性的值,在通过B属性(属性组)的值可以确定唯一C属性的值,则称 C 传递函数依赖于A
例如:学号–>系名,系名–>系主任
5. 码:如果在一张表中,一个属性或属性组,被其他所有属性所完全依赖,则称这个属性(属性组)为该表的码
例如: 成绩表中的码为:(学号,课程名称)
十、数据库的备份和还原
备份: mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
还原:
1. 登录数据库
2. 创建数据库
3. 使用数据库
4. 执行文件。source 文件路径
4. DCL 数据控制语言
-
管理用户
-
添加用户:
语法:CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’; -
删除用户:
语法:DROP USER ‘用户名’@‘主机名’; -
修改用户密码:
1.UPDATE USER SET PASSWORD = PASSWORD(‘新密码’) WHERE USER = ‘用户名’;
例子:UPDATE USER SET PASSWORD = PASSWORD(‘abc’) WHERE USER = ‘lisi’;2.SET PASSWORD FOR ‘用户名’@‘主机名’ = PASSWORD(‘新密码’);
例子:SET PASSWORD FOR ‘root’@‘localhost’ = PASSWORD(‘123’); -
查询用户:
– 1. 切换到mysql数据库
USE myql;
– 2. 查询user表
SELECT * FROM USER;
- 通配符: % 表示可以在任意主机使用用户登录数据库
-
总结:学习数据库有一些时日了,刚开始时感觉还不错,但随着深入学习有些知识点容易忘记和搞混,因此学习中需要注意基础,把握学习进度,贪多嚼不烂,每次学完一小节后最好去MySQL软件或者Navicat软件上操作一下,加深一下记忆。我知道自己天分不足,编程方面不如其他人,写代码时经常会出现一些错误,考虑情况不周全是常有的了,但是每天学一点,相信自己一定会有进步的,哪怕只有一点也好。学习编程时光漫长,愿与君共勉。