当前位置：首页 > article >正文

SQL自学，mysql从入门到精通 --- 第 15天，数据导入、导出

article 2026/4/28 3:07:58

数据的导入、导出

-- 查看当前设置的目录路径，限制从数据库服务器读取和写入文件的操作只能在指定的目录中进行,在安全性和文件操作限制方面具有重要意义。root@mysqldb 14:19:  [(none)]> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-------------------+
| Variable_name    | Value             |
+------------------+-------------------+
| secure_file_priv | /data/mysql/data/ |
+------------------+-------------------+
1 row in set (0.02 sec)

#配置文件
[root@mrloam ~]# grep secure_file_priv /etc/my.cnf 
secure_file_priv = /data/mysql/data

1.数据导入

root@mysqldb 14:40:  [(none)]> CREATE TABLE db3.user (-> name VARCHAR(30),-> password VARCHAR(8),-> uid INT,-> gid INT,-> comment VARCHAR(150),-> homedir CHAR(80),-> shell CHAR(60)-> );
Query OK, 0 rows affected (0.01 sec)root@mysqldb 14:40:  [(none)]> DESC db3.user;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| name     | varchar(30)  | YES  |     | NULL    |       |
| password | varchar(8)   | YES  |     | NULL    |       |
| uid      | int(11)      | YES  |     | NULL    |       |
| gid      | int(11)      | YES  |     | NULL    |       |
| comment  | varchar(150) | YES  |     | NULL    |       |
| homedir  | char(80)     | YES  |     | NULL    |       |
| shell    | char(60)     | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
7 rows in set (0.01 sec)root@mysqldb 14:40:  [(none)]> SELECT * FROM db3.user;
Empty set (0.00 sec)

-- 准备数据文件
root@mysqldb 14:41:  [(none)]> system cp /etc/passwd /data/mysql/data/
root@mysqldb 14:42:  [(none)]> system ls /data/mysql/data/
auto.cnf	 client-key.pem  ibdata1      mysql		  public_key.pem	    sys
ca-key.pem	 d1		 ib_logfile0  passwd		  server-cert.pem
ca.pem		 db3		 ib_logfile1  performance_schema  server-key.pem
client-cert.pem  ib_buffer_pool  ibtmp1       private_key.pem	  sql_self_learning_skills-- 导入数据
root@mysqldb 14:43:  [(none)]> load data infile "/data/mysql/data/passwd" into table db3.user fields terminated by ":" lines terminated by "\n";
Query OK, 21 rows affected (0.00 sec)
Records: 21  Deleted: 0  Skipped: 0  Warnings: 0root@mysqldb 14:45:  [(none)]> alter table db3.user  add  id  int  primary key  auto_increment first;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0root@mysqldb 14:46:  [(none)]> select  * from db3.user ;
+----+-----------------+----------+------+------+----------------------------+--------------------+----------------+
| id | name            | password | uid  | gid  | comment                    | homedir            | shell          |
+----+-----------------+----------+------+------+----------------------------+--------------------+----------------+
|  1 | root            | x        |    0 |    0 | root                       | /root              | /bin/bash      |
|  2 | bin             | x        |    1 |    1 | bin                        | /bin               | /sbin/nologin  |
|  3 | daemon          | x        |    2 |    2 | daemon                     | /sbin              | /sbin/nologin  |
|  4 | adm             | x        |    3 |    4 | adm                        | /var/adm           | /sbin/nologin  |
|  5 | lp              | x        |    4 |    7 | lp                         | /var/spool/lpd     | /sbin/nologin  |
|  6 | sync            | x        |    5 |    0 | sync                       | /sbin              | /bin/sync      |
|  7 | shutdown        | x        |    6 |    0 | shutdown                   | /sbin              | /sbin/shutdown |
|  8 | halt            | x        |    7 |    0 | halt                       | /sbin              | /sbin/halt     |
|  9 | mail            | x        |    8 |   12 | mail                       | /var/spool/mail    | /sbin/nologin  |
| 10 | operator        | x        |   11 |    0 | operator                   | /root              | /sbin/nologin  |
| 11 | games           | x        |   12 |  100 | games                      | /usr/games         | /sbin/nologin  |
| 12 | ftp             | x        |   14 |   50 | FTP User                   | /var/ftp           | /sbin/nologin  |
| 13 | nobody          | x        |   99 |   99 | Nobody                     | /                  | /sbin/nologin  |
| 14 | systemd-network | x        |  192 |  192 | systemd Network Management | /                  | /sbin/nologin  |
| 15 | dbus            | x        |   81 |   81 | System message bus         | /                  | /sbin/nologin  |
| 16 | polkitd         | x        |  999 |  998 | User for polkitd           | /                  | /sbin/nologin  |
| 17 | sshd            | x        |   74 |   74 | Privilege-separated SSH    | /var/empty/sshd    | /sbin/nologin  |
| 18 | postfix         | x        |   89 |   89 |                            | /var/spool/postfix | /sbin/nologin  |
| 19 | chrony          | x        |  998 |  996 |                            | /var/lib/chrony    | /sbin/nologin  |
| 20 | mysql           | x        | 1000 | 1000 |                            | /home/mysql        | /sbin/nologin  |
| 21 | test            | x        | 1001 | 1001 |                            | /home/test         | /bin/bash      |
+----+-----------------+----------+------+------+----------------------------+--------------------+----------------+
21 rows in set (0.00 sec)

2.数据导出

把表记录存储到系统文件里

批量处理数据

命令格式:

sql查询命令 into outfile “目录/文件名”

[ fields terminated by “列间隔符号” lines terminated by “行间隔符号”];

-- 数据导出
root@mysqldb 14:47:  [(none)]> select * from db3.user where id <= 10 into outfile "/data/mysql/data/a.txt";
Query OK, 10 rows affected (0.00 sec)root@mysqldb 14:51:  [(none)]> select  name ,shell from db3.user  into outfile "/data/mysql/data/b.txt" fields terminated  by "###";
Query OK, 21 rows affected (0.00 sec)root@mysqldb 14:51:  [(none)]> select name , shell ,  uid  from  db3.user where id <= 3-> into outfile  "/data/mysql/data/c.txt"-> fields terminated by "????" lines terminated by "###" ;
Query OK, 3 rows affected (0.00 sec)

