/× 創建一個數據庫*/
CREATE DATABASE Drug ON PRIMARY
(
NAME='Drug',
FILENAME='D:\Drug.mdf',
SIZE=3027KB,
MAXSIZE=UNLIMITED,
FILEGROWTH=1024KB
)
LOG ON
(
NAME='Drug_LOG',
FILENAME='D:\Drug.ldf',
SIZE=1024,
FILEGROWTH=10%
)

USE[Drug]
GO

/×創建一個用戶表*/
CREATE TABLE userinfo
(
id int identity(1,1) ,/×自增長列*/
name varchar(20),
pwd nvarchar(16),
sex? char(5) ,
address nvarchar(50) ,
phone nvarchar(20) ,
plity varchar(10) ,
)

/×創建一個成績表*/
create table score
(
id int identity(1,1) not null,
u_id int? not null,
score int,
)

/×設置創建的表中一些字段的約束*/

alter table userinfo add constraint pk_userinfo primary key(id)/×創建id為userinfo表的主鍵——-注意:此括號內沒有單引號*/
alter table score add constraint pk_score primary key(id)/×創建id為score表的主鍵*/

alter table userinfo add constraint df_userinfo default('True')for sex /×sex字段默認值為True:男 注意后面的 for 字段名*/
alter table userinfo add constraint ck_userinfo check(len(pwd )? between?? 6?? and?? 16)/×pwd字段必須在6-16之間的————————檢查約束 之間用and表示,而不是&&*/
alter table userinfo add constraint uk_userinfo unique (name)? /×name字段設置為唯一————- 注意:此括號內沒有單引號*/
alter table score add constraint fk_score foreign key(u_id) references userinfo (id)/×創建id為score表的外鍵*/
alter table userinfo alter? column name varchar(20)? not null /×創建或添加字段name 并且不能為空*/
alter table userinfo alter column pwd nvarchar(16) not null
alter table userinfo alter column sex char(5) not null
alter table userinfo alter column address nvarchar(50) not null
alter table userinfo alter column phone nvarchar(20) not null
alter table userinfo alter column plity varchar(10) not null

/×向表中插入一些數據*/
insert into userinfo
([name],pwd, sex,address,phone, plity) values
('zhaoliu','123dfd999','True','江蘇省無錫市','15658789123','黨員')

——模糊查詢
select * from userinfo where username like 's%'
select * from userinfo where username like '%1'
select * from userinfo where username like 's%1'
select * from userinfo where username like '[a-z]%'
select * from userinfo where username like '[a-z0-9]%'
select * from userinfo where username like '[^a-z0-9]%'

/×在表中查詢一些數據*/
select * from userinfo

select * from userinfo where name like '%張%'

select count(*)? as '數量' from userinfo

select name from userinfo

select name,sex from userinfo

select name+address+'鄉' as '個人信息' from userinfo

select name+address+'鄉' as '個人信息' from userinfo where name+address+'鄉' is not null

select name+address+'鄉' as '個人信息' from userinfo where len(phone)!=12

select * from userinfo where sex='False' order by sex asc
select * from userinfo order by id desc
select * from userinfo order by id ,phone

select count(*) as '人數',sex from userinfo group by sex

update userinfo set name='趙柳' where sex='true' and name='zhaoliu'

/×日期處理*/
update t_user_info1 set birth='1919-3-2' where username='yyxinyi'

update t_user_info1 set birth=birth+1 where username='yyxinyi'

update t_user_info1 set birth=getdate() where username='aaa'

select username,datepart("yyyy",birth) as 年,
????datepart("mm",birth) as 月,
????datepart("dd",birth) as 日,
????datepart("hh",birth) as 時,
????datepart("mi",birth) as 分,
????datepart("ss",birth) as 秒
?from t_user_info1

select getdate()+10
select dateadd("mm",10,getdate())

select username,birth,datediff("yyyy",birth,getdate()) from t_user_info1

/×日期處理結束*/

/×刪除數據*/

delete from demo1 where column1=3

/×先刪除外鍵表數據，再刪除主鍵表數據*/
delete from demo2
delete from demo1

truncate table demo2

/×刪除數據結束*/

?

/×查詢三十到四十之間的數據*/
——solution 1
select top 40 * from userinfo?
except select top 30 *from userinfo

——solution 2
select top 10 * from userinfo where id
not in(
select top 30 id from userinfo
)

——solution 3
with t as(select row_number() over(order by id asc) as number,* from userinfo)
select * from t where number? between 31? and 40

——solution 4
with t as(select top 40 row_number() over(order by id asc )as noo,* from userinfo)
select id,name,level,upid from t
group by noo ,id,name,level,upid
having noo between 31 and 40

/×select 列 from 表 where 條件 order by 排序 group by 分組 having 再給條件*/

