MySql 入门笔记整理

MySql基本的十个语句

链接数据库：mysql> -hlocalhost -u用户名 -p；

查看所有库：mysql>show databases;

选择数据库：use 库名；

新建数据库：create database 库名 charset utf8;

新建表：

create table stu(Id  int,Name varchar(20))engine myisam charset utf8;

改表名： rename table oldname to newname;

插入数据：insert into 表名 values(‘字段1’),(’字段2’);

删除表：drop table 表名;

查看表内容：select * from 表名;

清空表数据：truncate 表名;

Truncate与delete 区别：truncate 删除表后再重建一个相同的空表。

Delete是从删除所有的层面操作的。

添加列：alter table table_name column column_name column_type;
查看指定表的建表语句：show create table 表名；
把一张表里的数据批量导入到另一张表：

Insert into 库名.表名select列名From 库名.表名；

建表操作：
Mysql 列类型
整型：tinyint 1个字节，范围：有符号（-128—127）、无符号（0—255）
Samllint 2个字节，范围：（-32768—32767）、（0—65535）
Mediuint 3个字节，范围：（-8388608—8388607）、（0—16777215）
Int 4个字节，范围：（-2147483648到2147483647）、（0—4294967295）
Bigint 8个字节，范围：（很大很大）。
Int参数：（M）：补零宽度 unsigned（无符号） zerofill（与（M）配合，设置0填充）

Int型不设置时默认无符号。加列类型加unsigned表示其为无符号。

浮点型：float(M,D)， M：精度——>代表总位数，D代表标度——>小数位的总位数。

定点型：decimal(M,D)是把整数部分和小数部分开存储的，比float更为精确。如果

