基于Linux的MySQL操作实例之数据导入/导出，新增/查询表记录
来源：互联网 发布：网络文明与安全ppt 编辑：程序博客网 时间：2024/05/20 19:14
基于Linux的MySQL操作实例之数据导入/导出，新增/查询表记录
前言
本篇就数据的导入/导出数据表，新增/查询表记录这些部分进行了实例和基本概念说明。
本篇对板式做了一些修改，希望大家喜欢。
本篇内容结合本人其他几篇一起观看效果更佳，有兴趣的各位可以看看。
基于Linux的MySQL操作实例（软件安装，mysql基本操作，mysql数据类型，建表、插入数据操作）
基于Linux的MySQL操作实例(修改表结构，MySQL索引，MySQL数据引擎)
http://blog.csdn.net/ll845876425/article/details/54578113
http://blog.csdn.net/ll845876425/article/details/54588621
数据导入
概述
数据导入：把系统文件的内容存储到数据库的表里。
语法格式
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 changedmysql> 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: 0mysql> 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: 0mysql> 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 mysqluid=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]# lstest1.txt[root@mysql mysql_doc]# cat test1.txt root /bin/bashmysql /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----/rootbin----/bindaemon----/sbinadm----/var/admlp----/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: 0mysql> 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 valuemysql> 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",""都代表字符串，不表示NULLmysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> update userlist set rhce_n=90;Query OK, 31 rows affected (0.05 sec)Rows matched: 31 Changed: 31 Warnings: 0mysql> 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://blog.csdn.net/ll845876425/article/details/53958083
支持符号：”^”，”$”，”.”，”*”，”[ ]”
格式：字段名 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: 0mysql> 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 onmysql> 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 functionmysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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) 2 0
- 基于Linux的MySQL操作实例之数据导入/导出，新增/查询表记录
- 运维之mysql篇------3. 数据导入导出 、 表记录基本操作 、 查询及匹配条件 、 多表查询
- MySql之表记录操作（查询）
- MySql之表记录操作（增删改）
- 存储过程实例001--查询表记录
- PHP操作MySql数据库表记录
- linux 下mysql数据的导入、导出
- Linux下Mysql数据的导入导出
- mysql数据导入导出操作
- 【MySQL】关于大表记录的删除
- linux mysql 导入导出数据
- 导入,导出mysql数据【linux】
- Linux Mysql数据导入导出
- mysql连接查询-主从表连接查询：获取从表最新一条表记录；获取没有从表记录的主表信息
- mysql复制表记录
- 基于Linux的MySQL操作实例(修改表结构，MySQL索引，MySQL数据引擎)
- MySQL数据导入导出及查询
- 原生态jdbc查询mysql数据库表记录
- UNICODE 字符写文本 乱码
- Vue2入门小结
- 2017进入前端的必备资料！！~~
- 仿QQ6.0主页侧滑效果
- C:set标签结合javabean的使用
- 基于Linux的MySQL操作实例之数据导入/导出，新增/查询表记录
- STL的vector与MFC的CArray哪个速度更快？
- Python学习——Python Syntax
- read selection
- SQL 取出UserID相同记录中的一条，并且取的是ID最大的。
- AIDL 使用详解 （一） Android跨进程调用
- 挑战程序设计竞赛 POJ 1192食物链 带权并查集,略麻烦
- 云服务器搭建个人博客（上）
- servlet_13 Cookie处理
