当前位置:17727 > 数据库信息 > 数据库Tsql语句创建,SQL基本操作

数据库Tsql语句创建,SQL基本操作

文章作者:数据库信息 上传时间:2019-12-09

系统数据库

----------------------------------------SQL Server建库-建表-建约束创建School数据库--------------------------------------

1.创建数据库
use master
go
if exists(select * from sysdatabases where name='数据库名字')
drop database 数据库名字
go
create database 数据库名字

系统用户:

    1、master:用于记录所有SQL server系统级别的信息,比如说登录的信息,系统设置信息。如果他没了。你的SQL server就挂了。

--创建School数据库之前:首先判断数据库是否存在,若存在则删除后再创建,若不存在则创建--
--exists关键字:括号里边能查询到数据则返回‘true’ 否则返回‘false’
if exists(select * from sysdatabases where name = 'School')
--exists返回‘true’则执行删除数据库操作--
drop database School

create database 数据库名字
on primary
(
name = 'MySchool_data',
filename = 'D:projectMySchool_data',--主数据文件
size = 10mb,
maxsize = 100mb,
filegrowth=15%
)
log on
(
name = 'MySchool_log',
filename = 'D:projectMySchool_log',--日志文件
size = 3mb,
maxsize = 20mb,
filegrowth=1mb
)
2.五大约束
1.—-主键约束(Primay Key constraint) 唯一性,非空性
2.—-唯一约束 (Unique constraint)唯一性,可以空,但只能有一个
3.—-检查约束 (Check constraint) 对该列数据的范围、格式的限制(如:年龄、性别等)
4.—-默认约束 (Default constraint) 该数据的默认值
5.—-外键约束 (Foreign Key constraint) 需要建立两表间的关系并引用主表的列
3.五大约束的语法示例

1.sys system(sys权限最高)

    2、model:模型的意思,就像模板一样。创建数据库都是继承model。如果你在该数据库下创建一张表,以后每创建新数据库,就已经有了model的表。

--exists返回‘false’则表明数据库不存在,直接创建

1.—-添加主键约束(将stuNo作为主键)
alter table stuInfo
add constraint PK_stuNo primary key (stuNo)
2.—-添加唯一约束(身份证号唯一,因为每个人的都不一样)
alter table stuInfo
add constraint UQ_stuID unique(stuID)
3.—-添加默认约束(如果地址不填 默认为“地址不详”)
alter table stuInfo
add constraint DF_stuAddress default (‘地址不详’) for stuAddress
4.—-添加检查约束 (对年龄加以限定 15-40岁之间)
alter table stuInfo
add constraint CK_stuAge check (stuAge between 15 and 40)

2.sysman(操作企业管理器) 密码是安装的时候输入的密码

    3、msdb:和master一样记录信息,不同的是它记录任务调动,事件处理,数据备份及恢复,报警和异常的信息。

create database School
on primary
(
--主数据库文件--
name = 'School', --主数据文件逻辑名
fileName = 'D:projectSchool.mdf', --主数据文件物理逻辑名
size = 5MB, --初始值大小
maxsize = 100MB, --最大大小
filegrowth = 15% --数据文件增长量
)
log on
(
--日志文件--
name = 'School_log',
filename = 'D:projectSchool_log.ldf',
size = 2MB,
filegrowth = 1MB
)
go

alter table stuInfo
add constraint CK_stuSex check (stuSex=’男’ or stuSex=’女′)

3.scott(默认是tiger)

    4、tempdb:临时数据库,它为所有临时表,临时存储过程提供存储空间

----------------------------------------使用T-SQL创建employee数据库------------------------------------
create database employee
on primary
(
--主要数据文件--
name = 'employee1',
filename = 'D:projectemployee1.mdf',
size = 10MB,
filegrowth = 10%
),
(
--次要数据文件--
name = 'employee2',
filename = 'D:projectemployee2.ndf',
size = 20MB,
maxsize = 100MB,
filegrowth = 1MB
)
log on
(
--第一个日志文件--
name = 'employee_log1',
filename = 'D:projectemployee_log1.ldf',
size = 10MB,
filegrowth = 1MB
),
(
--第二个日志文件--
name = 'employee_log2',
filename = 'D:projectemployee_log2.ldf',
size = 10MB,
maxsize = 50MB,
filegrowth = 1MB
)

5.—-添加外键约束 (主表stuInfo和从表stuMarks建立关系,关联字段stuNo)

oracle中用户,权限,角色

注意:以上四个系统数据库,别去删,删了有大大小小的问题。

---------------------------------查询已存在的数据库信息---------------------------
select * from sysdatabases

