数据库学习总结

DML语句

- select 　　从数据库中获取数据
- update　　更新数据库表中的数据
- delete　　从数据库中删除数据
- insert into 向数据库中插入数据

DDL语句

- create database 创建新的数据库

- alter database　　修改数据库
- create table 　　创建新表
- alter table 　　　变更(改变)数据库表
- drop table 　删除表
- create index 　　创建索引(搜索键)
- drop index　　删除索引

DDL语句

create user　　创建用户
show grants 显示用户
grant　　授权
revoke　　撤销授权

PostgreSQL、MySQL、Oracle、MSSQL等数据库的规范不同，但规则基本一样，有的语句会单独提到,以下语句是我全部在 sql server 上敲过了。

语句小写为了阅读方便。

表名称：table_name　　列名称：column_name　　　懒得统一了，自行理解！！！！

select语句

select　列名称　from 表名称
select　　*　　 from 表名称
//*代表所有
select distinct 列名称 from 表名称
//关键字distinct用于返回唯一不同的值(相同的只返回一个)

where子句

select 列名称 from 表名称 where 列运算符值
例子：select * from Persons where LastName = 'Bush'
= 等于
<> 不等于//在某些版本的 SQL中,操作符 <> 可以写为!=
> 大于
< 小于
>= 大于等于
<= 小于等于
BETWEEN 在某个范围内
LIKE 搜索某种模式

and和or运算符

如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录
如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录

order by 语句

根据指定的列对结果集进行排序(默认是升序，降序可以用desc关键字)

例：
①select 　　Company,OrderNumber 　　from 　　Orders 　　order by 　　Company

//从Orders表中选择Company和OrderNumber两列，安Company表升序排序

②select 　　Company,OrderNumber 　　from 　　Orders 　　order by　　 Company，OrderNumber

//从Orders表中选择Company和OrderNumber两列，先安Company表升序排序，当Company内内容相同时，再安OrderNumber表升序排序

③select 　　Company,OrderNumber 　　from 　　Orders 　　order by 　　Company desc

//从Orders表中选择Company和OrderNumber两列，安Company表降序排序

④select　　 Company,OrderNumber 　　from 　　Orders 　　order by 　　Company desc，OrderNumber asc

//从Orders表中选择Company和OrderNumber两列，安Company表升序排序

insert into语句

insert 　　into 　　表名称　　values(值1,值2.....)

insert 　　into 　　table_name (列1,列2....) 　　values(值1,值2...)

insert 　　into 　　persons 　　values('Gates','Bill','xuanwuwen10','beijing')

insert 　　into 　　persons (LastName,Adderss) 　　VALUES ('Wilson', 'Champs-Elysees')
//一一对应

update语句

update 表名称 set 列名称 = 新值 where 列名称 = 某值

//当where匹配时，更新列名称所赋的新值

UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing'
where LastName = 'Wilson'

delete语句

delete from 表名称 where 列名称 = 值

DELETE 　　from 　　table_name
或
DELETE 　　* 　　from 　　table_name

top子句

select TOP 2 * from Persons

select TOP 50 PERCENT * from Persons

like操作符

select　　column_name
from 　　table_name
where column_name LIKE pattern
例：
select City from Person where [address] like '210'
select City from Person where [address] like '%10'
//"%" 可用于定义通配符（模式中缺少的字母）。

in操作符

in操作符允许在where子句中规定多个值
select column_name
from table_name
where column_name IN (value1,value2,...)
例：
select * from Person where City in('shanghai','beijing')

between操作符

select　　column_name 　　from 　　table_name 　　where　　column_name

between value1 and value2
//包括1不包括2在内的所有(包含2不包含1)
select column_name
from table_name
where column_name
not between value1 and value2
//上面的非

通配符

% 　　　　　　　　　替代一个或多个字符
_ 　　　　　　　　　　仅替代一个字符

[charlist] 　　　　　　　字符列中的任何单一字符

