【MySQL 第六天 表的增删改查 分组|排序|Limit语句查询限制】

---

---

【1】mysql单表数据插入

---

【1.1】mysql一次性插入数据

为表的所有列插入数据

insert into tab_name(column_name1,column_name2,...,column_namen) values(value1,value2,...,valuen);

---

【1.2】mysql指定列插入数据

---

【1.3】mysql同时插入多条数据

insert into tab_name (column_listname) values (value_list1),(values2),...,(valuesn);

insert into bookparent(book_id,book_name,parent_id)values(1,'C语言',1),
(2,'C++',2),(3,'mysql',3);

---

【1.4】mysql查询结果插入表中

insert into tab1_name (column_list1) select(column_list2) from tab2_name where (condition)

insert into test select *from bookparent where book_id>2;

---

【2】mysql设置自动编号1

希望第一列能自动增加

column_name datatype auto_increment 

---

举例

create table bookqq(
-> qq_id int primary key auto_increment,
-> 11_name varchar(20) not null unique,
-> parent_id int null
-> );

插入数据，插入的时候不按自增插入，查看的时候是自增，关键字auto_increment起作用

创建一个临时表，使用auto_increment，就算不赋值，也会自动赋值从1开始自增

对auto_increment 创建表的时候就给一个自增值的初始值

 create table tab_test(
	id int primary key auto_increment ,
	k_name varchar(30) not null unique ,
	parent_id int not null
)auto_increment=5;

insert into tab_test(k_name,parent_id)values('KK',1);

insert into tab_test(k_name,parent_id)values('DD',2);
insert into tab_test(k_name,parent_id)values('EE',3);
insert into tab_test(k_name,parent_id)values('AA',4);

---

【3】mysql设置自动编号2

alter table tab_name modify var_name datatype auto_increment ;

---

alter table tab_name auto_increment = x;

---

alter table tab_name modify var_name datatype ;

创建表 删除自增列 插入数据验证

删除自增列 添加自增列 指定初始值 插入值

对于有外键关联的表 需要删除外键后 才能添加自增列



删除成功 解除外键

---

---

---

【4】mysql单表数据记录的更新

update tab_name set 列名 =值 where (condition)

---

图书类别表插入数据

/*图书类别表*/
insert into bookparent(book_name,parent_id)values('计算机',0),('医学',0),('编程语言',1),('数据库',1),('后端',2);

---

图书信息表插入数据

/*图书信息表*/
insert into bookinfo(book_id,book_copy_id,book_name,author,price,store,email,press,pubdate)values
(20150201,3,'C++','jk',79.8,5,'3055555@qq.com','苏州出版社','2007-04-01'),
(20150202,4,'JAVA','jC',95,2,'3055533@qq.com','贵州出版社','2009-04-01'),
(20150203,3,'C#','jS',69,3,'3055522@qq.com','广州出版社','2003-09-01'),
(20150204,5,'Shell','ja',136,1,'3055511@qq.com','锦州出版社','2011-04-01'),
(20150205,5,'Linux','jq',24.5,4,'3055566@qq.com','金州出版社','2011-04-01');

---

读者信息表插入数据

/*读者信息表*/
insert into readerinfo(card_id,name,sex,age,tel,balance)values
(522429199628524432,'KK','女',12,'16635824456',300),
(522429199628524433,'DD','女',12,'16635824456',200),
(522429199628524434,'SS','男',12,'16635824456',300),
(522429199628524435,'AA','男',12,'16635824456',400),
(522429199628524436,'QQ','男',12,'16635824456',500);

---

借阅信息表数据的插入和计算

/*借阅信息表*/
/*图书信息表的第一列图书编号20150201 读者作者KK 522429199628524432 在2017-11-29借书一个月*/
insert into borrowinfo(book_id,card_id,borrow_date,return_date,status)values
(20150201,522429199628524432,'2017-11-29','2017-12-29','否');

---

/*查看图书信息表 编号为20150201的图书的价格*/
select price from bookinfo where book_id = 20150201;

