mysql 简单介绍

 

mysql 不允许修改数据库名，一些客户端可以变通的方式来修改数据库名称

mysql 1064错误，语法错误

更改表名：
 rename table jian to song;
 
删除表
drop tablde tableName;

DROP TABLE IF EXISTS table_name;

set names gbk; 告诉服务器客户端是什么字符集

建表：
create table song(
id int,
name varchar(100)
) charset [=] gbk;


同时插入多行：
INSERT INTO USER(`name`, `passwd`)
VALUES
    ('a', 'aa'),
    ('b', 'bb'),
    ('c', 'cc');


    范围：补码

//添加字段
alter table table_name add field_name int unsigned

zerofile  默认就是unsigned

null比较，和计算起来都非常不方面， 都无法和他比较大小  只可以用 ISNULL, is not null 比较

select not ISNULL(NULL)
select null is not null;

推荐设置默认值
not null default xxx comment abc;

浮点型 float（M,D） M代表总位数（不包括小数点）， D代表小数位数   注意这里的MD会影响小数的范围，这点这整形的不同 

char（0—255）个字符  利用率<=100%eee
varchar（0-65535）个字节  转换成utf8后大概两万个汉字  记录存了多少个字符需要占用（1-2个字节）所以利用率<100%;
 
 char(M)    M可容纳的字符宽度
 varchar(M) M可容纳的字符宽度
这两个都是记录的字符个数，如果想求占用了多少字节，只能是根据存的东西而定 

char型的如果不够指定长度mysql内部尾部用空格补齐，取出来的时候在踢除
会尾部空格丢失情况

char 比 varchar要快一些

用空间换时间


1.范围
2.利用率
3.空格的处理
4.速度

text不支持全文索引，不支持默认值           10

mysql能够自动识别错误的日期

time还可以表示时间差，所以可以为负值

enum  不符合数据库设计理念（不可分割）  也不节省字节

浮点：直接影响存储范围 float(5,2);  不包括小数点，正负号

银行四舍五入法：
五入不入   1 3 7 9  不入
            2 4 6 8  入
变成无符号的时候范围并不会翻倍

浮点数值
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2依赖于M和D的值

浮点、定点数的区别：

定点数超过设置的范围会有warning(最后会四舍五入存储)，浮点则会四舍五入
定点数在mysql中以字符串存储，精度比定点会高
字段一定长度下，浮点数可以表示更大的范围，但精度低

建议用定点数

所有DECIMAL列的基本计算(+，-，*，/)用65位精度完成

create  table 表名(
列名 列类型 [列属性] [默认值]
`author_id`  int(11) UNSIGNED ZEROFILL NOT NULL ,
) engin 引擎名  charset 字符集


create table aa(
 id1  int(11) UNSIGNED ZEROFILL NOT NULL,        //not null必须放在 unsigned 和zerofill后面，而这两个本身是没有顺序的
 id2 int(10)   unsigned  ZEROFILL  not null
);

between  允许等于俩个边界值

select max(id),name,age from user;  //name和age没有什么意义   记录下的是第一个遇到的

where  表达式
列明 当成变量来看

//查询每个栏目下面积压的货款
                sum是针对每个分组的所有的记录
select cat_id, sum(shop_price*goods_nums) from goods group by cat_id;

group by 只有与 sum,max ,min,count聚合函数配合起来才有意义

where 中加的字段必须是表中存在的（既文件中存在的），where发挥作用的时间在结果之前，所以不用用于结果的字段  对原始列起作用

所以：select a,b,a-b as alias where alias <100;  （错误的写法）

正确的写法：
1、select a,b,a-b as alias where a-b <100;  计算两次
2、select a,b,a-b as alias having alias<100;

查询两门及两门以上不及格的同学的平均分
tip：先求出所有人的平均分，在筛选条件

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

注意这里的sum不可以替换成count，count的统计记录的行数里面的表达式无论是正负还是 0 都会被统计到

查询结果可以看做一个表

having 筛选  对结果起作用  （先where后having）

在没有排序的情况下按硬盘上的物理文件顺序来存取

顺序：where->group->having->order by->limit

好的理解模型：
    1.where 把表达式放到行中看是否为真
    2.列 看做变量，变量可以进行运算
    3.取出的结果可以看做一张临时表