#查看
[root@mrloam ~]# cat /data/mysql/data/a.txt 
1	root	x	0	0	root	/root	/bin/bash
2	bin	x	1	1	bin	/bin	/sbin/nologin
3	daemon	x	2	2	daemon	/sbin	/sbin/nologin
4	adm	x	3	4	adm	/var/adm	/sbin/nologin
5	lp	x	4	7	lp	/var/spool/lpd	/sbin/nologin
6	sync	x	5	0	sync	/sbin	/bin/sync
7	shutdown	x	6	0	shutdown	/sbin	/sbin/shutdown
8	halt	x	7	0	halt	/sbin	/sbin/halt
9	mail	x	8	12	mail	/var/spool/mail	/sbin/nologin
10	operator	x	11	0	operator	/root	/sbin/nologin[root@mrloam ~]# cat /data/mysql/data/b.txt 
root###/bin/bash
bin###/sbin/nologin
daemon###/sbin/nologin
adm###/sbin/nologin
lp###/sbin/nologin
sync###/bin/sync
shutdown###/sbin/shutdown
halt###/sbin/halt
mail###/sbin/nologin
operator###/sbin/nologin
games###/sbin/nologin
ftp###/sbin/nologin
nobody###/sbin/nologin
systemd-network###/sbin/nologin
dbus###/sbin/nologin
polkitd###/sbin/nologin
sshd###/sbin/nologin
postfix###/sbin/nologin
chrony###/sbin/nologin
mysql###/sbin/nologin
test###/bin/bash[root@mrloam ~]# cat /data/mysql/data/c.txt 
root????/bin/bash????0###bin????/sbin/nologin????1###daemon????/sbin/nologin????2###

3.管理表记录

root@mysqldb 15:06:  [(none)]> insert  into  db3.user-> values-> (22,"tom","x",2001,2001,"test user","/home/tom","/bin/bash"), -> (23,"tom","x",2001,2001,"test user","/home/tom","/bin/bash"), -> (24,"tom","x",2001,2001,"test user","/home/tom","/bin/bash");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0root@mysqldb 14:59:  [(none)]> insert into  db3.user(name,uid,shell) values("bob",3000,"/bin/bash");
Query OK, 1 row affected (0.00 sec)root@mysqldb 15:00:  [(none)]> insert into  db3.user(name,uid,shell) ->   values->   ("bobf",3000,"/bin/bash"),("bobc",3003,"/bin/bash"),("bobA",3002,"/bin/bash");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

root@mysqldb 15:01:  [(none)]> select  *  from  db3.user where  shell = "/sbin/nologin" ;
+----+-----------------+----------+------+------+----------------------------+--------------------+-------------
| id | name            | password | uid  | gid  | comment                    | homedir            | shell       
+----+-----------------+----------+------+------+----------------------------+--------------------+-------------
|  2 | bin             | x        |    1 |    1 | bin                        | /bin               | /sbin/nologi
|  3 | daemon          | x        |    2 |    2 | daemon                     | /sbin              | /sbin/nologi
|  4 | adm             | x        |    3 |    4 | adm                        | /var/adm           | /sbin/nologi
|  5 | lp              | x        |    4 |    7 | lp                         | /var/spool/lpd     | /sbin/nologi
|  9 | mail            | x        |    8 |   12 | mail                       | /var/spool/mail    | /sbin/nologi
| 10 | operator        | x        |   11 |    0 | operator                   | /root              | /sbin/nologi
| 11 | games           | x        |   12 |  100 | games                      | /usr/games         | /sbin/nologi
| 12 | ftp             | x        |   14 |   50 | FTP User                   | /var/ftp           | /sbin/nologi
| 13 | nobody          | x        |   99 |   99 | Nobody                     | /                  | /sbin/nologi
| 14 | systemd-network | x        |  192 |  192 | systemd Network Management | /                  | /sbin/nologi
| 15 | dbus            | x        |   81 |   81 | System message bus         | /                  | /sbin/nologi
| 16 | polkitd         | x        |  999 |  998 | User for polkitd           | /                  | /sbin/nologi
| 17 | sshd            | x        |   74 |   74 | Privilege-separated SSH    | /var/empty/sshd    | /sbin/nologi
| 18 | postfix         | x        |   89 |   89 |                            | /var/spool/postfix | /sbin/nologi
| 19 | chrony          | x        |  998 |  996 |                            | /var/lib/chrony    | /sbin/nologi
| 20 | mysql           | x        | 1000 | 1000 |                            | /home/mysql        | /sbin/nologi
+----+-----------------+----------+------+------+----------------------------+--------------------+-------------
16 rows in set (0.00 sec)root@mysqldb 15:01:  [(none)]> select  name , shell  from  db3.user where  shell = "/sbin/nologin" ;
+-----------------+---------------+
| name            | shell         |
+-----------------+---------------+
| bin             | /sbin/nologin |
| daemon          | /sbin/nologin |
| adm             | /sbin/nologin |
| lp              | /sbin/nologin |
| mail            | /sbin/nologin |
| operator        | /sbin/nologin |
| games           | /sbin/nologin |
| ftp             | /sbin/nologin |
| nobody          | /sbin/nologin |
| systemd-network | /sbin/nologin |
| dbus            | /sbin/nologin |
| polkitd         | /sbin/nologin |
| sshd            | /sbin/nologin |
| postfix         | /sbin/nologin |
| chrony          | /sbin/nologin |
| mysql           | /sbin/nologin |
+-----------------+---------------+
16 rows in set (0.00 sec)

