SQL资料总结

最新推荐文章于 2021-12-28 11:00:14 发布

Huangwenting1990

最新推荐文章于 2021-12-28 11:00:14 发布

阅读量534

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/Huangwenting1990/article/details/54290605

本文详细介绍了SQL的各种操作，包括Insert Into、Update、Delete和Select的基础用法，以及聚合函数、子查询、联接查询、通配符匹配、条件过滤等高级查询技巧。此外，还探讨了数据聚合、行处理、事务处理和数据库优化策略，为数据库管理和开发提供全面指导。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Insert Into 数据表名称(字段名称1,字段名称2,...) values(字段值1,字段值2,...)

insert into user(username,password,age)values('李老四','6666',45)

Update 数据表名称 Set 字段名称=字段值,字段名称=字段值,...[Where 条件]

Delete From 数据表

下列查询返回在LONDON（伦敦）或SEATTLE（西雅图）的所有雇员：

SELECT * FROM employees WHERE UPPER(city) IN('LONDON'，'SEATTLE')

下面示例利用DATEDIFF函数，确定在 pubs 数据库中标题发布日期和当前日期间的天数。

SELECT DATEDIFF(day, OrderDate, getdate()) AS no_of_days FROMtable1

返回字符串"wonderful"在 titles 表的 notes 列中开始的位置。

SELECT CHARINDEX('wonderful', notes)

以下是返回的结果：（第47个字符位置）

显示工作站的名称：select host_name() as [Client Computer Name]

下例是检索 titles 表中百分之五十的书。如果 titles 表中包含了 18 行，则将检索前 9 行。

SELECT TOP 50 PERCENT title FROM titles

字段名称 [Not] Between 起始值 and 终止值

列出BOOK表中30至50元的书

select * from book where price between 30 and50

字段名称 [Not] In(列出值1,列出值2,...)

从BOOK表中列出价格为30,40,50,60的所有书

select * from book where price in(30,40,50,60)

字段名称 [Not] Like "通配符"

列出BOOK表中出版社含电的所有记录

select * from book where publishing like '*电*'

列出BOOK表中出版社第一个字是电的所有记录

select * from book where publishing like '电*'

---

select Sum/Count/Avg/Max/Min(字段名称) [As 新名称] From 数据表名称

sum求和：

求出总价格做为合计字段

select sum(price ) as 合计 from book

count统计数量：

统计BOOK表中有多少条记录做为数量字段

select count(id) as 数量 from book

AVG平均：

算出BOOK表中所有书的平均价格

select avg(price) as 平均价格 from book

MAX最大：

列出BOOK表中最贵的书

select max(price) as 最贵书 from book

MIN最小：

select min(price) as 最便宜书 from book

交叉联接: SELECT * FROM table1 CROSS JOIN table2

select x.[name], y.[name] from x left join yon x.[refid] = y.id

select y.[name], x.[name] from x right join yon x.[refid] = y.id

表联接查询

SELECT t1.ProductID, t2.VendorID, t2.Name

FROM table1 t1, table2 t2

WHERE t1.VendorID = t2.VendorID

AND StandardPrice > $10

AND Name LIKE 'F%'

SELECT ProductID, talbe2.VendorID, Name

FROM table1 JOIN table2

ON(table1.VendorID = table2.VendorID)

WHERE StandardPrice > $10

AND Name LIKE 'F%'

COMPUTE by 子句

可选 BY 关键字。它基于每一列计算指定的行聚合。

行聚合函数名称。包括 SUM、AVG、MIN、MAX 或 COUNT。

要对其执行行聚合函数的列。

SELECT RateId, ConnectionFee, Rate

FROM UTC_Rate

ORDER BY RateId

COMPUTE SUM(ConnectionFee), SUM(Rate) --Compute用法（总计）

SELECT RateId, ConnectionFee, Rate

FROM UTC_Rate

ORDER BY RateId

COMPUTE SUM(ConnectionFee), SUM(Rate) BY RateId --Computeby用法（小计）

DISTINCT

指定 AVG 只在每个值的唯一实例上执行，而不管该值出现了多少次。

SELECT AVG(DISTINCT Rate)—-计算不重复Rate的avg

