数据库笔记 1

2.4创建数据库

create database LtsDatabase

on /*对数据文件的描述*/

(

name=LtsDatabase_data,

filename='d:\temp\LtsDatabase_data.mdf',

size=6,/*数据库文件初始化大小*/

maxsize=12,/*数据库最大空间多大*/

filegrowth=10%/*数据库文件超过现有的最小初始化大小后的增长百分之多少*/

)

log on/*对数据事物日志的描述*/

(

name=LtsDatabase_log,

filename='d:\temp\LtsDatabase_data.ldf',

size=1,

maxsize=8,

filegrowth=10%

)

 

2.7创建表

useLtsDatabase /*打开数据库*/

create table Teacher

(

教师编号 int,

教师名称 varchar(50),

教师职称 varchar(50),

教师工资 money

)

 

2.8 查询数据

select 教师编号,教师职称from dbo.Teacher

 

2.9编辑前多少行 (编辑前200行，以SQL语句来显示)

SELECT   TOP (200) 教师编号, 教师名称, 教师职称, 教师工资

FROM      教师表

 

2.10

alter database LtsDatabase/*修改数据库文件,这是必须的，要取得这个数据库的权限*/

add file /*增加文件*/

(

name = LtsDabase2,

filename='d:\temp\LtsDatabase.mdf',

size=6

)

 

/*增加事物日志文件

add logfile

(

name=LtsDatabase_log2,

filename = 'd:\temp\LtsDatabase_log2.ldf',

filegrowth=10%

)

*/

 

2.11删除数据库

alter database LtsDatabase

删除事物数据文件

remove file LtsDabase2

删除事物日志文件

/*

removefile LtsDatabase_log2

*/

 

 

2.12修改数据库表

将Teacher表的表名，改成教师表

exec sp_rename"Teacher","教师表"

 

 

2.13 修改数据库表--增加列字段

打开数据库--修改表--增加列字段

useLtsDatabase

alter table 教师表

addEmail varchar(60)default'ZhangKuixun@yeah.net'

 

 

2.14修改数据库表--修改列字段

useLtsDatabase

alter table 教师表

alter column 教师薪金int  /*把之前的类型改成int*/

 

删除表的字段

useLtsDatabase

alter table 教师表

drop column 家庭住址  /*删除家庭住址这一列*/

 

 

2.15往数据库插入一条数据库记录

useLtsDatabase

insert into 教师表(教师编号,教师名称,教师职称,教师工资)values(1,'张老师','ios',2000)

 

 

2.17指定条件，更新数据库的某项记录

use LtsDatabase

update 教师表 set 教师编号=160,教师名称='孙老师',教师职称='高级教师',教师工资=2000where 教师编号=120/*设置教师编号=160,...,教师工资=2000,更新条件：教师编号=120的这一列*/

 

 

2.22

useLtsDatabase

create table 网站职员表

(

职员编号 int,

姓名     varchar(50),

年龄     int,

毕业院校 varchar(50),

家庭住址 varchar(50),

工资     money

)

 

 

2.24

useLtsDatabase

insert into dbo.网站职员表(职员编号,姓名,年龄,毕业院校,家庭住址,工资)values(1,'小伟',22,'黑河学院','黑龙江省北安市',2800)

insert into dbo.网站职员表(职员编号,姓名,年龄,毕业院校,家庭住址,工资)values(2,'小张',23,'黑河学院','黑龙江省北安市',2800)

insert into dbo.网站职员表(职员编号,姓名,年龄,毕业院校,家庭住址,工资)values(3,'小宽',24,'黑河学院','黑龙江省北安市',2800)

 

 

2.25逻辑或运算符

select *from 网站职员表where 工资< 2700or 工资 = 2700/*查询网站职员表中的工资大于2700或工资等于2700元的数据*/

 

 

2.26逻辑与运算符

select *from 网站职员表where 年龄=25and 毕业院校='黑河学院'/*查询网站职员表中的年龄=25并且毕业院校是黑河学院的的数据*/

 

 

2.27not运算符

