如何在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;