SQL_操作MySQL 数据库

数据库命令每一句都以分号结尾

一、基础操作命令

1.支持中文命令：
set character_set_datebase=‘gbk’;
set character_set_server=‘gbk’;
status;
2.查看已有数据库
show databases;
3.创建数据库
create database 数据库名;
补：库名前添加if not exists 免除错误
4.删除数据库
drop database 数据库名;
补：库名前添加if exists 免除错误
补6.1修改默认字符集 以及校对规则
alter database 数据库名
default character set 字符集名
default collate 校对规则名
5.从头创建全新的表
create table 表名
临时表关键字create temporary table
use 数据库名;
create table 表名
（
列名 数据类型（长度） 是否为空集 主键primary key，
列名 数据类型（长度） 是否为空集 default 常值，
列名 数据类型（长度） 是否为空集，
）engine=引擎名;:
例：mysql> use xscj;
Database changed
mysql> create table xs
(
学号 char(6) not null primary key,
姓名 char(8) not null,
专业名 char(10) null,
性别 tinyint(1) not null default 1,
出生日期 date not null,
总学分 tinyint(1) null,
照片 blob null,
备注 text null
)
engine=innodb;选择引擎
6.复制现成的表
打开数据库 use 数据库名;
复制表 create table 新表名 like 被复制表名;
另一种复制方式 create table 新表名 （select *from 被复制表名);
7.修改表
（1）修改表结构
修改操作有
a.增加或删除列 【first/after 列名】增加在前或后
增加列 alter table xs2 add column 考评 tinyint null;
删除列 alter table xs2 drop 考评;
b.创建或取消索引
创建索引

