默认
> 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