是账户这样敏感的字段，建议使用decimal。
字符串类型：char(0

char(0<N<255)    定长字符串。不论数据是否达到N个长度，都在尾部加空格补够N个长度。查找效率会相对边长高一点，同时也可能会造成浪费。Varchar（0<N<65535） 变长字符串。因为在数据开始前必须有开始地址+偏移量所以  Varchar的使用率始终<100%PS：M限制的是字符数，不是字节数。Text：文本型，可以存比较大的字段，搜索速度稍慢。因此如果不是特别大的 字段，建议使用varchar类型。声明text时不用设置默认值。Blob：二进制类型，用来存储图像、音频等二进制信息。意义：防止因为字符集的问题导致信息丢失。Date：日期类型。1000-01-01~~~9999-12-31Time：时间类型。-838:59:59~~838:59:59Datetime：日期时间类型。范围：1000-01-01 00:00:00~~9999-12-31 00:00:00timestamp：时间戳格式。Default CURRENT_TIMESTAMP自动获取当前时间。Year：年类型。1901~~2155

基本的增删改查

添加数据（增）：insert into 表名 values（‘字段’）；

修改数据行： update 表名 set 修改值 where 修改的地方;

删除数据行： delete from 表名 where 删除条件;

清空表：delete from 表名；

查询数据：select * from 表名;
列的增改删

增加列：alter table 表名 add 列名 列类型 列参数；

增加列在指定位置：alter table 表名 add 列名 列类型 列参数 after 指定列名；

增加列在首位：alter table table 表名 add 列名 列类型 列参数 first；

删除列：alter table 表名drop 列名；

修改列：alter table 表名 modify 列名 列类型 列参数；

修改列名及列类型：

alter table 表名 change 旧列名 新列名 新类型 新参数；Mysql查询

☞ where （条件查询）

Select * from 表名  where 表达式

☞ Group by与统计函数 分组查询

Mac 最大值    min 最小值   sum 求总和   avg求平均   count求总行数

☞ Having（筛选）

对结果集进行筛选

☞ Ordey by （排序）

当结果集出来之后就可以进行排序，即:where → group by → having → order by

Order by 结果集中的列名称 desc/asc

Order by 结果集中的列1 desc/asc,列2 desc/asc; (多字段排序)

☞ limit（限制条目）在语句的最后，起到限制的作用

Limit offset,n

Offset : 偏移量，即跳过几行 （如果不写相当于 limit 0,n）
N：取出条

理解查询的模型

列就是变量，在每一行上，列的值都在变化。

where条件是表达式。哪一行能让exp表达式为真哪一行就能能被取出来。

查询结果集在结构上可以当成表看。
子查询

Where型子查询：把内层查询的结果作为外层查询的比较条件

如果where 列=（内层sql），则内层sql返回的必是单行单列，单个值。

如果where 列 in(内层sql),则内层sql只返回单列，可以多行。

From型子查询： 把sql的查询结果当临时表用，查询的结果再次查询。

查询每个栏目下ID号最大的一条商品

mysql> select goods_id,cat_id,goods_name from (select goods_id,cat_id,goods_namefrom goods order by cat_id asc, goods_id desc) as tmp group by cat_id;

（mysql特性：group一张表时如果出现分组列和统计函数列以外的列，则会把这个列上第一次遇到的数据当做默认呈现。但这不符合SQL标准。）

Exists型子查询：是指把外层SQL的查询结果，拿到内层SQL去测试，如果内层SQL成立，则该行取出。
左链接语法

假设A表在左边不动，B表在A表的右边滑动

A表与B表通过一个关系来少选B表的行

左链接语法：

A left join B on 条件 条件为真，则B表对应的行取出

【A left join B on 条件】这一块形成的也是一个结果集，可以看成一张表 假设说是C表。既然如此，可以对C表做查询

Select goods_id,cat_id,goods_name,cat_name from ( goods left join category on goods.cat_id = category.cat_id);

左链接生成报价单（对两张表进行查询）

mysql> Select goods_id,category.cat_id,goods_name,cat_name from ( goods left join category on goods.cat_id = category.cat_id);

右链接语法：

A  right  join  B  on 条件    条件为真，则B表对应的行取出

内连接语法：

A  inner  join  B  on 条件   条件维珍妮，则B表对应的行取出

左链接 右链接 内连接的区别

左链接：以左边的表为基准，对两张表一一对比，取出左表符合条件的行。

右链接：以右边的表为基准，对两张表一一对比，取出右表符合条件的行。

内链接：以内容为基准，对两张表一一对比，取出符合条件的行。

求两张表的并集。

Select * from A union all select * from B;
all是所有的元素全部放在一起，没有all 的话会过滤掉一些重复的元素。

两次左链接例子：

mysql> select t1.tname as hname,mres,t2.tname as gname,matime from((m left join t as t1 on hid=t1.tid )  left join t as t2 on gid=t2.tid) where matime between'2006-06-01' and '2006-07-01' order by mid asc;

合并结果集（两张的表的并集）

union 合并2条语句或多条语句的结果集

语法： sql 1 union sql 2 (默认去重，不想去重加 all)

规范： 两个结果集必须列名一致，最终结果集列名以第一条语句为准。
Mysql数学函数与字符串函数

☞数学函数

  abs(x)   返回x的绝对值  bin(x)   返回一个2进制表示  hex(x)   返回一个16进制表示  floor(x)  返回不大于X的最大整数值   ceiling(x) 返回不小于N的最小整数值  rand()    返回一个0—1之间的随机值

☞字符串函数

   group_concat( )    拼接字符串   ascii()           计算字符串的ASCII码   length( )         计算字节长度      PS：GBK一个中文字符占2个字节，UTF8一个中文字符占3个字节   char_length()    计算字符数   reverse()        反转字符串   left(str,x)        返回字符串中最左边的x个字符   position(str1 in str2)  返回子字符串1在字符串2中首次出现的位置   dayofweek(‘0000-00-00’)  指定日期是一周中的第几天   week(‘’)         查询指定日期是那一年的第几周   curdate()         返回当前日期   md5()           字符串加密         PS：良好的加密：1、不可逆2、碰撞性低

☞流程控制函数

case 分支控制

     语法： case 值 when 条件1 then 返回值1 when 条件2 then 返回值2 end;

if 判断

 语法： if(条件,返回值1，返回值2)；（类似PHP中三目运算）ifnull  判断值是否为空     语法： `ifnull( 值 ，返回值1)`nullif(expr1,expr2) 如果expr1 = expr2 成立，那么返回值为NULL，否则返回值为 expr1     语法：nullif(expr1,expr2);

系统调试函数

user() 返回用户及所在主机

    database()  返回正在操作的数据库    version()    返回数据库版本

mysql函数与PHP函数都能实现某个功能时，如何取舍?

 1、mysql的函数肯定影响查询效率，应该在建表的时候通过合理的表结构减少函数的使用。 2、如果确实要使用函数，比如时间的格式化，优先放在业务逻辑层去处理（PHP）。 3、如果在查询某列是使用函数判断，该列将不能再使用索引。

视图及表的关系

视图view

 概念：视图是表的映射，是通过某种运算得到表的一个投影。

作用：

   1、简化查询过程。         进行复杂的统计时，可以用视图生成一个中间结果，再查询视图。   2、更精细的权限控制。          两个站点共享用户列表，又不想开放用户密码字段，可以用视图来限制。   3、数据多分表时可以用到。        需要在多张表查询数据时，先把这些表合并成一个虚拟视图再查询以提高效     率。

创建视图的语法：

   create view 视图名 as  select语句；

删除视图的语法：

   drop view 视图名;

表的数据在变化时对视图的影响。

  视图的变化会根据表的数据变化而变化。

algorithm

   merge       合并查询语句   temptable    临时表   undefined    未定义由系统判断

用一些简单的条件判断语句建立视图时效率反而较低，这个时候不需要建立一张临时表，可以指定algorithm为merge ，即只是简单的把两条查询语句合并就可以了。

字符的编码问题

ASCII码

在设计计算机时，只需表示127个字符。于是设计了ASCII码用1个字节的256中组合来表示这127个字符。

GB2312

    后来需要显示汉字，而汉字常用字符数千个。ASCII码字符集的容量不够，产生GB2312字符集用2字节（兼容ASCII码后还有10000+组合） 来表示中文，可以表示常用汉字在内的数千个字符。

GBK

  GB2312字符集无法容纳所有的汉字字符，于是有了GBK字符集。GBK字符集对第2个字节进行充分利用就   有了20000+组合，容量再一次扩充。

UNICODE

 随着计算机科学技术在世界范围内的普及，现在计算机需要显示世界各国的字符。于是有了UNICODE超大字符集， UNICODE采用6个字节，理论上有了数十亿的变化，来对世界各地的字符进行编号。但有些时候会产生大量的没  有必要的数据，于是采用UTF-8编码方式来简化UNICODE的编号。

MYSQL字符集参数

set character_set_client=GBK: 客户端请求参数

set character_set_connection=UTF8: 连接器请求参数

set character_set_results=GBK: 服务器返回蚕参数

以上三句话可以简写成set names gbk;
utf8的bom问题
存储引擎与事务介绍

引擎

     MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、  索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，  从而改善你的应用的整体功能。

事务

mysql中的事务是由一步或几步数据库操作序列组成的逻辑执行单元这序列操作要么全部执行，要么放弃执行。事务具有四种特性：原子性、一致性、隔离性和持续性。这四个也行也简称为ACID特性。

开启一个事务：start transaction

提交事务处理结果：commit

事务回滚：rollback