/× 創(chuàng)建一個(gè)數(shù)據(jù)庫*/
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

/×創(chuàng)建一個(gè)用戶表*/
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) ,
)

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

/×設(shè)置創(chuàng)建的表中一些字段的約束*/

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

alter table userinfo add constraint df_userinfo default('True')for sex /×sex字段默認(rèn)值為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字段設(shè)置為唯一————- 注意:此括號(hào)內(nèi)沒有單引號(hào)*/
alter table score add constraint fk_score foreign key(u_id) references userinfo (id)/×創(chuàng)建id為score表的外鍵*/
alter table userinfo alter? column name varchar(20)? not null /×創(chuàng)建或添加字段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

/×向表中插入一些數(shù)據(jù)*/
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]%'

/×在表中查詢一些數(shù)據(jù)*/
select * from userinfo

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

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

select name from userinfo

select name,sex from userinfo

select name+address+'鄉(xiāng)' as '個(gè)人信息' from userinfo

select name+address+'鄉(xiāng)' as '個(gè)人信息' from userinfo where name+address+'鄉(xiāng)' is not null

select name+address+'鄉(xiāng)' as '個(gè)人信息' 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 '人數(shù)',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 時(shí),
????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

/×日期處理結(jié)束*/

/×刪除數(shù)據(jù)*/

delete from demo1 where column1=3

/×先刪除外鍵表數(shù)據(jù)，再刪除主鍵表數(shù)據(jù)*/
delete from demo2
delete from demo1

truncate table demo2

/×刪除數(shù)據(jù)結(jié)束*/

?

/×查詢?nèi)剿氖g的數(shù)據(jù)*/
——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 再給條件*/

————子查詢:將一個(gè)查詢的結(jié)果作為另一個(gè)查詢的條件
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'

/×重要系統(tǒng)表*/

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

/×獲取數(shù)據(jù)庫信息*/
select * from master.dbo.sysdatabases

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

/×獲取數(shù)據(jù)庫中索引信息*/
select * from sysindexes

/×重要系統(tǒng)表結(jié)束*/

?

——可編程性里->系統(tǒng)函數(shù)->字符串函數(shù)
select? Ascii('ab')——返回字符串表達(dá)式中最左邊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

——可編程性里->系統(tǒng)函數(shù)->字符串函數(shù)

?

/×集合運(yùn)算*/

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

/×并集，前提是每個(gè)數(shù)據(jù)表中的字段個(gè)數(shù)都相等*/
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

?

/×聯(lián)接*/
select * from userinfo?——:4
select * from score?——:5

——交叉聯(lián)結(jié)(CROSS JOIN)?——:20
select * from userinfo
cross join score

——內(nèi)聯(lián)結(jié)(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

——左外聯(lián)結(jié)(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

——右外聯(lián)結(jié)(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

?

?

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

% -----------------------與包含一個(gè)或多個(gè)字符的字符串匹配

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

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

?

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

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

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

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

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

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

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

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

sql 的一些語句總結(jié)