mysql笔记

MAC 的环境变量有多个  其中两个是系统级 一般我们修改 
三个用户级 

设置的方法 终端是输入 vim ~/.bash_profile
如果提示该文件已经存在 就再输入E 强行编辑
如果不存则会创建并打开 

打开以后 添加新的一行 内容为
export PATH=$PATH:/usr/local/mysql/bin
表示在 新建了一个path 指向了mysql的可执行文件夹
编辑结束后 按ESC 输入:wq  表示保存并退出
重启终端  输入mysql  只要没提示 Command not found 就代表成功了

MySql常用命令
注意:一行指令输入完一定要加分号
#
登录Mysql服务器 
需要两个参数 -u加用户名 -p加密码

#
修改密码
set password for 用户名@服务器主机名(localhost) = password('新密码');

#
退出 exit

#
查看数据库
show databases;    查看所有
show create database db_name;    查看某个数据库属性

#
修改数据库属性
alter database db_name 属性 值;

#
查看当前选中的数据库
SELECT database();

#选中一个数据库
USE db_name;

#删除一个数据库
DROP DATABASE IF EXISTS db_name;

#创建表
create table 表名(类名 类型,...);

#修改表
1.新增列
alter table tableName add(columnName dataType.....);
2.修改某列数据类型
alter table tableName modify columnName dxataType;
3.删除某一列
alter table tableName drop columnName;
4.修改字符集
alter table  tableName character set  newSet;
5.修改某一列的名称
alter table tableName change oldColumn newColumn dataType;
6.修改表名
rename table tableName to newTableName

#查看当前数据库中所有表
SHOW TABLES;

#查表创建语句
SHOW CREATE TABLE tab_name;

#查看表结构
DESC table_name;

#
1.删除表
DROP TABLE[IF EXISTS] table_name;
2.重建表
TRUNCATE TABLE table_name;

#主键约束 用于唯一确定一条数据（主键必须是非空且唯一的）
CREATE TABALE table_name (列名 类型 PRIMARY KEY,列名2 类型,...);

#多字段组合主键
CREATE TABLE table_name(列名1 类型,列名2 类型,类名3 类型,PRIMARY KEY(列名1,列名2));

#唯一约束
CREATE TABLE 表名(列名1 类型 UNIQUE,列名2 类型);

#默认值约束
CREATE TABLE 表名(列名1 类型,列名2 类型,列名3 类型 DEFALUT 列名3的默认值);

#添加数据
insert into 表名 values("值①","值②"...);
注意：值的顺序与类型必须与表的字段一致，且每一个字段都需要赋值。

#同时添加多条数据
INSERT INTO table_name VALUES(value1,value2,..),(value1,value2,..),..;

#为表中指定字段添加数据
INSERT INTO table_name (column1,column2,..) VALUES(value1,value2,…..);
注意：使用这种方式，values中值的顺序和个数必须与前面小括号中声明的字段一致。

#更新数据
UPDATE table_name set column1 = value1,[column2 = value2]  [WHERE where_definition];
注意：SET 子句指明要修改哪些列以及新的值。
WHERE子句指定要更新哪些行，如果没有WHERE子句则更新所有行。例如where id = 1；则更新id为1的一行

#删除数据
delete from table_name [WHERE where_definition];
注意：
* 如果不使用where子句将删除表中所有数据。
* delete语句不能删除一列的值。
* delete删除的是表中的记录，不删除表，删除表使用drop table。
* 如果要删除所有数据 可以使用truncate table 。
* 同insert和update一样，删除记录应该考虑参照完整性问题，也就是外键约束。

#显示某表中的所有数据
select *from 表名;

#在创建表的同时创建索引
CREATE TABLE tab_name(column1 dataType,column2. .,[UNIQUE|FULLTEXT|SPATIAL] INDEX index_name (column [(length)][ASC|DESC])) [ENGING=指定引擎名];

#查看某索引的执行过程
explain select *from 表名 where 列名 = 值;

