mysql-sql语句进阶

网站建设1870 更新时间：2025-06-08 02:34:31

2023年6月21日发(作者：)

mysql-sql语句进阶回顾前⾯的基础命令语句修改数据表添加字段：alter table 表名 add 字段名列类型 [not null|null][primary key][unique][auto_increment][default value]alter table 表名 add 字段定义 after ar_id;删除字段：alter table 表名 drop 字段名修改字段：alter table 表名 modify 字段名字段新类型完整修改字段：alter table 表名 change 旧字段名称新字段定义修改表名称alter table 表名 rename 新名字删除表drop table [if (not) exists] 表名;

update -- 单表update 表名 set 字段名称=值,... [where 条件]如果省略WHERE条件将更新全部记录。

删除记录 -- 单表delete from 数据表名称 [where 条件]如果省略where条件，将删除全部记录

selectselect 字段列表 from 数据表 [[as] 别名] [where 条件]别名的⽤法：Select * from 数据表 [[as] 别名]字段名称 [[as]别名]Select product_offer_instance_object_id as ID, product_offer_instance_object_name name,coumn33 ‘⾦额’From tableselect btypeid as '图书类别ID',btypename as '图书类型' from category;

select语句返回零条或多条记录；属于记录读操作insert、update、delete只返回此次操作影响的记录数；属于写操作

数据类型MySQL中定义数据字段的类型对你数据库的优化是⾮常重要的。MySQL⽀持多种类型，⼤致可以分为三类：数值、⽇期/时间和字符串(字符)类型。数值类型

⽇期和时间类型字符串类型

整型tinyint,占1字节,有符号：-128~127,⽆符号位:0~255smallint,占2字节,有符号：-32768~32767,⽆符号位:0~65535mediumint,占3字节,有符号：-8388608~8388607,⽆符号位:0~16777215int,占4字节,有符号：-2147483648~2147483647,⽆符号位:0~4284967295bigint,占8字节bool 等价于tinyint(1) 布尔型浮点型float([m[,d]]) 占4字节,1.17E-38~3.4E+38double([m[,d]]) 占8字节decimal([m[,d]]) 以字符串形式表⽰的浮点数字符型char([m]):固定长度的字符,占⽤m字节varchar[(m)]:可变长度的字符,占⽤m+1字节，⼤于255个字符：占⽤m+2tinytext,255个字符(2的8次⽅)text,65535个字符(2的16次⽅)mediumtext,16777215字符(2的24次⽅)longtext,(2的32次⽅)enum(value,value,...)占1/2个字节最多可以有65535个成员set(value,value,...)占1/2/3/4/8个字节，最多可以有64个成员常⽤select命令使⽤select命令查看mysql数据库系统信息：-- 打印当前的⽇期和时间select now();-- 打印当前的⽇期select curdate();

-- 打印当前的时间select curtime();

-- 打印当前数据库select database();

-- 打印MySQL版本select version();

-- 打印当前⽤户select user();

--查看系统信息show variables;show global variables;show global variables like '%version%';show variables like '%storage_engine%'; 默认的存储引擎like模糊搜索还可⽤户where字句，例如select * from students where stname like '%l%1%2%3%';除了like 还有not likeshow engines;查看⽀持哪些存储引擎

--查看系统运⾏状态信息show status;show global status like 'Thread%';

多使⽤help

导出，导⼊数据库导⼊数据库导⼊数据库前必须创建⼀个空数据库mysql -e 'create database book' -uroot -p123456或者登陆 mysqlcreate database book;导⼊（⽅法⼀）mysql -uroot -p123456 book < ql> use book;mysql> show tables;+----------------+| Tables_in_book |+----------------+| books || catego+----------------+

导⼊（⽅法⼆）create database book;mysql> use book;

mysql> source /root/ #sql脚本的路径mysql> show tables;+----------------+| Tables_in_book |+----------------+| books || category |+----------------+

导出数据库导出数据库：mysqldump -u ⽤户名 -p 数据库名 > 导出的⽂件名mysqldump -u system -p123456 book>

扩展知识Mysqldump –uroot –p123456 –B 库名>⽂件.sql-B : 导出整个库包含建库语句-A：导出全部数据库如何把⼀个select的结果导出到⽂本select * into outfile '/tmp/' from books; 此处有个⽂件访问权限问题，mysql⽤户是可以访问/tmp路径的,所以这⾥放到tmp下select * from books into outfile '/tmp/';其实就是备份数据库扩展： 5.7版本导出报错，可以设置加上secure-file-priv="/ "Sql查询语句进阶在我们刚导⼊的book数据库进⾏测试查看表的内容：mysql> select * from category;mysql> select * from books;mysql> select * from booksG查看字段类型：desc 表名mysql> desc books;

