この記事では、SQL に関する関連知識を提供します。主に、SQL ステートメントの基本構文、SQL ステートメントの改善など、SQL ステートメントの知識に関連する問題を整理しています。一緒に見てみましょう。皆様のお役に立てれば幸いです。
SQL チュートリアル 」
1. SQL の概要SQL (Structured Query Language: 構造化照会言語)
は、リレーショナル データベース管理システム (RDBMS) を管理するために使用されます。 SQL の範囲には、データの挿入、クエリ、更新と削除、データベース スキーマの作成と変更、およびデータ アクセス制御が含まれます。 1. データベースとはデータベース (DB データベース) 概念: オペレーティング システムにインストールされたデータ ウェアハウス ソフトウェアは、大量のデータ 500wを保存できます。
機能: データ管理データの保存2. データベースの分類リレーショナル データベース (SQL)MySQL Oracle SqlServer DB2 SQLite テーブルおよびテーブル間の行を介して学生情報テーブル出席テーブル...
非リレーショナル データベース (noSQL) だけではない
非リレーショナル データベース オブジェクトは、オブジェクト独自のオブジェクトを通じて格納されます。決定するプロパティ
**B: * *table tab_new を作成し、col1,col2…を tab_old 定義のみから選択します4. 新しいテーブルを削除します
テーブルのタブ名を削除します
テーブル タブ名の変更主キーの追加(列)
主キーの削除: テーブル タブ名の変更主キー(列)を削除7.インデックスを作成します
注: インデックスは変更できません。変更したい場合は、インデックスを削除して再構築する必要があります。
8. ビューの作成create view viewname as select ステートメント
9. テーブル操作のためのいくつかの単純な SQL ステートメント
##Select:##select * from table1 where range
#Insert ::
:
：
：
：
:
:
UNION 演算子は、他の 2 つの結果テーブル (TABLE1 と TABLE2 など) を結合し、それらを削除します。テーブル内の重複行に対して導出されます。 ALL を UNION とともに使用する (つまり、UNION ALL) 場合、重複行は削除されません。どちらの場合も、派生テーブルのすべての行は TABLE1 または TABLE2 から取得されます。
EXCEPT演算子は、TABLE1 にはあるが TABLE2 には含まれていないすべての行を含め、重複する行をすべて削除することによって結果テーブルを導出します。 ALL を EXCEPT (EXCEPT ALL) とともに使用すると、重複行は削除されません。
INTERSECT演算子は、TABLE1 と TABLE2 の両方にある行のみを含め、重複する行を削除することによって結果テーブルを導出します。 ALL を INTERSECT (INTERSECT ALL) とともに使用すると、重複行は削除されません。
**注:** 演算子を使用した複数のクエリ結果行は一貫している必要があります。
左 (外部) 結合: 左外部結合 (左結合):結果セットには、結合テーブルの一致する行と、左側の結合テーブルのすべての行が含まれます。
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
right (outer) join: 右外部結合 (right join): 結果セットには、結合されたテーブルの一致する結合行と、右結合されたテーブルのすべての行の両方が含まれます。テーブル 。
完全/クロス (外部) 結合: 完全外部結合: シンボリック接続テーブルの一致する行だけでなく、接続されている 2 つのテーブル内のすべてのレコードも含まれます。テーブル。
たとえば、次のような関係を持つデータ:
1、内部結合
2.1、完全外部結合は完全結合と同じです
2.2、完全外部結合ですが、a.Key が null または b.Key が null
3.1、左外部結合は左結合と等しい
3.2、左外部結合ですが、b.Key は null
デカルト積
sp_renamedb 'old_name', 'new_name'
在我们操作的表数据量庞大而且又要关联其他表进行查询的时候或者我们操作的数据是临时性数据且在短期内会有很多DML操作(比如购物车)的时候或者我们做查询时需要连接很多个表的时候，如果直接操作数据库的业务表可能效率很低，这个时候我们就可以借助临时表来提升效率。
临时表顾名思义，是一个临时的表，数据库不会将其序列化到磁盘上(有些也会序列化到磁盘上)而是存在于数据库服务器的内存中(因此会增加数据库服务器内存的消耗)，在使用完之后就会销毁。临时表分为两种：会话临时表和全局临时表，区别在于可用的作用域和销毁的时机不同。会话临时表只在当前会话(连接)内可用，且在当前会话结束(断开数据库连接)后就会销毁；全局临时表创建后在销毁之前所有用户都可以访问，销毁的时机是在创建该临时表的连接断开且没有其他会话访问时才销毁，实际上在创建全局临时表的会话断开后，其他用户就已经不能在访问该临时表了，但此时该临时表并不会立即销毁，而是等所有正在使用该全局临时表的会话(或者说连接)断开之后才会销毁。当然有时考虑到内存占用的问题，我们也可以手动销毁(DROP)临时表。
目前大多数数据库厂商(Oracle、Sql Server、Mysql)都支持临时表，但不同的数据库创建和使用临时表的语法稍有不同。
方式一：
#会话临时表 CREATE TABLE #临时表名( 字段1 约束条件1, 字段2 约束条件2, ... ); #全局临时表 CREATE TABLE ##临时表名( 字段1 约束条件, 字段2 约束条件, ... );
方式二：
#会话临时表 SELECT 字段列表 INTO #临时表名 FROM 业务表; #全局临时表 SELECT 字段列表 INTO ##临时表名 FROM 业务表;
#查询临时表 SELECT * FROM #临时表名; SELECT * FROM ##临时表名;
#删除临时表 DROP TABLE #临时表名; DROP TABLE ##临时表名;
Mysql中没有全局临时表，创建的时候没有#
CREATE TEMPORARY TABLE [IF NOT EXISTS] 临时表名( 字段1 约束条件, 字段2 约束条件, ... ); #根据现有表创建临时表 CREATE TEMPORARY TABLE [IF NOT EXISTS] 临时表名 [AS] SELECT 查询字段 FROM 业务表 [WHERE 条件];
创建的临时表可以和业务表同名，若临时表和业务表同名时在该会话中会使用临时表
SELECT * FROM 临时表名;
为避免临时表名和业务表名相同时导致误删除，可以加上TEMPORARY关键字
DROP [TEMPORARY] TABLE 临时表名;
Oracle的临时表也只有会话级的，但同时又细化出了一个事务级别的临时表，事务级别的临时表只在当前事务中有效。
#会话级别 CREATE GLOBAL TEMPORARY TABLE 临时表名( 字段1 约束条件, 字段2 约束条件, ... ) ON COMMIT PRESERVE ROWS; #事务级别 CREATE GLOBAL TEMPORARY TABLE 临时表名( 字段1 约束条件, 字段2 约束条件, ... ) ON COMMIT DELETE ROWS;
SELECT * FROM 临时表名;
DROP TABLE 临时表名;
注意：一个SQL中不能同时出现两次临时表
企业开发中大多都是使用Spring进行事务管理的，很少自己开启事务、提交事务。我们大多都会将事务加在service层，这样在调用service层的每一个方法之前Spring都会为我们开启事务，在方法调用结束之后Spring会为我们提交事务，问题是数据库事务需要的数据库连接是在什么时候获取和释放的呢？这个是会影响我们对临时表的使用的。
一般来说，数据库连接是在事务开启之前获取的，也就是在我们调用事务方法之前，肯定要先获取数据库连接，然后才能开启事务，提交或回滚事务，然后关闭数据库连接，这种情况下貌似如果我们在该方法中创建了临时表，则在此之后直至方法结束之前我们都可以使用这个创建的临时表，这么说基本上是正确的。但有一种情况除外那就是如果我们在事务方法A中调用了另一个事务方法B，而事务方法B的事务传播机制是PROPAGATION_REQUIRES_NEW(将原事务挂起，并新开一个事务)时，如果临时表是在B方法中创建的，则A在调用完B之后(B的事务已经提交了)也不可以使用B中创建的事务级别的临时表，但是可以使用会话级别的临时表以及全局临时表。
(只复制结构,源表名：a 新表名：b) (Access可用)
法一：select * into b from a where 11（仅用于SQlServer） 法二：select top 0 * into b from a
(拷贝数据,源表名：a 目标表名：b) (Access可用)
insert into b(a, b, c) select d,e,f from a; --insert into b select * from a //从表a中获取数据，并将其插入到b中，只拷贝表的数据，不拷贝表的结构（前提：表b存在） --select * into b from a //将a中的数据拷贝到 b中，拷贝表的数据以及表的结构（前提：表b不存在） --select * into b from a where 1=0// 将a的表结构拷贝到b，不拷贝数据（前提：表b不存在）
(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子：..from b in '"&Server.MapPath(".")&"data.mdb" &"' where..(表名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)
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
(表名1：a 表名2：b)
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：a )
select * from (SELECT a,b,c FROM a) T where t.a > 1;
between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2select a,b,c, from table1 where a not between 数值1 and 数值2
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
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 .....
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
具体实现：
关于数据库分页：
declare @start int,@end int @sql nvarchar(600) set @sql=’select top’+str(@end-@start+1)+’+from T where rid not in(select top’+str(@str-1)+’Rid from T where Rid>-1)’ exec sp_executesql @sql
**注意：**在top后不能直接跟一个变量，所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列，如果top后还有具体的字段，这样做是非常有好处的。因为这样可以避免 top的字段如果是逻辑索引的，查询的结果后实际表中的不一致（逻辑索引中的数据有可能和数据表中的不一致，而查询时如果处在索引则首先查询索引）
select top 10 * form table1 where 范围
select top 10 * from tablename order by newid()
select newid()
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
评价： 这种操作牵连大量的数据的移动，这种做法不适合大容量的数据操作
**例如：**在一个外部表中导入数据，由于某些原因第一次只导入了一部分，但很难判断具体位置，这样只有在下一次全部导入，这样也就产生好多重复的字段，怎样删除重复字段
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
select name from sysobjects where type='U' // U代表用户
select name from syscolumns where id=object_id('TableName')TRUNCATE TABLE table1
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id des
1、where 1=1是表示选择全部，where 1=2全部不选
if @strWhere !='' begin set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere end else begin set @strSQL = 'select count(*) as Total from [' + @tblName + ']' end
我们可以直接写成
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 '+ @strWhere
2、收缩数据库
--重建索引 DBCC REINDEX DBCC INDEXDEFRAG --收缩数据和日志 DBCC SHRINKDB DBCC SHRINKFILE
3、压缩数据库
dbcc shrinkdatabase(dbname)
4、转移数据库给新用户以已存在用户权限
exec sp_change_users_login 'update_one','newname','oldname' go
5、检查备份集
RESTORE VERIFYONLY from disk='E:dvbbs.bak'
6、修复数据库
ALTER DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE [dvbbs] SET MULTI_USER
GO7、日志清除
SET NOCOUNT ONDECLARE @LogicalFileName sysname, @MaxMinutes INT, @NewSize INT USE tablename -- 要操作的数据库名 SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 @MaxMinutes = 10, -- Limit on time allowed to wrap log. @NewSize = 1 -- 你想设定的日志文件的大小(M) Setup / initialize DECLARE @OriginalSize int SELECT @OriginalSize = size FROM sysfiles WHERE name = @LogicalFileName SELECT 'Original Size of ' + db_name() + ' LOG is ' + CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' FROM sysfiles WHERE name = @LogicalFileName CREATE TABLE DummyTrans (DummyColumn char (8000) not null) DECLARE @Counter INT, @StartTime DATETIME, @TruncLog VARCHAR(255) SELECT @StartTime = GETDATE(), @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' DBCC SHRINKFILE (@LogicalFileName, @NewSize) EXEC (@TruncLog) -- Wrap the log if necessary. WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) AND (@OriginalSize * 8 /1024) > @NewSize BEGIN -- Outer loop. SELECT @Counter = 0 WHILE ((@Counter <p><strong>8、更改某个表</strong></p><pre class="brush:php;toolbar:false">exec sp_changeobjectowner 'tablename','dbo'
9、存储更改全部表
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch @OldOwner as NVARCHAR(128), @NewOwner as NVARCHAR(128) AS DECLARE @Name as NVARCHAR(128) DECLARE @Owner as NVARCHAR(128) DECLARE @OwnerName as NVARCHAR(128) DECLARE curObject CURSOR FOR select 'Name' = name, 'Owner' = user_name(uid) from sysobjects where user_name(uid)=@OldOwner order by name OPEN curObject FETCH NEXT FROM curObject INTO @Name, @Owner WHILE(@@FETCH_STATUS=0) BEGIN if @Owner=@OldOwner begin set @OwnerName = @OldOwner + '.' + rtrim(@Name) exec sp_changeobjectowner @OwnerName, @NewOwner end -- select @name,@NewOwner,@OldOwner FETCH NEXT FROM curObject INTO @Name, @Owner END close curObject deallocate curObject GO
10、SQL SERVER中直接循环写入数据
declare @i int set @i=1 while @i<p>案例：有如下表，要求就裱中所有沒有及格的成績，在每次增長0.1的基礎上，使他們剛好及格:</p><p>Name score</p><p>Zhangshan 80</p><p>Lishi 59</p><p>Wangwu 50</p><p>Songquan 69</p><pre class="brush:php;toolbar:false">while((select min(score) from tb_table)60 break else continue end
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //从少到多
select encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同 encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同declare @list varchar(1000), @sql nvarchar(1000) select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表A' set @sql='select '+right(@list,len(@list)-1)+' from 表A' exec (@sql)
EXEC master..xp_fixeddrives
if (select checksum_agg(binary_checksum(*)) from A) = (select checksum_agg(binary_checksum(*)) from B) print '相等' else print '不相等'
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 '?'开头到N条记录
Select Top N * From 表-------------------------------
N到M条记录(要有主索引ID)
Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc
----------------------------------
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
**例2：查询表中的最后以条记录，并不知道这个表共有多少数据,以及表结构。
set @s = 'select top 1 * from T where pid not in (select top ' + str(@count-1) + ' pid from T)' print @s exec sp_executesql @s
select Name from sysobjects where xtype=‘u’ and status>=0
select name from syscolumns where id=object_id(‘表名’)
select name from syscolumns where id in (select id from sysobjects where type = ‘u’ and name = ‘表名’)
两种方式的效果相同
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like ‘%表名%’
select name as 存储过程名称 from sysobjects where xtype='P’
select * from master…sysdatabases D where sid not in(select sid from master…syslogins where name='sa')
or
select dbid, name AS DB_NAME from master…sysdatabases where sid 0x01
select column_name,data_type from information_schema.columns
where table_name = 'テーブル名'
–リンク サーバーの作成
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', 'リモート サーバー名または IP アドレス'
exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, 'username', 'password'
–クエリ例
select * from ITSV.Database name.dbo.Table name
–インポート例
select * into ITSV のテーブル.データベース名.dbo.テーブル名
–使用されなくなったリンク サーバーを削除します
exec sp_dropserver 'ITSV ' , 'droplogins '
–リモート/LAN データに接続(openrowset/openquery/opendatasource)
–1, openrowset
–クエリ例
select * from openrowset( 'SQLOLEDB ', 'SQL サーバー名'; 'ユーザー名'; 'パスワード', データベース名.dbo . テーブル名)
–ローカル テーブルの生成
select * into table from openrowset( 'SQLOLEDB ', 'SQL サーバー名'; 'ユーザー名'; 'パスワード'、データベース名.dbo.テーブル名)
--ローカル テーブルをリモート テーブルに挿入します
insert openrowset( ' SQLOLEDB ', 'SQL サーバー名'; 'ユーザー名'; 'パスワード', データベース名.dbo.テーブル名)
select *from local table
–ローカル テーブルの更新
update b
set b.Column A=a.Column A
openrowset( 'SQLOLEDB ', 'SQL サーバー名'; 'ユーザー名'; 'パスワード', データベース名.dbo.テーブル名) を内部結合ローカル テーブルとして b
a.column1=b.column1
**
**
–2. Openquery を使用するには接続を作成する必要があります
–最初にリンク サーバーを作成するための接続を作成します
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', 'リモート サーバー名または IP アドレス'
–Query
select *
##FROM openquery(ITSV, 'SELECT * FROM database.dbo .Table name')
– ローカル テーブルをリモート テーブルにインポートします
insert openquery(ITSV, 'SELECT * FROM database.dbo.table Name')
select * from local table
–Update local table
update b
set b.Column B=a.Column B
FROM openquery(ITSV, 'SELECT * FROM データベース. dbo.テーブル名') を a.Column A=b.Column A
–3 の
#内部結合ローカル テーブル b として、opendatasource/ openrowsetSELECT *
##FROM opendatasource( 'SQLOLEDB ', 'データ ソース=ip/サーバー名;ユーザー ID=ログイン名;パスワード=パスワード' ).test.dbo .roy_ta
– ローカル テーブルをリモート テーブルにインポートします
insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID =ログイン名;Password=パスワード').Database.dbo.テーブル名
select * from local table
SQL Server の基本関数
6. SQL Server の基本関数##1. 文字列関数の長さと分析 use
1,datalength2、substring(expression,start,length) は部分文字列を受け取ります。文字列の **** 添え字は次のとおりです。 "1"、start は開始位置、length は文字列の長さです。実際のアプリケーションでは、len(expression) を使用して長さを取得します。
3,right(char_expr ,int_expr) は文字列の右側の int_expr 文字を返し、逆に左の文字も使用します。
4,isnull(check_ex*****pression*, *replacement_value* )check_expression が空の場合は、replacement_value の値が返されます。空でない場合は、check_expression が返されます。文字操作クラス
5,Sp_addtypeカスタマイズされたデータ型
例: EXEC sp_addtype 誕生日、日時、'NULL'6,set nocount { on|off}
返される結果から、Transact-SQL ステートメントの影響を受ける行数に関する情報が除外されます。ストアド プロシージャに実際のデータをあまり返さないステートメントが含まれている場合、この設定によりネットワーク トラフィックが大幅に削減され、パフォーマンスが大幅に向上します。 SET NOCOUNT 設定は、解析時ではなく、実行時または実行時に設定されます。
SET NOCOUNT が ON の場合、カウント (Transact-SQL ステートメントの影響を受ける行数を示す) は返されません。
SET NOCOUNT が OFF の場合、常識的なカウントが返されます
SQL クエリ: from が後に続くテーブルまたはビューの最大数: 256
SQL に Order by が含まれる場合SQL では、フィールドの最大容量は 8000 です。nvarchar は Unicode コードであるため、nvarchar の場合は (4000) です。
常識 SQL クエリ: from を後に続けることができるテーブルまたはビューの最大数: 256
クエリ時に SQL ステートメントに Order by が使用される場合、最初にソートし、その後
を取得します。SQL では、フィールドの最大容量は 8000 で、nvarchar(4000) の場合、nvarchar は Unicode コードであるため、
SQLチュートリアル #"
以上がSQL ステートメントの知識ポイントを要約するの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。
![[Web フロントエンド] Node.js クイック スタート](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
