数据操作详解

操作数据库

1、创建数据库

create database 数据库名;

2、切换数据库

use 数据库名;

3、删除数据库

drop database 数据库名;

操作数据表

1、创建数据表

create table 表名(
	字段名 数据类型 [字段特征],
    字段名 数据类型 [字段特征],
    ...
    字段名 数据类型 [字段特征]
)

2、删除数据表

drop table 表名;

3、修改数据表

• 对表进行重命名

  alter table 旧表名 rename to 新表明;
  

• 添加新字段

  alter table 表名 add column 字段名 数据类型 字段特征;
  

• 修改字段

  alter table 表名 change 旧字段名 新字段名 数据类型 字段特征
  

• 删除字段

  alter table 表名 drop 字段名
  

添加约束

1、添加非空约束

alter table 表名 change 旧字段名 新字段名 数据类型 not null;

2、添加主键约束

alter table 表名 add primary key(字段名);

3、添加唯一约束

alter table 表名 add unique(字段名);

4、添加默认值约束

alter table 表名 alter 字段名 set default '默认值';

5、添加外键约束

alter table 从表表名 add foreign key(从表外键字段) references 主表(主表主键字段)

添加约束的操作通常是对已存在的表进行修改和维护时使用。如果是一张新表，最好在创建的时候设计好约束。

建表的同时添加约束

-- 创建数据库gamedb
create database gamedb;
-- 切换数据库
use gamedb;
-- 创建游戏角色表hero
create table hero(
-- 编号 主键 自增 非空 
	id int not null primary key auto_increment comment '编号',
-- 姓名 非空 唯一
	name varchar(20) not null unique comment '姓名',
-- 定位 非空	
	position varchar(20) not null comment '编号',
-- 性别 默认男 非空	
	sex char(1) not null default '男' comment '性别',
-- 价格 整型 非空 默认4800	
	price int not null default '4800' comment '价格',
-- 上架日期
	shelf_date date comment '上架日期'  
)

-- 创建战斗表battle
create table battle(
	hero_id int not null,
	position varchar(20),
	foreign key(hero_id) references hero(id)
)

数据完整性

数据完整性是指数据精确可靠。不能够保存无意义或无效的数据。

如不合理的年龄，全都为空的记录、重复记录等。

为了保证保存在数据库中的数据是完整数据，就要在设计数据表时添加一些约束或特征来保证数据完整性。

MySQL中常见的数据类型

整型
tingint	短整型	对应java中的byte和short
int	整型	对应java中的int
bigint	长整型	对应java中的long

浮点型
float	单精度浮点型	对应java中的float
double	双精度浮点型	对应java中的double
decimal(宽度，精度)	指定保留的小数位数和整体宽度	如decimal(4,2) 3.415926–>3.14

字符串
char(长度)	定长字符串	char(10)表示占10个字符，就算保存了3个字符，也占10个字符，对应java中的String
varchar(长度)	可变字符串	varchar(10)表示最多占10个字符，保存了3个字符，就占3个字符，对应java中的String
text	文本

日期
date	日期	yyyy-MM-dd
time	时间	HH:mm:ss
datetime	日期时间	yyyy-MM-dd HH:mm:ss
timestamp(14或8)	毫秒	保存日期的毫秒数14表示yyyyMMddHHmmss,8表示yyyyMMdd

约束

字段特征	概念	关键字
非空约束	是否允许该字段为null	null表示可以为空，not null表示不可以为空
主键约束	主键(primary key)。用于区分表中的每条记录的一个字段。如果有现成的字段可以区分每条记录时，将该字段设置为主键字段；如果没有现成的字段可以区分每条记录时，通常会额外添加一个id字段用来设置为主键字段。通常一张表只有一个主键字段。	primary key
唯一约束	保证该字段值不重复	unique
默认值约束	如果在不给该字段添加数据的情况下，保证该字段有一个默认值。	default
外键约束	在主从关系的表中，给从表中的某个字段添加外键约束，该字段的值只能来自于主表中的某个主键字段。	foreign key references

操作数据

数据的操作，是指数据的增加crease，修改update，查询read和删除delete。

简称CURD。

数据添加insert

数据添加时，都是整行(一条记录)添加。不能只给一个字段添加数据。

如果只给某个字段添加数据，实际是修改。

给所有字段赋值

insert into 表名 values('值1','值2'...)

• 表名后无需添加字段名，添加时要保证值的顺序与字段的顺序一致
• 遇到自增字段时，不能省略不写，可以使用0、null、default让其自动填充
• 遇到有默认值的字段，不能省略不写，要使用default让其自动填充默认值
• 遇到允许为空的字段，不能省略不写，要使用null让其设置为空

