1、Windows服务
net start mysql
sc create mysql binPath= mysqld_bin_path(注意:等号与值之间有空格)
2、连接与断开服务器
mysql -h 地址 -P 端⼝ -u ⽤⼾名 -p 密码
SHOW PROCESSLIST
SHOW VARIABLES
3、数据库操作
- 查看当前数据库
SELECT DATABASE();
SELECT now(), user(), version();
CREATE DATABASE[ IF NOT EXISTS] 数据库名 数据库选项
数据库选项:
CHARACTER SET charset_name
COLLATE collation_name
SHOW DATABASES[ LIKE 'PATTERN']
SHOW CREATE DATABASE 数据库名
ALTER DATABASE 库名 选项信息
DROP DATABASE[ IF EXISTS] 数据库名
同时删除该数据库相关的⽬录及其⽬录内容
4、表的操作
- 创建表
CREATE [TEMPORARY] TABLE[ IF NOT EXISTS] [库名.]表名 ( 表的结构定义 )[ 表选项]
每个字段必须有数据类型
最后⼀个字段后不能有逗号
TEMPORARY 临时表,会话结束时表⾃动消失
对于字段的定义:
字段名 数据类型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']
CHARSET = charset_name
如果表没有设定,则使⽤数据库字符集
ENGINE = engine_name
表在管理数据时采⽤的不同的数据结构,结构不同会导致处理⽅式、提供的特性操作等不同
常⻅的引擎:InnoDB MyISAM Memory/Heap BDB Merge Example CSV MaxDB Archive
不同的引擎在保存表的结构和数据时采⽤不同的⽅式
MyISAM表⽂件含义:.frm表定义,.MYD表数据,.MYI表索引
InnoDB表⽂件含义:.frm表定义,表空间数据和⽇志⽂件
SHOW ENGINES
SHOW ENGINE 引擎名 {LOGS|STATUS}
AUTO_INCREMENT = ⾏数
DATA DIRECTORY = '⽬录'
INDEX DIRECTORY = '⽬录'
COMMENT = 'string'
PARTITION BY ... (详细⻅⼿册)
SHOW TABLES[ LIKE 'pattern']
SHOW TABLES FROM 表名
SHOW CREATE TABLE 表名 (信息更详细)
DESC 表名 / DESCRIBE 表名 / EXPLAIN 表名 / SHOW COLUMNS FROM 表名 [LIKE 'PATTERN']
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
ALTER TABLE 表名 表的选项
eg: ALTER TABLE 表名 ENGINE=MYISAM;
RENAME TABLE 原表名 TO 新表名
RENAME TABLE 原表名 TO 库名.表名 (可将表移动到另⼀个数据库)
ALTER TABLE 表名 操作名
ADD[ COLUMN] 字段定义
AFTER 字段名
FIRST
ADD PRIMARY KEY(字段名)
ADD UNIQUE [索引名] (字段名)
ADD INDEX [索引名] (字段名)
DROP[ COLUMN] 字段名
MODIFY[ COLUMN] 字段名 字段属性
CHANGE[ COLUMN] 原字段名 新字段名 字段属性
DROP PRIMARY KEY
DROP INDEX 索引名
DROP FOREIGN KEY 外键
DROP TABLE[ IF EXISTS] 表名 ...
TRUNCATE [TABLE] 表名
CREATE TABLE 表名 LIKE 要复制的表名
CREATE TABLE 表名 [AS] SELECT * FROM 要复制的表名
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
5、数据操作
INSERT [INTO] 表名 [(字段列表)] VALUES (值列表)[, (值列表), ...]
REPLACE 与 INSERT 完全⼀样,可互换。
INSERT [INTO] 表名 SET 字段名=值[, 字段名=值, ...]
SELECT 字段列表 FROM 表名[ 其他⼦句]
DELETE FROM 表名[ 删除条件⼦句]
没有条件⼦句,则会删除全部
UPDATE 表名 SET 字段名=新值[, 字段名=新值] [更新条件]
6、字符集编码
SHOW VARIABLES LIKE 'character_set_%'
character_set_client 客⼾端向服务器发送数据时使⽤的编码
character_set_results 服务器端将结果返回给客⼾端所使⽤的编码
character_set_connection 连接层编码
SET 变量名 = 变量值
SET character_set_client = gbk;
SET character_set_results = gbk;
SET character_set_connection = gbk;
SET NAMES GBK;
校对集⽤以排序
SHOW CHARACTER SET [LIKE 'pattern']/SHOW CHARSET [LIKE 'pattern'] 查看所有字符集
SHOW COLLATION [LIKE 'pattern'] 查看所有校对集
CHARSET 字符集编码 设置字符集编码
COLLATE 校对集编码 设置校对集编码
7、数据类型(列类型)
1. 数值类型
类型 字节 范围(有符号位)
tinyint 1字节 -128 ~ 127 ⽆符号位:0 ~ 255
smallint 2字节 -32768 ~ 32767
mediumint 3字节 -8388608 ~ 8388607
int 4字节
bigint 8字节
int(M) M表⽰总位数
- 默认存在符号位,unsigned 属性修改
- 显⽰宽度,如果某个数不够定义字段时设置的位数,则前⾯以0补填,zerofill 属性修改
例:int(5) 插⼊⼀个数'123',补填后为'00123'
- 在满⾜要求的情况下,越⼩越好。
- 1表⽰bool值真,0表⽰bool值假。MySQL没有布尔类型,通过整型0和1表⽰。常⽤tinyint(1)表⽰布尔型。
类型 字节 范围
float(单精度) 4字节
double(双精度) 8字节
浮点型既⽀持符号位 unsigned 属性,也⽀持显⽰宽度 zerofill 属性。
不同于整型,前后均会补填0.
定义浮点型时,需指定总位数和⼩数位数。
float(M, D) double(M, D)
M表⽰总位数,D表⽰⼩数位数。
M和D的⼤⼩会决定浮点数的范围。不同于整型的固定范围。
M既表⽰总位数(不包括⼩数点和正负号),也表⽰显⽰宽度(所有显⽰符号均包括)。
⽀持科学计数法表⽰。
浮点数表⽰近似值。
decimal
decimal(M, D) M也表⽰总位数,D表⽰⼩数位数。
保存⼀个精确的数值,不会发⽣数据的改变,不同于浮点数的四舍五⼊。
将浮点数转换为字符串来保存,每9位数字保存为4个字节。
2. 字符串类型
char 定⻓字符串,速度快,但浪费空间
varchar 变⻓字符串,速度慢,但节省空间
M表⽰能存储的最⼤⻓度,此⻓度是字符数,⾮字节数。
不同的编码,所占⽤的空间不同。
char,最多255个字符,与编码⽆关。
varchar,最多65535字符,与编码有关。
⼀条有效记录最⼤不能超过65535个字节。
utf8 最⼤为21844个字符,gbk 最⼤为32766个字符,latin1 最⼤为65532个字符
varchar 是变⻓的,需要利⽤存储空间保存 varchar 的⻓度,如果数据⼩于255个字节,则采⽤⼀个字节来保存⻓度,反之需要两个字节来保存。
varchar 的最⼤有效⻓度由最⼤⾏⼤⼩和使⽤的字符集确定。
最⼤有效⻓度是65532字节,因为在varchar存字符串时,第⼀个字节是空的,不存在任何数据,然后还需两个字节来存放字符串的⻓度,所以有效⻓度是64432-1-2=65532字节。
例:若⼀个表定义为 CREATE TABLE tb(c1 int, c2 char(30), c3 varchar(N)) charset=utf8; 问N的最⼤值是多少? 答:(65535-1-2-4-30*3)/3
blob ⼆进制字符串(字节字符串)
tinyblob, blob, mediumblob, longblob
text ⾮⼆进制字符串(字符字符串)
tinytext, text, mediumtext, longtext
text 在定义时,不需要定义⻓度,也不会计算总⻓度。
text 类型在定义时,不可给default值
类似于char和varchar,⽤于保存⼆进制字符串,也就是保存字节字符串⽽⾮字符字符串。
char, varchar, text 对应 binary, varbinary, blob.
3. ⽇期时间类型
⼀般⽤整型保存时间戳,因为PHP可以很⽅便的将时间戳进⾏格式化。
datetime 8字节 ⽇期及时间 1000-01-01 00:00:00 到 9999-12-31 23:59:59
date 3字节 ⽇期 1000-01-01 到 9999-12-31
timestamp 4字节 时间戳 19700101000000 到 2038-01-19 03:14:07
time 3字节 时间 -838:59:59 到 838:59:59
year 1字节 年份 1901 - 2155
datetime YYYY-MM-DD hh:mm:ss
timestamp YY-MM-DD hh:mm:ss
YYYYMMDDhhmmss
YYMMDDhhmmss
YYYYMMDDhhmmss
YYMMDDhhmmss
date YYYY-MM-DD
YY-MM-DD
YYYYMMDD
YYMMDD
YYYYMMDD
YYMMDD
time hh:mm:ss
hhmmss
hhmmss
year YYYY
YY
YYYY
YY
4. 枚举和集合
enum(val1, val2, val3...)
在已知的值中进⾏单选。最⼤数量为65535.
枚举值在保存时,以2个字节的整型(smallint)保存。每个枚举值,按保存的位置顺序,从1开始逐⼀递增。
表现为字符串类型,存储却是整型。
NULL值的索引是NULL。
空字符串错误值的索引值是0。
set(val1, val2, val3...)
create table tab ( gender set('男', '⼥', '⽆') );
insert into tab values ('男, ⼥');
最多可以有64个不同的成员。以bigint存储,共8个字节。采取位运算的形式。
当创建表时,SET成员值的尾部空格将⾃动被删除。
8、选择类型
1. 功能满⾜
2. 存储空间尽量⼩,处理效率更⾼
3. 考虑兼容问题
1. 只需存储,可⽤字符串
2. 如果需计算,查找等,可存储为4个字节的⽆符号int,即unsigned
1) PHP函数转换
ip2long可转换为整型,但会出现携带符号问题。需格式化为⽆符号的整型。
利⽤sprintf函数格式化字符串
sprintf("%u", ip2long('192.168.3.134'));
然后⽤long2ip将整型转回IP字符串
2) MySQL函数转换(⽆符号整型,UNSIGNED)
INET_ATON('127.0.0.1') 将IP转为整型
INET_NTOA(2130706433) 将整型转为IP
9、列属性(列约束)
1. PRIMARY 主键
- 能唯⼀标识记录的字段,可以作为主键。
- ⼀个表只能有⼀个主键。
- 主键具有唯⼀性。
- 声明字段时,⽤ primary key 标识。
也可以在字段列表之后声明
例:create table tab ( id int, stu varchar(10), primary key (id));
- 主键字段的值不能为null。
- 主键可以由多个字段共同组成。此时需要在字段列表后声明的⽅法。
例:create table tab ( id int, stu varchar(10), age int, primary key (stu, age));
2. UNIQUE 唯⼀索引(唯⼀约束)
使得某字段的值也不能重复。
3. NULL 约束
null不是数据类型,是列的⼀个属性。
表⽰当前列是否可以为null,表⽰什么都没有。
null, 允许为空。默认。
not null, 不允许为空。
insert into tab values (null, 'val');
4. DEFAULT 默认值属性
当前字段的默认值。
建表规范
insert into tab values (default, 'val');
create table tab ( add_time timestamp default current_timestamp );
current_date, current_time
5. AUTO_INCREMENT ⾃动增⻓约束
⾃动增⻓必须为索引(主键或unique)
只能存在⼀个字段为⾃动增⻓。
默认为1开始⾃动增⻓。可以通过表属性 auto_increment = x进⾏设置,或 alter table tbl auto_increment = x;
6. COMMENT 注释
例:create table tab ( id int ) comment '注释内容';
7. FOREIGN KEY 外键约束
⽤于限制主表与从表数据完整性。
alter table t1 add constraint `t1_t2_fk` foreign key (t1_id) references t2(id);
存在外键的表,称之为从表(⼦表),外键指向的表,称之为主表(⽗表)。
作⽤:保持数据⼀致性,完整性,主要⽬的是控制存储在外键表(从表)中的数据。
MySQL中,可以对InnoDB引擎使⽤外键约束:
语法:
foreign key (外键字段) references 主表名 (关联字段) [主表记录删除时的动作] [主表记录更新时的动作]
此时需要检测⼀个从表的外键需要约束为主表的已存在的值。外键在没有关联的情况下,可以设置为null.前提是该外键列,没有not null。
可以不指定主表记录更改或更新时的动作,那么此时主表的操作被拒绝。
如果指定了 on update 或 on delete:在删除或更新时,有如下⼏个操作可以选择:
1. cascade,级联操作。主表数据被更新(主键值更新),从表也被更新(外键值更新)。主表记录被删除,从表相关记录也被删除。
2. set null,设置为null。主表数据被更新(主键值更新),从表的外键被设置为null。主表记录被删除,从表相关记录外键被设置成null。但注意,要求该外键列,没有not null属性约束。
3. restrict,拒绝⽗表删除和更新。
注意,外键只被InnoDB存储引擎所⽀持。其他引擎是不⽀持的。
10、建表规范
- Normal Format, NF
- 每个表保存⼀个实体信息
- 每个具有⼀个ID字段作为主键
- ID主键 + 原⼦表
字段不能再分,就满⾜第⼀范式。
满⾜第⼀范式的前提下,不能出现部分依赖。
消除符合主键就可以避免部分依赖。增加单列关键字。
满⾜第⼆范式的前提下,不能出现传递依赖。
某个字段依赖于主键,⽽有其他字段依赖于该字段。这就是传递依赖。
将⼀个实体信息的数据放在⼀个表内实现。
SELECT [ALL|DISTINCT] select_expr FROM -> WHERE -> GROUP BY [合计函数] -> HAVING -> ORDER BY -> LIMIT
a. select_expr
select * from tb;
select stu, 29+25, now() from tb;
使⽤ as 关键字,也可省略 as.
select stu+10 as add10 from tb;
b. FROM ⼦句
⽤于标识查询来源。
SELECT * FROM tb1 AS tt, tb2 AS bb;
SELECT * FROM tb1, tb2;
USE INDEX、IGNORE INDEX、FORCE INDEX
SELECT * FROM table1 USE INDEX (key1,key2) WHERE key1=1 AND key2=2 AND key3=3;
SELECT * FROM table1 IGNORE INDEX (key3) WHERE key1=1 AND key2=2 AND key3=3;
c. WHERE ⼦句
=, <=>, <>, !=, <=, <, >=, >, !, &&, ||,
in (not) null, (not) like, (not) in, (not) between and, is (not), and, or, not, xor
is/is not 加上ture/false/unknown,检验某个值的真假
<=>与<>功能相同,<=>可⽤于null⽐较
d. GROUP BY ⼦句, 分组⼦句
GROUP BY 字段/别名 [排序⽅式]
分组后会进⾏排序。升序:ASC,降序:DESC
以下[合计函数]需配合 GROUP BY 使⽤:
count 返回不同的⾮NULL值数⽬ count(*)、count(字段)
sum 求和
max 求最⼤值
min 求最⼩值
avg 求平均值
group_concat 返回带有来⾃⼀个组的连接的⾮NULL值的字符串结果。组内字符串连接。
e. HAVING ⼦句,条件⼦句
与 where 功能、⽤法相同,执⾏时机不同。
where 在开始时执⾏检测数据,对原数据进⾏过滤。
having 对筛选出的结果再次进⾏过滤。
having 字段必须是查询出来的,where 字段必须是数据表存在的。
where 不可以使⽤字段的别名,having 可以。因为执⾏WHERE代码时,可能尚未确定列值。
where 不可以使⽤合计函数。⼀般需⽤合计函数才会⽤ having
SQL标准要求HAVING必须引⽤GROUP BY⼦句中的列或⽤于合计函数中的列。
f. ORDER BY ⼦句,排序⼦句
order by 排序字段/别名 排序⽅式 [,排序字段/别名 排序⽅式]...
升序:ASC,降序:DESC
⽀持多个字段的排序。
g. LIMIT ⼦句,限制结果数量⼦句
仅对处理好的结果进⾏数量限制。将处理好的结果的看作是⼀个集合,按照记录出现的顺序,索引从0开始。
limit 起始位置, 获取条数
省略第⼀个参数,表⽰从索引0开始。limit 获取条数
h. DISTINCT, ALL 选项
distinct 去除重复记录
默认为 all, 全部记录
11、UNION
将多个select查询的结果组合成⼀个结果集合。
SELECT ... UNION [ALL|DISTINCT] SELECT ...
默认 DISTINCT ⽅式,即所有返回的⾏都是唯⼀的
建议,对每个SELECT查询加上⼩括号包裹。
ORDER BY 排序时,需加上 LIMIT 进⾏结合。
需要各select查询的字段数量⼀样。
每个select查询的字段列表(数量、类型)应⼀致,因为结果中的字段名以第⼀条select语句为准。
12、子查询
⼦查询需⽤括号包裹。
from后要求是⼀个表,必须给⼦查询结果取个别名。
- 简化每个查询内的条件。
- from型需将结果⽣成⼀个临时表格,可⽤以原表的锁定的释放。
- ⼦查询返回⼀个表,表型⼦查询。
select * from (select * from tb where id>0) as subfrom where id>1;
- ⼦查询返回⼀个值,标量⼦查询。
- 不需要给⼦查询取别名。
- where⼦查询内的表,不能直接⽤以更新。
select * from tb where money = (select max(money) from tb);
如果⼦查询结果返回的是⼀列。
使⽤ in 或 not in 完成查询
exists 和 not exists 条件
如果⼦查询返回数据,则返回1或0。常⽤于判断条件。
select column1 from t1 where exists (select * from t2);
查询条件是⼀个⾏。
select * from t1 where (id, gender) in (select id, gender from t2);
⾏构造符:(col1, col2, ...) 或 ROW(col1, col2, ...)
⾏构造符通常⽤于与对能返回两个或两个以上列的⼦查询进⾏⽐较。
!= all() 相当于 not in
= some() 相当于 in。any 是 some 的别名
!= some() 不等同于 not in,不等于其中某⼀个。
all, some 可以配合其他运算符⼀起使⽤。
13、连接查询(join)
将多个表的字段进⾏连接,可以指定连接条件。
- 默认就是内连接,可省略inner。
- 只有数据存在时才能发送连接。即连接结果不能出现空⾏。
on 表⽰连接条件。其条件表达式与where类似。也可以省略条件(表⽰条件永远为真)
也可⽤where表⽰连接条件。
还有 using, 但需字段名相同。 using(字段名)
即,没有条件的内连接。
select * from tb1 cross join tb2;
- 如果数据不存在,也会出现在连接结果中。
如果数据不存在,左表记录会出现,⽽右表为null填充
如果数据不存在,右表记录会出现,⽽左表为null填充
⾃动判断连接条件完成连接。
相当于省略了using,会⾃动查找相同字段名。
natural join
natural left join
natural right join
select info.id, info.name, info.stu_num, extra_info.hobby, extra_info.sex from info, extra_info where info.stu_num
14、导出
select * into outfile ⽂件地址 [控制格式] from 表名;
load data [local] infile ⽂件地址 [replace|ignore] into table 表名 [控制格式];
⽣成的数据默认的分隔符是制表符
local未指定,则数据⽂件必须在服务器上
replace 和 ignore 关键词控制对现有的唯⼀键记录的重复的处理
fields 控制字段格式
默认:fields terminated by '\t' enclosed by '' escaped by '\\'
terminated by 'string'
enclosed by 'char'
escaped by 'char'
SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;
lines 控制⾏格式
默认:lines terminated by '\n'
terminated by 'string'
15、INSERT
select语句获得的数据可以⽤insert插⼊。
可以省略对列的指定,要求 values () 括号内,提供给了按照列顺序出现的所有字段的值。
或者使⽤set语法。
INSERT INTO tbl_name SET field=value,...;
可以⼀次性使⽤多个值,采⽤(), (), ();的形式。
INSERT INTO tbl_name VALUES (), (), ();
可以在列值指定时,使⽤表达式。
INSERT INTO tbl_name VALUES (field_value, 10+10, now());
可以使⽤⼀个特殊值 DEFAULT,表⽰该列使⽤默认值。
INSERT INTO tbl_name VALUES (field_value, DEFAULT);
可以通过⼀个查询的结果,作为需要插⼊的值。
INSERT INTO tbl_name SELECT ...;
可以指定在插⼊的值出现主键(或唯⼀索引)冲突时,更新其他⾮主键列的信息。
INSERT INTO tbl_name VALUES/SET/SELECT ON DUPLICATE KEY UPDATE 字段=值, …;
16、DELETE
DELETE FROM tbl_name [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]
按照条件删除。where
指定删除的最多记录数。limit
可以通过排序条件删除。order by + limit
⽀持多表删除,使⽤类似连接语法。
delete from 需要删除数据多表1,表2 using 表连接操作 条件。
17、TRUNCATE
TRUNCATE [TABLE] tbl_name
清空数据
删除重建表
区别:
1,truncate 是删除表再创建,delete 是逐条删除
2,truncate 重置auto_increment的值。⽽delete不会
3,truncate 不知道删除了⼏条,⽽delete知道。
4,当被⽤于带分区的表时,truncate 会保留分区
18、备份与还原
备份,将数据的结构与表内数据保存起来。
利⽤ mysqldump 指令完成。
mysqldump [options] db_name [tables]
mysqldump [options]
mysqldump [options]
1. 导出⼀张表
mysqldump -u⽤⼾名 -p密码 库名 表名 > ⽂件名(D:/a.sql)
2. 导出多张表
mysqldump -u⽤⼾名 -p密码 库名 表1 表2 表3 > ⽂件名(D:/a.sql)
3. 导出所有表
mysqldump -u⽤⼾名 -p密码 库名 > ⽂件名(D:/a.sql)
4. 导出⼀个库
mysqldump -u⽤⼾名 -p密码
可以-w携带WHERE条件
1. 在登录mysql的情况下:
source 备份⽂件
2. 在不登录的情况下
mysql -u⽤⼾名 -p密码 库名 < 备份⽂件
19、视图
什么是视图:
视图是⼀个虚拟表,其内容由查询定义。同真实的表⼀样,视图包含⼀系列带有名称的列和⾏数据。但是,视图并不在数据库中以存储的数据值集形式存在。⾏和列数据来⾃由定义视图的查询所引⽤的表,并且在引⽤视图时动态⽣成。
视图具有表结构⽂件,但不存在数据⽂件。
对其中所引⽤的基础表来说,视图的作⽤类似于筛选。定义视图的筛选可以来⾃当前或其它数据库的⼀个或多个表,或者其它视图。通过视图进⾏查询没有任何限制,通过它们进⾏数据修改时的限制也很少。
视图是存储在数据库中的查询的sql语句,它主要出于两种原因:安全原因,视图可以隐藏⼀些数据,如:社会保险基⾦表,可以⽤视图只显⽰姓名,地址,⽽不显⽰社会保险号和⼯资数等,另⼀原因是可使复杂的查询易于理解和使⽤。
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement
- 视图名必须唯⼀,同时不能与表重名。
- 视图可以使⽤select语句查询到的列名,也可以⾃⼰指定相应的列名。
- 可以指定视图执⾏的算法,通过ALGORITHM指定。
- column_list如果存在,则数⽬必须等于SELECT语句检索的列数
SHOW CREATE VIEW view_name
- 删除视图后,数据依然存在。
- 可同时删除多个视图。
DROP VIEW [IF EXISTS] view_name ...
- ⼀般不修改视图,因为不是所有的更新视图都会映射到表上。
ALTER VIEW view_name [(column_list)] AS select_statement
1. 简化业务逻辑
2. 对客⼾端隐藏真实的表结构
MERGE 合并
将视图的查询语句,与外部查询需要先合并再执⾏!
TEMPTABLE 临时表
将视图执⾏完毕后,形成临时表,再做外层查询!
UNDEFINED 未定义(默认),指的是MySQL⾃主去选择相应的算法。
20、事务(transaction)
事务是指逻辑上的⼀组操作,组成这组操作的各个单元,要不全成功要不全失败。
- ⽀持连续SQL的集体成功或集体撤销。
- 事务是数据库在数据晚⾃习⽅⾯的⼀个功能。
- 需要利⽤ InnoDB 或 BDB 存储引擎,对⾃动提交的特性⽀持完成。
- InnoDB被称为事务安全型引擎。
START TRANSACTION; 或者 BEGIN;
开启事务后,所有被执⾏的SQL语句均被认作当前事务内的SQL语句。
COMMIT;
ROLLBACK;
如果部分操作发⽣问题,映射到事务开启前。
1. 原⼦性(Atomicity)
事务是⼀个不可分割的⼯作单位,事务中的操作要么都发⽣,要么都不发⽣。
2. ⼀致性(Consistency)
事务前后数据的完整性必须保持⼀致。
- 事务开始和结束时,外部数据⼀致
- 在整个事务过程中,操作是连续的
3. 隔离性(Isolation)
多个⽤⼾并发访问数据库时,⼀个⽤⼾的事务不能被其它⽤⼾的事物所⼲扰,多个并发事务之间的数据要相互隔离。
4. 持久性(Durability)
⼀个事务⼀旦被提交,它对数据库中的数据改变就是永久性的。
1. 要求是事务⽀持的表类型
2. 执⾏⼀组相关的操作前开启事务
3. 整组操作完成后,都成功,则提交;如果存在失败,选择回滚,则会回到事务开始的备份点。
利⽤InnoDB的⾃动提交(autocommit)特性完成。
普通的MySQL执⾏语句后,当前的数据提交操作均可被其他客⼾端可⻅。
⽽事务是暂时关闭“⾃动提交”机制,需要commit提交持久化数据操作。
1. 数据定义语⾔(DDL)语句不能被回滚,⽐如创建或取消数据库的语句,和创建、取消或更改表或存储的⼦程序的语句。
2. 事务不能被嵌套
SAVEPOINT 保存点名称
ROLLBACK TO SAVEPOINT 保存点名称
RELEASE SAVEPOINT 保存点名称
SET autocommit = 0|1; 0表⽰关闭⾃动提交,1表⽰开启⾃动提交。
- 如果关闭了,那普通操作的结果对其他客⼾端也不可⻅,需要commit提交后才能持久化数据操作。
- 也可以关闭⾃动提交来开启事务。但与START TRANSACTION不同的是,
SET autocommit是永久改变服务器的设置,直到下次再次修改该设置。(针对当前连接)
⽽START TRANSACTION记录开启前的状态,⽽⼀旦事务提交或回滚后就需要再次开启事务。(针对当前事务)
21、锁表
表锁定只⽤于防⽌其它客⼾端进⾏不正当地读取和写⼊
MyISAM ⽀持表锁,InnoDB ⽀持⾏锁
LOCK TABLES tbl_name [AS alias]
UNLOCK TABLES
22、触发器
触发程序是与表有关的命名数据库对象,当该表出现特定事件时,将激活该对象
监听:记录的增加、修改、删除。
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt
参数:
trigger_time是触发程序的动作时间。它可以是 before 或 after,以指明触发程序是在激活它的语句之前或之后触发。
trigger_event指明了激活触发程序的语句的类型
INSERT:将新⾏插⼊表时激活触发程序
UPDATE:更改某⼀⾏时激活触发程序
DELETE:从表中删除某⼀⾏时激活触发程序
tbl_name:监听的表,必须是永久性的表,不能将触发程序与TEMPORARY表或视图关联起来。
trigger_stmt:当触发程序激活时执⾏的语句。执⾏多个语句,可使⽤BEGIN...END复合语句结构
DROP TRIGGER [schema_name.]trigger_name
可以使⽤old和new代替旧的和新的数据
更新操作,更新前是old,更新后是new.
删除操作,只有old.
增加操作,只有new.
1. 对于具有相同触发程序动作时间和事件的给定表,不能有两个触发程序。
concat(str1,str2,...])
concat_ws(separator,str1,str2,...)
if 条件 then
执⾏语句
elseif 条件 then
执⾏语句
else
执⾏语句
end if;
delimiter ⾃定义结束符号
SQL语句
⾃定义结束符号
delimiter ;
begin
语句块
end
1. 只要添加记录,就会触发程序。
2. Insert into on duplicate key update 语法会触发:
如果没有重复记录,会触发 before insert, after insert;
如果有重复记录并更新,会触发 before insert, before update, after update;
如果有重复记录但是没有发⽣更新,则触发 before insert, before update
3. Replace 语法 如果有记录,则执⾏ before insert, before delete, after delete, after insert
23、SQL编程
declare var_name[,...] type [default value]
这个语句被⽤来声明局部变量。要给变量提供⼀个默认值,请包含⼀个default⼦句。值可以被指定为⼀个表达式,不需要为⼀个常数。如果没有default⼦句,初始值为null。
使⽤ set 和 select into 语句为变量赋值。
- 注意:在函数内是可以使⽤全局变量(⽤⼾⾃定义的变量)
set 语句可以定义并为变量赋值。
set @var = value;
也可以使⽤select into语句为变量初始化并赋值。这样要求select语句只能返回⼀⾏,但是可以是多个字段,就意味着同时为多个变量进⾏赋值,变量的数量需要与查询的列数⼀致。
还可以把赋值语句看作⼀个表达式,通过select执⾏完成。此时为了避免=被当作关系运算符看待,使⽤:=代替。(set语句可以使⽤= 和 :=)。
select @var:=20;
select @v1:=id, @v2=name from t1 limit 1;
select * from tbl_name where @var:=30;
select into 可以将表中查询获得的数据赋给变量。
-| select max(height) into @max_height from tb;
为了避免select语句中,⽤⼾⾃定义的变量与系统标识符(通常是字段名)冲突,⽤⼾⾃定义变量在变量名前使⽤@作为开始符号。
@var=10;
- 变量被定义后,在整个会话周期都有效(登录到退出)
if search_condition then
statement_list
[elseif search_condition then
statement_list]
...
[else
statement_list]
end if;
CASE value WHEN [compare-value] THEN result
[WHEN [compare-value] THEN result ...]
[ELSE result]
END
[begin_label:] while search_condition do
statement_list
end while [end_label];
- 如果需要在循环内提前终⽌ while循环,则需要使⽤标签;标签需要成对出现。
退出整个循环 leave
退出当前循环 iterate
通过退出的标签决定退出哪个循环
abs(x)
format(x, d)
ceil(x)
floor(x)
round(x)
mod(m, n)
pi()
pow(m, n)
sqrt(x)
rand()
truncate(x, d)
now(), current_timestamp();
current_date();
current_time();
date('yyyy-mm-dd hh:ii:ss');
time('yyyy-mm-dd hh:ii:ss');
date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j');
unix_timestamp();
from_unixtime();
length(string)
char_length(string)
substring(str, position [,length])
replace(str ,search_str ,replace_str)
instr(string ,substring)
concat(string [,...])
charset(str)
lcase(string)
left(string, length)
load_file(file_name)
locate(substring, string [,start_position])
lpad(string, length, pad)
ltrim(string)
repeat(string, count)
rpad(string, length, pad)
rtrim(string)
strcmp(string1 ,string2)
case when [condition] then result [when [condition] then result ...] [else result] end 多分⽀
if(expr1,expr2,expr3) 双分⽀。
count()
sum();
max();
min();
avg();
group_concat()
md5();
default();
CREATE FUNCTION function_name (参数列表) RETURNS 返回值类型
函数体
- 函数名,应该合法的标识符,并且不应该与已有的关键字冲突。
- ⼀个函数应该属于某个数据库,可以使⽤db_name.funciton_name的形式执⾏当前函数所属数据库,否则为当前数据库。
- 参数部分,由"参数名"和"参数类型"组成。多个参数⽤逗号隔开。
- 函数体由多条可⽤的mysql语句,流程控制,变量声明等语句构成。
- 多条语句应该使⽤ begin...end 语句块包含。
- ⼀定要有 return 返回值语句。
DROP FUNCTION [IF EXISTS] function_name;
SHOW FUNCTION STATUS LIKE 'partten'
SHOW CREATE FUNCTION function_name;
ALTER FUNCTION function_name 函数选项
存储存储过程 是⼀段代码(过程),存储在数据库中的sql组成。
⼀个存储过程通常⽤于完成⼀段业务逻辑,例如报名,交班费,订单⼊库等。
⽽⼀个函数通常专注与某个功能,视为其他程序服务的,需要在其他语句中调⽤函数才可以,⽽存储过程不能被其他调⽤,是⾃⼰执⾏ 通过call执⾏。
CREATE PROCEDURE sp_name (参数列表)
过程体
参数列表:不同于函数的参数列表,需要指明参数类型
IN,表⽰输⼊型
OUT,表⽰输出型
INOUT,表⽰混合型
注意,没有返回值。
存储过程是⼀段可执⾏性代码的集合。相⽐函数,更偏向于业务逻辑。
调⽤:CALL 过程名
- 没有返回值。
- 只能单独调⽤,不可夹杂在其他语句中
IN|OUT|INOUT 参数名 数据类型
IN 输⼊:在调⽤过程中,将数据输⼊到过程体内部的参数
OUT 输出:在调⽤过程中,将过程体处理完的结果返回到客⼾端
INOUT 输⼊输出:既可输⼊,也可输出
CREATE PROCEDURE 过程名 (参数列表)
BEGIN
过程体
END
24、用户和权限管理
1. 停⽌MySQL服务
2. [Linux] /usr/local/mysql/bin/safe_mysqld
[Windows] mysqld
3. use mysql;
4. UPDATE `user` SET PASSWORD=PASSWORD("密码") WHERE `user` = "root";
5. FLUSH PRIVILEGES;
⽤⼾信息表:mysql.user
FLUSH PRIVILEGES;
CREATE USER ⽤⼾名 IDENTIFIED BY [PASSWORD] 密码(字符串)
- 必须拥有mysql数据库的全局CREATE USER权限,或拥有INSERT权限。
- 只能创建⽤⼾,不能赋予权限。
- ⽤⼾名,注意引号:如 'user_name'@'192.168.1.1'
- 密码也需引号,纯数字密码也要加引号
- 要在纯⽂本中指定密码,需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值,需包含关键字PASSWORD
-- 重命名⽤⼾
RENAME USER old_user TO new_user
-- 设置密码
SET PASSWORD = PASSWORD('密码') -- 为当前⽤⼾设置密码
SET PASSWORD FOR ⽤⼾名 = PASSWORD('密码') -- 为指定⽤⼾设置密码
-- 删除⽤⼾
DROP USER ⽤⼾名
-- 分配权限/添加⽤⼾
GRANT 权限列表 ON 表名 TO ⽤⼾名 [IDENTIFIED BY [PASSWORD] 'password']
- all privileges 表⽰所有权限
- *.* 表⽰所有库的所有表
- 库名.表名 表⽰某库下⾯的某表
GRANT ALL PRIVILEGES ON `pms`.* TO 'pms'@'%' IDENTIFIED BY 'pms0817';
SHOW GRANTS FOR ⽤⼾名
SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER();
REVOKE 权限列表 ON 表名 FROM ⽤⼾名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM ⽤⼾名
全局层级:全局权限适⽤于⼀个给定服务器中的所有数据库,mysql.user
GRANT ALL ON *.*和 REVOKE ALL ON *.*只授予和撤销全局权限。
数据库层级:数据库权限适⽤于⼀个给定数据库中的所有⽬标,mysql.db, mysql.host
GRANT ALL ON db_name.*和REVOKE ALL ON db_name.*只授予和撤销数据库权限。
表层级:表权限适⽤于⼀个给定表中的所有列,mysql.talbes_priv
GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限。
列层级:列权限适⽤于⼀个给定表中的单⼀列,mysql.columns_priv
当使⽤REVOKE时,您必须指定与被授权列相同的列。
ALL [PRIVILEGES]
ALTER
ALTER ROUTINE
CREATE
CREATE ROUTINE
CREATE TEMPORARY TABLES
CREATE USER
CREATE VIEW
DELETE
DROP
EXECUTE
FILE
INDEX
INSERT
LOCK TABLES
PROCESS
REFERENCES
RELOAD
REPLICATION CLIENT
REPLICATION SLAVE
SELECT
SHOW DATABASES
SHOW VIEW
SHUTDOWN
SUPER
UPDATE
USAGE
GRANT OPTION
25、表维护
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE 表名 ...
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
26、杂项
1. 可⽤反引号(`)为标识符(库名、表名、字段名、索引、别名)包裹,以避免与关键字重名!中⽂也可以作为标识符!
2. 每个库⽬录存在⼀个保存当前数据库的选项⽂件db.opt。
3. 注释:
单⾏注释
多⾏注释
单⾏注释
4. 模式通配符:
_ 任意单个字符
% 任意多个字符,甚⾄包括零字符
单引号需要进⾏转义 \'
5. CMD命令⾏内的语句结束符可以为 ";", "\G", "\g",仅影响显⽰结果。其他地⽅还是⽤分号结束。delimiter 可修改当前对话的语句结束符。
6. SQL对⼤⼩写不敏感
7. 清除已有语句:\c