数据检索
select 数据检索(注意sql不注重大小写)
-
常用语句:select *,select xxx,yyy
-
返回不重复数据:
DISTINCT
注意:DISTINCT 关键字作用于所有的列
SELECT DISTINCT vend_id FROM Products;
-
限制结果
mysql可以通过关键字limit限制结果数量select * from user where is_del = 0 limit 1 # 通过OFFSET关键字可以明确是第几行开始 SELECT prod_name FROM Products LIMIT 5 OFFSET 5;
TOP
SELECT TOP 5 prod_name FROM Products;
排序检索数据
关键字:order by(默认升序排练)
注意点:order by
必须作为sql语句的最后一个子句
当order by 对多个列进行排序,会根据顺序进行排序比如说
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price, prod_name;
只有在prod_price相同时才对prod_name进行排序
order支持按相对列位置进行排序
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY 2, 3;
指定升序降序排序
关键字:DESC 降序 ASC 升序
DESC 关键字只应用到直接位于其前面的列名
过滤数据
关键字 where
where的用法
select *
from user
where id = 10
select *
from user
where id=10 and age >10
where的组合用法
-
关键字:and or (注意and的优先级大于or),但我们可以用括号来对操作符进行明确分组
-
关键字:in
SELECT prod_name, prod_price FROM Products WHERE vend_id IN ( 'DLL01', 'BRS01' ) ORDER BY prod_name;
-
关键字:not
WHERE 子句中的 NOT 操作符有且只有一个功能,那就是否定其后所跟的 任何条件 -
关键字:like
like关键字通常跟%搭配SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE 'Fish%';
'Fish%'
查找所有以Fish开头的
'%bean bag%'
查找包含bean bag的
'F%y'
查找以F开头,y结尾的
'b%@forta.com'
邮件查找
创建计算字段
-
拼接字段
concatSELECT CONCAT(USER_NAME,MOBILE) FROM tb_org_member_info
-
别名(as)
使用函数处理数据
- 文本处理函数
SELECT cust_name, cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');
- 时间函数
关键函数:now()
获取当前日期:
mysql> SELECT curdate();
mysql> SELECT CURRENT_DATE();
获取当前时间:
mysql> SELECT curtime();
mysql> SELECT CURRENT_TIME();
获取当前时间日期:
mysql> SELECT now();
mysql> SELECT CURRENT_TIMESTAMP();
- 数值处理函数
汇总数据
-
聚集函数
实例代码select count(1) from tb_user
值得注意的是我们上面 DISTINCT 不能用于 COUNT(*),但可以用于 COUNT(),
SELECT AVG(DISTINCT prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';
分组数据
关键字:
GROUP BY
having
注意:
- 除聚集计算语句外,SELECT 语句中的每一列都必须在 GROUP BY 子句 中给出。
- GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前。
子查询
关键字:in
in 常和where搭配
SELECT cust_id
FROM Orders WHERE order_num IN (
SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01');
注意:作为子查询的 SELECT 语句只能查询单个列。企图检索多个列将返回 错误
子查询有时也会放在select 上面
SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;
联结
1.使用where进行联结,如果不加条件,就是笛卡儿积(cartesian product)由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
2.使用on进行联结
高级联结
1.使用别名
SELECT cust_name, cust_contact
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01';
2.自联结
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';
3.自然联结
SELECT C.*, O.order_num, O.order_date,
OI.prod_id, OI.quantity, OI.item_price
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01';
注意:自然联结要求你只能选择那些唯一的列,一般通过对一个表使用通配符 (SELECT *),而对其他表的列使用明确的子集来完成
4.外联结
主要两种联结:左外联结和右外联结,区别是用那个联结就是以哪边的表为主,另外一个表数据如果为空就置null
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行
SELECT Customers.cust_id,
COUNT(Orders.order_num) AS num_ord
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;
组合查询
关键字:union
MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
union和union all的主要区别是union all是把结果集直接合并在一起,而
union 是将union all后的结果镜像一次distinct,去除重复的记录后的结果。
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All'
ORDER BY cust_name, cust_contact;
插入数据
关键字:insert
INSERT INTO Customers
VALUES('1000000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA',
NULL,
NULL);
从一个表复制到另一个表
INSERT SELECT与SELECT INTO 它们之间的一个重要差别是前者导出数据,而后者导入数据。
SELECT *
INTO CustCopy
FROM Customers;
更新和删除数据
关键字:update、delete
UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = '1000000005';
DELETE FROM Customers
WHERE cust_id = '1000000006';
创建表
CREATE TABLE `tb_apple_info` (
`ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`APPLE_NAME` varchar(128) DEFAULT NULL COMMENT '应用名称',
`REMARK` varchar(255) DEFAULT NULL COMMENT '备注',
`CREATE_DATE` datetime DEFAULT NULL COMMENT '创建时间',
`UPDATE_DATE` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
修改表
ALTER TABLE Vendors
ADD vend_phone CHAR(20);
删除表
DROP TABLE CustCopy;
细节
在mysql中,is
, not is
用来判断是否为空,=只是用来进行值的比较