数据库常用命令整理

SELECT DISTINCT country FROM Websites; --distinct    去掉重复

INSERT INTO table_name 
VALUES (value1,value2,value3,...); --第一种形式无需指定要插入数据的列名，只需提供被插入的值即可
INSERT INTO table_name (column1,column2,column3,...) 
VALUES (value1,value2,value3,...); --第二种形式需要指定列名及被插入的值

insert into select 和select into from 的区别
insert into scorebak 
select * from socre 
where neza='neza'   --插入一行,要求表scorebak 必须存在
insert into table_name_new(column1,column2...) 
select column1,column2... from table_name_old; --复制指定列到另一表中
select * into scorebak from score 
where neza='neza'  --也是插入一行,要求表scorebak 不存在
SELECT Websites.name, access_log.count, access_log.date 
INTO WebsitesBackup2016 FROM Websites 
LEFT JOIN access_log 
ON Websites.id=access_log.site_id; --复制多个表中的数据插入到新表中

/* select into from ：将查询出来的数据整理到一张新表中保存，表结构与查询结构一致。
select *（查询出来的结果） into newtable（新的表名）form where （后续条件）
即，查询出来结果--->复制一张同结构的空表--->将数据拷贝进去。
insert into select ：为已经存在的表批量添加新数据。
insert into  (准备好的表) select *（或者取用自己想要的结构）frome 表名 where 各种条件
即，指定一张想要插入数据的表格--->对数据进行加工筛选--->填入一张准备好的表格。 */

UPDATE table_name SET column1=value1,column2=value2 WHERE some_column=some_value; --UPDATE 语句用于更新表中已存在的记录

DELETE FROM table_name WHERE some_column=some_value; --DELETE 语句用于删除表中的行

SELECT * FROM `tb_goods` WHERE `goods_note`  REGEXP '^[A-H]'; --下面的 SQL 语句选取 name 以 A 到 H 字母开头的网站
SELECT * FROM `tb_goods` WHERE `goods_note`  REGEXP '^[^A-H]'; --下面的 SQL 语句选取 name 不以 A 到 H 字母开头的网站

--在下面的 SQL 语句中，我们把三个列（url、alexa 和 country）结合在一起，并创建一个名为 "site_info" 的别名：
SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info FROM Websites;

--SQL join
--内连    
select * from a join b on a.key=b.key;
--左外    
select * from a left join b on a.key=b.key;
--左不外    
select * from a left join b on a.key=b.key where b.key is null;
--右外    
select * from a right join b on a.key=b.key;
--右不外    
select * from a right join b on a.key=b.key where a.key is null;
--全连接    
select * from a full outer join b on a.key=b.key;
--全不连接    
select * from a full outer join b on a.key=b.key where a.key is null or b.key is null;

--SQL UNION 操作符合并两个或多个 SELECT 语句的结果
SELECT country FROM Websites UNION SELECT country FROM apps ORDER BY country;
--UNION ALL 不去重

CREATE TABLE 新表 AS SELECT * FROM 旧表 --拷贝表结构及数据
create table table_name_new like table_name_old --仅拷贝表结构

CREATE DATABASE my_db; --创建一个名为 "my_db" 的数据库

--CREATE TABLE 语句用于创建数据库中的表
CREATE TABLE Persons
(
PersonID int NOT NULL PRIMARY KEY, --PRIMARY KEY 约束唯一标识数据库表中的每条记录，每个表只能有一个 PRIMARY KEY 约束
LastName varchar(255) NOT NULL, --NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录
FirstName varchar(255) NOT NULL UNIQUE, --UNIQUE 约束唯一标识数据库表中的每条记录，每个表可以有多个 UNIQUE 约束
Address varchar(255) DEFAULT 'Sandnes', --DEFAULT 约束用于向列中插入默认值
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (PersonID,LastName), --pk_PersonID 的值是由两个列（P_Id 和 LastName）组成的
CONSTRAINT fk_PerOrders FOREIGN KEY (LastName) REFERENCES Orders(LastName), --定义多个列的 FOREIGN KEY 约束
CONSTRAINT chk_Person CHECK (PersonID>0 AND City='Sandnes') --定义多个列的 CHECK 约束
);

--删除 NOT NULL 约束
ALTER TABLE Persons
MODIFY Age int NULL;

--撤销 UNIQUE 约束
--MySQL：
ALTER TABLE Persons
DROP INDEX uc_PersonID;
--SQL Server / Oracle / MS Access：
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID;

--撤销 PRIMARY KEY 约束
--MySQL：
ALTER TABLE Persons
DROP PRIMARY KEY;
--SQL Server / Oracle / MS Access：
ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID;

--撤销 FOREIGN KEY 约束
--MySQL：
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders;
--SQL Server / Oracle / MS Access：
ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders;

--撤销 CHECK 约束
--SQL Server / Oracle / MS Access：
ALTER TABLE Persons
DROP CONSTRAINT chk_Person;
--MySQL：
ALTER TABLE Persons
DROP CHECK chk_Person;

--撤销 DEFAULT 约束
--MySQL：
ALTER TABLE Persons
ALTER City DROP DEFAULT;
--SQL Server / Oracle / MS Access：
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;

--在 "Persons" 表的 "LastName、FirstName" 列上创建一个名为 "PIndex" 的索引；CREATE UNIQUE INDEX 创建一个唯一的索引
CREATE INDEX PIndex
ON Persons (LastName, FirstName);

--DROP INDEX 语句用于删除表中的索引。
--用于 MS Access 的 DROP INDEX 语法：
DROP INDEX index_name ON table_name;
--用于 MS SQL Server 的 DROP INDEX 语法：
DROP INDEX table_name.index_name;
--用于 DB2/Oracle 的 DROP INDEX 语法：
DROP INDEX index_name;
--用于 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 table_name
ADD column_name datatype;
--如需删除表中的列，请使用下面的语法（请注意，某些数据库系统不允许这种在数据库表中删除列的方式）：
ALTER TABLE table_name
DROP COLUMN column_name;

--要改变表中列的数据类型，请使用下面的语法：
--SQL Server / MS Access：
ALTER TABLE table_name
ALTER COLUMN column_name datatype;
--My SQL / Oracle：
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
--Oracle 10G 之后版本:
ALTER TABLE table_name
MODIFY column_name datatype;

--我们通常希望在每次插入新记录时，自动地创建主键字段的值
--我们可以在表中创建一个 auto-increment 字段

/* SQL Aggregate 函数计算从列中取得的值，返回一个单一的值。
有用的 Aggregate 函数：
AVG() - 返回平均值
COUNT() - 返回行数
MAX() - 返回最大值
MIN() - 返回最小值
SUM() - 返回总和

SQL Scalar 函数基于输入值，返回一个单一的值。
有用的 Scalar 函数：
UCASE() - 将某个字段转换为大写    SELECT UCASE(column_name) FROM table_name;    SQL Server：    大写UPPER(name)    小写LOWER (name)
LCASE() - 将某个字段转换为小写
MID() - 从某个文本字段提取字符，MySql 中使用
SubString(字段，1，end) - 从某个文本字段提取字符
LEN() - 返回某个文本字段的长度    MySQL 中函数为 LENGTH()
ROUND() - 对某个数值字段进行指定小数位数的四舍五入
NOW() - 返回当前的系统日期和时间
FORMAT() - 格式化某个字段的显示方式 */
SELECT name, url, DATE_FORMAT(Now(),'%Y-%m-%d') AS date
FROM Websites;

--EXISTS 运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);