一、使用查询分析器-管理数据库和数据表
#1. 创建数据库格式
create database databasename
on primary
(
name = "databasename_data" ,
filename = "filename\databasename_data.mdf" ,
size = 8 MB,
maxsize = 100 MB,
filegrowth = 10 %
)
log on
(
name = "databasename_log" ,
filename = "finename\database_log.ldf" ,
size = 5 MB,
filegrowth = 10 %
)
#2. 创建数据库
create database student -- 创建数据库
on primary -- 指定数据库文件位置
( name = "student_data" , -- 指定数据库的名称
filename = "d:\Dy231016\student_data.mdf" , -- 指定数据库库文件存放的位置
size = 8 MB, -- 指定数据库库文件的初始大小
maxsize = 100 MB, -- 指定数据库库文件的最大值
filegrowth = 10 % ) -- 指定数据库库文件的增长速度
log on -- 定义日志文件信息
( name = "student_log.ldf" , -- 指定日志文件名称
filename = "D:\Dy231016\student_log.ldf" , -- 指定日志文件存放位置
size = 1 MB, -- 指定日志文件大小
filegrowth = 10 % ) -- 指定日志文件增长率
-- 单行注释： --
-- 多行注释： /* 注释内容 */
/* 如果在添加数据库的时候，
不知道是否已经有数据库存在，
该如何操作？ */
#3. 删除数据库
drop database databasename
#4. 判断数据库是否已经存在
if exists ( select * from sys .databases where name = 'student1' ) -- 判断系统中是否
存在该数据库
drop database student1 -- 如果存在，使用 drop 语句删除该数
据库
create database student1
drop database student1 -- 删除数据库
#5. 创建表格式
/* 创建表的格式
create table 表名 (
字段 1 数据类型 属性 约束，
字段 2 数据类型 属性 约束，
字段 3 数据类型 属性 约束
)
*/
#6. 创建表
use student -- 切换数据库
create table stuinfo ( -- 创建表
stu_id varchar ( 20 ) NOT NULL , -- 指定表的学号字段
stu_name varchar ( 50 ) NOT NULL , -- 指定表的姓名字段
stu_sex char ( 2 ) NOT NULL , -- 指定表的性别字段
stu_age int, -- 指定表的年龄字段
stu_add varchar ( 100 ) -- 指定表的地址字段
)
-- 创建一个班级表
create table class (
id int identity ( 1 , 1 ) , -- 设置编号自增长
cla_id varchar ( 20 ) not null ,
cla_name varchar ( 10 ) not null
)
-- 如何决断表是否存在，如果存在将其删除。
if exists ( select * from sys .objects where name = 'class' and type = 'U' ) -- 查
询表是否存
drop table class -- 如
果存在就将其删除
/*
表约束：
主键约束： primary key
外键约束： foreign key
默认值： default
非空： not null
检查约束： check
唯一： unique
标识：
自增长： identity
*/
-- 添加约束的格式
/*
alter table tablename
add constraint 约束名称 约束类型
*/
-- 删除约束格式
/*
alter table table name
drop constraint 约束名称
*/
-- 添加主键约束：
alter table stuinfo
add constraint pk_id primary key ( stu_id )
-- 添加默认约束
alter table stuinfo
add constraint def_set default ( ' 男 ' ) for stu_sex
-- 添加检查约束
alter table stuinfo
add constraint ch_age check ( stu_age >= 0 and stu_age <= 100 )
-- 添加唯一约束
alter table stuinfo
add constraint un_addr unique ( stu_add )
-- 修改非空约束
alter table stuinfo
alter column stu_age int not null
-- 设置 class 表的主键
alter table class
add constraint pk_cla_id primary key ( cla_id )
-- 向已有的表中添加字段
alter table stuinfo
add cla_id varchar ( 20 ) not null
-- 向已有的表中添加字段
alter table stuinfo
add id int not null
-- 给 stuinfo 表中的 cla_id 添加外键
alter table stuinfo
add constraint for_cla_id foreign key ( cla_id ) references class ( cla_id )
#7. 删除约束
/*
删除约束格式：
alter table tablename
drop constraint 约束名称
*/
-- 删除主键约束
alter table stuinfo
drop constraint pk_id
-- 删除默认约束
alter table stuinfo
drop constraint def_set
-- 删除检查约束
alter table stuinfo
drop constraint ch_age
-- 删除唯一约事
alter table stuinfo
drop constraint un_addr
#8 、创建表时直接添加约束
-- 创建班级表
create table class (
id int identity ( 1 , 1 ) ,
cla_id varchar ( 20 ) primary key not null ,
cla_name varchar ( 30 ) not null
)
-- 创建学生信息表
create table stuinfo1 (
id int identity ( 1 , 1 ) not null , -- 设置序号为自增长
stu_id varchar ( 20 ) primary key not null , -- 设置学号
stu_name varchar ( 40 ) not null , -- 姓名
stu_sex char ( 2 ) default ( ' 男 ' ) , -- 设置性别默认值为男
stu_age int check ( stu_age >= 1 and stu_age <= 100 ) , -- 设置年龄范围
stu_tel char ( 11 ) unique not null , -- 设置手机号不能重复
stu_add varchar ( 50 ) , -- 地址
cla_id varchar ( 20 ) references class ( cla_id )
)
#9 、判断字段是否存在
-- 判断 stuinfo 表中是否存 tel 字段，如果有将其删除，如果没有创建。
if exists ( select * from sys .columns where name = 'tel' and
object_id = object_id ( 'stuinfo' ) ) -- 判断字段是否存
alter table stuinfo -- 更新表结构
drop column tel -- 删除字段
else -- 否则
alter table stuinfo -- 更新表字段
add tel varchar ( 11 ) not null -- 添加字段
#10 、删除表
drop table tablename
-- 删除学生表
use Dy231010
drop table stuinfo
二、使用查询分析器管理表
1 、向表中插入单条数据
-- 向表中插入数据
-- 格式
/**
insert [into] < 表名 > [ 字段名称 ] values < 字段的值 > 当给出指定的字段时，字段可以是部分，也
可以全部
insert into < 表名 > values < 字段的值 > 当省略字段项时，所给出的值列表必须与字段的位置、数
量、数据类型保持一直；
insert < 表名 > values < 字段的值 >
*/
-- 案例
insert class values ( '12345' , ' 运维实施 ' )
insert into stuinfo1 ( stu_id,stu_name,stu_sex,stu_add,stu_age,stu_tel )
values ( '10001' , ' 张三 ' , ' 男 ' , ' 郑州金水区 ' , 19 , '12345' )
insert into stuinfo1 values ( '10002' , ' 李四 ' , ' 男 ' , 19 , '19' , ' 郑州金水区 ' , '12345' )
2 、向表中插入多条数据
# 格式一
insert into < 表名 > values ( ''' 值 1' ， ' 值 2' , ' 值 3' , ' 值 4' ) , ( ' 值 1' ， ' 值 2' , ' 值 3' , ' 值 4' )
# 案例：
insert class values ( '1237' , 'abcf' ) , ( '128' , 'ad' )
# 格式二
insert into < 表名 > < 字段 1 ，字段 2 ，字段 3 ，字段 4 ，字段 5.... >
select ' 值 1' ， ' 值 2' , ' 值 3' , ' 值 4' , ' 值 5' , ' 值 6' unique ,
select ' 值 1' ， ' 值 2' , ' 值 3' , ' 值 4' , ' 值 5' , ' 值 6' unique ,
select ' 值 1' ， ' 值 2' , ' 值 3' , ' 值 4' , ' 值 5' , ' 值 6' unique ,
select ' 值 1' ， ' 值 2' , ' 值 3' , ' 值 4' , ' 值 5' , ' 值 6'
# 案例：
insert into class ( cla_id,cla_name )
select '113' , 'aaa' union
select '114' , 'bbb'
3 、查询数据表内容
# 格式
select < 字段名 1 > ， < 字段 2 > ,.. < 字段 n > from < 表名 > [ where 条件表达式 ]
# 案例
# 不使用条件查询
-- 查询整个表信息
select * from stuinfo1
-- 查询部分表 信息
select stu_id,stu_name from stuinfo1
# 使用条件查询
-- 查询学生信息表中名字叫张三的学生信息
select * from stuinfo1 where stu_name = ' 张三 '
-- 查询学生信息表中年龄大于 20 岁的学生信息
select * from stuinfo1 where stu_age > 20
-- 查询学生信息表中性别为男性的学生的姓名，手机号
select stu_name,stu_tel from stuinfo1 where stu_sex = ' 男 '
select stu_name,stu_tel from stuinfo1 where stu_sex <> ' 女 '
-- 查询学生信息表中年龄大于 20 岁并且性别为男的学生信息
select * from stuinfo1 where stu_age > 20 and stu_sex = ' 男 '
-- 查询学生信息表中年龄大于 20 岁或性别为男的学生信息
select * from stuinfo1 where stu_age >= 20 or stu_sex = ' 男 '
-- 模糊查询
-- 查询学生信息表中姓张学生的信息。模糊查询： like 通配符： % 表示 0 个或多个字符 _ ：表示一个字
符；
select * from stuinfo1 where stu_name like ' 张 %'
select * from stuinfo1 where stu_name like ' 李 %'
select * from stuinfo1 where stu_name like ' 李 _'
-- 区间查询
-- 查询年龄在 18 - 22 岁之间的所有学生信息； between and （包含界值）
select * from stuinfo1 where stu_age between 18 and 22
-- in 子查询
-- 查询年龄在一个范围之内的学生信息；（ 1 ， 5 ， 7 ， 18 ， 20 ， 22 ， 30 ， 50 ）
select * from stuinfo1 where stu_age in ( 1 , 5 , 7 , 18 , 20 , 22 , 30 , 50 )
# 子查询
select * from stuinfo1 where stu_age in ( select stu_age from stuinfo1 where
stu_age > 18 and stu_age < 22 )
-- not in 子查询：不在某个范围之内
select * from stuinfo1 where stu_age not in ( 1 , 5 , 7 , 18 , 20 , 22 , 30 , 50 )
-- null : 空值 表示时，使用 is null 表示某个值为空值；
select * from stuinfo1 where cla_id is null
-- is not null 表示某个值不为空值；
select * from stuinfo1 where cla_id is not null
4 、表更新操作
/*
表数据更新：格式
update < 表名 > set 字段名 = 值 [where 条件表达式 ]
*/
-- 将所有学生的年龄加 1 岁
select * from stuinfo1
update stuinfo1 set stu_age = stu_age + 1
select * from stuinfo1
select * from class
-- 将张姓学生的班级修改为 1236
select * from stuinfo1 where stu_name like ' 张 %'
update stuinfo1 set cla_id = '1236' where stu_name like ' 张 %'
5 、表备份操作
/*
给表做备份：
insert into < 新表名 > [ 字段名 ] select 字段名 from < 原表名 > [where 条件表达式；该方式备份
表，新表必须提前创建好
select [ 字段 ] into < 新表名称 > from < 原表名 > 注意：该方式备份，新表不需要提前创建；
*/
select * into stuinfo from stuinfo1
insert into stuinfo1(stu_id,stu_name,stu_sex,stu_age,stu_tel) select
stu_id,stu_name,stu_sex,stu_age,stu_tel from stuinfo
select * from stuinfo
select * from stuinfo1
6 、表删除操作
/*
删除数据： delete
delete from < 表名 > [where 条件表达式 ]
truncate table < 表名 >
delete 删除时，如果有自增长列，再添加数据时，不会从头开始；
truncate 删除时，如果有自增长列，再添加数据时，自增长列会从头开始；
drop 删除表时，表结构和内容全部删除。
*/
delete from stuinfo1
delete from stuinfo1 where stu_age = 20
select * from stuinfo1
truncate table stuinfo1
