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

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

一、基础:

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

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

(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)左外连接:

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

(2)右外连接:

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

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

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

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

http://pdf7.tarena.com.cn/tts8_source/ttsPage/JAVA/JSD_N_V06/ORACLE/DAY03/SUPERDOC/01/index.html#page_top_superdoc

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/114628.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)
blank

相关推荐

  • 关于File类概念及方法的一些介绍

    关于File类概念及方法的一些介绍java.io.File类是文件和目录路径名的抽象表示,主要用于文件和目录的创建、查找和删除等操作。Java把电脑中的文件和文件夹(目录)封装为一个File类,我们可以使用File类对文件和文件夹进行操作。File类的方法可以实现:1.创建一个文件/文件夹2.删除文件/文件夹3.获取文件/文件夹4.判断文件/文件夹是否存在5.对文件夹进行遍历6.获取文件的大小File类是一个与系统无关的类,任何操作系统都可以使用这个类中的方法重点:File:文件;Directory:文件夹/目录

  • 长轮询的使用实现_python 轮询

    长轮询的使用实现_python 轮询轮询(Polling):是指不管服务器端有没有更新,客户端(通常是指浏览器)都定时的发送请求进行查询,轮询的结果可能是服务器端有新的更新过来,也可能什么也没有,只是返回个空的信息。不管结果如何,客户端处理完后到下一个定时时间点将继续下一轮的轮询。长轮询(LongPolling):长轮询的服务其客户端是不做轮询的,客户端在发起一次请求后立即挂起,一直到服务器端有更新的时候,服务器才会主动推送信息到…

    2022年10月14日
  • 黑盒测试方法的选择策略_选择策略

    黑盒测试方法的选择策略_选择策略

  • HNUSTOJ-1543 字符串的运算再现

    HNUSTOJ-1543 字符串的运算再现

  • required属性的作用_required的作用

    required属性的作用_required的作用1,required属性-表示字段不能为空(注意:只有用户单击“提交”按钮提交表单的时候,浏览器才会执行验证。目前HTML5不支持指定验证的时间,而且验证消息的样式和内容各个浏览器不大一样,不能修

  • IdeaVim 基本操作[通俗易懂]

    IdeaVim 基本操作[通俗易懂]IdeaVim基本操作安装插件ideaVim剪贴板与系统剪贴板同步CapsLock键映射为Esc键光标的移动安装插件File-Settings-Plugins,BrowseRepositories,输入ideavim,安装2.重启IntelliJIDEAideaVim剪贴板与系统剪贴板同步在ideavimrc文件中添加一行:setclipboard=unnamedplus,unnamed保存并退出:wq重启IntelliJIDEACaps

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号