导图社区 Mysql数据库技术
- 98
- 7
- 0
- 举报
Mysql数据库技术
数据库入门，MySQL是一种开放源代码的关系型数据库管理系统，使用最常用的数据库管理语言进行数据库管理。
编辑于2022-01-13 10:49:44- 管理系统
- 相似推荐
- 大纲
Mysql数据库技术
1.Mysql安装及卸载
系统命令
win+R：运行
regedit：注册表
cmd：命令行
sc delete "服务名"：删除服务名（以管理员身份运行命令行）
net start：查看Windows系统已经启动的服务
net stop mysql57：关闭服务
net start mysql57：启动服务
查看默认存储引擎：show variables like '%storage_engine%'
2.数据库操作
创建
create {database| schema} [if not exsits] database_name;
"[ ]"表示可选，"|"表示或者
查看
show databases;
选择
use database_name;
删除
drop database_name;
3.数据库类型
整数型：int
小数类型：float、double、dec（M，D）
字符串类型：
char、varchar
text
日期/时间型：date、datetime
二进制类型：
binary
blob
复合型：set
4.创建数据表
创建数据表：create table 表名 （列名1 类型，列名2 类型，……）；
删除数据表：drop table 表名；
复制表格： create table 表名1 like 表名2；仅复制框架，不复制表内记录
修改表名：alter table 旧表名 rename 新表名；
添加字段： alter table 表名 add 新列名 类型 [frist|after 列名]；
删除字段：alter table 表名 drop 列名；
修改字段：alter table 表名 modify 列名1 数据类型 frist |after 列名2；
5.完整性约束
关键字：
not null
default
unique key
auto increment
primary key
foreign key
非空及默认值设置
新建：create table 表名 （列名1 数据类型 not null | default‘默认值’，……）
更改：alter table 表名 modify 列名 数据类型 not null | default ‘默认值’；
唯一键与自增设置
更改：alter table 表名 modify 列名 数据类型 unique | auto increment；
注意事项：
必须整数类型才能自增；
先定义主键或唯一键，才可以设置自增；
alter table 表名 modify 列名 类型 auto increment = default_value,为自增设置初始值。
删除：
①alter table 表名 modify 列名 数据类型；删除自增
②alter table 表名 drop index 列名；删除唯一键
主键与外键
主键
单字段主键
创建：create table 表名 （列名 数据类型 primary key，……）；
修改：create table 表名 add [constraint 约束名] primary key （列名）；最好不加约束名
删除：alter table 表名 drop primary key；
多字段主键
创建：create table 表名 （列名 数据类型，……[constraint 约束名] primary key（列名1，列名2……）
修改：alter table 表名 add [constraint 约束名] primary key （列名1，列名2，……）
外键
创建：create table 表名 （列名1 数据类型，……[constraint 约束名] foreign key （外键字段名） references 表名 （列名））；最好设置约束名称。
更改：alter table 表名 add [constraint 约束名] foreign key （外键字段名） references 表名 （列名）；最好设置约束名称。
删除：alter table 表名 drop foreign key 约束名；
①查找外键名称：show create table 表名；
② 彻底删除外键，应先删除一个在建立外键时自动创建的索引。索引名称一般为自定义的约束名称或字段名称。查看索引信息：show indexes from 表名；
6.数据表操作
查
基本查询
select distinct 列名 as 新名称 from 表名；
条件查询
关键字
① 连接条件：and or
②指定范围：between and
③空值：is null，is not null
④集合：in，not in
⑤模糊查询：like ，%， _
格式
select 列名1，列名2…… 列名n from 表名 where 条件 limit [offset] row_count limit ,限制输出记录的个数；offset，指定初始位置；row_count，指定查询的行数
例句：
①select book_name,book_price from t_book where book_price between 50 and 100;
②select isbn,book_name,press_id from t_book where press_id in (12,14);
③select isbn,book_name,book_price from t_book where book_name like '%共产党%'；
④select reader_id from t_reader where reader_name is null limit 1,2;
多表查询
内连接查询 （仅保留匹配的数据）
自连接查询（表A与表A）
select A.列名1,B.列名2 from 表名A inner join 表名B on A.列名X=B.列名X where 条件；
select 列名 from 表名A,表名B where A.列名=B.列名 and A.列名='……' and B.列名='……'；
实例：1.在表（sno，sname，major）中查询与‘王丽’在同一major的学生； 2.在表t_borrow_record(borrow_id,reader_id,ISBN)中至少借阅两种书的 reader_id;
等值连接查询（表A与表B）
select 表A.列名1，表B.列名2 from 表A inner join 表B on 表A.列名X
实例：
实例：查询女读者的所有的借阅信息
select t_borrow_record.* from t_reader inner join t_borrow_record on t_reader.reader_id=t_borrow_record.reader_id where t_reader.reader_sex='女'；
不等值连接查询
select 表A.列名1，表B.列名2 from 表A inner join 表B on 表A.列名X between 表B.列名M and 表B.列名N；
实例：
select a.reader_id,a.reader_name,a.reader_birthday,b.age_group from t_reader a inner join t_age_group b on timestrampdiff( year,a.reader_birthday,curdate()) between b.age_min and b.age_max;
//时间差函数：timestrampdiff（interval,datetime_expr1,datetime_expr2）返回日期datetime_expr1和datetime_expr2之间的整数差。其结果的单位由interva参数给出。该参数必须是day、week、month、year等其中的一个。
外连接查询 （保留匹配的数据+左或右表所有数据）
左连接查询
select 列名1，列名2，……列名n from 表名1 left join 表名2 on 连接条件；
右连接查询
select 列名1，列名2，……列名n from 表名1 right join 表名2 on 连接条件；
语句：
复制表格：create table 新表名 select * from 旧表名；
删除记录：delete from 表名 where 条件
子查询
in
select table1.col_namen from table1 where col_name1 in|not in (select col_name1 from table2 where condition);
select t_borrow_record.* from t_borrow_record where reader_id in (select reader_id from t_reader where reader_sex='女');
exisits
select table1.col_namen from table1 where exists | not exists (select col_name1 from table2 where condition);
select t_borrow_record.* from t_borrow_record where exists (select reader_id from t_reader where reader_sex='女' and t_borrow_record.reader_id=reader_id.reader_id);
exists子查询用来检查子查询是否有查询结果返回，只要返回exists的结果即为true，外查询语句将进行查询；反之结果为false，此时外层语句将不进行查询。
'>any'='>min' '>all'='>max' '<any'='<max' '<all'='<min'
any
select table1.col_namen from table1 where col_name1 >=any | <=any (select col_name1 from table2 where condition);
all
select table1.col_namen from table1 where col_name1 >=all | <=all (select col_name1 from table2 where condition);
增
插入完整记录
insert into table_name values (values1,values2……valuesn);
插入部分记录
insert into table_name (col_name1,`col_name2……col_namen） values (values1,values2……valuesn)；
插入记录一部分
insert into table_name （col_name1,col_name2……col_namen）， （col_name11,col_name21……col_namen1）， （col_name12,col_name22……col_namen2）；//是否缺少values
插入别表记录
insert into table_name1 (col_name1,`col_name2……col_namen） select name //name代表什么呢？ （col_name1,col_name2……col_namen） from table_name2;
改
update table_name set col_name1=values1, col_name2=values2, …… col_namen=valuesn where condition;
删
delete from table_name where condition;
主键与外键
约束
当要删除或更新父表中被参照列上在外键中出现的值时，拒绝对父表的操作；
从父表删除或更新行时自动删除或更新子表中匹配的行； //子表代表什么？
当从父表删除或更新行时，设置子表中与之对应的外键列为null。如果外键列没有指定not null限定词，这就是合法的。
不采取动作，如果有一个相关的外键值在被参考的表里，删除或更新父表中主要键值的企图不被允许，和restrict一样。
删除模式
限制删除
留空删除
级联删除
还需琢磨
7.索引与视图
索引
分类
普通索引 index
唯一索引 unique index
全文索引 fulltext index
适用情况
适合的情况
经常查询的字段：where子句中出现的字段
在分组的字段：group by子句中出现的字段
存在依赖关系的子表与父表之间的联合查询：主键与外键字段
设置唯一完整性约束的字段
需要排序、分组和表连接操作的字段
不适合情况
查询中很少被使用的字段
拥有许多重复值的字段
经常更新的表
数据量小的表
语法
创建
新表
create table table_name ( col_name col_definition, col_name col_definition, …… col_name col_definition, [unique|fulltext] index index_name (col_name asc |desc);
已有表
create [unique | fulltext] index index_name on table_name (col_name asc | desc);
alter table table_name add [unique | fulltext] index index_name (col_name asc |desc );
查看
show index from 表名；
视图
语法
创建
ceate VIEW view_name as select 语句 [with[cascaded|local]check option];
查看
show table status [like 'view_name'];
show table status [like 'view_name'];
show create view view_name;
describe | desc view_name;
修改
alter view view_name as select 语句 [with [cascaded|local] check option];
删除
drop view if exists view_name [cascaded | restrict]
8.存储过程的建立
定义变量
会话用户变量
set @var=值1 [,@var=值2,……]；
实例：set @price=5.5；
select 值1 into var1 [,值2 into var2，……]
实例： select min (book_price) into para1,max(book_price) into para2 ,avg(book_price) into para3;
局部变量
declare var1 [,var2]……数据类型 [default 默认值]; declare只能用于begin...end的开头部分定义局部变量
实例：declare num int default 0;
程序结构
顺序
分支
if语句
if 条件1 then 语句序列1 [elseif 条件2 then 语句序列2] ... [else 语句序列n] end if；
case语句
case when 条件1 then 语句序列1 [when 条件2 then 语句序列2] ... [else 语句序列n] end case；
case 表达式 when 值1 then 语句序列1 [when 值2 then 语句序列2] ... [esle 语句序列n] end case；
循环
while
[开始标号:] while 条件 do 程序段 end while [结束标号]；
loop
[开始符号:] loop 程序段 end loop [结束标号]；
repeat
[开始标号:] repeat 程序段 until 条件 end repeat [结束符号]；
语法
操作
创建
create procedure procedure_name ([procedure_parameter[,......]]) [characteristic ...... ] routine_boby
//procedure_name：名称，procedure_parameter：参数，characteristic：特性，routine_boby：SQL语句代码，可以用begin……end来标志SQL语句的开始和结束。
查看状态
show procedure status like 'procedure_name'
查看定义
show create procedure procedure_name;
修改
alter procedure procedure_name [characteristic......]
这个语法用于修改存储过程的某些特征，比如读写权限。如要修改存储过程的内容，可以先删除该存储过程，再重新创建。
删除
drop procedure procedure_name;
语句结构
delimiter $ begin ...... end $ delimiter;
带参数
参数
out
实例
delimiter $ create procedure p_out (out para_min float, out para_max float, out para_avg float) comment "查询图书的最高价格，最低价格和平均价格" begin select min(book_price), max(book_price), avg(book_price) into para_min,para_max,para_avg from t_book; end $ delimiter;
调用：call p_out (@para_min,@para_max,@para_avg); select @para_min,@para_max,@para_avg;
inout
实例
delimiter $ create procedure p_inout (inout price float) comment '对某本书的价格增加10元' begin set price=price+10; end $ delimiter;
调用：set @currentprice = 5.5; call p_inout (@currentprice); select (@currentprice);
in
实例
delimiter $ create procedure p_in (in pid char(6)) comment '查询某个ID对应的读者姓名' begin select reader_name from t_reader where reader_id=pid; end $ delimiter;
调用：call p_in('101101');
带控制条件
循环条件实例
delimiter $ create procedure p2 (out total int) begin declare num int default 0; set total =0; while num<=100 do set num =num+1; set total = total + num; end while; end $ delimiter;
调用：call p2 (@sum); select @sum;
IF分支条件实例
delimiter $ create procedure compare_age (out age1 int,out age2 int,in name1 varchar(50),in name2 varchar(50),out result char(20)) begin select year(curdate())-year(reader_birthday) into age1 from t-reader where reader_name=name1; select year(curdate())-year(reader_birthday) into age2 from t_reader where reader_name=name2; if age1>age2 then set result = concat (name1,'的年龄大于',name2); elseif age1=age2 then set result =concat (name1,'与',name2,'同岁'); else set result = concat (name1,'的年龄小于',name2); end if; end $ delimiter;
调用：call compare age (@age1,@age2,'','',@result); select @age1,@age2,@result;
CASE分支条件实例
delimiter $ create procedure poc_price (in bname varchar(50),out price float, out result varchar(10)) begin select book_price into price from t_book where book_name= bname; case when price >=100 then set result = '昂贵'； when price <=50 then set result ='便宜'； when price is null then set result = '无此书籍'； else set result ='中等'； end case； end $ delimiter;
调用： call proc_price ('',@price,@b); select @price ,@b;
创建函数
语法格式
create funtion func_name (parameters) returns type body
案例分析
创建一个函数，查询某本图书的副本量
delimiter $ create funtion func_bookcopy (bookid char(17)) return int(3) begin return (select book_copy from t_book where isbn = bookid); end $ delimiter;
调用函数：select func_bookcopy ('978-7-04-034745-6');
9.触发器的创建
语法
创建
create trigger trigger_name trigger_time trigger_event on table_name for each row [trigger_order] trigger_body
//触发器执行时间有after、before。触发器事件为insert、update和delete. //table_name表示触发作用的表名；for each row表示，在表中对任意一条记录进行操作都会触发。 //具体要执行的SQL语句。
查看所有触发器
show triggers;
查看触发器详细信息
show create trigger trigger_name;
删除触发器
drop trigger trigger_name;
创建
插入触发器
实例一：每次想图书馆t_book表中出入一条记录后，自动向该日志t_log中插入一条记录 create table t_log （logno int auto_increment primary key，tname varchar（20）， logtime datetime） create trigger trigger_log after insert on t_book1 for each row insert into t_log （tname，logtime） values （'t_book',now());
-----向表t_book1中插入数据 insert into t_book1 (isbn,book_name) values ('978-7-115-4563-2','Mysql数据库管理与开发');
-----查看日志表的数据 select * from t_log;
实例二：创建一个t_borrow_record表的插入触发器,当向t_borrow_record表中插入一条数据时,该记录对应的图书的库存量自动-1,对应的读者的借阅量自动+1. create trigger trigger_insert2 after insert on t_borrow_record for each row begin update t_reader set reader_borrowtotal =reader_borrowtotal+1 where reader_id =new.reader_id; update t_book set book_inventory =book_inventory-1 where isbn= new.isbn; end;
------执行一条插入语句，测试 insert into t_borrow_record (reader_id,isbn) values ('978-7-115-45663-2','Mysql数据库管理与开发');
------查看日志表数据 select * from t_log;
更新触发器
实例：创建一个t_press表的更新触发器，当更新t_press表中某一条记录的出版信息后，获取更新前后的出版社名称，并判断是否一致。 create trigger trigger_u2 after update on t_press for each row begin if（new.press_name= old.press_name) then select '相同' into @result； else select '不同' into @result； end if； end；
-----更新记录，测试触发器效果 update t_press set press_name = '清华大学出版社’ where press_id =‘101’; select @result;
删除触发器
实例：创建一个delete触发器，删除t_reader1中的数据时，将对应的t_borrow_record中的数据删除。 create trigger trigger_delete after delete on t_reader1 for each row begin delete from t_borrow_record1 where reader_id =old.reader_id; end；
------删除前先查询‘101101’的借阅数据 select * from t_borrow_record1 where reader_id ='101101';
------删除t_reader1中的数据 delete from t_reader1 where reader_id ='101101';
------再次查询‘101101’的查询数据，进行对比。 select * from t_borrow_record1 where reader_id =‘101101’；
10.数据库管理
管理事务
控制语法
start transaction | begin [work] commit [work] [and [no] chain] [[no] release] rollback [work] [and [no] chain] [[no] release] set autocommit ={0 | 1}
①start transaction、begin语句可以开始一项新的事务 ②commit、rollback语句用来定义提交、回滚事务 ③chain、release语句用来定义在事务提交或回滚之后的操作，chain会立即启动一个新事务，并且和刚才的事务具有相同的隔离级别，release则会断开和客户端的连接 ④set autocommit可以修改当前连接的提交方式，如果设置了set autocommit=0，则设置之后的所有事务都需要通过明确的命令进行提交或者回滚
实例
在向t_borrow_record插入一条记录，t_book库存对应的书自动减1，读者借阅的对应书籍自动加1，如果判断库存的书籍小于0，则将事务回滚，不能向t_borrow_record插入记录，库存不能减1，借书量也不能加1。
begin； insert into t_borrow_record values (借书序号，读者id，isdn号，借书日期)； update t_reader set reader_borrowtotal= reader_borrowtotal +1 where reader_name = '王明峰'； rollback；
begin； insert into t_borrow_record values (借书序号，读者id，isdn号，借书日期)； update t_reader set reader_borrowtotal= reader_borrowtotal +1 where reader_name = '王明峰'； update t_book set book_inventory = book _ inventory -1 where book_name ='Mysql数据库教程'; commit；
管理权限
权限表
User表
Db表、host表
table_priv表、Columns_priv表
语法
授权控制
grant priv_type [(Column_list)] on table to user [identified by [password] 'password' ] [, user [identified by [password] 'password']] ... [with with-option [with_option]...]
说明：①新创建的用户可以设置密码； ②with关键字之后有5个可选项，用于设置查询频率，该字段使用不多； ③赋予用户所有权限： grant all privileges on [dtabase_name.table_name] to 'username' @ 'hostname' with grant option;
查看权限
show grant for 'username' @ 'hostname';
取消授权
revoke priv_type [(column_list)] on table from user1 [identified by [password] 'password'] ... [,user2 [identified by [password]'password']]
回收全部权限
revoke all privileges，grant option from 'username '@'hostname';
刷新权限
flush privileges;
实例
create user '张同学' @ 'localhost' identified by '123456'; grant select on db_library.t_book to '张同学' @ 'localhost' identified by '123456';
数据库的备份与恢复
备份
右击数据库名——转存SQL文件——“数据和结构”——定义文件名称——点击保存
恢复
新建被删除的数据库——运行SQL文件——选择备份文件——点击开始 备注：新建数据库中，字符集为utf8，排序规则为utf8_general_ci
定时备份
自动运行——新建批处理作业——点击数据库——点击保存——输入文件名——设置任务计划——触发器——新建——指定周期
管理日志
错误日志
查询日志
慢查询日志
二进制日志