数据的导入导出
搜索路径
查看搜索路径安装时已经自动创建命令: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 /var/lib/mysql-files/
修改搜索路径
操作步骤:1.以管理员身份登录mysql,查看其当前默认搜索路径2.退出,进入到主配置文件/etc/f,添加新的搜索路径(注:一定要确认新的路径可以让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 /var/lib/mysql-files/[root@host51 ~]# vim /etc/f...[mysqld]secure_file_priv=/myloadvalidate_password_policy=0validate_password_length=6...[root@host51 ~]# mkdir /myload[root@host51 ~]# ls -ld /myloaddrwxr-xr-x. 2 root root 6 2月 15 11:14 /myload[root@host51 ~]# chown mysql /myload[root@host51 ~]# ls -ld /myloaddrwxr-xr-x. 2 mysql root 6 2月 15 11:14 /myload[root@host51 ~]# systemctl restart mysqld[root@host51 ~]# mysql -uroot -p123456mysql> 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: 0mysql> 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: 0mysql> 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.txt23girlitmysql> 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.txt1.1.1.1#21#allow1.1.1.1#22#deny2.1.1.1#22#denymysql> 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.txt11#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.txtroot/bin/bash/rootbin/sbin/nologin/bindaemon/sbin/nologin/sbinadm/sbin/nologin/var/admlp/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", , , "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: 0mysql> 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 | | | 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: 0mysql> 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 | | | 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 | | | 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: 0mysql> 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 | | | 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: 0mysql> 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 | | | 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 | | | 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)
数值比较字段必须是数值类型字符比较/匹配空/非空字段必须是字符类型逻辑匹配多个判断条件时使用范围匹配匹配范围内的任意一个值即可数值比较: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: 0mysql> 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 ‘正则表达式’
正则元字符 ^ $ . [] * |
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: 0mysql> 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: 0mysql> 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: 0mysql> 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字段,默认为20Query OK, 0 rows affected (0.05 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> 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 ,-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 existmysql> 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)去重复显示 distinctmysql> 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)
如果觉得《MySQL数据库基础(三)数据的导入导出 管理表记录 匹配条件》对你有帮助,请点赞、收藏,并留下你的观点哦!