mysql学习4：select,update,insert,delete

admin 管理员组

文章数量: 887021

mysql学习4：select,update,insert,delete

查询表格：
基本语法：
select 属性列表 from 表 [where 表达式] [group by 属性列表 [having 表达式]] [order by 属性 [asc/desc],属性 [asc/desc]] [limit [起始位置,]数量];

mysql> select * from employer;
+-----+--------+-----+------+------------+
| num | name   | sex | age  | homeaddr   |
+-----+--------+-----+------+------------+
|   1 | 黄冲   | 男  |   22 | 1号街区    |
|   2 | 张三   | 男  |   25 | NULL       |
|   3 | 李四   | 女  |   23 | 4号街区    |
|   4 | 王五   | 女  |   20 | 5号街区    |
|   5 | 王二   | 男  |   19 | 5号街区    |
|   6 | 麻子   | 女  |   26 | 6号街区    |
+-----+--------+-----+------+------------+
select name from employer;查找部分数据。将他们列出来。
select group_concat(name),sex from employer group by sex;用sex进行分类，列出name。一般group by每个分组只显示一条记录，都是用函数count(name)。
where语句：
select * from employer where homeaddr like "5%";匹配符%是多个匹配多个字节；homeaddr='5%'是不行的。'_'匹配一个字节。like要比=强一些。
select * from employer where num in(1,2,3);在in里面的集合。not in取反集合。
select * from employer where age between 20 and 25 order by age desc,num asc;not between；order by age desc用age排序asc升序,num asc辅助降序，这个与where语句无关。
select * from employer where homeaddr is not NULL;
where用and，or跟c语言一样的啦。
select name,sex,age from employer where sex='女' and age between 20 and 25 order by age asc;2个and不一样的啦。
select num,age,homeaddr from employer where sex like '男' and age<25 and num<>1;
mysql> select * from employer where sex like '女' and age>20 or homeaddr is not null and age<>19 limit 1,2;and比or优先级高。
+-----+--------+-----+------+------------+
| num | name   | sex | age  | homeaddr   |
+-----+--------+-----+------+------------+
|   1 | 黄冲   | 男  |   22 | 1号街区    |
|   3 | 李四   | 女  |   23 | 4号街区    |
|   4 | 王五   | 女  |   20 | 5号街区    |
|   6 | 麻子   | 女  |   26 | 6号街区    |
+-----+--------+-----+------+------------+
select distinct homeaddr from employer;distinct取消重复。5号街区打印一次。


group by分组：
select * from employer group by sex,homeaddr;
select sex,count(sex) from employer group by sex having sex like '男' and count(sex)>2;group
select sex,group_concat(name) from employer group by sex WITH ROLLUP;WITH ROLLUP和group_concat,count使用。多显示一行求总和。
函数：GROUP_CONCAT(),count(*/字段),SUM(),AVG(),MAX(),MIN();
select sex,avg(age) from employer group by sex;


mysql> select * from suremployer;
+-----------+------+--------------+------+-----+----------------------+
| num       | d_id | name         | age  | sex | homeaddr             |
+-----------+------+--------------+------+-----+----------------------+
| 201500001 | 1000 | 张三         |   25 | 男  | students street      |
| 201500002 | 1012 | 李四         |   24 | 女  | class street         |
| 201500003 | 1010 | 李四         |   24 | 女  | grade street         |
| 201500004 | 1011 | 王五         |   21 | 女  | home street          |
| 201500005 | 1012 | 黄六         |   27 | 女  | communication street |
| 201500006 | 1013 | 麻子望而     |   19 | 男  | hospital street      |
| 201500007 |  800 | 廖坑         |   32 | 女  | company street       |
| 201500008 | 1011 | 吴杰         |   26 | 女  | chongqing street     |
| 201500009 | 1010 | 杨冲         |   30 | 男  | hurryup street       |
| 201500010 | 1013 | 温江         |   26 | 男  | breakout street      |
| 201500011 | 1000 | 张杰         |   42 | 男  | mountion street      |
| 201500012 |  800 | 吴失         |   28 | 女  | department street    |
| 201500013 | 1013 | 杨慧         |   30 | 女  | localhost street     |
| 201500014 | 1012 | 红丝         |   34 | 男  | computer street      |
+-----------+------+--------------+------+-----+----------------------+
mysql> select * from surdepartment;
+------+-----------+----------+------+
| id   | name      | function | addr |
+------+-----------+----------+------+
|  800 | 采购部    | NULL     | NULL |
| 1000 | 生产部    | NULL     | NULL |
| 1001 | 档案部    | NULL     | NULL |
| 1010 | 销售部    | NULL     | NULL |
| 1011 | 管理部    | NULL     | NULL |
| 1012 | 财务部    | NULL     | NULL |
| 1013 | 物料部    | NULL     | NULL |
+------+-----------+----------+------+

