数据库相关知识总结

mysql数据库登录

mysql -u user_name -p -h ip_address -P port

创建数据库

create database database_name;

展示已有数据库

show databases;

使用数据库

use database_name;

展示数据库中已存在的表

show tables;

展示表列的属性

show columns from table_name;
or
desc table_name;

检索某个表中的所有数据

select * from table_name;

检索某个表中某些列的数据

select col_name1, col_name2 from table_name;

检索表中的数据且保持某列数据只出现一次(distinct)

select distinct col_name from table_name;

注:如果使用 DISTINCT关键字,它必须直接放在列名的前面,同时,DISTINCT关键字应用于所有列而不仅是前置它的列。除非,所选择的列中数据都不同,否则所有数据都将被检索出来

指定返回结果数量(limit)

select col_name from table_name limit num;

上述语句将导致检索只返回不多余num的条目

指定返回结果的开始行数以及数目

select col_name from table_name limit begin_num, num;

其中 begin_num 表示从该行开始,num 表示至多返回 num 个条目

使用完全限定表名来引用列

select table_name.col_name from table_name;

检索结果按照某列排序(order by)

select col_name, col2_name from table_name order by col_name;

order by不仅可以按照所选择的列进行排序,同时,还可以按照未选择的列进行排序

检索结果按多个列排序

select col_name, col2_name from table_name order by col_name, col2_name;

检索结果将首先按照col_name排序,之后按照col2_name排序

检索结果逆序排列(desc)

select * from table_name order by col_name desc;

检索结果按照某个属性逆序排列,然后按照另一个属性升序排列

select * from table_name order by col_name desc, col1_name;

desc 关键字只应用到直接位于其前面的列名,如果想在多个列上进行降序排序,必须对每个列指定desc关键字。升序使用 asc 。默认使用升序

过滤检索结果(where)

select * from table_name where col_name = * ;

在同时使用ORDER BY和WHERE子句时,应该让ORDER BY位于WHERE之后,否则将会产生错误
where 支持的操作符 >, <, >=, <=, <>, !=, =, between

范围值检索

select * from table_name where col_name between a and b;

空值检查(is null)

select * from table_name where col_name is null;

该语句将返回对应列为空值的数据

AND操作符

select * from table_name where col_name = * and col_name1 = *;

AND 用在WHERE子句中的关键字,用来指示检索满足所有给定条件的行

OR操作符

select * from table_name where col_name = * or col_name = *;

OR WHERE子句中使用的关键字,用来表示检索匹配任一给定条件的行

注: SQL 在处理OR操作符前,优先处理AND操作符

IN操作符

select * from table_name where col_name in (1,2);

IN操作符用来指定条件范围,范围中的每个条件都可以进行匹配。IN操作符后跟由逗号分隔的合法值清单,整个清单必须括在圆括号中。

NOT操作符

select * from table_name where col_name not in (1,2);

NOT WHERE子句中用来否定后跟条件的关键字,MySQL支持使用NOT对IN、BETWEEN和EXISTS子句取反

Like进行模糊搜索

select * from table_name where col_name like 'abs%';

其中%为通配符,表示任何字符出现任意次数,在执行这条子句时,将检索任意以abs起头的条目

下划线通配符

select * from table_name where col_name like 'abs_'

其中_为通配符,不过只匹配单个字符

正则表达式的使用(regexp)

select * from table_name where col_name regexp 'abs';

REGEXP后所跟的东西作为正则表达式处理,该语句表示选择出col_name为abs的条目。是需要注意的是,使用regexp之后,后面跟着的字符串使用的应该是正则表达式语言,eg .表示单个字符通配符,*表示任意通配符,?表示零个或一个匹配,+表示一个或多个匹配

Like和Regexpde的差别

LIKE匹配整个列。如果被匹配的文本在列值中出现,LIKE将不会找到它,相应的行也不被返回(除非使用通配符)。
而REGEXP在列值内进行匹配,如果被匹配的文本在列值中出现,REGEXP将会找到它,相应的行将被返回。
如:对 Like 'abs' 和 regexp 'abs'来说,条目 'aaa abs' 将不会在like结果中出现,而会在regexp结果中出现。
如果想要regexp表现出和like一样的特性,可使用定位符 ^ 和 $。
MySQL中的正则表达式匹配(自版本3.23.4后)不区分大小写(即,大写和小写都匹配)。
为区分大小写,可使用BINARY关键字,如WHERE prod_name REGEXP BINARY 'JetPack .000'