root@mysqldb 15:01:  [(none)]> update  db3.user set password="A" , comment="student" ;
Query OK, 28 rows affected (0.00 sec)
Rows matched: 28  Changed: 28  Warnings: 0root@mysqldb 15:02:  [(none)]> update db3.user set  password="x" , comment="root" where name="root";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@mysqldb 15:02:  [(none)]> delete from  db3.user where  id >= 21 ;
Query OK, 8 rows affected (0.00 sec)

root@mysqldb 15:07:  [(none)]> select  name  ,  uid  , gid   from db3.user  where  uid  !=  gid ;
+----------+------+------+
| name     | uid  | gid  |
+----------+------+------+
| adm      |    3 |    4 |
| lp       |    4 |    7 |
| sync     |    5 |    0 |
| shutdown |    6 |    0 |
| halt     |    7 |    0 |
| mail     |    8 |   12 |
| operator |   11 |    0 |
| games    |   12 |  100 |
| ftp      |   14 |   50 |
| polkitd  |  999 |  998 |
| chrony   |  998 |  996 |
+----------+------+------+
11 rows in set (0.00 sec)root@mysqldb 15:08:  [(none)]> select  name  ,  uid  , gid   from db3.user  where  uid  !=  gid ;
+----------+------+------+
| name     | uid  | gid  |
+----------+------+------+
| adm      |    3 |    4 |
| lp       |    4 |    7 |
| sync     |    5 |    0 |
| shutdown |    6 |    0 |
| halt     |    7 |    0 |
| mail     |    8 |   12 |
| operator |   11 |    0 |
| games    |   12 |  100 |
| ftp      |   14 |   50 |
| polkitd  |  999 |  998 |
| chrony   |  998 |  996 |
+----------+------+------+
11 rows in set (0.00 sec)root@mysqldb 15:08:  [(none)]> select  *   from db3.user  where  id <= 5 ;
+----+--------+----------+------+------+---------+----------------+---------------+
| id | name   | password | uid  | gid  | comment | homedir        | shell         |
+----+--------+----------+------+------+---------+----------------+---------------+
|  1 | root   | x        |    0 |    0 | root    | /root          | /bin/bash     |
|  2 | bin    | A        |    1 |    1 | student | /bin           | /sbin/nologin |
|  3 | daemon | A        |    2 |    2 | student | /sbin          | /sbin/nologin |
|  4 | adm    | A        |    3 |    4 | student | /var/adm       | /sbin/nologin |
|  5 | lp     | A        |    4 |    7 | student | /var/spool/lpd | /sbin/nologin |
+----+--------+----------+------+------+---------+----------------+---------------+
5 rows in set (0.00 sec)root@mysqldb 15:09:  [(none)]> select name  , shell  from  db3.user where  shell != "/bin/bash" ;
+-----------------+----------------+
| name            | shell          |
+-----------------+----------------+
| bin             | /sbin/nologin  |
| daemon          | /sbin/nologin  |
| adm             | /sbin/nologin  |
| lp              | /sbin/nologin  |
| sync            | /bin/sync      |
| shutdown        | /sbin/shutdown |
| halt            | /sbin/halt     |
| mail            | /sbin/nologin  |
| operator        | /sbin/nologin  |
| games           | /sbin/nologin  |
| ftp             | /sbin/nologin  |
| nobody          | /sbin/nologin  |
| systemd-network | /sbin/nologin  |
| dbus            | /sbin/nologin  |
| polkitd         | /sbin/nologin  |
| sshd            | /sbin/nologin  |
| postfix         | /sbin/nologin  |
| chrony          | /sbin/nologin  |
| mysql           | /sbin/nologin  |
+-----------------+----------------+
19 rows in set (0.00 sec)root@mysqldb 15:09:  [(none)]> select  name  , uid  , shell from  db3.user where  shell is not  null ;
+-----------------+------+----------------+
| name            | uid  | shell          |
+-----------------+------+----------------+
| root            |    0 | /bin/bash      |
| bin             |    1 | /sbin/nologin  |
| daemon          |    2 | /sbin/nologin  |
| adm             |    3 | /sbin/nologin  |
| lp              |    4 | /sbin/nologin  |
| sync            |    5 | /bin/sync      |
| shutdown        |    6 | /sbin/shutdown |
| halt            |    7 | /sbin/halt     |
| mail            |    8 | /sbin/nologin  |
| operator        |   11 | /sbin/nologin  |
| games           |   12 | /sbin/nologin  |
| ftp             |   14 | /sbin/nologin  |
| nobody          |   99 | /sbin/nologin  |
| systemd-network |  192 | /sbin/nologin  |
| dbus            |   81 | /sbin/nologin  |
| polkitd         |  999 | /sbin/nologin  |
| sshd            |   74 | /sbin/nologin  |
| postfix         |   89 | /sbin/nologin  |
| chrony          |  998 | /sbin/nologin  |
| mysql           | 1000 | /sbin/nologin  |
+-----------------+------+----------------+
20 rows in set (0.00 sec)root@mysqldb 15:09:  [(none)]> select  name  , uid  , shell from  db3.user where  shell is null ;
Empty set (0.00 sec)

root@mysqldb 15:10:  [(none)]> select name , uid from  db3.user where uid in (3 ,6,9,11);
+----------+------+
| name     | uid  |
+----------+------+
| adm      |    3 |
| shutdown |    6 |
| operator |   11 |
+----------+------+
3 rows in set (0.00 sec)root@mysqldb 15:10:  [(none)]> select shell  from  db3.user where  shell not  in  ("/bin/bash","/sbin/nologin"); 
+----------------+
| shell          |
+----------------+
| /bin/sync      |
| /sbin/shutdown |
| /sbin/halt     |
+----------------+
3 rows in set (0.00 sec)

