mysql入门笔记2

灰太狼 2022-07-21 02:15 471阅读 0赞

模糊查询 like

用%代替任意字符

Where goods\_name like ‘诺基亚%’;

明确几个字符就下划线  
下划线统配单个字符，%统配任意

习题

把一堆10几，20几的30几的数变成10,20,30  
也就是去掉个位数

查询出来 select出来

用到了mysql的函数

Floor 返回不大于x的最大整数

先让一堆数除10 ，然后floor 就是一个数了，也就取到了那个十位数，再乘10

就变成了10,20,30

也就是说可以这样更新数据~~~

Set的num =那个参与计算的值

把字符串 诺基亚改为 HTC

SUBSTRING

同样去那个mysql的文档去查对应的函数

**第12课 奇怪的NULL**

创建表

mysql> create table tmp(  
\-> id int,  
\-> name varchar(20)  
\-> )charset utf8 engine myisam;

写进去

mysql> insert into tmp  
\-> values  
\-> (1,’lili’),  
\-> (2,NULL);

定义了NULL  
后  
去查询，where name= NULL  
结果没有，，，查询不等于NULL，也是没有

mysql> select \* from tmp;  
\+——+——+  
| id | name |  
\+——+——+  
| 1 | lili |  
| 2 | NULL |  
\+——+——+  
2 rows in set (0.00 sec)

mysql> select \* from tmp where name=NULL;  
Empty set (0.00 sec)

mysql> select \* from tmp where name!=NULL;  
Empty set (0.00 sec)

NULL就是没有的意思，没法去比，也就是后面的where比较语句，不行

有专门查NULL的

Where name is NULL

**第13课，group分组与统计函数**

Max() 最大  
Count() 求行数  
Avg()  
Min()  
Sum() 求总和

以上是统计函数

其中  
Avg是平均值

mysql> select avg(shop\_price) from goods;  
\+—————–+  
| avg(shop\_price) |  
\+—————–+  
| 1232.526774 |  
\+—————–+  
1 row in set (0.00 sec)

取行数也就是有多少个

mysql> select count(shop\_price) from goods  
\+——————-+  
| count(shop\_price) |  
\+——————-+  
| 31 |  
\+——————-+  
1 row in set (0.00 sec)

mysql> select count(\*) from goods;  
\+———-+  
| count(\*) |  
\+———-+  
| 31 |  
\+———-+  
1 row in set (0.00 sec)

mysql> select count(1) from goods;  
\+———-+  
| count(1) |  
\+———-+  
| 31 |  
\+———-+  
1 row in set (0.00 sec)

以上是简单的统计

下面是分组统计

既然是分组统计，那么就有一个按什么统计，比如说是按小标题，那么就要先给这小标题先拍个序，然后在算，所以用group还是有点耗费资源的，  
也有可能利用索引

命令： from user group by 按的啥分组

记得是group by

先按如 cat\_id

mysql> select cat\_id , avg(shop\_price) from goods group by cat\_id;  
\+——–+—————–+  
| cat\_id | avg(shop\_price) |  
\+——–+—————–+  
| 2 | 823.330000 |  
| 3 | 1746.066667 |  
| 4 | 2297.000000 |  
| 5 | 3700.000000 |  
| 8 | 75.333333 |  
| 11 | 31.000000 |  
| 13 | 33.500000 |  
| 14 | 54.000000 |  
| 15 | 70.000000 |  
\+——–+—————–+  
9 rows in set (0.00 sec)

每个栏目下剩的库存量 用的count  
mysql> select cat\_id , count(\*) from goods group by cat\_id;  
\+——–+———-+  
| cat\_id | count(\*) |  
\+——–+———-+  
| 2 | 1 |  
| 3 | 15 |  
| 4 | 3 |  
| 5 | 1 |  
| 8 | 3 |  
| 11 | 2 |  
| 13 | 2 |  
| 14 | 2 |  
| 15 | 2 |  
\+——–+———-+  
9 rows in set (0.00 sec)

