初级sql小结

牛刀小试；呵呵！还望各位兄台要多多照顾 O~~！

 

下面就让我们开始吧！

 

 

 

一．【1】创建数据库

create database  mydb3--- 数据库名

--以下为数据文件

on

(

       name='mydb3_data',

       filename='d:/db/mydb3_data.mdf',--ndf

       size=5mb,

       maxsize=100mb,

       filegrowth=10%

)

--以下为日志文件

log on(

       name='mydb3_log',

       filename='d:/db/mydb3_log.ldf',

       size=1mb,

       maxsize=5mb,

       filegrowth=1mb

)

创建表

--使用数据库

Use 数据库名

create table student

(   stuid int primary key,--主键约束

       stuname varchar(20) not null,--非空约束，不写默认允许为空

       --默认值为'男',并限制只能为'男'或'女'

       stusex char(6) default('男') check(stusex='男' or stusex='女'),

       stuage int check(stuage>0 and stuage<100),--age在0和100之间

       nickname varchar(20) unique --唯一值约束

)

create table score(

       sid int identity(100,2) primary key,--设置标识列 默认种子为100，增量为2，identity默认种子为1，增量为1

       score decimal(4,1) not null,/*数字一共4位 且小数1位*/

     --或应用公式给表赋值如     列3 as(列1+1)，

       rdate datetime default(getdate()),--默认当前日期

       passdate datetime default('2008-9-1'),--默认2008-9-1，日期也要用单引号

）

 

       studentid int foreign key references student(stuid) --外键约束 约束它的选择范围 只能从主键中选择

)

 

--向表中插入数据

Insert into student(stuid,stuname,stuage,nickname) values(2,'tom2',-6,'killer2')

select * from student

insert into 恋人(youname,fname,age,startogethertime,endtime)

values('小名','小红',20,'2006-11-11' ,'2007-12-2')-----日期也要用' '括起来

 

--另一种方法是在建表之前插入 但只能用一次

Select  into test2 from test1

 

 

--声明局部变量

--declare @num int

 

--使用全局变量，查看SQL的版本

--select @@version

 

--distinct过滤重复的 select distinct * from 表名select distinct b,msdb两列 from orders表名

-- select top n * from 表名（查看表的头几行其中n为行数）*号或者是列名

--select top 10 percent * from order （查看表的头%10的几行其中n为行数）尤其注意不要忘记了* 号或者列名

--select * from 表名 where 条件（ ship='usa'）字符要加''号数字不要 and  or

-- 列名 in(1,2,3)相同的条件名的情况下 列名 not in (1,2,3)不在  字符要加''号数字不要

select price from titles where price in(select min(price) from titles)--单此括号了里是一个值的时候in可以用‘=’号

 

-- 列名 between 2 and 7 ，在2与7的之间范围包括2与7 与 列名 >=2 and  名<=7相同

--列名 between '1997-1-1' and '1998-7-5'    列名>'1997-1-1'

--列名 =null错误的查法   列名 is null 正确

-- select * from 表名 order by  列名  排列从大--小desc降序  默认asc升序

--union 联合语句把两个查询语句联合起来

--like 字符串查询select * from orders where customerid like 'v%' 如果知道它的确切字符--可把like该成=   'v-c%'

-- '%f%' （含f即可）与 '_f%'第二个是f 区别

--[]与[^]区别  '列名 [a,t,i]%' 等于a t i '列名 [^a,t,i]%'不能等于a t i

--'h---[a-h]%'a到h 的字母

--名 <> 8不等于8 符号=等号

-- -单个字符 % 所有字符 [] 是里面的值 [^]不是里面的值

--select * into test2 from test1 把表1的数据复制到表2格式没有复制

--create table #student 局部临时表 （建表人可以看到） create table ##student全局临时表（全部的人都可以看到）

--select count(*)as 记录1 from 表名，（不忽略空值）  算有几条记录

--select  count(列名)as 记录1 from 表名 忽略空值

--primary key (列 1，列2)--联合主键

 

--set identity-insert 表名 on/off  自动增长列手工插入，在主键设置为自动增长列时，要先把自动增长关闭才能插入

-- 插入是先查插主键表

--删除是先删外键表

--计算式不要用''

 

--sp_help 表名  -- 查询表的信息 定义的类型

sp_help orders

alter table 表名 add constraint 新列名 primary key（新列名）--给表设置主键 前提表应没有主键

alter table orders add constraint [新列名] primary key (新列名)

alter table orders add constraint新列名check (新列名>0)—添加约束

alter table orders add constraint新列名unique(新列名)—唯一值

altre tabel stuinfo drop constraint列名—删除表中的列

 