c.列名修改
alter table xs2 change 考评 考评分 tinyint;
d.数据类型的修改
alter table xs2 modify 考评分 integer not null;
e.表名的更改
alter table xs2 rename xs_2;
补：
add column 添加
alter column 修改默认值
change 修改列名 注 修改时需重新定义数据类型
drop 删除
四种命令可以在同一个 alter table 语句下执行 用逗号隔开
每种语句只能用一次
例：
Database changed
mysql> alter table xs
-> add 奖学金等级 tinyint null,
-> drop 姓名;
Query OK, 0 rows affected (1.16 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe xs;
+------------+------------+------+-----+---------+-------+
| Field      | Type       | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| 学号       | char(6)    | NO   | PRI | NULL    |       |
| 专业名     | char(10)   | YES  |     | NULL    |       |
| 性别       | tinyint(1) | NO   |     | 1       |       |
| 出生日期   | date       | NO   |     | NULL    |       |
| 总学分     | tinyint(1) | YES  |     | NULL    |       |
| 照片       | blob       | YES  |     | NULL    |       |
| 备注       | text       | YES  |     | NULL    |       |
| 奖学金等级 | tinyint(4) | YES  |     | NULL    |       |
+------------+------------+------+-----+---------+-------+

f.打印表格
describe 表格名; 可以用
g.回复已更改的表格
例：mysql> alter table xs
-> add 姓名 char(8) not null after 学号,
-> drop column 奖学金等级;
Query OK, 0 rows affected (1.10 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe xs
    -> ;
+----------+------------+------+-----+---------+-------+
| Field    | Type       | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| 学号     | char(6)    | NO   | PRI | NULL    |       |
| 姓名     | char(8)    | NO   |     | NULL    |       |
| 专业名   | char(10)   | YES  |     | NULL    |       |
| 性别     | tinyint(1) | NO   |     | 1       |       |
| 出生日期 | date       | NO   |     | NULL    |       |
| 总学分   | tinyint(1) | YES  |     | NULL    |       |
| 照片     | blob       | YES  |     | NULL    |       |
| 备注     | text       | YES  |     | NULL    |       |
+----------+------------+------+-----+---------+-------+

h.更改表格名
Database changed
mysql> rename table xsl to myxsl,xs_2 to myxs2;
8.删除表
use xscj;
Database changed
mysql> drop table if exists myxs2;

二，数据类型

1，整形
（1) tinyint 极小整形 存储长度1字节
（2）smallint 小整形 存储长度2字节
（3）mediumin 中等整形 存储长度3字节
（4）int 普通整形 原始数据成员 存储长度四字节
integer 类整形 相当于封装好的类，可定义对象 存储长度四字节

  补int 与 integer 不能相互转化

（5）bigint 大整形 存储长度8字节
2.定点型
定点数小数点前后位数固定的数
decimal(m,d)
numeric(m,d)
m:表示该数总长度
d:表示小数位数
默认m=10，d=0；
3.浮点型
（1）float 长度四字节
（2）double 占八字节
判断是否等于不应使用等于号判断
4.位型
bit(m)
m取值范围1-64
输入位数少于m,左侧用零补齐
5.字符串类型
（1）char(n) 占用空间n个字符 最多255个字节 中文占3n个字节
（2）varchar(n) 实际字符个数 n为最大存储长度 最多65535（2）字节
（3）tinytext 实际字符个数 最多255字节
(4) text 实际字符个数 最多65535字节
(5) mediumtext 实际字符个数 最多16777215字节
(6) longtext 实际字符个数 最多4294967295字节
6.二进制串类型
（1）binary 类
binary
varbinary
储存二进制串
（2）blob 二进制对象类 用于储存图片，视频等
tinyblob 最大长度255字节 2e8-1
blob 最大长度65535字节 2e16-1
mediumblob 最大长度2e24-1；
longblob 最大长度2e32-1
7.日期和时间类型
（1）date类
datetime “yyyy-mm-dd hh：mm：ss”（年月日 时分秒）
date 年月日
timestamp 储存记录修改时间，设为NULL默认当前时间
（2）time hhh：mm：ss
可以表示 时间，也可以表示几天时间差
（3）year 表示年
8.枚举类型
9.集合类型

三、数据类型属性

1.auto_increment 设为码列，唯一数字标识符，代表新插入行自加一
例。id smallint NOT NULL auto_increment primary key
注：利用alter table t1 auto_increment=n;强制转化
2.binary 用于 char varchar
被标注过的列，将区分大小写排列
例：name char(25) binary NOT NULL
3.defaul 设置默认值(对行添值)
sex enum(‘0’,‘1’) NOT NULL default ‘0’;
4.index 按照被标注的列创建索引列表
例create table employees
(
name varchar(15) not null;
index name(name)
)
也可以 create index name on employees (name(7));
5.not null 不是空值 输入时必须给出具体值
6.null 输入时允许空值
7.primary key 主键
8.unique 被赋予的列除空值外不同
9.zerofill 用零填补剩下字段

四、对表的记录进行修改

use xscj；
insert into xs
values(‘08101’,‘王林’,‘计算机’,1,‘1994-02-10’,50,null,null);
增加
insert into 表明
values（表中几列就几列）;
insert into 表名（列名）
values（与列名对应）;
insert into 表名
set 列名=‘数据’，列名='数据;
增加图片
1.储存存储位置
insert into xs
values(‘081102’,‘程明’,‘计算机’,1,‘1995-02-01’,50,‘C:\Users\ASUS\Pictures\Camera Roll’,null)
2.储存照片本身
insert into xs
-> values(‘081102’,‘程明’,‘计算机’,1,‘1995-02-01’,50,load_file(‘C:\Users\ASUS\Pictures\Camera Roll’),null)
3.把已有表插入当前表
use xscj;
drop table if exists xsl;
create table xsl like xs;
insert into xsl
select * from xs;
select *from xsl;
显示表select 想显示的列 from 表名; *：代表全部列
3.替换重新插入
replace into xs
values(‘081101’,‘王林’,‘计算机’,1,‘1994-02-10’,50,null,null);
修改
单个表
update xs
set 总学分=总学分+1;
update xs
set 学号=‘081250’,备注=‘辅修计算机专业’//许修改的列
where 姓名=‘刘华’;//条件；行；
多个表
update xs,xsl
set xs.总学分=xs.总学分+4,xsl.总学分=xsl.总学分+4
where xs.学号=xsl.学号;
删除
单一表删除某行
use xscj;
delete from xsl
where 姓名=‘刘华’;
从多个表中删除某行
use xscj;
delete xs,xsl
from xs,xsl
where xs.学号=xsl.学号;
删除表所有数据
truncate table 表名;

insert into xs
values('081106','李方方','计算机',1,'1994-11-20',50,null,null);

四点 Mysql 查询和视图

一、Mysql 数据库查询

1.选择输出项
use xscj;
select 姓名,专业名,总学分
from xs;
2.定义别名列
select 学号 as number,姓名 as name ,总学分 as mark
from xs
where 专业名=‘计算机’;
3.替换查询结果中的数据
select 学号,姓名,//注:有个逗号
case
when 总学分 is null then ‘尚未选课’
when 总学分 <50 then ‘不及格’
when 总学分 >=50 and 总学分 <=52 then ‘合格’
else ‘优秀’
end as 等级
from xs
where 专业名=‘计算机’;

select distinct name as 姓名,address as 住址,
case
when income < 2000 then ‘低收入’
when income >=2000 and income <=3000 then ‘中等收入’
else ‘高等收入’
end as 收入等级
from employees inner join salary
where employees.id = salary.id;
4.计算列值
select 学号,课程号,成绩*1.20 as 成绩120
from xs_kc
where 学号=‘081101’;

insert into xs_kc
values(‘081101’,‘101’,80);
5.消除结果集中重复行
select distinct 专业名,总学分
from xs;

注 结果中学分一至
6.聚合函数
常用函数
count 求组中项数,返回int类型整数
max 求最大值
min 求最小值
sum 返回表达式中所有值的和
avg 求组中值的平均值
std 或 stddev 返回给定表达式中所有值的标准差
variance 返回给定表达式中所有值的方差
group_concat 返回由属于一组的列值连接组合而成的结果
bit_and 逻辑或
bit_or 逻辑与
bit_xor 逻辑异或
bin()将结果转为二进制数
from

二、连接

1.内连接
select distinct 课程名,xs_kc.课程号
from kc inner join xs_kc
on (kc.课程号=xs_kc.课程号);

select 显示列名
from 表名 (inner内连接/outer外连接) join 表名
on(联合查询条件）

2.外连接

1.左外连接
有匹配行 和左表有右表没有的表组成
2.右外连接
有匹配行 和右表有左表没有的表组成
3.自然连接
natural join
distinct//显示不同行，省略相同行
3.交叉连接
拼接表 第一个表的一行和第二个表的每一行拼接
select 学号,姓名,课程号,课程名
from xs cross join kc;

三、where

1.条件判断
结果为 ture
false
unknown
判断符
是否等于 = <=>（支持NULL判断，都等于空算相等）
不等于 ！= <>()
其他 > < >= <=
2.模式匹配（ 字符串匹配）
（1）like
select 学号,姓名,性别
from xs
where 姓名 like ‘王%’;
注：用%和_ 代表模糊查询省略的部分 %省略多个 _ 省略单个
（2）regexp
select 学号,姓名,专业名
from xs
where 学号 regexp ‘[4,5,6]’;
符号表 书55页
3.范围比较
（1）between and
between and 前可加 not
用法
select 学号,姓名,专业名,出生日期
from xs
where 出生日期 between ‘1993-1-1’ and ‘1993-12-31’;
（2）in
select *
from xs

where 专业名 in ('计算机','通信工程');

4.空值比较
利用 is null 或者 is not null 进行比较
select *
from xs
where 总学分 is null;

四、重点 子查询

1.in子查询
select 姓名,学号
from xs
where 学号 in(
select 学号
from xs_kc
where 课程号 = ‘206’
);
在子查询时先执行子查询在执行外部查询
注：IN子查询只能返回一列的数据
所以在查询时可以进行嵌套查询
select 姓名,学号,专业名
from xs
where 学号 not in
(
select 学号
from xs_kc
where 课程号 in
(
select 课程号
from kc
where 课程名 = ‘离散数学’
)
);
2.比较子查询

用法 表达式 比较符号 （ALL|some|any）
（
select 语句
）
ALL 全部值 都相等 返回 ture 否者 返回 false
some = any 部分值相等即可
例1:
select 学号
from xs_kc
where 课程号 =
(
select 课程号
from kc
where 课程名 = ‘离散数学’
);

例2
select 学号,姓名,专业名,出生日期
from xs
where 出生日期 > all
(
select 出生日期
from xs
where 专业名 =‘计算机’
) ;
例3
select 学号
from xs_kc
where 课程号 = '206’and 成绩>= all
(
select 成绩
from xs_kc
where 课程号 = ‘201’
);
注;where 条件相邻的是用and链接
3.exists 子查询
查询子表是否为空表
前面可加 not 结果正相反
子查询对象为全部列
例1
select 姓名
from xs
where exists
(
select *
from xs_kc
where 课程号=‘206’ and 学号 =xs.学号
);

例2
select 姓名
  from xs
  where not exists
  (
    select *
    from kc
  where not exists
        (
              select *
              from xs_kc
            where 学号=xs.学号 and 课程号=kc.课程号
          )
    );

  注:可以建立子表，使from语句在子表中查询
  例3
  select 姓名,学号,总学分
        from (
                select 姓名,学号,性别,总学分
                      from xs
                      where 总学分>50
              ) as student
    where 性别='1';
  例4
  select 学号,姓名,year(出生日期)-year
                (
                (
                      select 出生日期
                      from xs
                    where 学号='081101'
                  )
                ) as 年龄差距
    from xs
  where 性别=0;
  注：where 语句中单句与多句进行比较

  例5 
  select 学号,姓名
        from xs
        where (性别,总学分)=(select 性别,总学分
                                    from xs
                                    where 学号='081101'
  );

4.分组子句 group by
原理 按字段进行分组
例1
select 专业名
from xs
group by 专业名;

select 专业名,count(*)as '学生数'
from xs 
group by 专业名;
	注：使用rollup 进行补充 加入汇总行
例2    
select 专业名,性别,count(*)as '人数'
    from xs
  group by 专业名,性别
  with rollup;

注：带rollup 可以进行复杂查询
例3
select 课程号,专业,avg(成绩)as ‘平均成绩’
from xs_kc,kc,xs
where xs_kc.课程号 =mkc.课程号 and xs_kc.学号 =xs.学号
group by 课程名,专业名
with rollup;
5.条件having 子句
条件与与where类似
having 子句需要引用 中的列，或者使用聚合函数的列
例
select 学号,avg(成绩) as ‘平均成绩’
from xs_kc
where 学号 in
(
select 学号
from xs
where 专业名 = ‘通信工程’
)
group by 学号
having avg(成绩)>=85;
6.排列子句order by
利用 ASC 表示升序排列 利用 DESC表示降序排列 注：空值最小
默认 ASC
select 学号,姓名,专业名
from xs
where 专业名 = ‘计算机’
order by(select avg(成绩)
from xs_kc
group by xs_kc.学号
having xs.学号= xs_kc.学号
);
7.限制行数子句 limit
限制返回行数
例，使用
select 学号,姓名,专业名,性别,出生日期,总学分
from xs
order by 学号
limit 5;
这里有一个省略 应是 limit 0,5;
表示从第一行开始显示5行
如
select 学号,姓名,专业名,性别,出生日期,总学分
from xs
order by 学号
limit 3,5;
为了兼容有时也limit 5 offset 3 结果一样
8.组合结果子句union
例
select 学号,姓名,专业名,性别,出生日期,总学分
from xs
where 学号 = ‘081101’
union
select 学号,姓名,专业名,性别,出生日期,总学分
from xs
where 学号 = ‘081210’;

查出的表1 union 查出的表2   做查出行的连接

五、视图

视图是一个虚表 数据不实际储存，只由用户关心的数据组成
数据库中的表是基本表
注: or replace 可以替换已有同名视图
命名时 create or replace 数据库名.新表名
创建视图时需指名表所在的数据库 用 库名.表名
加上with check option 使可更新视图进行修改符合 select_statement 条件
保证修改后可见
例：
create or replace view xscj.cs_kc
as
select xs.学号,课程号,成绩 //试图列明
from xscj.xs,xscj.xs_kc
where xs.学号 = xs_kc.学号 and xs.专业名= ‘计算机’
with check option;
1.创建视图
use xscj
create view cs_kc_avg(num,score_avg)
as
select 学号,avg(成绩)
from cs_kc
group by 学号;
2.查询视图
按表查询方式查询
3.更新视图
利用 inset,update delete,进行更行
包括 聚合函数及 查询子句的（where 除外）
（1）创建
create or replace view cs_xs
as
select *
from xs
where 专业名 = ‘计算机’
with check option;
（3）插入
insert into cs_xs
valuse(‘081255’,‘李牧’,‘计算机’,1,‘1994-10-14’,50,null,null);
4.修改视图
alter view cs_xs
as
select 学号,学号,总学分
from xs
where 专业名=‘计算机’;
5.删除视图
drop view cs_xs;

六、索引：增加数据库的查找速，人为添加交给数据库使用 优化数据库

1.索引分类
1).普通索引 index
2).唯一索引 unique //可以是空值
3).主键 primary key
4).全文索引 fulltext // 大文本对象
5).哈希索引 hash
升序索引 ase
降序索引 dese
组合索引 在多个列上建立索引 最左前缀