匹配连在一起的四个数字

select * from table_name where col_name regexp '[[:digit:]]{4}';

计算字段

拼接字段(concat)

select concat(col_name, '(', col1_name, ')') from table_name;

检索数据格式将呈现为 col_name (col1_name),Concat()需要一个或多个指定的串,各个串之间用逗号分隔

Trim函数

select LTrim(col_name) from table_name;

LTrim用于去掉串左边空格,RTrim用于去掉串右边空格,Trim用于去掉串左右两边空格

as别名

select col_name as a from table_name;

AS关键字可为列赋予别名

执行算术运算

select price*num as total_price from table_name;

Mysql支持的函数

Upper将文本转换为大写

select Upper(col_name) as up_col_name from table_name; 

常用的文本处理函数

函数 说明
Left() 返回串左边的字符
Length() 返回串的长度
Locate() 找出串的一个子串
Lower() 将串转换为小写
LTrim() 去掉串左边的空格
Right() 返回串右边的字符
RTrim() 去掉串右边的空格
Soundex() 返回串的SOUNDEX值
SubString() 返回子串的字符
Upper() 将串转换为大写

常用日期和时间处理函数

函 数 说 明
AddDate() 增加一个日期(天、周等)
AddTime() 增加一个时间(时、分等)
CurDate() 返回当前日期
CurTime() 返回当前时间
Date() 返回日期时间的日期部分
DateDiff() 计算两个日期之差
Date_Add() 高度灵活的日期运算函数
Date_Format() 返回一个格式化的日期或时间串
Day() 返回一个日期的天数部分
DayOfWeek() 对于一个日期,返回对应的星期几
Hour() 返回一个时间的小时部分
Minute() 返回一个时间的分钟部分
Month() 返回一个日期的月份部分
Now() 返回当前日期和时间
Second() 返回一个时间的秒部分
Time() 返回一个日期时间的时间部分
Year() 返回一个日期的年份部分

注:MySQL使用的日期格式 yyyy-mm-dd

日期函数使用举例

select * from orders where Date(order_date) between '2020-09-01' and '2020-09-30';
# 等价于
select * from orders where Year(order_date) == 2020 and Month(order_date) == 9;

常用数值处理函数

函 数 说 明
Abs() 返回一个数的绝对值
Cos() 返回一个角度的余弦
Exp() 返回一个数的指数值
Mod() 返回除操作的余数
Pi() 返回圆周率
Rand() 返回一个随机数
Sin() 返回一个角度的正弦
Sqrt() 返回一个数的平方根
Tan() 返回一个角度的正切

聚集函数(aggregate function) 运行在行组上,计算和返回单个值的函数

SQL聚集函数

函 数 说 明
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和

聚集函数使用举例

select AVG(prod_price) as avg_price from table_name;

注: AVG()函数忽略列值为NULL的行

找出顾客表中顾客总数

select count(*) as num_cust from customers;

COUNT(*)对所有行计数

创建分组(group by)

select id, count(*) as num from products group by id;

将产品按照id进行分组,同时统计产品数量,GROUP BY子句指示MySQL分组数据,然后对每个组而不是整个结果集进行聚集,GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前

使用HAVING过滤分组

select id, count(*) as num from product group by id having count(*) > 2;

HAVING和WHERE的差别:
WHERE在数据分组前进行过滤,HAVING在数据分组后进行过滤。这是一个重要的区别,WHERE排除的行不包括在分组中。这可能会改变计算值,从而影响HAVING子句中基于这些值过滤掉的分组

SELECT子句顺序

子 句 说 明 是否必须使用
SELECT 要返回的列或表达式
FROM 从中检索数据的表 仅在从表选择数据时使用
WHERE 行级过滤
GROUP BY 分组说明 仅在按组计算聚集时使用
HAVING 组级过滤
ORDER BY 输出排序顺序
LIMIT 要检索的行数

子查询

