oracle查看表修改记录 dml,DML、DQL与单表多表的增删改查（20190215下午）

極簡力

1558人浏览 · 2021-04-06 22:54:21

極簡力 · 2021-04-06 22:54:21 发布

MariaDB [db1]> select * from student;

+----+------+-----+--------+-------+

+----+------+-----+--------+-------+

| 1 | a | 20 | m | 119 |

| 2 | b | 20 | m | 120 |

| 3 | c | 20 | m | 110 |

| 4 | d | 20 | m | 135 |

| 5 | e | 20 | m | 121 |

| 6 | f | 20 | m | 100 |

+----+------+-----+--------+-------+

6 rows in set (0.00 sec)

修改其中一行内容：

MariaDB [db1]> update student set phone='1101' where name='c';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [db1]> select * from student;

+----+------+-----+--------+-------+

+----+------+-----+--------+-------+

| 1 | a | 20 | m | 119 |

| 2 | b | 20 | m | 120 |

| 3 | c | 20 | m | 1101 |

| 4 | d | 20 | m | 135 |

| 5 | e | 20 | m | 121 |

| 6 | f | 20 | m | 100 |

+----+------+-----+--------+-------+

6 rows in set (0.00 sec)

当我们加上-U登陆后，若要修改文件而没有使用where语句，系统将会爆出提醒：

[root@centos7 ~]# mysql -S /mysql/3306/socket/mysql.sock -p12345gxy -U

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 6

Server version: 5.5.60-MariaDB MariaDB Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use db1;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [db1]> select * from student;

+----+------+-----+--------+-------+

+----+------+-----+--------+-------+

| 1 | a | 20 | m | 119 |

| 2 | b | 20 | m | 120 |

| 3 | c | 20 | m | 1101 |

| 4 | d | 20 | m | 135 |

| 5 | e | 20 | m | 121 |

| 6 | f | 20 | m | 100 |

+----+------+-----+--------+-------+

6 rows in set (0.00 sec)

MariaDB [db1]> update student set phone='100' ;

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

MariaDB [db1]> (你正在使用安全登陆模式)

我们去修改客户端的配置文件:

[root@centos7 ~]# vim /etc/my.cnf.d/mysql-clients.cnf

These groups are read by MariaDB command-line tools

Use it for options that affect only one utility

[mysql]

safe-updates 加入左边的内容。

再去更新数据，不写where语句，系统是不会让更新的，如下：

Database changed

MariaDB [db1]> update student set phone='120';

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

MariaDB [db1]>

删表：MariaDB [db1]> delete from student where id=6;

Query OK, 1 row affected (0.00 sec)

MariaDB [db1]> select * from student;

+----+------+-----+--------+-------+

+----+------+-----+--------+-------+

| 1 | a | 20 | m | 119 |

| 2 | b | 20 | m | 120 |

| 3 | c | 20 | m | 1101 |

| 4 | d | 20 | m | 135 |

| 5 | e | 20 | m | 121 |

+----+------+-----+--------+-------+

5 rows in set (0.00 sec)

使用“ delete ”会记录日志，而使用“ truncate ”会更快速彻底，它是清空表，不记录日志：

给表设置别名，如下：

MariaDB [hellodb]> select stuID as 编号,name 姓名,Age as 年龄 from students;

+--------+---------------+--------+

| 编号 | 姓名 | 年龄 |

+--------+---------------+--------+

| 1 | Shi Zhongyu | 22 |

| 2 | Shi Potian | 22 |

| 3 | Xie Yanke | 53 |

| 4 | Ding Dian | 32 |

| 5 | Yu Yutong | 26 |

| 6 | Shi Qing | 46 |

| 7 | Xi Ren | 19 |

| 8 | Lin Daiyu | 17 |

| 9 | Ren Yingying | 20 |

| 10 | Yue Lingshan | 19 |

| 11 | Yuan Chengzhi | 23 |

| 12 | Wen Qingqing | 19 |

| 13 | Tian Boguang | 33 |

| 14 | Lu Wushuang | 17 |

| 15 | Duan Yu | 19 |

| 16 | Xu Zhu | 21 |

| 17 | Lin Chong | 25 |

| 18 | Hua Rong | 23 |

| 19 | Xue Baochai | 18 |

| 20 | Diao Chan | 19 |

| 21 | Huang Yueying | 22 |

| 22 | Xiao Qiao | 20 |

| 23 | Ma Chao | 23 |

| 24 | Xu Xian | 27 |

| 25 | Sun Dasheng | 100 |

+--------+---------------+--------+

25 rows in set (0.00 sec)

count函数的使用：

MariaDB [hellodb]> select count() from students;

+----------+

| count() |

+----------+

| 25 |

+----------+

1 row in set (0.00 sec)

MariaDB [hellodb]> select count() as 记录数 from students;

+-----------+

| 记录数 |