给指定字段赋值

insert into 表名(字段1,字段2...) value('值1','值2'...)

• 没有默认值的非空字段必须要写出来
• 表名后的字段顺序要和值的顺序一致

批量添加

可以用insert into语句添加多条记录

insert into 表名[(字段1,字段2)] values
		('值1','值2'...),
		('值1','值2'...),
		...
		('值1','值2'...)

• 可以省略表名后的字段名
• 值的顺序和字段顺序一致
• 如果一次添加多条记录，优先使用批量添加，效率更高

修改数据update

修改单个字段的所有值

update 表名 set 字段 = 值;

修改多个字段的所有值

update 表名 set 字段1 = 值,字段2 = 值...

根据条件修改(where子句)

update 表名 set 字段 = 值 where 条件

指定值

update 表名 set 字段 = '值' where 字段 = '值'

指定范围

• 使用>、<、>=、<=表示范围，使用and、or、&&、||将多个条件拼接串联起来

  update 表名 set 字段 = '值' where 字段
  

• 使用某个字段between 值1 and 值2 表示字段在闭区间[值1,值2]范围内

  update 表名 set 字段 = '值' where 字段 between 值1 and 值2
  

• 使用!=、<>表示不等于

  update 表名 set 字段='值' where 字段<>值
  

指定集合

• 在某个集合in

  update 表名 set 字段='值' where 字段 in ('值1','值2'..)
  

• 不在某个集合 not in

  update 表名 set 字段='值' where 字段 not in ('值1','值2'..)
  

空值

• 使用is null表示空

  update 表名 set 字段 = '值' where 字段 is null
  

• 使用is not null表示非空

  update 表名 set 字段 = '值' where 字段 is not null
  

模糊查询

-- 字段 like '%娜%'
-- 带有'娜'字

-- 字段 like '张%'
-- '张'字开头

-- 字段 like '%儿'
-- '儿'字结尾

-- 字段 like '%瑞_'
-- 倒数第二个字为'瑞'

-- 字段 like '___'
-- 3个字

update 表名 set 字段 = '值' where 字段 like '%文字%'

数据删除delete

数据删除是删除一条或多条记录。

删除所有

delete from 表名;
-- 或
truncate table 表名;

• delete会保留自增列删除前的值，删除后再添加时，自动从删除前开始自增
• truncate会重置自增列的值，效率更高。
• 如果要删除主从关系且设置了外键的表中的数据，如果从表中有数据，不能直接删除主表中的相关数据，先删除从表中的数据，才能删除主表中的数据。

条件删除

delete from 表 where 条件

删除时的条件同修改时的条件语句

数据查询select

查询所有字段

select * from 表名;

查询指定字段

select 字段名1,字段名2... from 表名;

字段重命名

select 字段名1 as '重命名',字段名2 as '重命名'... from 表名;

查询指定条数

-- 查询前n条记录
select * from 表名 limit N;

-- 查询从索引N开始的M条记录
select * from 表名 limit N,M;

-- 每页显示size条，第page页
select * from 表名 limit (page-1)*size,size

去重复

select distinct 字段名 from 表名;

条件查询

where子句，语法同修改、删除时的where

select * from 表名 where 条件

排序

select * from 表名 where 条件 order by 排序字段 [ASC/DESC],排序字段 [ASC/DESC]...

• 排序可以是升序或降序

  • 默认不写是升序asc
  • 降序需要写desc

• 排序是如果有条件，where条件写在表名之后，排序之前

• 多字段排序时，在order by之后写多个字段及排序规则，用逗号隔开

  • 按字段顺序优先排序

统计函数(聚合函数)

select 统计函数(字段名) from 表名;

函数名
count(字段名)	数量
sum(字段名)	求和
avg(字段名)	平均
max(字段名)	最大
min(字段名)	最小

数学相关函数

函数	作用
abs(值或字段)	绝对值
pow(值或字段)	次幂
sqrt(值或字段)	开平方
round(值或字段)	四舍五入
ceil(值或字段)	向上取整
floor(值或字段)	向下取整

字符串相关函数

函数
length(字符串或字段)	得到字符串长度
trim(字符串或字段)/ltrim(字符串或字段)/rtrim(字符串或字段)	去除字符串首尾/首/尾空格
left(字符串或字段)/right(字符串或字段)	从字符串左/右边开始截取n个字符
substr(字段或字符串,start)	从start开始截取至末尾
substr(字段或字符串,start,length)	从start开始截取length个字符
lcase(字符串或字段)/ucase(字符串或字段)	转换小写/大写
instr(字符串1,字符串2)/locate(字符串2,字符串1)	判断字符串2在字符串1中出现的顺序
reverse(字符串或字段)	翻转字符串
concat(字符串1,字符串2…)	拼接所有字符串
replace(字符串或字段,旧字符串,新字符串)	将字符串中的旧字符串替换为新字符串

