SQL学习日程笔记

SQL Server Management Studio 2017

use xs_dat 
go

create database student2
on--定义数据文件
(
name=student2_data,
filename='e:\db\student2_data.mdf',
size=5mb,
maxsize=30mb,
filegrowth=10mb
)
log on--定义事务日志文件
(
name=student2_log,
filename='e:\db\student2_log.ldf',
size=2mb,
maxsize=10mb,
filegrowth=10%
)
create database a2
logon
(name=a2,
filename='e:\db\a2.mdf')

create database b  --新建一个名叫b的数据库
on
(
name=b_data,--设置名字为b
filename='e:\db\b_data.mdf'--存放的文件位置
),
(
name=b1_data,--设置新建的第二个名字为b1
filename='e:\db\b1_data.ndf'--存放的位置
)
log on--新建一个日志文件
(name=b2_ldf,--设置名字为b2
filename='e:\db\b2_log.ldf'--文件位置
)



create database F
on
(
name=f1_data,
filename='e:\db\f1_data.mdf',
size=5mb,
maxsize=50mb,
filegrowth=5mb
)
,
(
name=f2_data,
filename='e:\db\f2_data.ndf',
size=5mb,
maxsize=30mb,
filegrowth=20%
)
log on
(
name=f3_log
filename='e:\db\f3_log.ldf'
)


drop database a--删除名称为a的数据库（delete仅用于删除小型数据）



--创建数据表（操作方式）