FROM UTC_Rate

SELECT distinct Rate FROMUTC_Rate

表的复制Select into

SELECT * into newtable FROM oldtable

联合查询 UNION所有查询中的列数和列的顺序必须相同, 数据类型必须兼容

SELECT Country, FirstName, LastName FROM Employees

UNION SELECT Country, NULL, NULL FROM Regions;

子查询:

--使用聚合函数

SELECT title FROM titles

WHERE advance >

(SELECTmax(advance)

FROMpublishers INNER JOIN titles ON

titles.pub_id = publishers.pub_id

WHEREpub_name = 'New Moon Books')

--使用all

SELECT title FROM titles

WHERE advance >all

(SELECTadvance

FROMpublishers INNER JOIN titles ON

titles.pub_id = publishers.pub_id

WHEREpub_name = 'New Moon Books')

--嵌套

SELECT field

FROM table1

WHERE id IN

(SELECT id

FROMtable2

WHERE title_idIN

(SELECT title_id

FROM table3

WHERE title_id like 'PC%'))

--EXISTS 和NOT EXISTS 查找交集与差集

SELECT DISTINCT field FROM table1

WHERE EXISTS

(SELECT * FROMtable2 WHERE table1.field = table2.field)

--等效

SELECT DISTINCT table1.field

FROM table1 INNER JOIN table2

ON table1.field = publishers.field

--差集

SELECT DISTINCT field

FROM table1

WHERE NOT EXISTS

(SELECT * FROMtable2 WHERE table1.field = table2.field)

--等效

SELECT DISTINCT field

FROM table1

WHERE field NOT IN

(SELECTfield

FROMtable2)

指定组或聚合的搜索条件。HAVING 只能与 SELECT 语句一起使用。HAVING 通常在 GROUP BY子句中使用。如果不使用 GROUP BY 子句，则 HAVING 的行为与 WHERE 子句一样。

SELECT id, SUM(price)

FROM Sales

GROUP BY id

HAVING SUM(price) > 10.00--检索超过$10.00 的每个 ID 的总计

ORDER BY id ;

更新表:

UPDATE titles

SET price = price * 2

WHERE pub_id IN

(SELECTpub_id

FROMpublishers

WHERE pub_name = 'New MoonBooks')

1：找出那些工资高于他们所在部门的manager的工资的员工。

selectw.ename,w.sal,w.mgr,m.empno,m.ename,m.sal

from emp w,emp m

where w.mgr=m.empno andw.sal>m.sal

2:哪些人是领导?

select distinct m.enamefrom emp w,emp m

where w.mgr=m.empno

select ename from emp where empno in(selectmgr from emp)

select ename from emp o where exists (select 'a' from emp i where o.empno=i.mgr)

3：哪些人不是领导？

selectm.ename from emp m,emp w

wherem.empno=w.mgr(+)

and w.mgris null

select ename from emp where empno not in(select mgr from emp where mgr is not null)

select ename from emp owhere not exists (select 'a' from emp i where o.empno=i.mgr)

哪个部门没有员工？哪个学生没有选课？哪个部门没有smith这个人？

selectename,emp.deptno,dept.deptno,dname from emp,dept

whereemp.deptno(+)=dept.deptno

andename(+)='SMITH'

and enameis null

4：找出公司里收入最高的前三名员工：

SQL> select rownum, last_name, salary

2 from (select last_name,salary

3 from s_emp

4 order by salary desc)

5 whererownum<=3;

ROWNUMLAST_NAME SALARY

---------- ------------------------- ----------

1Velasquez 4750

2Ropeburn 2945

3Nguyen 2897.5

不用max找出工资最高的人？

5：找出表中的某一行或某几行的数据：

SQL> l

1 select last_name,salary

2 from (select rownum a,b.*

3 from s_emp b)

4* where a=3

SQL> /

LAST_NAME SALARY

------------------------- ----------

Nagayama 2660

6：找出第三行到第五行之间的数据：

SQL> l

1 select last_name,salary

2 from (select rownum a,b.*

3 from s_emp b where rownum<=5)

4* where a between 3 and 5

SQL> /

LAST_NAME SALARY

