数据库学习笔记

数据库笔记（MYSQL）

打开数据库
use test
show databases;
显示数据库中的表
show tables；
删除表；
drop database +表名；
创建一个库
create database +库名
创建一个表
create table +表名
（
id int auto_increment not null,//自增型   必须设成int型
username varchar(10)  not null,
password varchar(10)  not null,
primary key (id）

);
显示表的结构
desc +表名；
插入一个记录
insert into user values(1,"字符串","日期")
注：插入属性值时整形不加（单）引号 字符型日期型加（单）引号；
看表的内容
select * from +表名；
自动增长
id  自动增长
例如
insert into user（username password) values (ming 12345)
其中若有两个字段 则插入的 id 为3；
修改数据库
改字符集
创建表 列的定义
复制表的结构
create table +新表名 like +旧表名;
复制表的结构+内容
create table +新表名 as(select *from 旧表名);
char 定长类型
varchar 变长类型
修改表的结构
ADD 增加列
DROP 删除列
MODIFY 该类型
CHANGE 改列名
RENAME TO new_tab_name 给表起名
ALTER TABLE +表名
例 加一列
alter table +表名+add +属性名（sex char(1) not null default'男‘;
删除
alert table +表名 drop +属性名；
加一列（在确定属性位置的后面）
alter table +表名+add +属性名（sex char(1) not null default'男‘after +属性名;
改列的类型
alter table +表名+modify+属性名(usename char(10) not null;)
改默认值
alter table +表名+alter+属性名(usename set default'mao‘ ;)
插入
insert into +表名（属性名）values(’1000 ‘）; 属性类型为char 应该用单引号引起来
改变列的名
alter table +表名 change +原名+新名 char(10) not null;
该表名
alter table +原名+rename+to+新表名;

insert into xs values('081101','王林','计算机',1,'1994-02-10',50,null,null);
insert into xs values('081100','王林','计算机',1,'1994-02-10',50,null,null);
insert into xs values('081102','张林','计算机',1,'1994-01-10',40,null,null);
空值代表不确定；
长文本是text 型
blob 是二进制类型；
insert into xs values('081104','王-林','计算机',1,'1995-02-10',47,null,null),('081105','张-林','计算机',1,'1993-01-10',40,null,null);
替换旧记录
replace into xs values('081104','王-林','计算机',1,'1995-02-10',47,null,null),('081105','张-林','计算机',1,'1993-01-10',40,null,null);
照片存的是一个路径
修改单个表
修改记录
update xs
set 总学分=总学分+10;
update xs
set 性别=0 where 姓名='张一林';
修改多个表
update 
show databases;
select *from +库名+表名；
删除记录
use +表名
delete from +表名  //清空表 表的结构在

use +表名
delete from +表名
where +条件

从多个表中删除
P69
清除表 
truncate table +表名；//清空表的数据
数据库信息显示
show tables ；
show databases；
show index from 表名；索引
show variables  显示变量的名
show processlist; 显示系统的进程
show privileges;显示用户的权限
第四章 MYSQL 查询和视图
最常用的操作 查询
P71 作业题 1 2 3
视图是从基础表中导出的表
视图的应用是为了安全
关系的运算 选择 满足条件的新表作为运算结果（临时表）行运算
投影  从表中选择指定的属性值  列运算 形成一张新表（临时表）
连接 
等值连接 相等的值
自然连接 两张表中有相同的属性
         把重复的属性去掉
自然连接一定是等值连接
select * from xs group by 性别；
delete from xs1;//删除数据 结构还在
drop table xs1;//结构什么的全都删了

select * from xs,kc,xs_kc;
select * from xs,kc,xs_kc where xs.学号=xs_kc.学号 and kc.课程号=xs_kc.课程号;

投影是列运算 选择是行运算

select 学分 from xs_kc where 课程号=101;
select 学分+100 from xs_kc where 课程号=101;

select count(*) from xs_kc;//统计
select count(*) from xs_kc where 课程号=101;//统计符合条件的

select * from xs group by 性别;

select 姓名,专业名,总学分 from xs;
select 学号 as number,姓名 as name,总学分 as mark from xs
where 专业名='计算机';//显示别名的有选择的投影
select 学号,姓名,
case
when 总学分 is null then '尚未选课'
when 总学分<50 then'不及格'
when 总学分>=50 and 总学分<=52 then'合格'
else'优秀'
end as 等级 from xs where 专业名='计算机';

select *from xs,xs;
select 学号 as number;
select 学号,姓名,
 case p78
计算列值
select +表达式 from +表名 where +条件；
select 学号,课号,成绩*1.20 as 成绩 120
from xs_kc
where 学号='081101';
select distinct 专业名,总学分
from xs;
count 函数
select count(*) as 学生总数 from xs;*代表所有字段
select count(备注) as 备注不为空 from xs;
select count(学分) as '总学分人数' from xs_kc where 学分>50;
求最高分 最低分
select max(成绩),min(成绩) from xs_kc
 where 课程号='101';
select sum(成绩) as '课程总成绩'
from xs_kc where 学号='081101';
select avg(成绩) as'课程101平均成绩'
from xs_kc;
select variance(成绩)
from xs_kc
where 课程号='101';
select 学号
from xs_kc
where 课程号='206';
select group_concat(学号)
    from xs_kc
    where 课程号='206';
select *from xs as student;
全连接 将各个表用逗号分隔
（笛卡尔积）
select distinct KC.课程名,xs_kc.课程号
from KC,xs_kc where KC.课程号=xs_kc.课程号;
先做笛卡尔积在执行where;
JOIN 连接
1）内连接 inner 关键字
先条件在笛卡尔积 on+条件
select *from kc,xs_kc
where kc.课程号=xs_kc.课程号;
select *from kc join xs_kc;
select *from kc join xs_kc
on kc.课程号=xs_kc.课程号;
先作筛选 后作笛卡尔积;
select xs.成绩,姓名,课程名,成绩
from xs join xs_kc on xs.学号=xs_kc.学号
join kc on xs_kc as 
外连接   左连接
select * from kc left join xs_kc on kc.课程号=xs_kc.课程号;
自然连接是特殊的等值连接 共同的属性及属性名 并可以把相同的属性名留一列

select xs_kc.*,课程名 from xs_kc right join kc on kc.课程号=xs_kc.课程号;
select 课程号,课程名 from kc
where 课程号 in
(select 课程号 from xs_kc);
select 课程号,课程名 from kc
where 课程号 in
(select distinct 课程号 from kc natural right outer join xs_kc);//子查询
自然连接没有连接条件
select *from kc natural join xs_kc; 相同的属性会只剩下一个
交叉连接 cross join
无条件 等同于笛卡尔积 有条件相当于inner join
where 子句 判定运算
模糊查询 like 先from 在where
指定范围 between   and
<=>相等或等于空 空怎么表达 is null
exist 
select 姓名,学号 from xs 
where 备注<=>null;
select 姓名,性别 from xs
where 专业名='计算机' and 性别=0;
select * from xs
where 姓名 like '王%';//字符串匹配
select * from xs
where 姓名 like '李_';//字符串匹配
%代表任意个字符
_代表单个字符
转义符 esc 特殊的字符
select * from xs
where 学号 like '%0_';
regexp 用来执行更复杂的字符比较运算
select * from xs
where 姓名 regexp '^李';
select * from xs
where 学号 regexp '[4,5,6]';//字符串一定要引起来；
select * from xs
where 学号 regexp '^08.*08$';
08开头08结尾
select * from xs
where 学号 regexp '^08...*08$';
select * from xs
where 学号 regexp '^08...08$';
.*表示一组字符
范围比较 between in
in 接值表
select * from xs
where 出生日期 not between '1993_01_01' and '1993_12_31';
select * from xs
where 专业名 in('计算机','通信工程','无线电')
5.子查询
in 子查询
查询xs表中名字包含下划线的学生信息
select * from xs
where 学号 like '%#_%' escape '#';//#表示转义字符
select 姓名,学号 from xs
where 学号 in(select 学号 from xs_kc
where 课程号='206');
查找未选修离散数学的学生信息
srlect * from xs where 学号 not in
(select 学号

2 比较子查询
有比较运算符 有两个select
all 
any
3 exists 子查询
select 学号,姓名
from xs 
where(性别，总学分)=(select 性别,总学分
from xs where 学号='081101
77 4.6 79 4.8
89 4.37 93 4.45
4.40 4.41 
96 4.54 97 4.57 98 4.60
100 4.64 101 4.67  102 104 简答题 108 4.74
4.76 117 5.5 120 5.9
select 专业名 
from xs
group by 专业名;
求 库里各专业的学生数
select 专业名,性别,count(*)as'学生数'
from xs
group by 专业名,性别
with rollup;//汇总行
group by 是为了和函数搭配的
select 课程名,专业名,avg(成绩)as 
having 后可代聚合函数 where 后不能加聚合函数
99 4.6.1
select 
handler xs open
handler xs read first where 总学分>4;
handler xs read next;
handler xs close;
视图是一个导出表 是从基本表中的导出表
引入视图的好处
1.为用户集中数据
2.屏蔽数据库的复杂性
3.简化用户权限
4.便于数据共享
5.可以重新组织数据以便输出到其他应用中
create view xscj.cs_kc
as select xs.学号,课程号,成绩
from xscj.xs,xscj.xs_kc
where xs.学号=xs_kc.学号 and xs.专业名='计算机'
with check option;//规则一致
use xscj
create view cs_kc_avg(num,score_avg)
as
select 学号,avg(成绩)
from cs_kc
group by 学号;
select *
from cs_kc_avg
where score_avg>=80;
insert into cs_kc values('081255','李牧','计算机',1'1994-10-14',50,null,null);
update cs_xs
set 总学分=总学分+8;//只能改一个表
delete from cs_xs
where 学号='081255';
drop view +视图名;
索引的作用
快速读取数据
保证数据的唯一性
实现表与表之间的参照完整性
减少排序和分组时间
浪费时间空间
建立索引
create index xh_xs
on xs(学号(5)asc);
show index from xs;
alter
127 1 2 5
唯一性索引是候选键
数据库的对象 表,默认约束 规则 视图 触发器 存储过程
表和视图的区别联系
create  创建库和表
alter 修改表
drop 删除表库
delete 删除记录
select null<>null //出现的是null
set @ x=2  //创建用户变量 临时存放一个数
set @ 变量名=一个值;
set @x=@x+1//注意有一个@
set @name='王林';
set @student=(select 姓名 from xs where 学号='081101');
系统变量
show variables;//得到系统变量名单
select '2017-12-06'+ INTERVAL 22 DAY;
select dayofweek('2017-12-06');

delimiter $$
create procedure delete_student7(in xh char(6))
begin
delete from xs where 学号=xh;
end $$
delimiter ;

call delete_student7('081101');
select * from xs;
delimiter //
delimiter ;
存储过程的优点
存储过程在服务器端进行，执行速度快
存储过程执行一次后，其执行规划就驻留在高速缓冲存储器中提高了系统性能
确保数据库安全
定义局部变量
declare +名+类型+默认值;
select +列名 into +变量名
7.6 175
delimiter $$
create procedure xscj.compar
(in k1 integer, in k2 integer, out k3 char(6) ）
begin 
if k1>k2 then
  set k3='大于';
elseif k1=k2 then
 set k3='等于';
else 
  set k3='小于';
end if;
end&&
delimiter ;
call xscj.comper(6,10,@k3);
select @k3;
7.7
7.9的局部变量变成用户变量 才能看到v1
处理程序和条件
处理程序的类型
continue exit
游标相当于一个指针 指向当前的一行数据
游标的四种语句
声明 打开 读取（移动游标）关闭 
定义游标
declare 游标名 cursor for 
打开游标 open 游标名 
读取游标 fetch 游标名 into 变量名
关闭游标 close 游标名
7.14 重点
delimiter $$
create procedure compure (out number integer)
begin
 declare xh char(6);
 declare found boolean default true;
 declare number_xs cursor for
 select 学号 from xs;
declare continue handler for not found 
  set found=false;
 set number=0;
 open number_xs;
 fetch number_xs into xh;
while found do
 set number=number+1;
fetch number_xs into xh;
end while;
close number_xs;
end$$
delimiter ;
过程和存储函数的区别
存储函数不能拥有输出参数 
不能用call来调用存储函数  select 调用函数
存储函数必须包含一条return语句 不允许出现在存储过程中

delimiter $$
create trigger xs_delete after delete
on xs for each row
begin
delete from xs_kc where 学号=old.学号;
end$$
delimiter ;
7.30
delimiter $$
create trigger xs_kc_update before update
on xs_kc for each row
begin
declare xf int(1);
select 学分 into xf from kc where 课程号=new.课程号;
if new.成绩<60 then
set new.学分=0;
else
set new.学分=xf;
end if;
end$$
delimiter ;
insert into xs_kc values('081408','001',90,3),('081408','002',80,3),('081408','003',85,3);
insert into xs values('081408','王林','计算机',1,'1990-02-10',50,null,null);