数据库-数据添加与删除-视图-索引-存储过程
表的数据添加与删除
-- 对表数据的操作称为 数据操纵语言（DML）
-- 1 、添加数据
-- 语法： insert into 表名 (字段1,字段2...) values(值1,值2...)
select * from t_user;
insert into t_user(userid,username,password,email)
values(3,'王麻子','8888','wang@163.com');
-- 当userid是自增的时候 可以使用null填充 字段和值的个数和数据类型要一一对应
insert into t_user(userid,username,password,email,sex,age,money)
values (null,'王小华','123456','wangxiao@163.com',null,20,100.25);
-- 如果需要所有字段都插入 ，那么字段名可以省略
insert into t_user values(null ,'马创','888888','999','男',22,100);
-- 也可以通过子查询添加
insert into 表名(字段1，字段2...) 子查询
-- 将学生表中1班的学生信息 添加到t_user表中
-- 表记录的复制
insert into t_user (userid ,username, sex,age)
select sid ,sname ,ssex ,sage from t_student where sclass =1;
select * from t_student
-- 表的备份 (约束没有备份)
create table t_user_temp as select * from t_user ;
select * from t_user_temp;
-- 删除备份
drop table t_user_temp;
-- 2、 数据的删除 ： 只删除数据，不删除表结构
-- 语法： delete from 表名 where 条件
select * from t_user;
-- 根据条件删除 删除userid = 1231的记录
delete from t_user where userid=1231;
-- 截断 truncate （将表的所有记录截断，不可回滚的 ）
truncate t_user_temp -- 数据不可回滚
delete from t_user_temp -- 在手动提交事务前提下，数据可回滚
select * from t_user_temp;
truncate 与 delete的区别？
1、truncate 只能截取（删除）整张表的数据，并不可回滚。
delete 可以按条件删除，并在手动提交事务的前提下可回滚数据。
2、共同点 都是对表记录的操作。
3、修改数据 ： 可以修改数据表的某些字段的值
-- update 表名 set 字段名1 = 值1 ,字段名2 = 值2 ... where 条件
-- 将性别为空的记录 全部变更为‘男’
update t_user set sex='男' where sex is null;
update t_user set sex='男' where sex='';
-- 更改多个字段
update t_user set password ='888' ,email=concat(username,'@163.com') where password is null ;
select * from t_user ;
-- 删除和修改的子查询 的问题
-- mysq 不支持边查询边删除
create table mytab(
id int primary key ,
username varchar(20),
password varchar(20)
)
insert into mytab values (1,'Tom','888');
insert into mytab values (2,'Jay','999');
insert into mytab values (3,'Tom','888');
insert into mytab values (4,'Jay','000');
insert into mytab values (5,'Scot','000');
insert into mytab values (6,'Tom','888');
insert into mytab values (7,'Jay','000');
insert into mytab values (8,'Jay','000');
select * from mytab;
-- 删除 除了id不相同 其他字段都相同的 用户记录
-- 查询思路 先按照 username 和 password 分组 ， 数量大于1的 就是有重复的
-- 然后在排除这些重复的id 就可以
delete from mytab where username in (
select temp1.username from (
select username from mytab group by username ,password
having count()>1
) as temp1
)
and password in (
select temp2.password from (
select password from mytab group by username ,password
having count()>1
) temp2
)
and id not in (
select temp3.id from (
select id from mytab group by username ,password
having count(*)>1
) temp3
)
-- 删除 除了id不相同，其他都相同的 重复记录。
select id, username ,password ,count(*) from mytab group by username ,password
having count(*)>1
select * from mytab;
myuser
uid 主键自增长
uname 非空
password 非空
email 唯一
sex 默认
phone
birthday 日期
视图
1、为什么需要使用视图
对于复杂的多表sql语句，需要弄清楚它们的关系非常耗时， 此时可以通过一个虚拟的表 将
表的关系查询并返回，其sql语句可以永久保存在数据库中 ，简化数据查询
2、视图的好处：
1)、提高sql 重用
2)、 简化sql语句 ，并复杂的sql逻辑封装成一个虚拟表，这个虚拟表就是视图
3)、保护原始表的数据，对部分不可公开的字段可以隐藏
4)、更改数据格式和显示方式。
select * from t_student;
-- 创建视图可以只显示表的部分字段 ，
3、 视图语法：
create or replace view 视图名 as
sql语句
-- 3.1 创建视图只显示id 姓名 性别 分数
create or replace view v_stuInfo as
select sid as id , sname as name , ssex as sex , sscore as score
from t_student ;
-- 查询视图数据 （和表一样 ，视图名不能和表名重复）
select * from v_stuInfo;
-- 对于逻辑关系较为复杂的sql ，也可以定义一个视图，封装sql
-- 3.2、 定义一个视图， 查询学生的学号，姓名，所属系名称 ，所属系主任。
create view v_stuInfo2 as
select sid ,sname ,dname , mname
from t_student t1 left join t_dept t2
on t1.did = t2.did
left join t_man t3
on t2.mid = t3.mid
-- 查询学生的相关信息 复用sql
select * from v_stuInfo2;
3、视图中的数据能否修改 ，删除呢？
-- 回答： 对于单表视图，或没有聚合函数的视图可以修改，其修改的就是原始表数据
delete from v_stuInfo where id= 1000;
对于多表视图或存在聚合或distinct的视图 ，不能修改，删除，添加
delete from v_stuinfo2 where sid = 1203;
注意视图的数据永远来自最新的原始表的数据。
索引
定义： 为了提高数据库的查询效率，建立的一种特殊数据库结构，
索引建立在字段字段上，相当于一本书的目录， 通过索引列查找记录要比普通类查询块
索引的分类 （Index）
1、普通索引
2、唯一索引
3、全文索引
4、单列索引
5、多列索引
6、空间索引
-- 1、创建普通索引
方式1： 在创建表时，指定某一个列为索引
create table mytab2(
id int ,
name varchar(20) ,
index(id) -- 普通索引
)
-- 可以继续给name加索引 create index 索引名 on 表名(列名)
create index indx_name on mytab2(name);
-- 删除索引
drop index indx_name on mytab2 ;
-- 2 唯一索引 unique 和 全文索引（fulltext）
create table mytab3(
id int primary key , -- 主键默认带有 唯一索引
username varchar(20), -- 唯一索引
mydesc text, -- 全文索引
unique index(username),
FULLTEXT index(mydesc)
)
-- 也可以直接创建唯一索引 和全文索引
create unique index un_username on mytab3(username) ;
create fulltext index un_username on mytab3(username) ;
3、单列索引： 以上3个索引都是单列索引 和
多列索引： 由多个字段组成的索引数据结构 ，
注意： 在一个表中并不是索引越多查询速度越快， 要根据经常查询的字段来确定增加哪一类索引 ，对于经常新增和修改的数据不适合增加多列索引，这样增加维护成本
-- 给 mytab3 的id 和username 增加多列索引
create index my_index on mytab3(id , username);
4、空间索引 spatial 必须满足字段是非空约束，且执行引擎是myiasm
mysql中的执行引擎常用的有两种
1、 myiasm : mysql5.5 以前版本的默认 ，
2、 innodb ：mysql5.5以后的默认引擎
面试题：
myiasm 和 innodb 的区别？
1、支持事务不同 ： myiasm不支持事务， innodb支持事务
2、数据存文件不同： myiasm 分3个文件（.frm .mydb . myi） innodb数据文件存在在一个文件中
3、锁的级别不同， myisam只支持表级别锁， innodb支持表 和 行级别锁
存储过程 （procedure）：
-- 定义： 为了完成数据库中特定功能的sql语句集（包含多个sql语句 ） ，定义在数据库中
-- 经过一次编译，可多次调用的 语句块。
-- 通常将存储过程也可称为函数（function）
-- 存储过程语法： （使用基础的plsql语法）
-- 创建存储过程
create procedure 过程名称 ([参数列表])
declare
-- 变量的声明
begin
-- sql语句集
end;
-- 存储过程分类，按照参数类型分为四类
-- 1、无参存储过程
-- 查询1班的平均成绩
create procedure pro_selectAvgScore()
begin
select avg(sscore) from t_student where Sclass =1 ;
end;
-- 调用存储过程
call 过程名称([参数])
call pro_selectAvgScore;
-- 2、输入参数存储过程
-- 查询某个班的 最高年龄 ，和 平均分
create procedure pro_selectMaxAge( in cid int)
begin
select max(sage),avg(sscore) from t_student where sclass = cid;
end ;
-- 设置变量
call pro_selectMaxAge(3);
-- 3、输出参数存储过程
-- 可以将存储过程的结果输出到变量中
create procedure pro_selectMaxAge2(in cid int , out v_age int ,out v_score double)
begin
-- 将结果输出到 变量中
select max(sage) , avg(sscore) into v_age , v_score from t_student where sclass = cid ;
end;
-- 调用输出存储过
set @v_age=0 ;
set @v_score=0;
call pro_selectMaxAge2(2 ,@v_age,@v_score)
-- 查询变量中的结果
select @v_age,@v_score;
loop 循环
-- 一些业务逻辑 可以在sql中实现
-- 定义一个存储过程 完成1-100的累加之和
create procedure getSum()
begin
-- 声明变量
declare i int ;
declare sum int;
set i=1; -- 需要赋初始值 才可以改变值
set sum=0; -- 同上
-- loop 表示循环
exit_loop : loop
set sum = sum +i;
-- select concat('i---'+i);
if i>10 then
LEAVE exit_loop;
end if;
set i = i+1;
end loop;
select sum;
end;
drop procedure getSum
-- 4、输入输出参数存储过程
call getSum();