select cust_id from orders where order_num in (
    select order_num from orderitems where prod_id = 'TNT2'
);

多表查询--两表联接

select * from product, orders where product.id = orders.product_id;

内部连接

select * from table1_name inner join table2_name on table1_name.col_name = table2_name.col_name;

使用inner join 时,联结条件用特定的ON子句而不是WHERE子句给出

外部连接

select * from customers left outer join orders on customers.id = orders.cust_id;

外部连接结果可以存在空值,因为外部联结还包括没有关联行的行,在使用OUTER JOIN语法时,必须使用RIGHT或LEFT关键字指定包括其所有行的表,LEFT OUTER JOIN从FROM子句的左边表(customers表)中选择所有行,Right选择右边表

使用union来组合两个查询

select * from table_name where cond union select * from table_name where cond1;
/*等价于*/
select * from table_name where cond or select * from table_name where cond1;

UNION必须由两条或两条以上的SELECT语句组成,语句之间用关键字UNION分隔
UNION中的每个查询必须包含相同的列、表达式或聚集函数
列数据类型必须兼容:类型不必完全相同,但必须是DBMS可以隐含地转换的类型
UNION从查询结果集中自动去除了重复的行,可以使用UNION ALL来避免重复行的删除

在进行数据查找时,如使用正则表达式匹配列值等的方法因为没有索引等原因,将会随着数据量的增加,耗时也成倍增加,使用全文本搜索将会为指定的列基于每个单词建立索引,从而极大的降低所需的搜索时间。不过并非所有搜索引擎都支持全文本搜索,两个最常使用的引擎为MyISAM和InnoDB,前者支持全文本搜索,而后者不支持。

启用全文本搜索支持(full text)

create table table_name(
    note_id int not null auto_increment,
    prod_id int char not null,
    note_date datetime not null,
    note_text null,
    primary key(note_id),
    fulltext(note_text)
)engine=myisam;

全文本搜索需要MyISAM引擎的支持,关键字fulltext指定被索引的列

在索引之后,使用两个函数Match()Against()执行全文本搜索,其中Match()指定被搜索的列,Against()指定要使用的搜索表达式

select note_text from productnotes where Match(note_text) Against('rabbit');

上述语句将检索出包含 'rabbit' 的 note_text

全文本搜索的一个重要部分就是对结果排序,具有较高等级的行先返回。

MySQL支持全文本搜索的另外一种形式,称为布尔方式(boolean mode)。以布尔方式,可以提供关于如下内容的细节

  1. 要匹配的词;
  2. 要排斥的词(如果某行包含这个词,则不返回该行,即使它包含其他指定的词也是如此);
  3. 排列提示(指定某些词比其他词更重要,更重要的词等级更高);
  4. 表达式分组;
  5. 另外一些内容
select note_text from table_name where Match(note_text) Against('heavy -rope*' in boolean mode);

匹配词heavy,但-rope*明确地指示MySQL排除包含rope*的行

全文本布尔操作符

布尔操作符 说 明
  • | 包含,词必须存在
  • | 排除,词必须不出现

| 包含,而且增加等级值
< | 包含,且减少等级值
() | 把词组成子表达式(允许这些子表达式作为一个组被包含、排除、排列等)
~ | 取消一个词的排序值

  • | 词尾的通配符
    "" | 定义一个短语(与单个词的列表不一样,它匹配整个短语以便包含或排除这个短语)

数据插入

插入完整的行

/* customer (id, name, city, age);*/
insert into customer 
values( null, name, city_name, 20);
/* 等价于 */
insert into customer(name, city, age) values(name, city_name, 20);

其中,id属性是自增的,我们不应该对其赋值,在使用第二种方式时,表名后面的列可以任意排列,只需values的值与其一一对应即可,同时,也可以省略一些列值,默认为null或指定的默认值

数据插入可能是比较耗时的操作,特别是当需要对其建立很多索引的时候,如果这时检索等其它操作是很重要的时候,可以降低数据插入的优先级:

insert low_priority into ...

同样,对于update和delete也可以执行该操作

插入多行

insert into table_name 
values(...),
values(...),
values(...);

插入检索出的数据

insert into table_name(...) select ... from table1_name;