root@mysqldb 15:10:  [(none)]> select name , uid  from  db3.user -> where  name = "sync" or  name = "apache" or  uid=3;
+------+------+
| name | uid  |
+------+------+
| adm  |    3 |
| sync |    5 |
+------+------+
2 rows in set (0.00 sec)root@mysqldb 15:11:  [(none)]> select  * from  db3.user where id >= 10  and  id <= 20 ;
+----+-----------------+----------+------+------+---------+--------------------+---------------+
| id | name            | password | uid  | gid  | comment | homedir            | shell         |
+----+-----------------+----------+------+------+---------+--------------------+---------------+
| 10 | operator        | A        |   11 |    0 | student | /root              | /sbin/nologin |
| 11 | games           | A        |   12 |  100 | student | /usr/games         | /sbin/nologin |
| 12 | ftp             | A        |   14 |   50 | student | /var/ftp           | /sbin/nologin |
| 13 | nobody          | A        |   99 |   99 | student | /                  | /sbin/nologin |
| 14 | systemd-network | A        |  192 |  192 | student | /                  | /sbin/nologin |
| 15 | dbus            | A        |   81 |   81 | student | /                  | /sbin/nologin |
| 16 | polkitd         | A        |  999 |  998 | student | /                  | /sbin/nologin |
| 17 | sshd            | A        |   74 |   74 | student | /var/empty/sshd    | /sbin/nologin |
| 18 | postfix         | A        |   89 |   89 | student | /var/spool/postfix | /sbin/nologin |
| 19 | chrony          | A        |  998 |  996 | student | /var/lib/chrony    | /sbin/nologin |
| 20 | mysql           | A        | 1000 | 1000 | student | /home/mysql        | /sbin/nologin |
+----+-----------------+----------+------+------+---------+--------------------+---------------+
11 rows in set (0.00 sec)-- || 也可以表示或
root@mysqldb 15:11:  [(none)]> select name , uid  from  db3.user -> where  name = "sync" ||  name = "apache"  ||  uid=3;
+------+------+
| name | uid  |
+------+------+
| adm  |    3 |
| sync |    5 |
+------+------+
2 rows in set (0.00 sec)-- && 表示and
root@mysqldb 15:11:  [(none)]> select  * from  db3.user where id >= 10 &&  id <= 20 ;
+----+-----------------+----------+------+------+---------+--------------------+---------------+
| id | name            | password | uid  | gid  | comment | homedir            | shell         |
+----+-----------------+----------+------+------+---------+--------------------+---------------+
| 10 | operator        | A        |   11 |    0 | student | /root              | /sbin/nologin |
| 11 | games           | A        |   12 |  100 | student | /usr/games         | /sbin/nologin |
| 12 | ftp             | A        |   14 |   50 | student | /var/ftp           | /sbin/nologin |
| 13 | nobody          | A        |   99 |   99 | student | /                  | /sbin/nologin |
| 14 | systemd-network | A        |  192 |  192 | student | /                  | /sbin/nologin |
| 15 | dbus            | A        |   81 |   81 | student | /                  | /sbin/nologin |
| 16 | polkitd         | A        |  999 |  998 | student | /                  | /sbin/nologin |
| 17 | sshd            | A        |   74 |   74 | student | /var/empty/sshd    | /sbin/nologin |
| 18 | postfix         | A        |   89 |   89 | student | /var/spool/postfix | /sbin/nologin |
| 19 | chrony          | A        |  998 |  996 | student | /var/lib/chrony    | /sbin/nologin |
| 20 | mysql           | A        | 1000 | 1000 | student | /home/mysql        | /sbin/nologin |
+----+-----------------+----------+------+------+---------+--------------------+---------------+
11 rows in set (0.00 sec)

-- 查询的优先级
root@mysqldb 15:15:  [(none)]> select  name , uid  from  db3.user  where   uid = 1 or  uid = 0  and  name = "root" ;
+------+------+
| name | uid  |
+------+------+
| root |    0 |
| bin  |    1 |
+------+------+
2 rows in set (0.00 sec)root@mysqldb 15:15:  [(none)]> 
root@mysqldb 15:16:  [(none)]> 
root@mysqldb 15:16:  [(none)]> select  name , uid  from  db3.user   where  ( uid = 1 or  uid = 0 )  and  name = "root";
+------+------+
| name | uid  |
+------+------+
| root |    0 |
+------+------+
1 row in set (0.00 sec)

通配符 _ %

root@mysqldb 15:16:  [(none)]> select  name  from  db3.user where  name like  '___';
+------+
| name |
+------+
| bin  |
| adm  |
| ftp  |
+------+
3 rows in set (0.00 sec)root@mysqldb 15:29:  [(none)]> select  name  from  db3.user where  name like  '____';
+------+
| name |
+------+
| root |
| sync |
| halt |
| mail |
| dbus |
| sshd |
+------+
6 rows in set (0.00 sec)root@mysqldb 15:29:  [(none)]> select  name  from  db3.user where  name like  '%a%';
+----------+
| name     |
+----------+
| daemon   |
| adm      |
| halt     |
| mail     |
| operator |
| games    |
+----------+
6 rows in set (0.00 sec)root@mysqldb 15:29:  [(none)]> select  name  from  db3.user where  name like  'a%';
+------+
| name |
+------+
| adm  |
+------+
1 row in set (0.00 sec)root@mysqldb 15:29:  [(none)]> select  name  from  db3.user where  name like  '___%___';
+-----------------+
| name            |
+-----------------+
| daemon          |
| shutdown        |
| operator        |
| nobody          |
| systemd-network |
| polkitd         |
| postfix         |
| chrony          |
+-----------------+
8 rows in set (0.00 sec)

正则匹配 regexp

where 字段名 regexp ‘正则表达式’
^ $ . * [] |

^：匹配字符串的开头
$：匹配字符串的结尾
.：匹配任意字符（除了换行符）
*：匹配前面的字符零次或多次
[]：匹配方括号中的任意一个字符
|：表示“或”的关系，匹配多个选项中的一个

