数据库的相关概念：
- 主键：
- 1、指的是一个列或多列的组合，其值能唯一地标识表中的每一行，通过它可强制表的实体完整性：
- 2、主键主要是用于其他表的外键关联，以及本记录的修改与删除。
- 索引：
- 1、是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
- 2、如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。
- 3、索引的一个主要目的就是加快检索表中数据的方法，亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。
- 约束：
- 1、约束是在表中定义的用于维护数据库完整性的一些规则。
- 2、通过为表中的列定义约束可以防止将错误的数据插入表中，也可以保持表之间数据的一致性。
- 外键：（Foreign Key）：
- 1、如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。
- 2、由此可见，外键表示了两个关系之间的联系。
- 3、以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。外键又称作外关键字。
- 字段：
- 1、在数据库中，大多数时，表的“列”称为“字段”，每个字段包含某一专题的信息。就像“通讯录”数据库中，“姓名”、“联系电话”这些都是表中所有行共有的属性，所以把这些列称为“姓名”字段和“联系电话”字段。
数据库的相关操作：
- 分页查询：
- 产生原因：
- 当数据量过大时，可能会导致各种各样的问题发生，例如：服务器资源被耗尽，因数据传输量过大而使处理超时，等等。最终都会导致查询无法完成。
- 解决方法：
- 解决这个问题的一个策略就是“分页查询”，也就是说不要一次性查询所有的数据，每次只查询一“页“的数据。这样分批次地进行处理，可以呈现出很好的用户体验，对服务器资源的消耗也不大。
- 表的关联查询：
- 内连接：
- 等值连接：
- 在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。
- 不等值连接：
- 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。
- 自然连接：
- 在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。
- 内连接：
- 内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。
- select * from book as a,stu as b where a.sutid = b.stuid
- select * from book as a inner join stu as b on a.sutid = b.stuid
- 外链接：
- 左连接：
- 是以左表为基准，将a.stuid = b.stuid的数据进行连接，然后将左表没有的对应项显示，右表的列为NULL
- select * from book as a left join stu as b on a.sutid = b.stuid
- 右连接：
- 是以右表为基准，将a.stuid = b.stuid的数据进行连接，然以将右表没有的对应项显示，左表的列为NULL
- select * from book as a right join stu as b on a.sutid = b.stuid
- 全连接：
- 完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。
- select * from book as a full outer join stu as b on a.sutid = b.stuid
- 交叉连接：
- 交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。
- select * from book as a cross join stu as b order by a.id
- 数据库的存储过程：
- 存储过程简介：
- SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。
- 存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。
- 存储过程的优点：
- 增强SQL语言的功能和灵活性：存储过程可以用控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
- 标准组件式编程：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
- 较快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
- 减少网络流量：针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。
- 作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。
数据库的基本语句：
-- 创建数据库并指定字符集
CREATE DATABASE IF NOT EXISTS hello DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 创建一个数据库但没有指定字符集
CREATE DATABASE hello;
--数据库的基本命令：
-- 查看所有的数据库
SHOW DATABASES;
-- 使用某个数据库
USE hello;
-- 创建表
CREATE TABLE hello_text2(id INT,姓名 VARCHAR(10),地点 VARCHAR(20));
-- 创建表
CREATE TABLE hello_text2(
id INT,
姓名 VARCHAR(10),
地点 VARCHAR(20)
);
-- 创建表并增加约束
-- UNIQUE KEY(唯一键)就是这个列的内容都是唯一的，没有重复值
CREATE TABLE hello_text3(
id INT NOT NULL PRIMARY KEY,
姓名 VARCHAR(10) NOT NULL UNIQUE KEY,
地点 VARCHAR(20) NOT NULL UNIQUE KEY
)
-- 查看表结构
-- DESC 表示按倒序排序(即：从大到小排序) -降序排列
DESC hello_text1;
-- 删除表字段,即就是删除每一行的总称
-- column表示一列
ALTER TABLE hello_text1 DROP COLUMN id;
-- 删除主键
ALTER TABLE hello_text3 PRIMARY KEY;
-- 删除索引
ALTER TABLE hello_text4 DROP INDEX idx_hello_text4_name;
-- 删除外键约束
ALTER TABLE hello_text3 DROP FOREIGN KEY fk_hello_text3_heroclass_id;
-- 删除表
DROP TABLE hello_text1;
-- 只复制表结构及约束，但不复制数据
CREATE TABLE hello_text4 LIKE hello_text1;
-- 复制表结构及数据，但不复制约束
CREATE TABLE hello_text4 AS SELECT * FROM hello_text1;
-- 添加表字段
ALTER TABLE hello_text4 ADD birth DATE NULL
-- column表示一列
ALTER TABLE hello_text4 ADD COLUMN memo TEXT NULL
-- 添加索引
ALTER TABLE hello_text3 ADD INDEX idx_hello_text3_name (NAME)
-- 添加主键
ALTER TABLE hello_text2 ADD PRIMARY KEY (id)
ALTER TABLE hello_text2 ADD UNIQUE KEY (NAME)
-- 创建分类表
CREATE TABLE hello_class (
id INT PRIMARY KEY,
classname VARCHAR(10) NOT NULL
)
-- 创建分类id
ALTER TABLE hello_textt ADD COLUMN classid INT
-- 增加外键约束
ALTER TABLE hello_text2 ADD FOREIGN KEY fk_hellotextt_helloclass_id(classid) REFERENCES hero_class(id)
-- 修改字段类型时要注意字段类型的兼容性及精度，除非该列全部数据为null
ALTER TABLE hello_text5 CHANGE COLUMN NAME helloname VARCHAR(20)
-- 修改字段类型,注意兼容性及精度，除非该列全部数据为null
ALTER TABLE hello_text5 MODIFY COLUMN heroname VARCHAR(10)
-- 如果给表中的某个字段添加了default约束，当向表中插入记录数据时，该字段如果不指定值，则系统自动填充default指定的值。
-- 设置default
ALTER TABLE hello_text6 ALTER COLUMN heroname SET DEFAULT 'name'
-- 删除default
ALTER TABLE hello_text6 ALTER COLUMN heroname DROP DEFAULT
-- 禁用约束
ALTER TABLE hello_text3 DISABLE KEYS
-- 启用约束
ALTER TABLE hello_text3 ENABLE KEYS
-- 查看数据库中的表
SHOW TABLES;
-- 备份数据库
mysqldump -uroot -p123456 —-database hello> e:\hello.sql
-- 恢复数据库
mysql -uroot -p123456<e:\hero11.sql
举例：
-- 创建学生表
-- COMMENT表示注释
CREATE TABLE student(
id INT PRIMARY KEY COMMENT '主键',
NAME VARCHAR(10) NOT NULL COMMENT '姓名'
)
-- 创建项目表
CREATE TABLE project(
pid INT PRIMARY KEY COMMENT'项目id主键',
pname VARCHAR(20) NOT NULL COMMENT'项目名称',
pdesc VARCHAR(100) NULL,
tianjiaren INT
)
-- 添加项目表和学生表之间的关系
-- ALTER:通过更改、添加或删除列和约束，重新分配分区，或者启用或禁用约束和触发器，从而修改表的定义。
-- 简单说就是修改表结构的
ALTER TABLE project ADD FOREIGN KEY fk_project_student(tianjiaren) REFERENCES student(id);
-- 给学生表添加信息
INSERT INTO student(id,NAME) VALUES(1,'学生1');
INSERT INTO student(id,NAME) VALUES(2,'学生2');
INSERT INTO student(id,NAME) VALUES(3,'学生3');
INSERT INTO student(id,NAME) VALUES(4,'学生4');
INSERT INTO student(id,NAME) VALUES(5,'学生5');
-- 给项目表添加信息
INSERT INTO project(pid,pname,tianjiaren) VALUES(1,'项目11',1);
INSERT INTO project(pid,pname,tianjiaren) VALUES(2,'项目12',1);
INSERT INTO project(pid,pname,tianjiaren) VALUES(3,'项目21',2);
INSERT INTO project(pid,pname,tianjiaren) VALUES(4,'项目22',2);
INSERT INTO project(pid,pname,tianjiaren) VALUES(5,'项目31',3);
INSERT INTO project(pid,pname,tianjiaren) VALUES(6,'项目32',3);
INSERT INTO project(pid,pname,tianjiaren) VALUES(7,'项目41',4);
INSERT INTO project(pid,pname,tianjiaren) VALUES(8,'项目42',4);
INSERT INTO project(pid,pname,tianjiaren) VALUES(9,'项目51',5);
INSERT INTO project(pid,pname,tianjiaren) VALUES(10,'项目52',5);
INSERT INTO project(pid,pname,tianjiaren) VALUES(11,'项目52aa',5);
-- 一次插入多个值
INSERT INTO project(pid,pname,tianjiaren) VALUES(12,'项目53',5),(13,'项目55',5);
-- 计算1+2的值
SELECT 1+2 FROM DUAL;
-- 查询项目表中的所有字段
SELECT pname,pid,tianjiaren FROM project;
-- 使用通配符*代表项目表中的所有字段
SELECT * FROM project;
-- 查询学生3提交的项目
-- 方式1:
SELECT * FROM project WHERE tianjiaren=3;
-- 方式2:
SELECT pname,pid,tianjiaren FROM project
WHERE tianjiaren=3;
-- 统计一下每个学生提交了几个项目
SELECT tianjiaren ,conut(*) FROM project GROUP BY tianjiaren;
-- 找到项目提交数不足3个的学生id
SELECT tianjiaren,COUNT(*) FROM project GROUP BY tianjiaren
HAVING COUNT(*)<3;
-- 按照学生的编号从大到小的顺序显示其提交的项目
-- DESC从大到小排序
SELECT * FROM project ORDER BY tianjiaren DESC;
-- 按照学生的编号从小到大的顺序显示项目，一次显示三行
-- ASC从小到大排序
SELECT * FROM project ORDER BY tianjiaren ASC LIMIT 3;
-- 内连接:
-- inner join是内连接
-- 指连接结果仅包含符合连接条件的行，参与连接的两个表都应该符合连接条件。
SELECT * FROM project,student WHERE project.`tianjiaren`=student.`id`;
SELECT * FROM project INNER JOIN student ON project.`tianjiaren`=student.`id`;
SELECT * FROM student INNER JOIN project ON project.`tianjiaren`=student.`id`;
-- 表的别名
SELECT * FROM project a,student b WHERE a.`tianjiaren`=b.`id`;
-- 根据学生名字查询该学生提交的项目
SELECT * FROM project,student WHERE project.`tianjiaren`=student.`id` AND student.`name` LIKE '%3%';
SELECT * FROM project,student WHERE student.`name` LIKE '%3%';
-- 使用子查询
-- IN:表示一个数据集
-- EXISTS:当数据很多的时候,它效率要远高于in的写法
-- 代码解释:循环project表找到tianjiaren,查找tianjiaren中`student`student表中name中含有3的行
SELECT * FROM project WHERE tianjiaren IN (SELECT id FROM student WHERE NAME LIKE '%3%');
-- 子查询作为操作数
-- DUAL:一行一列的表
SELECT (SELECT id FROM student WHERE id=1) FROM DUAL;
-- 子查询作为条件
-- >:表示大于号
SELECT * FROM project WHERE tianjiaren > (SELECT 2 FROM DUAL);
-- ANY:表示任意一个,any表示有任何一个满足就返回true
-- ALL:表示所有,all表示全部满足才返回true
-- tainjiaren大于4或者大于5都可以
SELECT * FROM project WHERE tianjiaren > ANY (SELECT id FROM student WHERE id>3);
-- tainjiaren必须大于4和大于5,但是这样没有结果显示,因为不存在
SELECT * FROM project WHERE tianjiaren > ALL (SELECT id FROM student WHERE id>3);
-- 多列比较
SELECT (3,2)<(3,4) FROM DUAL;
-- exists 和 not exists
-- 根据学生名字查询该学生提交的项目
SELECT * FROM project WHERE EXISTS (SELECT id FROM student WHERE id=project.`tianjiaren` AND NAME LIKE '%3%');
-- 子查询作为数据表使用
SELECT * FROM (SELECT * FROM project ,student WHERE project.`tianjiaren`=student.`id`) a WHERE a.name LIKE '%3%';
-- 修改数据
-- 将所有偶数项目id的状态变更为“是”
-- MOD:数学方法求余
UPDATE project SET pdesc='是' WHERE MOD(pid,2)=0;
-- 将项目序号5以内的项目介绍更新为项目名称+项目id
-- concat:字符串的拼接
-- cast:函数用于将某种数据类型的表达式显式转换为另一种数据类型
-- as:用于分隔两个参数，在AS之前的是要处理的数据，在AS之后是要转换的数据类型
SELECT CONCAT(pname,CAST(pid AS CHAR)) FROM project;
UPDATE project SET pdesc=CONCAT(pname,CAST(pid AS CHAR)) WHERE pid<5;
SELECT * FROM project;
-- update可以更新多列
UPDATE project SET pdesc='123',pname=CONCAT(pname,'123') WHERE pid BETWEEN 5 AND 8
-- 删除或更新时的约束
UPDATE project SET tianjiaren=6 WHERE pid=11;
-- 删除student表中id为5的行
DELETE FROM student WHERE id=5
-- 删除project表中tianjiaren为5的行
DELETE FROM project WHERE tianjiaren=5
SELECT * FROM student
-- 使用虚拟表显示2%3和2%4
SELECT MOD(3,2),MOD(4,2) FROM DUAL
笛卡尔积：
--笛卡尔积:
-- 创建两个表分别为tba和tbb
CREATE TABLE tba(
a1 INT,
a2 INT
)
CREATE TABLE tbb(
b1 INT,
b2 INT
)
-- 给两个表添加内容
INSERT INTO tba(a1,a2) VALUES(1,2),(3,4);
INSERT INTO tbb(b1,b2) VALUES(1,5),(8,10);
-- 笛卡尔积:
SELECT * FROM tba,tbb;
-- 内连接
SELECT * FROM tba INNER JOIN tbb ON tba.`a1`=tbb.`b1`;
-- 左外连接
SELECT * FROM tba LEFT JOIN tbb ON tba.`a1`=tbb.`b1`;
-- 右外连接
SELECT * FROM tba RIGHT JOIN tbb ON tba.`a1`=tbb.`b1`;
-- 全外连接
-- UNION:并集，用于将两个查询语句结果合并为一个记录集
SELECT * FROM tba LEFT JOIN tbb ON tba.`a1`=tbb.`b1`
UNION
SELECT * FROM tba RIGHT JOIN tbb ON tba.`a1`=tbb.`b1`;
-- 全外连接，也可以使用:FULL OUTER
SELECT * FROM tba FULL OUTER JOIN tbb ON tba.`a1`=tbb.`b1`;
-- 交叉连接：交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。
-- 交叉联接也称作笛卡尔积。
SELECT * FROM tba CROSS JOIN tbb ON tba.`a1`=tbb.`b1`;
触发器练习：
--触发器练习
CREATE TABLE dingdan(dingdanid INT PRIMARY KEY,zhuangtai VARCHAR(20));
CREATE TABLE mingxi(mingxiid INT PRIMARY KEY,shangpinid INT,lingyong INT,dingdanid INT);
CREATE TABLE shangpin(shangpinid INT NOT NULL PRIMARY KEY,shuliang INT);
INSERT INTO shangpin (shangpinid,shuliang) VALUES(1,100);
INSERT INTO shangpin (shangpinid,shuliang) VALUES(2,100);
INSERT INTO shangpin (shangpinid,shuliang) VALUES(3,100);
INSERT INTO dingdan(dingdanid,zhuangtai) VALUES(1,'no')
INSERT INTO mingxi(mingxiid,shangpinid,lingyong,dingdanid) VALUES(1,1,5,1);
INSERT INTO mingxi(mingxiid,shangpinid,lingyong,dingdanid) VALUES(2,2,14,1);
SELECT * FROM dingdan
DELIMITER $$
CREATE TRIGGER trg01 AFTER UPDATE ON dingdan
FOR EACH ROW BEGIN
IF new.zhuangtai='yes' AND old.zhuangtai!='yes' THEN
UPDATE shangpin a SET shuliang=shuliang-IFNULL((SELECT lingyong FROM mingxi b WHERE b.dingdanid=new.dingdanid AND b.shangpinid=a.shangpinid ),0);
END IF;
END$$
DELIMITER ;
UPDATE dingdan SET zhuangtai='yes'
SELECT * FROM shangpin
扫一扫
6858
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
