mysql基本用法笔记

mysql基础

基本信息

所有查询语句必须 ;分号作为结尾

展示数据库		show databases;
使用数据库   use database_name;
展示数据库的表				show tables;
展示数据库表的列信息	show columns from table_name;

检索数据 SELECT

-- 检索一张表多个列所有数据
SELECT column1,column2,column3 FROM table_name;

-- 对检索数据列进行去重，DISTINCT关键字应用于 检索的所有列，如果DISTINCT column1,column2 检索两列都不同的行
SELECT DISTINCT column1 FROM table_name;

-- 限制查询结果的行数 LIMIT 5限制展示5行
SELECT DISTINCT column1, column2 FROM table_name LIMIT 5;
-- 指定开始展示的位置和行数 从第六行开始展示最多4行（行数从0开始）
SELECT column1 FROM table_name LIMIT 5,4；
等价于 SELECT column1 FROM table_name LIMIT 4 OFFSET 5；

排序检索数据ORDER BY

-- 将搜索结果进行排序 下例先按pro_price排序， 在按pro_name排序。如果第一列的值在检索结果里都是唯一的则不会按第二列的结果排序
SELECT pro_id,pro_price,pro_name 
FROM products
ORDER BY pro_price,pro_name;

-- 指定排序方向 默认升序排列（ASC）， A-Z，降序排列指定DESC关键字,只应用到DESC前的关键字
SELECT pro_id,pro_price,pro_name 
FROM products
ORDER BY pro_price DESC,pro_name;

过滤数据

• WHERE字句的位置：WHERE在前， ORDER BY在后

-- 过滤到prod_price为2.50的行
SELECT prod_name,pro_price FROM products WHERE prod_price = 2.50;

-- 过滤到pro_name为张三的行， 字符串用单引号包括起来
SELECT prod_name,pro_price FROM products WHERE prod_name = '张三';

-- 过滤值在某个区间的数据
SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;

-- 空值检查 检查prod_price为空的行
SELECT prod_name,prod_price FROM products WHERE prod_price IS NULL;

• mysql支持的条件操作符

操作符	说明
=	等于
<> 或 !=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN	在指定的两个值之间

• WHERE的组合使用

  WHERE可以包含多个AND和OR的组合使用，默认情况下（未用括号区分优先级） AND的优先级高于OR

  -- AND 查询vend_id为111且price小于10的值
  SELECT prod_id,prod_price,prod_name FROM products WHERE vend_id=111 AND pro_price<10;
  
  -- OR 查询vend_id为111或price小于10的值
  SELECT prod_id,prod_price,prod_name FROM products WHERE vend_id=111 OR pro_price<10;
  

• IN操作符

  IN操作符用来指定条件范围，范围中的每一个条件都可以匹配。

  SELECT prod_name,prod_price FROM products
  WHERE vend_id IN(111,222) ORDER BY prod_name;
  

• NOT操作符

  NOT用来否定后面的条件

  SELECT prod_name,prod_price FROM products
  WHERE vend_id NOT IN(111,222);
  

• 搜索语句模板

  SELECT columnname,...
  FROM tablename,...
  [WHERE ...]
  [UNION]
  [GROUP BY]
  [HAVING]
  [ORDER BY]
  
  

模糊搜索

• 百分号% 通配符 % 表示任何字符出现任意次数

  -- 搜索prod_name为jet开头的行 （jet%, %jet%, %jet, j%t）
  SELECT pro_id,pro_name FROM products WHERE prod_name LIKE 'jet%';
  

• 下划线_ 通配符 _表示任意字符仅出现一次

• 正则表达式 REGEXP

  -- 普通字符匹配， 包含1000
  SELECT prod_name FROM products WHERE prod_name REGEXP '1000';
  
  -- OR匹配 prod_name包含1000或2000的行
  SELECT prod_name FROM products WHERE prod_name REGEXP '1000｜2000';
  
  -- 匹配多个中的一个 匹配1或2或3或4
  SELECT prod_name FROM products WHERE prod_name REGEXP '[1234]';
  
  -- 匹配范围数据 0-9， a-z
  SELECT prod_name FROM products WHERE prod_name REGEXP '[0-9]';
  
  -- 匹配正则表达式特殊字符 前面加 \\  如\\.匹配包含 . 的行
  SELECT prod_name FROM products WHERE prod_name REGEXP '[\\.]';
  

  正则表达式和LIKE的区别， LIKE匹配整列数据，正则可以只包含查找目标的结果。LIKE相当于正则开始末尾用^ $限定