逻辑运算符:and or notand且or 或not⾮选择出书籍价格为（30,40,50,60）的记录,只显⽰书籍名称，出版社，价格mysql> select bName,publishing,price from books where price=30 or price=40 or price=50 or price=60;+--------------------------------------+--------------------------+-------+| bName | publishing | price |+--------------------------------------+--------------------------+-------+| Illustrator 10完全⼿册 | 科学出版社 | 50 || FreeHand 10基础教程 | 北京希望电⼦出版 | 50 || ⽹站设计全程教程 | 科学出版社 | 50 || ASP数据库系统开发实例导航 | ⼈民邮电出版社 | 60 || Delphi 5程序设计与控件参考 | 电⼦⼯业出版社 | 60 || ASP数据库系统开发实例导航 | ⼈民邮电出版社 | 60 |+--------------------------------------+--------------------------+-------+

算术运算符：= 等于<> 不等于 !=> ⼤于< ⼩于>= ⼤于等于<= ⼩于等于in 运算符IN 运算符⽤于 WHERE 表达式中，以列表项的形式⽀持多个选择，语法如下：WHERE column IN (value1,value2,...)WHERE column NOT IN (value1,value2,...)Not in 与in相反当 IN 前⾯加上 NOT 运算符时，表⽰与 IN 相反的意思，即不在这些列表项内选择。

找出价格⼤于60的记录mysql> select bName,price from books where price>60;找出价格为60的mysql> select bName,price from books where price=60;找出价格不等于60的mysql> select bName,price from books where price<>60;找出价格是60，50，70的记录mysql> select bName,price from books where price in (50,60,70);找出价格不是60，50，70的记录mysql> select bName,price from books where price not in (50,60,70);

排序：升序：order by “排序的字段” asc 默认降序：oredr by “排序的字段” descmysql> select bName,price from books where price in (50,60,70) order by price asc;+------------------------------------------------+-------+| bName | price |+------------------------------------------------+-------+| Illustrator 10完全⼿册 | 50 || FreeHand 10基础教程 | 50 || ⽹站设计全程教程 | 50 || ASP数据库系统开发实例导航 | 60 || Delphi 5程序设计与控件参考 | 60 || ASP数据库系统开发实例导航 | 60 |mysql> select bName,price from books where price in (50,60,70) order by price desc;+--------------------------------------+-----------------+| bName | price |+--------------------------------------+-----------------+| ASP数据库系统开发实例导航 | 60 || Delphi 5程序设计与控件参考 | 60 || ASP数据库系统开发实例导航 | 60 || Illustrator 10完全⼿册 | 50 || FreeHand 10基础教程 | 50 || ⽹站设计全程教程 | 50 |多个字段排序select bName,price from books where price in (50,60,70) order by price desc,bName desc;范围运算：[not]between ....Between and 可以使⽤⼤于⼩于的⽅式来代替,并且使⽤⼤于⼩于意义表述更明确查找价格不在30到60之间的书名和价格mysql> select bName,price from books where price not between 30 and 60 order by price desc;注：这⾥的查询条件有三种：between。。。and，or 和 in(30,60) >30 and <60[30,60] >=30 and <=60模糊匹配查询：字段名 [not]like '通配符' ----》% 任意多个字符

查找书名中包括"程序"字样记录mysql> select bName from books where bName like '%程序%';不含有mysql> select bName from books where bName not like '%程序%';

MySQL⼦查询：概念：在select 的where条件中⼜出现了select查询中嵌套着查询

选择类型名为“⽹络技术”的图书：mysql> select bName,bTypeId from books where bTypeId=(select bTypeId from category where bTypeName='⽹络技术');选择类型名称为“⿊客”的图书；mysql> select bName,bTypeId from books where bTypeId=(select bTypeId from category where bTypeName='⿊客');

Limit限定显⽰的条⽬：SELECT * FROM table LIMIT [offset,] rows

偏移量⾏数　　LIMIT ⼦句可以被⽤于强制 SELECT 语句返回指定的记录数。LIMIT 接受⼀个或两个数字参数。参数必须是⼀个整数常量。如果给定两个参数，第⼀个参数指定第⼀个返回记录⾏的偏移量，第⼆个参数指定返回记录⾏的最⼤数⽬。初始记录⾏的偏移量是 0(⽽不是 1)：

⽐如select * from table limit m,n语句表⽰其中m是指记录开始的index，从0开始，表⽰第⼀条记录n是指从第m+1条开始，取n条。

查出category表中第2条到第6⾏的记录。⾸先2到6⾏有2，3，4，5，6总共有5个数字，从2开始，偏移量为1mysql> select * from category limit 1,5;+---------+--------------+| bTypeId | bTypeName |+---------+--------------+| 2 | ⽹站 || 3 | 3D动画 || 4 | linux学习 || 5 | Delphi学习 || 6 | ⿊客 |+---------+--------------+