建表2

create view  v3

as

select 表名.*,表名，* from ……    inner join ……    on ……

-------可以掩藏代码

--select * from v3

修改

--alter view v3

--as

--select

 

在表中的列有不同的情况下显示不同的语句

方法一

select [列名=]country=

case

when  条件1 then  结果1

else  结果2

end

from 表名

方法二

select case

[列名 ] country

  when 'usa'then '结果'

end

from 表名

 

select  学号,姓名,成绩=

case

when mark>=80  then '优秀'

when mark between 61 and 79 then '良'

when mark=60 then '及格'

when mark between 1 and 59 then '不及格'

when mark=0 then '缺考'

end

from  stuinfo inner join stumark on  stuinfo.学号=stumark.学号

select 姓名 from stuinfo where mark<60

 

--删除数据库

--drop database  mydb3

--as 重命名

select 姓名,datepart(yy,getdate())-年龄 as 出生年份 from 学生表

--删除表

drop table student

--delete from test2 删除表2的内容而drop 则删除表

truncate table  表名

-- VS

--drop table 表名

--delete from tesr2 where tname like '%2%'删除

--truncate table test2（表名） 删除数据不删除表格形式同delete

drop  是删除对象

delete 是删除数据

delete from 表名   是删除表中所有的内容

delete from 表名 where 条件and 条件  删除满足条件的数据

truncate table 表名  只能用与删除整个表的内容不能跟条件与delete from 表名功能相同

 

 

 

count(*)   统计所有行数但不忽略空值行

空值不纳入count(*)以外的计算，计算后的值为整数部分

select count（distinct 列名)消除重复的数据

 

 

A.实体完整性约束  主键  B.引用完整性约束  主外键表    C.域完整性约束  对列进行的约束  D.自定义完整性约束  用户自己定义的约束

alter table abc add 列名

 

列的自增列打开的话插不进去 – set identity _insert abc on/off 打开后插入时列名不能省略

 

select orderid,sum(UnitPrice*Quantity * (1-Discount))

from [order details]

group by ordered

 

select *from 表名 order by 列名 按顺序排列默认为asc升序

select *from orders order by employeeid desc 按顺序排列加上desc就按照降序排列

 

select *from orders where orderdate between '1997-1-1' and'1998-1-1'

日期的查询也可用关系运算符<、>、<>、=  !<，!> 等

select *from orders where shippeddate is null查询该表中shippeddate为空的记录

 

insert into course values('English') --在插入是按定义是的顺序插入时不要写名称可以

 

select ..

where ..

group by

having

where discount=0 --对分组之前的数据进行筛选

group by orderid --对数据分组

having orderid>50 --对分组后的数据进行筛选

 

--子查询

select * from orders where orderid in

(select orderid from [order details]

 where unitprice>50)

 

--在delete中使用子查询

delete from orders where orderid in

(select orderid from [order details]

 where unitprice>50)

 

--exists:存在

--exists(查询语句)：如果查询语句有返回一条以上的纪录，则为真

--    否则为假，即不存在纪录

if exists(select 4+4)

select * from orders

 

select * from orders where exists(select 4+4)

if exists (select * from sysobjects where name='课程表eee')

  select 'yes'

else

   select 'no'

 

 

--同样可以给表重新命名

select o.orderid as 编号,od.orderid from orders as o,[order details] as od

--基本连接

select c.*,s.* from score c join stu s on s.stuid=c.stuid

 

--左联接：左表中的所有纪录都显示，

--右表中有匹配的才显示；如果没有匹配的则用null替代

select s.*,c.* from stu s left outer join score c

on s.stuid=c.stuid

 

--右联接：右表中的所有纪录都显示，

--左表中有匹配的才显示；如果没有匹配的则用null替代

select c.*,s.* from score c right outer join stu s

on s.stuid=c.stuid

 

 

--内联接：只显示2个表都匹配的纪录

select c.*,s.* from stu s inner join score c

on s.stuid=c.stuid

 

select state, count(*) as 次数 from authors  group by state -- 次数=count(*)  count(state)

select 总数=sum(qty) from sales group by stor_id --命名的另一种方法

select 'tom'+' '+'came'   --连接

select * from sales where ord_date='1994-6-14'--1994/6/14

select title_id from titles where title_id like 'tc%'

select * into #test1 from titles where title_id like 'tc%'--只能用一次且只能在建立表前

create table #test1---在tempdb中建立

 

--insert6 into 表名(列名1，列名2，列名n)values(插入的内容)

insert into #test1 select * from titles where title_id like 'tc%'--插入时不把约束带入

 

 

 

select * from authors

更改列的值

