mysql整理笔记

001 认识并安装MySQL

002 客户端概念

003 基础知识

1 数据库的连接

mysql -h -u -p 
-u 用户名
-p 密码
-h lost主机

例如:
mysql -hlocalhost -uroot -proot;

2 库级知识

1. 显示数据库 show databases;
2. 选择数据库 use dbname;
3. 创建数据库 create database dbanme charset utf8;
4. 删除数据库 drop database dbname;

3 表级操作

1 显示库下面的表

show tables;

2 查看表的结构

desc tableName;

3 查看表的创建过程

show create table tableName;

4 创建表

create table tbName(
	列名称1 列类型 [列参数][not null defalut ''] [comment '注释'],
	...
)engine myisam/innodb charset utf8/gbk [comment='注释'];

例如

create table user(
	id int auto_increment comment 'id',
	name varchar(20) not null default '' comment '姓名',
	age tinyint unsigned not null default 0 comment '年龄',
	index id(id)
)engine=innodb charset=utf8 comment='用户管理';

5 修改表

• 1 修改表: 增加新的一列

alter table tbName add 列名称1 列类型 [列参数] [not null defalut ] # (add 之后的旧列名之后的语法和创建表的声明一样)
在上面最后可以 加一个 after 之前的列, 这样的话,可以指定位置插入.

例如

alter table user add age int unsigned not null default 0 after name;

• 2 修改表: 修改表中的列

alter table tbName change 旧列名 新列名 列类型 [列参数] [not null default ] # (注: 旧列名之后的语法和创建表时的列声明一样)

例如

alter table user change age nianling tinyint unsigned not null default 0;

• 3 修改表: 删除表中的一列

alter table tbName drop 列名称

例如

alter table user drop nianling;

• 4 修改表: 增加主键

alter table tbName add primary key(主键所在的列名);

例如

alter table user add primary key(id);

• 5 修改表: 删除主键

alter table tbName drop primary key;

例如

alter table user drop primary key;

• 6 修改表: 增加索引

alter table tbName add [unique index] 索引名(列名);

例如

alter table user add index age(age);
alter table user add unique height(height); // 仅仅是举例,现实中不会把身高作为unique key.

ps:显示所有索引 select index from tbName

• 7 修改表: 删除索引

alter table tbName drop index 索引名;

例如

alter table test drop index age;
alter table test drop index height;

• 8 清空表数据

truncate tableName;

004 select常用运算符

• 比较运算符

运算符	说明
<	小于
<=	小于或等于
=	等于
in	在某集合内
!= 或 <>	不等于
>=	大于或等于
>	大于
between	在某范围内

• 逻辑运算符

运算符	说明
NOT 或 !	逻辑非
OR 或 ||	逻辑或
AND 或 &&	逻辑与

• like 模糊匹配

1. % 通配任意字符
2. _ 通配单一字符

select goods_id,goods_name from goods where goods_name like “诺基亚%”;

select goods_id,goods_name from goods where goods_name like “诺基亚N__”;

005 insert 操作

insert 所有列
insert 指定列
注意: 列与值, 严格对应
数字和字符串的注意点: 数字不必加单引号,字符串必须加单引号.

语法: inset into 表名(字段1, 字段2, ...) values(字段1的值, 字段2的值, ...),(字段1的值, 字段2的值, ...)

如果第一个括号里面的字段没有写,表示所有字段,则 values里面必须一一对应,每个字段的值都要填写

insert into user(uid,name,age) values(1, ‘lisi’, 23);

insert into user(uid, name) values(2, ‘lucy’);

insert into user(name) values(‘lilei’);

insert into user(name) values(‘wangwu’), (‘zhaoliu’);

insert user values(4, ‘white’, 37);

006 update操作

update 所有行
update 指定行

语法: update 表名 set 列1=新的值,列2=新的值 where expr

update user set age=8 where name=“lilei”;

update user set age=9, name=‘nobody’ where uid = 2;

007 delete操作

删除部分列?
删除所有行
删除指定行

