MySQL Query Statement Simple Action Example

The example of this paper tells the simple operation of MySQL query statement. Share it for your reference, as follows:

Query

Create databases, data tables

--  Create a database 
create database python_test_1 charset=utf8;
--  Working with a 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 ',' Secrecy ') default ' Secrecy ',
  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 data

--  Toward students Insert data into a table 
insert into students values
(0,' Xiao Ming ',18,180.00,2,1,0),
(0,' Xiaoyueyue ',18,180.00,2,2,1),
(0,' Eddie Peng Yuyan ',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,' Sister Feng ',28,150.00,4,2,1),
(0,' Joey Wong ',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,' Zhou Jie ',34,176.00,2,5,0);

--  Toward classes Insert data into a 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  Column 1, Column 2,... from  Table name ;

Example:

select name from students;

Alias fields using as

select id as  Serial number , name as  Name , gender as  Gender  from students;

Tables can be aliased through as

--  If it is a single table query   You can omit the indication 
select id, name, gender from students;
--  Table name . Field name 
select students.id,students.name,students.gender from students;
--  It can be passed through  as  Alias a table  
select s.id,s.name,s.gender from students as s;

Eliminate duplicate lines

Use distinct before columns after select to eliminate duplicate rows

select distinct  Column 1,... from  Table name ;

Example:

select distinct gender from students;

For more readers interested in MySQL related content, please check the topics on this site: "MySQL Query Skills Encyclopedia", "MySQL Common Function Summary", "MySQL Log Operation Skills Encyclopedia", "MySQL Transaction Operation Skills Summary", "MySQL Stored Procedure Skills Encyclopedia" and "MySQL Database Lock Related Skills Summary"

I hope this article is helpful to everyone's MySQL database.