学习笔记☞ MySQL（三）

1、索引分类

    1、普通索引
    2、唯一索引
    3、主键索引
    4、外键索引

2、普通索引

    1、使用规则 (index)

        1、一个表中可以有多个index字段
        2、字段的值可以有重复也可以有空值
        3、经常把做查询条件的字典设置为index字段
        3、index字段的key标志为：MUL

    2、创建

        1、创建表时创建index

create table t1(
... ...,
... ...,
idex(id),
idex(name));            

        2、在已有表中添加索引字段

            1、语法格式
                create index 索引名 on 表名(字段名);
                    #索引名一般和字段名一样

    3、查看

        1、desc 表名;  ->查看key标志位MUL
        2、show index from 表名\G; \G的作用是分组显示

    4、删除

        drop index 索引名 on 表名
        注意：
            删除普通索引只能一个一个删除

3、唯一索引(unique)

    1、使用规则

        1、一个表中可以有多高unique字段
        2、unique 字段的值不允许重复，可以为空值NULL
        3、unique的KEY标志是 UNI

    2、创建 (基本等同index创建)

        1、创建表时创建

            1、字段名 数据类型 unique

            2、unique(字段名)

                 unique(字段名2)

        2、已由表中创建

            CREATE UNIQUE INDEX 索引名 ON 表名 (字段名);

    3、查看、删除唯一索引

        desc 表名;
        show index from 表名;
        drop index 索引名 on 表名；

4、主键索引(primary key) && 自增长属性(outo_increment)

    1、使用规则

        1、一个表中只能有一个主键字段
        2、对应字段的值不能重复且不能为空值NULL
        3、主键字段的key标志位PRI
        4、把表中能够唯一标识一条记录的字段设置为主键，通常把表中记录编号的字段设置为主键

    2、创建主键(PRI)

        1、创建表时创建

            1、字段名 数据类型 primary key auto_increment(创建自增长主键)
            2、

        id int auto_increment,
        ……,
        primary key(id)auto_increment=100;  #auto_increment=100  设置自增长初始值
        primary key(id,name)    #复合主键(很少使用)，id 和name不能都重复

        2、删除主键限制

            1、先删除自增长属性
                alter table 表名 modify 主键字段名 属性 ;
            2、删除主键
                alter table 表名 drop primary key;
            3、在已有表中添加主键

       ALTER TABLE 表名 ADD PRIMARY KEY (字段名); #（无自增长属性）

    3、已有表中添加自增长属性      

       ALTER TABLE 表名 MODIFY 字段名 数据类型 AUTO_INCREMENT;

5、外键索引

    1、定义

        让当前表字段的值在另一个表的范围内选择

    2、语法格式

        foreign key(参考字段名)
        references 被参考表名(被参考字段名)
        on delete 级联动作
        on update 级联动作

    3、案例

表1、缴费信息表(财务)(主表)
    学号  姓名      班级      缴费金额
    1    唐伯虎    AID01       28000
    2    点秋香    AID01       20000
    3    祝枝山    AID01       22000

CREATE TABLE jftab (
id    INT PRIMARY KEY,
name  CHAR(15),
class CHAR(5),
money INT
)
DEFAULT CHARSET = utf8;

INSERT INTO jftab VALUES
(1, '唐伯虎', 'AID01', 28000),
(2, '点秋香', 'AID01', 20000),
(3, '祝枝山', 'AID01', 22000);

        表2、学生信息表(班主任)(从表)
            学号  姓名            缴费金额
            1    唐伯虎           28000
            2    点秋香           20000