[^charlist]/[!charlist]　　不在字符列中的任何单一字符
　　　　　　　
as(别名)

select 　　column_name　　from 　table_name 　as　　 biao

select 　　column_name 　　　as 　　lie　　　　from 　　table_name

select po.OrderID, p.LastName, p.FirstName

from Persons AS p, Product_Orders AS po

where p.LastName='Adams' AND p.FirstName='John'

select Person.FirstName,Person.LastName,HomePrice.price from Person

inner join HomePrice on Person.City = HomePrice.City

--order by Person.LastName

select pe.FirstName,pe.LastName,hp.price from Person pe,HomePrice hp

where pe.City = hp.City

//第一个因为有order by 安照Person.LastName进行排序，order by可以不用
//第二个安照price降序排列

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
where Persons.Id_P = Orders.Id_P

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName

JOIN: 如果表中有至少一个匹配，则返回行
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配，就返回行

inner join关键字语法

select column_name(s)
from table_name1
inner join table_name2
on table_name1.column_name = table_name2.column_name

union操作符

select pe.City from Person pe
union all
select hp.City from HomePrice hp

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

//请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
//all是全部，不加all只输出不通项

select into 语句

select *
into new_table_name [in externaldatabase] //in是加入到其他数据库中，一般不用
from old_tablename

例：
select *
into test
from Person

//把所有列插入test表中(test必须不存在)

当只是拷贝某些域时：

select LastName,FirstName
into test
from Persons where Address = 'beijing'

被连接的表

select Person.FirstName,Person.LastName
into shan
from Person
inner join HomePrice
on Person.City = HomePrice.City

create database 语句

create table table_name
(
Column_name0 type_date,
Column_name1 type_date,
Column_name2 type_date,
......
)

integer(size)	仅容纳整数。在括号内规定数字的最大位数。
int(size)
smallint(size)
tinyint(size)
decimal(size,d)	容纳带有小数的数字。
numeric(size,d)	"size" 规定数字的最大位数。"d" 规定小数点右侧的最大位数
char(size)	容纳固定长度的字符串（可容纳字母、数字以及特殊字符）。在括号中规定字符串的长度。
varchar(size)	容纳可变长度的字符串（可容纳字母、数字以及特殊的字符）。在括号中规定字符串的最大长度。
date(yyyymmdd)	容纳日期。

//以上也可以不用参数，最好还是有，比较安全

定义约束

not null 约束强制列不接受NULL值

它强制字段始终包含值，如果不向字段添加值，就无法插入新纪录或者更新记录

例：
create table persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

UNIQUE 约束唯一标识数据库表中的每条记录。(唯一)

UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。

PRIMARY KEY 拥有自动定义的 UNIQUE 约束。

请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

例：
在persons表创建时在Id_P列创建unique约束：

mysql:

create table persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
unique Id_P
)

SQL Server / Oracle / MS Access:

create table persons
(
Id_P int NOT NULL unique,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

当Id_P被创建时：

alter table persons
add unique (Id_p)

命名unique约束，并定义多个时：

create table persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
constraint uc_personid unique (Id_p,LastName)
)

//这里un_personid是这个约束的名字

alter table persons
add constraint uc_personid unique (Id_p,LastName)

撤销unique约束：
mysql:

alter table persons
drop index uc_personid

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

primary key(主键)：

PRIMARY KEY 约束唯一标识数据库表中的每条记录。

主键必须包含唯一的值。

主键列不能包含 NULL 值。

每个表都应该有一个主键，并且每个表只能有一个主键。

MySQL:
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (Id_P)
)

SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
Id_P int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD PRIMARY KEY (Id_P)

MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)

MySQL:
ALTER TABLE Persons
DROP PRIMARY KEY

SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID

foreign key(外键)约束：

"Orders" 中的 "Id_P" 列指向 "Persons" 表中的 "Id_P" 列。