alter table stuInfo
add constraint FK_stuNo foreign key(stuNo)references stuinfo(stuNo)
4.直接在建表的时候添加约束
create table UserType--用户类别表
(
TypeID int not null primary key identity(1,1),
TypeName varchar(20) not null
)
create table Users--用户表
(
UserID int not null primary key identity(1,1),
UserName varchar(20) not null,
UserPwd int not null,
UserTyID int not null foreign key references UserType(TypeID)--用户类别
)
create table VisitArea--访问区域表
(
VisitID int primary key identity(1,1),
VisitName varchar(20)
)
create table UserTypeA--用户类别与区域表
(
TypeAID int not null primary key identity(1,1),
UserTypeID int not null foreign key references UserType(TypeID),
VisitID int not null foreign key references VisitArea(VisitID)
)
create table UsersLog--用户登录记录表
(
LogID int not null primary key identity(1,1),
LogName varchar(20) not null,
LogUserID int not null foreign key references Users(UserID),
LogTime date not null
)
create table VisitRecord--访问记录表
(
VRID int not null primary key identity(1,1),
VUserID int not null foreign key references Users(UserID),
VVisitID int not null foreign key references VisitArea(VisitID),
VisitTime date not null,
VUserType int not null foreign key references UserType(TypeID)
)
create table UpGrade
(
UGID int primary key not null identity(1,1),
UGUserID int not null foreign key references Users(UserID),
UpDown int not null , --升降标识
UpTypeID int not null check(UpTypeID>1 and UpTypeID<4 ),
DownTypeID int not null check(DownTypeID>1 and DownTypeID<4 ),
UpDownTime date not null,

用户:创建/修改/删除 用户

数据类型:

---------------------------------删除数据库------------------------------------
drop database School

)

use master
go
if exists(select * from sysdatabases where name='MySchool')
drop database MySchool
go
create database MySchool
on
(
name='MySchool_data',
filename='D:projectMySchool_data.mdf',
size=5MB,
filegrowth=15%
)
log on
(
name='MySchool_log',
filename='D:projectMySchool_log.ldf',
size=5mb,
filegrowth=1mb
)
go

use MySchool
go
if exists(select * from sysobjects where name='Student')
drop table Student
go
create table Student
(
StudentNo int not null, --学号
LoginPwd nvarchar(20) not null, --登录密码
StudentName varchar(20) not null, --学生姓名
Sex bit not null, -- 性别
GradeId int not null, --年级
Phone varchar(20) not null, --电话号码
Address nvarchar(100), --地址
BornDate datetime not null, --出生日期
Email nvarchar(50), --邮箱
IDEntityCard varchar(18) --身份证号
)
go
--主键约束 学生标号
alter table Student
add constraint PK_StudentNo primary key(StudentNo)
--唯一约束 身份证号
alter table Student
add constraint UQ_IDEntityCard unique(IDEntityCard)
--默认约束 地址不详
alter table Student
add constraint DF_Address default('地址不详') for Address
--检查约束 出生日期
alter table Student
add constraint CK_BornDate check(BornDate>'1980-01-01')

 

use MySchool
go
if exists(select * from sysobjects where name='Subject')
drop table Subject
go
create table Subject
(
SubjectNo int identity(1,1) not null, --课程编号
SubjectName nvarchar(20) not null, --课程名称
ClassHour int not null, --课时
GradeId int not null --年级
)
go

--主键约束
alter table Subject
add constraint PK_SubjectNo primary Key(SubjectNo)
--检查约束
alter table Subject
add constraint CK_ClassHour check(ClassHour>0)

alter table Subject
add constraint Ck_SubjectName check(SubjectName!=null)
--外键约束

use MySchool
go
if exists(select * from sysobjects where name='Result')
drop table Result
go
create table Result
(
StudentNo int not null, --学号
SubjectNo int not null, --课程编号
StudentResult int not null, --学生成绩
ExamDate datetime not null --考试日期
)
go

 

alter table Result
add constraint PK_fuhe primary Key(StudentNo,SubjectNo,ExamDate)
alter table Result--默认约束 日期
add constraint DF_ExamDate default(getdate()) for ExamDate
alter table Result
add constraint CK_StudentResult check (100>StudentResult )
alter table Result
add constraint CK_StudentResult2 check (StudentResult>0 )
alter table Result
add constraint FK_SubjectNo subject是主表
foreign Key(SubjectNo) references Subject(SubjectNo)
alter table Result
add constraint FK_StudentNo
foreign Key(StudentNo) references Student(StudentNo)

use MySchool
go
if exists(select * from sysobjects where name='Grade')
drop table Grade
go
create table Grade
(
GradeID int identity(1,1) not null, --年级编号
GradeName nvarchar(20) not null, --年级

)
go

alter table Grade
add constraint PK_GradeID primary Key(GradeID)

--外键约束
alter table Student
add constraint FK_GradeId
foreign Key(GradeId) references Grade(GradeId)

alter table Subject
add constraint FK_GradeId2
foreign Key(GradeId) references Grade(GradeId)

--向Grade表插入数据
INSERT INTO Grade VALUES('S1')

--向Subject表插入数据
INSERT INTO Subject VALUES('Winforms',20,1)

--向Student表插入数据
INSERT INTO Student VALUES('10000','GuoJing','郭靖',1,1,02088762106,'天津市河西区','1987-09-08 00:00:00','GuoJing@sohu.com',111111)

--向Result表插入数据
INSERT INTO Result VALUES('10001',2,70.6,'2013-02-15 00:00:00')

select * from Grade
select * from Result
select * from Student
select * from Subject

创建用户:    CREATE USER name

    1、数值型:

---------------------------------创建Student数据库表----------------------------
--1、选择操作的数据库--
use School
go

IDENTIFIED BY password

          整型:int  smallint  bigint  tinyint

--判断表是否存在--
if exists(select * from sysobjects where name = 'Student')
drop table Student

[ACCOUNT LOCK|UNLOCK]

          浮点型:float  real

--2、创建表---
create table Student
(
--具体的列名 数据类型 列的特征(是否为空)--
StudentNo int identity(2,1) not null,
LoginPwd nvarchar(20) not null,
StudentName nvarchar(20) not null,
Sex int not null,
GradeId int not null,
phone nvarchar(50) not null,
BornDate datetime not null,
Address nvarchar(255),
Email nvarchar(50),
IDENTITYcard varchar(18)
)
go

