SQL语法基本用法

查询记录
语法格式：

SELECT item FROM table_name WHERE conditions;

查询name为Simth,并且age < 30的记录

SELECT * FROM my_table WHERE name = 'Smith' AND age < 30;

查询birth字段值为空的记录

SELECT *  FROM   pet  WHERE ISNULL(birth);

查询记录数

SELECT COUNT(*) FROM my_table;

从表users中只查询name字段和main字段(输出为Email字段)

SELECT name, mail as Email FROM users;

降序排列

SELECT * FROM my_table ORDER BY id DESC;

升序排列
不指明默认为升序ASC

SELECT * FROM my_table ORDER BY id; 

SELECT * FROM my_table limit 10;

从第10条语句开始显示20条语句

SELECT * FROM my_table limit 10,20;

随机抽取10条语句

SELECT * FROM table_name ORDER BY rand() LIMIT 10;

向pet表中插入1条记录；

INSERT INTO pet 
VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

向pet表中插入1条记录(只插入name字段，其他字段采用默认值)；

INSERT INTO pet (name) VALUES('Alex');

UPDATE pet SET birth = '1989-08-31',name='Alex' WHERE name = 'Bowser';

DELETE FROM pet WHERE name='Bowser';

查询为空的字段：

SELECT *  FROM   pet  WHERE ISNULL(birth);

查找出userid最大值

SELECT max(userid) AS userid from boblog_user;

AS指定操作结果的名称(可省略)

计算某个字段的和：

SELECT sum(count) from tradelist;

计算表tradelistk中count字段的和

模糊匹配
SQL的模式匹配允许使用 _ 匹配任何单个字符，和 % 匹配任意数目字符（包括零个字符）

SELECT * FROM phonebook WHERE name Like '%三%';