sql语句体系
一.SQL语句体系
a) DQL(Data Query Language):数据库查询语言(select)
b) DDL(Data Define Language):数据库定义语言(create,drop,alter)
c) DML(Data Modify Language):数据库修改语言(insert,deleter,update)
d) TCL(Transaction Control Language):事物控制语言(commit,rollback)
二.DDL
a) create:创建表。
b) 语法格式:create table 表名(列名1 数据类型 [约束],列名2 数据类型 [约束]......);
c) 数据类型:
- Integer:整型
- Char:字符串类型。最多可存储2000个字符或者字节。该数据类型规定的长度是固定。
- Varchar、varchar2:字符串类型。最多可以存储4000字节数据。该类型是不固定长度的。
- Number:正数,负数,小数。Number(p,s):p指定的是数据的精度。S表示的是小数的位数。
- Date:日期类型。
- Clob:大文本类型。常用于存储基于字符的大数据。最多可存储4GB的数据。
- Blob:二进制的数据。最多可存储4GB。音频,视频......
d) 约束:一列可以有多个约束。主要是用来确保数据满足特定的规则。
- Not null(非空):该列数据值不能为空。
- Unique(唯一):数据不可以重复
- Primary key(主键):主键列中的数据不可重复,一个表中可以有0-1个主键约束。
- Foreign key(外键):用于定义两表之间的关联。
- Check(检查):用于强制数据必须满足一定的条件。
User(主):user_id,username,password,status,role
Book(辅):book_id,bookname,price,publish,user_id
主键,外键约束,密码不能为空,用户名不能重复,图书价格必须是10-100之间
create table t_user(
user_id integer primary key,--添加主键约束
username varchar2(50) unique,--添加唯一约束
password varchar2(50) not null,--添加非空约束
status integer,
role number(3)
);
create table book(
book_id integer primary key,
bookname varchar2(50),
price number(3,2) check(price>=10 and price<=100),
publish varchar2(50),
user_id integer references t_user(user_id)
);
--商品表:商品号,商品名称,单价,商品类别,供应商
--客户表:客户号,姓名,住址,邮箱,性别
--销售表:客户号,商品号,购买数量
create table products(
pro_id integer primary key,
pro_name varchar2(50),
pro_price number(5,3) check(pro_price>0),
pro_ca varchar2(50),
pro_privader varchar2(50)
) ;
create table customers(
cus_id integer primary key,
cus_name varchar2(30) not null,
cus_address varchar2(50),
cus_mail varchar2(50) unique,
cus_sex varchar2(10) check(cus_sex in('男','女'))
);
create table sells(
cus_id integer references customers(cus_id),
pro_id integer references products(pro_id),
p_count integer check(p_count between 1 and 50)
);
e) 删除表(drop)
- 语法结构:
- Drop table 表名;
a) 注意:如果删除的表中有主键被另外的表的外键所引用,则该表示不可以直接被删除的。如果强行删除应该先删除辅表,在删除当前的主表。
2.级联删除:drop table 表名 cascade constraints;
f) 修改表(alter):
- 语法格式
- 修改列的类型:alter table 表名 modify 列名 类型; 注意:只有空列才可以修改其数据类型。
- 修改列的名字:alter table 表名 rename column 原列名 to 新列名;
- 追加列(添加列):alter table 表名 add 列名 数据类型;
- 删除列:alter table 表名 drop column 列名;
--将customers表中的cus_address的类型修改成 char
alter table customers modify cus_address char;
--将customers表中的cus_address列的名字修改成cus_addr
alter table customers rename column cus_address to cus_addr;
--将customers表中追加一列(phonenum)
alter table customers add phonenum varchar2(11);
--删除customers中的phonenum列
alter table customers drop column phonenum;
三.DML(数据库修改语言:insert delete update)
a) 增加一行数据insert
- 语法格式1:insert into 表名(列1,列2......)values(值1,值2......);
- 语法格式2:insert into 表名 values(值1,值2......);
--添加一个注册用户
insert into t_user(user_id,username,password,status,role)
values(1,'bobo','boboadmin',0,1);
insert into t_user values(2,'bobo1','boboadmin',0,1);
b) 删除一行数据delete:
- 语法格式:delete from 表名 where XXX; XXX表示的删除条件
--删除t_user表中用户名为bobo的用户信息
delete from t_user where username='bobo';
--删除t_user表中role值为1的用户信息
delete from t_user where role=1;
C)修改数据update
I.语法格式:update 表名 set 列名=数据值 where XXX;XXX表示修改条件
--将t_user表中username为bobo的status的值修改成1
update t_user set status=1 where username='bobo';
--将t_user表中所有用户的role值修改为2
update t_user set role=2 where username like 'b%';
四.TCL(事物控制语言)
需求:客户端A通过insert sql语句向数据库中插入一条数据。客户端B去查看A插入的数据。
问题:客户端B查看不到A插入的数据
原因:A插入数据后没有提交事物。
事物:相当于一个业务功能。比如注册功能。一个事物中可以有多条sql语句,比如转账事物中就需要有至少两条的sql语句。
Commit提交事物:
显示提交:commit;
隐式提交:创建或者是删除表、正常退出客户端
Rollback撤销事物:
显示撤销:rollback;
隐式撤销:非正常退出客户端(断电,死机......)
事物的边界:
1.事物的开始:上一个事物的提交或者是撤销(回滚)。
2.事物的结束:当前事物的提交或者当前事物的撤销,表示当前事物的结束。
事物的特性(ACID)(了解):
1.原子性A:事物中的sql语句一起执行成功或失败
2.一致性C:实务操作前和事物操作后数据保持一致。
3.隔离性I:多个事物之间是相互独立的,互不干扰。
4.持久性D:提交的事物会被持久性的存储到数据库磁盘中。
五.序列(sequence)主要应用于实现主键值自动增长。
a) 创建序列:create sequence 序列名 start with 数值1 increment by 数值2;
数值1:表示该序列值的初始值。
数值2:序列值每次递增的值。
创建一个序列seq_index,让该序列值从1开始每次递增1;
create sequence seq_index start with 1 increment by 1;
实现主键自动增长:序列名.nextval表示的是序列增长一次后的结果值。
insert into test_seq values(seq_index.nextval,'hello world');
注意:序列名.nextval第一次被执行时返回的是序列初始值。
六.试图
a) 创建一个试图:create view 试图名 as (select语句);
b) 试图的作用:把一个查询的sql语句返回的结果当做一张表,给该表(查询的sql语句)起一个名字。以后就可以通过该名字来表示查询的sql语句。
c) 查询的多种情况:
- Select * from 表名;
- Select * from (select查询语句);
- Select * from 试图;
七.索引(index):提高查询效率
a) 索引就相当于是一本字典的目录。
b) 创建索引:create index 索引名 on 表名(列名);
--给t_user表中的password列添加索引:提升查询password列的效率
create index pwd_index on t_user(password);
--基于索引的查询:效率高
select password from t_user;
c) 删除索引:drop index 索引名;
d) 注意:Oracle数据库会自动为表的主键列添加一个索引。索引占用数据库的内存空间。