UPDate语句
1.可以同时更新多个表的语句:
update tabname a,tabname2 b set a.fideld1 =expr1,b.tabname2= expr2 where.....;
例如:
update kong a,emp1 b set a.name=66,b.ename =77 where ename =100;
tablename:emp1
+-------+------------+------+------+------+
| ename | hiredate | sal | name | age |
+-------+------------+------+------+------+
| 3 | NULL | NULL | 66 | NULL |
| 77 | NULL | NULL | 66 | NULL |
| aa | 2001-03-03 | NULL | 66 | NULL |
+-------+------------+------+------+------+
tablename:kong
+------+------+
| name | age |
+------+------+
| 66 | NULL |
+------+------+
2.删除记录:
delete from tablename where
mysql> delete from emp1 where ename=77;
Query OK, 1 row affected (0.01 sec)
mysql> select*from emp1;
+-------+------------+------+
| ename | hiredate | sal |
+-------+------------+------+
| 3 | NULL | NULL |
| aa | 2001-03-03 | NULL |
+-------+------------+------+
2 rows in set (0.00 sec)
同时也可以删除多张表,在where后面用end 连接多个表的条件
mysql> select*from emp1 order by ename;
+-------+------------+------+
| ename | hiredate | sal |
+-------+------------+------+
| 3 | NULL | NULL |
| 44 | NULL | NULL |
| 55 | NULL | NULL |
| 66 | NULL | NULL |
| aa | 2001-03-03 | NULL |
+-------+------------+------+
5 rows in set (0.00 sec)
如果有并列数据,可以在增加其他的条件mysql> select*from emp1 order by ename,hiredate desc;
+-------+------------+------+
| ename | hiredate | sal |
+-------+------------+------+
| NULL | 2016-10-11 | NULL |
| 3 | NULL | NULL |
| 3 | NULL | NULL |
| 44 | NULL | NULL |
| 55 | NULL | NULL |
| 66 | NULL | NULL |
| aa | 2001-03-03 | NULL |
+-------+------------+------+
7 rows in set (0.00 sec)
mysql>
如果只是希望显示一部分的数据可以使用LIMIT关键字mysql> select*from emp1 order by ename limit 3;//显示了前三行的数据
+-------+------------+------+
| ename | hiredate | sal |
+-------+------------+------+
| NULL | 2016-10-11 | NULL |
| 3 | NULL | NULL |
| 3 | NULL | NULL |
+-------+------------+------+
3 rows in set (0.00 sec)
如果想找行数之间的数据mysql> select*from emp1 order by ename limit 2,4;
+-------+----------+------+
| ename | hiredate | sal |
+-------+----------+------+
| 3 | NULL | NULL |
| 44 | NULL | NULL |
| 55 | NULL | NULL |
| 66 | NULL | NULL |
+-------+----------+------+
mysql> select count(1) from emp1;//统计empty 表中有多少的数据
+----------+
| count(1) |
+----------+
| 7 |
+----------+
mysql> select ename,count(1) from emp1 group by ename;//统计ename 的个个数据数量
+-------+----------+
| ename | count(1) |
+-------+----------+
| NULL | 1 |
| 3 | 2 |
| 44 | 1 |
| 55 | 1 |
| 66 | 1 |
| aa | 1 |
+-------+----------+
mysql> select ename,count(1) from emp1 group by ename with rollup;//统计之后在次汇总
+-------+----------+
| ename | count(1) |
+-------+----------+
| NULL | 1 |
| 3 | 2 |
| 44 | 1 |
| 55 | 1 |
| 66 | 1 |
| aa | 1 |
| NULL | 7 |
+-------+----------+
mysql> select ename,count(1) from emp1 group by ename having count(1)>1;//ename 中 3出现的次数是大于1的;
+-------+----------+
| ename | count(1) |
+-------+----------+
| 3 | 2 |
+-------+----------+
mysql> select*from kong;
+------+------+
| name | age |
+------+------+
| 66 | NULL |
| 11 | 22 |
| 66 | 88 |
+------+------+
mysql> select*from emp1;
+-------+------------+------+
| ename | hiredate | sal |
+-------+------------+------+
| 3 | NULL | NULL |
| aa | 2001-03-03 | NULL |
| 44 | NULL | NULL |
| 55 | NULL | NULL |
| 66 | NULL | NULL |
| 3 | NULL | NULL |
| NULL | 2016-10-11 | NULL |
+-------+------------+------+
内链select name,ename from kong,emp1 where kong.name = emp1.ename;//将两个不同的表,相互匹配
+------+-------+
| name | ename |
+------+-------+
| 66 | 66 |
| 66 | 66 |
+------+-------+
//外链:左链接和有链接mysql> select ename,name from emp1 left join kong on emp1.ename=kong.name;//列出两个表全部的对应关系没有的会用Null
+-------+------+
| ename | name |
+-------+------+
| 66 | 66 |
| 66 | 66 |
| 3 | NULL |
| aa | NULL |
| 44 | NULL |
| 55 | NULL |
| 3 | NULL |
| NULL | NULL |
+-------+------+
//子查询:查找出一个表中的另一个表的记录mysql> select*from emp1 where ename in(select name from kong);
+-------+----------+------+
| ename | hiredate | sal |
+-------+----------+------+
| 66 | NULL | NULL |
+-------+----------+------+