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命令。