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

最新推荐文章于 2022-03-07 11:16:36 发布
最新推荐文章于 2022-03-07 11:16:36 发布
阅读量2.4k 收藏 5
点赞数 2
分类专栏： MySQL 云计算 运维 Linux 文章标签： mysql linux 数据库 Shell
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ll845876425/article/details/54605767
版权
云计算 同时被 3 个专栏收录
113 篇文章 4 订阅
订阅专栏
运维
98 篇文章 1 订阅
订阅专栏
Linux
82 篇文章 3 订阅
订阅专栏

基于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 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://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: 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)
海渊_haiyuan
关注
专栏目录
【MySQL基础】 MySQL 查询/更新数据记录，导出/导入数据！
Sixstar_yanxin的博客
07-10 489
除非最终检索它们并利用它们来做点事情，否则将记录放入数据库没什么好处。这就是数据记录查询的用途，即帮助取出数据，也是 Python API 与 MySQL 交互的重要部分。SELECT 大概是 SQL 语言中最常用的语句，而且怎样使用它也最为讲究；用它来选择记录可能相当复杂，可能会涉及许多表中列之间的比较。本节介绍 Select 语句关于查询的最基本功能，并联系 Python 对查询数据的读取。 点击此处加人我们的学习基地吧！ SELECT 查询 语法： SELECT selection_list
如何使用SQL Server导入和导出向导将数据导入/导出到SQL Server
热门推荐
culuo4781的博客
07-19 1万+
This article will explain the steps for importing and exporting data using the SQL Server Import and Export Wizard. 本文将介绍使用SQL Server导入和导出向导导入和导出数据的步骤。 Depending on the options that are s...
linux下导入导出mysql数据库及其导出数据命令大汇总
12-08
linux下操作mysql数据库,导出数据库,只导出表结构，只导出数据。大汇总,挺全面的。希望对linux下用mysql数据库导数据的使用提供帮助
linux下导入、导出mysql数据库命令
weixin_30299539的博客
04-20 1117
一、导出数据库用mysqldump命令（注意mysql的安装路径，即此命令的路径）： 1、导出数据和表结构： mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql #/usr/local/mysql/bin/ mysqldump -uroot -p abc > abc.sql 敲回车后会提示输入密码 2、只导出表结构 mysqldump -u用户名 ...
Linux下mysql数据库简单导入和导出操作
weixin_33911824的博客
07-11 75
1、用mysql自带mysqldump命令行导出数据库一、命令格式mysqldump -u用户名 -p 数据库名 > 数据库名.sql范例：mysqldump -u root -p twinkle > twinkle.sql（导出数据库twinkle到twinkle.sql文件）提示输入密码时，输入该数据库用户名的密码二、如果导出mysql里面的所有数据库，则语句...
linux 服务器中 导出mysql查询出来的数据
weixin_42903592的博客
03-07 2773
查询导出的配置信息 show variables like '%secure%'; 其中secure_file_priv为导出路径 1、限制mysqld 不允许导入 | 导出 　　　 secure_file_prive=null 　　2、限制mysqld 的导入 | 导出 只能发生在/tmp/目录下 　　　secure_file_priv=/tmp/ 　　3、不对mysqld 的导入 | 导出做限制 secure_file_priv 4、可以导出至任意目录 secure_file_priv="/" 这个
linux下mysql数据全部导出
文盲青年的博客
04-25 6446
有些特殊时候，当我们的服务器崩了，需要重装系统，那么我们mysql的数据丢失了太可惜了，可以将root下所有数据导入到一个sql文件里面，命令如下： mysqldump -uroot -p --all-databases &amp;gt; / sqlfile.sql ...
SQLServer导出数据到MySQL实例介绍
12-15
本文将详细介绍如何从SQLServer导出数据并导入到MySQL实例中。 首先，我们关注SQLServer的数据导出过程。这里主要使用BCP（Bulk Copy Program）工具，这是一个强大的命令行实用程序，用于批量导入和导出SQL Server...
MySQL数据导入导出方法与工具mysqlimport.pdf
09-14
MySQL 数据导入导出方法与工具 mysqlimport MySQL 是一个功能强大且广泛使用的开源关系数据库管理系统，mysqlimport 是 MySQL 中的一个命令行工具，用于将文本文件导入到 MySQL 数据库中。下面我们将详细介绍 ...
linux命令行下使用mysql，怎么导出查询的记录集
yanmou8222的博客
08-27 2418
用命令select * from table into outfile ‘xxx.txt’;就可以把查询数据导出在当前数据库保存位置的xxx.txt文件里面了。 也可以直接在linux命令用mysql -h127.0.0.1 -uroot -pXXXX -P3306 -e”select * from table” &gt; /tmp/test.txt 远程连接推荐使用第二种，可以导出在本地目...
Linux中mysqldump命令实例详解
09-09
mysqldump是mysql数据库中备份工具，用于将MYSQL服务器中的数据库以标准的sql语言的方式导出，并保存到文件中。今天通过本文给大家介绍mysqldump命令实例详解，一起看看吧
linux环境下面导出mysql数据,Linux环境下mysql数据库导入导出方法
weixin_42134144的博客
05-11 263
导出整个数据库中的所有数据1、在linux命令行下输入：mysqldump -u userName -p dabaseName > fileName.sqlfileName.sql最好加上路径名导出数据库中的某个表的数据mysqldump -u userName -p dabaseName tableName > fileName.sql导出整个数据库中的所有的表结构在linux...
用shell脚本给mysql插入数据_linux上shell脚本数据插入mySql
weixin_35982453的博客
02-05 621
#!/bin/bash#每次插入数据量的随机数组及数组长度insertDataCountArray=(100 500 1000 1500 2000)insertDataNum=${#insertDataCountArray[*]}#名字的随机数组及数组长度nameArray=(LiSi ZhangSan WangWu SunLiu)nameNum=${#nameArray[*]}#年龄的随机数组及...
linux中的文件 上传、下载
every_evy的博客
06-02 538
1、上传 rz 弹出框选择文件 2、下载 sz 文件名 例： sz temp.txt  然后保存到本地 3、转储 将 table 转储 .sql 文件 mysqldump -uroot -paikan cp tbl_cp_partner_category_map > zuo/map.sql
Linux下mysql数据库的导入导出操作
weixin_34220623的博客
03-30 196
1、导入数据库在命令行下输入：mysql -u username -p test < /home/data/test.sql说明：username是数据库用户名，test为目标数据库，回车，输入数据库密码，就进行导入操作了！mysql>use test;mysql>set names utf8;mysql>source D:/www/sql/back....
mysql查看导入进度_查看MySQL LOAD DATA进度
weixin_27032989的博客
01-19 2299
概述开发过程中经常会使用MySQL的LOAD DATA功能，用于导入文件到MySQL的指定数据库表中。若已经将文件切分为N个小文件再进行LOAD操作(例如使用Linux下的 split 工具)，那么进度还是很容易把控的，可以通过直接查找当前正在进行导入的分片，进而判断当前的分片。可是，如果某些情况下直接对一个大型的文件进行进行LOAD操作，整个过程并不能直观的获取当前的进度的，需要通过一些相对曲折...
linux mysql 导出表_linux下用命令导出mysql表数据
weixin_39949473的博客
01-18 578
由于数据库服务器是内网环境，只能通过linux跳板机连接，所以navicat工具暂时用不上。1.用Xshell工具连接跳板机2.再通过跳板机连接数据库服务器>ssh -p port ip>输入密码进入到数据库服务器3.>cd /usr/bin进入到mysql的bin目录4.将数据库中的某一个表数据导入到home目录下的data.sql文件>mysqldump -u root...
把某个表的数据导出成insert语句（数据导出 insert语句）
蛙蛙池塘
11-12 1722
Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N[dbo].[proc_insert]) 
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值