SQL语句

--DELETE FROM Table_1;

/*插入
INSERT INTO tabone (id,name,age)
VALUES ('001','A',1),('002','B',2),('003','C',3),('004','D',4),('005','E',5),('006','F',6),('007','G',7),('008','H',8);

INSERT INTO tabtwo (site_id2,num,size2)
VALUES ('001',434,1),('002',1651,2),('003',2654,13),('004',165,41),('005',16,51),('006',16,16),('007',16,71),('008',165,18);
*/
--去重
--SELECT DISTINCT iD FROM Table_1

--选中 <>不等于，！=不等于
--between 5 and 10
--in (5,6,7)
--匹配字符串区分大小写
--like 模糊查询 _表示一个字符 %M% 包含M  M% M开头
--SELECT * FROM Table_1 
--WHERE id like '0%';

--删除
--DELETE FROM Table_1
--WHERE ID='4'AND name ='R';

--排序 默认ASC升序，DESC降序
--SELECT * FROM Table_1
--ORDER BY id ;

/*更新
set sql_safe_updates = 1;MySQL可用此避免
UPDATE Table_1
SET age = 10,id ='010'--若无where，所有数据都会被更新
WHERE name = 'F';
*/
--规定要返回的记录数目 数目50或百分比50PERCENT
--SELECT TOP 50percent age FROM Table_1;

--SELECT * FROM Table_1
--LIMIT 2;


--REGEXP '^[A-F]'以A到F开头的
--REGEXP '^[^A-F]'不以A到F开头的

--SELECT * FROM Table_1
--WHERE name REGEXP '^[A-F]';

--SELECT * FROM Table_1
--WHERE name IN ('C','F');
/*别名
SELECT id,CONCAT(name,', ',age) AS info
FROM Table_1;
*/
/*
SELECT tabone.id,tabone.name,tabone.age,tabtwo.num,tabtwo.size2
FROM tabone
INNER JOIN tabtwo
ON tabone.id = tabtwo.site_id2;
*/
/*合并多个SELECT语句
SELECT age FROM tabone
UNION --AlL显示重复
SELECT num FROM tabtwo;
*/
/*复制整张表
SELECT * 
INTO tab3
FROM tabone;
*/
/*复制指定列
SELECT id
INTO tab4
FROM tabone;
*/
/*
SELECT tabone.id,tabone.name,tabone.age,tabtwo.num,tabtwo.size2
INTO tab5
FROM tabone
INNER JOIN tabtwo
ON tabone.id = tabtwo.site_id2;
*/
--CREATE DATABASE my_db
/*创建新表

CREATE TABLE Persons
(
PersonID int PRIMARY KEY  AUTOINCREMENT,仅有一个
LastName varchar(255) NOT NULL,轻质字段包含值
FirstName varchar(255) UNIQUE,唯一标识
Address varchar(255),
City varchar(255)
);*/
/*CHECK (PersonID>0)约束范围
DEFAULT 0 默认值
CREATE TABLE Persons
(
PersonID int CHECK (PersonID>0) DEFAULT 0,
LastName varchar(255) ,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY (PersonID),
UNIQUE(Address)

);*/
/*当表已被创建
新增约束
ALTER TABLE Persons
ADD UNIQUE(LastName)
ADD CONSTRAINT uc_PersonID UNIQUE(PersonID)
撤销约束
ALTER TABLE Persons
DROP INDEX uc_PersonID --MySQL
DROP CONSTRAINT uc_PersonID --SQL Server / Oracle / MS Access
*/
/*
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id
*/

/*创建索引
CREATE INDEX Pindex
ON Persons (LastName,FirstName)
*/
/*撤销索引
DROP INDEX index_name ON table_name --MS Access
DROP INDEX table_name.index_name --MS SQL Server
DROP INDEX index_name --DB2/Oracle
ALTER TABLE table_name DROP INDEX index_name --MySQL
*/
/*删除表
DROP TABLE table—name
*/
/*删除数据库
DROP DATABASE database_name
*/
/*仅删除表内数据不删表
TRUNCATE TABLE table_name
*/
/*ALTER TABLE
用于在已有的表中添加、删除或修改列

在表中添加列
ALTER TABLE table_name
ADD column_name datatype

删除表中的列
ALTER TABLE table_name
DROP COLUMN column_name

改变表中列的数据类型
ALTER TABLE table_name
ALTER COLUMN column_name datatype
--SQL Server / MS Access
ALTER TABLE table_name
MODIFY COLUMN column_name datatype
--My SQL / Oracle
ALTER TABLE table_name
MODIFY column_name datatype;
Oracle 10G 之后版本
*/
/*
ALTER TABLE tabone
ADD score int*/
/*
UPDATE tabone
SET score = 25
WHERE id = '002'*/
/*
ALTER TABLE tabone
DROP COLUMN score
*/
/*
AUTO_INCREMENT
用法各不相同
*/

/*SQL CREATE VIEW 语法
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
*/
/*SQL CREATE OR REPLACE VIEW 语法
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
*/
/*创建视图
CREATE VIEW [ves]AS
SELECT id,name,age
FROM tabone
--WHERE name ='002'
*/

/*撤销视图
DROP VIEW view_name
*/
/*
CREATE TABLE Orders
(
OrderId int NOT NULL,
ProductName varchar(50) NOT NULL,
OrderDate datetime NOT NULL DEFAULT NOW(),
PRIMARY KEY (OrderId)
)
INSERT INTO Orders (ProductName) VALUES ('Jarlsberg Cheese')
*/
/*
无法使用比较运算符来测试 NULL 值，比如 =、< 或 <>。

必须使用 IS NULL 和 IS NOT NULL 操作符。
*/
/*
SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0))
FROM Products
--SQL Server / MS Access
SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0))
FROM Products
--Oracle
SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products
SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
FROM Products
--MySQL