root@mysqldb 15:29:  [(none)]> insert into  db3.user(name) values ("yaya9"),("y7aya"),("ya6ya"),("3yaya");
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0root@mysqldb 15:31:  [(none)]> select  name  from  db3.user where name regexp '[0-9]';
+-------+
| name  |
+-------+
| yaya9 |
| y7aya |
| ya6ya |
| 3yaya |
+-------+
4 rows in set (0.00 sec)root@mysqldb 15:31:  [(none)]> select  name  from  db3.user where  name regexp  '^[0-9]';
+-------+
| name  |
+-------+
| 3yaya |
+-------+
1 row in set (0.00 sec)root@mysqldb 15:31:  [(none)]> select name , uid  from   db3.user where  uid  regexp '..' ;
+-----------------+------+
| name            | uid  |
+-----------------+------+
| operator        |   11 |
| games           |   12 |
| ftp             |   14 |
| nobody          |   99 |
| systemd-network |  192 |
| dbus            |   81 |
| polkitd         |  999 |
| sshd            |   74 |
| postfix         |   89 |
| chrony          |  998 |
| mysql           | 1000 |
+-----------------+------+
11 rows in set (0.00 sec)root@mysqldb 15:31:  [(none)]> select name , uid  from   db3.user where  uid  regexp '^..$' ;
+----------+------+
| name     | uid  |
+----------+------+
| operator |   11 |
| games    |   12 |
| ftp      |   14 |
| nobody   |   99 |
| dbus     |   81 |
| sshd     |   74 |
| postfix  |   89 |
+----------+------+
7 rows in set (0.00 sec)root@mysqldb 15:31:  [(none)]> select name , uid  from   db3.user where  name  regexp '^a.*t$' ;
Empty set (0.00 sec)root@mysqldb 15:32:  [(none)]> select name , uid  from   db3.user where  name  regexp '^r|t$' ;
+------+------+
| name | uid  |
+------+------+
| root |    0 |
| halt |    7 |
+------+------+
2 rows in set (0.00 sec)

±*/%

root@mysqldb 15:36:  [(none)]> update db3.user set uid=uid+1   where id <= 5;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0root@mysqldb 15:38:  [(none)]> update db3.user set uid=uid-1   where id <= 5;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0root@mysqldb 15:38:  [(none)]> alter table db3.user add age tinyint unsigned  not null default 19 after name ;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0root@mysqldb 15:38:  [(none)]> select name  ,  2020  - age  s_year  from  db3.user where name = "root" ;
+------+--------+
| name | s_year |
+------+--------+
| root |   2001 |
+------+--------+
1 row in set (0.00 sec)root@mysqldb 15:38:  [(none)]> select name  ,  2020  - age  s_year  from  db3.user ;
+-----------------+--------+
| name            | s_year |
+-----------------+--------+
| root            |   2001 |
| bin             |   2001 |
| daemon          |   2001 |
| adm             |   2001 |
| lp              |   2001 |
| sync            |   2001 |
| shutdown        |   2001 |
| halt            |   2001 |
| mail            |   2001 |
| operator        |   2001 |
| games           |   2001 |
| ftp             |   2001 |
| nobody          |   2001 |
| systemd-network |   2001 |
| dbus            |   2001 |
| polkitd         |   2001 |
| sshd            |   2001 |
| postfix         |   2001 |
| chrony          |   2001 |
| mysql           |   2001 |
| yaya9           |   2001 |
| y7aya           |   2001 |
| ya6ya           |   2001 |
| 3yaya           |   2001 |
+-----------------+--------+
24 rows in set (0.00 sec)root@mysqldb 15:38:  [(none)]> select name,uid,gid ,(uid+gid)/2  pjf   from db3.user  where  name="games";
+-------+------+------+---------+
| name  | uid  | gid  | pjf     |
+-------+------+------+---------+
| games |   12 |  100 | 56.0000 |
+-------+------+------+---------+
1 row in set (0.00 sec)root@mysqldb 15:39:  [(none)]> select * from db3.user where id % 2 = 0 ;
+----+-----------------+-----+----------+------+------+---------+--------------------+---------------+
| id | name            | age | password | uid  | gid  | comment | homedir            | shell         |
+----+-----------------+-----+----------+------+------+---------+--------------------+---------------+
|  2 | bin             |  19 | A        |    1 |    1 | student | /bin               | /sbin/nologin |
|  4 | adm             |  19 | A        |    3 |    4 | student | /var/adm           | /sbin/nologin |
|  6 | sync            |  19 | A        |    5 |    0 | student | /sbin              | /bin/sync     |
|  8 | halt            |  19 | A        |    7 |    0 | student | /sbin              | /sbin/halt    |
| 10 | operator        |  19 | A        |   11 |    0 | student | /root              | /sbin/nologin |
| 12 | ftp             |  19 | A        |   14 |   50 | student | /var/ftp           | /sbin/nologin |
| 14 | systemd-network |  19 | A        |  192 |  192 | student | /                  | /sbin/nologin |
| 16 | polkitd         |  19 | A        |  999 |  998 | student | /                  | /sbin/nologin |
| 18 | postfix         |  19 | A        |   89 |   89 | student | /var/spool/postfix | /sbin/nologin |
| 20 | mysql           |  19 | A        | 1000 | 1000 | student | /home/mysql        | /sbin/nologin |
| 26 | y7aya           |  19 | NULL     | NULL | NULL | NULL    | NULL               | NULL          |
| 28 | 3yaya           |  19 | NULL     | NULL | NULL | NULL    | NULL               | NULL          |
+----+-----------------+-----+----------+------+------+---------+--------------------+---------------+
12 rows in set (0.00 sec)root@mysqldb 15:39:  [(none)]> select name , uid  from  db3.user where  uid % 2  !=  0 ;
+----------+------+
| name     | uid  |
+----------+------+
| bin      |    1 |
| adm      |    3 |
| sync     |    5 |
| halt     |    7 |
| operator |   11 |
| nobody   |   99 |
| dbus     |   81 |
| polkitd  |  999 |
| postfix  |   89 |
+----------+------+
9 rows in set (0.00 sec)

聚集函数

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

