mysql sql高级教程_SQL高级教程

一、top子句

top子句用于规定要返回的记录的数目

并非所有数据库系统都支持top子句

# sqlserverSELECT TOP number|percent column_name(s) FROMtable_name

# mysqlSELECT column_name(s) FROM table_name LIMIT number# oracleSELECT column_name(s) FROM table_name WHERE ROWNUM <= number

sqlserver top percent实例

# 选取50%内容SELECT TOP 50 PERCENT * FROM persons

二、like操作符实例

# 例1

# 从 "Persons" 表中选取居住在以 "N" 开始的城市里的人：SELECT * FROM Persons WHERE City LIKE 'N%'# 从 "Persons" 表中选取居住在以 "g" 结尾的城市里的人：SELECT * FROM Persons WHERE City LIKE '%g'# 从 "Persons" 表中选取居住在不包含 "lon" 的城市里的人：SELECT * FROM Persons WHERE City NOT LIKE '%lon%'

三、通配符

# 从上面的 "Persons" 表中选取名字的第一个字符之后是 "eorge" 的人SELECT * FROM Persons WHERE FirstName LIKE '_eorge'

# 从上面的 "Persons" 表中选取居住的城市以 "A" 或 "L" 或 "N" 开头的人SELECT * FROM Persons WHERE City LIKE '[ALN]%'

# 从上面的 "Persons" 表中选取居住的城市不以 "A" 或 "L" 或 "N" 开头的人

SELECT * FROM Persons WHERE City LIKE '[!ALN]%'

四、SQL IN操作符

SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)

五、 BETWEEN

# BETWEEN 操作符在 WHERE子句中使用，作用是选取介于两个值之间的数据范围

# 操作符 BETWEEN ... AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2

# 显示范围之外的人

SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Adams' AND 'Carter'

六、 SQL Alias(别名)

# 通过使用 SQL，可以为列名称和表名称指定别名(Alias)

# 表的别名用法

SELECT column_name(s) FROM table_name AS alias_name

# 列的别名用法

SELECT column_name AS alias_name FROM table_name

# 假设我们有两个表分别是："Persons" 和 "Product_Orders"。我们分别为它们指定别名 "p" 和 "po"。

SELECT PO.orderID, p.lastname, p.firstname FROM Persons AS p, Product_orders AS po WHERE p.LastName='Adams' AND p.FirstName='John'

# 使用一个列名别名

SELECT LastName AS Family, FirstName AS Name FROM Persons

七、 JOIN

# join用于根据两个或多个表中的列之间的关系，从这些表中查询数据

# 数据库的表可通过键将彼此联系起来

# 可以通过引用两个表的方式来获取数据

SELECT persons.lastname, persons.firstname, orders.orderNO FROM persons,orders

WHERE persons.ID = orders.ID

# 使用关键词 JOIN 来从两个表中获取数据

SELECT persons.lastname, persons.firstname, orders.orderNO FROM persons

INNER JOIN orders ON persons.ID = orders.ID ORDERBY persons.lastnam

# 不同的SQL JOIN上面使用的INNERJOIN(内连接)

还有这些join类型JOIN: 如果表中至少一个匹配，则返回行

LEFT_JOIN: 即使右表中没有匹配，也从左表返回所有的行

RIGHT_JOIN: 即使左表中没有匹配，也从右表返回所有的行FULL JOIN: 只要其中一个表存在匹配，就返回行

八、INNER JOIN

# 当表中至少存在一个匹配时， inner join关键字返回行， innerjoin和join是相同的SELECTcolumn_name(s)FROMtable_name1INNER JOINtable_name2ON table_name1.column_name=table_name2.column_name

九、LEFT JOIN

# left join关键字会从左表那里返回所有的行，即使在右表中没有破匹配的行SELECTcolumn_name(s)FROMtable_name1LEFT JOINtable_name2ON table_name1.column_name=table_name2.column_name

十、RIGHT JOIN

# RIGHT JOIN关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。SELECTcolumn_name(s)FROMtable_name1RIGHT JOINtable_name2ON table_name1.column_name=table_name2.column_name

十一、FULL JOIN

# 只要其中某个表存在匹配， FULLJOIN关键字就会返回行SELECTcolumn_name(s)FROMtable_name1FULL JOINtable_name2ON table_name1.column_name=table_name2.column_name

十二、 UNION 和 UNION ALL 操作符

# UNION操作用于合并两个或多个select 语句的结果集

#union# 默认的，union操作符选取不同的值，如果允许重复的值，应该使用unionall

SELECT column_name(s) FROMtable_name1UNION

SELECT column_name(s) FROM table_name2

# union all

SELECT column_name(s) FROM table_name1

UNION ALL

SELECT column_name(s) FROM table_name2

十三、select into

# selectinto语句用于创建表的备份复件

#selectinto从一个表中选取数据，然后插入另一个表

# 把所有列插入新表SELECT *

INTO new_table_name [IN externaldatabase]

FROMold_tablename

# 只把希望的列插入新表SELECTcolumn_name(s)INTO new_table_name [IN externaldatabase]

