Mysql学习笔记(更新中) - JunMo博客

JunMo的博客

2019
记录生活
首页 » 学习笔记 » Mysql学习笔记(更新中)

Mysql学习笔记(更新中)

最近在学习MySql,顺便记录一下学习笔记

先说数据库的 增(create) 删(delete) 改(update) 查(select

show databases; 查看数据库

然后使用 create databas 库名; 来创建数据库

使用status;查看数据库的状态

status; 查看数据库状态

然后使用use 来选择数据库;

use 数据库名;

使用show tables;来查看一下当前数据库里的表

show tables; 查看表

使用 desc 表名 看一下表的结构。

desc 表名; 查看表结构

使用 show create table 表名; 来查看建表语句

show create table; 查看建表语句

现在我们来删除啦用 drop 来删除。

drop database 库名; 删除数据库

drop table 表名; 删除表

现在来说建表。

   创建表:
 create table tbName (
列名称1 列类型 [列参数] [not null default ],
....列2...
....
列名称N 列类型 [列参数] [not null default ]
)engine myisam/innodb charset utf8/gbk

现在我们来创建一个名为user的表,表里面有id列,name列,age列

create table user (
    id int auto_increment,
    name varchar(20) not null default '',
    age tinyint unsigned not null default 0,
   index id (id)
   )engine=innodb charset=utf8;
注:
not null 是不能为空, default 0 代表默认值为 0
innodb是表引擎,也可以是myisam或其他,但最常用的是myisam和innodb,
charset时字符编码 常用的有utf8,gbk;

现在来使用show tables来查看一下表,和desc 来查看一下表的结构。

可以看到我们已经创建成功了,接下来干嘛呢?当然是插入数据啊,什么?你还要在建好的表里面修改列,当然可以。

添加列
alter table 表名
add 列名称1 列类型 [列参数] [not null default ] #(add之后的旧列名之后的语法和创建表时的列声明一样)

添加一个性别列,不得为空。

alter table user add sex varchar(4) not null default '';

剩下一些语句我就不演示了。

删除列:
alter table 表名
drop 列名称;

增加主键:
alter table 表名 add primary key(id); 添加列名为id的主键

删除主键:
alter table  表名 drop primary key;

增加索引:
alter table 表名 add [unique|fulltext] index 索引名(列名);

删除索引:
alter table 表名 drop index 索引名;

清空表的数据
truncate 表名;

接下来就是添加数据了。

insert into 表名(列1,列2) values(值1,值2);  插入指定列
	insert into 表名 values (值1,值2);    插入所有列
	insert into 表名 values	 一次插入多行 
	(值1,值2),
	(值1,值2),
	(值1,值2);

 

现在我们只插入id列和name列,选什么列就填什么值。没有的选的列会为默认值。

insert into user(id,name) values(1,'lishi');

在来插入所有列。(语句会被拦截所以我加个空格)

i nsert into user values(2,'lishi',15,'nan');

在来演示一下多行插入(好累啊!!!)

i nsert into user values(3,'zhangsan',19,'nan'),
(4,'xiaoho',20,'nv'),
(5,'wangwu',18,'nan');

添加完数据我们要干嘛呢?当然是查看一下啊,先简单查询一下,详细的下面在写。

s elect * from 表名;

可以看到我们刚刚添加的数据都在这里面。

现在我们来修改,试试看把xiaoho的age修改为15吧。

u pdate 表名 set 修改的值 where 条件; 

修改完成查看一下修改成功了。

是不是看第一行很不舒服啊?接下来我们就开始删除他。一定要加条件,不然所有数据都会被删除只能跑路了,删除完查看一下可以看到已经删除了。

 d elete from 表名 where 条件;

然后就到了查询,开始做练习吧。

练习数据库下载 点击下载 密码:ko4e 可以使用phpmyadmin导入,导入要先选择数据库在导入不然会出错。
 
商品表:goods
goods_id --主键,
goods_name -- 商品名称
cat_id  -- 栏目id
brand_id -- 品牌id
goods_sn -- 货号
goods_number -- 库存量
shop_price  -- 价格
goods_desc --商品详细描述

栏目表:category
cat_id --主键 
cat_name -- 栏目名称
parent_id -- 栏目的父id

然后就是题目以及答案。(当时练了有一段时间)在Windows下乱码的话输入 set names GB2312;

查出满足以下条件的商品
1.1:主键为32的商品
select goods_id,goods_name,shop_price 
     from ecs_goods
     where goods_id=32;
1.2:不属第3栏目的所有商品
select goods_id,cat_id,goods_name,shop_price  from ecs_goods
     where cat_id!=3;

1.3:本店价格高于3000元的商品

 select goods_id,cat_id,goods_name,shop_price  from ecs_goods
     where shop_price >3000;

1.4:本店价格低于或等于100元的商品
select goods_id,cat_id,goods_name,shop_price  from ecs_goods where shop_price <=100;

1.5:取出第4栏目或第11栏目的商品(不许用or)
select goods_id,cat_id,goods_name,shop_price  from ecs_goods
     where cat_id in (4,11);


1.6:取出100<=价格<=500的商品(不许用and)
select goods_id,cat_id,goods_name,shop_price  from ecs_goods
     where shop_price between 100 and 500;


1.7:取出不属于第3栏目且不属于第11栏目的商品(and,或not in分别实现)
select goods_id,cat_id,goods_name,shop_price  from ecs_goods     where cat_id!=3 and cat_id!=11;

select goods_id,cat_id,goods_name,shop_price  from ecs_goods     where cat_id not in (3,11);



1.8:取出价格大于100且小于300,或者大于4000且小于5000的商品()
select goods_id,cat_id,goods_name,shop_price  from ecs_goods where shop_price>100 and shop_price <300 or shop_price >4000 and shop_price <5000; 1.9:取出第3个栏目下面价格<1000或>3000,并且点击量>5的系列商品
select goods_id,cat_id,goods_name,shop_price,click_count from ecs_goods where
cat_id=3 and (shop_price <1000 or shop_price>3000) and click_count>5;

1.10:取出第1个栏目下面的商品(注意:1栏目下面没商品,但其子栏目下有)
select goods_id,cat_id,goods_name,shop_price,click_count from ecs_goods
     where cat_id in (2,3,4,5);

1.11:取出名字以"诺基亚"开头的商品
select goods_id,cat_id,goods_name,shop_price  from ecs_goods     where goods_name like '诺基亚%';


1.12:取出名字为"诺基亚Nxx"的手机
select goods_id,cat_id,goods_name,shop_price  from ecs_goods  
   where goods_name like '诺基亚N__';


1.13:取出名字不以"诺基亚"开头的商品
select goods_id,cat_id,goods_name,shop_price from ecs_goos
     where goods_name not like '诺基亚%';

1.14:取出第3个栏目下面价格在1000到3000之间,并且点击量>5 "诺基亚"开头的系列商品
select goods_id,cat_id,goods_name,shop_price  from ecs_goods where 
cat_id=3 and shop_price>1000 and shop_price <3000 and click_count>5 and goods_name like '诺基亚%';


select goods_id,cat_id,goods_name,shop_price  from ecs_goods where 
shop_price between 1000 and 3000 and cat_id=3  and click_count>5 and goods_name like '诺基亚%';

1.15 一道面试题
有如下表和数据,查出num>=20 and num<=39的数字,
并且,把num值处于[20,29]之间,显示为20
num值处于[30,39]之间的,显示30

mian表
+------+
| num  |
+------+
|    3 |
|   12 |
|   15 |
|   25 |
|   23 |
|   29 |
|   34 |
|   37 |
|   32 |
|   45 |
|   48 |
|   52 |
+------+

1.16 练习题:
把good表中商品名为'诺基亚xxxx'的商品,改为'HTCxxxx',
提示:大胆的把列看成变量,参与运算,甚至调用函数来处理 .
substring(),concat()


2	分组查询group:
2.1:查出最贵的商品的价格
select max(shop_price) from ecs_goods;

2.2:查出最大(最新)的商品编号
select max(goods_id) from ecs_goods;

2.3:查出最便宜的商品的价格
select min(shop_price) from ecs_goods;

2.4:查出最旧(最小)的商品编号
select min(goods_id) from ecs_goods;

2.5:查询该店所有商品的库存总量
select sum(goods_number) from ecs_goods;

2.6:查询所有商品的平均价
 select avg(shop_price) from ecs_goods;

2.7:查询该店一共有多少种商品
 select count(*) from ecs_goods;


2.8:查询每个栏目下面
最贵商品价格
最低商品价格
商品平均价格
商品库存量
商品种类
提示:(5个聚合函数,sum,avg,max,min,count与group综合运用)
select cat_id,max(shop_price) from ecs_goods  group by cat_id;


3 having与group综合运用查询:
3.1:查询该店的商品比市场价所节省的价格
select goods_id,goods_name,market_price-shop_price as j 
     from ecs_goods ;


3.2:查询每个商品所积压的货款(提示:库存*单价)
select goods_id,goods_name,goods_number*shop_price  from ecs_goods

3.3:查询该店积压的总货款
select sum(goods_number*shop_price) from ecs_goods;

3.4:查询该店每个栏目下面积压的货款.
select cat_id,sum(goods_number*shop_price) as k from ecs_goods group by cat_id;

3.5:查询比市场价省钱200元以上的商品及该商品所省的钱(where和having分别实现)
select goods_id,goods_name,market_price-shop_price  as k from ecs_goods
where market_price-shop_price >200;

select goods_id,goods_name,market_price-shop_price  as k from ecs_goods
having k >200;

3.6:查询积压货款超过2W元的栏目,以及该栏目积压的货款
select cat_id,sum(goods_number*shop_price) as k from ecs_goods group by cat_id
having k>20000

3.7:where-having-group综合练习题
有如下表及数据
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 张三 | 数学    |    90 |
| 张三 | 语文    |    50 |
| 张三 | 地理    |    40 |
| 李四 | 语文    |    55 |
| 李四 | 政治    |    45 |
| 王五 | 政治    |    30 |
+------+---------+-------+

要求:查询出2门及2门以上不及格者的平均成绩

## 一种错误做法
mysql> select name,count(score<60) as k,avg(score) from stu group by name having k>=2;
+------+---+------------+
| name | k | avg(score) |
+------+---+------------+
| 张三     | 3 |    60.0000 |
| 李四     | 2 |    50.0000 |
+------+---+------------+
2 rows in set (0.00 sec)

mysql> select name,count(score<60) as k,avg(score) from stu group by name; +------+---+------------+ | name | k | avg(score) | +------+---+------------+ | 张三 | 3 | 60.0000 | | 李四 | 2 | 50.0000 | | 王五 | 1 | 30.0000 | +------+---+------------+ 3 rows in set (0.00 sec) mysql> select name,count(score<60) as k,avg(score) from stu group by name having k>=2;
+------+---+------------+
| name | k | avg(score) |
+------+---+------------+
| 张三     | 3 |    60.0000 |
| 李四     | 2 |    50.0000 |
+------+---+------------+
2 rows in set (0.00 sec)

#加上赵六后错误暴露
mysql> insert into stu 
    -> values 
    -> ('赵六','A',100),
    -> ('赵六','B',99),
    -> ('赵六','C',98);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

#错误显现
mysql> select name,count(score<60) as k,avg(score) from stu group by name having k>=2;
+------+---+------------+
| name | k | avg(score) |
+------+---+------------+
| 张三 | 3 |    60.0000 |
| 李四 | 2 |    50.0000 |
| 赵六 | 3 |    99.0000 |
+------+---+------------+
3 rows in set (0.00 sec)

#正确思路,先查看每个人的平均成绩
mysql> select name,avg(score) from stu group by name;
+------+------------+
| name | avg(score) |
+------+------------+
| 张三 |    60.0000 |
| 李四 |    50.0000 |
| 王五 |    30.0000 |
| 赵六 |    99.0000 |
+------+------------+
4 rows in set (0.00 sec)

mysql> # 看每个人挂科情况
mysql> select name,score < 60 from stu;
+------+------------+
| name | score < 60 |
+------+------------+
| 张三 |          0 |
| 张三 |          1 |
| 张三 |          1 |
| 李四 |          1 |
| 李四 |          1 |
| 王五 |          1 |
| 赵六 |          0 |
| 赵六 |          0 |
| 赵六 |          0 |
+------+------------+
9 rows in set (0.00 sec)

mysql> #计算每个人的挂科科目
mysql> select name,sum(score < 60) from stu group by name;
+------+-----------------+
| name | sum(score < 60) |
+------+-----------------+
| 张三 |               2 |
| 李四 |               2 |
| 王五 |               1 |
| 赵六 |               0 |
+------+-----------------+
4 rows in set (0.00 sec)

#同时计算每人的平均分
mysql> select name,sum(score < 60),avg(score) as pj from stu group by name;
+------+-----------------+---------+
| name | sum(score < 60) | pj      |
+------+-----------------+---------+
| 张三 |               2 | 60.0000 |
| 李四 |               2 | 50.0000 |
| 王五 |               1 | 30.0000 |
| 赵六 |               0 | 99.0000 |
+------+-----------------+---------+
4 rows in set (0.00 sec)

#利用having筛选挂科2门以上的.
mysql> select name,sum(score < 60) as gk ,avg(score) as pj from stu group by name having gk >=2; 
+------+------+---------+
| name | gk   | pj      |
+------+------+---------+
| 张三 |    2 | 60.0000 |
| 李四 |    2 | 50.0000 |
+------+------+---------+
2 rows in set (0.00 sec)



4:	order by 与 limit查询
4.1:按价格由高到低排序
select goods_id,goods_name,shop_price from ecs_goods order by shop_price desc;

4.2:按发布时间由早到晚排序
select goods_id,goods_name,add_time from ecs_goods order by add_time;

4.3:接栏目由低到高排序,栏目内部按价格由高到低排序
select goods_id,cat_id,goods_name,shop_price from ecs_goods
     order by cat_id ,shop_price desc;


4.4:取出价格最高的前三名商品
select goods_id,goods_name,shop_price from ecs_goods order by shop_price desc limit 3;



4.5:取出点击量前三名到前5名的商品
select goods_id,goods_name,click_count from ecs_goods order by click_count desc limit 2,3;

5	连接查询
5.1:取出所有商品的商品名,栏目名,价格
select goods_name,cat_name,shop_price from 
ecs_goods left join ecs_category
on ecs_goods.cat_id=ecs_category.cat_id;

5.2:取出第4个栏目下的商品的商品名,栏目名,价格
select goods_name,cat_name,shop_price from 
ecs_goods left join ecs_category
on ecs_goods.cat_id=ecs_category.cat_id
where ecs_goods.cat_id = 4;



5.3:取出第4个栏目下的商品的商品名,栏目名,与品牌名
select goods_name,cat_name,brand_name from 
ecs_goods left join ecs_category
on ecs_goods.cat_id=ecs_category.cat_id
left join ecs_brand 
on ecs_goods.brand_id=ecs_brand.brand_id
where ecs_goods.cat_id = 4;

5.4: 用友面试题

根据给出的表结构按要求写出SQL语句。
Match 赛程表
字段名称	字段类型	描述
matchID	int	主键
hostTeamID	int	主队的ID
guestTeamID	int	客队的ID
matchResult	varchar(20)	比赛结果,如(2:0)
matchTime	date	比赛开始时间


Team 参赛队伍表
字段名称	字段类型	描述
teamID	int	主键
teamName	varchar(20)	队伍名称


Match的hostTeamID与guestTeamID都与Team中的teamID关联
查出 2006-6-1 到2006-7-1之间举行的所有比赛,并且用以下形式列出:
拜仁  2:0 不来梅 2006-6-21

mysql> select * from m;
+-----+------+------+------+------------+
| mid | hid  | gid  | mres | matime     |
+-----+------+------+------+------------+
|   1 |    1 |    2 | 2:0  | 2006-05-21 |
|   2 |    2 |    3 | 1:2  | 2006-06-21 |
|   3 |    3 |    1 | 2:5  | 2006-06-25 |
|   4 |    2 |    1 | 3:2  | 2006-07-21 |
+-----+------+------+------+------------+
4 rows in set (0.00 sec)

mysql> select * from t;
+------+----------+
| tid  | tname    |
+------+----------+
|    1 | 国安     |
|    2 | 申花     |
|    3 | 公益联队 |
+------+----------+
3 rows in set (0.00 sec)

mysql> select hid,t1.tname as hname ,mres,gid,t2.tname as gname,matime
    -> from 
    -> m left join t as t1
    -> on m.hid = t1.tid
    -> left join t as t2
    -> on m.gid = t2.tid;
+------+----------+------+------+----------+------------+
| hid  | hname    | mres | gid  | gname    | matime     |
+------+----------+------+------+----------+------------+
|    1 | 国安     | 2:0  |    2 | 申花     | 2006-05-21 |
|    2 | 申花     | 1:2  |    3 | 公益联队 | 2006-06-21 |
|    3 | 公益联队 | 2:5  |    1 | 国安     | 2006-06-25 |
|    2 | 申花     | 3:2  |    1 | 国安     | 2006-07-21 |
+------+----------+------+------+----------+------------+
4 rows in set (0.00 sec)

6	union查询
6.1:把ecs_comment,ecs_feedback两个表中的数据,各取出4列,并把结果集union成一个结果集.

6.2:3期学员碰到的一道面试题
A表:
+------+------+
| id   | num  |
+------+------+
| a    |    5 |
| b    |   10 |
| c    |   15 |
| d    |   10 |
+------+------+

B表:
+------+------+
| id   | num  |
+------+------+
| b    |    5 |
| c    |   15 |
| d    |   20 |
| e    |   99 |
+------+------+


要求查询出以下效果:
+------+----------+
| id   |    num   |
+------+----------+
| a    |        5 |
| b    |       15 |
| c    |       30 |
| d    |       30 |
| e    |       99 |
+------+----------+

create table a (
id char(1),
num int
)engine myisam charset utf8;

insert into a values ('a',5),('b',10),('c',15),('d',10);

create table b (
id char(1),
num int
)engine myisam charset utf8;

insert into b values ('b',5),('c',15),('d',20),('e',99);

 

如果需要反复用到一条查询语句可以建立视图。

 

create view 名字 as sql语句;

 

然后查询一下表就能看到了。

修改视图的表原表也会发生改变。

存储引擎的概念

什么是存储引擎? 数据库对同样的数据,有着不同的存储方式和管理方式 在mysql中,称为存储引擎
同样一段信息,你用什么方式来保存的? 记忆力好的可以保存在大脑中 -> 可以随时select 记忆力不好的可以抄在本子上

引擎不同,它组织数据的方式不同 mysql5以后,将 innodb 设置为默认引擎 我们之前在建t系列表的时候,直接结束,没有声明引擎,那mysql默认我们的引擎为 innodb

既然有不同的引擎,那不同的引擎之间肯定是有区别的 常用的引擎有三种 innodb,myisam,memory memory建立起来的表,数据,全部存在内存里,不存放在磁盘上,它的速度是非常快的 但是也有一个问题,如果服务器一旦关机,内容就都没有了 所以一般 ,不需要持久保存的,可以用memory引擎

面试中最容易问到的就是 面试中最容易问到的就是,myisam和 和innodb的区别 的区别 设有张马虎,李小心两人,都是地铁口的自行车管理员. 每天都有很多人来存取自行车 张马虎的管理方式是:来存自己存,不记录存的是什么车,取时交5毛,也不检查取的是否是自己的车. 李小心呢,则在存取自己车时,记录存车人的特征与自行车的特征,当人来取钱车,还要小心核一下,人与车的特征是否对应

思考: 张马虎和李小心谁对业务的处理速度更高? 张马虎 -> myisam 二者谁对自行车管理更安全? 李小心 -> innodb
张马虎,myisam,数据库崩了,数据丢失就丢失了,想找回来的难度比较大 李小心,innodb,数据库崩了,但是它有丰富的日志,每一行操作都有记录,数据比较容易找回来 说到数据库优化,这是个非常大的题目,我们学习两天都无法入门这个数据库优化, 所以此处,大家记住两者的区别就可以了

 

 

查看字符集 

show variables like '%charact%';
客户端字符集:character_set_client

连接器字符集:character_set_connection

查询返回字符集:character_set_results

当三个字符集都一样时就不会出现乱码的情况。

杜绝乱码的,检查的几个步骤

1  php 文件是 utf8

2  html 页面的 meta 信息 也是utf8

3  建表时,也是utf8
create table () charset=utf8;

4  php页面连接数据库时
set names utf8;

 

索引的概念,什么是索引?

index 中文有索引的意思 我们看的英文书,在目录部分,就写有index 数据库帮我们存储数据,存储在文件里,我们之前也看到了那个文件 MYD文件帮我存数据,当我们取数据的时候,我们希望取得比较快 那如何能让数据取的比较快呢? 当数据越来越多,MYD文件则越来越大 假设有 10000 行的数据 select .. where id=10000 那它应该迅速就来到文件的第10000行,取出这个数据 那它如何知道,这个第10000行,是在这个MYD文件中的第一个字节开始取呢?就是说,沿着这个文件,我们走多少字节,才能到第10000行呢 怎么样才能快速帮我们查到呢,这个时候就需要靠索引了

索引如何查到7? 先找到4

7>4,右拐找到6

7>6,右拐找到7

3次就能查到 不用索引来查询,40亿的数据,运气不好,需要40亿此才能查到 用索引,当数据40亿次的时候,它顶多查32次就能查到了 索引是高效组织的数据结构 我们查到 7 还不够,在7这里,还有一小行的数据,比如说是1281,这个代表了偏移字节 就是沿着MYD文件,偏移1281个字节,就能找到第7行的数据 这个就是索引的作用,它就是一个书的目录

因此: 索引提高了查询速度,降低了增删改的速度,并非加的越多越好 在实际中,有可能一张表的索引文件,比这张表的数据文件还要大 => 目录比书还厚 一般在查询频繁的列上加,而且在重复度低列上加效果更好

查看表的索引信息

show index from 表名;

 

索引的类型

1-key, 普通索引 纯粹就是帮我们组织数据,提高查询速度

2-unique key, 唯一索引 起到2个作用 1)是加快你的查询速度 是帮你约束数据,怎么个约束法? 这个数据不可能重复了,unique 中文 唯一的意思

