MySQL第三天（管理表记录）

原创

大宝dayday见 2018-03-01 20:32:19 ©著作权

文章标签 4564 654 文章分类 运维

©著作权归作者所有：来自51CTO博客作者大宝dayday见的原创作品，请联系作者获取转载授权，否则将追究法律责任

day03 一、管理表记录？导入数据：把系统文件的内容存储到数据库服务器的表里。

把系统用户信息/etc/passwod存储到数据库服务器的db3库下的user里。

用户名密码 UID GID 描述信息家目录shell

mysql> create table user( -> name char(50), -> password char(8), -> uid tinyint(6), -> gid tinyint(6), -> comment char(100), -> homedir char(100), -> shell char(30), -> index(name) ->); Query OK, 0 rows affected (0.06 sec)

[root@db1 ~]# cp /etc/passwd /var/lib/mysql-files/ ##复制passwd文件到数据库默认载体目录内

mysql> load data infile ##导入文件到数据库默认目录 -> "/var/lib/mysql-files/passwd" ##字段分隔为：行分隔为换行（字段分隔符要与文件一致） -> into table db3.user ##导入的数据要与表字段匹配 -> fields terminated by ":" ##禁止selinux -> lines terminated by "\n"; Query OK, 44 rows affected, 33 warnings (0.01 sec) Records: 44 Deleted: 0 Skipped: 0 Warnings: 33

mysql> select * from db3.user; +---------------------+----------+------+------+-----------------------------------------------------------------+---------------------------+----------------+ | name | password | uid | gid | comment | homedir | shell | +---------------------+----------+------+------+-----------------------------------------------------------------+---------------------------+----------------+ | root | x | 0 | 0 | root | /root | /bin/bash | | bin | x | 1 | 1 | bin | /bin | /sbin/nologin | | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin | | adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin | | lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin | | sync | x | 5 | 0 | sync | /sbin | /bin/sync | | shutdown | x | 6 | 0 | shutdown | /sbin | /sbin/shutdown | | halt | x | 7 | 0 | halt | /sbin | /sbin/halt | | mail | x | 8 | 12 | mail | /var/spool/mail | /sbin/nologin | | operator | x | 11 | 0 | operator | /root | /sbin/nologin | | games | x | 12 | 100 | games | /usr/games | /sbin/nologin | | ftp | x | 14 | 50 | FTP User | /var/ftp | /sbin/nologin | | nobody | x | 99 | 99 | Nobody | / | /sbin/nologin |

mysql> alter table db3.user add id int(2) primary key auto_increment first; ##插入id字段自增+1 Query OK, 44 rows affected (0.05 sec) Records: 44 Duplicates: 0 Warnings: 0

mysql> select * from db3.user; +----+---------------------+----------+------+------+-----------------------------------------------------------------+---------------------------+----------------+ | id | name | password | uid | gid | comment | homedir | shell | +----+---------------------+----------+------+------+-----------------------------------------------------------------+---------------------------+----------------+ | 1 | root | x | 0 | 0 | root | /root | /bin/bash | | 2 | bin | x | 1 | 1 | bin | /bin | /sbin/nologin | | 3 | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin | | 4 | adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin | | 5 | lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin | | 6 | sync | x | 5 | 0 | sync | /sbin | /bin/sync | | 7 | shutdown | x | 6 | 0 | shutdown | /sbin | /sbin/shutdown | | 8 | halt | x | 7 | 0 | halt | /sbin | /sbin/halt | | 9 | mail | x | 8 | 12 | mail | /var/spool/mail | /sbin/nologin | | 10 | operator | x | 11 | 0 | operator | /root | /sbin/nologin | | 11 | games | x | 12 | 100 | games | /usr/games | /sbin/nologin | | 12 | ftp | x | 14 | 50 | FTP User | /var/ftp | /sbin/nologin | | 13 | nobody | x | 99 | 99 | Nobody | / | /sbin/nologin |

mysql> quit bye

