Home > Database > Mysql Tutorial > SQL中DML(数据库操作语言)的使用

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

WBOY
Release: 2016-06-07 16:43:23
Original
1028 people have browsed it

1.插入语句: INSERT [INTO] table [(column1, column2, column3, . . .)] VALUES(value1, value2, value3, . . .); 例:INSERT

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条数据
 

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

本文永久更新链接地址:

linux

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template