时间相关函数

函数
now()	当前日期时间
current_date()/curdate()	当前日期
current_time()/curtime()	当前时间
year(日期)/month(日期)/day(日期)	得到年/月/日
datediff(时间1,时间2)	计算时间1与时间2相隔的天数
timediff(时间1,时间2)	计算时间1与时间2相隔的时分秒
timestampdiff(时间单位,时间1,时间2)	计算时间1与时间2之间相隔的指定时间单位

分组

select 分组字段,统计函数 from 表名 group by 分组字段;

按指定的字段进行分组，会将该字段值相同的记录归纳到同一分组。

分组通常配合统计函数使用。

如果统计函数作为条件，不能写在where之后，要写在having之后，having句子放在分组之后。

-- 按图书作者分组，查询平均价格大于50的信息
select book_auther ,avg(book_price) from book_info 
group by book_auther 
having avg(book_price)>50 

group_concat()函数

group_concat(字段1或字符串,字段2...)

select group_concat(某字段) from 表名 group by 分组字段
-- 根据图书类型分组，查看每组的图书名和作者
select group_concat(book_author,book_name) from book_info group by type_id

连接查询

交叉连接、笛卡尔积

将两张表中的数据两两组合，得到的结果就是交叉连接的结果，也称为笛卡尔积

集合A：{a,b}

集合B：{1,2,3}

集合A x 集合B={a1,a2,a3,b1,b2,b3}

select * from 表1,表2;
select * from 表1 cross join 表2;
select * from 表1 inner join 表2;

将两张表中的数据互相组合成一张新表，其中有很多无效数据

内连接

select * from 表1,表2 where 表1.字段 = 表2.字段;
select * from 表1 inner join 表2 on 表1.字段 = 表2.字段;

-- 查询图书类型表(类型编号、类型名称)和图书详情表(图书编号、类型编号、图书名称)
select *from 图书类型表 t1,图书详情表 t2 where t1.类型编号 = t2.类型编号;
select *from 图书类型表 t1 inner join 图书详情表 t2 on t1.类型编号 = t2.类型编号;

• 通常是通过主表的主键字段关联从表的外键字段
• 如果两张表中关联的字段名一致，一定要通过"表名.字段名"进行区分，通常给表重命名
• 如果使用inner join，带条件时需要加如where子句；如果使用，隔开各个表，带条件时用and拼接条件
• 内连接只会显示两张表中有关联的数据

左连接

-- 在保证左表数据显示完整的情况下，关联右表中的数据，没有关联的数据用null表示
select * from 表1 left join 表2 on 表1.字段 = 表2.字段;
-- 以上语句中表1称为左表，表2称为右表，会完整显示表1中的顺序

右连接

-- 在保证左表数据显示完整的情况下，关联右表中的数据，没有关联的数据用null表示
select * from 表1 right join 表2 on 表1.字段 = 表2.字段;
-- 以上语句中表2称为右表，表1称为左表，会完整显示表2中的顺序

嵌套查询

将查询出的结果继续嵌套使用在另一个查询中

-- 查询大于平均价格的图书
select * from 表
where price > (select avg(price) from 表)

-- 根据作者分组，查询每组大于平均价格的图书
select * from 表 t1,(select author,avg(price) avg_price from 表 group by author) t2 where t1.author = t2.author and price > avg_price

实际业务对应sql

登录

接收账号密码，查询用户表中是否存在记录

select from 表名 where 账号=？ and 密码=？

如果能查到数据，说明存在该用户，可以登录，如果查询结果为空，说明用户名或密码有误

注册

接收账号密码，添加到用户表中

select * from 表 where 账号=？

insert into 表 values(null,账号，密码)

充值

登录成功后，修改余额字段

update 表 set 余额=余额+充值金额 where 用户编号=？

删除

逻辑删除

不删除数据，只是不显示数据

可以给表中添加“是否删除”字段，用0表示未删除，用1表示已删除，如果删除数据时，执行修改操作，将“是否删除”字段的值设置为1。

update 表 set deleted=1 where 用户编号=？

最终在查询所有时，加上“是否删除”=0条件

select * from 表 where deleted = 0;

物理删除

将数据真正删除

delete from 表 where 用户编号=？