CREATE TABLE bjtab (
          bj_id    INT,
          bj_name  CHAR(15),
          bj_money INT,
          FOREIGN KEY (bj_id) REFERENCES jftab (id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
INSERT INTO bjtab VALUES
(1, '唐伯虎', 28000),
(2, '点秋香', 20000);


delect from 缴费信息表 where 姓名='点秋香';# 删除缴费信息表中点秋香信息
select * from 学生信息表;    # 学生信息表中也删除了
update 缴费信息表 set 学号=2 where 姓名='唐伯虎';   # 将唐伯虎学号改为2

    4、级联动作

        1、caseade ：数据级联更新

            当主表删除记录 或者 更新被参考字段的值时，从表会级联更新

        2、restrict(默认)

            1、当删除主表记录时，如果从表中有相关联记录则不允许主表删除
            2、更新同理

        3、set null

            1、当主键删除记录时,从表中相关联记录的参考字段之自动设置为NULL
            2、更新同理

        4、no action

            on delete no action on update on action
            同 restrict，都是立即检查外键限制

    5、删除外键

        alter table 表名 drop foreign key 外键名;
        1、外键查看方式

        show create table 表名;

show create table 表名;
'''以上运行结果
CREATE TABLE `orders` (
  `o_id` int(11) DEFAULT NULL,
  `o_name` varchar(30) DEFAULT NULL,
  `o_price` double(12,2) DEFAULT NULL,
  KEY `o_id` (`o_id`),
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`o_id`) REFERENCES `coustomers` (`c_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
其中CONSTRAINT 后 orders_ibfk_1  为外键名
'''

    6、已有表中减价外键

        # 会受到表中原有数据的限制
        alter  table 表名 add foreign key(参考字段名)
        references 被参考表名(被参考字段名)
        on delete 级联动作
        on update 级联动作

    7、外键使用规则

        1、两张表被参考字段和参考字段数据类型要一致
        2、被参考字段不许是key的一种，通常是primary key

6、数据导入

    1、作用：

        把文件系统的内容导入到数据库中

    2、语法：

    LOAD DATE INFILE '文件名'
    INTO TABLE 表名
    FIELDS TERMINATED BY '分隔符'  #列分割
    LINES TERMINATED BY '\n'        #行分割

    3、示例

        把/etc/passwd 文件中的内容导入到db2库下的userinfo表
        root    :    x    :    0     :    0     :  root      :    /root  :       /bin/bash
        用户名   密码  UID号   GID号   用户描述  用户主目录     登录权限

    4、操作步骤

        1、在数据库中创建对应的表

CREATE TABLE userinfo (
username CHAR(20),
password CHAR(1),
uid      INT,
gid      INT,
comment  VARCHAR(50),
homedir  VARCHAR(50),
shell    VARCHAR(50)
);

      2、将要导入的文件拷贝到数据库的默认搜索路径中

            1、查看数据库的默认搜索路径

SHOW VARIABLES LIKE 'secure_file_priv';
# 默认路径通常是/var/lib/mysql-files/

            2、

sudo cp /etc/passwd /var/lib/mysql-files/

        3、执行数据库导入语句

LOAD DATA INFILE '/var/lib/mysql-files/passwd'
INTO TABLE userinfo
FIELDS TERMINATED BY ':'
LINES TERMINATED BY '\n';

    5、练习：

                将AID1709.csv文件导入到数据库中

        1、创建表

CREATE TABLE student (
  id        TINYINT PRIMARY KEY AUTO_INCREMENT,
  name      CHAR(10),
  score     FLOAT(4, 1),
  phone_num BIGINT,
  class     CHAR(7)
);

        2、将要导入的文件复制到数据库的默认搜索路径中

sudo cp /home/tarena/AID1709.csv /var/lib/mysql-files/

            ###路径要用tab键补齐

        3、执行数据库导入语句

LOAD DATA INFILE '/var/lib/mysql-files/AID1709.csv'
INTO TABLE student
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

7、数据导出

    1、作用

        将数据库表中的数据记录保存到系统文件中

    2、语法格式

    SELECT ... FROM 表名
    INTO OUTFILE '文件名'
    FIELDS TERMINATED BY '分隔符'
    LINES TERMINATED BY '\n';

    3、把userinfo表中的username、password和UID导出到文件user.txt

SELECT
  username,
  password,
  uid
FROM userinfo
INTO OUTFILE '/var/lib/mysql-files/user.txt'
FIELDS TERMINATED BY '   '
LINES TERMINATED BY '\n';

    4、注意

        1、导出的内容有SQL查询语句决定
        2、执行导出命令时路径必须制定对应的数据库搜索路径

8、表的复制

    1、语法格式

         CREATE TABLE 表名 SELECT 查询命令;

    2、示例

        1、复制userinfo表中的全部记录，userinfo2

create table userinfo2 select * from userinfo;

        2、复制userinfo表中username，password，uid三个字段的第2-10条记录userinfo3

create table userinfo3 select username,password,uid from userinfo limit 1,9;

    3、复制表结构

    CREATE TABLE 表名 SELECT 查询命令 WHERE FALSE;

    4、注意

        复制表的时候不会把原有表的key属性复制过来

9、嵌套查询(子查询)

    1、定义

        把内层的查询结果作为外层的查询条件

    2、示例

        1、把uid的值小于uid平均值的用户名和uid号显示出来

SELECT
  username,
  uid
FROM userinfo
WHERE uid < (SELECT avg(uid)
             FROM userinfo);

10、连接查询

    1、内连接

        1、定义

            从表中删除与其他被连接的表中没有匹配到的行

        2、语法格式

    SELECT 字段名列表
    FROM 表1
      INNER JOIN 表2 ON 条件
    INNER JOIN 表3 ON 条件;

        3、示例

            1、显示省市的详细信息

SELECT
  sheng.s_name,
  city.c_name
FROM sheng
  INNER JOIN city ON sheng.s_id = city.cfather_id;

            2、显示省市县的信息

SELECT
  sheng.s_name,
  city.c_name,
  xian.x_name
FROM sheng
  INNER JOIN city ON sheng.s_id = city.cfather_id
  INNER JOIN xian ON city.c_id = xian.xfather_id;

    2、外链接

        1、左连接

            1、定义

                以左表为主显示查询结果

            2、语法格式

        SELECT 字段名列表 FROM 表1
        LEFT JOIN 表2 ON 条件；

            3、示例

                1、以省表为主显示省市详细信息

SELECT
  sheng.s_name,
  city.c_name
FROM sheng
  LEFT JOIN city ON sheng.s_id = city.cfather_id;

                2、以省表为主显示省市县的信息

SELECT
  sheng.s_name,
  city.c_name,
  xian.x_name
FROM sheng
  LEFT JOIN city ON sheng.s_id = city.cfather_id
  LEFT JOIN xian ON city.c_id = xian.xfather_id;

                3、显示省市县详细信息，要求市全部显示

SELECT
  sheng.s_name,
  city.c_name,
  xian.x_name
FROM sheng
  RIGHT JOIN city ON sheng.s_id = city.cfather_id
  LEFT JOIN xian ON city.c_id = xian.xfather_id;

        2、右连接

            1、定义

                用法同左连接，以右表为主显示查询结果

            2、语法格式

    SELECT 字段名列表 FROM 表1
    RIGHT JOIN 表2 ON 条件;

11、多表查询

    1、select 字段名列表 from 表名列表；   # 笛卡尔积
    2、select 字段名列表 from 表名列表 where 条件；#等同于多表联查的内连接inner join

示例
create TABLE t1(name VARCHAR(10));
create TABLE t2(name VARCHAR(10));

INSERT INTO t1 VALUES ('a'),('b');
INSERT INTO t2 VALUES ('c'),('d'),('e');

SELECT  * FROM t1,t2;

'''
下为结果
+------+------+
| name | name |
+------+------+
| a    | c    |
| b    | c    |
| a    | d    |
| b    | d    |
| a    | e    |
| b    | e    |
+------+------+
'''

INSERT INTO t1 VALUES ('c');
SELECT t1.name,t2.name FROM t1,t2
WHERE t1.name=t2.name;
'''
下为结果
+------+------+
| name | name |
+------+------+
| c    | c    |
+------+------+
'''