MySQL笔记

MySQL

• DDL
• DML
• DCL

启动/停止服务

net start MySQL  启动MySQL服务
net stop MySQL   停止MySQL服务

连接数据库服务器

mysql -hlocalhost -P3306 -uroot -p

创建数据库

create database test1;

查看当前存在的数据库

show databases;

查看数据库的创建语句

show create database test1;

数据库删除

drop database test1;

修改数据库信息

alter database test1 [修改指令];
指令：数据库属性的修改
修改名字：
- 直接改文件夹
- 将内容导入新数据库

创建表

create table `test1`.`table1` (id_no varchar(20),date_start date);

指定默认数据库

use test1;

查看表

show tables;
show tables like 'exam_%';
tips:用\G代替;结束可以在命令行中显示得不那么乱

查看(描述)表结构

desc table1;

删除表

drop table if exists table1;

修改表

修改表名

rename table table1 to table2;.

修改列定义

增加一个新列

alter table table1 add heig int;

修改一个列的定义

alter table table1 modify heig varchar(20);

删除一个列

alter table table1 drop heig;

重命名一个列

alter table table1 change heig heig_new int;

修改表选项

alter table table1 charset utf8;

创建数据

insert into table1 (id_no,`666`) values ('001','0');
可以省略字段列表，但是要求值的顺序与表中字段顺序一致

获得数据

select [字段列表] from 表名 查询条件;
字段列表可以用*表示所有字段，查询条件可以省略表示所有记录都获得相当于where 1
order by :可以在获得数据时将数据按照某个字段进行排序

删除数据

delete from table1 where fenshu<=60;
删除需要在逻辑上严格给条件，否则容易造成数据丢失

修改数据

update 表名 set 字段=新值 条件
update exam_student set fenshu=100 where fenshu>=72;

校对规则

当前字符集内，字符之间的比较关系
show collation like 'utf8%';

整数类型

• tinyint
• smallint
• mediumint
• int
• bigint
  定义最小的显示宽度：
  alter table tbl1_int add c tinyint(2) zerofill;

小数类型

浮点数 float double
定点数 decimal  
浮点数如果不写经度和标度，会按照实际精度值保存，如果有精度和标度，则会自动将四舍五入后的结果插入，系统不会报错,但是不精准；定点数如果不写精度和标度，则按照默认值decimal(10,0) 来操作，如果有精度和标度，当数据超过了精度和标度值，系统会报错，但是精准。

支持控制数值的范围 Type(M,D) M表示所有的数值位数，D表示小数的位数。 不包括符号和小数点  
create table num_3(a float(5,2),b double(8,3),c decimal(10,2));

日期时间类型（支持0值，如2017-04-00，表示4月整月）

• 年月日时分秒 Datetime 2017-06-01 11:11:11
• 时间戳 Timestamp 2017-06-01 11:11:11 存储时整型 表示时日期时间型，检索列时，+0可以看存储时的类型
• 年月日 Date
• 时分秒 Time
• 年 year

字符串类型

• char 固定长度（浪费空间，但是不用计算速度快）身份证号、手机号等等
• varchar 可变长度（节省空间，但是要计算速度慢）
  a varchar(65532)代表字符数，本来是65535，需要2个字节来计算长度，还有1个字节来记录是不是 not null ,不论有多少个字段，都是用统一的一个字节来表示，而不是每个字段一个字节
• text 文本，表示的字符串长度不一样
• enum 枚举 单选
  create table s_8 (gender enum(‘male’,’female’));
  insert into s_8 values (‘male’);
  insert into s_8 values (‘female’);
• set 集合 不定项选择
  create table s_9 (hobby set(‘basketball’,’football’,’swimming’));
  insert into s_9 values (‘basketball,football’);

列属性

• 是否为空 规定一个字段的值是否可以null
• 默认值属性 用default声明
• 主键 可以唯一标识某条记录的字段，用primary key声明，两种方式。1、id int primary key 2、primary key
  (name, class)
• 自动增长 auto_increment
  id int primary key auto_increment

实体之间的对应关系

• 1:1 一个表的主键对应另一个表的主键
• 1:n 在n的那个表，增加一个字段，用于指向该实体所属的另外的实体的标识
• n:m 利用一个中间表，表示实体之间的对应关系。处理成1：n和1:m的关系

外键

如果一个实体的某个字段，引用另一个实体的主键，就称这个字段为该实体的外键。父子表。作用是用于约束关系内的实体。
- 在从表上增加一个外键字段。
foreign key （class_id）references itcast_class (class_id)
- 设置级联操作：在主表数据发生改变时，与之关联的从表数据应该如何处理
主表更新 on update
主标删除 on delete
- cascade 子表随着父表的主键更新或删除
- set null 父表的主键被删除，子表外键设置为空
- restrict 不允许父表的主键更新或删除

