SQL笔记
SQL高级教程
SQL Top
TOP子句用于规定要返回的记录的数目
对于拥有数千条记录的大型表来说,TOP子句是非常有用的。
并非所有的数据库系统都支持TOP子句。
SQL Server的语法:
SELECT TOP number|percent * column_name(s)
FROM table_name
MySQL的语法:
SELECT column_name(S) FROM table_name
LIMIT number
Oracle的语法:
SELCET column_name(s) FROM table_name
WHERE ROWNUM <= number
这里我们看看MySQL的例子
SQL Like
LIKE操作符用于在WHERE子句中搜索列中的指定模式
SQL LIKE操作符语法
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern
pattern是匹配字符,这里涉及到通配符。
让我们来看看一个简单的例子
这里的‘%’就是通配符了
SQL通配符
在搜索数据库中的数据时,SQL通配符可以替代一个或多个字符。
SQL通配符必须与LIKE运算符一起使用。
在SQL中可以使用一下通配符:
通配符 | 描述 |
---|---|
% | 代表零个或多个字符 |
_ | 仅代表一个字符 |
[charlist] | 字符列中的任何单一字符 |
[^charlist]或者[!charlist] | 不在字符列中的任何单一字符 |
测试‘_’和’%'
这里’_‘占了一个字符位,’%'代表零个或多个字符
[charlist]
[ABC]匹配ABC中的任意一个。
[^ABC]或[!ABC]匹配除ABC的任意一个字符。
注意:MySQL的通配符和SQL的通配符有些不太一样
MySQL通配符
通配符 | 描述 |
---|---|
% | 代表零个或多个字符 |
_ | 仅代表一个字符 |
escape | 用来转义 |
前两条都一样,看看第三个escape
例子:
SELECT * FROM people WHERE name LIKE '%明/%' escape '/'
这里/后面的通配符就不作为通配符,而作为普通的字符进行匹配了。
看看结果
SQL IN
IN操作符
IN操作符允许我们在WHERE子句中规定多个值。
SQL IN 语法
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)
SQL BETWEEN
BETWEEN操作符在WHERE子句中使用,作用是选取介于两个值之间的数据范围
BETWEEN … AND 会选取介于两个值之间的数据范围。这些值是可以是数值、文本或者日期。
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2
不同的数据库对BETWEEN … AND操作符的处理是有差异的。某些数据库会包括边界值,某些数据库包括左边界不包括右边界,某些包括右边界,不包括左边界。
mysql包括边界。
SQL Aliases(别名)
通过使用SQL,可以为列名称和表名称指定别名(Alias)
表的SQL Alias语法
SELECT column_name(s)
FROM table_name
AS alias_name
列的SQL Alias语法
SELECT column_name AS alias_name
FROM table_name
SQL JOIN
SQL Join用于根据两个或多个表中的列之间的关系,从这些表中查数据
Join和Key
有时为了得到完整的结果,我们需要从两个或更多的表中获取结果。我们就需要执行join。
数据库中的表可以通过键将彼此联系起来。主键(Primary Key)是一个列,在这个列中的每一行的值都是唯一的。在表中,每个主键的值都是唯一的。这样做的目的是在不重复每个表中的所有数据的情况下,把表间的数据交叉捆绑在一起
引用两个表
我们可以通过引用两个表的方式,从两个表中获取数据。
使用Join
不同的SQL JOIN
除了我们在上面例子中使用的INNER JOIN(内连接),我们还可以使用其他几种连接。
下面列出几种JOIN类型,以及它们之间的差异
-
JOIN:如果表中有至少一个匹配,则返回行(INNER JOIN等价于JOIN)
A JOIN B ON C(条件),如果AB在条件C中存在一个匹配,就返回行,否则为空
-
LEFT JOIN:即使右表中没有匹配,也可以从左表中返回所有的行
A LEFT JOIN B ON C(条件),即使B在C条件下没有与A匹配,也会返回A所有的行
-
RIGHT JOIN:即使左表中没有匹配,也可以从右表中返回所有的行
A RIGHT JOIN B ON C ,即使A在C条件下没有与B匹配,也会返回B所有的行。
-
FULL JOIN:只要其中一个表中存在匹配,就返回行
即使A 与 B在条件C下不存在匹配行,也会返回AB所有的行。
因为MySQL不支持全连接(FULL JOIN),因此使用另外一种。
SQL Union
UNION操作符用于合并两个或多个SELECT语句的结果集。
请注意,UNION内部的SELECT语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时每条SELECT语句中的列的顺序必须相同。
这里什么意思呢。
首先UNION可以合并两个或两个以上的SELECT语句的结果集。然后多条SELECT搜索出的结果的列数相同,并且顺序相同。列也必须拥有相似的数据类型
如果有一个people(name,age)表
需要优先查询年龄在30-40岁之间的人,其他年龄照常显示。
这里很明显是需要两条SELECT语句,第一条查询出年龄在30-40之间的人,第二条是查询其他人,年龄在30-40之间的需要优先显示。
我们先看看第一种情况,两条SELECT不同列。
不同列的情况下会直接报错。
第二种,如果列的顺序不相同会发生什么情况
不报错,也会显示出来
这里我们注意一点BETWEEN的范围会包括边界(既会包括30和40,SQL BETWEEN中提及到mysql中会包括边界),因此我们后面就不需要写等于的情况了。
UNION ALL和UNION的区别是:UNION只会列出不同的值,而UNION ALL允许重复的值
SQL SELECT INTO
SQL SELECT INTO语句可用于创建表的备份复件。
SELECT INTO语句从一个表中选取数据,然后把数据插入了另一个表中。
SELECT INTO语句常用于创建表的备份复件或者用于对记录进行存档。
SELECT INTO语法
把所有的列插入新表
SELECT *
INTO new_table_name[IN externaldatabase]
FROM old_tablename
或者只把希望的列插入新表
SELECT column_name(s)
INTO new_table_name[IN externaldatabase]
FROM old_tablename
SELECT INTO 实例 - 制作备份复件
SELECT *
INTO people_backup
FROM people
mysql 不支持 SELECT INTO
可以改为
CREATE TABLE new_table_name(SELECT * FROM old_table_name)
IN子句可用于向另一个数据库中拷贝表:
SELECT *
INTO people IN 'Backup.mdb'
FROM people
mysql的语句与这个不同
如果我们希望拷贝某些域,可以在SELECT语句后面列出这些域
SELECT part_attribute_name
INTO new_table_name
FROM old_table_name
如果是MySQL
CREATE TABLE new_table_name(SELECT part_attribute_name FROM old_table_name)
带WHERE子句
SELECT *
INTO new_table_name
FROM old_table_name
WHERE condition
如果是mysql
CREATE TABLE new_table_name(
SELECT * FROM old_table_name WHERE condition
)
被链接的表
SELECT part_attribute_name
INTO new_table_name
FROM old_table_name
INNER JOIN join_table_name
ON Condition
如果是mysql
CREATE TABLE new_table_name(
SELECT * FROM old_table_name INNER JOIN
join_table_name ON Condition
)
#当然这里的*得注意两表中是否有冲突的属性
SQL CREATE DB
CREATE DATABASE语句
CREATE DATABASE 用于创建数据库
SQL CREATE DATABASE语法
CREATE DATABASE database_name
现在我们希望创建一个名为"my_db"的数据库
CREATE DATABASE my_db
可以通过CREATE TABLE来添加数据库表
这里留下一个问题,数据库引擎怎么设置?
SQL CREATE TABLE语句
CREATE TABLE语句用来创建数据库中的表
SQL CREATE TABLE语法
CREATE TABLE 表名称
{
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
}
sql中的数据类型
数据类型 | 描述 |
---|---|
Integer(size) | 仅容纳整数。在括号内规定数字的最大位数。 |
int(size) | 同上。 |
smallint(size) | 同上。 |
tinyint(size) | 同上。 |
decimal(size,d) | 容纳带有小数的数字。"size"规定数字最大的位数,"d"规定小数点右侧的最大位数 |
numeric(size,d) | 同上。 |
char(size) | 容纳固定长度的字符串(可容纳字母、数字以及特殊的字符)。在括号中规定字符串的最大长度。 |
date(yyyymmdd) | 容纳日期。 |
SQL CREATE TABLE实例
sql创建数据库
CREATE TABLE Persons
(
Id_P int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
SQL约束(Constraints)
约束用于限制加入表的数据的类型
可以在创建表时规定约束(通过CREATE TABLE语句),或在表创建之后(通过 ALTER TABLE 加约束)
我们主要探讨一下几种约束:
- NOT NULL
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
- CHECK
- DEFAULT
SQL NOT NULL约束
NOT NULL约束强制列不接受NULL值。
NOT NULL约束强制字段始终包含值。这意味着如果不向字段添加值,就无法插入新记录或更新新记录
CREATE TABLE Persons
{
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
}
SQL UNIQUE约束
UNIQUE约束唯一标识数据库表中的每条记录。
UNIQUE和PRIMARY KEY约束均为列或列集合提供唯一性的保证。
PRIMARY KEY拥有自动定义的UNIQUE约束。
请注意,每个表可以有多个UNIQUE约束,但是每个表只能有一个PRIMARY KEY约束。
SQL UNIQUE Constraint on CREATE TABLE
下面的SQL在“Persons”表创建时在Id_P列创建的UNIQUE约束。
MySQL:
CREATE TABLE Persons
{
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE(Id_P)#唯一约束
}
SQL Server / Oracle /MS Access
CREATE TABLE Persons
(
Id_P 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
(
Id_P int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE(Id_P,LastName)
)
SQL UNIQUE Constraint on ALTER TABLE
当表已经被创建时,如需加UNIQUE约束,可以使用以下sql语句
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD UNIQUE(Id_P)
如需命名UNIQUE约束,并定义多个列的UNIQUE约束,请使用以下SQL语法:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE(Id_P,LastName)
#constraint 约束
撤销UNIQUE约束
如需撤销UNIQUE约束,请使用下面的SQL:
MySQL:
MySQL:
ALTER TABLE Persons
DROP INDEX uc_PersonID
#这里的uc_PersonID就是索引名
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID
SQL PRIMARY KEY 约束
PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含NULL值。
每个表都应该有一个主键,并且每个表只能有一个主键。
SQL PRIMARY KEY Constaint on CREATE TABLE
下面SQL在“Persons”表创建时在“Id_P”列创建PRIMARY KEY约束:
MySQL
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Adress varchar(255),
City varchar(255),
PRIMARY KEY(Id_P)
)
SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
Id_P int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Adress varchar(255),
City varchar(255),
)
如果需要命名PRIMARY KEY约束,以及为多个列定义PRIMARY KEY约束,请使用下面的SQL语法。
MySQL / SQL Server / Oracle / MS Access
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Adress varchar(255),
City varchar(255),
CONSTRAINT pk_name PRIMARY KEY(Id_P,LastName)
)
#pk_name 为主键约束名称
SQL PRIMARY KEY Constraint on ALTER TABLE
如果表已存在的情况下创建PRIMARY KEY,请使用以下SQL语法:
MySQL / SQL Server / Oracle / MS Access
ALTER TABLE Persons
ADD PRIMARY KEY (Id_P)
#为Id_P添加主键约束
如果需要命名PRIMARY KEY 约束,以及为多个列定义PRIMARY KEY,
ALTER TABLE Persons
ADD Constraint pk_name PRIMARY KEY(Id_p,LastName)
如果使用ALTER TABLE语句添加主键,必须把主键声明为不包含NULL值(在表首次创建时)
撤销 PRIMARY KEY 约束
如需撤销PRIMARY KEY约束,请使用下面的SQL:
MySQL
ALTER TABLE Persons
DROP PRIMARY KEY
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT pk_name
SQL FOREIGN KEY约束
一个表中的FOREIGN KEY指向另一个表中的PRIMARY KEY。
FOREIGN KEY约束用于预防破坏表之间连接的动作。
**FOREIGN KEY约束也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一。 **
SQL FOREIGN KEY Constraint on CREATE TABLE
下面的SQL在“Orders”表创建时为“Id_P”列创建FOREIGN KEY:
MySQL
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY(Id_O),
FOREIGN KEY(Id_P) REFERENCES Persons(Id_P)
)
SQL Server / Oracle / MS Access
CREATE TABLE Orders
(
Id_O int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
Id_P int FOREIGN KEY REFERENCES Persons(Id_P)
)
如果需要命名FOREIGN KEY约束,已经为多个列定义FOREIGN KEY约束,请使用下面的SQL法:
MySQL / SQL Server / Oracle / MS Access
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY(Id_O),
CONSTRAINT fk_name FOREIGN KEY(Id_P) REFERENCES Persons(Id_P)
)
SQL FOREIGN KEY Constraint on ALTER TABLE
如果在“Orders”表已存在的情况下为“Id_P”列创建FOREIGN KEY约束,请使用下面的SQL:
MySQL / SQL Server / Oracle / MS Access
ALTER TABLE Orders
ADD FOREIGN KEY(Id_p)
REFERENCES Persons(Id_P)
如果需要命名FOREIGN KEY 约束,以及为多个列定义FOREIGN KEY 约束,请使用下面的SQL语句:
MySQL / SQL Server / Oracle / MS Access
ALTER TABLE Orders
ADD CONSTRAINT fk_name
FOREIGN KEY(Id_P)
REFERENCES Persons(Id_P)
撤销FOREIGN KEY约束
如需撤销FOREIGN KEY 约束,请使用下面的SQL:
MySQL
ALTER TABLE Orders
DROP FOREIGN KEY fk_name
SQL Server / Oracle / MS Access
ALTER TABLE Orders
DROP CONSTRAINT fk_name
SQL CHECK 约束
CHECK 约束用于限定列中的值的范围。
如果对单个列定义CHECK约束,那么该列只允许特定的值。
如果对一个表定义CHECK约束,那么此约束会在特定的列中对值进行限制。
SQL CHECK Constraint on CREATE TABLE
下面的SQL在“Persons”表创建时为“Id_P”列创建CHECK约束。CHECK约束规定“Id_P”列必须只包含大于0的整数。
MySQL
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (Id_P>0)
)
SQL Server / Oracle / MS Access
CREATE TABLE Persons
(
Id_P int NOT NULL CHECK(Id_P>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
)
如果需要命名 CHECK 约束,以及为多个列定义 CHECK 约束,请使用下面的 SQL 语法:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
Id_P int NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
Constraint ck_name CHECK(Id_P>0)
)
SQL CHECK Constraint on ALTER TABLE
如果在表已存在的情况下为 “Id_P” 列创建 CHECK 约束,请使用下面的 SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CHECK(Id_P>0)
这里mysql不强制CHECK
如果需要命名 CHECK 约束,以及为多个列定义 CHECK 约束,请使用下面的 SQL 语法:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD Constraint ck_name CHECK(Id_P>0 AND City='Sandnes')
撤销CHECK约束
如需撤销 CHECK 约束,请使用下面的 SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT ck_name
MySQL
ALTER TABLE Persons
DROP CHECK ck_name
SQL DEFAULT 约束
DEFAULT 约束用于向列中插入默认值。
如果没有规定其他值,那么会将默认值添加到所有的新记录。
SQL DEFAULT Constraint on CREATE TABLE
下面的 SQL 在 “Persons” 表创建时为 “City” 列创建 DEFAULT 约束
MySQL / SQL Server / Oracle / MS Access
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)
通过类似GETDATE()这样的函数,DEFAULT约束也可以用于插入系统值:
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
OrderDate date DEFAULT GETDATE()
)
SQL DEFAULT Constraint on ALTER TABLE
如果在表已存在的情况下为 “City” 列创建 DEFAULT 约束,请使用下面的 SQL:
MySQL
ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'
SQL Server / Oracle / MS Access
ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'
撤销DEFAULT约束
MySQL
ALTER TABLE Persons
ALTER City DROP DEFAULT
SQL Server / Oracle / MS Access
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT
SQL CREATE INDEX语句
CREATE INDEX语句用于在表中创建索引。
在不读取整个表的情况下,索引使数据库应用程序可以更快的查找数据。
索引
可以通过在表中创建索引,以便更加快速高效地查询数据。
用户无法看到索引,它们只能被用来加速搜索/查询。
注释:更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间,这是由于索引本身也需要更新。因此,理想的做法是仅仅在常常被搜索的列(以及表)上面创建索引。
SQL CREATE INDEX语法
在表上创建一个简单索引。允许重复值。
CREATE INDEX index_name
ON table_name(column_name)
注释:“column_name” 规定需要索引的列。
SQL CREATE UNIQUE INDEX 语法
在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。
CREATE UNIQUE INDEX index_name
ON table_name(column_name)
如果您希望以降序索引某个列中的值,您可以在列名称之后添加保留字 DESC:
CREATE INDEX PersonIndex
ON Person (LastName DESC)
假如您希望索引不止一个列,您可以在括号中列出这些列的名称,用逗号隔开:
CREATE INDEX PersonIndex
ON Person (LastName, FirstName)
SQL DROP
通过使用DROP语句,可以轻松地删除索引、表和数据库
SQL DROP INDEX语句
我们可以使用DROP INDEX命令删除表格中的索引
用于 Microsoft SQLJet (以及 Microsoft Access) 的语法:
DROP INDEX index_name ON table_name
用于 MS SQL Server 的语法:
DROP INDEX table_name.index_name
用于 IBM DB2 和 Oracle 语法:
DROP INDEX index_name
用于 MySQL 的语法:
ALTER TABLE table_name DROP INDEX index_name
SQL DROP TABLE 语句
DROP TABLE 语句用于删除表(表的结构、属性以及索引也会被删除):
DROP TABLE 表名称
我们可以看到表people_backup3被删除
SQL DROP DATABASE 语句
DROP DATABASE 语句用于删除数据库:
DROP DATABASE 数据库名称
SQL TRUNCATE TABLE 语句
如果我们仅仅需要除去表内的数据,但并不删除表本身,那么我们该如何做呢?
请使用 TRUNCATE TABLE 命令(仅仅删除表格中的数据,这里TRUNCATE是截断的意识):
TRUNCATE TABLE 表名称
接下来我们可以看到表people_backup3的数据被清理了,但是表还在。
SQL ALTER
ALTER TABLE
ALTER TABLE语句用于在已有的表中添加、修改或删除列。
SQL ALTER TABLE语法
如需在表中添加列,请使用下列语法:
ALTER TABLE table_name
ADD column_name datatype
#column_name 列名
#datatype 数据类型
要删除表中的列
ALTER TABLE table_name
DROP COLUMN column_name
**注释:**某些数据库系统不允许这种在数据库表中的输出列的方式(DROP COLUMN column_name)
要改变表中的列的数据类型,请使用下列语法:
ALTER TABLE table_name
ALTER COLUMN column_name datatype
SQL AUTO INCREMENT
Auto-increment会在新纪录插入表中时生成唯一的数字。
AUTO INCREMENT字段
我们通常希望在每次插入新记录时,自动地创建主键字段的值。
我们可以在表中创建一个auto-increment字段。
MySQL语法
下列SQL语句把Persons表中的Id_P列定义为auto-increment
CREATE TABLE Persons
(
Id_P int NOT NULL auto_increment,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY(P_Id)
)
默认地,AUTO_INCREMENT的开始值是1,每条记录递增1
要让AUTO_INCREMENT序列以其他的值起始,请使用下列的SQL语法:
ALTER TABLE Persons AUTO_INCREMENT=100
要在Persons表中插入新记录,我们不必为P_Id规定值(会自动添加一个唯一的值)
当执行全表删除时,仍然是从默认的值开始,但是删除单条或者多条,就是从下一个开始
SQL VIEW (视图)
SQL CREATE VIEW
什么是视图?
在SQL中,视图是基于SQL语句的结果集的可视化的表。
视图包含行和列,就像一个真是的表。视图中的字段就是来自一个或多个数据库中的真实表中的字段。我们可以向视图添加SQL函数、WHERE以及JOIN语句,我们也可以提交数据,就像这些来自于某一个单一的表。
**注释:**数据库的设计和结构不会受到视图中的函数、WHERE或join语句的影响。
SQL CREATE VIEW
CREATE VIEW view_namae AS
SELECT column_name(s)
FROM table_name
WHERE condition
注释: 视图总是显示最近的数据。每当用户查询视图时,数据库引擎通过使用SQL语句来重建数据。
SQL CREATE VIEW实例
可以从某个查询内部、某个存储过程内部、或者从另外一个视图内部来使用视图。通过向视图添加函数、join等等,我们可以向用户精准地提交我们提交的数据。
CREATE VIEW test_view AS
SELECT * FROM s_class WHERE classCode
IN (SELECT classCode FROM s_student GROUP BY classCode HAVING count(1)>20)
```![在这里插入图片描述](https://img-blog.csdnimg.cn/b7adfd496cb14a22ae9b2c43ef2c296e.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6YWS5pqW,size_20,color_FFFFFF,t_70,g_se,x_16)
**SQL更新视图**
可以使用下面语句更新视图
```sql
CREATE OR REPLACE VIEW test_view AS
SELECT * FROM s_class WHERE
classCode IN (SELECT classCode FROM s_student GROUP BY classCode
HAVING count(1)>10)
我们还可以删除列或者添加列
CREATE VIEW test_view AS
SELECT className FROM s_class WHERE
classCode IN (SELECT classCode FROM s_student GROUP BY classCode
HAVING count(1)>10)
SQL撤销视图
可以通过DROP VIEW命令来删除视图
DROP VIEW view_name
SQL Date函数
SQL日期
当我们处理日期时,最难的任务恐怕是确保所插入的日期的格式,与数据库中日期列的格式相匹配。
只要数据包含的只是日期部分,运行查询就不会出问题。但是,如果涉及时间,情况就有点复杂了。
在讨论日期查询的复杂性之前,我们先来看看最重要的内建日期处理函数。
MySQL Date 函数
下面的表格列出了 MySQL 中最重要的内建日期函数:
函数 | 描述 |
---|---|
NOW() | 返回当前的日期和时间 |
CURDATE() | 返回当前的日期 |
CURTIME() | 返回当前的时间 |
DATE() | 提取日期或日期/时间表达式的日期部分 |
EXTRACT() | 返回日期/时间按的单独部分 |
DATE_ADD() | 给日期添加指定的时间间隔 |
DATE_SUB() | 从日期减去指定的时间间隔 |
DATEDIFF() | 返回两个日期之间的天数 |
DATE_FORMAT() | 用不同的格式显示日期/时间 |
SQL Server Date 函数
下面的表格列出了 SQL Server 中最重要的内建日期函数:
函数 | 描述 |
---|---|
GETDATE() | 返回当前日期和时间 |
DATEPART() | 返回日期/时间的单独部分 |
DATEADD() | 在日期中添加或减去指定的时间间隔 |
DATEDIFF() | 返回两个日期之间的时间 |
CONVERT() | 用不同的格式显示日期/时间 |
SQL NULL值
如果表中的某个列是可选的,那么我们可以在不向该列添加值的情况下插入新记录或更新已有的记录。这意味着该字段将以 NULL 值保存。
NULL 值的处理方式与其他值不同。
NULL 用作未知的或不适用的值的占位符。
**注释:**无法比较 NULL 和 0;它们是不等价的。
可以使用IS NULL来查找NULL值。
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL
可是使用IS NOT NULL查找非NULL的值。
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL
SQL isnull()
当数据表中存在null值数据,不利于计算时,我们可以利用isnull函数来处理。
SQL Server 和MS Access用ISNULL(column_name,0)。
Oracle没有ISNULL函数,用NVL(column_name,0)来处理。
MySQL用的是IFNULL(),IFNULL(column_name,0)。
数据类型
Microsoft Access 数据类型
数据类型 | 描述 | 存储 |
---|---|---|
Text | 用于文本或文本与数字的组合。最多 255 个字符。 | |
Memo | Memo 用于更大数量的文本。最多存储 65,536 个字符。注释:无法对 memo 字段进行排序。不过它们是可搜索的。 | |
Byte | 允许 0 到 255 的数字。 | 1 字节 |
Integer | 允许介于 -32,768 到 32,767 之间的数字。 | 2 字节 |
Long | 允许介于 -2,147,483,648 与 2,147,483,647 之间的全部数字 | 4 字节 |
Single | 单精度浮点。处理大多数小数。 | 4 字节 |
Double | 双精度浮点。处理大多数小数。 | 8 字节 |
Currency | 用于货币。支持 15 位的元,外加 4 位小数。提示:您可以选择使用哪个国家的货币。 | 8 字节 |
AutoNumber | AutoNumber 字段自动为每条记录分配数字,通常从 1 开始。 | 4 字节 |
Date/Time | 用于日期和时间 | 8 字节 |
Yes/No | 逻辑字段,可以显示为 Yes/No、True/False 或 On/Off。在代码中,使用常量 True 和 False (等价于 1 和 0)注释:Yes/No 字段中不允许 Null 值 | 1 比特 |
Ole Object | 可以存储图片、音频、视频或其他 BLOBs (Binary Large OBjects) | 最多 1GB |
Hyperlink | 包含指向其他文件的链接,包括网页。 | |
Lookup Wizard | 允许你创建一个可从下列列表中进行选择的选项列表。 | 4 字节 |
MySQL 数据类型
在 MySQL 中,有三种主要的类型:文本、数字和日期/时间类型。
Text 类型:
数据类型 | 描述 |
---|---|
CHAR(size) | 保存固定长度的字符串(可包含字母、数字以及特殊字符)。在括号中指定字符串的长度。最多 255 个字符。 |
VARCHAR(size) | 保存可变长度的字符串(可包含字母、数字以及特殊字符)。在括号中指定字符串的最大长度。最多 255 个字符。注释:如果值的长度大于 255,则被转换为 TEXT 类型。 |
TINYTEXT | 存放最大长度为 255 个字符的字符串。 |
TEXT | 存放最大长度为 65,535 个字符的字符串。 |
BLOB | 用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字节的数据。 |
MEDIUMTEXT | 存放最大长度为 16,777,215 个字符的字符串。 |
MEDIUMBLOB | 用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 字节的数据。 |
LONGTEXT | 存放最大长度为 4,294,967,295 个字符的字符串。 |
LONGBLOB | 用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。 |
ENUM(x,y,z,etc.) | 允许你输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值,则插入空值。注释:这些值是按照你输入的顺序存储的。可以按照此格式输入可能的值:ENUM(‘X’,‘Y’,‘Z’) |
SET | 与 ENUM 类似,SET 最多只能包含 64 个列表项,不过 SET 可存储一个以上的值。 |
Number 类型:
数据类型 | 描述 |
---|---|
TINYINT(size) | -128 到 127 常规。0 到 255 无符号*。在括号中规定最大位数。 |
SMALLINT(size) | -32768 到 32767 常规。0 到 65535 无符号*。在括号中规定最大位数。 |
MEDIUMINT(size) | -8388608 到 8388607 普通。0 to 16777215 无符号*。在括号中规定最大位数。 |
INT(size) | -2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中规定最大位数。 |
BIGINT(size) | -9223372036854775808 到 9223372036854775807 常规。0 到 18446744073709551615 无符号*。在括号中规定最大位数。 |
FLOAT(size,d) | 带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。 |
DOUBLE(size,d) | 带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。 |
DECIMAL(size,d) | 作为字符串存储的 DOUBLE 类型,允许固定的小数点。 |
* 这些整数类型拥有额外的选项 UNSIGNED。通常,整数可以是负数或正数。如果添加 UNSIGNED 属性,那么范围将从 0 开始,而不是某个负数。
Date 类型:
数据类型 | 描述 |
---|---|
DATE() | 日期。格式:YYYY-MM-DD注释:支持的范围是从 ‘1000-01-01’ 到 ‘9999-12-31’ |
DATETIME() | *日期和时间的组合。格式:YYYY-MM-DD HH:MM:SS注释:支持的范围是从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’ |
TIMESTAMP() | *时间戳。TIMESTAMP 值使用 Unix 纪元(‘1970-01-01 00:00:00’ UTC) 至今的描述来存储。格式:YYYY-MM-DD HH:MM:SS注释:支持的范围是从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-09 03:14:07’ UTC |
TIME() | 时间。格式:HH:MM:SS 注释:支持的范围是从 ‘-838:59:59’ 到 ‘838:59:59’ |
YEAR() | 2 位或 4 位格式的年。注释:4 位格式所允许的值:1901 到 2155。2 位格式所允许的值:70 到 69,表示从 1970 到 2069。 |
* 即便 DATETIME 和 TIMESTAMP 返回相同的格式,它们的工作方式很不同。在 INSERT 或 UPDATE 查询中,TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式,比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。
SQL Server 数据类型
Character 字符串:
数据类型 | 描述 | 存储 |
---|---|---|
char(n) | 固定长度的字符串。最多 8,000 个字符。 | n |
varchar(n) | 可变长度的字符串。最多 8,000 个字符。 | |
varchar(max) | 可变长度的字符串。最多 1,073,741,824 个字符。 | |
text | 可变长度的字符串。最多 2GB 字符数据。 |
Unicode 字符串:
数据类型 | 描述 | 存储 |
---|---|---|
nchar(n) | 固定长度的 Unicode 数据。最多 4,000 个字符。 | |
nvarchar(n) | 可变长度的 Unicode 数据。最多 4,000 个字符。 | |
nvarchar(max) | 可变长度的 Unicode 数据。最多 536,870,912 个字符。 | |
ntext | 可变长度的 Unicode 数据。最多 2GB 字符数据。 |
Binary 类型:
数据类型 | 描述 | 存储 |
---|---|---|
bit | 允许 0、1 或 NULL | |
binary(n) | 固定长度的二进制数据。最多 8,000 字节。 | |
varbinary(n) | 可变长度的二进制数据。最多 8,000 字节。 | |
varbinary(max) | 可变长度的二进制数据。最多 2GB 字节。 | |
image | 可变长度的二进制数据。最多 2GB。 |
Number 类型:
数据类型 | 描述 | 存储 |
---|---|---|
tinyint | 允许从 0 到 255 的所有数字。 | 1 字节 |
smallint | 允许从 -32,768 到 32,767 的所有数字。 | 2 字节 |
int | 允许从 -2,147,483,648 到 2,147,483,647 的所有数字。 | 4 字节 |
bigint | 允许介于 -9,223,372,036,854,775,808 和 9,223,372,036,854,775,807 之间的所有数字。 | 8 字节 |
decimal(p,s) | 固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1 之间的数字。p 参数指示可以存储的最大位数(小数点左侧和右侧)。p 必须是 1 到 38 之间的值。默认是 18。s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。 | 5-17 字节 |
numeric(p,s) | 固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1 之间的数字。p 参数指示可以存储的最大位数(小数点左侧和右侧)。p 必须是 1 到 38 之间的值。默认是 18。s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。 | 5-17 字节 |
smallmoney | 介于 -214,748.3648 和 214,748.3647 之间的货币数据。 | 4 字节 |
money | 介于 -922,337,203,685,477.5808 和 922,337,203,685,477.5807 之间的货币数据。 | 8 字节 |
float(n) | 从 -1.79E + 308 到 1.79E + 308 的浮动精度数字数据。 参数 n 指示该字段保存 4 字节还是 8 字节。float(24) 保存 4 字节,而 float(53) 保存 8 字节。n 的默认值是 53。 | 4 或 8 字节 |
real | 从 -3.40E + 38 到 3.40E + 38 的浮动精度数字数据。 | 4 字节 |
Date 类型:
数据类型 | 描述 | 存储 |
---|---|---|
datetime | 从 1753 年 1 月 1 日 到 9999 年 12 月 31 日,精度为 3.33 毫秒。 | 8 bytes |
datetime2 | 从 1753 年 1 月 1 日 到 9999 年 12 月 31 日,精度为 100 纳秒。 | 6-8 bytes |
smalldatetime | 从 1900 年 1 月 1 日 到 2079 年 6 月 6 日,精度为 1 分钟。 | 4 bytes |
date | 仅存储日期。从 0001 年 1 月 1 日 到 9999 年 12 月 31 日。 | 3 bytes |
time | 仅存储时间。精度为 100 纳秒。 | 3-5 bytes |
datetimeoffset | 与 datetime2 相同,外加时区偏移。 | 8-10 bytes |
timestamp | 存储唯一的数字,每当创建或修改某行时,该数字会更新。timestamp 基于内部时钟,不对应真实时间。每个表只能有一个 timestamp 变量。 |
其他数据类型:
数据类型 | 描述 |
---|---|
sql_variant | 存储最多 8,000 字节不同数据类型的数据,除了 text、ntext 以及 timestamp。 |
uniqueidentifier | 存储全局标识符 (GUID)。 |
xml | 存储 XML 格式化数据。最多 2GB。 |
cursor | 存储对用于数据库操作的指针的引用。 |
table | 存储结果集,供稍后处理。 |