MySQL 语句 大全 持续更新

//database 基本操作
show create database mysql;
select database();                                                 //当前在哪个库
show DATABASES;
ALTER DATABASE quanfen DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;//修改库的字符集
show create database quanfen;
drop database quanfen;
show databases;
use quanfen;                                     //切换库


//table 基本操作
create table stu(stu_id int(10),stu_name varchar(10),stu_age int(10));
show tables;                                           //显示所有表
desc students;                                         //表中字段的相关信息
alter table stu rename students;                       //改表名
alter table student add stu_hp varchar(5);             //加字段
alter table student drop stu_hp;                       //删除字段
alter table students change stu_age stu_sex varchar(5);//修改字段
alter table students modify stu_sex char(5);           //修改数据类型
show create table students;                            


//数据的增删改查

insert//插入数据

insert into student(SID,Sname,Sage,Ssex)values('99','蓝猫',"1995-05-10",'女')
insert into student values("96",'蓝瘦','1956-06-29','男')
insert into student values("94",'蓝瘦1','1956-06-29','男'),("93",'蓝瘦2','1956-06-29','男');
insert into student values('23','王4','2018-10-25','中');


update //更行数据
update student set Sname="红红" where Sid='93';
update student set Sname=null where Sid='02';



select//基础查询例子

select * from student;
select Sname from student where Sid='93';
select * from student where Ssex="男";
SELECT * from student where Ssex='女'and Sid>90;
select * from student where Sname like '%孙%';
select * from student where Sname like  '%孙%' or Sid>10;
SELECT * FROM STUDENT where Sname like '%王%' OR Sid in(10,22,99,8);
SELECT * FROM STUDENT where sid between 1 and 20;

查询知识点： and  or   in(值1,值2) not in(值1,值2) 
 between 20 and 100 //20至100范围内 

delete//删除数据
delete from student where Sid='02';