存储引擎

也就是表的存储数据结构，默认是innodb，还有如myisam等

order by

按照字段值进行排序
- order by 字段 升序|降序（asc|desc）

limit

限制获得的记录数量
- limit 0,1000 从0索引开始的1000条记录
- limit 1000

group

• 与聚合函数一起用 max min avg sum count
  select cat_id,max(shop_price) from goods group by cat_id;
  -把列名当成变量看待
  select cat_id,sum(shop_price * goods_number) as temp from goods group by cat_id;

having

• 也是过滤条件，与where的区别就是：where必须用表内的字段，而having可以用别名
  面试题：select name,avg(score) as avg_score, sum(score < 60) as gk from stu group by name having gk >=2;
  注意：不能用count替代sum，因为count只计算行的数量，一般count(),和括号内的表达式没关系*

distinct

• 去掉重复记录
  select distinct days from teacher_class;

union

• 联合查询
  将多条select语句的结果，合并到一起称之为联合操作。注意，如果union的结果存在重复的记录，那么会消除重复。可以使用 union all
• (select …) union all (select…);
• 排序问题：
  子语句结果的排序：1，将子语句包裹在子括号内。2，子语句的order by 只有在配合limit时才生效。原因是union在做子语句时会对没有limit的order by优化（忽略）
  对所有结果进行统一排序：只需要在最后一个select语句后增加一个order by即可
• 规则：多条select语句的查询字段数和类型必须一致，不一致有时也行，因为能进行类型转换。最终的查询字段数和类型是根据第一天select语句来决定的

子查询

• select t_name, gender from teacher_class where days = (select max(days) from teacher_class);
• 子查询分类，不同的分类，会有不同的使用方式。分类标准：出现位置，返回值形式。
• 出现位置：where from exists
• 返回值形式：单一值，一列，一行，一表
• 集合操作符：in, not in, = any相当于in, != all相当于not in, some同义词any
  select t_name, c_name, days from teacher_class where t_name in (select t_name from teacher_class where c_name='001');
select * from (select * from teacher_class) as tc

连接查询

• 每个实体一个表，若要使用多个实体就要将多个表连接起来。思路：将所有数据按照某种条件连接起来再进行筛选处理
• 连接的分类：内连接、外连接、自然连接
• 内连接：数据内部的连接，要求连接的多个数据都必须存在才能进行连接 tbl_left inner join tbl_right on 连接条件
  
  1. inner join 可省略inner
  2. cross join 在MySQL中与inner join相同，但在数据库定义中是没有条件的inner join
  3. 建议：在有同名字段时使用
     using (同名字段)，在通用条件时使用on，在数据过滤时使用where
• 外连接：如果负责连接的一个或者多个数据不真实存在，称之为外连接（不能用where）
  
  1. left (outer) join 保留左表的所有字段，即使右表没数据也用null填充
  2. right (outer) join 保留右表的所有字段，即使左表没数据也用null填充
  3. full join ，mysql不支持
• 自然连接
  
  1. natural join 相当于 inner join + using ()
  2. natural left join 相当于 left join + using ()
  3. natural right join 相当于 right join + using ()
     重点面试题：连接同一个表两次与自连接（起别名防止冲突）

outfile

将查询结果备份到文件里面
select * into outfile 'e:/amp/one' fields terminated by '\t' enclosed by '' escaped by '\\' lines terminated by '\n' from teacher_class where t_name='韩信';

infile

load data infile 'e:/' into teacher

增加数据

1. insert into teacher_class (t_name) values ('张三丰');
2. insert into teacher_class set t_name='张无忌';
3. insert into teacher_class (id, t_name, c_name) values (13, '杨露禅', '太极') on duplicate key update t_name='杨露禅', c_name='太极';
4. insert into teacher_class (t_name, c_name) select t_name, c_name from teacher;
5. replace into teacher values (1, '韩非', '法家', 30);

删除更新

1. limit 配合 order by来使用
   先将结果排序，再删除固定数量的记录，只有order by是没用的
   delete from teacher order by days limit 10;
2. 允许连接删除
   允许使用类似的join语法，同时删除多个表内的记录。需要先提供表名，再提供连接条件
   delete from one,two using one join two on one.public_field=two.public_field where one_id=2;
3. 清空表
   delete from 是逐行删除，truncate 是整表删除，再整表新建
   truncate teacher;
4. 多表更新
   update one join two on one.public_field=two.public_field set one_data='x',two_data='y' where one_id=3;

备份还原

