MySQL数据库基础（三）数据的导入导出、管理表记录、匹配条件

最新推荐文章于 2022-12-08 10:54:09 发布

哦吼吼吼tian

最新推荐文章于 2022-12-08 10:54:09 发布

阅读量269

点赞数

本文链接：https://blog.csdn.net/tian1345/article/details/104348087

版权

数据的导入导出

搜索路径

查看搜索路径

安装时已经自动创建
命令：mysql > show variables like “secure_file_priv”;

mysql> show  variables  like "secure_file_priv" ;
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)

[root@host51 ~]# ls -ld /var/lib/mysql-files/
drwxr-x---. 2 mysql mysql 6 11月 29 2016 /var/lib/mysql-files/

修改搜索路径

操作步骤：
1.以管理员身份登录mysql，查看其当前默认搜索路径
2.退出，进入到主配置文件/etc/my.cnf,添加新的搜索路径（注：一定要确认新的路径可以让mysql用户有读写执行权限）
3.重启mysqld服务
4.再次以管理员身份登录mysql，查看现在的默认路径

+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)

[root@host51 ~]# ls -ld /var/lib/mysql-files/
drwxr-x---. 2 mysql mysql 6 11月 29 2016 /var/lib/mysql-files/

[root@host51 ~]# vim  /etc/my.cnf
...
[mysqld]
secure_file_priv=/myload
validate_password_policy=0
validate_password_length=6
...

[root@host51 ~]# mkdir  /myload

[root@host51 ~]# ls  -ld  /myload
drwxr-xr-x. 2 root root 6 2月  15 11:14 /myload

[root@host51 ~]# chown  mysql  /myload

[root@host51 ~]# ls  -ld  /myload
drwxr-xr-x. 2 mysql root 6 2月  15 11:14 /myload

[root@host51 ~]# systemctl  restart  mysqld

[root@host51 ~]# mysql  -uroot  -p123456
mysql> show  variables  like  "secure_file_priv";
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| secure_file_priv | /myload/ |
+------------------+----------+
1 row in set (0.00 sec)

数据导入

数据导入：

批量存储数据,把系统文件的内容，存储到数据库下的表里
默认只有root用户有数据导入权限
步骤建库-------->建表------> 把系统文件拷贝到检索目录------导入数据------>查看数据
**命令格式：**mysql > load data infile “目录名/文件名” into table 库名.表名 fields terminated by “分隔符” lines terminated by “\n” ;

数据导入注意事项

1.字段分隔符要与文件一致
2.表字段类型和字段个数要与文件相匹配
3.导入数据时指定文件的绝对路径

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

mysql> create  table  db3.user(name  char(50) ,password  char(1)  ,uid  int  ,gid int  ,comment  varchar(200)   ,homedir  varchar(100)  ,shell  char(60) );
Query OK, 0 rows affected (0.01 sec)
#以/etc/passwd文件为导入数据

mysql> desc  db3.user;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| name     | char(50)     | YES  |     | NULL    |       |
| password | char(1)      | YES  |     | NULL    |       |
| uid      | int(11)      | YES  |     | NULL    |       |
| gid      | int(11)      | YES  |     | NULL    |       |
| comment  | varchar(200) | YES  |     | NULL    |       |
| homedir  | varchar(100) | YES  |     | NULL    |       |
| shell    | char(60)     | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
7 rows in set (0.01 sec)


[root@host51 ~]# cp  /etc/passwd  /myload/

mysql> load  data  infile  "/myload/passwd"  into  table  db3.user  fields  terminated  by ":"  lines  terminated  by  "\n" ;     #导入数据
Query OK, 20 rows affected (0.01 sec)
Records: 20  Deleted: 0  Skipped: 0  Warnings: 0

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  |
| systemd-network | x        |  192 |  192 | systemd Network Management | /                  | /sbin/nologin  |
| dbus            | x        |   81 |   81 | System message bus         | /                  | /sbin/nologin  |
| polkitd         | x        |  999 |  998 | User for polkitd           | /                  | /sbin/nologin  |
| sshd            | x        |   74 |   74 | Privilege-separated SSH    | /var/empty/sshd    | /sbin/nologin  |
| postfix         | x        |   89 |   89 |                            | /var/spool/postfix | /sbin/nologin  |
| chrony          | x        |  998 |  996 |                            | /var/lib/chrony    | /sbin/nologin  |
| mysql           | x        |   27 |   27 | MySQL Server               | /var/lib/mysql     | /bin/false     |
+-----------------+----------+------+------+----------------------------+--------------------+----------------+

mysql> alter  table  db3.user  add  id  int  primary key  auto_increment  first;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc   db3.user;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| name     | char(50)     | YES  |     | NULL    |                |
| password | char(1)      | YES  |     | NULL    |                |
| uid      | int(11)      | YES  |     | NULL    |                |
| gid      | int(11)      | YES  |     | NULL    |                |
| comment  | varchar(200) | YES  |     | NULL    |                |
| homedir  | varchar(100) | YES  |     | NULL    |                |
| shell    | char(60)     | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

mysql> alter table  db3.user  add  id  int  primary key auto_increment first;    #添加行号id的字段，将其设为主键，并且自增

mysql> select  *   from  db3.user  where  id=6;
+----+------+----------+------+------+---------+---------+-----------+
| id | name | password | uid  | gid  | comment | homedir | shell     |
+----+------+----------+------+------+---------+---------+-----------+
|  6 | sync | x        |    5 |    0 | sync    | /sbin   | /bin/sync |
+----+------+----------+------+------+---------+---------+-----------+
1 row in set (0.01 sec)

数据导出

命令格式：

格式一：mysql > select命令 into outfile “目录名/文件名”;
格式二：mysql > select命令 into outfile “目录名/文件名” fields terminated by “分隔符”;
格式三：mysql > select命令 into outfile “目录名/文件名” fields termianted by “分隔符” lines terminated by “\n”;

注意事项：

导出数据行数由SQL查询决定
导出的是表记录，不包括字段名
自动创建存储数据的文件
存储数据文件，具有唯一性

mysql> select  *  from  db2.stuinfo    into outfile   "/myload/one.txt" ;   #将db2库中sutinfo表中的数据导出到/myload/one.txt文件
Query OK, 1 row affected (0.00 sec)

[root@host51 ~]# cat  /myload/one.txt
23		girl	it


mysql> select  *  from  db2.t5    into outfile   "/myload/two.txt"    fields  terminated  by  "#";         
Query OK, 3 rows affected (0.00 sec)