2.创建索引
1)利用create 创建
create 索引类型 索引名
on 表明（列名 （前几个长度） 升降序）

2)利用添加语句 alter table 创建
alert table 表名
  add index 索引名（列名）
3)创建表时添加索引
例 
create table xs
(
  姓名 char(9)  not null,
  学号 char(8)  not null,
  成绩 int null,
  primary key(姓名，学号),
  index cj(成绩)
)

3.删除索引
drop 删除
drop index 索引名 on 表名;
利用修改表语句进行删除
alert table 表名
drop index 索引名；

一、mysql 数据完整性约束

1.对表表的每一次更新，进行检查，检查数据的一致性和正确性
1.主键约束
2.替代键约束 //候补 主键 （可以有很多个） 关键字 unique
3.参照完整性约束
如参照表 中外键中有的值 被参照表中必须含有
foreign key(本表列名) //定义外键 其定义必须和父表的父键保持一致。每一个非空的foreign key都必须在父表的父键里面找到对应的值。
关键字 references 被参照表（被参照列）
on delete 动作行为 //具有删除动作语句
on update 动作 //具有修改的动作
例1
create table 子表名
（
name char(6) not null,
学号 char(8) null,
primary key(姓名), // 本表的主键
foreign key(学号) //与父表相关键
references 父表（学号）
on delete restrict //拒绝子表对父表删除
on update restrict //拒绝子表对父表修改
）
4.check 完整性约束
限制本表本列取值范围
用法 check(条件)
例
create table 表名
（
name char(6) not null,
性别 char(1) not null,
出身日期 date not null,
check(性别 in(‘男’,‘女’))，
check(出身日期>‘1990-01-01’)
）
5.对完整性约束进行 命名
语法 constraint 完整性约束名字 primary key(列名)
创建表的时候添加
6.删除主键
alert table 表名 drop primary key;