[root@db1 ~]# vim /etc/my.cnf [root@db1 ~]# mkdir /mydata [root@db1 ~]# ls -ld /mydata/ drwxr-xr-x. 2 root root 6 2月 23 22:51 /mydata/ [root@db1 ~]# chown mysql /mydata [root@db1 ~]# setenforce 0 [root@db1 ~]# getenforce Permissive [root@db1 ~]# systemctl restart mysqld

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 导出数据：把表记录存储到系统文件里。

命令格式： sql查询 into outfile "目录名/文件名" fields terminated by "符号" lines terminated by "符号"； ##目录为"secure_file_priv"指定的目录，文件自动生成,行分隔默认为"\n"

mysql> select * from db3.user into outfile "/mydata/a.txt" fields terminated by ":"; Query OK, 44 rows affected (0.02 sec)

mysql> quit Bye

mysql> select * from db3.user into outfile "/mydata/b.txt"; ##字段分隔默认为tab Query OK, 44 rows affected (0.00 sec)

mysql> quit Bye

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 表记录的增、删、改、查插入数据（增）一次插入1条记录给所有字段赋值一次插入1条记录给指定字段赋值

insert into 库.表 values(字段值列表)； insert into 库.表(字段名列表) values(字段值列表),(字段值列表),(字段值列表)；

mysql> insert into db3.user values(45,"jack","x",30,30,"this is student","/home/jack","/bin/bash"); ##一次插入1条记录给所有字段赋值 Query OK, 1 row affected (0.00 sec)

mysql> insert into db3.user(name,uid,gid) values("tom",31,31); ##一次插入1条记录给指定字段赋值 Query OK, 1 row affected (0.00 sec)

查询记录： select 字段名列表 from 库.表 [where 条件]； select * from db3.user;
select name,uid from db3.user; ##查询 name，uid字段
mysql> select name,uid from db3.user where id<=3; ##条件查询 +--------+------+ | name | uid | +--------+------+ | root | 0 | | bin | 1 | | daemon | 2 | +--------+------+ 3 rows in set (0.00 sec)

查询/删除/更新数据时的匹配条件？ 1)数值比较 > >= < <= = != 字段符号数字 mysql> select name from db3.user where uid=500; Empty set (0.00 sec)

mysql> select * from db3.user where uid<=500;

2)字符比较 = != 字段符号 "字符"

mysql> select name from db3.user where name="root"; +------+ | name | +------+ | root | +------+ 1 row in set (0.00 sec)

mysql> select name from db3.user where shell!="/bin/bash";

3)范围内匹配 between 数字1 and 数字2 ##在...之间

mysql> select * from db3.user where uid between 10 and 20; +----+----------+----------+------+------+----------+------------+---------------+ | id | name | password | uid | gid | comment | homedir | shell | +----+----------+----------+------+------+----------+------------+---------------+ | 10 | operator | x | 11 | 0 | operator | /root | /sbin/nologin | | 11 | games | x | 12 | 100 | games | /usr/games | /sbin/nologin | | 12 | ftp | x | 14 | 50 | FTP User | /var/ftp | /sbin/nologin | +----+----------+----------+------+------+----------+------------+---------------+ 3 rows in set (0.00 sec)

4)in (值列表) ##在..里面 not in (值列表) ##不在...里面

mysql> select * from db3.user where name in("root","bin","adm"); +----+------+----------+------+------+---------+----------+---------------+ | id | name | password | uid | gid | comment | homedir | shell | +----+------+----------+------+------+---------+----------+---------------+ | 4 | adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin | | 2 | bin | x | 1 | 1 | bin | /bin | /sbin/nologin | | 1 | root | x | 0 | 0 | root | /root | /bin/bash | +----+------+----------+------+------+---------+----------+---------------+ 3 rows in set (0.00 sec)

mysql> select name,uid from db3.user where uid in(500,100,20); ##查询name，uid列的记录，条件是字段uid是500或者100或者20的 Empty set (0.01 sec)

