MySQL简单查询和单表查询
程序员文章站
2022-04-29 18:42:27
MySQL记录操作 概览 MySQL数据操作： DML 在MySQL管理软件中，可以通过SQL语句中的DML语言来实现数据的操作，包括 1. 使用INSERT实现数据的插入 2. UPDATE实现数据的更新 3. 使用DELETE实现数据的删除 4. 使用SELECT查询数据以及。 插入数据inse ......
mysql记录操作
概览
mysql数据操作： dml
在mysql管理软件中，可以通过sql语句中的dml语言来实现数据的操作，包括
- 使用insert实现数据的插入
- update实现数据的更新
- 使用delete实现数据的删除
- 使用select查询数据以及。
插入数据insert
1. 插入完整数据（顺序插入）
语法一：
insert into 表名(字段1,字段2,字段3…字段n) values(值1,值2,值3…值n);
语法二：
insert into 表名 values (值1,值2,值3…值n);
2. 指定字段插入数据
语法：
insert into 表名(字段1,字段2,字段3…) values (值1,值2,值3…);
3. 插入多条记录
语法：
insert into 表名 values
(值1,值2,值3…值n),
(值1,值2,值3…值n),
(值1,值2,值3…值n);
4. 插入查询结果
语法：
insert into 表名(字段1,字段2,字段3…字段n)
select (字段1,字段2,字段3…字段n) from 表2
where …;
更新数据update
语法：
update 表名 set
字段1=值1,
字段2=值2,
where condition;
示例：
update mysql.user set password=password(‘123’)
where user=’root’ and host=’localhost’;
删除数据delete
语法：
delete from 表名
where conition;
示例：
delete from mysql.user
where password=’’;
练习：
更新mysql root用户密码为mysql123
删除除从本地登录的root用户以外的所有用户
查询数据 search
1.单表查询
关键字执行的优先级:
from where group by select distinct having order by limit
1.找到表:from
2.拿着where指定的约束条件，去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.执行select（去重）
5.将分组的结果进行having过滤
6.将结果按条件排序：order by
7.限制结果的显示条数
简单查询
#创建表
create table employee(
id int not null unique auto_increment,
emp_name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);
#查看表结构
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| field | type | null | key | default | extra |
+--------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | no | pri | null | auto_increment |
| emp_name | varchar(20) | no | | null | |
| sex | enum('male','female') | no | | male | |
| age | int(3) unsigned | no | | 28 | |
| hire_date | date | no | | null | |
| post | varchar(50) | yes | | null | |
| post_comment | varchar(100) | yes | | null | |
| salary | double(15,2) | yes | | null | |
| office | int(11) | yes | | null | |
| depart_id | int(11) | yes | | null | |
+--------------+-----------------------+------+-----+---------+----------------+
#插入记录
#三个部门：教学，销售，运营
insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','办事处',7300.33,401,1), #以下是教学部
('anwen','male',78,'20150302','teacher',1000000.31,401,1),
('wudi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwen','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),
('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),
('张xx','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;
#ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk
准备表和记录
#简单查询
select id,emp_name,sex,age,hire_date,post,post_comment,salary,office,depart_id
from employee;
select * from employee;
select emp_name,salary from employee;
#避免重复distinct
select distinct post from employee;
#通过四则运算查询
select emp_name, salary*12 from employee;
select emp_name, salary*12 as annual_salary from employee;
select emp_name, salary*12 annual_salary from employee;
#定义显示格式
concat() 函数用于连接字符串
select concat('姓名: ',emp_name,' 年薪: ', salary*12) as annual_salary
from employee;
concat_ws() 第一个参数为分隔符
select concat_ws(':',emp_name,salary*12) as annual_salary
from employee;
结合case语句：
select
(
case
when emp_name = 'jingliyang' then
emp_name
when emp_name = 'alex' then
concat(emp_name,'_bigsb')
else
concat(emp_name, 'sb')
end
) as new_name
from
employee;
小练习：
1 查出所有员工的名字，薪资,格式为
<名字:egon> <薪资:3000>
2 查出所有的岗位（去掉重复）
3 查出所有员工名字，以及他们的年薪,年薪的字段名为annual_year
select concat('<名字:',emp_name,'> ','<薪资:',salary,'>') from employee;
select distinct depart_id from employee;
select emp_name,salary*12 annual_salary from employee;
where约束
where字句中可以使用：
- 比较运算符：> < >= <= <> !=
- between 80 and 100 值在80到100之间
- in(80,90,100) 值是80或90或100
- like 'e%'
通配符可以是%或_，
%表示任意多字符
_表示一个字符 - 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
#1:单条件查询
select emp_name from employee
where post='sale';
#2:多条件查询
select emp_name,salary from employee
where post='teacher' and salary>10000;
#3:关键字between and
select emp_name,salary from employee
where salary between 10000 and 20000;
select emp_name,salary from employee
where salary not between 10000 and 20000;
#4:关键字is null(判断某个字段是否为null不能用等号，需要用is)
select emp_name,post_comment from employee
where post_comment is null;
select emp_name,post_comment from employee
where post_comment is not null;
select emp_name,post_comment from employee
where post_comment=''; 注意''是空字符串，不是null
ps：
执行
update employee set post_comment='' where id=2;
再用上条查看，就会有结果了
#5:关键字in集合查询
select emp_name,salary from employee
where salary=3000 or salary=3500 or salary=4000 or salary=9000 ;
select emp_name,salary from employee
where salary in (3000,3500,4000,9000) ;
select emp_name,salary from employee
where salary not in (3000,3500,4000,9000) ;
#6:关键字like模糊查询
通配符’%’
select * from employee
where emp_name like 'eg%';
通配符’_’
select * from employee
where emp_name like 'al__';
小练习：
1. 查看岗位是teacher的员工姓名、年龄 2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄 3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资 4. 查看岗位描述不为null的员工信息 5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资 6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资 7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪 select emp_name,age from employee where post = 'teacher'; select emp_name,age from employee where post='teacher' and age > 30; select emp_name,age,salary from employee where post='teacher' and salary between 9000 and 10000; select * from employee where post_comment is not null; select emp_name,age,salary from employee where post='teacher' and salary in (10000,9000,30000); select emp_name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000); select emp_name,salary*12 from employee where post='teacher' and emp_name like 'jin%';
group by
单独使用group by关键字分组
select post from employee group by post;
注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数
group by关键字和group_concat()函数一起使用
select post,group_concat(emp_name) from employee group by post;#按照岗位分组，并查看组内成员名
select post,group_concat(emp_name) as emp_members from employee group by post;
group by与聚合函数一起使用
select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人
强调：
如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义
多条记录之间的某个字段值相同，该字段通常用来作为分组的依据
聚合函数
#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组
示例：
select count(*) from employee;
select count(*) from employee where depart_id=1;
select max(salary) from employee;
select min(salary) from employee;
select avg(salary) from employee;
select sum(salary) from employee;
select sum(salary) from employee where depart_id=3;
小练习：
1. 查询岗位名以及岗位包含的所有员工名字 2. 查询岗位名以及各岗位内包含的员工个数 3. 查询公司内男员工和女员工的个数 4. 查询岗位名以及各岗位的平均薪资 5. 查询岗位名以及各岗位的最高薪资 6. 查询岗位名以及各岗位的最低薪资 7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资 #题1：分组 mysql> select post,group_concat(emp_name) from employee group by post; #题目2： mysql> select post,count(id) from employee group by post; #题目3： mysql> select sex,count(id) from employee group by sex; #题目4： mysql> select post,avg(salary) from employee group by post; #题目5 mysql> select post,max(salary) from employee group by post; #题目6 mysql> select post,min(salary) from employee group by post; #题目七 mysql> select sex,avg(salary) from employee group by sex;
having过滤
having与where不一样的地方在于!!!!!!
#！！！执行优先级从高到低：where > group by > having #1. where 发生在分组group by之前，因而where中可以有任意字段，但是绝对不能使用聚合函数。 #2. having发生在分组group by之后，因而having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
select * from emp where salary > 100000; mysql> select post,group_concat(emp_name) from emp group by post having salary > 10000;#错误，分组后无法直接取到salary字段 error 1054 (42s22): unknown column 'salary' in 'having clause' mysql> select post,group_concat(emp_name) from emp group by post having avg(salary) > 10000;
小练习：
1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数 3. 查询各岗位平均薪资大于10000的岗位名、平均工资 4. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资 select post,group_concat(emp_name),count(id) from employee group by post having count(id) < 2; select post,avg(salary) from employee group by post having avg(salary) > 10000; select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) <20000;
order by 查询排序
按单列排序
select * from employee order by salary;
select * from employee order by salary asc;
select * from employee order by salary desc;
按多列排序:先按照age排序，如果年纪相同，则按照薪资排序
select * from employee
order by age,
salary desc;
小练习：
1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序 2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列 3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列 select * from employee order by age asc,hire_date desc; select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc; select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc;
limit 限制查询的记录数
示例：
select * from employee order by salary desc
limit 3; #默认初始位置为0
select * from employee order by salary desc
limit 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条
select * from employee order by salary desc
limit 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
小练习：
select * from employee limit 0,5; select * from employee limit 5,5; select * from employee limit 10,5;
使用正则表达式查询
select * from employee where emp_name regexp '^ale';
select * from employee where emp_name regexp 'on$';
select * from employee where emp_name regexp 'm{2}';
小结：对字符串匹配的方式
where emp_name = 'egon';
where emp_name like 'yua%';
where emp_name regexp 'on$';