数据库SQL语句代码 常见问题

use student

go

create table x_student

(

 s_name char (10),

s_number char(10)not null,

sex char(2) default'男',

birthday datetime,

polity char(4)

)

go

*********************************************

 use student

 go

sp_renamedb 'student','student-back'

 

sp_renamedb'student-back','student0'                                                                                     

 

sp_renamedb'student0','student'

*********************************************

create database class

on primary

(

 name=class_data,

 filename='E:/data/class.mdf',

size=3,

maxsize=20,

filegrowth=15%

)

log on

(

  name=class_log,

  filename='E:/data/class.ldf',

size=2,

maxsize=60,

filegrowth=1

)

*******************************************

drop database class

*******************************************

create database class0

on primary

(

 name=class0_data,

 filename='E:/data0/class.mdf',

size=3,

maxsize=20,

filegrowth=15%

)

log on

(

  name=class0_log,

  filename='E:/data0/class0.ldf',

size=2,

maxsize=60,

filegrowth=1

)

*************************************

drop database class0

*************************************

use class

go

create table student

(

  number char(10) not null,

 name   char(8),

sex char(2),

)

go

**************************************

sp_renamedb 'class','08512-class'

sp_renamedb '08512-class','my-class'

sp_renamedb 'my-class','class'

*************************************

use class

go

select *from  student(来自表)

*************************************

use student

go

select *from x_student(来自表)

go

****************************************

(必须声明使用哪个库建表)

create table student0

(

  number char(10) not null,

 name   char(8),

sex char(2),

)

go

****************************************

drop table class.dbo.student0

drop table student.dbo.student0

****************************************

use student

go

create table score

(

s_number char(10) primary key,

c_number char (10) ,

score real

)

*****************************************

use student

go

select *from course１

select *from score

select *from x_student

*****************************************

select *from student (只能选择表，而不能选择数据库)

=========================================

use student

go

select top 3  s_number,s_name,sex,birthday from x_student

=========================================

select distinct sex from X_student

=========================================

select 学号=s_number,s_name as 姓名,birthday 出生日期,sex 性别from X_student

=========================================

select 学号=s_number,s_name as 姓名,birthday 出生日期,sex 性别,year(getdate())-year(birthday)

as 年龄from X_student

=========================================

select count(*) from X_student

=========================================

select count(*) from X_student where sex='女'

****************************************

select X_student .s_number,s_name,c_number,score from

X_student,score where X_student.s_number=score.s_number

=========================================

select *from X_student where polity='党员' and sex='女'

go

=========================================

select *from score where score between 80 and 90

go

=========================================

select *from X_student where  polity in('团员','群众')

*****************************************

 insert X_student

values('0851204','高劲松','男','87.5.6','团员')

insert X_student

values('0851201','周克胜','男','89.11.25','团员')

insert X_student

values('0851202','高萍','女','87.5.6','团员')

insert X_student

values('0851203','周康','男','89.11.25','团员')

insert X_student

values('0851205','赵辉','男','87.5.6','群众')

insert X_student

values('0851206','周敏','女','89.11.25','党员')

select *from X_student

truncate table X_student

select *from X_student

delete from X_student

*****************************************

select sex,count(sex)人数from X_student group by sex

select * from X_student

*****************************************

 use student

 go

select x_student.s_number,s_name,sex,c_number,score from x_student,score

where x_student.s_number=score.s_number

=========================================

use student

go

select x_student.s_number,s_name,c_number,score

  from x_student inner join score

  on  ｘ_student.s_number=score.s_number

****************************************

use student

go

select *from score

select *from score where score>80

select *from score where score=80

select *from score where score between 80 and 90

select *from score where c_number='011'

order by score asc

========================================

select *from score where c_number='011'

order by score desc

****************************************

use student

go

create table student3

(

 number char(10)primary key,

 name  char(8),

 sex char(2),

 birthday datetime,

 polity char(4)

)

select *from student3

insert into student3

values('0851201','李丽','女','1990.5.6','党员')

insert into student3

values('0851202','莉莉','女','1990.5.6','团员')

insert into student3

values('0851203','丽丽','女','1990.5.6','群众')

===============================================

select *from student3

===============================================

insert student3

values('0851204','高丽','女','1987.5.7','党员')

================================================

select *from student3

===============================================

delete from student3

where name='莉莉'

================================================

select *from student3

***********************************************

use student

go

select*from X_student

union

select *from student3

==============================================

truncate table student3(清除表中所有记录)

select *from student3

select *from X_student

drop table student.dbo.student3

============================================

use student

go

select*from X_student where s_number=any

(select s_number from score

group by s_number

having count (c_number)>=1

)

*********************************************

insert X_student

values('0851223','敏敏','女','1988.3.6','党员')

insert X_student

values('0851230','周杰','男','1989.12.25','团员')

select *from X_student

***********************************************

update X_student

set polity='群众'

where s_name='0851223'

select *from X_student

=============================================

delete from X_student

where s_name='周杰'

select *from X_student

***********************************************

insert into X_student

values('0851207','李丽','女','1989.2.3','党员')

select *from X_student