------------------------- ----------

Nagayama 2660

Quick-To-See 2755

Ropeburn 2945

7：找出那些工资高于他们所在部门的平均工资的员工。

（1）：第一种方法：

SQL> select last_name, dept_id, salary

2 from s_emp a

3 wheresalary>(select avg(salary)

4 from s_emp

5 where dept_id=a.dept_id);

LAST_NAME DEPT_ID SALARY

------------------------- ---------- ----------

Velasquez 50 4750

Urguhart 41 2280

Menchu 42 2375

Biri 43 2090

Catchpole 44 2470

Havel 45 2483.3

Nguyen 34 2897.5

Maduro 41 2660

Nozaki 42 2280

Schwartz 45 2090

10 rows selected.

（2）：第二种方法：

SQL> l

1 select a.last_name,a.salary, a.dept_id, b.avgsal

2 from s_emp a, (selectdept_id, avg(salary) avgsal

3 from s_emp

4 group by dept_id) b

5 wherea.dept_id=b.dept_id

6* and a.salary>b.avgsal

SQL> /

LAST_NAME SALARY DEPT_ID AVGSAL

------------------------- ---------- ---------- ----------

Velasquez 4750 50 3847.5

Urguhart 2280 41 2181.5

Menchu 2375 42 2055.16667

Biri 2090 43 1710

Catchpole 2470 44 1995

Havel 2483.3 45 2069.1

Nguyen 2897.5 34 2204

Maduro 2660 41 2181.5

Nozaki 2280 42 2055.16667

Schwartz 2090 45 2069.1

10 rows selected.

8：找出重复数据:

SQL> l

1 select rowid, e.* from ae

2* where e.rowid>(selectmin(x.rowid) from a x where x.bm=e.bm and x.mc=e.mc);

ROWID BM MC

------------------ ---- --------------------

AAABdcAAGAAAAYyAAE 1111 1111

AAABdcAAGAAAAYyAAF 1112 1111

AAABdcAAGAAAAYyAAG 1113 1111

AAABdcAAGAAAAYyAAH 1114 1111

9：删除重复数据:

SQL> l

1 delete from a

2 where rowid in (selectrowid from a e

3* where e.rowid>(selectmin(x.rowid) from a x where x.bm=e.bm and x.mc=e.mc))

SQL> /

10：行列转置

select sname,

max(decode(cname,'wuli',result,null)) wuli,

max(decode(cname,'shuxue',result,null)) shuxue

from stu,cou,rou

wherestu.sno=rou.sno

and cou.cno=rou.cno

group by sname

SNAME WULI SHUXUE

---------- ---------- ----------

lisi 89 88

zhangsan 99 98

请在SQLServer中设计表来保存一个树状结构的组织图（假定结构图中只有名称这一项内容需要保存），如果我想查询某一职位下的所有职位，用一个存储过程来实现，你有什么思路？

答：三个字段Id（可以自增）， Name ，ParendId ，其中ParendId是父结点的ID。

使用存储过程查询一个职位下的所有职位，存储过程需要一个输入参数，这个参数就是父结点的id，可以查询ParendId等于输入参赛id，查询所有这个职位下的职位的记录。

一个新闻系统，有两张表，news表，new_class表，表结构如下：

表参加原始文档

其中2张表的newsID字段是关联的，请写出您写出classID是101101的新闻标题的SQL语句：

答：select N.title from News N, News_class C where N.NewsId=C.NewsId and C. NewsId=101101

写出一条Sql语句：取出表A中第31到第40记录（SQLServer,以自动增长的ID作为主键, 注意：ID可能不是连续的。）

Select top 10 * from a where id not in (select top 30 id froma);go

SQLSERVER服务器中，给定表 table1 中有两个字段 ID、LastUpdateDate，ID表示更新的事务号，LastUpdateDate表示更新时的服务器时间，请使用一句SQL语句获得最后更新的事务号。

select top 1 id from table1 orderby lastupdate desc

select id from table1 where lastupdate = (select max(lastipdate)from table1)

写出一条Sql语句：取出表A中第31到第40记录（SQLServer,以自动增长的ID作为主键, 注意：ID可能不是连续的。）

