在 Shell 脚本中执行 SQL 语句

以下执行 SQL 的方式, 既可以在 终端直接执行, 也可以把命令写到 shell 脚本中执行.

测试环境:

• Contos 7.9
• MySQL 8.0

mysql — The MySQL Command-Line Client

短 SQL 命令执行

直接通过 mysql 命令执行 SQL, 也可以将此写入 shell 脚本中.

–execute=statement, -e statement

执行该语句并退出。默认的输出格式类似于使用 --batch 生成的输出格式。

使用 MySQL 客户端参数中的 --execute 可以直接在客户端执行 SQL 命令

mysql --user=root --password=root lanhai_user --execute "select FULLNAME_ from os_user limit 1;"

根据文档 --user=user_name, -u user_name 和 --password[=password], -p[password] 可以看出, 用户名必须直接指出, 密码可以直接写出或者之后命令后输入. 使用 -u 时用户名和命令之间有空格, 使用 -p 直接写出密码时, 密码和命令直接没有空格

可以直接使用简写的命令

mysql -u root -proot lanhai_user -e "select FULLNAME_ from os_user limit 1;"

因为 execute 命令有退出功能, 所以不用在 SQL结尾写退出命令. 如果在 SQL 中第一句没有指定数据库, 则直接在命令行指定数据库名

输出结果

+-----------+
| FULLNAME_ |
+-----------+
| 管理员     |
+-----------+

因为使用了 --execute 所以输出格式做了优化

长 SQL 命令执行

如果 SQL 比较长, 直接在引号里写 SQL 可能比较麻烦, 可以利用 Here Document (一般约定俗称使用 EOF ) 来实现

mysql -u root -proot lanhai_user  <<EOF
select FULLNAME_ from os_user limit 1;
EOF

同样, 也直接直接把以上语句写入到 shell 脚本文件中. 这里, 也是不能使用 --execute 命令选项.

输出结果

FULLNAME_
管理员

因为使用 --execute 会报错, 所以去掉了此选项, 输出结果也就不在格式化

执行含有 SQL 的文件

根据 Executing SQL Statements from a Text File 文档介绍. 可以通过

mysql db_name < text_file

这种方式来执行 text_file 文件中的 SQL 语句, 如果在 text_file 文件的第一行指定了数据库, 可以直接使用以下语句执行

mysql < text_file

现在在 text_file 文件写入 SQL 语句

echo "select FULLNAME_ from os_user limit 1;" > text_file

或者

cat > text_file <<EOF
"select FULLNAME_ from os_user limit 1;"
EOF

如果文件内容太长, 先用 touch 创建文件后, 再写入内容.

直接执行下列语句

mysql -u root -proot lanhai_user < text_file

输出结果

FULLNAME_
管理员

登录 MySQL 客户端后执行含有 SQL 的文件

Executing SQL Statements from a Text File 文档还介绍了, 登录了 MySQL 客户端以后, 如何执行含有 SQL 的文件

如果您已经在运行 mysql 客户端，您可以使用 source 命令或 \.命令来执行含有 SQL 的文件:

mysql> source file_name
mysql> \. file_name

使用下列命令登录 MySQL 客户端

 mysql -u root -proot lanhai_user

同时指定了使用 lanhai_user 数据库

然后执行上面的 text_file 文件

mysql> source text_file

输出结果

+-----------+
| FULLNAME_ |
+-----------+
| 管理员     |
+-----------+

利用登录 MySQL 客户端后执行 SQL 文件这个特性, 我们可以直接不等了 客户端, 直接通过 source 执行含有 SQL 的文件

 mysql -u root -proot lanhai_user -e "source text_file"

这里必须加 -e 来指定 MySQL 的语句.

结果输出

+-----------+
| FULLNAME_ |
+-----------+
| 管理员     |
+-----------+

1、查询图书馆中所有图书的详细信息。（BookInfo表）

1	select * from bookinfo;

　　

2、查询所有图书的图书编号、图书名称和图书价格。

1	select b_id 编号,b_name 名称,b_price 价格 from bookinfo

　　

3、 查询所有图书的图书编号、图书名称和图书总额。

1	select b_id 编号,b_name 名称,b_price*b_quantity 价格 from bookinfo

　　

4、查询所有图书的图书编号、图书名称和总价值，但希望以汉字标题图书编号、图书名称和总价值表示b_ID、b_Name和b_Price*b_Quantity。

1	select b_id 编号,b_name 名称,b_price*b_quantity 价格 from bookinfo

　　

5、查询所有图书中的“刘志成”编写的图书的所有信息。

1	select * from bookinfo where b_author like '%刘志成%'

6、 查询图书类别编号为“17”，图书价格在25~30元之间的图书信息，要求以汉字标题显示图书编号、图书名称、图书类别编号和图书价格。

