MySQL(2)

DBMS(关系型数据库): MySQL SQL Server Oracle SQL==SQUEL(最开始的叫法)

增删改查有一个术语：CRUD操作 : Create（增） Retrieve（检索） Update（修改） Delete（删除）
SQL脚本太大打不开,直接在dos命令窗口输入source 文件路径 回车 直接运行
------------------------------------------------MySQL--------------------------------

1.取别名时加上单引号或双引号,可以允许单词之间出现空格
简单关键词:
distinct去重 where 条件判断 in 属于 is null limit union
default默认值 delete 删除

2.类型
(1)int 占用4字节 tinyint 占用1字节内存,存储0-255数字 smallint 2字节 0-65535 bigint 8字节
(2)decimal(9,2)代表一个有小数的数字,最多9位数,小数点后面有2位数

---

3.regexp(分为替换regexp_replace和解析regxp_extract) 正则表达式 * . + ?
*表示0次或多次 +表示1次或多次 .匹配除换行符 \n 之外的任何单字符 ?匹配前面的子表达式零次或一次，或指明一个非贪婪限定符
1)where name like “%field%”; where name regexp “field”; 效果是相同的
2) ^ 表示字符串的开头 $ 表示字符串的结尾
3)where name regexp “field|mac” |表示多个搜索模式,可以搜索含有 field 或 mac 的内容 [还是忽略大小写]
4) where name regexp “[gi]c” 表示搜索含有 (在c前面有g) 或 (在c前面有i) 的内容
5) where name regexp “[a-h]c” 表示搜索含有 (在c前面有a) 或 (在c前面有b)…(在c前面有h) 的内容
总结: --^ beginn --$ end --| logical or --[abcd] 单个字符 --[a-h] 从a到h的单个字符

4.using 连接条件是2个表里面相同的变量,内连接和外连接都可以使用
1) on t1.customer=t2.customer 等同于 using(customer)
2)on t1.customer=t2.customer and t1.id=t2.id 等同于 using(customer,id)

5.natual join 自然连接 [让数据库自己选择连接条件]

6.cross join 交叉连接 [没有连接条件] 这是显示连接,隐式的用","连接 笛卡尔积

7.varchar来存储字符串或文本值不浪费空间,用char存储的话,剩下的会用空字符来填充

8.update 表名 set 修改列的数据 where 修改哪列
mysql工作台默认一次只允许修改一条数据(取消安全更新即可一次更新多条数据)
例如: update

9.with rollup 该运算符只适合用于聚合函数,在使用rollup运算符的时候,不能在group by子句中使用列别名

10.子查询返回的是一列值,在where语句中,如果是等于可以用 “in” 或者"= any"关键词,要是是大于小于用 ALL 关键字可以一次比较多个,
也可以在子查询里面用聚合函数max替代

11.相关子查询 :执行的慢,数据量越大,越占内存
例如: select * from employees e where salary>(select avg(salary) from employees where office_id=e.office_id)
不同部门的薪水大于平均工资的人

12. EXISTS 会返回一个指令,来查看表中是否存在符号这个条件的行 [用 in 的话万一在子查询里满足条件的数据量很大,使用exists会提高效率]
    select * from clients c where exists (select client_id from invoices where client_id =c.client_id )

13.多表查询
(1)笛卡尔积 多表查询语法：select * from a,b;
(2)隐式内连接查询 语法：select 列名 , 列名 … from 表名1,表名2 where 表名1.列名 = 表名2.列名;
(3)显式内连接查询 语法：select * from 表名1 inner join 表名2 on 条件 ;
(4)左外链接 语法：select * from 表1 left [outer] join 表2 on 条件;
(5)右外连接 语法：select * from 表1 right [outer] join 表2 on 条件;
(6)全连接 语法:mysql不支持 可以用union all代替,可能会有重复的出现
(7)差集:left join 和right join写差集： a是主表，b是副表(a表存在，表不存在)
a left join b on a.cid=b.cid where b.cid is null

