Mysql基础2

Mysql基础2

一、有关数据库和表的信息

1.1、需要显示数据库

select database();

1.2、查找数据库中的表

show tables;

1.3、当你需要了解表的数据结构

describe 表名;

二、常见的查询示例

1、列的最大值

1.1,先创建一张表shop，填充两条数据

CREATE TABLE shop (
    article INT UNSIGNED  DEFAULT '0000' NOT NULL,
    dealer  CHAR(20)      DEFAULT ''     NOT NULL,
    price   DECIMAL(16,2) DEFAULT '0.00' NOT NULL,
    PRIMARY KEY(article, dealer));
INSERT INTO shop VALUES
    (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
    (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

1.2查询表的数据

select *from shop order by article

1.3最大的article

select max(article) from shop

as的用法，一般用于列名，表名的重命名

select max(article) as article from shop

2、查询列的最大值

2.1通过子查询轻松完成

select *from shop
where price=(
select max(price) from shop);

2.2另一种解决方案，使用left join

如果在Mysql1中还对left join on没有了解透彻

这里再讲解一下。

笛卡尔积：什么是笛卡尔积？笛卡尔积又叫笛卡尔乘积，在数学中有A{a,b},B{0,1,2}两个集合

它的笛卡尔积分别是：

(a,0) , (a,1) ,(a,2) ,(b,0) ,(b,1) ,(b,2)

left join 先把左边的表数据全部查询出来，将表A和表B做个笛卡尔积，然后用A作为基表，去掉笛卡尔积中表A为null的数据，就是你的结果。

假设有两张表

table1

id	size
1	10
2	20
3	30

table2

size	name
10	AA
20	BB
30	CC

select *from table1 t1 left join table2 as t2 on t1.size=t2.size where t2.name='AA';

id	size	size(1)	name
1	10	10	AA

select *from table1 t1 left join table2 as t2 on t1.size=t2.size and t2.name='AA';

id	size	size(1)	name
1	10	10	AA
2	20
3	30

可以看出来left join on左边的是查询所有的，where右边的是条件，在左边所有的数据中筛选size相等(3条数据)

并且name='AA'的

and 是先把左边的全部查询出来，筛选size相等(3条数据)，在再右边并上name='AA'的t2中查询

2、第二种查询

select s1.article,s1.dealer,s1.price
from shop s1
left join shop s2 on s1.price<s2.price
where s2.article is null;

left join on左边查询了所有的shop1记录 有7条

s1.price<s2.price 有21条数据（6+5+4+3+2+1）s1.max.price()==s2.max.price(); 共22条数据

所有最后一条数据一定有null

而，查询s1数据是22条，查询s2数据 21条有记录，最后一条是null

最后通过where判断，为null的就是最大的，

sql中使用*做查询的弊端

1、如果采用 select * 进行查找时，查询到的列是按照它们在表的原始位置展示的；如果客户端同样采用列的原始位置进行引用，如果更改表结构，会导致难以察觉的错误；

2、使用 * 时，数据库会先查数据字典，明确 * 代表什么，这会在分析阶段造成大量开销；

3、select * 最大的问题是可能会多出一些不用的列，导致无法使用索引覆盖，导致查询成本几何层级的增加

4、不需要的字段会增加数据传输的时间，如果是本地客户端，连接的事本地的mysql服务器，tcp协议传输数据会增加额外时间；如果是db和客户端不在同一台机器，比如连接到阿里云，则开销会更加明显

5、如果查询的时候获取了不必要的列，字段较多时，mysql并非一次性保存，而是主次分布内存，当时用完后，再次分配。如此会导致多次分配，频繁分配会增加额外消耗时间

6、如果sql语句复杂，select * 会解析更多的对象，字段，权限，属性等内容，增加数据库负担

max()函数的错误理解，max()只是求出最大的值，并不是最大的一列

2.3、第三种查询,通过按价格降价排序

select article,dealer,price
from shop
order by price desc
limit 1;

按price倒序，然后求出第一行就是最大的

3、每组最大列数

select article,max(price) as price
from shop          #查找出来的只有一行，就是最大值，这个时候需要分组
group by article
order by article

4、保持某一列组间最大的值

4.1：子查询

select article,dealer,price
from shop s1
where price=(select max(s2.price)
             from shop s2
            where s1.article=s2.article)
            order by article;

虽然子查询可以方便的解决，但是一般开发是不会这样写的，效率低下

中庸之道：

代码越简单，计算机就要一个方法一个方法的去解析，只要你自己代码多，方法少，计算机识别的快，开销就小。

但是你又不想写的复杂，又要让计算机识别的快，减少开销，所以就有了sql的调优

而编程语言中的各种框架，模式就是为了，让你不用写的那么复杂，也让计算机识别的快，一样的道理。

4.2：不相关的子查询

select s1.article,dealer,s1.price
from shop s1
join (select article,max(price) as price
     from shop
     group by article) as s2
     on s1.article=s2.article and s1.price =s2.price
     order by article;

4.3：left join

select s1.article,s1.dealer,s1.price
from shop s1
left join shop s2 on s1.article=s2.article
and s1.price<s2.price
where s2.article is null
order by s1.article;

4.4:带窗口函数的公用表达式

WITH s1 AS (
   SELECT article, dealer, price,
          RANK() OVER (PARTITION BY article
                           ORDER BY price DESC
                      ) AS `Rank`       #左上角esc下方的`
     FROM shop
)
SELECT article, dealer, price
  FROM s1
  WHERE `Rank` = 1
ORDER BY article;

5、使用用户自定义的变量

select @min_price:=min(price),@max_price:=max(price) from shop

select *from shop where price=@min_price or price=@max_price;

@后面自己定义名字

6、使用外键

1、创建父表和子表

create table parent(
 id int not null,
 primary key(id)
)engine=innode;

create table child(
 id int,
 parent_id int,
 index par_ind (parent_id),
 foreign key (parent_id)
  references parent(id)
 )engine=innode;

index 是为了创建一个索引

innodb是mysql的存储引擎，方便处理存储量大的数据

2、插入两条数据

insert into parent (id) values (1);
insert into child (id,parent_id) values (1,1);

3、在子表中插入一行，其值在父表不存在，会报错

insert into child (id,parent_id) values(2,2);

1452 - Cannot add or update a child row: a foreign key constraint fails (coronavirus.child, CONSTRAINT child_ibfk_1 FOREIGN KEY (parent_id) REFERENCES parent (id))
时间: 0.007s

4、正确操作

drop table child;
create table child(
  id int,
  parent_id int,
  index par_ind (parent_id),
  foreign key parent(id)
  on update cascade
  on delete cascade
) engine=innodb;

drop table child：drop删除表的结构以及所有数据，并且将表所占用的控件全部释放

casede：表的关联结构，与这个表有关系的表现在没关系了

7、or的使用

如果你搜索两个不同的键，可以使用or

select field1,field2 from test
where field1='1' or field2='2';

也可以使用单独的两个select

select field1,fidld2 from test
where field1='1'
union
select field1,field2 from test
where field2='2';

8、计算每天的访问量

8.1组函数的使用，计算每月用户访问网页的天数

unsigned 无符号， 针对整型的一种增加长度的数据类型

int 65535

int unsigned 65535*2

create table t1(year,month,month int unsigned,
               day int unsigned);
           
 insert into t1 values(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
                      (2000,2,23),(2000,2,23);

示例中包含了访问的年月日

select year,month,bit_count(bit_or(1<<day)) as days
from t1
group by year,month;

1<<day，将1向左移动day位，001

bit_or()

9、使用AUTO_INCREMENT

9.1该属性可为新行生成唯一标识

create table animals(
id mediumint not null auto_increment,
name char(30) not null,
primary key (id)
);
##一般情况下主键自增简写mediumint 比int范围就小一丢丢
id int(5) primary key auto_increment;

insert into animals (name) values
  ('dog'),('cat'),('penguin'),
  ('lax'),('whale'),('ostrich');
  
select *from animals;  

简单写几个

id	name
1	dog
2	cat
3	penguin

没有为列指定序列号，mysql是自动分配的，还可以显式的将0分配给列以生成序列号，除非启用了

NO_AUTO_VALUE_ON_ZERO SQL模式

INSERT INTO animals (id,name) VALUES(0,'groundhog');

insert into animals(id,name) values(null,'squirrel');

即使设置成0，也是从第7为开始排序的。

要想设置成功需要重置序列 0和null除过

insert into animals(id,name) values(100,'rabbit');

下一次再设置就是从100以后了

如果需要从1以外的值来键入

alter table tab1 auto_increment=100;

枚举：

可以使用枚举使多列数据中，列的值相同

Mysql的另外一个存储引擎MyISAM，不支持事务，不支持外键，支持全文索引，由三个文件组成。

frm文件保存表的结构，MYD文件保存表的数据，MYI文件保存表的索引文件。

读取阻塞：我写数据的时候，其他人不能读，也不能同时写，但是我读取数据的时候，你可以和我一起读。

这里只是简单介绍一下。

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

grp	id	name
fish	1	lax
mammal	1	dog
mammal	2	cat
mammal	3	whale
bird	1	penguin
bird	2	ostrich

enum：枚举中fish mammal bird都是1

​