就是按了那个分组！！！！！！！！！！！！！

**14课 having筛选**

就是说，如果把用计算的列了出来，后面的条件又是这个的话，就要计算两次，所以可以向语言那样进行变量赋值，把这个计算的赋值给另一个变量~~~

就是

Selecct goods\_name , (market\_price-shop\_price) as sheng from goods having sheng >200;

正常的查询，就是先看后面的条件，先把磁盘中的数据按着条件取出来放在临时的存储中。。。。。

也就是说提出来的数据中的有那些运算的，但是磁盘的真实数据中是没有的。

然后想在这个提出来的临时表的基础上再进行数据的筛选就要用having了~~~

Where是针对磁盘的，having是针对取出来的临时表

就是说先  
Selecct goods\_name , (market\_price-shop\_price) as sheng from goods where 1

把这些都去取出来，然后在对取出来的临时数据表进行操作就用到having了。

Having sheng >200;

和上面的那个直接用having的结果是一样的。

Cout是数的行数 ，score<60，无论什么，一定会有结果，0或1，那么用count的话就会去不管0或1都去数行了。应该用sum！！！！求那个和，然后用group by name 分组，  
用where取出来，在用having在取 sum那个大于2的

mysql> select name,subject,sum(score<60) as gk, avg(score) from result group by  
name ;  
\+——+———+——+————+  
| name | subject | gk | avg(score) |  
\+——+———+——+————+  
| 张三 | 数学 | 2 | 60.0000 |  
| 李四 | 语文 | 2 | 50.0000 |  
| 王五 | 政治 | 1 | 30.0000 |  
\+——+———+——+————+  
3 rows in set (0.00 sec)

mysql> select name,subject,sum(score<60) as gk, avg(score) from result group by  
name  
\-> having gk>=2;  
\+——+———+——+————+  
| name | subject | gk | avg(score) |  
\+——+———+——+————+  
| 张三 | 数学 | 2 | 60.0000 |  
| 李四 | 语文 | 2 | 50.0000 |  
\+——+———+——+————+  
2 rows in set (0.00 sec)

mysql>

**15、order by 排序**

就是order by 后面加排序的条件，以什么排序！  
很简单额  
如  
Select goods\_id goods\_name from goods order by shop\_price;

就是以商品价格的去排序，如果在磁盘上是没有排序的，那么就要在内存中排序

但是吧有正序，倒叙，等

默认是升序 asc

直接后面加desc就变成了降序了

select goods\_id,goods\_name,shop\_price from goods order by shop\_price;

但是可以能会有，就是先按cat\_id 排序，然后发现商品的价格是乱的，那么让价格也是  
排序的。。

第一个字段没有比较完，直接在后面加个逗号加比较的字段。

并且可以第一个比较是升序，第二个是降序也可以。

mysql> select goods\_id,goods\_name,shop\_price from goods order by shop\_price desc  
,shop\_price desc;  
\+———-+——————————+————+  
| goods\_id | goods\_name | shop\_price |  
\+———-+——————————+————+  
| 22 | 多普达touch hd | 5999.00 |  
| 23 | 诺基亚n96 | 3700.00 |  
| 32 | 诺基亚n85 | 3010.00 |  
| 18 | 夏新t5 | 2878.00 |

还可以继续去加

也就是分列排序。

**17课 limit 限制取出条目**

如，取出价格前三高，

选择性的拿出几条，先排序，然后就可以跳过几个还可以直接取

有，两个参数 ，1 偏移量 就是跳过几个

2取几行。

mysql> select goods\_id,goods\_name,shop\_price from goods order by shop\_price limit 0,3;  
\+———-+———————–+————+  
| goods\_id | goods\_name | shop\_price |  
\+———-+———————–+————+  
| 30 | 移动20元充值卡 | 18.00 |  
| 26 | 小灵通/固话20元充值卡 | 19.00 |  
| 5 | 索爱原装m2卡读卡器 | 20.00 |  
\+———-+———————–+————+  
3 rows in set (0.00 sec)