二、索引实现原理

不同的存储引擎
1.哈希
2.全文查找
3.B ，B+ 树

注：索引脱离关系模式 只与DBMS有关
只能非运行时创建索引
1）优点
加快速度
减少IO操作
2）缺点
占用空间
降低维护速度
创建增加

3）建立方案
经常查询 排序 使用聚合函数 外键的列建立索引
经常修改 记录少 不建立索引
一个SELECT 只能用一遍

总、MYSQL 过程式数据库对象

包括 ：
1.存储过程
2.存储函数
3.触发器
4.事件

一、存储过程 实质一段代码 由 声明式 与 过程式 储存 可以由其他 如 程序、触发器、 或者另一个存储过程调用

1.创建存储过程
查看有哪些存储结构 show procedure status
查看存储结构具体信息 show create procedure
注：创建存储过程之前 应先修改语句结束符使用 delimiter 修改的符号
使用之后应恢复 opdelimiter;
形参定义 in 表示输入形参 out 输出形参 inout 输入输出 加参数名 加变量类型
多个参数用,号隔开
例：
delimiter $$createproceduredelet{e}_{s}tudent(inxhchar(6))//in表示输入形参out输出形参inout输入输出加参数名加变量类型begindeletefromxswhere学号=xh;//过程体end$$
delimiter;
2.存储过程体
1)局部变量
declare 变量名 变量类型
例：declare num int (4);
declare str1,str2 varchar(6);
2)使用SET语句赋值
set 变量名 = 表达式 ;
例：
set num = 1,str = ‘hello’;
3)select … into
select 只能查到一行 返回
直接将返回值存储到变量中
例: select 姓名,专业名 into name,project
from xs;
where 学号 = ‘081101’;
4)流程控制语句
（1）if语句
if-then-else
if 条件 then 执行语句
else if 条件
then 执行语句
else
then 执行语句
例1
delimiter $$createprocedurexscj.compar(ink1integer,ink2integer,outk3char(6))beginifk1>k2thensetk3{=}^{\prime }大{于}^{\prime };elseifk1=k2thensetk3{=}^{\prime }等{于}^{\prime };elsesetk3{=}^{\prime }小{于}^{\prime };endif;end$$
delimiter;
(2)case语句 // 相当与 switch-case语句
用法一
case 表达式
when 值1 then 执行语句;
when 值2 then 执行语句;
else 执行语句
end case
用法二
case
when 条件1 then 执行语句;
when 条件2 then 执行语句;
else 执行语句
end case
例2
delimiter $$createprocedurexscj.result(instrvarchar(4),outsexvarchar(4))begincasestrwhe{n}^{\prime }{m}^{\prime }thensetsex{=}^{\prime }{男}^{\prime };whe{n}^{\prime }{f}^{\prime }thensetsex{=}^{\prime }{女}^{\prime };elsesetsex{=}^{\prime }{无}^{\prime };endcase;end$$
delimiter;

  (3)循环语句 
      while 
      repeat
      loop
      *1*while语句
        while 条件 do
        语句序列
        end while 
          例
          delimiter $$
          create procedure dowhile()
          begin
              declare v1 int default 5;
              while v1 > 0 do
              set v1 = v1 -1;
          end while;
          end$$
          delimiter;
      *2*repeat语句   相当于 do-while
        repeat 
          语句序列s
          until 条件
        end repeat;
          例
          repert
            set v1 = v1 - 1;
            until v1 < 1
          end repert;
      *3*loop语句
          例
          delimiter$$
          create procedure doloop()
          begin
            set @a = 10;
            label :loop
              set @a = @a - 1;
              if @a < 0 then
                leave label;
              end if;
            end loop label;
          end$$
          delimiter;