update 表名 set 列名1=值1，列名2=值2，  where 条件

update test1 set age=50 where age>60,

update test1 set tname='liba',age=26,score=0 where tname is null改变数据

update authors set au_lname=replace(replace(au_lname,'n','45678'),'45678','n')--可嵌套使用更简单

update test1 set age=50 where age>60

update test1 set tname='liba',age=26,score=0

where tname is null from authors

 

update authors set au_lname=replace(au_lname,'45678','n')

from authors

 

以下4个聚合函数忽略空值(null)

--sum avg  count max min  select max(列名)from 表名 只算有值的空值不算--只算列的  求个人的平均值时用     列1+列2+列3+列n/n

select max(ytd_sales) from titles—最大值

select min(ytd_sales) from titles—最小值

select sum(royalty) from titles—求总和

select avg(royalty) from titles—求平均值

字符串函数

select len(' ad f ')—计算长度

select right('afdfdg',5)—取右边的5个字符

select replace('abc%defcd','c%d','de')—替换

select stuff('abcdefgcd',3,2,'de')—从第三个字母开始计到后两位替换为

select substring('adbhgrtr',3,4)—从第三个计取出后四个字母

select char(23)

select 'a'+space(10)+'b'—a与b之间隔着10个空格

字符逆输出

select reverse('abcdefg') --变成gfedcba

把第一字符串中第m个位置开始的n个字符换成最后一个字符串

select stuff('abcdefg',2,4,'12356')--从b开始到e 都变成12356

 

取出字符串中第m个位置开始的n个字符

select substring('aabggdfg',2,4)--变成abgg

 

--查找第一个字符串在第二个字符串中开始的位置

select charindex('fa','testagdadbbdffas')--查询结果为14

 

--查询第一个字符串在列中所处的位置

select charindex('ee',au_lname) from authors order by au_id

 

--计算字符串的长度

select len('abcdef') as 长度--计算结果为6

 

select len(au_lname) as 名字长度 from authors

--大小写

select upper('abcdEf') 大写--变成ABCDEF,lower('abcdEf') 小写--变成abcdef

select upper(au_lname),lower(au_lname) from authors

--删除左边或者右边的空格

select 'start'+ltrim('  ab c d efg  ')+'end'--变成startab c d efg  end

select 'start'+rtrim('  ab c d efg  ')+'end'--变成start  ab c d efgend

select 'start'+rtrim(ltrim('  ab c d efg  '))+'end'--变成startab c d efgend

select 'start'+'  ab c d efg  '+'end'--连接变成start  ab c d efg  end

 

--用第三个表达式替换第一个字符串表达式中

--出现的所有第二个字符串表达式的匹配项

select replace('abcdecfgh','cf','123')--变成abcde123gh

select replace('abcdecfgh','cfe','123')--变成abcdecfgh  当不存在时候就是本身

 

日期函数

对日期的制定部分增加相应的值

select dateadd(mm,3,getdate())

--yy/yyy/year 年

--mm/month 月

--dd/day 天

select getdate()--获取当前系统日期

select dateadd(MM,3,'2008-1-3')--在原有的日期的月上加上3-

select datediff(ss,'2006-2-1','2008-11-1')--计算两个日期之间相差多少秒-ss-

select datepart(minute,getdate())--取日期的分钟

select month(dateadd(MM,3,'2008-1-3'))

 

 

数字计算函数

floor返回小于或等于指定数值表达式的最大整数。

Select floor(4.5) --变成4

 

abs返回指定数值表达式的绝对值

select abs(7-15)--绝对值

 

ceiling返回大于或等于指定数值表达式的最小整数。

select ceiling(4.5)--变成5

 

power返回指定表达式指定幂的值

select power(3,4)--3的4次方

 

round返回一个数值表达式，四舍五入到指定的长度或精度。

select round(3456.789,2)

select round(3456.784,2)--精确两位小数包含四舍五入

 

sqrt返回指定表达式的平方根。

select sqrt(4)--开方

 

square返回指定表达式的平方。

select square(4)--平方

 

rand返回从0到1之间的随机float值.

select rand()--随机数

 

SQL 常用语句的简单语法

Select

SELECT "栏位" FROM "表格名"

Distinct

SELECT DISTINCT "栏位"

FROM "表格名"

Where

SELECT "栏位"

FROM "表格名"

WHERE "condition"

And/Or

SELECT "栏位"

FROM "表格名"

WHERE "简单条件"

{[AND|OR] "简单条件"}+

In

SELECT "栏位"

FROM "表格名"

WHERE "栏位" IN ('值1', '值2', ...)

Between

SELECT "栏位"

FROM "表格名"

WHERE "栏位" BETWEEN '值1' AND '值2'