==============================================

delete from X_student

where s_name='李丽'

select *from X_student

============================================

use student

go

create table student6(

 number char(10),

 name char(8),

 sex char(2)default'男',

phonenum int

)

==========================================

create table student5

(

 number char(10),

name char(8),

sex char(2),

phonenum int,

constraint check_sex check(sex in ('男','女'))

)

=========================================

create table student4

(

  number char(10) primary key,

name char(8),

sex char(2),

birthday datetime ,

polity char(4),

constraint unike_name unique(name)

)

===========================================

use student

go

create table student2

(

  number char(10) not null,

 name char(8),

 sex char(2),

 phonenum int

)

 ******************************************

select *from X_student where polity='团员'

select *from X_student where s_name like '周%'

select*from X_student where polity='团员'and sex='女'

select*from X_student where polity='党员'and sex='女'

=======================================

create table student7

(

number char(10) not null,

 name char(8),

 sex char(2),

birthday datetime,

 phonenum int

)

select*from X_student

select*from student7

drop table student.dbo.student7

 =======================================

select top 50 percent s_number,s_name,sex,birthday,polity into student7

from X_student

select*from student7

drop table student.dbo.student7

======================================

select*from X_student

======================================

select top 20 percent s_number,s_name,sex,birthday,polity into student8

from X_student

select*from student8

***************************************

use student

go

sp_help student

====================================

use student

go

sp_help

************************************

use student

go

create table student6(

 number char(10),

 name char(8),

 sex char(2)default'男',

phonenum int

)

===================================

create table student5

(

 number char(10),

name char(8),

sex char(2),

phonenum int,

constraint check_sex check(sex in ('男','女'))

)

====================================

create table student4

(

  number char(10) primary key,

name char(8),

sex char(2),

birthday datetime ,

polity char(4),

constraint unike_name unique(name)

)

=====================================

use student

go

create table student3

(

 number char(10)primary key,

 name  char(8),

 sex char(2),

 birthday datetime,

 polity char(4)

)

====================================

use student

go

create table student2

(

  number char(10) not null,

 name char(8),

 sex char(2),

 phonenum int

)

=====================================

select top 3 *from X_student

*************************************

select *from score

select *from score where score<60

select *from score where score<70

select *from ｘ＿student where polity='党员'

select *from ｘ＿student where polity='党员'and sex='男'

select *from x_student where s_name like'张%'

======================================

select c_number as 学号,count(c_number)选修课程数from score

group by c_number

having count(c_number)>=2

**************************************************

use student

go

SELECT     s_number, s_name, sex

FROM         dbo.student7

WHERE     (s_name LIKE '周%')

ORDER BY sex

==================================================

SELECT     s_number, s_name, sex,polity,birthday

FROM         dbo.X_student

WHERE     (s_name LIKE '周%')

ORDER BY sex

================================================

SELECT  s_number, s_name, sex,polity,birthday

FROM         dbo.X_student

WHERE     s_name LIKE '刘%'

ORDER BY sex

**********************************************

(在SCORE表中插入数据)

select *from score

truncate table score

insert into score

values('0851201','10010218',82)

insert into score

values('0851202','10010218',75)

insert into score

values('0851203','10010218',93)

insert into score

values('0851204','10010218',59)

insert into score

values('0851205','10010218',82)

insert into score

values('0851206','10010218',77)

insert into score

values('0851207','10010218',52)

insert into score

values('0851208','10010218',68)

insert into score

values('0851209','10010218',60)

insert into score

values('0851201','30020215',82)

insert into score

values('0851202','30020215',68)

insert into score

values('0851203','30020215',75)

insert into score

values('0851104','30020215',58)

select *from score

***********************************************

use student

go

create table course1

(

 C_number char (10),

c_name char(30),

hours int,

credit real

)

go

select *from score

select *from course1

insert   course1

values('10011218','高等数学',140,4)

insert into course1

values('40050405','基于ACCESS数据库设计',64,3)

insert   course1

values('20050421','专业英语',54,2.5)

insert into course1

values('40051060','关系型数据库原理',48,2)

insert into course1

values('30020215','单片机原理',64,3)

insert into course1

values('10011218','高等数学',140,4)

　因为没有主键约束，所以可以反复插入，

　并且表中呈现多个相同的记录

select *from course1

truncate table course1

和

delete from course1

此两条删除语句等价，都删除表中记录，而不删除表，删除表用：

drop table student.dbo.course1

创建表：

use student

go

设置主键约束保证了确保了纪录的唯一性

create table course

(

 c_number char (10) primary key,

 c_name char(30),

 hours int,

 credit real

)

go

select *from course

drop table student.dbo.course

insert   course

values('10011218','高等数学',140,4)

insert into course

values('40050405','基于ACCESS数据库设计',64,3)

insert   course

values('20050421','专业英语',54,2.5)

insert into course

values('40051060','关系型数据库原理',48,2)

insert into course

values('30020215','单片机原理',64,3)

********************************************

insert course

values('10021202','我的数据库',45,3)

delete from course

where C_number='10021202'

不加条件限制，会删除表中所有记录

select *from course

