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条数据
提示：[]表示可选
在字段名上加上反引号``可以避免字段名与数据库关键字冲突
字符串值需要用引号''引起来，数值不需要
本文永久更新链接地址：