MySQL_day2笔记

数据表的基本操作

建表

create table commoditytype(
ct_id int(11) primary key,
ct_name varchar(50) not null
)default charset=utf8; 

create table commodity(
c_id int(11) primary key,
c_name varchar(50) not null,
c_madein varchar(50) not null,
c_type int(11) not null,
c_inprice int(11) not null,
c_outprice int(11) ,
c_num int(11) default '100',
constraint fk_1 foreign key (c_type) references commoditytype (ct_id)
#commodity与commoditytype的外键
)defalut charset=utf8;

• 根据以上的两张表进行增删改查

增

#为第一张表进行增加操作，多个操作用“，”分隔
insert into commoditytype (ct_id,ct_name) values (1,'玩具'),(2,'文具'),(3,'家具');
#为第二张表进行增加操作
insert into `commodity` VALUES ('1', '变形金刚-擎天柱', '中国', '1', '20', '50', '60');

删

delete from commoditytype where ct_id=2;
#全表删除
delete from commoditytype;

改

update commmoditytype set ct_name='家具' where ct_id=2;
#全表修改为相同数据
update commoditytype set ct_name='家具';

关系运算符与逻辑运算符

算数运算符	描述	逻辑运算符	描述
>	大于	AND(&&)	逻辑与
<	小于	OR(||)	逻辑或
=	等于	XOR	逻辑异或
!=(<>)	不等于	NOT(!)	逻辑非
<=	小于等于
>=	大于等于

查
查询表中所有数据

select * from commoditytype;

查询删除重复项

select distinct c_type from commodity;

关系运算与逻辑运算与between and
筛选进价在10~100的类型为玩具(1)的商品

select c_name as 商品名称,c_inprice as 商品进价
from commodity
where (c_inprice >= 10 and c_inprice <= 100) and c_type = 1;
#where (c_inprice between 10 and 100) and c_type = 1;#包括两端的值

筛选进价不在10~100的类型为玩具(1)的商品

select c_name as 商品名称,c_inprice as 商品进价
from commodity
where (c_inprice < 10 or c_inprice > 100) and c_type = 1;
#where (c_inprice not between 10 and 100) and c_type = 1;#不包括两端的值

关键字

is null：判断是否为空

select c_name,c_outprice,c_outprice-c_inprice
from commodity
where c_outprice is null;

is not null：判断非空

select c_name,c_outprice,c_outprice-c_inprice
from commodity
where c_outprice is not null;

in：里面的数字之间的关系是或关系

select c_name,c_inprice
from commodity
where c_inprice not in (10,20,30,40);

not in：里面的数字之间的关系是且关系

select c_name,c_inprice
from commodity
where c_inprice not in (10,20,30,40);

like:_ 匹配一个字符；% 匹配一个或多个字符
     匹配第二个字符为华的商品名称

select c_name
from commodity
where c_name like '_华%';

     匹配最后一个字符为典的商品名称

select c_name
from commodity
where c_name like '%典';

order by：排序（desc 逆序；默认为 asc:从大到大排序）
     选出5个价格最高的玩具显示出他的商品名称和商品进价

     使用limit实现翻页效果

select c_name,c_inprice
from commodity
where c_type=1
order by c_inprice desc
limit 5;

group by：实现分组，一般都是和统计函数一起出现的，如下avg()举例。

统计函数(聚合函数)

聚合函数
COUNT()函数：统计记录数
AVG()函数：求平均值
SUM()函数：求和
MAX()函数：求最大数
MIN()函数：求最小数
统计函数均忽略NULL值

• count() 函数
  如果表中无数据，count()函数返回的是0，其它函数返回null，可以解决Java读取数据时的空指针异常*

     统计类型为玩具的总数量

select count(*)
from commodity
where c_type=1;

• avg()函数

     查找各商品类型的平均进价

select c_type,avg(c_inprice)
from commodity
group by c_type;

     查找各类型的平均进价，并筛选出平均进价大于100的商品类型

     对分组查询结果进行条件限制查询，不能使用WHERE关键字，需要使用HAVING关键字，having优先级比where低，where不能在having后使用

select c_type,avg(c_inprice)
from commodity
group by c_type
having avg(c_inprice)>100;

• sum()函数；Max函数；Min函数

select sum(c_inprice),max(c_inprice),min(c_inprice)
from commodity;

sql优化（1）

• 索引扫描

SQL CREATE INDEX语法

# 在表上创建一个简单的索引，允许使用重复的值
create index index_name
on table_name(column_name)

SQL CREATE UNIQUE INDEX语法

# 在表上创建一个唯一的索引，唯一的索引意味着两个行不能拥有相同的索引值
create unique index index_name
on table_name(column_name)

• 注意项：

1、尽量不要有空判断的语句，空判断将导致全表扫描；解决方案对有null值得列创建数据库默认值

select *
from commodity
where c_outprice is null;# 空判断将导致全表扫描
# where c_outprice = '';# 假设有默认值：空字符串

2、尽量不要使用不等于条件，这会导致全表扫描对于不等于这种情况，考虑改为范围查询解决

select *
from commodity
where c_outprice <> 20;# 不等于条件，将导致全表扫描
# where c_outprice > 20;# 假设20是最大值

3、尽量不要使用左右模糊查询,这会导致全表扫描对于左右模糊查询的情况，试着改为右侧模糊查询，这样是可以索引查找的

select *
from commodity
where c_name like '%玩具%';# 将导致全表扫描
# where c_name like '乐高玩具%';# 右侧模糊查询