关系
PL/SQL是ORACLE数据库的一种语言他是一种编程语言;
SQL数据库的查询语言;
MySQL 是个数据库,只有SQL语言。
查询
查找所有
select * from CarModel;
有条件查找
select CarModel from CarSales where CarSoldDate between 'May' and 'June';
select CarModel from CarSales where PersonFileName='Bob';
查找某几列
select column1,column2,...,columnx from table_name;
数据库
创建数据库
create database AABB;
删除数据库
drop database MyFirstDatabase;
PS: Oracle
表
创建表
create table CCDD
(
start_location nvarchar(75),
destination varchar(75);
departs time
);
PS: char是定长;nvarchar(n)包含 n 个字符的可变长度 Unicode 字符数据;varchar[(n)]长度为n个字节的可变长度且非 Unicode 的字符数据。日期时间上,Oracle用data,MySQL用datetime。
更改现有表
alter table name_of_table
增加列
add name_of_field data_type;
删除列
drop column name_of_field;
删除现有表
drop table name_of_table
数据
插入数据
insert into table_name(column_numes) values (data_value);
insert into Category(CategoryId, Category) values(1, 'Thriller');
PS: 可调换但需对应,等同于insert into Category(Category, CategoryId) values('Thriller',1);
当
create table MemberDetails
(
MemberID interger,
FirstName nvarchar(50),
DataOfBirth date
);
插入
insert into MemberDetails values
(
1,
'Katie',
'1977-01-09'
);
PS: Email也属于字符。日期的格式与安装有关,Access需将日期包含在#号内,如where DateOfBirth<#2005-12-23#
更新数据
update table_nume set column_nume1=value1, column_nume2=value2 where condition;
删除数据
delete from MemberDetails where MemberID=3 and MemberID=6;
运算符
比较运算符
= <> > >= <= <
数字运算符
乘除加减*/+-
逻辑运算符
按优先级()->NOT->AND->ALL,ANY,BETWEEN,IN,LIKE,OR,SOME
有括号没括号效果一样
select State, DateOfJoing from MemberDetails where State='Newscale' or (State='Golden State' and DateOfJoing>='2005-08-01');
Like运算符
select LastName from MemberDetails where Lastname like'J%';
PS: 匹配J后一个或者多个字符,MS Access数据库中%应改为*,而放在中间表示中间位置由未知的-应改为?
In运算符
用来列举
select City from MemberDetails where City in('Town','Write');
别名组合
select LastName as Surname from MemberDetails;
select FirstName+' '+LastNme as [Fullname] from MemberDetails where MemberID=3 or MemberID=6;
PS: as是结果表的列名,而[fullname]可有可无,若别名中含有不合法字符则需用[],如select LastName as [One***Two], FirstName as [Christam Name] from MemberDetails;或是select LastName as Surname, FirstName as ChristainName from MemberDetails。Oracle中用||,如select 'First name is ' || FirstName || ', lastname is ' || LastName Fullname From MemberDetails;其中Fullname是结果表名。
排序
单个排序
select YearReleased from Films order by YearRelease DESC;
PS: DESC代表降序,从大到小,若不写则默认为ASC升序,从小到大。
多个排序
select FileName, Rating, YearReleased from Films order by Rating , YearReleased, FileName;
PS: 按从左到右顺序排列结果
多表查询
select FilmName, YearReleased, Rating from Films join Category on Film.CategoryID=category.CategoryID where Category.categoryID=6;
select FileName, YearReleased, Rating from Films, Category where Films.CategoryID=Category.CategoryID and Category.CategoryID=6;
select Favcategory.CategoryID, FavCategory.MemberID from FavCategory;
空值判断
xx IS NULL是操作符
约束
NOT NULL约束
不能为空
创建表
create table MyTable
(
Column1 int NOT NULL,
Column2 varchar(12) NOT NULL
);
添加约束,或有不支持的情况
alter table MyTable
MODIFY column2 varchar(20) NOT NULL;
UNIQUE约束
不能有一致的值
create table MyUniqueTable
(
Column1 int,
Column2 varchar(20) UNIQUE
);
CHECK约束
满足条件才可放入表中
create table NameAges
(
Name varchar(50),
Age int CHECK(Age>=0)
);
主键与PRIMARY KEY约束
每个表只能有一个
create table HoildayBookings
(
CustomerID int PRIMARY KEY,
BookingID int,
Destination varchar(50)
);
索引
PS: 大数据处理时可先删除索引,处理完后再重新创建。
创建
create INDEX member_name_index on MemberDetails(FirstName, LastName);
create unique index menber_name_index on MemberDetails(LastName DESC, FirstName);
查询
select FirstName, LastName from MemberDetails;
PS: 结果默认按升序排
删除
drop index MemberDetails.menber_name_index;
分组
显示种类情况,同样的合在一起
select City, State from MemberDetails where State in('Mage State','Golden State','New State') group by City, State;
Having
select Category, count(FavCategory.CategoryID) as Popularity from FavCategory inner jonin Category on FavCategory.CategoryID=Category.CategoryID group by Category.Category having count(FavCategory.Catefory)>3 order by Popularity DESC;
UNION
用于结合两个完全不同的查询结果,其间可能不存在链接,而只是想将它们先是在一个结果集中,要满足两表提的列数目一致,还要满足先后值得类型匹配。
函数
CONCAT
拼接字符
select concat(DataOfBirth, LastName) from MemberDetails;
select concat('The menber is called', 'FirstName,' ',LastName) as 'MemberName' from MemberDetails;
ABS(x)
求绝对值
POWER(x,N)
求x的N次方幂
SQRT(x)
求平方根
RAND()
从0到1间取随机数
CEILING(x)
向正无穷取整舍小数
FLOOR(x)
向负无穷区整舍小数
ROUND(x)
3.42->3,-4.6->-5
SUBSTRING()
substring(string) from start_character_position获取字符串中某部分或者整个字符串中一个或多个文字
UPPER('x')
转为大写
LOWER('x')
转为小写
REVERSE(x)
颠倒字符串中的字符顺序,如ABC->CBA
TRIM(x)
删除一个字符开始和末尾部分的空格,其中包含两个函数,LTRIM(x):删除左侧空格;RTRIM(x):删除右侧空格
LENGTH(x)
获得字符串中字符的个数
SOUNDEX(x)
将一个字符串转换成一种特殊4字符编码,表示文字的发音方式,而不是拼写,第一个字符总是出示字符串中第一个字符,其后一个3位数字的数值
DIFFERENCE(x,x)
解SOUNDEX编码值,返回一个0-4之间的值,反映两字符串发音的相似度,越相似数值越大
DAY(),MONTH(),YEAR()
获取日期拆分。PS: ORACLE没有
CAST()
将数据转换成特定数据类型
COALESCE()
返回传递给它作为参数值的列表中第一个非NULL值,若所有参数为NULL则返回NULL
COUNT(*)
*为通配符,即所有,用于计算数量
SUM(x)
累加
AVG(x)
求平均值
MAX(x)
求最大值
MIN(x)
求最小值
视图
构建并保存
create view MyViewName as select LastName, FirstName, Email, DateOfJoining from MemberDetails;
使用
select * from MemberNameEmail;
用户
创建
create user username identified (by password |Externally| globally as external_name) Options
更改
alter user username options
alter username,...{GRANT|REVOKE} proxy_options}
删除
drop user username[CASCADE]