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

已于 2023-09-12 15:59:14 修改
阅读量69 收藏
点赞数
分类专栏： SQL 文章标签： sql mysql android
于 2023-09-12 15:48:40 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_46153337/article/details/132834137
版权

数据的导入、导出

-- 查看当前设置的目录路径，限制从数据库服务器读取和写入文件的操作只能在指定的目录中进行,在安全性和文件操作限制方面具有重要意义。

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: 0

root@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: 0

root@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: 0



root@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: 0

root@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: 0

root@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: 0

root@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: 0

root@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: 0

root@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)
Mr.L-OAM
关注
专栏目录
shell mysql 批量读写数据库_shell批量导入mysql数据库 - mysql数据库栏目 - 自学
weixin_42349434的博客
02-11 218
因为项目协同开发, 同事发了一个项目的sql文件过来, 打开一看是个目录, 里面有上百个数据库.每个数据库作为一个单独的文件.每个sql文件里面都有CREATE DATABASE IF NOT EXISTS `XXXX`USE `XXXX`;他可以直接用mysql_workbench 6以上的版本直接导入文件夹的方式来导入多个sql文件. 但是在我的服务器上不可能为了批量导这个sql单独装个GUI...
mysql自学文档--教你怎么自学mysql
04-23
6. **数据备份/导入导出**：掌握使用mysqldump进行备份，以及使用LOAD DATA INFILE和SELECT INTO OUTFILE进行数据导入导出。 7. **基本数据类型**：熟悉MySQL支持的数据类型，如INT、VARCHAR、DATE等，以及它们的...
SQL自学，mysql从入门到精通 --- 第 1 天，系统环境搭建，mysql部署
Mr.L-OAM的博客
09-04 425
SQL自学通，SQL的增删改查以及高级应用，从入门到熟练到精通，SQL自学，mysql从入门到精通 --- 第 1 天，系统环境搭建，mysql部署
【粉丝福利社】 MySQL从入门到精通（第3版）（文末送书-进行中）
时光隧道
05-29 4万+
"MySQL从入门到精通"是指从初学者开始学习MySQL数据库，逐步掌握其基本概念、语法和功能，直到能够熟练运用MySQL进行数据库设计、管理、优化和应用开发的过程。入门阶段：初学者了解MySQL数据库的基本概念、特点和用途，学习安装MySQL服务器和客户端工具，掌握基本的数据库操作，包括创建数据库、创建表、插入数据、查询数据等。基础语法学习：学习MySQL数据库的基本语法，包括SQL语句的编写、数据类型的使用、约束条件的设置、索引的创建等，以及常用的SQL函数和操作符的应用。进阶技能掌握。
mysql自学第17天 --- mysql主从同步模式服务部署
Mr.L-OAM的博客
09-19 121
mysql自学第17天 --- mysql主从同步模式服务部署 部署mysql主从同步模式服务器，实现在主服务器操作表，改变数据，自动同步到从服务器。提高mysql数据库的可靠性。
牛逼！Java 从入门到精通，超全汇总版
热门推荐
程序员cxuan的个人主页
05-06 7万+
文章目录Java 基础Head First JavaJava 核心技术卷一Java 编程思想设计模式Head First 设计模式图解设计模式设计模式重学 Java 设计模式Java 进阶Java 并发编程实战Java 并发编程艺术Java 并发编程之美图解Java多线程设计模式JVM深入理解 Java 虚拟机Java 虚拟机规范HotSpot 实战自己动手写 Java 虚拟机MySQLMySQL 基础教程SQL 基础教程深入浅出 MySQLMySQL 必知必会SQL 必知必会高性能 MySQLMySQL
mysql导入导出命令exp_Oracle--数据的导入与导出(exp/imp命令详解3)
weixin_30630377的博客
01-19 1034
Oracle数据导入导出imp/exp就相当于oracle数据还原与备份。exp命令可以把数据从远程数据库服务器导出到本地的dmp文件，imp命令可以把dmp文件从本地导入到远处的数据库服务器中。 利用这个功能可以构建两个相同的数据库，一个用来测试，一个用来正式使用。oracle Exp命令详解1 将数据库TEST完全导出,用户名system 密码manager 导出到D:daochu.dmp中4...
12-TDengine数据迁移：导入与导出
2401_84048895的博客
04-22 653
对于面试，一定要有良好的心态，这位小伙伴面试美团的时候没有被前面阿里的面试影响到，发挥也很正常，也就能顺利拿下美团的offer。小编还整理了大厂java程序员面试涉及到的绝大部分面试题及答案，希望能帮助到大家，也祝愿大家都能够升职加薪！《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！[外链图片转存中…(img-0GFAMHkD-1713787841011)]
MySQL自学（从入门到高手）
Dawn_Newbie的博客
07-13 359
基于武沛齐老师的课程以及提供的笔记，归纳并整理的MySQL学习笔记。其中包括作者的个人理解和实验结果
初学MySQL必会SQL语句-操作数据库
2401_83916326的博客
04-19 840
1.3.查看数据库zabbix/*!/ /!以上信息为：创建zabbix数据库使用的字符集为utf8mb4，校对规则为utf8mb4_0900_ai_ci，DEFAULT ENCRYPTION='N’表示为未使用加密技术。1.4.修改数据库步骤如下：1.创建一个test_old测试库，在库中创建table_test测试表2.创建test_new数据库3.将test_old中table_test表重命名到test_new数据库下4.查看test_new下数据表。
SQLserver21天自学通SQL21天自学通
02-17
- **从外部数据源中导入和导出数据**：介绍如何将数据从其他数据库或文件格式导入到SQL Server中，以及如何将数据导出到其他格式。 #### 第九天：创建和操作表 - **CREATEDATABASE语句**：用于创建新的数据库。 - ...
sqlalchemy JSON 字段写入时中文序列化问题
llc的博客
09-12 311
_table_args__ = ({"comment": "表名称"})...extra = Column(JSON, comment="其他属性")...
SQL(结构性查询语句）
2301_78693337的博客
09-11 451
以上就是今天要讲的内容，本文介绍了基础的SQL语法使用，而sql还提供了大量能使我们快速便捷地处理数据的函数和方法等着我们探索。
大数据-132 - Flink SQL 基本介绍 与 HelloWorld案例
永远好奇，无限进步！
09-12 2290
Flink SQL 是 Apache Flink 提供的一种高层次的查询语言接口，它基于 SQL 标准，为开发者提供了处理流式数据和批处理数据的能力。Flink SQL 允许用户使用标准 SQL 查询语言在数据流和数据表上执行复杂的操作，适用于多种应用场景，如实时分析、数据流处理、机器学习等。Flink SQL 的一大特点是流处理和批处理的统一性。通过同一套 SQL 语法，用户可以同时处理静态数据（批处理）和动态数据（流处理）。这使得应用程序的开发更加简化，因为可以用相同的逻辑编写实时流数据处理和历史数据的
sqlserver常用的sql命令
2301_76664379的博客
09-11 701
备注：D 表示全备份，i 表示差异备份，L 表示日志备份。
RocketMQ出现The broker does not support consumer to filter message by SQL92
山不见我，我自见山
09-12 250
然后通过rocketmq-console来查看，集群中的这个属性是否为true，为true就可以了。在使用RocketMQ使用SQL过滤消息的时候，出现下面错误。原因是我们的配置文件没有开启SQL过滤功能，我们需要在。接着我们重启namesrv与broker服务就解决问题。配置文件中添加下面命令。
mysql DBA常用的sql
最新发布
weixin_44550507的博客
09-12 679
DBA常用sql，方便性能优化
SQL导入导出详细教程：从入门到精通
_sql文档导入导出精通详细_ 本文档详细介绍了SQL文档的导入和导出操作，涵盖了从基本概念到实践操作的所有步骤。通过本文档，您将学习如何使用MySQL数据库软件环境，安装ODBC驱动程序，Navicat Premium数据库管理...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Mr.L-OAM

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值