基于Linux的MySQL操作实例之数据导入/导出，新增/查询表
2017-01-18 本文已影响102人
海渊_haiyuan
基于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)