Select top 10 * from a where id not in (select top 30 id froma);go

有一人事表,计算两个同时入职的员工的生日的差,用一句 SQL语句将EGTB1中的结果变成EGTB2的结果?

Select deffdate(ss, '2005-03-20','2005-03-28') from table therec1.datetime = c2.datetime

SQL中游标的定义?

类似于指针,它用于指向的某一行即为选定或执行到的当前行

基本的步骤:声明游标、打开游标、提取数据、关闭游标。

如何在定义游标的时候使用动态sql语句？

declare @qa nvarchar(200)

declare @i int

declare @sql nvarchar(255)

set @i = 1

while @i <= 5

begin

set @sql = 'declare cur cursor for

select q' + convert(nvarchar(2), @i) + ' from ImportSurveyInfo'

set @i = @i + 1

exec(@sql)

open cur

fetch cur into @qa

select @qa

close cur

deallocate cur

end

产品表　　　　　　　　　　　　　　　　　　　销售计划表

ProductID　产品名　单价 ProductID 客户名　　订货数量　　销售单价

1 name1 110 2 m 2 250

2 name2 220 1 n 3 110

3 name3 330 3 n 1 300

4 name4 440 　 4 m 2 400

a.是否能删除产品表中的name2？请说明理由与过程。

答案：不能,两表都用ProductId做字段,要删除name2也要删除销售计划表ProductId为2的一行

………………………………………………………………………………………………

b.将销售计划表中m客户的产品名为name2的销售单价更换为产品表中相应的单价

update 销售计划表 set 销售单价 = select 单价 from 产品表 inner join 销售计划表

on 产品表.ProductID=销售计划表.ProductID where客户名=m

…………………………………………………………………………………

c.求销售计划表中各客户各自的销售总金额的sql语句

select total(销售单价*订货数量) as 销售总金额 from 销售计划表 group by 客户名

………………………………………………………………………………

d.求客户中销售总量最大的销售总金额的客户的sql语句

select max(销售单价*订货数量) as 销售总金额 from 销售计划表 group by 客户名

…………………………………………………………………………………

e.什么是事务（transaction）?它有什么好处。

将更改数据库的操作作为一个单元来提交或取消.

原子性（全部执行），

一致性（所有的数据保持一致状态），

隔离性（由并发事物所做的修改必须与任何其他并发食务所做的修改隔离），

持久性（完成事务，对系统的影响是永久性的）

………………………………………………………………………………………

f.update table2...数据库为默认设置能不能开始事务？为什么

答案：

数据库开发技巧:

　　内容： 　

　　1、开发人员如果用到其他库的Table或View，务必在当前库中建立View来实现跨库操作，最好不要直接使用“databse.dbo.table_name”，因为sp_depends不能显示出该SP所使用的跨库table或view，不方便校验。　　

　　2、开发人员在提交SP前，必须已经使用set showplan on分析过查询计划，做过自身的查询优化检查。 　

　　3、高程序运行效率，优化应用程序，在SP编写过程中应该注意以下几点： 　　

　　a)SQL的使用规范：

　　　i.　尽量避免大事务操作，慎用holdlock子句，提高系统并发能力。

　　　ii.　尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接。

　　　iii.　尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。

　　　iv.　注意where字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小。

　　　v.　不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

　　　vi.　尽量使用exists代替selectcount(1)来判断是否存在记录，count函数只有在统计表中所有行数时使用，而且count(1)比count(*)更有效率。

　　　vii.　尽量使用“>=”，不要使用“>”。

　　　viii.　注意一些or子句和union子句之间的替换

　　　ix.　注意表之间连接的数据类型，避免不同类型数据之间的连接。

　　　x.　注意存储过程中参数和数据类型的关系。

　　　xi.　注意insert、update操作的数据量，防止与其他应用冲突。如果数据量超过200个数据页面（400k），那么系统将会进行锁升级，页级锁会升级成表级锁。　　

　　b)索引的使用规范：

　　　i.　索引的创建要与应用结合考虑，建议大的OLTP表不要超过6个索引。

　　　ii.　尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过indexindex_name来强制指定索引