select *from 网站职员表wherenot 工资>2700/*工资不大于2700元的数据   where工资!>2700*/

 

 

2.28模糊查询

运算符的优先级：

     1.括号

     2.not(非)、正好、负号

     3.乘、除

     4.加、减

     5.比较运算符

     6.and

     7.or

select *from 网站职员表where(毕业院校='黑大'or 毕业院校='浙大')and年龄=25/*

先查询括号里面的。

查询网站职员表中毕业院校=黑大或者毕业院校=浙大并且查询年龄=25*/

 

 

2.32 in()/*在什么范围内*/    not in()/*不在什么范围之内*/

select *from 网站职员表where 毕业院校in('黑大','浙大','哈工程学院')/*查询网站职员表中的毕业院校是'黑大','浙大','哈工程学院'的数据*/

 

is null /*是空值*/  is not null/*不是空值*/

select *from 网站职员表where 家庭住址isnull

 

 

2.33 between  在谁和谁之间

select *from 网站职员表where 工资between 2000and 2600/*网站职员表中工资在2000到2600之间的数据*/

 

not between  不在谁和谁之间

select *from 网站职员表where 工资notbetween 2000 and 2600/*网站职员表中工资不在2000到2600之间的数据*/

 

between和 in 比较：in要把所有的值都列举出来，between只指定一个范围值

 

 

2.34 distinct 消除已查询出的重复字段记录

select distinct 毕业院校from 网站职员表/*查询网站职员表中的毕业院校数据，去除重复的院校*/

 

 

2.35 in(...)  或者  = (...)  都是查询谁和谁相同的数据

select *from 网站职员表where 工资=(select 工资from 网站职员表 where 姓名='小江')/*查询网站职员表中的和小江相同工资的员工*/

 

 

2.36 identity (1,1)==创建标识列，包括标识种子和标识自增量为1  primarykey==创建主键

create table 网站经营项目

(

项目编号 int identity (1,1)  primarykey,

项目名称 varchar (50),

负责人 varchar (50),

合作单位 varchar (50)

)

insert into 网站经营项目(项目名称,负责人,合作单位)values('远程教学','小赵','独立开发')

insert into 网站经营项目(项目名称,负责人,合作单位)values('远程教学','小江','独立开发')

insert into 网站经营项目(项目名称,负责人,合作单位)values('软件开发','小伟','独立开发')

 

 

2.37 多表联合查询

select *from 网站职员表where 姓名in(selectdistinct 负责人 from 网站经营项目)/*查询网站职员表中的符合条件的数据，条件是姓名在这个集合中==>>查询出网站经营项目表中的负责人这个字段的值，并且去除重复的字段（负责人）*/

 

 

2.38 多表联合查询-排序asc==从低到高排序 desc==从高到低排序

select *from 网站职员表where 姓名in(selectdistinct 负责人 from 网站经营项目)

order by 工资desc

/*查询网站职员表中的符合条件的数据，条件是姓名在这个集合中==>>查询出网站经营项目表中的负责人这个字段的值，并且去除重复的字段（负责人）

按照工资从高到低排序*/

 

 

2.39多级排序

select *from 网站职员表orderby 年龄 asc,工资asc/*查询网站职员表中的内容，年龄从低到高排序，工资从低到高排序*/

 

统计函数COUNT==统计查询出来的数据有多少行

select COUNT(*) from 网站职员表/*查询网站职员表中符合条件的记录有多少行*/

 

select COUNT(*) as 记录行数from 网站职员表/*查询网站职员表中记录有多少行，查询出来的数据列名用“记录行数”显示*/

 

 

2.40 COUNT(家庭住址) 如果家庭住址有6行，有一行为NULL，则不计算为NULL的那一行记录，查询出来的总行数为5

select COUNT(家庭住址)as 住址行数from 网站职员表/*查询网站职员表中家庭住址的行数为多少行，查询出来的数据列名用“住址行数”显示。如果家庭住址列有一行为NULL，则不计算为NULL的那一行*/

 

SUM求总和

