基于Linux的MySQL操作实例之数据导入/导出，新增/查询表记录

前言

本篇就数据的导入/导出数据表，新增/查询表记录这些部分进行了实例和基本概念说明。
本篇对板式做了一些修改，希望大家喜欢。
本篇内容结合本人其他几篇一起观看效果更佳，有兴趣的各位可以看看。

基于Linux的MySQL操作实例（软件安装，mysql基本操作，mysql数据类型，建表、插入数据操作）
基于Linux的MySQL操作实例(修改表结构，MySQL索引，MySQL数据引擎)

http://www.jianshu.com/p/444482ff5986
http://www.jianshu.com/p/1ca8da8ff190

数据导入

概述

数据导入：把系统文件的内容存储到数据库的表里。

语法格式

LOAD DATA INFILE '文件名' INTO TABLE 表名
FIELDS TERMINATED BY '分隔符' LINES TERMINATED BY '\n';

fields 表示区域划分，即列的划分
lines 表示列的划分

注意事项

• 字段分隔符要与文件内的一致
• 指定导入文件的绝对路径
• 导入数据的表字段类型要与文件字段匹配

实例操作

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

mysql> use test_db;
Database changed

mysql> create table userlist ( uname char(25) not null, passwdmark char(1) not null, uid  int(3), gid smallint(3), comment varchar(50), homedir char(30), shell char(30), index(uname) );
Query OK, 0 rows affected (0.74 sec)

mysql> desc userlist;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| uname      | char(25)    | NO   | MUL | NULL    |       |
| passwdmark | char(1)     | NO   |     | NULL    |       |
| uid        | int(3)      | YES  |     | NULL    |       |
| gid        | smallint(3) | YES  |     | NULL    |       |
| comment    | varchar(50) | YES  |     | NULL    |       |
| homedir    | char(30)    | YES  |     | NULL    |       |
| shell      | char(30)    | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
7 rows in set (0.01 sec)

mysql> load data infile "/etc/passwd" into table userlist fields terminated by ':' lines terminated by "\n";
Query OK, 25 rows affected (0.17 sec)
Records: 25  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from userlist;
+-----------+------------+------+------+------------------------------+---------------------+----------------+------+
| uname     | passwdmark | uid  | gid  | comment                      | homedir             | shell          | u_id |
+-----------+------------+------+------+------------------------------+---------------------+----------------+------+
| root      | x          |    0 |    0 | root                         | /root               | /bin/bash      |   01 |
| bin       | x          |    1 |    1 | bin                          | /bin                | /sbin/nologin  |   02 |
| daemon    | x          |    2 |    2 | daemon                       | /sbin               | /sbin/nologin  |   03 |
| adm       | x          |    3 |    4 | adm                          | /var/adm            | /sbin/nologin  |   04 |
| lp        | x          |    4 |    7 | lp                           | /var/spool/lpd      | /sbin/nologin  |   05 |
……

