mysql必知必会总结

SELECT * from products;
-- 查询所有
select DISTINCT vend_id from products;
-- DISTINCT 去重 应用于所有列不仅仅是前置它的列
SELECT prod_name FROM products LIMIT 5;
-- 限制结果的行数
SELECT prod_name FROM products LIMIT 5,5;
-- 从第几行到第几行

SELECT products.vend_id FROM testmysql.products;
-- 完全限定的表名和列名

排序

– 子句 order by

SELECT prod_name FROM products ORDER BY prod_name;

– 通过非选择列排序

SELECT prod_name FROM products ORDER BY prod_id;

– 多列排序 先按前一个字段再按后一个字段排序 DESC 降序 asc 升序

SELECT prod_price,prod_name,prod_id FROM products ORDER BY prod_name,prod_price;
SELECT prod_price,prod_name,prod_id FROM products ORDER BY prod_price DESC;
SELECT prod_price,prod_name,prod_id FROM products ORDER BY prod_price DESC,prod_name ;

– 找出最贵的价格

SELECT prod_price FROM products ORDER BY prod_price desc limit 1;

过滤数据

– where 子句 + 过滤条件
SELECT prod_name,prod_price FROM products where prod_price =2.50;
– where 位置应在order by之前
– where 子句操作符 = <> != < <= > >= BETWEEN
– 单引号什么时候用 1.字符串需要用 2.值与串进行比较 注:值与数值列比较不需要
– != 与<>的区别 != 比较的数值 <>比较的是字符串
SELECT vend_id ,prod_name FROM products where vend_id !=1003;
SELECT vend_id ,prod_name FROM products where vend_id <>1003;
– BETWEEN start and end 包含开始值和结束值
– 空值检查
SELECT prod_name FROM products where prod_price is null;

数据过滤

– 组合where 子句 用 and 或 or 组合子句
SELECT prod_id,prod_price,prod_name FROM products where prod_id=1003 and prod_price<=10;
SELECT prod_id,prod_price,prod_name FROM products where prod_id=1003 or prod_price<=10;
– 计算顺序问题 and 优先级高 可以通过()来解决优先级问题 注:尽量加上括号取消歧义
SELECT prod_name ,prod_price FROM products where vend_id=1003 or vend_id=1003 and prod_price>=10;
SELECT prod_name ,prod_price FROM products where (vend_id=1003 or vend_id=1003 )and prod_price>=10;
– in not 操作
– in 和 or 完成功能类似 why use in ? 1.长合法选项清单时,in更加清楚和直观 2.计算次序更容易管理 3.比or快 4.可以包含其他select 语句,更加动态的建立where 子句.
– not 否定之后跟的任何条件 支持对 in BETWEEN EXISTS 取反
SELECT prod_name,prod_price FROM products where vend_id in (1003,1002) ORDER BY prod_name;
SELECT vend_id, prod_name,prod_price FROM products where vend_id not in (1003,1002) ORDER BY prod_name;

通配符过滤

– like 指示mysql后跟的搜索模式不是直接相等匹配,而是利用通配符
– xxx% 以xxx开头 %xxx 以xxx结尾 %xxx% 包含xxx的字符 搜索区分大小写 注:不可以匹配null
– _匹配符与%用法一致,但是表示单个字符
SELECT prod_id,prod_name FROM products WHERE prod_name like ‘% ton anvil’;
– 使用注意: 更加耗费性能 1.其他操作符能实现的话不要用通配符 2.确实需要不要放在起始处,这样最慢

正则搜索

