数据库知识点

一、数据库设计

（一）数据库基本概念

  1.数据库技术

是一门研究数据管理的技术

2.DB

数据库（ database ）：存储数据的“仓库”。它保存了一系列有组织的数据。

  3.DBMS

数据库管理系统（ Database Management System ）是采用数据库技术构建的复杂计算机系统。

  4.SQL

结构化查询语言（ Structure Query Language ）：专门用来与数据库通信的语言。   

5.什么是数据模型？

数据（data）是描述事物的符号记录。

模型（Model)是现实世界的抽象。

数据模型（Data Model ）是数据特征的抽象。

6.数据模型描述了什么？

数据结构        数据操作        数据约束

7.什么叫关系模型？

用二维表的形式表示实体和实体间联系的数据模型

        8.Oracle/MySql/SQLServer就是关系型数据库管理系统

（二）软件项目开发周期中数据库设计

1.需求分析 ：分析客户的业务和数据处理需求 ( 我们需要什么样的数据库 )

2.概要 ( 概念 ) 设计 ：设计数据库的 E-R 模型图，确认需求信息的正确和完整

3.详细 ( 逻辑 ) 设计 ：应用三大范式审核数据库结构 （ 转化成数据模型，形成二维表 ）

4.物理设计 ( 代码编写 ) ：物理实现数据库，编码实现应用 ( 物理设备 上 存储结构和存取方法 )

5.验证设计 ( 软件测试 ) ：合理性、正确性

6.安装部署：运行与维护设计

 （三）实体

        实体是客观存在并可相互区分的事物，可以是人、物等实际对象，也可以是某些概念

        实体可以是事物本身，也可以是指事物与事物之间的联系。

        具有相同属性的实体的集合称为实体集

（四）E-R图

符号	含义
长方形	实体，一般是名词
椭圆	属性，一般是名词
菱形	关系，一般是动词
	码

 数据库常用SQL语句

--创建表
CREATE TABLE userInfo(
id int(10) not NULL auto_increment,
name VARCHAR(10),
age int(10),
bir DATE,
PRIMARY KEY(id)

);
--添加列
ALTER TABLE userinfo ADD phone VARCHAR(20);
--修改列
ALTER TABLE userinfo MODIFY phone int(20);
ALTER TABLE userinfo MODIFY deptid INT(20);
ALTER TABLE userinfo ADD aa int(20);
--删除列
ALTER TABLE userinfo DROP aa;
--插入数列
INSERT INTO userinfo VALUES(5,'admin',20,'2000-11-1',15858545);
INSERT INTO userinfo (name,age) VALUES('aa',30);
--查询数据
SELECT *FROM userinfo;
DELETE FROM userinfo;
DELETE FROM userinfo WHERE name='aa';
--修改数据
UPDATE userinfo SET name='张三'；
UPDATE userinfo SET name='张三' WHERE age=30；

--模糊查询
SELECT*FROM userinfo WHERE name LIKE '%a%';

--空值查询
SELECT*FROM userinfo WHERE phone is NULL;
SELECT*FROM userinfo WHERE phone is NOT NULL;
-排序
SELECT*FROM userinfo ORDER BY age;
SELECT*FROM userinfo ORDER BY age ASC;
SELECT*FROM userinfo ORDER BY age DESC;
SELECT*FROM userinfo ORDER BY age,id ASC;
SELECT*FROM userinfo ORDER BY age DESC,id ASC;
--查询数据
SELECT*FROM userinfo LIMIT 0,2;
SELECT*FROM userinfo ORDER BY age LIMIT 0,2;
--范围数据
SELECT* FROM userinfo WHERE age BETWEEN 20 AND 30;
--组成员查询
SELECT* FROM userinfo WHERE name in ('admin');
SELECT *FROM userinfo WHERE name not in ('admin')

--求和
SELECT sum(id) as sum from userinfo;
SELECT sum(sal) sum from userinfo;
--分组求和
SELECT sum(sal) sum from userinfo GROUP BY deptid;
SELECT  sal,sum(sal) sum from userinfo GROUP BY deptid;

--联合查询
SELECT max(id) num from userinfo
UNION
SELECT min(id) FROM userinfo;
SELECT* FROM userinfo WHERE deptid=1
union
SELECT * from userinfo;