root@mysqldb 15:39:  [(none)]> select max(uid) from db3.user ;
+----------+
| max(uid) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)root@mysqldb 15:41:  [(none)]> select max(uid) from db3.user  where id <= 10;
+----------+
| max(uid) |
+----------+
|       11 |
+----------+
1 row in set (0.00 sec)root@mysqldb 15:41:  [(none)]> select min(uid) from db3.user  where id <= 10;
+----------+
| min(uid) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)root@mysqldb 15:41:  [(none)]> select avg(uid) from db3.user;
+----------+
| avg(uid) |
+----------+
| 180.2500 |
+----------+
1 row in set (0.00 sec)root@mysqldb 15:41:  [(none)]> select count(name) from  db3.user where shell!="/bin/bash";
+-------------+
| count(name) |
+-------------+
|          19 |
+-------------+
1 row in set (0.00 sec)root@mysqldb 15:41:  [(none)]> select count(*) from  db3.user;
+----------+
| count(*) |
+----------+
|       24 |
+----------+
1 row in set (0.00 sec)root@mysqldb 15:41:  [(none)]> select min(uid) , max(gid) from db3.user;
+----------+----------+
| min(uid) | max(gid) |
+----------+----------+
|        0 |     1000 |
+----------+----------+
1 row in set (0.00 sec)root@mysqldb 15:41:  [(none)]> select min(uid) zx , max(gid) zd from db3.user;
+------+------+
| zx   | zd   |
+------+------+
|    0 | 1000 |
+------+------+
1 row in set (0.00 sec)

查询结果排序 order by 字段名 asc|desc

root@mysqldb 15:41:  [(none)]> select name , uid  from  db3.user  where  uid >=10 and  uid <= 800 -> order by uid asc;
+-----------------+------+
| name            | uid  |
+-----------------+------+
| operator        |   11 |
| games           |   12 |
| ftp             |   14 |
| sshd            |   74 |
| dbus            |   81 |
| postfix         |   89 |
| nobody          |   99 |
| systemd-network |  192 |
+-----------------+------+
8 rows in set (0.00 sec)root@mysqldb 15:42:  [(none)]> select name , uid  from  db3.user  where  uid >=10 and  uid <= 800-> order by uid  desc;
+-----------------+------+
| name            | uid  |
+-----------------+------+
| systemd-network |  192 |
| nobody          |   99 |
| postfix         |   89 |
| dbus            |   81 |
| sshd            |   74 |
| ftp             |   14 |
| games           |   12 |
| operator        |   11 |
+-----------------+------+
8 rows in set (0.00 sec)

查询结果分组 group by 字段名

root@mysqldb 15:43:  [(none)]> select shell  from  db3.user where   uid >= 10  group by shell;
+---------------+
| shell         |
+---------------+
| /sbin/nologin |
+---------------+
1 row in set (0.00 sec)root@mysqldb 15:43:  [(none)]> select shell from db3.user group by shell;
+----------------+
| shell          |
+----------------+
| NULL           |
| /bin/bash      |
| /bin/sync      |
| /sbin/halt     |
| /sbin/nologin  |
| /sbin/shutdown |
+----------------+
6 rows in set (0.00 sec)

distinct 去重显示

root@mysqldb 15:43:  [(none)]> select shell from db3.user;
+----------------+
| shell          |
+----------------+
| /bin/bash      |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /bin/sync      |
| /sbin/shutdown |
| /sbin/halt     |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| /sbin/nologin  |
| NULL           |
| NULL           |
| NULL           |
| NULL           |
+----------------+
24 rows in set (0.00 sec)root@mysqldb 15:44:  [(none)]> select  distinct shell from db3.user ;
+----------------+
| shell          |
+----------------+
| /bin/bash      |
| /sbin/nologin  |
| /bin/sync      |
| /sbin/shutdown |
| /sbin/halt     |
| NULL           |
+----------------+
6 rows in set (0.00 sec)root@mysqldb 15:44:  [(none)]> select  distinct gid from db3.user;
+------+
| gid  |
+------+
|    0 |
|    1 |
|    2 |
|    4 |
|    7 |
|   12 |
|  100 |
|   50 |
|   99 |
|  192 |
|   81 |
|  998 |
|   74 |
|   89 |
|  996 |
| 1000 |
| NULL |
+------+
17 rows in set (0.00 sec)

过滤查询结果 having 条件

root@mysqldb 15:44:  [(none)]> select name from  db3.user where shell != "/bin/bash"  having  name in ("sync","games");
+-------+
| name  |
+-------+
| sync  |
| games |
+-------+
2 rows in set (0.00 sec)root@mysqldb 15:45:  [(none)]> select  id , name from  db3.user where id >= 10   having  name is null ;
Empty set (0.00 sec)root@mysqldb 15:45:  [(none)]> select id , name from db3.user where id >= 10 and name is null ;
Empty set (0.00 sec)

限制查询结果显示行数 limit

root@mysqldb 15:46:  [(none)]> select name , uid  , gid  from db3.user where  shell !=  "/bin/bash" limit 1;
+------+------+------+
| name | uid  | gid  |
+------+------+------+
| bin  |    1 |    1 |
+------+------+------+
1 row in set (0.00 sec)root@mysqldb 15:46:  [(none)]> select name , uid  , gid  from db3.user where  shell !=  "/bin/bash" limit 3;
+--------+------+------+
| name   | uid  | gid  |
+--------+------+------+
| bin    |    1 |    1 |
| daemon |    2 |    2 |
| adm    |    3 |    4 |
+--------+------+------+
3 rows in set (0.00 sec)

SQL自学，mysql从入门到精通 --- 第 15天，数据导入、导出

数据的导入、导出 -- 查看当前设置的目录路径，限制从数据库服务器读取和写入文件的操作只能在指定的目录中进行,在安全性和文件操作限制方面具有重要意义。rootmysqldb 14:19: [(none)]> SHOW VARIABLES LIKE "secure_file_priv"; -----------------…...

编程日记 2026/2/23 0:47:19

android skia渲染介绍

Android AOSP 的渲染系统主要使用 Skia 图形库。Skia 是一个开源的 2D 图形库，它被广泛应用于 Android 的图形渲染中，负责绘制 UI 元素、文本、图像以及其他 2D 图形内容。以下是 Android AOSP 中 Skia 的作用和它在渲染系统中的位置： 1. 什…...

