MySQL基础相关

Database

1、关系型数据库与非关系型数据库

① 关系型数据库（mysql）：
由二维表及其实体之间的联系构成的数据组织。关系型数据库使用SQL。
② 非关系型数据库(redis,mogondb)：
not only sql,不仅仅是SQL。非关系型数据库的数据是键值结构，key/value，非关系型数据库不需要SQL。
③ 缓存系统（memcache）
Memcache的数据结构是键值结构，但是它的数据放在内存里，它是一个缓存系统，没有存储数据到磁盘文件的功能，因此它不是非关系型数据库。

2、结构化查询语言（SQL）

① SQL：structured query language 专门用于关系型数据库的一门编程语言
② 编程语言的特征:
变量：它可以使用declare来定义变量
数据类型：数值型，字符串型，日期时间型
操作符：+ - * / % > < != and or
函数：count, sum, avg, max, ceil, floor, round
流程控制：分支结构if，循环结构while

3、常用基本命令

① mysql -hlocalhost -P3306 -uroot -p  客户端连接数据库服务器端命令
② services.msc  Windows的服务管理命令 
③ net start/stop mysql 命令行窗口启动和停止数据库
④ netstat -an 检验本机各端口的网络连接情况

4、找回root密码

① 修改my.ini配置文件
[mysqld]后面增加一行：skip-grant-tables 跳过权限认证

[mysqld]
skip-grant-tables

② 停止mysql服务运行

net stop mysql

③ 重新启动mysql

net start mysql

④ 客户端进入mysql(此时已不需要密码)
⑤ 修改root密码

update user set password=password('123456') where user='root';

⑥ 退出客户端，删除或注释掉my.ini中添加的语句重新启动mysql

[mysqld]
# skip-grant-tables

⑦ 重新登录

mysql -hlocalhost -P3306 -uroot -p123456

5、存储引擎的知识

使用不同的引擎，会影响数据的文件存储方式以及是否支持事务处理功能。
① Myisam：数据操作快速的一种引擎，不支持事务处理功能，支持全文检索。文件保存在数据库名称为目录名的目录中，有3个文件。

.frm 表结构文件 
.MYD 表数据文件
.MYI 表索引文件

② Innodb：功能强大的一种引擎，支持事务处理功能，不支持全文检索。文件保存在两个地方，一个是在数据库名称为目录名的目录中存放表结构文件，它的数据是保存在一个共有的文件中的。

ibdata1 Innodb类型数据表的数据保存文件

6、字符集与校对集

① 字符集4个级别：
服务器级：在安装服务器时指定
数据库级：创建数据库时指定，如果不指定，则使用服务器的默认字符集
数据表级：创建数据表时指定，如果不指定，则使用数据库的默认字符集
字段级：在创建数据表时定义字段属性时指定，如果不指定，则使用数据表的默认字符集
② 编码
汉字在Utf8编码下占用3个字节存储空间
汉字在gbk编码下占用2个字节存储空间

show character set (like 'gbk'); 查看数据库支持的字符集
show collation like 'gbk%'; 查看所有的校对集

③ 校对集有三种方式
Ci：代表不区分大小写 （gbk_chinese_ci）
Cs：代表区分大小写 (gbk_cs)
Bin：代表使用二进制去进行比较 (gbk_bin) 按照ASCII码值进行排序

7、相关编码

在PHP和html中，是utf-8，在数据库中是utf8

show variables like 'character_set_%'; 查看编码参数
set character_set_client=gbk;设置character_set_client参数 
set character_set_results=gbk;设置character_set_result参数 
set names gbk;代替了设置character_set_client(数据库服务器接收sql), character_set_results(数据库服务器返回结果), character_set_connection（数据库服务器转码）

8、相关函数

$link = mysql_connect('localhost:3306','root','123456') 连接并认证
mysql_query('set names utf8') 设置字符集参数
mysql_select_db('itcast') 选择操作当前的数据库
mysql_fetch_assoc() 键值数组
mysql_fetch_row() 下标数组
mysql_fetch_array() 既有键值又有下标，双数组
mysql_fetch_object() 对象方式
mysql_num_rows() 
mysql_error() 返回错误信息
mysql_errno() 返回错误编码
mysql_data_seek() 移动结果集的指针
mysql_free_result() 释放结果集
mysql_close($link) 关闭数据库连接

9、数据类型

Mysql支持三大类数据类型：数值型，字符串型，日期时间型

10、数值型

① 整数型

② 小数浮点型

定义方式：字段名称 类型(M, D) unsigned zerofill
M：代表总有效位数，不包括小数点
D：小数部分的位数
`travel_fee_adult` float(10,2) unsigned NOT NULL  COMMENT 'xx',