MySQL不关心SELECT返回的列名。它使用的是列的位置,因此SELECT中的第一列(不管其列名)将用来填充表列中指定的第一个列,第二列将用来填充表列中指定的第二个列,如此等等

更新数据

update table_name set col_name = * where cond;
update table_name set col_name = *, col1_name = * where cond;

更新数据时可能发生错误,为即使是发生错误,也继续进行更新,可使用IGNORE关键字

update ignore table_name ...

删除数据

delete from table_name where cond;

如果想要删除表中所有数据,可以使用truncate

truncate table_name;

TRUNCATE实际是删除原来的表并重新创建一个表,而不是逐行删除表中的数据

创建数据表
为利用CREATE TABLE创建表,必须给出下列信息:

  1. 新表的名字,在关键字CREATE TABLE之后给出
  2. 表列的名字和定义,用逗号分隔。

CREATE TABLE语句也可能会包括其他关键字或选项,但至少要包括表的名字和列的细节

表的主键可以在创建表时用PRIMARY KEY关键字指定

create table table_name(
    col_name attr,
    col1_name attr,
    primary key(col_name)
);
/*等价于*/
create table table_name(
    col_name attr primary key,
    col1_name attr
);

如果你仅想在一个表不存在时创建它,应该在表名后给出IF NOT EXISTS

create table table_name if not exists(
    ...
);

多列主键

create table table_name(
    col_name attr,
    col1_name attr,
    col2_name attr,
    primary key(col_name, col1_name)
);

可以使用last_insert_id()来获取上一个使用AUTO_INCREMENT自增得到的主键值,但是需要注意的是,该方法与表无关,同时如果一次插入多个数据,也只会返回第一个插入的数据的主键值,使用方法如下

select last_insert_id();

详细介绍

指定默认值

create table table_name(
    id int auto_increment primary key,
    name char(20),
    weight int default 0
);

Mysql内置了多个数据库引擎,可在语句的最后使用ENGINE=语句来指定执行命令的引擎,不同的引擎可能带来性能差异

create table table_name(
    ...
)engine=innodb;

以下是三个比较重要的引擎

  1. InnoDB是一个可靠的事务处理引擎,它不支持全文本搜索;
  2. MEMORY在功能等同于MyISAM,但由于数据存储在内存(不是磁盘)中,速度很快(特别适合于临时表);
  3. MyISAM是一个性能极高的引擎,它支持全文本搜索,但不支持事务处理

引擎可以混用,但是外键不能跨引擎

为表添加一个列

alter table table_name add col_name attr;

删除表的一个列

alter table table_name drop column col_name;

在表外添加外键

alter table table_name 
add foreign key (col_name) references other_table(col_name);

删除表

drop table table_name;

重命名表

rename table table_name to table1_name;

视图是虚拟的表。与包含数据的表不一样,视图只包含使用时动态检索数据的查询,视图的常见应用:

  1. 重用SQL语句。
  2. 简化复杂的SQL操作。在编写查询后,可以方便地重用它而不必知道它的基本查询细节
  3. 使用表的组成部分而不是整个表
  4. 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限
  5. 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据

视图本身不包含数据,因此它们返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时,视图将返回改变过的数据

视图用CREATE VIEW语句来创建。
使用SHOW CREATE VIEW viewname;来查看创建视图的语句。
用DROP删除视图,其语法为DROP VIEW viewname;
更新视图时,可以先用DROP再用CREATE,也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在,则第2条更新语句会创建一个视图;如果要更新的视图存在,则第2条更新语句会替换原有视图。

创建视图

create view view_name as 
select col_name, col2_name from table_name, table1_name
where cond;

利用视图进行数据查询

select * from view_name where cond;

对于视图的更新相当于对其基表进行更新,对视图增加或删除行,实际上是对其基表增加或删除行。一般情况下,视图应该用于检索,而不用于数据更新。并且当视图的定义中存在 分组(使用GROUP BY和HAVING);联结;子查询;并;聚集函数(Min()、Count()、Sum()等);DISTINCT; 导出(计算)列时,不能对视图进行更新,可见,使用这些操作时,视图中的数据和其基表并不一一对应