5)处理程序和条件
    SQL处理过程中如果出错 就会停止执行
    为了防止这样 就使用处理程序   //相当于异常处理

    用 declare (处理程序类型 continue exit undo)  handler for 类型及编号
    例
    use xscj;
    delimiter $$
    create procedure my_insert()
    begin
        declare continue handler for sqlstate '23000' set @x2 = 1;
        set @x = 2;
        insert into xs values('081101','王民','计算机',1,'1994-02-10',50,null,null);
        set @x=3;
    end$$
    delimiter;

6)游标
    处理select 返回 多行数据时 利用游标
    游标相当于指针
    游标须在 过程或函数中调用
    有
    declare cursor 声明游标
    open cursor 打开游标
    fetch cursor读取游标
    close cursor 关闭游标

    （1）声明游标
        declare 游标名 cursor for select 语句
        例
        declare xs_curl cursor for
          select 学号,姓名,性别,出生日期,总学分
            from xs
        where 专业名 = '计算机';
    (2)打开游标
        open 游标名
   （3）读取数据

f elch 游标名 into 变量名

   （4）关闭游标
        close 游标名

    例
    delimiter $$
    create proceddure compute(out number integer)
    begin
          declare xh char(6);
          declare found booleam default ture;
          declare numbber_xs cursor for
          select 学号 from xs;
    declare continue handler for not found
        set found  = false;
        set number = 0;
        open number_xs;
        fetch numner_xs into xh;
        while found do
        set number = number + 1;
        fetch number_xs into xh;
        end while;
        close number_xs;
    end$$
    delimiter;