[root@host51 ~]# cat  /myload/two.txt
1.1.1.1#21#allow
1.1.1.1#22#deny
2.1.1.1#22#deny


mysql> select  *  from  db2.t6    into outfile   "/myload/three.txt"    fields  terminated  by  "#"   lines  terminated  by "?";      
Query OK, 3 rows affected (0.00 sec)


[root@host51 ~]# cat  /myload/three.txt
11#qqq#133512#222344?12#eee#13332112#26744?13#rrr#004858#26989?


mysql> select  name ,shell , homedir  from  db3.user where id <=5 ;
+--------+---------------+----------------+
| name   | shell         | homedir        |
+--------+---------------+----------------+
| root   | /bin/bash     | /root          |
| bin    | /sbin/nologin | /bin           |
| daemon | /sbin/nologin | /sbin          |
| adm    | /sbin/nologin | /var/adm       |
| lp     | /sbin/nologin | /var/spool/lpd |
+--------+---------------+----------------+
5 rows in set (0.00 sec)


mysql> select  name ,shell , homedir  from  db3.user where id <=5   into outfile "/myload/four.txt";          #将db3库中user表内id小于等于5的字段数据导出到/myload/four.txt中
Query OK, 5 rows affected (0.00 sec)

[root@host51 ~]# cat  /myload/four.txt
root	/bin/bash	/root
bin	/sbin/nologin	/bin
daemon	/sbin/nologin	/sbin
adm	/sbin/nologin	/var/adm
lp	/sbin/nologin	/var/spool/lpd

管理表记录

增加表记录

语法格式

格式一：添加一条记录，给所有字段赋值
mysql > insert into 表名 values（字段值列表）;

格式二：添加多条记录，给所有字段赋值
mysql > insert into 表名 values（字段值列表），（字段值列表），（字段值列表）;

格式三：添加一条记录，给指定字段赋值
mysql > insert into 表名（字段名列表） values（字段值列表）;

格式四：添加多条记录，给指定字段赋值
mysql > insert into 表名（字段名列表） values（字段值列表），（字段值列表），（字段值列表）;

注意事项：

1.字段值要与字段类型相匹配
2.字符类型的字段，要用“ ”号括起来
3.依次给所有的字段赋值时，字段名可以省略
4.只给部分字段赋值时，必须要明确写出对应的字段名称
5.没有复制的字段使用默认值或自增长赋值

一次插入一条记录，给所有列赋值
mysql> insert  into db3.user  values  (30 , "bob"  ,"x",2020 , 2020 , "testuser" ,  "/home/bob" , "/bin/bash");
Query OK, 1 row affected (0.01 sec)

一次插入多条记录，给所有列赋值

mysql> insert  into db3.user  values  (40 , "tom"  ,"x",2030 , 2030 , "testuser" ,  "/home/bob" , "/sbin/nologin") , (50 , "tom"  ,"x",2030 , 2030 , "testuser" ,  "/home/bobbob" , "/sbin/nologin");
Query OK, 2 rows affected (0.00 sec)
Records: 2  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  |
| 14 | systemd-network | x        |  192 |  192 | systemd Network Management | /                  | /sbin/nologin  |
| 15 | dbus            | x        |   81 |   81 | System message bus         | /                  | /sbin/nologin  |
| 16 | polkitd         | x        |  999 |  998 | User for polkitd           | /                  | /sbin/nologin  |
| 17 | sshd            | x        |   74 |   74 | Privilege-separated SSH    | /var/empty/sshd    | /sbin/nologin  |
| 18 | postfix         | x        |   89 |   89 |                            | /var/spool/postfix | /sbin/nologin  |
| 19 | chrony          | x        |  998 |  996 |                            | /var/lib/chrony    | /sbin/nologin  |
| 20 | mysql           | x        |   27 |   27 | MySQL Server               | /var/lib/mysql     | /bin/false     |
| 30 | bob             | x        | 2020 | 2020 | testuser                   | /home/bob          | /bin/bash      |
| 40 | tom             | x        | 2030 | 2030 | testuser                   | /home/bob          | /sbin/nologin  |
| 50 | tom             | x        | 2030 | 2030 | testuser                   | /home/bobbob       | /sbin/nologin  |
+----+-----------------+----------+------+------+----------------------------+--------------------+----------------+
23 rows in set (0.00 sec)


一次插入一条记录，给指定列表赋值
mysql> insert  into  db3.user(name)   values("alice");
Query OK, 1 row affected (0.00 sec)

一次插入多条记录，给指定列表赋值
mysql> insert  into  db3.user(name,uid,gid)   values("tomA" ,123 ,123),("tomB" ,234 ,234),("tomC" ,345 ,345) ;
Query OK, 3 rows affected (0.00 sec)
Records: 3  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  |
| 14 | systemd-network | x        |  192 |  192 | systemd Network Management | /                  | /sbin/nologin  |
| 15 | dbus            | x        |   81 |   81 | System message bus         | /                  | /sbin/nologin  |
| 16 | polkitd         | x        |  999 |  998 | User for polkitd           | /                  | /sbin/nologin  |
| 17 | sshd            | x        |   74 |   74 | Privilege-separated SSH    | /var/empty/sshd    | /sbin/nologin  |
| 18 | postfix         | x        |   89 |   89 |                            | /var/spool/postfix | /sbin/nologin  |
| 19 | chrony          | x        |  998 |  996 |                            | /var/lib/chrony    | /sbin/nologin  |
| 20 | mysql           | x        |   27 |   27 | MySQL Server               | /var/lib/mysql     | /bin/false     |
| 30 | bob             | x        | 2020 | 2020 | testuser                   | /home/bob          | /bin/bash      |
| 40 | tom             | x        | 2030 | 2030 | testuser                   | /home/bob          | /sbin/nologin  |
| 50 | tom             | x        | 2030 | 2030 | testuser                   | /home/bobbob       | /sbin/nologin  |
| 51 | alice           | NULL     | NULL | NULL | NULL                       | NULL               | NULL           |
| 52 | tomA            | NULL     |  123 |  123 | NULL                       | NULL               | NULL           |
| 53 | tomB            | NULL     |  234 |  234 | NULL                       | NULL               | NULL           |
| 54 | tomC            | NULL     |  345 |  345 | NULL                       | NULL               | NULL           |
+----+-----------------+----------+------+------+----------------------------+--------------------+----------------+
27 rows in set (0.00 sec)
#不指定id值，id值会默认自增长

查询表记录

语法格式：