1	select b_id 图书编号,b_name 图书名称,bt_id 图书类别编号,b_price 图书价格 <br>from bookinfo where bt_id = 17 and b_price between 25 and 30;

7、 查询所有入库在5年以上并且图书价格在10~20之间的图书的名称、图书作者、图书价格和入库时间（用“入库时长”表示，该列不是基本表中的字段，是计算出来的列）。

1 2 3 4 5 6 7

select b_name 图书名称,b_author 图书作者,b_price 图书价格, b_date 入库时间   from bookinfo   where ROUND(TO_NUMBER(SYSDATE - b_date))>=5   and b_price between 10 and 20;

　　

8、 查询所有入库在5年以上并且图书价格不在10~20之间的图书的名称、图书作者、图书价格和入库时间

1 2 3 4 5 6 7

select b_name 图书名称,b_author 图书作者,b_price 图书价格, b_date 入库时间   from bookinfo   where ROUND(TO_NUMBER(SYSDATE - b_date))>=5   and b_price not between 10 and 20;

　　

9、查询出版社编号为“001”和“003”的图书详细信息。

1	select * from bookinfo where p_id in (001,003)

　　

10、查询图书名称中包含“数据库”字样的图书的详细信息。

1	select * from bookinfo where b_name like '%数据库%'

　　

11、查询姓“王”且名字中有3个汉字的读者的编号、读者的姓名及可借书数量。

1 2 3

select r_id,r_name,r_quantity from readerinfo   where r_name like '王__';

　　

12、查询暂时没有图书封面图片的图书信息。

1	select * from bookinfo where b_pricture is null;

　　

13、       通过图书管理系统查询借阅图书的读者编号，如果一个读者借阅了多本图书，只需要显示一次读者编号。

1	select distinct r_id from borrowreturn;

　　

14、查询前5行图书的详情信息。

1	select * from bookinfo where rownum <6;

　　

15、查询前20%行图书的详情信息。

1 2 3 4 5

select *   from bookinfo   where rownum <to_number(0.2*(select count(*)from bookinfo)+1);

　　

 

16、查询图书出版社编号为“001”的图书的图书编号、图书名称、图书价格和出版社编号，并要求根据图书价格进行降序排列。

1 2 3 4 5

select b_id 图书编号,b_name 图书名称,b_price 图书价格,p_id 图书出版社编号   from (select * from bookinfo order by b_price desc)   where p_id = 001;

　　

17、查询价格在20元以上的图书的图书编号、图书名称、图书价格和出版社编号的信息，并要求按出版社编号升序排列；如果是同一出版社的图书，则按价格降序排列。

1 2 3

select b_id 图书编号,b_name 图书名称,b_price 图书价格,p_id 图书出版社编号   from bookinfo order by p_id asc,b_price desc;

　　

18、查询图书馆中每一个出版社的图书种类数。

1 2 3 4 5 6 7

select p_id 出版社编号,count(p_id) 图书种类   from bookinfo   group by p_id   order by p_id;

　　

19、查询图书馆中每一个出版社的图书种类数及图书总数。

1 2 3 4 5 6 7

select p_id 出版社编号,count(p_id) 图书种类,sum(b_quantity) 图书总数   from bookinfo   group by p_id   order by p_id;

　　

20、分组后的数据按图书总数进行降序排列。

1 2 3 4 5 6 7

select p_id 出版社编号,count(p_id) 图书种类,sum(b_quantity) 图书总数   from bookinfo   group by p_id   order by sum(b_quantity) desc;

　　

21、对分组后的数据进行筛选，要求显示图书总量大于15的出版社编号、图书种类和图书总数，筛选后的结果按“图书总数”升序排列。

1 2 3 4 5 6 7 8 9

select p_id 出版社编号,count(p_id) 图书种类,sum(b_quantity) 图书总数   from bookinfo   group by p_id   having sum(b_quantity)>15   order by sum(b_quantity) asc;

　　

22、查询每种图书的图书编号、图书名称和图书类型名称（不是图书类别编号）。

1 2 3 4 5

select b_id 图书编号, b_name 图书名称,bt_name 图书类型名称   from bookinfo,booktype   where bookinfo.bt_id = booktype.bt_id

　　

 

1 2 3 4 5

select b_id 图书编号, b_name 图书名称,bt_name 图书类型名称   from bookinfo join booktype   on bookinfo.bt_id = booktype.bt_id

　　

23、查询借还表中所有“未还”图书的借阅ID、借阅人（读者）、图书名称、详细存放位置和借出日期。

1 2 3 4 5 6 7 8 9 10 11

select borrowreturn.r_id 借阅ID, r_name 借阅人,b_name 图书名称,s_position   from borrowreturn   join readerinfo on borrowreturn.r_id=readerinfo.r_id   join bookstore on borrowreturn.s_id=bookstore.s_id   join bookinfo on bookstore.b_id=bookinfo.b_id   where br_status = '未还';

　　

