SQL中DML(数据库操作语言)的使用

1.插入语句：
 INSERT [INTO] table [(column1, column2, column3, . . .)] VALUES(value1, value2, value3, . . .);
 例：INSERT INTO user VALUES(NULL, 'test', '123456');
 例：INSERT INTO user (username, password) VALUES('test', '123456');
 实际上还有一种方式：
例：INSERT INTO user SET username='test', password='123456';
 

2.查询语句：
 
SELECT [option] item [INTO file_details] FROM tables [WHERE condition] [GROUP BY group_type] [HAVING where_definition]
 
[ORDER BY order_type] [LIMIT limit_criteria] [PROCEDURE proc_name(arguments)] [lock_option];
 
①简单查询
 
例：SELECT * FROM user WHERE uid=4;
 
②多表查询
 
基本的多表查询
 
例：SELECT user.username, userinfo.age, userinfo.sex, userinfo.phone FROM user, userinfo WHERE user.uid=userinfo.uid;
 
左关联
 
SELECT user.uid, uid.name, orders.orderid FROM user LEFT JOIN orders ON user.uid=orders.uid;
 
在没有使用做关联的情况下只会返回满足条件的记录，如果使用左关联左表将全部返回即使右表并不匹配，所查的右边的值将用NULL代替
 
（换句话说：左关联将返回所有满足条件的记录，还会将左表没有返回的记录也返回，所需的右表数据如orders.orderid用NULL填充）
 
③使用子查询
 
基本的子查询
 
例：SELECT uid, amount FROM orders WHERE amount=(SELECT max(amount) FROM orders);
 
关联子查询
 
例：SELECT isbn, title FROM books WHERE exists (SELECT * FROM orders WHERE orders.isbn=books.isbn); //内部查询引用外部查询数据
 
行子查询
 
例：SELECT c1, c2, c3 FROM t1 WHERE (c1, c2, c3) IN (SELECT c1, c2, c3 FROM t2);
 
使用子查询作为临时表
 
例：SELECT * FROM (SELECT uid, username FROM user WHERE city='Beijing') AS user_beijing;
 
④合计函数与分组
 
合计函数：
 
avg()
 
count()
 
min()
 
max()
 
std()
 
stddev()
 
sum()
 
. . .
 
分组通常结合合计函数使用，对结果集进行分组
 
例：SELECT uid, avg(amount) FROM orders GROUP BY uid;
 
HAVING类似于WHERE，只用于合计与分组，SQL中增加HAVING的原因是，WHERE中无法使用合计函数
 
例：SELECT uid, avg(amount) FROM orders GROUP BY uid HAVING avg(amount)>100;
 
⑤排序与限制的使用
 
例：SELECT username, password FROM user ORDER BY username ASC;    //查询user表并按username字段升序排序
 
例：SELECT username, password FROM user ORDER BY uid DESC;    //查询user表并按uid字段降序排序
 
例：SELECT username, password FROM user LIMIT 4;    //查询user表前四条记录
 
例：SELECT username, password FROM user LIMIT 3, 4;    //查询user表从第三条开始之后4条记录
 

3.更新语句：
 
UPDATE [LOW_PRIORITY] [IGNORE] table SET column=expression1, column2=expression2, . . .
 
[WHERE condition] [ORDER BY order_criteria] [LIMIT number];
 
例：UPDATE user SET password='111111';    //将user表中所有人密码设成111111
 
例：UPDATE user SET password='111111' WHERE uid=4;    //将user表中uid等于4的记录的密码设成111111
 
例：UPDATE user SET password='111111'    ORDER BY uid DESC LIMIT 5;    //将user表中按uid倒序的前5条记录的密码设成111111
 
这里的ORDER BY通常与LIMIT结合使用，单独使用ORDER BY没有意义
 

4.删除语言：
 
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table [WHERE condition] [ORDER BY order_cols] [LIMIT number];
 
例：DELETE FROM user;    //删除user所有数据
 
例：DELETE FROM user WHERE uid=4;    //删除uid=4的记录
 
例：DELETE FROM user ORDER BY uid DESC LIMIT 5;    //删除user表中按uid倒序的前5条数据
 

提示：[]表示可选
 
          在字段名上加上反引号``可以避免字段名与数据库关键字冲突
 
          字符串值需要用引号''引起来，数值不需要