============================================

select *from X_student

************************************* ????

use student

go

创建视图：

create view view_score

as

select X_student.s_number,X_student.s_name,

course.c_name,score.score

from

X_student,course,score

where X_student.s_number=score.s_number

and course.c_number=score.c_number

and left(X_student.s_number,5)='08512'

====================================

select *from view_score

**************************************

use student

go

create view view_score_good1

with encryption

as

select x_student.s_number,

       x_student.s_name,

       course.c_name,

       score.score

from

x_student,course,score

where x_student.s_number=score.s_number

and  course.c_number=score.c_number

and score between 70 and 80

================================

select *from view_score_good1

采用encryption对视图进行加密

use student

go

create view view_score_not_well

with encryption

as

select ｘ_student.s_number,

       x_student.s_name,

       course.c_name,

       score.score

from

x_student,course,score

where x_student.s_number=score.s_number

and  course.c_number=score.c_number

and score <70

==============================

select *from view_score_not_well

==============================

在视图中加一个条件，只显示高等数学

不及格的学生信息

use student

go

alter view view_score_not_well

with encryption

as

select x_student.s_number,

x_student.s_name,

course.c_name,score.score

from

x_student,course,score

where x_student.s_number=score.s_number

and course.c_number=score.c_number

and course.c_name='高等数学'

and score<60

select *from view_score_not_well

重命名视图：

sp_rename   view_score_not_well,view_score_not_well_高等数学

sp_rename   view_score_not_well_高等数学,view_score_not_well

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

use student

go

sp_helptext view_score_not_well

因为视图加密，所以不能查看视图的定义信息

******************************************

use student

go

create view view_score_sunandaverage1

as

select substring(x_student.s_number,1,len(X_student.s_number)-2)as 班级,

course.c_name as 课程名称,

sum (score) as 总分,

avg (score)as 平均分

from

x_student,course,score

where x_student.s_number=score.s_number

and course.c_number=score.c_number

group by

substring(x_student.s_number,1,len(x_student.s_number)-2),

course.c_name

select*from view_score_sunandaverage1

******************************************

use student

go

create view view_score_sumandaverage0

as

select substring(x_student.s_number,1,len(x_student.s_number)-2)as 班级,

course.c_name as 课程名称,

sum (score) as 总分,

avg (score)as 平均分

from

x_student,course,score

where x_student.s_number=score.s_number

and course.c_number=score.c_number

group by

substring(x_student.s_number,1,len(x_student.s_number)-2),

course.c_name

=========================================

select*from view_score_sumandaverage0

删除视图：

drop view view_score_sumandaverage0

使用sp_helptext显示视图在系统表中的定义：

sp_helptext view_score_sumandaverage0

*******************************************

use student

go

使用系统存储过程sp_help，显示视图的特征信息

sp_help view_score

use student

go

使用sp_helptext,显示视图在系统表中的定义

注意：如果在创建视图时，对视图的定义进行了加密，则不能查看视图的定义信息

sp_helptext view_score

××××××××××××××××××××××××××××××××××××

use student

go

显示视图所依赖的对象

sp_depends view_score

sp_depends view_score_good

sp_depends view_score_sunandaverage

sp_depends view_score_not_well

************************************

use student

go

create view view_x_student_08512

(s_number,s_name,sex,birthday,polity)

as

select s_number,s_name,sex,birthday,polity

from X_student

where s_number like '08512%'

=====================

select *from  view_x_student_08512

=====================

insert into view_X_student_08512

values('0851233','何贝','女','1988.6.9','团员')

insert  view_X_student_08512

values('0851232','何楠','男','1988.9.1','党员')

insert into view_x_student_08512

values('0851233','何贝','男','1988.6.9','团员')

insert   view_x_student_08512

values('0851232','何楠','女','1988.9.1','党员')

====================

select *from  view_x_student_08512

select *from X_student

在视图中插入的数据，最终实质插入到了表中

select *from  view_x_student_08512

select s_number,s_name,sex,birthday,polity

from  view_x_student_08512

以上三条语句得到同样结果

＝＝＝＝＝＝＝＝＝＝

select*from student

＝＝＝＝＝＝＝＝＝＝＝＝

用汉字显示字段名：

select s_number as学号,姓名=s_name ,sex 性别,birthday 出生日期

from Ｘ_student

where s_number like'08512%'

＝＝＝＝＝＝＝＝＝＝＝＝

use student

go

create view view_ｘ_student_2008512_男

as

select *from Ｘ_student

where s_number like'08512%'

and sex ='男'

go

显示表中所有记录：

 select *from Ｘ_student

====================================

删除视图：

 drop view  view_X_student_2008512_男

=====================================

select *from view_Ｘ_student_2008512_男

where sex='男'

select *from view_Ｘ_student_08512

where sex='男'

此两条语句等价，都显示男生信息

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

select *from view_Ｘ_student_08512

此条语句显示表中所有记录

===============================

insert into view_x_student_2008512_男

values('0851212','王丽','女','1988.5.9','党员')

insert into view_x_student_2008512_男

values('0851212','王楠','男','1988.5.9','团员')