Mysql 中select 语句单表查询

1.查看Mysql 内置函数

复制代码
  1 # 查看Mysql 内置函数
  2 mysql> help contents;
  3 You asked for help about help category: "Contents"
  4 For more information, type 'help <item>', where <item> is one of the following
  5 categories:
  6    Account Management
  7    Administration
  8    Compound Statements
  9    Data Definition
 10    Data Manipulation
 11    Data Types
 12    Functions
 13    Functions and Modifiers for Use with GROUP BY
 14    Geographic Features
 15    Help Metadata
 16    Language Structure
 17    Plugins
 18    Procedures
 19    Storage Engines
 20    Table Maintenance
 21    Transactions
 22    User-Defined Functions
 23    Utility
 24    
 25 # mysql> help Functions;
 26 You asked for help about help category: "Functions"
 27 For more information, type 'help <item>', where <item> is one of the following
 28 categories:
 29    Bit Functions
 30    Comparison operators
 31    Control flow functions
 32    Date and Time Functions
 33    Encryption Functions
 34    Information Functions
 35    Logical operators
 36    Miscellaneous Functions
 37    Numeric Functions
 38    String Functions
 39    
 40 # mysql> help String Functions     查看字符功能函数
 41 You asked for help about help category: "String Functions"
 42 For more information, type 'help <item>', where <item> is one of the following
 43 topics:
 44    ASCII
 45    BIN
 46    BINARY OPERATOR
 47    BIT_LENGTH
 48    CAST
 49    CHAR FUNCTION
 50    CHARACTER_LENGTH
 51    CHAR_LENGTH
 52    CONCAT                                         #******
 53    CONCAT_WS
 54    CONVERT
 55    ELT
 56    EXPORT_SET
 57    EXTRACTVALUE
 58    FIELD
 59    FIND_IN_SET
 60    FORMAT
 61    FROM_BASE64
 62    HEX
 63    INSERT FUNCTION
 64    INSTR
 65    LCASE
 66    LEFT
 67    LENGTH
 68    LIKE
 69    LOAD_FILE
 70    LOCATE
 71    LOWER
 72    LPAD
 73    LTRIM
 74    MAKE_SET
 75    MATCH AGAINST
 76    MID
 77    NOT LIKE
 78    NOT REGEXP
 79    OCT
 80    OCTET_LENGTH
 81    ORD
 82    POSITION
 83    QUOTE
 84    REGEXP
 85    REPEAT FUNCTION
 86    REPLACE FUNCTION
 87    REVERSE
 88    RIGHT
 89    RPAD
 90    RTRIM
 91    SOUNDEX
 92    SOUNDS LIKE
 93    SPACE
 94    STRCMP
 95    SUBSTR
 96    SUBSTRING
 97    SUBSTRING_INDEX
 98    TO_BASE64
 99    TRIM
100    UCASE
101    UNHEX
102    UPDATEXML
103    UPPER
104    WEIGHT_STRING
复制代码

2.select 单独用法 单独使用

复制代码
#1.select 配合内置函数使用
 #查看当前用户
 mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
 #查看当前mysql 版本
 mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.13    |
+-----------+
1 row in set (0.00 sec)
 #查看当前时间
 mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-11-02 20:10:55 |
+---------------------+
1 row in set (0.00 sec)
#查看当前在哪个数据库
mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)
#拼接命令   concat("")
mysql> select concat("hello world!");
+------------------------+
| concat("hello world!") |
+------------------------+
| hello world!           |
+------------------------+
1 row in set (0.01 sec)

mysql> select user,host from mysql.user;    #拼接格式为:mysql.sys@localhost
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| mysql.sys | localhost |
| root      | localhost |
+-----------+-----------+
2 rows in set (0.00 sec)
mysql> select concat(user,"@",host) from mysql.user;   #拼接最终结果
+-----------------------+
| concat(user,"@",host) |
+-----------------------+
| mysql.sys@localhost   |
| root@localhost        |
+-----------------------+
2 rows in set (0.00 sec)

#2.计算
mysql> select 10 * 100;
+----------+
| 10 * 100 |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