#查看某索引的结果
select *from 表名 where 列名 = 值;

#给某表添加一个普通索引
CREATE INDEX 自定义索引名 ON 表名(列名);

#用ALTER给某表添加索引
ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX 自定义索引名(列名);

#删除索引
ALTER TABLE table_name DROP INDEX index_name;

#
存储引擎指的是文件最终存放的方式和数据结构，不同的数据结构有不同的优缺点。（有的存取快，有的查找快）默认为InnoDB。

#
空间数据类型（SPATIAL），用来存放一些空间数据，例如：一个点，一条线，一个形状，坐标等等。如果我们自己单独保存这样的数据，会花费很大的代价，所以提供了专门用于表示空间的数据类型。

#简单查询语句
SELECT [DISTINCT] 列名称1,列名称2,... FROM 表名称;
注意：Distinct表示过滤掉重复内容

#查询某表中的全部内容
SELECT * FROM 表名称

#备份数据（使用mysqldump这个工具，与mysql一样，它包含在安装目录下的bin文件夹中，如果设置了环境变量，可以直接在终端调用它）
mysqldump -u -h -p 数据库名 > [可以用绝对路径，也可以不写，直接导出到终端当前的执行路径]导出文件名.sql;

#备份数据的常用可选参数
·-d 不备表中的任何数据，只备份表结构
·-t 不备份表结构（即不添加建表语句）
·-c 在insert语句中加入字段名
·--skip-add-drop-talbe 不添加drop语句
·--add-locks 添加表锁，防止在恢复中被并发修改（默认是添加的）
·--skip-add-locks 取消表锁

#数据的恢复
1、使用mysql工具    例如：mysql -uroot -p newDB < 导入的文件路径
注：不会自动创建newDB，需要在恢复前手动创建
2、登录到mysql后，先选择一个数据库，再使用“source 导入的文件路径”

#在select语句中可使用表达式对查询的列进行运算
SELECT *|{column1｜expression, column2｜expression，..}FROM tablename;

#在select语句中可使用as语句为列（或运算后的列）取别名
SELECT columnname as 别名 from table_name;

#WHERE 子句中可用的运算符
·比较运算符
    ·>   <   <=   >=   =    !=  ：大于、小于、大于(小于)等于、不等于
    ·BETWEEN  ...AND... ：显示在某一区间的值
    ·IN(set) ：显示在in列表中的值，例：in(100,200)
    ·LIKE ‘张pattern’ ：模糊查询  %代表零个或多个任意字符，_ 代表一个字符，例first_name like ‘_a%’;
    ·IS NULL ：判断是否为空
·逻辑运算符
    ·and ：多个条件同时成立
    ·or ：多个条件任一成立
    ·not ：不成立，例：where not(salary>100);

#Count(列名)返回某一列，行的总数
Select count(*)|count(列名) from tablename[WHERE where_definition];

#Sum函数返回满足where条件的行的和
Select sum(列名)[,sum(列名)] from tablename[WHERE where_definition];

#AVG函数返回满足where条件的一列的平均值
Select avg(列名)[,avg(列名)…] from tablename[WHERE where_definition];

#Max/min函数返回满足where条件的一列的最大/最小值
Select max|min(列名)　from tablename[WHERE where_definition];

#使用order by 子句排序查询结果
SELECT column1, column2. column3..FROM    table order by column asc|desc;    
注： Order by 指定排序的列，排序的列即可是表中的列名，也可以是select 语句后指定的列名。Asc 升序、Desc 降序。ORDER BY 子句应位于SELECT语句的结尾。

#GROUP BY:作用: 把行按照字段分组
SELECT column, . FROM table_name group by column1,column2;
使用场景:常见于统计场合,如计算平均分,统计数据量等

#使用having 语句对GROUP BY的结果进行分组
SELECT column1,column2,. . FROM table_name group by column having column=[或者其他条件]'[某值]';
having 用于对分组结果继续过滤。
where和having的区别：where用于在原始数据中进行查询。
having用于在结果集中进行过滤。
当一个语句中既有where 又有having时 先执行where 后执行having
where条件中不能出现聚合函数，having中可以。