/*借书费用=书的价格*%5   借书的人KK 522429199628524432 她的余额=300 借书之后要扣费*/
select balance from readerinfo where name = 'KK';
update readerinfo set balance = balance - 79.80*0.05 where name = 'KK';
select *from readerinfo;

---

/*借书之后，图书信息表里库存-1*/
update bookinfo set store = store -1 where book_id =20150201;
select *from bookinfo;

---

【5】mysql单表数据记录的删除

delete from tab_name [where <condition>]

/*删除KK用户*/
delete from readerinfo where card_id = '522429199628524432';

---

/*删除表中的所有数据*/
delete from bookinfo;

---

truncate table tab_name;

/*删除表中数据 和表  并且以这个表在创建一个相同的表名*/
truncate TABLE readerinfo;

---

/*查询某个数据 并且将他删除*/
SELECT book_name FROM bookparent where book_id =5;
DELETE FROM bookparent WHERE book_id =5;

---

【6】mysql单表数据记录的查询

【1】select *from tab_name;
【2】select * column_name_1, column_name_2, ..., column_name_n from tab_name;

---

select distinct column_name from tab_name;

---

select * from tab_name where column_name is null;

---

/*查询表格所有列数据*/
select *from borrowinfo;

/*查询其中的两列*/
select book_id,card_id,borrow_date from borrowinfo;
/*查询其中的1列*/
select borrow_date from borrowinfo;

/*根据条件查询指定的列*/
select id,name,pid from test where pid>2;

/*查询是空的列,没有值  pid列那些值为空*/
select *from test where pid is null;

/*查询没有重复的列*/
select distinct pid from  test; 

---

【7】mysql查询结果进行分组

[ group by column_name ] [having <条件表达式expre>] 

[ having <条件表达式> ]

---

/*查询test表中 name列 java的个数*/
select count(*) from test where name ='java';

---

/*按照性别进行分组*/
insert into readerinfo(card_id,name,sex,age,tel,balance)values
(522429199628524432,'KK','女',12,'16635824456',300),
(522429199628524433,'DD','女',12,'16635824456',200),
(522429199628524434,'SS','男',12,'16635824456',300),
(522429199628524435,'AA','男',12,'16635824456',400),
(522429199628524436,'QQ','男',12,'16635824456',500);

select sex from readerinfo group by sex;

---

/*以分组方式 查看性别的人数*/
select sex,count(*) from readerinfo group by sex;

---

/*分组方式 按条件查看 性别sex列中 having过滤分组 人数大于2的*/
select sex from readerinfo group by sex having count(sex)>2;

---

【8】mysql查询结果排序

order by  column_name [ASC|DESC]

---

select * from tab_name order by column_name;

---

select * from tab_name order by column_name_1,... ,column_name_n;

---

select * from tab_name order by column_name_1 ASC,... ,column_name_n DESSC;

---

/*按照读者信息表的余额进行排序 升序*/
select *from readerinfo order by balance ASC;

/*按照读者信息表的余额进行排序 降序*/
select *from readerinfo order by balance DESC;

---

/*余额升序 年龄降序*/
select *from readerinfo order by balance ASC,age DESC;

---

【9】mysql Limit语句限制查询记录的数量

limit [offset,] row_count

【1】select *from tab_name limit showtab_rows;//显示表前三行
【2】select *from tab_name limit 2,2;//跳过前两行 从第三行开始显示 显示2条

---

读者信息表

---

/*显示读者信息表的前3行记录*/
select *from readerinfo limit 3;

/*跳过前两行 从第三行开始显示 显示2条*/
select *from readerinfo limit 2,2;

/*向后偏移2行 显示偏移位置后的三行  （不是最后三行切记）/
select *from readerinfo limit 3 offset 2;

---

/*将读者信息表按照年龄进行分组，统计每组年龄下的个数。然后按照年龄进行排序，并查看结果中的前四条记录*/
select age,count(*) from readerinfo group by age order by age desc limit 4;

---

这章内容比较多，没有难不难的说法，熟能生巧，下一章更精彩/😕😕😕