mysql> select goods\_id,goods\_name,shop\_price from goods order by shop\_price asc limit 0,3;  
\+———-+———————–+————+  
| goods\_id | goods\_name | shop\_price |  
\+———-+———————–+————+  
| 30 | 移动20元充值卡 | 18.00 |  
| 26 | 小灵通/固话20元充值卡 | 19.00 |  
| 5 | 索爱原装m2卡读卡器 | 20.00 |  
\+———-+———————–+————+  
3 rows in set (0.00 sec)

mysql> select goods\_id,goods\_name,shop\_price from goods order by shop\_price desc limit 0,3;  
\+———-+—————-+————+  
| goods\_id | goods\_name | shop\_price |  
\+———-+—————-+————+  
| 22 | 多普达touch hd | 5999.00 |  
| 23 | 诺基亚n96 | 3700.00 |  
| 32 | 诺基亚n85 | 3010.00 |  
\+———-+—————-+————+  
3 rows in set (0.00 sec)

记住两个参数，一个偏移量，一个取出条目数

取出最新的

就是先按goods\_id 倒叙，然后0，1 就是直接取出了最新的漏洞e

在oracle中没有  
在分页中发挥作用

**18、子句的查询陷阱**

查询每个栏目下最大最新的产品。Goods\_id 作为最大最新

综合运用

每个栏目下的最新的就是id最大的。

这5种子句是有顺序也就是我们现在学习的顺序。

子查询了就~~~

**19课 where型子查询**

内层的查询结果作为外面查询的条件

就是上面的题，不用排序和limist

假如只取直接就最大的话，不是按cat\_id分的话，

可以 select goods\_id ,goods\_name from goods where =32;

但是不知道最大的是32，所以  
可以  
先查 select max(goods\_id) from goods;  
返回了最大的，然后在就可以了。

那么让返回的这个最大值给那么32的位置

子查询 ：

Select goods\_id ,goods\_name from goods where goods\_id =

(select max(goods\_d) from goods);

mysql> select goods\_id goods\_name from goods where goods\_id = (select max(goods\_id) from goods);  
\+————+  
| goods\_name |  
\+————+  
| 32 |  
\+————+  
1 row in set (0.03 sec)

解决上面的问题，就是先查出每个标题下最大的也就是

Select max(goods\_id) from goods group by cat\_id ;  
返回每个标题下最大的goods\_Id；  
那么在此基础上查询他们的信息就行了但是他们没有规律  
那么用in语句去查

就是

Select goods\_id ,goods\_name where goods\_id in  
(select max(goods\_id) from goods group by cat\_id);

不用group by的话就是整个的最大的goods\_id ，用了group by来分组

就在每个标题下的最大的goods\_id ~~~~

mysql> select goods\_id,goods\_name from goods where goods\_id in  
\-> (select max(goods\_id) from goods group by cat\_id )  
\-> ;  
\+———-+——————————+  
| goods\_id | goods\_name |  
\+———-+——————————+  
| 6 | 胜创kingmax内存卡 |  
| 7 | 诺基亚n85原装立体声耳机hs-82 |  
| 16 | 恒基伟业g101 |  
| 18 | 夏新t5 |  
| 23 | 诺基亚n96 |  
| 26 | 小灵通/固话20元充值卡 |  
| 28 | 联通50元充值卡 |  
| 30 | 移动20元充值卡 |  
| 32 | 诺基亚n85 |  
\+———-+——————————+  
9 rows in set (0.00 sec)

如果不用子查询返回的序号和名字会乱

**20课 from型子查询**

正常的查询后，会返回一个列表，也就是一个表，然后可以  
在用from来对这个表进行查询

就是把第一次的查询的结果是个列表 as tmp 作为一个临时表，然后

