数据库基础操作

目录

1.数据操作
1.1 增:insert
1.2 删:delete
1.3 改:update
1.4 编码问题
2. 单表查询
2.1 基础查询
2.2 拼接concat
2.3 where语句
2.4 聚合函数
2.5 分组 group by
2.6 过滤 having
2.7 排序 order by
2.8 limit的使用

数据库中的数据操作以及单表操作

1.数据操作

1.1:增insert

insert into 表名 values(1,'alex',23,'female',2.2,'linux,python'), (1,'alex',23,'female',2.2,'linux,python');
insert into 表名(id,name) values(5,'wusir'),(6,'wusir'); ​
insert into emp2 select * from emp; 将emp的数据导入到emp2里 ​
insert into emp2(id,name) select id,name from emp;将emp的id和name导入到emp2里

1.2.删:delete

delete from 表名 where 条件 删除一行数据

1.3.改:update

update 表 set 字段1=值1,字段2=值2 where 条件; ​
\c取消当前操作, '>' \c 取消当前操作

1.4编码问题:

1.临时解决问题 在客户端执行 set xxxx = utf8;
2.永久解决问题 在my.ini添加 set xxxx=utf8;
3.实时解决问题 创建表的时候 create table 表名() charset=utf8;

2.单表查询

2.1基础查询

select:(并不能直接在表中进行修改,只是在临时修改后查询到)
select * from 表名;
指定列查询
select name,age from 表名;
在列中进行四则运算(查找姓名和薪水*12打印出来的表)
select name,salary*12 from employee;
重命名(并不能真正修改,只是修改后查询出来)
select name,salary as annul_salary from employee;
select name,salary annul_salary from employee;
去重
select distinct post from 表名;
select distinct sex,post from 表名;

2.2 拼接concat

函数concat()拼接
select concat('要拼接的',name),concat('要拼接的',age)from 表名;
select concat_ws('|','a','b','c');

2.3 where语句

case when语句 ==if 条件判断句
select语句筛选列
where语句筛选行
​ where语句:根据条件筛选行
可用范围符号进行筛选:= > < >= <= != <>(不等于)
select * from employee where age>18;
范围: between a and b 在什么什么之间
select * from employee where age between 10 and 20 只找age里面10~20之间的
精准范围: in
select * from employee where age in (10,20);只找age里等于10和20的
模糊查询 like
通配符 _(表示一个字符长度的任意内容)
select * from employee where name like 'jin_'一个_代表一个字符
通配符 % 表示任意字符的任意内容
select * from employee where name like 'jin%' name里面以jin开头的
select * from employee where name like '%g' name里面以g结尾的
select * from employee where name like '%g%' name里面带g的数值
正则匹配 regexp
select * from employee where emp_name regexp '^jin'
逻辑运算
and
select * from employee where age>18 and post='teacher';
查询employee表里面age大于18的而且post是teacher的
or
select * from employee where salary20000;
查询employee表里面salary小于1000的或者是salary大于20000的
not
select * from employee where salary not in(1000,2000);不是1000和2000的
查询employee表里面salary里不是1000和2000的
​ 关于null:
查看岗位描述为NULL的员工信息
select * from employee where comment(指岗位描述) is null;
查看岗位描述不为NULL的员工信息
select * from employee where comment(指岗位描述) is not null;
​

2.4聚合函数

聚合函数:
count:计数看看有多少行(非空的)
select count(salary) from employee;
max:最大值
min:最小值
avg:平均值
select avg(salary) from employee;
sum:相加
select sum(salary) from employee;
分组
select * from 表 where 条件 group by 分组;
分组聚合
select post,max(salary) from employee group by post;
​ select group_connect(emp_nae) from employee group by post;

2.5 分组 group by

分组
select * from 表 where 条件 group by 分组;
分组聚合
select post,max(salary) from employee group by post;
​ select group_connect(emp_nae) from employee group by post;

2.6 过滤 having

过滤 hving(group by + 聚合函数)
查询平均年龄大于15的人员
select name,avg(age) from employee group by post having avg(age)>15;
查询各岗位内包含的员工个数小于2的岗位名,岗位名包含员工姓名,个数
select post,name,count(id) from employee group b post having count(id)<2;
查询各岗位平均薪资大于10000的岗位名,平均薪资
select post,avg(salary) from employee group by post having avg(salary)>10000;
3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;

2.7 排序 order by

排序:
order by
升序
select * from employee order by salary;
select * from employee order by salary asc;
降序
select * from employee order by salary desc;
​ select * from employee order by age,salary;
select * from employee order by age,salary desc;
select * from employee order by age desc,salary;

2.8 limit的使用

limit:
select * from 表 order by 列 limit n; 取前n条
select * from 表 order by 列 limit m,n; 从m开始,取n条
select * from 表 order by 列 limit n offset m; 从m+1开始,取n条
select * from 表 where 条件 group by 分组 having 过滤 order by 排序 limit n;
回到顶部