SQL基礎之基本操作

垂釣貓兒 2019-05-26

展開全文

1.UNION操作符

　　union操作符用來合并兩個或多個select語句的結果，要注意union內(nèi)部的每個select語句必須擁有相同數(shù)量的列，而且列也必須擁有相似的數(shù)據(jù)類型和相同的列順序。下面是我的數(shù)據(jù)表的截圖一個小例子。

那現(xiàn)在對數(shù)據(jù)表進行聯(lián)合操作，這里我們聯(lián)合這兩個表的bookName。

select bookName from myrank
union
select bookName from mybook;

從結果可以看出bookName這列顯示的是兩張表的聯(lián)合的數(shù)據(jù)，而且你如果仔細看會發(fā)現(xiàn)其實應該是有重復數(shù)據(jù)的，而結果沒有顯示說明重復數(shù)據(jù)被過濾掉了。如果想顯示重復數(shù)據(jù)可在union后添加All關鍵字。

select bookName from myrank
union all
select bookName from mybook;

2.select into與insert into select操作符

　　通過select into語句我們可以將一個表的信息復制到另一個表中，新創(chuàng)建的表中的列名會和原來的表的列名一樣。

--將mybook表的數(shù)據(jù)復制到mybookcopy表中，mybookcopy表無需我們創(chuàng)建，執(zhí)行這條語句后會自動創(chuàng)建mybookcopy表
select * into mybookcopy from mybook;
--復制多個表的列到新表中
select mybook.bookName,myuser.userName into newtable from mybook join myuser on mybook.userID=myuser.userId

insert into select語句則是從一個表中復制數(shù)據(jù)然后插入到另一個已存在的表中，目標表中原有的行不會受到任何影響。

insert into myrank(userId) select userID from mybook

3.約束

約束用于規(guī)定表中的數(shù)據(jù)規(guī)則，如果存在違反約束的數(shù)據(jù)行為，行為將會被約束終止。在SQl中有以下六個約束：

NOT NULL：這個應該很熟悉，就是不能為空

UNIQUE:保證列的每行都必須有唯一的值

PRIMARY KEY：主鍵，可以理解為是not null和unique的結合，確保這個表的某列有唯一標識

FOREIGN KEY：外鍵，它標識這個列是另外一個表的主鍵，用于保證這兩個表之間的參照完整性

CHECK：創(chuàng)建列中的值的范圍，保證列中的值符合指定的條件

DEFAULT：規(guī)定列的默認值

接下來是這6大操作的sql語句，每條語句上都有它的解釋。

--創(chuàng)建表時在列的后面加上NOT NULL關鍵字則添加了非空的約束
create table mytable
(
myID int NOT NULL,
myName nvarchar(32) NOT NULL
)
--創(chuàng)建表時在列的后面加上UNIQUE關鍵字則添加了唯一的約束
create table mytable2
(
myID int NOT NULL UNIQUE,
myName nvarchar(32) NOT NULL
)
--當表被創(chuàng)建好后，可以使用alter來新增唯一性約束
alter table mytable2 add constraint nameunique unique(myName)
--使用alter和drop來刪除唯一性約束
alter table mytable2 drop nameunique

--將mytable2刪除后重新創(chuàng)建，并設置myID為主鍵
create table mytable2
(
myID int NOT NULL,
myName nvarchar(32) NOT NULL,
constraint myprimary primary key(myID)
)
--而且在這個地方我還發(fā)現(xiàn)當刪除數(shù)據(jù)表后，如果馬上新建名字和字段一樣的表會直接將剛剛刪除的表重新顯示，原有的數(shù)據(jù)還在，只是添加了主鍵
--刪除主鍵
alter table mytable2 drop myprimary
--新增主鍵
alter table mytable2 add constraint myprimary primary key(myID,myName)

--首先刪除上面mytable2的主鍵，創(chuàng)建mytable3，并設置myName為主鍵
create table mytable3
(
myName nvarchar(32) NOT NULL PRIMARY KEY,
age int NOT NULL
)
--然后設置mytable2的myName為外鍵,刪除方式和上面差不多，也是采用drop關鍵字
alter table mytable2 add constraint myforeign foreign key(myName) references mytable3(myName)

--創(chuàng)建表時在列的后面加上check關鍵字則添加了約束
create table mytable3
(
myID int NOT NULL check (myID>0),
myName nvarchar(32) NOT NULL
)
--新增check約束
alter table mytable3 add constraint mycheck check(myName!='hh')
--刪除check約束
alter table mytable3 drop mycheck