编程日记 2025/11/5 21:27:11

【网络安全】服务器安装Docker及拉取镜像教程

文章目录 1. 安装 Docker2. 拉取镜像3. 运行 Ubuntu 容器4. 执行相关操作5. 退出并停止容器1. 安装 Docker # 更新软件包索引 sudo apt update# 安装必要的依赖 sudo apt install -y ca-certificates curl gnupg...

编程日记 2026/4/11 22:27:28

Day87：游戏事件绑定

在游戏开发中，事件绑定是指通过监听和处理用户的输入或其他事件（如鼠标点击、键盘按键、碰撞等），来控制游戏中的行为和流程。事件绑定在游戏中扮演着至关重要的角色，它能够让游戏具备互动性和实时反馈。今天，我们将学习如何在 Python 中使用 Pygame 进行游戏事件绑定，…...

编程日记 2026/3/3 0:52:17

elementplus 使用日期时间选择器，设置可选范围为前后大于2年且只能选择历史时间不能大于当前时间点

需求：时间选择器可选的时间范围进行限制，-2年<a<2年且a<new Date().getTime()核心：这里需要注意plus版没有picker-options换成disabled-date属性了，使用了visible-change和calendar-change属性逻辑：另设一个参…...

编程日记 2026/4/11 21:25:13

将 AMD Zynq™ RFSoC 扩展到毫米波领域

目录将 AMD Zynq™ RFSoC 扩展到毫米波领域Avnet XRF RFSoC 系统级模块适用于 MATLAB 的 Avnet RFSoC Explorer 工具箱5G mmWave PAAM 开发平台突破性的宽带毫米波波束成形特征：OTBF103 Mathworks Simulink 模型优化毫米波应用中的射频信号路径用于宽带毫米波上/下…...

编程日记 2026/4/11 22:41:46

Redis企业开发实战(五)——点评项目之分布式锁Redission与秒杀优化

目录一、Redisson (一)Redisson基本介绍 (二)Redisson入门 1.引入依赖 2.配置Redisson客户端 3.使用Redission的分布式锁 4.tryLock参数解析 4.1tryLock() 4.2tryLock(long waitTime, TimeUnit unit) 4.3tryLock(long waitTime, long leaseTime, TimeUnit unit) 4…...

编程日记 2026/4/13 9:26:58

IDEA安装离线插件（目前提供了MavenHelper安装包）

目录 1、离线安装方式2、Maven Helper 1、离线安装方式首先访问 IDEA插件网站下载离线插件安装包，操作如下： 然后打开IDEA的Settings配置，点击Plugins，点击右侧设置按钮（齿轮），选择Install P…...

编程日记 2026/4/14 18:49:09

LabVIEW 开发航天项目软件

在航天项目软件开发中，LabVIEW 凭借其图形化编程优势被广泛应用。然而，航天项目的高可靠性、高精度及复杂环境适应性要求，使得在使用 LabVIEW 开发时，有诸多关键要点需要特别关注。本文将详细分析在开发航天项目软件时需要重点注意…...

编程日记 2026/4/27 11:21:58

互联网大厂中面试的高频计算机网络问题及详解

前言哈喽各位小伙伴们，本期小梁给大家带来了互联网大厂中计算机网络部分的高频面试题，本文会以通俗易懂的语言以及图解形式描述，希望能给大家的面试带来一点帮助，祝大家offer拿到手软！！！话不多说，我们立刻进入本期正题！一、计算机网络基础部分 1 先来说说计算机网…...

编程日记 2026/4/10 23:13:03

如何定义“破坏环境”

当我们谈论破坏环境时，通常会从人类活动对自然生态造成负面影响的角度来定义。例如，大规模的森林砍伐、工业污染排放、温室气体增加等，都是典型的破坏环境的行为。我们常常看到这些行为导致了生态系统的破坏、物种灭绝、气候变化等问题&#…...

编程日记 2026/2/21 0:04:49

WPS接入DeepSeek模型

1.wps 下载安装 WPS-支持多人在线协作编辑Word、Excel和PPT文档_WPS官方网站 （最好是安装最新的wps） 2.offieceAi工具下载安装软件下载 | OfficeAI助手下载后安装下载下来的两个工具。安装路径可以自行修改 3.打开WPS,点击文件-》选项-》信任中心勾…...

编程日记 2026/4/18 15:49:05

自然语言处理NLP_[1]-NLP入门

文章目录 1.自然语言处理入门1. 什么是自然语言处理2.自然语言处理的发展简史3 自然语言处理的应用场景1. **机器翻译**2. **文本分类**3. **情感分析**4. **问答系统**5. **文本生成**6. **信息抽取**7. **语音识别与合成**8. **文本摘要**9. **搜索引擎优化**10. **聊天机器人…...

编程日记 2026/4/26 19:44:36

详解在Pytest中忽略测试目录的三种方法

关注开源优测不迷路大数据测试过程、策略及挑战测试框架原理，构建成功的基石在自动化测试工作之前，你应该知道的10条建议在自动化测试中，重要的不是工具你是否曾因无关或过时的代码导致测试失败？ 这可能会增加调试和故障排除…...

编程日记 2026/2/19 15:22:16

IDEA中列举的是否是SpringBoot的依赖项的全部？在哪里能查到所有依赖项，如何开发自己的依赖项让别人使用

在 IntelliJ IDEA 中列举的依赖项并不一定是 Spring Boot 项目的全部依赖项。IDEA 通常只显示你在 pom.xml（Maven）或 build.gradle（Gradle）中显式声明的依赖项，而这些依赖项本身可能还会引入其他传递性依赖。 1. 如何…...

编程日记 2026/3/22 19:47:03

ECG分析0210

指标计算方法 1. HR (心率，Heart Rate)： 心率是每分钟心跳的次数。它通常通过计算RR间期（即两次R波之间的时间间隔）来获得。计算方法： 首先，检测到R波的位置（例如通过find_peaks函数检测&a…...