create table t22 (
name char(10),
email char(20),
key name(name),
unique key email(email)
);

key name(name) 的意思是,针对name的列,创建索引,索引的名称也叫name 我们可以在所有的列声明完毕之后,再加索引

给t22表插入两条相同的数据

insert into t22 values ('lisi','lisi@qq.com');
insert into t22 values ('lisi','lisi@qq.com');

当我们插入第二条数据的时候,会报错 这个时候,它在维护索引的时候,会检查,这个email已经存在了,就不允许我们插入这个相同email这列值,这个是 unique key 的一个特点 比如我们在创建一个网站的时候,用户名,email这两个字段是不许重复的,我们可以用unique key ,既能提高查询速度,又能起到约束的作用。

3-primary key , 主键索引 一张表中,只可能有一个 primary key

create table t23 (
id int unsigned auto_increment primary key
);

 

4-fulltext 全文索引

注意: 中文环境下,全文索引无效 为什么呢? 因为英文,每个单词是分开的,它将每个单词分为别类,建立索引 你查这行中的任意一个单词,都有可能查询到这行数据 而中文的每个字是连在一起的,它无法区分每个字 所以就导致,在中文环境下,全文索引是无效的 在中文环境下,需要 分词+索引才有效 一般用第三方解决方案,如sphinx

 

