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

默认

> 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