Mysql数据库知识点

最新推荐文章于 2022-03-06 10:03:09 发布

quge_name_harder

最新推荐文章于 2022-03-06 10:03:09 发布

阅读量282

点赞数

分类专栏： Mysql 文章标签：数据约束三大范式关联查询存储过程触发器

本文链接：https://blog.csdn.net/quge_name_harder/article/details/81058614

版权

Mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

数据库相关操作

查询所有数据库

mysql>show databases;

创建数据库

mysql> create database 数据库名 -- 指定默认字符集创建数据库
-> default character set utf8

查看数据库的默认字符集

mysql> show create database 数据库名;

删除数据库

mysql> drop database 数据库名;

修改数据库

mysql> alter database 数据库名 default character set gbk;

表相关操作

查看所有表

mysql> show tables;

创建表

mysql> create table student[表名](
-> sid[字段名称] int[字段类型],
-> sname varchar(20)[类型长度],
-> sage int
-> );

查看表结构

mysql> desc student;

删除表

mysql> drop table student;

修改表

1）添加字段

mysql> alter table student add column sgender varchar(2);

2）删除字段

mysql> alter table student drop column sgender;

3）修改字段类型

mysql> alter table student modify column remark varchar(100);

4）修改字段名称

mysql> alter table student change column sgender gender varchar(2);

5）修改表名称

mysql> alter table student rename to teacher;

增删改数据

增加数据

-- 插入所有字段。一定依次按顺序插入
INSERT INTO student VALUES(1,'张三','男',20);
-- 注意不能少或多字段值
-- INSERT INTO student VALUES(2,'李四','女');
-- 插入部分字段
INSERT INTO student(id,NAME) VALUES(2,'李四');

修改数据

-- 修改所有数据（建议少用）
UPDATE student SET gender='女';
-- 带条件的修改（推荐使用）
UPDATE student SET gender='男' WHERE id=1; -- 修改id为1的学生，修改性别为男
-- 修改多个字段,注意: SET 字段名=值,字段名=值,....

UPDATE student SET gender='男',age=30 WHERE id=2;

删除数据

-- 删除所有数据（建议少用）
DELETE FROM student;
-- 带条件的删除(推荐使用)
DELETE FROM student WHERE id=2;
-- 另一种方式
-- delete from: 可以全表删除 1)可以带条件删除 2）只能删除表的数据，不能删除表的约束
3）使用delete from删除的数据可以回滚（事务）
-- truncate table: 可以全表删除 1）不能带条件删除 2）即可以删除表的数据，也可以删除表的
约束 3）使用truncate table删除的数据不能回滚
TRUNCATE TABLE student;

查询数据（重点）

查询所有列

SELECT * FROM student;

查询指定列

SELECT id,NAME,gender FROM student;

查询时添加常量列

SELECT id,NAME,gender,age,'国家' AS '国籍' FROM student;

查询时合并列

-- 需求：查询每个学生的servlet和jsp的总成绩
SELECT id,NAME,(servlet+jsp) AS '总成绩' FROM student;
-- 注意：合并列只能合并数值类型的字段

查询时去除重复记录(DISTINCT)

SELECT DISTINCT gender FROM student;

条件查询

逻辑条件： and(与) or(或)

-- 需求：查询id为2，且姓名为李四的学生
SELECT * FROM student WHERE id=2 AND NAME='李四'; -- 交集
-- 需求：查询id为2，或姓名为张三的学生
SELECT * FROM student WHERE id=2 OR NAME='张三'; -- 并集

比较条件： > < >= <= = <>(不等于) between and (等价于>= 且 <=)

-- 需求：查询servlet成绩大于70分的学生
SELECT * FROM student WHERE servlet>70;
-- 需求：查询jsp成绩大于等于75，且小于等于90分的学生
SELECT * FROM student WHERE jsp>=75 AND jsp<=90;
-- 另一个语法
SELECT * FROM student WHERE jsp BETWEEN 75 AND 90; -- (包前包后)
SELECT * FROM student WHERE gender<>'男';

判空条件(null 空字符串)： is null / is not null / ='' / <>''

-- 需求：查询地址为空的学生（包括null和空字符串）
-- null vs 空字符串
-- null：表示没有值
-- 空字符串：有值的！
-- 判断null
SELECT * FROM student WHERE address IS NULL ;
-- 判断空字符串
SELECT * FROM student WHERE address='';
SELECT * FROM student WHERE address IS NULL OR address=''; -- （包括null和空字符串）

