美文网首页
Ⅱ.sql进阶

Ⅱ.sql进阶

作者: 唐騦忆 | 来源:发表于2019-10-29 21:40 被阅读0次

1.SELECT TOP

规定返回记录数目

mysql
SELECT column_name(s)
FROM table_name
LIMIT number;

oracle
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

2.like

在 WHERE 子句中搜索列中的指定模式

SELECT * FROM Websites WHERE name LIKE 'G%';
SELECT * FROM Websites WHERE name LIKE '%oo%';
SELECT * FROM Websites WHERE name NOT LIKE '%oo%';

3.通配符

通配符

4.in

在where中规定多个值

SELECT column_name(s) FROM table_name
WHERE column_name IN (value1,value2,...);
SELECT * FROM Websites
WHERE name IN ('Google','baidu');

5.between

BETWEEN 操作符选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期。

SELECT column_name(s) FROM table_name
WHERE column_name BETWEEN value1 AND value2;

数值
SELECT * FROM Websites
WHERE alexa BETWEEN 1 AND 20;
SELECT * FROM Websites
WHERE alexa NOT BETWEEN 1 AND 20;

文本
SELECT * FROM Websites
WHERE name BETWEEN 'A' AND 'H';

日期
SELECT * FROM access_log
WHERE date BETWEEN '2016-05-10' AND '2016-05-14';

6.别名

通过使用 SQL，可以为表名称或列名称指定别名。
创建别名是为了让列名称的可读性更强。
在下面的情况下，使用别名很有用：

  1. 在查询中涉及超过一个表
  2. 在查询中使用了函数
  3. 列名称很长或者可读性差
  4. 需要把两个列或者多个列结合在一起
表
SELECT column_name(s) FROM table_name AS alias_name;

列
SELECT column_name AS alias_name FROM table_name;
SELECT name AS n, country AS c FROM Websites;

组合列
SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info FROM Websites;

连表查询
使用别名
SELECT w.name, w.url, a.count, a.date
FROM Websites AS w, access_log AS a
WHERE a.site_id=w.id and w.name="菜鸟教程";
正常语句
SELECT Websites.name, Websites.url, access_log.count, access_log.date
FROM Websites, access_log
WHERE Websites.id=access_log.site_id and Websites.name="菜鸟教程";

7. join

把来自两个或多个表的行结合起来。

7.1 inner join

INNER JOIN 关键字在表中存在至少一个匹配时返回行
INNER JOIN 与 JOIN 是相同的


inner join 
SELECT column_name(s) FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
SELECT Websites.name, access_log.count, access_log.date FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count;

在使用 join 时，on 和 where 条件的区别如下：

  1. on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录。
  2. where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉

7.2 left join

LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。


left join 
SELECT column_name(s) FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
SELECT Websites.name, access_log.count, access_log.date FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count DESC;

7.3 right join

RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。


right join

7.4 full outer join(mysql 不支持)

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行。

SELECT column_name(s) FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;
SELECT Websites.name, access_log.count, access_log.date FROM Websites
FULL OUTER JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count DESC;

8. union

合并两个或多个 SELECT 语句的结果。

去重
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

不去重
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

9. insert into select

SELECT INTO 语句从一个表复制数据，然后把数据插入到另一个新表中。

复制所有的列
INSERT INTO table2
SELECT * FROM table1;

只复制希望的列
INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

10. constraints

SQL 约束用于规定表中的数据规则。
如果存在违反约束的数据行为，行为会被约束终止。
约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

10.1 not null

NOT NULL 约束强制列不接受 NULL 值。
NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

#下面的 SQL 强制 "ID" 列、 "LastName" 列以及 "FirstName" 列不接受 NULL 值：
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);

#在一个已创建的表的 "Age" 字段中添加 NOT NULL 约束如下所示：
ALTER TABLE Persons
MODIFY Age int NOT NULL;

#删除NOT NULL 约束
ALTER TABLE Persons
MODIFY Age int NULL;

10.2 unique

UNIQUE 约束唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
PRIMARY KEY 约束拥有自动定义的 UNIQUE 约束。
每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

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)
)

#如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束:
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)
)

#ALTER TABLE 时的 SQL UNIQUE 约束
ALTER TABLE Persons
ADD UNIQUE (P_Id)
#或
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

#撤销 UNIQUE 约束
ALTER TABLE Persons
DROP INDEX uc_PersonID

10.3 primary key

PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键，并且每个表只能有一个主键。

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

#命名 PRIMARY KEY 约束，并定义多个列的 PRIMARY KEY 约束
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

#ALTER TABLE 时的 SQL PRIMARY KEY 约束
ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)

