简单学习三、Mysql语句之表内容操作
表内容的操作
1、增
insert into 表 (列名,列名...) values (值,值,值...)insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)
insert into 表 (列名,列名...) select 列名,列名... from 表
2、删
#删除表drop table 表名 #先清空表#清表数据delete from 表名delete from 表名 where 条件1 and 条件2
3、改
update 表 set 列名 ＝ '值' where 条件
4、查
select * from 表select * from 表 where 条件select 列名1,列名2,列名3 as 新列名 from 表 where 条件 #as 更改列名3显示的列名
5、其他(条件、通配符、分页、排序、连表、组合、分组)
A、按条件
mysql> select * from a;+-----+------------+------+----------+| nid | name | age | part_nid |+-----+------------+------+----------+| 1 | zhangsan | 18 | 2 || 2 | wanwu | 18 | 2 || 3 | lisi | 19 | 3 || 4 | zhangfei | 22 | 5 || 5 | wuhua | 12 | 1 || 6 | laohu | 33 | 4 || 7 | houyi | 23 | 2 || 8 | zhengzheng | 33 | 5 |+-----+------------+------+----------+8 rows in set (0.00 sec)~~~A.a~~~ select * from 表 where 条件1 and 条件2 and 条件3;mysql> select * from a where age = 23 and part_nid = 2;+-----+-------+------+----------+| nid | name | age | part_nid |+-----+-------+------+----------+| 7 | houyi | 23 | 2 |+-----+-------+------+----------+1 row in set (0.00 sec)~~~A.b~~~ select * from 表 where nid between 2 and 5; #nid等于2到5范围内mysql> select * from a where nid between 2 and 5;+-----+----------+------+----------+| nid | name | age | part_nid |+-----+----------+------+----------+| 2 | wanwu | 18 | 2 || 3 | lisi | 19 | 3 || 4 | zhangfei | 22 | 5 || 5 | wuhua | 12 | 1 |+-----+----------+------+----------+4 rows in set (0.00 sec)~~~A.c~~~ select * from 表 where nid in (1,3,4) #nid 等于1或3或4mysql> select * from a where nid in (1,3,4);+-----+----------+------+----------+| nid | name | age | part_nid |+-----+----------+------+----------+| 1 | zhangsan | 18 | 2 || 3 | lisi | 19 | 3 || 4 | zhangfei | 22 | 5 |+-----+----------+------+----------+3 rows in set (0.00 sec)~~~A.e~~~ select * from 表 where id not in (11,22,33)#id 不等于1或3或4mysql> select * from a where nid not in (1,3,4);+-----+------------+------+----------+| nid | name | age | part_nid |+-----+------------+------+----------+| 2 | wanwu | 18 | 2 || 5 | wuhua | 12 | 1 || 6 | laohu | 33 | 4 || 7 | houyi | 23 | 2 || 8 | zhengzheng | 33 | 5 |+-----+------------+------+----------+5 rows in set (0.00 sec)~~~A.f~~~ select * from 表 where nid in (select nid from 表) #nid 是否包含在nid里#b表nid只有1-6mysql> select * from a where nid in (select nid from b);+-----+----------+------+----------+| nid | name | age | part_nid |+-----+----------+------+----------+| 1 | zhangsan | 18 | 2 || 2 | wanwu | 18 | 2 || 3 | lisi | 19 | 3 || 4 | zhangfei | 22 | 5 || 5 | wuhua | 12 | 1 || 6 | laohu | 33 | 4 |+-----+----------+------+----------+6 rows in set (0.00 sec)
B、通配符
~~~B.a~~~ select * from 表 where 列名 like 'zh%' - zh开头的所有（多个字符串）mysql> select * from a where name like 'zhang%';+-----+----------+------+----------+| nid | name | age | part_nid |+-----+----------+------+----------+| 1 | zhangsan | 18 | 2 || 4 | zhangfei | 22 | 5 |+-----+----------+------+----------+2 rows in set (0.00 sec)~~~B.b~~~ select * from 表 where 列名 like 'zhangsa_' - zhengsa开头的所有（一个字符）mysql> select * from a where name like 'zhangsa_';+-----+----------+------+----------+| nid | name | age | part_nid |+-----+----------+------+----------+| 1 | zhangsan | 18 | 2 |+-----+----------+------+----------+1 row in set (0.00 sec)~~~B.c~~~ select * from 表 where 列名 like '%ng%' - 列里面所有包含ngmysql> select * from a where name like '%ng%';+-----+------------+------+----------+| nid | name | age | part_nid |+-----+------------+------+----------+| 1 | zhangsan | 18 | 2 || 4 | zhangfei | 22 | 5 || 8 | zhengzheng | 33 | 5 |+-----+------------+------+----------+3 rows in set (0.00 sec)
C、分页
~~~C.a~~~ select * from 表 limit 2; - 前2行mysql> select * from a limit 2;+-----+----------+------+----------+| nid | name | age | part_nid |+-----+----------+------+----------+| 1 | zhangsan | 18 | 2 || 2 | wanwu | 18 | 2 |+-----+----------+------+----------+2 rows in set (0.00 sec)~~~C.b~~~ select * from 表 limit 2,5; - 从第2行开始的5行select * from 表 limit 5 offset 2 - 从第2行开始的5行 #2种写法一样 尽量用这种mysql> select * from a limit 5 offset 2;+-----+----------+------+----------+| nid | name | age | part_nid |+-----+----------+------+----------+| 3 | lisi | 19 | 3 || 4 | zhangfei | 22 | 5 || 5 | wuhua | 12 | 1 || 6 | laohu | 33 | 4 || 7 | houyi | 23 | 2 |+-----+----------+------+----------+5 rows in set (0.01 sec)
D、排序
~~~D.a~~~ select * from 表 order by 列 asc - 根据 “列” 从小到大排列mysql> select * from a order by part_nid;+-----+------------+------+----------+| nid | name | age | part_nid |+-----+------------+------+----------+| 5 | wuhua | 12 | 1 || 1 | zhangsan | 18 | 2 || 2 | wanwu | 18 | 2 || 7 | houyi | 23 | 2 || 3 | lisi | 19 | 3 || 6 | laohu | 33 | 4 || 4 | zhangfei | 22 | 5 || 8 | zhengzheng | 33 | 5 |+-----+------------+------+----------+8 rows in set (0.00 sec)~~~D.b~~~ select * from 表 order by 列 desc - 根据 “列” 从大到小排列mysql> select * from a order by part_nid desc;+-----+------------+------+----------+| nid | name | age | part_nid |+-----+------------+------+----------+| 4 | zhangfei | 22 | 5 || 8 | zhengzheng | 33 | 5 || 6 | laohu | 33 | 4 || 3 | lisi | 19 | 3 || 1 | zhangsan | 18 | 2 || 2 | wanwu | 18 | 2 || 7 | houyi | 23 | 2 || 5 | wuhua | 12 | 1 |+-----+------------+------+----------+8 rows in set (0.00 sec)~~~D.c~~~ select * from 表 order by 列1 desc,列2 asc - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序mysql> select * from a order by age desc,age asc;+-----+------------+------+----------+| nid | name | age | part_nid |+-----+------------+------+----------+| 6 | laohu | 33 | 4 || 8 | zhengzheng | 33 | 5 || 7 | houyi | 23 | 2 || 4 | zhangfei | 22 | 5 || 3 | lisi | 19 | 3 || 1 | zhangsan | 18 | 2 || 2 | wanwu | 18 | 2 || 5 | wuhua | 12 | 1 |+-----+------------+------+----------+8 rows in set (0.00 sec)
E、分组 group by 必须在where之后，order by之前
~~~E.a~~~ select 列名1 from 表 group by 列名1mysql> select part_nid from a group by part_nid;+----------+| part_nid |+----------+| 1 || 2 || 3 || 4 || 5 |+----------+5 rows in set (0.00 sec)~~~E.b~~~ select 列名1,列名2 from 表 group by 列名1,列名2mysql> select part_nid,age from a group by part_nid,age;+----------+------+| part_nid | age |+----------+------+| 1 | 12 || 2 | 18 || 2 | 23 || 3 | 19 || 4 | 33 || 5 | 22 || 5 | 33 |+----------+------+7 rows in set (0.00 sec)~~~E.c~~~ select 列名1,列名2 from 表 where 条件1 group by 列名,列名1 order by 列名1 descmysql> select part_nid,age from a where age < 30 group by part_nid,age order by age desc;+----------+------+| part_nid | age |+----------+------+| 2 | 23 || 5 | 22 || 3 | 19 || 2 | 18 || 1 | 12 |+----------+------+5 rows in set (0.00 sec)~~~E.d~~~ select 列名1,count(列名2) from 表 group by 列名1mysql> select part_nid,count(age) from a group by part_nid;+----------+------------+| part_nid | count(age) |+----------+------------+| 1 | 1 || 2 | 3 || 3 | 1 || 4 | 1 || 5 | 2 |+----------+------------+5 rows in set (0.00 sec)~~~E.e~~~ select 列名1 from 表 group by 列名1 having max(列名2) > 条件mysql> select part_nid from a group by part_nid having max(age) > 30;+----------+| part_nid |+----------+| 4 || 5 |+----------+2 rows in set (0.00 sec)
F、连表
mysql> select * from a;+-----+------------+------+----------+| nid | name | age | part_nid |+-----+------------+------+----------+| 1 | zhangsan | 18 | 2 || 2 | wanwu | 18 | 2 || 3 | lisi | 19 | 3 || 4 | zhangfei | 22 | 5 || 5 | wuhua | 12 | 1 || 6 | laohu | 33 | 4 || 7 | houyi | 23 | 2 || 8 | zhengzheng | 33 | 5 |+-----+------------+------+----------+8 rows in set (0.00 sec)mysql> select * from b;+-----+-----------+| nid | caption |+-----+-----------+| 1 | it || 2 | 驯兽师 || 3 | 飞行员 || 4 | 清洁员 || 5 | 所长 || 6 | 经理 |+-----+-----------+6 rows in set (0.00 sec)~~~F.a~~~ 关联显示select A.列名1, A.列名2, B.列名2from A,BWhere A.列名3 = B.列名3mysql> select a.nid,a.name,a.age,b.caption from a,b where a.part_nid = b.nid;+-----+------------+------+-----------+| nid | name | age | caption |+-----+------------+------+-----------+| 5 | wuhua | 12 | it || 1 | zhangsan | 18 | 驯兽师 || 2 | wanwu | 18 | 驯兽师 || 7 | houyi | 23 | 驯兽师 || 3 | lisi | 19 | 飞行员 || 6 | laohu | 33 | 清洁员 || 4 | zhangfei | 22 | 所长 || 8 | zhengzheng | 33 | 所长 |+-----+------------+------+-----------+8 rows in set (0.00 sec)~~~F.b~~~ 无对应关系则不显示select A.列名1, A.列名2, B.列名2from A inner join Bon A.列名3 = B.列名3mysql> select a.nid,a.name,a.age,b.caption from a inner join b where a.part_nid = b.nid;+-----+------------+------+-----------+| nid | name | age | caption |+-----+------------+------+-----------+| 5 | wuhua | 12 | it || 1 | zhangsan | 18 | 驯兽师 || 2 | wanwu | 18 | 驯兽师 || 7 | houyi | 23 | 驯兽师 || 3 | lisi | 19 | 飞行员 || 6 | laohu | 33 | 清洁员 || 4 | zhangfei | 22 | 所长 || 8 | zhengzheng | 33 | 所长 |+-----+------------+------+-----------+8 rows in set (0.00 sec)~~~F.c~~~ A表所有显示，如果B中无对应关系，则值为nullselect A.列名1, A.列名2, B.列名2from A left join Bon A.列名3 = B.列名3mysql> select a.nid,a.name,a.age,b.caption from a left join b on a.part_nid = b.nid;+-----+------------+------+-----------+| nid | name | age | caption |+-----+------------+------+-----------+| 5 | wuhua | 12 | it || 1 | zhangsan | 18 | 驯兽师 || 2 | wanwu | 18 | 驯兽师 || 7 | houyi | 23 | 驯兽师 || 3 | lisi | 19 | 飞行员 || 6 | laohu | 33 | 清洁员 || 4 | zhangfei | 22 | 所长 || 8 | zhengzheng | 33 | 所长 |+-----+------------+------+-----------+8 rows in set (0.00 sec)~~~F.e~~~ B表所有显示，如果B中无对应关系，则值为nullselect A.列名1, A.列名2, B.列名2from A right join Bon A.列名3 = B.列名3mysql> select a.nid,a.name,a.age,b.caption from a right join b on a.part_nid = b.nid;+------+------------+------+-----------+| nid | name | age | caption |+------+------------+------+-----------+| 1 | zhangsan | 18 | 驯兽师 || 2 | wanwu | 18 | 驯兽师 || 3 | lisi | 19 | 飞行员 || 4 | zhangfei | 22 | 所长 || 5 | wuhua | 12 | it || 6 | laohu | 33 | 清洁员 || 7 | houyi | 23 | 驯兽师 || 8 | zhengzheng | 33 | 所长 || NULL | NULL | NULL | 经理 |+------+------------+------+-----------+9 rows in set (0.00 sec)
G、组合
~~~G.a~~~ 组合，自动处理重合select 列名 from A union select 列名 from Bmysql> select nid from a union select nid from b;+-----+| nid |+-----+| 1 || 2 || 3 || 4 || 5 || 6 || 7 || 8 |+-----+8 rows in set (0.00 sec)~~~G.b~~~ 组合，不处理重合select 列名 from A union all select 列名 from Bmysql> select nid from a union all select nid from b;+-----+| nid |+-----+| 1 || 2 || 3 || 4 || 5 || 6 || 7 || 8 || 1 || 2 || 3 || 4 || 5 || 6 |+-----+14 rows in set (0.00 sec)
一时失言乱红尘
你的名字
灰太狼
还没有评论，来说两句吧...