[sql]sql基本操作
1,基本操作
2,pymysql
3,数据库连表操作
4,存储过程,触发器,函数,索引....
5.orm框架
t1
nid name email partment
1 maotai 1@qq.com DBA
2 maotai 1@qq.com DBA
3 maotai 1@qq.com DBA
4 maotai 1@qq.com CBA
t2
nid name
1 maotai
3 maotai
5 maotai
where语句:
char varchar区别
搜索速度: char给力
占用空间: varchar小
逻辑运算: and, in
select * from t1 where nid in (1,3,5);
取...范围的数据: between and
select * from t1 where nid between 1 and 3;
倒序排列: desc
select * from t1 order by nid desc; #注意: nid不能有引号.
分页: limit
select name,email form t1 limit 10,5;
select name,email form t1 limit 5 offset 10;
mysql> select * from stu limit 1;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | maotai | 20 |
+----+--------+------+
1 row in set (0.00 sec)
mysql> select * from stu limit 1,2; # 1开始 往后数两个
+----+--------+------+
| id | name | age |
+----+--------+------+
| 2 | maomao | 21 |
| 3 | maodou | 23 |
+----+--------+------+
2 rows in set (0.00 sec)
django中分页用这个语法
分组聚合: group by
select count(nid) from t1 group by partment;
- 常见聚合函数
count,sum,avg,min,max
从聚合结果中条件筛选: having
- 希望通过聚合函数的结果条件搜索, 结果>2的
select count(nid) from t1 group by partment having count(nid) > 2;
栗子:
- 找出最大的前4个:
select * from t1 order by 1 desc limit 4;
字段别名+连表操作: as
-- 最简单的连表操作
select name,email from t1 where nid in (1,3,5);
select * from t2;
select name,email from t1 where nid in (select nid from t2); # select nid from t2的结果作为查询条件.
为字段名取别名
select name as nickname,email from t1 where nid in (select nid from t2);