③ 小数定点型

定点型又称为严格的浮点数。它不会发生数据不准确的情况。
定义格式：字段名称 decimal(M, D) unsigned zerofill

11、字符串型

char说明：

定义方法：字段名 char(L)
L：代表字符数。它会根据字符集自动去分配空间。一个字母当成一个字符，一个汉字也当成一个字符。我们不用操心使用了何种字符集。
0 < L <= 255

varchar说明：

定义方法：字段名 varchar(L)
L：代表占用的字节数。
实际占用有两种情况
0 < L <= 255，L+1个字节
255 < L < 65535时，占用L+2个字节
额外出来的1或2也要从65535中扣除。
我们是不是还要字符集的问题。Utf8一个汉字要占用3个字节，gbk占用2个字节。
实际上系统还会把第一个字节来占用掉。
Utf8：(65535-3)/3 = 21844
Gbk：(65535-3)/2 = 32766
以上就是两种字符集情况下可以定义的最大长度。

12、日期时间型

timestamp的特殊性：

`pubtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENTAMP
`edittime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'

13、设计范式

① 第一范式，NF1，字段的原子性(字段的不可拆分性)
② 第二范式，NF2,消除部分依赖(有一个字段可以来代表整条记录 增加主键字段)
③ 第三范式，NF3,消除传递依赖(不同实体的数据拆分成不同的数据表)

14、外键

foreign key (classid) references classinfo(id); 创建外键
alter table studinfo add foreign key (classid) references classinfo(id);添加外键
alter table studinfo drop foreign key studinfo_ibfk_1;删除外键
修改外键（关联方式）：严格方式：restrict 级联方式：cascade 设置为null：set null
alter table studinfo add foreign key (classid) references classinfo(id)
on update cascade 
on delete set null;

15、索引

① 索引分类
primary key:主键，一个表只能有一个主键
unique key:唯一索引，一个表可以有多个唯一索引
index:普通索引，一个表可以有多个普通索引

② 建立索引的原x则：

• 在数据较小的数据表上不应该建立索引
• 一般是先建立数据表，再考虑建索引
• 不要在值的内容比较少的字段上建立索引，如性别。在值的内容比较多的字段上建立索引是比较合适的
• 如果where，order by经常使用多个字段来查询，应该建立一个多字段的索引，而不是建立多个独立的索引
• 一次查询只能使用一个索引
• 系统尽管在有索引的情况下，通过计算发现不使用索引效率更好，则就不用

16、连接查询

① 连接查询的分类

Inner join：内连接
Outer join：（left join和rigth join）外连接
Cross join：交叉连接
Natural join：自然连接
语法格式：SELECT * FROM 表A  [inner|left|right|cross|natural] join 表B  on  连接条件

② 内连接（inner join）

有连接条件的查询：
1.  总的字段数量是俩个表的字段之和
2.  左表中不符合连接条件的数据不会出现
3.  右表中不符合连接条件的数据也不会出现
4.  右表中会有重复的记录出现
如果不加连接条件，则会出现两个表记录数乘积数量的记录

③ 左外连接(left join)

1.  总的字段数是两个数据表字段之和
2.  左表中的记录会全部出现
3.  右表满足连接的记录会和左表记录对接
4.  左表中连接条件不满足的记录，右表数据部分会用null来填充
5.  右表中的数据会有重复出现的情况，也会有不显示的记录
当前左表是多 对 右表的一 left join  结果是多对一

④ 右外连接(right join)

1.  右表的记录会全部出现
2.  右表中记录不满足连接条件的记录左表部分的数据会用null填充
3.  左表不满足连接条件的记录不会出现
4.  如果左表有多个记录满足右表同一记录的连接条件，右表记录会出现多次

⑤ 交叉连接(cross join)

1.满足连接条件的记录才会出现
2.无论左表还右表，只要不满足连接条件，都不出现
3.交叉连接时，两个表的地位是平等的

⑥ 自然连接(natural join)

Natural join：自然连接，它会自己找出连接的字段
Natural left join：左外连接，省略连接条件的方式
Natural right join：右外连接，省略连接条件的方式
Natural join相当于cross join + using

17、安全事务处理

把一组SQL当成一个集合，视为一个事务，集合中的SQL要么全部执行成功，要么全部没有效果。如果在执行中间出现了问题，可以回滚到初始状态，保持了完整性。
注意：只有innodb存储引擎才支持事务处理功能。
① 使用transaction实现安全事务处理

start transaction ；开启事务处理
commit；提交事务
rollback；回滚操作

