问题和知识点
- 1.删除表级约束
- 2.删除表级唯一约束
- 3.删除外键
- 4.向表emp2中添加列dept_id,并在其中定义foreign key约束,与之相关联的列是dept2表中的id列。
- 5.创建表,实现主键自增长,并插入数据
- 6.自增长列\标识列必须和主键搭配吗?
- 7.一个表可以有几个标识列?
- 8.标识列的类型只能是数值类型
- 9.标识列可以通过set auto_increment_increment=3;设置步长,可以通过手动插入值,设置起始值
- 10.修改表时设置标志列
- 11.TCL的全称是什么?
- 12.什么是事务?为什么要有事务?
- 13.通过什么命令查看mysql的存储引擎?
- 14.在mysql中用的最多的存储引擎有:innodb,myisam,memory等,其中innnodb支持事务,而myisam、memory等不支持事务
- 15.事务的ACID(acid)属性有哪些?
- 16.事务的创建
- 17.数据库的隔离级别
- 18.数据库的隔离级别
- 19.查看数据库的隔离级别
- 20.设置当前mysql连接数据库的隔离级别
- 21.设置数据系统的全局的隔离级别
- 22.savepoint的使用
- 23.视图
- 24.应用场景:
- 25.示例
- 26.试图的修改
- 27.删除试图
- 28.查看试图
- 29.创建试图emp_v1,要求查询电话号码以’011‘开头的员工姓名和工资、邮箱
- 30.具备一下特点的试图不允许更新
- 31.删除数据的事务代码
- 32.设置级联删除
- 33.级联置空
- 34.MySQL中有哪几个类型的变量?
- 35.系统变量
- 36.全局变量的作用域
- 37.会话变量的作用域
- 38.用户变量的作用域
- 39.用户变量的初始化方式
- 40.局部变量
- 41.存储过程
- 42.创建存储过程
- 43.调用存储过程
- 44.存储过程测试样例代码
- 45.创建存储过程实现 根据女神名,查询对应的男神信息
- 46.创建存储过程实现用户是否登录成功
- 47.根据女神名,查询对应的男神名
- 48.根据女神名,查询对应的男神名和男神魅力值
- 49.传入a和传入b两个值,最终a和b都翻倍并返回
- 50.创建存储过程实现传入用户名和密码,插入到admin表中
- 51.创建存储过程实现传入女神编号,返回女神名称和女神电话
- 52.创建存储过程实现传入两个女神生日,返回大小
- 53.删除存储过程
- 54.查看存储过程
- 55.不能修改存储过程中的逻辑语句
- 56.创建存储过程实现传入一个日期,格式化成xx年xx月xx日并返回
- 57.创建存储过程实现传入女神名称,返回女神 and 男神 格式的字符串
- 58.函数和存储过程的区别
- 59.函数的创建语法
- 60.函数的调用
- 61.返回公司的员工个数
- 62.根据员工名,返回它的工资
- 63.根据部门名,返回该部门的平均工资
- 64.查看函数
- 65.删除函数
- 66.创建函数,实现传入两个float,返回二者之和
- 67.流程控制结构
- 68.if函数
- 69.case结构
- 70.创建存储过程,根据传入的成绩来显示等级,比如传入的成绩:90-100,显式A,80-90,显式B,60-80显式C,否则,显示D
- 71.if结构
- 72.根据传入的成绩,来显示等级,比如传入的成绩:90-100,返回A,80-90,返回B,60-80,返回C,否则,返回D
- 73.while循环语句
- 74.loop
- 75.repeat
- 76.批量插入,根据次数插入到admin表中多条记录
- 77.批量删除,根据次数删除admin表中多条记录,如果次数>20则停止
- 78.批量插入,根据次数插入到admin表中多条记录,只插入偶数次
- 79.向该表中插入指定个数的随机字符串
1.删除表级约束
ALTER TABLE stuinfo Drop primary key;
2.删除表级唯一约束
ALTER TABLE stuinfo drop index seat;
3.删除外键
alter table stuinfo drop foreign key fk_stuinfo_major;
4.向表emp2中添加列dept_id,并在其中定义foreign key约束,与之相关联的列是dept2表中的id列。
alter table emp2 add column dept_id int;
alter table emp2 add constraint fk_emp2_depts foreign key(dept_id) references dept2(id);
5.创建表,实现主键自增长,并插入数据
CREATE TABLE tab_identity(
id int PRIMARY key auto_increment,
name VARCHAR(20)
);
INSERT into tab_identity values(null,'john');
6.自增长列\标识列必须和主键搭配吗?
不一定,但是要求是一个key,即为主键或者unique;
7.一个表可以有几个标识列?
最多只能有一个。
8.标识列的类型只能是数值类型
9.标识列可以通过set auto_increment_increment=3;设置步长,可以通过手动插入值,设置起始值
10.修改表时设置标志列
alter table tab_identity modify column id int primary key auto_increment;
11.TCL的全称是什么?
Transaction Control Language事务控制语言
12.什么是事务?为什么要有事务?
1.一个或一组sql语句组成的一个执行单元,这个执行单元要么全部执行,要么全部不执行。
2.为了确保相互影响的sql语句操作的完整性。
13.通过什么命令查看mysql的存储引擎?
show engines;
14.在mysql中用的最多的存储引擎有:innodb,myisam,memory等,其中innnodb支持事务,而myisam、memory等不支持事务
15.事务的ACID(acid)属性有哪些?
- 原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。 - 一致性(Consistency)
事务必须使数据库从一个一致性状态变换到另外一个一致性状态。 - 隔离性(Isolation)
事务的隔离性是指一个事务的执行不能被其他事务干扰,即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。 - 持久性(Durability)
持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来的其他操作和数据库故障不应该对其有任何影响
16.事务的创建
1.隐式创建:事务没有明显的开启和结束的标签
2.显式事务:事务具有明显的开启和结束的标记。必须先设置自动提交功能为禁用。命令为:set autocommit=0;
查看事务是否关闭的语句:
show variables like 'autocommit';
步骤:
1.开启事务
set autocommit=0;
start transaction;
2.编写事务中的sql语句
3.结束事务
commit;提交事务
rollback;回滚事务
17.数据库的隔离级别
1.脏读:对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段.之后, 若 T2 回滚, T1读取的内容就是临时且无效的.
2.不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段.之后, T1再次读取同一个字段, 值就不同了.
3.幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行.
数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力,使它们不会相互影响, 避免各种并发问题.
18.数据库的隔离级别
1.Oracle 支持的 2 种事务隔离级别:READ COMMITED,SERIALIZABLE。 Oracle 默认的事务隔离级别为: READCOMMITED
2.Mysql 支持 4 种事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE READ
19.查看数据库的隔离级别
select @@tx_isolation;
20.设置当前mysql连接数据库的隔离级别
set session transaction isolation level read committed
21.设置数据系统的全局的隔离级别
set global transaction isolation level read committed;
22.savepoint的使用
set autocommit=0;
start transaction;
delete from account where id=25;
savepoint a;
delete from account where id=28;
rollback to a;
23.视图
MySQL从5.0.1版本开始提供视图功能。一种虚拟存在的表,行和列的数据来自定义视图的查询中使用的表,并且是在使用视图时动态生成的,只保存了sql逻辑,不保存查询结果
24.应用场景:
– 多个地方用到同样的查询结果
– 该查询结果使用的sql语句较复杂
25.示例
CREATE VIEW my_v1
AS
SELECT studentname,majorname
FROM student s
INNER JOIN major m
ON s.majorid=m.majorid
WHERE s.majorid=1;
26.试图的修改
方式一:
create or replace view 视图名
as
查询语句;
方式二:
alter view 视图名
as
查询语句;
27.删除试图
drop view 视图名,视图名
28.查看试图
Desc 视图名
show create view 视图名;
29.创建试图emp_v1,要求查询电话号码以’011‘开头的员工姓名和工资、邮箱
use myemployees;
create or REPLACE view emp_v1
as
select last_name,salary,email
from employees
where phone_number like '001%'
30.具备一下特点的试图不允许更新
1.包含以下关键字的sql语句:分组函数、distinct、group by、having、union或者union all
2.常量视图
3.select中包含子查询
4.使用到了join语句
5.from后面跟的是一个不能更新的视图
31.删除数据的事务代码
set autocommit=0;
start transaction;
delete from account;
rollback;
32.设置级联删除
alter table stuinfo add constraint fk_stu_major foreign key(majorid) references major(id) on delete cascade;
33.级联置空
alter table stuinfo add constraint fk_stu_major foreign key(majorid) references major(id) on delete set null;
34.MySQL中有哪几个类型的变量?
系统变量:全局变量、会话变量
自定义变量:用户变量、局部变量
35.系统变量
说明:变量由系统提供,不是用户定义,属于服务器层面
使用的语法:
1.查看所有的系统变量
show global variables;
2.查看会话变量
show session variables;
3.查看满足条件的部分系统变量
global|[session] variables like ‘%char%’
4.查看指定的某个系统变量的值
select @@global|session.系统变量名;
5.为某个系统变量赋值
set global|session 系统变量名=值;
set @@global|[session].系统变量名=值;
注意:
如果是全局级别,则需要加global,如果是会话级别,则需要加session,如果不写,则默认是session
36.全局变量的作用域
作用域:服务器每次重启将为所有的全局变量赋初始值,针对所有的会话(连接)有效,但是不能跨重启
37.会话变量的作用域
仅仅针对于当前会话(连接)有效
38.用户变量的作用域
针对当前会话(连接)有效,同于会话变量的作用域
可以放在任何地方
39.用户变量的初始化方式
方式一:
set @用户变量名=值;
set @用户变量名:=值;
set @用户变量名:=值;
方式二:
select 字段 into @变量名
from 表;
案例:
#声明并初始化
set @name='john';
set @name=100;
set @count=1;
#赋值
select count(*) into @count
from employees;
#查看
select @count;
40.局部变量
作用域:仅仅在定义它的begin end中有效,应用在begin end中的第一句话
1.声明
declare 变量名 类型;
declare 变量名 类型 default 值;
2.赋值
方式一:通过set或select
set 局部变量名=值;
set 局部变量名:=值;
select @局部变量名:=值;
方式二:通过select into
select 字段 into 局部变量名
from 表;
3.使用
select 局部变量名;
41.存储过程
含义:一组预先编译好的SQL语句的集合,理解成批量处理语句
1.提高代码的重用性
2.简化操作
3.减少了编译次数并且减少了和数据据服务器的连接次数,提高了效率
42.创建存储过程
create procedure 存储过程名(数据列表)
begin
存储过程体(一组合法的sql语句)
end
注意:参数列表包含三部分
参数模式 参数名 参数类型
举例:
IN stuname varchar(20)
参数模式:
in:该参数可以作为输入
out:输出
inout:输入输出
存储过程体
如果存储过程体仅仅只有一句话,begin end可以省略;
存储过程体中的每条sql语句的结尾要求必须加分号。
存储过程的结尾可以使用delimiter重新设置
语法:
delimiter 结束标记
delimiter $
43.调用存储过程
call 存储过程名(实参列表)
44.存储过程测试样例代码
use students;
select * from major;
delimiter $
CREATE PROCEDURE myp1()
BEGIN
insert into major values(1,"1"),(2,"2"),(3,"3"),(4,"4");
end $
call myp1() $
45.创建存储过程实现 根据女神名,查询对应的男神信息
create PROCEDURE myp2(in beautyName VARCHAR(20))
begin
select bo.*
from boys bo
right join beauty b on bo.id=b.boyfriend_id
where b.name=beautyName;
end $
call myp2('柳岩') $
46.创建存储过程实现用户是否登录成功
create PROCEDURE myp4(in username VARCHAR(20),in password VARCHAR(20))
BEGIN
DECLARE result int default 0;
select count(*) into result
from admin
where admin.username=username
and admin.password=PASSWORD;
select if(result>0,'成功','失败');
END $
call myp4('张飞','8888') $
47.根据女神名,查询对应的男神名
delimiter $
CREATE PROCEDURE myp5(in beautyName varchar(20),out boyName VARCHAR(20))
BEGIN
select bo.boyName into boyName
from boys bo
INNER JOIN beauty b on bo.id=b.boyfriend_id
where b.name=beautyName;
END $
call myp5('小昭',@bName) $
select @bName$
48.根据女神名,查询对应的男神名和男神魅力值
create PROCEDURE myp6(in beautyName varchar(20),out boyName VARCHAR(20),out userCP int)
BEGIN
select bo.boyName,bo.userCP into boyName,userCP
FROM boys bo
INNER JOIN beauty b on bo.id=b.boyfriend_id
where b.name=beautyName;
END $
call myp6('小昭',@bName,@usercp)$
select @bName,@usercp
49.传入a和传入b两个值,最终a和b都翻倍并返回
create PROCEDURE myp8(INOUT a int,inout b int)
BEGIN
set a=a*2;
set b=b*2;
END $
set @a=10$
set @b=10$
call myp8(@a,@b)$
select @a,@b$
50.创建存储过程实现传入用户名和密码,插入到admin表中
CREATE PROCEDURE test_pro1(in username varchar(20),in loginPwd VARCHAR(20))
BEGIN
insert into admin(admin.username,PASSWORD)
values(username,loginPwd);
END $
51.创建存储过程实现传入女神编号,返回女神名称和女神电话
create PROCEDURE test_pro2(in id int,out name VARCHAR(20),out phone VARCHAR(20))
BEGIN
select b.name,b.phone into name,phone
from beauty b
where b.id=id;
END $
call test_pro2('1')$
52.创建存储过程实现传入两个女神生日,返回大小
create PROCEDURE test_pro3(in birth1 datetime,in birth2 datetime,out result int)
BEGIN
select DATEDIFF(birth1,birth2) into result;
end $
53.删除存储过程
drop procedure p1;
54.查看存储过程
show create PROCEDURE myp2;
55.不能修改存储过程中的逻辑语句
56.创建存储过程实现传入一个日期,格式化成xx年xx月xx日并返回
CREATE PROCEDURE test_pro4(in mydate datetime,out strDate VARCHAR(50))
BEGIN
select DATE_FORMAT(mydate,'%y年%m月%d日') into strDate;
END $
call test_pro4(now(),@str)$
select @str $
57.创建存储过程实现传入女神名称,返回女神 and 男神 格式的字符串
create PROCEDURE test_pro5(in beautyName varchar(20),out str VARCHAR(50))
BEGIN
select concat(beautyName,' and ',ifnull(boyName,'null')) into str
from boys bo
RIGHT JOIN beauty b on b.boyfriend_id=bo.id
where b.name=beautyName;
END $
call test_pro5('小昭',@str)$
select @str $
58.函数和存储过程的区别
存储过程:可以有0个返回,也可以有多个返回,适合做批量插入、批量更新
函数:有且仅有1个返回值,适合做处理数据后返回一个结果
59.函数的创建语法
create function 函数名(参数列表) returns 返回类型
begin
函数体
end
注意:
1.参数列表 包含两部分:
参数名 参数类型
2.函数体:肯定会有return语句,如果没有会报错
如果return语句没有放在函数体的最后也会报错,但是不建议
return 值;
3.函数体中仅有一句话则可以省略begin end
4.使用delimiter语句作为设置结束标志
60.函数的调用
select 函数名(参数列表)
61.返回公司的员工个数
use myemployees
delimiter $
CREATE FUNCTION myf1() RETURNs INT
BEGIN
DECLARE c int default 0;
select count(*) into c
from employees;
return c;
END $
select myf1()$
62.根据员工名,返回它的工资
create function myf2(empName VARCHAR(20)) returns double
BEGIN
set @sal=0;
select salary into @sal
from employees
where last_name=empName;
return @sal;
end $
select myf2('kochhar') $
63.根据部门名,返回该部门的平均工资
CREATE FUNCTION myf3(deptName VARCHAR(20)) returns double
BEGIN
DECLARE sal double;
select avg(salary) into sal
from employees e
join departments d on e.department_id=d.department_id
where d.department_name=deptName;
return sal;
END $
select myf3('IT')$
64.查看函数
show create function myf3;
65.删除函数
drop function myf3;
66.创建函数,实现传入两个float,返回二者之和
create function test_fun1(num1 float,num2 float) returns float
begin
declare sum float default 0;
set sum=num1+num2;
return sum;
end $
select test_fun1(1,2)$
67.流程控制结构
顺序结构
分支结构
循环结构
68.if函数
if(表达式1,表达式2,表达式3)
表达式1成立执行表达式2不成立则执行表达式3
69.case结构
语法:
情况1:
case 变量|表达式|字段
when 要判断的值 then 返回的值1或语句1;
when 要判断的值 then 返回的值2或语句2;
…
else 要返回的值n或语句n;
end
情况2:
case
when 要判断的条件1 then 返回的值1或语句1;
when 要判断的条件2 then 返回的值2或语句2;
…
else 要返回的值n或语句n;
end
特点:
可以作为表达式,嵌套在其他语句中使用,可以放在任何地方,begin end中或begin end的外边
可以作为独立的语句去使用,只能放在begin end中。
ELSE可以省略,如果ELSE省略了,并且所有WHEN条件都不满足,则返回null.
70.创建存储过程,根据传入的成绩来显示等级,比如传入的成绩:90-100,显式A,80-90,显式B,60-80显式C,否则,显示D
delimiter $
create PROCEDURE test_case(in score int)
BEGIN
case
when score>=90 and score<=100 then select 'A';
when score>=80 then select 'B';
when score>=60 then select 'C';
else select 'D';
end case;
END $
call test_case(95)$
71.if结构
语法:
if 条件1 then 语句1;
elseif 条件2 then 语句2;
…
[else 语句n;]
end if;
应用在begin end中
72.根据传入的成绩,来显示等级,比如传入的成绩:90-100,返回A,80-90,返回B,60-80,返回C,否则,返回D
create function test_if(score int) returns CHAR
BEGIN
if score>=90 and score<=100 then RETURN 'A';
ELSEIF score>=80 then return 'B';
ELSEIF score>=60 then return 'C';
else RETURN 'D';
end if;
END $
select test_if(10)$
73.while循环语句
语法:
[标签:]while 循环条件 do
循环体;
end while [标签];
74.loop
[标签:] loop
循环体;
end loop [标签];
75.repeat
[标签:]repeat
循环体;
until 结束循环的条件
end repeat [标签];
76.批量插入,根据次数插入到admin表中多条记录
create PROCEDURE pro_while(in insertCount int)
BEGIN
DECLARE i int DEFAULT 1;
while i<=insertCount DO
INSERT into admin(username,`PASSWORD`) values(concat('Rose',i),'666');
set i=i+1;
end while;
END $
call pro_while(100)$
77.批量删除,根据次数删除admin表中多条记录,如果次数>20则停止
CREATE PROCEDURE test_while2(in insertCount int)
BEGIN
DECLARE i int default 1;
a:while i<insertCount DO
delete from admin WHERE username=CONCAT('Rose',i);
if i>=20 then leave a;
end IF;
set i=i+1;
end while a;
END $
call test_while2(100)$
78.批量插入,根据次数插入到admin表中多条记录,只插入偶数次
create procedure test_while3(in insertCount int)
BEGIN
DECLARE i int DEFAULT 0;
a:WHILE i<=insertCount DO
set i=i+1;
if mod(i,2)!=0 then ITERATE a;
end if;
INSERT into admin(username,`password`) values(concat('xiaohua',i),'0000');
end while a;
END $
call test_while3(100)$
79.向该表中插入指定个数的随机字符串
create table stringcontent(
id int PRIMARY key auto_increment,
content varchar(20)
);
delimiter $
CREATE PROCEDURE test_randstr_insert(in insertCount int)
BEGIN
DECLARE i int default 1;
DECLARE str VARCHAR(26) DEFAULT 'abcdefghijgkmnopqrstuvwxyz';
DECLARE startIndex int DEFAULT 1;
DECLARE len int DEFAULT 1;
WHILE i<insertCount DO
set len=floor(rand()*(20-startIndex+1)+1);
set startIndex=floor(RAND()*26+1);
insert into stringcontent(content) values(SUBSTR(str,startIndex,len));
set i=i+1;
end while;
END $
call test_randstr_insert(2)$