+-----------+

| 25 |

+-----------+

1 row in set (0.00 sec)

挑出特定范围的内容：

MariaDB [hellodb]> select from students where age > 30;

+-------+--------------+-----+--------+---------+-----------+

+-------+--------------+-----+--------+---------+-----------+

| 3 | Xie Yanke | 53 | M | 2 | 16 |

| 4 | Ding Dian | 32 | M | 4 | 4 |

| 6 | Shi Qing | 46 | M | 5 | NULL |

| 13 | Tian Boguang | 33 | M | 2 | NULL |

| 25 | Sun Dasheng | 100 | M | NULL | NULL |

+-------+--------------+-----+--------+---------+-----------+

5 rows in set (0.00 sec)

使用select命令把表中有多少行就将23的结果显示多少遍：

MariaDB [hellodb]> select 23 from students;

+-----+

| 2*3 |

+-----+

| 6 |

+-----+

25 rows in set (0.00 sec)

字符串和乘积均可显示：

MariaDB [hellodb]> select 'abcd', stuid from students;

+------+-------+

| abcd | stuid |

+------+-------+

| abcd | 1 |

| abcd | 2 |

| abcd | 3 |

| abcd | 4 |

| abcd | 5 |

| abcd | 6 |

| abcd | 7 |

| abcd | 8 |

| abcd | 9 |

| abcd | 10 |

| abcd | 11 |

| abcd | 12 |

| abcd | 13 |

| abcd | 14 |

| abcd | 15 |

| abcd | 16 |

| abcd | 17 |

| abcd | 18 |

| abcd | 19 |

| abcd | 20 |

| abcd | 21 |

| abcd | 22 |

| abcd | 23 |

| abcd | 24 |

| abcd | 25 |

+------+-------+

25 rows in set (0.00 sec)

MariaDB [hellodb]> select 23, stuid from students;

+-----+-------+

| 23 | stuid |

+-----+-------+

| 6 | 1 |

| 6 | 2 |

| 6 | 3 |

| 6 | 4 |

| 6 | 5 |

| 6 | 6 |

| 6 | 7 |

| 6 | 8 |

| 6 | 9 |

| 6 | 10 |

| 6 | 11 |

| 6 | 12 |

| 6 | 13 |

| 6 | 14 |

| 6 | 15 |

| 6 | 16 |

| 6 | 17 |

| 6 | 18 |

| 6 | 19 |

| 6 | 20 |

| 6 | 21 |

| 6 | 22 |

| 6 | 23 |

| 6 | 24 |

| 6 | 25 |

+-----+-------+

25 rows in set (0.00 sec)

select命令还可以这样做：

MariaDB [hellodb]> select from students where age between 20 and 30;

+-------+---------------+-----+--------+---------+-----------+

+-------+---------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 22 | M | 2 | 3 |

| 2 | Shi Potian | 22 | M | 1 | 7 |

| 5 | Yu Yutong | 26 | M | 3 | 1 |

| 9 | Ren Yingying | 20 | F | 6 | NULL |

| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |

| 16 | Xu Zhu | 21 | M | 1 | NULL |

| 17 | Lin Chong | 25 | M | 4 | NULL |

| 18 | Hua Rong | 23 | M | 7 | NULL |

| 21 | Huang Yueying | 22 | F | 6 | NULL |

| 22 | Xiao Qiao | 20 | F | 1 | NULL |

| 23 | Ma Chao | 23 | M | 4 | NULL |

| 24 | Xu Xian | 27 | M | NULL | NULL |

+-------+---------------+-----+--------+---------+-----------+

12 rows in set (0.00 sec)

select命令查看某列内容为空的的字符：(某id is NULL)

MariaDB [hellodb]> select from students where teacherid is NULL;

+-------+---------------+-----+--------+---------+-----------+

+-------+---------------+-----+--------+---------+-----------+

| 6 | Shi Qing | 46 | M | 5 | NULL |

| 7 | Xi Ren | 19 | F | 3 | NULL |

| 8 | Lin Daiyu | 17 | F | 7 | NULL |

| 9 | Ren Yingying | 20 | F | 6 | NULL |

| 10 | Yue Lingshan | 19 | F | 3 | NULL |

| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |

| 12 | Wen Qingqing | 19 | F | 1 | NULL |

| 13 | Tian Boguang | 33 | M | 2 | NULL |

| 14 | Lu Wushuang | 17 | F | 3 | NULL |

| 15 | Duan Yu | 19 | M | 4 | NULL |

| 16 | Xu Zhu | 21 | M | 1 | NULL |

| 17 | Lin Chong | 25 | M | 4 | NULL |

| 18 | Hua Rong | 23 | M | 7 | NULL |

| 19 | Xue Baochai | 18 | F | 6 | NULL |

| 20 | Diao Chan | 19 | F | 7 | NULL |