#
·分组函数与聚合函数一起使用才有意义；
·group by 后面的列必须出现在select后面；
·如果在select 语句中同时包含有group by ,having ,order by 那么他们的顺序是group by , having , order by。

#使用limit关键字来查询指定位置和指定数量的数据。
SELECT *|column. . from table_name limit [offset,] count;
offset可选用于指定从什么位置开始获取。
count指定要查询数据的条数。

#可以用SELECT来查看函数的返回结果
例如：SELECT CURRENT_DATE (  );

#流程控制函数
IF(expr1,expr2,expr3)    如果expr1为True ,则返回 expr2 否则返回 expr3
IFNULL(expr1,expr2)    如果expr1不为空NULL,则返回expr1,否则返回expr2
"SELECT CASE WHEN expr1 THEN expr2 ELSE expr3 END; [也可以多重分支.]"    如果expr1 为TRUE,则返回expr2,否则返回expr3(多重分支的时候，有几个CASE，就要有几个END)

#字符相关函数
CHARSET(str)    返回字串字符集
CONCAT (string2  [,... ])    连接字串
INSTR (string ,substring )    返回substring在string中出现的位置,没有返回0
UCASE (string2 )    转换成大写
LCASE (string2 )    转换成小写
LEFT (string2 ,length )    从string2中的左边起取length个字符
LENGTH (string )    string长度
REPLACE (str ,search_str ,replace_str )    在str中用replace_str替换search_str
STRCMP (string1 ,string2 )    逐字符比较两字串大小,
SUBSTRING (str , position  [,length ])    从str的position开始,取length个字符
LTRIM (string2 ) RTRIM (string2 )  trim    去除前端空格或后端空格

#数学相关函数
ABS (number2 )    绝对值
BIN (decimal_number )    十进制转二进制
CEILING (number2 )    向上取整
CONV(number2,from_base,to_base)    进制转换
FLOOR (number2 )    向下取整
FORMAT (number,decimal_places )    保留小数位数
HEX (DecimalNumber )    转十六进制
LEAST (number , number2  [,..])    求最小值
MOD (numerator ,denominator )    求余
RAND([seed])    RAND([seed])

#日期相关函数
CURRENT_DATE (  )    当前日期
CURRENT_TIME (  )    当前时间
CURRENT_TIMESTAMP (  )    "当前时间戳"
DATE_ADD (date2 , INTERVAL d_value d_type )    在date2中加上日期或时间
DATE_SUB (date2 , INTERVAL d_value d_type )    在date2上减去一个时间
DATEDIFF (date1 ,date2 )    两个日期差(结果是天)
TIMEDIFF(date1,date2)    两个时间差(多少小时多少分钟多少秒)
NOW (  )    当前时间
YEAR|Month|DATE (datetime )    返回datetime的某个部分

#其他函数
USER()    查询用户
DATABASE()    数据库名称
MD5(str)    为字符串算出一个 MD5 128比特检查和,通常用于对应用程序使用到的表的某个字段(比如用户密码)加密（其实是计算数据的特征码）
PASSWORD(str)    从原文密码str 计算并返回密码字符串,通常用于对mysql数据库的用户密码加密

#在创建表的同时添加自动增长
例如：create table student (id int primary key auto_increment,name char(30),age int);

#已经存在的表 添加自动增长
例如：alter table student3 modify id int auto_increment;

#外键约束
·是一种保证数据完整性的约束，具体表现是：在从表中保存主表的主键（逻辑上先有哪张表，哪张就是主表）
alter table [从表名] add foreign key([关联列名]) references 主表名([关联列名]);
·添加约束之后的变化：
    ·如果从表中有数据参照着主表的某个主键，那么这个主键对应的数据将不能直接删除（若一定要删除，则需先把从表对应的数据删除，再删除该主键）。
    ·如果在从表中插入一个新的数据，那么这个新数据的外键的值必须已经存在于主表中。
    ·外键尽量不添加，有外键，会降低一些性能。