**聚合函数和其他字段共同取出时，其他字段以第一次碰到的数据为准    

子查询查询出来的结果可以当做一张表来看，必须加别名
 create table card2 like card;

where  型子查询：把内层的查询的结果作为外层的查询条件
select * from card where id = (select id from card limit 1);

from    型子查询： 把内层的查询结果当成临时表，供外层sql再次查询
select * from (select * from card) as mycard ;

exists    型子查询    把外层的查询结果拿到内层，看内层的查询结果是否正确

union 联合查询结果
要求：两次查询结果列数一致
如果列类型不一致会转换成  blob类型
推荐：查询的每一列的列类型也一致
列名不一致的时候以第一列为主
如果字句中有order by  ,limit 必须加() 推荐放到字句之后，即对最终合并后的结果进行排序

(select * from classes where id in(2,3) order by id desc limit
ll (select * from classes where id in(4,5) order by id desc limit 2)；


如果字句中order by不配合limit,order by 将失效  被语法分析器优化时会被过滤掉（where = 1）;


集合：
表-----集合
行---集合的元素

集合相乘：笛卡尔积 其实就是两个集合的完全组合  --->select * from ta, tb;


理论上讲不存在两个相同的行，但是表中可以存在相同的行，因为表内部有一个rowid

a left join b ==== b right join a;

推荐用左连接代替右连接

左连接：以左表为准，去右表匹配数据，找不到用null补齐

内连接是左右连接的交集

mysql能否查出左右连接的并集呢?
目前不能，mysql不支持外连接，可以用 union（左链接 union 右连接） 来实现    

//建表
create table kang(
id int unsigned primary key auto_increment comment '主键',
name varchar(100) not null default '' comment '姓名'
) engine=myisam charset=utf8;

//添加字段
alter table kang add height tinyint unsigned not null default '123' comment '身高'; 

//删除字段
alter table kang drop height after id;  #默认在最后增加一列  [after] [first]

//修改字段
alter table kang change [height 要修改的列名] [height int unsigned default 456 列声明];


tee命令：
命令行中的结果保存到外部文件中
notee/ \t 停止记录

试图：由查询结果形成的一张虚拟表，可以简化查询

//创建视图
create [ALGORITHM= temptable] view view_name as [select 语句];

//删除视图
drop view view_name;

//视图的修改
alter view view_name as [select 语句];

视图可以修改并影响表，但并不是所有的视图都可以增删改
视图中的数据可以和表中的数据一一对应时可以修改
insert 还要注意必须包含所有没有默认值的列


视图的 ALGORITHM  算法
merge:只记录一条sql，当查询时把这个sql和以前的sql合并在在原始表中进行查询
temptable 形成一个临时表，再在这个临时表中进行查询
undefined：

*******************mysql 字符集 **********************
mysql 列，表，数据库，服务器都可以指定字符集，如果哪级没有指定则按他的上级

set names N;

等价于下面三句：

 客户端字符集：character_set_client
 客户端高速中转器转成什么编码：character_set_connection
 查询结果用什么编码：character_set_results
 
set character_set_client=gbk;
set character_set_connection=utf8;
set character_set_results=gbk;
 
latin1 比较特殊 ， 当数据库为latin1（iso-8859-1）时，必须 set names latin1
其他的情况 set names的编码应该和文件的编码和想要的编码保持一致


 校对集：utf8_general_ci不区分大小写
 校对集是指对字符集的排序规则
 一个字符集可以有一个或多个排序规则
 
 声明的校对集必须是字符集合法的校对集


触发器的监视范围：增，删，改
触发范围：增，删，改

触发器语法：
delimiter $
create trigger triggerName
after/before insert/update/delete on 表名
for each row(mysql默认，只可以是行触发器)
begin
# sql todo;
end
delimiter ;

#删除触发器
drop trigger triggerName

insert 触发器中用new.列名来引用
 
//示例
delimiter $$ 
create tgrigger tg1
for each row
begin
update g set num = num - new.much where id = new.gid;
end

delete 触发器中用old.列名来引用

对update来说 修改前的数据用old.列明，修改后的数据用new.列明，

before /after区别：

操作之前：相当于饭前检查你有没有足够的钱

before sql语句发过来了，对语句做一个审查，此时语句并没有对硬盘上的数据进行修改
after 是先完成数据的增删改在触发
触发语句晚于增删改，无法影响前面的增删改产生影响

before是先完成触发，在增删改
触发的语句先于监视的增删改，我们有审核，判断，修改即将要发生的操作

下订单前进行判断，强制把所下订单数量<=5(最多只可以下五个订单)

查看触发器：show triggers;

***************************
  Trigger: tg1
    Event: INSERT
    Table: a
Statement: BEGIN
update g set num = num -3 w
END
   Timing: AFTER
  Created: NULL
 sql_mode:
 Definer: root@localhost

事务：
原子性：不可分割
隔离性：中间的过程不可以被看到
一致性：总额一致 
持久性：不可以撤销

#查看mysql的模式
show variables like "%mod%" ;

#切换到严格模式
set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

 
set autocommit = off;#禁止自动提交 查看 show variables like "%commit%";

start transaction;#开启事务

当一个事务 commit/rollback 时这个事务就结束了

注意：有一些语句会隐式的提交事务 参见mysql手册

事务的原理：
    sql 先作用于事务日志 ，然后一起在作用于表

倒大量数据：先去掉索引，统一加索引的速度是非常快的

#执行一条语句然后退出
mysql -uroot -proot dealer  -e"select * from dealer_area where id=54";

备份和回复：
增加备份和整体备份

自带工具：mysqldump
导出表：
mysqldump -u root -p 123456 库名 表一 表二…… >地址 
C:\Users\root>mysqldump -uroot -p ci >ci.sql（ci 库下的所有的表）

不写表名会导出库下的所有的表

导出库：
mysqldump -u root -p 123456 -B 库名 库一 库二…… >地址   [-A] 导出下面所有的库

#只导出表结构加参数 -d 即可
mysqldump -uroot -p -B -d ci >ci.sql #库以及库下的所有的表
mysqldump -uroot -p -d ci >ci.sql     #库下的所有的表
mysqldump -uroot -p -d ci kang >ci.sql# ci 库下的 kang 表

#只导出数据 -t 参数
mysqldump -uroot -p -t db_house t_region -w "fid>1">/home/v_jksong/dump.sql

#导出指定的查询结果
mysqldump -uroot -p db_house t_region -w "true  order by fid desc limit 1">/home/v_jksong/dump.sql #没有where条件时记得添加 true 拼凑

mysqldump -uroot -p db_house t_region -w "fid>1">/home/v_jksong/dump.sql

#一行行的导出 insert  
--skip-extended-insert

恢复：
source d:\\a.sql;

mysql -uroot -p < d:\\(/)song.sql;(库级)
mysql -uroot -p 库名 < d:\\(/)song.sql;(表级)


二叉树：log2N
哈希：一次：

索引：
哈希索引：（理论上是指差一次）  比较浪费空间
散列算法算出对应的地址
根据散列值在分配空间
弊端：1.算出来的值不连续，申请空间的时候必须连续 ，中间有空缺   2.算出来的值相同

索引：加快查询速度，降低增删改速度，同时占用空间

不要过度索引
索引条件 （where后面最频繁的条件比较适宜）


除主键外的数据一般叫做原数据--主键加在元数据上

主键必唯一，唯一不一定为主键，一张表上只有一个主键

查看一张表上的索引：
show index from 表名

索引名称可以省略，省略后默认是字段名称
增加普通索引：
alter table 表名 add index/unique/fulltext [索引名]（列名）
增加主键索引：
alter table 表名 add primary key (列名)  //不要加索引名称，因为主键只有一个

删除非主键索引：
alter table 表名  drop index 索引名

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

全文索引：（对文章的每个词建立索引）
select * from user where  match(name) against ('匹配词');

select match('name') against ('匹配词') from user;  //匹配度

不针对非常频繁的词进行索引 this , is ,you ,my等等

存储过程：
show procedure status;

删除存储过程
drop procedure 名称

查看某个具体的存储过程
show CREATE PROCEDURE song;

 

 

mysql -uroot -proot dealer  -e"select * from dealer_area where id=54";