mysql> select 100 / 10;
+----------+
| 100 / 10 |
+----------+
|  10.0000 |
+----------+
1 row in set (0.00 sec)

mysql> select 100 - 50;
+----------+
| 100 - 50 |
+----------+
|       50 |
+----------+
1 row in set (0.00 sec)

mysql> select 100 + 30;
+----------+
| 100 + 30 |
+----------+
|      130 |
+----------+
1 row in set (0.00 sec)

mysql> select 100 % 10;
+----------+
| 100 % 10 |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

#3.查询数据库参数        
   #查看全部参数: 504个
| transaction_prealloc_size                                | 4096                                   
| transaction_write_set_extraction                         | OFF                                   
| tx_isolation                                             | REPEATABLE-READ                       
| tx_read_only                                             | OFF                                   
| unique_checks                                            | ON                                     
| updatable_views_with_limit                               | YES                                   
| version                                                  | 5.7.13                                 
| version_comment                                          | Source distribution                   
| version_compile_machine                                  | x86_64                                 
| version_compile_os                                       | Linux                                 
| wait_timeout                                             | 28800                         
| warning_coun                                            | 0                                   
504 rows in set (0.00 sec) #版本不一样,总的参数数量也不一样

#查看只记住参数部分时命令  用Like 
mysql> show variables like "%trx%";
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_api_trx_level           | 0     |
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
2 rows in set (0.00 sec)


mysql> select @@datadir;           #查看数据目录
+-------------------+ 
| @@datadir               
+-------------------+
| /home/mysql/data/ |
+-------------------+
1 row in set (0.00 sec)

mysql> select @@socket;     #查看socket 
+-----------------+
| @@socket        |        
+-----------------+
| /tmp/mysql.sock |
+-----------------+
1 row in set (0.00 sec)
mysql> select @@port;    #查看port
+--------+
| @@port |                
+--------+
|   3306 |
+--------+
1 row in set (0.00 sec)

mysql> select @@innodb_flush_log_at_trx_commit;     
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
|                                1 |
+----------------------------------+
1 row in set (0.00 sec)
复制代码

3.select 标准用法 (配合其它子句使用)

复制代码
#1.单表
默认执行顺序(严格按照执行顺序)
select * 
1. from      表1,表2,l...
2. where     过滤条件1 过滤条件2 ...
3. group by  条件列1 条件列2
4. select_list 
5. having    过滤条件1 过滤条件2 ...
6. order by  条件列1 条件列2
7. limit     限制条件

先导入数据world.sql
[root@shell ~21:01:45]# mysql -uroot -p123456 <world.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> show databases;     #查看所有库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| world              |
+--------------------+
5 rows in set (0.00 sec)

mysql> use world;       #进入world库
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
mysql> show tables;      #查看world库中所有表
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)

mysql> desc city;     #查看city 表结构
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> show create table city;       #查看city 建表语句
| Table | Create Table                                                                                 CREATE TABLE `city` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1 |

#1. select 配合from 子句使用
 语法:select 列 from 表; 类似 于cat /etc/passwd
  select * from 表 ;
---查询表中所有数据
 select *  from world.city;
 select Id,name,countycode,district,population from world.city;
---查询部分列值    类似于awk 取列
select name,population from world.city;

#2. select+from +where   其中where类似于grep 过滤
 #a.where 配合比较符来使用,例如: > = < >= <= !=
 查询city表中,所有中国城市信息
 mysql> select * from world.city where countrycode='CHN';
 查询city表中,人口数小于1000的城市
 mysql> select * from world.city where population <1000;    
 #b.where 配合like 进行模糊查询   适合于字符串类型,不能like数字等
 查询city表中,国家代号为CH开头的城市信息
 select * from world.city where countrycode like 'CH%';
 #注意:like语句在使用时,切记不可出现前面带%的模糊查询,因为不走索引
 #c.where 配合逻辑链接符  AND OR XOR(非)
 查询中国人口数小于500万的城市信息
 select * from world.city where countrycode='CHN' AND population <5000000;
 查询中国或者美国的城市信息
 select * from world.city where countrycode='CHN' OR countrycode='USA';
 
 #3.select +from +where +group by   #分组后一定要显示该列,
     select CountryCode, count(id/name)from world.city  group by CountryCode;
   #a.gtoup by 配合聚合函数  
   max()           #求最大值
   min()           #求最小值
   avg()           #求平均值
   count()         #统计个数
   sum()           #求和
   group_concat()  #列转行
   说明:碰到group by  必然会有聚合函数
   统计每个国家的总的人口数
   select CountryCode, count(id/name)from world.city  group by CountryCode;
   统计中国,每个省的总人口数
   select District,sum(population) from city  where CountryCode='CHN' group by District;
   统计中国,每个省总人口,城市个数,城市名列表
   select District,sum(population),count(id) from city  where CountryCode='CHN' group by District;
 