mysql> alter table userlist  add u_id int(2) zerofill primary key auto_increment first;
Query OK, 0 rows affected (0.94 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from userlist;
+------+-----------+------------+------+------+------------------------------+---------------------+----------------+
| u_id | uname     | passwdmark | uid  | gid  | comment                      | homedir             | shell          |
+------+-----------+------------+------+------+------------------------------+---------------------+----------------+
|   01 | root      | x          |    0 |    0 | root                         | /root               | /bin/bash      |
|   02 | bin       | x          |    1 |    1 | bin                          | /bin                | /sbin/nologin  |
|   03 | daemon    | x          |    2 |    2 | daemon                       | /sbin               | /sbin/nologin  |
|   04 | adm       | x          |    3 |    4 | adm                          | /var/adm            | /sbin/nologin  |
……

数据导出

概述

数据导出: 把表中的记录存储到系统文件里。

语法格式：

sql查询命令 INTO OUTFILE '文件名'；
sql查询命令 INTO OUTFILE '目录名/文件名'；
sql查询命令 INTO OUTFILE '目录名/文件名' fields terminated by "符号"；
sql查询命令 INTO OUTFILE '目录名/文件名' lines terminated by "!!!"；
sql查询命令 INTO OUTFILE '目录名/文件名' fields terminated by "符号" lines terminated by "符号"；

注意事项

• 导出的内容由SQL查询语句决定
• 若不指定路径，默认会放在执行导出命令时所在库对应的数据库目录下。
• 应确保mysql用户对目标文件夹有写权限。

实例操作

//创建文件存放目录
[root@mysql var]# mkdir /var/mysql_doc/
[root@mysql var]# ll -d /var/mysql_doc/
drwxr-xr-x. 2 root root 4096 Jan 17 06:44 /var/mysql_doc/
[root@mysql var]# id mysql
uid=27(mysql) gid=27(mysql) groups=27(mysql)
//
[root@mysql var]# chown mysql: /var/mysql_doc/
[root@mysql var]# ll -d /var/mysql_doc/
drwxr-xr-x. 2 mysql mysql 4096 Jan 17 06:44 /var/mysql_doc/

mysql> select uname,shell from userlist where shell="/bin/bash" ;
+-------+-----------+
| uname | shell     |
+-------+-----------+
| root  | /bin/bash |
| mysql | /bin/bash |
+-------+-----------+
2 rows in set (0.00 sec)

//测试当文件存放目录没有操作权限的情况：
[root@mysql var]# chown root: /var/mysql_doc/
[root@mysql var]# ll -d /var/mysql_doc/
drwxr-xr-x. 2 root root 4096 Jan 17 06:44 /var/mysql_doc/

mysql> select uname,shell from userlist where shell="/bin/bash"  into outfile "/var/mysql_doc/test1.txt";
ERROR 1 (HY000): Can't create/write to file '/var/mysql_doc/test1.txt' (Errcode: 13 - Permission denied)

//正确操作
[root@mysql var]# chown mysql: /var/mysql_doc/
[root@mysql var]# ll -d /var/mysql_doc/
drwxr-xr-x. 2 mysql mysql 4096 Jan 17 06:44 /var/mysql_doc/

mysql> select uname,shell from userlist where shell="/bin/bash"  into outfile "/var/mysql_doc/test1.txt";
Query OK, 2 rows affected (0.00 sec)

[root@mysql var]# cd mysql_doc/
[root@mysql mysql_doc]# ls
test1.txt
[root@mysql mysql_doc]# cat test1.txt 
root    /bin/bash
mysql   /bin/bash

//指定列分割符的情况：
mysql> select uname,homedir from userlist into outfile "/var/mysql_doc/test2.txt"  fields terminated by "----";
Query OK, 25 rows affected (0.00 sec)
[root@mysql mysql_doc]# head -5 test2.txt 
root----/root
bin----/bin
daemon----/sbin
adm----/var/adm
lp----/var/spool/lpd

//指定行的分割符的情况
mysql> select uname from userlist into outfile "/var/mysql_doc/test3.txt" lines terminated by "||";
Query OK, 25 rows affected (0.00 sec)

[root@mysql mysql_doc]# cat test3.txt 
abrt||adm||bin||daemon||dbus||ftp||games||gopher||haldaemon||halt||lp||mail||mysql||nobody||ntp||operator||postfix||root||saslauth||shutdown||sshd||sync||tcpdump||uucp||vcsa||[root@mysql mysql_doc]# 

/*
导出时，文件若已经存在，会报错。
不指定文件目录时，文件会默认存放在当前数据库所在的目录中
若没有指定当前的数据库，文件会默认存放在MySQL目录（/var/lib/mysql）中
也可以自行创建目录，但要保证mysql进程的用户对该目录有相应的操作权限。
*/

新增表记录操作（insert into）

语法格式

若是当前操作的表就是当前库之内的可直接使用表名（即使用命令“use 数据库名”切换数据库）

一次插入一条记录 给记录的所有字段赋值
insert into 库.表 values(值列表);

一次插入多条记录 给记录的所有字段赋值
insert into 库.表 values(值列表),(值列表);

一次插入1条记录 给记录的指定字段赋值
insert into 库.表(字段名列表) values(值列表);

一次插入多条记录 给记录的指定字段赋值
insert into 库.表(字段名列表) values(值列表)，(值列表);

操作实例

mysql> insert into userlist values(34,"job","X",502,600,"job","/home/job/","/bin/bash"), (35,"tom","x",503,601,"tom","/home/job/","/bin/bash"); 
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from userlist where u_id=34 || u_id=35;
+------+-------+------------+------+------+---------+------------+-----------+
| u_id | uname | passwdmark | uid  | gid  | comment | homedir    | shell     |
+------+-------+------------+------+------+---------+------------+-----------+
|   34 | job   | X          |  502 |  600 | job     | /home/job/ | /bin/bash |
|   35 | tom   | x          |  503 |  601 | tom     | /home/job/ | /bin/bash |
+------+-------+------------+------+------+---------+------------+-----------+
2 rows in set (0.00 sec)

mysql> insert into userlist(uname,uid,gid) values("jack",503,605);
ERROR 1364 (HY000): Field 'passwdmark' doesn't have a default value
mysql> insert into userlist(uname,passwdmark,uid,gid) values("jack","x",503,605);
Query OK, 1 row affected (0.03 sec)

查询表记录(select)

语法格式：

select 字段列表 from 表名;
select 字段列表 from 表名 where 表达式；
select 字段列表 from 表名 where 条件表达式；

实例操作

由于select操作的方式条件过多，我们将分开进行实例说明。

数值比较实例操作

格式：字段名 符号 数字
符号：= != > >= < <=

//数值比较
mysql> select uname,shell from userlist where u_id=500;
Empty set (0.00 sec)

mysql> select uname,shell from userlist where u_id>500;
Empty set (0.00 sec)

mysql> select uname,shell from userlist where uid>500;
+-------+-----------+
| uname | shell     |
+-------+-----------+
| job   | /bin/bash |
| tom   | /bin/bash |
| jack  | NULL      |
+-------+-----------+
3 rows in set (0.00 sec)

mysql> select uname,shell from userlist where uid=500;
Empty set (0.00 sec)

mysql> select uname,shell from userlist where uid>=500;
+-------+-----------+
| uname | shell     |
+-------+-----------+
| job   | /bin/bash |
| tom   | /bin/bash |
| jack  | NULL      |
+-------+-----------+
3 rows in set (0.00 sec)

mysql> select u_id,uname,gid,homedir from userlist where gid<50;
……

mysql> select uname,uid,shell from userlist where uid>10&& uid<20;
+----------+------+---------------+
| uname    | uid  | shell         |
+----------+------+---------------+
| operator |   11 | /sbin/nologin |
| games    |   12 | /sbin/nologin |
| gopher   |   13 | /sbin/nologin |
| ftp      |   14 | /sbin/nologin |
+----------+------+---------------+
4 rows in set (0.00 sec)

mysql> select uname,uid,shell from userlist where uid>10 and uid<20;
+----------+------+---------------+
| uname    | uid  | shell         |
+----------+------+---------------+
| operator |   11 | /sbin/nologin |
| games    |   12 | /sbin/nologin |
| gopher   |   13 | /sbin/nologin |
| ftp      |   14 | /sbin/nologin |
+----------+------+---------------+
4 rows in set (0.00 sec)

字符比较操作实例

格式：字段名 符号 "值"
符号：= !=

mysql> select uname,shell from userlist where uname!="root";
+-----------+----------------+
| uname     | shell          |
+-----------+----------------+
| bin       | /sbin/nologin  |
| daemon    | /sbin/nologin  |
| adm       | /sbin/nologin  |
……

mysql> select uname,shell from userlist where uname="root";
+-------+-----------+
| uname | shell     |
+-------+-----------+
| root  | /bin/bash |
+-------+-----------+
1 row in set (0.00 sec)

范围内比较实例操作

格式：

• between ... and ... 在...之间
• in (值列表) 在....里
• not in (值列表) 不在....里

//适合用于数值型的变量，字符型或其他型虽然不会报错，但是会出现结果显示不准确的情况
mysql> select uname,uid,shell from userlist where uid between 10 and 20;
+----------+------+---------------+
| uname    | uid  | shell         |
+----------+------+---------------+
| uucp     |   10 | /sbin/nologin |
| operator |   11 | /sbin/nologin |
| games    |   12 | /sbin/nologin |
| gopher   |   13 | /sbin/nologin |
| ftp      |   14 | /sbin/nologin |
+----------+------+---------------+
5 rows in set (0.00 sec)

mysql> select uname,uid,homedir from userlist where uid in (10,20,30,40,500);
+-------+------+-----------------+
| uname | uid  | homedir         |
+-------+------+-----------------+
| uucp  |   10 | /var/spool/uucp |
+-------+------+-----------------+
1 row in set (0.00 sec)

mysql> select uname,uid,homedir from userlist where uname in ("root","shutdown","mysql","natasha");
+----------+------+----------------+
| uname    | uid  | homedir        |
+----------+------+----------------+
| root     |    0 | /root          |
| shutdown |    6 | /sbin          |
| mysql    |   27 | /var/lib/mysql |
+----------+------+----------------+
3 rows in set (0.00 sec)

mysql> select uname,homedir,shell from userlist where shell not in ("/sbin/nologin");
+----------+----------------+----------------+
| uname    | homedir        | shell          |
+----------+----------------+----------------+
| root     | /root          | /bin/bash      |
| sync     | /sbin          | /bin/sync      |
| shutdown | /sbin          | /sbin/shutdown |
| halt     | /sbin          | /sbin/halt     |
| mysql    | /var/lib/mysql | /bin/bash      |
| job      | /home/job/     | /bin/bash      |
| tom      | /home/job/     | /bin/bash      |
+----------+----------------+----------------+
7 rows in set (0.00 sec)

匹配null操作实例

格式：

• 匹配空 is null
• 匹配非空 is not null

mysql> select u_id,uname,shell from userlist where shell is null;
+------+-------+-------+
| u_id | uname | shell |
+------+-------+-------+
|   36 | jack  | NULL  |
+------+-------+-------+
1 row in set (0.00 sec)

mysql> select u_id,uname,shell from userlist where shell is not NULL;
+------+-----------+----------------+
| u_id | uname     | shell          |
+------+-----------+----------------+
|   01 | root      | /bin/bash      |
|   02 | bin       | /sbin/nologin  |
|   03 | daemon    | /sbin/nologin  |
……

//"null","NULL",""都代表字符串，不表示NULL
mysql> insert into userlist (u_id,uname) values(40,"null"),(41,"NULL"),(42,"");
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select u_id,uname from userlist where u_id>=40;
+------+-------+
| u_id | uname |
+------+-------+
|   40 | null  |
|   41 | NULL  |
|   42 |       |
+------+-------+
3 rows in set (0.00 sec)

mysql> select u_id,uname from userlist where uname="";
+------+-------+
| u_id | uname |
+------+-------+
|   42 |       |
+------+-------+
1 row in set (0.00 sec)

mysql> select u_id,uname from userlist where uname="null";
+------+-------+
| u_id | uname |
+------+-------+
|   40 | null  |
|   41 | NULL  |
+------+-------+
2 rows in set (0.00 sec)

mysql> select u_id,uname from userlist where uname="NULL";
+------+-------+
| u_id | uname |
+------+-------+
|   40 | null  |
|   41 | NULL  |
+------+-------+
2 rows in set (0.00 sec)

mysql> select u_id,uname from userlist where uname=NULL;
Empty set (0.00 sec)

逻辑比较操作实例

逻辑比较（多个查询条件）

格式：

• 逻辑与 and 多个查询条件必须同时成立
• 逻辑或 or 多个查询条件某个条件成立就可以
• 逻辑非 ！ 取反

//and的优先级高于or，（）的优先级最高
mysql> select u_id,uid,uname,homedir,shell from userlist where uname="root" or uid=1 or shell="/bin/bash";
+------+------+-------+----------------+---------------+
| u_id | uid  | uname | homedir        | shell         |
+------+------+-------+----------------+---------------+
|   01 |    0 | root  | /root          | /bin/bash     |
|   02 |    1 | bin   | /bin           | /sbin/nologin |
|   25 |   27 | mysql | /var/lib/mysql | /bin/bash     |
|   34 |  502 | job   | /home/job/     | /bin/bash     |
|   35 |  503 | tom   | /home/job/     | /bin/bash     |
+------+------+-------+----------------+---------------+
5 rows in set (0.00 sec)

mysql> select u_id,uid,uname,homedir,shell from userlist where uname="bin" and uid=0;
Empty set (0.00 sec)

mysql> select u_id,uid,uname from userlist where uname="root" or uname="bin" and uid=1;
+------+------+-------+
| u_id | uid  | uname |
+------+------+-------+
|   02 |    1 | bin   |
|   01 |    0 | root  |
+------+------+-------+
2 rows in set (0.00 sec)

mysql> select uname,uid from userlist where (uname="root" or uname="bin") and uid=1;
+-------+------+
| uname | uid  |
+-------+------+
| bin   |    1 |
+-------+------+
1 row in set (0.00 sec)

四则运算操作实例

符号："+","-","*","/","%"

//调整表结构，添加测试数据
mysql> alter table userlist add age tinyint(2) not null default 19;
Query OK, 0 rows affected (0.74 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table userlist add rhcsa_n double(5,2) not null default 0 ,
    -> add rhce_n double(5,2) not null default 0;
Query OK, 0 rows affected (0.77 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc userlist;
+------------+--------------------------+------+-----+---------+----------------+
| Field      | Type                     | Null | Key | Default | Extra          |
+------------+--------------------------+------+-----+---------+----------------+
| u_id       | int(2) unsigned zerofill | NO   | PRI | NULL    | auto_increment |
| uname      | char(25)                 | NO   | MUL | NULL    |                |
| passwdmark | char(10)                 | YES  |     | NULL    |                |
| uid        | int(3)                   | YES  |     | NULL    |                |
| gid        | smallint(3)              | YES  |     | NULL    |                |
| comment    | varchar(50)              | YES  |     | NULL    |                |
| homedir    | char(30)                 | YES  |     | NULL    |                |
| shell      | char(30)                 | YES  |     | NULL    |                |
| age        | tinyint(2)               | NO   |     | 19      |                |
| rhcsa_n    | double(5,2)              | NO   |     | 0.00    |                |
| rhce_n     | double(5,2)              | NO   |     | 0.00    |                |
+------------+--------------------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)

mysql> update userlist set rhcsa_n=70;
Query OK, 31 rows affected (0.04 sec)
Rows matched: 31  Changed: 31  Warnings: 0

mysql> update userlist set rhce_n=90;
Query OK, 31 rows affected (0.05 sec)
Rows matched: 31  Changed: 31  Warnings: 0

mysql> select * from userlist;
+------+-----------+------------+------+------+------------------------------+---------------------+----------------+-----+---------+--------+
| u_id | uname     | passwdmark | uid  | gid  | comment                      | homedir             | shell          | age | rhcsa_n | rhce_n |
+------+-----------+------------+------+------+------------------------------+---------------------+----------------+-----+---------+--------+
|   01 | root      | x          |    0 |    0 | root                         | /root               | /bin/bash      |  19 |   70.00 |  90.00 |
|   02 | bin       | x          |    1 |    1 | bin                          | /bin                | /sbin/nologin  |  19 |   70.00 |  90.00 |
|   03 | daemon    | x          |    2 |    2 | daemon                       | /sbin               | /sbin/nologin  |  19 |   70.00 |  90.00 |
|   04 | adm       | x          |    3 |    4 | adm                          | /var/adm            | /sbin/nologin  |  19 |   70.00 |  90.00 |
|   05 | lp        | x          |    4 |    7 | lp                           | /var/spool/lpd      | /sbin/nologin  |  19 |   70.00 |  90.00 |
……

//where u_id<10 是避免显示结果过多
mysql> select uname,2017-age as s_year ,age from userlist where u_id<10;
+----------+--------+-----+
| uname    | s_year | age |
+----------+--------+-----+
| root     |   1998 |  19 |
| bin      |   1998 |  19 |
| daemon   |   1998 |  19 |
| adm      |   1998 |  19 |
| lp       |   1998 |  19 |
| sync     |   1998 |  19 |
| shutdown |   1998 |  19 |
| halt     |   1998 |  19 |
| mail     |   1998 |  19 |
+----------+--------+-----+
9 rows in set (0.02 sec)

//使用as设置别名，在结果显示处
mysql> select uname,year(now())-age as s_year,age from userlist where u_id<10
    -> ;
+----------+--------+-----+
| uname    | s_year | age |
+----------+--------+-----+
| root     |   1998 |  19 |
| bin      |   1998 |  19 |
| daemon   |   1998 |  19 |
| adm      |   1998 |  19 |
| lp       |   1998 |  19 |
| sync     |   1998 |  19 |
| shutdown |   1998 |  19 |
| halt     |   1998 |  19 |
| mail     |   1998 |  19 |
+----------+--------+-----+
9 rows in set (0.03 sec)

mysql> select uname,rhcsa_n,rhce_n,rhcsa_n+rhce_n as add_result,(rhcsa_n+rhce_n)/2 as av_result from userlist where u_id>30;
+-------+---------+--------+------------+-----------+
| uname | rhcsa_n | rhce_n | add_result | av_result |
+-------+---------+--------+------------+-----------+
| job   |   70.00 |  90.00 |     160.00 | 80.000000 |
| tom   |   70.00 |  90.00 |     160.00 | 80.000000 |
| jack  |   70.00 |  90.00 |     160.00 | 80.000000 |
| null  |   70.00 |  90.00 |     160.00 | 80.000000 |
| NULL  |   70.00 |  90.00 |     160.00 | 80.000000 |
|       |   70.00 |  90.00 |     160.00 | 80.000000 |
+-------+---------+--------+------------+-----------+
6 rows in set (0.00 sec)

//请注意as是生成别名，只是一个别名，不是类似与变量，不可以在后续使用，
mysql> select uname,rhcsa_n,rhce_n,rhcsa_n+rhce_n as add_result,add_result/2 as av_result from userlist where u_id>30;
ERROR 1054 (42S22): Unknown column 'add_result' in 'field list'

模糊查询（like）操作实例

格式：
where 字段名 like '表达式'

• "_"：任意一个字符
• "%"：零个或多个字符

该表达式不同于正则表达式等，是SQL自带的匹配方式。

//"_"表示单个字符，"%"表示任意个字符（零个或多个）

mysql>  select uname from userlist where uname like "a___";
+-------+
| uname |
+-------+
| abrt  |
+-------+
1 row in set (0.00 sec)

mysql>  select uname from userlist where uname like "___a";
+-------+
| uname |
+-------+
| vcsa  |
+-------+
1 row in set (0.00 sec)

//uname字段至少两个字符
mysql>  select uname from userlist where uname like "_%_";
+-----------+
| uname     |
+-----------+
| abrt      |
| adm       |
| bin       |
| daemon    |
| dbus      |
| ftp       |
……

//以r开头的uname字段的值
mysql>  select uname from userlist where uname like "r%";
+-------+
| uname |
+-------+
| root  |
+-------+
1 row in set (0.00 sec)

//uname为空（null）的字段
mysql> select u_id,uname from userlist where uname is null;
Empty set (0.00 sec)

mysql> alter table userlist modify uname char(10) ;
Query OK, 31 rows affected (1.37 sec)
Records: 31  Duplicates: 0  Warnings: 0

//插入一条uname为空的字段
mysql> insert into userlist(u_id,uname) values (44,null);
Query OK, 1 row affected (0.04 sec)

mysql> select u_id,uname from userlist where uname is null;
+------+-------+
| u_id | uname |
+------+-------+
|   44 | NULL  |
+------+-------+
1 row in set (0.00 sec)

//该方式无法检索到uname为null的条目
mysql> select u_id,uname from userlist where uname like "%";
+------+-----------+
| u_id | uname     |
+------+-----------+
|   42 |           |
|   18 | abrt      |
|   04 | adm       |
|   02 | bin       |
|   03 | daemon    |
|   16 | dbus      |
|   14 | ftp       |
|   12 | games     |
|   13 | gopher    |
|   19 | haldaemon |
|   08 | halt      |
|   36 | jack      |
|   34 | job       |
|   05 | lp        |
|   09 | mail      |
|   25 | mysql     |
|   15 | nobody    |
|   20 | ntp       |
|   40 | null      |
|   41 | NULL      |
|   11 | operator  |
|   22 | postfix   |
|   01 | root      |
|   21 | saslauth  |
|   07 | shutdown  |
|   23 | sshd      |
|   06 | sync      |
|   24 | tcpdump   |
|   35 | tom       |
|   10 | uucp      |
|   17 | vcsa      |
+------+-----------+
31 rows in set (0.00 sec)

mysql> select u_id,uname from userlist where uname like "%" and u_id=44;
Empty set (0.00 sec)

正则表达式操作实例

使用正则表达式做查询条件
由于正则表达式是一个独立的兼容性好的其他表达式语法，这里就不展开讲解了。
关于正则表达式的更多细节可以去我的正则表达式的博客查看。

基于Linux下详解正则表达式（基本正则和扩展正则命令使用实例）
http://www.jianshu.com/p/8edb84b49ce8

支持符号："^"，"$"，"."，"*"，"[ ]"

格式：字段名 regexp '正则表达式'

//正则表达式操作实例

mysql> insert into userlist(uname) values ("bob"),("plj9"),("pl8j"),("p7lj"),("6plj");
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select uname from userlist where uname regexp "bb";
Empty set (0.00 sec)

mysql> select uname from userlist where uname regexp "b.*b";
+-------+
| uname |
+-------+
| bob   |
+-------+
1 row in set (0.00 sec)

mysql> select uname from userlist where uname regexp "^b.*b$";
+-------+
| uname |
+-------+
| bob   |
+-------+
1 row in set (0.00 sec)

mysql> select uname from userlist where uname regexp "^bb$";
Empty set (0.00 sec)

mysql> select uname from userlist where uname regexp "^a.*t$";
+-------+
| uname |
+-------+
| abrt  |
+-------+
1 row in set (0.00 sec)

mysql> select uname from userlist where uname regexp "[0-9]";
+-------+
| uname |
+-------+
| 6plj  |
| p7lj  |
| pl8j  |
| plj9  |
+-------+
4 rows in set (0.00 sec)

mysql> select uname from userlist where uname regexp "^[0-9]";
+-------+
| uname |
+-------+
| 6plj  |
+-------+
1 row in set (0.00 sec)

mysql> select uname from userlist where uname regexp "[0-9]$";
+-------+
| uname |
+-------+
| plj9  |
+-------+
1 row in set (0.00 sec)

//mysql不区分大小写，所以正则表达式的大小写依旧不区分
mysql> select uname from userlist where uname regexp "[A-Z]";
+-----------+
| uname     |
+-----------+
| 6plj      |
| abrt      |
| adm       |
| bin       |
| bob       |
| daemon    |
| dbus      |
| ftp       |
| games     |
| gopher    |
| haldaemon |
| halt      |
……

聚集函数操作实例

• max(字段名) 获取最大值
• min(字段名) 获取最小值
• avg(字段名) 获取平均值
• sum(字段名) 求和
• count(字段名) 获取字段值个数

//内置函数
//该类函数不能在where后面的表达式中使用，SQL不支持这样操作

mysql> select shell from userlist where shell is null;
+-------+
| shell |
+-------+
| NULL  |
| NULL  |
| NULL  |
| NULL  |
| NULL  |
| NULL  |
| NULL  |
| NULL  |
| NULL  |
| NULL  |
+-------+
10 rows in set (0.00 sec)

mysql> select count(shell) from userlist where shell is null;
+--------------+
| count(shell) |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

mysql> select count(u_id) from userlist where shell is null;
+-------------+
| count(u_id) |
+-------------+
|          10 |
+-------------+
1 row in set (0.00 sec)

mysql> select count(*) from userlist;
+----------+
| count(*) |
+----------+
|       37 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*),count(u_id),count(uname),count(shell) from userlist;
+----------+-------------+--------------+--------------+
| count(*) | count(u_id) | count(uname) | count(shell) |
+----------+-------------+--------------+--------------+
|       37 |          37 |           36 |           27 |
+----------+-------------+--------------+--------------+
1 row in set (0.00 sec)

//rhcsa_n这一列的数值的和
mysql> select sum(rhcsa_n) from userlist;
+--------------+
| sum(rhcsa_n) |
+--------------+
|      2170.00 |
+--------------+
1 row in set (0.00 sec)

mysql> select avg(rhcsa_n) from userlist;
+--------------+
| avg(rhcsa_n) |
+--------------+
|    58.648649 |
+--------------+
1 row in set (0.00 sec)

mysql> select max(rhcsa_n) from userlist;
+--------------+
| max(rhcsa_n) |
+--------------+
|        70.00 |
+--------------+
1 row in set (0.00 sec)

mysql> select max(uid) from userlist;
+----------+
| max(uid) |
+----------+
|      503 |
+----------+
1 row in set (0.00 sec)

mysql> select min(gid) from userlist;
+----------+
| min(gid) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

//聚合函数不能这样使用
mysql> select uname,rhcsa_n from userlist where rhcsa_n < avg(rhcsa_n);
ERROR 1111 (HY000): Invalid use of group function

分组操作实例

格式：

• 查询分组 group by 字段名
• sql查询命令 group by 字段名;

//group on

mysql> select shell from userlist where uid<10;
+----------------+
| shell          |
+----------------+
| /bin/bash      |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /bin/sync      |
| /sbin/shutdown |
| /sbin/halt     |
| /sbin/nologin  |
+----------------+
9 rows in set (0.00 sec)

mysql> select shell from userlist where uid<10 group by shell;
+----------------+
| shell          |
+----------------+
| /bin/bash      |
| /bin/sync      |
| /sbin/halt     |
| /sbin/nologin  |
| /sbin/shutdown |
+----------------+
5 rows in set (0.00 sec)

mysql> select shell from userlist where uid<100 group by shell;
+----------------+
| shell          |
+----------------+
| /bin/bash      |
| /bin/sync      |
| /sbin/halt     |
| /sbin/nologin  |
| /sbin/shutdown |
+----------------+
5 rows in set (0.00 sec)

//该操作没有意义，只能显示当前shell值的第一次出现的uname的值
mysql> select uname from userlist where uid<100 group by shell;
+----------+
| uname    |
+----------+
| root     |
| sync     |
| halt     |
| bin      |
| shutdown |
+----------+
5 rows in set (0.00 sec)

mysql> select uid,uname,shell from userlist where uid<100 group by shell;
+------+----------+----------------+
| uid  | uname    | shell          |
+------+----------+----------------+
|    0 | root     | /bin/bash      |
|    5 | sync     | /bin/sync      |
|    7 | halt     | /sbin/halt     |
|    1 | bin      | /sbin/nologin  |
|    6 | shutdown | /sbin/shutdown |
+------+----------+----------------+
5 rows in set (0.00 sec)


mysql> select uid,uname,shell from userlist group by shell;
+------+----------+----------------+
| uid  | uname    | shell          |
+------+----------+----------------+
|  503 | jack     | NULL           |
|    0 | root     | /bin/bash      |
|    5 | sync     | /bin/sync      |
|    7 | halt     | /sbin/halt     |
|    1 | bin      | /sbin/nologin  |
|    6 | shutdown | /sbin/shutdown |
+------+----------+----------------+
6 rows in set (0.00 sec)

//两者效果相同
mysql> select distinct shell from userlist ;
+----------------+
| shell          |
+----------------+
| /bin/bash      |
| /sbin/nologin  |
| /bin/sync      |
| /sbin/shutdown |
| /sbin/halt     |
| NULL           |
+----------------+
6 rows in set (0.00 sec)

查询排序操作实例：

查询排序 order by

格式：
sql查询命令 order by 字段名 排序方式
asc 升序（默认）
desc 降序

mysql> select uid,uname,shell from userlist where uid<10 order by uid;
+------+----------+----------------+
| uid  | uname    | shell          |
+------+----------+----------------+
|    0 | root     | /bin/bash      |
|    1 | bin      | /sbin/nologin  |
|    2 | daemon   | /sbin/nologin  |
|    3 | adm      | /sbin/nologin  |
|    4 | lp       | /sbin/nologin  |
|    5 | sync     | /bin/sync      |
|    6 | shutdown | /sbin/shutdown |
|    7 | halt     | /sbin/halt     |
|    8 | mail     | /sbin/nologin  |
+------+----------+----------------+
9 rows in set (0.00 sec)

mysql> select uid,uname,shell from userlist where uid<10 order by uid desc;
+------+----------+----------------+
| uid  | uname    | shell          |
+------+----------+----------------+
|    8 | mail     | /sbin/nologin  |
|    7 | halt     | /sbin/halt     |
|    6 | shutdown | /sbin/shutdown |
|    5 | sync     | /bin/sync      |
|    4 | lp       | /sbin/nologin  |
|    3 | adm      | /sbin/nologin  |
|    2 | daemon   | /sbin/nologin  |
|    1 | bin      | /sbin/nologin  |
|    0 | root     | /bin/bash      |
+------+----------+----------------+
9 rows in set (0.00 sec)

mysql> select uid,uname,shell from userlist where uid<10 order by uname desc;
+------+----------+----------------+
| uid  | uname    | shell          |
+------+----------+----------------+
|    5 | sync     | /bin/sync      |
|    6 | shutdown | /sbin/shutdown |
|    0 | root     | /bin/bash      |
|    8 | mail     | /sbin/nologin  |
|    4 | lp       | /sbin/nologin  |
|    7 | halt     | /sbin/halt     |
|    2 | daemon   | /sbin/nologin  |
|    1 | bin      | /sbin/nologin  |
|    3 | adm      | /sbin/nologin  |
+------+----------+----------------+
9 rows in set (0.00 sec)

mysql> select uid,uname,shell from userlist where uid<10 order by shell;
+------+----------+----------------+
| uid  | uname    | shell          |
+------+----------+----------------+
|    0 | root     | /bin/bash      |
|    5 | sync     | /bin/sync      |
|    7 | halt     | /sbin/halt     |
|    1 | bin      | /sbin/nologin  |
|    2 | daemon   | /sbin/nologin  |
|    3 | adm      | /sbin/nologin  |
|    4 | lp       | /sbin/nologin  |
|    8 | mail     | /sbin/nologin  |
|    6 | shutdown | /sbin/shutdown |
+------+----------+----------------+
9 rows in set (0.00 sec)


mysql> select uid,uname,shell from userlist where uid<10 order by uname;
+------+----------+----------------+
| uid  | uname    | shell          |
+------+----------+----------------+
|    3 | adm      | /sbin/nologin  |
|    1 | bin      | /sbin/nologin  |
|    2 | daemon   | /sbin/nologin  |
|    7 | halt     | /sbin/halt     |
|    4 | lp       | /sbin/nologin  |
|    8 | mail     | /sbin/nologin  |
|    0 | root     | /bin/bash      |
|    6 | shutdown | /sbin/shutdown |
|    5 | sync     | /bin/sync      |
+------+----------+----------------+
9 rows in set (0.00 sec)

having 条件操作实例

在查询结果里顾虑数据 having 条件

//从结果上来说和and等条件查询是一样的，但是由于having是从查询结果中再次进行匹配和检索
//这样操作的开销会比联合多条件查询要小，速度也会更快，
//多条件的联合查询是逐条进行检索操作
//在开发中或可以使用having语法进行二次检索
//select uname from userlist where uname is not null and uname="tom";
mysql> select uname from userlist where uname is not null having uname="tom";
+-------+
| uname |
+-------+
| tom   |
+-------+
1 row in set (0.00 sec)

mysql> select uname from userlist where shell="/bin/bash" having uname="root";
+-------+
| uname |
+-------+
| root  |
+-------+
1 row in set (0.00 sec)

mysql> select uname,shell from userlist where shell="/bin/bash" having uname="root";
+-------+-----------+
| uname | shell     |
+-------+-----------+
| root  | /bin/bash |
+-------+-----------+
1 row in set (0.00 sec)

mysql> select u_id,uname from userlist where uname like '%' having u_id in (30,31,32);
Empty set (0.00 sec)

mysql> select u_id,uname from userlist where uname like '%' having u_id in (10,11,12);
+------+----------+
| u_id | uname    |
+------+----------+
|   12 | games    |
|   11 | operator |
|   10 | uucp     |
+------+----------+
3 rows in set (0.00 sec)

limit操作实例

limit 限制显示查询结果记录的行数。

格式:

• limit 数字；
• limit 起始行，共显示几行

mysql> select u_id,uname,uid from userlist order by uid desc limit 1;
+------+-------+------+
| u_id | uname | uid  |
+------+-------+------+
|   35 | tom   |  503 |
+------+-------+------+
1 row in set (0.00 sec)

mysql> select u_id,uname,uid from userlist order by uid desc limit 5,10;
+------+-----------+------+
| u_id | uname     | uid  |
+------+-----------+------+
|   15 | nobody    |   99 |
|   22 | postfix   |   89 |
|   16 | dbus      |   81 |
|   23 | sshd      |   74 |
|   24 | tcpdump   |   72 |
|   17 | vcsa      |   69 |
|   19 | haldaemon |   68 |
|   20 | ntp       |   38 |
|   25 | mysql     |   27 |
|   14 | ftp       |   14 |
+------+-----------+------+
10 rows in set (0.00 sec)

mysql> select * from userlist limit 1;
+------+-------+------------+------+------+---------+---------+-----------+-----+---------+--------+
| u_id | uname | passwdmark | uid  | gid  | comment | homedir | shell     | age | rhcsa_n | rhce_n |
+------+-------+------------+------+------+---------+---------+-----------+-----+---------+--------+
|   01 | root  | x          |    0 |    0 | root    | /root   | /bin/bash |  19 |   70.00 |  90.00 |
+------+-------+------------+------+------+---------+---------+-----------+-----+---------+--------+
1 row in set (0.00 sec)

mysql> select * from userlist limit 3;
+------+--------+------------+------+------+---------+---------+---------------+-----+---------+--------+
| u_id | uname  | passwdmark | uid  | gid  | comment | homedir | shell         | age | rhcsa_n | rhce_n |
+------+--------+------------+------+------+---------+---------+---------------+-----+---------+--------+
|   01 | root   | x          |    0 |    0 | root    | /root   | /bin/bash     |  19 |   70.00 |  90.00 |
|   02 | bin    | x          |    1 |    1 | bin     | /bin    | /sbin/nologin |  19 |   70.00 |  90.00 |
|   03 | daemon | x          |    2 |    2 | daemon  | /sbin   | /sbin/nologin |  19 |   70.00 |  90.00 |
+------+--------+------------+------+------+---------+---------+---------------+-----+---------+--------+
3 rows in set (0.00 sec）

distinct 操作实例

DISTINCT 不显示重复的值

mysql> select distinct shell from userlist ;
+----------------+
| shell          |
+----------------+
| /bin/bash      |
| /sbin/nologin  |
| /bin/sync      |
| /sbin/shutdown |
| /sbin/halt     |
| NULL           |
+----------------+
6 rows in set (0.00 sec)

where操作实例

where 子查询
把内层查询结果做为外层的查询条件。
select 字段名列表 from 表A 条件 （select 字段名列表 from 表B）；

//where 子查询
//常用于多表查询或单个表的嵌套递归查询

//聚合函数不能这样使用
mysql> select uname,rhcsa_n from userlist where rhcsa_n < avg(rhcsa_n);
ERROR 1111 (HY000): Invalid use of group function

mysql> select uname,rhcsa_n from userlist where rhcsa_n < (select avg(rhcsa_n) from userlist);
+-------+---------+
| uname | rhcsa_n |
+-------+---------+
| NULL  |    0.00 |
| bob   |    0.00 |
| plj9  |    0.00 |
| pl8j  |    0.00 |
| p7lj  |    0.00 |
| 6plj  |    0.00 |
+-------+---------+
6 rows in set (0.00 sec)

//安装mysql时自带的数据库
mysql> select user,host from mysql.user;
+------+---------------+
| user | host          |
+------+---------------+
| root | 127.0.0.1     |
| root | ::1           |
| root | localhost     |
| root | mysql.wolf.cn |
+------+---------------+
4 rows in set (0.00 sec)

mysql> select uname from userlist where uname in (select user from mysql.user);
+-------+
| uname |
+-------+
| root  |
+-------+
1 row in set (0.00 sec)

mysql> select uname from userlist where uname in (select user from mysql.user where host="localhost");
+-------+
| uname |
+-------+
| root  |
+-------+
1 row in set (0.00 sec)

复制表操作

• 复制表（备份表 快速创建新表）
  create table 新表名 sql查询命令；

• 复制全表
  create table 新表名 select * from 表名；
  create table stu4 select * from student;

• 复制部分数据
  create table 新表名 select 字段名列表

from 表名 where 条件；
create table stu3 select name,homedir,shell from student where uid<=10 ;

• 只复制表结构
  create table 新表名 select * from 表名 where 1 = 2；
  create table stu2 select * from student where 1 = 2;

在复制表时，源表的索引属性并不会随之到新表中。例如主键并不会随之复制。

mysql> create  table userlist_bak select * from userlist;
Query OK, 37 rows affected (0.52 sec)
Records: 37  Duplicates: 0  Warnings: 0

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| userlist          |
| userlist_bak      |
+-------------------+
2 rows in set (0.00 sec)

mysql> select * from userlist_bak;
+------+-----------+------------+------+------+------------------------------+---------------------+----------------+-----+---------+--------+
| u_id | uname     | passwdmark | uid  | gid  | comment                      | homedir             | shell          | age | rhcsa_n | rhce_n |
+------+-----------+------------+------+------+------------------------------+---------------------+----------------+-----+---------+--------+
|   01 | root      | x          |    0 |    0 | root                         | /root               | /bin/bash      |  19 |   70.00 |  90.00 |
|   02 | bin       | x          |    1 |    1 | bin                          | /bin                | /sbin/nologin  |  19 |   70.00 |  90.00 |
……

//源表的索引属性并不会随之到新表中
mysql> desc userlist_bak;
+------------+--------------------------+------+-----+---------+-------+
| Field      | Type                     | Null | Key | Default | Extra |
+------------+--------------------------+------+-----+---------+-------+
| u_id       | int(2) unsigned zerofill | NO   |     | 00      |       |
| uname      | char(10)                 | YES  |     | NULL    |       |
| passwdmark | char(10)                 | YES  |     | NULL    |       |
| uid        | int(3)                   | YES  |     | NULL    |       |
| gid        | smallint(3)              | YES  |     | NULL    |       |
| comment    | varchar(50)              | YES  |     | NULL    |       |
| homedir    | char(30)                 | YES  |     | NULL    |       |
| shell      | char(30)                 | YES  |     | NULL    |       |
| age        | tinyint(2)               | NO   |     | 19      |       |
| rhcsa_n    | double(5,2)              | NO   |     | 0.00    |       |
| rhce_n     | double(5,2)              | NO   |     | 0.00    |       |
+------------+--------------------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

mysql> create table ush_bak select uname,shell from userlist;
Query OK, 37 rows affected (0.57 sec)
Records: 37  Duplicates: 0  Warnings: 0

mysql> desc ush_bak;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| uname | char(10) | YES  |     | NULL    |       |
| shell | char(30) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> create table  uframe_bak select * from userlist  where 1=2;
Query OK, 0 rows affected (0.42 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc uframe_bak;
+------------+--------------------------+------+-----+---------+-------+
| Field      | Type                     | Null | Key | Default | Extra |
+------------+--------------------------+------+-----+---------+-------+
| u_id       | int(2) unsigned zerofill | NO   |     | 00      |       |
| uname      | char(10)                 | YES  |     | NULL    |       |
| passwdmark | char(10)                 | YES  |     | NULL    |       |
| uid        | int(3)                   | YES  |     | NULL    |       |
| gid        | smallint(3)              | YES  |     | NULL    |       |
| comment    | varchar(50)              | YES  |     | NULL    |       |
| homedir    | char(30)                 | YES  |     | NULL    |       |
| shell      | char(30)                 | YES  |     | NULL    |       |
| age        | tinyint(2)               | NO   |     | 19      |       |
| rhcsa_n    | double(5,2)              | NO   |     | 0.00    |       |
| rhce_n     | double(5,2)              | NO   |     | 0.00    |       |
+------------+--------------------------+------+-----+---------+-------+
11 rows in set (0.01 sec)

mysql> select * from uframe_bak;
Empty set (0.00 sec)