MySQL数据库基础(三)数据的导入导出及表记录管理

一、数据导入导出

1.1 搜索路径 查看 修改

查看搜索路径

mysql> show variables like "secure_file_priv";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)

[root@mysql ~]# ls -ld /var/lib/mysql-files/
drwxr-x--- 2 mysql mysql 6 11月 24 14:07 /var/lib/mysql-files/

修改搜索路径

[root@mysql ~]# mkdir /myload
[root@mysql ~]# chown mysql /myload/

[root@mysql ~]# vim /etc/my.cnf
[mysqld]
secure_file_priv="/myload"      //在[mysqld]下面追加配置
[root@mysql ~]# systemctl restart mysqld

mysql> show variables like "secure_file_priv";
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| secure_file_priv | /myload/ |
+------------------+----------+

1.2 数据导入导出

命令格式
数据导入步骤
-默认只有root用户有数据导入权限
-建表
-导入数据

命令格式
mysql> load data infile “目录名/文件名"
into table 库名.表名
fields terminated by “分隔符”
lines terminated by "\n";

注意事项
具体如下︰
-字段分隔符要与文件一致
-表字段类型和字段个数要与文件匹配
-导入数据时指定文件的绝对路径

数据的导出
命令格式
·格式1
mysql> select 命令 into outfile “目录名/文件名”

·格式2
mysql> select 命令 into outfile “目录名/文件名” fields terminated by “分隔符”;

·格式3
mysql> select命令into outfile “目录名/文件名”
fields terminated by “分隔符”
lines terminated by “\n”;

注意事项
-导出数据行数由SQL查询决定
-导出的是表记录,不包括字段名
-自动创建存储数据的文件
-存储数据文件,具有唯一性

1.3 案例1∶数据导入导出

具体要求如下:
1 )修改检索目录为/myload 。
2 )将/etc/passwd文件导入db3库的user表里,并添加行号字段。
3)将db3库user表所有记录导出,存到/myload/user.txt 文件里。

mysql> create database db3;         //创建数据库
Query OK, 1 row affected (0.01 sec)

mysql> create table db3.user(      //创建表单
    -> name char(50),
    -> password char(1),
    -> uid int,
    -> gid int,
    -> comment char(150),
    -> homedir char(50),
    -> shell char(50));
Query OK, 0 rows affected (0.09 sec)

mysql> load data infile "/myload/passwd"     
    -> into table db3.user fields terminated by ":"     //导入数据其中以 : 为分隔符
    -> lines terminatedd by "\n";                               

Query OK, 21 rows affected (0.02 sec)
Records: 21  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from db3.user;
+-----------------+----------+------+------+-----------------------------------------------------------------+-----------------+----------------+
| name            | password | uid  | gid  | comment                                                         | homedir         | shell          |
+-----------------+----------+------+------+-----------------------------------------------------------------+-----------------+----------------+
| root            | x        |    0 |    0 | root                                                            | /root           | /bin/bash      |
| bin             | x        |    1 |    1 | bin                                                             | /bin            | /sbin/nologin  |
| daemon          | x        |    2 |    2 | daemon                                                          | /sbin           | /sbin/nologin  |
| adm             | x        |    3 |    4 | adm                                                             | /var/adm        | /sbin/nologin  |
| lp              | x        |    4 |    7 | lp                                                              | /var/spool/lpd  | /sbin/nologin  |
| sync            | x        |    5 |    0 | sync                                                            | /sbin           | /bin/sync      |
| shutdown        | x        |    6 |    0 | shutdown                                                        | /sbin           | /sbin/shutdown |
| halt            | x        |    7 |    0 | halt                                                            | /sbin           | /sbin/halt     |
| mail            | x        |    8 |   12 | mail                                                            | /var/spool/mail | /sbin/nologin  |
| operator        | x        |   11 |    0 | operator                                                        | /root           | /sbin/nologin  |
| games           | x        |   12 |  100 | games                                                           | /usr/games      | /sbin/nologin  |
| ftp             | x        |   14 |   50 | FTP User                                                        | /var/ftp        | /sbin/nologin  |
| nobody          | x        |   99 |   99 | Nobody                                                          | /               | /sbin/nologin  |
| systemd-network | x        |  192 |  192 | systemd Network Management                                      | /               | /sbin/nologin  |
| dbus            | x        |   81 |   81 | System message bus                                              | /               | /sbin/nologin  |
| polkitd         | x        |  999 |  998 | User for polkitd                                                | /               | /sbin/nologin  |
| tss             | x        |   59 |   59 | Account used by the trousers package to sandbox the tcsd daemon | /dev/null       | /sbin/nologin  |
| sshd            | x        |   74 |   74 | Privilege-separated SSH                                         | /var/empty/sshd | /sbin/nologin  |
| chrony          | x        |  998 |  996 |                                                                 | /var/lib/chrony | /sbin/nologin  |
| tcpdump         | x        |   72 |   72 |                                                                 | /               | /sbin/nologin  |
| mysql           | x        |   27 |   27 | MySQL Server                                                    | /var/lib/mysql  | /bin/false     |
+-----------------+----------+------+------+-----------------------------------------------------------------+-----------------+----------------+
21 rows in set (0.00 sec)

