數(shù)據(jù)的導(dǎo)入導(dǎo)出

搜索路徑

• 查看搜索路徑

• 安裝時(shí)已經(jīng)自動(dòng)創(chuàng)建
• 命令：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，查看其當(dāng)前默認(rèn)搜索路徑
• 2.退出，進(jìn)入到主配置文件/etc/my.cnf,添加新的搜索路徑（注：一定要確認(rèn)新的路徑可以讓mysql用戶有讀寫執(zhí)行權(quán)限）
• 3.重啟mysqld服務(wù)
• 4.再次以管理員身份登錄mysql，查看現(xiàn)在的默認(rèn) 路徑

+------------------+-----------------------+ | 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)

數(shù)據(jù)導(dǎo)入

• 數(shù)據(jù)導(dǎo)入：

• 批量存儲(chǔ)數(shù)據(jù),把系統(tǒng)文件的內(nèi)容，存儲(chǔ)到數(shù)據(jù)庫(kù)下的表里
• 默認(rèn)只有root用戶有數(shù)據(jù)導(dǎo)入權(quán)限
• 步驟 建庫(kù)-------->建表------> 把系統(tǒng)文件拷貝到檢索目錄------導(dǎo)入數(shù)據(jù)------>查看數(shù)據(jù)
• **命令格式：**mysql > load data infile “目錄名/文件名” into table 庫(kù)名.表名 fields terminated by “分隔符” lines terminated by “\n” ;

• 數(shù)據(jù)導(dǎo)入注意事項(xiàng)

1.字段分隔符要與文件一致
2.表字段類型和字段個(gè)數(shù)要與文件相匹配
3.導(dǎo)入數(shù)據(jù)時(shí)指定文件的絕對(duì)路徑

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文件為導(dǎo)入數(shù)據(jù)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" ; #導(dǎo)入數(shù)據(jù) 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; #添加行號(hào)id的字段，將其設(shè)為主鍵，并且自增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)

數(shù)據(jù)導(dǎo)出

• 命令格式：

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

• 注意事項(xiàng)：

• 導(dǎo)出數(shù)據(jù)行數(shù)由SQL查詢決定
• 導(dǎo)出的是表記錄，不包括字段名
• 自動(dòng)創(chuàng)建存儲(chǔ)數(shù)據(jù)的文件
• 存儲(chǔ)數(shù)據(jù)文件，具有唯一性

mysql> select * from db2.stuinfo into outfile "/myload/one.txt" ; #將db2庫(kù)中sutinfo表中的數(shù)據(jù)導(dǎo)出到/myload/one.txt文件 Query OK, 1 row affected (0.00 sec)[root@host51 ~]# cat /myload/one.txt 23 girl itmysql> 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#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.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庫(kù)中user表內(nèi)id小于等于5的字段數(shù)據(jù)導(dǎo)出到/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

管理表記錄

增加表記錄

• 語(yǔ)法格式

• 格式一：添加一條記錄，給所有字段賦值
• mysql > insert into 表名 values（字段值列表）;

• 格式二：添加多條記錄，給所有字段賦值
• mysql > insert into 表名 values（字段值列表），（字段值列表），（字段值列表）;

• 格式三：添加一條記錄，給指定字段賦值
• mysql > insert into 表名（字段名列表） values（字段值列表）;

• 格式四：添加多條記錄，給指定字段賦值
• mysql > insert into 表名（字段名列表） values（字段值列表），（字段值列表），（字段值列表）;

• 注意事項(xiàng)：

• 1.字段值要與字段類型相匹配
• 2.字符類型的字段，要用“ ”號(hào)括起來(lái)
• 3.依次給所有的字段賦值時(shí)，字段名可以省略
• 4.只給部分字段賦值時(shí)，必須要明確寫出對(duì)應(yīng)的字段名稱
• 5.沒有復(fù)制的字段使用默認(rèn)值或自增長(zhǎng)賦值

一次插入一條記錄，給所有列賦值 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: 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 | 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: 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 | 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值會(huì)默認(rèn)自增長(zhǎng)

查詢表記錄

• 語(yǔ)法格式：

• 格式一：查所有字段
• mysql > select 字段1，……，字段N from 庫(kù)名.表名;

• 格式二：條件查詢
• mysql > select 字段1，……，字段N from 庫(kù)名.表名 where 條件表達(dá)式;

• 注意事項(xiàng)：

• *表示所有字段
• 查看當(dāng)前庫(kù)表記錄時(shí)庫(kù)名可以省略
• 字段列表決定顯示列個(gè)數(shù)
• 條件決定顯示行的個(gè)數(shù)

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)

更新表記錄

• 語(yǔ)法格式：

• 格式一：批量更新
  mysql > update 庫(kù)名.表名 set 字段名=值，字段名=值，……;

• 格式二：條件匹配更新
• mysql > update 庫(kù)名.表名 set 字段名=值，字段名=值，字段名=值，…… where 條件表達(dá)式;

• 注意事項(xiàng)：

