mysql 数据库基本sql语句

显示所有数据库：

show databases;
//使用哪个数据库
use test;
//展示该数据库下面的所有数据表
show tables;

首先是对创建数据库的操作：

create database test;

删除数据库的操作：

drop database test;

创建数据表的操作：

 create table student(sno int(11) unsigned not null auto_increment primary key,
     sname char(4) not null,
     ssex tinyint(1) unsigned default 1  not null,
     sage tinyint(2) not null,
     sdept tinyint(2) not null);

 create table course(cno int(11) unsigned not null auto_increment primary key,
     cname char(10) not null);

 create table sc(id int(11) unsigned not null auto_increment primary key,
     sno int(11) not null,
     cno int(11) not null,
     grade tinyint(2));

创建带有索引、描述、存储引擎的数据表

create table sm_clear_money_check(id int(11) unsigned not null auto_increment comment '自增id',
name varchar(50) not null comment '创建者用户名',
type tinyint(1) unsigned default 1 comment '清理货币类型1:积分',
num int(11) unsigned default 0 comment '清理货币数量',
reason varchar(255) not null comment '清理理由',
status tinyint(1) unsigned default 1 comment '审核状态1:初始入库2:通过3:拒绝',
check_status tinyint(1) unsigned default 1 comment '核查状态1:未核查2:已核查',
create_at datetime comment '创建时间',
update_at datetime comment '每次操作的时间',
primary key(id),
index(name),
index(reason),
index(update_at)
) engine=innodb auto_increment=1 default charset=utf8mb4 COMMENT='清理货币审核表';

create table sm_clear_money_check_user(id int(11) unsigned not null auto_increment comment '自增id',
uid int(11) unsigned not null comment '被清理的用户uid',
cid int(11) unsigned not null comment '审核表id',
primary key(id),
index(uid,cid)
) engine=innodb auto_increment=1 default charset=utf8mb4 COMMENT='清理货币审核用户表';

删除数据表操作：

drop table sc;

查看表结构：

desc student;

添加表字段：

alter table student add address char(50) not null;

修改表字段：

alter table student modify address char(30) not null;

删除表字段：

alter table drop address;

修改表名：

alter table student rename bbb;

对数据的操作，添加数据：

//单条插入数据
insert into student(sno,sname,ssex,sage,sdept) values(1,'yzl',1,19,4);
//多条插入数据
insert into student(sno,sname,ssex,sage,sdept) values(2,'cxh',2,20,4),(3,'刘',1,20,3);

修改数据，更新操作：

update student set sdept=5 where sno=2;

删除数据：

delete from student where sno=3;

插入数据主键或唯一索引发生冲突时使用

// 插入有冲突时，删除原数据再插入，其他没有的字段设置为null
replace into student values (1, 'test');
// 插入有冲突时，在原有数据上修改为后面设置的数据，没设置则不变
insert into student values(1, 'test') on duplicate key update sname = 'test';
insert into student values(1, 'test') on duplicate key update sname = values(sname);

查询数据，查询操作：

//简单查询
select * from student;
//where条件查询
select * from student where sno=1;
select * from student where sno=1 or sno=2;
select * from student where sno=1 and sno=2;
select * from student where sage >19;
//like模糊查询
select * from student where sname like '%y%';
select * from student where sname like 'y__';
//in查询
select * from student where sno in (1,2);
//between区间查询
select * from student where sage between 18 and 20;
//查询去重
select distinct  * from student where ssex=1;
//聚合函数
select count(*) as number from student;
select sum(sage) as sum from student;
select avg(sage) as avg from student;
select max(sage) as max from student;
select min(sage) as min from student;
//分组查询
select * from student group by sage;
//having对分组后进一步做筛选，判断字段必须是聚合函数的返回结果
select sage,count(sage) as number from student group by sage having number>=2;
//排序查询
select * from student order by sno desc;
select * from student order by sno asc;
//限制查询,第一个参数为重第几个数据开始(不包括本身),第二个参数限制的数据条数
select * from student limit 2,5;
//多表查询
select student.sno,course.cno,course.cname from student,course where student.sno=course.cno;
//内连接inner join查询(只查出符合连接条件的)
 select s.sno,s.sname,c.id,c.grade from student s inner join sc c on s.sno=c.sno;
//左连接left join查询(左表全显示，右表符合条件才显示)
select s.sno,s.sname,c.id,c.grade from student s left join sc c on s.sno=c.sno;
//右连接right join查询(右表全显示，左表符合条件才显示)
select s.sno,s.sname,c.id,c.grade from student s right join sc c on s.sno=c.sno;