计算字段

• 字段拼接

  -- 字段拼接， Concat()进行拼接 (mysql专用， 多数DBMS使用+或||进行拼接)
  SELECT Concat(vend_name, '(',vend_country, ')') FROM vendors ORDER BY vend_name;
    
  -- RTrim()函数删除右侧多余空格  LTrim()删除左侧多余空格  Trim()删除两侧空格
  SELECT Concat(RTrim(vend_name), '(',RTrim(vend_country), ')') FROM vendors ORDER BY vend_name;
  

• 列别名

  -- 使用关键字AS赋予别名
  SELECT Concat(RTrim(vend_name), '(',RTrim(vend_country), ')') AS new_vend_title FROM vendors ORDER BY vend_name;
  
  -- 算数计算
  SELECT prod_id,quantity,quantity*item_price AS sum_price FROM orderitems WHERE order_num = 1555;
  

数据处理函数

不同MDBS支持函数不同，以下函数仅在mysql保证可以使用

• 文本处理函数

-- 将结果字母转化全部大写
SELECT vend_name,UPPER(vender_name) AS vend_name_upcase FROM venders;

函数	说明
Left()	返回串左边的字符
Length()	返回串的长度
Locate()	找出串的一个子串
Lower()	将串转化为小写
LTrim()	去掉串左边的空格
Right()	返回串右边的字符
RTrim()	去掉串右边边的空格
Soundex()	返回串的SOUNDEX值(匹配类似发音的串)
SubString()	返回子串的字符
Upper()	将串转化为大写

• 日期和时间处理函数

函数	说明
AddDate()	增加一个日期（天，周，月等）
AddTime()	增加一个时间（时分秒）
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回时间日期的日期部分
DateDiff()	计算两个日期之差
Date_format()	返回一个格式化的日期或时间串
Hour()|Minite()|Month()|Second()	返回时间的小时|分钟|秒｜月份
Now()	返回当前日期和时间
Time()	返回一个日期时间的时间部分

-- 提取日期部分比较
SELECT cust_id, order_num FROM orders WHERE Date(order_date) = '2014-12-12';

-- 查找在某一时间的数据
SELECT cust_id, order_num FROM orders WHERE Date(order_date) BETWEEN '2014-12-12' AND '2014-12-30';
SELECT cust_id, order_num FROM orders WHERE YEAR(order_date)=2014 AND MONTH(order_date)=12;

聚集函数

-- AVG()计算平均值 对应字段值为null不计算
SELECT AVG(prod_price) AS avg_price FROM products;

-- COUNT()计算特定行的数目
SELECT COUNT(*) AS num_cust FROM customs;

-- MAX() 返回列中的最大值
SELECT MAX(pro_price) FROM product;

-- MIN() 返回列中的最小值 若为字符数据， 返回对应列排序的第一条数据
SELECT MIN(pro_price) FROM product;

-- SUM() 返回指定列的数值的总和
SELECT SUM(quanlity) AS sum_quanlity FROM orders WHERE order_num=5;

-- DISTINCT() 只考虑非重复值 DISTINCT必须使用列名
SELECT COUNT(DISTINCT price) AS avg_price FROM products;

数据分组

-- GROUP BY 按vend_id排序并分组数据， 对每个vend_id计算一次num_prods
-- SELECT语句中出现的每个列都必须在GROUP BY子句中出现， 分组列中null值将作为一组返回
-- GROUP BY 必须跟在WHERE语句之后， ORDER BY之前
SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;

-- HAVING  过滤得到分组行数大于等于2的分组
SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*)>=2;

子查询

把一条SELECT语句执行的结果用于以一条SELECT语句的WHERE子句