#级联操作的作用：
    ·当主表发生了 update 或 delete 事件时，从表也相应地更新或删除。
    ·相应的动作有：
        ·CASCADE 同步更新或删除
        ·NO ACTION 不允许执行更新或删除
        ·SET NULL 当主表中某个主键被删除时，从表中将外键设为NULL。
        ·RESTRICT 拒绝对父表的删除或更新操作

#数据库的范式：是设计数据库的规范（一共有8种1NF,2NF,3NF,BCNF,4NF,5NF,DKNF,6NF，每一层都更加严格，如果满足了n+1层，则必然满足n层）
    ·基本的1NF,2NF,3NF
    ·1NF：表中的每一列都是原子性的，不可再分的。
    ·2NF：每一列都完全依赖主键（所以每个表都必然有自己的主键）。
        ·在设计时具体表现为不要把不相关的数据放到同一个表中。
        ·依赖：现有两个值 X,Y，若 Y 的值由 X 来决定，此时称为 Y 依赖于 X。
    ·3NF：每一列都与主键直接相关，而不是间接相关。
        ·任何非主键都不依赖于其他非主键。（即一个字段完全不依赖于其他字段，函数依赖也不行）
        ·在设计时具体表现为不要在一个表中保存其他表的非主键字段。
    ·为满足某种商业目标，数据库性能比规范化数据更重要。

#E-R图（Entity-Relation）：将数据库中的实体与它们之间的关系用图来描述
    ·矩形代表实体
    ·椭圆代表属性
    ·菱形代表关系

#多表查询：同时在多个表中查询数据
    ·笛卡尔积查询：select *from table1,talbe2;        (此方法会产生大量的无用重复数据，所以用的不是很多)。
    ·内连接查询：select *from table1 inner join table2 on 条件;（在不添加条件的情况下，内连接查询与笛卡尔积查询得到的结果是一样的）
    ·左内连接：select *from table1 left join table2 on 条件; （右内连接与此相反，关键词为right）
    ·全连接：mysql中并不支持，但可以利用union关键字把左右连接合并到一起，实现全连接的效果。

#关键字：union
    ·union --> 合并查询，可将多个表查询结果进行合并（不包括重复行）
    ·union all -->与union不同，此项包括重复行
    ·注：被合并的表的结构必须一样。
    ·例如：select *from table1 union select*from table2;

#视图：
    ·是一张虚拟的表，表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上。
    ·视图中不存放数据，数据存放在视图所引用的原始表中，一个原始表，根据不同用户端的不同需求，可以创建不同的视图。

#创建视图：create view view_name as select语句;

#删除视图：drop view [if exists] view_name;

#查看视图：select 字段1，字段2... from view_name;（其实是先执行了创建视图时的sql语句，然后对这个sql语句的结果进行查询）

#事务：即一组sql语句的集合，具有以下特点
    ·原子性：指这一组sql是不可分割的一个整体，要么都执行成功，要么都失败。
    ·一致性：当事务完成时，数据必须处于一致状态。
    ·隔离性：并发事务之间彼此隔离、独立，即a事务的操作不会受到b事务的影响。（具体的隔离级别可以手动调整）
    ·持久性：当事务执行完成后，事务对数据的修改都是持久性的，不可恢复的。

#事务的使用：
    ·开启一个事务：begin|start transaction;
    ·事务提交：commit;
    ·事务回滚：rollback;（默认回滚到事务开始前的状态）
    ·为事务创建保存点：savepoint point_name;  （可以利用该点，让事务在回滚到指定位置）
    ·回滚事务至指定保存点：rollback to point_name;
    （注：如果有多个保存点，可以不断向前回滚，但无法向后前进，一旦回滚，那么该保存点之后的代码将不复存在。）
    （默认情况下，每一条sql都是一条单独的事务，默认自动提交。可以通过set autocommit=0，关闭自动提交）