3.储存过程调用，删除，修改
1)调用
用法
call 储存过程名（参数）
删除
drop procedure 储存过程名
drop procedure if exists dowhile
2)修改
（1）先删除后修改
例
delimiter $$dropprocedureifexistsd{o}_{q}uery;createprocedured{o}_{q}urey()beginselect\ast fromxs;end$$
delimiter;
(2)直接修改储存过程
delimiter$$alterprocedured{o}_{q}uery()beginselect\ast fromxs;end$$
delimiter;

二、存储函数

1.创建存储函数
create function
查看数据库中有哪些存储函数
show function status
例;
delimiter $$createfunctionn{u}_o{f}_{x}s()returnsinteger//返回的数据类型beginreturn(selectcount(\ast )fromxs);//包含SELECT时只能返回一行一列的一个值end$$
delimiter;

2.函数的调用
利用 select 调用
3.函数的删除
利用 drop function 函数名 删除
4.修改函数声明 alter function 函数名 （参数）
修改函数体用先删除在重写来修改

三、触发器

指定关联到一个表的对象  使用关联到一个表
1.查看触发器 show triggers
2.创建触发器 create trigger 触发器名 触发时刻 触发事件
    on 表名 for each row 触发器动作
3.触发时刻（激活后执行多个改变用after  新数据是否满足使用条件before）
4.触发事件 
    insert 插入时激活触发器
    update 更改行时激活触发器
    delete 删除行时加入触发器
    for each row 受触发器影响的每一行都要激活触发器