[PASSWORD EXPIRE];(口令立刻过期)

          货币型:money

---查看所有数据库对象(数据库表)---
select * from sysobjects

修改用户:    ALTER USER name

    2、布尔型(bit):

drop table Student

IDENTIFIED BY password

         True

----------------------创建subject课程表-------------------
-----1、判断表是否存在;若存在则删除再创建,若不存在则直接创建--------
if exists(select * from sysobjects where name = 'subject')
drop table subject

[ACCOUNT LOCK|UNLOCK]

         False

use School
go

[PASSWORD EXPIRE];(口令立刻过期)

    3、二进制型

---创建subject课程表--
create table subject
(
SubjectNo int not null identity(1,1),
SubjectName nvarchar(50),
ClassHour int,
GradeID int
)

删除用户:DDROP USER name [CASCADE];

         binary

----------------------------------------创建Result成绩表-------------------
-----1、判断表是否存在;若存在则删除再创建,若不存在则直接创建--------
if exists(select * from sysobjects where name = 'Result')
drop table Result

具体操作:

         varbinary

use School
go

CREATE USER test

         image

---创建Result成绩表--
create table Result
(
StudentNo int not null,
SubjectNo int not null,
ExamDate Datetime not null,
StudentResult int not null
)

IDENTIFIED BY test

    4、文本数据类型

-----------------------------------------创建Grande年级表-------------------
-----1、判断表是否存在;若存在则删除再创建,若不存在则直接创建--------
if exists(select * from sysobjects where name = 'Grade')
drop table Grade

ACCOUNT LOCK

        char    固定长度

use School
go

PASSWORD EXPIRE;

        vachar  可变长度

---创建Grande年级表--
create table Grade
(
GradeId int not null,
GrandeName nvarchar(50)
)

grant create session to test;(用户授权)

        nvarchar

-----------------------------------------T-SQL添加约束-------------------------
--给StudentNo添加主键约束---
alter table Student
add constraint pk_StuNo primary key(StudentNo)

ALTER USER test ACCOUNT UNLOCK;(解锁)

        nchar  

--给身份证添加唯一约束--
alter table Student
add constraint uq_StuIdcard unique(IDENTITYcard)

登录

        text    长文本信息

---给地址address添加默认约束--
alter table Student
add constraint df_stuaddress default('地址不详') for Address

1.使用system用户登录

        ntext   可变长度的长文本

---删除地址address默认约束---
alter table Student
drop constraint df_stuaddress

system/password@server

        datetime 日期和时间

----------出生日期添加检查约束--------
alter table Student
add constraint ck_stuBorndate check(Borndate > '1980-01-01')

2.使用sys用户登录

新建数据库:

---------与Grand(年级表)建立主外键关系--------

connect sys/password as sysdba

    1、数据库文件  :主数据文件和副数据文件

--1、添加Grade主键(操作Grade)---
alter table Grade
add constraint pk_graid primary key(GradeId)

查看登录用户

    2、日志文件:一个或多个

--2、添加Grade外键(操作Student)--
alter table Student
add constraint fk_stuGradeID foreign key(GradeId) references Grade(GradeId)

1.show user命令

    3、create database 名称

 

2.dba_users数据字典(数据字典是数据库提供的表,用于查看数据库的信息)

重命名数据库:exec sp_renamedb new,old

-------------------给subject课程表添加约束-----------------------

desc dba_users; (查看数据字典里包含哪些字段)

删除数据库:drop database 名称1,名称2

----给subjectNo列添加主键约束------
alter table subject
add constraint pk_SubID primary key(SubjectNo)

结果包含如下一些字段:USERNAME    NOTNULL VARCHAR2(30)

新建数据库:

------给课程名称subjectName添加非空约束;-----
-----with nocheck:已经存在数据不通过check约束-------
alter table subject with nocheck
add constraint ck_subName check(SubjectName is not null)

USER_ID      NOTNULL NUMBER

    create database test(数据库文件名)

-----学时必须大于0-----
alter table subject with nocheck
add constraint ck_ClassHour check(ClassHour > 0)

PASSWORD            VARCHAR2(30)

    on

-----与Grade年级表添加主外键约束----
alter table subject with nocheck
add constraint fk_GradeID foreign key(GradeID)
references Grade(GradeID)