mysql> select name,uid from db3.user where uid not in (20,50,80); ##查询name，uid列的记录，条件是字段uid不是20或者50或者80的

5)逻辑比较（有多个判断条件）逻辑与and 多个条件必须同时成立逻辑或 or 多个条件某一个成立即可逻辑非 not mysql> select * from db3.user where name="jin" or uid=100 or shell="/bin/bash"; +----+-------+----------+------+------+-----------------+-------------+-----------+ | id | name | password | uid | gid | comment | homedir | shell | +----+-------+----------+------+------+-----------------+-------------+-----------+ | 1 | root | x | 0 | 0 | root | /root | /bin/bash | | 43 | usetr | x | 127 | 127 | usetr | /home/usetr | /bin/bash | | 45 | jack | x | 30 | 30 | this is student | /home/jack | /bin/bash | +----+-------+----------+------+------+-----------------+-------------+-----------+ 3 rows in set (0.00 sec)

mysql> select * from db3.user where name="root" and uid=0;
+----+------+----------+------+------+---------+---------+-----------+ | id | name | password | uid | gid | comment | homedir | shell | +----+------+----------+------+------+---------+---------+-----------+ | 1 | root | x | 0 | 0 | root | /root | /bin/bash | +----+------+----------+------+------+---------+---------+-----------+ 1 row in set (0.00 sec)

6)匹配空 is null 匹配非空 is not null

mysql> select * from db3.user where shell is null; +----+------+----------+------+------+---------+---------+-------+ | id | name | password | uid | gid | comment | homedir | shell | +----+------+----------+------+------+---------+---------+-------+ | 46 | tom | NULL | 31 | 31 | NULL | NULL | NULL | +----+------+----------+------+------+---------+---------+-------+ 1 row in set (0.00 sec)

mysql> select name,uid from db3.user where uid is not null;

7)不显示查询结果的重复值

8)查询时做四则运算(+ - * /)

mysql> alter table db3.user add age tinyint(2) default 21; ##添加新字段age 默认值为21 Query OK, 46 rows affected (0.05 sec) Records: 46 Duplicates: 0 Warnings: 0

mysql> select name,age from db3.user; ##查看name，age的记录 +---------------------+------+ | name | age | +---------------------+------+ | root | 21 | | bin | 21 | | daemon | 21 | | adm | 21 | | lp | 21 | | sync | 21 | | shutdown | 21 | | halt | 21 | | mail | 21 | | operator | 21 | | games | 21 | | ftp | 21 | | nobody | 21 | | avahi-autoipd | 21 | | systemd-bus-proxy | 21 | | systemd-network | 21 |

mysql> select name,age,2018-age as syear from db3.user where name="root"; 查看name，age，2018-age(字段名为syear)的记录，条件为字段name为root +------+------+-------+ | name | age | syear | +------+------+-------+ | root | 21 | 1997 | +------+------+-------+ 1 row in set (0.00 sec)

mysql> select name,uid,gid,(uid+gid)/2 as pjz from db3.user where name="sync"; +------+------+------+--------+ | name | uid | gid | pjz | +------+------+------+--------+ | sync | 5 | 0 | 2.5000 | +------+------+------+--------+ 1 row in set (0.00 sec)

mysql> select name,uid,gid from db3.user where uid=gid; +---------------------+------+------+ | name | uid | gid | +---------------------+------+------+ | root | 0 | 0 | | bin | 1 | 1 | | daemon | 2 | 2 | | nobody | 99 | 99 | | avahi-autoipd | 127 | 127 | | systemd-bus-proxy | 127 | 127 | | systemd-network | 127 | 127 | | dbus | 81 | 81 | | polkitd | 127 | 127 |

9)使用聚集函数（内置的，对数据做统计的命令） count(字段名) 统计个数（null值不做统计） sum（字段名）求和 avg（字段名）计算平均数 max（字段名）求最大值 min（字段名）求最小值

