MySQL – 随波逐流

mysql命令

启动/关闭MySQL服务(cmd要以管理员身份运行)
net start mysql
net stop mysql
windows命令行中mysql登录命令
mysql -h localhost -uroot -proot
显示当前数据库服务器中的数据库列表：
SHOW DATABASES;

DDL语言：数据定义语句

建立数据库：
CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>]
[[DEFAULT] COLLATE <校对规则名>];
例:
CREATE DATABASE my_db;
CREATE DATABASE IF NOT EXISTS educ default charset utf8 COLLATE utf8_general_ci;
查看数据库定义声明:
SHOW CREATE DATABASE <数据库名>
修改数据库编码
ALTER DATABASE <数据库名> CHARACTER SET <字符集名> COLLATE <校对规则名>
例：
alter database test character set utf8 collate utf8_general_ci;
删除数据库：
DROP DATABASE <数据库名>;
打开数据库
use <数据库名>
use educ;
创建表
CREATE TABLE <表名>(
<列名1> <数据类型> [<列级约束>], /列级约束只对本列起作用。/
……
<列名n> <数据类型> [<列级约束>] /列与列之间用","隔开/
[,<表级约束1>] /表级约束对2个以上的列起作用。/
[,<表级约束2>]
)
[ENGINE=<数据库引擎>] /不写ENGINE，则为系统默认引擎/
[CHARSET=<CHARSET=utf8>] /不写CHARSET，则跟数据库编码一致/

列级完整性约束条件如下:

1. PRIMARY KEY：指定该字段为主键。
2. NULL /NOT NULL：指定的字段允许为空/不允许为空，如果没有约束条件，则默认为NULL。
3. UNIQUE：指定字段取值唯一，即每条记录的指定字段的值不能重复。
4. DEFAULT <默认值>：指定设置字段的默认值。
5. AUTO_INCREMENT：指定设置字段的值自动增加。
6. CHECK（条件表达式）：用于对输入值进行检验，拒绝接受不满足条件的值。

表级完整性约束条件如下:

1. PRIMARY KEY用于定义表级主键约束，语法格式如下。
   CONSTRAINT <约束名> PRIMARY KEY [CLUSTERED](字段名1,字段名2,…,字段名n)
2. FOREIGN KEY用于设置参照完整性规则，即指定某字段为外键，语法格式如下。
   CONSTRAINT <约束名> FOREIGN KEY <外键> REFERENCES <被参照表(主键)>
3. UNIQUE既可用于列级完整性约束，也可用于表级完整性约束，语法格式如下。
   CONSTRAINT <约束名> UNIQUE(<字段名>)

例：

create table student(
no int auto_increment primary key,
sid char(6) not null unique,
sname varchar(20) not null,
sex bit(1) null default 1,
birthday date,
phone char(11),
classid char(11)
)
engine=innodb charset=utf8;
create table course(
no int auto_increment primary key,
cid char(3) not null,
cname varchar(20) not null,
credit decimal(3,1),
constraint UQ_cid unique(cid)
)
engine=innodb charset=utf8;
create table if not exists sc(
sid char(6),
cid char(3),
scores decimal(4,1) check(scores>=0 and scores<=100),
constraint PK_sc primary key(sid,cid),
constraint FK_sid_student foreign key(sid) references student(sid) on delete cascade on update cascade,
constraint FK_cid_course foreign key(cid) references course(cid) on delete cascade on update cascade
)
engine=innodb charset=utf8;

外键约束级联操作设置