...

    (

----------给result成绩表添加约束------------

在数据字典中包含哪些用户?select username from dba_users;

    name=test_data(主数据文件),

-------添加多个约束---------
alter table Result
add
constraint pk_No_subID_date primary key(StudentNo,SubjectNo,ExamDate),
constraint df_examdate default(getdate()) for ExamDate,
constraint ck_StudentResult check(StudentResult between 0 and 100),
constraint fk_StuNo foreign key(StudentNo) references Student(StudentNo),
constraint fk_subNo foreign key(SubjectNo) references Subject(SubjectNo)

启用scott用户

    filename = 'D:ProgramFilesSQLdatatest_data.mdf',

--删除多个约束--
alter table Result
drop constraint pk_No_subID_date,fk_subNo,fk_StuNo,ck_StudentResult,df_examdate

alter user username account unlock (解锁)

    size=6(初始化大小),

--------更改列的数据类型----------
alter table Result
alter column StudentResult int

alter user scott  ccount unlock;(用户已更改0

    maxsize=12,

使用scott用户登录SQL Plus

    filegrowth(增长率)=10%

conn scott/tiger

    )

修改scott密码

    log on

SQL>  conn /as sysdba

    (

SQL>  alter user scott identified by tiger;

    name=test_log(日志文件),

SQL>  conn scott/tiger

    filename='D:ProgramFilesSQLdatatest_log.ldf',

SQL>  select * from tab;

    size=1,

表空间:

    maxsize=8,

理解表空间:数据库与表空间(一个数据库有多个表空间)

    filegrowth=10%

表空间与数据文件

    )

表空间的分类:永久表空间(表,视图,存储过程)

创建表:

临时表空间(数据库操作当中,中间执行的过程,执行结束后会被释放)

  create table 教师表

undo表空间(保存事务所修改的旧值,可以对数据进行回滚即撤销操作)

  (

查看用户的表空间:

  教师编号 int identity(1,1) primary key,

1.dba_tablespaces(系统用户表空间) user_tablespaces(普通用户表空间) 数据字典

  教师姓名 varchar(20) not null,

步骤:conn system/abc...123

  教师年龄 int,

select tablespace_name from dba_tablespaces;

  教师工资 money,

结果为:system    (存放sys系统表空间)

  奖金 money,

sysaux    (作为一个example的辅佐表空间)

  总收入 money,

undotbs1 (存储撤销的表空间)

  合作院校 varchar

temp      (临时表空间)

  )

users

  说明:

exmple

    identity:表示自增长,通常为ID赋值

步骤:desc user_tablespaces;

    primary key:主键

select tablespace_name from user_tablespaces;

    not null:不为空

2.dba_users user_usres数据字典

  注意:每个字段后面的逗号别忘了

conn sys/abc...123 as sysdba

查询表:

desc dba_users;

    select * from 教师表

查看system默认的表空间和临时表空间是什么?

    select 奖金,总收入 from 教师表

select defult_tablespace,temporary_tablespce from dba_users where username='SYSTEM';

删除表:

结果是:SYSTEM  TEMP(学会如何更改默认表空间和临时表空间,方便数据的备份及恢复)

    use test--切换到该表所在的数据库下,如果已经在,就不用切换

设置用户默认或临时表空间:

    drop table 教室表--删除整个表

ALTER USER usernme DEFAULT|TEMPORARY TABLESPACE tablespace_name

删除表,但不删除表结构,只清空数据;

alter user system default tablespace user;

    Truncate table 教师表

创建表空间:

重命名表:

create [temporary] tablespace tablespace_name tempfile|datafile 'xx.dbf' size xx

    exec sp_rename 教师表,教师表1

创建永久表空间:

新增字段:

create tablespace test1_tablespace datafile 'textfile.dbf' size 10m;

    alter table 教师表

创建临时表空间:

    add Email varchar(60) default ''

create temporary tablespace temptest1_tablespace tempfile 'tempfile1.dbf' size 10m;

修改字段:

通过数据字典查看表空间位置:

    alter table 教师表

通过数据字典查看创建的永久表空间:

    alter column 教师薪资 int

desc dba_data_files;(返回的字段有file_name tablespace_name...)

删除字段:

查看表空间所在的文件位置:

    alter table 教师表

select file_name from dba_data_files where tablespce_name='TEST1_TABLESPACE';

    drop column 家庭住址

查看临时表空间:desc dba_temp_files;

新增数据:

查看表空间所在的文件位置:

    insert into 教师表(教师年龄,教师职称,教师薪资,课程)

select file_name from dba_temp_files where tablespce_name='TEMPTEST1_TABLESPACE';

    values(19,'微微',100000,'java')

修改表空间:

查询数据:

1.修改表空间的状态

    select * from 教师表 where 教师薪资<10000

a.设置联机或脱机状态:alter tablespace tablespace_name online|offline;

 

创建完一个表空间之后,默认状态是联机状态;

    select * from 教师表 where 教师薪资 in(5555,6666,9999)

具体操作:

    select * from 教师表 where 姓名  not in(select 班主任 from 课程表)

alter tablespace test1_tablespace offline;

    select 姓名,教师薪资+奖金 as 总收入 from 教师表

desc dba_tablespaces;数据字典查看status

 

select status from dba_tablesaces where tablespace_name='TEST1_TABLESPACE';结果为:status:offline

    select * from 课程表 where 合作学校 like '%马%'

再设置回联机状态:alter tablespace test1_tablespace online;

    select * from 课程表 where 合作学校 like '黑%'

select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';结果为status:online

 

b.设置只读或可读写状态:alter tablespace tablespace_name read only|read write;

     select top 4 * from 课程表

默认情况下是可读写的,联机状态才可以更改读写性

    select top 20 percent * from 教师表

具体操作:

 

alter tablespace test1_tablespace read only;

    select * from 课程表1 intersec select * from 课程表2   集合交

select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';结果为:status:online

 

2.修改数据文件

    select * from 教师表,课程表1 where 教师表.姓名=课程表1.班主任

a.增加数据文件:

    select * from 教师表,课程表1 where 姓名=班主任     去后缀

alter tablespace tablespace_name add datafile 'xx.dbf' size xx;

    排序:

具体操作:向表空间test1里添加数据文件

        select * from 教师表 order by 年龄 --默认升序,倒序desc

alter tablespace test1_tablespace add datafile 'text2_file.dbf' size 10m;

        select * from 教师表 order by 年龄,教师薪资

select file_name from dba_files where tablespace_name='TEST_TABLESPACE';查看新增的文件位置

    去重:

b.删除数据文件:

        select distinct 姓名 from 教师表

alter tablespace tablespace_name drop datafile 'filename.dbf'

    统计求和:

不能删除表空间里面的第一个数据文件,如果要删除的话需要把整个表空间删掉。

        select COUNT(*) as 行数 from 教师表

具体操作:删除上面新增的text2_file.dbf

        select COUNT(姓名) from 教师表

alter tablespace test1_tblespace drop datafile'text2_file.dbf';

    平均:

select file_name from dba_files where tablespace_name='TEST_TABLESPACE';查看文件位置发现text2已经删除

        select avg(教师薪资) from 教师表

删除表空间:

    最大和最小:

drop tablespace tablespace_name [INCLUEDING CONTENTS]

        select max(教师薪资) from 教师表

如若仅删除表空间,不删除数据文件:则不需要加 [INCLUDING CONTENTS]

更新数据:

具体操作:删除表空间test1

    update 教师表 set 教师年龄=28,教师薪资=7777 where 教师编号=4

drop tablespace test1_tablespace including contents;删除表空间成功

    update 教师表 set 姓名='莲花中学之'+姓名

操作表:

删除数据:

1.认识表

    delete from 教师表 where 教师编号=7

a.表都存放在表空间里,是数据库基本存储单位,是一个二维结构,行(记录)和列(域和字段)

 

b.约定:1.每一列数据必须具有相同的数据类型

约束:

2.列名唯一

1、unique:唯一约束,一个表可以有多个约束,但primary 约束只有一个

3.每行数据的唯一性(重复的行会造成表中数据的冗余)

 

2.数据类型

2、Primary key:约束唯一标识数据库中的每条记录,主键必须包含唯一值,不能包含null值,有且只有一个主键。

a.字符型:

 

char(n),nchar(n)是固定长度的,nchar是按照unique格式存放数据,char的n最大值是2000,而nchar的n最大值是1000;一般用nchar存储汉子。不够会自动补上空格。

3、Foreign key:两个表的约束

varchar2(n),nvarchar2(n),varchar2(n)最大值是4000,nvarchar2(n)最大值是2000.不够不会补上空格,可以节省空间。

 

b.数值型

4、check 约束:如果对单个列定义check约束,那么该列值运行特定的值。

number(p,s) p代表有效数字,s代表小数点后的位数;例如:number(5,2) 有效数字5位,保留2位小数,如123.45s

                  如果对一个表定义check约束,那么此约束会在特定的列中对值          进行限制。

float(n) 主要存储二进制数据(能表示二进制的数据位数是1-126位),二进制数转换成十进制需要乘以0.30103才能得到。所以在oracle中使用number比较多

5、default:默认值

c.日期型

alter table userIofo

data date类型表示范围:公元前4712年1月1日到公元9999年12月31日

add constraint PK_userId primary key(userId),

可以精确到秒。

    constraint CK_userPwd check(len(userPwd)>6),

timestamp时间戳类型,能精确到小数秒

    constraint Ck_Gender check(Gender=0 or Gender=1),

d.其他

    constraint DF_Gender default(0) for gender,

blob:能存放4g的数据(二进制)

    constraint CK_Email check(Email like '%@%')

clob:能存放4g的数据(字符串类型)

--[1]外键约束时注意类型,长度必须与引用的主键列的类型,长度一致

3.管理表

--[2]外键约束时注意引用表中必须要有主键列

创建表:

--[3]exec sp_helpconstraint 表名称

基本语法:

create table table_name

(

column_name datatype,...

)在同一个用户下,所有的表名要是唯一的

创建用户信息表:

所需字段:  编号    用户名 密码  邮箱  注册时间

字段的类型:number    varchar2(n)        date

create table userinfo

(id number(6,0),

username varchar2(20),

userpwd varchar2(20),

email varchar2(30),

regdate date);

查看表的结构与查看数据字典的方法一样:desc userinfo

修改表:(修改表的结构,而不是数据)

1.添加字段(列或者域):

如果一个表已经创建好,还想再添加一些字段:

alter table table_name add column_name datatype;凡是对表进行修改都是alter table

具体操作:向useinfo表里添加一个备注字段

alter table usreinfo add remarks varchars(500);

2.更改字段的数据类型:

alter table table_name modify column_name datatype;

操作:修改数据类型的长度,更换数据类型

alter table userinfo modify remarks varchar2(400);注意:如果在一个表当中已经存在了数据,则不能修改,只有在数据为空的情况下才能进行更改)。

