Mysql Manual

数据操作

数据库

#创建数据库
create database DATABASE_NAME;        
#查看数据库
show databases;  
#查看数据库信息    
show create database DATABASE_NAME;   
#删除数据库
drop database DATABASE_NAME;
#直接创建数据库且设置编码方式
create database DATABASE_NAME DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

数据表

• 创建

#首先选定操作的数据库
use DATABASE_NAME;
#创建表student
create table student(
  id  int(11),
  name  varchar(20),
  age int(11)
);
#查看数据表
show tables;
#查看数据表信息，后面加上参数/G可使结果更加美观
show create table TABLE_NAME;
#查看表的字段信息
desc TABLE_NAME;

• 修改

#修改表名
alter table student rename to DATABASE_NAME;
#修改字段名
alter table TABLE_NAME change FIELD_NAME_1 FIELD_NAME_2 varchar(20);
#修改字段的数据类型
alter table TABLE_NAME modify FIELD_NAME int(20);
#添加字段
alter table TABLE_NAME add FIELD_NAME FIELD_DATA_TYPE;
#删除字段
alter table TABLE_NAME drop FIELD_NAME;
#修改字段的位置
alter table TABLE_NAME modify FIELD_NAME varchar(20) first;
alter table TABLE_NAME modify FIELD_NAME_1 int(11) after FIELD_NAME_2;
#删除数据表
drop table TABLE_NAME;

数据

• 创建

#例子
create table student(
  id int,
  name varchar(20) not null,
  grade float
);
#插入数据
insert into student(id,name,grade) values(1,'howie',70);
#不指定字段名
insert into student values(2,'howie',80);
#或者
insert into student set id=3,name="howie",grade=90;
#同时添加多条数据
insert into student values
(4,'howie',80),
(5,'howie',80),
(6,'howie',80);

• 修改

#更新id=1的数据
update student set name="howie1",grade=60 where id=1;
#批量更新,如果没有where条件，会更新表中所有对应数据
update TABLE_NAME set grade=100 where id<4;
#删除id=6的数据
delete from TABLE_NAME where id=6;
#批量删除数据
delete from TABLE_NAME where id>3;
#删除所有数据
truncate table TABLE_NAME;
delete from TABLE_NAME;

表操作

单表

• 查询

#查询全部
select * from TABLE_NAME;
#查询某个字段
select FIELD_NAME from TABLE_NAME;
#条件查询
select * from TABLE_NAME where id=2;
#in关键字查询,排除法使用not in
select * from TABLE_NAME where id IN(1,2,3);
#between and关键字查询
select * from TABLE_NAME where id between 2 and 5;
#空值(NULL)查询，使用IS NULL来判断
select * from TABLE_NAME where grade is null;
#distinct关键字查询
select distinct name from TABLE_NAME;
#like关键字查询,查询以h开头，e结尾的数据
select * from TABLE_NAME where name like "h%e";
#and关键字多条件查询,or关键字的使用也是类似
select * from TABLE_NAME where id>5 and grade>60;

#聚合函数:count()函数,sum()函数,avg()函数,max()函数,min()函数
select count(*) from student;
select sum(grade) from student;
select avg(grade) from student;
select max(grade) from student;
select min(grade) from student;
#对查询结果进行排序
select * from student order by grade;

#分组查询
#1.单独使用group by分组
select * from TABLE_NAME group by grade;
#2.和聚合函数一起使用
select count(*),grade from TABLE_NAME group by grade;
#3.和having关键字一起使用
select sum(grade),name from TABLE_NAME group by grade having sum(grade) >100;
#使用limit限制查询结果的数量
select * from TABLE_NAME limit 5;
select * from TABLE_NAME limit 2,2;
select * from TABLE_NAME order by grade desc limit 2,2;
#函数
select concat(id,':',name,':',grade) from TABLE_NAME;
#为表取别名
select * from TABLE_NAME as stu where stu.name="howie";
#为字段取别名,as关键字也可以不写
select name as stu_name,grade stu_grade from TABLE_NAME;