mysql> alter table db3.user
    -> add id int primary key auto_increment first;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 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 | tss             | x        |   59 |   59 | Account used by the trousers package to sandbox the tcsd daemon | /dev/null       | /sbin/nologin  |
| 18 | sshd            | x        |   74 |   74 | Privilege-separated SSH                                         | /var/empty/sshd | /sbin/nologin  |
| 19 | chrony          | x        |  998 |  996 |                                                                 | /var/lib/chrony | /sbin/nologin  |
| 20 | tcpdump         | x        |   72 |   72 |                                                                 | /               | /sbin/nologin  |
| 21 | mysql           | x        |   27 |   27 | MySQL Server                                                    | /var/lib/mysql  | /bin/false     |
+----+-----------------+----------+------+------+-----------------------------------------------------------------+-----------------+----------------+
21 rows in set (0.00 sec)

mysql> select * from db3.user into outfile "/myload/user1.txt";
Query OK, 21 rows affected (0.00 sec)

mysql> exit
Bye
[root@mysql ~]# cat /myload/user1.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
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    tss    x    59    59    Account used by the trousers package to sandbox the tcsd daemon    /dev/null    /sbin/nologin
18    sshd    x    74    74    Privilege-separated SSH    /var/empty/sshd    /sbin/nologin
19    chrony    x    998    996        /var/lib/chrony    /sbin/nologin
20    tcpdump    x    72    72        /    /sbin/nologin
21    mysql    x    27    27    MySQL Server    /var/lib/mysql    /bin/false

二、管理表记录

2.1 增加表记录

语法格式
·格式1∶ 添加1条记录,给所有字段赋值
-insert into 表名 values (字段值列表);

·格式2∶ 添加多条记录,给所有字段赋值
-insert into 表名
values (字段值列表),(字段值列表),(字段值列表);

语法格式(续1)
·格式3︰添加1条记录,给指定字段赋值
-insert into 表名(字段名列表) values(字段值列表);

·格式4∶添加多条记录,给指定字段赋值
-insert into 表名(字段名列表)
values (字段值列表), //第1条记录
(字段值列表), //第2条记录
(字段值列表); //第3条记录

2.2 查询表记录

语法格式
格式1
-select字段1,......字段N from 库名.表名;

格式2条件查询
-select字段1,....字段N from 库名.表名 where 条件表达式;

注意事项
要求如下
-* 表示所有字段
-查看当前库表记录时库名可以省略
-字段列表决定显示列个数
-条件决定显示行的个数

2.3 更新表记录

语法格式
·格式1,批量更新
-update 库名.表名
set 字段名=值,字段名=值,字段名=值......;

·格式2,条件匹配更新
-update 库名.表名
set 字段名=值字段名=值字段名=值,.....
where 条件表达式;

注意事项
·具体如下:
-字段值要与字段类型相匹配
-对于字符类型的字段,值要用双引号括起来
-若不使用where限定条件,会更新所有记录字段值-限定条件时,只更新匹配条件的记录的字段值

2.4 删除表记录

语法格式
·格式1,条件匹配删除
-delete from 库名.表名 where条件表达式;

·格式2,删除所有记录
-delete from 库名.表名;

