T-SQL - 记录01_基础sql语句操作
1.创建数据库(不设置参数)
create database MyDatabaseOne;
--2.删除数据库
drop database MyDatabaseOne;
--3.创建数据库时设置一些参数选项
create database MydatabaseTwo
on primary
(
--配置主数据文件的选项
name=‘MyDatabaseTwo‘,--主数据文件的逻辑名称
filename=‘C:\DB_Test\MyDatabaseTwo.mdf‘,--主数据文件的实际保存路径
size=5MB,
maxsize=150MB,
filegrowth=20%
)
log on
(
--配置日志文件的选项
name=‘MyDatabaseTwo_log‘,--日志文件的逻辑名称
filename=‘C:\DB_Test\MyDatabaseTwo_log.ldf‘,--日志文件的实质保存路径
size=5MB,--日志文件的初始大小
filegrowth=5MB
)
--此处可能会报错,是一个C盘权限的问题
--如果忘记命令,还可以通过鼠标操作生成脚本的方式查看命令
--4.在数据库中创建一张表
--在执行之前先要切换到要创建的数据库到当前
use School
create table [Departments]
(
ID int identity(1,1) primary key,
Name nvarchar(50) not null
)
--5.通过代码删除数据库和表
use School
drop database [MyDatabaseTwo]
drop table [Departments]
--6.通过代码创建员工表
use [School];
create table Employees
(
ID int identity(1,1) primary key,
IDCard varchar(18) not null,
Name nvarchar(50) null,
Gender bit not null,
JoinDate datetime,
Age int,
Address nvarchar(300),
PhoneNumber varchar(100),
DeptID int not null,
Email varchar(100)
)
--------------------------------------------
--7.创建学生表
create table Student
(
id int identity(1,1) primary key,
name nvarchar(50) not null,
gender nchar(1),
address nvarchar(500),
age int,
birthday datetime,
card_id varchar(18),
class_id int
)
--创建班级表
create table Class
(
class_id int identity(1,1) primary key,
class_name nvarchar(50)
)
--创建成绩表
create table Score
(
score_id int identity(1,1) primary key,
id int not null,--引用学生表中的学生id
English float,
Math float,
Chinese float
)
--创建老师表
create table Teacher
(
teacher_id int identity(1,1) primary key,
teacher_name nvarchar(50) not null,
gender bit,
salary money,
age int,
birthday datetime
)
--8.向表中插入数据
/*
create table Class
(
class_id int identity(1,1) primary key,
class_name nvarchar(50)
)
*/
--①自动编号列,默认自动增长,不能也不需要插入值
insert into [Class](class_name) values(‘.NET黑马一期‘)
select * from [Class]
--向学生表中插入一条数据
/*
create table Student
(
id int identity(1,1) primary key,
name nvarchar(50) not null,
gender nchar(1),
address nvarchar(500),
age int,
birthday datetime,
card_id varchar(18),
class_id int
)
*/
insert into [Student](name,gender,address,age,birthday,card_id,class_id)
values(‘刘德华‘,‘男‘,‘北京市海淀区‘,25,‘1970-10-10‘,‘123652199010021012‘,1)
--如果除自动编号列以外所有列都要插入时也可以省略列名
insert into [Student]
values(‘郭富城‘,‘男‘,‘北京市天安门‘,25,‘1988-10-10‘,‘123652569010021012‘,1)
--向部分列插入数据
insert into [Student](name,gender,age)
values(‘宋祖英‘,‘女‘,25)
--向自动编号列插入值
--a.需要启动某个表的"自动编号列"手动插入值的功能
--b.再次插入数据时,自动编号只会按照当前最大的编号增加
set identity_insert [Student] on --打开插入自动编号功能
insert into [Student](id,name)
values(10,‘唐三藏‘)
set identity_insert [Student] off --使用完后关闭插入自动编号列功能
--c.在插入字符串时如果包含中文一般在字符串引号前加上一个大写N,是为了防止"双字节字符乱码"(中文乱码)
-- 在之前添加数据时,没有加N,也没有出现乱码,这跟当前的数据库排序规则有关(当前为简体中文排序规则)
insert into [Student](name)values(N‘猪八戒‘)
select * from [Student]
--小技巧:打开和关闭查询结果窗口:Ctrl + R
--9.数据更新(数据修改)
--修改[Student]表中gender等于‘女‘的行的数据,年龄减一,姓名后加‘<女>‘
update [Student] set age=age-1,name=name+‘<女>‘ where gender=‘女‘
select * from [Student]
--where中可以使用的其他逻辑运算符:
--or、and、not、<、>、>=、<=、<>(或!=)等
--10.删除数据
--删除表中所有数据
delete from [Student]
--删除表中指定数据(删除一部分数据)
delete from [Student] where gender=‘女‘
--删除表中的全部数据,有两种方式
--a.delete from [表名]
--b.truncate table [表名] (推荐使用这中方法)
truncate table [Student]
--truncate的特点:
--a.truncate语句不能跟where条件(无法根据条件删除,只能全部删除)
--b.truncate删除可以使自动编号恢复到初始值
--c.使用truncate删除表中的所有数据要比delete效率高很多
--d.truncate删除数据,不会触发dalete触发器
--e.truncate删除数据当这个表与其他表有主外键关联时,无法删除
select * from [Student]
--11.练习和整理
--创建数据库
create database [LiangshanHero]
--删除数据库
drop database [LiangshanHero]
--创建表
use [LiangshanHero]
go
create table [Hero]
(
id int identity(1,1) primary key,
name nvarchar(50) not null,
nick_name nvarchar(50),
star nvarchar(10),
age int,
hometown nvarchar(50),
sex nchar(1),
sal int
)
select * from [Hero]
--为表添加数据
insert into [Hero] values(‘宋江‘,‘及时雨‘,‘天魁星‘,28,‘山东‘,‘男‘,6000)
insert into [Hero] values(‘卢俊义‘,‘玉麒麟‘,‘天罡星‘,35,‘河北‘,‘男‘,7500)
insert into [Hero] values(‘吴用‘,‘智多星‘,‘天机星‘,31,‘黑龙江‘,‘男‘,1000)
insert into [Hero] values(‘公孙胜‘,‘入云龙‘,‘天闲星‘,32,‘重庆‘,‘男‘,5000)
insert into [Hero] values(‘关胜‘,‘大刀‘,‘天勇星‘,36,‘江苏‘,‘男‘,3500)
insert into [Hero] values(‘林冲‘,‘豹子头‘,‘天雄星‘,34,‘浙江‘,‘男‘,4000)
insert into [Hero] values(‘秦明‘,‘霹雳火‘,‘天猛星‘,35,‘上海‘,‘男‘,5500)
insert into [Hero] values(‘呼延灼‘,‘双鞭‘,‘天威星‘,33,‘湖南‘,‘男‘,2800)
insert into [Hero] values(‘花荣‘,‘小李广‘,‘天英星‘,24,‘湖北‘,‘男‘,3400)
insert into [Hero] values(‘柴进‘,‘小旋风‘,‘天贵星‘,26,‘广西‘,‘男‘,4800)
insert into [Hero] values(‘鲁智深‘,‘花和尚‘,‘天孤星‘,38,‘河南‘,‘男‘,5000)
insert into [Hero] values(‘武松‘,‘行者‘,‘天伤星‘,28,‘河南‘,‘男‘,2600)
insert into [Hero] values(‘史进‘,‘九纹龙‘,‘天微星‘,36,‘河南‘,‘男‘,8000)
insert into [Hero] values(‘扈三娘‘,‘一丈青‘,‘地慧星‘,28,‘云南‘,‘女‘,7000)
insert into [Hero] values(‘顾大嫂‘,‘母大虫‘,‘地阴星‘,37,‘贵州‘,‘女‘,6000)
insert into [Hero] values(‘孙二娘‘,‘母夜叉‘,‘地壮星‘,32,‘四川‘,‘女‘,5400)
insert into [Hero] values(‘石秀‘,null,null,null,null,null,null)
insert into [Hero] values(‘燕青‘,null,null,null,null,null,null)
insert into [Hero] values(‘阮小七‘,null,null,null,null,null,null)
insert into [Hero] values(‘李逵‘,null,null,null,null,null,null)
--查询所有数据
select * from [Hero]
--带条件查询
select name from [Hero] where sex=‘男‘
select count(*) as ‘工资大于5000人数‘ from hero where sal>=5000
--更新数据
update [Hero] set sal=sal*1.1 where sal<3000
--删除指定数据
delete from [Hero] where sex=‘女‘
--清空表中数据
truncate table [Hero]
--删除整张表(包括表结构和所有数据)
drop table [Hero]
--12.约束(保证数据完整性,约束是建立在表上的)
--约束大致分类
--a.非空约束
--b.主键约束(PK)primary key constraint唯一且不为空
--c.唯一约束(UQ)unique constraint唯一,允许为空,但只能出现一次
--d.默认约束(DF)default constraint默认值
--e.检查约束(CK)check constraint范围以及格式限制
--f.外键约束(FK)foreign key constraint表关系
--新建一张表(无约束的表)
use [School]
go
create table [Employees]
(
id int identity(1,1),
name nvarchar(50),
gender char(2),
age int,
email varchar(100),
home_address varchar(500)
)
create table [Department]
(
dep_id int identity(1,1),
dep_name varchar(50)
)
--删除表
drop table [Employees]
drop table [Department]
--查询数据
select * from [Employees]
select * from [Department]
--13.t-sql语句实现约束(手动增加约束)
--新建一张表(无约束的表)
use [School]
go
create table [Employees]
(
id int identity(1,1),
name nvarchar(50),
gender char(2),
age int,
email varchar(100),
home_address varchar(500)
)
create table [Department]
(
dep_id int identity(1,1),
dep_name varchar(50)
)
--增加约束-------------------------------------
--手动删除一列
alter table [Employees] drop column home_address
--手动增加一列
alter table [Employees] add home_address1 nvarchar(1000)
--手动修改一下某列的数据类型
alter table [Employees] alter column email varchar(200)
--为某列增加一个主键约束(修改表结构,增加约束,约束类型:主键约束)
alter table [Employees] add constraint PK_Employees_id primary key(id)
--为某列增加一个非空约束(以修改列的方式完成,增加一个not null关键字就好)
alter table [Employees] alter column name nvarchar(50) not null
--为某列增加一个唯一约束(唯一约束要求值不能重复,允许只能有一个空值)
alter table [Employees] add constraint UQ_Employees_name unique(name)
--为性别增加一个默认约束,默认值为‘男‘
alter table [Employees] add constraint DF_Employees_gender default(‘男‘) for gender
--为性别增加一个检查约束,要求性别只能是:‘男‘ or ‘女‘
alter table Employees add constraint CK_Employees_gender check(gender=‘男‘ or gender=‘女‘)
--为年龄增加一个检查约束:年龄必须在0-120岁之间,含岁与岁.
alter table [Employees] add constraint CK_Employees_age check(age>=0 and age<=120)
--为部门表设置主键,主键列:dep_id(即为部门表某列增加主键约束)
alter table [Department] add constraint PK_Employees_dep_id primary key(dep_id)
--为员工表增加列,部门ID:dep_id
alter table [Employees] add dep_id int not null
--增加某张表外键约束(命令后增加:on delete cascade 可实现级联删除)
alter table [Employees] add constraint FK_Employees_Department foreign key(dep_id) references Department(dep_id)
--删除约束--------------------------------
alter table [Employees] drop constraint FK_Employees_Department,CK_Employees_age,CK_Employees_gender,UQ_Employees_name,DF_Employees_gender
--通过一条代码来增加多个约束
alter table [Employees] add
constraint FK_Employees_Department foreign key(dep_id) references Department(dep_id),
constraint CK_Employees_gender check(gender=‘男‘ or gender=‘女‘),
constraint CK_Employees_age check(age>=0 and age<=120),
constraint UQ_Employees_name unique(name)
--创建表的同时就为表增加约束
use [School]
go