--創(chuàng)建表時在列的后面加上default關鍵字,只是這個地方SQL2008做得還不夠好,添加一行數(shù)據(jù)如果不填myName它會顯示NULL,關了再查看才為fyz
create table mytable3
(
myID int NOT NULL check (myID>0),
myName nvarchar(32) default'fyz'
)
--刪除default約束
alter table mytable3 drop constraint DF__mytable3__myName__2B3F6F97;
--新增default約束
alter table mytable3 add default'hhh' for myName;

　　最后一個default約束我差不多搞了一上午才解決。本來我是按照菜鳥教程上寫的，結果發(fā)現(xiàn)并不可以，百度到w3c又發(fā)現(xiàn)菜鳥其實是復制w3c的，而百度基本前兩頁頁又全是那些復制菜鳥或者根本沒有具體例子的。好了，首先是我要刪除default約束，前面我都是給約束起了名字的，刪除時可以直接刪除約束。在default約束這里我是直接在創(chuàng)建表時指定的，使用菜鳥上的drop default并不可以，最后知道如果我在創(chuàng)建表時就添加約束則會有一個默認約束，數(shù)據(jù)庫系統(tǒng)會自動給這個約束命名，可以使用下面的方法得到這個約束的名字：

select c.name from sysconstraints a 
inner join syscolumns b on a.colid=b.colid 
inner join sysobjects c on a.constid=c.id 
where a.id=object_id('mytable3') 
and b.name='myName'

得到名字后再去刪除。第二個問題是我要修改約束，最后在微軟官網(wǎng)上才知道原來如果要修改default約束，必須要先刪除已有的約束然后再設置新的約束。我不知道是不是版本問題，菜鳥上是ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT，但是在我的SQL 2008上并沒有什么用，最后看到一位前輩使用add才添加成功。

4.create index、auto increment和null操作

　　當數(shù)據(jù)表的數(shù)據(jù)很多時，我們可以利用索引在不讀取整個表的情況下更快地查找數(shù)據(jù)。我們可以在表中創(chuàng)建索引，它是看不到的，只能利用加速搜索/查詢。但是使用索引也有缺點，更新一個包含索引的表比更新一個沒有索引的表花費更多的時間，因為索引本身也需要更新。由于索引有這個缺點，因此一般較好的做法是僅僅在常常被搜索的列上使用索引。我在敲完教程上的索引后竟然發(fā)現(xiàn)沒有講如何使用索引，把教程上的例子敲完我會再去深入學習下索引。auto increment從名字可以看到是自增的意思，這樣可以保證在新記錄插入表中時生成一個唯一的數(shù)字。

--創(chuàng)建索引,可以使用重復的值
create index myIdindex on mytable3(myId);
--創(chuàng)建不允許有重復值，一個索引只能對應這唯一的一行
create unique index myuniqueIndex on mytable3(myId);

--使用drop可以刪除索引、表、數(shù)據(jù)庫等
drop index myIdindex on mytable3;
drop index mytable3.myuniqueIndex;
--tip:如果我們需要清空表里的數(shù)據(jù)而不刪除表本身
truncate table myrank;
--alter table可用于在已有的表中添加、刪除和修改列
alter table mybook add bookOther nvarchar(32);
alter table mybook alter column bookOther nvarchar(64);
alter table mybook drop column bookOther;

--設置auto-increment主鍵字段,identity是設置主鍵的關鍵字，第一個1表示從1開始，第二個1表示每條記錄遞增1
create table mytable4
(
myId int identity(1,1) primary key,
myname nvarchar(16)
)

null代表未知數(shù)據(jù)，在sql里我們不能用“=”來判斷是否為null，必須使用is null和is not null來進行判斷。另外有時候我們需要對某一列的數(shù)據(jù)進行算術運算，可是這個時候這列的某些數(shù)據(jù)還是null，這樣就無法完成操作。既然有這個需求那肯定是有解決方案的，sql里有一個isnull函數(shù)，可將為null的某列的值設置一個默認值。下面是sql語句：

insert into mytable4 values('hh');
insert into mytable4 (myname) values (null);
--查找為空的行和不為空的行
select * from mytable4 where myname is null;
select * from mytable4 where myname is not null;
--isnull()函數(shù)，這樣如果某行的myname為null，則會輸出fyz
select myId,ISNULL(myname,'fyz') as myaname from mytable4;