语法: delete from 表名 where expr

注意: delete from 表名. 如果这样的话,会删除表中所有数据,一定注意.

delete from user where uid=4;

008 简单 select

查询所有行所有列
对行的选择
对列的选择

语法: select 列1,列2,...列n from 表名 where expr

select * from user;

select * from user where uid=2;

select * from user where uid>1;

select * from user where uid>1 and uid<3;

select uid,name from user where uid=2;

select name from user where uid=2;

009 理解查询模型

列是变量
变量可以计算
where是表达式,值为真假

select uid,name,age+1 from user where 1;

select goods_id,goods_name,shop_price,market_price,market_price-shop_price cj from goods;

010 查询练习

• 1 主键为32的商品

select goods_id,goods_name,shop_price from goods where goods_id=32;

• 2 不属第3栏目的所有商品

select goods_id,goods_name from goods where cat_id <> 3;

或

select goods_id,goods_name from goods where cat_id != 3;

• 3 本店价格高于3000元的商品

select goods_id,goods_name,shop_price from goods where shop_price > 3000;

• 4 本店价格低于或等于100元的商品

select goods_id,goods_name,shop_price from goods where shop_price <= 100;

• 5 取出第4栏目或第11栏目的商品

select goods_id,goods_name,cat_id from goods where cat_id = 4 || cat_id=11;

或

select goods_id,goods_name,cat_id from goods where cat_id = 4 or cat_id=11;

或

select goods_id,goods_name,cat_id from goods where cat_id in (4,11);

• 6 取出100<=价格<=500的商品

select goods_id,goods_name,shop_price from goods where shop_price >= 100 && shop_price <= 500;

或

select goods_id,goods_name,shop_price from goods where shop_price >= 100 and shop_price <= 500;

或

select goods_id,goods_name,shop_price from goods where shop_price between 100 and 500;

• 7 取出不属于第3栏目且不属于第11栏目的商品(and,或not in分别实现)

select goods_id,goods_name,cat_id from goods where cat_id != 3 and cat_id != 11;

或

select goods_id,goods_name,cat_id from goods where cat_id != 3 && cat_id != 11;

not in 实现

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

• 8 取出价格大于100且小于300,或者大于4000且小于5000的商品

select goods_id,goods_name,shop_price from goods where (shop_price > 100 and shop_price < 300) or (shop_price > 4000 and shop_price < 6000);

• 9 取出第3个栏目下面价格<1000或>3000,并且点击量>5的系列商品

select goods_id,goods_name,cat_id,shop_price from goods where cat_id=3 and (shop_price between 1000 and 3000) and click_count > 5;

