数据库基本操作（二） DML语句：对数据库表记录的操作

阳光穿透心脏的1/2处 2022-06-09 12:10 313阅读 0赞

#### 以下语句要注意逗号，分号的中英文，不要直接复制 ####

> 插入记录

mysql>insert into emp(age1,ename,birth,hiredate,sal,depton)values(23,'lisa'，'2017-02-03','2008-05-06','1000',1);
    //下面的效果是一样的，但是必须保持与字段一一对应
    mysql>insert into emp values(23,'lisa'，'2017-02-03','2008-05-06','1000',1);

![这里写图片描述][SouthEast]

> 插入部分

mysql>insert into emp(age1,ename)values(23,'lisa');

![这里写图片描述][SouthEast 1]

> 查询所有

mysql>select * from emp;//最常见的mysqlyuju

> 对表格中一次性插入多条数据

mysql>insert into dept values(1,'kangkang'),(2,'lili');

![这里写图片描述][SouthEast 2]

> 更新数据

mysql>update emp set deptname = "biaoge" where deptno = 5;

![这里写图片描述][SouthEast 3]

> 根据条件同时更新多个表格中的数据

mysql> update  emp a,dept b  set a.ename = 'zhangwei',b.deptname='zhanglan' where a.age1=b.deptno;
    //根据两个表格中相同的部分来更新数据

![这里写图片描述][SouthEast 4]

> 删除记录

mysql>delete from emp where ename ='zhangwei';

![这里写图片描述][SouthEast 5]

> 跟据条件搜索

mysql>select ename,birth from emp;

![这里写图片描述][SouthEast 6]

> 搜索后排序

mysql>select * from emp order by age1;

![这里写图片描述][SouthEast 7]

> 搜索后排序，有重复情况

mysql>select * from emp order by age1，deptno;

![这里写图片描述][SouthEast 8]

> 搜索后排序，有重复情况 降序

mysql>select * from emp order by age1，deptno desc;

![这里写图片描述][SouthEast 9]

> 搜索后排序，限制条数

mysql>select * from emp order by age1 limit 2

![这里写图片描述][SouthEast 10]

> 搜索后排序，限制条数 固定开始行

mysql>select * from emp order by age1 limit 1,3;

![这里写图片描述][SouthEast 10]

> 根据条件搜索

mysql>select * from emp where age1>23 order by age1;

![这里写图片描述][SouthEast 11]

#### 这里插一句话 ####

外连接分为左连接和右连接  
左连接：包含所有左边表中的记录甚至是有边表中没有和他匹配的  
记录  
右连接：包含所有有边表中的记录甚至是左边表格中没有和他匹配  
的记录

> 连表查询

mysql> select * from emp where deptno in (select deptno from dept);

![这里写图片描述][SouthEast 12]

> 联合

mysql> select deptno from emp
        -> union all
        -> select deptno from dept;

![这里写图片描述][SouthEast 13]

> 取消联合

mysql> select deptno from emp
        -> union
        -> select deptno from dept;

[SouthEast]: /images/20220609/41f24a5d962342f3936973f2a77d29bd.png
[SouthEast 1]: /images/20220609/7a0a397b55374e1da418596ba805c82a.png
[SouthEast 2]: /images/20220609/1d192a5d8edb4486ae50f5e7978c30eb.png
[SouthEast 3]: /images/20220609/dd2ca12d57794752a84e3443bc1255e8.png
[SouthEast 4]: /images/20220609/f9c1e71e45b74b4d8f2aa0cd9ee2c4a1.png
[SouthEast 5]: /images/20220609/3f0386cd839c4bdd808e92e31b2a1aec.png
[SouthEast 6]: /images/20220609/e8e9fe5a3d8e4934bd0d174275d3a69b.png
[SouthEast 7]: /images/20220609/71a85e6dec7c4674a792071a0167ee91.png
[SouthEast 8]: /images/20220609/c4542cb0185a40c7acec4cd5519e84d6.png
[SouthEast 9]: /images/20220609/f61b2cbc97494c518c294e2d0a611134.png
[SouthEast 10]: /images/20220609/091ffa0e65f54e3dab2df43cb98c50f1.png
[SouthEast 11]: /images/20220609/251be7160844420cbf2915dfcf7c75df.png
[SouthEast 12]: /images/20220609/28931f5402344831bce872ce24fff16b.png
[SouthEast 13]: /images/20220609/98b06fc873904661a57f0f1dd1d2c4d1.png