alter table userinfo modify userpwd number(6,0);修改密码字段的数据类型

3.删除字段:

alter table table_name drop column column_name;

具体操作:alter table userinfo drop column remarks;

4.修改字段名:

alter table table_name rename column column_name to new_column_name;

具体操作:alter table useinfo rename column email to new_email;

5.修改表名:

rename table_name to new_table_name;

rename userinfo tp new_userinfo;

4.删除表:

truncate table table_name;(删除表当中的全部数据,也叫截断表,比delete速度快)

truncate table new_userinfo;删除表里的数据,表的结构仍然存在。

drop table table_name;删除整张表的结构:

drop table new_userinfo;删除表不存在了。

操作表中的数据:

1.添加数据:

insert语句:

insert into table_name

(column1,column2,...)

values(value1,value2,...)

操作实例:

1.向表中所有字段添加值:

insert into userinfo values(1,'xxx','123','xxx@126.com',sysdate);向表中插入一条数据,sysdate函数获取当前系统时间。

查看该条数据:select * from userinfo;

2.向表中指定字段添加值:

insert into userinfo(id,username,userpwd) values(2,'yyy','123');

查看数据:select username,userpwd from userinfo;

3.向表中添加默认值:

a.创建表时就设置;create table userinfo1(id number(6,0),regdate date default sysdate);

