MySQL

mysql -h 主机IP -P端口号 -u 用户 -p密码
 

新建数据库

新建表                      

添加数据：table添加一行数据

 数据库三层结构：         //MySQL是数据库包含3部分

数据库管理系统：DBMS
        数据库：DB
                表：表的本质仍是文件

终端连接到计算机，数据库软件的端口监听，运行mysql数据库的操作

表的结构 

行row：一条记录              //Java中一行记录往往使用1个对象表示
列/字段column

DDL：数据定义语言         创建create表、库
DML：数据操作语言         增加insert、删除delete、修改update
DQL：数据查询语言         查询select
DCL：数据控制语言         管理数据库的用户权限grand、revoke

创建数据库：CREATE DATABASE [IF EXISTS]  数据库名 [CHARACTER SET 字符集 COLLATE 校对规则];                              
                                                        //默认------->字符集：utf8    校对规则：utf8_general_ci 不区分大小写                                 //推荐校对规则: 区分大小写utf8_bin 
删除数据库：DROP DATABASE [IF EXISTS] 数据库名；
展示数据库：SHOW DATABASES;
展示创建数据库时的语句： SHOW CREATE DATABASE 数据库名;
备份数据库：mysqldump -u 用户名-p密码 -B 数据库1 数据库2 > [路径]文件名.sql                     //Dos里，mysqldump命令在bin下
备份表：       mysqldump -u 用户名-p密码 数据库 表1 表2 > [路径]文件名.sql 
恢复数据库：source [路径]文件名.sql                                                                                          //MySQL里
 

创建表：CREATE TABLE 表名 (字段名 字段类型 字段 类型 [NOT NULL DEFAULT 默认值]，字段名 字段类型，…… )
                                                                                                                                [CHARACTER SET 字符集 COLLATE 校对规则 ENGINE 存储引擎];
                                                                                                                             不写时默认：所在数据库字符集、校对规则 
添加列：ALTER TABLE 表名 ADD (列名1 类型，列名2 类型) AFTER 字段；                           //在字段后添加该列
修改列：ALTER TABLE 表名 MODIFY (列名 类型)； 
删除列：ALTER TABLE 表名 DROP 列名；
查看表结构：DESC 表名；                                      //查看哪些字段
修改表名：RENAME TABLE 表名 TO 新表名；
修改表字符集：ALTER TABLE 表名 CHARACTER SET 字符集；
修改列名：ALTER TABLE 表名 CHANGE 列名 新列名 新属性；

添加数据：INSERT INTO 表名 (列名1,列名2,……）VALUES (值1,值2,……)； 
                        //字符、日期类型要包含在单引号当中       '    '
                        //若字段允许插入空值，则可以插入空值：NULL                                          //
                        //添加多条数据   VALUES (),(),()               //每个()代表一条数据
                        //若给所有字段添加数据，可省略： (列名1,列名2,……）
                        //当不给某个字段值时，有默认值会用默认值，没有默认值报错        //每个字段没有指定NOT NULL，则默认给NULL



修改数据：UPDATE 表名 SET 字段1=值/表达式，字段2=值/表达式，…… [WHERE 条件]          //若没有WHERE则所有行的该字段都修改



删除数据：DELETE FROM 表名 [WHERE 条件]                        //若没有WHERE则所有行都删除



查询数据：SELECT [DISTINCT] *或字段1，字段2，…… FROM 表名                     //DISTINCT：对查询结果去重          *：所有字段
                        对列进行运算：SELECT 字段，表达式，…… FROM 表名
                        对列使用别名：SELECT 字段 AS 别名，表达式 AS 别名，…… FROM 表名                             
                        1)WHERE子句经常使用的运算符：SELECT 字段，表达式，…… FROM 表名  [WHERE 条件] 
                                比较：>  <  >=  <=  =  !=            不等于还可以用<> 
                                           BETWEEN…AND…   在区间内
                                           IN(数值，数值)          在集合之中
                                           LIKE XXX                模糊查询                %：0到多任意字符      _：单个任意字符  
                                           NOT LIKE XXX        
                                           IS NULL                是否位空
                                逻辑：AND           多条件同时成立
                                           OR             多条件任一成立
                                           NOT           不成立
                                 //判断是否为null 要使用is null, 判断不为空使用is not

                        2)ORDER BY子句列排序：SELECT 字段，表达式，…… FROM 表名  ORDER BY 列名或别名 asc升序或desc降序        //默认升序
                                   （排序后再排序）：SELECT 字段，表达式，…… FROM 表名  ORDER BY 列名1 排序，列名2 排序，列名3 排序……                                                          3)GROUP BY子句：分组查询（相同的lie'ming分为一组）：                       SELECT 字段，表达式，…… FROM 表名  GROUP BY 列名1，列名2，……
                        4)HAVING子句列分组后结果后进行过滤：SELECT 字段，表达式，…… FROM 表名  GROUP BY 列名 HAVING 条件
                        5）顺序：GROUP BY        HAVING        ORDER BY        LIMIT
                         6）指定某个表的列：表名.列名