存储过程简单来说,就是为以后的使用而保存的一条或多条MySQL语句的集合

存储过程的调用

call fun_name(@param1, @param2, @param3);

存储过程的创建

create procedure productpricing()
begin
    select Avg(prod_price) as priceaverage
    from products;
end;

该语句创建了一个存储过程名为productpricing

注:mysql的默认分割符为;,而在创建存储过程的语句中,存在;,为了避免该语法错误,可使用delimiter来临时更改分隔符

delimiter \\
...
end \\
delimiter ;

在上述语句中先使用delimiter \\将分割符改成了\\,再定义了存储过程之后,再将其更改回;

删除存储过程

drop procedure if exists productpricing;

可选参数(if exists)

一般,存储过程并不显示结果,而是把结果返回给你指定的变量。

create procedure productpricing(
    out p1 decimal(8, 2),
    out p2 decimal(8, 2),
    out p3 decimal(8, 2)
)
begin
select min(prod_price) into p1 from products;
select max(prod_price) into p2 from products;
select avg(prod_price) into p3 from products;
end;

此存储过程接受3个参数, p1,p2以及p3。关键字OUT指出相应的参数用来从存储过程传出一个值(返回给调用者)。MySQL支持IN(传递给存储过程)、OUT(从存储过程传出,如这里所用)和INOUT(对存储过程传入和传出)类型的参数并通过指定INTO关键字来将处理结果保存在对应的变量中

为调用上述存储过程,使用下面方法

call productpricing(@p1, @p2, @p3);

显示结果

select @p1, @p2, @p3;

向存储过程传入参数

create procedure ordertotal(
    in onumber int,  /*传入参数*/
    out ototal decimal(8, 2) /*传出参数*/
)
begin
select sum(item_price*quantity)
from orderitems where order_num = onumber
into ototal;
end;

call ordertotal(100, @total); /*调用存储过程*/
select @total; /*展示结果*/

为显示用来创建一个存储过程的CREATE语句,使用SHOW CREATE PROCEDURE语句:

show create procedure procedure_name;

为了获得包括何时、由谁创建等详细信息的存储过程列表,使用SHOW PROCEDURE STATUS。

show procedure status like 'procedure_name';

like语句用于限制输出,否则将列出所有存储过程

游标(cursor) 是一个存储在MySQL服务器上的数据库查询,它不是一条SELECT语句,而是被该语句检索出来的结果集。在存储了游标之后,应用程序可以根据需要滚动或浏览其中的数据,mysql的游标只能用于存储过程

游标的使用步骤:

  1. 在能够使用游标前,必须声明(定义)它。这个过程实际上没有检索数据,它只是定义要使用的SELECT语句
  2. 一旦声明后,必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来
  3. 对于填有数据的游标,根据需要取出(检索)各行
  4. 在结束游标使用时,必须关闭游标

游标用DECLARE语句创建。DECLARE命名游标,并定义相应的SELECT语句,根据需要带WHERE和其他子句

create procedure processorders()
begin
declare ordernumbers cursor
for 
select order_num from orders;
end;

打开游标

open cursor_name;

关闭游标

close cursor_name;

在一个游标被打开后,可以使用FETCH语句分别访问它的每一行。FETCH指定检索什么数据(所需的列),检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针,使下一条FETCH语句检索下一行

declare done boolean default 0;
declare o int;
declare continue handler for sqlstate '02000' set done=1;
/*,当REPEAT由于没有更多的行供循环而不能继续时,出现‘02000’这个条件,执行该语句*/

open cursor_name;
fetch cursor_name into o;
/*loop*/
repeat 
fetch cursor_name into o;
until done end repeat;

close cursor_name;

DECLARE语句的发布存在特定的次序。用DECLARE语句定义的局部变量必须在定义任意游标或句柄之前定义,而句柄必须在游标之后定义

触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句(或位于BEGIN和END语句之间的一组语句):

  1. delete
  2. insert
  3. update

在创建触发器时,需要给出4条信息

  1. 唯一的触发器名;
  2. 触发器关联的表;
  3. 触发器应该响应的活动(DELETE、INSERT或UPDATE);
  4. 触发器何时执行(处理之前或之后)

触发器用CREATE TRIGGER语句创建

