How to query mysql statement-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

How to query mysql statement

coldplay.xixi

Dec 16, 2020 am 10:30 AM

查询mysql语句的方法：查询一张表中的记录时，代码为【select * from 表名 where name='long' and age ='18'】，from后面加表名，where后面是条件，select后面是筛选出的字段。

How to query mysql statement

本教程操作环境：windows7系统、mysql8.0.22版，该方法适用于所有品牌电脑。

相关免费学习推荐：mysql视频教程

查询mysql语句的方法：

在mysql中查询一张表中的记录的时候

　　书写顺序是: select * from 表名 where name='long' and age ='18';

但是mysql中的执行顺序是

　　from 后面加表名确定你是那张表
　　where后面是条件通过条件来筛选这表的内容
　　select后面是你where筛选出的数据中的 某些字段 * 是所有字段

　　# 查询语句执行的结果也是一张表，可以看成虚拟表

我们创建一张 emp的员工表

当我们的字段特别多的时候结果的排版可能会出现凌乱现象我们可以在查询语句末尾加上\G来规范查询结果

select * from 表名\G;

当我们遇到一个需求时怎么来分析? 例如

1.查询id大于等于3小于等于6的数据

　　　给你展示下实际操作 1.先确定来自哪一张表 from emp 2. 筛选条件 where id >= 3 and id

　　 select * from emp where id >= 3 and id = 3 and id <p>2.查询薪资是20000或者18000或者17000的数据</p><pre class="brush:php;toolbar:false">    select id,name from emp where salary = 20000 or salary = 18000 or salary = 17000;
    select id,name from emp where salary in (20000,18000,17000);

3.查询员工姓名中包含o字母的员工姓名和薪资

模糊匹配 % 匹配多个任意字符 _ 匹配一个任意字符

select name,salary from emp where name like '%o%';

4.查询员工姓名是由四个字符组成的员工姓名与其薪资　

select name, salary from emp where length(name) =4;
select name ,salary from emp where name like "____"

5.查询id小于3或者大于6的数据

select * from emp where id6;
select * from emp where id not between 3 and 6;

6.查询薪资不在20000，18000，17000范围的数据

select * from emp  where  salary not in (20000,17000,18000);

7.查询岗位描述为空的员工名与岗位名 针对null判断的时候只能用is 不能用=

select name ,post from emp where post_comment is null;

MySQL对大小写不敏感平时写的时候大小写都可以

1、group by 分组

select * from emp group by post;  # 按照部门分组

分组后应该做到最小单位是组 ,而不应该是展示组内的单个数据信息

向上面那样他会直接给你打印出来而没有给你报错说明你的严格模没有设置

show variables '%mode%'; # 找到严格模式所在的地方set session # 临时有效 set global  # 永久有效set global sql_mode= 'strict_trans_tables' # 设置字符类型的自动截取set global sql_mode="strict_trans_tables,pad_char_to_full_length" #char 取出时 取消自动去空格set global sql_mode='strict_trans_tables,only_full_group_by' # 设置分组后   最小单位是组

此时你如果还使用 select name from emp group by post; 就会报错 #ERROR 1055 (42000): 'day37.emp.name' isn't in GROUP BYselest 后应该接的是 你分组的字段名

2、聚合函数(max, min ,sum,count, avg) 聚集到一起合成为一个结果

mysql中分组之后只能拿到分组的字段信息 无法直接获取其他字段的信息但是你可以通过其他方法来间接的获取(聚合函数)

获取每个部门的最高工资

　　　需求是每一个部门说明有分组所以先分组在使用聚合函数来取值

select post ,max(salary) from emp group by post;

每个部门的最低工资

select post,min(salary)  emp group by post;
 
select post,min(salary) as '最小' from emp group by post;

每个部门的平均工资

select post,avg(salary) from emp group by post;

每个部门的工资总和

select post,sum(salary) from emp group by post;

每个部门的人数

    select post,count(age) from emp group by post;
    select post,count(salary) from emp group by post;
    select post,count(id) from emp group by post;
    select post,count(post_comment) from emp group by post;

在统计分组内个数的时候填写任意非空字段都可以完成计数,推荐使用能够唯一标识数据的字段比如id字段

　　　　　　聚合函数会自动将每一个分组内的单个数据做想要的计算,无需你考虑

3、group_concat

查询分组之后的部门名称和每个部门下所有的学生姓

select post, group_concat(name) from emp group by post;

select post,group_concat('hahha',name) from emp group by post;

还可以拼接

group_concat()能够拿到分组后每一个数据指定字段(可以是多个)对应的值

concat就是用来帮你拼接数据

group_concat（分组之后用）

concat（不分组时用）

查询每个员工的年薪

select name,salary*12 from emp;

相关免费学习推荐：php编程（视频）

The above is the detailed content of How to query mysql statement. For more information, please follow other related articles on the PHP Chinese website!

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AM

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

MySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Learning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AM

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

See all articles

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Assassin's Creed Shadows: Seashell Riddle Solution

3 weeks agoByDDD

What's New in Windows 11 KB5054979 & How to Fix Update Issues

2 weeks agoByDDD

Where to find the Crane Control Keycard in Atomfall

3 weeks agoByDDD

Assassin's Creed Shadows - How To Find The Blacksmith And Unlock Weapon And Armour Customisation

1 months agoByDDD

Roblox: Dead Rails - How To Complete Every Challenge

3 weeks agoByDDD

Hot Tools

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

Zend Studio 13.0.1

Powerful PHP integrated development environment

Safe Exam Browser

Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),