mysql> select min(uid) from db3.user where uid>100; ##查看最小uid，条件为uid大于100 +----------+ | min(uid) | +----------+ | 107 | +----------+ 1 row in set (0.00 sec)

mysql> select count(name) from db3.user where shell="/bin/bash"; ##查看name的个数，条件为shell字段为"/bin/bash" +-------------+ | count(name) | +-------------+ | 3 | +-------------+ 1 row in set (0.00 sec)

mysql> select count(name),count(id) from db3.user; ##查看name字段值的个数，id字段值的个数 +-------------+-----------+ | count(name) | count(id) | +-------------+-----------+ | 46 | 46 | +-------------+-----------+ 1 row in set (0.00 sec)

mysql> insert into db3.user(name) values(null); ##插入name字段的值，值为空 Query OK, 1 row affected (0.00 sec)

mysql> select count(name),count(id) from db3.user; ##查看name,id字段值的个数，id多一个，因为自增，name为空没有增加个数 +-------------+-----------+ | count(name) | count(id) | +-------------+-----------+ | 46 | 47 | +-------------+-----------+ 1 row in set (0.00 sec)

mysql> select count(name) from db3.user where name is null; ##查看name字段值的个数，条件为name为空，个数为0，因为值为空什么都没有，不统计。 +-------------+ | count(name) | +-------------+ | 0 | +-------------+ 1 row in set (0.00 sec)

mysql> select count(id) from db3.user where name is null; ##查看id字段值的个数，条件为name字段值为空。（名字为空的id个数） +-----------+ | count(id) | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec)

模糊匹配 like '表达式'； % 匹配零个或多个字符 _ 匹配任意一个字符

mysql> select name from db3.user where name like '___'; ##查看name字段值位数为3个的记录 +------+ | name | +------+ | adm | | bin | | ftp | | gdm | | ntp | | rpc | | tom | | tss | +------+ 8 rows in set (0.00 sec)

正则匹配

mysql> insert into db3.user(name)values("8yaya"),("ya6ya"),("yaya4"),("YayA"),("TOM"); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0

mysql> select name,uid from db3.user where uid regexp '..'; ##查看na +---------------------+------+ | name | uid | +---------------------+------+ | operator | 11 | | games | 12 | | ftp | 14 | | nobody | 99 | | avahi-autoipd | 127 | | systemd-bus-proxy | 127 |

mysql> select name,uid from db3.user where uid regexp '^..$'; ##查看name为2个字符的记录 +----------+------+ | name | uid | +----------+------+ | operator | 11 | | games | 12 | | ftp | 14 | | nobody | 99 |

查询分组(过滤掉相同的) sql查询 group by 字段名；

mysql> select shell from db3.user where uid between 10 and 20 group by shell; +---------------+ | shell | +---------------+ | /sbin/nologin | +---------------+ 1 row in set (0.00 sec)

比较

查询排序 order by asc|desc sql查询 order by 字段名升序|降序 ##默认为升序

mysql> select id,name,uid,gid from db3.user where uid between 10 and 80 order by uid desc; +----+----------+------+------+ | id | name | uid | gid | +----+----------+------+------+ | 31 | radvd | 75 | 75 | | 41 | sshd | 74 | 74 | | 42 | tcpdump | 72 | 72 | | 38 | avahi | 70 | 70 | | 20 | tss | 59 | 59 | | 36 | gdm | 42 | 42 | | 40 | ntp | 38 | 38 | | 28 | rpc | 32 | 32 | | 46 | tom | 31 | 31 | | 45 | jack | 30 | 30 | | 33 | rpcuser | 29 | 29 | | 44 | mysql | 27 | 27 | | 12 | ftp | 14 | 50 | | 11 | games | 12 | 100 | | 10 | operator | 11 | 0 | +----+----------+------+------+ 15 rows in set (0.00 sec)

