[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);