14.数值函数
(1)round() 四舍五入 round(数,取几位数)
(2)ceiling() 向上取整
(3)truncate(数,取几位数) 不会进行四舍五入
(4)floor()向下取整
(5)ABS() 计数绝对值
(6)rand() 随机生成0-1之间的数

15.字符串函数
(1)length() 拿到字符串长度
(2)upper() 和lower() 将字符串转换成大写和小写字母
(3)ltrim() 左修整 rtrim()右修整 trim()修整左右 修整的意思是删除空格
(4)left(字符串或字段,返回字符串字符数) 返回字符串左侧的几个字符
(5)substring(字符串或字段,起始位置,返回字符串字符数)
(6)locate(‘n’,‘kindergarten’) 输出3 [不区分大小写] 如果里面没用要查询的字符就输出0
(7)replace(‘kindergarten’,‘garten’,‘gam’) 输出 kindergam
(8)concat() 拼接字符串,也可以拼接字段 [2个字段中间加空格]

16.日期函数
(1)now() 获取当前时间和日期 curdate()返回当前日期 curtime()返回当前时间
(2)year() month() day() hour() minute() second() 返回整数 dayname() 返回字符串格式的星期数 monthname()返回字符串格式的月份
(3)extract() 把代码录入别的DBMS 最好用该函数 如: select extract (year from now()) 返回当前的年份
(4)data_format(now(),格式 ) %y 显示2位数的年 %Y 显示4位的年份 %m 数字月 %M 英文月 %d 数字天 %D 天th
(5)date_add(,) data_sub(,)
(6)datediff(,) 2个日期的差
(7)time_to_sec()从零点开始计时返回时长

17.if
(1)ifnull(name,‘not name’)如果name有null,里面的数据就会变成后面的字符串not name [如果2个都是字段的话,第一个字段为null就会返回第二个字段的值]
(2)coalesce(name,id,‘not name’ )
(3)ifnull里面可以用其他的内容替换空值,而coalesce会返回这堆值中的第一个非空值
(4)if语句语法: if then else ; end if; [if then 可以写多个,遇到end if 结束]
(5)if(表达式,值1,值2) 表达式为真,返回值1,表达式为假,返回第二个

***18.视图
在sql语句上面写create view 名字 as SQL语句; 可以把他当成一张表来用,视图不存储数据.[基础班的内容做出来了改变,那么由其创建的视图内容也会变]
有2种方法修改视图:
(1)删除视图并且重新创建 [使用drop view 名字]
(2)在sql语句上面写create or replace view 名字 as SQL语句; 不需要删除视图
在mysql里看其他的源码,发现上面有许多加上反引号,是为了防止名称冲突
(3)可更新视图: 没有使用distinct,聚合函数,group by ,having union 函数. [里面的数据可以进行操作.]
(4)在create or replace view 名字 as SQL语句 with check option; 加上with check option 可以防止视图里的行消失 [删除某行时,会得到错误提示]
(5)优点:1)视图可以帮助我们简化查询
2)视图可以减少数据库设计改动的影响 [加强数据的安全性]
(6)视图的作用？
视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图对象进行CRUD。

19.SQL存储 相当于java中的封装
(1)存储过程 :数据更安全,存储得更快
(2)语法:create procedure 名字 ()
BEGIN
SQL语句
END
解释:名字用小写单词加下划线分割开,可以在小括号里添加参数,给存储过程传递值。BEGIN和END关键字之间的内容成为存储过程的主体(body),body可以写多条SQL语句,用分号隔开
在create前面写DELIMITER 语句可以改变默认分隔符,在END后面也要加上,在用一次DELIMITER;把分隔符修改为用来的。
例子： DELIMITER $$createprocedurege{t}_{c}lients()beginselect\ast fromclients;select\ast fromclients;END$$
DELIMITER ;
(3)创建存储过程之后,可以用call来调用 如: call get_clients() --如果在创建的时候小括号里有参数列表,调用的时候必须写上参数
(4)删除存储过程 drop procedure [if exists] 名字;
(5)在创建过程时,参数列表里的参数名称前面加上 out 即为输出参数 [默认为输入]
在用call函数调用时需要加上@来区分输入参数与输出参数。
在调用过程时
1)用@来定义变量 set @变量名=0; 设置变量的初始值为0
2)在用select @变量名;来显示输出变量值