插入数据时:insert into userinfo1 (id) values(1);则regdate字段会自动补上。

查询:select * from userinfo1;

修改userinfo表email字段

b.已经创建好表,后来再修改成default

更改字段的数据类型:alter table userinfo modify email default '无';

再向表中插入数据:insert into userinfo (id) values(2);

查看:select email from useinfo;

注意:字符型加单引号;如果在创建表市,某些字段不能为空时添加值时需不为空。

2.复制表数据:

1.在建表时复制:

create table table_new as select column1,...|*from table_old;

create table userinfo_new as select * from userinfo;全部复制

create table userinfo_new1 as select id,username from userfino;复制部分

2.在添加时复制:

insert into table_new [(column1,..)] select column1,...| * from table_old;

表已经存在,在添加数据时复制数据:

insert into userinfo_new select * from userinfo;复制userinfo表的全部数据

查看:select if from userinfo_new;

inert into uerinfo_new(id,username) select id,username from userinfo;复制userinfo表的部分数据

3.修改数据:

update语句:

update table_name set column1=value1,...[where cinditions];修改的条件,如果不加where condition则会给所有的行更改

操作实例:

无条件的更新:

update userinfo set userpwd='111111';一个字段的更改

select userpwd from userinfo;

update userinfo set userpwd='111',email='111@126.com';多个字段的更改

select userpwd,email from userinfo;

有条件的更新:

用户名是xxx的密码改为:123456

update userinfo set userpwd='123456' where username='xxx';

select userpwd from userinfo;

4.删除数据:

以行为单位来进行删除。

delete from table_name; truncate table截断表 和delete from table_name效果一样

操作实例:

无条件的删除:

先创建删除表:create table testdel1 as select * from userinfo;

删除表;delete from testdel1;

查看:select * from testdel1;结果:未选定行,删除成功。

有条件的删除:删除用户名是yyy的用户:

先查看yyy用户是否存在:select username from userinfo;

删除用户名yyy:detele from userinfo where username='yyy';

约束:数据类型是控制输入的值的类型,约束是控制你输入的值要满足你设定好的一些要求的。

1.约束的作用:

1.定义规则

2.确保完整性

2.oracle中的5个约束:

1.非空约束

1.在创建表时设置非空约束:

create table table_name (column_name datatype not null,...);

create table userinfo_1(id number(6,0),username varchar2(20) not null, userpwd varchar2(20) not null);

2.在修改表时添加非空约束:

alter table table_name modify column_name dtatype not null;

给userinfo添加非空约束:alter table userinfo modify username varchar2(20) no tnull;报错,因为表内有数据。

此时需要把表内数据删除才能添加约束:delete from userinfo;

再执行: alter table table_name modify column_name dtatype not null;添加约束,就会成功。

3.在修改表时去除非空约束:

alter table table_name modify column_name datatype null;

alter table table_name modify column_name dtatype null;

2.主键约束

作用:确保表当中每一行数据的唯一性(非空,唯一)一张表里只能设置一个主键约束,但是这个主键约束可以由多个字段构成(联合主键或复合主键)

1.在创建表的时候设置主键约束:

列集:

create table table_name ( column_name datatype primary key,...)

create table userinfo_p

(id number(6,0) primary key,

username varchar2(20),

userpwd varchar2(20)

); 主键约束默认是not null;

表集:

联合主键:(在所有的字段写完后,再写此句约束)表集约束

constraint constraint_name primary key(column_name1,...)

create table userinfo_p1

(id number(6,0),

username varchar2(20),

userpwd varchar2(20),

constraint pk_idusername primary key(id,username));

结果表userinfo_p1的id 和username都为not null.

desc user_constraints;通过这个数据字典来查询约束的名字,类型等

例如查约束的名字:

select constraint_name from user_constraints where table_name=' userinfo_p1';

查询到的主键约束名字为:pk_idusername

之前给userinfo_p的id字段设置主键约束但并未设置名字,而是由系统生成的, select constraint_name from user_constraints where table_name=' userinfo_p';结果为:SYS_C0010836

2.在修改表的时候添加主键约束:

ADD CONSTRAINT constraint_name primary key(column_name1,...);

alter table userinfo

add constraint pk_id primary key(id);

如果表当中已经存在值了,值必须得是唯一的,不能为空值;最好的在添加主键时表内没有值。

select constraint_name from user_constraints where table_name=' userinfo';结果:pk_id

3.更改约束的名称:

rename constraint old_name to new_name

alter table userinfo

rename constraint pk_id to new_pk_id;

可以更改主键约束的名字,也可以更改任何一个约束的名字。

4.删除主键约束:

1.禁用|启用:

disable|enable constraint constraint_name

操作:alter table userinfo

disable constriant new_pk_id;

查看禁用的约束:

select constraint_name,status from user_constraints where table_name='USERINFO';

2.drop constraint constraint_name完全删除约束

操作:alter table userinfo

drop constraint new_pk_id;