#查看当前各变量情况：show variables [like '变量名'];

#事务的隔离级别：
（事务默认是可以并发执行的，可能发生，
脏读：读取到另一个事务未提交的数据；
不可重复读：两次对同一记录的查询结果不一致，即一个事务在查询，另一个事务在更新或修改；
幻读：对同一表中的查询结果数量不一致，即一个事务在查询，另一个事务在添加或删除；）
    ·设置隔离级别分两种：全局 与 会话级
    ·set 变量名=级别;
    ·例如：set global_isolation|isolation read-committed;
    ·查看tx_isolation当前的隔离级别：select @@global.tx_isolation,@@tx_isolation;

#mysql中的变量
    ·全局变量
    ·会话级变量
    ·局部变量

#存储过程：是一组sql语句集，它与java中方法很相似，也有输入参数，输出参数
    ·使用场景：通常情况下，我们需要把业务逻辑放在客户端处理，例如：注册新用户：
        ·首先发起一个请求，将要注册的用户名发给服务器进行查询；
        ·服务器将查询结果返回客户端；
        ·客户端根据结果判断是否可以注册；
        ·如果可以，则再发起请求，向数据库中插入数据；
        ·服务器返回插入结果。
        （此时需要与数据库服务器进行多次通讯，如果网络状态很差，将会需要很长时间，这时候就可以使用“存储过程”来将原本需要在客户端处理的逻辑放在数据库服务器中，服务器只需要返回一个注册成功或失败即可，大大减少了通讯次数。）
    ·缺点：不同数据库的“存储过程”大不相同，即不可移植，重用性太低。
    ·“存储过程”使用方法：
        ·创建：create procedure pro_name(p_type p_name data_type)
                begin
                sql语句
                end + 结束符

            ·p_type 表示参数的输入输出类型 有 in out inout;
            ·p_name 参数名
            ·data_type 数据类型
            ·注：在过程中，一行结束使用“;”
            ·在存储过程中可以插入任意的sql语句。
            ·为变量赋值需要使用 set 关键字;
            ·由于mysql默认结束符就是“;”，而存储过程也使用“;”来表示结束，
            所以需要使用 “delimiter + 结束符” 更换结束符。
        ·调用：使用关键字 call
            ·例如： call pro_name(参数);
            ·如果有返回值，需要用变量接收，定义变量的方式 set @变量名[=默认值];
            ·定义局部变量：declare 变量名 类型 default 值;
            ·定义的变量在断开连接后被释放。
        ·IF语句：
            ·if 条件1 then 语句1；elseif 条件2 then 语句2；else 语句3；end if；
        ·CASE语句：
            ·case 变量名 when 值1 then 语句1;when 值2 then 语句2;when 值3 then 语句3;
            else 语句4; end case;
        ·WHILE语句：        
            ·WHILE 条件 do
            语句。。。
            end WHILE；
        ·LOOP语句：无条件循环，需自己定义结束语句（添加if条件，并执行 “leave 循环名”）
        (可以用iterate 跳过本次循环)
            ·循环名：LOOP
            语句。。。
            end LOOP 循环名;
        ·REPEAT：额，也用于循环，流程与java中的dowhile相似
            ·repeat
            语句。。。
            until 结束条件 end repeat;
    ·存储过程中的条件定义与错误处理：
        ·条件定义：可以给系统已存在的错误指定一个方便记忆的名字。（但该条件定义必须被包含在一个存储过程中）
            ·语法：DECLARE error_name CONDITION FOR 错误码|状态码;
        ·错误处理：在执行存储过程中，发生了某个错误时的处理方法。有三种处理方法：
            ·CONTINUE（跳过错误继续执行）
            ·EXIT（立即退出）
            ·UNDO（回滚 mysql暂不支持）
            ·语法：DECLARE handtype HANDLER FOR condition_name|error_code|error_value 处理语句...