20.变量 variable
(1)输出变量在整个会话过程中被保存,如果从mysql掉线,数据会被清空,所有又把它们称为’用户或会话变量’
用set 语句定义它们,并用@符号作为前缀
(2)本地变量 local variable : 这些变量是我们可以在存储过程或函数内定义的,它们不会在整个会话过程中被保存
一旦我们的存储过程完成执行任务,它们就会被清空
1)在begin 后面用declare语句声明变量,然后在设定它的数据类型,再在后面设定一个默认值,否则默认值为null
–如 : declare money decimal(9,2) default 0;
2)想要在SQl语句中使用,在select 里面用INTO 修饰,后面在接要使用的变量名.就这样把查询到的值放入到变量里
–如:select count(*),sum(total) into invoices_count,invoices_total from invoices;

21.自定义函数
(1)概述:函数和存储过程非常相似,主要区别是:函数只能返回单一值,而存储过程可以返回拥有多行和多列的结果集
(2)语法: create function 名字(参数列表)
returns 返回值类型
属性 [每个mysql都至少要有一个属性] deterministic (确定性[根据公式返回值]) reads sql data (读取SQL函数,[函数中会配置选择语句,用来读取一些数据]) modifies sql data(修改sql数据[函数中有插入,更新或者删除函数])
begin
sql 语句;
return 参数名称;
end
(3)删除函数: drop function [if exists] 名字;

22.触发器
(1)概述:触发器是在插入,更新和删除语句前后自动执行的一堆sql代码
(2)语法: DELIMITER $
create trigger 表名
after insert on 表 [after 表示在之后,before表示在之前,insert 也可以用update或drop]
for each row [这个触发器会作用于每一个受影响的行]
begin
sql语句;
end$
DELIMITER ;
(3)old与new
1)old在更新或删除行的时候很有用,old关键字会返回更新前的行以及对应数值
2)new 关键字会返回刚刚插入的数据,可以用 new.属性 ,来单独调用某个属性
使用OLD和NEW关键字，能够访问受触发程序影响的行中的列（OLD和NEW不区分大小写）。在INSERT触发程序中，仅能使用NEW.col_name，没有旧行。
在DELETE触发程序中，仅能使用OLD.col_name，没有新行。在UPDATE触发程序中，可以使用OLD.col_name来引用更新前的某一行的列，也能使用NEW.col_name来
引用更新后的行中的列。
用OLD命名的列是只读的。你可以引用它，但不能更改它。对于用NEW命名的列，如果具有SELECT权限，可引用它。在BEFORE触发程序中，如果你具有UPDATE权限，
可使用“SET NEW.col_name = value”更改它的值。这意味着，你可以使用触发程序来更改将要插入到新行中的值，或用于更新行的值。
在BEFORE触发程序中，AUTO_INCREMENT列的NEW值为0，不是实际插入新记录时将自动生成的序列号。OLD和NEW是对触发程序的MySQL扩展。
(4)可以使用 show triggers 来查看当前数据库的所用触发器。如果只想看某一个触发器，show trigger like '名字 ’

23.事件 帮助数据库维护任务实现自动处理
(1)用show variables like ‘event%’;来查看
set global event_scheduler=on;来打开
(2)语法:
delimiter $
create event 名字
on schedule
–at ‘时间’ [不加every的而有at话只执行一次]
every 间隔时间 [starts 起始时间 ends 结束时间] [starts 到ends 不是必须的]
do begin
SQL语句
end $
delimiter ;
(3)查看事件 show events;
(4)修改事件 把事件的复制一遍,把create改为alter
alter event 名字 enable;启用事件 alter event 名字 disable;关闭事件

24.约束(Constraint)
在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。
常见的约束
非空约束(not null)：约束的字段不能为NULL
唯一约束(unique)：约束的字段不能重复
主键约束(primary key)：约束的字段既不能为NULL，也不能重复（简称PK） auto_increment 自增
外键约束(foreign key)：…（简称FK）
检查约束(check)：注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束。