From 这个临时表，后面还可以正常的加条件，就是单纯的在临时的表上进行查询

As 就是起名

Select goods\_id,goods\_name from goods where goods\_id<25 ;

Select goods\_id ,goods\_name from ( Select goods\_id,goods\_name from goods where goods\_id<25 ) as tmp order by goods\_id;

**21课 exists 子查询**

查出所有有商品的栏目，

mysql> select \* from category where exists(select \* from goods where goods.cat\_i  
d = category.cat\_id);  
\+——–+——————-+———–+  
| cat\_id | cat\_name | parent\_id |  
\+——–+——————-+———–+  
| 2 | CDMA手机 | 1 |  
| 3 | GSM手机 | 1 |  
| 4 | 3G手机 | 1 |  
| 5 | 双模手机 | 1 |  
| 8 | 耳机 | 6 |  
| 11 | 读卡器和内存卡 | 6 |  
| 13 | 小灵通/固话充值卡 | 12 |  
| 14 | 移动手机充值卡 | 12 |  
| 15 | 联通手机充值卡 | 12 |  
\+——–+——————-+———–+  
9 rows in set (0.00 sec)

两个表之间的的查询，对应的让他们显示都存在的cat\_id 号显示出来。

Esists 存在的意思。

**22课，新手1+N模式查询**

查询价格大于2000元的商品及栏目名称。

一个goods表的话，根本查不出来栏目名称的，但是有栏目的id ，

先查出来商品价格大于2000的，包含栏目id的，然后查出N条数据，由这N条数据  
可以再次去另一个表中进行查询。代码实现。

这样很麻烦，是新手嘛，然后就可以引出连接查询

**23课 内连接查询**

删除表，，，， drop table 表的名字； 就可以删除了

truncate 和 delete 只删除数据不删除表的结构(定义)

速度，一般来说: drop> truncate > delete

查询时两张表连接是 inner join 后面 on 是条件 连接规则

想删除部分数据行用 delete，注意带上where子句. 回滚段要足够大.  
想删除表,当然用 drop  
想保留表而将所有数据删除，如果和事务无关，用truncate即可

[http://www.cnblogs.com/8765h/archive/2011/11/25/2374167.html][http_www.cnblogs.com_8765h_archive_2011_11_25_2374167.html]

SQL truncate 、delete与drop区别  
相同点：  
1.truncate和不带where子句的delete、以及drop都会删除表内的数据。

2.drop、truncate都是DDL语句(数据定义语言),执行后会自动提交。

不同点：  
1. truncate 和 delete 只删除数据不删除表的结构(定义)  
drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index)；依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。

1.  delete 语句是数据库操作语言(dml)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。  
    truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。

3.delete 语句不影响表所占用的 extent，高水线(high watermark)保持原位置不动  
drop 语句将表所占用的空间全部释放。  
truncate 语句缺省情况下见空间释放到 minextents个 extent，除非使用reuse storage；truncate 会将高水线复位(回到最开始)。

4.速度，一般来说: drop> truncate > delete

5.安全性：小心使用 drop 和 truncate，尤其没有备份的时候.否则哭都来不及  
使用上,想删除部分数据行用 delete，注意带上where子句. 回滚段要足够大.  
想删除表,当然用 drop  
想保留表而将所有数据删除，如果和事务无关，用truncate即可。如果和事务有关,或者想触发trigger,还是用delete。  
如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

6.delete是DML语句,不会自动提交。drop/truncate都是DDL语句,执行后会自动提交。

7、TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

8、TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。

9、对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

10、TRUNCATE TABLE 不能用于参与了索引视图的表。  
…………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………….

| hid | bname |  
\+——+——–+  
| A | 屌丝 |  
| B | 杨过 |  
| C | 陈冠希 |  
\+——+——–+  
3 rows in set (0.00 sec)

