一、MySQL
1、连接数据库
mysql -u root -p
输入密码即可进入：mysql>
mysq -uroot -ppassword
即可进入：mysql>
2、查看MySQL版本等信息
mysql>status;
3、退出
mysql>exit；
4、启动/关闭
Windows下（需要管理员权限）
启动/停止：net start/stop <mysql服务名>
例：net stop MySQL57
Linux下
启动：sudo service mysql start 或 sudo /etc/init.d/mysql start 或 sudo start mysql
停止：sudo service mysql stop 或 sudo /etc/init.d/mysql stop 或 sudo stop mysql
重启：sudo service mysql restart 或 sudo /etc/init.d/mysql restart 或 sudo restart mysql
查看状态：sudo service mysql status
5、导入
source导入
选择数据库
mysql>use 数据库名;
导入sql文件
mysql>source /home/live-app.sql;（/home/live-app.sql;为sql文件路径）
直接导入
mysql -u用户名 -p数据库名 < 数据库名.sql
mysql -uroot -p live-app < /home/live-app.sql
注：导入时的路径应为进入mysql命令行时路径的相对路径（不然会显示error:2）
6、导出
导出结构
导出数据库为dbname的结构（其中用户名root,密码为password，生成的脚本名為db.sql）
mysqldump -uroot -ppassword -d dbname >db.sql;
导出数据库为dbname的表为tableName的表结构
mysqldump -uroot -ppassword -d dbname tableName>db.sql;
导出结构及数据
导出数据库为dbname的结构及数据（不加-d）
mysqldump -uroot -ppassword dbname >db.sql;
导出数据库为dbname的表tableName的结构及数据（不加-d）
mysqldump -uroot -ppassword dbname tableName >db.sql;
7、mysql中exists的作用和用法
例：SELECT c.CustomerId, CompanyName FROM Customers c WHERE EXISTS( SELECT OrderID FROM Orders o WHERE o.CustomerID = cu.CustomerID)
EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False。
语法：EXISTS subquery。参数 subquery 是一个受限的 SELECT 语句 （不允许有 COMPUTE 子句和 INTO 关键字）。结果类型为 Boolean，如果子查询包含行，则返回 TRUE。
二、数据库定义语言（DDL）和基本操作
1、数据库
显示数据库
show databases;
切换数据库
use 数据库名;
创建数据库
create database 数据库名;
删除数据库
drop database 数据库名;
drop database 数据库名 if exists 数据库名;
2、表
显示表
show tables;
显示表结构
desc 表名;
创建表
create table 表名 ( 字段名1 类型1 [,字段名2 类型2...]);
SQL中最常用的数据类型：
| 数据类型 | 描述 |
|---|---|
| 仅容纳整数。在括号内规定数字的最大位数。 |
| 容纳带有小数的数字。 "size" 规定数字的最大位数。"d" 规定小数点右侧的最大位数。 |
| char(size) | 容纳固定长度的字符串（可容纳字母、数字以及特殊字符）。 在括号中规定字符串的长度。 |
| varchar(size) | 容纳可变长度的字符串（可容纳字母、数字以及特殊的字符）。 在括号中规定字符串的最大长度。 |
| date(yyyymmdd) | 容纳日期。 |
例：
mysql> create table MyClass(
> id int(4) not null primary key auto_increment,
> name char(20) not null,
> sex int(4) not null default '0',
> degree double(16,2)
> );
删除表
drop table 表名;
修改表字段 （修改某个表的字段名称及指定为空或非空）
alter table 表名 change 字段原名称 字段新名称 字段类型 [是否允许非空];
alter table 表名 alter column 字段新名称 字段类型;
alter table 表名 add 字段名称 字段类型;
alter table 表名 drop column 字段名称;
显示创建表语句
show create table 表名;
3、约束
可以在创建表时规定约束，如下面几种约束：
NOT NULL：强制列不接受 NULL 值。
例：
Id_P int NOT NULL,
UNIQUE：约束唯一标识数据库表中的每条记录。
例：
定义约束
UNIQUE (Id_P) mysql
Id_P int NOT NULL UNIQUE, SQL Server / Oracle / MS Access
CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName) MySQL / SQL Server / Oracle / MS Access，命名约束，多个列
添加约束
ALTER TABLE Persons ADD UNIQUE (Id_P) MySQL / SQL Server / Oracle / MS Access
ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName) MySQL / SQL Server / Oracle / MS Access
撤销约束
ALTER TABLE Persons DROP INDEX uc_PersonID MySQL
ALTER TABLE Persons DROP CONSTRAINT uc_PersonID SQL Server / Oracle / MS Access
PRIMARY KEY：主键（每表必有），约束唯一标识数据库表中的每条记录。
例：
PRIMARY KEY (Id_P) MySQL
Id_P int NOT NULL PRIMARY KEY, SQL Server / Oracle / MS Access
ALTER TABLE Persons ADD PRIMARY KEY (Id_P)
ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)
ALTER TABLE Persons DROP PRIMARY KEY
ALTER TABLE Persons DROP CONSTRAINT pk_PersonID
FOREIGN KEY：外键。
例：
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)
CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
ALTER TABLE Orders ADD FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders
ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders
CHECK：约束用于限制列中的值的范围。
如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。
例：
CHECK (Id_P>0)
Id_P int NOT NULL CHECK (Id_P>0),
CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')
ALTER TABLE Persons ADD CHECK (Id_P>0)
ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')
ALTER TABLE Persons DROP CONSTRAINT chk_Person
ALTER TABLE Persons DROP CHECK chk_Person
DEFAULT：约束用于向列中插入默认值。
如果没有规定其他的值，那么会将默认值添加到所有的新记录。 例：
City varchar(255) DEFAULT 'Sandnes'
OrderDate date DEFAULT GETDATE()
ALTER TABLE Persons ALTER City SET DEFAULT 'SANDNES'
ALTER TABLE Persons ALTER COLUMN City SET DEFAULT 'SANDNES'
ALTER TABLE Persons ALTER City DROP DEFAULT
ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT
4、Auto-increment
Auto-increment会在新记录插入表中时生成一个唯一的数字。
例：
P_Id int NOT NULL AUTO_INCREMENT,
5、MySQL 数据类型
在 MySQL 中，有三种主要的类型：文本、数字和日期/时间类型。
Text 类型：| 数据类型 | 描述 |
|---|---|
| CHAR(size) | 保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。 |
| VARCHAR(size) | 保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。 注释：如果值的长度大于 255，则被转换为 TEXT 类型。 |
| TINYTEXT | 存放最大长度为 255 个字符的字符串。 |
| TEXT | 存放最大长度为 65,535 个字符的字符串。 |
| BLOB | 用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字节的数据。 |
| MEDIUMTEXT | 存放最大长度为 16,777,215 个字符的字符串。 |
| MEDIUMBLOB | 用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 字节的数据。 |
| LONGTEXT | 存放最大长度为 4,294,967,295 个字符的字符串。 |
| LONGBLOB | 用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。 |
| ENUM(x,y,z,etc.) | 允许你输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。 注释：这些值是按照你输入的顺序存储的。 可以按照此格式输入可能的值：ENUM('X','Y','Z') |
| SET | 与 ENUM 类似，SET 最多只能包含 64 个列表项，不过 SET 可存储一个以上的值。 |
Number 类型：
| 数据类型 | 描述 |
|---|---|
| TINYINT(size) | -128 到 127 常规。0 到 255 无符号*。在括号中规定最大位数。 |
| SMALLINT(size) | -32768 到 32767 常规。0 到 65535 无符号*。在括号中规定最大位数。 |
| MEDIUMINT(size) | -8388608 到 8388607 普通。0 to 16777215 无符号*。在括号中规定最大位数。 |
| INT(size) | -2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中规定最大位数。 |
| BIGINT(size) | -9223372036854775808 到 9223372036854775807 常规。0 到 18446744073709551615 无符号*。在括号中规定最大位数。 |
| FLOAT(size,d) | 带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。 |
| DOUBLE(size,d) | 带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。 |
| DECIMAL(size,d) | 作为字符串存储的 DOUBLE 类型，允许固定的小数点。 |
* 这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED 属性，那么范围将从 0 开始，而不是某个负数。
Date 类型：| 数据类型 | 描述 |
|---|---|
| DATE() | 日期。格式：YYYY-MM-DD 注释：支持的范围是从 '1000-01-01' 到 '9999-12-31' |
| DATETIME() | *日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59' |
| TIMESTAMP() | *时间戳。TIMESTAMP 值使用 Unix 纪元('1970-01-01 00:00:00' UTC) 至今的描述来存储。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC |
| TIME() | 时间。格式：HH:MM:SS 注释：支持的范围是从 '-838:59:59' 到 '838:59:59' |
| YEAR() | 2 位或 4 位格式的年。 注释：4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069。 |
* 即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。
三、数据库操作语言（DML）
1、插入
insert into 表名 [(字段名1[,字段名2...])] values (值1[, ...值n]);
例：
insert into Persons values ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing');
insert into users (username,password) values ('username1','password1');
2、查询
查询所有字段（列）
select * from 表名 [where 表达式];
例：
select * from users where id=1;
查询指定字段（列）
select 字段1[,字段2...] from 表名 [where 表达式];
例：
select username,id from users where username='user1';
查询所有字段记录数（行数）
select count(*) from 表名 [where 表达式];
例：
select count(*) from users;
查询指定字段唯一值（不重复显示）
select distinct 字段 from 表名;
例：
select distinct username from users;
3、删除
delete from 表名 where 表达式;
例：
delete from Person where LastName = 'Wilson';
4、修改
update 表名 set 字段=新值[,字段=新值,…] where 表达式;
例：
update Person set FirstName = 'Fred' where LastName = 'Wilson';
5、where子句
操作符
| 操作符 | 描述 |
|---|---|
| = | 等于 |
| <> | 不等于 |
| > | 大于 |
| < | 小于 |
| >= | 大于等于 |
| <= | 小于等于 |
| BETWEEN | 在某个范围内 |
| LIKE | 搜索某种模式 |
SQL 使用单引号来环绕文本值（大部分数据库系统也接受双引号）。如果是数值，请不要使用引号。
and和or运算符
可在where子语句中把两个或多个条件结合起来。
例：
select * from Persons where FirstName='Thomas' and LastName='Carter';
select * from Persons where (FirstName='Thomas' or FirstName='William') and LastName='Carter';
like 操作符
like 操作符用于在where子句中搜索列中的指定模式
select 字段名1[,字段名2...] from 表名 where 字段名 like '模式';
例：select * from Persons where City like 'N%';
提示："%" 可用于定义通配符（模式中缺少的字母）。
通过使用not关键字，我们可以从表中选取不包含指定模式的情况
例：select * from Persons where City not like '%lon%';
SQL 通配符
SQL 通配符可以替代一个或多个字符。
| 通配符 | 描述 |
|---|---|
| % | 替代一个或多个字符 |
| _ | 仅替代一个字符 |
| [charlist] | 字符列中的任何单一字符 |
[^charlist]或者[!charlist] | 不在字符列中的任何单一字符 |
例：select * from Persons where City llike '[ALN]%';
选取A，L，N开头的
in操作符
in操作符允许在where子句中规定多个值。
select 字段1[,字段1...] from 表名 where 字段 in (值1,值2);
between...and操作符
between...and会选取介于两个值（数字、文本或日期）之间的数据范围。
select 字段1[,字段1...] from 表名 where 字段 between 值1 and 值2;
不同的数据库对 between...and操作符的处理方式是有差异的。某些数据库会列出介于值1和值2之间，不包括值1和值2的结果，
有些则包括。
6、union 操作符
union 操作符用于合并两个或多个 select 语句的结果集。
select 字段1[,字段2...] from 表名 union select 字段1[,字段2...] from 表名;
select 字段1[,字段2...] from 表名 union all select 字段1[,字段2...] from 表名;
注：
union 内部的 select 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 select 语句中的列的顺序必须相同。
注：默认地，union操作符选取不同的值。如果允许重复的值，请使用union all。
另外，union结果集中的列名总是等于union中第一个select语句中的列名。
7、alias
alias可以为列名称和表名称指定别名。
select 字段1[,字段2...] from 表名 as 表别名;
select 字段 as 字段别名 from 表名;
8、join和key
join 用于根据两个或多个表中的列之间的关系，从这些表中查询数据。
几种join类型：
join: 如果表中有至少一个匹配，则返回行。
left join: 即使右表中没有匹配，也从左表返回所有的行。
right join: 即使左表中没有匹配，也从右表返回所有的行。
full join: 只要其中一个表中存在匹配，就返回行。
select 字段1[,字段2...] from 表名1 inner join 表名2 on 表名1.字段=表名2.字段;
select 字段1[,字段2...] from 表名1 left join 表名2 on 表名1.字段=表名2.字段;
select 字段1[,字段2...] from 表名1 right join 表名2 on 表名1.字段=表名2.字段;
select 字段1[,字段2...] from 表名1 full join 表名2 on 表名1.字段=表名2.字段;
例：
select Persons.LastName, Persons.FirstName, Orders.OrderNo from Persons, Orders where
Persons.Id_P = Orders.Id_P;
select Persons.LastName, Persons.FirstName, Orders.OrderNo from Persons inner join
Orders ON Persons.Id_P = Orders.Id_P ORDER BY Persons.LastName
9、order by语句
order by语句用于根据指定的列对结果集进行排序，默认按照升序（asc）对记录进行排序。降序可以使用 desc 关键字。
例：
select Company, OrderNumber from Orders order by Company;
select Company, OrderNumber from Orders order by Company, OrderNumber;
select Company, OrderNumber from Orders order by Company desc;
select Company, OrderNumber from Orders order by Company desc, OrderNumber asc;
10、select into
select into 语句从一个表中选取数据，然后把数据插入另一个表中。
select into 语句常用于创建表的备份复件或者用于对记录进行存档。
select * into 新表名 [in 另一数据库] from 旧表名 [where 表达式];
select 字段1[,字段2...] into 新表名 [in 另一数据库] from 旧表名 [where 表达式];
例：select * into Persons in 'Backup.mdb' from Persons;
11、CREATE INDEX
CREATE INDEX 语句用于在表中创建索引。在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。
CREATE [UNIQUE] INDEX index_name ON table_name (column_name) UNIQUE：唯一的索引意味着两个行不能拥有相同的索引值。
例：
CREATE INDEX PersonIndex ON Person (LastName)
CREATE INDEX PersonIndex ON Person (LastName DESC)
CREATE INDEX PersonIndex ON Person (LastName, FirstName)
12、DROP INDEX
DROP INDEX 命令删除表格中的索引。
DROP INDEX index_name ON table_name Microsoft SQLJet (以及 Microsoft Access)
DROP INDEX table_name.index_name MS SQL Server
DROP INDEX index_name IBM DB2 和 Oracle
ALTER TABLE table_name DROP INDEX index_name MySQL
13、SQL TRUNCATE TABLE
TRUNCATE TABLE 命令，仅仅删去表内的数据，但并不删除表本身。
TRUNCATE TABLE 表名称
14、日期
| 函数 | 描述 |
|---|---|
| NOW() | 返回当前的日期和时间 |
| CURDATE() | 返回当前的日期 |
| CURTIME() | 返回当前的时间 |
| DATE() | 提取日期或日期/时间表达式的日期部分 |
| EXTRACT() | 返回日期/时间按的单独部分 |
| DATE_ADD() | 给日期添加指定的时间间隔 |
| DATE_SUB() | 从日期减去指定的时间间隔 |
| DATEDIFF() | 返回两个日期之间的天数 |
| DATE_FORMAT() | 用不同的格式显示日期/时间 |
SQL Server
| 函数 | 描述 |
|---|---|
| GETDATE() | 返回当前日期和时间 |
| DATEPART() | 返回日期/时间的单独部分 |
| DATEADD() | 在日期中添加或减去指定的时间间隔 |
| DATEDIFF() | 返回两个日期之间的时间 |
| CONVERT() | 用不同的格式显示日期/时间 |
SQL Date 数据类型
MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：
DATE - 格式 YYYY-MM-DD
DATETIME - 格式: YYYY-MM-DD HH:MM:SS
TIMESTAMP - 格式: YYYY-MM-DD HH:MM:SS
YEAR - 格式 YYYY 或 YY
SQL Server 使用下列数据类型在数据库中存储日期或日期/时间值：
DATE - 格式 YYYY-MM-DD
DATETIME - 格式: YYYY-MM-DD HH:MM:SS
SMALLDATETIME - 格式: YYYY-MM-DD HH:MM:SS
TIMESTAMP - 格式: 唯一的数字
15、 NULL
NULL，如果表中的某个列是可选的，那么我们可以在不向该列添加值的情况下插入新记录或更新已有的记录。这意味着该字段将以 NULL 值保存。NULL 值的处理方式与其他值不同。NULL 用作未知的或不适用的值的占位符。
注释：NULL 和 0是不等价的。
例：
WHERE Address IS NOT NULL
16、SQL函数
AVG() 函数返回数值列的平均值。NULL 值不包括在计算中。
COUNT() 函数返回匹配指定条件的行数。
FIRST() 函数返回指定的字段中第一个记录的值。
LAST() 函数返回指定的字段中最后一个记录的值。
MAX() 函数返回一列中的最大值。NULL 值不包括在计算中。
MIN() 函数返回一列中的最小值。NULL 值不包括在计算中。
SUM() 函数返回数值列的总数（总额）。
UCASE() 函数把字段的值转换为大写。
LCASE() 函数把字段的值转换为小写。
MID() 函数用于从文本字段中提取字符。
SELECT MID(column_name,start[,length]) FROM table_name
例：
SELECT MID(City,1,3) as SmallCity FROM Persons 从"City" 列中提取前 3 个字符。
LEN() 函数返回文本字段中值的长度。
SELECT LEN(column_name) FROM table_name
例：
SELECT LEN(City) as LengthOfCity FROM Persons
ROUND() 函数用于把数值字段舍入为指定的小数位数。
SELECT ROUND(column_name,decimals) FROM table_name
例：
SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products
NOW() 函数返回当前的日期和时间。
FORMAT() 函数用于对字段的显示进行格式化。
SELECT FORMAT(column_name,format) FROM table_name
例：
SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate FROM Products
17、GROUP BY
GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。
SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name
例：
SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer
18、HAVING
HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。
SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value
例：
SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer HAVING SUM(OrderPrice)<2000
19、CONCAT()函数
CONCAT(str1,str2...)，返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。
20、ELT()函数
ELT(N,str1,str2,str3,...)，如果N =1返回str1，如果N= 2返回str2，等等。返回NULL如果参数的数量小于1或大于N。ELT()是FIELD()的补集。
21、RAND()，ROUND()，FLOOR()，CEILING()函数
rand()生成0到1之间的随机小数。
rand()函数只能生成0到1之间的随机小数，如果想要生成0到10,0到100就rand()*相应的值。
round(x)是四舍五入 。
floor(x)是去小于等于x的整数。
ceiling(x)是取大于等于x的整数。
其中round函数还有round(x，n)的形式，保留n位小数。
要得到指定范围的随机数 round(rand()*(max-min)+min)即可。
22、LIMIT
LIMIT后的第一个参数是输出记录的初始位置，第二个参数偏移量，偏移多少，输出的条目就是多少。
以上为基本命令和语法，日后用到再行补充。
参考自：http://www.w3school.com.cn/sql/index.asp
154
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
