MySQL常用增删改查语句 create insert delete update select

create 语句

建立数据库

create database school;

使用数据库

use school;

建立数据表

create table student(
student_id int primary key auto_increment,
student_name varchar(30),
student_score int);

create table book(
book_id int primary key auto_increment,
book_name varchar(30),
book_amount int,
student_id int,
foreign key (student_id) references student (student_id) on delete cascade);

• primary key 代表该字段为主键。
• auto_increment 代表主键自增。
• foreign key (student_id) references student (student_id) 为外键约束语句。
• on delete cascade 设置级联删除。

insert 语句

添加数据

insert into student (student_id, student_name, student_score) values (1, "孙悟空", 80);
insert into student (student_id, student_name, student_score) values (2, "猪八戒", 70);

insert into book (book_id, book_name, book_amount, student_id) values (1, "JAVA", 100, 1);
insert into book (book_id, book_name, book_amount, student_id) values (2, "MySQL", 90, 1);
insert into book (book_id, book_name, book_amount, student_id) values (3, "C", 80, 2);
insert into book (book_id, book_name, book_amount, student_id) values (4, "C++", 70, 2);

delete 语句

删除数据

delete from book where book_id = 1;

delete from student where student_id = 2;

update 语句

执行 insert 语句，使之恢复原来状态。

更改数据

update book set book_amount = 200 where book_id = 1; 

update book set book_amount = 500 where student_id = 2; 

select 语句

查询数据总数量

 select count(1) from book;

查询表中所有数据

select * from book;

查询某个字段数据

select book_name from book;

条件查询

select * from book where book_id = 1;

分页查询

select * from book limit 1,3;

模糊查询

select * from book where book_name like "%C%";

联表查询

select * from student, book where book.book_id = student.student_id;

最大值查询

select max(book_amount) from book;

最小值查询

select min(book_amount) from book;

平均值查询

select avg(book_amount) from book;

求和查询

select sum(book_amount) from book;