select SUM(工资) as 工资总和 from 网站职员表 /*查询网站职员表中工资总和，查询出来的数据列名用“工资总和”显示*/

 

 

2.41 AVG求品均值

select AVG(工资) as 平均工资 from 网站职员表 /*查询网站职员表中平均工资，查询出来的数据列明用“平均工资”显示*/

 

max求最大值

select max(工资) as 最高工资 from 网站职员表 /*查询网站职员表中最高工资，查询出来的数据列明用“最高工资”显示*/

 

min求最小值

select min(工资) as 最低工资 from 网站职员表 /*查询网站职员表中最低工资，查询出来的数据列明用“最低工资”显示*/

 

 

2.42通过统计值作为查询条件

select *from 网站职员表where 工资>(selectAVG(工资)from 网站职员表)

 /*查询网站职员表中符合条件的记录,条件是工资大于平均工资的记录*/

 

 

2.43

alter database LtsDatabase

add 奖金 money/*增加表字段奖金*/

 

update 网站职员表 set 奖金=500/*更新网站职员表，设置网站职员表的奖金等于500*/

 

select 职员编号,姓名,工资+奖金as 总收入from 网站职员表/*查询网站职员表中的职员编号，姓名，总收入（工资+奖金）。总收入的列名用“总收入”显示*/

 

2.44 模糊查询

select *from 网站职员表where 毕业院校like'%黑%'/*模糊查询出网站职员表中的毕业院校的名字中包括“黑”字的记录*/

 

select *from 网站职员表where 毕业院校like'黑%'/*模糊查询出网站职员表中的毕业院校的名字中以“黑”字开头的记录*/

 

select *from 网站职员表where 毕业院校like'%黑'/*模糊查询出网站职员表中的毕业院校的名字中以“黑”字结尾的记录*/

 

 

2.46汇总分组（汇总查询）  order by “年龄”  compute by“年龄”要求查询的列名前后对应

select *from 网站职员表orderby 年龄 compute max(工资),min(工资),sum(工资),avg(工资)by 年龄/*查询网站职员表重中符合条件的记录，根据年龄排序，通过年龄进行分表汇总*/

 

 

2.48 group by分组查询

select 毕业院校,max(工资)as 最高工资,min(工资)as 最低工资,avg(工资)as 平均工资,sum(工资)as 工资总额,sum(工资+奖金)as 收入总额from 网站职员表groupby 毕业院校

/*对同一个毕业院校做如上的统计*/

 

having只显示部分记录

select 毕业院校,max(工资)as 最高工资,min(工资)as 最低工资,avg(工资)as 平均工资,sum(工资)as 工资总额,sum(工资+奖金)as 收入总额from 网站职员表groupby 毕业院校 having sum(工资)> 5000

/*对同一个毕业院校做如上的统计,只显示工资大于5000元的分组*/

 

 

2.49

select *from 网站职员表where 毕业院校in(select 毕业院校from 网站职员表groupby 毕业院校 having sum(工资)> 5000)

/*查询网站职员表中，在某一个毕业院校毕业的所有学员总收入超过一个指定值，查看在这样毕业院校毕业的学员的经济情况。

查询网站职员表符合条件的记录，毕业院校的值在这个范围之内（只查询网站职员表中每个毕业院校总工资>5000元的记录,并通过毕业院校分组）*/

 

 

2.50  谓词: exists==存在 not exists==不存在   （in和not in 也是谓词）

select *from 网站职员表whereexists(select*from 网站职员表where 毕业院校='哈工大')

/*网站职员表中只要有一个哈工大的学生存在，就把网站职员表中所有的记录都查询出来。

查询网站职员表中符合条件的记录,条件是:由exists限制，后面括号中是一个集合，只要这个集合中包含毕业院校=‘哈工大’，这个条件就为true，为true就把网站职员表中的记录全部查询出来*/

 

select *from 网站职员表wherenot exists(select*from 网站职员表where 毕业院校='哈工大')

