一、基础:
1、创建数据库:CREATE DATABASE database-name
2、删除数据库:drop database dbname
3、备份sql server:
— (1)创建备份数据的 device:
USE master
EXEC sp_addumpdevice ‘disk’, ‘testBack’, ‘c:\mssql7backup\MyNwind_1.dat’
— (2)开始备份
BACKUP DATABASE pubs TO testBack
4、创建新表:
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根据已有的表创建新表:
(1)create table tab_new like tab_old (使用旧表创建新表)
(2)create table tab_new as select col1,col2… from tab_old definition only
5、删除数据库表:
drop table tabname
6、增加一个列:
Alter table tabname add column col type
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。
7、添加主键: Alter table tabname add primary key(col)
删除主键: Alter table tabname drop primary key(col)
8、创建索引:create [unique] index idxname on tabname(col….)
删除索引:drop index idxname
注:索引是不可更改的,想更改必须删除重新建。
9、创建视图:create view viewname as select statement
删除视图:drop view viewname
10、几个简单的基本的sql语句:
选择: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%’
11、聚合函数:(聚合函数会忽略null值)
总数:select count as totalcount from table1
求和:select sum(field1) as sumvalue from table1
平均:select avg(field1) as avgvalue from table1
最大:select max(field1) as maxvalue from table1
最小:select min(field1) as minvalue from table1
11、使用ANY和ALL条件:
ANY表示“任一”,ALL表示“全部”,但是ALL和ANY不能单独使用,需要配合单行比较操作符>、>=、<、<=一起使用。
> ANY : 大于最小
< ANY:小于最大
> ALL:大于最大
< ALL:小于最小
12、使用distinct过滤重复行:SELECT DISTINCT deptno, job FROM emp;
13、使用order by排序:
SELECT <*, column [alias], …>
FROM table
[WHERE condition(s)]
[ORDER BY column [ASC | DESC]] ;
默认是ASC升序,NULL视作最大。
14、分组:Group by:
SELECT <*, column [alias], …>
FROM table [WHERE condition(s)]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column [ASC | DESC]] ;
(1)HAVING字句:
HAVING子句用来对分组后的结果进一步限制,比如按部门分组后,得到每个部门的最高薪水,可以继续限制输出结果。必须跟在GROUP BY后面,不能单独存在。例如查询每个部门的最高薪水,只有最高薪水大于4000的记录才被输出显示:
SELECT deptno, MAX(sal) max_sal FROM emp
GROUP BY deptno HAVING MAX(sal) >4000;
15、如何修改数据库的名称:
sp_renamedb ‘old_name’, ‘new_name’
16、 查询语句的执行顺序:
当一条查询语句中包含所有的子句,执行顺序依下列子句次序:
- FROM 子句:执行顺序为从后往前、从右到左。数据量较少的表尽量放在后面。
- WHERE子句:执行顺序为自下而上、从右到左。将能过滤掉最大数量记录的条件写在WHERE子句的最右。
- GROUP BY:执行顺序从左往右分组,最好在GROUP BY前使用WHERE将不需要的记录在GROUP BY之前过滤掉。
- HAVING 子句:消耗资源。尽量避免使用,HAVING 会在检索出所有记录之后才对结果集进行过滤,需要排序等操作。
- SELECT子句:少用*号,尽量取字段名称。ORACLE 在解析的过程中, 通过查询数据字典将*号依次转换成所有的列名, 消耗时间。
- ORDER BY子句:执行顺序为从左到右排序,消耗资源。
17、几个高级查询运算词:
(1)UNION 运算符:
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
(2)EXCEPT 运算符:
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。
(3)INTERSECT 运算符:
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。
注:使用运算词的几个查询结果行必须是一致的。
二、关联查询:
1、内连接:
内连接返回两个关联表中所有满足连接条件的记录。例如查询员工的名字和所在部门的名字:
SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
或者:
SELECT e.ename, d.dname
FROM emp e JOIN dept d
ON(e.deptno = d.deptno);
2、外连接:
内连接返回两个表中所有满足连接条件的数据记录,在有些情况下,需要返回那些不满足连接条件的记录,需要使用外连接,即不仅返回满足连接条件的记录,还将返回不满足连接条件的记录。比如把没有职员的部门和没有部门的职员查出来。外连接的语法如下:
SELECT table1.column, table2.column
FROM table1 [LEFT | RIGHT | FULL] JOIN table2
ON table1.column1 = table2.column2;
(1)左外连接:
(2)右外连接:
外连接查询的例子,Emp表做驱动表:
SELECT e.ename, d.dname
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno = d.deptno;
Dept表做驱动表:
SELECT e.ename, d.dname
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno;
3、全连接:
全外连接是指除了返回两个表中满足连接条件的记录,还会返回不满足连接条件的所有其它行。即是左外连接和右外连接查询结果的总和。例如:
SELECT e.ename, d.dname
FROM emp e FULL OUTER JOIN dept d
ON e.deptno = d.deptno;
4、自连接:
自连接是一种特殊的连接查询,数据的来源是一个表,即关联关系来自于单表中的多个列。表中的列参照同一个表中的其它列的情况称作自参照表。
自连接是通过将表用别名虚拟成两个表的方式实现,可以是等值或不等值连接。例如查出每个职员的经理名字,以及他们的职员编码:
SELECT worker.empnow_empno, worker.enamew_ename, manager.empnom_empno, manager.enamem_ename
FROM emp worker join emp manager
ON worker.mgr = manager.empno;
三、提升:
1、复制表(只复制结构,源表名:a,新表名:b)
法一:select * into b from a where 1<>1(仅用于SQlServer)
法二:select top 0 * into b from a
2、拷贝表(拷贝数据,源表名:a,目标表名:b)
insert into b(a, b, c) select d,e,f from a;
3、跨数据库之间表的拷贝(具体数据库使用绝对路径)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子:..from b in ‘”&Server.MapPath(“.”)&”\data.mdb” &”‘ where..
4、子查询(表名1:a,表名2:b)
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)
5、显示文章、提交人和最后回复时间:
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
6、between的用法,between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2
7、in 的使用方法
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
8、两张关联表,删除主表中存在,但副表中没有的信息 :
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
9、四表联查问题:
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where …..
10、日程安排提前五分钟提醒 :
SQL: select * from 日程安排 where datediff(‘minute’,f开始时间,getdate())>5
11、一条sql 语句搞定数据库分页:
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
12、前10条记录:
select top 10 * form table1 where 范围
13、选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.):
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
14、包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)
15、随机取出10条数据:
select top 10 * from tablename order by newid()
17、删除重复记录:
(1)delete from tablename where id not in (select max(id) from tablename group by col1,col2,…)
(2)select distinct * into temp from tablename
delete from tablename
insert into tablename select * from temp
评价: 这种操作牵连大量的数据的移动,这种做法不适合大容量但数据操作
(3)例如:在一个外部表中导入数据,由于某些原因第一次只导入了一部分,但很难判断具体位置,这样只有在下一次全部导入,这样也就产生好多重复的字段,怎样删除重复字段:
alter table tablename
–添加一个自增列
add column_b int identity(1,1)
delete from tablename where column_b not in(
select max(column_b) from tablename group by column1,column2,…)
alter table tablename drop column column_b
18、列出数据库里所有的表名
select name from sysobjects where type=’U’ // U代表用户
19、列出表里的所有的列名
select name from syscolumns where id=object_id(‘TableName’)
20、列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。
select type,sum(case vender when ‘A’ then pcs else 0 end),sum(case vender when ‘C’ then pcs else 0 end),sum(case vender when ‘B’ then pcs else 0 end) FROM tablename group by type
显示结果:
type vender pcs
电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机 C 3
TRUNCATE TABLE table1
22、选择从10到15的记录:
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc
四、技巧:
“where 1=1” 是表示选择全部 “where 1=2”全部不选,
2、按姓氏笔画排序:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //从少到多
3、数据库加密:
select encrypt(‘原始密码’)
select pwdencrypt(‘原始密码’)
select pwdcompare(‘原始密码’,’加密后密码’) = 1–相同;否则不相同 encrypt(‘原始密码’)
4、查看硬盘分区:
EXEC master..xp_fixeddrives
5、比较A,B表是否相等:
if (select checksum_agg(binary_checksum(*)) from A)
=
(select checksum_agg(binary_checksum(*)) from B)
print ‘相等’
else
print ‘不相等’
6、杀掉所有的事件探察器进程:
DECLARE hcforeach CURSOR GLOBAL FOR SELECT ‘kill ‘+RTRIM(spid) FROM master.dbo.sysprocesses
WHERE program_name IN(‘SQL profiler’,N’SQL 事件探查器’)
EXEC sp_msforeach_worker ‘?’
7、记录搜索:
(1)开头到N条记录:
Select Top N * From 表
(2)N到M条记录(要有主索引ID):
Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc
(3)N到结尾记录:
Select Top N * From 表 Order by ID Desc
案例:
例1:一张表有一万多条记录,表的第一个字段 RecID 是自增长字段, 写一个SQL语句, 找出表的第31到第40个记录。
select top 10 recid from A where recid not in(select top 30 recid from A)
分析:如果这样写会产生某些问题,如果recid在表中存在逻辑索引。
select top 10 recid from A where……是从索引中查找,而后面的select top 30 recid from A则在数据表中查找,这样由于索引中的顺序有可能和数据表中的不一致,这样就导致查询到的不是本来的欲得到的数据。
解决方案:
(1)用order by select top 30 recid from A order by ricid 如果该字段不是自增长,就会出现问题
(2) 在那个子查询中也加条件:select top 30 recid from A where recid>-1
8、获取当前数据库中的所有用户表:
select Name from sysobjects where xtype=’u’ and status>=0
9、获取某一个表的所有字段:
select name from syscolumns where id=object_id(‘表名’)
select name from syscolumns where id in (select id from sysobjects where type = ‘u’ and name = ‘表名’)
两种方式的效果相同。
10、查看与某一个表相关的视图、存储过程、函数:
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like ‘%表名%’
11、查看当前数据库中所有存储过程:
select name as 存储过程名称 from sysobjects where xtype=’P’
12、查询用户创建的所有数据库:
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name=’sa’)
或者
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01
13、查询某一个表的字段和数据类型:
select column_name,data_type from information_schema.columns
where table_name = ‘表名’
文章转自:
https://www.cnblogs.com/1234abcd/p/5530314.html
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/114628.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...