软件测试-Mysql数据库4

1. LEFT JOIN ：左外连接，（两个表及其以上的多表查询），左表的数据都全部显示在结果中，右表中符合条件的数据显示在查询结果中，不符合的以 null 代替。
   例如 以 表名1 为左表

 SELECT 别名1.字段名1，别名2.字段名2
 FROM 库名.表名1  [AS] 别名1
 LEFT JOIN 库名.表名2 [AS] 别名2
 ON 别名1.字段名3=别名2.字段名3  
 
 三表则加上下面一段代码
 
LEFT JOIN 库名.表名3 [AS] 别名3
 ON 别名3.字段名4=别名2.字段名5 

若有条件，则最后以 where 结尾。

WHERE 字段名6=字段值6;

2. RIGHT JOIN：右外连接，（两个表及其以上的多表查询），右表的数据都全部显示在结果中，左表中符合条件的数据显示在查询结果中，不符合的以 null 代替。
   例如 以 表名1 为右表

 SELECT 别名1.字段名1，别名2.字段名2
 FROM 库名.表名2  [AS] 别名2
 RIGHT JOIN 库名.表名1 [AS] 别名1
 ON 别名2.字段名3=别名1.字段名3  
 
 三表则
 SELECT 别名1.字段名1，别名2.字段名2
 FROM 库名.表名3  [AS] 别名3
 RIGHT JOIN 库名.表名2 [AS] 别名2
 ON 别名2.字段名3=别名3.字段名3  
 RIGHT JOIN 库名.表名1 [AS] 别名1
 ON 别名1.字段名4=别名2.字段名5 

若有条件，则最后以 where 结尾。

WHERE 字段名6=字段值6;

3. FULL JOIN 全外连接（mysql 没有）
   oracle 语法：
   例如
   SELECT 别名 1.字段名 1，别名 2.字段名 2
   FROM 库名.表名 1 [AS] 别名 1
   FULL JOIN 库名.表名 2 [AS] 别名 2
   ON 别名 1.字段名 3=别名 2.字段名 3
   WHERE 字段名 4=字段值 4;

   左右两个表的所有数据全部显示出来，没有的以null 代替。

4. UNION 联合查询
   select name1 from student1
   UNION
   select name2 from student2;
   展示两个表的所有数据 （注：字段类型一定要相同）。

5. 自连接 ，顾名思义，即自己连接自己。
   例如
   SELECT n.id, m.name
   from student n , student m
   ON n.s_id=m.t_id;
   将一个表看成两个表，通过为表命名不同的别名进行区分这两个表。

6. 用户权限即管理
   在 mysql 中,只有超级用户(root)才可以创建用户，添加一个新用户，共分为三步：
   步骤一：添加用户
   步骤二：为用户授权
   步骤三：使授权生效

   GRANT 权限列表 ON 库名.表名 TO 用户名@主机 IP IDENTIFIED BY “密码”;

   例如

   INSERT INTO mysql.user (HOST,USER,PASSWORD) VALUES(“IP 地址”,“用户名”,PASSWORD(“密码”)); – 创建用户

   mysql.user：这是用户表，不可更改。
   IP 地址：若为 “%”，则该用户可在任何一台电脑登录数据库。
   若为 “localhost”，则该用户只能在该电脑登录数据库。
   若为 ip地址，则该用户只能在指定的电脑登录数据库。
   PASSWORD() 加密函数

   GRANT SELECT,INSERT ON *.* TO test@ localhost IDENTIFIED BY “123456”;

   SELECT,INSERT：管理员给该用户授予的权限。
   *.* ： * 代表所有，*.* 代表该用户对所有的库的所有表有权限，
   school.student 代表对学校库的学生表有权限。
   test：用户名，与创建用户时相同。
   localhost：ip地址，与创建用户时相同。
   “123456”：用户密码，与创建用户时相同。

   FLUSH PRIVILEGES; – 刷新授权表

