mysql句式增删改_1MySQL语法基础--增删改查

建表语句

2 CREATE TABLE `students`('id'INT NOT NULL AUTO_INCREMENT,`name`VARCHAR(200)NOT NULL);3

4 USE `school`;5

6 CREATE TABLE `students`(7 `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,8 `name` VARCHAR(20) NOT NULL,9 `nickname` VARCHAR(20) NULL,10 `sex` CHAR(1) NULL,11 `intime` DATETIME NULL12 );

插入语句

INSERT INTO  `table`  VALUE(S)(...);

INSERT INTO `students` VALUE(1,'张三','三哥','男',now());

如果有空置，那么可以写成以下代码：

INSERT INTO `students`(`name`,`nickname`) VALUE('张三','三哥');

如果插入多条信息呢

INSERT INTO `students`(`name`,`nickname`) VALUES

('张三2','三哥2'),

('张三3','三哥3'),

('张三2','三哥2')

;

查询语句

SELECT

select_expr,...

SELECT * FROM `students`;

or

SELECT `id`,`name`,`nickname`FROM `students` WHERE `sex`='男';

倒序

SELECT `id`,`name`,`nickname`FROM `students` WHERE `sex`='男' ORDER BY `id` DESC;

分页

SELECT `id`,`name`,`nickname`FROM `students` WHERE `sex`='男' ORDER BY `id` DESC LIMIT 4,2 ;

修改语句

UPDATE  table_references

SET col_name1 = expr1[,col_name2=expr2...]

[WHERE where_definition]

UPDATE `students` SET `sex` ='女' WHERE `sex`='男';

UPDATE `students` SET `sex` ='女', `nickname` ='没有昵称';

删除

DELETE FROM

DELETE FROM `students` WHERE `sex`='男';