mysql> select id,name,uid,gid from db3.user where uid between 10 and 80 order by uid asc; +----+----------+------+------+ | id | name | uid | gid | +----+----------+------+------+ | 10 | operator | 11 | 0 | | 11 | games | 12 | 100 | | 12 | ftp | 14 | 50 | | 44 | mysql | 27 | 27 | | 33 | rpcuser | 29 | 29 | | 45 | jack | 30 | 30 | | 46 | tom | 31 | 31 | | 28 | rpc | 32 | 32 | | 40 | ntp | 38 | 38 | | 36 | gdm | 42 | 42 | | 20 | tss | 59 | 59 | | 38 | avahi | 70 | 70 | | 42 | tcpdump | 72 | 72 | | 41 | sshd | 74 | 74 | | 31 | radvd | 75 | 75 | +----+----------+------+------+ 15 rows in set (0.00 sec)

mysql> select id,name,uid,gid from db3.user where uid between 10 and 80 order by uid; +----+----------+------+------+ | id | name | uid | gid | +----+----------+------+------+ | 10 | operator | 11 | 0 | | 11 | games | 12 | 100 | | 12 | ftp | 14 | 50 | | 44 | mysql | 27 | 27 | | 33 | rpcuser | 29 | 29 | | 45 | jack | 30 | 30 | | 46 | tom | 31 | 31 | | 28 | rpc | 32 | 32 | | 40 | ntp | 38 | 38 | | 36 | gdm | 42 | 42 | | 20 | tss | 59 | 59 | | 38 | avahi | 70 | 70 | | 42 | tcpdump | 72 | 72 | | 41 | sshd | 74 | 74 | | 31 | radvd | 75 | 75 | +----+----------+------+------+ 15 rows in set (0.00 sec)

限制显示查询记录行数 sql查询 limit 数字; #显示查询结果的前几行

sql查询 limit 数字1,数字2； ##限制显示行的范围,数字2限制显示的行数,第一行的编号为0 mysql> select * from db3.user limit 2,4; +----+--------+----------+------+------+---------+----------------+---------------+------+ | id | name | password | uid | gid | comment | homedir | shell | age | +----+--------+----------+------+------+---------+----------------+---------------+------+ | 3 | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin | 21 | | 4 | adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin | 21 | | 5 | lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin | 21 | | 6 | sync | x | 5 | 0 | sync | /sbin | /bin/sync | 21 | +----+--------+----------+------+------+---------+----------------+---------------+------+ 4 rows in set (0.00 sec)

mysql> select * from db3.user order by uid desc limit 3; ##查询uid最大三个所有记录 +----+-------------------+----------+------+------+----------------------------+------------------------+---------------+------+ | id | name | password | uid | gid | comment | homedir | shell | age | +----+-------------------+----------+------+------+----------------------------+------------------------+---------------+------+ | 14 | avahi-autoipd | x | 127 | 127 | Avahi IPv4LL Stack | /var/lib/avahi-autoipd | /sbin/nologin | 21 | | 15 | systemd-bus-proxy | x | 127 | 127 | systemd Bus Proxy | / | /sbin/nologin | 21 | | 16 | systemd-network | x | 127 | 127 | systemd Network Management | / | /sbin/nologin | 21 | +----+-------------------+----------+------+------+----------------------------+------------------------+---------------+------+ 3 rows in set (0.00 sec)

查询/删除/更新数据时都可以条件修改 update 库.表 set 字段=值，字段="值" where 条件；

批量修改 update 库.表 set 字段=值，字段="值";

mysql> update db3.user set age=18; Query OK, 52 rows affected (0.00 sec) Rows matched: 52 Changed: 52 Warnings: 0

mysql> update db3.user set name=null where name="root"; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

mysql> update db3.user set uid=uid+1 where uid<=10; Query OK, 9 rows affected (0.00 sec) Rows matched: 9 Changed: 9 Warnings: 0

删除指定记录

delete from 库.表 where 条件；

mysql> delete from db3.user where name is null; Query OK, 2 rows affected (0.00 sec)

删除所有表记录 delete from 库.表;

