一、数据库设计
(一)数据库基本概念
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 ;