--添加列
ALTER TABLE userinfo ADD deptid INT(20);
ALTER TABLE userinfo ADD sal INT(20);
--工资从高到低前三位
SELECT *FROM userinfo ORDER BY sal desc LIMIT 3;
--按部门查询平均工资

SELECT deptid,avg(sal) FROM userinfo GROUP BY deptid ;
--按部门查询年龄20岁以上人员的平均工资
SELECT deptid,AVG(sal) FROM userinfo WHERE age>=20 GROUP BY deptid ;


--查询工资高于admin的信息
SELECT * FROM userinfo WHERE sal>(SELECT sal FROM userinfo WHERE name in('admin'));
--工资高于部门1所以用户工资的数据

--工资高于部门1任意用户工资的数据
SELECT * FROM userinfo WHERE sal>(SELECT MAX(sal) FROM userinfo WHERE deptid in (1)) ;



CREATE TABLE result(
	id int(6) not null auto_increment,
	student_id int not null,
	course_id int not null,
	score int,
	primary key(id)

)
INSERT INTO result(student_id,course_id,score)VALUES ('1','1','66');
INSERT INTO result(student_id,course_id,score)VALUES ('1','1','69');
INSERT INTO result(student_id,course_id,score)VALUES ('2','1','82');
INSERT INTO result(student_id,course_id,score)VALUES ('2','2','93');
INSERT INTO result(student_id,course_id,score)VALUES ('3','1','55');
INSERT INTO result(student_id,course_id,score)VALUES ('111','1','55');
--查询有成绩信息用户

--（先关子查询）查询科目2有成绩信息的用户
SELECT *FROM userinfo WHERE id in(SELECT student_id from result WHERE course_id=2);--循环多次
SELECT*FROM userinfo as u WHERE EXISTS(SELECT student_id FROM result r WHERE course_id=2 and r.student_id=u.id)

--交叉连接
SELECT * FROM userinfo u,result r;
SELECT*FROM userinfo u cross join result r;
SELECT *FROM userinfo u,result r WHERE u.id=r.student_id;

--外连接
SELECT *FROM userinfo u LEFT OUTER JOIN result r on u.id-r.student_id;
SELECT*FROM userinfo u LEFT join result r on u.id=r.student_id;--等效于上句
SELECT *FROM userinfo u RIGHT OUTER JOIN result r on u.id-r.student_id;
SELECT*FROM userinfo u RIGHT join result r on u.id=r.student_id;--等效于上句
--内连接
SELECT * FROM userinfo u inner join result r on u.id=r.stufent_id;
SELECT * FROM userinfo u join result r on u.id=r.stufent_id;
--自连接
SELECT*FROM userinfo u WHERE role=(SELECT role from userinfo WHERE name ='admin')

--判断语句
SELECT*,IFNULL(phone,0) FROM userinfo;

SELECT *,role,
CASE role WHEN 1 THEN '管理员'
					WHEN 2 THEN '领导'
					WHEN 3 THEN '用户'
					WHEN NULL THEN '无'
	END roleName
	from userinfo;
	SELECT *,role,
	
	SELECT *,role,
CASE IFNULL(role,0)  WHEN 1 THEN '管理员'
					WHEN 2 THEN '领导'
					WHEN 3 THEN '用户'
					WHEN 0 THEN '无'
	END roleName
	from userinfo;
	
	SELECT *,role,
CASE  WHEN role=1 THEN '管理员'
					WHEN role=2 THEN '领导'
					WHEN role=3 THEN '用户'
					WHEN role is NULL THEN '无'
	END roleName
	from userinfo;










- 视图(创建create,修改alter)
CREATE VIEW ur AS
  SELECT t.id,t.name,COUNT(id),GROUP_CONCAT(rname) rnames from(
  SELECT u.*,r.* FROM userinfo u LEFT JOIN userrole ur ON u.id= ur.u_id LEFT JOIN role r on r.rid=ur.r_id
) t GROUP BY t.id;

-- 查看视图
SELECT * FROM ur;
-- 删除视图
DROP VIEW ur;

-- 存储过程
-- 创建存储过程

CREATE PROCEDURE myproc()

BEGIN
UPDATE userinfo set rol=2 WHERE id=1;
UPDATE result SET score=100 WHERE id=1;
END

-- 更改存储过程
-- 事务（TRANSACTION）:是作为单个逻辑工作单元执行的一系列操作
DROP PROCEDURE if EXISTS myproc;

