sql基本语句整理

//sql基本语句整理


//创建数据库
CREATE DATABASE database_name;


//删除数据库
DROP DATABASE database_name;


//创建模式
CREATE SCHEMA name_of_schema AUTHORIZATION name_of_user;


//删除模式
DROP SCHEMA name_of_schema CASCADE;     //连级删除,包括所有对象
DROP SCHEMA name_of_schema RESTRICT;    //若有下属对象,拒绝执行删除操作


//创建表
CREATE TABLE name_of_table
(
name_of_column1 column_datatype,
name_of_column2 column_datatype,
name_of_column3 column_datatype
);


//例子
CREATE TABLE Train_Times
(
start_location varchar(75),
destination varchar(75),
departs time,
arrives time
);


//基本表的修改
ALTER TABLE name_of_table
ADD name_of_newcolumn data_type condition;
DROP condition;
ALTER COLUMN name_of_column data_type condition;


//删除基本表
DROP TABLE name_of_table CASCADE;             //删除都有依赖对象
DROP TABLE name_of_table RESTRICT;  //有依赖对象则拒绝删除


DROP TABLE Student CASCADE;


//数据类型
CHAR(n)            // 长度 为n的定长字符串
VARCHAR(n)         // 最大长度为n的变长字符串
INT/INTEGER        //长整数
SMALLINT           //短整数
NUMERIC(p,d)       //定点数,p位数字,d位小数
REAL               //机器精度的浮点数
Double Precision   //机器长度的双精度浮点数
FLOAT(n)           //浮点数,精度至少为n
DATA               //日期   YYYY-MM-DD
TIME               //时间   HH:MM:SS






//插入新数据
INSERT INTO
table_name (column_name1,column_name2,column_name3,....)
INTO
VALUES(data_value1,data_value2,data_value3,....);


//显示表中所有数据
SELECT *
FROM table_name


CREATE TABLE MemberDatils    //
(
MemberId integer,
FirstName varchar(50),
LastName varchar(50),
DateOfBirth data,
Street varchar(100),
City varchar(75),
_State varchar(75),
ZipCode varchar(12),
Email varchar(200),
DateOfJoining date
);




INSERT INTO
MemberDatils
VALUES
(
1,
'Katie',
'Smith',
'1977-01-09',
'Main Road',
'Townsville',
'Stateside',
'123456',
'[e-mail]',
'2004-02-23'
);


CREATE TABLE Category
(
CategoryID integer,
Category varchar(190)
);


INSERT INTO
Category(categoryID,Category)
VALUES(6,'historical');




//更新数据
UPDATE table_name
SET column_name=data_value
WHERE condition


UPDATE MemberDetails
SET 
Street='45 upper road',
city='new town',
_state='new state',
ZipCode='99112'
WHERE MemberID=4;


//WHERE子句,用于查询和更新时作为条件
//<>表示不等于
//逻辑运算符AND和OR
//相当于C++中的&&和||


UPDATE MemberDetils
SET _State='mega state'
WHERE
_State='small state'
OR
_State='stateside';


UPDATE Location
SET _State='Mega State'
WHERE
_State='small state'
OR
_State='stateside';


//删除数据
DELETE FROM MemberDetails    //删除表中所有记录


DELETE FROM MemberDetails     //可以用WHERE指定条件
WHERE MemberID=3;




//提取信息与查询
SELECT column1,column2,columnx
FROM table_name;


//
SELECT MemberID,FirstName
FROM MemberDetails;


//用*取代所有列
SELECT *
FROM Location;
//尽量不使用所有列,影像处理速度浪费内存


//如果只想知道一个记录中的唯一值,可以用DISTINCT
SELECT DISTINCT City
FROM MemberDetails;
//city只会出现唯一值




//使用别名
SELECT sname NAME,yearOfBirth BIRTH,2004-sage BIRTHDAY,LOWER(sdept) DEPARTMENT
FROM Student;


SELECT LastName AS Surname
FROM MemberDetails;


//利用WHERE过滤子结果
SELECT FirstName + ' ' + LastName AS [FULL NAME]
FROM MemberDetails
WHERE City='Big City';


//运算符优先级
//圆括号()
//NOT
//AND
//ALL,ANY,BETWEEN,IN,LIKE,OR,SOME


//尽量使用园括号改变优先级
//基于true的条件 被过滤
SELECT FirstName
FROM MemberDetails
WHERE NOT State='Golden State';


