SQL数据库基础知识--------用sql语句创建数据库

1 sql语句创建数据库日志

/*
create database People
on
(
    name='people',
    filename='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\People_data.mdf',
    size=5,
    filegrowth=10%,
    maxsize=unlimited
)
log on
(
    name='people_log',
    filename='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\People_log.ldf',
    size=2,
    filegrowth=1,
    maxsize=10
)

use People

2.  sql创建数据库表和基本添加删除修改查询

CREATE DATABASE School

USE School

CREATE TABLE Professional
(
    pro_Id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    pro_Name NVARCHAR(50) NOT NULL
)

CREATE TABLE Student
(
    stu_Id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    stu_Name NVARCHAR(10) NOT NULL,
    stu_Age INT NOT NULL CHECK (stu_Age>=18 and stu_Age<=30),
    stu_Sex NVARCHAR(1) NOT NULL CHECK (stu_Sex='男' OR stu_Sex='女') DEFAULT '女',
    pro_Id INT NOT NULL ,
    FOREIGN KEY (pro_Id) REFERENCES Professional(pro_Id)
)

CREATE TABLE Subject
(
    sub_Id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    sub_Name NVARCHAR(10) NOT NULL,
)

CREATE TABLE SelectSub
(
    stu_Id INT NOT NULL,
    sub_Id INT NOT NULL,
    score INT NOT NULL,
    FOREIGN KEY (stu_Id) REFERENCES Student(stu_Id),
    FOREIGN KEY (sub_Id) REFERENCES Subject(sub_Id)
)

INSERT INTO Professional VALUES('计算机')
INSERT INTO Professional VALUES('工商管理')
INSERT INTO Professional VALUES('国际贸易')

INSERT INTO Student VALUES('杨过',20,'男',1)
INSERT INTO Student VALUES('小龙女',19,'女',1)
INSERT INTO Student VALUES('郭靖',26,'男',2)
INSERT INTO Student VALUES('黄蓉',24,'女',2)
INSERT INTO Student VALUES('张无忌',20,'男',3)
INSERT INTO Student VALUES('赵敏',20,'女',3)


INSERT INTO Subject VALUES('高等数学')
INSERT INTO Subject VALUES('*理论')
INSERT INTO Subject VALUES('大学英语')

INSERT INTO SelectSub VALUES(1,1,98)
INSERT INTO SelectSub VALUES(1,2,96)
INSERT INTO SelectSub VALUES(2,2,88)
INSERT INTO SelectSub VALUES(2,3,87)
INSERT INTO SelectSub VALUES(3,3,63)
INSERT INTO SelectSub VALUES(3,1,59)
INSERT INTO SelectSub VALUES(4,2,97)
INSERT INTO SelectSub VALUES(4,3,93)
INSERT INTO SelectSub VALUES(5,1,79)
INSERT INTO SelectSub VALUES(5,2,73)

INSERT INTO SelectSub VALUES(6,3,95)
INSERT INTO SelectSub VALUES(6,1,93)

SELECT * FROM Professional
SELECT * FROM Student
SELECT * FROM Subject
SELECT * FROM SelectSub

--将课程“大学英语改为“高等数学”
UPDATE subject SET sub_Name='高等数学' WHERE sub_Name='大学英语'
--把赵敏的年龄改为18
UPDATE student set stu_Age='18' where stu_Name='赵敏'
--查询出年龄在18到20之间的学生姓名
SELECT stu_Name,stu_Age FROM Student where stu_Age>=18 and stu_Age<=20
SELECT stu_Name,stu_Age FROM Student where stu_Age between 18 and 20
--查询出年龄在18到20之间以外的学生姓名
SELECT stu_Name,stu_Age FROM Student where stu_Age not between 18 and 20
SELECT stu_Name,stu_Age FROM Student where stu_Age<18 or stu_Age>20
--查询出姓为“郭”的学生信息
SELECT * FROM student WHERE stu_Name like '郭%'
--查询出计算机专业的学生信息
SELECT * FROM Student where pro_Id=
(SELECT pro_Id FROM Professional WHERE pro_Name='计算机')
--查询张无忌他所在的专业
SELECT pro_Name FROM Professional WHERE pro_Id=
(SELECT pro_Id FROM Student WHERE stu_Name='张无忌')
--查询出小龙女的年龄和所在专业
SELECT stu_Name,stu_Age,pro_Name FROM Student inner join 
Professional on Student.pro_Id=Professional.pro_Id WHERE stu_Name='小龙女'
--三表联查
select * from 
Professional inner join Student on Professional.pro_Id=Student.pro_Id inner join Selectsub on Student.stu_Id=Selectsub.stu_Id

3. sql创建约束

use School
create table Pro
(
    pro_Id int not null primary key identity(1,1),
    pro_Name nvarchar(50) not null
)
--主键不能为空,且不能重复
--唯一键不能重复,有且只能有一个空值

create table Student
(
    stu_Id int not null primary key identity(1,1),
    stu_Name nvarchar(10),
    stu_Age int not null check (stu_Age>=18 and stu_Age<=30),
    stu_Sex nvarchar(1) not null default '女',
    pro_Id int not null,
    foreign key (pro_Id) references Pro(pro_Id)
)
--外健只能连接另一张表当中的主键或唯一键,且不能为空,外键与其相连的主键类型要一致

create table Subject
(
    sub_Id int not null primary key identity(1,1),
    sub_Name nvarchar(10) not null
)

create table SelectSub
(
    stu_Id int not null,
    sub_Id int not null,
    score int not null check (score>=0 and score<=100),
    foreign key (stu_Id) references Student(stu_Id),
    foreign key (sub_Id) references Subject(sub_Id)
)

insert into Pro values('计算机')
insert into Pro values('工商管理')
insert into Pro values('金融')
insert into Student values('张三',18,'男',1)
insert into Student values('李四',25,'男',2)
insert into Student values('王五',19,'女',3)
insert into Subject values('c\c++')
insert into Subject values('sql server')
insert into Subject values('java')
insert into SelectSub values(2,1,95)
insert into SelectSub values(2,3,80)
insert into SelectSub values(1,1,88)
insert into SelectSub values(1,2,78)
insert into SelectSub values(3,2,60)
insert into SelectSub values(3,3,58)

select * from Pro
select * from Student
select * from Subject
select * from SelectSub