Like

SELECT "栏位"

FROM "表格名"

WHERE "栏位" LIKE {模式}

Order By

SELECT "栏位"

FROM "表格名"

[WHERE "条件"]

ORDER BY "栏位" [ASC, DESC]

Count

SELECT COUNT("栏位")

FROM "表格名"

SQL 常用语句简单语法_smalpg

Group By

SELECT "栏位1", SUM("栏位2")

FROM "表格名"

GROUP BY "栏位1"

Having

SELECT "栏位1", SUM("栏位2")

FROM "表格名"

GROUP BY "栏位1"

HAVING (栏位)

Create Table

CREATE TABLE "表格名"

("栏位 1" "栏位 1 资料种类",

"栏位 2" "栏位 2 资料种类",

... )

Drop Table

DROP TABLE "表格名"

Truncate Table

TRUNCATE TABLE "表格名"

Insert Into

INSERT INTO "表格名" ("栏位1", "栏位2", ...)

VALUES ("值1", "值2", ...)

Update

UPDATE "表格名"

SET "栏位1" = [新值]

WHERE {条件}

Delete From

DELETE FROM "表格名"

WHERE {条件}

常用的查询方法

-1)  查询所有同学的姓名及其出生年份。

  select 姓名,datepart(year,getdate())-年龄 as 出生年份 from 学生表

 

--2) 假设系别用英文字符串表示，查询全体学生的姓名、年龄和所在系，并要求用小写字母表示所有系名。

select 姓名,年龄,lower(系别) from 学生表

 

--3) 查询与“刘翔”同学同在一个系学习的同学，并按年龄的升序排序。

/*

  所查内容：同学的

  查询寻条件：“刘翔”同学的系

       select 系别 from 学生表 where 姓名='刘翔'

  题目的条件:“刘翔”

    

*/

select * from 学生表 where 系别 in

(

       select 系别 from 学生表 where 姓名='刘翔'

)

and 姓名 <> '刘翔'

order by 年龄 asc

 

--4) 用外连接查询每个学生及选修课程的情况。

/*

   学生,选修课程  学生表,学生选课表

*/

select s.学号,s.姓名,sc.课程号 from 学生选课表 sc right outer join 学生表 s on s.学号=sc.学号

 

select s.学号,s.姓名,sc.课程号 from 学生表 s

left join 学生选课表 sc on s.学号=sc.学号

--left join 课程表 c on c.课程号=sc.课程号

 

--5) 查询选修了‘3’号课程的学生学号以及授课老师的详细情况。

/*

   学号 学生选课表 ,授课老师的详细情况 教师表,

     所授课程号  课程号

*/

select sc.学号,t.* from 学生选课表 sc

 join 教师表 t on sc.课程号=t.所授课程号

   where sc.课程号='c3'

 

--6) 用多表查询每个学生的学号、姓名、选修课程名及成绩。

/*

  学号、姓名  s,  选修课程名 c,  成绩  sc

*/

select s.学号,姓名,课程名,成绩 from 学生表 s

  join 学生选课表 sc on s.学号=sc.学号

   join 课程表 c on c.课程号=sc.课程号

 

--7) 插入学号为“S108080”的同学的记录。

insert into 学生表 values('S108080',,,,)

--8) 删除所有选修了‘1’号课程的同学的记录。

   --选修了‘1’号课程的同学

drop trigger sc_tri

 

create trigger sc_tri on 学生选课表

for

delete

as

       delete from 学生表 where 学号 in

       (

           select 学号 from 学生选课表 where 课程号='c1'

       )

go

       delete from 学生选课表 where 课程号='c1'

delete from 表名

where 条件

delete from test2 where tname like '%2%'

 

 

--9) 查询所有选修了‘5’号课程的学生姓名。

       --选修了‘5’号课程的学生

          --select * from 学生选课表 where 课程号='c5'

select 姓名 from 学生表 where 学号 in

(

       select 学号 from 学生选课表 where 课程号='c5'

)

 

select 姓名 from 学生表 where exists

(

       select * from 学生选课表 where 学生表.学号=学号 and 课程号='c5'

)

 

select 姓名 from 学生表 join 学生选课表 on 学生表.学号=学生选课表.学号 where 课程号='c5'

 

--10)查询没有选修‘c2’号课程的学生信息。

select * from 学生表 where 学号 not in

(

  select 学号 from 学生选课表 where 课程号='c2'

)

 

 

常见的运算符

=	<>或!=  不等于	<	<=	>	>=
Any in	<max	<=max	>min	>=min
All	not in（）	<min	<=min	>max	>=max

了解更多请查看SQL语言参考资料.chm