数据库表及数据的增删改查

一、数据库建表

在创建表之前，先了解一下常用的数据类型，主要分为三类：数值类型、字符串类型、日期时间类型。（加粗的属于常用类型）

了解表常用的数据类型：

数值类型示例：
1).年龄字段 - 不会出现负数, 而且人的年龄不会太
大
age tinyint unsigned (0-255)
2).分数 - 总分100分, 最多出现一位小数
score double(4,1)
日期时间示例:
1).生日字段 birthday
birthday date
2).创建时间 createtime
createtime datetime
字符类型示例：
1).用户名 username ------> 长度不定, 最长不会
超过50
username varchar(50) - > 2 2
2).性别 gender ---------> 存储值, 不是男,就是女
gender char(10) - > 1 10
3).手机号 phone --------> 固定长度为11
phone char(11)

 注：char 与 varchar 都可以描述字符串，char是定长字符串，指定长度多长，就占用多少个字符，和字段值的长度无关 。而varchar是变长字符串，指定的长度为最大占用长度 。

 表操作-修改 DDL

• 创建表

CREATE TABLE [IF NOT EXISTS] tab_name(
col_name datatype [COMMENT '注释'],
col_name datatype
)[CHARACTER set 编码格式];

 

create table goods(                    
good_no int comment'商品编号',
goods_name varchar(50) comment'商品名称',
cost double comment'商品成本',
price double comment'商品售价',
count int comment'商品库存',
create_time datetime comment'商品上架时间',
category_no int comment'商品分类编号');

• 添加列

alter table 表名 add [column] 字段名 类型;

• 修改列

alter table 表名 modify [column] 字段名 类型;

• 修改列名称和类型

alter table 表名 change [column] 原字段名 新字段名 新列类型 [comment 注释] [约束];

• 删除列

alter table 表名 drop [column] 字段名;

• 修改表名

rename table 旧名称 to 新名称;
或者
alter table 表名称 rename to 新名称;

• 删除表

drop table [if exists] 表名;

• 截断表

truncate table 表名;
 # 删除表之后会创建一个空表

注：删除表的时候，表中的全部数据也都会被删除

•  创建和某表结构一样的表

create table 表名 like 要复制的表;
# 当要复制的表不在当前数据库时
create table 表名 like 数据库名.要复制的表;

二、数据管理

1.插入数据 DML

insert into 表名[(字段名称1，字段名称2，字段名称3)] value(value1，value 2，
value 3);
insert into 表名[(字段名称1，字段名称2，字段名称3)] values(value1，value 2，
value 3);
# 如果前面有字段按照前面的字段顺序添加，没有按照默认的顺序添加
# 插入多条数据
insert into 表名 values (), (), ();

 2.查询数据 DQL

# 无条件查询
select *(字段1，字段2，字段3 . ) from
表名;
# 等值查询
select * from 表名 where 字段名 =value;
# 设置列别名
select 字段名 [as] '别名' from 表名 WHERE 条件;
# 剔除重复行
select distinct * from 表名;
# 只是在显示的时候不显示重复的数据，数据未删除

3.修改数据

update 表名 set 字段 = value where 条件;
# 修改满足条件的字段值

4.删除数据

delete from 表名 where 条件;
# 删除满足条件的行

三、条件查询

1.条件过滤

# WHERE 子句表示查询的条件
# [value1, value2]
SELECT * FROM 表名 WHERE 字段 BETWEEN value1 AND value2;
# 字段在[value1, value2 . ]任意一个就可以
SELECT * FROM 表名 WHERE 字段 IN(value1, value2, value3);
# 字段不在[value1,value2,value3]中
SELECT * FROM 表名 WHERE 字段 NOT IN(value1, value2, value3);
- & 条件1 和条件2 都为 true 的结果显示出来
SELECT * FROM 表名 WHERE 条件1 AND 条件2;
满足条件1 和满足条件2的结果都可以显示出来
SELECT * FROM 表名 WHERE 条件1 OR 条件2;

例：

1. select * from student02 where id between 2 and 4;

2. select * from student02 where id in(2,4,5);

 

3.  select * from student02 where id not in(2,4,5);

4.select * from student02 where id=2 and name='陆光';

5.select * from student02 where id=4 or name='陆光';

 2.算术运算

# 可以对查询出来的结果进行算数运算
SELECT 字段1+ 字段2 FROM 表名;
# 修改的时候也可以进行算数运算
UPDATE 表名 SET 字段=字段+value WHERE 条件;

例：

1.select score+addscore from student02;

2.update student02 set score=score+30;

 3.NULL值查询

# NULL 值只能无法通过等值操作查询
# NULL值指的是未填值，注意跟空字符串做区分
SELECT * FROM 表名 WHERE 字段 IS NULL;
SELECT * FROM 表名 WHERE 字段 IS NOT NULL;

例：

1.select * from student02 where age is null;

 2.select * from student02 where age is not null;

 4.模糊查询

LIKE 模糊查询 _ 表示一个任意字符 %表示零个或多个任意字符
# xa xb
SELECT * FROM 表名 WHERE 字段 LIKE 'x_';
# xa x xbs
SELECT * FROM 表名 WHERE 字段 LIKE 'x%';
# xa ax axb axbc
SELECT * FROM 表名 WHERE 字段 LIKE '%x%';
# ax axb axbc
SELECT * FROM 表名 WHERE 字段 LIKE '_x%';

例：select * from student02 where name like '%小_';

5.逻辑运算

SELECT * FROM 表名 WHERE 字段 > value;
# WHERE 条件语句里面可以写 > < =

例：select * from student02 where id<=2;

 6.排序

# ORDER BY 对结果集排序 DESC 降序 ASC 升序（默认）
SELECT * FROM 表名 ORDER BY 字段1 DESC, 字段2 ASC;
# 先按照dept_no 降序排序，如果dept_no字段相同，按照d_name降序排序
select * from dept ORDER BY dept_no desc, d_name desc;
SELECT * FROM 表名 ORDER BY 字段 ASC;

例：

select id,name from student02 order by id desc ;

（降序）

 

（升序）

7.分页查询

# LIMIT 对结果集分页 参数1：起始行；参数2：显示条数
# 页码 n 页大小 s LIMIT (n-1) * s, s
SELECT * FROM 表名 LIMIT 0,2; # 从第0行开始显示两条数据

例：第二页，一页三行select id,name from student02 limit 3,3;

8.单行函数

# length 计算长度
SELECT length("123");
select length(字段) from 表；
# upper/lower 大、小写转换
SELECT UPPER("a"), LOWER('A');
select upper(字段),lower(字段) from表；
# concat 字符串拼接
SELECT CONCAT(upper('smith'),'john');
select concat(字段1, 字段2) from 表；