选择:select * from table1 where 范围
插入:insert into table1(field1,field2) values(value1,value2)
删除:delete from table1 where 范围
更新:update table1 set field1=value1 where 范围
查找:select * from table1 where field1 like ’%value1%’ ---like的语法很精妙,查资料!
排序:select * from table1 order by field1,field2 [desc]
总数:select count as totalcount from table1
求和:select sum(field1) as sumvalue from table1
平均:select avg(field1) as avgvalue from table1
A. UNION 运算符 :
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。
当 ALL 随 UNION一起使用时(即 UNION ALL),不消除重复行。
两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
B.EXCEPT 运算符 :
运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。
当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。
C.INTERSECT 运算符:
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。
当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。
二.外链接
A、left (outer) join:
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行
B.right (outer) join:
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行
C:full/cross (outer) join:
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。
eg:什么是LEFT JOIN请各位自行了解,废话不多说,先直接上三张表
组织表(t_organization)
部门表(t_department)
用户表(t_user)
逻辑是组织下面有部门,部门下面有用户,组织和部门通过organization_id字段关联,部门和用户通过department_id关联
实现以下几种查询
1.两张表的查询:
查询所有组织信息以及下属所有部门信息:
SELECT * FROM t_organization AS o LEFT JOIN t_department AS d ON o.organization_id = d.organization_id
查询结果如下:
2.三张表的查询(更多表以此类推)
查询所有组织信息以及下属所有部门信息以及下属所有用户信息:
SELECT * FROM t_organization AS o LEFT JOIN t_department AS d ON o.organization_id = d.organization_id
LEFT JOIN t_user AS u ON d.department_id = u.department_id
查询结果如下:
因为LEFT JOIN是以左表为主表,所以只要左表有数据,不管右表有没有数据(如果右表没有数据则为null,比如最后两条数据),查询结果都会存在。
查询某张表的最后几条数据:
SELECT * FROM t_school_student ORDER BY id asc LIMIT 5;(查询这个表的id从小到大排序的五条数据,desc是从大到小)
模糊查询:SELECT * FROM [user] WHERE u_name LIKE ‘%三%’AND u_name LIKE‘%猫%’
排序:select * from table1 order by field1,field2 desc
asc 表示升序, desc表示降序
求和:select sum(orderprice) as ordertotal from orders
最大值:select max(field1) as maxvalue from table1
删除:delete from table1 where 范围
插入:insert into table1(field1,field2) values(value1,value2)
删除:delete from table1 where 范围
更新:update table1 set field1=value1 where 范围
如何修改数据库的名称:
sp_renamedb 'old_name', 'new_name'
说明:前10条记录
select top 10 * form table1 where 范围
select * from 表名 order by 列名desc (降序) limit 显示的条数
#模糊查询含有
#SELECT * FROM t_school_teacher WHERE teacherName LIKE '%静'
#只显示该列
#SELECT teacherName from t_school_teacher
#去掉重复的
#SELECT DISTINCT teacherName from t_school_teacher
#or的用法
#SELECT * from t_school_teacher WHERE teacherName='惠玲8' OR teacherName='于哥'
#按照id排序进行排序
#select * FROM t_school_teacher ORDER BY id
#SELECT id,appId from t_school_teacher ORDER BY id,appId
#SELECT * from t_school_teacher LIMIT 5
#表中选取姓氏为
#SELECT * FROM t_school_teacher WHERE teacherName IN ('于哥','惠玲8')
#使用一个列名别名
#SELECT teacherAccount AS phone, teacherName AS name FROM t_school_teacher
#可以通过引用两个表的方式,从两个表中获取数据
SELECT t_school_teacher.teacherName,t_school_teacher.teacherAccount,t_school_student.studentName,t_school_student.studentAccount
FROM t_school_teacher,t_school_student
where t_school_teacher.id=t_school_student.id
group by查找每个客户的总金额
select customer,sum (orderprice) from orders group by customer
在SQL 中增加HAVING 子句原因是,WHERE 关键字无法与合计函数一起使用。
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000
Ucase()字段的值转换为大写
LCASE 函数把字段的值转换为小写。
SELECT UCASE(LastName) as LastName,FirstName FROM Persons
MID 函数用于从文本字段中提取字符
SELECT MID(City,1,3) as SmallCity FROM Persons
LEN 函数返回文本字段中值的长度
SELECT LEN(City) as LengthOfCity FROM Persons
ROUND 函数用于把数值字段舍入为指定的小数位数。
SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products
NOW 函数返回当前的日期和时间
SELECT ProductName, UnitPrice, Now() as PerDate FROM Products
参考地址:http://www.w3school.com.cn/sql/sql_isnull.asp