#命名 PRIMARY KEY 约束，并定义多个列的 PRIMARY KEY 约束
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

#撤销 PRIMARY KEY 约束
ALTER TABLE Persons
DROP PRIMARY KEY

10.4 foreign key

一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)。

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)
)

#ALTER TABLE 时的 SQL FOREIGN KEY 约束
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

#命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

#撤销 FOREIGN KEY 约束
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders
删除未命名外键

10.5 check

CHECK 约束用于限制列中的值的范围。
如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
如果对一个表定义 CHECK 约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制。

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)

#命名 CHECK 约束，并定义多个列的 CHECK 约束
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

#ALTER TABLE 时的 SQL CHECK 约束
ALTER TABLE Persons
ADD CHECK (P_Id>0)

#命名 CHECK 约束，并定义多个列的 CHECK 约束
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

#撤销 CHECK 约束
ALTER TABLE Persons
DROP CHECK chk_Person

10.6 default

DEFAULT 约束用于向列中插入默认值。
如果没有规定其他的值，那么会将默认值添加到所有的新记录。

CREATE TABLE Persons
(
    P_Id int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255) DEFAULT 'Sandnes'
)

#使用类似 GETDATE() 这样的函数，DEFAULT 约束也可以用于插入系统值
CREATE TABLE Orders
(
    O_Id int NOT NULL,
    OrderNo int NOT NULL,
    P_Id int,
    OrderDate date DEFAULT GETDATE()
)

#ALTER TABLE 时的 SQL DEFAULT 约束
ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'

#撤销 DEFAULT 约束
ALTER TABLE Persons
ALTER City DROP DEFAULT

11. 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)

#撤销索引
ALTER TABLE table_name 
DROP INDEX index_name

12.auto_increment

Auto_increment 会在新记录插入表中时生成一个唯一的数字。

CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
)

变更开始值
ALTER TABLE Persons AUTO_INCREMENT=100

13.view

没懂

14.日期

getdate()：返回当前日期

datepart()：返回日期时间


datepart
orders 
SELECT DATEPART(yyyy,OrderDate) AS OrderYear,
DATEPART(mm,OrderDate) AS OrderMonth,
DATEPART(dd,OrderDate) AS OrderDay,
FROM Orders
WHERE OrderId=1

DATEADD()：函数在日期中添加或减去指定的时间间隔。

SELECT OrderId,DATEADD(day,45,OrderDate) AS OrderPayDate
FROM Orders

DATEDIFF()：返回两个日期之间的天数

SELECT DATEDIFF(day,'2008-06-05','2008-08-05') AS DiffDate

CONVERT() 函数是把日期转换为新数据类型的通用函数。
CONVERT() 函数可以用不同的格式显示日期/时间数据。

CONVERT(data_type(length),expression,style)

15.null

#选取所有带null的记录
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL

#选取所有不带null的记录
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL

16. 数据类型

数据类型

相关文章

  • JDBC编程目录

    JDBC编程目录 SQL基础JDBC基础JDBC进阶

  • Ⅱ.sql进阶

    1.SELECT TOP 规定返回记录数目 2.like 在 WHERE 子句中搜索列中的指定模式 3.通配符 4...

  • greenDAO-初探

    SQL进阶地址https://www.runoob.com/sqlite/sqlite-where-clause....

  • 每天一SQL语句（02）：SQL 语句进阶篇-上

    【开篇】SQL 语句进阶篇-上 【1】SQL TOP 子句 top 子句用于规定要返回的记录的数目。 对于拥有数千...

  • SQL进阶1

    CASE函数用法 语法： 1.Case...end 会生成一个新列，作用是判断你指定字段的值或者范围，然后得...

  • SQL进阶2

    --表连接Join-- 查询所有学生的姓名、年龄及所在班级 使用子查询 使用from多表 使用多表连接 表 in...

  • SQL进阶教程

    1神奇的 SQL1-1 case表达式CASE 表达式有简单 CASE 表达式 (simple case expr...

  • SQL语法进阶

    基础语句 1. 创建数据库 CREATE DATABASE database_name 2. 删除数据库 drop...

  • SQL语言进阶

    mysql加强 -- 1 数据约束 -- 非空 -- 唯一 -- 主键（非空+唯一） -- 自增长 -- 外键 -...

  • SQL注入进阶

    绕过条件过滤 条件过滤，顾名思义就是黑名单机制，过滤掉符合条件的语句。因此我们要想办法绕过过滤，用其他方法来实现注...

网友评论

      本文标题：Ⅱ.sql进阶

      本文链接：https://www.haomeiwen.com/subject/mlxnvctx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|Ⅱ.sql进阶|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！