"Persons" 表中的 "Id_P" 列是 "Persons" 表中的 PRIMARY KEY。

"Orders" 表中的 "Id_P" 列是 "Orders" 表中的 FOREIGN KEY。

FOREIGN KEY 约束用于预防破坏表之间连接的动作。

FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

MySQL:
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
)

SQL Server / Oracle / MS Access:
CREATE TABLE Orders
(
Id_O int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
Id_P int FOREIGN KEY REFERENCES Persons(Id_P)
)

MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
)

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)

如果需要命名 FOREIGN KEY 约束，以及为多个列定义 FOREIGN KEY 约束

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)

撤销 FOREIGN KEY 约束
MySQL:
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

SQL Server / Oracle / MS Access:
ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders

CHECK 约束
CHECK 约束用于限制列中的值的范围。

如果对单个列定义 CHECK 约束，那么该列只允许特定的值。

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

SQL CHECK Constraint on CREATE TABLE
下面的 SQL 在 "Persons" 表创建时为 "Id_P" 列创建 CHECK 约束。CHECK 约束规定 "Id_P" 列必须只包含大于 0 的整数。

My SQL:
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (Id_P>0)
)
SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
Id_P int NOT NULL CHECK (Id_P>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
如果需要命名 CHECK 约束，以及为多个列定义 CHECK 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')
)
SQL CHECK Constraint on ALTER TABLE
如果在表已存在的情况下为 "Id_P" 列创建 CHECK 约束，请使用下面的 SQL：

MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CHECK (Id_P>0)
如果需要命名 CHECK 约束，以及为多个列定义 CHECK 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')

撤销 CHECK 约束

SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT chk_Person

MySQL:
ALTER TABLE Persons
DROP CHECK chk_Person

DEFAULT 约束
DEFAULT 约束用于向列中插入默认值。

如果没有规定其他的值，那么会将默认值添加到所有的新记录。

SQL DEFAULT Constraint on CREATE TABLE
下面的 SQL 在 "Persons" 表创建时为 "City" 列创建 DEFAULT 约束：

My SQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

通过使用类似 GETDATE() 这样的函数，DEFAULT 约束也可以用于插入系统值：

CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
OrderDate date DEFAULT GETDATE()
)

如果在表已存在的情况下为 "City" 列创建 DEFAULT 约束，请使用下面的 SQL：

MySQL:
ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'

SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'

撤销 DEFAULT 约束
如需撤销 DEFAULT 约束，请使用下面的 SQL：

MySQL:
ALTER TABLE Persons
ALTER City DROP DEFAULT

SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT

create index 语句
//创建索引，使数据库应用程序可以更快的查找数据。
//用户无法看见索引，它只能用来加速查询/搜索
//但索引本身要更新，所以尽量在常被查询的表(列)上创建索引

create index index_name
on table_name (column_name)

//"column_name" 规定需要索引的列。

创建唯一的索引：
//就是加上unique约束条件
create unique index index_name
on table_name (column_name)

例：
名为 "PersonIndex"，在 Person 表的 LastName 列：

CREATE INDEX PersonIndex
ON Person (LastName)

以降序索引某个列中的值，您可以在列名称之后添加保留字 DESC：

CREATE INDEX PersonIndex
ON Person (LastName DESC)

索引不止一个列，可以在括号中列出这些列的名称，用逗号隔开：

CREATE INDEX PersonIndex
ON Person (LastName, FirstName)

sql撤销索引，表以及数据库：
//用的drop index语句

用于 Microsoft SQLJet (以及 Microsoft Access) 的语法:
DROP INDEX index_name ON table_name

用于 MS SQL Server 的语法:
DROP INDEX table_name.index_name

用于 IBM DB2 和 Oracle 语法:
DROP INDEX index_name

用于 MySQL 的语法:
ALTER TABLE table_name DROP INDEX index_name

//以上是撤销索引

sql drop table语句：
//用于删表

