MySQL数据库操作总结

1 数据库

1.1 数据库常用命令

• show databases;#查看数据库
• create database db_name;#创建数据库
• use db_name;#使用数据库
• drop database db_name;#删除数据库
• show create database db_name\G #查看创建详情

1.2 数据库存储引擎

• show engines;#查看数据库支持引擎
  InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、BLACKHOLE

2 数据表

2.1 创建数据表

• create table table_name(id int(11) primary key,name varchar(25),salary float); #使用主键约束

• create table table_name(id int(11),name varchar(25),salary float,primary key(id,name));#多字段联合主键

• create table class(id int(11) primary key,name varchar(25));

  create table student(id int(11) primary key,name varchar(25),classId int(11),constraint fk_class foreign key(classId) references class(id));#外键

• create table test4( id int(11) primary key, name varchar(25) not null unique );#非空约束、唯一性约束

• create table test5( id int(11) primary key, name varchar(25) not null unique, sex tinyint default 1); #默认值索引

• create table test6( id int(11) primary key auto_increment, name varchar(25) not null unique, sex tinyint default 1); #属性值自增

• insert into test6(name,sex) values(“yangyue”,1),(“hello”,0);#插入

2.2 查看数据表

• show tables; #查看当前库下所有表
• describe table_name; #查看表信息
• desc table_name #查看表信息
• select * from test6; #查询全表,;换成\G输出更友好
• show create table test6\G查看创建详情：建表语句和存储引擎
• set time_zone =’+10:00’; #时区改为东10区

2.3 修改数据表

• alter table old_table_name rename new_table_name;#修改表名
• alter table table_name modify varchar(32)…#修改字段属性，和创建时候类似
• alter table test6 change name username varchar(25)…;#修改字段名和属性
• alter table test6 add age int(3) not null default 0 after id; #在id后添加一个字段，并设置相关属性，如果要设置为开头，直接添加first即可
• alter table test6 drop age; #删除字段
• alter table test6 modify sex tinyint(4) after id; #移动字段位置
• alter table test6 engine=myisam; #修改表的存储引擎
• alter table student drop foreign key fk_class;#删除之前创建的外键，名为fk_class
• alter table student drop key fk_class;#删除key
• alter table student add constraint fk_class foreign key(classId) references class(id);#添加主键

2.4 删除数据表

• drop table table_name #删除无外键约束数据表[父表]
• alter table student drop foreign key fk_class; drop table table_name #先删除外键约束再删表；

3. 数据结构

3.1 整数类型

类型	存储大小（字节）	有符号	无符号
tinyint	1	-128-127	0-255
smallint	2	-32768-32767	0-65535
mediumint	3	-8 388 608~8 388 607	0~16 777 215
int	4	-2 147 483 648~2 147 483 647	0~4 294 967 295
bigint	8	-9 223 372 036 854 775 808~9 223 372 036 854 775 807	0~18 446 744 073 709 551 615

注意： int(4)这种写法只是指定了显示的宽度，而不是实际存储的大小。比如，tinyint默认显示宽度是4，以为tinyint可以表示的最小值是-128.占四位，所以是这儿默认的宽度是4.

3.2 浮点数类型和定位数类型

类型	存储大小（字节）	有符号	无符号
FLOAT(Length, Decimals)	4
DOUBLE(Length, Decimals)	8
DECIMAL(m,d),dec	m+2

float(6,1)，指的是这个浮点数最大长度为6，也就是六位，整数部分5位，小数部分为1位。在插入数据时候mysql会自动四舍五入

3.3 日期与时间数据类型

类型名称	时间格式	日期范围	存储需求（字节）
YEAR	YYYY	1901~2155	1
TIME	HH:MM:SS	-838:59:59~838:59:59	3
DATE	YYYY-MM-DD	1000-01-01~9999-12-31	4
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00~9999-12-31 23:59:59	8
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01UTC~2038-01-19 03:14:07UTC	4

3.4 字符串格式

4. 常用函数

5. 查询数据

• select * from table_name #查询所有
• select field from table_name #查询指定字段
• select field1, field2 from table_name #查询多个字段
• select * from fruit where price=1.3 #条件=
• select * from fruit where price<1.3 #条件<
• select * from fruit where price>1.3 #条件>
• select * from fruit where id!=1 #条件！
• select * from fruit where price between 1.0 and 1.2 #between
• select * from fruit where price not between 1.0 and 1.2 #not between
• select * from fruit where id in (1,2) #in
• select * from fruit where id not in (1,2) #not in
• select * from fruit where name like ‘b%’;#like %通配符，匹配任意多个字符，b开头的
• select * from fruit where name like ‘%b%’;#查询有b字符的，不管前面和后面有多少个
• select * from fruit where name like ‘____t’;#下划线通配符，匹配一个字符
• select * from fruit where name is null #查询空字段
• select * from fruit where name is not null #查询非空字段
• select * from fruit where name is not null and price<1.3 #and多条件查询
• select * from fruit where name is not null or price<1.3 #or查询
• select distinct name from fruit; #查询结果不重复
• select name from fruit order by price #查询结果排序，单列排序
• select name from fruit order by name,price;#查询结果排序，只有第一列相同元素才会使用第二列排序
• select name from fruit order by name desc;#降序排序
• select name from fruit order by name asc;#升序排序
• select classId, count(*) as total from student group by classId; #分组，按照classId分组，统计每班人数
• select classId, count(*) as total,group_concat(name) from student group by classId; #分组，按照classId分组，统计每班人数,并且使用group_concat列出每个班都有哪些人
• select classId, count(*) as total,group_concat(name) from student group by classId having count(name)>2; #分组，按照classId分组，统计每班人数,找出每班人数大于2的班级,并且列出每个班都有哪些人。having和select都是用来过滤数据的，但是having是在分组之后进行过滤，但是select是在分组之前对记录进行过滤。select过滤掉的不会进入分组。
• select classId, count(*) as total,group_concat(name) from student group by classId with rollup; #with rollup将以上查询结果增加一行，对结果计算总和。
• select * from fruit group by id, name;#多字段分组，先对第一个字段分组，再对第二个字段分组。
• select * from fruit group by name order by price desc; # group by 和 order by同时使用,但是order by和with rollup不能同时使用
• select * from fruit limit 10;# limit限制查询结果数量limit n
• select * from fruit limit 0,10;# limit限制查询结果数量limit start,n 第一行的偏移量为0