分页查询：一个表，分多次查询
        SELECT语句 LIMIT 开始数, 行数;                                //从开始数+1行开始取，取多少行
                            //LIMIT 每页显示记录数* (第几页-1) , 每页显示记录数

        


多表查询：查询的结果来自多个表
        SELECT * FROM 表名1，表名2，……                 //把表1的每行拿出来，配接表2的每一行，再拼接表3每一行……       总行数：表1行*表2行*表3行*……       //笛卡尔集
                                   //所以多表查询重点在于过滤条件WHERE，即表1和表2同列名的哪行 值是相同的         WHERE 表1.列 = 表2.列

        1）自连接：将同一张表当作两张表使用           SELECT * FROM 表名1 别名1，表名1 别名2                     //表别名：表名 列名         列别名：列名 AS 别名
        外连接：
                左外连接：左侧的表完全显示（即使左表与右表没有完全匹配）        SELECT …… FROM 表1 LEFT JOIN 表2 ON 条件     //，改成LEFT JOIN    WHRER改成ON
                右外连接：右侧的表完全显示（即使左表与右表没有完全匹配）        SELECT …… FROM 表1 RIGHTJOIN 表2 ON 条件     //，改成RIGHTJOIN    WHRER改成ON


子查询：指嵌入在其它sql 语句中的select 语句,也叫嵌套查询    //select语句作为子语句，其查询结果运用在其他SQL语句

        单列子查询：
               单行子查询：子查询语句只返回一行数据
               多行子查询：多行子查询指返回多行数据的子查询：使用关键字in             //    xxx in 子查询语句
                        在多行子查询中使用all 操作符：         xxx 比较操作符 ALL(子语句)                        比全部
                        在多行子查询中使用any操作符：        xxx 比较操作符 ANY(子语句)                      比任一
         子查询当做临时表使用
        多列子查询：（字段1，字段2）=（子查询:select 字段1，字段2 form……）


合并查询：
        union all：就是将两个查询结果合并，不会去重            SELECT1  UNION ALL   SELECT2
        union：                                                会去重                SELECT1  UNION   SELECT2
        
        
     

 函数：

统计函数：
COUNT：返回查询的结果有多少行        SELECT COUNT(*或字段) FROM 表 WHERE 条件       //*：满足条件的行数有多少个     字段：满足条件的列有多少个，排除NULL
SUN：返回查询结果行的和（一般用在数值列）    SELECT SUN(字段或表达式)，SUN(字段或表达式)，……  FROM 表 WHERE 条件
AVG：返回查询结果行的平均分                             SELECT AVG(字段或表达式)，AVG(字段或表达式)，……  FROM 表 WHERE 条件
MAX：返回查询结果行的最大值                             SELECT MAX(字段或表达式)，MAX(字段或表达式)，……  FROM 表 WHERE 条件
MIX：返回查询结果行的最小值                              SELECT MIX(字段或表达式)，MIX(字段或表达式)，……  FROM 表 WHERE 条件

