基本概念
数据库(database) 保存有组织的数据的容器(通常是一个文件或一组文件)。类似于文件柜。
表(table) 某种特定类型数据的结构化清单。类似于文件。
模式(schema) 关于数据库和表的布局及特性的信息。
列(column) 表中的一个字段。所有表都是由一个或多个列组成的。
数据类型(datatype) 所容许的数据的类型。每个表列都有相应的数据类型,它限制(或容许)该列中存储的数据。
行(row) 表中的一个记录。
主键(primary key) 列(或一组列),其值能够唯一区分表中每个行。主键列不允许NULL值。
不更新主键列中的值;
不重用主键列的值;
不在主键列中使用可能会更改的值。
使用
use databaseName; -- 选择数据库 之后才能读取其中的数据
show databases; -- 显示可用数据库
show tables; -- 返回当前选择的数据库内可用表的列表
show columns from tableName; -- 对每个字段返回字段名、数据类型、是否允许 NULL 、键信息、默认值以及其他信息
describe tableName; -- 同上
show grants; -- 显示授予用户的安全权限
show status; -- 显示广泛的服务器状态信息
show errors; -- 显示服务器错误消息
show warnings; -- 显示服务器警告消息
检索
select columnName1,... from tableName; -- 从表中检索列
select * from tableName; -- 检索所有列
select distinct columnName from tableName; -- 返回不同值 distinct 关键字应用于所有列 所有列均相同 则不返回
select * from tableName limit count; -- 返回不多于 count 行
select * from tableName limit start, count; -- 返回 start 开始的 count 行
select * from tableName limit count offset start; -- 同上
排序
select * from tableName order by columnName,... (desc/asc); -- 按 columnName 列字母顺序排序
select columnName from tableName order by columnName (desc/asc) limit 1; -- 最大值/最小值
过滤
select * from tableName where condition;
| 操作符 | 说明 |
|---|---|
| = | 等于 |
| <> | 不等于 |
| != | 不等于 |
| < | 小于 |
| <= | 小于等于 |
| > | 大于 |
| >= | 大于等于 |
| between ... and ... | 之间 |
| is null | 空值 |
| in ( x, y, z,..) | 与 or 类似 |
| not | 非 |
通配符
select * from tableName where columnName like 'a%'; -- 以a开头
| 通配符 | 说明 |
|---|---|
| % | 任何字符出现任何次数 |
| _ | 一个字符 |
正则表达式
select * from tableName where columnName regexp pattern;
常用函数
| 函数 | 说明 |
|---|---|
| left() | 返回串左边的字符 |
| length() | 返回串的长度 |
| locate() | 找出串的一个子串 |
| lower() | 将串转换为小写 |
| ltrim() | 去掉串左边的空格 |
| right() | 返回串右边的字符 |
| rtrim() | 去掉串右边的空格 |
| soundex() | 返回串的SOUNDEX值 |
| substring() | 返回子串的字符 |
| upper() | 将串转换为大写 |
| adddate() | 增加一个日期(天、周等) |
| addTime() | 增加一个时间(时、分等) |
| curdate() | 返回当前日期 |
| curtime() | 返回当前时间 |
| date() | 返回日期时间的日期部分 |
| datediff() | 计算两个日期之差 |
| date_format() | 返回一个格式化的日期或时间串 |
| day() | 返回一个日期的天数部分 |
| dayofweek() | 对于一个日期,返回对应的星期几 |
| hour() | 返回一个时间的小时部分 |
| minute() | 返回一个时间的分钟部分 |
| month() | 返回一个日期的月份部分 |
| now() | 返回当前日期和时间 |
| second() | 返回一个时间的秒部分 |
| time() | 返回一个日期时间的时间部分 |
| year() | 返回一个日期的年份部分 |
| abs() | 返回一个数的绝对值 |
| exp() | 返回一个数的指数值 |
| mod() | 返回除操作的余数 |
| rand() | 返回一个随机数 |
| sqrt() | 返回一个数的平方根 |
| avg() | 返回某列的平均值 |
| count() | 返回某列的行数 |
| max() | 返回某列的最大值 |
| min() | 返回某列的最小值 |
| sum() | 返回某列值之和 |
分组
select count(*) as num from tableName group by columnName;
联结表
联结是一种机制,用来在一条 SELECT 语句中关联表,因此称之为联结。使用特殊的语法,可以联结多个表返回一组输出,联结在运行时关联表中正确的行。
内部联结(等值联结)：基于表间的相等测试
select vend_name, prod_name from vendors inner join products on vendors.vend_id = products.vend_id;
自联结：替代从相同表中检索的子查询
select p1.prod_id, p1.prod_name from product as p1, product as p2
where p1.vend_id = p2.vend_id and p2.prod_id = 'DTNTR';
自然联结：排除列的多次出现
外部联结：包含在相关表中没有关联行的行
left:outer join 左边的表
right:out join 右边的表
select vend_name, prod_name from vendors left outer join products on vendors.vend_id = products.vend_id;
组合查询
用 union 分隔 select 语句 自动去除重复行
- 在单个查询中从不同的表返回类似结构的数据
- 对单个表执行多个查询,按单个查询返回数据
插入数据
insert into tableName(columnName, ...) values(value, ...); -- 插入一条数据
insert into tableName(columnName, ...) values(value, ...), (value, ...); -- 插入多条数据
更新数据
update tableName set columnName = value where condition;
删除数据
delete from tableName where condition;
- 除非确实打算更新和删除每一行,否则绝对不要使用不带 WHERE 子句的 UPDATE 或 DELETE 语句。
- 保证每个表都有主键,尽可能像 WHERE 子句那样使用它(可以指定各主键、多个值或值的范围)。
- 在对 UPDATE 或 DELETE 语句使用 WHERE 子句前,应该先用 SELECT 进行测试,保证它过滤的是正确的记录,以防编写的 WHERE 子句不正确。
- 使用强制实施引用完整性的数据库,这样MySQL将不允许删除具有与其他表相关联的数据的行。
创建表
create table tableName (
columnName type NOT NULL AUTO_INCREMENT,
columnName type NULL,
columnName type NOT NULL DEFAULT 1,
primary key (columnName)
) engine = engineName;
更新表
alter table tableName add columnName type; -- 添加列
alter table tableName drop column columnName; -- 删除列
删除表
drop table tableName;
重命名表
rename table oldName to newName;
视图
视图是虚拟的表。与包含数据的表不一样,视图只包含使用时动态检索数据的查询。
作用：
- 重用SQL语句。
- 简化复杂的SQL操作(如联结)。在编写查询后,可以方便地重用它而不必知道它的基本查询细节。
- 使用表的组成部分而不是整个表。
- 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
- 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
使用：
- 视图用 CREATE VIEW 语句来创建。
- 使用 SHOW CREATE VIEW viewname 来查看创建视图的语句。
- 用 DROP 删除视图,其语法为 DROP VIEW viewname;。
- 更新视图时,可以先用 DROP 再用 CREATE,也可以直接用 CREATE ORREPLACE VIEW。如果要更新的视图不存在,则第 2 条更新语句会创建一个视图;如果要更新的视图存在,则第 2 条更新语句会替换原有视图。
create view viewName as select ...;
存储过程
为以后的使用而保存的一条或多条MySQL语句的集合。
-- 创建存储过程
create procedure procedureName (
in p1 type, -- in 传递给存储过程
out p2 type, -- out 从存储过程传出
inout p3 type -- inout 对存储过程传入和传出
) comment '注释 可选'
begin
-- sql 语句
select count(*) into p2 from tableName;
-- 声明变量
declare varName type;
-- if 语句
if varName then
-- sql 语句
end if;
end;
-- 调用存储过程
call procedureName(@p1, @p2, @p3);
-- 显示变量
select @p1;
-- 删除存储过程
drop procedure procedureName;
事务处理
__事务处理(transaction processing)__可以用来维护数据库的完整性,它保证成批的MySQL操作要么完全执行,要么完全不执行。
并非所有引擎都支持事务处理。 MyISAM 和 InnoDB 是两种最常使用的引擎。前者不支持明确的事务处理管理,而后者支持。
事务(transaction) 指一组SQL语句;
回退(rollback) 指撤销指定SQL语句的过程;
提交(commit) 指将未存储的SQL语句结果写入数据库表;
保留点(savepoint) 指事务处理中设置的临时占位符(place-holder),你可以对它发布回退。事务处理完成后自动释放。
start transaction; -- 开始事务
delete from tableName;
rollback; -- 回退 只能在一个事务处理内使用 不能回退 create drop
start transaction;
delete from tableName where condition;
commit; -- 提交 若 delete 失败 则不会提交
savepoint p1; -- 创建保留点
rollback to p1; -- 回退
用户管理
-- 查看用户列表
use mysql;
select user from user;
create user username identified by 'password'; -- 创建用户
drop user username; -- 删除用户
show grants for username; -- 查看用户权限
grant select on database.table to username@host -- 赋予 select 权限
revoke select on database.table from username -- 撤销权限
权限控制：
- 整个服务器,使用 GRANT ALL 和 REVOKE ALL;
- 整个数据库,使用 ON database.*;
- 特定的表,使用 ON database.table;
- 特定的列;
- 特定的存储过程。
数据类型
串
| 类型 | 说明 |
|---|---|
| CHAR | 1～255个字符的定长串 创建时指定长度 |
| ENUM | 接受最多64 K个串组成的一个预定义集合的某个串 |
| LONGTEXT | 与TEXT相同,但最大长度为4 GB |
| MEDIUMTEXT | 与TEXT相同,但最大长度为16 K |
| SET | 接受最多64个串组成的一个预定义集合的零个或多个串 |
| TEXT | 最大长度为64 K的变长文本 |
| TINYTEXT | 与TEXT相同,但最大长度为255字节 |
| VARCHAR | 长度可变,最多不超过255字节。VARCHAR(n),可存储0到n个字符的变长串 |
数值
| 类型 | 说明 |
|---|---|
| BIT | 位字段,1～64位 |
| BIGINT | 整数值 |
| BOOLEAN(或BOOL) | 布尔标志,或者为0或者为1 |
| DECIMAL(或DEC) | 精度可变的浮点值 |
| DOUBLE | 双精度浮点值 |
| FLOAT | 单精度浮点值 |
| INT(或INTEGER) | 整数值 |
| MEDIUMINT | 整数值 |
| REAL | 4字节的浮点值 |
| SMALLINT | 整数值 |
| TINYINT | 整数值 |
日期和时间
| 类型 | 说明 |
|---|---|
| DATE | 表示1000-01-01~9999-12-31的日期,格式为 YYYY-MM-DD |
| DATETIME | DATE 和 TIME 的组合 |
| TIMESTAMP | 功能和DATETIME相同(但范围较小) |
| TIME | 格式为 HH:MM:SS |
| YEAR | 用2位数字表示,范围是70(1970年)69(2069年),用4位数字表示,范围是1901年2155年 |
二进制
| 类型 | 说明 |
|---|---|
| BLOB | Blob最大长度为64 KB |
| MEDIUMBLOB | Blob最大长度为16 MB |
| LONGBLOB | Blob最大长度为4 GB |
| TINYBLOB | Blob最大长度为255字节 |
参考资料：《MySQL 必知必会》 Ben Forta 著