create trigger newproduct after insert on product
for each row select 'Product added' into @info;

此触发器将在INSERT语句成功执行后执行。这个触发器还指定FOR EACH ROW,因此代码对每个插入行执行。在这个例子中,文本Product added将对每个插入的行显示一次。注:MYSQL5以后,不允许触发器返回任何结果,因此使用into @变量名,将结果赋值到变量中,用select调用即可

触发器按每个表每个事件每次地定义,每个表每个事件每次只允许一个触发器。因此,每个表最多支持6个触发器(每条INSERT、UPDATE
和DELETE的之前和之后)。单一触发器不能与多个事件或多个表关联,所以,如果你需要一个对INSERT和UPDATE操作执行的触发器,则应该定义两个触发器。

删除触发器

drop trigger trigger_name;

INSERT触发器在INSERT语句执行之前或之后执行。需要知道以下几点:

  1. 在INSERT触发器代码内,可引用一个名为NEW的虚拟表,访问被插入的行;
  2. 在BEFORE INSERT触发器中,NEW中的值也可以被更新(允许更改被插入的值);
  3. 对于AUTO_INCREMENT列,NEW在INSERT执行之前包含0,在INSERT执行之后包含新的自动生成值。
create trigger neworder after insert on orders 
for each row select NEW.order_num;

DELETE触发器在DELETE语句执行之前或之后执行。需要知道以下两点:

  1. 在DELETE触发器代码内,你可以引用一个名为OLD的虚拟表,访问被删除的行;
  2. OLD中的值全都是只读的,不能更新

将删除的行保存到存档中

create trigger deleteorder before delete on orders
for each row
begin
insert into archive_orders(order_num, order_id, order_time)
values(old.order_num, old.order_id, old.order_time);

UPDATE触发器在UPDATE语句执行之前或之后执行。需要知道以下几点:

  1. 在UPDATE触发器代码中,你可以引用一个名为OLD的虚拟表访问以前(UPDATE语句前)的值,引用一个名为NEW的虚拟表访问新更新的值;
  2. 在BEFORE UPDATE触发器中,NEW中的值可能也被更新(允许更改将要用于UPDATE语句中的值);
  3. OLD中的值全都是只读的,不能更新
create trigger updatevendor before update on vendors
for each row set new.vend_state = Upper(new.vend_state);

事务处理 是一种机制,用来管理必须成批执行的MySQL操作,以保证数据库不包含不完整的操作结果。利用事务处理,可以保证一组操作不会中途停止,它们或者作为整体执行,或者完全不执行(除非明确指示)。如果没有错误发生,整组语句提交给(写到)数据库表。如果发生错误,则进行回退(撤销)以恢复数据库到某个已知且安全的状态。

在使用事务和事务处理时,有几个关键词汇反复出现。下面是关于事务处理需要知道的几个术语:

  1. 事务(transaction)指一组SQL语句;
  2. 回退(rollback)指撤销指定SQL语句的过程;
  3. 提交(commit)指将未存储的SQL语句结果写入数据库表;
  4. 保留点(savepoint)指事务处理中设置的临时占位符(place holder),你可以对它发布回退(与回退整个事务处理不同)。

MySQL使用下面的语句来标识事务的开始

start transaction;

回退Mysql语句

start transaction;
delete from orders;
rollback;

上述语句将开始事务,删除所有订单,然后回退到上一个保留点,结果是该语句没对数据库造成影响

事务处理用来管理INSERT、UPDATE和DELETE语句。你不能回退SELECT语句。(这样做也没有什么意义。)你不能回退CREATE或DROP操作。事务处理块中可以使用这两条语句,但如果你执行回退,它们不会被撤销。

一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交(implicit commit),即提交(写或保存)操作是自动进行的。但是,在事务处理块中,提交不会隐含地进行。为进行明确的提交,使用COMMIT语句,如下所示:

start transaction;
delete from orders;
delete from customers;
commit;

最后的COMMIT语句仅在不出错时写出更改。如果第一条DELETE起作用,但第二条失败,则DELETE不会提交(实际上,它是被自动撤销的)。

当COMMIT或ROLLBACK语句执行后,事务会自动关闭

