如何在MySQL中进行简单的增删改查
如何在MySQL中进行简单的增删改查
数据准备：
-- 创建dept表并设置主键
create table dept(
deptno int(2) primary key ,
dname varchar(14),
loc varchar(13)
);
-- 查看表结构
desc dept;
-- 插入数据
insert into dept values (10,'ACCOUNTING','NEW YORK'),
(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSTON');
-- 创建emp表并设置主键和外键约束
create table emp(
empno int(4) primary key,
ename varchar(10),
job varchar(9),
mgr int(4),
hiredate date,
sal float(7,2),
comm float(7,2),
deptno int(2),
constraint deptno foreign key(deptno) references dept(deptno)
);
-- 查看表结构
desc emp;
-- 插入数据
insert into emp values (7369,'SMITH','CLERK',7902,'1980-12-17',800.00,NULL,20),
(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600.00,300.00,30),
(7521,'WARD','SALESMAN',7698,'1981-02-22',1250.00,500.00,30),
(7566,'JONES','MANAGER',7839,'1981-04-02',2975.00,NULL,20),
(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250.00,1400.00,30),
(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850.00,NULL,30),
(7782,'CLARK','MANAGER',7839,'1981-06-09',2450.00,NULL,10),
(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000.00,NULL,20),
(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000.00,NULL,10),
(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500.00,0.00,30),
(7876,'ADAMS','CLERK',7788,'1987-05-23',1100.00,NULL,20),
(7900,'JAMES','CLERK',7698,'1981-12-03',950.00,NULL,30),
(7902,'FORD','ANALYST',7566,'1981-12-03',3000.00,NULL,20),
(7934,'MILLER','CLERK',7782,'1982-01-23',1300.00,NULL,10);
-- 查询emp表的所有信息
select * from emp;
修改指定的名字
语法1：alter table 旧表名 rename to 新表名;
alter table bbq rename to bbb;
语法2：rename table 旧表名 to 新表名;
rename table bbp to ppp;
rename table ppp to bbp;
删除表包括表结构
语法1：drop table 表名；
drop table bbb;
只复制表结构
语法：create table 新表名 like 被复制的旧表名;
create table aaa like bbp;
复制表结构加数据
语法：create table 新表名 as select * from 被复制的旧表名;
create table bbb as select * from dept;
create table ccc as select * from bbb;
create table fff as select * from ccc;
create table ddd as select * from fff;
drop table aaa;
drop table bbb;
drop table ccc;
drop table bbp;
drop table bbp;
表中数据的操作
1.向表中插入数据
单行插入语法:
insert into表名[(列名1,列名2......] values（值 1,值⒉,值n..….）;
注意:如果插入的是 string类型的数据则需要用单引号或双引号包裹起来,
书写的值的个数需要与声明的列的个数一致(如果没有声明列,那么插入的值的个数必须与表中的列的个数相等)
多行插入语法: insert into表名[(列名1,列名2...)
values (值1，值⒉,值n.....),(值1,值2,值n....),(值1,值2,值n...…);
insert into ccc values(50,'ABB','大连');
insert into ccc values(60,null,null);
insert into ccc values(70,'','');
2.修改表中的数据
一整列的更新语法：update 表名 set 列名=新值;(一般不用)
update a set mark = 99;
某一个值的更新语法：update 表名 set 列名=新值 where 限制条件;
-- 将a表中的王五同学的数学成绩更新为100
update a set mark = 100 where sname = '王五' and course = '数学';
-- 将a表中的数学更新成100
update a set mark = 100 where course = '数学' ;
-- 将a表中的英语更新成98
update a set mark = 98 where course = '英语';
update a set sname ='李四' where sname ='王五' and course = '数学';
3.删除表中的数据
删除一行的语法:delete from 表名 where 列名 = 值;
注意：如果该语句不用where条件 ，那么表内的所有数据都会被删除（慎用，一般不用）
-- delete from ccc;
如果涉及需要删除的表中所有数据，除了使用的delete之外，
还有可以使用truncate语句
-- truncate ddd;
drop delete truncate三者的区别
1.delete
可以删除指定的数据行，不会删除表的结构，可以和where语句搭配使用
2.truncate
可以删除除表中的所有数据，不会删除表的结构，删除速度比delete快
3.drop
可以删除表中的数据，但是整张表都会被删除，包括但是不限于主键、外键等约束，
但是如果想删主表但是主表中又有列被从表引用，
需要先删除从表才能删除主表
-- 删除李四的数学成绩数据
delete from a where sname = '李四' and course = '数学';
删除 来自 a 条件 通过列名和值定位要删除的行;
-- 插入数据
insert into a values('李四','数学',100);
-- 将王五的英语成绩删除（只是删除成绩数据不是删除行数据）
-- 思考:将王五的英语成绩删除(只涉及删成绩数据不是删行数据)
-- 等同就是将王五的英语成绩改成nuli
update a set mark = null where course = '英语' and sname = '王五';
4.where 条件语句
-- 在where条件语句中有比较运算符：
-- <:小于 ,>：大于, =：等于, !=：不等于, <>：不等于 ,<=：小于等 ,>=：大于等于
-- and :和（与） or：或 not:非
结构化查询语句
structured query language:结构化查询语言 简称sql,是一种应用最广泛的关系型数据库
5.select 语法查询顺序：
select 需要查询的列名 如果时全查 *
👇
from 数据来源 如果要查询的数据涉及多张表，那么逗号隔开
👇
where 条件语句
👇
group by 分组列名
👇
having 条件表达书
👇
order by 排序字段 asc 升序(默认的) desc降序
👇
limit m,n m:从哪儿开始，从0开始（位数-1） n:显示几个
-- 注意：在查询语句中，句子出现的词语顺序不能乱，否则会报错
-- 查询a表中的所有信息
select * from a;
-- 查询工号为:7782的所有信息
select * from emp where empno=7782;
-- 查询工号为:7769的所有信息
select * from emp where empno=7369;
-- 查询工号为：7788的员工姓名
select ename from emp where empno=7788;
-- 查询SMITH的岗位和工资
select job,sal from emp where ename='SMITH';
6.简单查询的语法：
select 列1,列2,列n from 表名 where 条件表达式;
注：如果查询的是所有列使用*
7.常见函数
avg(需要求平均值的列) 求平均值
sum(需要求和的列) 求和
max(需要求最大值的列) 求最大值
min(需要求最小值的列) 求最小值
count(需要统计计数的列) 计数函数 ,不会计数null的数据
round(需要处理的数据,保留小数位数)四舍五入函数
练习
例如: round(avg(sal),2) 将平均工资保留两位小数
-- 查询部门编号10的员工平均工资
select deptno as '部门编号',avg(sal) as '平均工资'
from emp
where deptno=10;
-- 查询20号部门员工的工资总和
select deptno ,sum(sal) from emp where deptno=20;
-- 查询工资最高的员工姓名和工资
select ename,sal
from emp
order by sal desc limit 1;
-- select ename,max(sal) from emp;
-- 查询工资最低的员工姓名和工资
select ename,sal
from emp
order by sal limit 1;
-- select ename,min(sal) from emp;
-- 统计有多少个员工
select count(empno)
from emp;
-- 求各部门的平均工资保留其两位小数
select deptno,round(avg(sal),2) from emp group by deptno;
-- 查询工资在2000-4000之间的员工姓名和工资
select ename, sal from emp where sal between 2000 and 4000;
-- 查询职位是MANAGER的平均工资
select round(avg(sal),2)'平均工资',job
from emp where job='MANAGER';
-- 查询在DALLAS工作的员工姓名。（需要用到dept表）
select ename from emp ,dept where
emp.deptno=dept.deptno and loc='DALLAS';
-- 查询工资是800,1250,1300的员工姓名和工资
select ename,sal from emp
where sal=800 or sal=1250 or sal=1300;
select ename,sal from emp
where sal in(800,1250,1300);
select ename,sal from emp
where sal not in(800,1250,1300);
-- 查询工资在1000到2000的员工姓名和工资
select ename,sal from emp
where sal >= 1000 and sal <= 2000;
select ename,sal from emp
where sal between 1000 and 2000;
-- 查询工资小于平均工资的员工姓名和工资
select avg(sal) from emp;
select ename ,sal from emp
where sal<(select avg(sal) from emp);
-- 查询部门平均工资最低的部门编号和工资
select deptno,round(avg(sal))
from emp
group by deptno order by avg(sal) limit 1;
-- 查询有多少个领导
select count(distinct mgr) from emp;
-- 查询哪些员工是领导
select ename from emp
where
empno in(select mgr from emp where mgr is not null);
-- 查询工龄大于40年的员工姓名
select ename from emp where datediff(curdate(),hiredate)/365 > 40;
select ename from emp where datediff(curdate(),hiredate) > 365*40;
-- 查询各个部门员工人数
select deptno ,count(empno) from emp group by deptno;
