MySQL学习笔记
数据备份
备份
| mysqldump -u user -h host -ppassword dbName [tableName[,...]]>fileName.sql;
user 用户名；host 主机名；dbName 数据库；tableName表名；fileName.sql另存的文件名及后缀；
■备份一个数据库
mysqldump -u root -p myDatabase>C:\backup\myBackUp_20220901.sql enter password 666;
■备份数据表
mysqldump -u user -h host -p dbName [tableName[,...]]>fileName.sql;
mydqldump -u ‘root’-h ‘localhost’-p ‘myDB’student>C:\Users\Administrator\Desktop\backup.sql;
■备份多个数据库
mysqldump -u root -h host -p --databases dbName[,...]>fileName.sql;
databases后面至少指定一个数据库名称，多个用逗号隔开；
mysqldump -u root -h ‘localhost’ -p --databases db1,db2,db3>threeBackUp.sql;
■备份所有数据库
mysqldump -u root -h host -p --all-databases >fileName.sql;
mysqldump -u root -h ‘localhost’-p --all-databases >allBackUp.sql;
■MyISAM引擎备份插件（Unix系统）
mysqlhotcopy dbName[,...] /path/to/new.directory;
mysqlhotcopy -u root -p test /usr/backup
| 还原
| ■用C:\personOut.txt;
mysql -u root -p --html --execute=”select*from person;” test>C:\personOut.html;
mysql -u root -p --xml --execute=”select*from person;” test>C:\personOut.xml;
| 导入
| load data infile ‘fileName.txt’int table tableName [options] [ignore number lines]
--optoins 同上
load data infile ‘C:\person.txt’ into table test.person;
还原之前，应先删除原有数据；use test; delete from person;
load data infile ‘C:\person.txt’ into table test.person
fields terminated by ‘,’ enclosed by ‘\”’ escaped by’\’’
lines terminated by ‘\r\n’;
mysqlimport -u root-p dbName fileName.txt [options];
C:\>mysqlinport -u root -p test C:\backup\person.txt --fields-terminated-by=,--fields-optionally-enclosed-by=\” --fields-escaped-by=? --lines-terminated-by=\r\n;
|
用户管理
概述
| MySQL是一个多用户数据库，可以为不同的用户指定不同的权限；
root用户=超级管理员：拥有所有权限；
权限：创建用户、删除用户、修改用户密码；
普通用户：拥有被授予的权限；
| 信息表
| 信息表：user,db,host,tables_priv,columns_priv,procs_priv;
■user表的权限是全局级的，user表有删除权限，则可以删除所有数据库的所有数据；
user表有42个字段，用户列、权限列、安全列、资源控制列；
用户列：3字段，包括Host、User、Password，主机名、用户名、密码，3个值都匹配才能连接；
权限列：Privilege英 /ˈprɪvəlɪdʒ/ 美 /ˈprɪvəlɪdʒ/n. 特权，权限；
可以用grant或update修改权限列；
普通权限（操作数据库）：查询、修改等；
高级权限（管理数据库）：关闭服务器、加载用户；
安全列：6字段，2个ssl用于加密，2个x509用于标识用户，plugin用于验证用户身份的插件；
资源控制列：4字段，控制每小时的查询、更新、连接次数和同时连接次数；
|
■db表：对某个数据库的操作权限；
■host表：对主机的操作权限；
■tables_priv表：对表的操作权限；
■columns_priv表：对列的操作权限；
select*from mysql.tables_priv;
select user,password,host,select_priv,update_priv from mysql.user;
select*from mysql.db;
|
| ■procs_priv表：对存储过程和存储函数的权限；
| 登录
| -h主机名（host），指定主机名或IP，不指定默认localhost；
-u用户名（user）；
-p密码（password），密码和-p之间不能有空格；
-P端口号（Port）默认3306；
数据库名，可以再命令最后指定数据库名称；
-e要执行的sql语句，登录→执行→退出；
mysql --user=tom --password=guess db_name;
mysql -u tom -pguess db_anme;-p和密码之间不能有空格；
mysql -uroot -p -hlocalhost test;
mysql-uroot-p-hlocalhost test -e”desc person;”
| 用户
| ■■■创建用户create user
create user userSpecification [,...]
userSpecification:user@host
[identified by [password]’password’|identified with authPlugin[as ‘authString’] ]
l user 表示用户名，host表示允许登录的主机名称；
l identified by用来设置登录密码；
l [password]表示使用哈希值密码；
l ’password’表示使用普通明文密码；
l identified with为用户指定身份验证插件；
l authPlugin身份验证插件名称，可以是单引号字符串或双引号字符串；
l authString’该参数传递给身份验证插件，由该插件解释参数的意义；
使用create user必须要有全局的create user权限，或者数据库的insert权限；
创建后，会在user表中添加一条记录，但是该账户没有任何权限；
如果待添加账户已经存在，则会返回错误；
create user ‘jeffrey’@’localhost’ indentified by ‘mypass’;账号+密码
create user ‘jeffrey’@’localhost’;不指定密码，则登录不需要密码
create user ‘jeffrey’;不指定主机，则可访问所有主机；
如果只指定用户名，主机名则默认‘%’，表示对所有主机开放；
select password(‘mypass’);显示密码的哈希值
显示结果：password('mypass') *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4
create user ‘jeffrey’@’localhost’
identified by password ‘*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4’;
使用哈希值设置密码；
create user ‘jeffrey’@’localhost’ idendified with my_auth_plugin;
使用插件进行身份验证；
identified by和identified with是互斥的，只能二选一；
■■■创建用户grant（推荐）
grant privileges on db.table to user@host [idendified by ‘password’][,...]
[with grant option] ;
privileges 赋予用户的权限类型；
db.table 表示用户权限作用的表；
idendified by设置密码；‘password’表示用户密码；
[with grant option]赋予用户grant权限，该用户可以对其他用户赋予权限；
select host,user,select_priv,update_priv from mysql.user where user=’testUser’;
■■■创建用户 编辑user表
insert into mysql.user(host,user,password,[privlegeList])
values(‘host’,’userName’,password(‘password’),[privilegeValueList]);
password()函数为密码的加密函数；
privilegeValueList的值只能是‘Y’或‘N’；
insert into user(host,user,password) values(‘localhost’,’testUser’,password(‘123456’));
mysql> insert into mysql. user(host,user,password) values('localhost','testUser',password('666'));
ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value
flush privileges;重新加载授权表；
insert into需要使用加密函数password()，而grant会自动加密，不需要加密函数；
■■■删除用户
drop user[,...];
drop user ‘user’@’localhost’;删除user在本地登录权限；
drop user;删除来自所有授权表的账户权限记录；
被删除用户打开时，drop不会立即生效，等到用户关闭对话后才会生效；
当用户再次尝试登录时，则登录失败；
delete from mysql.user where host=’hostName’ and user=’userName’;
delete from mysql.user where host=’localhost’ and user=’test’;
| 密码
| ■■■root用户改密码
①用mysqladmin 改密码
mysqladmin -u username -h localhost -p password ‘newPassword’;
mysqladmin -u root -p password “654321”;新密码需要用双引号；
②通过user表改密码
update mysql.user set password=password(“newPassword”)
where user=’root’and host=’localhost’
update mysql.user set password=password(“645321”);
③用set改密码
set password=password(“666888”);
■■■root修改普通用户密码
set password for ‘user’@’host’=password(“newPassword”);
set password for’test’@’localhost’=password(“666”);
update mysql.user set password=password(“newPassword”)
where user=’userName’and host=’hostName’;
update mysql.user set password=password(“999”)where user=’test’and host=’localhost’;
grant usage on *.* to ‘userName’@’%’idendified by ‘newPassword’;
grant usage on *.* to ‘test’@’localhost’idendified by ‘666’;
■■■普通用户自己改密码
set password=password(‘newPassword’);
■■■忘记密码的解决方案
以skip-grant-tables选项启动时，不加载权限判断，任何用户都能访问数据库；
mysqld --skip-grant-tables;（Windows系统）
mysqld-nt --skip-grant-tables;（Windows系统）
mysqld_safe --skip-grant-tables user=mysql;（Linux系统）
/etc/init.d/mysql start-mysql --skip-grant-tables;（Linux系统）
使用root登录，重新设置密码（XP系统）
① C:\>net stop mysql 停止服务（命令行窗口输入）
② C:\mysqld --skip-grant-tables 启动服务（命令行窗口输入）
③ C:\mysql -u root 无密码登录（打开另外一个命令行窗口）
④ update mysql,user set password=password(‘newPassword’)where user=’root’and host=’localhost’; 重新设置密码；
⑤ flush privileges;重新加载权限表，新的密码才会生效；
关闭输入 mysqld --skip-grant-tables窗口，用新密码登录；
| 权限
| ■■■授权grant
全局层级：grant all on *.*;revoke all on *.*;存储在mysql.user表中
数据库层级：grant all on dbName;revoke all on dbName;存储在mysql.db和mysql.host表中；
表层级：grant all on dbName.tableName; revoke all on dbName.tableName;存储在mysql.tables_priv中；
列层级：存储在mysql.columns_priv表中；当使用revoke时，必须指定与被授权列相同的列；
子程序层级：create routine,alter routine,execute,grant适用于已存储的子程序；这些权限可以授予全局层级和数据库层级；除了create routine外，可以授予子程序层级，存储在mysql.procs_priv表中；
grant privType [(columns)] [,...] on [objectType] table1,table2...
to user [identified by [password]’password’][,...] [with grant option]
grant option:
max_queries_per_hour count每小时查询次数
max_updates_per_hour count每小时更新次数
max_connections_per_hour count每小时连接次数
max_user_connections count同时最大连接数
privType 权限类型；columns权限作用的列，不指定表示作用于整个表；
objectType授权作用的对象类型，有table，function，procedure；
table1,table2...权限作用的表；
user用户账户，用户名+主机名，例如’userName’@’hostName’；
identified by密码；
grant select,insert on *.* to ‘student’@’localhost’ idendified by ‘123456’;
■■■取消权限revoke
revoke取消权限后，用户的账户记录将从db,host,tables_priv,columns_priv表中删除；
但是用户账号不会删除，仍在user表中，想要删除账号，用drop user；
从user表中删除账号前，应该回收所有权限；
revoke all privileges,grant option from ‘userName’@’hostName’[,...]
revoke privType [(columns)] [,...] on table1 [,...] from ‘userName’@’hostName’[,...];
revoke update on *.* from ‘student’@’localhost’;
| 查看
| show grants for ‘userName’@’hostName’;
show grants for ‘student’@’localhost’;
select privilegesList from user where user=’userName’,host=’hostName’;
select tables_priv,update_priv from user where user=’tom’,host=’localhost’;
| 访问
| ①连接核实阶段
MySQL用3个字段检查身份，host,user,password;
②请求核实阶段
对用户将要进行的操作，检查是否有足够的权限来执行；
检查顺序：user表→db表→tables_priv表→columns_priv表；
|
触发器：由事件触发的程序段
定义
| 1. 触发器也是一段程序，相当于事件处理程序EventHandler；
2. 触发器和存储过程一样，都是嵌入到MySQL中的一段程序；
3. 触发器由事件来触发，触发事件包括insert,update,delete;
4. 触发器基于某个表，当该表进行特定操作时，就会激活该触发器；
5. 触发器不需要call来调用，而是由系统自动调用；
| 创建
| create trigger triggerName triggerTime triggerEvent
on tableName for each row triggerBody;
triggerName 触发器名称，用户指定；
triggerTime 触发时机，可以指定为before或after；
triggerEvent 触发事件，包括insert、update、delete；
tableName 建立触发器的表名，即在哪张表上建立触发器；
triggerBody触发程序体；
触发器可以包含多条语句，用begin和end标识；
create talbe account(num int,amount decimal(10,2));
create trigger myTrigger before insert on account for each row set@sum=@sum+new.amount;
create trigger triggerName triggerTime triggerEvent
on tableName for each row triggerBody;
delimiter//
create trigger myTrigger before insert on t1
for each row begin
insert into t2 set a2=new.a1;
delete from t3 where a3=new.a1;
update t4 set set b4=b4+1 where a4 =new.a1;
end
delimiter;
| 自定义语句结束符为//
创建触发器
语句块开始，相当于｛
.
.语句块
.
.
//语句块结束，相当于｝
恢复默认语句结束符；
|
1
| 查看
| show triggers;
select*from information_schema.triggers where condition;
select*from infromation_schema.triggers where trigger_name=’myTrigger’\g;
| 使用
| 当满足触发条件时，系统自动激活触发器，执行相关代码；
| 删除
| drop trigger [dbName.]triggerName;
如果不指定数据库名称，则在当前数据库执行删除操作；
drop trigger test.myTrigger;
|
视图：一个虚拟表；
概述
| 1. 视图中的数据是对现有表数据的引用；
2. 视图是从一个表或者多个表中导出的；
3. 视图中可以使用：select,insert,update,delete,,
4. 视图可以从现有视图中创建，视图一经定义便存储于数据库中；
5. 通过视图看到的数据只是基本表中的数据；
6. 视图中数据修改，基本表中数据也同时修改；
7. 基本表中的数据修改，视图中的数据也同时修改；
| 创建
| 视图创建基于select语句和基本表数据；
create [or replace] [algorithm={undefined|merge|templateble}]
view viewName[{columnList}]
as select statement
[with [cascaded|local] check option]
create 创建新视图，replace替换已有视图；
algorithm选择的算法；
viewName视图名称 ；columnList属性列；select statement选择语句；
[with [cascaded|local] check option]视图更新时保证在视图的权限范围内；
l undefined自动选择算法|
l merge将使用的视图语句和视图定义结合起来，使得视图定义的某一部分取代语句对应的部分|
l templateble将视图结果存入临时表，然后用临时表来执行语句；
Ø cascaded更新视图时需要满足所有相关视图和表的条件；（满足所有条件）
Ø local表示可更新视图时满足该视图本身定义的条件即可；（满足自身条件）
要求具有create view权限和select权限，如果replace要求drop视图权限；
视图属于数据库，默认在当前数据库创建；
要在指定数据库创建视图，需要使用完全限定名daName.viewName;
create view myView as select quantity,price,quantity*price from t;
create view myView(quantity,price,total) as select quantity,price,quantity*price from t;
create view class(id,name,class) as select student.id,student.name,stuInfo.class from student,stuInfo where student.id=stuInfo.id;
| 查看
| describe viewName;
show table status like’viewName’;show table status like ‘viewT’\g;
show create view viewName;show create view myView;
select*from information_schema.views;
| 修改
| create or replace view myView as select*from t;
可以用create or replace修改，语法同创建；
alter view myView as select quantity from t;
| 更新
| update view myView set quantity=5;
insert into t values(3,5);更新表，视图会跟随更新；
delete from myView where price=5;
视图中的删除是通过删除基本表中的数据实现的；
| 删除
| drop view [if exists]viewName[,...] [restrict|cascade]
可以同时删除多个视图，逗号隔开；必须要有drop权限；
drop view if exists myView;
|
存储程序=存储过程+存储函数
创建
| create procedure create function
■■■存储过程
create procedure pName([pParameter])[characteristics...]routineBody;
create procedure 过程名([参数])[特性...]过程体;
参数：[in|out|inout]pParameter输入、输出、可入可出
特性：characteristics
[table] | ①语言特性language sql ②结果是否确定[not]deterministic
③使用SQL语句的限制：{constrains sql|no sql|reads sql data|modifies sql data}
l constrains sql表明子程序包含SQL语句，但是不包含读写数据的语句；（默认）
l no sql表明子程序不包含SQL语句；
l reads sql data子程序包含读写数据的语句；
l modidies sql data包含读写数据的语句；
④指明谁拥有权限来执行sql security{definer|invoker}
l definer表示只有定义者才能执行；（默认）
l invoker表示拥有权限的调用者可以执行；
⑤comment ‘string’注释信息，用来描述存储过程或函数；
|
示例
create prodedure avgAge()
begin
select avg(age) as avgAge from student;
end;
| delimiter //
create procedure proc()
begin
select*from student;
end//
delimiter;
|
delimiter//将MySQL结束符设置为//，保证procedure的完整性，完事后，再用delimiter；将结束符设置为默认值分号；delimiter也可以指定其他符号作为结束符；
delimiter应该避免使用反斜杠\,因为\是SQL的转义字符；
create procedure CountProc (out count int)
begin
select count(*)into count from student;
end;
| create funtion myFunc() returns char(50)
return (select name from student where age=22);
|
■■■存储函数
create function funcName([parameter]) returns type [characteristic...]routineBody
create function funcName([参数]) returns type [特性]函数体
如果定义的返回值类型与实际返回值类型不同，系统会自动转型；
只能对procedure指定参数类型in/out/inout;
不能对function指定参数类型in/out/inout，function参数默认in;
[/td][/tr][tr][td=1,1,93]变量
[/td][td=1,1,796]■■■变量
声明语句：declare varName[,varName2]...dataType[default value];
declare 变量名 dataType[default 默认值];
declear count int default 0;
默认值可以指定为一个常量，也可以指定为一个表达式；
如果不指定默认值，初始值就是null；
赋值语句：set varName=expr[,varName2=expr2]...;
declare a,b,c int;set a=1,b=2;set c=a+b;
select colName[,...]into varName[,...] tableExpr;
declare fruitName char(50);
declare fruitPrice decimal(8,2);
select name,price into fruitName,fruitPrice
from fruits where id=’a1’;
|
[/td][/tr][tr][td=1,1,93]异常
[/td][td=1,1,796]■■■异常条件：将一个名字和一个错误条件相关联
意义：保证过程/函数在遇到警告或错误时能够继续运行，避免程序异常停止；
declare conditionName condition for [conditionType]
l [conditionType]:sqlstate[value]sqlstateValue|mysqlErrorCode
l conditionName 条件名称conditionType条件类型
l sqlstateValue错误代码-长度为5的字符串
l mysqlErrorCode错误代码-数值类型
declare command_not_allowed condition for sqlstate’42000’;
declare command_not_allowed condition for 1148;
■■■定义异常处理程序：
declare handlerType handler for conditionValue[,...] spStatement
handlerType:continue|exit|undo
conditionValue:sqlstate[value]sqlstateValue
|conditionName|sqlWarning|notFound|sqlException|mySqlErrorCode
handlerType错误处理方式，参数可取3个值continue|exit|undo
l continue表示不处理错误，继续执行|
l exit表示遇到错误马上推出|
l undo表示遇到错误后，撤回之前的操作，MySQL中断暂时不支持这样的操作；
conditionValue表示错误类型，可以取值如下：
l sqlstate[value]sqlstateValue包含5个字符的错误字符串；
l conditionName表示declare condition定义的错误条件名称；
l sqlWarning匹配所有以01开头的sqlstate错误代码；
l notFound匹配所有以02开头的sqlstate错误代码；
l sqlException匹配所有未被sqlWarning和notFound匹配的错误代码；
l mySqlErrorCode匹配数值型错误代码；
① 捕获字符串错误码sqlStateValue
declare continue handler for sqlstate’42s02’set@info=’no_such_table’;
② 捕获数字错误码mysqlErrorCode
declare continue handler for 1146 set@info’no_such_table’;
③ 捕获自定义错误码
declare no_such_table conditon for 1146;
delare continu handler for no_such_table set@info=’no_such_table’;
④ 捕获警告错误码sqlWarning
declare exit handler for sqlwarning set@info=’error’;
⑤ 捕获未找到错误码notFound
declare exit handler for not found set@info=’no_such_table’;
⑥ 捕获数据库异常错误码sqlException
declare exit handler for sqlexception set @info=’error’;
[/td][/tr][tr][td=1,1,93]光标
[/td][td=1,1,796]光标：用来逐条读取大量数据
顺序：声明条件和变量→声明光标→声明处理程序
顺序：声明declare→打开open→使用fetch→关闭close
declare cursorName cursor for selectStatement
declare cursorFruit sursor for select name,price from fruits;
open cursorName;open cursorFruit;
fetch cursorName into varName[,varName2...]
fetch cursorFruit into name ,price;
close cursorName;close cursorFruit;
[/td][/tr][tr][td=1,1,93]流程
[/td][td=1,1,796]流程控制：条件转移语句
if,case,loop,while,leave,iterate,repeat,,,
每个流程控制，可以包含单个语句，可以包含复合语句（begin end），可以嵌套；
if exprCondition then statement;
[elseif exprCondition then statement2]...;
[else statementN];
end if;
| if val is null
then select ‘val is null’;
else select ‘val is not null’;
end if;
| case expr
when value1 then statement1
[when value2 then statement2]...
[else statementN]
end case;
| case val
when 1 then select’val is 1’;
when 2 then select’val is 2’;
else select ‘val is not 1 or 2’;
end case;
| case
when expr then statement
[when expr2 then statement2]...
[else statementN]
end case;
| case
when val is null then select’val is null’;
when val0 then select’val is greater than 0’;
else select’val is 0’;
end case;
| [loopLabel:]loop
statement
end loop [loopLabel]
| declare id int default 0;
addLoop:loop
set id=id+1;
if id>=10 then leave addLoop;
end if;
end loop addLoop;
|
leave用来退出任何流程控制构造；leave label;
iterate用来跳转到开头处；可以放在loop,while,repeate内，表示再次循环；iterate label;
[repeatLabel:]repeat
statement
until expr
end repeat[repeatLabel]
| declare id int default 0;
repeat
set id=id+1;
until id>=10
end repeat;
|
until后面是循环结束的条件，如果条件为真，则结束循环，条件为假，继续循环；
[table][tr][td=1,1,389][whileLabel:]while expr do
statement
end while[whileLabel]
[/td][td=1,1,389]declare i int default 0;
while i |