3.drop primary key[cascade],由于每张表只有一个主键约束,cascade是表存在及联约束时,删除其余表对这表的外键约束

3.外键约束

1.在创建表时设置外键约束:

列集:

create table table1

(column_name datatype references

table2(column_name),...);

table2是主表,table1是从表;设置外键约束时,主表的字段必须是主键,主从表中相应的字段必须是同一个数据类型;从表中外键字段的值必须来自主表中的相应字段的值,或者为null的值

操作:

1.先创建主表:(用户类型表)

create table typeinfo

(typeid varchar2(10) primary key,

typename varchar2(20));

2.创建从表:

create table userinfo_f

(id varchar2(10) primary key,

username varchar2(20),

typeid_new varchar2(10) references typeinfo(typeid));

3.验证向从表当中的typeid的来源要么是主表当中的值要么是空值。

insert into typeinfo values(1,1);

insert into userinfo_f(id,typeid_new) values(1,2)报错

insert into userinfo_f(id,typeid_new) values(1,1); ok了

插入空值insert into userinfo_f(id,typeid_new) values(2,null); ok了

表集:

关键字                    外键约束关键字

constraint constraint_name foreign key(column_name) references table_name(column_name) [ON DELETE CASCADE]及联删除:主表当中的数据删除之后,从表当中使用了这条数据的字段也会被删除。确保了主从表数据的完整性。约束的名字是唯一的

create table userinfo_f2

(id varchar2(10) primary key,

username varchar2(20),

typeid_new varchar2(10),

constraint fk_typeid_new foreign key(typeid_new) references typeinfo(typeid));

操作:

create table userinfo_f3

(id varchar2(10) primary key,

username varchar2(20),

typeid_new varchar2(10),

constraint fk_typeid_new1 foreign key(typeid_new) references typeinfo(typeid) on delete cascade);

2.在修改表时添加外键约束:

add constraint constraint_name foreign key(column_name) references table_name(column_name) [on delete cascade]

操作:

1.创建表:create table userinfo_f4

(id varchar2(10) primary key,

username varchar2(20),

typeid_new varchar2(10));

2.更改表:

add constraint fk_typeid_alter foreign key(typeid_new)references typeinfo(typeid);

3.删除外键约束:

1.禁用|启用:

disable|enable constraint constraint_name

因为忘记了外键约束的名字,所以先查找外键约束的名字:

select constraint_name,constraint_type,status from user_constraints where table_name='USERINFO_F4';

结果:一个P,主键约束;一个R,外键约束,名字为:FK_TYPEID_ALTER

操作:alter table userinfo

disable constraint FK_TYPEID_ALTER;

验证是否禁用:

select constraint_name,status from user_constraints where table_name='USERINFO_F4';

2.彻底删除:

drop constraint constraint_name完全删除约束

操作:alter table userinfo

drop constraint FK_TYPEID_ALTER;

4.唯一约束

作用:保证字段值的唯一性

主键约束作用是保证值的唯一性;唯一约束和主键约束的区别:主键字段必须是非空的,而唯一约束则可以为空值;每张表的主键约束只能有一个,而唯一约束却可以有多个。

1.在创建表时设置唯一约束:

列集:

create table table_name

(column_name datatype UNIQUE,...)

操作:create table userinfo_u

(id varchar2(10) primary key,

username varchar2(20) unique,

userpwd varchar2(20));

表集:

constraint constraint_name unique(column_name),这句话放在所有字段定义完成之后。

要将多个字段设置成唯一的约束,需要写多个唯一约束语句。

操作:create table userinfo_u1

(id varchar2(10) primary key,

username varchar2(20),

constraint un_username unique(username));

2.在修改表时添加唯一约束:

add constraint constraint_name unique(column_name);

操作:1.创建表:create table userinfo_u2

(id varchar2(10) primary key,

username varchar2(20));

2.添加唯一约束:

alter table userinfo_u2 add constraint un_username_new unique(username);

3.删除唯一约束:

1.暂时禁用:

disable|enable constraint constraint_name

查看约束的名字,类型,状态:

select constraint_name,constrint_type,status from user_constraints where table_name=' userinfo_ u2';

结果为: 一个u 一个p.

alter table userinfo_u2

disable constraint UN_USENAME_NEW;

2.彻底删除:

drop constraint constraint_name

5.检查约束

作用:让表中的数据更具有实际意义

员工个人信息:年龄,工资,电话号码

50    1000  134XXXXX

1.在创建表时设置检查约束:

在一个表当中也可以有多个。

列集:

create table table_name (column_name datatype check(expressions),...)

操作:create table userinfo_c

(id varchar2(10) primary key,

username varchar2(20),

salary number(5,0) check(salary>0));

这样就不能往里插负数的工资数据了。

表集:

constraint constraint_name check(expressions);通常以ck,chk开头

操作:

create table userinfo_c1

(id varchar2(10) primary key,

username varchar2(20),

salary number(5,0),

constraint ck_salary check(salary>0));

2.在修改表时添加检查约束:

add constraint constraint_name check(expressions);

操作:create table userinfo_c3

(id varchar2(10) primary key,

username varchar2(20),

salary number(5,0));

alter table userinfo_c3

add constraint ck_salary_new check(salary>0);

3.删除检查约束:

1.暂时禁用:

disable|enable constraint constraint_name

2.彻底删除:

drop constrint constraint_name

约束小结:1.只有主键约束在每张表里面只能有一个,可以由多个字段构成。

