SQL学习笔记

一个很有趣的个人博客,不信你来撩 fangzengye.com



在这里插入图片描述

语法

  • use RUNOOB; 命令用于选择数据库。
  • set names utf8; 命令用于设置使用的字符集。
  • SELECT * FROM Websites; 读取数据表的信息。

命令

  • SELECT - 从数据库中提取数据
  • UPDATE - 更新数据库中的数据
  • DELETE - 从数据库中删除数据
  • INSERT INTO 向数据库中插入新数据
  • CREATE DATABASE - 创建新数据库
  • ALTER DATABASE - 修改数据库
  • CREATE TABLE - 创建新表
  • ALTER TABLE - 变更（改变）数据库表
  • DROP TABLE - 删除表
  • CREATE INDEX -创建索引（搜索键）
  • DROP INDEX - 删除索引

SELECT

SELECT column_name,column_name
FROM table_name;

或

SELECT * FROM table_name;

SQL SELECT DISTINCT 语句

在表中，一个列可能会包含多个重复值，有时您也许希望仅仅列出不同（distinct）的值。

SELECT DISTINCT column_name,column_name
FROM table_name;

SQL WHERE 子句

WHERE 子句用于提取那些满足指定条件的记录。

SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

eg

SELECT * FROM Websites WHERE country='CN';

文本字段 vs. 数值字段

SQL 使用单引号来环绕文本值（大部分数据库系统也接受双引号）。

在上个实例中 ‘CN’ 文本字段使用了单引号。

如果是数值字段，请不要使用引号。

WHERE 子句中的运算符

运算符 描述
= 等于
<> 不等于。注释：在 SQL 的一些版本中，该操作符可被写成 !=

z>大于
< 小于
z>= 大于等于
<= 小于等于
BETWEEN 在某个范围内
LIKE 搜索某种模式
IN 指定针对某个列的多个可能值

SQL AND & OR 运算符

SELECT * FROM Websites
WHERE country='CN'
AND alexa > 50;

SELECT * FROM Websites
WHERE country='USA'
OR country='CN';

SELECT * FROM Websites
WHERE alexa > 15
AND (country='CN' OR country='USA');

ORDER BY 关键字

SELECT * FROM Websites
ORDER BY alexa;

降序

SELECT * FROM Websites
ORDER BY alexa DESC;

SELECT * FROM Websites
ORDER BY country,alexa;

INSERT INTO 语句

INSERT INTO 语句用于向表中插入新记录。

INSERT INTO Websites (name, url, alexa, country)
VALUES ('百度','https://www.baidu.com/','4','CN');

UPDATE 语句

UPDATE Websites 
SET alexa='5000', country='USA' 
WHERE name='菜鸟教程';

DELETE 语句

DELETE FROM Websites
WHERE name='百度' AND country='CN';

SELECT TOP, LIMIT, ROWNUM 子句

SELECT TOP 子句

SELECT TOP 子句用于规定要返回的记录的数目。

SELECT TOP 子句对于拥有数千条记录的大型表来说，是非常有用的。
选择前2个

SELECT * FROM Websites LIMIT 2;

LIKE 操作符

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

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

通配符

用于替代字符串中的任何其他字符。
% 替代 0 个或多个字符
_ 替代一个字符
[charlist] 字符列中的任何单一字符
[^charlist]
或
[!charlist] 不在字符列

IN 操作符

符允许您在 WHERE 子句中规定多个值。

SELECT * FROM Websites
WHERE name IN ('Google','菜鸟教程');

BETWEEN 操作符

SELECT * FROM Websites
WHERE alexa BETWEEN 1 AND 20;

NOT BETWEEN 操作符实例

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

SELECT * FROM Websites
WHERE (alexa BETWEEN 1 AND 20)
AND country NOT IN ('USA', 'IND');

带有日期值的 BETWEEN 操作符实例

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

别名

可以为表名称或列名称指定别名。

基本上，创建别名是为了让列名称的可读性更强。

SELECT name AS n, country AS c
FROM Websites;

表的别名实例

使用 “Websites” 和 “access_log” 表，并分别为它们指定表别名 “w” 和 “a”（通过使用别名让 SQL 更简短）：

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="菜鸟教程";

连接(JOIN)

自两个或多个表的行结合起来。
在这里插入图片描述

SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id;

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

INNER JOIN 关键字

INNER JOIN 关键字在表中存在至少一个匹配时返回行

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;

SELECT 列名
FROM 表名
INNER JOIN 表名
ON 表名.列=表名.列
ORDER BY 表名.要排序的列名;

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;

RIGHT JOIN 关键字

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

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;

INSERT INTO SELECT 语句

一个表复制信息到另一个表

INSERT INTO table2
SELECT * FROM table1;

插入到table2

CREATE DATABASE 语句

CREATE DATABASE dbname;

CREATE TABLE 语句

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

约束（Constraints）

用于规定表中的数据规则。

如果存在违反约束的数据行为，行为会被约束终止。

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,
....
);