注意事项
具体如下:
-不加条件删除表中的所有行

2.5 案例2∶管理表记录

-具体要求如下:
-练习插入表记录
-练习更新表记录
-练习查询表记录
-练习删除表记录

mysql> create table stu_info(
    -> name char(20),
    -> gender char(10) default "boy",
    -> age int(10));
Query OK, 0 rows affected, 1 warning (0.28 sec)

mysql> desc stu_info;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| name   | char(20) | YES  |     | NULL    |       |
| gender | char(10) | YES  |     | boy     |       |
| age    | int      | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+


mysql> insert stu_info values
    -> ("Jim","girl",24),
    -> ("Tom","boy",21),
    -> ("Lily","girl",20);


mysql> select * from stu_info;
+------+--------+------+
| name | gender | age  |
+------+--------+------+
| Jim  | girl   |   24 |
| Tom  | boy    |   21 |
| Lily | girl   |   20 |
+------+--------+------+
3 rows in set (0.00 sec)

mysql> insert into stu_info(name,age) values("Jerry",27);
Query OK, 1 row affected (0.01 sec)

mysql> select * from stu_info;
+-------+--------+------+
| name  | gender | age  |
+-------+--------+------+
| Jim   | girl   |   24 |
| Tom   | boy    |   21 |
| Lily  | girl   |   20 |
| Jerry | boy    |   27 |
+-------+--------+------+
4 rows in set (0.00 sec)

mysql> update stu_info set age=20;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select * from stu_info;
+-------+--------+------+
| name  | gender | age  |
+-------+--------+------+
| Jim   | girl   |   20 |
| Tom   | boy    |   20 |
| Lily  | girl   |   20 |
| Jerry | boy    |   20 |
+-------+--------+------+
4 rows in set (0.00 sec)

mysql> update stu_info set age=25 where name="Lily";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from stu_info;
+-------+--------+------+
| name  | gender | age  |
+-------+--------+------+
| Jim   | girl   |   20 |
| Tom   | boy    |   20 |
| Lily  | girl   |   25 |
| Jerry | boy    |   20 |
+-------+--------+------+
4 rows in set (0.01 sec)

mysql> delete from stu_info where age >20;
Query OK, 1 row affected (0.01 sec)

mysql> select * from stu_info;
+-------+--------+------+
| name  | gender | age  |
+-------+--------+------+
| Jim   | girl   |   20 |
| Tom   | boy    |   20 |
| Jerry | boy    |   20 |
+-------+--------+------+
3 rows in set (0.00 sec)

mysql> delete from stu_info;
Query OK, 3 rows affected (0.01 sec)

mysql> select * from stu_info;
Empty set (0.00 sec)

三、基本匹配条件

3.1 数值比较 字符比较 逻辑匹配 范围匹配

数值比较
-字段必须是数值类型
MySQL数据库基础(三)数据的导入导出及表记录管理_第1张图片

字符比较/匹配空非空
-字段必须是字符类型
MySQL数据库基础(三)数据的导入导出及表记录管理_第2张图片

逻辑匹配
-多个判断条件时使用
MySQL数据库基础(三)数据的导入导出及表记录管理_第3张图片

范围匹配/去重显示
·匹配范围内的任意一个值即可
MySQL数据库基础(三)数据的导入导出及表记录管理_第4张图片

3.2 高级匹配条件 模糊查询 正则表达式 四则运算

模糊查询·用法
where 字段名 like "通配符'
_ 表示1个字符
% 表示0~n个字符

-示例
-列出name值 “4个字符” 的记录

mysql> select * from mysql.user 
       where user  like "_ _ _ _"\G;

正则表达式.
用法
where 字段名 regexp '正则表达式’
正则元字符 ^ $ . [] * |

示例
列出name值“以m开头或n结尾”的记录

mysql> select name from db3.user where name regexp "^m|n$";
+----------+
| name     |
+----------+
| bin      |
| daemon   |
| shutdown |
| mail     |
| mysql    |
+----------+
5 rows in set (0.00 sec)

四则运算
运算操作
-字段必须是数值类型
MySQL数据库基础(三)数据的导入导出及表记录管理_第5张图片