/*查询网站职员表中符合条件的记录,条件是:由not exists限制，后面括号中是一个集合，只要这个集合中包含毕业院校=‘哈工大’，这个条件就为true，由于前面是not exists，所以前面的记录就不被查询出来*/

 

exists和in比较

in:前面的字段必须在后面的集合中所包含，前面的记录才会白查询出来

exists:只要后面的集合中为true,就是后面集合中只要有值，前面的记录都会被查询出来

 

 

 

2.51量词  any前面的值大于后面任意一个值  all前面的值大于后面所有的值

select *from 网站职员表where 工资>any(select 工资from 网站职员表where 毕业院校='北京大学')

/*先把后面括号查询出来的值看成一个集合。然后在理解，查询网站职员表中符合条件的记录,条件是:前面查询的网站职员表中的记录中工资大于后面括号查询的集合中任意一个值。*/

 

select *from 网站职员表where 工资>all(select 工资from 网站职员表where 毕业院校='北京大学')

/*先把后面括号查询出来的值看成一个集合。然后在理解，查询网站职员表中符合条件的记录,条件是:前面查询的网站职员表中记录中的工资字段的值大于后面括号查询的集合中每一个值。*/

 

 

2.52  percent:前百分之几

select top 2percent*from 网站职员表/*查询前百分之2*/

 

 

2.53 查询的结果如何保存

select *into 第二网站职员表from 网站职员表where 工资>2800/*将网址职员表中查询出来的工资大于2800元的记录，存到一个新的表“第二网址职员表”中*/

 

 

2.54

update 第二网站职员表 set 姓名='网站职员表之'+姓名/*将表中所有的指定字段记录做更改*/

 

 

2.55 union：集合并运算    interset：集合交运算

select *from 网站职员表unionselect *from 第二网站职员表/*查询网站职员表和第二网站职员表中的记录，合并为一个表显示*/

 

select *from 网站职员表 intersetselect*from 第二网站职员表 /*查询网站职员表和第二网站职员表中的记录，分成两个表显示*/

 

 

2.56连接查询

select *from 网站职员表,网站经营项目

 

 

2.57 按条件连接查询

select *from 网站职员表,网站经营项目where 网站职员表.姓名=网站经营项目.负责人

/*条件：查询网站职员表和网站经营项目表中，手里有项目的员工，以及他个人的信息

 

如果表中没有重复字段可以直接用姓名=负责人，如果有有相同字段名就用网站职员表.姓名=网站经营项目.负责人*/

 

 

2.58 超连接查询  内连接：

select *from 网站职员表innerjoin 网站经营项目on 网站职员表.姓名=网站经营项目.负责人

/*连接网站职员表和网站经营项目表，查询网站职员表.姓名=网站经营项目.负责人的记录，组合成新的表显示*/

 

 

2.59 左连接

select *from 网站职员表leftjoin 网站经营项目 on 网站职员表.姓名=网站经营项目.负责人

/*连接网站职员表和网站经营项目表，查询出的结果组合成新的表显示:先显示左边网站职员表中的所有记录，然后再显示“网站职员表.姓名=网站经营项目.负责人”的记录；如果没有“网站职员表.姓名”对应的“网站经营项目.负责人”，则只显示左边“网站职员表.姓名”的数据，右边不显示“网站经营项目.负责人”数据，右边只显示NULL*/

 

右连接

select *from 网站职员表rightjoin 网站经营项目on 网站职员表.姓名=网站经营项目.负责人

/*连接网站职员表和网站经营项目表，查询出的结果组合成新的表显示:先显示右边网“站经营项目表”中的所有记录，然后再显示左边“网站职员表.姓名=网站经营项目.负责人”的记录；如果没有“网站职员表.姓名”对应的“网站经营项目.负责人”，则只显示右边“网站经营项目.负责人”的数据，左边不显示“网站职员表.姓名”数据*/

 

全连接

select *from 网站职员表fulljoin 网站经营项目 on 网站职员表.姓名=网站经营项目.负责人

/*连接网站职员表和网站经营项目表，查询出的结果组合成新的表显示:显示出所有的网站职员表和网站经营项目表的记录*/