24、查询不低于《JSP程序设计安全教程》价格的图书编号、图书名称和图书价格，查询后的结果要求按图书价格升序排列。

1 2 3 4 5 6 7

select b_id 图书编号,b_name 图书名称,b_price 图书价格   from bookinfo   where b_price>=(select b_price from bookinfo where b_name = 'JSP程序设计案例教程')   order by b_price asc;

　　

 

 

1 2 3 4 5 6 7

select G2.b_id 图书编号,G2.b_name 图书名称,G2.b_price 图书价格   from bookinfo G1 join bookinfo G2 on G1.b_name = 'JSP程序设计案例教程'   and G1.b_Price<=G2.b_price   order by G2.b_price;

　　

25、查询所有图书类别及其对应图书信息，如果该图书类别没有对应图书也需要显示其类别信息。将BookType表和BookInfo表进行左外连接，BookType为左表，BookInfo表为右表。

1 2 3 4 5 6 7

select G2.b_id 图书编号,G2.b_name 图书名称,G2.b_price 图书价格   from bookinfo G1 join bookinfo G2 on G1.b_name = 'JSP程序设计案例教程'   and G1.b_Price<=G2.b_price   order by G2.b_price;

　　

26、查询所有图书的信息（即使是不存在对应的图书类别信息，实际上这种情况是不存在的）。

1 2 3 4 5

select booktype.bt_id,bt_name,b_id,b_name,b_price,b_quantity   from booktype left join bookinfo   on booktype.bt_id = bookinfo.bt_id

　　

27、查询和《UML用户指南》为同一出版社的图书的图书编号、图书名称和出版社编号。

1 2 3 4 5

select b_id 图书编号,b_name 图书名称,p_id 出版社编号   from bookinfo   where p_id =(select p_id from bookinfo where b_name = 'UML用户指南')

　　

28、查询借阅了《SQL Server 2005实例教程》的借阅号、借阅日期和操作员。

1 2 3 4 5

select s_id 借阅号,br_outdate 借阅日期,br_operator 操作员   from borrowreturn   where s_id in(select s_id from bookstore where b_id in(select b_id from bookinfo where b_name = 'SQL Server 2005实例教程'));

　　

 

1 2 3 4 5 6 7 8 9

select borrowreturn.s_id 借阅号,br_outdate 借阅日期,br_operator 操作员   from borrowreturn   join bookstore on borrowreturn.s_id = bookstore.s_id   join bookinfo on bookstore.b_id = bookinfo.b_id   where b_name = 'SQL Server 2005实例教程';

　　

29、查询比出版社编号为“007”任一图书入库日期晚的图书信息，查询结果按降序排列。

1 2 3 4 5 6 7 8 9

select b_id,b_name,b_date,b_price   from bookinfo   where b_date>all   (select b_date from bookinfo where p_id='007')   order by b_date desc;

　　

 

1 2 3 4 5 6 7

select b_id,b_name,b_date,b_price   from bookinfo   where b_date>(select max(b_date) from bookinfo where p_id='007')   order by b_date desc;

　　

30、针对ReadInfo表中的每一位读者，在BorrowReturn表中查找借阅过图书并且图书状态为“已还”的所有借阅信息。

1 2 3 4 5 6 7 8 9

select br_id,s_id,br_outdate,br_indate   from borrowreturn   where br_status = '已还'   and exists   (select * from readerinfo where readerinfo.r_id=borrowreturn.r_id);

　　

31、求每一类别图书的平均价格，并将结果保存到数据库中。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

create table avgbookprice(   bt_id char(10),   p_avg number(7,2)   );   insert into avgbookprice(bt_id,p_avg)   select bt_id,avg(b_price)   from bookinfo   group by bt_id;

　　

32、将图书管理系统中的图书出版社名称为“机械工业出版社”的图书的数量加1.

1 2 3 4 5

update bookinfo set b_quantity=b_quantity + 1   where p_id = (select p_id from publisher   where p_name = '机械工业出版社');

　　

33、删除出版社名称为“机械工业出版社”的所有图书信息。

1 2 3 4 5

delete from bookinfo   where p_id =   (select p_id from publisher where p_name = '机械工业出版社')

原创博文，欢迎转载，转载时请务必附上博文链接，感谢您的尊重。

前言

通过本篇，你将学到MySQL数据库关于【列】的操作，包括【新增，移动，删除，清空数据，数据替换】等，着急的朋友请直接看【正文】部分。

2021年的第一场沙尘暴，比以往时候来得更晚了一些。虽然天气很糟糕，但是我纸醉金迷生活却丝毫不受影响。今晚约了吃饭，但是碰巧下午需要更版，所以此刻的DBA正在更新SQL，可我的内心毫无波澜，对我来说：发版就是下班。