5.触发器动作
    触发时执行的动作 多行时使用 begin end
    注：触发器不可以返回结果  不可以使用select返回

    使用列名时
    加注 new.  或   old.

    例
    delimiter $$
    create trigger xs_delete after delete
              on xs for each row
    begin
        delete from xs_kc where 学号 = old.学号;
    end$$
    delimiter;


    触发器涉及对出发关联表跟新操作时 只能使用before
    例
    触发器中可以调用存储过程
    delimiter$$
    create procedure changes()
    begin
      replace into xsl select *from xs;
    end$$
    delimiter;
    create trigger xs1_changes after insert
      on xs for each row
    call changes();
6.删除触发器
    drop trigger 触发器名

四、事件 可能是考点

  用来定时的触发某些过程
  创建事件之前需要打开调度器

1.创建事件
create event 时间名
on schedule 时间描述（at+某个时刻 时间间隔 ）||（every 在指定时间区间多久发生一次 （指定区间用starts 开始 end 结束））
do 执行语句
例
立刻执行
use xscj
create event direct
on schedule at now()
do
insert into xs
valuse(‘091103’,‘张建’,‘软件工程’,1,‘1994-06-05’,50,null,null);
只执行一次的事建 执行后立刻消失
2.修改事件
利用alter events 修改1
语法
alter events 事件名
rename to 新事件名;
3.删除事件
drop event 事件名

五、视图

也是一种表是用户视角中的表，来源是数据库中的其他表

1.创建视图
第一步 create view 授权创建数据视图
第二步 替换已有的同名视图 or replace
第三步 定义处理视图的方法 1.默认的undefined
2.merge
3.temptable 试图结果被置于临时表中
可以使用order by 定义排序方式
创建视图
例
use xscj
create view cs_kc_avg(num,score_avg)
as
select 学号,avg(成绩)
from cs_kc
group by 学号;
2.查询视图
当作表进行查询
3.更新视图
首先保证是可更新列
插入数据 用 insert
更改数据 用 update
删除数据 delete
4.修改视图
alter
修改表
删除表
drop