5-设置索引长度

设置索引长度是为了优化索引 建索引时,可以只定索引列的前一部分的内容 比如前10个字符 例: email列 后面都都是 @qq.com,@163.com, 等 @后面的内容区分度不高,都是类似的内容 那我们可不可以 将 @ 前的10个字符内容给截取下来建索引 -> 可以

create table t24 (
name char(10),
email char(20),
key name(name),
unique key email(email(10))
);

注意: 这个并不是限制的email长度,只能填10个字符 而是索引只取10个字符 插入大于 10个字符 长度的email

6-多列索引

有的时候,一列索引还查询不出来什么效果 多列索引,就是把2列或多列的值,看成一个整体,然后建索引 例: 我们注册国外网站的会员时,会让我们分别注册 firstname,lastname 姓和名 分开存姓名,可以方便以后查询不同姓氏的注册用户 但大部分使用还是将姓名连接在一起查询的

create table t25 (
xing char(10),
ming char(10),
key xm(xing,ming)
);
insert into t25 values ('zhang','san');
desc t25;

以下两个select 多列索引是可以发挥作用的

select * from t25 where xing='zhang' and ming='san';
select * from t25 where xing='zhang';

下面这个select 索引是不能发挥作用的

select * from t25 where ming='san';

我们可以同 explain sql语句 来显示mysql如何使用索引来处理select语句以及连接表