– 基本字符搜索 REGEXP 与like 类似,不过把regexp 后跟的东西当做正则处理
– 可以使用. ^ $ |不区分大小写,如果要区分使用 BINARY
– 匹配几个字符之一 [123] 1或2或3 [123]是1|2|3 的缩写 [^123] 可以匹配除123外的任何东西
– 匹配范围[0123456789]可以写为[0-9] 不仅数字[a-z]
– 匹配特殊字符用\为前导转义 如要找包含.的字符 \. \f换页 \n 换行 \r 回车 \v 纵向制表 \\ 表示
– 匹配字符类
– [：alnum:]任意字母和数字（同[a-zA-Z0-9]）
– [：alpha:]任意字符（同[a-zA-Z]）
– [：blank:]空格和制表（同[\t]）
– [：cntr1：]ASCⅡ控制字符（ASCII0到31和127）
– [：digit:]任意数字（同[0-9]）
– [：graph:]与[：print:]相同，但不包括空格
– [：1ower:]任意小写字母（同[a-z]）
– [：print:]任意可打印字符
– [：punct:]既不在[：alnum:]又不在[：cntr1：]中的任意字符
– [：space:]包括空格在内的任意空白字符（同[\f\n\rl\t\v]）
– [：upper:]任意大写字母（同[A-Z]）
– [：xdigit:]任意十六进制数字（同[a-fA-FO-9]）
– 多实例匹配 当要匹配的不止一个时
– * 0个或多个匹配
– + 1个或多个匹配({1,})
– ？0个或1个匹配（等于{0，1}）
– {n} 指定数目的匹配
– {n，}不少于指定数目的匹配
– {n，m}匹配数目的范围（m不超过255）

SELECT prod_name FROM products where prod_name REGEXP ‘1000’ ORDER BY prod_name;
SELECT prod_name FROM products where prod_name REGEXP ‘\.’ ORDER BY prod_name;
SELECT prod_name FROM products where prod_name REGEXP ‘.000’ ORDER BY prod_name;
SELECT prod_name FROM products where prod_name REGEXP ‘1000|2000’ ORDER BY prod_name;
SELECT prod_name FROM products where prod_name REGEXP ‘[123] Ton’ ORDER BY prod_name;
SELECT prod_name FROM products where prod_name REGEXP ‘[1-5] Ton’ ORDER BY prod_name;
SELECT prod_name FROM products where prod_name REGEXP ‘1|2|3 Ton’ ORDER BY prod_name;
SELECT prod_name FROM products where prod_name REGEXP ‘.000’ ORDER BY prod_name;
– ? 很好解决stick 与sticks 不好匹配的问题
SELECT prod_name FROM products where prod_name REGEXP ‘\([0-9] sticks?\)’ ORDER BY prod_name;
– 连在一起的四位数字 [[:digit:]] 表示数字 {4}表示4个数 可以写为[0-9][0-9][0-9][0-9]
SELECT prod_name FROM products where prod_name REGEXP ‘[[:digit:]]{4}’ ORDER BY prod_name;
SELECT prod_name FROM products where prod_name REGEXP ‘[0-9][0-9][0-9][0-9]’ ORDER BY prod_name;
SELECT prod_name FROM products where prod_name REGEXP ‘[0-9]{4}’ ORDER BY prod_name;
– 定位符 ^文本开始 $文本结尾 [[:<:]]词开始 [[:>:]]词结尾 ^两种用法 ¹表示开始 [^]表示否定
– 找出以数开始的产品
SELECT prod_name FROM products where prod_name REGEXP ‘²’ ORDER BY prod_name;

计算字段 不存在于数据库中,是执行查询时创建的

– 使用场景:两个不同表的字段放在一个字段中 把不同列的东西检索出来作为一个字段 列的数据大小写混写但需要按大写的格式展示 计算总数 平均数或其他计算
– 拼接字段 Concat()来拼接列 Rtrim() 删除右侧空格 trim 去两侧空格 ltrim 去右侧空格 as 跟别名
SELECT CONCAT(vend_name,’(’,vend_country,’)’) FROM vendors ORDER BY vend_name;
SELECT CONCAT(vend_name,’(’,Rtrim(vend_country),’)’) FROM vendors ORDER BY vend_name;
SELECT CONCAT(vend_name,’(’,vend_country,’)’) as aa FROM vendors ORDER BY vend_name;
– 算术计算 ±/
SELECT prod_id ,quantity,item_price, quantityitem_price as expanded_price from orderitems where order_num=20005;

数据处理函数

