mysql笔记

<mysql基础>

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

 

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

 

Where 子查询

把内层查询结果作为外层查询得比较条件

 

From 子查询

把内层查询结果当作临时表，供外层SQL再次查询

 

Exists 子查询

把外层得查询结果拿到内层，看内层得查询是否成立

 

Selectname,avg(score) from stu where name in (select name form (select name,count(*)as gk from stu where score < 60 group by name having gk >= 2) as tmp)group by name;

 

查 select

Where  表达式

表达式在哪一行成立，哪一行就取出来

=,!=,<>,>,<,>=,<=

In,between and

Or,and,not

 

Group by

分组，一般和统计函数配合使用

Max,min,avg,sum,count

 

Having 表达式

数据在表中，表在硬盘或内存以文件形式存在

Where就是针对表文件发挥作用

查询出得结果 也可以看成一张表，其文件一般临时存在缓冲区

Having就是针对查询得结果发挥作用

 

Order by

排序

可以针对字段，升序[asc] 降序[desc] 排列

有可能一个字段排不出结果，可以选用其他字段继续排序

Order by 字段1[asc/desc], 字段2[asc/desc]…

 

Limit

限制条目

Limit [offset,] N

Offset 偏移量

N 取出条目

Limit 2,3

 

 

Select

Union的用法

合并查询结果

 

连接查询

              左连接

              右连接

              内连接

 

Union 联合

作用：把2次或多次查询得结果合并起来

要求：两次查询得列数一致

推荐：查询得每一列，相对应得列类型也一样

可以来自多张表

多次SQL语句取出得列名可以不一致，这时以第一个SQL的列名为准

如果不同语句中取出得行，每个列的值都相同，那么相同的行会合并（去重复）

如果不去重复可以加all    union all

如果子句中有order by limit  要加括号，  推荐放到所有子句之后，即对所有合并的结果进行排序

子句中 order by配合limit使用才有意义

如果子句中用了 order by ,limit  那么子句要用（）包起来

 

 

一张表，就是一个集合，一行数据是集合的一个元素

表内部有一个rowid

M*N个

 

左连接

Select 列 ，1列，2列 N from

Table a left jointable b on table a 列 = table b（两个表关系） （此处表连接成一张大表，完全当成普通表来看）

Where group having……照常写

以左表为准，去右表找匹配数据，找不到匹配用NULL补齐左表，有多行匹配，多行取出

 

 

右连接 right

A 站在B的左边   == B站在A的右边

A left join B == Bright join A

左右连接可以相互转化  推荐把右连接转换为左连接 并推荐使用左连接代替右连接

内连接 inner

查询左右表都有的数据，即：不要左/右中 NULL的部分

内连接是左右连接的交集

目前MYSQL不支持外连接，但是可以用UNION来达到目的

 

 

Select t1.tname ashname, mres,t2.tname as gname,matime

From m left join tas t1 on m.hid = t1.hid

Left join t as t2on m.gid = t2.tid

Where matimebetween ‘2006-06-01’ and ‘2006-07-01’;

 

建表语句

Create table 表名 （

列名称列类型[列属性][默认值]，     （列声明）  unsigned not null default 默认值 

）charset = utf8/gbk;                             primary keyauto_increment

 

Update 表名 set 列名 = *****

 

增加列

Alter table 表名 add 列声明

增加的列默认在表最后一列

可以用after 来声明新增的列在哪一些后面

Alter table 表名 add 列声明 after 列名

如果新增的放在最前面

Alter table 表名 add 列声明 first

 

列修改

Alter table 表名 change 被改列名 新列声明

 

删除列

Alter table 表名 drop 列名

 

视图 view

 

如果某个查询结果出现的非常频繁，也就是拿这个结果当作进行子查询出现的非常频繁

 

视图的定义

视图是由查询结果形成的一张虚拟表

没有真实的数据存在，只是与表的一种查询产生的关系

 

视图的创建语法

Create[algorithm=merge|temptable|undefined] view 视图名 as select语句

视图的删除

Drop view 视图名

为什么要视图：

1.        可以简化查询

2.        可以进行权限控制

把表的权限封锁 但是开放相应的视图权限，视图里只开放部分数据

3. 大数据分表时可以用到

比如，表的行数超过200万行，就会变慢

可以把一张表的数据拆成4张表来存放

 

视图修改

Alter view 视图名as *****

 

视图与表的关系

视图是表的数据的查询结果，自然表的数据改变了，影响视图的结果

 

视图改变了呢？

0.视图增删改也可以影响表 

1但是.视图并不总是能增删改的

视图的数据与表的数据一一对应时，可以修改

对于视图insert还应注意

视图必须包含所以表中没有默认值的列

 

视图的algorithm

Akgortithm = merge/temptable/undefined

Merge 当引用视图时，引用视图的语句与定义视图的语句合并

Temptable 当引用视图时，根据视图的创建语句建立一个临时表

Undefined 未定义，自动，让系统帮选

 