字符串相关函数：
CHARSET：返回字串字符集                                        SELECT CHARSET(列) FROM 表;
CONCAT：连接字串, 将多个列拼接成一列                   SELECT CONCAT(列, ' 字符串', 列，……) FROM 表;
INSTR (string ,substring ) ：返回substring字符串 在string字符串 中出现的位置,没有返回0                SELECT INSTR('abcd', 'abc') FROM DUAL;
UCASE：转换成大写                                                    SELECT UCASE(字段) FROM 表;
LCASE：转换成小写
LEFT (string2 ,length )：从string2 字符串(列)中的左边起取length 个字符                SELECT LEFT(列, 个数) FROM 表;
RIGHT(string2 ,length )：                                      右边
LENGTH：统计字节长度                                                SELECT LENGTH(列) FROM 表;
REPLACE (str ,search_str ,replace_str ) ：在str 中用replace_str 替换search_str                    SELECT ename, REPLACE(字段,'被替换', '替换成') FROM 表;
STRCMP (string1 ,string2 )： 逐字符比较两字串大小，相等返回0，大于0返回1，小于返回-1
SUBSTRING (str , position [,length ])：从str 的position 开始【从1 开始计算】,取length 个字符                SELECT SUBSTRING(字段, 开始位置, 个数) FROM 表;
LTRIM (string2 )                去左边空格
RTRIM (string2 )                   右边
TRIM(string)                          两端


数学函数：
ABS(num) ：绝对值                                                                          SELECT ABS(-10) FROM DUAL;
BIN (decimal_number )：十进制转二进制                                        SELECT BIN(10) FROM DUAL;
CEILING (number2 ) ：向上取整, 得到比num2 大的最小整数         SELECT CEILING(-1.1) FROM DUAL;
FLOOR (number2 ) ：向下取整,得到比num2 小的最大整数             SELECT FLOOR(-1.1) FROM DUAL;
FORMAT (number,decimal_places ) ：保留小数位数(四舍五入)        SELECT FORMAT(78.125458,2) FROM DUAL;
CONV(number2,from_base,to_base) ：进制转换，把number2当作from_base进制，转成to_base进制                SELECT CONV(8, 10, 2) FROM DUAL;
HEX (DecimalNumber ) ：转十六进制
LEAST (number , number2 ，……) ：求最小值
MOD (numerator ,denominator ) ：求余
RAND() ：返回随机数其范围为0 ≤ v ≤ 1.0               RAND(数值) 返回固定的随机数其范围为0 ≤ v ≤ 1.0


时间日期函数：
CURRENT_DATE ( ) 当前日期                                        SELECT CURRENT_DATE() FROM DUAL;
CURRENT_TIME ( )当前时间                                          SELECT CURRENT_TIME() FROM DUAL;
CURRENT_TIMESTAMP ( ) 当前时间戳                         SELECT CURRENT_TIMESTAMP() FROM DUAL;
NOW()                      当前日期时间                                  SELECT NOW() FROM DUAL;
            这四个DATE函数日期类型可以是date、datetime、datestamp：
DATE(datetime)                只返回日期
DATE_ADD(date2, INTERVAL 数值 单位)      在date2中加上日期或时间         //单位：YEAR、MINUTE、SECOND、DAY、HOUR等
DATE_SUB(date2, INTERVAL 数值 单位)      在date2中减去日期或时间         //单位：YEAR、MINUTE、SECOND、DAY、HOUR等
DATEDIFF(date1, date2)                                返回日期差(天) 
 
TIMEDIFF(date1, date2)                                返回时间差
YEAR|Month|DAY|DATE (datetime )                得到年、月、日、日期
unix_timestamp() : 返回的是1970-1-1 到现在的秒数
FROM_UNIXTIME() : 可以把一个unix_timestamp 秒数[时间戳]，转成指定格式的日期                                 //'%Y-%m-%d''%Y-%m-%d %H:%i:%s'格式是规定好的，表示年月日时分秒
                                                                SELECT FROM_UNIXTIME(1618483484, '%Y-%m-%d''%Y-%m-%d %H:%i:%s') FROM DUAL;       

             

