mysql 必知必会总结

浏览数：21 / 时间：2015年06月12日

以前 mysql 用的不是很多， 2 天看了一遍 mysql 必知必会又复习了一下基础。 200 页的书，很快就能看完，

大部分知识比较基础，但还是了解了一些以前不知道的知识点。自己做一个备份，随时查看。

命令：
sql 不区分大小写，语句大写，列、表名小写是一种习惯
连接命令：mysql -u user_name –h example.mysql.alibabalabs.com –P3306 –pxxxx
quit
help show; // 查看所有 show 命令
show databases;
use dbname;
show tables;
show columns from tablename; (简写 desc tablename;)
show status;
show grants;
show create database dbname; // 查看创建某一个数据库的语句
show create table tablename; // 查看创建某一个表的语句

查询：
// 检索过多不需要的列会降低性能，尽量用什么查什么
select * from users;
// distinct
select distinct title from users;
// limit
select * from users limit 5;
// 检索从第 5 行开始的 5 行
select * from users limit 5,5;
// 检索第二行，行号从 0 开始
select * from users limit 1,1;

排序：
// ** mysql 按字母顺序排序，默认 A 和 a 是一样的，但可配置
select * from news order by title;
// between and
select * from news where id between 3 and 6;
// NULL 空值检查
select * from news where created is null;
// 括号改变计算顺序
select prod_name,prod_price from products where (id=1002 or id=1003) and prod_price >= 10;
// IN
select * from news where id in(6,8,9,222);
通配符避免过度使用

正则表达式
select title from news where title regexp ‘.11‘;
select title from news where title regexp ‘1000|2000|3000‘; // [1,2,3] [1|2|3] [^123]
// 匹配特殊字符需要 \\ 转义
select * from news where content regexp ‘\\.‘
// 预定义字符集
[:alnum:] 任意字母和数字
[:alpha:] 任意字符同 [a-zA-Z]
[:digit:] 任意数字同 [0-9]
‘\$[0-9] sticks?\$‘
定位符
^ $
[[:<:]] 匹配词的开始
[[:>:]] 匹配词的结束

数据处理
// concat 函数连接字符串
select concat(title,‘ (‘,content,‘)‘) as aaa from news;
// ltrim rtim trim 函数去除空格
select trim(title) from news where title regexp ‘bbb‘;

select 语句也可以用来测试语句
select 3*2;
select now();
select ‘hello world‘ regexp ‘world‘; // 返回 1

函数标准 SQL 可移植性强，函数移植性不是很强，各个 DBMS 实现很可能不同
// 字符串处理
upper() lower() length() sustring()
// 日期和时间处理函数
AddDate() AddTime() CurDate() CurTime() Date() DateDiff()
Date_Add() Date_Format() Day() DayOfWeek() Hour() Minute()
Month() Now() Second() Time() Year()
// 查询 2014-12-15 的数据
select * from news where Date(created) = ‘2014-12-15‘;
// 查询 2014年11月的数据
select * from news where Year(created)=2014 and Month(created)=11;

// 数值处理函数
Abs() Sqrt() Sin() Cos() Mod() Pi() Rand()
// 聚合函数, 能用 SQL 直接计算的尽量不要用程序处理
AVG() COUNT() MAX() MIN() SUM()
// count 作用于列时会忽略 NULL
select count(created) from news;

// HAVING 用于过滤分组
select title ,count(*) as num from news group by title having count(*)>=2;
// 分组时不能保证排序，一般还需要自己显示排序
select title ,count(*) as num from news group by title having count(*)>=2 order by num;
// 相关子查询
// 不只一种解决方案, 不一定是解决这种查询最有效的方法，也可联结表，多做实验，验证性能
select cust_name,(select count(*) from orders where orders.cust_id=customers.cust_id) from customers order by cust_name;
// 子查询嵌套过多时一层一层调试，先从最里层硬编码

表联结
select vender.id as vender_id,vender_name,products.id as prod_id,prod_name,prod_price from vender, products where vender.id=products.vender_id;
// 内部联结结果同上,推荐这种明确联结的语法
select vender_name,prod_name,prod_price from vender inner join products on vender.id=products.vender_id;
// 自联结，联结自身进行查询（相比子查询究竟哪个效率高，应该多做实验）
select p1.prod_name from products as p1, products as p2 where p1.vender_id=p2.vender_id and p2.prod_name=‘product2‘;

// 外部联结联结中包含了那些在相关表中没有关联的行
// 没有订单的客户也会被联结进来
// left right 指定左边或右边的表必须包含所有的行(无论是否匹配)
SELECT `cust_name`, `order_num` from `customer` left outer join `order` on `customer`.`cust_id`=`order`.`cust_id`;

UNION
// 组合多条 select 语句
// UNION ALL （union 默认不包含重复行， UNION ALL 包含重复行）