在SELECT语句中， 子查询总是从内向外处理

-- 先从order_items表中查到prod_id为TNT2的order_num,返回两个订单号， 这两个值以IN要求的逗号分隔形式传递给列外部的WHERE子句 
SELECT cust_id FROM orders
WHERE order_num IN (SELECT order_num
                   FROM order_items
                   WHERE prod_id = 'TNT2');

联结表

-- 创建联结表
SELECT  vend_name, prod_name, pro_price 
FROM vendors, products
WHERE vendors.vend_id=product.vend_id
ORDER BY vend_name,prod_name;
-- 等效于 (内部连接) INNER JOIN 与ON 组合使用， ON等效于WHERE
SELECT vend_name, prod_name,prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

-- 联结表时使用别名
SELECT cust_name,cust_contact
FROM customers AS c, orders AS o, orderitems AS io
WHERE c.cust_id = o.cust_id
	AND oi.order_num = o.order_num
	AND proi_id = 'TNT2';
	
-- 外部联结 为了能检索到没有关联行的那些行信息 为了检索客户，包括那些没有订单的客户
-- 外联结必须使用RIGHT 或LEFT指定需要包括所有行的表
SELECT customer.cust_id, orders.order_num
FROM customer LEFT OUT JOIN orders
ON customer.cust_id = order.cust_id

组合查询

搜索语句使用UNION进行联结，UNION中的查询必须包含相同的列，表达式或聚集函数

UNION从查询结果中自动取出了重复的行，如果要匹配所有行， 则使用UNION ALL

增删改语句

• 插入数据

  -- 不指定修改的列， 需要将所有列的值都写明， 包括NULL值
  INSERT INTO customers VALUES ('A','B', 'C');
  
  -- 指定增加的列值
  INSERT INTO customers(name, address, city) VALUES ('A', 'B', 'C');
  
  -- 插入多行数据， 数据用逗号进行分组
  INSERT INTO customers(name, address, city) VALUES('A', 'B', 'C'),('D','E','F');
  
  -- 插入SELECT的搜索结果
  INSERT INTO customers(name, address, city)
  	SELECT name,address, city FROM newcustomers;
  
  

• 更新数据

  -- UPDATE 需要指定WHERE条件， 否则会更新整张表的数据 
  -- 更新多行数据时， 如果在UPDATE和表名之间加入IGNORE关键字，则如果有数据更新失败， 也会继续执行更新
  UPDATE customers SET email='newEmail.mail.com', address = 'newAddress' WHERE cust_id = '1005';
  
  

• 删除数据

  DELETE FROM customers WHERE cust_id = 10006;
  

操纵表

• 创建表

-- mysql 默认使用MyISAM引擎建表
-- InnoDB是一个可靠的事务处理引擎，不支持全文本搜索
-- MyISAM不支持事务处理， 但支持全文本搜索
-- MEMORY功能等同于MyISAM， 但是存储在内存而不是磁盘， 因此速度很快
CREATE customers(
	cust_id INT	NOT NULL AUTO_INCREMNET,
  cust_name char(30) NOT NULL,
  cust_city char(50) NULL,
 	cust_quanlity int DEFAULT 1,
  PRIMARY KEY (cust_id)
)ENGINE=InnoDB;

-- 建立索引
CREATE INDEX indexname ON tablename(columnname [ASC|DESC],...);

• 更新表

-- 新增一列
ALTER TABLE vendors ADD COLUMN vend_phone;
-- 删除一列
ALTER TABLE vendors DROP COLUMN vend_phone;

-- 定义外键
ALTER TABLE orderitems ADD CONSTRAINT fk_orders_orderitems FOREIGN KEY(order_num) REFERENCES orders(order_num);

-- 删除表
DROP TABLE vendors;

-- 重命名表
RENAME TABLE customers2 TO cumtomers;

视图

视图：不包含表中应该有的任何数据或列， 包含的是SQL查询结果。

作用：

1. 重用SQL语句
2. 简化复杂的SQL操作
3. 使用表的组成部分而不是全部
4. 保护数据， 
5. 修改数据格式和表示， 视图可返回与底层表示格式不同的数据		