‘“老师，预演环境出问题了，赶紧看一下！！”，WTF，怎么可能（程序员的第一反应都是如此，太真实了！）。

中间省略10分钟的查日志时间...

原来是运维私自下架了首页“我的店铺”功能，但是用户“最近使用”记录里依然存在该ID，导致这部分用户查询“最近使用”数据失败引发的血案。代码逻辑不够严谨是一定的，不能全怪运维工作疏忽不认真看操作手册。当下最要紧的就是上线，我分析了一下，提出了快速解决问题的方案：

• 今天，先用SQL处理异常数据，在用户“最近使用”表，去掉"recent_function"字段中的“我的店铺”功能ID，确保当前版本快速上线；
• 后续，记录问题，优化代码逻辑，避免此类问题再次出现，后端代码在下一版本中上线即可。

描述一下今天的具体工作：去掉recent_function字段中的“17”字符串（“我的店铺”功能对应的就是“17”），同时不能破坏已有的用户数据，因为是生产环境。

因为不是什么大事儿，交给我任劳任怨的大弟子解决吧，我今晚有约要先走了。打卡下班，走之前再来关照下我的关门大弟子，我勒个去，真是“不看不知道，一看下一跳”，这家伙居然在一条条的复制SQL？？

update user_function set recent_function = '11,12,13'  WHERE id = 100;
update user_function set recent_function = '11,15,18,16'  WHERE id = 101;
update user_function set recent_function = '19,20,21,22'  WHERE id = 102; 

。。。

“你告诉我，受影响的用户数据有多少？”，大弟子乐观的跟我讲“还行，400多条，一会就搞完了，师傅先走”，我上去就呼他一巴掌，敲着电脑恼屏幕质问他“400条？如果是1W条数据，你还这么搞？？”，看来，是时候再展现一下我的MySQL功底了：

update user_function set recent_function = replace(recent_function, '17', '') where recent_function LIKE '%17%';

写下一句SQL，潇洒离开，挥一挥衣袖，不带走一片云彩，在众人钦佩的目光中打卡，下班。

---

正文

扯了不少写作的初衷，本篇就把【MySQL】数据库关于【列】的操作做个总结，包含【标题】（和前言）中提到的问题，以MySQL为例：

以下案例假设 —— 表名：'table_name'，待处理的列名： 'column_name'，待处理的字符串：'str_1'，替换为：'str_2'（可自定义）

1. 替换字符

使用函数REPLACE(s,s1,s2)，将字符串s中的 s1替换成s2，可以实现批量的列操作，同时可以保留原始数据，非常适合我遇到的场景。

update 表 set 列 = replace(列, 待替换的字符串, 替换为的字符串) where 列 like '%待替换的字符串%';;
update table_name set column_name = replace(column_name, str_1 , str_2) where column_name like '%str_1%';

2. 新增

新增的列默认是在表的最后，但是也可以使用 first、after关键字来声明具体位置。

ALTER TABLE 语句用于在已有的表中添加、修改或删除列

1. 新增列默认是在表的最后
alter table 表名 add 列名 列属性;
alter table table_name add column_name tinyint unsigned not null default 100;
—— default 100(设置列的默认值)

2. 用first，声明新增的列在最前面
alter table 表名 add 列名 列属性 first;
alter table table_name add column_name int primary key auto_increment first;
—— primary key auto_increment(设为主键，并自动增加)

3.用after，声明新增的列在哪个字段的后面
alter table 表名 add 列名 列属性 after 另一个已存在的列名;
alter table table_name add column_name tinyint unsigned not null default 100 after other_exist_column_name;

3. 修改

• change：既可以只修改列属性——列名和原来的相同但属性不同，也可以同时修改列名和属性——列名和和属性都不同；
• modify：只能用于字段类型的修改

1. change可以修改名称和属性
alter table 表名 change 被修改的列名 新的列名 新列的属性;
alter table table_name change column_name column_name smallint unsigned not null default 100;
alter table table_name change column_name other_column_name int unsigned not null default 110;

2. alter 只能用于修改字段类型
alter table 表名 modify 列名 列类型;
alter table table_name modify column_name int;

4. 删除/清空

使用update清空某一列数据时，注意该列的属性是不为null才可以，否则可以用''替代。

1. 删除某列（机构上删除）
alter table 表名 drop column 列名;
alter table table_name drop column column_name;

2.清空某列（删除改列所有数据）
update 表名 set 列名 = '';
update table_name set column_name = null;

持续更新...

---

我是「IT无知君」，您的点赞、评论和关注，是我创作的动力源泉。
学无止境，气有浩然，让我们一起加油，天涯未远，江湖有缘再见！！