| 21 | Huang Yueying | 22 | F | 6 | NULL |

| 22 | Xiao Qiao | 20 | F | 1 | NULL |

| 23 | Ma Chao | 23 | M | 4 | NULL |

| 24 | Xu Xian | 27 | M | NULL | NULL |

| 25 | Sun Dasheng | 100 | M | NULL | NULL |

+-------+---------------+-----+--------+---------+-----------+

20 rows in set (0.00 sec)

MariaDB [hellodb]> select from students where teacherid is not NULL;

+-------+-------------+-----+--------+---------+-----------+

+-------+-------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 22 | M | 2 | 3 |

| 2 | Shi Potian | 22 | M | 1 | 7 |

| 3 | Xie Yanke | 53 | M | 2 | 16 |

| 4 | Ding Dian | 32 | M | 4 | 4 |

| 5 | Yu Yutong | 26 | M | 3 | 1 |

+-------+-------------+-----+--------+---------+-----------+

5 rows in set (0.00 sec)

select命令查询特定字段：

MariaDB [hellodb]> select from students where classid in (1,2,5);

+-------+--------------+-----+--------+---------+-----------+

+-------+--------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 22 | M | 2 | 3 |

| 2 | Shi Potian | 22 | M | 1 | 7 |

| 3 | Xie Yanke | 53 | M | 2 | 16 |

| 6 | Shi Qing | 46 | M | 5 | NULL |

| 12 | Wen Qingqing | 19 | F | 1 | NULL |

| 13 | Tian Boguang | 33 | M | 2 | NULL |

| 16 | Xu Zhu | 21 | M | 1 | NULL |

| 22 | Xiao Qiao | 20 | F | 1 | NULL |

+-------+--------------+-----+--------+---------+-----------+

8 rows in set (0.00 sec)

新create一张表，insert命令写入数据：

MariaDB [hellodb]> create table user (id int,name char(10),password varchar(10));

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> insert user values(1,'admin','xingxiaoyatongxue'),(2,'mage','xiqukeke');

