MySQL query statement simple operation example

This article uses examples to illustrate the simple operation of MySQL query statements. Share with you for your reference, the details are as follows:

Query

Create database and data table

-- Create database create database python_test_1 charset=utf8;
--Use database use python_test_1;
--students table create table students(
  id int unsigned primary key auto_increment not null,
  name varchar(20) default '',
  age tinyint unsigned default 0,
  height decimal(5,2),
  gender enum('male', 'female', 'neutral', 'confidential') default 'confidential',
  cls_id int unsigned default 0,
  is_delete bit default 0
);
--classes table create table classes (
  id int unsigned auto_increment primary key not null,
  name varchar(30) not null
);

Prepare the data

-- Insert data into the students table insert into students values
(0,'Xiaoming',18,180.00,2,1,0),
(0,'Xiao Yueyue',18,180.00,2,2,1),
(0,'Eddie Peng',29,185.00,1,1,0),
(0,'Andy Lau',59,175.00,1,2,1),
(0,'Huang Rong',38,160.00,2,1,0),
(0,'Fengjie',28,150.00,4,2,1),
(0,'王祖贤',18,172.00,2,1,1),
(0,'Jay Chou',36,NULL,1,1,0),
(0,'Cheng Kun',27,181.00,1,2,0),
(0,'Liu Yifei',25,166.00,2,2,0),
(0,'Venus',33,162.00,3,3,1),
(0,'Shizuka',12,180.00,2,4,0),
(0,'Guo Jing',12,170.00,1,4,0),
(0,'周杰',34,176.00,2,5,0);

-- Insert data into the classes table insert into classes values (0, "python_01 period"), (0, "python_02 period");

Query all fields

select * from table name;

example:

select * from students;

Query the specified field

select column1, column2,... from tablename;

example:

select name from students;

Use as to alias a field

select id as serial number, name as name, gender as gender from students;

You can give a table an alias using as

-- If it is a single table query, you can omit it to indicate select id, name, gender from students;
-- Table name. Field name select students.id, students.name, students.gender from students;
-- You can use as to give the table an alias select s.id,s.name,s.gender from students as s;

Eliminate duplicate rows

Use distinct before select to eliminate duplicate rows.

select distinct column1,... from tablename;

example:

select distinct gender from students;

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.

You may also be interested in:

A brief discussion on the problem of passing parameters when using in in pymysql query statements
MySQL fuzzy query statement collection
MySQL query statement process and basic concepts of EXPLAIN statement and its optimization
An example of using PHP to execute multiple SQL query statements simultaneously using mysqli
MySQL infrastructure tutorial: detailed explanation of the query statement execution process
MySql multi-condition query statement with OR keyword
Mysql multi-condition query statement with And keyword
Detailed explanation of MySQL limit usage and performance analysis of paging query statements
PHP mysqli query statement return value type example analysis
A complete collection of MySQL query statements
The most complete MySQL query statement collection
Introduction to the differences between paging query statements in Oracle, MySQL and SqlServe
Detailed explanation of the execution process of MySQL query statements

<<: Implementation of Nginx load balancing cluster

>>: vue+springboot realizes login function