1. 只适用于myisam：复制粘贴3个表结构文件 .frm .myd .myi
2. 将建表结构与插入数据的sql语句生成并保存，下次如果需要该结构和数据，直接将数据语句执行即可
3. 利用 mysqldump.exe
   mysqldump -uroot -p php_one > e:/php_one.sql
4. 使用source还原指定需要执行SQL语句的源代码
   source e:/php_one.sql

视图

1. 视图就是一个存在于数据库中的虚拟表，本身没有数据，只是通过执行相应的select语句完成获得相应的数据
   
   • 创建视图
     create view v_teacher as select id,t_name from info_teacher; 相当于 select * from (select id,t_name from info_teacher) as temp;
   • 删除视图
     drop view if exists v_teacher;
   • 修改视图
     alter view v_name (v1,v2) as select id,t_name from info_teacher;
   • 缩减业务逻辑
     通过视图还可以使复杂的业务逻辑简单地完成，先使用视图完成一定的逻辑，再在视图的基础上完成另外的逻辑。通常视图完成的逻辑都是相对比较基础的逻辑。
     create view join_info as select tc.id as tc_id,t_name,c_name,days from join_teacher_class as tc left join join_teacher as t on tc.t_id=t.id left join join_class as c on tc.c_id=c.id;
select * from join_info;
   • 视图的执行算法
2. merge
3. temptable
   指的是一个视图是在什么时候执行，依据哪些方式执行。
   merge ：合并的执行方式，每当执行时，先将我们视图的sql语句与外部查询视图的sql语句混合在一起，最终执行
   temptable : 临时表，每当查询的时候将视图所使用select语句生成一个结果的临时表，再在当前的临时表内进行查询。
   当用户创建视图时，mysql默认使用一种undefine的处理算法：就是会自动在合并和临时表内进行选择。
   注意：
4. 尽量使用视图完成读操作
5. 如果使用视图，则需要注意，对视图的修改，也是真实表的修改，会即时生效
6. 删除视图时，是不会销毁实体表内的数据的
7. 如果大家做的是外部接口，一个数据库多个应用，针对每一个应用，采取不同的视图接口。

事务使用

一组sql语句操作单元。组内所有sql语句完成一个业务。如果整组成功，意味着全部sql都实现。如果其中一个失败，意味着整个操作都失败。意味着整个过程都是没有意义的。应该是数据库回到操作前的初始状态。
- 如何处理？
1. 失败后，可以回到开始位置
2. 没都成功之前，别的用户是不能看到操作内的数据修改的。
3. 思路：就是在一组操作之间，设计一个记号，备份点
- 实现
利用innodb存储引擎的事务日志功能。
1. 先执行sql语句
2. 将结果提交到数据库
其中我们的事务日志就是保存执行阶段的结果，如果用于选择提交，则才将执行结果提交到数据库。
默认的执行方式叫 自动提交。执行完毕自动完成提交工作。因此需要关闭自动提交功能。
set autocommit=0;
成功：commit;
失败：rollback;
然后再set autocommit=1;
常见的事务指令：
start transaction; 关闭自动提交。如果事务结束了都会将自动提交机制回到start的状态
成功：commit;
失败：rollback;
- 事务的特点（ACID）
1. 原子性
2. 一致性
3. 隔离性
4. 持久性

触发器

监听数据进行操作：
在当前的表上，设置一个对每行数据的一个监听器，监听相关事件，每当事件发生时。会执行一段由sql完成的一段功能代码。
触发器的元素：事件，执行代码
事件：
插入 insert
删除 delete
修改 update
事件的时机： 执行之前和执行之后，before after
由时机和事件在一起形成了六种事件
- 创建触发器 create trigger 名字 事件 可执行性代码
create trigger jiaobanfei after update on czbk_student for each row update class set cz_money=cz_money-20;
- 管理触发器
删除：Drop trigger trigger_name;
查看：Show create trigger trigger_name;
在触发器内，获得触发该触发程序时的数据：利用触发程序内的new和old来完成
create trigger jiaobanfei after update on czbk_student for each row update class set cz_money=cz_money+（old.stu_money - new.stu_money）;
- 注意
如果一个触发程序由多条sql语句组成，应该：
1. 语句组成语句块（begin end）用来标识语句块
2. 语句块的语句需要独立的语句结束符，分号。所以要把整个触发器的结束符通过delimiter修改为其他符号
delimiter $$
delimiter ;

索引

• 概念
  是针对数据所建立的目录
  作用：加快查询速度
  负面影响：降低了增删改的速度
  案例:设有新闻表500W行数据，共15列，10列有索引
  答：1. 把空表的索引全部删除
  
  1. 导入数据
  2. 数据导入完毕后集中建索引
• 索引的创建原则
  
  1. 不要过度索引
  2. 尽量在where条件最频繁的列上加
  3. 尽量索引散列值，过于集中的列上加索引无意义