mysql> select \* from girl;  
\+——+——–+  
| hid | gname |  
\+——+——–+  
| B | 小龙女 |  
| C | 张柏芝 |  
| D | 死宅女 |  
\+——+——–+  
3 rows in set (0.00 sec)

mysql>  
mysql>  
mysql> select boy.hid , boy.bname ,girl.hid ,girl.gname  
\-> from  
\-> boy inner join girl  
\-> on boy.hid = girl.hid  
\-> ;  
\+——+——–+——+——–+  
| hid | bname | hid | gname |  
\+——+——–+——+——–+  
| B | 杨过 | B | 小龙女 |  
| C | 陈冠希 | C | 张柏芝 |  
\+——+——–+——+——–+  
2 rows in set (0.04 sec)

也就是 select 表.什么什么 另一个表.

From 表1 inner join 表2  
On 连接的条件

**24课 左右连接查询**

之前那么查，左面的boy 没有把，因为没有对应的，  
那么现在让他对应NULL

如左连接，就是以左边的为主导

mysql> select boy.hid , boy.bname ,girl.hid ,girl.gname  
\-> from  
\-> boy left join girl  
\-> on boy.hid = girl.hid;  
\+——+——–+——+——–+  
| hid | bname | hid | gname |  
\+——+——–+——+——–+  
| A | 屌丝 | NULL | NULL |  
| B | 杨过 | B | 小龙女 |  
| C | 陈冠希 | C | 张柏芝 |  
\+——+——–+——+——–+  
3 rows in set (0.00 sec)

就是先把左面的表全找出来，然后以左表数据为准，  
查不到的写NULL

右连接

就是把left改为 right

mysql> select boy.hid , boy.bname ,girl.hid ,girl.gname  
\-> from  
\-> boy right join girl  
\-> on boy.hid = girl.hid;  
\+——+——–+——+——–+  
| hid | bname | hid | gname |  
\+——+——–+——+——–+  
| B | 杨过 | B | 小龙女 |  
| C | 陈冠希 | C | 张柏芝 |  
| NULL | NULL | D | 死宅女 |  
\+——+——–+——+——–+  
3 rows in set (0.00 sec)

Inner就是内连接 他们俩的交集  
Left就是左面的加上交集。

Mysql不支持外连接也就是所有的并集。

例如

mysql> select goods\_id,goods\_name,shop\_price,cat\_name  
\-> from  
\-> goods left join category  
\-> on goods.cat\_id = category.cat\_id;

写表.是因为不同的表中有相同的字段，为了区别

mysql> select goods\_id,cat\_name,shop\_price  
\-> from  
\-> goods left join category  
\-> on goods.cat\_id = category.cat\_id  
\-> where goods.cat\_id = 4;  
\+———-+———-+————+  
| goods\_id | cat\_name | shop\_price |  
\+———-+———-+————+  
| 1 | 3G手机 | 1388.00 |  
| 14 | 3G手机 | 2625.00 |  
| 18 | 3G手机 | 2878.00 |  
\+———-+———-+————+  
3 rows in set (0.00 sec)

Select m.\*,t1.tname as htame,t2.tame as gteam  
From  
M inner join t as t1 on m.hid = t1.tid inner join t as t2 on m.gid = t2.tid

还有 输入 \\c后就让前面的语句没用了，有时候输入错误了，回车，输入\\c 就可以结束  
这个语句的书写

mysql> select m.\*,t1.tname as htname ,t2.tname as gtname  
\-> from  
\-> m inner join t as t1 on m.hid = t1.tid inner join t as t2 on m.gid = t  
id;  
\+——+——+——+——+————+———-+———-+  
| mid | hid | gid | mres | matime | htname | gtname |  
\+——+——+——+——+————+———-+———-+  
| 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)

这个T表连了两次，连了两次就要起别名

[http_www.cnblogs.com_8765h_archive_2011_11_25_2374167.html]: http://www.cnblogs.com/8765h/archive/2011/11/25/2374167.html