格式一：查所有字段
mysql > select 字段1，……，字段N from 库名.表名;

格式二：条件查询
mysql > select 字段1，……，字段N from 库名.表名 where 条件表达式;

注意事项：

*表示所有字段
查看当前库表记录时库名可以省略
字段列表决定显示列个数
条件决定显示行的个数

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  |
| 14 | systemd-network | x        |  192 |  192 | systemd Network Management | /                  | /sbin/nologin  |
| 15 | dbus            | x        |   81 |   81 | System message bus         | /                  | /sbin/nologin  |
| 16 | polkitd         | x        |  999 |  998 | User for polkitd           | /                  | /sbin/nologin  |
| 17 | sshd            | x        |   74 |   74 | Privilege-separated SSH    | /var/empty/sshd    | /sbin/nologin  |
| 18 | postfix         | x        |   89 |   89 |                            | /var/spool/postfix | /sbin/nologin  |
| 19 | chrony          | x        |  998 |  996 |                            | /var/lib/chrony    | /sbin/nologin  |
| 20 | mysql           | x        |   27 |   27 | MySQL Server               | /var/lib/mysql     | /bin/false     |
| 30 | bob             | x        | 2020 | 2020 | testuser                   | /home/bob          | /bin/bash      |
| 40 | tom             | x        | 2030 | 2030 | testuser                   | /home/bob          | /sbin/nologin  |
| 50 | tom             | x        | 2030 | 2030 | testuser                   | /home/bobbob       | /sbin/nologin  |
| 51 | alice           | NULL     | NULL | NULL | NULL                       | NULL               | NULL           |
| 52 | tomA            | NULL     |  123 |  123 | NULL                       | NULL               | NULL           |
| 53 | tomB            | NULL     |  234 |  234 | NULL                       | NULL               | NULL           |
| 54 | tomC            | NULL     |  345 |  345 | NULL                       | NULL               | NULL           |
+----+-----------------+----------+------+------+----------------------------+--------------------+----------------+
27 rows in set (0.00 sec)
mysql> select  *  from  db3.user  where  id<=10;
+----+----------+----------+------+------+----------+-----------------+----------------+
| 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  |
+----+----------+----------+------+------+----------+-----------------+----------------+
10 rows in set (0.00 sec)
mysql> select  name ,shell from  db3.user  where shell="/sbin/nologin";
+-----------------+---------------+
| name            | shell         |
+-----------------+---------------+
| bin             | /sbin/nologin |
| daemon          | /sbin/nologin |
| adm             | /sbin/nologin |
| lp              | /sbin/nologin |
| mail            | /sbin/nologin |
| operator        | /sbin/nologin |
| games           | /sbin/nologin |
| ftp             | /sbin/nologin |
| nobody          | /sbin/nologin |
| systemd-network | /sbin/nologin |
| dbus            | /sbin/nologin |
| polkitd         | /sbin/nologin |
| sshd            | /sbin/nologin |
| postfix         | /sbin/nologin |
| chrony          | /sbin/nologin |
| tom             | /sbin/nologin |
| tom             | /sbin/nologin |
+-----------------+---------------+
17 rows in set (0.00 sec)

更新表记录

语法格式：

格式一：批量更新
mysql > update 库名.表名 set 字段名=值，字段名=值，……;

格式二：条件匹配更新
mysql > update 库名.表名 set 字段名=值，字段名=值，字段名=值，…… where 条件表达式;

注意事项：

1.字段值要与字段类型相匹配
2.对于字符类型的字段，值要用双引号括起来
3.若不使用where限定条件，会更新所有记录字段值
4.限定条件时，只更新匹配条件的记录的字段值

mysql> update  db3.user  set  password="a" ,comment="student";
Query OK, 27 rows affected (0.00 sec)
Rows matched: 27  Changed: 27  Warnings: 0

mysql> select  *  from  db3.user;
+----+-----------------+----------+------+------+---------+--------------------+----------------+
| id | name            | password | uid  | gid  | comment | homedir            | shell          |
+----+-----------------+----------+------+------+---------+--------------------+----------------+
|  1 | root            | a        |    0 |    0 | student | /root              | /bin/bash      |
|  2 | bin             | a        |    1 |    1 | student | /bin               | /sbin/nologin  |
|  3 | daemon          | a        |    2 |    2 | student | /sbin              | /sbin/nologin  |
|  4 | adm             | a        |    3 |    4 | student | /var/adm           | /sbin/nologin  |
|  5 | lp              | a        |    4 |    7 | student | /var/spool/lpd     | /sbin/nologin  |
|  6 | sync            | a        |    5 |    0 | student | /sbin              | /bin/sync      |
|  7 | shutdown        | a        |    6 |    0 | student | /sbin              | /sbin/shutdown |
|  8 | halt            | a        |    7 |    0 | student | /sbin              | /sbin/halt     |
|  9 | mail            | a        |    8 |   12 | student | /var/spool/mail    | /sbin/nologin  |
| 10 | operator        | a        |   11 |    0 | student | /root              | /sbin/nologin  |
| 11 | games           | a        |   12 |  100 | student | /usr/games         | /sbin/nologin  |
| 12 | ftp             | a        |   14 |   50 | student | /var/ftp           | /sbin/nologin  |
| 13 | nobody          | a        |   99 |   99 | student | /                  | /sbin/nologin  |
| 14 | systemd-network | a        |  192 |  192 | student | /                  | /sbin/nologin  |
| 15 | dbus            | a        |   81 |   81 | student | /                  | /sbin/nologin  |
| 16 | polkitd         | a        |  999 |  998 | student | /                  | /sbin/nologin  |
| 17 | sshd            | a        |   74 |   74 | student | /var/empty/sshd    | /sbin/nologin  |
| 18 | postfix         | a        |   89 |   89 | student | /var/spool/postfix | /sbin/nologin  |
| 19 | chrony          | a        |  998 |  996 | student | /var/lib/chrony    | /sbin/nologin  |
| 20 | mysql           | a        |   27 |   27 | student | /var/lib/mysql     | /bin/false     |
| 30 | bob             | a        | 2020 | 2020 | student | /home/bob          | /bin/bash      |
| 40 | tom             | a        | 2030 | 2030 | student | /home/bob          | /sbin/nologin  |
| 50 | tom             | a        | 2030 | 2030 | student | /home/bobbob       | /sbin/nologin  |
| 51 | alice           | a        | NULL | NULL | student | NULL               | NULL           |
| 52 | tomA            | a        |  123 |  123 | student | NULL               | NULL           |
| 53 | tomB            | a        |  234 |  234 | student | NULL               | NULL           |
| 54 | tomC            | a        |  345 |  345 | student | NULL               | NULL           |
+----+-----------------+----------+------+------+---------+--------------------+----------------+
27 rows in set (0.00 sec)

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