#4.select +from +where +group by+having   #having 属于后过滤,以group by 为中间,where 为前过滤.
  having 作用:与where 子句类型,having 属于后过滤
  场景:需要在group by +聚合函数后,再做过滤时使用
   统计中国,每个省的总人口数并只显示总人口数大于500万
   select  District,sum(population)\
   from city\
   where CountryCode='CHN'\
   group by District\
   having sum(population) >5000000;

#5.select +from +where +group by+having +order by    
  order by 作用: 排序作用
  统计中国,每个省的总人口数,只显示总人口数大于500万并以总人口数以小到大进行排序
  select  District,sum(population)\
  from city\
  where CountryCode='CHN'\
  group by District\
  having sum(population) >5000000\
  order by sum(population);
  统计中国,每个省的总人口数,只显示总人口数大于500万并以总人口数以大到小进行排序
  select District,sum(population)\
  from city\
  where CountryCode='CHN'\
  group by District\
  having sum(population) >5000000\
  order by sum(population) desc;
  
#6.select +from +where +group by+having +order by+limit
  limit作用:分页显示结果集
  统计中国,每个省的总人口数,只显示总人口数大于500万并以总人口数以大到小进行排序,只显示前5名
  select District,sum(population)\
  from city\
  where CountryCode='CHN'\
  group by District\
  having sum(population) >5000000\
  order by sum(population) desc\
  limit 5;
  
  统计中国,每个省的总人口数,只显示总人口数大于500万并以总人口数以大到小进行排序,只显示6-10名
  select District,sum(population)\
  from city\
  where CountryCode='CHN'\
  group by District\
  having sum(population) >5000000\
  order by sum(population) desc\
  limit 5,5;  #意思为跳过5(左边开始第2个5为跳),再显示5,及为6-10名
  
  select District,sum(population)\
  from city\
  where CountryCode='CHN'\
  group by District\
  having sum(population) >5000000\
  order by sum(population) desc\
  limit 5 offset 5;  #意思为跳过5,再显示5,及为6-10名
  
  显示3-5行
  limit 2,3 或 limit 3 offset 2
  
  #7.select 的别名
  #a.列别名(在select 后面就是列),或者不接AS 直接别名也行.
  作用:
  1.做为好看
  2.group by having order by 子句调用,where 子句不行(因为执行顺序原因)
  select District AS '城市名称', sum(population) AS '总人口数'\
  from city\
  where CountryCode='CHN'\
  group by District\
  having sum(population) >5000000\
  order by sum(population) desc\
  limit 5 offset 5;
  
   #别名子名调用
  例子:
  select District AS '城市名称', sum(population) AS '总人口数'\
  from city\
  where CountryCode='CHN'\
  group by 城市名称\
  having 总人口数 >5000000\
  order by 总人口数 desc\
  limit 5 offset 5;
  
  #b.表别名
   作用:全局调用定义表别名
   select a.sno AS '学号',a.sname AS '姓名',group_concat(c.cname) AS '课程名称'\
   from student AS a\
   join sc AS b\
   on a.sno=b.sno\
   join course AS c\
   where a.sname='zhang3'\
   group by a.sno,a.sname;
复制代码

 

posted @   Linux运维-Friend  阅读(211)  评论(0)  编辑  收藏  举报
编辑推荐：
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
阅读排行：
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
点击右上角即可分享
微信分享提示