FROMold_tablename

# 下面的例子会制作 "Persons" 表的备份复件：SELECT *

INTOPersons_backupFROMPersons

#IN子句可用于向另一个数据库中拷贝表：SELECT *

INTO Persons IN 'Backup.mdb'

FROM Persons

十四、CREATE DB

# 创建数据库CREATE DATABASEdzp;

# 查看数据库

SHOW DATABASES;

# 查看当前数据库select database();CREATE DATABASE IF NOT EXISTSt1;

SHOW WARNNINGS;

十五、create table

CREATE TABLE表名称

(

列名称1 数据类型,

列名称2 数据类型,

列名称3 数据类型,

....

)

# 例子CREATE TABLEPersons

(

Id_Pint,

LastNamevarchar(255),

FirstNamevarchar(255),

Addressvarchar(255),

Cityvarchar(255)

)

十六、约束 constraints

# 约束用于限制加入表的数据的类型

# 可以在创建表的时候规定约束，通过CREATE TABLE语句创建

# 或者在表创建之后也可以，通过ALTER TABLE语句修改

# 有以下几种约束NOT NULL

UNIQUE

PRIMARY KEY

FOREIGN KEY

CHECK

DEFAULT

十七、NOT NULL

# NOTNULL约束强制列不接受null值，强制字段始终包含值

# 这就意味着，如果不向字段添加值，就无法插入新记录或者是修改记录CREATE TABLEPersons

(

Id_Pint NOT NULL,

LastNamevarchar(255) NOT NULL,

FirstNamevarchar(255),

Addressvarchar(255),

Cityvarchar(255)

)

十八、UNIQUE约束

# UNIQUE约束唯一表示数据库表中的每条记录

#UNIQUE 和 PRIMARYKEY约束均为列或列集合提供了唯一性的保证

#PRIMARYKEY拥有自动定义的UNIQUE约束

# 注意！！每个表可以有多个UNIQUE约束，但是每个表只能有一个PRIMARY KEY约束

# Mysql 创建UNIQUE约束CREATE TABLEPersons

(

Id_Pint NOT NULL,

LastNamevarchar(255) NOT NULL,

FirstNamevarchar(255),

Addressvarchar(255),

Cityvarchar(255),UNIQUE(Id_P)

)

十九、primary key 主键约束

# primarykey约束唯一表示数据库表中的每条记录

# 主键必须包含唯一的值

# 主键列不能包含null值

# 每个表都应该有一个主键，并且每个表只能有一个主键

# mysql 创建表的时候创建主键CREATE TABLEPersons

(

Id_Pint NOT NULL,

LastNamevarchar(255) NOT NULL,

FirstNamevarchar(255),

Addressvarchar(255),

Cityvarchar(255),PRIMARY KEY(Id_P)

)

# mysql/ SQL Server / Oracle /MS Access:CREATE TABLEPersons

(

Id_Pint NOT NULL PRIMARY KEY,

LastNamevarchar(255) NOT NULL,

FirstNamevarchar(255),

Addressvarchar(255),

Cityvarchar(255)

)

# 表已存在的时间添加主键，如果您使用 ALTER TABLE 语句添加主键，必须把主键列声明为不包含 NULL 值(在表首次创建时)

ALTER TABLE Persons

ADD PRIMARY KEY (Id_P)

# 撤销主键约束

ALTER TABLE Persons

DROP PRIMARY KEY

二十、外键约束 FOREIGN KEY

# 父表 persons 子表 orders

# persons中的Id_P是父表的primarykey# orders中的Id_P是字表中的foreignkey# 外键约束用于预防破坏表之间连接的动作

# 外键约束也能预防非法数据插入外键列，因为他必须指向父表中的值之一

# 在创建子表的时候创建外键CREATE TABLEOrders

(

Id_Oint NOT NULL,

OrderNoint NOT NULL,

Id_Pint,PRIMARY KEY(Id_O),FOREIGN KEY (Id_P) REFERENCESpersons(Id_P)

}

# 在子表已经存在的情况下创建外键约束

ALTER TABLE ORDERS ADD FOREIGN KEY(Id_P) REFERENCES persons (Id_P)

# 如果需要命名FOREIGN KEY约束，以及为多个列定义外键约束

ALTER TABLE orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)

# 撤销外键约束

ALTER TABLE orders DROP FOREIGN KEY fk_PerOrders

二十一、check 约束

# CHECK 约束用于限制列中的值的范围。

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

-> );

Query OK, 0 rows affected (0.02 sec)

# 定义多个列CHECK约束

mysql> CREATE TABLE persons1(

-> Id_P int NOT NULL,

-> LastName varchar(255) NOT NULL,

-> City varchar(255),

-> CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')

-> );

Query OK, 0 rows affected (0.03 sec)

# 已存在表，为Id_P创建CHECK约束

ALTER TABLE Persons ADD CHECK(Id_P>0)

# 为多个列创建CHECK约束

ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')

# 撤销CHECK约束