explain select * from t25 where xing='zhang' and ming='san' \G

explain select * from t25 where xing='zhang' \G

possible_keys: xm 有可能使用到的key 是 xm key: xm

 

key: xm 既然索引没有发挥作用,为什么key还是xm呢? 例: 一片文章,有标题,有内容

标题:<<静夜思>>为索引 -> 对应的内容 在第9页 1是我们看到内容在第九页,就去第9也看内容了 2是我们找到index索引标题,

而不去看它的内容 从索引直接返回数据,不用走MYD文件去查询 下面这个语句可以使用上索引 因为mysql没那么傻,它会分析语义

 

下面这个语句可以使用上索引 因为mysql没那么傻,它会分析语义

explain select * from t25 where ming='san' and xing='zhang' \G

怎样区分它是否能使用上索引呢? 左前缀 一个数据,从左到右 abcdefg.. 我们给书建目录,目录是有顺序的 我们们前半部分能记得 abcd..后面记不得了,它可以帮我们定位大致的范围 那如果前面记不住,后面记得 ...efg ,那如何定位 前面的数据千变万化 有可能是zzzzefg,或这aaaaefg 等,根据右半部分无法定位 这个就是健使用的左前缀规则,从左往右侧查,索引还能部分发挥作用,左侧是确定的,相对好查询 右侧固定,左侧不知道,那则是千变万化,无法使用索引