CREATE PROCEDURE myproc()
BEGIN
DECLARE t_error INTEGER DEFAULT 0;
DELETE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;

START TRANSACTION;-- 开始事务
UPDATE userinfo SET role=20 WHERE id=1;
UPDATE result SET role=20 WHERE id=1;
IF t_error=1 THEN
ROLLBACK;-- 异常回滚事务
ELSE
COMMIT;-- 否则提交事务
END if;
END
CALL myproc();-- 执行存储过程
CREATE PROCEDURE myproc_(_id int ,_role int)
BEGIN 
SELECT * rrom userinfo WHERE id=_id AND role=_role;
END
CALL myproc(1,1);-- 执行存储过程


-- in模式参数:输入模式的参数，用于接收参数，在子程序内部，不能为外部赋值。默认的参数模式：in。
-- 测试in模式的存储过程
create PROCEDURE myProc_1(in p1 int,in p2 int)
BEGIN
		select p1,p2;
		set p2:=11;	-- in模式参数不能为外部赋值
		select p1,p2;
end

set @var1=1;
set @var2=2;
select concat("调用前：",@var2); -- concat拼接字符串
call myProc_1(@var1,@var2);-- 执行带参存储过程
select concat("调用后：",@var2);

-- out模式参数:输出模式的参数，用于输出值，会忽略传入的值。在子程序内部可以对其进行修改。 
-- 输出：子程序执行完毕后，out模式参数最终的值会赋值给调用时对应的<实参变量>
-- 测试out模式的存储过程
CREATE PROCEDURE myProc_2(OUT p3 INT)
BEGIN
  select p3;-- p3会忽略传入的值
  set p3:=33;-- 设定存储过程调用后的值
  select p3;-- p3的值
END;

set @var3=30;-- 声明一个变量用于设定存储过程调用前的值
-- call myProc_2(20); -- error,20对应过程中out模式的参数，out会输出结果给调用的实参，但是20不能作为赋值目标
select CONCAT('存储过程调用前的值：',@var3); -- CONCAT()拼接字符串
call myProc_2(@var3);-- 调用pro存储过程重新赋值；调用过程，如果过程形参是out模式，必须采用变量实参
select CONCAT('存储过程调用后的值：',@var3);

-- 测试inout模式的存储过程
CREATE PROCEDURE myProc_3(inout p4 INT)
BEGIN
  select p4;
  set p4:=44;-- inout模式参数的值可以修改
  select p4;
END;

set @var4=40;-- 声明一个变量用于设定存储过程调用前的值
select CONCAT('存储过程调用前的值：',@var4);
call myProc_3(@var4);-- 调用pro存储过程重新赋值
select CONCAT('存储过程调用后的值：',@var4);



-- 索引
-- 创建索引
-- CREATE INDEX 索引名 ON 表名（列名）;
-- CREATE INDEX 索引名 ON 表名（列名1,列名2）;--联合（组合）索引
CREATE INDEX u_name_index on userinfo(name);
CREATE UNIQUE INDEX u_name_index on userinfo(name);
-- 删除索引
DROP INDEX u_name_index on userinfo;

-- 查看表的所有索引
SHOW INDEX FROM userinfo;



-- 触发器
-- 添加触发器
CREATE TRIGGER inseruser
AFTER INSERT on userinfo
FOR EACH ROW
BEGIN
-- 像另一个表添加一条数据
INSERT INTO userrole (u_id,r_id) VALUES(new.id,3);
end;
-- 删除时的触发器
CREATE TRIGGER DELETEuser
BEFORE DELETE on userinfo
FOR EACH ROW
BEGIN
-- 删除关联表相关数据
DELETE FROM userrole WHERE u_id=old_id;
end;
-- 清除触发器
DROP TRIGGER DELETEuser;


-- 带参存储过程
CREATE PROCEDURE myproc_(_id int ,_role int )
BEGIN
 SELECT * FROM userinfo WHERE id=_id AND role=_role;
END
CALL myProc_(1,1)