多表组合查询：
内联查询：where
select num,suremployer.name as employer_name, age, sex, surdepartment.name as department_name from suremployer, surdepartment where suremployer.d_id=surdepartment.id;
如果没有where suremployer.d_id=surdepartment.id将打印两表的合并组合信息：5(一表有5行)*4(2表有4行)=20条信息。
外联查询：left/right join on。
select num,suremployer.name as employer_name, age, sex, surdepartment.name as department_name from suremployer, surdepartment left join on suremployer.d_id=surdepartment.id;以左表为主查询，用右表添加到左表。
复合条件查询:where或者on 有and或者OR。
select num,suremployer.name as employer_name, age, sex, surdepartment.name as department_name from suremployer, surdepartment left join on suremployer.d_id=surdepartment.id and suremployer.age>25;

where用in,not in,any,all,exists,not exist.
select * from suremployer where d_id in(select id from surdepartment where id=1012);
mysql> select * from suremployer where d_id in(select id from surdepartment where id=1012);
+-----------+------+--------+------+-----+----------+
| num       | d_id | name   | age  | sex | homeaddr |
+-----------+------+--------+------+-----+----------+
| 201500002 | 1012 | 李四   |   24 | 女  | NULL     |
| 201500005 | 1012 | 黄六   |   27 | 女  | NULL     |
| 201500014 | 1012 | 红丝   |   34 | 男  | NULL     |
+-----------+------+--------+------+-----+----------+
mysql> select * from surdepartment where id not in(select d_id from suremployer);
+------+-----------+----------+------+
| id   | name      | function | addr |
+------+-----------+----------+------+
| 1001 | 档案部    | NULL     | NULL |
+------+-----------+----------+------+
select * from suremployer where d_id>=(select id from surdepartment where id=1013);>=后面需要一个值，而不是一个组合。还包括：>,<,<=,!=,<>,=等。
mysql> select * from suremployer where d_id>1009 and exists(select name from surdepartment where id=1000);exists用来判断后面的语句是否成功，后面的语句有数据则为1，无数据则为0。如果用 not exists则相反。
select * from suremployer where d_id>any(select id from surdepartment where id<1012);any任意一个，只要d_id>any最小值即满足条件。对应的all需要满足d_id>all最大值。

合并查询结果：
select d_id from suremployer union select id from surdepartment;将suremployer表与surdepartment的ID无重复组合。union all则用于罗列suremployer的id然后在罗列surdepartment的id。

为表格取别名：
select * from suremployer a where a.num>201500010;a为suremployer的别名。就可以使用a.num了。
为字段取别名：
select num as employer_ID,name as employer_name from suremployer where d_id=1013;
组合：
select a.num as employer_ID,a.name as employer_name from suremployer a where a.d_id=1013;

使用正则表达式：regexp
//
    ^l:以l或者字符串开头。
    $l:以l或者字符串结尾。
    '.':匹配一个字节
    [abc]:集合，匹配其中一个即可。
    [^abc]:集合，不在该集合即可。
    string1|string2|string3:匹配一个字符串即可。
    a*c:*表示c之前出现过0个或者一个'a'。出现c即满足条件。
    a+c:+表示c前一个字符是1个'a'。
    string{n}:string出现n次。
    string{m,n}:string出现m~n次。至少m次，最多n次。

select * from suremployer where name regexp '杨';包含'杨'即可满足条件。相当于匹配一个字符串。
select * from suremployer where name regexp '$杨';以'杨'结尾可满足条件。插入一条字符串，一般情况下都是以‘\0’结束。
select * from suremployer where name regexp '^杨';以'杨'开头可满足条件。
select * from suremployer where name regexp '[子]';没有考虑中文的情况，'子'有两个字节，匹配其中一个即可。
select * from suremployer where name regexp '[1-9a-d]';
select * from suremployer where name regexp '[^1-9a-d]';
mysql> select * from suremployer where homeaddr regexp 'la|ca|an';包含3个字符串其中一个即可
mysql> select * from suremployer where homeaddr regexp 'm{2}';m连续出现2次。



插入：
insert into employer(num,name,sex) values(2,'张三','男'),(3,'李四','女');(num,name,sex)指定插入参数，没有这些默认所有字段都需要提供；插入可以多条。
mysql> insert into employer values(1,'李四','女','4号街区');
insert into usetable1(id,name,sex) select mid,mname,msex from usetable2 where departmentID='采购部';将表格usetable2的数据插入到usetable1中。
insert into temptable select num,name,age,sex from suremployer;
mysql> insert into temptable(id,name,age,sex) select num,name,age,sex from suremployer where age>23 and age<31;
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'意思是主键需要唯一，1在主键中已经有了。
自增字段的插入：insert into surdepartment(id,name) values(null,'物料部');id为主键和自增。但id也可以指定。


修改：
update employer set name='黄冲',sex='男',homeaddr='1号街区' where num=1;

删除：
mysql> delete from suremployer where name='李四' and homeaddr='class street';条件删除
mysql> delete from suremployer;全部删除

本文标签： mysql学习4select update insert delete