mysql 操作合集(二)

Python进阶学习交流

    这里接着mysql 操作合集(一)写。
    主要有两部分,join和约束。
    5.两个表间的操作
    创建表
    CREATE TABLE test (  id int(11) NOT NULL AUTO_INCREMENT,  name varchar(20) DEFAULT NULL,  course varchar(20) DEFAULT NULL,  score int(11) DEFAULT NULL,  PRIMARY KEY (id))
    插入数据
    insert into test(name,course,score)values('张三','语文',80),('李四','语文',90),('王五','语文',93),('张三','数学',77),('李四','数学',68),('王五','数学',99),('张三','英语',90),('李四','英语',50),('王五','英语',89);
    现在有两个表newstudent和表test
    select * from test;+----+--------+--------+-------+| id | name   | course | score |+----+--------+--------+-------+|  1 | 张三   | 语文   |    80 ||  2  | 李四   | 语文   |    90 ||  3 | 王五   | 语文   |    93 ||  4  | 张三   | 数学   |    77 ||  5 | 李四   | 数学   |    68 ||  6  | 王五   | 数学   |    99 ||  7 | 张三   | 英语   |    90 ||  8  | 李四   | 英语   |    50 ||  9 | 王五   | 英语   |    89 |+----+--------+--------+-------+select * from newstudent;+--------+----+--------+--------+-------+| userid | id | name   | gender | score |+--------+----+--------+--------+-------+|  10011 |  1 | 张三   | 男     |    83 ||  10012 |  2 | 李四   | 女     |    87 ||   1001 |  3 | NULL   | 男     |    90 ||   1001 |  4 | NULL   | 女     |    97 ||   1001 |  5 | 王五   | NULL   |    97 ||   1001 |  6 | 打人   | NULL   |    97 |+--------+----+--------+--------+-------+
    5.1
    select newstudent.id,newstudent.name,test.cid,test.name from newstudent,test where newstudent.id=test.cid;+----+--------+-----+--------+| id | name   | cid | name   |+----+--------+-----+--------+|  1 | 张三   |   1 | 张三   ||  2 | 李四   |   2 | 李四   ||  3 | NULL   |   3 | 王五   ||  4 | NULL   |   4 | 张三   ||  5 | 王五   |   5 | 李四   ||  6 | 打人   |   6 | 王五   |+----+--------+-----+--------+
    5.2 inner join 内连接
    select * from a inner join b on a.id=b.id
    a为主表,将b表中符合a.id=b.id的部分join到表a
    select * from newstudent inner join test on newstudent.name=test.name; +--------+----+--------+--------+-------+-----+--------+--------+-------+| userid | id | name   | gender | score | cid | name   | course | score |+--------+----+--------+--------+-------+-----+--------+--------+-------+|  10011 |  1 | 张三   | 男     | 83    |   1 | 张三   | 语文   |    80 ||  10012 |  2 | 李四   | 女     | 87    |   2 | 李四   | 语文   |    90 ||   1001 |  5 | 王五   | NULL   | 97    |   3 | 王五   | 语文   |    93 ||  10011 |  1 | 张三   | 男     | 83    |   4 | 张三   | 数学   |    77 ||  10012 |  2 | 李四   | 女     | 87    |   5 | 李四   | 数学   |    68 ||   1001 |  5 | 王五   | NULL   | 97    |   6 | 王五   | 数学   |    99 ||  10011 |  1 | 张三   | 男     | 83    |   7 | 张三   | 英语   |    90 ||  10012 |  2 | 李四   | 女     | 87    |   8 | 李四   | 英语   |    50 ||   1001 |  5 | 王五   | NULL   | 97    |   9 | 王五   | 英语   |    89 |+--------+----+--------+--------+-------+-----+--------+--------+-------+select * from test inner join newstudent on test.name=newstudent.name;+-----+--------+--------+-------+--------+----+--------+--------+-------+| cid | name   | course | score | userid | id | name   | gender | score |+-----+--------+--------+-------+--------+----+--------+--------+-------+|   1 | 张三   | 语文   |    80 |  10011 |  1 | 张三   | 男     | 83    ||   2 | 李四   | 语文   |    90 |  10012 |  2 | 李四   | 女     | 87    ||   3 | 王五   | 语文   |    93 |   1001 |  5 | 王五   | NULL   | 97    ||   4 | 张三   | 数学   |    77 |  10011 |  1 | 张三   | 男     | 83    ||   5 | 李四   | 数学   |    68 |  10012 |  2 | 李四   | 女     | 87    ||   6 | 王五   | 数学   |    99 |   1001 |  5 | 王五   | NULL   | 97    ||   7 | 张三   | 英语   |    90 |  10011 |  1 | 张三   | 男     | 83    ||   8 | 李四   | 英语   |    50 |  10012 |  2 | 李四   | 女     | 87    ||   9 | 王五   | 英语   |    89 |   1001 |  5 | 王五   | NULL   | 97    |+-----+--------+--------+-------+--------+----+--------+--------+-------+
    5.3 left join 左连接
    select * from a left join b on a.name=b.name;
    左连接从左表产生一套完整的记录,再与匹配的记录(右表b)进行匹配,生成的新表格顺序以匹配的记录为准,没有匹配则为null
    select * from test left join newstudent on test.name=newstudent.name;+-----+--------+--------+-------+--------+------+--------+--------+-------+| cid | name   | course | score | userid | id   | name   | gender | score |+-----+--------+--------+-------+--------+------+--------+--------+-------+|   1 | 张三   | 语文   |    80 |  10011 |    1 | 张三   | 男     | 83    ||   4 | 张三   | 数学   |    77 |  10011 |    1 | 张三   | 男     | 83    ||   7 | 张三   | 英语   |    90 |  10011 |    1 | 张三   | 男     | 83    ||   2 | 李四   | 语文   |    90 |  10012 |    2 | 李四   | 女     | 87    ||   5 | 李四   | 数学   |    68 |  10012 |    2 | 李四   | 女     | 87    ||   8 | 李四   | 英语   |    50 |  10012 |    2 | 李四   | 女     | 87    ||   3 | 王五   | 语文   |    93 |   1001 |    5 | 王五   | NULL   | 97    ||   6 | 王五   | 数学   |    99 |   1001 |    5 | 王五   | NULL   | 97    ||   9 | 王五   | 英语   |    89 |   1001 |    5 | 王五   | NULL   | 97    |+-----+--------+--------+-------+--------+------+--------+--------+-------+
    select * from newstudent left join test on test.name=newstudent.name;+--------+----+--------+--------+-------+------+--------+--------+-------+| userid | id | name   | gender | score | cid  | name   | course | score |+--------+----+--------+--------+-------+------+--------+--------+-------+|  10011 |  1 | 张三   | 男     | 83    |    1 | 张三   | 语文   |    80 ||  10012 |  2 | 李四   | 女     | 87    |    2 | 李四   | 语文   |    90 ||   1001 |  5 | 王五   | NULL   | 97    |    3 | 王五   | 语文   |    93 ||  10011 |  1 | 张三   | 男     | 83    |    4 | 张三   | 数学   |    77 ||  10012 |  2 | 李四   | 女     | 87    |    5 | 李四   | 数学   |    68 ||   1001 |  5 | 王五   | NULL   | 97    |    6 | 王五   | 数学   |    99 ||  10011 |  1 | 张三   | 男     | 83    |    7 | 张三   | 英语   |    90 ||  10012 |  2 | 李四   | 女     | 87    |    8 | 李四   | 英语   |    50 ||   1001 |  5 | 王五   | NULL   | 97    |    9 | 王五   | 英语   |    89 ||   1001 |  3 | NULL   | 男     | 90    | NULL | NULL   | NULL   |  NULL ||   1001 |  4 | NULL   | 女     | 97    | NULL | NULL   | NULL   |  NULL ||   1001 |  6 | 打人   | NULL   | 97    | NULL | NULL   | NULL   |  NULL |+--------+----+--------+--------+-------+------+--------+--------+-------+
    5.4 left join 左连接
    selece * from a left join b on a.key=b.key where b.key is null;
    与5.3的左连接不同的是,仅保留a表a.key!=b.key的部分,同时将表b的字段左连接到表a
    select * from newstudent left join test on newstudent.name=test.name where test.name is null;+--------+----+--------+--------+-------+------+------+--------+-------+| userid | id | name   | gender | score | cid  | name | course | score |+--------+----+--------+--------+-------+------+------+--------+-------+|   1001 |  3 | NULL   | 男     | 90    | NULL | NULL | NULL   |  NULL ||   1001 |  4 | NULL   | 女     | 97    | NULL | NULL | NULL   |  NULL ||   1001 |  6 | 打人   | NULL   | 97    | NULL | NULL | NULL   |  NULL |+--------+----+--------+--------+-------+------+------+--------+-------+以newstudent表为基准,将newstudent.name=test.name的部分去掉,再把test表的字段左连接到newstudent表
    select * from test left join newstudent on newstudent.name=test.name where newstudent.name is null;Empty set (0.00 sec)返回空值的原因:以test表为基准,但test表的name字段“张三”,“李四”,“王五”在newstudent表都存在,所以去掉相同部分后test为空
    desc下这个表结构desc select * from test left join newstudent on newstudent.name=test.name where newstudent.name is null;+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+|  1 | SIMPLE      | test       | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |   100.00 | NULL                                               ||  1 | SIMPLE      | newstudent | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |    16.67 | Using where; Using join buffer (Block Nested Loop) |+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
    5.5 right join 右连接
    select * from a right join b on a.key=b.key;
    以表b为基准,将表a匹配项(a.key=b.key)右连接,与表b匹配,生成内容以匹配的记录为准,没有匹配则为null
    select * from test right join newstudent on test.name=newstudent.name;+------+--------+--------+-------+--------+----+--------+--------+-------+| cid  | name   | course | score | userid | id | name   | gender | score |+------+--------+--------+-------+--------+----+--------+--------+-------+|    1 | 张三   | 语文   |    80 |  10011 |  1 | 张三   | 男     | 83    ||    2 | 李四   | 语文   |    90 |  10012 |  2 | 李四   | 女     | 87    ||    3 | 王五   | 语文   |    93 |   1001 |  5 | 王五   | NULL   | 97    ||    4 | 张三   | 数学   |    77 |  10011 |  1 | 张三   | 男     | 83    ||    5 | 李四   | 数学   |    68 |  10012 |  2 | 李四   | 女     | 87    ||    6 | 王五   | 数学   |    99 |   1001 |  5 | 王五   | NULL   | 97    ||    7 | 张三   | 英语   |    90 |  10011 |  1 | 张三   | 男     | 83    ||    8 | 李四   | 英语   |    50 |  10012 |  2 | 李四   | 女     | 87    ||    9 | 王五   | 英语   |    89 |   1001 |  5 | 王五   | NULL   | 97    || NULL | NULL   | NULL   |  NULL |   1001 |  3 | NULL   | 男     | 90    || NULL | NULL   | NULL   |  NULL |   1001 |  4 | NULL   | 女     | 97    || NULL | NULL   | NULL   |  NULL |   1001 |  6 | 打人   | NULL   | 97    |+------+--------+--------+-------+--------+----+--------+--------+-------+
    select * from newstudent right join test on newstudent.name=test.name;+--------+------+--------+--------+-------+-----+--------+--------+-------+| userid | id   | name   | gender | score | cid | name   | course | score |+--------+------+--------+--------+-------+-----+--------+--------+-------+|  10011 |    1 | 张三   | 男     | 83    |   1 | 张三   | 语文   |    80 ||  10011 |    1 | 张三   | 男     | 83    |   4 | 张三   | 数学   |    77 ||  10011 |    1 | 张三   | 男     | 83    |   7 | 张三   | 英语   |    90 ||  10012 |    2 | 李四   | 女     | 87    |   2 | 李四   | 语文   |    90 ||  10012 |    2 | 李四   | 女     | 87    |   5 | 李四   | 数学   |    68 ||  10012 |    2 | 李四   | 女     | 87    |   8 | 李四   | 英语   |    50 ||   1001 |    5 | 王五   | NULL   | 97    |   3 | 王五   | 语文   |    93 ||   1001 |    5 | 王五   | NULL   | 97    |   6 | 王五   | 数学   |    99 ||   1001 |    5 | 王五   | NULL   | 97    |   9 | 王五   | 英语   |    89 |+--------+------+--------+--------+-------+-----+--------+--------+-------+
    5.6 right join 右连接
    select * from a right join b on a.key=b.key where a.key is null;
    和5.5右连接不同的是:以表b为基准,先把a.key=b.key的部分从表b去除;再把表a的字段粘贴过来
    select * from test right join newstudent on test.name=newstudent.name where test.name is null;+------+------+--------+-------+--------+----+--------+--------+-------+| cid  | name | course | score | userid | id | name   | gender | score |+------+------+--------+-------+--------+----+--------+--------+-------+| NULL | NULL | NULL   |  NULL |   1001 |  3 | NULL   | 男     | 90    || NULL | NULL | NULL   |  NULL |   1001 |  4 | NULL   | 女     | 97    || NULL | NULL | NULL   |  NULL |   1001 |  6 | 打人   | NULL   | 97    |+------+------+--------+-------+--------+----+--------+--------+-------+
    select * from newstudent right join test on newstudent.name=test.name where newstudent.name is null;Empty set (0.00 sec)返回空值原因同上5.4
    5.7 full outer join 全连接
    select * from a full join b on a.key=b.key;
    不知道为什么这个语法一直报错,网上找了一种替代法:join + union(去重)
    select * from A left join B on A.id = B.id union select * from A right join B on A.id = B.id;
    select * from newstudent left join test on newstudent.name=test.name union select *  from newstudent right join test on newstudent.name=test.name;+--------+------+--------+--------+-------+------+--------+--------+-------+| userid | id   | name   | gender | score | cid  | name   | course | score |+--------+------+--------+--------+-------+------+--------+--------+-------+|  10011 |    1 | 张三   | 男     | 83    |    1 | 张三   | 语文   |    80 ||  10012 |    2 | 李四   | 女     | 87    |    2 | 李四   | 语文   |    90 ||   1001 |    5 | 王五   | NULL   | 97    |    3 | 王五   | 语文   |    93 ||  10011 |    1 | 张三   | 男     | 83    |    4 | 张三   | 数学   |    77 ||  10012 |    2 | 李四   | 女     | 87    |    5 | 李四   | 数学   |    68 ||   1001 |    5 | 王五   | NULL   | 97    |    6 | 王五   | 数学   |    99 ||  10011 |    1 | 张三   | 男     | 83    |    7 | 张三   | 英语   |    90 ||  10012 |    2 | 李四   | 女     | 87    |    8 | 李四   | 英语   |    50 ||   1001 |    5 | 王五   | NULL   | 97    |    9 | 王五   | 英语   |    89 ||   1001 |    3 | NULL   | 男     | 90    | NULL | NULL   | NULL   |  NULL ||   1001 |    4 | NULL   | 女     | 97    | NULL | NULL   | NULL   |  NULL ||   1001 |    6 | 打人   | NULL   | 97    | NULL | NULL   | NULL   |  NULL |+--------+------+--------+--------+-------+------+--------+--------+-------+
    5.8 union
    select key1 from a union select key2 from b;
    select name,id,score from newstudent union select name,cid,score from test;+--------+----+-------+| name   | id | score |+--------+----+-------+| 张三   |  1 | 83    || 李四   |  2 | 87    || NULL   |  3 | 90    || NULL   |  4 | 97    || 王五   |  5 | 97    || 打人   |  6 | 97    || 张三   |  1 | 80    || 李四   |  2 | 90    || 王五   |  3 | 93    || 张三   |  4 | 77    || 李四   |  5 | 68    || 王五   |  6 | 99    || 张三   |  7 | 90    || 李四   |  8 | 50    || 王五   |  9 | 89    |+--------+----+-------+注意:union 后的表字段是引用表a的字段名
    5.9 union all
    select key1 from a union all select key2 from  b;
    select name from test union all select userid from newstudent;+--------+| name   |+--------+| 张三   || 李四   || 王五   || 张三   || 李四   || 王五   || 张三   || 李四   || 王五   || 10011  || 10012  || 1001   || 1001   || 1001   || 1001   |+--------+
    5.10 求两个表格的非相同部分
    SELECT * FROM A LEFT JOIN B ON A.name = B.name
    WHERE B.id IS NULL
    union
    SELECT * FROM A right JOIN B ON A.name = B.name
    WHERE A.id IS NULL;
    select * from test right join newstudent on test.name=newstudent.name where test.name is null union select * from test left join newstudent on newstudent.name=test.name where newstudent.name is null;+------+------+--------+-------+--------+------+--------+--------+-------+| cid  | name | course | score | userid | id   | name   | gender | score |+------+------+--------+-------+--------+------+--------+--------+-------+| NULL | NULL | NULL   |  NULL |   1001 |    3 | NULL   | 男     | 90    || NULL | NULL | NULL   |  NULL |   1001 |    4 | NULL   | 女     | 97    || NULL | NULL | NULL   |  NULL |   1001 |    6 | 打人   | NULL   | 97    |+------+------+--------+-------+--------+------+--------+--------+-------+
    6.约束
    6.1 unique
    unique约束用来标识数据库表中每条记录;
    在一个表中,可以使某一列有unique约束,也可以多列一起unique约束
    6.1.1
    CREATE TABLE persons(id int NOT NULL,lastname varchar(255) NOT NULL,firstName varchar(255),city varchar(255),CONSTRAINT uc_PersonID UNIQUE (id,lastname));
    这个表将id,lastname共同作为表persons的约束,确保id+lastname不重复
    要注意的是uc_PersonID是虚拟的,不属于表真实存在的字段。
    desc persons;+-----------+--------------+------+-----+---------+-------+| Field     | Type         | Null | Key | Default | Extra |+-----------+--------------+------+-----+---------+-------+| id        | int(11)      | NO   | PRI | NULL    |       || lastname  | varchar(255) | NO   | PRI | NULL    |       || firstName | varchar(255) | YES  |     | NULL    |       || city      | varchar(255) | YES  |     | NULL    |       |+-----------+--------------+------+-----+---------+-------+
    6.1.2-1
    insert into persons(id,lastname,firstname) values (1,'z','q');Query OK, 1 row affected (0.00 sec)
    6.1.2-2
    insert into persons(id,lastname,firstname) values (2,'z','q');Query OK, 1 row affected (0.01 sec)
    此时虽然lastname重复了'z',但是id+lastname分别为1+'z'和2+'z';所以不算重复,因此插入成功
    6.1.2-3
    insert into persons(id,lastname,firstname) values (2,'zz','q');Query OK, 1 row affected (0.01 sec)
    和第二次(6.1.2-2)插入的数据相比,虽然id重复了都为2,但lastname分别为"z"和'zz';所以id+lastname分别为2+'z'和2+'zz';所以不算重复,因此插入成功
    6.1.2-4
    insert into persons(id,lastname,firstname) values (2,'zz','q');ERROR 1062 (23000): Duplicate entry '2-zz' for key 'uc_PersonID'
    在第四次操作时,由于id+lastname重复为2+'zz',约束项uc_PersonID重复,所以报错。
    
    
    1  2  下一页>