-- 游标
CREATE PROCEDURE new_proc()
BEGIN 
-- 定义变量（接收游标数据）
DECLARE id int;
-- 定义一个标识
DECLARE done INT DEFAULT FALSE;
-- 定义游标
DECLARE mycur CURSOR for SELECT u.id from userinfo u;
-- 绑定结束标识
DECLARE CONTINUE HANDLER FOR not FOUND SET done=TRUE;
-- 打开游标
	OPEN mycur;
	read_loop: LOOP -- 循环开始
		-- 提取游标里面数据
		FETCH mycur into id;
		-- 判断结束
		IF done THEN
				LEAVE read_loop;
		end if;
		-- 循环操作
		INSERT INTO userrole(u_id) VALUES(id);
		END LOOP;-- 循环结束
	-- 关闭游标
	CLOSE mycur;
END


call new_proc();


create table student(
    stuId int primary key auto_increment,
    stuName varchar(20),
    stuSex varchar(2),
    stuAge int
)default charset=utf8;
​
insert into student(stuName,stuSex,stuAge) values
('小明','男',20),
('小花','女',19),
('大赤','男',20),
('可乐','男',19),
('莹莹','女',19);​




delimiter //
create procedure p1()
begin
    declare id int;
    declare name varchar(100) character set utf8;
    declare done int default 0;
    -- 声明游标
    declare mc cursor for select stuId,stuName from student where stuAge >19;
    declare continue handler for not found set done = 1;
    -- 打开游标
    open mc;
    -- 获取结果
    fetch mc into id,name;
    -- 这里是为了显示获取结果
    select id,name;
    -- 关闭游标
    close mc;
end //
delimiter ;


-- 定义语法结束符号
delimiter //
-- 创建一个 名称为 p2 的存储过程
create procedure p2()
begin
    -- 创建 用于接收游标值的变量
    declare id,age,total int;
    -- 注意 接收游标值为中文时 需要 给变量 指定 字符集为utf8
    declare name,sex varchar(20) character set utf8;
    
    -- 游标结束的标志
    declare done int default 0;
    -- 声明游标
    declare cur cursor for select stuId,stuName,stuSex,stuAge from student where stuAge > 19;
    -- 指定游标循环结束时的返回值 
    declare continue handler for not found set done =1;
    -- 打开游标
    open cur;
    
    -- 初始化 变量
    set total = 0;
    
    -- loop 循环
    xxx:loop
        -- 根据游标当前指向的一条数据  
        fetch cur into id,name,sex,age;
        -- 当 游标的返回值为 1 时 退出 loop循环 
        if done = 1 then
            leave xxx;
        end if;
        -- 累计
        set total = total + 1;
    end loop;
    -- 关闭游标
    close cur;
    -- 输出 累计的结果
    select total;
end //
delimiter ;



delimiter //
-- 创建一个 名称为 p3 的存储过程
create procedure p3()
begin
    -- 创建 用于接收游标值的变量
    declare id,age,total int;
    -- 注意 接收游标值为中文时 需要 给变量 指定 字符集为utf8
    declare name,sex varchar(20) character set utf8;
    -- 游标结束的标志
    declare done int default 0;
    -- 声明游标
    declare cur cursor for select stuId,stuName,stuSex,stuAge from student where stuAge > 19;
    -- 指定游标循环结束时的返回值 
    declare continue handler for not found set done = 1;
    -- 打开游标
    open cur;
    
    -- 初始化 变量
    set total = 0;
    
    -- while 循环
    while done != 1 do
        fetch cur into id,name,sex,age;
          if done != 1 then
             set total = total + 1;
          end if;    
    end while;
    -- 关闭游标
    close cur;
    -- 输出 累计的结果
    select total;
end //
delimiter ;





delimiter //
-- 创建一个 名称为 p3 的存储过程
create procedure p3()
begin
    -- 创建 用于接收游标值的变量
    declare id,age,total int;
    -- 注意 接收游标值为中文时 需要 给变量 指定 字符集为utf8
    declare name,sex varchar(20) character set utf8;
    -- 游标结束的标志
    declare done int default 0;
    -- 声明游标
    declare cur cursor for select stuId,stuName,stuSex,stuAge from student where stuAge > 19;
    -- 指定游标循环结束时的返回值 
    declare continue handler for not found set done = 1;
    -- 打开游标
    open cur;
    
    -- 初始化 变量
    set total = 0;
    
    -- repeat 循环
    repeat
        fetch cur into id,name,sex,age;
           if done != 1 then
              set total = total + 1;
           end if;
        until done = 1
    end repeat;
    -- 关闭游标
    close cur;
    -- 输出 累计的结果
    select total;
end //
delimiter ;