– 函数类型 : 文本函数 数值函数 日期时间函数 系统函数
– 文本处理函数:Rtrim 去空格 Upper 大写 left 返回左边字符串 length 返回串长度 locate找出子串 right() lower() ltrim() right() soudex() substring()
SELECT vend_name,upper(vend_name) as uppname from vendors ORDER BY vend_name;
– left(str,length)截取左边一定长度的字符
SELECT vend_name,upper(vend_name) as uppname ,left(vend_name,4) as leftname FROM vendors ORDER BY vend_name;
SELECT vend_name,upper(vend_name) as uppname ,LENGTH(vend_name) FROM vendors ORDER BY vend_name;
– SOUNDEX(str)返回发音类似的
SELECT cust_name,cust_contact from customers where SOUNDEX(cust_contact)=SOUNDEX(‘Y lie’);
– 日期时间函数
– AddDate（）增加一个日期（天、周等）AddTime（）增加一个时间（时、分等）CurDate（）返回当前日期CurTime（）返回当前时间Date（）返回日期时间的日期部分DateDiff（）计算两个日期之差Date_Add（）高度灵活的日期运算函数Date_Format（）返回一个格式化的日期或时间串Day（）返回一个日期的天数部分DayofWeek（）对于一个日期，返回对应的星期几Hour（）返回一个时间的小时部分Minute（）返回一个时间的分钟部分Month（）返回一个日期的月份部分Now（）返回当前日期和时间Second（）返回一个时间的秒部分Time（）返回一个日期时间的时间部分Year（）返回一个日期的年份部分
– 存的日期带时间 根据日期查询怎么办 Date(字段)=日期
SELECT cust_id,order_num,order_date from orders where order_date=‘2005-09-01’;
SELECT cust_id,order_num from orders where date(order_date)=‘2005-09-01’;
– 日期范围查找 data(字段) between startDate and enddate 或者 Year(columns)=YYYY and Month(COLUMNS)=mm 查某月
SELECT cust_id,order_num ,order_date from orders where date(order_date)BETWEEN ‘2005-09-01’ and ‘2005-09-30’;
SELECT cust_id,order_num ,order_date from orders where year(order_date)=2005 and MONTH(order_date)=9;
– 数值处理函数
– Abs（）返回一个数的绝对值
– Cos（）返回一个角度的余弦
– EXp（）返回一个数的指数值
– Mod（）返回除操作的余数
– Pi（）返回圆周率
– Rand（）返回一个随机数
– Sin（）返回一个角度的正弦
– Sqrt（）返回一个数的平方根
– Tan（）返回一个角度的正切

汇总数据

– 场景: 确定表行数 行组的和 表列最大值最小值平均值
– AVG（）返回某列的平均值
– COUNT（）返回某列的行数
– MAX（）返回某列的最大值
– MIN（）返回某列的最小值
– SUM（）返回某列值之和
SELECT avg(prod_price) as avg_price FROM products;
SELECT avg(prod_price) as avg_price FROM products WHERE vend_id=1003;
– count 函数 count() 对表行进行计数,不关表列是否为空值 count(column) 对特定列计数,忽略null
SELECT count() as num_cust from customers;
SELECT count(cust_email) as num_cust from customers;
– max(COLUMN) 返回列中最大值 min 最小值 sum 指定列的总和
– 聚集不同值 默认对所有行执行计算 只计算不同的值用distinct
SELECT avg(DISTINCT prod_price) as avgprice from products where vend_id=1003;
– 组合聚合函数
SELECT COUNT(*)AS num_items,MIN(prod_price)AS price_min,MAX(prod_price)AS price_max, AVG(prod_price)AS price_avg FROM products;

数据分组

– 显示每个分组的个数 用于统计每个分组的XXX 注:聚集是聚集的表,分组是把分好的组聚集
– GROUP BY 后面可以跟多个列,可以更细致的分组; 后面的列必须是真实的列不可以是聚集后的列;SELECT后的每个列都要在group by 中给出 ;group by在 where 之后 ORDER BY 之前
SELECT vend_id,count() as numprods from products group by vend_id;
SELECT vend_id,count() as numprods from products group by vend_id WITH ROLLUP;
– 过滤分组 having 注:having 过滤分组,where过滤行 ;// where 在分组前过滤,having在分组后过滤
SELECT cust_id ,count() as orders from orders GROUP BY cust_id HAVING count()>=2;
SELECT vend_id ,count() as orders from products WHERE prod_price>=10 GROUP BY vend_id HAVING count()>=2;
SELECT order_num,sum(quantityitem_price) as ordertotal from orderitems GROUP BY order_num HAVING sum(quantityitem_price)>=50 ORDER BY ordertotal;
– SELECT子句顺序 select from WHERE GROUP BY HAVING ORDER BY LIMIT;