drop table table_name

sql drop database语句：
//用于删库

drop database database_name

sql truncate table语句：
//仅删除数据，不删除表本身

truncate table table_name

alter table 语句：
//在已有的表中添加，修改，删除列

添加列：
alter table table_name
add column_name data_type

删除列：
alter table table_name
drop column column_name

修改列的数据类型：
alter table table_name
alter column column_name data_type

sql auto increment字段：

Auto-increment 会在新记录插入表中时生成一个唯一的数字。
在每次插入新记录时，自动地创建主键字段的值。

用于 MySQL 的语法:

SQL 语句把 "Persons" 表中的 "P_Id" 列定义为 auto-increment 主键：
CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)
MySQL 使用 AUTO_INCREMENT 关键字来执行 auto-increment 任务。

默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1。

要让 AUTO_INCREMENT 序列以其他的值起始，请使用下列 SQL 语法：

ALTER TABLE Persons AUTO_INCREMENT=100
要在 "Persons" 表中插入新记录，我们不必为 "P_Id" 列规定值（会自动添加一个唯一的值）：

INSERT INTO Persons (FirstName,LastName)
VALUES ('Bill','Gates')
上面的 SQL 语句会在 "Persons" 表中插入一条新记录。"P_Id" 会被赋予一个唯一的值。"FirstName" 会被设置为 "Bill"，"LastName" 列会被设置为 "Gates"。

SQL Server 的语法:

把 "Persons" 表中的 "P_Id" 列定义为 auto-increment 主键：

CREATE TABLE Persons
(
P_Id int PRIMARY KEY IDENTITY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
MS SQL 使用 IDENTITY 关键字来执行 auto-increment 任务。

默认地，IDENTITY 的开始值是 1，每条新记录递增 1。

要规定 "P_Id" 列以 20 起始且递增 10，请把 identity 改为 IDENTITY(20,10)

要在 "Persons" 表中插入新记录，我们不必为 "P_Id" 列规定值（会自动添加一个唯一的值）：

// Oracle和Access 的语法用的时候自行百度就行。

view(视图)

create view 语句：

创建：

create view view_name as
select column_name(s)
from table_name
where condition(条件)

例：
create view view_name as//这里view_name可以是[1563 568 315]这样
select Person.FirstName,Person.LastName,HomePrice.price,Person.City
from HomePrice,Person
where Person.City = 'beijing' and HomePrice.City = Person.City

查询：

select
删除：
drop view view_name

仅仅选取带有NULL的值

select lastName,FirstName,Address from Person
where address IS NULL

选取不带NULL的值

select lastName,FirstName,Address from Person
where address IS NOT NULL

SQL 函数：

内建SQL函数语法:

select function(列) from 表

AVG()：返回数值列的平均值///NULL值不包括其中

select AVG(column_name) as [column_name函数返回的列名]
from table_name
例：
①select AVG(price) as heihei from HomePrice

OrderPrice 值高于 OrderPrice 平均值的客户。
②SELECT Customer FROM Orders
WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)

触发器

书上的mysql：

create trigger trigger_name
before|after insert,delete,update on table_name
referencing new|old row as varible_
for each row|statement --row是行级触发，statement是语句级触发。

begin
if(..)

end if;
end

或：
create trigger trigger_name
before|after insert,delete,update on table_name
referencing new|old row as varible_
for each row|statement --row是行级触发，statement是语句级触发。

begin
if(..)

end if;
end

授予权限

great 权限........................on 类型....表名 to .....

great insert,delete,update,select on table Student to Role1
great select on table Course to Role1
great select on table SC to Role1

创建角色

用户名角色名
create role role1

将user1-user3定义为role1角色

Grant role1 to user1,user2,user3

posted @ 2020-03-16 10:29 菠菜面筋阅读(246) 评论(0) 收藏举报

刷新页面返回顶部

菠菜面筋

数据库学习总结

公告