//
SELECT FirstName
FROM MemberDetails
WHERE State<> 'Golden State';


//BETWEEN 用于指定一个范围
//这个范围位于两个值之间


SELECT FilitiName,Rating
FROM Films
WHERE Rating BETWEEN 3 AND 5;


//LIKE 运算符
//用于字符串匹配
SELECT LastName 
FROM MemberDetails
WHERE LastName LIKE 'j%';


//在某些数据库中,LIKE区分大小写如oracle
//%匹配一个或多个字符
//_匹配一个字符


SELECT LastName 
FROM MemberDetails
WHERE LastName LIKE 'D__s';


//LIKE经常和NOT LIKE 合用


//IN运算符
SELECT City
FROM MemberDetails
WHERE
City IN ('TOWNSVILL','windy village','Dover','Big City');


SELECT FolmName,YearReleased,Rating
FROM Films
WHERE
YearReleased IN (1967,1977,1987)
AND
Rating IN (4,5);


//利用ORDER BY排序结果
//默认升序
SELECT YearReleased
FROM Films
ORDER BY YearReleased;


ORDER BY YearReleased DESC;


//若有WHERE ,ORDER BY要置于WHERE的后面
//BETWEEN '2005-01-01' AND '2005-02-03'


//从多个表选择数据
SELECT FileName,YearReleased,Rating
FROM Films INNER JOIN Category
ON Films.CategoryId = Category.CategoryId
WHERE Category.CategoryId =6;


SELECT FileName,YearReleased,Rating
FROM Films,Category
ON Films.CategoryId = CategoryId.CategoryId
WHERE Category.CategoryID=6;


//约束
//NOT NULL
CREATE TABLE MyTable
(
Column1 int NOT NULL,
Column2 varchar(20),
Column3 varchar(12) NOT NULL
);


ALTER TABLE MyTable
MODIFY Column2 varchar(20) NOT NULL;


//UNIQUE
//防止特定列中两个值一样
//保证特定列数据唯一性
CREATE TABLE MyTable
(
Column1 int UNIQUE,
Column2 varchar(20) UNIQUE,
Column3 varchar(12) UNIQUE
);


//CHECK约束
///检查每一个输入到记录中的值,若为假,则违反约束,并不输入到表中.
CREATE TABLE NamesAges
(
Name varchar(50),
Age int CHECK(Age>0)
);


INSERT INTO
NamesAges(Name,Age)
VALUES('jim',30);




//主键约束,每个表都应当有一个主键
CREATE TABLE HolidayBookings
(
CustomerId int PRIMARY KEY,
BookingId int,
Destination varchar(50)
);




//IBM DB2中,主键列必须为NOT NULL
CREATE TABLE HolidayBookings
(
CustomerId int NOT NULL PRIMARY KEY,
BookingId int,
Destination varchar(50)
);


//
CREATE TABLE HolidayBookings
(
CustomerId int NOT NULL,
BookingId int NOT NULL,
Destination varchar(50),
PRIMARY KEY(CustomerId,BookingId)
);




//外键约束
//外键是访问另一个表中的主键的列
FOREIGN KEY (NAME_OF_COLUMN) REFERENCES NAME_OF_TABLE(NAME_OF_PRIMARYCOLUMN);


CREATE TABLE SC
(
Sno CHAR(9),
Cno CHAR(4),
PRIMARY KEY (Sno,Cno),


FOREIGN KEY (Sno) REFERENCES Student(Sno),
FOREIGN KEY (Cno) REFERENCES Course(Cno)
);




//索引
CREATE INDEX NAME_OF_INDEX
ON NAME_OF_TABLE(COLUMN1 ASC,COLUMN2 DESC,...)


CREATE UNIQUE INDEX NAME_OF_INDEX   //一个索引对应唯一的记录
CREATE CLUSTER INDEX NAME_OF_INDEX   //创建群簇索引,经常更新的数据不宜采用


//删除索引


DROP INDEX name_of_table.index_name;


//设计好的数据库的技巧
//不要设计仅满足普通情况的情形,要覆盖所有可能发生的情况
//为表和字段选择有意义的名称
//尽量使名称简单
//命名和数据类型要一致
//先在纸上分析需求
//仔细选择主键
//创建一个索引,加快搜索速度
//添加一个多列索引
//避免使用保留字作为表名或字段名

//考虑储存空间

//转载请注明出处