查看所有书籍中价格中最低的三条记录我们对所有记录排序以升序排列，取出前⾯3个来mysql> select bName,price from books order by price asc limit 0,3;+-----------------------------+-------+| bName | price |+-----------------------------+-------+| ⽹站制作直通车 | 34 || ⿊客与⽹络安全 | 41 || ⽹络程序与设计－asp | 43 |

我们将⼦查询和限制条⽬，算术运算结合起来查询显⽰字段bName ,price ；条件：找出价格⽐电⼦⼯业出版社出版的书中最便宜还要便宜的书。针对这种查询，我们⼀步步的来，先找出电⼦⼯业出版社出版中最便宜的书mysql> select bName,price from books where publishing="电⼦⼯业出版社" order by price asc limit 0,1;mysql> select bName,price from books where price<(select price from books where publishing="电⼦⼯业出版社" order by price asc limit0,1); 或者多⾏⼦查询： all表⽰⼩于⼦查询中返回全部值中的最⼩值mysql> select bName,price from books where price

连接查询：以⼀个共同的字段，求两张表当中符合条件的并集。通过共同字段把这两张表连接起来。常⽤的连接：内连接：根据表中的共同字段进⾏匹配外连接分两种：左外连接、右外链接。

内连接语法：select 字段 from 表1 inner join 表2 on 表1.字段=表2.字段内连接：根据表中的共同字段进⾏匹配测试Select ,,ame from books a inner join category b on d=d;实际使⽤中inner可省略掉跟WHERE ⼦句结果⼀样select ,,ame from books a, category b where d=d;

外连接 (分为左外连接；右外连接)1.左连接： select 字段 from a表 left join b表 on 连接条件a表是主表，都显⽰。b表从表主表内容全都有，从表内没有的显⽰null。Select ,,ame from books a left join category b on d=d;

2.右连接：select 字段 from a表 right join b表 on 条件a表是从表，b表主表，都显⽰。Select ,b.* from books a right join category b on d=d;右连接，可以多表连接

聚合函数函数：执⾏特定功能的代码块。算数运算函数：Sum()求和显⽰所有图书单价的总合 mysql> select sum(price) from books; 或select sum(price) as 图书总价 from books;+------------+| sum(price) |+------------+| 10048 |+------------+avg()平均值：求书籍Id⼩于3的所有书籍的平均价格mysql> select avg(price) from books where bId<=3;+------------+| avg(price) |+------------+| 39.3333 |+------------+max() 最⼤值：求所有图书中价格最贵的书籍mysql> select bName,max(price) from books; 这种⽅法是错误的我们来查⼀下最贵的图书是哪本？select bname,price from books order by desc price limit 0,3;可见最贵书是Javascript与Jscript从⼊门到精通，⽽不是⽹站制作直通车select bName,price from books where price=(select max(price) from books);+----------------------------------------+-------+| bName | price |+----------------------------------------+-------+| Javascript与Jscript从⼊门到精通 | 7500 |+----------------------------------------+-------+

min()最⼩值：求所有图书中价格便宜的书籍mysql> select bName,price from books where price=(select min(price) from books);+-----------------------+-------+| bName | price |+-----------------------+-------+| ⽹站制作直通车 | 34 |+-----------------------+-------+

count()统计记录数:统计价格⼤于40的书籍数量mysql> select count(*) from books where price>40;+----------+| count(*) |+----------+| 43 |+----------+Count（）中还可以增加你需要的内容，⽐如增加distinct来配合使⽤select count(distinct price) from books where price>40;算数运算： + - * /给所有价格⼩于40元的书籍，涨价5元 mysql> update books set price=price+5 where price<40; 给所有价格⾼于70元的书籍打8折 mysql> update books set price=price*0.8 where price>70;字符串函数： substr(string ,start,len) 截取：从start开始，截取len长.start 从1开始算起。mysql> select substr(bTypeName,1,7) from category where bTypeId=10;+-----------------------+| substr(bTypeName,1,7) |+-----------------------+| AutoCAD | 本来是AutoCAD技术+-----------------------+select substr(bTypeName,8,2)from category where bTypeId=10;+-----------------------+| substr(bTypeName,8,2) |+-----------------------+| 技术 | 只截取汉字+-----------------------+1 row in set (0.00 sec)

concat(str1,) 拼接。把多个字段拼成⼀个字段输出mysql> select concat(bName,publishing) from books;mysql> select concat(bName,"-----",publishing) from books;⼤⼩写转换 upper()⼤写 : 转为⼤写输出mysql> select upper(bname) from books where bId=9;+---------------------------+| upper(bname) |+---------------------------+| DREAMWEAVER 4ǽɡµň¶Ľ |+---------------------------+这样转换中⽂会出现乱码 lower()⼩写：转为⼩写输出 mysql> select lower(bName) from books where bId=10;+-------------------------------+| lower(bName) |+-------------------------------+| 3d max 3.0 创作效果百例 |+-------------------------------+