注：数据库中 有 表，视图，存储过程，存储函数，触发器，事件组成，表中列可以增加索引加快搜索速度

总、MYSQL数据库备份与恢复

  用来保证数据安全
  1.通过导出数据或复制表文件
  2.二进制日之文件 保存更新数据的所有子句
  3.利用主从数据库模式。

一、通过导出数据或复制表文件

1.数据导出
select * into outfile 文件名 导出选项 （FILEDS/LINes）
FiLEds  子句
（1） TERMINATED by 符号 指定符号作为 两个字段的分隔符
（2）  ENCLOSED BY '符号' 包裹文件中字符值的符号
（3） ECSAPED BY 定义文中转义符号
LINes 子句
terminated by 符号 定义一行的结束符
2.表数据导入（将文件中的数据保存在数据库中）
load data      infile '文件名.扩展名'
例
use xscj;
select *from kc
    into outfile 'd:/file/myfile1.tex'
  fields terminated by ','
        optionally enclosed by '"'
lines terminated by '?';  
3.可以直接复制表 复制.frm .MYD .MYI文件

二、日志文件

1.启用日志

log-bin= 文件绝对路径
关闭服务器 
net stop mysql
打开服务器
net start mysql

2.利用 mysqlbinlog 处理日志
mysqlbinlog 文件名
3.查看内容 并保存
mysqlbinlog bin_log.000001>保存查看日志内容
4.使用日志恢复
mysqlbinlog bin_log.000001|mysql -uroot -p密码

总、MYSQL安全

一、用户管理

root mysql 最高权限
user SQL用户
1.添加用户 create user
用法
create use
‘用户名’@‘主机名’ identified by ‘密码’;
密码可以指定混编传输 密码前加 password

  例：
  create user
    'king'@'localhost' identifien by 'queen'
    'palo'@'localhost' identifien by '530415'

2.查用户记录
use mysql
show table;
select *from user;
用户名相同主机名不同 视为不同用户
注：刚建立的数据库 需要权限才能访问root 用户的数据

3.删除用户
drop user 用户
例 drop user palo@loaclhost;
4.修改用户名，密码

1)修改名字
rename user
用法 rename user 老用户 to 新用户
需要权限 create user updata 权限
例
rename user
‘king’@‘loaclhost’to’ken’@‘loaclost’;
2)修改密码
set password for 用户 = password(‘qen’);

二、权限控制

1.权限授予
1）列权限 表中具体列
2）表权限 表中数据
3）数据库权限 库中表
4）用户权限 数据库
授予权限子句 grant
grant 权限类型
on 被操作的数据 列 据 表 库
to 用户 （对象类型table function procedure ）（表名 * . 数据库名.）
1）授予表权限 和列权限
select
insert
delete
update 修改数据的权利
references 创建外键参照一个表的权力
create 创建表的权力
alter 修改表的权力
index 添加索引的权利
drop 删除表的权利
all 所有权利
注：授予权限时 on 后加表明
例1
use xscj;
grant select
on xs
to ken@localhost
注：对不存在的用户授予权限
授予之后创建该用户
例2
grant select,update
on xs
to liu@loaclhost identified by ‘lpwd’;
2)授予数据库权限
select
insert
delete
update
references
create
alter
index
drop
creaate temporary tables
create view
show view
注：授予权限时 on 后加 db_name.*
例3
grant select
on xscj.*
to ken@localhost;

    use xscj;
    grant all
      on *
      to ken@localhost

3)授予用户权限
  create user 创建和删除新用户的权利
  show databases 查看数据库定义的权利
  创建时 on 后 *.*

2.权限的转移和限制
授予字句后添加 with grant option
指该用户可以将自己的权限授予其他用户
3.权限回收
1)收回部分权限
revoke 权限类型
on 操作对象
from 用户

  表维护子句
  analyze table 检查索引
  check table 检查表
  optimize table 重利用空间