约束命令

  • NOT NULL - 指示某列不能存储 NULL 值。
  • UNIQUE - 保证某列的每行必须有唯一的值。
  • PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
  • FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
  • CHECK - 保证列中的值符合指定的条件。
  • DEFAULT - 规定没有给列赋值时的默认值。

NOT NULL 约束

约束强制列不接受 NULL 值。

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);

添加 NOT NULL 约束

ALTER TABLE Persons
MODIFY Age int NOT NULL;

删除 NOT NULL 约束

ALTER TABLE Persons
MODIFY Age int NULL;

UNIQUE 约束

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

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 约束

ALTER TABLE Persons
DROP INDEX uc_PersonID

ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

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 约束

MySQL：

ALTER TABLE Persons
DROP PRIMARY KEY

SQL Server / Oracle / MS Access：

ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID

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

ALTER TABLE 时的 SQL CHECK 约束

当表已被创建时，如需在 “P_Id” 列创建 CHECK 约束，请使用下面的 SQL：

ALTER TABLE Persons
ADD CHECK (P_Id>0)

撤销 CHECK 约束

ALTER TABLE Persons
DROP CONSTRAINT chk_Person

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

ALTER TABLE 时的 SQL DEFAULT 约束

表已被创建时，如需在 “City” 列创建 DEFAULT 约束，请使用下面的 SQL：

ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'

撤销 DEFAULT 约束

ALTER TABLE Persons
ALTER City DROP DEFAULT

CREATE INDEX 语句

在表中创建索引。

CREATE INDEX index_name
ON table_name (column_name)

撤销索引、撤销表以及撤销数据库

使用 DROP 语句，可以轻松地删除索引、表和数据库。

用于 MySQL 的 DROP INDEX 语法：

ALTER TABLE table_name DROP INDEX index_name

DROP TABLE 语句

DROP TABLE table_name

DROP DATABASE 语句

DROP DATABASE database_name

TRUNCATE TABLE 语句

仅仅需要删除表内的数据，但并不删除表本身，

TRUNCATE TABLE table_name

ALTER TABLE 语句

用于在已有的表中添加、删除或修改列。

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

视图（Views）

如何创建、更新和删除视图。

CREATE VIEW 语法

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

SQL 更新视图

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

SQL 撤销视图

DROP VIEW view_name

Date 函数

  • NOW() 返回当前的日期和时间
  • CURDATE() 返回当前的日期
  • CURTIME() 返回当前的时间
  • DATE() 提取日期或日期/时间表达式的日期部分
  • EXTRACT() 返回日期/时间的单独部分
  • DATE_ADD() 向日期添加指定的时间间隔
  • DATE_SUB() 从日期减去指定的时间间隔
  • DATEDIFF() 返回两个日期之间的天数
  • DATE_FORMAT() 用不同的格式显示日期/时间

SQL Server Date 函数

  • GETDATE() 返回当前的日期和时间
  • DATEPART() 返回日期/时间的单独部分
  • DATEADD() 在日期中添加或减去指定的时间间隔
  • DATEDIFF() 返回两个日期之间的时间
  • CONVERT() 用不同的格式显示日期/时间

SQL Date 数据类型

MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：

  • DATE - 格式：YYYY-MM-DD
  • DATETIME - 格式：YYYY-MM-DD HH:MM:SS
  • TIMESTAMP - 格式：YYYY-MM-DD HH:MM:SS
  • YEAR - 格式：YYYY 或 YY

SQL Server 使用下列数据类型在数据库中存储日期或日期/时间值：

  • DATE - 格式：YYYY-MM-DD
  • DATETIME - 格式：YYYY-MM-DD HH:MM:SS
  • SMALLDATETIME - 格式：YYYY-MM-DD HH:MM:SS
  • TIMESTAMP - 格式：唯一的数字

通用数据类型

数据类型定义列中存放的值的种类。
请看

用于各种数据库的数据类型

使用的数据类型和范围。
请看

函数

拥有很多可用于计数和计算的内建函数。

Aggregate 函数

计算从列中取得的值，返回一个单一的值。

  • AVG() - 返回平均值
  • COUNT() - 返回行数
  • FIRST() - 返回第一个记录的值
  • LAST() - 返回最后一个记录的值
  • MAX() - 返回最大值
  • MIN() - 返回最小值
  • SUM() - 返回总和

Scalar 函数

基于输入值，返回一个单一的值。

  • UCASE() - 将某个字段转换为大写
  • LCASE() - 将某个字段转换为小写
  • MID() - 从某个文本字段提取字符，MySql 中使用
  • SubString(字段，1，end) - 从某个文本字段提取字符
  • LEN() - 返回某个文本字段的长度
  • ROUND() - 对某个数值字段进行指定小数位数的四舍五入
  • NOW() - 返回当前的系统日期和时间
  • FORMAT() - 格式化某个字段的显示方式

AVG() 函数

通式

SELECT AVG(column_name) FROM table_name
posted @ 2020-03-16 14:36  开源的Boy  阅读(113)  评论(0)    收藏  举报