3.3 操作查询结果 聚集函数 查询结果排序 分组 过滤等

聚集函数
·MySQL内置数据统计函数

  • avg(字段名) //统计字段平均值
  • sum(字段名) //统计字段之和
  • min(字段名) //统计字段最小值
  • max(字段名) //统计字段最大值
  • count(字段名) //统计字段值个数
mysql> select count(name) from db3.user;

mysql> select count(name) from db3.user
    -> where shell !="/bin/bash";

查询结果排序
·用法

  • SQL查询 order by 字段名 [ asc | desc ];
  • asc 升序排序
  • desc 降序排序 //通常是数值类型的字段

通常是数值类型字段

mysql> select id,name from db3.user where name regexp "^m|n$" order by id asc;
+----+----------+
| id | name     |
+----+----------+
|  2 | bin      |
|  3 | daemon   |
|  7 | shutdown |
|  9 | mail     |
| 21 | mysql    |
+----+----------+
5 rows in set (0.00 sec)

查询结果分组
·用法

  • SQL查询 group by 字段名;

通常是字符类型字段

mysql> select shell  from db3.user group by shell;

mysql> select shell  from db3.user 
       where uid <=1000 group by shell;

查询结果过滤
.having用法

  • SQL查询 having 条件表达式;
mysql> select shell  from db3.user where uid<=1000 group by shell having shell="/bin/bash";
+-----------+
| shell     |
+-----------+
| /bin/bash |
+-----------+
1 row in set (0.00 sec)

在这里,如果用WHERE代替HAVING就会出错。

mysql> select shell  from db3.user where uid<=1000 group by shell where shell="/bin/bash";
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where shell="/bin/bash"' at line 1

mysql> select name,shell  from db3.user where uid<=1000 having name="adm";
+------+---------------+
| name | shell         |
+------+---------------+
| adm  | /sbin/nologin |
+------+---------------+
1 row in set (0.00 sec)

having语句通常与GROUP BY语句联合使用,用来过滤由GROUP BY语句返回的记录集。
having语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。
限制查询结果显示行数

注意事项 :
1、where 后不能跟聚合函数,因为where执行顺序大于聚合函数。
2、where 子句的作用是在对查询结果进行分组前,将不符合where条件的行去掉,即在分组之前过滤数据,条件中不能包含聚组函数,使用where条件显示特定的行。
3、having 子句的作用是筛选满足条件的组,即在分组之后过滤数据,条件中经常包含聚组函数,使用having 条件显示特定的组,也可以使用多个分组标准进行分组。

·用法

  • SQL查询limit 数字;1 //显示查询结果前多少条记录
  • SQL查询limit 数字1,数字2; //显示指定范围内的查询记录 其中:数字1 代表起始行(О表示第1行) 数字2代数总的显示行数
mysql> select name,shell  from db3.user limit 3;  //显示前面3行
+--------+---------------+
| name   | shell         |
+--------+---------------+
| root   | /bin/bash     |
| bin    | /sbin/nologin |
| daemon | /sbin/nologin |
+--------+---------------+
3 rows in set (0.00 sec)

mysql> select name,shell  from db3.user limit 3,4;   //显示从第3行开始 后面的4行
+----------+----------------+
| name     | shell          |
+----------+----------------+
| adm      | /sbin/nologin  |
| lp       | /sbin/nologin  |
| sync     | /bin/sync      |
| shutdown | /sbin/shutdown |
+----------+----------------+
4 rows in set (0.00 sec)

3.4 案例3:匹配条件

·具体要求如下︰
-练习数值比较的使用
-练习字符比较的使用
-练习逻辑比较的使用
-练习模糊匹配的使用
-练习正则匹配的使用
-练习查询结果分组、排序、过滤、限制显示记录行数
-练习聚集函数的使用
-练习四则运算的使用

mysql> insert stu_info values
    -> ("Jim","girl",24), 
    -> ("Tom","boy",21), 
    -> ("Lily","girl",20),
    -> ("Betty","girl",25),
    -> ("Byrom","boy",25);  
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from stu_info;
+-------+--------+------+
| name  | gender | age  |
+-------+--------+------+
| Jim   | girl   |   24 |
| Tom   | boy    |   21 |
| Lily  | girl   |   20 |
| Betty | girl   |   25 |
| Byrom | boy    |   25 |
+-------+--------+------+
5 rows in set (0.00 sec)

