数据处理之增删改

添加数据

#方式一：一条一条添加数据
#1.没有指明添加的字段
select database() from dual;
show tables from test;
create table if not exists myempl(
id int,
name varchar(15)
);
select *
from myempl;
insert into myempl
values(1,'aaa');#一定要按声明的字段先后顺序添加

#2.自行指明添加字段先后顺序（推荐）
#也可以不用把表中字段全部指明
#缺少的字段（即没有赋值的字段）则默认内容为null
insert into empl(id,name,salary)#与表中字段顺序无关
values()

#同时插入多行数据
insert into myempl(id,name,salary)
values
(1,'aa'),
(2,'ss');

#方式二：将查询结果插入到表中
insert into myempl(id,name)
select id,name #查询的字段要与添加到表的字段顺序一致
from myempl
where id=2;

注意
方式二中，若在表2中查询字段信息插入到表1，则表2中字段的长度不能超出表一字段长度的范围，否则有添加不成功的风险。若插入的数据没超出范围，则不会失败，报错，反之则报错。

可以实现批量修改数据。
update…set…where…

#将name='aaa'的员工id改为50
update myempl
set id=50
where name='aaa';

同时修改一条数据的多个字段

update myempl
set id=50,salary=6000
where name='aaa';

修改数据时有可能出现修改不成功的情况。（可能是由于约束的影响造成的）

删除id=1的数据

drop myempl
where id=1;

删除数据时也可能因为约束的影响删除失败。

小结：DML操作默认情况下执行完后会自动提交（commit）数据。若不希望主动提交数据，则需要使用SET autocommit =FALSE

可以直接计算出指定字段计算结果

create table if not exists test1(
a int,
b int,
c int generated always as(a*b) virtual
);
insert into test1(a,b)
values(3,2);
select *
from test1;

综合练习

select database() from dual;
create database if not exists test;
use test;
create table if not exists books(
id int,
name varchar(50),
authors varchar(100),
price float,
pubdate year,
note varchar(100),
num int
);
select *
from books;

insert into books 
values(1,'ddd','rrr',5,'1995','novel',55);

update books
set num=0
where note='novel';

#删除库存为0的记录
delete from books
where num=0;

#统计书名中包含a子母的书
select name
from books 
where name like '%a%';

#统计书名中包含a字母的书的种类数量和库存总量
select count(*),sum(num)
from books 
where name like '%a%'

#找到'novel'类型的书，按照价格降序排序
select name,price
from books 
where note='novel'
order by price desc ;

#按照note分类，统计书的种类数
select name,count(*)
from books 
group by note;;

#按照note分类统计书的库存量，显示库存量超过30本的书
select note,name,sum(num)
from books 
group by note
having sum(num)>30

#查询所有图书，每页显示5本,显示第二页
select *
from books 
limit 5,5;

#按照note分类统计书的库存量，显示库存最多的
#方法一
select note,sum(num)
from books
group by note
having sum(num)>=all(
                    select sum(num)
                    from books 
                    group by note
                    );
#方法二
select note,sum(num) sum_num
from books 
group by note
order by sum_num desc 
limit 0,1;

#查询书名达到10个字符的书，不包含里面的空格
#这里要用到函数replace(str,str1,str2)
#表示将str中的str1替换成str2
select name
from books
where char_length(replace(name,' ',''))>=10;

#查询书名和类型，其中note值为novel显示小说，
#law显示法律，medicine显示医药，cartoon显示卡通
select name '书名',case note when 'novel' then '小说'
                      when 'law'   then '法律'
                      when 'medicine' then '医药'
                      when 'cartoon' then '卡通'
                      else '其他'
                      end "类型"
from books;

#查询书名、库存，其中num超过30显示滞销，大于0小于10显示畅销
#为0显示无货，其他显示正常
select name '书名',num '库存',case when num>30 then '滞销'
                                  when num>0 and num<10 then '畅销'
                                  when num=0 then '无货'
                                  else '正常'
                                  end "状态"
from books;

#统计每一种note的库存量，并合计总量
#with rollup：合计总量
select ifnull(note,'合计库存总量') as note,sum(num)
from books
group by note with rollup;

#统计每一种note的数量，并合计总量
select ifnull(note,'总量') as note,count(*)
from books
group by note with rollup;

#将userid为Bbiri的users表和my_employees表的记录全部删除
#方式一
delete from my_employeees
where userid='Bbiri';
delete from users
where userid='Bbiri';
#方式二
delete m,u
from my_employees m
join users u
on m.userid=u.userid
where m.userid='Bbiri';