mysql 增删改查排序分页最简单的语法

时间:2021-08-21 11:23:09
UPDATE `USER` SET username=1 where userId=1;
UPDATE `USER` SET `PASSWORD` = 2 WHERE userId = 2;
SELECT * FROM `USER`;
SELECT * FROM USER WHERE userId = 1;
INSERT INTO `USER`(userid,username,password) VALUES('5','huangzhen','123456789')


SELECT * from `USER`  LIMIT 0,2;


SELECT * from `USER` where 1=1  ORDER BY userId DESC LIMIT 0,2;


create table student(
studentId int PRIMARY key AUTO_INCREMENT,


stuName VARCHAR(20) not null,


stuNO varchar(21) not NULL,


stuAge varchar(2) not null,


buildTime VARCHAR(30) not null,


stuState VARCHAR(1) DEFAULT 0


)


--清空表内的数据,但是自增的主键不归零--    
DELETE from student;
--清空表内的数据,自增的主键归零--
TRUNCATE TABLE student;




SELECT * from student where  stuState=1;


--批量插入第一种方法  效率比较低 同一个时间插入-- 
INSERT INTO 
student(stuName,stuNo,stuAge,buildTime) 
VALUES('张三',123456,18,NOW()),
('张1',1234561,11,NOW()),
('张2',1234562,12,NOW()),
('张3',1234563,13,NOW()),
('张4',1234564,14,NOW()),
('张5',1234565,15,NOW()),
('张6',1234566,16,NOW()),
('张7',1234567,17,NOW()),
('张8',1234568,18,NOW()),
('张9',1234569,18,NOW()),
('张10',12345610,8,NOW()),
('张11',12345611,38,NOW()),
('张12',12345612,48,NOW()),
('张13',12345613,28,NOW()),
('张14',12345614,58,NOW()),
('张15',12345615,18,NOW());


--批量更新
update student set stuState=0 where stuState=1;




SELECT * from student where stuState=1  AND stuName LIKE '%1%' ORDER BY buildTime DESC LIMIT 0,5