SQL基础
SQL DML和DDL
可以把Sql分为两部分:数据操作语言(DML)和数据定义语言(DDL)
查询和更新构成了SQL的DML
SELECT、UPDATE、DELETE、INSERT INTO
SQL的定义语言使我们能够创建和删除表格,也可以定义索引(键),规定表链接
CREATE DATABASE
ALTER DATABASE
CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE INDEX
DROP INDEX
SQL SELECT语法
SELECT 列名 FROM 表名
SELECT * FROM 表名
SQL DISTINCT
DISTINCT语句用于对选取的列进行去重。
如:SELECT DISTINCT name FROM tb_person;
SQL WHERE
WHERE有条件的选取数据
SELECT 列名 FROM 表 WHERE 列 运算符 值
运算符:=、<>、<、<=、>、>=、BETWEENT、LIKE
SQL使用单引号(或双引号)来环绕文本的,数值则不需要
SELECT * FROM tb_person WHERE city='Beijing'
SELECT * FROM tb_person WHERE month>=5
AND 和 OR可以将多个条件联合起来进行判断,如果是复杂条件和二者接合使用,注意使用括号组织
SELECT * FROM tb_person WHERE city='Beijing' OR (city='Shanghai' AND month=5)
ORDER BY
ORDER BY对指定列的结果集进行排序,默认是升序排序ASC,降序是DESC
如:以name升序,相同字段使用year使用降序排序。
SELECT name,year FROM tb_person WHERE ORDER BY name ASC,year DESC;
INSERT INTO
向表格中插入新的行
INSERT INTO table_name (col1,col2...) VALUES (value1,value2...);
如果按照列名全部插入的话,col1、col2这个列字段可以省去
INSERT INTO tb_person (name,year,address) VALUES ('colin',23,'china');
UPDATE
修改表中数据
UPDATE table_name SET col_name=value WHERE col_name=value
UPDATE tb_person SET year=23 WHERE name='colin';
DELETE
删除表中的行
DELETE FROM tb_name WHERE col_name=value
DELETE FROM Person WHERE col_name='colin';
删除表中所有行
DELETE FROM table_name;或者DELETE * FROM table_name;
SQL高级操作
TOP子句用于规定返回的记录数目
SELECT TOP nums|percent FROM table_name WHERE clo_name=value
Mysql中的LIMIT和Oracle中的ROWNUM是等价的
SELECT TOP 10 * FROM table_name
SELECT TOP 50 PERCENT * FROM table_name;
SELECT * FROM table_name LIMIT number;
Oracle中语法:
SELECT * FROM table_name ROWNUM<=number;
LIKE
LIKE操作符用于在WHERE子句中搜索列中的匹配模式
SELECT column_names FROM table_name WHERE column_name LIKE partter
SELECT * FROM tb_person WHERE name LIKE yang%;
通配符:
%:替代一个或任意多个字符
_:仅替代一个字符
[charlist]:字符序列中的任意一个字符
[^charlist]:不在字符序列中的任意一个字符
SQL IN
IN操作符允许我们在WHERE子句中规定多个值
SELECT column_names FROM table_name WHERE column_name IN (value1,value2,....);
SELECT * FROM table_name WHERE name IN ('colin','yang');
BETWEEN...AND
BETWEEN...AND 会选取两个值之间的范围,这个可以是数值、文本或时间
SELECT column_names FROM table_name WHERE column_name BETWEENT vlue1 AND value2;
根据数据库的不同,BETWEENT...AND的范围有可能包括value1、value2,也有可能不包括
SQL Alias
有表别名和列别名,使用别名能够使SQL语句可读性更强
SELECT column_name AS as_name FROM table_name AS as_name WHERE ...
SELECT p.name,s.sco FROM tb_person AS p,tb_score AS s WHERE p.name='colin' AND s.name='Scala';
SQL JOIN
将多个表联合查询,有内连接、外连接
INNER JOIN(内连接)两个表中都有匹配才会返回
SELECT column_names FROM table_name1 INNER JOIN table_name2 ON table_name1.id=table_name2.id;
JOIN与INNER JOIN是相同的,代表内连接
LEFT JOIN左连接,返回左表中查找的所有行,右边表即使没有也要返回(返回空值)
SELECT column_names FROM table_name1 LEFT JOIN table_name2 ON table_name1.id=table_name2.id;
RIGHT JOIN返回查找的右边表的所有列,即使左边表中没有也要返回(返回空值)
SELECT column_names FROM table_name1 RIGHT JOIN table_name2 ON table_name1.id=table_name2.id;
FULL JOIN也称FULL OUTER JOIN
只要某个表中存在对应的行,无论另一个表中是否存在都会返回
SELECT column_names FROM table_name1 FULL JOIN table_name2 ON table_name1.id=table_name2.id;
SQL UNION
合并多个SELECT语句的结果集,但是需要保证每个SELECT后面的数量、类型相同,并且UNION的结果集中列名与第一个SELECT的列名相同。
SELECT column_names FROM table_name1 UNION SELECT column_names FROM table_name2
UNION的结果集会去重,仅保留不同的结果。列出所有的可以使用UNION ALL。
SELECT INTO
将查询的结果集插入到另一个表中
SELECT columns_names INTO new_table [IN new_db] FROM table_name WHERE ....
SELECT p.name,s.score INTO tb_rs FROM tb_person AS p INNER JOIN tb_score AS s ON tb_score AS s;
CREATE DB
创建数据库
CREATE DATABASE database_name;
CREATE TABLE
创建表
CREATE TABLE table_name (
column_name1 type1,
column_name2 type2,
column_name3 type3
....
)
SQL 约束
在创建表时进行约束,或者是创建之后添加约束(ALTER TABLE)
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECk
DEFAULT
SQL NOT NULL
NOT NULL 约束列强制不能为空,如果为空则不能插入
CREATE TABLE table_name (
column_name1 type1 NOT NULL,
column_name2 type2 NOT NULL,
column_name3 type3
....
)
SQL UNIQUE
UNIQUE用于唯一标识数据表中的每条记录,他与PRIMARY KEY都是提供了唯一性保护,但是PRIMARY KEY只能有一个,而UNIQUE可以有多个
CREATE TABLE person(
id int NOT NULL,
name varchar(20) NOT NULL,
UNIQUE(id)
)
UNIQUE(id)是Mysql定于,Sql Server/Oracle是放在NOT NULL(如果有的话)的后面。
如果是多个记录使用UNION,Mysql/Sql server/Oracle
CREATE TABLE person(
id int NOT NULL,
name varchar(20) NOT NULL,
CONSTRAINT un_personId UNIQUE(id,name)
)
如果表创建完毕,需要添加,Mysql/Sql server/Oracle
ALTER TABLE table_name ADD UNIQUE(column_name);
添加多个,Mysql/Sql server/Oracle
ALTER TABLE table_name ADD CONSTRAINT un_personId UNIQUE(column_name1,column_name2...);
撤消UNIQUE约束,Mysql
ALTER TABLE table_name DROP INDEX un_personId;
sql server/Oracle
ALTER TABLE table_name DROP CONSTRAINT un_personId;
PRIMARY KEY
创建主键唯一标识,主键必须唯一,主键不能为NULL,每个表都应该有一个主键,并且只能有一个
Mysql中
CREATE TABLE person(
id int NOT NULL,
name varchar(20) NOT NULL
PRIMARY KEY(id);
)
sql server/Oracle
CREATE TABLE person(
id int PRIMARY KEY,
name varchar(20) NOT NULL
)
添加撤消与UNIQUE一样
FOREIGN KEY
一个表中的FOREIGN KEY指向另一个表中的PRIMARY KEY。
mysql
CREATE TABLE score(
id int NOT NULL,
name varchar(20) NOT NULL,
id_p int,
PRIMARY KEY(id,0),
FOREIGN KET(id_p) REFERENCES tb_person(id)
)
sql server/oracle
CREATE TABLE score(
id int PRIMARY KEY,
name varchar(20) NOT NULL,
id_p int FOREIGN KEY REFERENCES tb_person(id)
)
同样如果需要命名别名使用CONSTRAIN new_name FOREIGN KEY(id) REFERENCES tb_person(id);
撤消
Mysql
ALTER TABLE table_name DROP FOREIGN KEY column_name;
Sql Server/Oracle
ALTER TABLE table_name DROP CONSTRAIN column_name;
CHECK
限制列中值的范围,Mysql/SQL server/Oracle等数据库的使用方法同上
比如约束id大于0
CHECK(id>0) 不同数据库放的位置不同
DEFAULT
向列中插入默认值,如果没有规定其他值,则会将默认值添加到新记录中
CREATE TABLE score(
id int PRIMARY KEY,
name varchar(20) NOT NULL,
sex int DEFAULT 0
)
添加
MySQL:
ALTER TABLE Persons ALTER City SET DEFAULT 'SANDNES'
SQL Server / Oracle / MS Access:
ALTER TABLE Persons ALTER COLUMN City SET DEFAULT 'SANDNES'
撤消的话直接将SET改为DROP
CREATE INDEX
创建索引,方便快速查询,更新一个包含索引的表比不包含索引的表的时间要多,因为索引也要更新,索引一般只在需要的被搜索的列中创建索引
允许有重复的值
CREATE INDEX index_name ON table_name(column_name);
创建唯一索引(需要保证两行不能有相同的值)
CREATE UNIQUE INDEX index_name ON table_name(column_name);
创建多个索引
CREATE INDEX index_name ON table_name(column_name1,column_name2..);
索引降序
CREATE INDEX index_name ON table_name(column_name DESC);
SQL DROP
删除索引
mysql
ALTER TABLE table_name DROP INDEX index_name;
oracle
DROP INDEX index_name;
Sql server
DROP INDEX table_name.indexname;
删除表
DROP TABLE table_name;
删除数据库
DROP DATABASE db_name;
删除表中的数据
TRUNCATE TABLE table_name
SQL ALTER
修改表包括添加、删除、修改字段
ALTER TABLE table_name ADD column_name type;
ALTER TABLE table_name DROP column_name type;
ALTER TABLE table_name ALTER CULUMN culumn_name type;
SQL AUTO INCREMENT
新纪录插入时自动生成唯一的数字
MySql中
CREATE TABLE table_name(
id int not null AUTO_INCREMENT,
name varchar(20),
PRIMARY KEY(id)
)
默认是从1自增长的,如果需要指定起始值
ALTER TALBE table_name AUTO_INCREMENT=100;
借鉴:http://www.w3school.com.cn/sql/index.asp