mysql> select  *  from  db3.user;
+----+-----------------+----------+------+------+---------+--------------------+----------------+
| id | name            | password | uid  | gid  | comment | homedir            | shell          |
+----+-----------------+----------+------+------+---------+--------------------+----------------+
|  1 | root            | x        |    0 |    0 | student | /root              | /bin/bash      |
|  2 | bin             | a        |    1 |    1 | student | /bin               | /sbin/nologin  |
|  3 | daemon          | a        |    2 |    2 | student | /sbin              | /sbin/nologin  |
|  4 | adm             | a        |    3 |    4 | student | /var/adm           | /sbin/nologin  |
|  5 | lp              | a        |    4 |    7 | student | /var/spool/lpd     | /sbin/nologin  |
|  6 | sync            | a        |    5 |    0 | student | /sbin              | /bin/sync      |
|  7 | shutdown        | a        |    6 |    0 | student | /sbin              | /sbin/shutdown |
|  8 | halt            | a        |    7 |    0 | student | /sbin              | /sbin/halt     |
|  9 | mail            | a        |    8 |   12 | student | /var/spool/mail    | /sbin/nologin  |
| 10 | operator        | a        |   11 |    0 | student | /root              | /sbin/nologin  |
| 11 | games           | a        |   12 |  100 | student | /usr/games         | /sbin/nologin  |
| 12 | ftp             | a        |   14 |   50 | student | /var/ftp           | /sbin/nologin  |
| 13 | nobody          | a        |   99 |   99 | student | /                  | /sbin/nologin  |
| 14 | systemd-network | a        |  192 |  192 | student | /                  | /sbin/nologin  |
| 15 | dbus            | a        |   81 |   81 | student | /                  | /sbin/nologin  |
| 16 | polkitd         | a        |  999 |  998 | student | /                  | /sbin/nologin  |
| 17 | sshd            | a        |   74 |   74 | student | /var/empty/sshd    | /sbin/nologin  |
| 18 | postfix         | a        |   89 |   89 | student | /var/spool/postfix | /sbin/nologin  |
| 19 | chrony          | a        |  998 |  996 | student | /var/lib/chrony    | /sbin/nologin  |
| 20 | mysql           | a        |   27 |   27 | student | /var/lib/mysql     | /bin/false     |
| 30 | bob             | a        | 2020 | 2020 | student | /home/bob          | /bin/bash      |
| 40 | tom             | a        | 2030 | 2030 | student | /home/bob          | /sbin/nologin  |
| 50 | tom             | a        | 2030 | 2030 | student | /home/bobbob       | /sbin/nologin  |
| 51 | alice           | a        | NULL | NULL | student | NULL               | NULL           |
| 52 | tomA            | a        |  123 |  123 | student | NULL               | NULL           |
| 53 | tomB            | a        |  234 |  234 | student | NULL               | NULL           |
| 54 | tomC            | a        |  345 |  345 | student | NULL               | NULL           |
+----+-----------------+----------+------+------+---------+--------------------+----------------+
27 rows in set (0.00 sec)

删除表记录

语法格式：

格式一：条件匹配删除
mysql > delete from 库名.表名 where 条件表达式;

格式二：删除所有记录
mysql > delete from 库名.表名;

注意事项：

不加条件删除表中所有的行

mysql> select   *  from  db3.user  where  id>21;
+----+-------+----------+------+------+---------+--------------+---------------+
| id | name  | password | uid  | gid  | comment | homedir      | shell         |
+----+-------+----------+------+------+---------+--------------+---------------+
| 30 | bob   | a        | 2020 | 2020 | student | /home/bob    | /bin/bash     |
| 40 | tom   | a        | 2030 | 2030 | student | /home/bob    | /sbin/nologin |
| 50 | tom   | a        | 2030 | 2030 | student | /home/bobbob | /sbin/nologin |
| 51 | alice | a        | NULL | NULL | student | NULL         | NULL          |
| 52 | tomA  | a        |  123 |  123 | student | NULL         | NULL          |
| 53 | tomB  | a        |  234 |  234 | student | NULL         | NULL          |
| 54 | tomC  | a        |  345 |  345 | student | NULL         | NULL          |
+----+-------+----------+------+------+---------+--------------+---------------+
7 rows in set (0.00 sec)

mysql> delete  from  db3.user  where  id>21;
Query OK, 7 rows affected (0.00 sec)

mysql> select   *  from  db3.user  where  id>21;
Empty set (0.00 sec)

匹配条件

基本匹配条件（适用于 select update delete）

数值比较
字段必须是数值类型

类型	比较	例子
=	相等	uid=3
>	大于	uid > 3
>=	大于或等于	uid >= 3
<	小于	uid < 3
<=	小于或等于	uid <= 3
!=	不相等	uid != 3

字符比较/匹配空/非空
字段必须是字符类型

类型	比较	例子
=	相等	name = “root”
!=	不相等	name != “root”
is null	空	shell is null
is not null	非空	shell is not null

逻辑匹配
多个判断条件时使用

类型	用途	格式
or	逻辑或	条件1 or 条件2 or 条件3 ，某一个条件成立即可
and	逻辑与	条件1 and 条件2 and 条件3 ，所有条件同时成立
! 或 not	逻辑非	取反

范围匹配
匹配范围内的任意一个值即可

类型	比较
in（值列表）	在…里
not in（值列表）	不在…里
between 数字 and 数字	在…之间

数值比较:

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

mysql> select name ,uid  ,gid from  db3.user where  uid=gid;   #找uid和gid相等的字段，列出它的name，uid，gid字段
+-----------------+------+------+
| name            | uid  | gid  |
+-----------------+------+------+
| root            |    0 |    0 |
| bin             |    1 |    1 |
| daemon          |    2 |    2 |
| nobody          |   99 |   99 |
| systemd-network |  192 |  192 |
| dbus            |   81 |   81 |
| sshd            |   74 |   74 |
| postfix         |   89 |   89 |
| mysql           |   27 |   27 |
+-----------------+------+------+
9 rows in set (0.00 sec)

