Java中最全面的SQL操作指南 SQL基础 基本SQL操作

发表于

这是我参与11月更文挑战的第7天，活动详情查看：2021最后一次更文挑战

SQL基础

创建数据库
1
sql复制代码CREATE DATABASE database_name
删除数据库
1
sql复制代码DROP DATABASE database_name
备份数据库
  • 创建备份的device:
1
2
sql复制代码USE master
EXEC sp_addumpdevice 'disk','database_backup','d:\mssql7backup\database_backup.dat'
  • 开始备份:
1
sql复制代码BACKUP DATABASE origin_database TO database_backup
分离数据库
1
sql复制代码sp_detach_db
附加数据库
1
sql复制代码sp_attach_db 'database_name','d:\mssql7backup\database_backup.dat'
修改数据库名称
1
sql复制代码sp_renamedb 'old_name','new_name'
创建新表
1
2
3
4
sql复制代码CREATE TABLE table_name (
	col1 type1 [NOT NULL] [primary key],
	col2 type2 [NOT NULL],
	...)
根据已有的表创建新表
  • 根据旧表创建新表:
1
sql复制代码CREATE TABLE table_name LIKE table_old
1
sql复制代码CREATE TABLE tab_name AS SELECT col1,col2… FROM tab_old definition only
删除新表
1
sql复制代码DROP TABLE table_name
增加一个列
1
sql复制代码ALTER TABLE table_name ADD COLUMN col TYPE
  • 列增加后将不能删除,列加上后数据类型也不能改变,唯一能够改变的是增加varchar类型的长度
添加主键
1
sql复制代码ALTER TABLE table_name ADD PRIMARY KEY (col)
删除主键
1
sql复制代码ALTER TABLE table_name DROP PRIMARY KEY (col)
创建索引
1
sql复制代码CREATE [UNIQUE] INDEX index_name ON table_name (col,...)
删除索引
1
sql复制代码DROP INDEX index_name
  • 索引是不可更改的,想要更改索引必须删除重新建立新的索引
创建视图
1
sql复制代码CREATE VIEW view_name AS SELECT statement
删除视图
1
sql复制代码DROP VIEW view_name

基本SQL操作

查找
1
sql复制代码SELECT filed,... FROM table_name WHERE field LIKE ’%value%’
选择
1
sql复制代码SELECT field,... FROM table_name WHERE 范围
插入
1
sql复制代码INSERT INTO table_name (field1,field2) VALUES (value1,value2)
修改
1
sql复制代码UPDATE table_name SET field1=value1 WHERE 范围
删除
1
sql复制代码DELETE FROM table_name WHERE 范围
排序
1
sql复制代码SELECT filed,... FROM table_name ORDER BY field1,field2 [DESC]
总数
1
sql复制代码SELECT count AS total_count FROM table_name
求和
1
sql复制代码SELECT SUM(field) AS sum_value FROM table_name
平均
1
sql复制代码SELECT AVG(field) AS avg_value FROM table_name
最大
1
sql复制代码SELECT MAX(field) AS max_value FROM table_name
最小
1
sql复制代码SELECT MIN(field) AS min_value FROM table_name

SQL高级查询

UNION运算符
  • UNION运算符是通过组合其余两个结果表并且消去表中任何重复行而派生出一个结果表
  • 当UNION和ALL一起使用时,即UNION ALL, 此时不会消除重复行
EXCEPT运算符
  • EXCEPT运算符通过包括所有在某一个表但是不在另一个表中的行并消除所有重复行而派生出一个结果集
  • 当EXCEPT和ALL一起使用时,即EXPCET ALL, 此时不会消除重复行
INTERSECT运算符
  • INTERSECT运算符通过只包括两个表中都有的行并消除所有重复行而派生出一个结果集
  • 当INTERSECT和ALL一起使用时,即INTERSECT ALL, 此时不会消除重复行
LEFT JOIN
  • 左连接: 左外连接. 结果集既包括连接表的匹配行,也包括左连接表的所有行
1
sql复制代码SELECT a.a, a.b, a.c, b.c, b.d, b.f FROM a LEFT JOIN b ON a.a = b.c
RIGHT JOIN
  • 右连接: 右外连接. 结果集既包括连接表的匹配连接行,也包括右连接表的所有行
FULL JOIN
  • FULL JOIN或者CROSS JOIN
  • 全连接: 全外连接. 结果集既包括连接表的匹配行,也包括两个连接表中的所有记录
GROUP BY
  • 一张表,如果分组完成后,查询后,只能得到组相关信息
    • 统计信息:
      • COUNT
      • SUM
      • MAX
      • MIN
      • AVG
    • 分组的标准
  • 分组时: 不能以text, ntext, image类型的字段作为分组依据
  • 在SELECT统计函数中的字段,不能和普通字段放在一起

SQL提升

复制表
  • 复制表: 只复制表结构.源表名为a,新表名为b
1
sql复制代码SELECT filed,... INTO b FROM a WHERE 1<>1
1
sql复制代码SELECT TOP 0 field,... INTO b FROM a
拷贝表
  • 拷贝表: 拷贝数据.源表名a,新表名b
1
sql复制代码INSERT INTO b(a, b, c) SELECT d,e,f FROM a
跨数据库之间表的拷贝
  • 跨数据库之间的表的拷贝: 具体数据使用绝对路径