　　　iii.　避免对大表查询时进行table scan，必要时考虑新建索引。

　　　iv.　在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用。

　　　v.　要注意索引的维护，周期性重建索引，重新编译存储过程。　　

　　c)tempdb的使用规范：

　　　i.　尽量避免使用distinct、order by、groupby、having、join、cumpute，因为这些语句会加重tempdb的负担。

　　　ii.　避免频繁创建和删除临时表，减少系统表资源的消耗。

　　　iii.　在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替createtable，避免log，提高速度；如果数据量不大，为了缓和系统表的资源，建议先create table，然后insert。

　　　iv.　如果临时表的数据量较大，需要建立索引，那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中，这样才能保证系统能够很好的使用到该临时表的索引。

　　　 v.　如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table，然后droptable，这样可以避免系统表的较长时间锁定。

　　　 vi.　慎用大的临时表与其他大表的连接查询和修改，减低系统表负担，因为这种操作会在一条语句中多次使用tempdb的系统表。　　

　　d)合理的算法使用： 　　

根据上面已提到的SQL优化技术和ASETuning手册中的SQL优化内容,结合实际应用,采用多种算法进行比较,以获得消耗资源最少、效率最高的方法。具体可用ASE调优命令：set statistics io on, set statistics time on , set showplanon 等。

微软提供了以下四种数据库连接方式：

　　System.Data.OleDb.OleDbConnection

　　System.Data.SqlClient.SqlConnection

　　System.Data.Odbc.OdbcConnection

　　System.Data.OracleClient.OracleConnection

　　下面我们以范例的方式，来依次说明：

　　System.Data.SqlClient.SqlConnection

　　常用的一些连接字符串（C#代码）：

　　SqlConnection conn

　　= new SqlConnection（ "Server=（local）；IntegratedSecurity=SSPI；database=Pubs"）；

　　SqlConnection conn

　　= newSqlConnection（"server=（local）\\NetSDK；database=pubs；IntegratedSecurity=SSPI"）；

　　SqlConnection conn = new SqlConnection（

　　"Data Source=localhost；Integrated Security=SSPI；InitialCatalog=Northwind；"）；

　　SqlConnection conn = new SqlConnection（

　　" data source=（local）；initial catalog=xr；integratedsecurity=SSPI；

　　persist security info=False；workstation id=XURUI；packetsize=4096； "）；

　　SqlConnection myConn = new

　　System.Data.SqlClient.SqlConnection（"Persist SecurityInfo=False；Integrated

　　Security=SSPI；database=northwind；server=mySQLServer"）；

　　SqlConnection conn = new SqlConnection（

　　" uid=sa；pwd=passwords；initial catalog=pubs；datasource=127.0.0.1；Connect Timeout=900"）；

　　System.Data.OleDb.OleDbConnection

　　常用的一些连接字符串（C#代码）：

　　OleDbConnection conn = newOleDbConnection（@"Provider=Microsoft.Jet.OLEDB.4.0；DataSource=D：\MyWeb\81\05\GrocerToGo.mdb"）；

　　OleDbConnection conn = new OleDbConnection（

　　@"Provider=Microsoft.Jet.OLEDB.4.0；Password=；

　　User ID=Admin；Data Source=grocertogo.mdb；"）；

　　OleDbConnection conn = new OleDbConnection（

　　"Provider=MSDAORA； Data Source=ORACLE8i7；Persist SecurityInfo=False；Integrated Security=yes"）；

　　OleDbConnection conn = new OleDbConnection（

　　"Provider=Microsoft.Jet.OLEDB.4.0； DataSource=c：\bin\LocalAccess40.mdb"）；

　　OleDbConnection conn = new OleDbConnection（

　　"Provider=SQLOLEDB；Data Source=MySQLServer；IntegratedSecurity=SSPI"）；

　　System.Data.OracleClient.OracleConnection

　　常用的一些连接字符串（C#代码）：

　　OracleConnection myConn = newSystem.Data.OracleClient.OracleConnection（

　　"Data Source=Oracle8i；Integrated Security=yes"）；

　　System.Data.Odbc.OdbcConnection

　　常用的一些连接字符串（C#代码）：