编程日记 2026/3/10 2:06:26

计算机毕业设计Python+Spark知识图谱医生推荐系统医生门诊预测系统医生数据分析医生可视化医疗数据分析医生爬虫大数据毕业设计机器学习

温馨提示：文末有 CSDN 平台官方提供的学长联系方式的名片！ 温馨提示：文末有 CSDN 平台官方提供的学长联系方式的名片！ 温馨提示：文末有 CSDN 平台官方提供的学长联系方式的名片！ 作者简介：Java领…...

编程日记 2026/4/11 21:23:17

JavaScript：CPU缓存预取以及确定数据下直接更改数组length的好处

CPU缓存预取以及确定数据下直接更改数组length的好处 1. CPU 缓存预取（Cache Preloading）：CPU 缓存预取：为什么反向填充栈能利用缓存预取： 2. 为什么可以直接改变数组的 length：数组的动态长度：…...

编程日记 2026/4/27 6:35:04

Selenium常用自动化函数

博主主页: 码农派大星. 数据结构专栏:Java数据结构数据库专栏:数据库 JavaEE专栏:JavaEE 软件测试专栏:软件测试关注博主带你了解更多知识目录 1.元素的定位 1.1 定位步骤 1,要想定位,就先打开开发者工具 2,先点击左上角图标 1.2 cssSelector 1.3 xpath 2.操作测…...

编程日记 2026/4/25 8:28:52

【故障排除】ls: command not found 终端命令失效的解决办法

【TroubleShooting】ls: command not found 终端命令失效的解决办法 A Solution to Solve “Command not found” of Terminal on Mac 一直在使用心爱的MacBook Pro的Terminal，并且为她定制了不同的Profile。这样，看起来她可以在不同季节&#xff0c…...

编程日记 2026/4/11 21:25:01

OpenStack-Train版-Allinone自动化部署脚本

一、环境准备操作系统：CentOS 7 或以上版本建议配置： CPU：8 核或以上内存：16 GB 或以上磁盘：500 GB 或以上网络配置： 确保虚拟机已配置静态 IP 地址确保虚拟机可以正常访问外部网络二、自动…...

编程日记 2025/5/28 1:38:30

12.翻转、对称二叉树，二叉树的深度

反转二叉树递归写法很简单 class Solution { public:TreeNode* invertTree(TreeNode* root) {if(rootnullptr)return root;TreeNode* tmp;tmproot->left;root->leftroot->right;root->righttmp;invertTree(root->left);invertTree(root->right);return …...

编程日记 2026/4/18 19:58:03

新电脑配置安装下载

1、谷歌浏览器地址https://www.google.cn/chrome/ 下载安装即可。 2、nvm下载下载地址：地址https://nvm.uihtm.com/#google_vignette nvm install 相对应的node版本 // 安装 nvm list 可以查看已下载的node版本 // 查看 nvm use 相对应的node版本号 // 使用 nv…...

编程日记 2025/11/17 8:03:37

数字孪生智慧停车管理可视化平台

采用图扑可视化技术搭建智慧停车管理平台，实现了全面的数据整合与实时监控，提升了停车场运营效率和用户体验。通过 HT 可视化界面，管理者能够实时观察和分析停车位使用情况，进行精准调度与优化决策。...

编程日记 2026/4/11 21:23:47

win10 llamafactory模型微调相关②

微调使用微调神器LLaMA-Factory轻松改变大语言模型的自我认知_llamafactory 自我认知-CSDN博客【大模型微调】使用Llama Factory实现中文llama3微调_哔哩哔哩_bilibili 样本数据集 （数据集管理脚本处需更改，见报错解决参考1） 自我认知微…...

编程日记 2026/4/27 1:11:36

车载测试工具 --- CANoe VH6501 进行Not Acknowledge (NAck) 测试

我是穿拖鞋的汉子，魔都中坚持长期主义的汽车电子工程师。老规矩，分享一段喜欢的文字，避免自己成为高知识低文化的工程师：简单，单纯，喜欢独处，独来独往，不易合同频过着接地气的生活，除了生存温饱问题之外，没有什么过多的欲望，表面看起来很高冷，内心热情，如果你身…...

编程日记 2026/4/11 21:23:23

Mysql中存储引擎各种介绍以及应用场景、优缺点

概述 MySQL 提供了多种存储引擎，每种引擎有不同的特点和适用场景。以下是几种常见的 MySQL 存储引擎的详细介绍，包括它们的底层工作原理、优缺点，以及为什么 MySQL 默认选择某种引擎。 1. InnoDB 底层工作原理： 事务支持&#…...

编程日记 2026/3/31 22:21:53

使用 AlexNet 实现图片分类 | PyTorch 深度学习实战

前一篇文章，CNN 卷积神经网络处理图片任务 | PyTorch 深度学习实战本系列文章 GitHub Repo: https://github.com/hailiang-wang/pytorch-get-started 本篇文章内容来自于强化学习必修课：引领人工智能新时代【梗直哥瞿炜】使用 AlexNet 实现图片分类…...

编程日记 2026/4/27 17:21:54

Linux系统引导与服务管理

目录一、Linux引导过程 1、引导过程概述 1.1、BIOS开机自检 1.2、MBR读取 1.3、加载引导加载程序（GRUB） 1.4、内核加载 1.5、初始化进程（init） 二、服务 2.1、服务类型 2.2、服务管理工具三、运行级别四、systemd …...

编程日记 2026/4/11 21:23:58

【Hadoop】大数据权限管理工具Ranger2.1.0编译

目录编辑一、下载 ranger源码并编译二、报错信息报错1 报错2 报错3 报错4 一、下载 ranger源码并编译 ranger官网 https://ranger.apache.org/download.html 由于Ranger不提供二进制安装包，故需要maven编译。安装其它依赖： yum install gcc …...

编程日记 2026/4/11 21:23:24

1.数据导入

2.数据导出

3.管理表记录

相关文章：