加密和系统函数：                           
USER() ：查询当前使用的用户                            SELECT USER() FROM DUAL;          //格式：用户@IP
DATABASE()：查询当前使用数据库名称
MD5(str) ：为字符串算出一个MD5 32位的字符串，常用(用户密码)加密 
PASSWORD(str)：加密函数, MySQL 数据库的用户密码就是PASSWORD 函数加密     
                                                             


流程控制函数：          
IF(expr1,expr2,expr3) ：如果expr1 为True ,则返回expr2 否则返回expr3                                                   
IFNULL(expr1,expr2)： 如果expr1 不为空NULL,则返回expr1,否则返回expr2                  
SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4  …… ELSE expr5 END;                              // 类似多重分支.
                //如果expr1 为TRUE,则返回expr2,如果expr2 为true, 返回expr4, 否则返回expr5 
 

 在创建数据库、表时，为了规避关键字，可以使用反引号         `xxxx`

字段类型/列类型/数据类型：

 常用：int、double、decimal、char、varchar、text、datetime、timestamp
                     //decimal:位数最大65，小数点位数最大30，默认位数10，小数点位数0
                字段范围：
                     //char(4)保存长度是固定4个字符        //4字符属于0-255 字符范围        
                     //varchar(4)保存长度变长4个字符。  //4字符属于是65535字节范围， 字符可以用的范围是：（65535-3 字节）/ 3         
                                        //-3，这三个字节是记录该字段长度大小的，不能用           /3是utf-8每个字符3个字节
                //当保存长度固定的数据时，推荐char---->查询速度快。如：通过md5加密后固定32位大小
                //timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP        //如果是不填写，则用当前时间，修改时自动更新
            MYSQL中日期类型可以直接比较

