文章目录
SELECT 语句
尽量不要使用select * 原因:
其实就是三点:
1.造成硬盘上面不必要的输入输出交通堵塞
2.明确输出的列可以预知输出结果
3.可能造成加密信息暴露
I/O disk,RAM https://blog.csdn.net/TigerrrRose/article/details/105702166
ORDER BY
数据:
根据多列对结果进行排序,语法:
RDER BY
column1 ASC,
column2 DESC;
SELECT dept_no,dept_name
FROM departments
ORDER BY
dept_no,dept_name;
利用field()函数按照列内容排序
语法 order by field(column_name,‘first item content’,‘second content’…)
SELECT
orderNumber,
status
FROM
orders
ORDER BY
FIELD(status,
'In Process',
'On Hold',
'Cancelled',
'Resolved',
'Disputed',
'Shipped');
列中不在’first item content’,‘second content’…的内容排在最前面,再根据content的内容排序;
上面语句倒序的话,加个desc,按照“second content”,'first content’的序列排序,不在函数里的内容排到最后。
where
where里面可以套用 and or between like 等关键字
顺序:
先from 表-再where 过滤-select -group by - order by;
SELECT
order_num,cust_id
FROM
Orders
where order_date between '2012-01-12' and '2012-05-01';
选出日期在这区间的订单;
like用占位符: _代表任何一个字符串,%代表0或多个字符串
搜索以o结尾的姓并返回姓名;
SELECT
first_name,last_name
FROM
Name
WHERE
first_name like '%o';
SELECT
first_name,last_name
FROM
Name
WHERE
first_name in ('Parto','Patricio','Kazuhito');
NULL 在mysql表达 这个值是缺少的或者是没有, 并不等于空字符串也不等于0;
SELECT
lastName,
firstName,
reportsTo
FROM
employees
WHERE
reportsTo IS NULL;
DISTINCT
distinct 字段对有null的列都当成一个值,并只返回一个null;
mysql返回 多列组合的唯一值
如得到唯一洲跟城市的组合,
SELECT DISTINCT
state, city
FROM
customers
WHERE
state IS NOT NULL
ORDER BY
state,
city;