mysql> select * from stu_info;
+-------+--------+------+
| name  | gender | age  |
+-------+--------+------+
| Jim   | girl   |   24 |
| Tom   | boy    |   21 |
| Lily  | girl   |   20 |
| Betty | girl   |   25 |
| Byrom | boy    |   25 |
+-------+--------+------+
5 rows in set (0.00 sec)

mysql> select count(*) from stu_info;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

mysql> select avg(age),max(age),min(age) from stu_info;
+----------+----------+----------+
| avg(age) | max(age) | min(age) |
+----------+----------+----------+
|  23.0000 |       25 |       20 |
+----------+----------+----------+
1 row in set (0.00 sec)

mysql> select count(gender) from stu_info where gender="boy";
+---------------+
| count(gender) |
+---------------+
|             2 |
+---------------+
1 row in set (0.00 sec)

mysql> select * from stu_info where age=25;
+-------+--------+------+
| name  | gender | age  |
+-------+--------+------+
| Betty | girl   |   25 |
| Byrom | boy    |   25 |
+-------+--------+------+
2 rows in set (0.00 sec)

mysql> select * from stu_info where age>=21;
+-------+--------+------+
| name  | gender | age  |
+-------+--------+------+
| Jim   | girl   |   24 |
| Tom   | boy    |   21 |
| Betty | girl   |   25 |
| Byrom | boy    |   25 |
+-------+--------+------+
4 rows in set (0.00 sec)

mysql> select * from stu_info where age between 20 and 25;
+-------+--------+------+
| name  | gender | age  |
+-------+--------+------+
| Jim   | girl   |   24 |
| Tom   | boy    |   21 |
| Lily  | girl   |   20 |
| Betty | girl   |   25 |
| Byrom | boy    |   25 |
+-------+--------+------+
5 rows in set (0.00 sec)

mysql> select * from stu_info where age between 21 and 24;
+------+--------+------+
| name | gender | age  |
+------+--------+------+
| Jim  | girl   |   24 |
| Tom  | boy    |   21 |
+------+--------+------+
2 rows in set (0.00 sec)

mysql> select * from stu_info where age>22 and gender='girl';
+-------+--------+------+
| name  | gender | age  |
+-------+--------+------+
| Jim   | girl   |   24 |
| Betty | girl   |   25 |
+-------+--------+------+
2 rows in set (0.00 sec)

mysql> select * from stu_info where age<25 or gender='girl';
+-------+--------+------+
| name  | gender | age  |
+-------+--------+------+
| Jim   | girl   |   24 |
| Tom   | boy    |   21 |
| Lily  | girl   |   20 |
| Betty | girl   |   25 |
+-------+--------+------+
4 rows in set (0.00 sec)

mysql> select * from stu_info where name in ("jim","lily","minnie");
+------+--------+------+
| name | gender | age  |
+------+--------+------+
| Jim  | girl   |   24 |
| Lily | girl   |   20 |
+------+--------+------+
2 rows in set (0.00 sec)


mysql> select 123+3455;
+----------+
| 123+3455 |
+----------+
|     3578 |
+----------+
1 row in set (0.00 sec)

mysql> select 123*3455;
+----------+
| 123*3455 |
+----------+
|   424965 |
+----------+
1 row in set (0.00 sec)

mysql> select 12323/3455;
+------------+
| 12323/3455 |
+------------+
|     3.5667 |
+------------+
1 row in set (0.00 sec)

mysql> select name,age+15 from stu_info;
+-------+--------+
| name  | age+15 |
+-------+--------+
| Jim   |     39 |
| Tom   |     36 |
| Lily  |     35 |
| Betty |     40 |
| Byrom |     40 |
+-------+--------+
5 rows in set (0.00 sec)

mysql> select * from stu_info where name like 'j%';
+------+--------+------+
| name | gender | age  |
+------+--------+------+
| Jim  | girl   |   24 |
+------+--------+------+
1 row in set (0.00 sec)