[CONSTRAINT ] [约束名] FOREIGN KEY(<建立外键的列名>, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION
  1. CASCADE: 从父表中删除或更新对应的行，同时自动的删除或更新自表中匹配的行。ON DELETE CANSCADE和ON UPDATE CANSCADE都被InnoDB所支持。
  2. SET NULL: 从父表中删除或更新对应的行，同时将子表中的外键列设为空。注意，这些在外键列没有被设为NOT NULL时才有效。ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支持。
  3. NO ACTION: InnoDB拒绝删除或者更新父表。
  4. RESTRICT: 拒绝删除或者更新父表。指定RESTRICT（或者NO ACTION）和忽略ON DELETE或者ON UPDATE选项的效果是一样的。

查看表的信息
show tables; /*显示数据表清单*/
describe <表名>; /*显示表中的字段信息*/
describe student;
show create table tableName; /*显示创建表的SQL语句*/
show table status where NAME ='<表名>' /*显示表的各种参数信息*/
修改表的结构
alter table <表名>
add/modify/drop /*add添加，modify修改，drop删除*/

添加列
alter table <表名>
add <列名><数据类型>[<列级约束>]
alter table student
add scredit decimal(4,1) null;
alter table student
add column specialty varchar(30) default '大数据' after birthday;

删除列
alter table <表名>
drop <列名>
alter table student
drop scredit;

修改列属性
alter table <表名>
modify <列名> <新数据类型> <新列级约束> [<位置关键字>]
/*位置关键字为FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）/
/*没有设置的属性或其它，自动设置为默认值或取消，如自动增长/
alter table student
modify phone varchar(20) not null unique;
alter table student
modify column specialty varchar(50) not null default '软件技术' after sname;

修改列名
alter table <表名> change column <列名> <新列名> <数据类型>
alter table student change column phone pnumber varchar(30)

添加默认值
alter table <表名> alter <列名> set default <默认值>
alter table student alter classid set default "信管19302";

删除默认值
alter table <表名> alter <列名> drop default
alter table student alter classid drop default;

创建class表
create table class(
id char(11),
specialty varchar(20),
number tinyint
)
engine=InnoDB;

添加约束
alter table <表名> //alter是修改对象的命令，table 是要修改的对象类型
add constraint <约束名><约束类型>
添加唯一键
alter table student
add constraint UQ_sname Unique(sname);
添加主键
alter table class add constraint PK_class primary key(id);
添加外键
alter table student add constraint FK_student_classid foreign key(classid) references class(id) on update cascade on delete cascade;

删除索引
alter table <表名>
drop index <索引名>
删除唯一键
alter table student
drop index UQ_sname;
删除外键
alter table <表名> drop foreign key <外键名>
alter table student drop foreign key FK_student_classid;
alter table student drop index FK_classid; //删除创建外键时，创建的索引。创建外键时会同时创建一个同名的索引。

删除主键
alter table <表名> drop primary key
alter table class drop primary key;
⚠️注意： 要删除的约束不能被其他地方引用。设置了自动增长的列，不能修改。

查看约束和索引
show keys from <表名>;
show index from <表名>;
show keys from student;
show index from student;

修改表名
alter table <表名> rename to <新表名>

修改表数据库引擎和字符集
alter table <表名> engine=<数据库引擎> charset=<字符集>;
alter table cla engine=MyISAM charset=gb2312;

删除表 /*先删子表，再删父表*/
drop table <表名>
drop table sc;
⚠️注意： 要删除的表，不能被引用

DML语言:数据操作语句

添加记录
insert into <表名>[(列1,列2,……,列n)] values(值1,值2,……值n);
insert into student values(2,"100002","李四",1,"2000-02-02",NULL,NULL);

student表记录
insert into student(sid,sname,sex,birthday,classid)
values('100001',"李四",1,"2001-01-11","信管19301");
insert into student(sid,sname,sex,birthday,classid)
values('100002',"张三",1,"2000-09-08","信管19301");
insert into student(sid,sname,sex,birthday,classid)
values('100003',"曹操",1,"2000-07-10","信管19302");
insert into student(sid,sname,sex,birthday,classid)
values('100004',"司马昭",1,"2000-08-17","信管19302");
insert into student(sid,sname,sex,birthday,classid)
values('100005',"司理理",0,"2000-12-21","信管19301");
insert into student(sid,sname,sex,birthday,classid)
values('100006',"刘备",1,"2001-02-03","信管19301");
insert into student(sid,sname,sex,birthday,classid)
values('100007',"吴越",1,"2001-03-01","信管19303");
insert into student(sid,sname,sex,birthday,classid)
values('100008',"张丽丽",0,"2001-01-07","信管19301");
insert into student(sid,sname,sex,birthday,classid)
values('100009',"于莎莎",0,"2000-08-31","信管19303");
insert into student(sid,sname,sex,birthday,classid)
values('100010',"赵高",1,"2000-07-06","信管19301");
insert into student(sid,sname,sex,birthday,classid)
values('100011',"赵菲菲",0,"2000-10-16","信管19303");
insert into student(sid,sname,sex,birthday,classid)
values('100012',"马莎",0,"2000-10-21","信管19302");
insert into student(sid,sname,sex,birthday,classid)
values('100013',"马奇朵",0,"2000-11-25","信管19302");
insert into student(sid,sname,sex,birthday,classid)
values('100014',"张弘毅",1,"2001-02-26","信管19301");
course表记录
insert into course(cid,cname,credit)
values("101","C语言",6.0);
insert into course(cid,cname,credit)
values("102","php程序设计",3.0);
insert into course(cid,cname,credit)
values("103","MySQL数据库",4.0);
insert into course(cid,cname,credit)
values("104","网页设计",3.0);
insert into course(cid,cname,credit)
values("105","图像处理",3.0);
insert into course(cid,cname,credit)
values("106","linux操作系统",6.0);
sc表记录
insert into sc(sid,cid,scores) values("100001","101",88);
insert into sc(sid,cid,scores) values("100001","102",77);
insert into sc(sid,cid,scores) values("100001","103",93);
insert into sc(sid,cid,scores) values("100002","101",64);
insert into sc(sid,cid,scores) values("100002","102",52);
insert into sc(sid,cid,scores) values("100002","103",71);
insert into sc(sid,cid,scores) values("100003","101",79);
insert into sc(sid,cid,scores) values("100003","102",68);
insert into sc(sid,cid,scores) values("100003","103",73);
insert into sc(sid,cid,scores) values("100004","101",91);
insert into sc(sid,cid,scores) values("100004","102",85);
insert into sc(sid,cid,scores) values("100004","103",74);
insert into sc(sid,cid,scores) values("100005","101",34);
insert into sc(sid,cid,scores) values("100005","102",70);
insert into sc(sid,cid,scores) values("100005","103",53);
insert into sc(sid,cid,scores) values("100006","101",42);
insert into sc(sid,cid,scores) values("100006","102",10);
insert into sc(sid,cid,scores) values("100006","103",67);
insert into sc(sid,cid,scores) values("100007","101",81);
insert into sc(sid,cid,scores) values("100007","102",92);
insert into sc(sid,cid,scores) values("100007","103",20);
insert into sc(sid,cid,scores) values("100008","101",68);
insert into sc(sid,cid,scores) values("100008","102",72);
insert into sc(sid,cid,scores) values("100008","103",82);
insert into sc(sid,cid,scores) values("100009","101",81);
insert into sc(sid,cid,scores) values("100009","102",80);
insert into sc(sid,cid,scores) values("100009","103",78);
insert into sc(sid,cid,scores) values("100010","101",45);
insert into sc(sid,cid,scores) values("100010","102",69);
insert into sc(sid,cid,scores) values("100010","103",75);
insert into sc(sid,cid,scores) values("100011","101",76);
insert into sc(sid,cid,scores) values("100011","102",64);
insert into sc(sid,cid,scores) values("100011","103",63);

乱码解决方法
设置character_set_results、 character_set_client、 character_set_connection 的表字符集一致;
set character_set_results=gb2312;
set character_set_connection=gb2312;
或
set names gb2312;
set names utf8;
set names latin1;

MySQL中涉及的几个字符集
character-set-server/default-character-set：服务器字符集，默认情况下所采用的。
character-set-database：数据库字符集。
character-set-table：数据库表字符集。
优先级依次增加。所以一般情况下只需要设置character-set-server，而在创建数据库和表时不特别指定字符集，这样统一采用character-set-server字符集。
character-set-client：客户端的字符集。客户端默认字符集。当客户端向服务器发送请求时，请求以该字符集进行编码。
character-set-results：结果字符集。服务器向客户端返回结果或者信息时，结果以该字符集进行编码。
character_set_connection：连接字符集。
查看各种字符集
show variables like 'character%';

修改记录
update <表名> set 列名1=<赋值表达式>[,列名2=<赋值表达式>]…… /*赋值表达式可以为具体值，也可以是一个表达式*/
[where <条件表达式>] /*限制更新的行，没有就全部更新*/
update student set sname='红光',sex=1;
删除记录
delete from <表名>
[where <条件表达式>] /*限制删除的行,没有就全部删除*/
delete from student where no=1;
单表数据查询
select 结果 //一般为列名，可根据自己的需求设置不同的表达式
from 数据源 //一般为表或视图，也可以是其他查询结果
where 筛选条件 //行记录的筛选
group by 分组条件 //多分组条件按顺序依次分组
having 分组筛选条件 //根据分组的属性筛选分组
order by 排序条件 //多排序条件按顺序依次排序
limit 限定结果数量 //可以设置起始位置和返回记录数
- 15.1 列筛选
  查询全部列
  select * from <表名> //"*"指代表中所有的列
  select * from student;
  查询指定列
  select 列名1,列名2,…. from <表名> //结果按顺序显示列数据
  select sname,birthday
  from student;
  查询经过计算的列
  select 表达式1,…. from<表名> //结果由表达式计算获得
  select sname,year(now())-year(birthday)
  from student;
  例：查询出每个同学姓名的第一个字。
  给表达式起别名
  select 列表达式 as 新列名 from <表名> //给结果气个新名字
  select sname,year(now())-year(birthday) as age
  from student;
  结果中消除重复的数据 //按属性的组合判定是否重复
  select distinct 列名1,列名2… from <表名> //重复的数据只显示一条
  select distinct year(now())-year(birthday) as age,classid
  from student;
- 15.2 行记录筛选
  比较运算符查询
  select <结果列表> from <表名>
  where 列名=<值> //等号也可以换成其他比较运算符
  范围查询
  select <结果列表> from <表名>
  where 列名 between 起始值 and 终值 //between相当于>=和<=
  集合查询
  select <结果列表> from <表名>
  where 列名 in <数据集合> //<数据集合>可以是另一个查询的结果
  字符匹配查询
  select <结果列表> from <表名>
  where 列名 like <%字符串%> //<字符串>一般还有通配符。
  // % :指代任意多个字符，包括0个字符
  // _ :指代任意一个字符，不包括0个字符
  空值查询
  select <结果列表> from <表名>
  where 列名 is Null //空值是一种特殊的数据类型，不能直接用比较运算符进行比较
  多条件查询
  select <结果列表> from <表名>
  where <条件表达式1> and|or <条件表达式2>
  逻辑运算符：真值/1/true 假值/0/false
  not：逻辑值取反。
  and：两个逻辑值都为真，结果为真，否则结果为假。
  or：两个逻辑值都为假，结果为假，否则结果为真。
- 15.3 分组查询
  select <结果列表> from <表名> //<结果列表>常含有聚合函数(count,sum,max,min,avg)
  group by <分组条件> //多个分组条件按顺序依次分组
  having <筛选分组条件> //以分组的属性作为筛选条件。
  例：

select specialty,count(*) from student
group by specialty
having count(sid)>=5;
select sex,specialty,count(sid) from student group by sex,specialty;
select sname,sex,specialty,count(sid) from student group by grouping sets((sname,sex),specialty);

15.4 排序
select <结果列表> from <表名>
order by <排序条件> //多排序条件按顺序依次排序。
例：
select * from student
order by specialty,Ascores;
15.5 结果数限制
select <结果列表> from <表名>
limit [<起始记录>],<返回记录数> //起始记录默认为0
15.6 查询结果输出
查询结果存储成表
create table <表名> as <select查询>
例1：
create table stu_bak
as
select * from student;
例2：
create table stu_num
as
select specialty,count(*) as nums from student
group by specialty;
查询结果输出到文件
select * from student into outfile 'F:/result/student.txt' fields terminated by ','; //可能被禁止，需修改my.ini
查看结果输出路径设置
SHOW VARIABLES LIKE "secure_file_priv";
secure_file_priv的3种值:
null:禁止输出
具体的路径:只能输出到指定的路径(不包括子路径)
什么都没有:可以输出到自定义的路径
导入上例中文件数据
load data infile 'F:/result/student.txt' into table stu1 charset gbk fields terminated by ','; //表要提前建好
15.7连接查询
交叉连接:cross join //笛卡尔积
select student.sid,sc.sid,scores
from student cross join sc;
内连接:inner join
select student.sid,sname,scores //结果可以是2张表中任意的列
from student inner join sc //连接的表及连接方式
on student.sid=sc.sid; //连接条件
将course表和sc表内连接。
查询C语言不及格的学生学号。
查询出每个同学的总分，结果显示姓名和总分。
左外连接:left outer join
select student.sid,sname,scores
from student left outer join sc
on student.sid=sc.sid;
右外连接:right outer join
select course.cid,cname,scores
from sc right outer join course
on sc.cid=course.cid;
全外连接:full outer join //mysql不支持
select student.sid,sname,course.cid,cname
from student full outer join course
on student.sid=course.cid;
不等值连接
//创建表grade
create table grade(
GPA decimal(2,1) not null,
loscores tinyint,
hiscores tinyint
)engine=innodb charset=utf8;
//添加纪录
insert into grade(GPA,loscores,hiscores) values(3,100,100);
insert into grade(GPA,loscores,hiscores) values(2.5,90,99);
insert into grade(GPA,loscores,hiscores) values(2.0,80,89);
insert into grade(GPA,loscores,hiscores) values(1.5,70,79);
insert into grade(GPA,loscores,hiscores) values(1.0,60,69);
insert into grade(GPA,loscores,hiscores) values(0,0,59);
//不等值连接案例
select sc.sid,sc.cid,sc.scores,sgrade.grade
from sc inner join sgrade
on sc.scores>=sgrade.lowscore and sc.scores<=sgrade.hiscore
3表连接
select student.sid,sname,cname,scores
from student inner join sc on student.sid=sc.sid
inner join course on sc.cid=course.cid
查询出每个同学获得的学分。
自连接
select a.classmaster,b.sid,b.sname from student a inner join student b on a.classmaster=b.sid;
15.8 子查询
select 结果 from 数据源
where 列名列表 in ( select 列名列表 from 数据源)
1. 单行子查询
  select 结果 from 表名
  where 列名 = (单行子查询) –子查询结果为单行数据
  查询某位同学的成绩
  select sid,scores from sc where sid = (select sid from student where sname='张飞')
2. 多行子查询
  select 结果 from 表名
  where 列名 in (多行子查询) –子查询结果为多行数据
  查询所有有挂科同学的名单
  select sid,sname from student where sid in (select sid from sc where scores<60)
  查询入学成绩最高的学生姓名
  select sname from student where Ascores >=all (select Ascores from student)
3. 多列子查询
  select 结果 from 表名
  where 列1,列2 in (select 列1,列2 from 表2)
  select sname from student where (sex,classid) in (select sex,classid from student where sid='100006');
4. 相关子查询 –子查询引用父查询信息
  查询每门课程的最高分学生学号
  select cid,sid,scores from sc a where scores>=all(select scores from sc b where b.cid=a.cid)
  更新student表的Ascores列值为sc表中学生成绩的总分
  update student set Ascores=(select sum(scores) from sc where sc.sid=student.sid)
5. 嵌套子查询 –子查询中还有子查询
  select 结果 from 表名 where 列名 in (select 列名 from 表名 where 列名 in (select 列名 from 表名 where 条件表达式))
  查询出C语言及格的同学名单
  select sname from student where sid in (select sid from sc where scores>=60 and cid in (select cid from course where cname='C语言'))
  查询出与刘备，C语言成绩评级相同的同学名单。
  select sname from student where sid in (select sid from sc where cid='000001' and scores between (select lowscore from sgrade where grade=(select grade from sgrade where (select scores from sc where sid=(select sid from student where sname='刘备') and cid=(select cid from course where cname='C语言')) between lowscore and hiscore)) and (select hiscore from sgrade where grade=(select grade from sgrade where (select scores from sc where sid=(select sid from student where sname='刘备') and cid=(select cid from course where cname='C语言')) between lowscore and hiscore)))

索引

16.1创建索引
- 1.创建表时创建索引
  CREATE TABLE <表名>
  (<字段1> <数据类型1> [<列级完整性约束条件1>]
  [,<字段2> <数据类型2> [<列级完整性约束条件2>]]
  [,…]
  [,<表级完整性约束条件1>]
  [,<表级完整性约束条件2>]
  [,…]
  [UNIQUE|FULLTEXT|SPATIAL] <INDEX|KEY>
  [索引名](属性名[(长度)] [,…])
  );
  例:

create table student(
sid char(6) not null primary key,
sname varchar(20) not null,
sex bit(1) null default 1,
birthday date,
phone char(11),
classid char(10),
index idx_sname(sname)
);
create table course(
cid char(3) not null primary key,
cname varchar(20) not null,
credit decimal(3,1),
unique index idx_cname(cname)
);

2.单独创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX <索引名> ON <表名> (属性名[(长度)] [,…]);
例:
create unique index idx_phone on student(phone);
3.修改表结构添加索引
ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX <索引名> (属性名[(长度)] [,…]);
例:
alter table student add index idx_birthday(birthday);

16.2查看索引信息:
show index from table_name;
例:
show index from student;

16.3删除索引
方法1：直接删除索引
DROP INDEX <索引名> ON <表名>;
例:

drop index idx_birthday on student;
方法2：修改表结构删除索引
ALTER TABLE <表名> DROP INDEX <索引名>;
例:
alter table student drop index idx_phone;

17.视图
创建视图
CREATE VIEW view_name [(Column [,…n])]
AS select_statement [WITH CHECK OPTION];

WITH CHECK OPTION：强制所有通过视图修改的数据满足select_statement语句中指定的选择条件。
例:

create or replace view faillist
as
select sname,cname,scores from student inner join sc on student.sid=sc.sid inner join course on sc.cid=course.cid where sc.scores<60 with check option;

修改视图
ALTER VIEW view_name [(Column[,…n])]
AS select_statement
[WITH CHECK OPTION];
alter view v2
as
select * from sc where scores<60 with check option;

更新视图数据
update v2 set scores=65 where sid='0000000002';

查看视图信息(方法与表一致)
show tables;
show create table/view view_name;
show table status like 'view_name';

删除视图
DROP VIEW view_name;

18.SQL编程
18.1存储过程
create procedure 存储过程名([参数])
begin
SQL语句及程序体;
end;

例:带参数执行查询的存储过程，输入学号，查询出总分。
delimiter ##
create procedure p1(in myid char(6))
begin
  select student.sid,sum(sc.scores) as tot_scores
  from student inner join sc on student.sid=sc.sid
  where student.sid=myid;
end;
##
delimiter ;
//执行存储过程
call proc1('100001');
例:带循环的存储过程，输入一个数，计算1+2+3...+n的和
delimiter ##
 create procedure p3(in a int)
 begin
 declare sum int default 0;
 declare i int default 1;
 while i<=a do
 begin
 set sum=sum+i;
 set i=i+1;
 end;
 end while;
 select sum;
 end;
 ##
delimiter ;
//调用存储过程
call p3(5);
例：带输出参数的存储过程，输入学号，输出总分
delimiter ##
create procedure p2(in myid char(6),out myscores int)
begin
set myscores=(select sum(sc.scores) from student inner join sc on stude
nt.sid=sc.sid where student.sid=myid);
end;
##
delimiter ;
//调用存储过程
call p2('100002',@result);
//查看存储过程
show procedure status where db='数据库名'
show procedure status where db='educ';

18.2存储函数
create function 函数名([参数])
returns 数据类型
begin
SQL语句及程序体;
end;

例：带循环的，计算阶乘的函数
delimiter ##
create function f1(n int)
returns int
begin
  declare i int default 1;
  declare m int default 1;
while i<=n do
begin
  set m=m*i;
  set i=i+1;
end;
end while;
return m;
end;
##
delimiter ;
//调用函数
select f1(5);
例：计算年龄的函数
delimiter ##
create function f_age(d date)
returns int
begin
  declare age int default 0;
  set age=year(now())-year(d);
  return age;
end;
##
delimiter ;
//调用函数f_age
select sname,f_age(birthday) from student;
例：计算总分的函数
delimiter ##
create function f_scores(id char(6))
returns int
begin
  declare sum int default 0;
  set sum=(select sum(scores) from sc where sid=id);
  return sum;
end;
##
delimiter ;
//调用函数f_scores
select sname,f_scores(sid) from student;
例：计算GPA的函数
delimiter ##
create function f_GPA(myid char(6))
returns decimal(3,1)
begin
  declare my_GPA decimal(3,1) default 0;
  set my_GPA=(select sum(GPA) from sc inner join grade on sc.scores between grade.loscores and hiscores where sc.sid=myid);
  return my_GPA;
end;
##
delimiter ;
//查看存储函数
show function status where db='数据库名'
show function status where db='educ';

18.3 DML触发器
create trigger 触发器名
after/before insert/update/delete on 表名
for each row
begin
SQL语句;#触发程序
end;
//insert/update/delete是触发事件
//after/before是触发时间

例1：阻止向student表中插入记录
delimiter ##
create trigger t1
before insert on student
for each row
begin
  signal sqlstate "44000";
end;
##
//测试案例
insert into student(sid,sname,sex,birthday,classid) values('100015',"孙权",0,"2002-02-26","信管19303");
class表
 create table class(
 classid char(11),
 number tinyint
 );
例2：当在student表中添加和删除记录时，更新class表中班级人数。
//更新所有班级人数
delimiter ##
create trigger tr_delete
after delete on student
for each row
begin
update class set number=(select count(sid) from student where class.classid=student.classid);
end;##
delimiter ;
delimiter ##
create trigger tr_insert
after insert on student
for each row
begin
update class set number=(select count(sid) from student where class.classid=student.classid);
end;##
delimiter ;
//精准定位班级修改人数
delimiter ##
create trigger tr_student_insert
after insert on student
for each row
begin
  update class set number=number+1 where classid=new.classid;
end;
##
create trigger tr_student_delete
after delete on student
for each row
begin
  update class set number=number-1 where classid=old.classid;
end;
##
delimiter ;
例3:设置sc表的scores数据范围在0-100分之间
delimiter ##
create trigger tr_sc_insert
before insert on sc
for each row
begin
  if(new.scores<0 or new.scores>100) then
    signal sqlstate "44000";
  end if;
end;
##
create trigger tr_sc_update
before update on sc
for each row
begin
  if(new.scores<0 or new.scores>100) then
    signal sqlstate "44000";
  end if;
end;
##
delimiter ;
//delimiter ##设置结束符为##
//signal sqlstate "44000";取消操作并发送警告信息
//insert检测
insert into sc values("0000000012","000003","101");
例4:设置触发器将student表中删除的记录的学生学号和学生姓名放入新表del_student.
create table del_student(
sid char(6),
sname varchar(30)
);
create trigger tr_student_delete
after delete on student
for each row
begin
  insert into del_student(sid,sname) values(old.sid,old.sname);
  update class set number=number-1 where classid=old.classid;
end;
##

//查看触发器
show triggers;
//删除触发器
drop trigger 触发器名;

19.事物
//启动事物
start transaction;
//提交事物
commit;
//回滚事物
rollback;
//
案例：
start transaction;
insert into sc values("0000000017","000002",67);
select * from sc;
rollback;
select * from sc;
//查看事物隔离级别
select @@tx_isolation; //会话级
select @@global.tx_isolation; //系统级
级别1：read uncommitted :
   读取尚未提交的数据，哪个问题都不能解决
级别2：read committed：
   读取已经提交的数据（可以读取到其他事务提交的update更新和insert新增），可以解决脏读，oracle默认的
级别4：repeatable-read：
   重读读取（只能读取其他事务已经提交的insert新增数据），可以解决脏读和不可重复读，mysql默认的
级别8：serializable：
   串行化：可以解决脏读不可重复读和虚读—相当于锁表
//设置事物隔离级别
set session transaction isolation level read committed; //会话级
set global transaction isolation level repeatable read; //系统级

20.用户管理
//查看用户
use mysql; //选择mysql数据库
select * from user;

20.1创建用户
CREATE USER 'username'@'hostname'[IDENTIFIED BY [PASSWORD]'password'];
例：
create user 'u1'@'localhost' identified by '123';

20.2授予权限
grant privilegesCode on dbName.tableName to username@'host' [identified by "password"] with grant option;

//privilegesCode表示授予的权限类型，常用的有以下几种类型[1]：
all privileges：所有权限。
select：读取权限。
delete：删除权限。
update：更新权限。
create：创建权限。
drop：删除数据库、数据表权限。
// dbName.tableName表示授予权限的具体库或表，常用的有以下几种选项：
*.*：授予该数据库服务器所有数据库的权限。
dbName.*：授予dbName数据库所有表的权限。
dbName.dbTable：授予数据库dbName中dbTable表的权限。
//username@host表示授予的用户以及允许该用户登录的IP地址。其中Host有以下几种类型：
localhost：只允许该用户在本地登录，不能远程登录。
%：允许在除本机之外的任何一台机器远程登录。
192.168.52.32：具体的IP表示只允许该用户从特定IP登录。
//password指定该用户登录时的密码。
//flush privileges表示刷新权限变更。

例：
//授予a1在educ数据库的创建表和索引权限
grant create on educ.* to a1@'localhost';	
flush privileges;
//授予a1在educ数据库的插入数据权限
grant insert on educ.* to a1@'localhost';
flush privileges;
//授予a1在服务器的创建数据库、表和索引权限
grant create on *.* to a1@'localhost';
flush privileges;

//权限查询
show grants [for 'username'@'hostname'];
show grants; //当前用户权限
show grants for u1@'localhost';

20.3收回权限
revoke privilegesCode on dbName.tableName from 'username'@'hostname';
例:
revoke insert on educ.* from 'a1'@'localhost';
flush privileges;
// privilegesCode和dbName.tableName都必须准确
revoke all privileges from 'a1'@'localhost'; //收回所有权限
flush privileges;

20.4修改用户名及密码
//修改密码set password方法
例：
set password for 'a1'@'localhost'=password('456')
//修改密码update更新user表方法
例：
update mysql.user set password = password('789') where user = 'a1' and host = 'localhost';
flush privileges;
//修改用户名
rename user old_username to new_username;
例：
rename user a1@'localhost' to b1@'%';

20.5删除用户
//drop方法
drop user 'username'@'hostname';
例:
drop user b1;

21.MySQL数据库备份
21.1备份数据库
//备份全部数据库
mysqldump -h localhost -uroot -proot –lock-all-tables –all-databases > ALLDB.sql
//备份指定数据库
mysqldump -uroot -proot –default-character-set=utf8 -l library>F:\bak\lib.sql
//备份指定的表
mysqldump -uroot -proot -hlocalhost -l –default-character-set=utf8 educ student>F:\bak\student.sql

//-u,–user=username # 指定用户名
//-p,–password=password # 指定密码
//-h,–host=hostname # 指定服务器ip
//-P,–port=port # 指定连接端口
//–add-drop-database # 在每个数据库创建语句前加上drop database语句
//–add-drop-table # 在每个表创建语句前加上drop table语句
//–default-character-set=latin1 # 指定导出数据的编码
//-n,–no-create-db # 不包括数据库的创建语句
//-t,–no-create-info # 不包括数据表的创建语句
//-d,–no-data # 不包括数据
//–compact # 输出更为简洁，不包括各种注释语句
//-c,–complete-insert # 使输出文件中的insert语句包括字段名称，默认不包括字段名称
//-F,–flush-logs # 备份前刷新日志
//-l,–lock-tables # 备份期间对所有表加锁
//–help mysqldump # 命令帮助

21.2恢复数据
//在windows环境导入.sql文件
mysql -uroot -proot educ<F:\bak\educ.sql –default-character-set=utf8

//在mysql环境导入.sql文件
use educ;
set names utf8;
source F:\bak\educ.sql;

DML语言:数据操作语句

发送评论 编辑评论

发送评论编辑评论