mysql> select name ,uid  ,gid from  db3.user where  uid!=gid;   #不等
+----------+------+------+
| name     | uid  | gid  |
+----------+------+------+
| adm      |    3 |    4 |
| lp       |    4 |    7 |
| sync     |    5 |    0 |
| shutdown |    6 |    0 |
| halt     |    7 |    0 |
| mail     |    8 |   12 |
| operator |   11 |    0 |
| games    |   12 |  100 |
| ftp      |   14 |   50 |
| polkitd  |  999 |  998 |
| chrony   |  998 |  996 |
+----------+------+------+
11 rows in set (0.00 sec)

字符比较：

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

mysql> select  name ,shell  from  db3.user  where  shell != "/bin/bash" ;
+-----------------+----------------+
| name            | shell          |
+-----------------+----------------+
| bin             | /sbin/nologin  |
| daemon          | /sbin/nologin  |
| adm             | /sbin/nologin  |
| lp              | /sbin/nologin  |
| sync            | /bin/sync      |
| shutdown        | /sbin/shutdown |
| halt            | /sbin/halt     |
| mail            | /sbin/nologin  |
| operator        | /sbin/nologin  |
| games           | /sbin/nologin  |
| ftp             | /sbin/nologin  |
| nobody          | /sbin/nologin  |
| systemd-network | /sbin/nologin  |
| dbus            | /sbin/nologin  |
| polkitd         | /sbin/nologin  |
| sshd            | /sbin/nologin  |
| postfix         | /sbin/nologin  |
| chrony          | /sbin/nologin  |
| mysql           | /bin/false     |
+-----------------+----------------+
19 rows in set (0.00 sec)

空  非空
mysql> insert  into   db3.user(name)  values("bob"),("tom"),("haha");
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select *  from  db3.user  where  uid  is  null;
+----+------+----------+------+------+---------+---------+-------+
| id | name | password | uid  | gid  | comment | homedir | shell |
+----+------+----------+------+------+---------+---------+-------+
| 55 | bob  | NULL     | NULL | NULL | NULL    | NULL    | NULL  |
| 56 | tom  | NULL     | NULL | NULL | NULL    | NULL    | NULL  |
| 57 | haha | NULL     | NULL | NULL | NULL    | NULL    | NULL  |
+----+------+----------+------+------+---------+---------+-------+
3 rows in set (0.00 sec)



逻辑匹配（多个匹配条件）:

mysql> select  name , uid   from   db3.user  where  shell="/bin/bash" and  name="root";
+------+------+
| name | uid  |
+------+------+
| root |    0 |
+------+------+
1 row in set (0.00 sec)

mysql> select  name  from   db3.user  where  name="apache"  or  name="sync"  or  name="mysql" ;
+-------+
| name  |
+-------+
| sync  |
| mysql |
+-------+
2 rows in set (0.00 sec)

mysql> select  name  ,shell  from  db3.user  where  shell  != "/bin/bash"  ;
+-----------------+----------------+
| name            | shell          |
+-----------------+----------------+
| bin             | /sbin/nologin  |
| daemon          | /sbin/nologin  |
| adm             | /sbin/nologin  |
| lp              | /sbin/nologin  |
| sync            | /bin/sync      |
| shutdown        | /sbin/shutdown |
| halt            | /sbin/halt     |
| mail            | /sbin/nologin  |
| operator        | /sbin/nologin  |
| games           | /sbin/nologin  |
| ftp             | /sbin/nologin  |
| nobody          | /sbin/nologin  |
| systemd-network | /sbin/nologin  |
| dbus            | /sbin/nologin  |
| polkitd         | /sbin/nologin  |
| sshd            | /sbin/nologin  |
| postfix         | /sbin/nologin  |
| chrony          | /sbin/nologin  |
| mysql           | /bin/false     |
+-----------------+----------------+
19 rows in set (0.00 sec)


范围匹配 in 、 not in 、between...and：

mysql> select  name , uid  from  db3.user  where  uid  in (1,9,20,7);
+------+------+
| name | uid  |
+------+------+
| bin  |    1 |
| halt |    7 |
+------+------+
2 rows in set (0.00 sec)

mysql> select  name , shell  from  db3.user  where  shell  in ("/bin/bash" , "/sbin/nologin");     #查找/bin/bash , /sbin/nologin的登录shell，显示其name以及shell字段
+-----------------+---------------+
| name            | shell         |
+-----------------+---------------+
| root            | /bin/bash     |
| bin             | /sbin/nologin |
| daemon          | /sbin/nologin |
| adm             | /sbin/nologin |
| lp              | /sbin/nologin |
| mail            | /sbin/nologin |
| operator        | /sbin/nologin |
| games           | /sbin/nologin |
| ftp             | /sbin/nologin |
| nobody          | /sbin/nologin |
| systemd-network | /sbin/nologin |
| dbus            | /sbin/nologin |
| polkitd         | /sbin/nologin |
| sshd            | /sbin/nologin |
| postfix         | /sbin/nologin |
| chrony          | /sbin/nologin |
+-----------------+---------------+
16 rows in set (0.00 sec)

mysql> select  name , shell  from  db3.user  where  shell not  in ("bin/bash" , "/sbin/nologin");     #查找不是/bin/bash,/sbin/nologin的name字段和shell字段  
+----------+----------------+
| name     | shell          |
+----------+----------------+
| root     | /bin/bash      |
| sync     | /bin/sync      |
| shutdown | /sbin/shutdown |
| halt     | /sbin/halt     |
| mysql    | /bin/false     |
+----------+----------------+
5 rows in set (0.00 sec)

mysql> select  name , uid  from  db3.user  where  uid  between  10 and 30;      #查找uid在10到30之间的name和uid字段，包含10和30本身
+----------+------+
| name     | uid  |
+----------+------+
| operator |   11 |
| games    |   12 |
| ftp      |   14 |
| mysql    |   27 |
+----------+------+
4 rows in set (0.00 sec)

mysql> select  name , uid  from  db3.user  where  uid  between  1 and 10;     #查找uid在1到10之间的name和uid字段，包含1和10
+----------+------+
| name     | uid  |
+----------+------+
| bin      |    1 |
| daemon   |    2 |
| adm      |    3 |
| lp       |    4 |
| sync     |    5 |
| shutdown |    6 |
| halt     |    7 |
| mail     |    8 |
+----------+------+
8 rows in set (0.00 sec)

高级匹配条件（适用于 select update delete）

模糊查询

用法：
where 字段名 like ‘通配符’
_ 表示一个字符
% 表示0~n个字符

正则表达式

用法：
where 字段名 regexp ‘正则表达式’
正则元字符 ^ $ . [] * |

四则运算
字段必须是数值类型