————子查詢:將一個查詢的結果作為另一個查詢的條件
select * from userinfo where id=
(
?select id from userinfo where name='%x%'
)
————連接
select t_i_topical.*,t_user_info1.username from t_i_topical
inner join? t_user_info1
on? t_user_info1.uid=t_i_topical.uid
where username='xinyi'

/×重要系統表*/

if not exists(select * from sysobjects where xtype='U' and name='backup_t_user_info1')
?select username,password into backup_t_user_info1 from t_user_info1
else
?insert into backup_t_user_info1 select username,password from t_user_info1

/×獲取數據庫信息*/
select * from master.dbo.sysdatabases

/×獲取數據庫中對象(表、視圖、約束...)信息*/
select * from sysobjects where xtype='U'

/×獲取數據庫中索引信息*/
select * from sysindexes

/×重要系統表結束*/

?

——可編程性里->系統函數->字符串函數
select? Ascii('ab')——返回字符串表達式中最左邊ASCII代碼值

select left('123.45678',charindex('.',round(123.45678,2),0)+2)

select left('123.45678',charindex('.','123.45678',0)+2)

select charindex('.','12345678',0)

select left('12323.545',2)

?

declare @i int
select @i=convert(char(10),1234)
select @i

select max(pwd),min(phone),avg(pwd),count(id),sum(phone) from userinfo

——可編程性里->系統函數->字符串函數

?

/×集合運算*/

create table temp1
(
?t1 int identity(1,1) primary key,
?t2 int
)

insert into temp1(t2) values(1)
insert into temp1(t2) values(2)
insert into temp1(t2) values(3)

create table temp2
(
?t1 int identity(1,1) primary key,
?t3 int
)

insert into temp2(t3) values(1)
insert into temp2(t3) values(2)
insert into temp2(t3) values(11)

?

select * from temp1
select * from temp2

/×并集，前提是每個數據表中的字段個數都相等*/
select * from temp1
ｕｎｉｏｎ
select * from temp2

/×集合差*/
select * from temp1
except
select * from temp2

select * from temp2
except
select * from temp1

/×交集*/
select * from temp1
intersect
select * from temp2

——in 或者or
select * from userinfo
select * from userinfo where name='張三' or name='李四'

select * from userinfo where name in('張三','李四')

——with
——ok
with t as(select * as t1 from demo2)
delete from t
——error
with t as(select count(*) as t1 from demo2)
delete from t

?

/×聯接*/
select * from userinfo?——:4
select * from score?——:5

——交叉聯結(CROSS JOIN)?——:20
select * from userinfo
cross join score

——內聯結(INNER JOIN)?——:5
select u.id,u.name,u.pwd,u.sex,u.address,u.phone,u.plity,s.id,s.u_id,s.score from userinfo as u
inner join score as s
on u.id=s.u_id

——左外聯結(left outer JOIN)?——:5
select? u.id,u.name,u.pwd,u.sex,u.address,u.phone,u.plity,s.id,s.u_id,s.score from userinfo as u
left outer join score as s
on u.id=s.u_id

——右外聯結(right outer JOIN)?——:5
select? u.id,u.name,u.pwd,u.sex,u.address,u.phone,u.plity,s.id,s.u_id,s.score from userinfo as u
right outer join score as s
on u.id=s.u_id

?

?

_ -----------------------與任意單字符匹配

% -----------------------與包含一個或多個字符的字符串匹配

[] ----------------------與特定范圍（例如，[a-f]）或特定集（例如，[abcdef]）中的任意單字符匹配。

[^] -----------------------與特定范圍（例如，[^a-f]）或特定集（例如，[^abcdef]）之外的任意單字符匹配。

?

使用like比較字，加上SQL里的通配符，請參考以下：

a、LIKE 'Mc%' 將搜索以字母 Mc 開頭的所有字符串（如 McBadden）。

b、LIKE '%inger' 將搜索以字母 inger 結尾的所有字符串（如 Ringer、Stringer）。

c、LIKE '%en%' 將搜索在任何位置包含字母 en 的所有字符串（如 Bennet、Green、McBadden）。

d、LIKE '_heryl' 將搜索以字母 heryl 結尾的所有六個字母的名稱（如 Cheryl、Sheryl）。

e、LIKE '[CK]ars[eo]n' 將搜索下列字符串：Carsen、Karsen、Carson 和 Karson（如 Carson）。

f、LIKE '[M-Z]inger' 將搜索以字符串 inger 結尾、以從 M 到 Z 的任何單個字母開頭的所有名稱（如 Ringer）。

g、LIKE 'M[^c]%' 將搜索以字母 M 開頭，并且第二個字母不是 c 的所有名稱（如MacFeather）。

sql 的一些語句總結