复制表 create table 库.表 sql查询；

mysql> create table db3.user2 select * from db3.user; ##备份表user表给user2, 不能备份key Query OK, 50 rows affected (0.09 sec) Records: 50 Duplicates: 0 Warnings: 0

mysql> create table db3.user3 select * from db3.user where 1=2; ##快速建表结构 Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from user3; ##表内没有记录 Empty set (0.00 sec)

单表查询 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

where嵌套查询 select 字段名 from 库.表 where 条件 (select 字段名 from 库.表 where 条件); 外面的查询在内的结果查询

查找UID的字段值大于uid平均值的name和uid mysql> select name,uid from db3.user where uid > (select avg(uid) from db3.user); +---------------------+------+ | name | uid | +---------------------+------+ | nobody | 99 | | avahi-autoipd | 127 | | systemd-bus-proxy | 127 | | systemd-network | 127 | | dbus | 81 | | polkitd | 127 | | unbound | 127 | | colord | 127 | | usbmuxd | 113 | | geoclue | 127 | | saslauth | 127 | | libstoragemgmt | 127 | | abrt | 127 | | setroubleshoot | 127 | | rtkit | 127 | | chrony | 127 | | radvd | 75 | | qemu | 107 | | nfsnobody | 127 | | pulse | 127 | | gnome-initial-setup | 127 | | postfix | 89 | | sshd | 74 | | usetr | 127 | +---------------------+------+ 24 rows in set (0.00 sec)

mysql> select avg(uid) from db3.user; +----------+ | avg(uid) | +----------+ | 73.5111 | +----------+

查找uid最小值的name mysql> select name from db3.user where uid = (select min(uid) from db3.user); +------+ | name | +------+ | bin | +------+ 1 row in set (0.00 sec)

mysql> select name,uid from db3.user where uid = (select min(uid) from db3.user); +------+------+ | name | uid | +------+------+ | bin | 2 | +------+------+ 1 row in set (0.00 sec)

借用另一个表里面查询本表 mysql> select name from db3.user where name in (select user from mysql.user where host="localhost"); Empty set (0.00 sec)

+++++++++++++++++++++++++++++++++++++++++++++++++++ 多表查询（将两个以上的表，按某个条件连接起来，取所要的数据） select 字段名列表 from 表名列表;笛卡尔集合（查询） select 字段名列表 from 表名列表 where 条件；

mysql> create database db4; Query OK, 1 row affected (0.00 sec)

mysql> create table db4.t1 select name,uid,homedir from db3.user limit 3; Query OK, 3 rows affected (0.03 sec) Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from db4.t1; +--------+------+----------+ | name | uid | homedir | +--------+------+----------+ | bin | 2 | /bin | | daemon | 3 | /sbin | | adm | 4 | /var/adm | +--------+------+----------+ 3 rows in set (0.00 sec)

mysql> create table db4.t2 select name,uid,homedir from db3.user limit 5; Query OK, 5 rows affected (0.03 sec) Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from db4.t2; +--------+------+----------------+ | name | uid | homedir | +--------+------+----------------+ | bin | 2 | /bin | | daemon | 3 | /sbin | | adm | 4 | /var/adm | | lp | 5 | /var/spool/lpd | | sync | 6 | /sbin | +--------+------+----------------+ 5 rows in set (0.00 sec)