2.外键约束时唯一一个涉及两张表之间关系的约束

3.在创建表时设置约束:非空约束只能在列级设置,不能在表级设置,并且设置非空约束时,非空约束没有名字。

4.在修改表时添加约束:在修改表的时候添加非空约束实际上用的是:修改字段的语句,为:alter table table_name modify column_name datatype not null;

5.更改约束的名称:非空约束没有名字,不能使用该语句

1.不知道约束名称时,可以更加数据字典来查看:user_constraints

2.rename constraint old_name to new_name 此语句就是更改约束名称的语句

6.删除约束:1.alter table table_name modify column_name datatype null;

2.禁用:disable | enable constraint constraint_name(放在alter table之后出现)

3.彻底删除:drop constraint constraint_name

4.删除主键约束:drop primary key

查询:

1.基本查询语句:

select [distinct] column_name1,...|* from table_name [where conditions]

2.在SQL*PLUS中设置格式:设置查询结果的显示格式

1.更改查询后结果的字段名:column column_name heading new_name; (column可以简写成col)

操作:设置新的字段名:col username heading 用户名;

2.column column_name format dataformat; (注意:字符类型只能设置显示的长度)

操作:把用户名字段的数值长度改为10:

col username format a10;(a代表字符型,10代表10个长度)

把工资字段设置为显示一位小数:

col salary format 9999.9;(9代表一个数值)

把工资格式改为美元符号开头:

col salary format $9999.9;

3.清除设置的格式:col column_name clear;

3.查询表中的所有字段及指定字段:

1.查询所有字段:select * from table_name;

操作:select * from users;

2.查询部分字段:select column1_name,column2_name from table_name;

4.给字段设置别名  :(注意:是针对查询结果进行的,并没有更改字段的名字)

可以一次为多个字段设置别名

select column_name as new_name,... from table_name;(注意:as可以省略,用空格隔开原来的字段名和新名字即可)

操作:select id as 编号,username as 用户名,salary 工资 from users;

通过distinct去除重复的用户名:

select distinct username as 用户名 from users;

5.运算符和表达式:

表达式=操作数+运算符

操作数:变量,常量,字段;

运算符:算术运算符:+ - * /

比较运算符:>, >=, <, <=, =, <>

逻辑运算符:and or not (not是非)

6.在select语句中使用运算符:(只影响查询结果,不影响实际表中的数据,要真的改表中的数据,需要使用update语句)

使用算术运算符:select id,username,salary+200 from users;(给工资加200元)

使用比较运算符:select username from users where salary>800;(查询工资超过800元的人)

使用逻辑运算符:select username from users where salary>800 and salary<>1500;(查询工资高于800,但不等于1500的人);

7.带条件的查询:

1.单一条件查询: select salary from users where username='aaa';

2.多条件的查询: 使用逻辑运算符连接语句 select * from users where username='aaa' or salary>2000;

select * from users where username='aaa' or (salary>800 and salary<=2000);

逻辑运算符的有限性:not > and > or; 比较运算符 >逻辑运算符

select * from users where not (username='aaa');

8.模糊查询:

like

通配符的使用:_ %(一个_只能代表一个字符,%可以代表0到多个任意字符)

操作:查找以a开头的用户:select * from users where username like 'a%';

查找含有a的用户名:select * from users where username like '%a%';

9.范围查询:

between...and(从什么值到什么值)

操作:查询工资从800到2000的用户:select * from users where salary between 800 and 2000;(是闭合区间)

in/not in (后面的值不是范围,而是一个具体的值)查询的值等于什么,不等于什么。

select * from users where username in ('aaa','bbb');

10.对查询结果排序:

select... from...[where...] order by column1 desc/asc,...

操作:select * from users order by username desc,salary asc;(用户名)

11.case...when语句使用:

  1. case column_name

when calue1 then result1,...

[else result] end

操作:select username, case username when 'aaa' then '计算机部门'

when 'bbb' then '市场部门' else '其他部门' end as 部门

from users;

  1. case

when column_name=value1

then result1,...[else result] end

操作: select username,case when username='aaa' then '计算机部门'

when username='bbb' then '市场部' else '其他部门' end as 部门

from users;

select username,case when salary<800 then '工资低'

when salary>5000 then '工资高' end as 工资水平

from users;

12.decode函数的使用:

decode (column_name,value1,result1,...,defaultvalue)

操作: select username, decode(username,'aaa','计算机部门','bbb','市场部门','其他')as 部门

from users;

总结:

1.用户表空间:

1.如何查看登录用户:show user命令 dba_users数据字典

2.启用scoot用户

3.如何查看某个用户的默认表空间和临时表空间

4.表空间管理:创建,修改,删除表空间

2.表与约束:

1.数据类型:char(n) nchar(n) varchar2(n) nvarchar2(n) ;数值型:number(p,s) float(n) ;日期型:date,timestamp ;其他类型:blob,clob

2.对表的创建,修改,删除

3.对表中数据的操作:添加数据insert 修改数据update 删除数据:delete

4.5个约束:非空约束,主键约束,外键约束,唯一约束,检查约束

5.查询语句:查询所有字段和指定字段;在查询语句中使用运算符和表达式;在查询语句中加入where;范围查询;模糊查询:like关键字,通配符;case...when语句和Decode函数

本文由17727发布于数据库信息,转载请注明出处:数据库Tsql语句创建,SQL基本操作

关键词: