2023年6月27日发(作者：)

mysql增、删、改、查常⽤命令1、插⼊⼀列数据insert into school values('1','2','3');2、插⼊⼀列中部分数据INSERT INTO school(idname,id) VALUES ('78','7');3、插⼊多列数据insert into school values('1','2','3'),('2','3','3'),('5','2','3');4、从查询表中插⼊数据如果两表字段相同，则可以直接这样⽤。insert into table_a select * from table_b如果两表字段不同，a表需要b中的某⼏个字段即可，则可以如下使⽤：insert into table_a(field_a1,field_a2,field_a3) select field_b1,field_b2,field_b3 from table_b,还可以加上where条件INSERT INTO school(idname) SELECT NAME FROM stud WHERE NAME>13;5、在创建表时增加⾃动编码，⾃动编码的初始值=4（如果不设置初始值，默认为1）CREATE TABLE test(test_id INT PRIMARY KEY AUTO_INCREMENT,test_name VARCHAR(20),test_bate DATETIME)AUTO_INCREMENT=4;

INSERT INTO test(test_name,test_bate)VALUES('lili','2019-05-27');6、在已有的列上删除⾃动编号ALTER TABLE test MODIFY test_id INT;7、在已有的列上删增加⾃动编号ALTER TABLE test MODIFY test_id INT AUTO_INCREMENT;8、在已有的列上修改⾃动编号的初始值ALTER TABLE test AUTO_INCREMENT=15;INSERT INTO test(test_name,test_bate)VALUES('lala','2019-05-08');10、更新某列数据updat test set id=id-1 where name=lala11、删除表中某列delete from test where id=2;查询表中test_idselect test_is from test where test_name;12、删除表中所有列；delete from test;13、删除本表并重新创建⼀个表；truncate table tbl_name;14、查询表中所有信息select * from test;15、查询表中某列信息select test_id from test;16、查询表中多列信息select test_id,test_date from test;17、查询指定条件的信息select * from where test_id=1;18、查询某列信息，返回数据不重复select distinct test_is from test;19、查询指定条件为空的数据select * from test where test_date is null;20、对查询结果进⾏统计(常⽤聚合函数：max(),min(),count(),avg())select cunt(*) from readerinfo where sex='男'；21、对查询结果进⾏分组select sex from readerinfo group by sex;22、对查询分组数据进⾏统计select sex,count(*) from readeinfo group by sex;23、对查询分组数据进⾏统计，并过滤select sex from readeinfo group by sex having count(sex)>2;24、对查询结果某列进⾏排序select * from readerinfo order by price;25、对查询结果多列进⾏排序select * from readerinfo order by price,store;26、对查询结果进⾏升序/降序排列(asc升序，desc降序，默认升序)select * from readerinfo order by price asc,store desc;27、查看前3⾏数据select * from readerinfo limit 3；28、查看从第4⾏开始查看2⾏数据（3为偏移量）；select * from readerinfo limit 3，2；