7-冗余索引

就是在某个列上,可能存在多个索引 比如 xm(xing,ming) 我们单查ming,索引不发挥作用 ming(ming) 给ming也加上索引,单独查ming的时候,也是有索引发挥作用的,那如果 xing和ming都查,对于ming则会后两个索引发挥作用 在开发中,为了提高查询速度,我们也经常会用到冗余索引

create table t26 (
xing char(10),
ming char(10),
key xm(xing,ming),
key ming(ming)
);
show index from t26 \G
explain select * from t26 where ming='san' \G

possible_keys: ming key: ming 冗余索引在开发中,有时候是必要的.

索引操作

1.查看一张表的索引 show index from 表名 show create table 表名 查看建表语句,后面也会显示索引

2.删除索引 删除哪张表的哪个索引? 删除 alter table 表名 drop index 索引名 或 drop index 索引名 on 表名

3.添加索引 alter table 表名 add index/unique xm(xing,ming);

4.添加主键索引,不需要起名字,因为是唯一的 alter tables 表名 add primary key(id[主键名称]);

5.删除主键索引 alter table 表名 drop primary key;

常用函数

mysql中的列可以当成变量来看,既然是变量,就可以参与运算(+-*/)

select 3+2;
select 3*2;

既然能参与运算,我们在php中可以用函数来处理某一个变量 在mysql中也有很多函数 打开常用函数.txt 这里面罗列着一些mysql常用函数 要求: 这些函数要统览一遍,准确的参数可以不必记得,但是要知道有这么一些东西,知道用的时候到哪去找 这几类分别举个例子,讲解几个个函数 1)floor(x) 返回小于x的最大整数值 goods表的shop_price取出整数

