首页 > 数据库 > sql学习笔记(一)
您的足迹
  • 你没有浏览过任何文章或者你没有开启cookies。

sql学习笔记(一)

sql 学习笔记,本文是学习《sql必知必会》记录的一些记录备忘。

1.操作符

DESC 降序
ORDER BY 必须位于最后
LIKE 操作符(仅针对文本)
select prod_name ,prod_price
from products
where prod_name like 'fish%';
:like 用于匹配多个字符,下化线(_)用于匹配单个字符,方括号([])用于匹配字符集
SELECT cust_contact
FROM customers
WHERE cust_contact LIKE '[^JM]%'
order BY cust_contact;
通配符比较一般操作时间更长,不宜多用
IN 操作符
select prod_name ,prod_price
from products
where vend_id in ('DLL01','BRS01');

2.计算次序

select prod_name ,prod_price
from products
where (vend_id = 'DLL01' or vend_id ='BRS01');
and prod_price >= 10
3.多表查询
select a.employee_no,a.name,a.email,a.phone_ext,b.dept_name
from employee as a ,dept as b
where active_flag <> 0
and a.dept_id = b.dept_id;

*=左外连  =*右外连

//oracle中没有AS,应该去掉

select s.id , s.name ,s.age , s.value,c.sorce
from stuInfo s
inner join course c on s.id = c.id

select s.id , s.name ,s.age , s.value,c.sorce
from stuInfo s
left outer join course c on s.id = c.id
4.拼接字段
mysql中
SELECT CONCAT(RTRIM(mid),'(',TRIM(ipaddress),')'),ipaddress
FROM device_hardware
WHERE ipaddress IN ('127.0.0.1','192.168.23.100')
ORDER BY mid ;
其它使用||或+符号
SELECT mid + '('+ipaddress+')',ipaddress
FROM device_hardware
WHERE ipaddress IN ('127.0.0.1','192.168.23.100')
ORDER BY mid ;
5.计算字段
SELECT prod_id,quantity,item_price,quantity*item_price as expanded_price
FROM ordertitem
where order_num=20008;
操作符优先顺序:+,-,*,/
6.汇总五个函数:AVG ,MIN,MAX,COUNT,SUM
聚集不同的值:DISTINCT,如:
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id ='DLL01';

7.group BYorder BY

order by和group by 的区别:

order by 从英文里理解就是行的排序方式,默认的为升序。order by后面必须列出排序的字段名,可以是多个字段名。

group by 从英文里理解就是分组。必须有”聚合函数”来配合才能使用,使用时至少需要一个分组标志字段。

什么是”聚合函数”?

像sum()、count()、avg()等都是“聚合函数”

使用group   by   的目的就是要将数据分类汇总。

一般如:

select 单位名称,count(职工id),sum(职工工资)   form   [某表]
group by 单位名称
这样的运行结果就是以“单位名称”为分类标志统计各单位的职工人数和工资总额。
在sql命令格式使用的先后顺序上,group by 先于order by
8.过滤分组HAVING
SELECT cust_id,count(*) as orders
from orders
group BY cust_id
HAVING COUNT(*) >= 2;
9.子查询 (subquery)
SELECT cust_id
FROM Orders
WHERE order_num IN (20007,20008)
SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01')
--从里向外查询,只能是单列,并且有性能问题
10.组合查询(compound query)
操作符UNION
select * from RecTape where timestamp like '2010-07-19%' and localParty = ${tel}
union select * from RecTape where timestamp like '2010-07-19%' and  remoteParty=${tel};
--有order BY 只能放在最后
  1. Moon 2011/01/20 下午 2:56 | #1

    what’s your meaning

评论提交中, 请稍候...

留言

可以使用的标签: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>
Trackbacks & Pingbacks ( 0 )
  1. 还没有 trackbacks
Feed