sql

SQL 语句简记

创建/删除 数据库 ( mysqladmin 之间没有空格)：

~$ mysqladmin -u root -p create database_name
~$ mysqladmin -u root -p drop database_name

登陆/退出 mysql：

~$ mysql -u root -p
mysql> exit

选择 数据库：

mysql> use table_name;

创建/删除 数据表：

mysql> create table table_name (column_name column_type);
mysql> drop table table_name;

mysql> create table users (user_id int not null auto_increment,
    -> user_name varchar(40) not null,
    -> primary key (user_id) );

插入数据：

mysql> insert into table_name ( field1, field2, ..., fieldN)
                              values
                              ( value1, value2, ..., valueN);

mysql> insert into users (user_name)
    -> values
    -> ("myname");

查询数据：

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][OFFSET M]
mysql> select * from users;

• 查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
• SELECT 命令可以读取一条或者多条记录。
• 你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
• 你可以使用 WHERE 语句来包含任何条件。
• 你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
• 你可以使用 LIMIT 属性来设定返回的记录数。

where 子句：

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2...]

mysql> select user_name from users where user_name = "myname";

update 查询/修改：

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

mysql> update users set user_name="mysecondname" 
    -> where user_name="myname";

delete 删除记录：

DELETE FROM table_name [WHERE Clause]

mysql> delete from users where user_id=1;

like 匹配查询：

SELECT field1, field2,...fieldN 
FROM table_name1, table_name2...
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

mysql> select user_name from users where user_name like "%name";

• SQL LIKE 子句中使用百分号(%)字符来表示任意字符，类似于UNIX或正则表达式中的星号 (*)。
• 如果没有使用百分号(%), LIKE 子句与等号（=）的效果是一样的。

order 排序：

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]

mysql> select user_name from users order by user_name asc;

• 你可以设定多个字段来排序。
• 你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升排列。
• 你可以添加 WHERE…LIKE 子句来设置条件。

group 分组/统计：

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

mysql> select user_name, count(*) from users group by user_name;

• GROUP BY 语句根据一个或多个列对结果集进行分组。
• 在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

with rollup 对 group 统计结果再进行相同的统计：

mysql> select user_name, count(*) from users group by user_name with rollup;

coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：

select coalesce(a,b,c);

参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

mysql> select coalesce(user_name, "total"), count(*) from users 
    ->group by user_name with rollup;

join 联合多表查询：

JOIN 按照功能大致分为如下三类：

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

inner join:
mysql> select a.user_id, a.user_name, b.signin from users a inner join signin_count b on a.user_name=b.user_name;
# 等价于：
mysql> select a.user_id, a.user_name, b.signin from users a, signin_count b where a.user_name=b.user_name;

left join:
mysql> select a.user_id, a.user_name, b.signin from users a left join signin_count b on a.user_name=b.user_name;

right join:
mysql> select a.user_id, a.user_name, b.signin from users a right join signin_count b on a.user_name=b.user_name;

数据类型：

三类：数值、日期/时间和字符串类型。

• 数值类型：int, float …
• 日期和时间类型：date(yyyy-mm-dd), time(hh:mm:ss) datetime(date+time), timestamp(yyyymmddhhmmss)
• 字符串类型：char, varchar, text, blob …

mysql> create table signin_date(user_name varchar(40) not null, date date not null);
mysql> insert into signin_date 
    -> values
    -> ("why", now());

null 查询：

is null
is not null
<=> 两个值为 null 返回 true, null=null 返回 false

regexp 正则表达式：