表复制：
        按照表1的结构创建表2        CREATE TABLE 表2 LIKE 表1;
        表1的记录复制到表2        INSERT INTO 表2 (列名1,列名2,……）SELECT 列名一,列名二,……FROM 表1
        自我复制                         INSERT INTO 表名 SELECT * FROM 表名




MYSQL约束：用于确保数据库的数据满足特定规则  5种
        1）primary key主键：                          字段名 字段类型 primary key
                                                                     字段名 字段类型，字段名 字段类型，primary key(字段名，……)
                                        该列的值不能重复(唯一值) 
                                        不能是NULL
                                        主键只有一个，但可以复合主键(多个字段)：CREATE TABLE 表名（字段1 类型，字段2 类型，字段3 类型，primary key(字段1，字段2)）；
                        
        2）not null 不能是空值：                          字段名 字段类型 not null
        3）unique 唯一：                                      字段名 字段类型 unique                   
                                        该值不能重复
                                        若没有not null，可以有多个NULL值
        4）foreign key 外键：                              FOREIGN KEY (本表字段) REFERENCES 主表(主表的主键/unique)
                                                          //CREATE TABLE 从表（字段1 类型，字段2 类型，字段3 类型，foreign key(字段1）REFERENCES 主表（主表的主键/unique）)；
                                //定义主表1和从表2关系(即2个表，有一列相同数据)：如果表2中的该列是必须存在的，则可以做成一个外键，表2外键列的值必须在表1对应列中存在
                                //删掉表2外键列的某个值的行，表1的对应列该值的行找不到对应也会删除
                                                外键要建在从表，主表要有主键primary key或unique约束，
                                                只有innodb类型的表才支持外键
                                                外键字段类型和主表的字段：类型一致，可以长度不一致
                                                外键字段的值，必须在主键字段出现 或 null（前提是外键字段允许位NULL）
                                                一旦建立外键关系，那么主表不能随便删除
                                               
        5）check：满足条件                                字段 类型 check (条件)                       //sal DOUBLE CHECK ( sal > 1000 AND sal < 2000)                  //低版本MYSQL不支持check




自增长：                字段 整型 primary key AUTO_INCREMENT 
                INSERT INTO 表 (字段1，字段2，……) VALUES(NULL, NULL, ……);            //填写NULL为自增长
                INSERT INTO 表 (字段2，……) VALUES(值, ……);                                    //此时字段1也是自动增长
                INSERT INTO 表 VALUES(NULL, 值, 值);                                                  //填写NULL为自增长
                1）自增长一般配合主键使用，也可以配合unique使用
                2）自增长修饰字段为整形（小数也可以但极少）
                3）自增长默认从1开始，也可以设置开始值        alter table 表名 AUTO_INCREMENT= 默认值
                4）指定值优先于自增长值


索引：                                                                                                                                                                                         //只会增加创建索引的列作为条件时的速度
        创建索引：CREATE INDEX 索引名 ON 表名 (字段)        //ON 表名 (字段) 表示在哪个表的哪个列创建索引             
                          ALTER TABLE 表名 ADD INDEX 索引名 (字段)
        没有索引时：全表扫描
        创建索引时：根据索引字段形成二叉树，查找时根据二叉树查询所以速度快
        缺点：文件变大了、对：修改、删除、添加有影响
        
    索引种类：
        1）主键索引：主键自动为索引                        1）建表时设主键，则自动为主键索引
                                                                                 2）ALTER TABLE 表名 ADD primary key (字段) 
        2）唯一索引：unique                                           //CREATE unique INDEX 索引名 ON 表名 (字段)
        3）普通索引：INDEX                                        1）CREATE INDEX 索引名 ON 表名 (字段)
                                                                                   2）ALTER TABLE 表名 ADD INDEX 索引名 (字段)
        4）全文索引：FULLTEXT        //适用于MyISAM存储引擎      //mysql全文索引不好用，一般开发都会用Solr或ElasticSearch框架


        查询表的索引：SHOW INDEX FROM 表
                                 SHOW INDEXES FROM 表
                                 SHOW KEYS FROM 表
                                 DESC 表
        删除索引：DROP INDEX 索引名ON 表                      //可以用上条查询一下索引的名
        删除主键索引：ALTER TABLE 表名 DROP primary key 
        哪些字段适合建立索引：1）频繁作为查询条件的字段        2）唯一性太差的不适合        3）更新频繁的不适合        4）不会出现在WHERE子句中的字段不要创建索引




事务：
        用于保证数据的一致性，由一组dml语句组成，该组语句要么全部成功，要么全部失败
        
        执行事务操作时，MySQL会在表上加锁，防止其他用户修改表的数据
        1）开始一个事务：               START TRANSACTION
        2）保存点名：设置保存点    SAVEPOINT 保存点名                      //保持点名：随便起一个
        3）保存点名：回退事务        ROLLBACK TO 保存点名                     //回退到保存点      注意：回到过去，那么现在与过去之间的保持点自然没有了 （姥姥没了，自然没有妈妈）
        4）回退全部事务                   ROLLBACK                                //回退到事务开始状态
        5）提交事务                          COMMIT            //最终提交，事务生效，不能再回退了，删除保存点，释放锁，数据生效。其他会话(连接)就可以查看到事物变化后的新数据

        默认情况下dml操作自动提交，不能回滚
        开启一个事务，没有创建保存点，可以默认回到开始的状态   ROLLBACK                                
        可以再事务中创建多个保存点，可以回退到某个保存点
        MySQL的事务机制需要innodb的存储引擎才可以使用，myisam存储引擎不支持
        开启一个事务：START TRANSACTION        或        set autocommit=off
        


事务的隔离级别：        
        多个连接开启各自事务操作数据库的数据时，数据库相同要负责隔离操作，以保证各个连接在获取数据时的准确性
        //即一个连接操作数据时，另外的连接据级别看到的数据执行程度是不一样的
        
        脏读：一个事务读取另一个事务未提交的改变
        不可重复读：事务中一个查询多次进行，由于其他提交事务所做的修改、删除导致每次返回的结果不一样
        幻读：事务中一个查询多次进行，由于其他提交事务所做的插入操作导致每次返回不同结果  

                                         该控制台事务是否能看到其他控制台事务的操作 的级别：
                                
        查看当前会话的隔离级别：SELECT @@tx_isolation;
        查看系统当前隔离级别：    SELECT @@global.tx_isolation
       设置当前会话的隔离级别：          SET SESSION TRANSACTION ISOLATION LEVEL 隔离级别;                   
        设置系统当前隔离级别：             SET GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别；

        加锁：如果发现有一张表正在被其他事务操作还没提交，会卡在此处代码(该执行会等待)。等其他事务提交完会再执行     //可能会出现等待超时报错哦

设置默认隔离级别：        //默认是：可重复读

事务的ACID特性：
        原子性：事务内不可分隔，要么都发生要么都不发生
        一致性：事务必须从数据库的一个一致性状态转换到另一个一致性状态          //事务是一个整体，提交后就改变了，才能开启另外一个状态(事务)
        隔离性：多并发用户访问数据库时，每个用户开启的事务不能被其他事务的操作数据干扰，多个并发事物之间要互相隔离
        持久性：一旦事务提交，对数据库的改变就是永久的

MySQL表类型、存储引擎
        MySQL表的类型由存储引擎决定，开发中根据需求选表的类型
        
        常用表类型：CSV、MEMORY、ARCHIVE、MRG_MYISAM、MyISAM、InnoDB       
                分为2大类：                事务安全型：支持事务  InnoDB
                                                    非事务安全型：不支持事务 其他5种
查看所有的存储引擎：SHOW ENGINES

 重点是MyISAM、InnoDB、MEMORY这3种：

不需要事务，知识处理增删改查-----MyISAM
需要事务-----InnoDB
暂时的数据，变化频繁，不用放到数据库中，只放内存就可以(如用户在线状态)-----MEMORY

 指令修改存储引擎：ALTER TABLE 表 ENGINE = 存储引擎

视图：
        需求：表里某些列比较私密
        是一个虚拟表，其内容由查询定义。其数据来自真实的表(基表)(可以有多个基表)。视图当作表使用：用户操作视图，会映射到对应的基表

创建视图：CREATE VIEW 视图名 AS SELECT语句
修改视图：ALTER VIEW 视图名 AS SELECT语句                 //更新视图，相当于重新定义一个视图
查看创建视图的指令：SHOW CREATE VIEW 视图
删除视图：DROP VIEW 视图1，视图2，……
查看视图：DESC 视图

                1）创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式: 视图名.frm)
                2）视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ]
                3）视图中可以再使用视图，数据仍来自于基表

 
