MySQL数据库：常见经典SQL语句

待我称王封你为后i 2022-04-10 04:10 341阅读 0赞

### 一、基础： ###

**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\]\] ;

![20181224193933604][]

**（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、 查询语句的执行顺序：**

当一条查询语句中包含所有的子句，执行顺序依下列子句次序：

1.  FROM 子句：执行顺序为从后往前、从右到左。数据量较少的表尽量放在后面。
2.  WHERE子句：执行顺序为自下而上、从右到左。将能过滤掉最大数量记录的条件写在WHERE子句的最右。
3.  GROUP BY：执行顺序从左往右分组，最好在GROUP BY前使用WHERE将不需要的记录在GROUP BY之前过滤掉。
4.  HAVING 子句：消耗资源。尽量避免使用，HAVING 会在检索出所有记录之后才对结果集进行过滤，需要排序等操作。
5.  SELECT子句：少用\*号，尽量取字段名称。ORACLE 在解析的过程中, 通过查询数据字典将\*号依次转换成所有的列名, 消耗时间。
6.  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）左外连接：

![20181224193933709][]

（2）右外连接：

![20181224193933793][]

外连接查询的例子,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;

![20181224193933812][]

**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()

**16、随机选择记录：**  
select 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

**21、初始化表table1**

TRUNCATE TABLE table1

**22、选择从10到15的记录：**  
select top 5 \* from (select top 15 \* from table order by id asc) table\_别名 order by id desc

### 四、技巧： ###

**1、1=1，1=2的使用，在SQL语句组合时用的较多：**

“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][https_www.cnblogs.com_1234abcd_p_5530314.html]

http://pdf7.tarena.com.cn/tts8\_source/ttsPage/JAVA/JSD\_N\_V06/ORACLE/DAY03/SUPERDOC/01/index.html\#page\_top\_superdoc

[20181224193933604]: /images/20220403/5af535175ef64157b444a343e46c98bb.png
[20181224193933709]: /images/20220403/a02148faf58b4908816481fcbf54ffeb.png
[20181224193933793]: /images/20220403/f58ae9ee590e4fe7800625acd6d84749.png
[20181224193933812]: /images/20220403/c1f6cc3c308b4acea345fce2f91f4237.png
[https_www.cnblogs.com_1234abcd_p_5530314.html]: https://www.cnblogs.com/1234abcd/p/5530314.html