4.1 复制表&多表查询
创建表的方式：复制表
复制表：复制表的字段和内容取决于SQL的查询结果，但是不包括表中的键值 将源表AAAA复制为新表XXXX mysql>use mysql; mysql> create table mysqlu select User,Host from user; Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select * from mysqlu; +---------------+-----------+ | User | Host | +---------------+-----------+ | centos | 127.0.0.1 | | vsftpd | 127.0.0.1 | | centos | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +---------------+-----------+ 6 rows in set (0.00 sec)
多表查询：
准备两张表
mysql> create table duobiao.user(name char(50),password char(1),uid smallint,gid smallint,describ char(200),homedir char(200),shell char(200)); Query OK, 0 rows affected (0.00 sec)
mysql> load data infile "/var/lib/mysql-files/passwd" into table user fields terminated by ":" lines terminated by "\n"; Query OK, 21 rows affected (0.00 sec) Records: 21 Deleted: 0 Skipped: 0 Warnings: 0
mysql> create table user2 select name,shell from user; Query OK, 21 rows affected (0.01 sec) Records: 21 Duplicates: 0 Warnings: 0
多表查询 mysql> select * from user,user2 where user.name=user2.name; +-----------------+----------+------+------+----------------------------+--------------------+----------------+-----------------+----------------+ | name | password | uid | gid | describ | homedir | shell | name | shell | +-----------------+----------+------+------+----------------------------+--------------------+----------------+-----------------+----------------+ | root | x | 0 | 0 | root | /root | /bin/bash | root | /bin/bash | | bin | x | 1 | 1 | bin | /bin | /sbin/nologin | bin | /sbin/nologin | | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin | daemon | /sbin/nologin | | adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin | adm | /sbin/nologin | | lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin | lp | /sbin/nologin | | sync | x | 5 | 0 | sync | /sbin | /bin/sync | sync | /bin/sync | | shutdown | x | 6 | 0 | shutdown | /sbin | /sbin/shutdown | shutdown | /sbin/shutdown | | halt | x | 7 | 0 | halt | /sbin | /sbin/halt | halt | /sbin/halt | | mail | x | 8 | 12 | mail | /var/spool/mail | /sbin/nologin | mail | /sbin/nologin | | operator | x | 11 | 0 | operator | /root | /sbin/nologin | operator | /sbin/nologin | | games | x | 12 | 100 | games | /usr/games | /sbin/nologin | games | /sbin/nologin | | ftp | x | 14 | 50 | FTP User | /var/ftp | /sbin/nologin | ftp | /sbin/nologin | | nobody | x | 99 | 99 | Nobody | / | /sbin/nologin | nobody | /sbin/nologin | | systemd-network | x | 192 | 192 | systemd Network Management | / | /sbin/nologin | systemd-network | /sbin/nologin | | dbus | x | 81 | 81 | System message bus | / | /sbin/nologin | dbus | /sbin/nologin | | polkitd | x | 999 | 998 | User for polkitd | / | /sbin/nologin | polkitd | /sbin/nologin | | sshd | x | 74 | 74 | Privilege-separated SSH | /var/empty/sshd | /sbin/nologin | sshd | /sbin/nologin | | postfix | x | 89 | 89 | | /var/spool/postfix | /sbin/nologin | postfix | /sbin/nologin | | mysql | x | 27 | 27 | MySQL Server | /var/lib/mysql | /bin/false | mysql | /bin/false | | ntp | x | 38 | 38 | | /etc/ntp | /sbin/nologin | ntp | /sbin/nologin | | www | x | 1000 | 1000 | | /home/www | /sbin/nologin | www | /sbin/nologin | +-----------------+----------+------+------+----------------------------+--------------------+----------------+-----------------+----------------+ 21 rows in set (0.00 sec)
mysql> select user.gid from user,user2 where user.name=user2.name; +------+ | gid | +------+ | 0 | | 1 | | 2 | | 4 | | 7 | | 0 | | 0 | | 0 | | 12 | | 0 | | 100 | | 50 | | 99 | | 192 | | 81 | | 998 | | 74 | | 89 | | 27 | | 38 | | 1000 | +------+ 21 rows in set (0.00 sec)
where 子查询
语法 使用where子查询 把内层的查询结果作为外层的查询条件 语法格式 select 字段名列表 from 表名 where 条件 in (select 字段名列表 from 表名 where 条件);
mysql> select * from user where name in (select name fro where name ="root"); +------+----------+------+------+---------+---------+-----------+ | name | password | uid | gid | describ | homedir | shell | +------+----------+------+------+---------+---------+-----------+ | root | x | 0 | 0 | root | /root | /bin/bash | +------+----------+------+------+---------+---------+-----------+ 1 row in set (0.00 sec)
左连接查询
基本用法 select 字段名列表 from 表A left join 表B on 条件表达式； 当条件成立时，以左表为主显示查询结果 mysql> insert into user2 (name) values ("user2"); Query OK, 1 row affected (0.00 sec) mysql> select * from user2 left join user on user2.name=user.name; +-----------------+----------------+-----------------+----------+------+------+----------------------------+--------------------+----------------+ | name | shell | name | password | uid | gid | describ | homedir | shell | +-----------------+----------------+-----------------+----------+------+------+----------------------------+--------------------+----------------+ | root | /bin/bash | root | x | 0 | 0 | root | /root | /bin/bash | | bin | /sbin/nologin | bin | x | 1 | 1 | bin | /bin | /sbin/nologin | | daemon | /sbin/nologin | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin | | adm | /sbin/nologin | adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin | | lp | /sbin/nologin | lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin | | sync | /bin/sync | sync | x | 5 | 0 | sync | /sbin | /bin/sync | | shutdown | /sbin/shutdown | shutdown | x | 6 | 0 | shutdown | /sbin | /sbin/shutdown | | halt | /sbin/halt | halt | x | 7 | 0 | halt | /sbin | /sbin/halt | | mail | /sbin/nologin | mail | x | 8 | 12 | mail | /var/spool/mail | /sbin/nologin | | operator | /sbin/nologin | operator | x | 11 | 0 | operator | /root | /sbin/nologin | | games | /sbin/nologin | games | x | 12 | 100 | games | /usr/games | /sbin/nologin | | ftp | /sbin/nologin | ftp | x | 14 | 50 | FTP User | /var/ftp | /sbin/nologin | | nobody | /sbin/nologin | nobody | x | 99 | 99 | Nobody | / | /sbin/nologin | | systemd-network | /sbin/nologin | systemd-network | x | 192 | 192 | systemd Network Management | / | /sbin/nologin | | dbus | /sbin/nologin | dbus | x | 81 | 81 | System message bus | / | /sbin/nologin | | polkitd | /sbin/nologin | polkitd | x | 999 | 998 | User for polkitd | / | /sbin/nologin | | sshd | /sbin/nologin | sshd | x | 74 | 74 | Privilege-separated SSH | /var/empty/sshd | /sbin/nologin | | postfix | /sbin/nologin | postfix | x | 89 | 89 | | /var/spool/postfix | /sbin/nologin | | mysql | /bin/false | mysql | x | 27 | 27 | MySQL Server | /var/lib/mysql | /bin/false | | ntp | /sbin/nologin | ntp | x | 38 | 38 | | /etc/ntp | /sbin/nologin | | www | /sbin/nologin | www | x | 1000 | 1000 | | /home/www | /sbin/nologin | | user2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +-----------------+----------------+-----------------+----------+------+------+----------------------------+--------------------+----------------+ 22 rows in set (0.00 sec) mysql> select * from user left join user2 on user.name=user2.name; +-----------------+----------+------+------+----------------------------+--------------------+----------------+-----------------+----------------+ | name | password | uid | gid | describ | homedir | shell | name | shell | +-----------------+----------+------+------+----------------------------+--------------------+----------------+-----------------+----------------+ | root | x | 0 | 0 | root | /root | /bin/bash | root | /bin/bash | | bin | x | 1 | 1 | bin | /bin | /sbin/nologin | bin | /sbin/nologin | | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin | daemon | /sbin/nologin | | adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin | adm | /sbin/nologin | | lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin | lp | /sbin/nologin | | sync | x | 5 | 0 | sync | /sbin | /bin/sync | sync | /bin/sync | | shutdown | x | 6 | 0 | shutdown | /sbin | /sbin/shutdown | shutdown | /sbin/shutdown | | halt | x | 7 | 0 | halt | /sbin | /sbin/halt | halt | /sbin/halt | | mail | x | 8 | 12 | mail | /var/spool/mail | /sbin/nologin | mail | /sbin/nologin | | operator | x | 11 | 0 | operator | /root | /sbin/nologin | operator | /sbin/nologin | | games | x | 12 | 100 | games | /usr/games | /sbin/nologin | games | /sbin/nologin | | ftp | x | 14 | 50 | FTP User | /var/ftp | /sbin/nologin | ftp | /sbin/nologin | | nobody | x | 99 | 99 | Nobody | / | /sbin/nologin | nobody | /sbin/nologin | | systemd-network | x | 192 | 192 | systemd Network Management | / | /sbin/nologin | systemd-network | /sbin/nologin | | dbus | x | 81 | 81 | System message bus | / | /sbin/nologin | dbus | /sbin/nologin | | polkitd | x | 999 | 998 | User for polkitd | / | /sbin/nologin | polkitd | /sbin/nologin | | sshd | x | 74 | 74 | Privilege-separated SSH | /var/empty/sshd | /sbin/nologin | sshd | /sbin/nologin | | postfix | x | 89 | 89 | | /var/spool/postfix | /sbin/nologin | postfix | /sbin/nologin | | mysql | x | 27 | 27 | MySQL Server | /var/lib/mysql | /bin/false | mysql | /bin/false | | ntp | x | 38 | 38 | | /etc/ntp | /sbin/nologin | ntp | /sbin/nologin | | www | x | 1000 | 1000 | | /home/www | /sbin/nologin | www | /sbin/nologin | | user | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +-----------------+----------+------+------+----------------------------+--------------------+----------------+-----------------+----------------+ 22 rows in set (0.00 sec)
右连接查询
基本用法 select 字段名列表 from 表A right join 表B on 条件表达式； 当条件成立时，以右表为主显示查询结果，没有相匹配时用NULL表 mysql> select * from user2 right join user on user.name=user2.name; +-----------------+----------------+-----------------+----------+------+------+----------------------------+--------------------+----------------+ | name | shell | name | password | uid | gid | describ | homedir | shell | +-----------------+----------------+-----------------+----------+------+------+----------------------------+--------------------+----------------+ | root | /bin/bash | root | x | 0 | 0 | root | /root | /bin/bash | | bin | /sbin/nologin | bin | x | 1 | 1 | bin | /bin | /sbin/nologin | | daemon | /sbin/nologin | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin | | adm | /sbin/nologin | adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin | | lp | /sbin/nologin | lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin | | sync | /bin/sync | sync | x | 5 | 0 | sync | /sbin | /bin/sync | | shutdown | /sbin/shutdown | shutdown | x | 6 | 0 | shutdown | /sbin | /sbin/shutdown | | halt | /sbin/halt | halt | x | 7 | 0 | halt | /sbin | /sbin/halt | | mail | /sbin/nologin | mail | x | 8 | 12 | mail | /var/spool/mail | /sbin/nologin | | operator | /sbin/nologin | operator | x | 11 | 0 | operator | /root | /sbin/nologin | | games | /sbin/nologin | games | x | 12 | 100 | games | /usr/games | /sbin/nologin | | ftp | /sbin/nologin | ftp | x | 14 | 50 | FTP User | /var/ftp | /sbin/nologin | | nobody | /sbin/nologin | nobody | x | 99 | 99 | Nobody | / | /sbin/nologin | | systemd-network | /sbin/nologin | systemd-network | x | 192 | 192 | systemd Network Management | / | /sbin/nologin | | dbus | /sbin/nologin | dbus | x | 81 | 81 | System message bus | / | /sbin/nologin | | polkitd | /sbin/nologin | polkitd | x | 999 | 998 | User for polkitd | / | /sbin/nologin | | sshd | /sbin/nologin | sshd | x | 74 | 74 | Privilege-separated SSH | /var/empty/sshd | /sbin/nologin | | postfix | /sbin/nologin | postfix | x | 89 | 89 | | /var/spool/postfix | /sbin/nologin | | mysql | /bin/false | mysql | x | 27 | 27 | MySQL Server | /var/lib/mysql | /bin/false | | ntp | /sbin/nologin | ntp | x | 38 | 38 | | /etc/ntp | /sbin/nologin | | www | /sbin/nologin | www | x | 1000 | 1000 | | /home/www | /sbin/nologin | | NULL | NULL | user | NULL | NULL | NULL | NULL | NULL | NULL | +-----------------+----------------+-----------------+----------+------+------+----------------------------+--------------------+----------------+ 22 rows in set (0.00 sec)
浙公网安备 33010602011771号