视图使用：1）为了私密数据时        2）连接多个表时        3）利用映射灵活修改旧表，即旧表难以适应，就可以利用视图做中间层

MySQL用户管理
        MySQL有个数据库，该数据库名就叫 mysql，其中的user表---->用户都存在这里面
        表的重要字段：
                        host：该用户允许登录的位置
                        user：用户名
                        authentication_string：密码        //MySQL通过password()函数加密后的密码

创建用户：CREATE USER 用户名@允许登录地址 IDENTIFIED BY 密码                                         //用户名、地址、密码都是字符串
删除用户：DROP USER 用户名@允许登录地址
修改密码：
                        修改自己的密码：SET PASSWORD  = PASSWORD(密码);
                        修改他人的密码：SET PASSWORD FOR  用户名@允许登录地址 = PASSWORD(密码);


MySQL的权限：不同的数据库用户登录到DBMS，根据对应的权限，可以操作的数据库和数据库对象(表、视图、触发器)都不一样

给用户授权：GRANT 权限1，权限2，…… ON 数据库.对象名 TO 用户@允许登录地址 [identified by 密码]      
                                                                                *.*     所有数据库的所有对象
                                identified by 密码：若用户存在则修改密码            若不存在则创建用户
回收用户权限：REVOKE 权限1，权限2，…… ON 数据库.对象名 FROM 用户@允许登录地址
如果权限没有生效，可以使用权限生效指令：FLUSH PRIVILEGES

         若创建用户时没有指定Host，则为%，表示任何地址都可以登录           // CREATE USER 用户名
        create user 用户名@'192.168.1.%'                  //表示192.168.1.开头的IP都可以登录
        在删除用户的时候，如果host 不是%, 需要明确指定'用户'@'host 值'
                        若是%则可以这样删除：DROP USER jack    ------>   默认就是DROP USER 'jack'@'%'