全文本搜索 InnoDB 不支持 MyISAM 支持
全文搜素会对结果根据优先级进行排序

复制导入表数据 insert select
insert into customers(id,name) select cust_id,cust_name from newcust;

删除表中所有的行 truncate tablename 比 delete from tablename 更快
因为 truncate 是先删除原来的表再创建一个新表

// autoincrement 也可以手动插入一个值，只要唯一就行
select last_insert_id()；返回自增长列当前最大值,与表无关

数据库引擎
InnoDB 支持可靠的事物处理
MEMORY 功能等同与 MyISAM ，数据存储在内存中（试用与临时表）
MyISAM 高性能引擎，支持全文搜索，不支持事物处理
外键不能跨引擎

// 更改表
alter table news add name varchar(50);
alter table news drop column name;
alter table products add constraint fk_products_venders foreign key (vender_id) references venders(vender_id);
rename table news to news1;

// 视图就是一个 SQL 语句
// 视图最常用的是查询，但也能进行更新操作
create view viewname;
show create view viewname;
drop view viewname;

存储过程
// 调用存储过程
call procedurename(@param1,@param2,@param3);
// 创建存储过程
creage procedure getTitle()
begin
select title from news;
end;
// 命令行临时改变分隔符
delimiter //
delimiter ;
// 删除存储过程
drop procedure procedurename;
drop procedure procedurename if exists;
// 传参数
delimiter //
CREATE procedure getCount(out p int)
begin
select count(*)
into p
from news;
end //
delimiter ;
// 调用
call getCount(@ret);
// 查询
select @ret;

// 查看存储过程创建语句
show create procedure procedurename;
// 获取存储过程列表
show procedure status;

// ***********************
// 存储过程一般用于处理比较复杂的业务逻辑
// 一般根据不同条件执行不同的操作
-- Name: ordertotal
-- Parameters: onumber = order number
-- taxable = 0 if not taxable. 1 if taxable
-- ototal = order total variable
create procedure ordertotal(
in onumber int,
in taxable boolean,
out ototal decimal(8,2)
)
begin
declare total decimal(8,2);
declare taxrate int default 6;
select sum(item_price * quantity)
from orderitems
where order_num = onumber
into total;

if taxable then
select total + (total/100*taxrate) into total;
end if;

select total into ototal;
end;
// ************************

// 游标（作用于存储过程和函数）
// 使用游标遍历数据

触发器(定义在表上), 触发器中不能返回数据
create trigger inserttrigger after insert on news for each row insert into vender(vender_name) values(‘new vender‘);
// 删除触发器
drop trigger inserttrigger;
// insert 触发器可以引用一个名为 new 的虚拟表访问到被插入的数据
// delete 触发器可以引用一个名为 old 的虚拟表访问到被删除的数据

事务处理
start transaction
rollback
commit
// rollback 能回退 insert update delete 操作，不能回退 create drop 操作
// 保留点 savepoint
// 复杂的事务可以设置多个保留点，便于回退到某个保留点而不是回滚整个事务
savepoint delete1
rollback to delete1
// 禁用自动提交
set autocommit = 0

// 显示所有字符集
show character set;
// 显示所有校对 (字符比较规则)
show collation;
// 显示当前使用的字符集
show variable like ‘character%‘;
// 显示当前使用的校对
show variable like ‘collation‘;
// 给表指定字符集和校对
// 列也可以指定字符集和校对
create table mytable(
column1 int,
column2 varchar(10) character set latin1 collate latin1_general_ci
) default character set utf8
collate utf8_general_ci;
// select 时临时改变默认校对
select * from customers order by lastname collate latin1_geeral_cs

安全管理
现实中一定不要使用 root
所有用户存储在 mysql 数据库的 user 表中
// password 采用 MD5加密,简单的密码一搜就能查出来
select user,password from user;
创建用户
CREATE USER ‘aaa‘@‘localhost‘ IDENTIFIED BY ‘***‘;
// 重命名
rename user aaa to bbb;
// 删除用户账户及相关的权限
drop user aaa;
// 查看访问权限,权限由用户名和 host 结合定义
show grants for aaa; (=show grants for aaa@%;)
show grants for aaa@localhost;
// 授予用户权限
// 用户 aaa 可以访问 mydb 数据库的所有表
grant select on mydb.* to aaa;
mysql> show grants for aaa;
+---------------------------------------+
| Grants for aaa@% |
+---------------------------------------+
| GRANT USAGE ON *.* TO ‘aaa‘@‘%‘ |
| GRANT SELECT ON `mydb`.* TO ‘aaa‘@‘%‘ |
+---------------------------------------+
// 撤销权限
revoke select on mydb.* from aaa;
// 更新口令
set password for aaa = Password(‘123456‘);
// 更新当前用户的口令
set password = Password(‘‘);