select goods_id,goods_name,floor(shop_price) from goods;

2-rand()返回0到1内的随机值,可以通过提供一个参数(种子)使rand()随机数生成器生成一个指定的值 生成随机5-10的值

select rand();
select floor(5+rand()*5);

 

3-left(列名,位数)返回字符串str中最左边的x个字符

create table t27 (
email char(20)
);

插入 email 数据

insert into t27 values ('abc@163.com'),('89520475@qq.com');

我们想要分别统计各邮箱种类的使用率 需要将email字段截取成两部分

select left(email,3) from t27; 取出前3位

我们需要获取@的位置,将@前面的截取出来 position(substr in str) 返回子串substr在字符串str中第一次出现的位置

select position('@' in email) from t27;

select left(email,position('@' in email)-1) from t27; 使用这个取出邮箱前面的值

4-now() 返回当前的日期和时间

select now();

 

事务

 

1.了解事务的概念

比如: 银行转帐 张三 -> 转账给李四500元 张三的钱-500 李四的钱+500 两个update操作 这次事务才算完成

那: 张三的钱刚-500,打雷闪电机房断电,李四的钱还没加上 最终这500快哪里去了??? 日常生活中,汇款二字包含两个小动作,1扣张三的钱,2加李四的钱 汇款成功,那扣钱和增加钱都需完成才算汇款成功