use student
go--(切换可用数据库）
create table student2
(
sno char(10) not null,
sname char(10) not null,
ssex char(2),--不写“not null”就是可以为空
sbirthday datetime,
sscore numeric(18,0),
classno char(8) not null
)

use LIRS
go
create table class
(
classno char(8) not null,
classname char(16) not null,
pno char(4)not null
)

use LIRS
go
create table teaching
(
tno char(4),
cno char(7)
)

use LIRS
go
create table choice
(
sno char(10) not null,
cno char(7) not null,
grade real
)
-----------------------------------------------------------------------------------------

--向数据表添加数据
insert into class--向class这个表添加数据（use LIRS go注意这个，需要先定义切换的数据库）
(classno,classname,pno)
values
('18203','18级财务3班','0001'),
('18204','18级财务4班','0001')
----------------------------------------------------------------------------------------
--删除表格class

delete(仅用于删除小型数据)
drop table class(删除class这个table(表))（一般用drop删除）（注意切换数据库）

----------------------------------------------------------------------------------------

create database A    --（这是一个例子，创建一个名称为A的数据库，并向里面添加数据）
use A
go  --这两行的意思是切换到“A”这个数据库
create table 销售记录表  --新建一个“销售记录表(table)”并设定每一列的表头和数据要求
(
no char(12) not null,
name char(100) not null,
type char(50) not null,
price real,
seller char(100) not null,--注意每行后都要加逗号
buyer char(100) not null,
time datetime  not null
)
insert into 销售记录表--向“销售记录表”这个表中添加数据
(no,name,type,price,seller,buyer,time)--  一一对应下面的关系
values
('001','白菜','蔬菜','88','楚','18390120306','2019/09/08'),--注意这个逗号
('002','香蕉','水果','99','今','18390120399','2019/06/08')


-- ----------------------------------------------------------
--数据完整性，即想方法使数据符合规则,不存在重复的行

--DEFUAT默认
-- ---------------------------------------------------------

--以下为修改一个列为主键的代码

use student
go--使用（student）这个数据库
alter table student2--选择“student2”这个表格
add constraint pk_student2--向“student2”添加主键
primary key (sno)--将“sno”这一列为"主键"--.如果将“primary key（主键）”改为“unique（唯一约束）”则为设定‘唯一约束’
--（此为行完整性）
--列完整性为前设置的（not null）

----------------------------------------------------------

--练习
use student
go
alter table professional
add constraint pk_professional
primary key (pno)

---------------------------------------------------------------

--组合主键

alter table professional
add constraint pk_professional
primary key (pname,pno)



---------------------------------------------------------------
给teacher表的tsex字段设置默认值为女
use student
go
alter table student2
add constraint df_ssex
default '女' for ssex 

---------------------------------------------------------------
--检查约束-为表中能接受的值进行约束(取值范围)
--设置教师的性别取值只能为男或女
use student
go
alter table student2--选择student2这个表格
add constraint ck_stu_ssex--向“student2”中的“ssex”添加条件
check(ssex='男' or ssex='女')--设置ssex的条件为只能选择‘男’或‘女’
--
---------------------------------------------------------------
--外键约束--foreign key
--如何判断A,B表能不能建立外键关系？
--1.是否有相同的列
--2.该列在其中一个表是单独作为主键
--设置该列为另一个表的外键
--判断student表和class表能否建立外键关系?可实现
--------------------------------------------------------------
alter table choice--选择choice这个表格，如果没有，新建一个
add constraint fk_stu_choice
foreign key(sno) references student(sno)--设置约束，sno作为sno的外键


--练习：判断choice表和course表能否建立外键关系。如果能，建立
--2:判断course表和teaching表能否建立外键关系，代码
--3：判断teaching表和teacher表能否建立外键关系，代码
--4：判断class表和professional表能否建立外键关系，操作
--5：判断professional表和 department表能否建立外键关系，代码

----------------------------------------------------------------
create database A
--在数据库中创建表格A1，A1各行要求如下：
on
(
name=A_data,
filename='d:\db\A_data.mdf',
size=5mb,
maxsize=30mb,
filegrowth=10mb
)
log on--定义事务日志文件
(
name=A_log,
filename='d:\db\A_log.ldf',
size=2mb,
maxsize=10mb,
filegrowth=10%
)
use A
go
create table A1
(
no int primary key,
name char(20) not null,
type char(10) check(type='日用品' or type='食品') default '食品',
price real check(price>='5'))


--
use A
go
create table A2
(
no2 int primary key,
x1 char(2) unique,
no int references A1(no)
)

----------------------------------------------

--在数据库A中创建表A3
no int 


--------------------------------------
use A
go
create table A3
(
no int references A1(no),
no2 int references A2(no2),
primary key(no,no2)--这个表的主键由no,no2共同构成
)
-----------------------------------------
select GETDATE()




---------------------------------------------------------
use A
go
create database sales
on
(
name=sales_data,
filename='d:\db\sales_data.mdf',
size=10mb,
maxsize=500mb,
filegrowth=10%
)

log on
(
name=sales_log,
filename='d:\db\sales_log.ldf',
size=5mb,
maxsize=100mb,
filegrowth=2mb
)
---------------------------------------------



drop database sales3--删除“sales3”这个数据库





----------------------------------------------

use sales 
go 
----------------------------
create table salers 
(
saleid char(3) primary key,
salename varchar(8) not null,
sex char(2) check(sex='男' or sex='女') default '男',
birthday datetime,
hiredate datetime,
salary money,
department char(30),
address char(60),
telephone char(13)
)
----------------------------
create table product
(
productid int primary key,
productname varchar(100),
price Decimal(18,2),
stocknumber int,
sellnumber int
)
----------------------------
create table customer
(
customerid int primary key,
companyname varchar(100),
contactname char(20),
phone char(15),
address varchar(100),
email varchar(50)
)
----------------------------
create table orders
(
orderid int primary key,
productid int references product (productid),--与product表的productid外键关联
customerid int references customer (customerid),--与customer表的customerid外键关联
saleid char(3)references salers (saleid),
ordernumber int not null,
orderdate datetime default datatime
)
----------------------------


--以下是老版本设置内外键关系，适合建立完表之后设置


alter table orders
add constraint productid_fk foreign key (productid) references product (productid)--与product表的productid外键关联
alter table orders
add constraint customerid_fk foreign key (customerid) references customer (customerid)--与customer表的customerid外键关联
----------------------------
use sales
go

drop table customer--如果出现“无法删除对象 'customer'，因为该对象正由一个 FOREIGN KEY 约束引用。”请参考以下解决。
-------------------------------------------------------------------------------------------------
select fk.name,fk.object_id,OBJECT_NAME(fk.parent_object_id) as referenceTableName
from sys.foreign_keys as fk
join sys.objects as o on fk.referenced_object_id=o.object_id
where o.name='customer'--此四行代码用于查询单引号内的表格的外键位置
------------------
ALTER TABLE dbo.orders  DROP CONSTRAINT customerid_fk--用于删除存在的外键，以解决不能删除表的问题
-------------------------------------------------------------------------------------------------
------------------------
--在A中创建表A3要求如下
--no int 参照A1表的no列，是主键
--no int 与A2表的no2关联，是主键
use a
go
create table A3
(
productid int references product (no),
no2 int references A2 (no2),
primary key (no,no2)
)
------------------------------------------------------------------------------------------------
--insert语句
create database students
use students
go
create table course
(
cno char(7),
cname char(30),
credits real
)
---------------------------------------------------------------------------------
--insert语句向表添加数据
-------------------------------------------------------------------------
insert into course
(cno,cname,credits)
values
('0301001','毛泽东思想概论','3')
-------------------------------------------------------------------------------
--查看是否添加成功
select * from course
----------------------------------------------------------