nodejs中mysql的常用语句

命令	语义
select	提取数据
update	更新数据
delete	删除数据
insert into	插入数据
create database	创建数据库
alter database	修改数据库
create table	创建表
alter table	修改表
drop table	删除表
create index	创建索引（搜索键）
drop index	删除索引

• 注：命令可以小写，为方便区分，文中所有命令使用大写

select

语法：SELECT column_name,column_name FROM table_name.
例子：SELECT * FROM user WHERE id=1;

关键字distinct：用于返回唯一不同的值

SELECT DISTINCT class FROM student
一个列中可能会包含多个重复的值，这里仅仅列出不同(distinct)的值

select 语句后面需要跟子句来进行筛选或者操作数据，子句在下文有详细介绍:

1. where

SELECT column_name,column_name FROM table_name WHERE column_name operator value;
例：SELECT * FROM student WHERE age=15.

and & or 运算符

SELECT * FROM user WHERE age>15 AND (conutry=‘china’ OR country=‘usa’)

2. order

对搜索结果按照一列或者多列进行排序。
语法：SELECT column_name,column_name FROM table_name ORDER BY column_name,column_name.

排序关键字：ASC|DESC （升序|降序）。

例子1：SELECT * FROM user ORDER BY age。
例子2：SELECT * FROM user ORDER BY age DESC;(按照年龄降序排序)
例子3：SELECT * FROM user ORDER BY age ASC, height DESC;(按照年龄升序排列，年龄相同按照身高降序排列)

3. group

聚类：合并相同
例子：SELECT * FROM students GROUP BY class;(统计每班人数)。

GROUP BY class, 以class为合并依据，单独用只是个去重的作用
SELECT class FROM student GROUP BY class;

只是单独列出有哪些班级
SELECT class,COUNT(class) FROM student GROUP BY class

每个班的平均分
SELECT class,AVG(score) FROM student GROUP BY class;
SELECT class,AVG(score),MAX(score),MIN(SCORE),AVG(age) FROM student GROUP BY class.

4. limit

SELECT * form 表名 WHERE 条件 limit 0,10; //从第0条开始，要10条数据
SELECT * form 表名 WHERE 条件 limit 5,-1; //检索6到最后一条数据
SELECT * form 表名 WHERE 条件 limit 5; //检索前5条数据

insert into

语法1：INSERT INTO table_name (column_name,column_name,…) VALUES (column_name,column_name,…)
语法2：INSERT INTO table_name VALUES (column_name,column_name,…)
例子：INSERT INTO Websites (name, url, country)
VALUES (‘stackoverflow’, ‘http://stackoverflow.com/’, ‘IND’);

update

语法：UPDATE table_name SET column1=value1,column2=value2,… WHERE some_column=some_value.
例子：UPDATE user SET name=‘alex’ WHERE id=1.

注：where字句为判断条件，如果省略，则所有数据都将被更新！

delete

删除表中的行

语法： DELETE FROM table_name WHERE column_name=value;
例子：DELETE FROM user WHERE name=‘alex’ AND age=15;

可以在不删除表的情况下，删除表中所有行。这意味着表的结构、属性、索引将保持不变：DELETE * FROM table_name 或者 DELETE FROM table_name.

• 本文参考http://www.cnblogs.com/adoctors/p/9250964.html，作者adoctors
• 更多sql语句和技巧可参见https://www.cnblogs.com/1234abcd/p/5530314.html