• 索引的类型
  
  1. 普通索引 index 仅仅是加快查询速度
  2. 唯一索引 unique index 行上的值不能重复
  3. 主键索引 primary key 不能重复，主键索引必是唯一索引。唯一索引不一定是主键索引。一张表上只能有一个主键，但是可以有一个或多个唯一索引
  4. 全文索引 fulltext index
• 查看一张表上的所有索引
  show index from 表名
• 建立索引
  alter table 表名 add index tel(tel);
alter table 表名 add unique (tel);
alter table 表名 add fulltext (tel);
alter table 表名 add primary key (id);
• 删除索引
  alter table 表名 drop index tel;
alter table 表名 drop unique tel;
alter table 表名 drop fulltext tel;
alter table 表名 drop primary key;
• 全文索引的用法
  在mysql的默认情况下，对于中文意义不大,因为无法像英文单词那样用空格区分开。关于全文索引的停止词，不针对非常频繁的词做索引如 my this you
  select * from table1 where (intro) against ('keyword');

sql编程

• 注释
  
  1. #
  2. –[空格]
• 变量
  
  1. set 变量名 = 变量值 注意，为了区分系统变量与用户自定义变量，需要在用户变量前增加@符号
     set @who = '韩当';
select @who;
  2. select 字段列表 表达式 … into 变量列表
     select 10,15,20 into @a,@b,@c;
select c_name from join_class where id=3 into @c_name;
     注意，=应该赋值，但是在select语句中，就成了关系等于。使用专门的赋值运算符 := 同样适用于set
• 应用
  
  1. 存储过程
  2. 存储函数
  3. 触发器
• 函数
  
  1. 内置函数
     substring()截取
     concat()字符串连接
     lpad()左边补足
     MD5()
     password()
  2. 存储函数/自定义函数
     create function 函数名（参数列表）返回值类型 函数体

delimiter $$ create function sayhello() returns varchar(20) begin return 'hello world'; end $$
delimiter ;
select sayhello();

• sql中的流程控制：
  
  1. 分支
     if 条件1 then
     执行的语句1
     elseif 条件2 then
     执行的语句2
     else
     上面的条件都不满足，执行的语句3
     end if
     create function func1() returns varchar(20)
begin
-- hour可以获得当前时间的小时部分
if hour(now()) >= 18 then
return '晚';
else
return '早';
end if;
end
  2. 循环
     delimiter $$ create function func2() returns int begin -- 1-10的和 set @i=1; set @sum=0; while @i<=10 do set @sum=@sum + @i; set @i = @i + 1; end while; return @sum; end $$
delimiter ;

循环的提前终止
leave 相当于 break 终止循环
iterate 相当于 continue 终止当前循环
注意，不是根据leave和iterate所在的位置来决定终止哪个循环，而是由循环的标签来决定的。
循环的标签，给循环起名字。
delimiter $$ create function func3() returns int begin set @i=1; set @sum=0; w:while @i<=10 do set @i = @i + 1; if @i = 5 then leave w; end if; set @sum=@sum + @i; end while w; return @sum; end $$
delimiter ;

函数内使用的变量
@var的形式，相当于全局变量，函数内和函数外通用。

函数的参数：
参数，同样需要确定类型。
参数名 类型
delimiter $$ drop function if exists sayHello; create function sayHello(user_name varchar(20)) returns varchar(20) begin return concat('hello',user_name); end $$
delimiter ;
select *, sayHello(t_name) from join_teacher;

函数声明的局部变量：
使用declare声明局部变量，需要指定类型，可以指定默认值default
#获得给定班级内最大的学号
delimiter $$ create function sno(c_id int) returns char(10) begin declare s_no char(10); declare class_name char(7); #保存当前班级内最大的学号，如果没有就是null select stu_no from join_student where id=c_id order by stu_no desc into s_no; if isnull(s_no) then select c_name from join_class where id=c_id into class_name; return concat(class_name,'001'); else return concat(left(s_no,7),lpad(right(s_no,3)+1,3,'0')); end if; end $$
delimiter ;
select sno(5);
- 存储过程
概念类似于函数，就是把一段代码封装起来，当要执行这一段代码的时候，可以通过调用该存储过程来实现。
1. 查看现有的存储过程
show procedure status
2. 删除存储过程
drop procedure 存储过程的名字
3. 创建存储过程
delimiter $$ create procedure p1(n int,j char(1)) begin if j='h' then select * from g where num > n; else select * from g where num <= n; end if; end$$
delimiter ;
4. 调用存储过程
call p1(10,'h');
5. 存储过程和存储函数的区别
一个是名词不同，二个是存储过程没有返回值