模糊条件： like

-- 通常使用以下替换标记：
-- % : 表示任意个字符
-- _ : 表示一个字符
-- 需求：查询姓‘张’的学生
SELECT * FROM student WHERE NAME LIKE '李%';

聚合查询

聚合查询（使用聚合函数的查询）
-- 常用的聚合函数： sum() avg() max() min() count()
-- 需求：查询学生的servlet的总成绩 (sum() :求和函数)
SELECT SUM(servlet) AS 'servlet的总成绩' FROM student;
-- 注意：count（）函数统计的数量不包含null的数据
-- 使用count统计表的记录数，要使用不包含null值的字段

分页查询

分页查询（limit 起始行,查询几行）
-- 起始行从0开始
-- 分页：当前页每页显示多少条
-- 分页查询当前页的数据的sql: SELECT * FROM student LIMIT (当前页-1)*每页显示多少条,每页显
示多少条;
-- 需求：查询第1,2条记录（第1页的数据）
SELECT * FROM student LIMIT 0,2;

查询排序

查询排序（order by ）
-- 语法：order by 字段 asc/desc
-- asc: 顺序，正序。数值：递增，字母：自然顺序（a-z）
-- desc: 倒序，反序。数值：递减，字母：自然反序(z-a)
-- 默认情况下，按照插入记录顺序排序
SELECT * FROM student;

分组查询

分组查询(group by)
SELECT gender,COUNT(*) FROM student GROUP BY gender;

分组查询后筛选

-- 需求：查询总人数大于2的性别
-- 1) 查询男女的人数
-- 2）筛选出人数大于2的记录(having)
--- 注意：分组之前条件使用where关键字，分组之前条件使用having关键字
SELECT gender,COUNT(*) FROM student WHERE GROUP BY gender HAVING COUNT(*)>2;

数据约束

什么数据约束

对用户操作表的数据进行约束

默认值

作用：当用户对使用默认值的字段不插入值的时候，就使用默认值。
注意：
1）对默认值字段插入null是可以的。
2）对默认值字段可以插入非null
CREATE TABLE student(
id INT,
NAME VARCHAR(20),
address VARCHAR(20) DEFAULT '安徽' -- 默认值
)

非空

作用：限制字段必须赋值
注意：
1）非空字符必须赋值
2）非空字符不能赋null
需求： gender字段必须有值（不为null）
CREATE TABLE student(
id INT,
NAME VARCHAR(20),
gender VARCHAR(2) NOT NULL -- 非空
)
-- 非空字段必须赋值
-- 非空字符不能插入null

唯一

作用：对字段的值不能重复
注意：
1）唯一字段可以插入null
2）唯一字段可以插入多个null
CREATE TABLE student(
id INT UNIQUE, -- 唯一
NAME VARCHAR(20)
)

主键

作用：非空+唯一
注意：
1）通常情况下，每张表都会设置一个主键字段。用于标记表中的
每条记录的唯一性。
2）建议不要选择表的包含业务含义的字段作为主键，建议给每张表独立设计一个非业务含义的 id字段。
CREATE TABLE student(
id INT PRIMARY KEY, -- 主键
NAME VARCHAR(20)
)

自增长

作用：自动递增
CREATE TABLE student(
id INT(4) ZEROFILL PRIMARY KEY AUTO_INCREMENT, -- 自增长，从0开始 ZEROFILL 零填充
NAME VARCHAR(20)
)
-- 自增长字段可以不赋值，自动递增

外键

作用：约束两种表的数据
出现两种表的情况：
解决数据冗余高问题：独立出一张表
例如：员工表和部门表
问题出现：在插入员工表数据的时候，员工表的部门ID字段可以随便插入！！！！！
使用外键约束：约束插入员工表的部门ID字段值
解决办法：在员工表的部门ID字段添加一个外键约束
-- 部门表（主表）
CREATE TABLE dept(
id INT PRIMARY KEY,
deptName VARCHAR(20)
)
-- 修改员工表（副表/从表）
CREATE TABLE employee(
id INT PRIMARY KEY,
empName VARCHAR(20),
deptId INT,-- 把部门名称改为部门ID
-- 声明一个外键约束
CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)
-- 外键名称外键参考表(参考字段)
)
注意：
1）被约束的表称为副表，约束别人的表称为主表，外键设置在副表上的！
2）主表的参考字段通用为主键！
3）添加数据：先添加主表，再添加副表
4）修改数据：先修改副表，再修改主表
5）删除数据：先删除副表，再删除主表

