MySQL 基础

基本概念

数据库(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;

通配符

select * from tableName where columnName like 'a%'; -- 以a开头

正则表达式

select * from tableName where columnName regexp pattern;

常用函数

分组

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      -- 赋予 select 权限
revoke select on database.table from username       -- 撤销权限

权限控制：?

• 整个服务器,使用 GRANT ALL 和 REVOKE ALL;
• 整个数据库,使用 ON database.*;
• 特定的表,使用 ON database.table;
• 特定的列;
• 特定的存储过程。

数据类型

串

数值

日期和时间

二进制

参考资料：《MySQL 必知必会》 Ben Forta 著