• 1.字段值要與字段類型相匹配
• 2.對(duì)于字符類型的字段，值要用雙引號(hào)括起來(lái)
• 3.若不使用where限定條件，會(huì)更新所有記錄字段值
• 4.限定條件時(shí)，只更新匹配條件的記錄的字段值

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 | 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: 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 | 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)

刪除表記錄

• 語(yǔ)法格式：

• 格式一：條件匹配刪除
• mysql > delete from 庫(kù)名.表名 where 條件表達(dá)式;

• 格式二：刪除所有記錄
• mysql > delete from 庫(kù)名.表名;

• 注意事項(xiàng)：

• 不加條件刪除表中所有的行

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）

• 數(shù)值比較
• 字段必須是數(shù)值類型

類型比較例子

=	相等	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

• 邏輯匹配
• 多個(gè)判斷條件時(shí)使用

類型用途格式

or	邏輯或	條件1 or 條件2 or 條件3 ， 某一個(gè)條件成立即可
and	邏輯與	條件1 and 條件2 and 條件3 ，所有條件同時(shí)成立
! 或 not	邏輯非	取反

• 范圍匹配
• 匹配范圍內(nèi)的任意一個(gè)值即可

類型比較

in（值列表）	在…里
not in（值列表）	不在…里
between 數(shù)字 and 數(shù)字	在…之間

數(shù)值比較: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)邏輯匹配（多個(gè)匹配條件）: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)

高級(jí)匹配條件（適用于 select update delete）

• 模糊查詢

• 用法：
  where 字段名 like ‘通配符’
  _ 表示一個(gè)字符
  % 表示0~n個(gè)字符

• 正則表達(dá)式

• 用法：
  where 字段名 regexp ‘正則表達(dá)式’
  正則元字符 ^ $ . [] * |

• 四則運(yùn)算
• 字段必須是數(shù)值類型

符號(hào)用途例子

+	加法	uid + uid
-	減法	uid - uid
*	乘法	uid * uid
/	除法	uid / uid
%	取余數(shù)（求模）	uid % uid
()	提高優(yōu)先級(jí)	(uid + uid) /2

mysql> select name from db3.user where name like '_ _ _'; #查找name字段是三個(gè)字符的 +------+ | name | +------+ | bin | | adm | | ftp | | bob | | tom | +------+ 5 rows in set (0.00 sec)mysql> select name from db3.user where name like '_ _ _ _'; #查找name字段是四個(gè)字符的 +------+ | 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字段有至少五個(gè)字符 的 +-----------------+ | 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字段包含數(shù)字的 +---------+ | name | +---------+ | haha2 | | lala6 | | hei8hei | +---------+ 3 rows in set (0.00 sec)mysql> select name from db3.user where name regexp '.*[0-9].*'; #查找數(shù)字在中間的 +---------+ | name | +---------+ | haha2 | | lala6 | | hei8hei | +---------+ 3 rows in set (0.00 sec)四則運(yùn)算（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字段，再求他們的平均數(shù)，為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是偶數(shù) +----+-----------------+----------+------+------+---------+--------------------+---------------+ | 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字段，默認(rèn)為20 Query 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 ,2020-age born from db3.user where name="root"; #查找name字段為root的出生年月 +------+------+------+ | name | age | born | +------+------+------+ | root | 20 | 2000 | +------+------+------+ 1 row in set (0.00 sec)

操作查詢結(jié)果（適用于select）

• 聚集函數(shù)

• MySQL內(nèi)置數(shù)據(jù)統(tǒng)計(jì)函數(shù)
  avg（字段名）----------//統(tǒng)計(jì)字段平均值
  sum（字段名）----------//統(tǒng)計(jì)字段之和
  min（字段名）----------//統(tǒng)計(jì)字段最小值
  max（字段名）----------//統(tǒng)計(jì)字段最大值
  count（字段名）----------//統(tǒng)計(jì)字段值個(gè)數(shù)

• 查詢結(jié)果排序

• 用法：
  SQL查詢 order by 字段名 [asc |desc];
  字段名通常是數(shù)值類型字段
  asc 升序排列（默認(rèn)）
  desc 降序排列

• 查詢結(jié)果分組

• 用法：
  SQL查詢 group by 字段名;
  字段名通常是字符類型字段

• 去重顯示

• 用法：distunct 字段名

• 查詢結(jié)果過(guò)濾

• 用法：
  SQL查詢 having 條件表達(dá)式;

• 限制查詢結(jié)果顯示行數(shù)

• 用法：
  SQL查詢 limit 數(shù)字; //顯示查詢結(jié)果前多少條記錄
  SQL查詢 limit 數(shù)字1，數(shù)字2; //顯示指定范圍內(nèi)的查詢記錄
  數(shù)字1 起始行（0表示第一行）代表從第幾行開始顯示
  數(shù)字2 代表總行數(shù)

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)去重復(fù)顯示 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 | +------+查詢結(jié)果過(guò)濾 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)限制顯示記錄數(shù) 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)

總結(jié)

以上是生活随笔為你收集整理的MySQL数据库基础（三）数据的导入导出、管理表记录、匹配条件的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。