事物就是给你一种保证,什么样的保证呢? 在一个业务的下的具体多个小的语句,要么让你都完成,要么让你都不完成,从而保证你数据的一个安全性 否则,张三钱少了,李四没收到钱...俩人决裂了

 

2.事务的特性

隔离性,原子性,一致性,持久性 引擎要选 innodb,因为myisam不支持事务

create table t29 (
id int,
name char(10),
money int
)engine=innodb default charset=utf8;

插入zhangsan和lisi的数据

insert into t29 values (1,'zhangsan',5000),(2,'lisi',5000);

hangsan借给lisi500,给lisi的钱+500

update t29 set money=money+500 where id=2;

在另外一个窗口查看lisi的钱是否多了500

select * from t29;

这个事务还没完成,zhangsan的钱还没减500,lisi不应该看到自己多的500

之前就有这种骗术,一个人在柜台存钱,另一个人在ATM取钱 银行职员经过一系列操作,已经将钱转过去了

存钱的人在最后一步确认签字前,告诉银行职员,自己不存钱了 而取钱的人在收到钱的时候立刻就取出来

这个钱肯定无法扣除掉了,总不能为负数把,即使为负数,反正卡也不要了 在事务没有完成的中间状态,你不应该看到自己账户上多了钱。

 

A窗口启用事务,来试一试,看看是什么样的效果

start transaction;

先查看两个人各有多少钱,再次给lisi+500