符号	用途	例子
+	加法	uid + uid
-	减法	uid - uid
*	乘法	uid * uid
/	除法	uid / uid
%	取余数（求模）	uid % uid
()	提高优先级	(uid + uid) /2

mysql> select  name from  db3.user  where  name  like '_ _ _';    #查找name字段是三个字符的
+------+
| name |
+------+
| bin  |
| adm  |
| ftp  |
| bob  |
| tom  |
+------+
5 rows in set (0.00 sec)

mysql> select  name from  db3.user  where  name  like '_ _ _ _';    #查找name字段是四个字符的
+------+
| name |
+------+
| root |
| sync |
| halt |
| mail |
| dbus |
| sshd |
| haha |
+------+
7 rows in set (0.00 sec)

mysql> select  name from  db3.user  where  name  like '%a%';
#查找name字段包含字母a的
+----------+
| name     |
+----------+
| daemon   |
| adm      |
| halt     |
| mail     |
| operator |
| games    |
| haha     |
+----------+
7 rows in set (0.00 sec)

mysql> select  name from  db3.user  where  name  like '%_ _ _ _ _%';     #查找name字段有至少五个字符 的
+-----------------+
| name            |
+-----------------+
| daemon          |
| shutdown        |
| operator        |
| games           |
| nobody          |
| systemd-network |
| polkitd         |
| postfix         |
| chrony          |
| mysql           |
+-----------------+
10 rows in set (0.00 sec)

mysql> select  name  from  db3.user  where  name  regexp  '^[ab]';
#查找name字段以a或者b开头的
+------+
| name |
+------+
| bin  |
| adm  |
| bob  |
+------+
3 rows in set (0.00 sec)

mysql> select  name  from  db3.user  where  name  regexp  '^a|^b';
#查找name字段以a或者b开头的
+------+
| name |
+------+
| bin  |
| adm  |
| bob  |
+------+
3 rows in set (0.00 sec)

mysql> insert  into  db3.user(name)  values ("haha2"),("lala6"),("hei8hei");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select  name  from  db3.user  where  name  regexp  '[0-9]';
#查找name字段包含数字的
+---------+
| name    |
+---------+
| haha2   |
| lala6   |
| hei8hei |
+---------+
3 rows in set (0.00 sec)

mysql> select  name  from  db3.user  where  name  regexp  '.*[0-9].*';         #查找数字在中间的
+---------+
| name    |
+---------+
| haha2   |
| lala6   |
| hei8hei |
+---------+
3 rows in set (0.00 sec)

四则运算（select 和 update）:

mysql> select  name ,uid ,gid  from  user  where  name ="mysql";
+-------+------+------+
| name  | uid  | gid  |
+-------+------+------+
| mysql |   27 |   27 |
+-------+------+------+
1 row in set (0.00 sec)


mysql> select  name ,uid ,gid ,uid+gid zonghe  from  user  where  name ="mysql";    #查找name等于mysql的字段，使其uid和gid相加，为zonghe字段
+-------+------+------+--------+
| name  | uid  | gid  | zonghe |
+-------+------+------+--------+
| mysql |   27 |   27 |     54 |
+-------+------+------+--------+
1 row in set (0.00 sec)

mysql> select  name ,uid ,gid ,uid+gid zonghe ,(uid+gid)/2 pingjun  from  user  where  name ="mysql";     #查找name等于mysql的字段，使其uid和gid相加，为zonghe字段，再求他们的平均数，为pingjun字段
+-------+------+------+--------+---------+
| name  | uid  | gid  | zonghe | pingjun |
+-------+------+------+--------+---------+
| mysql |   27 |   27 |     54 | 27.0000 |
+-------+------+------+--------+---------+
1 row in set (0.00 sec)

mysql> select  *  from db3.user  where  id%2 =0;   #查找user表中id是偶数
+----+-----------------+----------+------+------+---------+--------------------+---------------+
| id | name            | password | uid  | gid  | comment | homedir            | shell         |
+----+-----------------+----------+------+------+---------+--------------------+---------------+
|  2 | bin             | a        |    1 |    1 | student | /bin               | /sbin/nologin |
|  4 | adm             | a        |    3 |    4 | student | /var/adm           | /sbin/nologin |
|  6 | sync            | a        |    5 |    0 | student | /sbin              | /bin/sync     |
|  8 | halt            | a        |    7 |    0 | student | /sbin              | /sbin/halt    |
| 10 | operator        | a        |   11 |    0 | student | /root              | /sbin/nologin |
| 12 | ftp             | a        |   14 |   50 | student | /var/ftp           | /sbin/nologin |
| 14 | systemd-network | a        |  192 |  192 | student | /                  | /sbin/nologin |
| 16 | polkitd         | a        |  999 |  998 | student | /                  | /sbin/nologin |
| 18 | postfix         | a        |   89 |   89 | student | /var/spool/postfix | /sbin/nologin |
| 20 | mysql           | a        |   27 |   27 | student | /var/lib/mysql     | /bin/false    |
| 56 | tom             | NULL     | NULL | NULL | NULL    | NULL               | NULL          |
| 58 | haha2           | NULL     | NULL | NULL | NULL    | NULL               | NULL          |
| 60 | hei8hei         | NULL     | NULL | NULL | NULL    | NULL               | NULL          |
+----+-----------------+----------+------+------+---------+--------------------+---------------+
13 rows in set (0.00 sec)


mysql> select  name ,uid  from  db3.user  where  uid <=5  ;  
#查找uid小于等于5的字段
+--------+------+
| name   | uid  |
+--------+------+
| root   |    0 |
| bin    |    1 |
| daemon |    2 |
| adm    |    3 |
| lp     |    4 |
| sync   |    5 |
+--------+------+
6 rows in set (0.00 sec)