模式	描述
‘^st’	匹配以 ‘st’ 开头的字符串，如 ‘street’
‘ly$’	匹配以 ‘ly’ 结尾的字符串，如 ‘lovely’
‘mar’	匹配包含 ‘mar’ 的字符串，如 ‘marmar’
‘.’	匹配任意一个字符，除了’\n’
[abc]	字符集合，匹配所包含的任意一个字符。如 ‘plain’ 中的 ‘a’
[^abc]	负值字符集合。匹配未包含的任意字符。如 ‘plain’ 中的 ‘p’ 等
p1|p2|p3	匹配 p1 或 p2 或 p3。例如，’z|food’ 能匹配 “z” 或 “food”。’(z|f)ood’ 则匹配 “zood” 或 “food”。
*	匹配前面的子表达式零次或多次。例如，zo* 能匹配 “z” 以及 “zoo”。
+	匹配前面的子表达式一次或多次。例如，’zo+’ 能匹配 “zo” 以及 “zoo”，但不能匹配 “z”。
{n}	n 是一个非负整数。匹配确定的 n 次。例如，’o{2}’ 不能匹配 “Bob” 中的 ‘o’，但是能匹配 “food” 中的两个 o。
{n,m}	m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

事务。。。

alter 修改数据表：

# 删除，添加或修改表字段
mysql> SHOW COLUMNS FROM table_name;
mysql> ALTER TABLE testalter_tbl DROP i;
mysql> ALTER TABLE testalter_tbl ADD i INT;
mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);
mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;
mysql> ALTER TABLE testalter_tbl MODIFY j BIGINT NOT NULL DEFAULT 100;
mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

# 修改数据表类型
mysql> SHOW TABLE STATUS LIKE 'table_name'\G;

# 修改表名
mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

index 索引

• 创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

• 实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

# 普通索引 index
# 唯一索引 unique index 允许有空值
CREATE INDEX indexName ON mytable(username(length)); 
ALTER mytable ADD INDEX [indexName] ON (username(length));
CREATE TABLE mytable(ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)));
DROP INDEX [indexName] ON mytable; 
ALTER TABLE mytable DROP INDEX (c);

# 使用 ALTER 命令添加和删除索引
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) # 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list) # 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list) # 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list) # 该语句指定了索引为 FULLTEXT ，用于全文索引。

# 显示索引信息
mysql> SHOW INDEX FROM table_name\G;

temporary 临时表：

MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，MySQL会自动删除表并释放所有空间。

mysql> CREATE TEMPORARY TABLE ...
mysql> DROP TABLE table_name;

复制表：

mysql> SHOW CREATE TABLE w3cschool_tbl \G;
mysql> CREATE TABLE `clone_tbl` ...
mysql> INSERT INTO clone_tbl (w3cschool_id,
    ->                        w3cschool_title,
    ->                        w3cschool_author,
    ->                        submission_date)
    -> SELECT w3cschool_id,w3cschool_title,
    ->        w3cschool_author,submission_date
    -> FROM w3cschool_tbl;

元数据：

# 数据库和数据表
show databases;  # 列出所有数据库
show tables; # 列出默认数据库的所有表
show table status;  # 查看表的描述性信息
show columns from tbl_name;  # 查看列信息  
show index from tbl_name;  # 查看索引信息

# 服务器元数据
SELECT VERSION( ) # 服务器版本信息
SELECT DATABASE( )  # 当前数据库名 (或者返回空)
SELECT USER( )  # 当前用户名
SHOW STATUS # 服务器状态
SHOW VARIABLES  # 服务器配置变量

处理重复数据：

# 防止表中出现重复数据
设置指定的字段为 PRIMARY KEY（主键） 或者 UNIQUE（唯一） 索引来保证数据的唯一性。
双主键模式来设置数据的唯一性。
PRIMARY KEY (last_name, first_name)
UNIQUE (last_name, first_name)
INSERT IGNORE INTO
INSERT REPLACE INTO

# 查询/统计重复记录
select user_name,count(*) as count from user_table group by user_name having count>1;
select * from people 
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1);

# 过滤重复数据
mysql> SELECT DISTINCT last_name, first_name
    -> FROM person_tbl

# 删除重复数据
mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
    ->                  FROM person_tbl;
    ->                  GROUP BY (last_name, first_name);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

mysql> ALTER IGNORE TABLE person_tbl
    -> ADD PRIMARY KEY (last_name, first_name);

SQL 注入：

所谓SQL注入，就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。

导出数据：

导入数据：