• 10 取出第1个栏目下面的商品(注意:1栏目下面没商品,但其子栏目下有

select goods_id,goods_name,cat_id,shop_price from goods where cat_id in (2,3,4,5);

• 11 取出名字以"诺基亚"开头的商品, like 模糊匹配 % 通配单一字符

select goods_id,goods_name from goods where goods_name like “诺基亚%”;

• 12 取出名字为"诺基亚Nxx"的手机, like 模糊匹配 _ 通配单一字符

select goods_id,goods_name from goods where goods_name like “诺基亚N_ _”;

011 查询练习解释

题目一: 有如下表和数组
把num值处于[20,29]之间,改为20
num值处于[30,39]之间的,改为30

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

解答:

update mian set num=20 where num between 20 and 29;

update mian set num=30 where num between 30 and 39;

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

select goods_id,concat(‘HTC’,substring(goods_name,4)) from goods where goods_name like ‘诺基亚%’;

update goods set goods_name = concat(‘HTC’,substring(goods_name,4)) where goods_name like ‘诺基亚%’;

012 奇怪的 NULL

select * from tmp where name is null;

013 group 分组统计

• max() 求最大
• count() 求数量
• avg() 求平均数
• min() 求最小
• sum() 求总和

select avg(shop_price) from goods;

select count(*) from goods;

select sum(shop_price*goods_number) from goods;

• 根据种类,查看 一个种类的评价价格

select cat_id,avg(shop_price) price from goods group by cat_id;

• 根据种类查询每个种类下的数量

select cat_id,count(*) from goods group by cat_id;

• 查询每一类下最贵的那个产品的价格

select cat_id,goods_name, max(shop_price) from goods group by cat_id;

014 having 筛选结果集

having 筛选与 where 不同

• 1 查询该店的商品比市场价所节省的价格

select goods_id,goods_name, market_price-shop_price from goods;

015 having 综合案例讲解

select goods_id,goods_name,(market_price-shop_price) as sheng from goods having sheng>200;

注意: 上述是用的 having 而不是 where, 因为 sheng这个是结果集,不是最原始的字段, where 是用不了的.

题目一
有如下表及数据
±-----±--------±------+
| name | subject | score |
±-----±--------±------+
| 张三 | 数学 | 90 |
| 张三 | 语文 | 50 |
| 张三 | 地理 | 40 |
| 李四 | 语文 | 55 |
| 李四 | 政治 | 45 |
| 王五 | 政治 | 30 |
±-----±--------±------+

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

select name,subject,sum(score<60) as gk, avg(score) as pj from result group by name having gk>=2;

016 order by 排序

• 降序 desc
• 升序 asc 默认是升序
• 多列排序 逗号隔开

select goods_id,goods_name,shop_price from goods order by shop_price desc;

select goods_id,cat_id,goods_name,shop_price from goods order by cat_id asc,shop_price desc;

如上: 先按照 cat_id 进行 升序排序, 然后再 cat_id 中,再用 shop_price 进行降序排列.

017 limit 用法

• 接受2个参数,第一个参数是偏移量(从0开始)(可以省略),第二个参数是取多少数据. 例如 limit 0,3 从第0项开始,取3项. 例如 limit 1,3 从第一项开始,取3项.

• 取出价格最高的前三名商品

select goods_id,goods_name,shop_price from goods order by shop_price desc limit 3;

018 字句查询陷阱

之前学习过 where后面可以接 group by, having, order by, limit

• 查询每个栏目下最新的产品(goods_id 最大为最新)

注意

1. 5种字句有严格的顺序, where, group by, having, order by, limit
2. 引出子查询

019 where型子查询

原理 内层查询的结果,作为外层的sql的比较条件

• 查询每个栏目下最新的产品(goods_id 最大为最新)

select goods_id,goods_name from goods where goods_id=(select max(goods_id) from goods);

如18中的例子,用 where 自查询解决

select goods_id,goods_name,cat_id from goods where goods_id in (select max(goods_id) from goods group by cat_id);

020 from 型子查询

原理 将查询的结果集作为新的表,供 sql继续查询

select goods_id,cat_id,goods_name from (select * from goods where 1 order by cat_id asc, goods_id desc) as tmp group by cat_id;

021 exists 型子查询

• 查出所有有商品的栏目

select * from category where exists (select * from goods where goods.cat_id = category.cat_id);

022 新手1+N查询

• 查询价格大于 200元的商品及栏目名称

select goods_id,goods_name,cat_id,shop_price from goods where shop_price > 2000;

023 内联接查询

• 内连接是查询 a,b 两个表共同满足条件的部分.

• 左连接是查询 a,b 两个表中, a的所有数据及b中满足条件的数据.

• 右连接是查询 a,b 两个表中, b的所有数据及a中满足条件的数据.

• 连接出的结果,可以当做一个新表继续连接, 内连接,左连接,右连接都是如此.

语法

select xxx 
from 
table1 inner join table2
on
table1.xx = table2.xx;

select boy.hid,boy.bname,girl.hid,girl.gname 
from 
boy inner join girl
on boy.hid = girl.hid;

024 左右连接查询及练习题

• 左连接是查询 a,b 两个表中, a的所有数据及b中满足条件的数据.
• 左连接是以左边数据为准,查询右边相关数据,查不到的补null.

左连接语法

select xxx 
from 
table1 left join table2
on
table1.xx = table2.xx;

select boy.hid,boy.bname,girl.hid,girl.gname 
from 
boy left join girl
on boy.hid = girl.hid;

右连接语法

select xxx 
from 
table1 right join table2
on
table1.xx = table2.xx;

select boy.hid,boy.bname,girl.hid,girl.gname 
from 
boy right join girl
on boy.hid = girl.hid;

题目一: 取出所有商品的商品名,栏目名,价格

select goods.goods_id, goods.goods_name,cat_name,shop_price
from 
goods left join category
on goods.cat_id = category.cat_id;

025 连接查询面试题

题目

根据给出的表结构按要求写出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

select m.mid, t.tname as hteam, m.mres, t1.tname as gteam, m.matime
from m
inner join t
on m.hid = t.tid
inner join t as t1
on m.gid = t1.tid
where m.matime between '2006-06-01' and '2006-07-01'
order by m.mid asc;

PS: 第一次 inner join 之后, 把结果当初一个新表,继续 inner join 就可以了. 这里注意, 连续连了2次 t 表,这里要用别名!

026 union查询

• union查询就是把 2条或多条 sql的查询结果, 合并成1个结果集.
• union的语句必须满足1个条件: 各语句取出的列数必须相同.
• 列名称未必要一致,列名称会使用第一条sql的列名称为准.

注意: 使用 union时,完全相等的行,将会被合并!
合并是比较耗时的操作.
一般不让union合并,使用 union all 可以避免合并, 速度有一个很好的提升

sql1 返回N行
sql2 返回M行
sql1 union sql2 返回N+M行

场景: 2条语句,各自的 where 条件非常复杂, 可以简化成简单条件, 再 Union.

select goods_id,cat_id,goods_name from goods where cat_id=4
union
select goods_id,cat_id,goods_name from goods where cat_id=2;

select * from a 
union all
select * from b;

union 子句中, 不用写 order by, 因为 sql合并后得到的总的结果,可以order by, 字句 order by 失去意义.

027 union查询面试题

题目, 2个表相同列求和
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 |
±-----±---------+

select id,sum(num)
from
(select * from a 
union all
select * from b) as tmp
group by id;

如上思路 是把union的结果集作为一个新的表,再进行操作

---

028 建表过程

• 理解表与列的关系,掌握基本建表语法

create table 表名(
	列1 列类型 [列属性 默认值],
	列2 列类型 [列属性 默认值],
	...
	列n 列类型 [列属性 默认值]
) 
engine = 存储引擎
charset = 字符集

029 整形列

• tinyint 1个字节
• smallint 2个字节
• mediumint 3个字节
• int 4个字节
• bigint 8个字节
• 1个字节 8位

030 整形列的可选参数属性

• unsigned 无符号,列的值从0开始,不为负
• zerofill 0填充,适合用于学号,编码等固定宽度的数字,可以用0填充至固定宽度. 0001 0002 0123 1322 …
• M 如上,zerofill是0填充,那么填充至多宽,由M来定.

注意: zerofill 属性默认决定列为 unsigned

alter table t2 add unum tinyint unsigned;

alter table t2 add sn tinyint(5) zerofill; 红色标志位是 M的值,表示填充至5位.

031 浮点列与定点列

• float 单精度浮点数,有精度损失

FLOAT(M,D)[UNSIGNED][ZEROFILL]

M 是小数纵位数, 精度,总位数.
D 是小数点后面的位数.

如 float(5,2) 表示总位数是 5位,小数点后面是2位,则前面是3位.

• double 双进度浮点数,有精度损失

和 float 同理,也是M,D参数. 只是范围更大.

• decimal 定点型,更精确

032 字符型列

• char
• varchar
• text
• 枚举型 enum, 单选,是定义好,值就在某几个枚举范围内 ,set 是多选.

类型	宽度	可存字符	实存字符(i<=M)	实占空间	利用率
char	M	M	i	M	i/M <= 100%
varchar	M	M	i	i字符+(1-2)字节	i/(i+1-2)<100%

create table t7(
    gender enum('男','女')
);

033 日期时间整列

日期时间类型
Year 年(1字节) 95/1995, [1901-2155]
在 insert 时,可以简写年的后2位,但是不推荐这样.
[00-69] + 2000
[70-99] + 1900
即: 填2位, 表示 1970-2069

Date 日期, 1998-12-31
范围: 1000/01/01, 9999/12/31

Time 时间 13:56:23
范围: -838:59:59 --> 838:59:59

datetime 时间日期 1998-12-31 13:56:23
范围: 1000/01/01 00:00:00 --> 9999:12:31 23:59:59

create table t8 (
    ya year,
    dt date,
    tm time,
    dttm datetime
);

insert into t8(ya) values(1901);
insert into t8(dt) values('1990-12-23');
insert into t8(tm) values('18:23:45');
insert into t8(dttm) values('1990-12-23 18:23:45');

时间戳 timestamp
是 1970-01-01 00:00:00 到当前的秒数.
一般存注册时间,商品发布时间等,并不是用 datetime 存储,而是用时间戳.
因为 datetime 虽然直观,但计算不便.

034 列的默认值

1. NULL 查询不便
2. NULL 的索引效率不高

所以, 实际使用中,避免列的值为 NULL
如何避免: 声明列 NOT NULL default 默认值. 以此来避免

035 主键与自增

• 主键: primary key, 此列不重复, 是能够区分每一行!
• 自增: auto_increment

注意: 一张表只能有1列为auto_increment , 切此列必须加索引(index/key).

如下2种写法都可以.

create table t11(
    id int primary key auto_increment,
    name char(2)
);

create table t11(
    id int,
    name char(2),
    primary key(id)
);

036 综合建表案例

• 定长与变长分离
• 常用与不常用列 分离

037 列的增删改

• 修改 表名称, rename table xx1 to xx2;

• 增加一列 alter table reg3 add height tinyint unsigned not null default 0;

alter table 表名 add 列名 列类型 列属性… 成功之后默认在表的最后

指定位置 after

alter table reg3 add height tinyint unsigned not null default 0 after weight;

• 删除一列 alter table reg3dropcolumn height;

alter table 表名 drop column 列名

• 修改一列 alter table reg3 change height shengao smallint unsigned not null default 0;

alter table 表名 change 列名 新列名 类型 …

alter table 表名 modify 列名 新属性… modify 不能修改原来列的名称. 可以修改属性.

038 视图 view

• 视图: view 又被称为虚拟表, view 是 sql 的查询结果.

从表中查询出来的结果集, 可以当做一张新表, 来继续查询. 如果这个结果集经常被用到.

create view vgoods as select goods_id,goods_name,(market_price - shop_price) as sheng from goods;

这个 vgoods 就是一个视图,可以当做一个表来使用.

优点

1. 权限控制可以用
   比如某几个列允许用户查询,其他列不允许.
   可以通过视图, 开放其中一列或几列, 起到权限控制的作用.

2. 简化复杂的查询
   例如, 很复杂的嵌套查询, 可以把里面的嵌套做成视图,这样就当做一个新表继续查询,更容易理解.

例题:

查询每个栏目下商品的平均价格,并按平均价格排序,查询平均价前三高的栏目.

create view v3 as select cat_id,avg(shop_price) as pj from goods group by cat_id;

select * from v3 order by pj desc limit 0,3;

3. 视图能不能更新,删除?
   答:
   如果视图的每一行,是与物理表,一一对应的,则可以.
   如果 view 的行是由物理表多行经过计算得到的结果,view 不可以更新的.

039 视图 algorithm

• 视图放哪里了(存储的都是语句)

对于简单查询形成的view, 再对 view 查询时,如 where, order 等等.
可以把建视图语句+查视图语句 合并成 查物理的语句
这种视图的算法叫 merge(合并)

create create algorithm=merge view v7 as select goods_id,goods_name from goods;

也有可能,视图的语句本身比较复杂,很难再和查询视图的语句合并, mysql 可以先执行视图的创建语句,把结果集形成内存中的临时表.
然后再去查询临时表.
这种视图的算法叫 temptable

create create algorithm=temptable view v7 as select goods_id,goods_name from goods;

• 自动判断

create view v7 as select goods_id,goods_name from goods;

040 常用表管理语句

• 查询所有表 show tables 也会显示视图view

• 查询表结构 desc 表名/视图名

• 查看建表过程 show create table 表名

• 查看建视图过程 show create view 视图名

• 删除表 drop table 表名

• 删除视图 drop view 视图名

• 改表名 rename table oldName to newName

• 清空表数据 truncate 表名 (相当于删除表,再重建!)

• 查看数据库下所有表的信息 show table status \G;

• 查看数据库下指定表的信息 show table status where name ='goods' \G;

041 存储引擎的概念

MyISAM 数据以下面格式存储

• xxx.frm 表结构
• xxx.MYD 表数据
• xxx.MYI 表索引

create table goods(
	goods_id int unsigned primary key auto_increment,
	goods_name char(10) not null default '',
	...
) engine= myisam default charset=utf8;

InnoDB 数据是存储在一起

create table goods(
	goods_id int unsigned primary key auto_increment,
	goods_name char(10) not null default '',
	...
) engine= innodb default charset=utf8;

042 字符集与乱码问题

• 字符集 utf8
• 校对集
• 乱码

043 索引概念 index

索引是数据的目录, 能快速定位行数据的位置
索引提高了查询速度, 降低了增删改的速度, 并非加的越多越好.
**一般在查询频率高的列上加, 而且在重复度低的列上加效果更好. **

• key 普通索引,提升查询速度
• unique key 唯一索引,提升查询速度,约束数据
• primary key 主键索引
• fulltext 全文索引, 在中文环境下,是无效的, 要分词+索引,一般用第三方解决方案,如 sphinx.

索引长度: 建索引时, 可以只索引列的前一部分内容,比如,前10个字符
如 key email(email(10)); abcdeft@qq.com 索引 @ 前面的字符.

create table t18(
    id int unsigned primary key auto_increment,
    name char(10) not null default '',
    email char(20) not null default '',
    key name(name),
    unique key email(email(10))
);

• 多列索引, 就是把 2列或多列的值,看成一个整体,然后建索引

• 冗余索引, 就是在某个列上, 可能存在多个索引.
  比如 xm(xing,ming) ming(ming)

044 索引操作语法

• 查看 show index from 表名 \G; \G是竖着显示,易于观看. 或 show create table 表名 也可以看到
• 删除 alter table 表名 drop index 索引名; 或 drop index 索引名 on 表名;
• 添加 alter table 表名 add [index/unique] 索引名(列名)

alter table t19 add index xm(xing, ming);

alter table t19 add unique m(ming);

alter table t19 add index x(xing);

• 添加主键索引 alter table 表名 add primary key(列名称)

alter table t19 add primary key(xing);

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

alter table t19 drop primary key;

045 常用函数

• floor 小数变成整数

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

• left(str, x) 返回字符串str中最左边的x个字符
• position(字串 in 父串) 返回位置

select left(email, position(’@’ in email)-1) from t21;

• date_format(now(), ‘%Y’)

select date_format(now(), ‘%Y/%m/%d’);

046 事务概念

1. 开2个窗口
2. A窗口在线汇款
3. 另一个窗口查询余额,提现

分析: 隔离性,原子性,一致性,持久性

4. 事务引擎
5. 开启事务
6. 2个窗口 体现隔离性
7. 提交
8. 回滚

①开启事务 start transaction
②运行sql;
③提交,同时生效\回滚 commit\rollback

-- 开始事务
start transaction;

update account set money=money+5000 where id=2;
update account set money=money-5000 where id=1;

-- 提交事务,提交之后,就无法回滚了
commit;

-- 回滚撤销, 和提交是同级的.
rollback;