不加条件 mysql> select * from t1,t2; ##迪卡尔（表少的行数*表多的行数） +--------+------+----------+--------+------+----------------+ | name | uid | homedir | name | uid | homedir | +--------+------+----------+--------+------+----------------+ | bin | 2 | /bin | bin | 2 | /bin | | daemon | 3 | /sbin | bin | 2 | /bin | | adm | 4 | /var/adm | bin | 2 | /bin | | bin | 2 | /bin | daemon | 3 | /sbin | | daemon | 3 | /sbin | daemon | 3 | /sbin | | adm | 4 | /var/adm | daemon | 3 | /sbin | | bin | 2 | /bin | adm | 4 | /var/adm | | daemon | 3 | /sbin | adm | 4 | /var/adm | | adm | 4 | /var/adm | adm | 4 | /var/adm | | bin | 2 | /bin | lp | 5 | /var/spool/lpd | | daemon | 3 | /sbin | lp | 5 | /var/spool/lpd | | adm | 4 | /var/adm | lp | 5 | /var/spool/lpd | | bin | 2 | /bin | sync | 6 | /sbin | | daemon | 3 | /sbin | sync | 6 | /sbin | | adm | 4 | /var/adm | sync | 6 | /sbin | +--------+------+----------+--------+------+----------------+ 15 rows in set (0.00 sec)

+++++++++++++++++++++++++++++++++++++++

连接查询 select 字段名列表 from 表1 left join 表2 on 条件; select 字段名列表 from 表1 right join 表2 on 条件;

mysql> create table db4.t3 select name,uid,homedir from db3.user limit 4; Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0

mysql> create table db4.t4 select name,uid,homedir from db3.user limit 6; Query OK, 6 rows affected (0.04 sec) Records: 6 Duplicates: 0 Warnings: 0

mysql> select * from db4.t3; +--------+------+----------------+ | name | uid | homedir | +--------+------+----------------+ | bin | 2 | /bin | | daemon | 3 | /sbin | | adm | 4 | /var/adm | | lp | 5 | /var/spool/lpd | +--------+------+----------------+ 4 rows in set (0.00 sec)

mysql> select * from db4.t4; +----------+------+----------------+ | name | uid | homedir | +----------+------+----------------+ | bin | 2 | /bin | | daemon | 3 | /sbin | | adm | 4 | /var/adm | | lp | 5 | /var/spool/lpd | | sync | 6 | /sbin | | shutdown | 7 | /sbin | +----------+------+----------------+ 6 rows in set (0.00 sec)

左连接 mysql> select * from t3 left join t4 on t3.uid = t4.uid; ##以左表为主显示所有记录，条件显示相同的记录 +--------+------+----------------+--------+------+----------------+ | name | uid | homedir | name | uid | homedir | +--------+------+----------------+--------+------+----------------+ | bin | 2 | /bin | bin | 2 | /bin | | daemon | 3 | /sbin | daemon | 3 | /sbin | | adm | 4 | /var/adm | adm | 4 | /var/adm | | lp | 5 | /var/spool/lpd | lp | 5 | /var/spool/lpd | +--------+------+----------------+--------+------+----------------+ 4 rows in set (0.00 sec)

右连接 mysql> select * from t3 right join t4 on t3.uid = t4.uid; ##以右表为主显示所有记录，条件显示相同的记录，不相同的用null显示 +--------+------+----------------+----------+------+----------------+ | name | uid | homedir | name | uid | homedir | +--------+------+----------------+----------+------+----------------+ | bin | 2 | /bin | bin | 2 | /bin | | daemon | 3 | /sbin | daemon | 3 | /sbin | | adm | 4 | /var/adm | adm | 4 | /var/adm | | lp | 5 | /var/spool/lpd | lp | 5 | /var/spool/lpd | | NULL | NULL | NULL | sync | 6 | /sbin | | NULL | NULL | NULL | shutdown | 7 | /sbin | +--------+------+----------------+----------+------+----------------+ 6 rows in set (0.00 sec)

mysql> select t3.name,t4.name from t3 right join t4 on t3.uid = t4.uid; ##以右表为主显示name记录，条件显示相同的记录，不相同的用null显示 +--------+----------+ | name | name | +--------+----------+ | bin | bin | | daemon | daemon | | adm | adm | | lp | lp | | NULL | sync | | NULL | shutdown | +--------+----------+ 6 rows in set (0.00 sec)

商品衣服库存信息表

销售信息表

对象学生

缴费信息表班级表就业表

扩展知识？

+++++++++++++++++++++++++++++++++++++++++++++++++++