7. 权限列表
   – 权限列表如下：
   – (1)create：创建库、创建表、创建视图、创建存储过程等
   – (2)drop：删除库、删除表、删除视图、删除存储过程、删除字段等
   – (3)alter:修改库、修改表等
   – (4)insert：往表中插入数据
   – (5)select : 查询表中的数据
   – (6)update：修改表中数据
   – (7)delete:删除表中数据
   – 特殊权限：
   – all privileges:所有权限 – privilege 权限的意思
   – usage：没有权限，具有该权限的用户只能连接数据库，不能做其他任何操作

8. 查看用户权限
   SHOW GRANTS FOR 用户名@主机 IP; 查看指定用户的权限
   主机IP 若为 “%”,则 可以查看 任意一台主机上访问数据库的用户的权限。
   SHOW GRANTS; 查看当前登录的用户。
   SHOW GRANTS FOR 用户名@localhost; 查看本地用户。

9. 密码修改（root 用户可以修改任何用户的密码。）
   第一步：
   UPDATE mysql.user SET PASSWORD=PASSWORD(“新密码”)
   WHERE USER=“用户名” AND HOST=“IP 地址”;
   第二步：
   FLUSH PRIVILEGES；（刷新用户表）
   如果对mysql数据库进行更新，特别是对user表进行操作的，必须刷新用户表

10. 权限收回
    REVOKE 权限 ON 数据库.数据表 FROM ‘用户名’@‘数据库所在主机 IP’

11. 用户删除
    DROP USER 用户名@主机 IP 地址;

12. 日期函数
    SELECT SYSDATE(),NOW(); – 获取系统当前的日期时间
    SELECT CURTIME(); – 获取系统的当前时间
    SELECT WEEKDAY(SYSDATE()); – 判断给定的时间日期属于礼拜几
    SELECT WEEKDAY(“2018-11-11”); – 判断给定的时间日期属于礼拜几
    SELECT MONTHNAME(“2018-11-11”); – 判断给定的日期时间属于几月份
    SELECT DATE_FORMAT(“2018-11-11”,"%Y:%m:%d"); – 按照指定的格式显示给定的日期时间
    SELECT DATE_FORMAT(NOW(),"%Y-%m-%d %H:%i:%s"); – 按照指定的格式显示给定的日期时
    间
    SELECT DATE_ADD(“1997-12-31 23:59:59”,INTERVAL 1 SECOND); – 将给定的日期时间增加 1
    秒
    SELECT DATE_ADD(“1997-12-31 23:59:59”,INTERVAL 1 DAY); – 将给定的日期时间增加 1 天
    SELECT DATE_ADD(“1997-12-31 23:59:59”,INTERVAL 1 MONTH); – 将给定的日期时间增加 1 月
    SELECT DATE_ADD(“1997-12-31 23:59:59”,INTERVAL -1 MONTH); – 将给定的时间减少 1 个月
    SELECT DATE_ADD(“1998-01-01 00:00:00”, INTERVAL “-1 10” DAY_HOUR); – 在当前给定的时
    间基础上后退 1 天 10 小时

13. 事物
    BEGIN 开始一个事务
    ROLLBACK 事务回滚
    COMMIT 事务确认

14. 其他
    SELECT UPPER(字段名1),LOWER(字段名2)FROM 表名;
    UPPER：全部改大写，
    LOWER：全部改小写。

    SELECT UPPER(字段名1), LOWER(字段名2) ,
    CONCAT(字段名3,字段名4) FROM 表名;

    UPPER：小写变大写
    LOWER：大写变小写
    CONCAT：连接字段值
    SUBSTRING：截取字符串
    例如
    select SUBSTRING(字段名,2,3) from 表名。
    从第二个字符截取，取三个。
    LENGTH(字段名):获取字符长度。
    INSTR(字段名,“T”):获取T在该字段值的位置。
    TRIM( 字段名 ):去除前后空格
    ROUND(123.456,2)：四舍五入，保留两位小数。
    TRUNCATE(123.456,2)：仅保留两位小数。
    MOD(115,10)：取余数。