本文共 16257 字,大约阅读时间需要 54 分钟。
存储数据的方法
大脑
来记住数据纸
上计算机的内存
中磁盘
文件存储大量数据,方便检索和访问
保持数据信息的一致、完整
共享和安全
通过组合分析,产生新的有用信息
应用程序
响应操作并显示结果、向数据库请求数据
美观、操作简单方便
数据库
存储数据、检索数据、生成新的数据
统一、安全、性能
等Oracle
SQL Server
易用性好
网站应用广泛
非关系型
数据库客观存在的、可以被描述的事物都是“实体”
格式相同的实体
表、关系以及操作对象
组成数据存放在表中
需求分析
阶段:分析客户的业务和数据处理需求
概要设计
阶段:设计数据库的E-R模型图,确认需求信息的正确和完整
详细设计
阶段:应用三大范式审核数据库结构
代码编写
阶段:物理实现数据库,编码实现应用
软件测试
阶段:……安装部署
:……数据冗余、存储空间浪费
内存空间浪费
数据更新和插入的异常
收集信息
标识实体 (Entity)
标识每个实体的属性(Attribute)
标识实体之间的关系(Relationship)
映射基数
主键列
主外键
,体现实体之间的映射关系
不合规范的表设计
信息重复
更新异常
插入异常
无法正确表示信息
删除异常
丢失有效信息
确保每列的原子性
不可再分的最小数据单元
(也称为最小的原子单元
),则满足第一范式(1NF)每个表只描述一件事情
除了主键以外的其他列都不传递依赖于主键列
,则满足第三范式(3NF) (非主键字段之间也存在依赖关系
)一张表描述了多件事情:
应用第二范式规范化:
应用第三范式规范化:
数据库性能比规范化数据库更重要
在给定的表中添加额外的字段
,以大量减少需要从中搜索信息所需的时间
在给定的表中插入计算列
(如成绩总分),以方便查询
综合考虑数据库的性能
实体完整性
引用完整性
域完整性
标识列和主键
域完整性和域约束
登录mysql服务器
:mysql -u root -p
查看
当前服务器上的所有数据库信息
:show databases;
创建数据库
:create database 库名;
打开数据库
:use 库名;
查看
数据库中所有数据表
: show tables;
创建数据表
: CREATE TABLE table_name (column_name column_type);
导入
数据文件: mysql -u用户名 -p密码 < 要导入的数据库数据(runoob.sql)
查看
数据: select * from 表;
备份
: mysqldump -h主机名 -P端口 -u用户名 -p密码 –database 数据库名 > 文件名.sql
还原
: mysql -hhostname -uusername -ppassword databasename < backupfile.sql
quit
结构化查询语言
(Structured Query Language)简称SQL,是一种特殊目的的编程语言
DDL:数据定义语言
DQL:数据查询语言
DML:数据操作语言
获取所有表中的数据
select 字段名 [*] from 表名
select empId,eName,....,deptId from emp;
举例
## 查询每个员工的编号、姓名select empId,eName from emp;## 查询员工的职位select job from emp;
重复数据的处理方式
select distinct 列名 from 表名
distinct 去重
举例
## 去重查询select distinct job from emp;
查询语句支持数据的运算
举例
## 查询员工姓名、职位和年收入。年收入等于年薪加上每月100元伙食补贴。select eName 姓名, job 职位, (salary+100)*12 年收入 from emp;
数据的组合查询方式
concat函数用于把多个数据组合在一起输出显示
语法
concat(参数1,参数2,参数3......)
select concat(empId,eName) from emp;
举例
## 完成以下显示的查询结果, 年收入按年薪加上每月100元伙食补贴和每年2000元的车费补贴。select concat('编号:',empId,'姓名:',eName) 基本信息, (salary+100)*12+2000 年收入 from emp;
Select 列名 from 表名 where 条件集合
条件集合
包含 and,or,not
和 > ,< ,=,<>,between and
等组合符号为空判断
采用 is null 和 is not null , not …. Is null
范围查询
采用 in,not in
模糊查询
采用 like
,匹配单个字符’_’
,匹配任意多个字符’%’
## 查询月工资大于 1500 元的员工信息select * from emp where salary > 1500;## 查询职位是销售员的员工信息select * from emp where job = '销售员';## 查询月工资在 1500 和 3000 之间的员工信息select * from emp where salary > 1500 and salary < 3000;select * from emp where salary between 1500 and 3000 (between...and...包含 1500 和 3000)## 查询月工资在 1500 和 3000 之间,并且职位是经理的员工信息select * from emp where salary > 1500 and salary < 3000 and job = '经理';## 查询职位是经理或者分析师,并且月薪大于 2800 的员工信息,注意优先级问题select * from emp where (job = '经理' or '分析师') and salary > 2800;## in 范围查询,相当于 or 运算select * from emp where job in ('经理','分析师') and salary > 2800## 查询入职时间在 1981 年的员工信息select * from emp where hireDate > '1980-12-31' and hireDate < '1982-1-1';## 查询所有姓名中包含’云’的员工信息select * from emp where eName like '%云%'## 查询没有奖金的员工信息,为空比较不能用等于select * from emp where comm is null## 查询奖金不为空的员工信息select * from emp where comm is not nullselect * from emp where not comm is null
Order by 列名集合 asc(desc)
## 查询员工信息,以工资从高到低排列;工资相同时,以入职日期从早到晚排列显示select * from emp order by salary,hireDate## 查询员工信息,以工资从高到低排列;工资相同时,以入职日期从早到晚排列显示select * from emp order by salary desc,hireDate desc (倒序)
count:统计个数
sum
:获取指定列的数据总和
avg
:获取指定列的数据平均值
max
:获取指定列的数据最大值
min
:获取指定列的数据最小值
## 查询显示公司的全部员工数,每月的工资总额以及平均工资select count(empId) 总人数, sum(empId) 月工资总金额, avg(salary) 平均工资, max(salary) 最高薪资, min(salary) 最低薪资 from emp;## 显示员工中最早入职的日期和最晚入职的日期select min(hireDate) 最早入职日期, max(hireDate) 最晚入职日期 from emp;## 查询当前拿佣金的人员总数select count(empId) from emp where comm is not null;select count(comm) from emp (count函数会过滤掉null值的记录)## 查询公司职位的总数select count(distinct job) from emp; (去重+统计)
使用场合
重复的数据
,分组才有意义分组函数经常和统计函数一起使用
Select
字段集合from
表集合where
条件集合(where不能使用统计函数进行条件筛选
) Group by
字段集合having
条件集合(having是在分组统计基础上进行筛选
)Order by
字段集合## 按部门编号分组,显示员工总数和平均工资select count(empId) 部门员工总数, avg(salary) 部门平均工资 from emp group by deptIdselect * from emp group by depId; 只适用于MySQL,其他数据库中会显示语法错误## 按职位分组,求出每个职位的最高工资和最低工资select job 职位, max(salary) 最高工资, min(salary) 最低工资 from emp GROUP BY job;## 按职位分组,求出每个职位的员工人数,和工资总和,同时工资总和超过5000select job, count(empId), sum(salary) from emp group by job having sum(salary) > 5000
注意事项
单独使用
,或者组合使用
,但是不要和普通列一起使用
当存在分组函数时,统计函数和普通列可以一起使用
分组函数存在时,select子句中只能出现分组的字段和统计函数
分组函数允许多列
## 不合适语句## 1. 普通列和统计函数一起使用select eName, count(empId) from emp; (x)## 2. 有分组的情况下,普通列中出现非分组字段select eName, job, max(salary), min(salary) from emp group by job (x)
表连接
子查询
From子句中放置多张数据表实现多表查询
Select * From 表1,表2,….表n
多表查询的笛卡儿积
Select * from emp,dept
去除笛卡儿积的办法 —— 关联查询
Select * from emp,dept where emp.deptno = dept.deptno
表名前缀
给数据表取别名
Select * from emp e,dept d where e.deptId = d.deptId
举例
## 查询显示每个员工的编号、姓名、工资、工资级别select empId, eName, salary, gradeId from emp e, salgrade s where e.salary between s.losal and s.hisal
语法
Select * From 表1 join 表2 on 表1.字段 = 表2.字段
表连接的类型
内连接
左外连接
右外连接
表外连接的使用场合
数据关联时,匹配不上造成的数据缺失
举例
## 内连接select empId, eName, d.deptId, dName, location from emp e join dept d on e.deptId = d.deptId## 外连接(左外连接 -- 当数据不匹配的时候,确保 join 左边的表显示所有数据,右边的表的数据不匹配用null代替)select empId, eName, d.deptId, dName, location from emp e left join dept d on e.deptId = d.deptId## 外连接(右外连接 -- 当数据不匹配的时候,确保 join 右边的表显示所有数据,左边的表的数据不匹配用null代替)select empId, eName, d.deptId, dName, location from emp e right join dept d on e.deptId = d.deptId
特殊的多表查询——自身关联查询
## 显示员工的姓名、编号、职位和领导的编号和姓名## 内连接select e2.empId, e2.eName, e2.job, e2.leader, e1.eName from emp e1,emp e2 where e1.emp e2 where e1.empId = e2.leader## 外连接select e2.empId, e2.eName, e2.job, e2.leader, e1.eName from emp e1 right join emp e2 on e1.empId = e2.leader## 查找工资比张三高的所有员工信息select * from emp e1 join emp e2 on e1.salary > e2.salary where e2.eName = '张金山';
子查询 = 简单查询 + 限定查询 + 多表查询 + 统计查询
Select 字段集合, [ ( 子查询语句 ) ]
From 表名, [ ( 子查询语句 ) 表别名 ]
Where 条件 [ ( 子查询语句 ) ]
from
和where
子句中非相关子查询
只执行一次
,把查询结果提交给外部查询
,效率较高
相关子查询
执行依赖于外部查询数据
,外部执行一次
,子查询就执行一次
## 查询薪资超出相同部门员工平均薪资的员工信息## 相关子查询select * from emp e1 where salary > ( select avg(salary) from emp e2 where e1.deptId = e2.deptId)## 表连接select * from emp join (select deptId,avg(salary) deptAvg from emp group by deptId) temp on emp.deptId = temp.deptId where salary > deptAvg
Where 子查询
返回单行单列,与比较运算符联合使用
From 子查询
返回多行多列,类似于数据表
## 查询在C区工作的所有员工信息select e.* from emp e join dept d on e.deptId = d.deptId where location = 'C区'; -- 表连接select * from emp where deptId = ( select deptId from dept where location = 'C区' -- 子查询)## 查询与张金山相同职位的所有员工信息select * from emp where job = ( select job from emp where eName = '张金山')## 查询工资高于全体员工平均工资的员工信息select * from emp where salary > ( select avg(salary) from emp)
减少查询的次数
## 查询每个部门编号、名称、所在位置,人数和平均工资select d.deptId,dName,location,count(empId),avg(salary) from emp e right join dept d on e.deptId = d.deptId group by e.deptId## 优化select * from dept left join ( select deptId, count(empId), avg(salary) from emp group By depId) as temp on dept.deptId = temp.deptId
分类查询
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 。。。。。。。 ELSE 其他结果 END
## 分类查询select empId 工号, eName 姓名, salary 月薪, case when salary < 1000 then '屌丝' when salary between 1000 and 1500 then '草根' when salary between 1501 and 3000 then '白领' else '土豪' end 评价 from emp
## 查询员工奖金是 300,500或者为空的员工信息select * from emp where comm = 300 or comm = 500 or comm is nullselect * from emp where comm in (300,500) or comm is null## 查询显示所有领取奖金的员工人数和不领取奖金的员工人数select 10 年龄,'男' 性别select count(empId) 领取奖金的人数 from emp where comm is not null;select count(empId) 不领取奖金的人数 from emp where comm is null;select (select count(empId) from emp where comm is not null) 领取奖金的人数,(select count(empId) from emp where comm is null) 不领取奖金的人数## 查询出工资中百位上是5的员工姓名select * from emp where salary like '%5__' -- 利用模糊查询select * from emp where salary / 100 MOD(10) = 5 -- 利用系统函数## 获取所有职位的平均工资的最高值select max(avgSal) from (select job,avg(salary) avgSal from emp group by job) tempselect * from (select job,avg(salary) avgSal from emp group by job order by avg(salary) desc temp limit 1## 查询出每个部门的名称,部门人数1以及部门平均工资select deptId,count(empId),avg(salary) from emp group by deptIdselect dName,temp.* from dept d left join (select deptId,count(empId),avg(salary) from emp group by deptId) temp on d.deptId = temp.deptId## 在上题基础上,再显示部门编号和所在位置,并且部门平均工资高于2000select * from dept d join (select deptId,count(empId),avg(salary) from emp group by deptId having avg(salary) > 2000) temp on d.deptId = temp.deptId## 显示非销售人员的职位名称,以及从事同一职位工作的员工工资总和,且该总和必须大于8000元,输出结果按总和升序排列select job,sum(salary) from emp where job not in ('销售员','销售') group by job having sum(salary) > 4000 order by sum(salary)## 按工资级别分组,显示级别名称和每级的员工总数和该级别员工工资总和select gradeId,count(empId),sum(salary) from emp e join salgrade s on e.salary between s.loSal and s.hisal group by gradeId## 列举出工资高于30部门所有员工工资的员工姓名、部门,所在地和部门平均工资select * from ( select temp.*,dept.location,dept.dName from dept,( select * from emp where salary >( select max(salary) from emp where emp.deptId = 30 ) ) temp where dept.deptId = temp.deptId) a,( select emp.deptId,avg(salary) from emp group by emp.deptId) b where a.deptId = b.deptId## 列举出目前尚无员工的部门编号、名称和位置select * from dept where deptId not in ( select distinct deptId from emp)## 计划给任职超过35年或者入职在8月份的员工加薪,10部门加10%,20部门加15%,30部门加20%,40部门加30%,显示出员工姓名,部门名称,和加薪前后的工资select eName,deptId,salary 加薪前,case when deptId = 10 then salary * 1.1 when deptId = 20 then salary * 1.15 when deptId = 30 then salary * 1.2 else salary * 1.3end 加薪后from emp where year(now()) - year(hireDate) > 35 or month(hireDate) = 8
对表中的记录进行插入、更新和删除的操作
数据插入:Insert
数据删除:Delete
数据更新:Update
插入完整记录
insert into 表名 values(value1,value2…)
插入部分数据
insert into 表名(字段名1,字段名2…) values(value1,value2…)
多行插入:插入一个集合
insert into 表名 子查询语句
## 完整插入语句insert into dept values(50,'法律部','10楼')## 插入部分语句insert into dept(deptId,location) values(60,'18楼')// insert into dept(deptId,dName) values(70,'发展部') -- 注意不可为空的约束## 关于自动增长列的数据插入create table TestTB( uId int auto_increment primary key, uName varchar(10) not null, uPwd varchar(10) not null)## mysql 自动增长列的添加可以接受手动输入insert into TestTB values(200,'zs','666')insert into TestTB values(uName,uPwd) values('Mike','999')## 多行插入create table temp( id int not null, pName varchar(20) not null, pLocation varchar(20) not null)insert into tempselect * from dept where deptId in ( select distinct deptId from emp)
delete
命令来删除表中的记录 delete from 表名 [where 条件]
from 指定要删除的表名
where 指定要删除的记录应满足的条件
没有 where 子句,则删除全部记录
## 删除 temp 的数据delete from temp where id = 1101
update
语句来修改表中数据
update 表名 set 字段名1 = 新的字段值 [,字段名2=新的字段值…] [WHERE 条件]
set
指定要更新
的字段及其更新后的值
,一次可以更新多个字段
where
子句指定要更新
的记录应满足的条件,如果没有where,则更新全部记录
update stuInfo set stuSex = '女' where stuNo = 's25301'
虚拟表
一张表的部分数据或多张表的综合数据
,其结构和数据是建立在对表的查询基础上
不存放数据
,而是存放在视图所引用的原始表
中同一张原始表,根据不同用户的不同需求,可以创建不同的视图
支持增删改(单表)
,视图可以嵌套
创建视图语法
create view 视图名as 查询语句
通过视图执行查询命令,实现对查询结果的显示
select 字段集合 from 视图名
## 创建视图,显示每个工资级别的员工数量,最高薪资,最低薪资,平均薪资create view v_1as select gradeId,count(empId),max(salary),min(salary),avg(salary) from emp e join salgrade s on e.salary between s.losal and s.hisal group by gradeId## 使用视图select * from v_1========================================================================================## 课堂练习1create view v_stuScore1asselect (select count(1) from stuInfo) 应到人数, (select count(1) from stuMark) 实考人数, (select count(1) from stuInfo) - (select count(1) from stuMark) 缺考人数select * from v_stuScore1========================================================================================## 课堂练习2create view v_stuScore2selectstuName 姓名, s1.stuNo 学号,case when writtenExam is null then '缺考' else writtenExamend 笔试成绩,case when labExam is null then '缺考' else labExamend 机试成绩,case when writtenExam >= 60 and labExam >= 60 then '是' else '否'end 是否通过from stuInfo s1 left join stuMark s2on s1.stuNo = s2.stuNoselect * from v_stuScore2========================================================================================## 课堂练习3create view v_stuScore3asselect (select count(1) from stuInfo) 总人数, (select count(1) from v_stuScore2 where 是否通过='是') 通过人数, concat (round(100 * (select count(1) from v_stuScore2 where 是否通过='是') / (select count(1) from stuInfo)),'%') 通过率select * from v_stuScore3
实现特定功能的sql语句块
执行速度更快
允许模块化程序设计
提高系统安全性
减少网络流通量
create procedure 过程名 [(参数表)]begin 执行的SQL语句块;end;
调用存储过程的语法
call 存储过程名()
## 获取员工表中的记录总数,并显示输出create procedure p_1()begin select count(1) 总人数 from emp;end;## 使用存储过程call p_1()
参数类型
实参
: 在调用时传入的具体参数值
形参
: 在定义语句中定义的参数
参数模式
: 用于控制形参的行为
IN 模式(默认方式)
OUT 模式
输入参数
call 存储过程名(输入参数)
输出参数
call 存储过程名(@变量)
select @变量
## 输入一个员工姓名,查询工资大于该员工的所有员工的信息create procedure p_2(pName varchar(10))begin select * from emp where salary > ( select salary from emp where eName = pName );end;call p_2('张金山')==========================================================================## 实现一个注册用户的功能,要求输入用户名和密码,然后返回该用户自动增长所生成的IDcreate procedure p_3(p_uName varchar(10), p_uPwd varchar(10), out p_uId int)begin // 1. 添加新用户 insert into testtb(uName, uPwd) values(p_uName, p_uPwd); // 2. 返回新用户的id select max(uId) into p_uId from testtb;end;call p_3('张三','996',@newId);select @newId==========================================================================## 同存储过程接受一个关键字,查询名称中包含该关键字的部门的员工总数,部门名称和平均薪资create procedure p_4(keyword varchar(10))beginselect dName, count(1),avg(salary) from emp e join ( select * from dept where dName like concat('%',keyword,'%')) t on e.deptId = t.deptId;end;call p_4('技术')
定义变量来保存临时的数据
变量定义语法
declare 变量名 数据类型 [default 数值]declare stuName varchar(20) default ‘前端开发工程师’
变量赋值语法
set 变量名 = 数值 /*简单赋值*/select 字段 into 变量名 from 表 /*查询赋值*/
逻辑结构
## 顺序结构// 每句语句以`分号结尾`## 条件分支结构if 条件1 then 执行代码块1;elseif 条件2 执行代码块2;else 执行代码块3;end if;## 循环结构while 条件表达式 do 执行代码块;end while;
存储过程中的语法
create procedure p_5(num int)begin declare i int default 0; declare sum int default 0; where i < num do set sum = sum + i; set i = i + 1; end where; select sum '累加结果';end;call p_5(10)
字段的值可以使用分类查询
select salary from emp
分类加分
update emp set salary = case end
## 编写存储过程,输入每页多少条数据和第几页,实现对数据表记录的分页查询
传参
pageIndex:页码
pageSize:每页记录的数量
执行语句的拼接
concat
函数拼接分页参数的设置
select * from 表名 limit start , pagesize// 数据分页select * from emp limit 5 // 显示记录的数量select * from emp limit 0,5 // 第一个参数是开始的索引位置,第二个参数显示记录的数量
执行语句的执行
prepare strsql from @strSqls;execute strsql;deallocate prepare strsql;
事务(Transaction)
是作为单个逻辑工作单元
执行的一系列操作保证一组操作的完整性
,在商业级应用中普遍使用银行转帐案例
帐户A的资金减少,并进行汇率转换
然后帐户B的资金相应增加
ACID 特性
: 原子性(Atomicity)
完整的操作
不可分的(原子的)
一致性(Consistency)
一致状态
隔离性(Isolation)
所有并发事务是彼此隔离
的,这表明事务必须是独立
的,它不应以任何方式依赖于或影响其他事务
永久性(Durability)
对数据库的修改被永久保持
,事务日志能够保持事务的永久性
DECLARE t_error int;DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;START TRANSACTION。。。。。。IF t_error = 1 THEN ROLLBACK;ELSE COMMIT;END IF;
转载地址:http://bcqwi.baihongyu.com/