头歌实践平台--数据库原理

默认

> sqlcmd -S localhost -U sa -P '<123123Aa!@>'
> create database MyDb
> use MyDb

外键约束

> create table t_class(id int,name varchar(22),primary key(id))
> create table t_student(id int primary key,name varchar(22),classId int,constraint fk_stu_class1 foreign key(classId) references t_class(id))

添加常用约束

> create table t_user(id int primary key identity(1,1) not null,username varchar(32)  not null unique,sex int default 0)

数据的插入

-- ********** create database ********** --
-- ********** Begin ********** --
create database school
 
 
 
-- ********** End ********** --
go
 
use school
go
 
-- ********** create table ********** --
-- ********** Begin ********** --
 
create table teacher(ID int not null,Name varchar(20) not null,sex char(2) not null,Phone varchar(20) null)
 
 
 
-- ********** End ********** --
go
 
SET NOCOUNT ON
 
 
-- ********** insert ********** --
-- ********** Begin ********** --
insert into teacher values (1,"Lucy",'F',null)
 
 
-- ********** End ********** --
go

数据的删除

-- ********** create database ********** --
-- ********** Begin ********** --
 
create database website
-- ********** End ********** --
go
 
use website
go
 
-- ********** create table ********** --
-- ********** Begin ********** --
 
 create table shopping(
     ID int identity(1,1) not null,
     Name varchar(20) not null,
     address varchar(30) not null
 )   
    
-- ********** End ********** --
go
 
SET NOCOUNT ON
 
insert into shopping (Name, address) values ('eBay', 'www.ebay.com')
go
 
SET NOCOUNT ON
 
-- ********** insert ********** --
-- ********** Begin ********** --
 
insert into shopping (Name, address) values ('amazon', 'www.amazon.com')
-- ********** End ********** --
go
 
SET NOCOUNT ON
 
 
-- ********** delete ********** --
-- ********** Begin ********** --
delete from shopping where ID = 1
 
-- ********** End ********** --
go

数据的更改

-- ********** create database ********** --
-- ********** Begin ********** --
create database Books
-- ********** End ********** --
go
 
use Books
go
 
-- ********** create table ********** --
-- ********** Begin ********** --
create table prices(
    ID int identity(1,1) not null,
    Name varchar(20) not null,
    price varchar(30) not null
)
 
-- ********** End ********** --
go
 
SET NOCOUNT ON
 
-- ********** insert ********** --
-- ********** Begin ********** --
insert into prices (Name,price) values ('Harry Potter','$128')
 
-- ********** End ********** --
go
 
SET NOCOUNT ON
 
insert into prices (Name, price) values ('Walden', '$5')
go
 
SET NOCOUNT ON
 
-- ********** update ********** --
-- ********** Begin ********** --
 
update prices
set price = '$6'
where Name ='Walden'
 
-- ********** End ********** --
go

AVG() 函数的使用

USE Mall
GO
 
SET NOCOUNT ON
 
------ return two columns that the price bigger than average price ------
-- ********** Begin ********** --
select prod_name,prod_price
from Products
where prod_price> (
    select avg(prod_price) from Products
)
 
-- ********** End ********** --
 
GO

COUNT() 函数的使用

USE Mall
GO
 
SET NOCOUNT ON
 
------ return the number of product which price bigger than 10 -----
-- ********** Begin ********** --
 
select count(prod_price) from Products where prod_price > 10
 
-- ********** End ********** --
 
GO

MAX() 函数和 MIN() 函数的使用

USE Mall
GO
 
SET NOCOUNT ON
 
------ return the price of the least expensive item ------
-- ********** Begin ********** --
 
select prod_name,prod_price from Products where prod_price = (
    select min(prod_price) from Products
)
 
-- ********** End ********** --
 
GO

SUM() 函数的使用

USE Mall
GO
 
SET NOCOUNT ON
 
------ return the amount of all products ------
-- ********** Begin ********** --
select sum(prod_price * quantity) as amount
from Products
 
 
-- ********** End ********** --
 
GO

带 WHERE 子句的多表查询

USE Mall
GO
 
SET NOCOUNT ON
 
--********** Begin **********--
select * from Products p,Vendors v where p.vend_id = v.vend_id
 
--********** End **********--
 
GO

内连接查询

USE Mall
GO
 
SET NOCOUNT ON
 
--********** Begin **********--
 
select p.*,v.vend_name,v.vend_phone
from Products p inner join Vendors v
on p.vend_id = v.vend_id
 
--********** End **********--
 
GO