mysql> update  db3.user  set  uid=uid+1 where uid <=5;
#使uid小于等于5的加一
Query OK, 6 rows affected (0.01 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> select  name ,uid  from  db3.user  where  uid <=5  ;
#查看uid小于等于5的字段
+--------+------+
| name   | uid  |
+--------+------+
| root   |    1 |
| bin    |    2 |
| daemon |    3 |
| adm    |    4 |
| lp     |    5 |
+--------+------+
5 rows in set (0.00 sec)


mysql> select  name ,uid  from  db3.user  where  uid <=6 ;
+----------+------+
| name     | uid  |
+----------+------+
| root     |    1 |
| bin      |    2 |
| daemon   |    3 |
| adm      |    4 |
| lp       |    5 |
| sync     |    6 |
| shutdown |    6 |
+----------+------+
7 rows in set (0.00 sec)

mysql> update  db3.user  set  uid=uid-1 where uid <=5;
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> select  name ,uid  from  db3.user  where  uid <=5  ;
+--------+------+
| name   | uid  |
+--------+------+
| root   |    0 |
| bin    |    1 |
| daemon |    2 |
| adm    |    3 |
| lp     |    4 |
+--------+------+
5 rows in set (0.00 sec)

mysql> alter  table db3.user  add  age  tinyint  unsigned  default  20  after name;    #在name字段后面加入age字段，默认为20
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select  *  from  db3.user  ;
+----+-----------------+------+----------+------+------+---------+--------------------+----------------+
| id | name            | age  | password | uid  | gid  | comment | homedir            | shell          |
+----+-----------------+------+----------+------+------+---------+--------------------+----------------+
|  1 | root            |   20 | x        |    0 |    0 | student | /root              | /bin/bash      |
|  2 | bin             |   20 | a        |    1 |    1 | student | /bin               | /sbin/nologin  |
|  3 | daemon          |   20 | a        |    2 |    2 | student | /sbin              | /sbin/nologin  |
|  4 | adm             |   20 | a        |    3 |    4 | student | /var/adm           | /sbin/nologin  |
|  5 | lp              |   20 | a        |    4 |    7 | student | /var/spool/lpd     | /sbin/nologin  |
|  6 | sync            |   20 | a        |    6 |    0 | student | /sbin              | /bin/sync      |
|  7 | shutdown        |   20 | a        |    6 |    0 | student | /sbin              | /sbin/shutdown |
|  8 | halt            |   20 | a        |    7 |    0 | student | /sbin              | /sbin/halt     |
|  9 | mail            |   20 | a        |    8 |   12 | student | /var/spool/mail    | /sbin/nologin  |
| 10 | operator        |   20 | a        |   11 |    0 | student | /root              | /sbin/nologin  |
| 11 | games           |   20 | a        |   12 |  100 | student | /usr/games         | /sbin/nologin  |
| 12 | ftp             |   20 | a        |   14 |   50 | student | /var/ftp           | /sbin/nologin  |
| 13 | nobody          |   20 | a        |   99 |   99 | student | /                  | /sbin/nologin  |
| 14 | systemd-network |   20 | a        |  192 |  192 | student | /                  | /sbin/nologin  |
| 15 | dbus            |   20 | a        |   81 |   81 | student | /                  | /sbin/nologin  |
| 16 | polkitd         |   20 | a        |  999 |  998 | student | /                  | /sbin/nologin  |
| 17 | sshd            |   20 | a        |   74 |   74 | student | /var/empty/sshd    | /sbin/nologin  |
| 18 | postfix         |   20 | a        |   89 |   89 | student | /var/spool/postfix | /sbin/nologin  |
| 19 | chrony          |   20 | a        |  998 |  996 | student | /var/lib/chrony    | /sbin/nologin  |
| 20 | mysql           |   20 | a        |   27 |   27 | student | /var/lib/mysql     | /bin/false     |
| 55 | bob             |   20 | NULL     | NULL | NULL | NULL    | NULL               | NULL           |
| 56 | tom             |   20 | NULL     | NULL | NULL | NULL    | NULL               | NULL           |
| 57 | haha            |   20 | NULL     | NULL | NULL | NULL    | NULL               | NULL           |
| 58 | haha2           |   20 | NULL     | NULL | NULL | NULL    | NULL               | NULL           |
| 59 | lala6           |   20 | NULL     | NULL | NULL | NULL    | NULL               | NULL           |
| 60 | hei8hei         |   20 | NULL     | NULL | NULL | NULL    | NULL               | NULL           |
+----+-----------------+------+----------+------+------+---------+--------------------+----------------+
26 rows in set (0.00 sec)

mysql> select  name ,age ,2020-age  born  from  db3.user  where name="root";       #查找name字段为root的出生年月
+------+------+------+
| name | age  | born |
+------+------+------+
| root |   20 | 2000 |
+------+------+------+
1 row in set (0.00 sec)

操作查询结果（适用于select）

聚集函数

MySQL内置数据统计函数
avg（字段名）----------//统计字段平均值
sum（字段名）----------//统计字段之和
min（字段名）----------//统计字段最小值
max（字段名）----------//统计字段最大值
count（字段名）----------//统计字段值个数

查询结果排序

用法：
SQL查询 order by 字段名 [asc |desc];
字段名通常是数值类型字段
asc 升序排列（默认）
desc 降序排列

查询结果分组

用法：
SQL查询 group by 字段名;
字段名通常是字符类型字段

去重显示

用法：distunct 字段名

查询结果过滤

用法：
SQL查询 having 条件表达式;

限制查询结果显示行数

用法：
SQL查询 limit 数字; //显示查询结果前多少条记录
SQL查询 limit 数字1，数字2; //显示指定范围内的查询记录
数字1 起始行（0表示第一行）代表从第几行开始显示
数字2 代表总行数

mysql> select  avg(uid)  from  db3.user;
+----------+
| avg(uid) |
+----------+
| 131.6500 |
+----------+
1 row in set (0.00 sec)

mysql> select  min(uid)  from  db3.user;
+----------+
| min(uid) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select  max(uid)  from  db3.user;
+----------+
| max(uid) |
+----------+
|      999 |
+----------+
1 row in set (0.00 sec)

mysql> select  sum(uid)  from  db3.user;
+----------+
| sum(uid) |
+----------+
|     2633 |
+----------+
1 row in set (0.00 sec)

mysql> select  count(name)  from  db3.user where  shell ="/sbin/nologin";
+-------------+
| count(name) |
+-------------+
|          15 |
+-------------+
1 row in set (0.00 sec)

mysql> select  count(*)  from  db1.user;
ERROR 1146 (42S02): Table 'db1.user' doesn't exist
mysql> select  count(*)  from  db3.user;
+----------+
| count(*) |
+----------+
|       26 |
+----------+
1 row in set (0.00 sec)

排序  order  by 字段 asc | desc:

mysql> select  name ,uid  from  db3.user  where uid >=10 and  uid <=100 ;
+----------+------+
| name     | uid  |
+----------+------+
| operator |   11 |
| games    |   12 |
| ftp      |   14 |
| nobody   |   99 |
| dbus     |   81 |
| sshd     |   74 |
| postfix  |   89 |
| mysql    |   27 |
+----------+------+
8 rows in set (0.00 sec)

mysql> select  name ,uid  from  db3.user  where uid >=10 and  uid <=100 order by  uid;
+----------+------+
| name     | uid  |
+----------+------+
| operator |   11 |
| games    |   12 |
| ftp      |   14 |
| mysql    |   27 |
| sshd     |   74 |
| dbus     |   81 |
| postfix  |   89 |
| nobody   |   99 |
+----------+------+
8 rows in set (0.00 sec)

mysql> select  name ,uid  from  db3.user  where uid >=10 and  uid <=100 order by  uid  desc;
+----------+------+
| name     | uid  |
+----------+------+
| nobody   |   99 |
| postfix  |   89 |
| dbus     |   81 |
| sshd     |   74 |
| mysql    |   27 |
| ftp      |   14 |
| games    |   12 |
| operator |   11 |
+----------+------+
8 rows in set (0.00 sec)

分组  group by  字段名：

mysql> select  shell  from  db3.user ;
+----------------+
| shell          |
+----------------+
| /bin/bash      |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /bin/sync      |
| /sbin/shutdown |
| /sbin/halt     |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /bin/false     |
| NULL           |
| NULL           |
| NULL           |
| NULL           |
| NULL           |
| NULL           |
+----------------+
26 rows in set (0.00 sec)

mysql> select  shell  from  db3.user group by shell;
+----------------+
| shell          |
+----------------+
| NULL           |
| /bin/bash      |
| /bin/false     |
| /bin/sync      |
| /sbin/halt     |
| /sbin/nologin  |
| /sbin/shutdown |
+----------------+
7 rows in set (0.01 sec)

mysql> select  shell  from  db3.user where  id <= 20  group  by  shell ;
+----------------+
| shell          |
+----------------+
| /bin/bash      |
| /bin/false     |
| /bin/sync      |
| /sbin/halt     |
| /sbin/nologin  |
| /sbin/shutdown |
+----------------+
6 rows in set (0.00 sec)

去重复显示   distinct

mysql> select gid  from  db3.user;
+------+
| gid  |
+------+
|    0 |
|    1 |
|    2 |
|    4 |
|    7 |
|    0 |
|    0 |
|    0 |
|   12 |
|    0 |
|  100 |
|   50 |
|   99 |
|  192 |
|   81 |
|  998 |
|   74 |
|   89 |
|  996 |
|   27 |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
+------+
26 rows in set (0.00 sec)

mysql> select distinct gid  from  db3.user ;
+------+
| gid  |
+------+
|    0 |
|    1 |
|    2 |
|    4 |
|    7 |
|   12 |
|  100 |
|   50 |
|   99 |
|  192 |
|   81 |
|  998 |
|   74 |
|   89 |
|  996 |
|   27 |
| NULL |
+------+

查询结果过滤  having  条件:

mysql> select name from db3.user  where shell = "/sbin/nologin" having name="bin" ;
+------+
| name |
+------+
| bin  |
+------+
1 row in set (0.00 sec)
mysql> select  *  from  db3.user  where  id <=5;
+----+--------+------+----------+------+------+---------+----------------+---------------+
| id | name   | age  | password | uid  | gid  | comment | homedir        | shell         |
+----+--------+------+----------+------+------+---------+----------------+---------------+
|  1 | root   |   20 | x        |    0 |    0 | student | /root          | /bin/bash     |
|  2 | bin    |   20 | a        |    1 |    1 | student | /bin           | /sbin/nologin |
|  3 | daemon |   20 | a        |    2 |    2 | student | /sbin          | /sbin/nologin |
|  4 | adm    |   20 | a        |    3 |    4 | student | /var/adm       | /sbin/nologin |
|  5 | lp     |   20 | a        |    4 |    7 | student | /var/spool/lpd | /sbin/nologin |
+----+--------+------+----------+------+------+---------+----------------+---------------+
5 rows in set (0.00 sec)

限制显示记录数  limit:

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

mysql> select  *  from  db3.user  where  id <=5 limit 2;
+----+------+------+----------+------+------+---------+---------+---------------+
| id | name | age  | password | uid  | gid  | comment | homedir | shell         |
+----+------+------+----------+------+------+---------+---------+---------------+
|  1 | root |   20 | x        |    0 |    0 | student | /root   | /bin/bash     |
|  2 | bin  |   20 | a        |    1 |    1 | student | /bin    | /sbin/nologin |
+----+------+------+----------+------+------+---------+---------+---------------+
2 rows in set (0.00 sec)

mysql> select  shell  from  db3.user  group  by  shell  limit 2 ;
+-----------+
| shell     |
+-----------+
| NULL      |
| /bin/bash |
+-----------+
2 rows in set (0.00 sec)

mysql> select  name ,uid  from  db3.user  order  by  uid  desc  limit 5;
+-----------------+------+
| name            | uid  |
+-----------------+------+
| polkitd         |  999 |
| chrony          |  998 |
| systemd-network |  192 |
| nobody          |   99 |
| postfix         |   89 |
+-----------------+------+
5 rows in set (0.00 sec)

mysql> select  name ,shell  from  db3.user  where  shell="/sbin/nologin";
+-----------------+---------------+
| name            | shell         |
+-----------------+---------------+
| bin             | /sbin/nologin |
| daemon          | /sbin/nologin |
| adm             | /sbin/nologin |
| lp              | /sbin/nologin |
| mail            | /sbin/nologin |
| operator        | /sbin/nologin |
| games           | /sbin/nologin |
| ftp             | /sbin/nologin |
| nobody          | /sbin/nologin |
| systemd-network | /sbin/nologin |
| dbus            | /sbin/nologin |
| polkitd         | /sbin/nologin |
| sshd            | /sbin/nologin |
| postfix         | /sbin/nologin |
| chrony          | /sbin/nologin |
+-----------------+---------------+
15 rows in set (0.00 sec)

mysql> select  name ,shell  from  db3.user  where  shell="/sbin/nologin"  limit  3,4;
+----------+---------------+
| name     | shell         |
+----------+---------------+
| lp       | /sbin/nologin |
| mail     | /sbin/nologin |
| operator | /sbin/nologin |
| games    | /sbin/nologin |
+----------+---------------+
4 rows in set (0.00 sec)

哦吼吼吼tian

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库基础（三）数据的导入导出、管理表记录、匹配条件

数据的导入导出搜索路径查看搜索路径安装时已经自动创建命令：mysql > show variables like “secure_file_priv”;mysql> show variables like "secure_file_priv" ;+------------------+-----------------------+| Va...
复制链接

扫一扫