mysql> select * from stu_info where name like 'b%';
+-------+--------+------+
| name  | gender | age  |
+-------+--------+------+
| Betty | girl   |   25 |
| Byrom | boy    |   25 |
+-------+--------+------+
2 rows in set (0.00 sec)

mysql> select * from stu_info where name like '___';
+------+--------+------+
| name | gender | age  |
+------+--------+------+
| Jim  | girl   |   24 |
| Tom  | boy    |   21 |
+------+--------+------+
2 rows in set (0.00 sec)

mysql> select * from stu_info where name like '____';
+------+--------+------+
| name | gender | age  |
+------+--------+------+
| Lily | girl   |   20 |
+------+--------+------+
1 row in set (0.00 sec)

mysql> select * from stu_info where name like 'b___';
Empty set (0.00 sec)

mysql> select * from stu_info where name like 'b__';
Empty set (0.00 sec)

mysql> select * from stu_info where name like 'b____';
+-------+--------+------+
| name  | gender | age  |
+-------+--------+------+
| Betty | girl   |   25 |
| Byrom | boy    |   25 |
+-------+--------+------+
2 rows in set (0.00 sec)

mysql> select * from stu_info where name regexp '^b|m$';
+-------+--------+------+
| name  | gender | age  |
+-------+--------+------+
| Jim   | girl   |   24 |
| Tom   | boy    |   21 |
| Betty | girl   |   25 |
| Byrom | boy    |   25 |
+-------+--------+------+
4 rows in set (0.00 sec)

mysql> select * from stu_info where name like 'b%' or name like '%m';
+-------+--------+------+
| name  | gender | age  |
+-------+--------+------+
| Jim   | girl   |   24 |
| Tom   | boy    |   21 |
| Betty | girl   |   25 |
| Byrom | boy    |   25 |
+-------+--------+------+
4 rows in set (0.00 sec)

mysql> select  * from stu_info order by age;
+-------+--------+------+
| name  | gender | age  |
+-------+--------+------+
| Lily  | girl   |   20 |
| Tom   | boy    |   21 |
| Jim   | girl   |   24 |
| Betty | girl   |   25 |
| Byrom | boy    |   25 |
+-------+--------+------+
5 rows in set (0.00 sec)

mysql> select  * from stu_info order by age asc;
+-------+--------+------+
| name  | gender | age  |
+-------+--------+------+
| Lily  | girl   |   20 |
| Tom   | boy    |   21 |
| Jim   | girl   |   24 |
| Betty | girl   |   25 |
| Byrom | boy    |   25 |
+-------+--------+------+
5 rows in set (0.00 sec)

mysql> select  * from stu_info order by age desc;
+-------+--------+------+
| name  | gender | age  |
+-------+--------+------+
| Betty | girl   |   25 |
| Byrom | boy    |   25 |
| Jim   | girl   |   24 |
| Tom   | boy    |   21 |
| Lily  | girl   |   20 |
+-------+--------+------+
5 rows in set (0.00 sec)

mysql> select  * from stu_info limit 3;
+------+--------+------+
| name | gender | age  |
+------+--------+------+
| Jim  | girl   |   24 |
| Tom  | boy    |   21 |
| Lily | girl   |   20 |
+------+--------+------+
3 rows in set (0.00 sec)

mysql> select age  from stu_info group by age;
+------+
| age  |
+------+
|   24 |
|   21 |
|   20 |
|   25 |
+------+
4 rows in set (0.00 sec)

mysql> select  * from stu_info order by age desc limit 3;
+-------+--------+------+
| name  | gender | age  |
+-------+--------+------+
| Betty | girl   |   25 |
| Byrom | boy    |   25 |
| Jim   | girl   |   24 |
+-------+--------+------+
3 rows in set (0.01 sec)

mysql> select gender,count(gender) from stu_info group by gender;
+--------+---------------+
| gender | count(gender) |
+--------+---------------+
| girl   |             3 |
| boy    |             2 |
+--------+---------------+
2 rows in set (0.00 sec)

mysql> select gender as "性别",count(gender) as "人数" from stu_info group by gender;
+--------+--------+
| 性别   | 人数   |
+--------+--------+
| girl   |      3 |
| boy    |      2 |
+--------+--------+
2 rows in set (0.00 sec)

你可能感兴趣的