Merge意味视图只是一个规则  语句规则  当查询视图时，

把查询视图的语句（比如 where那些）与创建时的语句where子语句等合并，

分析形成一条select语句

.

而temptable是根据创建语句瞬间创建一张临时表

然后查询视图的语句从该临时表查数据

 

 

字符集与校对集

Mysql的字符集设置非常灵活

可以设置服务器默认字符集

数据库默认字符集

表默认字符集

列字符集

如果某一个级别没有指定字符集，则继承上一级

 

以表声明UTF8为例

存储的数据在表中，最终是UTF8  

1告诉服务器，我给你发送的数据是什么编码 character_set_client 

2.告诉转换器转换成什么编码　character_set_connect

3查询结果用什么编码 character_set_results

如果以上3者都为字符集N，则可以简写为  setnames N

 

推论  什么时候会乱码

1 client 声明与事实不符

2results 与客户端页面不符

 

排序必须按某种规则   这种规则就是校对集

 

Charset utf8 collate utf8_bin

 

 

校对集指字符集的排序规则

一种字符集可以有一个或多个排序规则

声明的校对集必须是字符集合法的校对集

 

 

 

触发器 trigger,枪击，扳机，引线

作用 监视某种情况并触发某种操作

 

可以监视某表的变化  当发生某种变化时，触发某个操作

触发器能监视三种变化   增删改

触发 增删改

监视地点  监视事件 触发事件 触发时间

创建触发器的语法

Create trigger triggerName

After/before insert/update/delete on 表名

For each row

Begin

Sql语句；  (一句或多句)  insert/update/delete 范围内（update g set num =num – new.much where id = new.gid;）

End;

 

如何在触发器引用行的值

对于insert 而言  新增的行 用new来表示

行中的每一列的值  用new列名来表示

删除触发器的语法

Drop trigger 触发器名

 

Truncate 表名  清空表

 

对于delete而言  原本有一行

引用被删除的一行用old来表示 ，old.列名引用被删除行中的值

 

对于update

被修改的行

修改前的数据  用old来表示  old列名引用被修改之前行中的值

修改后的数据  用new 来表示  new.列名引用被修改之后行中的值

 

 

触发器里before 和 after 区别

After是先完成数据的增删改再触发

触发中的语句晚于增删改  无法影响前面的增删改动作

Before是先完成触发再增删改

触发的语句先于监视的增删改发生  我们有机会判断 修改即将发生的操作

 

典型案例

对于所下订单进行判断 如果订单的数量>5就认为是恶意订单

强制把所订的商品数量修改成5

 

Create trigger tgs 

Before insert on o

For each row

Begin

If new.much>5 then

Set new.much = 5;

End if ;

Update g set num = num – new.much where id =new.gid;

End$

 

 

删触发器  droptrigger triggername;

 

查看触发器  showtriggers;

 

存储引擎

表里的数据存储在硬盘上 具体如何存储？

存储方式也有很多种

 

对于用户来说  同样一张表的数据  无论用什么引擎来存储 用户看到的数据是一样的 对服务器来说有区别

 

常用的表的引擎

Myisam  innoDB

批量插入的速度  高    低

事务安全             支持

全文索引       支持  支持

锁机制         表锁  锁行

 

通俗的说事务  指一组操作 要么都成功执行  要么都不执行

在所有的操作没有执行完毕之前  其他的会话不能够看到中间的改变的过—

 

索引与优化

好处  加快了查询速度 select

坏处  降低了增删改的速度   update/delete/insert

增大了表的文件大小（索引文件甚至可能比数据文件还大）

 

大数据量  先去掉索引 再导入   最后统一加索引

 

不要过度索引

索引列条件（尽量索引查询最频繁的条件列  where后面最频繁的条件）

尽量索引散列值（越不重复  碰撞性越低）  过于集中的值不要索引 例如给男女加索引

 

普通索引（index） 仅仅加快查询速度

主键索引(primary key)  行上的值不能重复

唯一索引(unique) 不能重复

主键必唯一   但是唯一索引不一定是主键

 一张表上只能有一个主键 但是可以有多个唯一索引

全文索引(fulltext  index)

 

 

查看一张表上的所有索引 

Show index form 表名

 

建立索引

Alter table 表名 add index/unique/fulltex/primary key 索引名【可选】（列名）  【默认索引名为列名】

主键不要加索引名 因为主键只有一个

 

删除索引

Alter table 表名 drop index 索引名

删除主键 alter table 表名 drop primary key

 

索引：是针对数据所建立的目录

作用  可以加快查询速度

负面影响 降低了增删改的速度

 

全文索引：在myslq默认的情况下，对中文意义不大

Match(全文索引名) against （’keyword’）;

关于全文索引的停止词

全文索引不针对非常频繁的词做索引

 

 

存储过程：

概念类似于函数 就是把一段代码封装起来

当要执行这一段代码的时候  可以通过调用该存储过程来实现

在封装的语句体里   可以用if/else,case,while等控制结构

可以进行sql编程