子查询

– 子查询都是从里向外查询
SELECT order_num from orderitems where prod_id=‘TNT2’;
SELECT cust_id FROM orders WHERE order_num in (20005,20007);

SELECT cust_id FROM orders WHERE order_num in (SELECT order_num from orderitems where prod_id=‘TNT2’);
SELECT cust_name ,cust_contact from customers where cust_id in (10001,10004);
SELECT cust_name ,cust_contact from customers where cust_id in (SELECT cust_id FROM orders WHERE order_num in (SELECT order_num from orderitems where prod_id=‘TNT2’));
– 子查询可以作为计算字段
SELECT cust_name, cust_state,(SELECT COUNT(*)FROM orders WHERE orders. cust_id=customers. cust_id) AS orders FROM customers ORDER BY cuSt_name;

联结表

– 创建联结 指定联结关联就好 如果没有where条件则会将第一个表的每个行与第二个表每行配对,进行笛卡尔积
SELECT vend_name ,prod_name,prod_price from vendors,products where vendors.vend_id=products.vend_id ORDER BY vend_name,prod_name;
– 直接联结 与inner join等价 推荐用inner join 联结的表越多越影响性能 子查询 可以用联结表替换
SELECT cust_name ,cust_contact from customers where cust_id in (SELECT cust_id FROM orders WHERE order_num in (SELECT order_num from orderitems where prod_id=‘TNT2’));
– 等价于下面
SELECT cust_name,cust_contact from customers,orders,orderitems where customers.cust_id=orders.cust_id and orders.order_num=orderitems.order_num and orderitems.prod_id=‘TNT2’;

高级联结

– 使用别名 1.缩短SQL 2.可以在单条SQL中多次使用相同的表
– 自联结
SELECT p1.prod_id,p1.prod_name FROM products as p1,products as p2 WHERE p1.vend_id=p2.vend_id and p2.vend_id=‘DTNTR’;
– left join right join

组合查询 将多个SQL语句组合成一个结果集

– 场景: 1.单个查询中从不同的表返回类似结构的数据 2.对单表执行多个查询按单个查询返回数据
– union 使用 每条SQL之间放union
SELECT vend_id,prod_id,prod_price from products WHERE prod_price<=5 UNION SELECT vend_id,prod_id,prod_price from products WHERE vend_id in(1001,1002);
– union联结多个select语句,每个查询包含相同的列,表达式和聚集函数;列数据类型兼容 注:默认去重行
SELECT vend_id,prod_id,prod_price from products WHERE prod_price<=5 UNION all SELECT vend_id,prod_id,prod_price from products WHERE vend_id in(1001,1002);
– 排序 对结果集排序,不能加多个orderby
SELECT vend_id,prod_id,prod_price from products WHERE prod_price<=5 UNION SELECT vend_id,prod_id,prod_price from products WHERE vend_id in(1001,1002) ORDER BY vend_id,prod_price;

全文本搜索 myisam 支持,innodb不支持

– 通配符和正则耗费性能,难以精确匹配 // 全文本可以解决问题
– 建表时 使用fulltext(字段) 使用 select from where match(字段) against(’’) 查什么不要什么
– 布尔文本搜索

插入语句

– 插入完整行
insert into customers VALUES(null,‘pep e.lapew’,‘100’,‘los’,‘ca’,‘900356’,‘usa’,NULL,NULL);
– 插入部分
insert into customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email) VALUES(‘pep e.lapew’,‘100’,‘los’,‘ca’,‘900356’,‘usa’);
– 设置执行优先级
insert LOW_PRIORITY into customers VALUES(null,‘pep e.lapew’,‘100’,‘los’,‘ca’,‘900356’,‘usa’,NULL,NULL);
– 单挑insert 可以插入多条数据 insert into table VALUES(’’,’’,’’),(’’,’’,’’)
– insert select 插入查询出的数据
insert into customers SELECT cust_id ,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country from custnew;

更新和删除数据

– 更新特定行或所有行 注:条件不要忘 update table set COLUMN=’’ where xxx
– 删除特定行或所有行 注:条件 delete from table where

创建和操纵表