25.存储引擎
(1)建表的时候可以指定存储引擎，也可以指定字符集。mysql默认使用的存储引擎是InnoDB方式。默认采用的字符集是UTF8
(2)mysql支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。
(3)查看当前mysql支持的存储引擎？ show engines \G
(4)常见的存储引擎？
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
MyISAM这种存储引擎不支持事务。
MyISAM是mysql最常用的存储引擎，但是这种引擎不是默认的。
MyISAM采用三个文件组织一张表：
xxx.frm（存储格式的文件）
xxx.MYD（存储表中数据的文件）
xxx.MYI（存储表中索引的文件）
优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。
缺点：不支持事务。
-------------------------------------------------------------------------------------
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。
表的结构存储在xxx.frm文件中
数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读。
这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。
InnoDB支持级联删除和级联更新。
-------------------------------------------------------------------------------------
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
缺点：不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的。
优点：查询速度最快。
以前叫做HEPA引擎。

26.数据库设计三范式（重点内容，面试经常问）
(1)什么是设计范式
设计表的依据。按照这个三范式设计的表不会出现数据冗余。
(2)三范式都是哪些
第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。
第二范式：建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖。 多对多？三张表，关系表两个外键。
第三范式：建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖。 一对多？两张表，多的表加外键。
(3)一对一设计有两种方案：主键共享 ;外键唯一。

27.DBA命令
(1)将数据库当中的数据导出
在windows的dos命令窗口中执行：（导出整个库）
mysqldump bjpowernode>D:\bjpowernode.sql [ -uroot -proot]
在windows的dos命令窗口中执行：（导出指定数据库当中的指定表）
mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot –proot
(2)导入数据
create database bjpowernode;
use bjpowernode;
source D:\bjpowernode.sql

27.索引
(1)什么是索引？有什么用？
索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。
在数据库方面，查询一张表的时候有两种检索方式：第一种方式：全表扫描;第二种方式：根据索引检索（效率很高）
(2)索引为什么可以提高检索效率呢？
其实最根本的原理是缩小了扫描的范围。索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护。
是有维护成本的。比如，表中的数据经常被修改这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。添加索引是给某一个字段，或者说某些字段添加索引。
select ename,sal from emp where ename = ‘SMITH’;
当ename字段上没有添加索引的时候，以上sql语句会进行全表扫描，扫描ename字段中所有的值。当ename字段上添加索引的时候，以上sql语句会根据索引扫描，快速定位。
(3)怎么创建索引对象？怎么删除索引对象？
创建索引对象：create index 索引名称 on 表名(字段名);
删除索引对象：drop index 索引名称 on 表名;
(4)什么时候考虑给字段添加索引？（满足什么条件）
* 数据量庞大。（根据客户的需求，根据线上的环境）
* 该字段很少的DML操作。（因为字段进行修改操作，索引也需要维护）
* 该字段经常出现在where子句中。（经常根据哪个字段查询）
注意：主键和具有unique约束的字段自动会添加索引。根据主键查询效率较高。尽量根据主键检索。
(5)索引的实现原理？
通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，
通过物理地址定位表中的数据，效率是最高的。
select ename from emp where ename = ‘SMITH’;通过索引转换为：select ename from emp where 物理地址 = 0x3;
索引底层采用的数据结构是：B + Tree
(6)索引的分类？
单一索引：给单个字段添加索引
复合索引: 给多个字段联合起来添加1个索引
主键索引：主键上会自动添加索引
唯一索引：有unique约束的字段上会自动添加索引
(7)索引什么时候失效？
select ename from emp where ename like ‘%A%’;模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。

sqlstate errors 查错误代码 ibm.com的表好看点
前2个字母界定了错误类别,如22表示数据异常
signal sqlstate 会对数据进行截断,在再后面写set message_text=‘文本’;
如:if id<=0,then signal sqlstate ‘22003’ set message_text=‘输入的数据有误’;end if;