SQL是什么?
SQL是一种数据库计算机语言,用于检索和管理关系数据库中的数据。SQL代表结构化查询语言,包括数据库的创建、删除、获取、修改等。SQL是关系数据库系统的标准语言。所有关系数据库管理系统 (RDMS),如MySQL、MS Access、Oracle、Sybase、Informix、Postgres和SQL Server都使用SQL作为它们的标准数据库语言。
为什么要用SQL?
SQL广受欢迎是因为它提供了以下优点:
允许用户在关系数据库管理系统中访问数据
允许用户来描述数据
允许用户定义的数据库中的数据和操作数据
允许嵌入在其他语言使用SQL模块、库和pre-compilers
允许用户创建和删除数据库和表
允许用户创建视图、存储过程、函数在数据库中
允许用户设置权限表、procedures和视图
SQL命令
SQL命令与关系数据库交互的标准,SQL命令包含CREATE, SELECT, INSERT, UPDATE, DELETE and DROP。这些命令按其功能可分为以下3类:
(1)数据定义语言
(2)数据操作语言
(3)数据控制语言
SQL语法
所有SQL语句都以SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW等关键字开头,所有语句都以分号 (;) 结尾。这里需要注意的一点是SQL不区分大小写,这意味着SELECT和select在SQL语句中具有相同的含义。而MySQL在表名上有所不同。因此,如果使用的是MySQL,那么需要提供数据库中存在的表名。
(1)SQL SELECT Statement
SELECT column1, column2....columnNFROM table_name;
(2)SQL DISTINCT Clause
SELECT DISTINCT column1, column2....columnNFROM table_name;
(3)SQL WHERE Clause
SELECT column1, column2....columnNFROM table_nameWHERE CONDITION;
(4)SQL AND/OR Clause
SELECT column1, column2....columnNFROM table_nameWHERE CONDITION-1 {AND|OR} CONDITION-2;
(5)SQL IN Clause
SELECT column1, column2....columnNFROM table_nameWHERE column_name IN (val-1, val-2,...val-N);
(6)SQL BETWEEN Clause
SELECT column1, column2....columnNFROM table_nameWHERE column_name BETWEEN val-1 AND val-2;
(7)SQL LIKE Clause
SELECT column1, column2....columnNFROM table_nameWHERE column_name LIKE { PATTERN };
(8)SQL ORDER BY Clause
SELECT column1, column2....columnNFROM table_nameWHERE CONDITIONORDER BY column_name {ASC|DESC};
(9)SQL GROUP BY Clause
SELECT SUM(column_name)FROM table_nameWHERE CONDITIONGROUP BY column_name;
(10)SQL COUNT Clause
SELECT COUNT(column_name)FROM table_nameWHERE CONDITION;
(11)SQL HAVING Clause
SELECT SUM(column_name)FROM table_nameWHERE CONDITIONGROUP BY column_nameHAVING (arithematic function condition);
(12)SQL CREATE TABLE Statement
CREATE TABLE table_name(column1 datatype,column2 datatype,column3 datatype,.....columnN datatype,PRIMARY KEY( one or more columns ));
(13)SQL DROP TABLE Statement
DROP TABLE table_name;
(14)SQL CREATE INDEX Statemen
CREATE UNIQUE INDEX index_nameON table_name ( column1, column2,...columnN);
(15)SQL DROP INDEX Statement
ALTER TABLE table_nameDROP INDEX index_name;
(16)SQL DESC Statement
DESC table_name;
(17)SQL TRUNCATE TABLE Statement
TRUNCATE TABLE table_name;
(18)SQL ALTER TABLE Statement
ALTER TABLE table_name {ADD|DROP|MODIFY} column_name {data_ype};
(19)SQLALTER TABLE Statement (Rename)
ALTER TABLE table_name RENAME TO new_table_name;
(20)SQL INSERT INTO Statement
INSERT INTO table_name( column1, column2....columnN)VALUES ( value1, value2....valueN);
(21)SQL UPDATE Statement
UPDATE table_nameSET column1 = value1, column2 = value2....columnN=valueN[ WHERE CONDITION ];
(22)SQL DELETE Statement
DELETE FROM table_nameWHERE {CONDITION};
(23)SQL CREATE DATABASE Statement
CREATE DATABASE database_name;
(24)SQL DROP DATABASE Statement
DROP DATABASE database_name;
(25)SQL USE Statement
USE database_name;
(26)SQL COMMITStatement
COMMIT;
(27)SQL ROLLBACK Statement
ROLLBACK;