目录
选择数据库(USE)
USE 库名; 进入某数据库
显示数据库信息(SHOW)
代码 显示XX
SHOU DATABASES; 所有数据库名
SHOU TABLES; 所有 当前库的 表名
SHOU COLUMN FROM 表名1; 指定表的列的相关信息
SHOU STATUS; 广泛的服务器状态信息
SHOU CREATE DATABASE/TABLE; 创建库/表的语句
SHOU GRANTS; 用户的安全权限
SHOU GRANTS FOR 用户名; 特定用户的安全权限,这是管理员的事
SHOU ERRORS/WORINGS; 服务器错误/警告信息
列的相关信息:字段名,数据类型,是否允许NULL,键信息,默认值,其他信息
检索数据(SELECT FROM)
SELECT 列名
FROM 表名
WHERE 条件
UNION ALL
GROUP BY 列名
HAVING 条件
ORDER BY 列名
LIMIT 开始位置,数量;
基础结构(SELECT FROM)
SELECT 列名
FROM 表名;
列名和表名都可以有多个,用逗号分隔
SELECT 列名1,列名2
FROM 表名1,表名2;
返回所有行(*)
SELECT *
FROM 表名;
*表示输出表里的所有列
返回唯一值(DISTINCT)
SELECT DISTINCT 列名1,列名2
FROM 表名;
某一个列名1与列名2的组合只会显示一列
DISTINCT 指定的是所有在其后的列名,而不只是一个
计算字段
拼接字段(Concat)
如
SELECT Concat(列名1,'(',列名2,')')
FROM 表名;
会显示:列名1(列名2)
算术计算(±*/)
如
SELECT 列名1*列名2
FROM 表名;
会显示:列名1*列名2
别名(AS)
别名可以用在SELECT,WHERE,ORDER BY等
列和字段可以自己定义想要它显示的名字,即别名
给列取别名
SELECT 列名1 AS 别名1
FROM 表名;
修改子拼接字段的例子,使用拼接字段需要用到Concat函数
SELECT Concat(列名1,'(',列名2,')') AS 别名1
FROM 表名;
会显示:别名1
表也可以有别名
SELECT 列名1
FROM 表名1 AS 别名1;
同一个表也可以有多个别名(将会在表的连结中用到)
分别用两个别名是,互不干扰
SELECT 别名1.列名1,别名2.列名2,
FROM 表名1 AS 别名1,表名1 AS 别名2;
...
一个表有多个别名,但选取字段时却没有用别民限定时,别民具有二义性,不会报错,但会产生错误
数据处理函数
函数名后均有()
文本 | 说明 |
---|---|
Left | 返回串左边的字符 |
Right | 返回串右边的字符 |
Length | 返回串的长度 |
Locate | 找出串的一个子串 |
Lower | 将串转换为小写 |
Upper | 将串转换为大写 |
LTrim | 去掉串左边的空格 |
RTrim | 去掉串右边的空格 |
Trim | 去掉串左右两边的空格 |
Soundex | 返回串的SOUNDEX值 |
SubString | 返回子串的字符 |
日期 | 说明 |
---|---|
AddDate | 增加一个日期(天、周等) |
AddTime | 增加一个时间(时、分等) |
CurDate | 返回当前日期 |
CurTime | 返回当前时间 |
Date | 返回日期时间的日期部分 |
DataDiff | 计算两个日期之差 |
Data_Add | 高度灵活的日期运算函数 |
Data_Format | 高度灵活的日期运算函数 |
Day | 返回一个日期的天数部分 |
DayOfWeek | 对于一个日期,返回对应的星期几 |
Hour | 返回一个时间的小时部分 |
Minute | 返回一个时间的分钟部分 |
Month | 返回一个日期的月份部分 |
Now | 返回当前日期和时间 |
Second | 返回一个时间的秒部分 |
Time | 返回一个日期时间的时间部分 |
Year | 返回一个日期的年份部分 |
数值 | 说明 |
---|---|
Abs | 返回一个数的绝对值 |
Cos | 返回一个角度的余弦 |
Exp | 返回一个数的指数值 |
Mod | 返回除操作的余数 |
Pi | 返回圆周率 |
Rand | 返回一个随机数 |
Sin | 返回一个角度的正弦 |
Sqrt | 返回一个数的平方根 |
Tan | 返回一个角度的正切 |
聚集函数
以下函数大都忽略为NULL的值
函数名 | 说明 |
---|---|
AVG() | 某列平均值 |
COUNT() | 某列行数,(*)不忽略 |
MAX() | 某列最大值 |
MIN() | 某列最小值 |
SUM() | 某列之和 |
完全限定的列名/表名
列名 可以是 表名1.列名1
表名 可以是 库名1.表名
SELECT product.prod_name
FROM crashcourse.products;
在某些时候需要用到,比如多个表内有相同的列名
过滤数据(WHERE)
条件组合(AND OR () )
可以用AND和OR将条件组合在一起,
AND优先级高于OR,不要太依赖优先级
尽量不要同时使用AND和OR,同时使用时添加()以当面理解
一下
检查单个值
使用方法
列名 符号 值
符号有 =,<>,!=,<,<=,>,>=
范围值检查
操作符 | 用法 |
---|---|
BETWEEN | 列名 BETWEEN 数1 AND 数2 |
IN | 列名 IN(值1,值2,…) |
NOT IN | 列名 NOT IN(值1,值2,…) |
空值检查
操作符:IS NULL
用法:
列名 IS NULL
通配符(LIKE)
检查字符串是否匹配,若是就显示整个串
用法:
列名 LIKE '搜索模式'
搜索模式:可包含以下通配符的字符串
%:任意个任意字符
_:一个字符
其他字符
不要过度使用通配符
正则表达式(REGEXP)
检查字符串内是否符合的子串,若是就显示整个串
MySQL的正则表达式仅为正则表达式的一个很小的子集
用法:
列名 REGEXP '正则表达式'
基本字符
- 任意一个字符:点(.)
-一个数字:(0-9),自己代表自己 - 一个英文字母(a-z,A-Z),自己代表自己
’1000’
OR或匹配(|,[])
|
‘1 Ton|2 Ton|3 Ton’搜索到子集中有‘1 Ton’,‘2 Ton’,‘3 Ton’的字符串
在不受到限制时,|应用于整个串
'1|2|3 Ton’会搜索到子集中有‘1’,‘2’,‘3 Ton’,
而不是‘1 Ton’,‘2 Ton’,‘3 Ton’
[ ]
[]里有字符集合,匹配里面的任意单一字符
里面可以包含字符
‘[123] Ton’与|的第一个例子一样
- []里也可以有字符范围,如0-9,a-z,A-Z,或者其中的一段
- 可以单个使用[0-9],[a-z],[A-Z],分别代表了匹配各自范围内的任何一个字符
- 也可以一起用[0-9a-zA-Z],而不需要分隔符,代表匹配0-9,a-z,A-Z这三个范围内的任意一个字符
'[1-3] Ton’与|的第一个例子一样
转义特殊字符(\\)
MySQL用两个反斜杠\\转义. | [ ] ( ) \等,多数正则表达式一个
比如\\.就是匹配点这个字符
还有特殊的元字符也可以转义
元字符 | 说明 |
---|---|
\\f | 换页 |
\\n | 换行 |
\\r | 回车 |
\\t | 制表 |
\\v | 纵向制表 |
匹配字符类([: :])
即使本身
类 | 说明 | 同 |
---|---|---|
[:alnum:] | 任意字母和字符 | [a-zA-Z0-9] |
[:alpha:] | 任意字符 | [0-9A-Z] |
[:blank:] | 空格和制表 | [//t] |
[:cntrl:] | ASCII控制字符 | ASCII 0-31和127 |
[:digit:] | 任意数字 | [0-9] |
[:graph:] | 与[:print:]相同,但不包括空格 | |
[:lower:] | 任意小写字母 | [a-z] |
[:print:] | 任意可打印字符 | |
[:punct:] | 既不在[:alnum:]又不在[:cntrl:]中的任意字符 | |
[:space:] | 包括空格在内的任意空白字符 | [\f\n\r\t\v] |
[:upper:] | 任意大写字母 | [A-Z] |
[:xdigit:] | 任意十六进制数字 | [a-fA-F0-9] |
匹配多个实例
元字符 | 说明 |
---|---|
* | 0个或多个匹配,{0,} |
+ | 1个或多个匹配,{1,} |
? | 0个或1个匹配,{0,1} |
{n} | 指定数目的匹配 |
{n,} | 不少于指定数目的匹配 |
{n,m} | 匹配数目的范围,m<=255 |
‘[[:digit:]]{4}’
定位符
元字符 | 说明 |
---|---|
^ | 文本的开始 |
$ | 文本的结尾 |
[[:<:]] | 词的开始 |
[[:<:]] | 词的结尾 |
‘^[0-9]’
数据分组(GROUP BY)
GROUP BY 列名1,列名2
分组过滤(HAVING)
同 过滤数据(WHERE)
结果排序(ORDER BY)
ORDER BY 列名1
按列名排序,默认升序
每个列名后可选择排序方式:DESC/ASC->降序/升序
如:ORDER BY 列名1 DESC/ASC 按列名降序/升序排序
ORDER BY 列名1,列名2…,前面的优先级高
限制结果数量(LIMIT)
LIMIT 5 从第0条开始最多显示5条
LIMIT 6,5 从第6条开始最多显示5条
组合查询(UNION)
SELECT后必须有一样的列名,表达式,聚集函数等
每两个之间必须有且只有一个UNION分隔
UNION默认取消重复行
如果不需要取消,须使用UNION ALL
SELECT DISTINCT 列名1,列名2
FROM 表名;
UNION
SELECT DISTINCT 列名1,列名2
FROM 表名;
子查询
- 在过滤条件中
SELECT ...
FROM ...
WHERE ... IN (
SELECT ...
FROM ...
...
);
- 作为计算字段
SELECT ...
(
SELECT COUNT(*)
FROM ...
...
)
FROM ...
...;
表的连结(JOIN)
FROM 表1,表2; 自然连结
FROM 表1 INNER JOIN 表2 ON 条件; 内部连结
FROM 表1 LEFT OUTER JOIN 表2 ON 条件; 左外部连结
FROM 表1 RIGHT OUTER JOIN 表2 ON 条件; 右外部连结
左外部连结:表2中只显示符合条件的
右外部连结:表1中只显示符合条件的
全文本搜索(FULLTEXT)
创建
在表创建时,添加
CREATE TABLE 表名
(
id int NOT NULL AUTO_INCREMENT,
name char(50) NULL,
quantity int NULL DEFAULT 1,
PRIMARY KEY(id),
FULLTEXT(指定列名)<-------
)ENGINE=引擎类型;
FULLTEXT里可以有多个列名,‘,‘分隔
在增加/更新/删除行时,索引随之自动更新
在表创建后添加,修改表
此处比较模糊,建议自己查询
ALTER TABLE 表名
ADD FULLTEXT INDEX fulltext_表名(列名);
使用(Match Against)
Match(列名) Against(‘字符串’)会对每一个字符串计算等级值,不匹配的返回0
这一条可以在SELECT中也可以在WHERE中
SELECT node_text
FROM productnotes
WHERE Match(node_text) Against('单词');
在WHERE中时(不适用以下的查询扩展和布尔文本搜索),默认以等级值排序
SELECT node_text,
Match(node_text) Against('单词') AS rank
FROM productnotes;
使用查询扩展(WITH QUERY EXPANSION)
Match(列名) Against(‘字符串’ WITH QUERY EXPANSION);
这样可以找到不止时含有指定单词的值,
也会找到包含其他有用的词(出现在 含有指定单词的值 中的词)的值
- 进行一个基本的全文本搜索,找出与搜索条件匹配的所有行
- MySQL检查这些匹配行并选择所有有用的词(有一些规则,比如去除频率很高(>50%)的词)
- MySQL再次进行全文本搜索,这次不仅使用原来的条件,而且还使用所有有用的词
布尔文本搜索(IN BOOLEAN MODE)
可以使用布尔操作符
布尔操作符 | 说明 |
---|---|
+ | 包含,词必须存在 |
- | 排除,词必须不出现 |
> | 包含,且增加等级值 |
< | 包含。且减少等级值 |
( ) | 把词组成子表达式 |
~ | 取消一个词的排序值 |
* | 词尾的通配符 |
" " | 定义一个短语 |
子表达式:允许这些子表达式作为一个组被包含,排除,排列等
短语:与单个词的列表不一样,它匹配整个短语以便包含或排除这个短语
SELECT node_text
FROM productnotes
WHERE Match(node_text) Against('>rabbit <carrot' IN BOOLEAN MODE);
匹配rabbit和carrot,增加前者等级,降低后者等级
SELECT node_text
FROM productnotes
WHERE Match(node_text) Against('+safe +(<combination)' IN BOOLEAN MODE);
匹配safe和combination,降低后者的等级
数据 增/改/删
插入(INSERT INTO VALUES)
默认列名插入简单但不安全,避免使用
需列出所有列上的值,不多不少,顺序也一一对应
INSERT INTO 表名
VALUES(
列1的值1,
...
);
给出列名的插入
可以仅对列出的列对应赋值,未列出的列必须 允许NULL/给出默认值
INSERT INTO 表名(
列1名,
...
)
VALUES(
列1的值1,
...
);
插入多行
需列出所有列上的值,不多不少,顺序也一一对应
INSERT INTO 表名
VALUES(
列1的值1,
...
),
(
列1的值2,
...
);
更新(UPDATE SET WHERE)
UPDATE 表名
SET 列名1 = 值1,
列名2 = 值2,
...
WHERE 条件;
删除(DELETE FROM WHERE)
DELETE FROM 表名
WHERE 条件;
表 增/更/删/重命名
创建(CREATE TABLE)
简易
CREATE TABLE 表名
(
列名1 数据类型,
...
# 列级完整性约束
# <属性名> <类型> [[NOT] NULL] [AUTO_INCREMENT] [UNIQUE] [CHECK(<判断表达式>)] [DEFAULT <值>] [PRIMARY KEY],
# 表级完整性约束
# PRIMARY KEY(属性1[,属性2 ...])
# [CONSTRAINT 完整性约束条件名] CHECK(<判断表达式>) [ON <操作> <级联操作 CASCADE,NO ACTION,>]],
);
完整性约束
列级完整性约束 | 解释 |
---|---|
PRIMARY KEY | 主码 |
AUTO_INCREMENT | 自增 |
[NOT] NULL | NULL(默认)->值可以为空 |
UNIQUE | 唯一 |
DEFAULT <值> | 默认值为<> |
CHECK(<判断表达式>) | 插入时判断 |
表级完整性约束
- PRIMARY KEY(属性1[,属性2 …]),
- FOREIGN KEY(外码名) REFERENCES 表名(主码) [ON <操作> <级联操作 CASCADE,NO ACTION,>],
- CHECK(<判断表达式>) [ON <操作> <级联操作 CASCADE,NO ACTION,>]],
完整性语句约束命名子句(可以为每一个约束命名)
- [CONSTRAINT 完整性约束条件名] <完整性约束子句>
CREATE TABLE 表名
(
id int [[NOT] NULL] [AUTO_INCREMENT],
name char(50) [NULL],
quantity int [NULL] [DEFAULT 1] [CHECK(<判断表达式>)],
PRIMARY KEY(id),
[FOREIGN KEY(外码名) REFERENCES 表名(主码) [ON <操作> <级联操作 CASCADE,NO ACTION,>],]
[[CONSTRAINT 完整性约束条件名] CHECK(<判断表达式>),]
FULLTEXT(列名)
)ENGINE=引擎类型;
为防止相同的表重复创建,可以加上 IF NOT EXISTS
CREATE TABLE 表名 IF NOT EXISTS
(
列名1 数据类型 NULL/NOT NULL,
...
);
NULL值
列默认允许为NULL,可以指出NULL/NOT NULL
CREATE TABLE 表名
(
列名1 数据类型, 默认为NULL
列名2 数据类型 NULL, 指定为NULL
列名3 数据类型 NOT NULL, 指定为NOT NULL
...
);
引擎类型(ENGINE)
- InnoDB:可靠的事务处理引擎,不支持全文本搜索
- MEMORY:功能等同于MyISAM,由于数据存储在内存(不是磁盘),中,速度很快(特别适合于临时表)
- MyISAM:性能极高的引擎,支持全文本搜索,不支持事务处理
可以省略 ENGINE=引擎类型,将使用默认引擎(很可能是MyISAM)
官方网址:所支持引擎的完整列表(及它们之间的不同)
但外键不能跨引擎
自动增量(AUTO_INCREMENT)
某一个列可以被指定为AUTO_INCREMENT,
被指定的列必须被索引(如,成为主键PRIMARY KEY())
主键(PRIMARY KEY())
可以没有,可以有一个,也可以有多个
有的话,每一个都必须NOT NULL
主键的组合必须唯一
- 不更新主键列中的值
- 不重用主键列的值
- 不在主键列中使用可能会更改的值
默认值(DEFAULT)
可以在最后添加默认值,在插入数据时不对它赋值,将使用默认值(DEFAULT 默认值)
CREATE TABLE 表名
(
id int NOT NULL AUTO_INCREMENT,
name char(50) NULL,
quantity int NULL DEFAULT 1,
PRIMARY KEY(id)
)ENGINE=引擎类型;
删除(DROP TABLE)
DROP TABLE 表名;
重命名(RENAME TABLE TO)
RENAME TABLE 表原名 TO 表新名;
列 增/删
尽量不要在有数据时使用
增加列
ALTER TABLE 表名
ADD 列名 数据类型;
删除列
ALTER TABLE 表名
DROP COLUMN 列名;
视图(VIEW)
- 视图命名唯一
- 数目没有限制
- 创建需要权限
- 视图可以嵌套,可以利用其他视图中检索数据的查询来构造一个视图
- 视图里可以有ORDER BY,但SELECT中也有ORDER BY,视图中的将被覆盖
- 视图不能索引,也不能有关联的触发器或默认值
- 视图可以和表一起使用
- CREATE 时,若视图已存在,会覆盖
创建
CREATE VIEW 视图名 [AS <别名 DX>] [CASCADE]
SELECT ...
...;01
查看
SHOW CREATE VIEW 视图名;
使用
将其跟在FROM后面即可,可以做筛选等其他操作
删除
DROP VIEW 视图名;
更新
视图本身没有数据,如果对视图增/删/改,实际是对基表的操作
如果视图定义中有以下操作,则不能进行视图的更新
- 分组(GROUP BY,HAVING)
- 连结
- 子查询
- 并
- 聚焦函数
- DISTINCT
- 导出(计算)列
存储过程(PROCEDURE)
创建(CREATE)
CREATE PROCEDURE 存储过程名(
IN/OUT/INOUT 参数名1 类型,
...
)
BEGIN
语句1
...
END;
变量使用
对于IN参数,直接用变量名参与计算即可
对于OUT参数,
SELECT 聚焦函数
FROM 表名
INTO 变量;
内部语句
用BEGIN END;中间可以使用多个语句块,比如之前的范例,当然也可以直接并且只使用一个语句块
内部可以定义变量
DECLARE 参数名 类型;
也可以
DECLARE 参数名 类型 DEFAULT 默认值;
也可以用IF语句
IF 条件 THEN
语句
END IF;
删除(DROP)
DROP PROCEDURE 存储过程名;
如果指定的存储过程不存在,将产生错误,可在最后加 IF EXISTS
DROP PROCEDURE 存储过程名 IF EXISTS;
使用(CALL)
CALL 存储过程名(参数);
OUT参数传入的名字需要以@开头
在运行完成后,OUT参数有一个值,可以用SELECT查看值
如,一个名为@total的参数,存储过程完成之后
SELECT @total;
将会显示它的值
检查(SHOU)
显示创建存储过程的CREATE语句
SHOW CREATE PROCEDURE 存储过程名
显示包括何时,由谁创建等详细信息的存储过程列表
SHOW PROCEDURE STATUS
限制输出,指定过滤模式
SHOW PROCEDURE STATUS LIKE '存储过程名';
COMMENT
CREATE PROCEDURE 存储过程名(
...
)COMMENT 注释
BEGIN
...
END;
注释是一个字符串,‘ ’
这个注释如果给出,将在SHOW PROCEDURE STATUS中显示
游标(CURSOR)
游标是一个变量类型,在存储过程中使用
定义
DECLARE 变量名 CURSOR
FOR
SELECT ...;
打开(OPEN)与关闭(CLOSE)
打开 OPEN 游标变量名;
关闭 CLOSE 游标变量名;
只有打开游标才可以使用,打开后自动从第一条开始
在存储过程运行到END时,会自动关闭
使用
打开后
FETCH 列名 INTO 符合数据类型的变量名
这样只能使用第一条
下面的可以一直操作
DECLARE 变量名 CURSOR
DECLARE done BOOLEAN DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
REPEAT
FETCH 列名 INTO 符合数据类型的变量名
UNTIL done END REPEAT;
可以定义一个done,以检测是否到最后,默认为0,即没有到最后
- 定义了一个CONTINUE HANDLER,
- 当**SQLSTATE ‘02000’**出现时,
- SET done=1,
- 当REPEAT由于没有更多的行供循环而不能继续时,出现这个条件
REPEAT中的操作也可以时,将每一条数据添加到自己的数据库(可能需要新建)中
官方网站:关于MySQL5使用的MySQL错误代码列表
触发器(TRIGGER)
在满足触发条件即运行,共6类,
AFTER/BEFORE INSERT/UPDATE/DELETE
创建(CREATE)
需要权限
CREATE TRIGGER 触发器名 AFTER/BEFORE INSERT/UPDATE/DELETE ON 表名
FOR EACH ROW
语句
语句中,不支持CALL,不能调用存储过程,需要手动搬运代码
OLD表里的数据不可更改,NEW里的数据是可更改的
- INSERT有 NEW 表,存 插入的数据
- UPDATE有 NEW 和 OLD 表,存 更新后的数据 和 更新前的数据
- DELETE有 OLD 表,存 删除的数据
在BEFORE INSERT/UPDATE中修改NEW表,可对即将用到的值进行修改
在AFTER INSERT/UPDATE中修改NEW表,显然是无效的
DELETE中,使用BEFORE相比AFTER的优势是,由于某种原因,将要删除的数据无法存档(假设要将删除的数据存入另外的表中),DELETE本身将被废弃
里面的语句可以是SELECT,INSERT INTO等,可以用BEGIN END;
CREATE TRIGGER 触发器名 BEFORE INSERT ON 表名
FOR EACH ROW
SELECT NEW.列名;
显示将要插入的部分数据
CREATE TRIGGER 触发器名 BEFORE UPDATE ON 表名
FOR EACH ROW
SET NEW.列名 = 值,
WHERE 条件;
将更新后的记录中,对 符合条件的记录 的值进行修改
CREATE TRIGGER 触发器名 BEFORE DELETE ON 表名
FOR EACH ROW
BEGIN
INSERT INTO 保存删除数据的表(列名1,列名2)
VALUES(OLD.列1,OLD.列2)
END;
将删除的数据保存在另外的表中,
若存档失败,DELETE将被废弃(这就是上面说的DELETE中BEFORE的优点)
删除(DROP)
DROP TRIGGER 触发器名;
管理事务处理
事务处理用来管理INSERT,UPDATE,DELETE
不能回退SELECT,CREATE,DROP
以下语句均如此,都对中间的语句回退
但不会对SELECT,CREATE,DROP有任何作用
不在在事务处理模块中,隐含提交(autocommit=1)
在事务处理模块中,提交不会隐含地进行(autocommit=0)
START TRANSACTION;
语句
ROLLBACK;
START TRANSACTION;
语句
COMMIT;
SAVEPOINT 保留点名;
语句
ROLLBACK TO 保留点名;
下面这一条是对autocommit进行修改
SET autocommit=0;
语句
SET autocommit=1;
字符集(CHARACTER SET),校对顺序(COLLATE)
代码 显示
SHOW CHARACTER SET; 所有(可用字符集+描述+默认校对)
SHOW COLLATION; 所有(可用校对+适用的字符集)
SHOW VARIABLES LIKE 'character%';当前数据库 默认的字符集
SHOW VARIABLES LIKE 'collation%';当前数据库 默认的校对顺序
字符集(CHARACTER SET)
校对顺序(COLLATE)
指定了字符集,才可以指定校对顺序
- 指定字符集+指定校对顺序
- 指定字符集+默认校对顺序
- 默认字符集+默认校对顺序
数据表 列 都有对应的字符集和校对顺序,可以使用默认的,也可以单独指定
可以在创建表时指定 字符集和校对顺序 ,
CREATE TABLE 表名(
...
)DEFAULT CHARACTER SET 字符集名 COLLATE 校对顺序名;
CREATE TABLE 表名(
...
)DEFAULT CHARACTER SET 字符集名;
CREATE TABLE 表名(
...
);
也可以单独对每列指定
CREATE TABLE 表名(
列名1 数据类型 NULL CHARACTER SET 字符集名 COLLATE 校对顺序名;
列名1 数据类型 NULL CHARACTER SET 字符集名;
列名1 数据类型 NULL;
...
)
两者也可以结合使用
CREATE TABLE 表名(
列名1 数据类型 NULL CHARACTER SET 字符集名1;
...
)DEFAULT CHARACTER SET 字符集名2 COLLATE 校对顺序名2;
用户与权限
查看用户(user)
进入MySQL库,从user表查看user列
USE MySQL;
SELECT user FROM user;
创建用户(CREATE USER)
用户定义为 用户名@主机名
如果不指定主机名,使用默认的主机名%
CREATE USER 用户名 IDENTIFIED BY '散列口令';
可以修改口令,Password会对进行加密
为某一用户修改口令 SET PASSWORD FOR 用户名 = Password('新口令');
为当前用户修改口令 SET PASSWORD = Password('新口令');
GRANT可以用来创建用户,但一般来说CREATE是最清楚,最简单的例子
INSERT可以用来创建用户,插入行到user表,但这个表很重要,任何破坏都能严重地伤害MySQL服务器
重命名用户(RENAME USER TO)
RENAME USER 原名 TO 新名 ;
删除用户(DROP USER)
DROP USER 用户名;
设置访问权限(GRANT REVOKE)
SHOW grant FOR 用户; 显示该用户的所有权限
GRANT 权限 ON 库名.表名 TO 用户; 授予权限
REVOKE 权限 ON 库名.表名 TO 用户;撤销权限
权限可以有多个,用,隔开
GRANT <权限(有多个,用,隔开) SELECT,CREATE,UPDATE(sno),ALL PRIVILEGES>
ON 库名 <表名(有多个,用,隔开)>
TO <用户 -用户名,PUBLIC>;
[WITH GRANT OPTION]
库名.表名
GRANT SELECT ON *.* TO 用户; * .* 整个服务器
GRANT SELECT ON 库名.* TO 用户; 某库名.* 整个库
GRANT SELECT ON 库名.表名 TO 用户; 某库名.某表名 整个表
[CASCADE]
权限 | 说明 |
---|---|
ALL | 除GRANT OPTION外的所有权限 |
ALTER | 使用ALTER TABLE |
ALTER ROUTINE | 使用ALTER PROCEDURE和DROP PROCEDURE |
CREATE | 使用CREATE TABLE |
CREATE ROUTINE | 使用CREATE PROCEDURE |
CREATE TEMPORARY TABLES | 使用CREATE TEMPORARY TABLES |
CREATE USER | 使用CREATE USER,DROP USER,RENAME USER,REVOKE ALL PRIVILEGES |
CREATE VIEW | 使用CREATE VIEW |
DELETE | 使用DELETE |
DROP | 使用DROP TABLE |
EXECUTE | 使用CALL和存储过程 |
FILE | 使用SELECT INTO OUTLIFE和LOAD DATA INFILE |
GRANT OPTION | 使用GRANT和REVOKE |
INDEX | 使用CREATE INDEX和DROP INDEX |
INSERT | 使用INSERT |
LOCK TABLES | 使用LOCK TABLES |
PROCESS | 使用SHOW FULL PROCESSLIST |
RELOAD | 使用FLUSH |
REPLICATION CLIENT | 服务器位置的访问 |
REPLICATION SLAVA | 有复制从属使用 |
SELECT | 使用SELECT |
SHOW DATABASES | 使用SHOW DATABASES |
SHOW VIEW | 使用SHOW CREATE VIEW |
SHUTDOWN | 使用mysqlasmin shutdown(用来关闭MySQL) |
SUPER | 使用CHANGE MASTER,KILL,LOGS,PURGE,MASTER,SET GLOBAL,还允许mysqladmin调试登录 |
UPDATA | 使用UPDATA |
USAGE | 无权限访问 |
数据库维护
备份数据
- 像所有数据一样,MySQL的数据也必须经常备份。由于MySQL数据库是基于磁盘的文件,普通的备份系统和例程就能备份MySQL的数据。但是,由于这些文件总是处于打开和使用状态,普通的文件副本备份不一定总是有效。
- 下面列出这个问题的可能解决方案。
- 使用命令行实用程序mysqldump转储所有数据库内容到某个外部文件。在进行常规备份前这个实用程序应该正常运行,以便能正确地备份转储文件。
- 可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据(并非所有数据库引擎都支持这个实用程序)。
- 可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所有数据到某个外部文件。这两条语句都接受将要创建的系统文件名,此系统文件必须不存在,否则会出错, 数据可以用RESTORE TABLE来复原。
首先刷新未写数据:为了保证所有敦据被写到磁盘(包括索引数据),可能需要在进行备份前使用FLUSH TABLES语句。
数据库维护
输入一下语句,显示4列:Table Op Msg_type Msg_text
ANALYZE TABLE 表名; 检查表键是否正确
CHECK 发现和修复问题
CHANGED 检查自最后一次检查以来改动过的表
EXTENDED 执行最彻底的检查
FAST 只检查未正常关闭的表
MEDIUM 检查所有被删除的链接并进行键检查
QUICK 只进行快速扫描
- REPAIR TABLE:如果MyISAM表访问产生不确定和不一致的结果,可能需要用REPAIR TABLE来修复相应的表。这条语句不应该经常使用,如果需要经常使用,可能会有更大的问题需要解决。
- OPTIMIZE TABLE:如果从一个表中删除大量数据,应该使用OPTIMIZE TABLE来收回所用的空间,从而优化表的性能。
REPAIR TABLE
OPTIMIZE TABLE
诊断启动问题
- 服务器启动问题通常在对MySQL配置或服务器本省进行更改时出现。MySQL在这个问题发生时报告错误,但由于多数MySQL服务器时作为 系统进程或服务 自动启动的,这些消息可能看不到。
- 在排除系统问题时,首先应该尽量用手动启动服务器。MySQL服务器自身通过在命令行上执行mysqld启动,下面是几个重要的mysqld命令行选项
- –help显示帮助——一个选项列表
- –safe-mode装载减去某些最佳配置的服务器
- –verbose显示全文本消息(为获得更详细的帮助消息与–help联合使用)
- –version显示版本信息然后退出
几个另外的命令行选项(与日志文件的使用有关)在下面列出
查看日志文件
MySQL维护管理员依赖的一系列日志文件。主要的日志文件有以下几种。
- 错误日志。它包含启动和关闭问题以及任意关键错误的细节。此日志通常名为hostname.err,位于data目录中。词日志名可用–log-error命令行选项更改。
- 查询日志。它记录所有MySQL活动,在诊断问题是非常有用。此日志文件可能会很快变得非常大,因此不应该长期使用它。此日志通常名为hostname.log,位于data目录中。词名字可以用–log命令行选项更改。
- 二进制日志。它记录更新过数据(或者可能更新过数据)的所有语句。此日志通常名为hostname-bin,位于data目录中。此名字可以用–log-bin命令行选项更改。注意,这个日志文件是MySQL 5中添加的,此前的MySQL版本使用的是更新日志。
- 缓慢查询日志。顾名思义,此日志查询执行缓慢的任何查询。这个日志在确定数据库何处需要优化很有用。此日志通常名为hostname-slow.log,位于data目录中。此名字可以用–log-slow-queries命令行选项更改。
在使用日志时,可用FLUSH LOGS语句来刷新和重新开始所有日志文件