② 使用autocommit手工执行事务处理

show variables like 'autocommit'； 系统参数
autocommit的值为on时，表示客户端的SQL是执行+提交的状态

手工执行事务处理的过程：

1.  Set autocommit=0，代表现在SQL只执行先不提交
2.  然后我们可以执行我们的一组SQL
3.  如果有失败的情况，则可以使用rollback进行回滚
4.  如果全部正常完成，可以使用commit进行提交，或者直接set autocommit=1

③ 事务日志

MYSQL的事务处理实际上是利用了日志，在开启安全事务处理的情况下，SQL只是先写到日志文件，并未写入真实的数据表中。

④ 事务处理的ACID特点

事务处理具有原子性，atomicity
    事务处理中的SQL是一个整体，不能够被分割
事务处理具有一致性，consistency 
    事务处理中的SQL要么全执行成功，要么都不生效
事务处理是想到隔离的，isolation
    不同的事务不会相互影响，之间是有隔离的
事务处理的结果要有持久性，durability
    事务处理的结果数据要能够实际的写入数据表中

18、矛盾体sql语句

在数据库test中的一个表student，字段是name，class，score。分别代表姓名、所在班级，分数。 
1) 算出每个班级中的学生,成绩按照降序排序; 
select name,class,score from student order by class,score desc; 
2) 查出每个班的及格人数和不及格人数，格式为：class、及格人数、不及格人数；
①case语句
SELECT class,  
SUM(CASE WHEN score>=60 THEN 1 ELSE 0 END),  
SUM(CASE WHEN score<60 THEN 1 ELSE 0 END)  
FROM tb1 GROUP BY class
②if语句
SELECT class,
SUM(IF(score>=60,1,0))jige,
SUM(IF(score<60,1,0))bujige
FROM student
GROUP BY class
LIMIT 0,30;
3) 用PHP写入连接数据库("localhost","msuser","mspass")、执行以上SQL、显示结果、判断错误、关闭数据库的过程 
$connect =mysql_connect("localhost","msuser","mspass");
mysql_select_db('test');
$sql="SELECT class,
SUM(IF(score>=60,1,0))jigei,
SUM(IF(score<60,1,0))bujigei
FROM student
GROUP BY class
LIMIT 0,30";
if($result = mysql_query($sql)){
while($row=mysql_fetch_assoc($result)){
print_r($row);
}
}
mysql_close($connect);

19、基本语句

1) alter table user add sex enum(1,2,3)not null default '1'
comment '1:男 2:女 3:保密';//增加字段
2) alter table user add index in_age(age);//增加索引 

20、sql语句统计

不同操作员不同营业部

select * from (select contract_id as business_id,business_sector,operator from `youngmanager_contract_info` group by business_sector union select contract_id as operator_id,business_sector,operator from `youngmanager_contract_info` group by operator) as uni order by business_sector

laravel按两个字段分组并统计相关信息：

$travelAgencyUnique = DB::table('contract_info')->select('contract_id', 'travel_agency', 'travel_type',DB::raw('count(travel_agency) as contract_num'), DB::raw('count(operator) as operator_num'),DB::raw('sum(passenger_number) as tourist_num'), DB::raw('sum(travel_fee_total) as contract_fee'))->groupBy('travel_type', 'travel_agency')->orderBy('contract_id', 'desc')->get();
相应的sql语句：
select `contract_id`, `travel_agency`, `travel_type`, count(travel_agency) as contract_num, count(distinct operator) as operator_num, sum(passenger_number) as tourist_num, sum(travel_fee_total) as contract_fee from `youngmanager_contract_info` group by `travel_type`, `travel_agency` order by `contract_id` desc
select contract_id,travel_agency,operator,
sum(case when travel_type=1 then 1 else 0 end )as typeA ,sum(case when travel_type=1 then passenger_number else 0 end) as numberA,sum(case when travel_type=1 then travel_fee_total else 0 end) as feeA,
sum(case when travel_type=2 then 1 else 0 end )as typeB ,sum(case when travel_type=2 then passenger_number else 0 end) as numberB,sum(case when travel_type=2 then travel_fee_total else 0 end) as feeB,
sum(case when travel_type=3 then 1 else 0 end )as typeC ,sum(case when travel_type=3 then passenger_number else 0 end) as numberC,sum(case when travel_type=3 then travel_fee_total else 0 end) as feeC
from youngmanager_contract_info group by operator,travel_agency

21、MySql开启远程用户登录

grant all privileges on *.* to 'root'@'%' identified by 'mm0715xx1201' with grant option;
flush privileges;//重新加载权限表