1
sql复制代码INSERT INTO b(a, b, c) SELECT d,e,f FROM b IN '"&Server.MapPath(".")&"\data.mdb" &"' where 条件
子查询
1
sql复制代码SELECT a,b,c FROM a WHERE a IN (SELECT d FROM b )
显示文章,提交人和最后回复时间
1
sql复制代码SELECT a.title,a.username,b.adddate FROM table_name a,(SELECT MAX(adddate) adddate FROM table_name WHERE table_name.title=a.title) b
连接查询
1
sql复制代码SELECT a.a, a.b, a.c, b.c, b.d, b.f FROM a LEFT OUT JOIN b ON a.a = b.c
视图查询
1
sql复制代码SELECT filed,... FROM (SELECT a,b,c FROM a) T WHERE t.a > 1
BETWEEN
  • BETWEEN:
    • BETWEEN限制查询数据范围时包括边界值
    • NOT BETWEEN限制查询数据范围时不包括边界值
1
2
3
sql复制代码SELECT a,b,c FROM table1 WHERE TIME BETWEEN time1 AND time2

SELECT a,b,c FROM table1 WHERE a NOT BETWEEN 数值1 AND 数值2
IN
1
sql复制代码SELECT a,b,c FROM table1 WHERE a [NOT] IN (‘值1’,’值2’,’值4’,’值6’)
EXISTS
  • 两张关联表,删除主表中已经在副表中没有的信息
1
sql复制代码DELETE FROM table1 WHERE NOT EXISTS (SELECT a,b,c FROM table2 WHERE table1.field1=table2.field1 )
四表联查
1
sql复制代码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 条件
日程安排提前5分钟提醒
1
sql复制代码SELECT filed,... FROM 日程安排 WHERE datediff('minute',f开始时间,getdate())>5
数据库分页
1
sql复制代码SELECT TOP 10 b.filed,... FROM (SELECT TOP 20 主键字段,排序字段 FROM 表名 ORDER BY 排序字段 DESC) a,表名 b WHERE b.主键字段 = a.主键字段 ORDER BY a.排序字段
查询前10条记录
1
sql复制代码SELECT TOP 10 field,... form table1 WHERE 范围
选择每一组b值相同的数据中a最大的记录的所有信息
  • 每月的排行榜
  • 热销产品分析
  • 按科目成绩排名
1
sql复制代码SELECT a,b,c FROM table_name ta WHERE a=(SELECT MAX(a) FROM table_name tb WHERE tb.b=ta.b)
包含所有在tableA中但是不在tableB和tableC中的行并消除所有重复行而派生出的一个结果表
1
sql复制代码SELECT a FROM tableA EXCEPT (SELECT a FROM tableB) EXCEPT (SELECT a FROM tableC)
随机取出10条数据
1
sql复制代码SELECT TOP 10 field,... FROM table_name ORDER BY newid()
随机选择记录
1
sql复制代码select newid()
删除重复记录
1
sql复制代码DELETE FROM table_name WHERE id NOT IN (SELECT MAX(id) FROM table_name GROUP BY col1,col2,...)
  • 下面这种做法可以实现删除重复记录,但是操作牵连到大量的数据移动,不适合大容量的数据操作:
1
2
3
sql复制代码SELECT DISTINCT field,... INTO TEMP FROM table_name
DELETE FROM table_name
INSERT INTO table_name SELECT field,... FROM TEMP
  • 示例: 在一个外部表导入数据,由于某些原因第一次只导入了一部分,但是很难判断具体位置,只有在下一次全部导入,这样也就产生好多重复的字段,需要删除重复的字段:
1
2
3
4
5
6
sql复制代码
ALTER TABLE table_name
ADD column_b INT IDENTITY(1,1)
DELETE FROM table_name WHERE column_b NOT IN(
SELECT MAX(column_b) FROM tablename GROUP BY column1,column2,...)
ALTER TABLE table_name DROP COLUMN column_b
列出数据库里所有的表名
1
sql复制代码SELECT name FROM sysobjects WHERE TYPE='用户名'
列出表里所有的列名
1
sql复制代码SELECT name FROM syscolumns WHERE id=object_id('table_name')
初始化表
1
sql复制代码TRUNCATE TABLE table_name
选择10到15的记录
1
sql复制代码SELECT TOP 5 field,... FROM (SELECT TOP 15 field,... FROM TABLE ORDER BY id ASC) a ORDER BY id DESC

基本SQL函数

字符串函数

DATALENGTH
  • DATALENGTH(char_expr): 返回字符串包含的字符数,但是不包含后面的空
SUBSTRING
  • SUBSTRING(expresion, start, length): 取子串,字符串的下标从 “1” 开始 ,start为起始位置 ,length为字符串长度.
  • 工程实践中,使用**LEN(expression)**取得表达式的长度
  • RIGHT(char_expr, int_expr): 返回字符串右边第int_expr个字符
LEFT
  • LEFT(char_expr, int_expr): 返回字符串左边第int_expr个字符
ISNULL
  • ISNULL(check_expression, replacement_value): 如果check_expression为空,则返回replacement_value的值.如果check_expression不为空,则返回check_expression表达式的值
SP_ADDTYPE
1
sql复制代码EXEC SP_ADDTYPE birthday, datetime, 'NULL'
SET NOCOUNT
  • SET NOCOUNT: 使返回的结果中不包含有关受Transact-SQL语句影响的行数的信息
  • 如果存储的过程中包含的一些语句并不返回许多实际的数据,那么该设置可以大量减少网络流量,显著提高性能
  • SET NOCOUNT是在执行或运行时设置,而不是在分析时设置:
    • 当SET NOCOUNT设置为NO时,不返回计数,即不返回受Transact-SQL语句影响的行数
    • 当SET NOCOUNT设置为OFF时,返回计数

SQL注意点

  • 在SQL查询中 ,FROM后面最多可以跟256张表或者视图
  • 在SQL语句中出现ORDER BY查询时,先进行排序,然后再取值
  • 在SQL中,一个字段的最大容量是8000, 而对于nvarchar而言最大容量是4000, 因为nvarchar是Unicode码

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%