create table table_new(
cust_id int not null AUTO_INCREMENT,
cust_name char(50) not null,
PRIMARY KEY (cust_id)
) ENGINE=INNODB;
– 不想表被覆盖 在表名后跟 is not EXISTS
create table table_new1 if not EXISTS(
cust_id int not null AUTO_INCREMENT,
cust_name char(50) not null,
PRIMARY KEY (cust_id)
) ENGINE=INNODB;
– 主键可以是多个primary key(c1,c2),但是必须唯一
– 设置默认值 default 1;
– 更新表 新加列 alter table tablename add COLUMN_name char(20); 删除列 alter table drop column column_name
– 删表 drop table table_name;
– 重命名 rename table old_name to new_name,old_name2 to new_name2,oldname3 to newName3;

视图

– 场景:1.重用SQL 2.简化SQL操作 3.使用表的组成部分而不是表 4.保护数据 5.更改数据格式和表示
– 注:视图连接或嵌套复杂影响性能
– 规则:视图名唯一/视图数量无限/视图可以嵌套/order by 可以使用,但会覆盖视图中的 ORDER BY/无索引/可以和表一起使用
– create view SELECT 创建视图
– show create view view_name //查看创建视图的语句
– drop再create view /replace view 更新视图

存储过程

– why use? 把处理封装简化操作/不用重复建立操作步骤,开发用的代码一样保证数据一致/简化变动管理/提高性能/功能强大
– 执行 call procedure_name(arg1,arg2)
– 创建 CREATE PROCEDURE name() BEGIN xxx END
– 删除 drop PROCEDURE name;
– 参数使用
CREATE PROCEDURE productpricing(
OUT p1 DECIMAL(8,2),OUT ph DECIMAL(8,2), OUT pa DECIMAL(8,2)
BEGIN SELECT Min(prod_price)
INTO p1
FROM products; SELECT Max(prod_price)
INTO ph FROM products; SELECT Avg(prod_price)
INTO pa FROM products; END;
– mysql变量以 @开始
– show create PROCEDURE name;
show CREATE TABLE customers;

游标

– 只能用于存储过程
– 创建游标
CREATE PROCEDURE processorders()BEGIN DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders; END;
– 打开游标 open cursor
open ordernumbers
close ordernumbers;
CREATE PROCEDURE processorders()BEGIN DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders; OPEN ordernumbers; CLOSE ordernumbers; END;
DROP PROCEDURE processorders;
– 使用游标检索数据
CREATE PROCEDURE processorders()BEGIN DECLARE o INT; DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders; open ordernumbers; fetch ordernumbers into o; close ordernumbers; END;

CREATE PROCEDURE processorders()BEGIN DECLARE o INT;DECLARE done BOOLEAN DEFAULT 0; DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ set done =1; open ordernumbers;REPEAT
fetch ordernumbers into o;
UNTIL done END REPEAT;
close ordernumbers; END;

触发器

– 使用场景: 验证信息/连锁操作 delete insert update
– 创建触发器 名/表/活动/何时执行
CREATE TRIGGER newproduct AFTER INSERT on products for each ROW SELECT ‘prodcut added’;
– 注:仅支持表
– drop TRIGGER name; insert update DELETE 之前之后执行

事务处理

– start TRANSACTION ; COMMIT ;ROLLBACK to delete1;回滚到保留点;

全球化

– 字符集
show CHARACTER set ;
– 校对
show COLLATION;

安全

– 访问控制
– 用户管理
use mysql;
– 查询用户
SELECT user FROM user;
– 创建用户
CREATE user lyj IDENTIFIED by ‘p@$$wOrd’;
– 重命名用户
rename user lyj to taotao;
– 删除账号
drop user taotao;
– 显示账户权限
show grants for lyj;
– 授权 select
grant SELECT on testmysql.* to lyj;
– 收回权限
REVOKE select on testmysql.* FROM lyj;
– 更改口令
set PASSWORD for lyj =password(‘123’);

数据库维护

– 备份

改善性能

– 查看当前设置 内存 缓冲区大小
show VARIABLES;
show status;
– 多线程多用户 一个慢则会影响整体
show PROCESSLIST;
– select 调优 找出最优写法
– explain分析执行计划
– 存储过程比SQL快
– 数据类型准确
– select * 避免
– 索引
– 使用select子句和union替换or
– 最好用fulltext 不是like

---

1. ↩︎

2. 0-9\. ↩︎