-- 创建视图 创建一个名为productcustomer的视图，联结三个表， 返回订购任意产品的所有客户的表信息
CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact,prod_id
FROM customers, orders, ordersitems
WHERE customers.cust_id = orders.cust_id
	AND orderitems.order_num  = orders.order_num;
	
-- 检索视图内容
SELECT cust_name, cust_contact
FROM productcostomers
WHERE prod_id = 'TNT2';

存储过程

-- 创建存储过程
CREATE PROCEDURE produce_name()
BEGIN
	SELECT avg(prod_price) AS priceavg
	FROM products
END;

-- 创建带参数的存储过程
-- MySQL支持IN(传递给存储过程)、OUT(从存 储过程传出，如这里所用)和INOUT(对存储过程传入和传出)类型的参 数。
CREATE PROCEDURE produce_name_param(
	IN onumber INT,
  OUT ototal DECIMAL(8,2)
)
BEGIN
	SELECT Sum(item_price* quanlity)
	FROM ordersitems
	WHERE order_num = onumber
	INTO ototal;
END;

-- 使用存储过程
call produce_name();
call produce_name_param(20005, @total);



-- 删除存储过程
DROP PROCEDURE produce_name;

-- 检查存储过程
SHOW CREATE PROCEDURE produce_name;

-- 展示所有存储过程
SHOW PROCEDURE STATUS LIKE 'name';

游标

游标(cursor)是一个存储在MySQL服务器上的数据库查询， 它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

在能够使用游标前，必须声明(定义)它。这个过程实际上没有 检索数据，它只是定义要使用的SELECT语句。

-- 创建游标
CREATE PROCEDURE processorders()
BEGIN
	-- 定义一个游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
  -- 打开游标
  OPEN ordernumbers;
  -- 检索数据
  FETCH ordernumbers INTO o;
  -- 关闭游标
  CLOSE ordernumbers;-- 如果没有主动关闭，将在END时进行关闭
END;

在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。 FETCH指定检索什么数据(所需的列)，检索出来的数据存储在什么地方。

触发器

-- 创建触发器
CREATE TRIGGER product_tri AFTER INSERT ON products FOR EACH ROW SELECT 'Product ADD';

-- 删除触发器
DROP TRIGGER prodct_tri

事务处理

-- 如果第一条成功， 第二条失败， 则DELETE不会提交
START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 1999;
DELETE FROM orders WHERE order_num = 1999;
COMMIT;

-- 设置保留点
SAVEPOINT delete1;

-- 回退至保留点
ROLLBACK TO delete1;

索引的建立原则

• 哪些情况需要常见索引
  • 主键自动创建唯一索引
  • 频繁查询的字段需创建索引
  • 多表关联查询，on两边的关键字都要创建索引
  • 查询中需要排序或分组的字段（order by/group by）需要创建索引
• 不需要创建索引
  • 表的记录很少
  • 经常进行增删改操作的表
  • 频繁更新的字段
  • where条件里使用频率不高的字段
• 索引设计原则
  • 索引区分度低
  • 常用查询字段建立索引
  • 常排序/分组/去重字段建立索引
  • 主键和外键建立索引
• 索引失效情况
  • 对索引使用函数或进行运算
  • 对索引使用<>, not in, not exists, !=
  • 对索引使用前导模糊查询 like %sele
  • 隐式转化
  • or联结的字段 有未索引字段
• 索引优化
  • 使用联结代替子查询
  • 多表关联时， 小表在前， 大表在后
  • 优化where语句联结顺序， 将能过滤条件多的子句放在前面
  • 使用联合索引而非单个索引
• 其他
  • 数据的增删改会导致索引频繁变化，当短时间内数据有大量更新时， 可以先删除索引，数据更新完毕后重建索引。另外， 建立索引时会产生锁，锁住整张表，所有DML语句都不能执行

sql的优化

• 对于条件查询，考虑在where和order by 后的字段建立索引
• 避免在where条件后进行null值判断， 避免进行<>, not in, not exists, !=
• 避免在where后使用函数
• 避免where后使用or联结