ALTER TABLE Persons DROP CHECK chk_Perso

二十二、DEFAULT约束

# detault约束

# default约束用于向列中插入默认值

#CREATE TABLEPersons

(

Id_Pint NOT NULL,

LastNamevarchar(255) NOT NULL,

FirstNamevarchar(255),

Addressvarchar(255),

Cityvarchar(255) DEFAULT 'Sandnes')

# 通过使用类似GETDATE() 这样的函数，DEFAULT约束也可以用于插入系统值：CREATE TABLEOrders

(

Id_Oint NOT NULL,

OrderNoint NOT NULL,

Id_Pint,

OrderDate dateDEFAULT GETDATE()

)

# 如果在表已存在的情况下为 "City" 列创建DEFAULT约束ALTER TABLE Orders ALTER City SET DEFAULT 'Fuzhou'# 撤销defaultALTER TABLE Orders ALTER City DROP DEFAULT

二十三、 CREATE INDEX 索引

# createindex语句用于在表中创建索引

# 在不读取整个表的情况下，索引使数据库应用程序可以更快的查找数据

# 可以在表中创建索引，一遍更加快速高效的查询数据

# 用户无法看到索引，他们只能用来加速搜索/查询

#更新一个包含索引的表需要比更新一个没有索引的表 要花更多的时间，这是因为索引本身也需要更新，因此理想的做法是：仅仅在常常被搜索的列(以及表)上面创建索引

# 语法

在表上创建一个简单的索引，允许使用重复的值, column_name对顶需要索引的列CREATE INDEX index_name ONtable_name (column_name)

# 创建唯一索引CREATE UNIQUE INDEX index_name ONtable_name (column_name)

# 创建索引CREATE INDEXPersonIndexONPerson (LastName)

# 降序索引某个列中的值，在列名称之后添加保留字DESC

CREATE INDEXPersonIndexON Person (LastName DESC)

# 索引不止一个列可以在括号中列出这些列的名称，用逗号隔开：CREATE INDEXPersonIndexON Person (LastName, FirstName)

二十四、DROP语句

# 通过使用 DROP语句，可以轻松地删除索引、表和数据库。ALTER TABLE table_name DROP INDEXindex_nameDROP TABLE表名称DROP DATABASE数据库名称

# 如果我们仅仅需要除去表内的数据，但并不删除表本身，那么我们该如何做呢？

#truncate table表名称

# 仅仅是删除表格中的数据TRUNCATE TABLE 表名称

二十五、ALTER语句

# 添加ALTER TABLEtable_nameADDcolumn_name datatype

# 删除ALTER TABLEtable_nameDROPcolumn_name datatype

# 在表 "Persons" 中添加一个名为 "Birthday" 的新列ALTER TABLE Persons ADDBirthday date

# 改变 "Persons" 表中 "Birthday" 列的数据类型ALTER TABLE Persons ALTER CLOUMN Birthday year# 删除 "Person" 表中的 "Birthday" 列ALTER TABLE Persons DROP COLUMN Birthday

二十六、AUTO_INCREMENT

# 我们通常希望在每次插入新记录时，自动地创建主键字段的值CREATE TABLEPersons

(

P_Idint NOT NULL PRIMARY KEYAUTO_INCREMENT,

LastNamevarchar(255) NOT NULL,

FirstNamevarchar(255),

Addressvarchar(255),

Cityvarchar(255)

)

MySQL 使用 AUTO_INCREMENT 关键字来执行 auto-increment 任务。

默认地，AUTO_INCREMENT 的开始值是1，每条新记录递增 1。

要让 AUTO_INCREMENT 序列以其他的值起始，请使用下列 SQL 语法：ALTER TABLE Persons AUTO_INCREMENT=100

二十七、视图 VIEW

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

# 视图是基于SQL语句结果集的可视化的表

# 视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN语句，我们也可以提交数据，就像这些来自于某个单一的表。

# 数据库的设计和结构不会受到视图中的函数、where 或 join语句的影响CREATE VIEW view_name AS

SELECTcolumn_name(s)FROMtable_nameWHEREcondition

# 使用以下语法来更新视图

SQLCREATE OR REPLACE VIEWSyntaxCREATE OR REPLACE VIEW view_name AS

SELECTcolumn_name(s)FROMtable_nameWHERE condition

# DROP VIEW 命令来删除视图

SQL DROP VIEW Syntax

DROP VIEW view_name

DROP VIEW IF EXISTS view_name

二十八、mysql数据类型

# mysql中有三种主要的类型：文本、数字、日期/时间

# text类型CHAR(size)VARCHAR(size)

TINYTEXT 存放最大长度为255个字符的字符串

ENUM(x,y,z,etc.) 允许你输入可能值的列表。可以在 ENUM 列表中列出最大65535个值。如果列表中不存在插入的值，则插入空值。(枚举)

# Number类型TINYINT(size)SMALLINT(size)INT(size)BIGINT(size)FLOAT(size,d)DOUBLE(size,d)

# date类型

DATE()TIMESTAMP()

TIME()YEAR()