数据库高级操作
SQL SELECT TOP, LIMIT, ROWNUM 子句
SELECT TOP 子句用于规定要返回的记录的数目。
SELECT TOP 子句对于拥有数千条记录的大型表来说,是非常有用的。
MySQL语法:
SELECT column_name(s)
FROM table_name
LIMIT number;
实例1:
返回前五条数据:
SELECT *
FROM student
LIMIT 5;
执行结果:
实例二:
变相返回后 N 行:
SELECT *
FROM student
ORDER BY id DESC
LIMIT 5;
查询按照降序排列的student表前五行数据(也就是正常表的后五行)
执行结果:
Oracle语法:
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;
实例:
SELECT *
FROM Persons
WHERE ROWNUM <=5;
SQL LIKE 操作符
LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。
SQL LIKE 语法
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
SQL LIKE 操作符实例
SELECT * FROM student
WHERE uname LIKE 'z%';
查询以z开头的uname字段数据
***提示***:"%" 符号用于在模式的前后定义通配符(默认字母)。
执行结果:
实例二:
SELECT * FROM student
WHERE uname LIKE '%m';
查询以m结尾的uname字段数据
执行结果:
实例三:
SELECT * FROM student
WHERE uname LIKE '%oo%';
选取 uname 包含模式 "ao" 的所有学生数据:
执行结果:
![在这里插入图片描述](https://img-blog.csdnimg.cn/2020110310215168.png#pic_center
SQL LIKE 操作符实例模糊匹配字段
%=======>替代 0 个或多个字符
'%a' //以a结尾数据
'a%' //以a开头的数据
'%a%' //含有a的数据
_========>替代一个字符
'_a_' //三位且中间字母是a的
'_a' //两位且结尾字母是a的
'a_' //两位且开头字母是a的
SQL IN 操作符
IN 操作符允许您在 WHERE 子句中规定多个值。
SQL IN 语法
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);
IN 操作符实例
SELECT * FROM student
WHERE uname IN ('xm','zbz');
执行结果:
SQL BETWEEN 操作符
BETWEEN 操作符选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期。
SQL BETWEEN 语法
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
BETWEEN 操作符实例
下面的 SQL 语句选取 bjid介于 1 和 5 之间的所有学生数据:
实例:
SELECT * FROM student
WHERE bjid BETWEEN 1 AND 5;
执行结果:
NOT BETWEEN 操作符实例
如需显示不在上面实例范围内的网站,请使用 NOT BETWEEN:
实例
SELECT * FROM student
WHERE bjid NOT BETWEEN 5 AND 10
ORDER BY id DESC;
显示不在bjid不在5-10之间的数据,并按照id降序进行排列
执行结果:
带有 IN 的 BETWEEN 操作符实例
下面的 SQL 语句选取 alexa 介于 1 和 20 之间但 country 不为 USA 和 IND 的所有网站:
实例
SELECT * FROM student
WHERE (bjid BETWEEN 1 AND 10)
AND STATUS NOT IN (1);
查询bjid在1-10直接 并且status值不为1的值
执行结果:
带有文本值的 BETWEEN 操作符实例
下面的 SQL 语句选取 uname 开头以介于 ‘x’ 和 ‘z’ 之间字母的所有学生信息:
SELECT * FROM student
WHERE uname BETWEEN 'x' AND 'z';
执行结果:
带有日期值的 BETWEEN 操作符实例
SELECT * FROM student
WHERE savetime BETWEEN '2012-02-09 22:45:58' AND '2012-02-09 22:47:05';
执行结果:
SQL 连接(JOIN)
SQL JOIN 子句用于把来自两个或多个表的行结合起来,基于这些表之间的共同字段。
最常见的 JOIN 类型:SQL INNER JOIN(简单的 JOIN)。 SQL INNER JOIN 从多个表中返回满足 JOIN 条件的所有行。
SQL INNER JOIN 关键字
INNER JOIN 关键字在表中存在至少一个匹配时返回行。只返回两个表中连接字段相等的行
SQL INNER JOIN 语法
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
或者
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
SQL INNER JOIN 实例
SELECT s.uname, b.bjname, s.tname,s.savetime,b.id
FROM student AS s
INNER JOIN banji AS b
ON s.bjid=b.id
ORDER BY b.id DESC;
表单数据:
student表:
banji表:
执行结果:
注释:INNER JOIN 关键字在表中存在至少一个匹配时返回行。
如果 "Websites" 表中的行在 "access_log" 中没有匹配,则不会列出这些行。
例如:
SELECT s.uname, b.bjname, s.tname,s.savetime,b.id
FROM student AS s
INNER JOIN banji AS b
ON s.upass=b.id
ORDER BY b.id DESC;
左表中的upass在右表中的id没有匹配到相同数据,则不返回
执行结果:
SQL LEFT JOIN 关键字
LEFT JOIN 关键字从左表(table1)返回所有所查询的行,右表(table2)中有匹配时,则同时返回右表中该匹配数据所查询的行。如果右表中没有匹配,则返回右表中所查询的行为 NULL。
SQL LEFT JOIN 语法
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
或:
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;
SQL LEFT JOIN 实例
SELECT s.uname, b.bjname, b.savetime, s.tname,s.savetime,b.id AS bjid
FROM student AS s
LEFT JOIN banji AS b
ON s.bjid=b.id
ORDER BY b.id DESC;
代码解读:
查询出student表中的uname,tname,savetime字段数据返回一行
匹配student表中的bjid与banji表中的id字段
若是匹配到数据则在此行返回banji表中的bjname,savetime,id字段
若是匹配不到则在此行返回banji表中的bjname,savetime,id字段数据为NULL
运行结果:
SQL RIGHT JOIN 关键字
RIGHT JOIN 关键字从右表(table2)返回所有所查询的行,左表(table1)中有匹配时,则同时返回左表中该匹配数据所查询的行。如果左表中没有匹配,则返回左表中所查询的行为 NULL。
SQL RIGHT JOIN 语法
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
或:
SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;
SQL RIGHT JOIN 实例
SELECT s.uname, b.bjname, b.savetime, s.tname,s.savetime,b.id AS bjid
FROM student AS s
RIGHT JOIN banji AS b
ON s.bjid=b.id
ORDER BY b.id DESC;
代码解读:
与left join类似
运行结果:
SQL UNION 操作符
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意,UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每个 SELECT 语句中的列的顺序必须相同。
SQL UNION 语法
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
注释:默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。
SQL UNION ALL 语法
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
注释:UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
SQL SELECT INTO 语句
通过 SQL,您可以从一个表复制信息到另一个表。
SELECT INTO 语句从一个表复制数据,然后把数据插入到另一个新表中。
**注意**:
MySQL 数据库不支持 SELECT ... INTO 语句,但支持 INSERT INTO ... SELECT 。
当然你可以使用以下语句来拷贝表结构及数据:
INSERT INTO abc(id,uname,pwd) SELECT id,uname,pwd FROM sysuser;
执行前:
执行后:
CREATE TABLE 新表
AS
SELECT * FROM 旧表
实例:
CREATE TABLE abc
AS
SELECT id,uname,pwd FROM sysuser;
SQL 约束(Constraints)
SQL 约束用于规定表中的数据规则。
如果存在违反约束的数据行为,行为会被约束终止。
约束可以在创建表时规定(通过 CREATE TABLE 语句),或者在表创建之后规定(通过 ALTER TABLE 语句)。
在 SQL 中,我们有如下约束:
NOT NULL - 指示某列不能存储 NULL 值。
实例:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);
添加 NOT NULL 约束
在一个已创建的表的 “Age” 字段中添加 NOT NULL 约束如下所示
实例:
ALTER TABLE Persons
MODIFY Age int NOT NULL;
删除 NOT NULL 约束
在一个已创建的表的 “Age” 字段中删除 NOT NULL 约束如下所示:
实例:
ALTER TABLE Persons
MODIFY Age int NULL;
UNIQUE - 保证某列的每行必须有唯一的值。
UNIQUE 约束唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
PRIMARY KEY 约束拥有自动定义的 UNIQUE 约束。
请注意,每个表可以有多个 UNIQUE 约束,但是每个表只能有一个 PRIMARY KEY 约束。
CREATE TABLE 时的 SQL UNIQUE 约束
MySQL:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)
SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
如需命名 UNIQUE 约束,并定义多个列的 UNIQUE 约束,请使用下面的 SQL 语法:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)
--创建表,并创建约束
CREATE TABLE tb2(
tb2_id INT UNIQUE,
tb2_name VARCHAR(20),
tb2_age INT,
UNIQUE(tb2_name)
);
SELECT * FROM tb2;
INSERT INTO tb2(tb2_id,tb2_name,tb2_age) VALUES (1,'张三',20);
--违反唯一约束
INSERT INTO tb2 VALUES(2,'张三',25);
--去除约束
ALTER TABLE tb2
DROP INDEX tb2_name;
--删除约束后,允许存在多个tb2_name为张三的记录
INSERT INTO tb2 VALUES(2,'张三',25);
--添加多个约束
ALTER TABLE tb2
ADD CONSTRAINT us_tb2Id UNIQUE (tb2_id,tb2_name);
--删除多个约束
ALTER TABLE Persons
DROP INDEX us_tb2Id;
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。
确保某列(或两个列多个列的结合)有唯一标识,有助于更容易更快速地找到表中的一个特定的记录。
操作与UNIQUE类似。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
MySQL:
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)
SQL Server / Oracle / MS Access:
CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)
操作与UNIQUE类似。
create table father(
f_no number(2));--插入数据1 2 3 4
create table son(
s_no number(2),
f_no number(2))--插入两列1 2 3 4
--删除父表数据
delete from father where f_no=1 --报错违反约束
--删除父表
drop table father --报错违反约束
--更新父表数据
--禁用约束可以更新ALTER TABLE son disable constraint f_f;
update father set f_no =7 where f_no=1--报错违反约束
--对父表插入数据
insert into father values(6)--可以插入
--删除子表数据
delete from son where s_on=1--可以删除
--删除子表
drop table son--可以删除
--更新子表数据
update son set s_no=6 where s_no=1 --可以更新
update son set f_no=6 where s_no=6 --可以更新
--对子表插入数据
insert into son values(7,7)--父表没有7,违反约束
insert into son values(1,1)--父表有1可以插入
insert into son values(8,1)--父表有1可以插入
总结:
一、删除时,未指定cascade时
1)删除父表/数据
a.因为子表与父表一一对应,删除父表数据时,需要先把子表对应数据删除否则无法删除
b. 同理,删除表的时候,也需要先删除子表再删除父表
解决方案:
a.指定cascade,删除父表、数据
CASCADE指当删除主表中被引用列的数据时,级联删除子表中相应的数据行。
b.禁用约束(子表的外键约束)
ALTER TABLE 表名 disable constraint 约束名;
2)删除子表:可以删除子表或者数据不报错
二、更新时
a.更新父表会违反约束
b.可以更新子表
c.没有针对约束的级联更新
三、插入时
a.父表可以插入
b.子表插入会违反约束
CHECK - 保证列中的值符合指定的条件。
CHECK 约束用于限制列中的值的范围。
如果对单个列定义 CHECK 约束,那么该列只允许特定的值。
如果对一个表定义 CHECK 约束,那么此约束会基于行中其他列的值在特定的列中对值进行限制。
DEFAULT - 规定没有给列赋值时的默认值。
DEFAULT 约束用于向列中插入默认值。
如果没有规定其他的值,那么会将默认值添加到所有的新记录。
CREATE TABLE tb4(
tb4_id INT,
tb4_name VARCHAR(20),
tb4_age INT DEFAULT 10
);
INSERT INTO tb3 (tb3_id,tb3_name)VALUES (2,'李四') ;
没有插入tb4_age 的值,则默认为‘10’;
SQL CREATE INDEX 语句(索引)
CREATE INDEX 语句用于在表中创建索引。
在不读取整个表的情况下,索引使数据库应用程序可以更快地查找数据。
索引
您可以在表中创建索引,以便更加快速高效地查询数据。
用户无法看到索引,它们只能被用来加速搜索/查询。
注释:更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间,这是由于索引本身也需要更新。因此,理想的做法是仅仅在常常被搜索的列(以及表)上面创建索引。
SQL CREATE INDEX 语法
在表上创建一个简单的索引。允许使用重复的值:
CREATE INDEX index_name
ON table_name (column_name)
SQL CREATE UNIQUE INDEX 语法
在表上创建一个唯一的索引。不允许使用重复的值:唯一的索引意味着两个行不能拥有相同的索引值。
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
CREATE INDEX 实例
下面的 SQL 语句在 “Persons” 表的 “LastName” 列上创建一个名为 “PIndex” 的索引:
CREATE INDEX PIndex
ON Persons (LastName)
如果您希望索引不止一个列,您可以在括号中列出这些列的名称,用逗号隔开:
CREATE INDEX PIndex
ON Persons (LastName, FirstName)