Query OK, 2 rows affected, 1 warning (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 1

MariaDB [hellodb]> select from user;

+------+-------+------------+

| id | name | password |

+------+-------+------------+

| 1 | admin | xingxiaoya |

| 2 | mage | xiqukeke |

+------+-------+------------+

2 rows in set (0.00 sec)

用户登录时，只有输入正确的用户名及口令，才能登陆：

MariaDB [hellodb]> select from user where name='mage' and password='xiqukeke';

+------+------+----------+

| id | name | password |

+------+------+----------+

| 2 | mage | xiqukeke |

+------+------+----------+

1 row in set (0.00 sec)

我们修改服务器端的配置文件：

[root@centos7 ~]# vim /mysql/3306/etc/my.cnf

[mysqld]

character-set-server=utf8mb4 将其加入

port=3306

datadir=/mysql/3306/data

socket=/mysql/3306/socket/mysql.sock

我们修改客户端的配置文件：

[root@centos7 ~]# vim /etc/my.cnf.d/mysql-clients.cnf

These groups are read by MariaDB command-line tools

Use it for options that affect only one utility

[mysql]

default-character-set=utf8mb4 将其加入

[mysql_upgrade]

重启服务：

[root@centos7 ~]# /mysql/3306/bin/mysqld restart

Restarting MySQL...

Stoping MySQL...

Starting MySQL...

[root@centos7 ~]# mysql -S /mysql/3306/socket/mysql.sock -p12345gxy

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 1

Server version: 5.5.60-MariaDB MariaDB Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| db1 |

| hellodb |

| mysql |

| performance_schema |

| test |

| test1 |

+--------------------+

7 rows in set (0.00 sec)

修改完配置文件后，新create一张表，查看其字符集类型均是utf8mb4：

MariaDB [(none)]> create database db2;

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show create database db2;

+----------+-----------------------------------------------------------------+

| Database | Create Database |

+----------+-----------------------------------------------------------------+

| db2 | CREATE DATABASE db2 /!40100 DEFAULT CHARACTER SET utf8mb4/ |

+----------+-----------------------------------------------------------------+

1 row in set (0.00 sec)

MariaDB [(none)]> show variables like 'character%';

+--------------------------+----------------------------+

| Variable_name | Value |

+--------------------------+----------------------------+

| character_set_client | utf8mb4 |

| character_set_connection | utf8mb4 |

| character_set_database | utf8mb4 |

| character_set_filesystem | binary |

| character_set_results | utf8mb4 |

| character_set_server | utf8mb4 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

8 rows in set (0.00 sec)

在db2中create一个t1，查看其字符集类型：

MariaDB [(none)]> use db2;

Database changed

MariaDB [db2]> create table t1 (id int,name char(20));

Query OK, 0 rows affected (0.01 sec)

MariaDB [db2]> select * from t1;

Empty set (0.00 sec)

MariaDB [db2]> show create table t1;

+-------+-------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+-------------------------------------------------------------------------------------------------------------------------+

| t1 | CREATE TABLE t1 (

id int(11) DEFAULT NULL,

name char(20) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |

+-------+-------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

字符串去重：

MariaDB [hellodb]> select distinct gender from students;

+--------+

| gender |

+--------+

| M |

| F |

+--------+

2 rows in set (0.01 sec)

模糊匹配：

MariaDB [hellodb]> select * from students where name like 's%';

+-------+-------------+-----+--------+---------+-----------+

+-------+-------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 22 | M | 2 | 3 |

| 2 | Shi Potian | 22 | M | 1 | 7 |

| 6 | Shi Qing | 46 | M | 5 | NULL |

| 25 | Sun Dasheng | 100 | M | NULL | NULL |

+-------+-------------+-----+--------+---------+-----------+

4 rows in set (0.00 sec)

MariaDB [hellodb]> select from students where name like '%in%';

+-------+---------------+-----+--------+---------+-----------+

+-------+---------------+-----+--------+---------+-----------+

| 4 | Ding Dian | 32 | M | 4 | 4 |

| 6 | Shi Qing | 46 | M | 5 | NULL |

| 8 | Lin Daiyu | 17 | F | 7 | NULL |

| 9 | Ren Yingying | 20 | F | 6 | NULL |

| 10 | Yue Lingshan | 19 | F | 3 | NULL |

| 12 | Wen Qingqing | 19 | F | 1 | NULL |

| 17 | Lin Chong | 25 | M | 4 | NULL |

| 21 | Huang Yueying | 22 | F | 6 | NULL |

+-------+---------------+-----+--------+---------+-----------+

8 rows in set (0.00 sec)

查询只有四个字母的名字：

MariaDB [hellodb]> select from user where name like '____';

+------+------+----------+

| id | name | password |

+------+------+----------+

| 2 | mage | xiqukeke |

+------+------+----------+

1 row in set (0.00 sec)

group by 分组：

MariaDB [hellodb]> select avg(age) from students group by gender;

+----------+

| avg(age) |

+----------+

| 19.0000 |

| 33.0000 |

+----------+

2 rows in set (0.00 sec)

加上gender，才能看出是m或f：

MariaDB [hellodb]> select gender,avg(age) from students group by gender;

+--------+----------+

| gender | avg(age) |

+--------+----------+

| F | 19.0000 |

| M | 33.0000 |

+--------+----------+

2 rows in set (0.00 sec)

设置别名为汉字：

MariaDB [hellodb]> select gender,avg(age) 平均年龄 from students group by gender;

+--------+--------------+

| gender | 平均年龄 |

+--------+--------------+

| F | 19.0000 |

| M | 33.0000 |

+--------+--------------+

2 rows in set (0.00 sec)

统计最大值，最小值，总和：

MariaDB [hellodb]> select gender,max(age) from students group by gender;

+--------+----------+

| gender | max(age) |

+--------+----------+

| F | 22 |

| M | 100 |

+--------+----------+

2 rows in set (0.00 sec)

MariaDB [hellodb]> select gender,min(age) from students group by gender;

+--------+----------+

| gender | min(age) |

+--------+----------+

| F | 17 |

| M | 19 |

+--------+----------+

2 rows in set (0.00 sec)

MariaDB [hellodb]> select gender,sum(age) from students group by gender;

+--------+----------+

| gender | sum(age) |

+--------+----------+

| F | 190 |

| M | 495 |

+--------+----------+

2 rows in set (0.00 sec)

group by 与having的语法使用：

MariaDB [hellodb]> select gender,avg(age) 平均年龄 from students group by gender having gender='M';

+--------+--------------+

| gender | 平均年龄 |

+--------+--------------+

| M | 33.0000 |

+--------+--------------+

1 row in set (0.00 sec)

“ order by ”正向从小到大排序：

MariaDB [hellodb]> select from students order by age;

+-------+---------------+-----+--------+---------+-----------+

+-------+---------------+-----+--------+---------+-----------+

| 8 | Lin Daiyu | 17 | F | 7 | NULL |

| 14 | Lu Wushuang | 17 | F | 3 | NULL |

| 19 | Xue Baochai | 18 | F | 6 | NULL |

| 12 | Wen Qingqing | 19 | F | 1 | NULL |

| 10 | Yue Lingshan | 19 | F | 3 | NULL |

| 7 | Xi Ren | 19 | F | 3 | NULL |

| 15 | Duan Yu | 19 | M | 4 | NULL |

| 20 | Diao Chan | 19 | F | 7 | NULL |

| 9 | Ren Yingying | 20 | F | 6 | NULL |

| 22 | Xiao Qiao | 20 | F | 1 | NULL |

| 16 | Xu Zhu | 21 | M | 1 | NULL |

| 1 | Shi Zhongyu | 22 | M | 2 | 3 |

| 21 | Huang Yueying | 22 | F | 6 | NULL |

| 2 | Shi Potian | 22 | M | 1 | 7 |

| 23 | Ma Chao | 23 | M | 4 | NULL |

| 18 | Hua Rong | 23 | M | 7 | NULL |

| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |

| 17 | Lin Chong | 25 | M | 4 | NULL |

| 5 | Yu Yutong | 26 | M | 3 | 1 |

| 24 | Xu Xian | 27 | M | NULL | NULL |

| 4 | Ding Dian | 32 | M | 4 | 4 |

| 13 | Tian Boguang | 33 | M | 2 | NULL |

| 6 | Shi Qing | 46 | M | 5 | NULL |

| 3 | Xie Yanke | 53 | M | 2 | 16 |

| 25 | Sun Dasheng | 100 | M | NULL | NULL |

+-------+---------------+-----+--------+---------+-----------+

25 rows in set (0.00 sec)

“ desc ”倒序排序：

MariaDB [hellodb]> select from students order by age desc;

+-------+---------------+-----+--------+---------+-----------+

+-------+---------------+-----+--------+---------+-----------+

| 25 | Sun Dasheng | 100 | M | NULL | NULL |

| 3 | Xie Yanke | 53 | M | 2 | 16 |

| 6 | Shi Qing | 46 | M | 5 | NULL |

| 13 | Tian Boguang | 33 | M | 2 | NULL |

| 4 | Ding Dian | 32 | M | 4 | 4 |

| 24 | Xu Xian | 27 | M | NULL | NULL |

| 5 | Yu Yutong | 26 | M | 3 | 1 |

| 17 | Lin Chong | 25 | M | 4 | NULL |

| 23 | Ma Chao | 23 | M | 4 | NULL |

| 18 | Hua Rong | 23 | M | 7 | NULL |

| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |

| 21 | Huang Yueying | 22 | F | 6 | NULL |

| 1 | Shi Zhongyu | 22 | M | 2 | 3 |

| 2 | Shi Potian | 22 | M | 1 | 7 |

| 16 | Xu Zhu | 21 | M | 1 | NULL |

| 22 | Xiao Qiao | 20 | F | 1 | NULL |

| 9 | Ren Yingying | 20 | F | 6 | NULL |

| 15 | Duan Yu | 19 | M | 4 | NULL |

| 7 | Xi Ren | 19 | F | 3 | NULL |

| 20 | Diao Chan | 19 | F | 7 | NULL |

| 10 | Yue Lingshan | 19 | F | 3 | NULL |

| 12 | Wen Qingqing | 19 | F | 1 | NULL |

| 19 | Xue Baochai | 18 | F | 6 | NULL |

| 8 | Lin Daiyu | 17 | F | 7 | NULL |

| 14 | Lu Wushuang | 17 | F | 3 | NULL |

+-------+---------------+-----+--------+---------+-----------+

25 rows in set (0.00 sec)

“ desc ”工具排序，在“class ”后加“ - ”，进正序排序，有空值就会被放于最后：

MariaDB [hellodb]> select from students order by -classid desc;

+-------+---------------+-----+--------+---------+-----------+

+-------+---------------+-----+--------+---------+-----------+

| 2 | Shi Potian | 22 | M | 1 | 7 |

| 22 | Xiao Qiao | 20 | F | 1 | NULL |

| 16 | Xu Zhu | 21 | M | 1 | NULL |

| 12 | Wen Qingqing | 19 | F | 1 | NULL |

| 1 | Shi Zhongyu | 22 | M | 2 | 3 |

| 13 | Tian Boguang | 33 | M | 2 | NULL |

| 3 | Xie Yanke | 53 | M | 2 | 16 |

| 7 | Xi Ren | 19 | F | 3 | NULL |

| 10 | Yue Lingshan | 19 | F | 3 | NULL |

| 5 | Yu Yutong | 26 | M | 3 | 1 |

| 14 | Lu Wushuang | 17 | F | 3 | NULL |

| 23 | Ma Chao | 23 | M | 4 | NULL |

| 17 | Lin Chong | 25 | M | 4 | NULL |

| 4 | Ding Dian | 32 | M | 4 | 4 |

| 15 | Duan Yu | 19 | M | 4 | NULL |

| 6 | Shi Qing | 46 | M | 5 | NULL |

| 9 | Ren Yingying | 20 | F | 6 | NULL |

| 21 | Huang Yueying | 22 | F | 6 | NULL |

| 19 | Xue Baochai | 18 | F | 6 | NULL |

| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |

| 20 | Diao Chan | 19 | F | 7 | NULL |

| 18 | Hua Rong | 23 | M | 7 | NULL |

| 8 | Lin Daiyu | 17 | F | 7 | NULL |

| 24 | Xu Xian | 27 | M | NULL | NULL |

| 25 | Sun Dasheng | 100 | M | NULL | NULL |

+-------+---------------+-----+--------+---------+-----------+

25 rows in set (0.00 sec)

limit ”只显示限定的内容：

“MariaDB [hellodb]> select from students order by age limit 3;

+-------+-------------+-----+--------+---------+-----------+

+-------+-------------+-----+--------+---------+-----------+

| 8 | Lin Daiyu | 17 | F | 7 | NULL |

| 14 | Lu Wushuang | 17 | F | 3 | NULL |

| 19 | Xue Baochai | 18 | F | 6 | NULL |

+-------+-------------+-----+--------+---------+-----------+

3 rows in set (0.01 sec)

先对班级排序，再对年龄排序，结果看起来比较齐整：

MariaDB [hellodb]> select * from students order by classid, age;

+-------+---------------+-----+--------+---------+-----------+

+-------+---------------+-----+--------+---------+-----------+

| 24 | Xu Xian | 27 | M | NULL | NULL |

| 25 | Sun Dasheng | 100 | M | NULL | NULL |

| 12 | Wen Qingqing | 19 | F | 1 | NULL |

| 22 | Xiao Qiao | 20 | F | 1 | NULL |

| 16 | Xu Zhu | 21 | M | 1 | NULL |

| 2 | Shi Potian | 22 | M | 1 | 7 |

| 1 | Shi Zhongyu | 22 | M | 2 | 3 |

| 13 | Tian Boguang | 33 | M | 2 | NULL |

| 3 | Xie Yanke | 53 | M | 2 | 16 |

| 14 | Lu Wushuang | 17 | F | 3 | NULL |

| 10 | Yue Lingshan | 19 | F | 3 | NULL |

| 7 | Xi Ren | 19 | F | 3 | NULL |

| 5 | Yu Yutong | 26 | M | 3 | 1 |

| 15 | Duan Yu | 19 | M | 4 | NULL |

| 23 | Ma Chao | 23 | M | 4 | NULL |

| 17 | Lin Chong | 25 | M | 4 | NULL |

| 4 | Ding Dian | 32 | M | 4 | 4 |

| 6 | Shi Qing | 46 | M | 5 | NULL |

| 19 | Xue Baochai | 18 | F | 6 | NULL |

| 9 | Ren Yingying | 20 | F | 6 | NULL |

| 21 | Huang Yueying | 22 | F | 6 | NULL |

| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |

| 8 | Lin Daiyu | 17 | F | 7 | NULL |

| 20 | Diao Chan | 19 | F | 7 | NULL |

| 18 | Hua Rong | 23 | M | 7 | NULL |

+-------+---------------+-----+--------+---------+-----------+

25 rows in set (0.00 sec)

多表操作：

使用“ union ”将多表组合在一起：

MariaDB [hellodb]> select stuid,name from students 学生的序号和名字

-> union

-> select tid,name from teachers;教师的序号和名字

+-------+---------------+

| stuid | name |

+-------+---------------+

| 1 | Shi Zhongyu |

| 2 | Shi Potian |

| 3 | Xie Yanke |

| 4 | Ding Dian |

| 5 | Yu Yutong |

| 6 | Shi Qing |

| 7 | Xi Ren |

| 8 | Lin Daiyu |

| 9 | Ren Yingying |

| 10 | Yue Lingshan |

| 11 | Yuan Chengzhi |

| 12 | Wen Qingqing |

| 13 | Tian Boguang |

| 14 | Lu Wushuang |

| 15 | Duan Yu |

| 16 | Xu Zhu |

| 17 | Lin Chong |

| 18 | Hua Rong |

| 19 | Xue Baochai |

| 20 | Diao Chan |

| 21 | Huang Yueying |

| 22 | Xiao Qiao |

| 23 | Ma Chao |

| 24 | Xu Xian |

| 25 | Sun Dasheng |

| 1 | Song Jiang |

| 2 | Zhang Sanfeng |

| 3 | Miejue Shitai |

| 4 | Lin Chaoying |

+-------+---------------+

29 rows in set (0.00 sec)

将一张表与另一张表进行“union”多表纵向合并：

MariaDB [hellodb]> insert teachers (name,age,gender)values('abc',50,'F');

Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> select * from teachers;

+-----+---------------+-----+--------+

| TID | Name | Age | Gender |

+-----+---------------+-----+--------+

| 1 | Song Jiang | 45 | M |

| 2 | Zhang Sanfeng | 94 | M |

| 3 | Miejue Shitai | 77 | F |

| 4 | Lin Chaoying | 93 | F |

| 5 | abc | 50 | F |

+-----+---------------+-----+--------+

5 rows in set (0.00 sec)

MariaDB [hellodb]> select * from teacher2;

+-----+---------------+-----+--------+

| TID | Name | Age | Gender |

+-----+---------------+-----+--------+

| 3 | Miejue Shitai | 77 | F |

| 4 | Lin Chaoying | 93 | F |

+-----+---------------+-----+--------+

2 rows in set (0.00 sec)

MariaDB [hellodb]> select from teachers union select from teacher2;

+-----+---------------+-----+--------+

| TID | Name | Age | Gender |

+-----+---------------+-----+--------+

| 1 | Song Jiang | 45 | M |

| 2 | Zhang Sanfeng | 94 | M |

| 3 | Miejue Shitai | 77 | F |

| 4 | Lin Chaoying | 93 | F |

| 5 | abc | 50 | F |

+-----+---------------+-----+--------+

5 rows in set (0.00 sec)

“inner join ”取二者交集：

MariaDB [hellodb]> select from students inner join teachers on students.teacherid=teachers.tid;

+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+

+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+

| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |

| 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang | 45 | M |

| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |

| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |

+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+

4 rows in set (0.00 sec)

设置别名 as ，并横向合并：

MariaDB [hellodb]> select stuid,s.name,s.age,tid,t.name,t.age from students as s inner join teachers t on s.teacherid=t.tid;

+-------+-------------+-----+-----+---------------+-----+

| stuid | name | age | tid | name | age |

+-------+-------------+-----+-----+---------------+-----+

| 5 | Yu Yutong | 26 | 1 | Song Jiang | 45 |

| 25 | Sun Dasheng | 100 | 1 | Song Jiang | 45 |

| 1 | Shi Zhongyu | 22 | 3 | Miejue Shitai | 77 |

| 4 | Ding Dian | 32 | 4 | Lin Chaoying | 93 |

+-------+-------------+-----+-----+---------------+-----+

4 rows in set (0.00 sec)

为了看得清楚，再次设置别名，使其名字，年龄都显示更清楚：

MariaDB [hellodb]> select stuid,s.name studentname,s.age studentage,tid,t.name teachername,t.age teacherage from students as s inner join teachers t on s.teacherid=t.tid;

+-------+-------------+------------+-----+---------------+------------+

+-------+-------------+------------+-----+---------------+------------+

| 5 | Yu Yutong | 26 | 1 | Song Jiang | 45 |

| 25 | Sun Dasheng | 100 | 1 | Song Jiang | 45 |

| 1 | Shi Zhongyu | 22 | 3 | Miejue Shitai | 77 |

| 4 | Ding Dian | 32 | 4 | Lin Chaoying | 93 |

+-------+-------------+------------+-----+---------------+------------+

4 rows in set (0.00 sec)

“ left join ”左外连接：

MariaDB [hellodb]> select from students left join teachers on students.teacherid=teachers.tid;

+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+

+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+

| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |

| 2 | Shi Potian | 22 | M | 1 | 7 | NULL | NULL | NULL | NULL |

| 3 | Xie Yanke | 53 | M | 2 | 16 | NULL | NULL | NULL | NULL |

| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |

| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |

| 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang | 45 | M |

+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+

25 rows in set (0.00 sec) 左边全要，右边只要有交集的部分!

学生和教师的交集，设置别名了的左外连接：

MariaDB [hellodb]> select stuid, s.name studentname,tid,t.name teachername from students as s left join teachers as t on s.teacherid=t.tid;

+-------+---------------+------+---------------+

+-------+---------------+------+---------------+

| 1 | Shi Zhongyu | 3 | Miejue Shitai |

| 4 | Ding Dian | 4 | Lin Chaoying |

| 5 | Yu Yutong | 1 | Song Jiang |

| 25 | Sun Dasheng | 1 | Song Jiang |

+-------+---------------+------+---------------+

25 rows in set (0.00 sec)

“ right ”右外连接：

MariaDB [hellodb]> select stuid, s.name studentname,tid,t.name teachername from students as s right join teachers as t on s.teacherid=t.tid;

+-------+-------------+-----+---------------+

+-------+-------------+-----+---------------+

| 1 | Shi Zhongyu | 3 | Miejue Shitai |

| 4 | Ding Dian | 4 | Lin Chaoying |

| 5 | Yu Yutong | 1 | Song Jiang |

| 25 | Sun Dasheng | 1 | Song Jiang |

| NULL | NULL | 5 | abc |

+-------+-------------+-----+---------------+

6 rows in set (0.00 sec)

实现左外连接只取没有交集的部分：

MariaDB [hellodb]> select stuid, s.name teacherid,tid,t.name teachername from students as s left join teachers as t on s.teacherid=t.tid where t.tid is null;

+-------+---------------+------+-------------+

+-------+---------------+------+-------------+

+-------+---------------+------+-------------+

25 rows in set (0.00 sec)

完全外连接：(先挑出来左外连接，再挑出来右外连接，再union，union有去重的特性，共27条记录)

MariaDB [hellodb]> select stuid, s.name studentname,tid,t.name teachername from students as s left join teachers as t on s.teacherid=t.tid

-> union

-> select stuid, s.name studentname,tid,t.name teachername from students as s right join teachers as t on s.teacherid=t.tid;

+-------+---------------+------+---------------+

+-------+---------------+------+---------------+

| 1 | Shi Zhongyu | 3 | Miejue Shitai |

| 4 | Ding Dian | 4 | Lin Chaoying |

| 5 | Yu Yutong | 1 | Song Jiang |

| 25 | Sun Dasheng | 1 | Song Jiang |

| NULL | NULL | 5 | abc |

+-------+---------------+------+---------------+

27 rows in set (0.00 sec)

使用子查询挑出除了交集以外的部分：(子查询：将一个select命令嵌套入另一个select命令中)

MariaDB [hellodb]> select avg(age) from students;

+----------+

| avg(age) |

+----------+

| 27.4000 |

+----------+

1 row in set (0.00 sec)

MariaDB [hellodb]> select from students where age < (select avg(age) from students);

+-------+---------------+-----+--------+---------+-----------+

+-------+---------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 22 | M | 2 | 3 |

| 2 | Shi Potian | 22 | M | 1 | 7 |

| 5 | Yu Yutong | 26 | M | 3 | 1 |

| 7 | Xi Ren | 19 | F | 3 | NULL |

| 8 | Lin Daiyu | 17 | F | 7 | NULL |

| 9 | Ren Yingying | 20 | F | 6 | NULL |

| 10 | Yue Lingshan | 19 | F | 3 | NULL |

| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |

| 12 | Wen Qingqing | 19 | F | 1 | NULL |

| 14 | Lu Wushuang | 17 | F | 3 | NULL |

| 15 | Duan Yu | 19 | M | 4 | NULL |

| 16 | Xu Zhu | 21 | M | 1 | NULL |

| 17 | Lin Chong | 25 | M | 4 | NULL |

| 18 | Hua Rong | 23 | M | 7 | NULL |

| 19 | Xue Baochai | 18 | F | 6 | NULL |

| 20 | Diao Chan | 19 | F | 7 | NULL |

| 21 | Huang Yueying | 22 | F | 6 | NULL |

| 22 | Xiao Qiao | 20 | F | 1 | NULL |

| 23 | Ma Chao | 23 | M | 4 | NULL |

| 24 | Xu Xian | 27 | M | NULL | NULL |

+-------+---------------+-----+--------+---------+-----------+

20 rows in set (0.01 sec)

上述内容是查询小于平均年龄小于27的部分!

删除交集部分：(取出：你有的，我没有的，我有的，你没有的)

MariaDB [hellodb]> select from (select s.stuid,s.name s_name,s.teacherid,t.tid,t.name t_name from students s left join teachers t on s.teacherid=t.tid union select s.stuid,s.name s_name,s.teacherid,t.tid,t.name t_name from students s right join teachers t on s.teacherid=t.tid) as a where a.teacherid is null or a.tid is null;

+-------+---------------+-----------+------+---------------+

+-------+---------------+-----------+------+---------------+

+-------+---------------+-----------+------+---------------+

23 rows in set (0.00 sec)

天工造物局

北京人形旗下天工造物具身智能开源社区，聚焦具身天工与慧思开物两大平台

更多推荐

deck.gl TripsLayer 出租车轨迹动画示例实战：从示例运行到着色器原理

TripsLayer 是 deck.gl 中用于渲染**随时间流动的轨迹动画**（如出租车行程、物流路径、迁徙路线）的核心图层。本文以仓库中的最小独立示例 [examples/website/trips](https://link.gitcode.com/i/1442af8f442f4cff39ff546e050fceb5) 为骨架，带你完成从安装依赖、启动应用、理解曼哈顿出租车数据格式，到深入

天工造物局

Foundry Anvil 中 eth_simulateV1 的 Blob 交易与 Blob Gas 字段规范化：原理、实现与测试验证

本篇技术指南以 [anvil-simulate-blob-transactions.md](https://link.gitcode.com/i/3d2ebe227262de169335eb290af80da8) 这条 changelog 条目为起点，深入剖析 Foundry Anvil 对 `eth_simulateV1` RPC 返回结果中 blob（EIP-4844）交易与 blob gas

天工造物局

LMCache CLI 实战：用 `lmcache kvcache clear` 管理运行中服务的 KV Cache 状态

本文是一份针对 LMCache 官方 CLI 命令 `lmcache kvcache` 的完整技术指南。该命令用于在**运行中的 LMCache 服务（MP HTTP server）**上管理 KV cache 状态，当前提供 `clear` 子命令，用于清空 L1（CPU 内存）层中缓存的全部 KV 数据。读完本文，你将掌握该命令的完整参数体系、退出码语义、终端/JSON/静默三种输出模式，并能