为了在当复杂事务出错时,只回退部分操作,可以使用保留点。为了支持回退部分事务处理,必须能在事务处理块中合适的位置放置占位符。这样,如果需要回退,可以回退到某个占位符。这些占位符即保留点。

创建占位符

savepoint point_name;

回退到保留点

rollback to point_name;

保留点在事务处理完成(执行一条ROLLBACK或COMMIT)后自动释放。自MySQL 5以来,也可以用RELEASE SAVEPOINT明确地释放保留点

指示MySQL不自动提交更改

set autocommit=0;

autocommit标志决定是否自动提交更改,不管有没有COMMIT语句。设置autocommit为0(假)指示MySQL不自动提交更改,直到autocommit被设置为真为止。autocommit标志是针对每个连接而不是服务器的

为了给表指定字符集和校对(指定排序方式),可使用带子句的CREATE TABLE

create table test(
    col_name int,
    col1_name char
)default character set hebrew collate hebrew_general_ci;

该语句指定了字符集为 hebrew,校对方式为 hebrew_general_ci

除了能指定字符集和校对的表范围外,MySQL还允许对每个列设置它们,如下所示

create table test(
    col_name int,
    col1_name char,
    col2_name char character set latin1 collate latin1_general_ci
)default character set hebrew collate hebrew_general_ci;

如果你需要用与创建表时不同的校对顺序排序特定的SELECT语句,可以在SELECT语句自身中进行

select * from table_name 
order by col_name collate hebrew_general_ci;

安全管理

获得所有用户账号列表

use mysql;
select user from user;

创建用户账户

create user user_name identified by 'password';

除了上述语句也可以使用 grant语句和向user表中插入数据的方式来创建用户

重命名用户

rename user user_name to another_user_name; 

删除用户

drop user user_name;

查看用户权限

show grants for user_name;

为设置权限,使用GRANT语句。GRANT要求你至少给出以下信息:

  1. 要授予的权限;
  2. 被授予访问权限的数据库或表;
  3. 用户名。

授予权限

grant select on database_name.* to user_name;

此GRANT允许用户在database_name.*(database_name数据库的所有表)上使用SELECT,即该用户在该数据库的所有表上有且仅有select权限

撤销权限

revoke select on database_name.* from user_name;

修改密码

set password for user_name = password("password");

SET PASSWORD更新用户口令。新口令必须传递到Password()函数进行加密。在不指定用户名时,SET PASSWORD更新当前登录用户的口令。

数据库备份

  1. 使用命令行实用程序mysqldump转储所有数据库内容到某个外部文件。在进行常规备份前这个实用程序应该正常运行,以便能正确地备份转储文件。
  2. 可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据(并非所有数据库引擎都支持这个实用程序)。
  3. 可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所有数据到某个外部文件。这两条语句都接受将要创建的系统文件名,此系统文件必须不存在,否则会出错。数据可以用RESTORETABLE来复原。

数据库维护

-- 检查表键是否正确
analyze table table_name;
-- 发现和修复问题
check table table_name;

mysql主要日志

  1. 错误日志。它包含启动和关闭问题以及任意关键错误的细节。此日志通常名为hostname.err,位于data目录中。此日志名可用 --log-error命令行选项更改
  2. 查询日志。它记录所有MySQL活动,在诊断问题时非常有用。此日志文件可能会很快地变得非常大,因此不应该长期使用它。此日志通常名为hostname.log,位于data目录中。此名字可以用--log命令行选项更改
  3. 二进制日志。它记录更新过数据(或者可能更新过数据)的所有语句。此日志通常名为hostname-bin,位于data目录内。此名字可以用--log-bin命令行选项更改。注意,这个日志文件是MySQL 5中添加的,以前的MySQL版本中使用的是更新日志
  4. 缓慢查询日志。顾名思义,此日志记录执行缓慢的任何查询。这个日志在确定数据库何处需要优化很有用。此日志通常名为hostname-slow.log ,位于 data 目录中。此名字可以用--log-slow-queries命令行选项更改

待添加:

  1. 范式
  2. 事务管理级别
posted @ 2021-04-23 10:50  范中豪  阅读(130)  评论(0编辑  收藏  举报