级联操作

问题：当有了外键约束的时候，必须先修改或删除副表中的所有关联数据，才能修改或删除主表！但是，我们希望直接修改或删除主表数据，从而影响副表数据。可以使用级联操作实现
级联修改： ON UPDATE CASCADE
级联删除： ON DELETE CASCADE
CREATE TABLE employee(
id INT PRIMARY KEY,
empName VARCHAR(20),
deptId INT,-- 把部门名称改为部门ID
-- 声明一个外键约束
CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE -- ON CASCADE UPDATE ：级联修改
)
注意：级联操作必须在外键基础上使用

三大范式

设计原则：建议设计的表尽量遵守三大范式。
第一范式：要求表的每个字段必须是不可分割的独立单元
第二范式：在第一范式的基础上，要求每张表只表达一个意思。表的每个字段都和表的主键有依赖。
第三范式：在第二范式基础，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系。

关联查询(多表查询)

交叉连接查询

（不推荐。产生笛卡尔乘积现象：4 * 4=16，有些是重复记录）

SELECT empName,deptName FROM employee,dept;

内连接查询

只有满足条件的结果才会显示(使用最频繁)

SELECT empName,deptName -- 2）确定哪些哪些字段
FROM employee,dept -- 1）确定查询哪些表
WHERE employee.deptId=dept.id -- 3）表与表之间连接条件
内连接的另一种语法
SELECT empName,deptName
FROM employee
INNER JOIN dept
ON employee.deptId=dept.id;

左[外]连接查询

使用左边表的数据去匹配右边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null

-- （注意：左外连接：左表的数据一定会完成显示！）
SELECT d.deptName,e.empName
FROM dept d
LEFT OUTER JOIN employee e
ON d.id=e.deptId;

右[外]连接查询

使用右边表的数据去匹配左边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null

-- （注意：右外连接：右表的数据一定会完成显示！）
SELECT d.deptName,e.empName
FROM employee e
RIGHT OUTER JOIN dept d
ON d.id=e.deptId;

自连接查询

SELECT e.empName,b.empName
FROM employee e
LEFT OUTER JOIN employee b
ON e.bossId=b.id;

存储过程

存储过程，带有逻辑的sql语句

存储过程特点

1）执行效率非常快！存储过程是在数据库的服务器端执行的
2）移植性很差！不同数据库的存储过程是不能移植。

存储过程语法

-- 创建存储过程
DELIMITER $ -- 声明存储过程的结束符
CREATE PROCEDURE pro_test() --存储过程名称(参数列表)
BEGIN -- 开始
-- 可以写多个sql语句; -- sql语句+流程控制
SELECT * FROM employee;
END $ -- 结束结束符
-- 执行存储过程
CALL pro_test(); -- CALL 存储过程名称(参数);

参数：
IN：表示输入参数，可以携带数据带存储过程中
OUT：表示输出参数，可以从存储过程中返回结果
INOUT：表示输入输出参数，既可以输入功能，也可以输出功能

-- ***mysql的变量******
-- 全局变量（内置变量）：mysql数据库内置的变量（所有连接都起作用）
-- 查看所有全局变量： show variables
-- 查看某个全局变量： select @@变量名
-- 修改全局变量： set 变量名=新值
-- character_set_client: mysql服务器的接收数据的编码
-- character_set_results：mysql服务器输出数据的编码

-- 会话变量：只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开，那么会话变量全部丢失！
-- 定义会话变量: set @变量=值
-- 查看会话变量： select @变量

-- 局部变量：在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕，局部变量就丢失！！

触发器

当操作了某张表时，希望同时触发一些动作/行为，可以使用触发器完成！！
例如：当向员工表插入一条记录时，希望同时往日志表插入数据
-- 需求：当向员工表插入一条记录时，希望mysql自动同时往日志表插入数据
-- 创建触发器(添加)
CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW -- 当往员工表插入一条记录时
INSERT INTO test_log(content) VALUES('员工表插入了一条记录');
-- 创建触发器(修改)
CREATE TRIGGER tri_empUpd AFTER UPDATE ON employee FOR EACH ROW -- 当往员工表修改一条记录时
INSERT INTO test_log(content) VALUES('员工表修改了一条记录');
-- 创建触发器(删除)
CREATE TRIGGER tri_empDel AFTER DELETE ON employee FOR EACH ROW -- 当往员工表删除一条记录时
INSERT INTO test_log(content) VALUES('员工表删除了一条记录');