#游标CURSOR
    ·它是一个类似光标的指针，可以在表中行间移动，每次向后移动一行。
    ·作用：可以逐行取出数据，类似Java中的迭代器。
    ·它也是只用于存储过程中的。
    ·创建语法：DECLARE cur_name CURSOR FOR SELECT语句;
    ·游标声明后需要先用OPEN cur_name;来打开。
    ·打开后使用 FETCH 语句来从结果中获取一个数据，可以配合into，将获取到的数据存储至变量中
        ·例如：FETCH myCursor INTO name;
    ·使用完毕后用CLOSE cur_name;来关闭游标。
    ·注：变量要定义在游标定义之前，游标要定义在处理程序定义之前，变量名不要和目的表中的字段名重复。

#触发器：也是一段与表有关的mysql程序，当这个表发生了某种事件时，将会自动执行相应的触发器程序。
    ·支持的事件：UPDATE、INSERT、DELETE
    ·支持的时间点：事件发生前(BEFORE)、事件发生后(AFTER)。
    ·在触发器中可以访问到将被修改的那一行数据，根据事件不同，能访问的数据也不同：
        ·UPDATE 可用OLD访问旧数据，NEW访问新数据；
        ·INSERT 可用NEW访问新数据；
        ·DELETE 可用OLD访问旧数据。
    ·创建语法：
        ·CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt...;
    ·删除某个触发器：
        ·DROP TRIGGER trigger_name;
        ·注意：外键不能触发事件，主表删除了某个主键，从表也会相应删除，但不会执行触发器。
        ·触发器中不能使用事务。
        ·相同时间点的相同事件的触发器不能同时存在。

#定义函数：
    ·CREATE FUNCTION f_name(parameters...)
     returns dataType return value;
    ·说明：parameters 只能是in输入参数，格式： 参数名 类型，且方法必须有返回值（只能返回一个值）。
    ·函数一般不涉及数据的增删改查，就是一个通用的功能。
    ·调用自定义的函数，与调用系统函数的方法一致，使用 select 获得返回值。
    ·函数中不能使用sql语句。

#MySQL用户管理：
    ·创建一个本地用户monty，密码‘some_pass’，并给它所有权限：
        GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
    ·ALL PRIVILEGES：所有权限；
    ·*.*：所有数据库的所有表；
    ·TO 'monty'@'localhost'：用户名和主机名；
    ·IDENTIFIED BY 'some_pass'：密码设为‘some_pass’；
    ·WITH GRANT OPTION：可以再授权给其他用户（此句不写则不可以再授权）。
    ·创建一个用户u1，密码：admin ，只能对mydb01.sanguo表进行增删改查，且在任何电脑('%')上都可以登录:
        ·GRANT DELETE,UPDATE,INSERT,SELECT ON mydb01.sanguo TO 'u1'@'%' IDENTIFIED BY 'admin';
    ·删除某用户：
        ·DROP user u_name@host;(直接操作系统user表，删除指定用户的记录也可以)。
    ·收回指定的权限：
        ·REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] {tbl_name | * | *.* | db_name.*} FROM user@host [, user] ...;
        REVOKE ALL PRIVILEGES, GRANT OPTION FROM user@host [, user] ...;
        ·priv_type：要收回的权限；
        ·db_name.tbl_name：从哪个数据库的哪个表中，可使用通配符*；
        ·FROM user：从谁的手里收回    
    ·查看某用户的权限：
        ·SHOW GRANTS FOR 用户名@主机;（主机可以使用'%'来表示全部主机）。

关系型数据库 常见优化手段
1.为经常查询的表建立索引
2.分表 
垂直分    当一个表中字段比较多的时候 可以将其拆为多个表 使得单个表中字段减少
水平分    当一个表中的记录行数太多时 可以将数据分散到多个表中
3.sql 语句优化    例如:不要查询你不需要的任何数据 指定需要的列;select name from table