---

1、大小写不敏感（内容除外）

2、命令可以写在一行 也可以写在多行

3、命令以;结束

显示有哪些数据库

show databases;

创建数据库

create database school;

删除数据库

drop database school;

使用数据库

use school;

查看表

show tables;

新建表

create table student(

id int(10) not null primary key,

name varchar(20),

age int(10),

sex varchar(10) default '男',

classid int(10)

);

查看表结构

desc student;

删除表

drop table student;

删除主键

alter table student drop primary key;

增加主键

alter table student add primary key(id);

删除字段

alter table student drop classid;

增加字段

alter table student add classid int(10);

增加记录

insert into student(id,name,age,sex,classid) values(1000,'张三',20,'男',1);

insert into student(id,name) values(1001,'张三');

insert into student values(1002,'李四',21,'女',1);

删除

delete from student ;//删除全部记录

delete from student where id = 1000;

delete from student where age>20;

delete from student where age is null; //is not null

delete from student where sex = '男';

修改

update student set sex = '男' where id=1004;

update student set sex = '男',name='王五' where id=1006;

update student set age = 21 where name = 'xiao' and sex = '女';

update student set age = 22 where name = 'xiao' or sex = '女';

update student set age = 19 where age between 19 and 22;

通配符 %:多个任意的字符 _:一个字符

update student set age = 20 where name like '%i%';

update student set age = 21 where name like '_i%';

查询

select * from student;

select id,name from student;

select * from student where name like '%i%';

排序 默认升序asc 降序desc

select * from student order by classid asc;

select * from student order by classid desc;

select * from student order by classid desc,age asc;

组函数 min max count avg

select count(*) from student;

select min(age) from student;

select avg(age) from student;

分组

select classid,avg(age) from student group by classid;

select classid,avg(age) from student group by classid having avg(age)>20;

字句作为查询的条件

select * from student where age = (select min(age) from student);

---

    出自：何冰华个人网站

    地址：https://www.hebinghua.com/

    转载请注明出处