select * from t29;
update t29 set money=money+500 where id=2;

B窗口查看lisi的钱

select * from t29;

A窗口应该给zhangsan-500

update t29 set money=money-500 where id=1;

B窗口查看t29

select * from t29;

事务完成,A窗口结束事务

commit;

看不到事务的中间状态,只能看到事务的开始前的状态,和结束后的状态,这个叫做事务的隔离性

 

事物的原子性 commite之后,事务就结束了 如果再想使用事务,需要重新 start transaction; zhangsan继续借给了lisi500

start transaction;
update t29 set money=money+500 where id=2;
update t29 set money=money-500 where id=1;

结果在commit之前,想撤销借出的钱 需要用到:回滚 rollback;

rollback;
select * from t29;

 

B窗口的lisi,无法看到,zhangsan转了500,半道又撤销了,这叫做隔离性 原子性体现在哪里呢?

start transaction;
update t29 set money=money+500 where id=2;
update t29 set money=money+300 where id=2;
update t29 set money=money-600 where id=1;
rollback;

 

我们开启事务之后,不论将数据改的多乱,只要rollback 回滚,数据就全部回到了事务之前,要是一提交,commit,就全部来到了事务之后,半道如果有一个语句 没有执行成功,断电了,只要回滚,数据即可恢复到事务开启之前.这个体现了事务的原子性和隔离性 事务的一致性如何体现? 一致性指的是事务的之前和之后,它的业务逻辑上保持总体的一致 什么意思呢? 看一下代码演示

alter table t29 change money money int unsigned;

张三还剩下4000,如果他要借给lisi5000,能否成功呢?

select * from t29;
start transaction;
update t29 set money=money+5000 where id=2;
update t29 set money=money-5000 where id=1;

zhangsan减钱的时候,他的钱已经不够5000,money字段不能为负数,所以mysql报错了 但是mysql它有几种语句的模式,有时候检测不是很严格,它存在一个语句检测模式问题 我们如果用严格模式,它超出存储范围,不能运行 如果采用的sql_mod,不是很严格,这句话是能够执行的 只不过它会将zhangsan的减5000,给减去4000,返回0 那zhangsan-4000,lisi+5000,平白无故的多了1000 => 合作生财之道 这个问题处在,汇款事务的前后,账面的总的额度变了,事务的一致性,指的就是事务前后的数据应该有保持一致性

 

事务的持久性

指的是,一旦commit之后,是无法回滚 rollback 的 因为:一旦commit,这个事实就已经发生了,就不可能撤销回去了 如: 取钱时,有可能发生,卡里的钱已经扣了,但是人民币没出来 遇到这种情况,不要离开ATM机,然后打银行电话 查看你的交易记录,确实是扣除了钱 不过还有一条,冲证记录 银行将钱又冲证,还给你了 一个事务发生了,是无法撤回的,只能再来一条补偿性的事务 将原来产生的恶果给你补偿回去 一个事情发生了,这就是铁一般的事实,这个流水在这呢,你不能将历史事实给去除 比如你小时候偷过一个西瓜,过80年之后,你之前还是偷过西瓜,这是铁一般的历史事实,你只能再买一个西瓜还回去,但是小时候的偷瓜记录还是在的,这个 就叫持久性

 

然后就是一些安全配置的代码。

检查是否对错误日志进行管理:

show variables like 'log_error'; 

 

检查是否配置二进制日志:

show variables like 'log_bin';

 

检查是否配置通用查询日志安全:

show variables like '%general%';

 

检查是否设置禁止MySQL对本地文件存取:

show variables like 'local_infile'; 

 

检查是否对无关账号进行管理:

use mysql;
SELECT user,host FROM mysql.user WHERE user = '';

 

检查是否对账号运行权限进行管理:

select * from mysql.user\G;
show grants;

 

检查是否配置了单个用户最大连接数:

show variables like '%max_connections%'; //整个服务器
show variables like 'max_user_connections'; //单个用户最大连接数

 

检查是否使用默认端口:

show global variables like 'port';

 

在看到其他的我在继续写吧,未完待续!

文章如无特别注明均为原创!
本文作者: JunMo
转载或复制请带上本文地址 http://mo60.cn/post-2.html
并注明出处 JunMo博客
原文地址《 Mysql学习笔记(更新中)
发布于2019-6-27
收录状态: [百度已收录][360未收录]

分享到:


打赏

评论

游客

看不清楚?点图切换

切换注册

登录

您也可以使用第三方帐号快捷登录

切换登录

注册