MySQL single-table query (FAT teacher)

Data preparation
drop table if exists class;
CREATE TABLE Class (
class_no Int (2) unsigned Zerofill primary key auto_increment comment ' class number ',
Class_name varchar (+) NOT null comment ' class name '
);
INSERT into class values (1, ' Pui gifted class ');
INSERT into class values (2, ' normal class ');

drop table if exists student;
CREATE TABLE Student (
Stu_no Int (2) unsigned Zerofill primary key auto_increment comment ' student number ',
Stu_name varchar (+) NOT null comment ' learner name ',
Stu_sex varchar (3) NOT null comment ' trainee sex ',
Stu_age tinyint (2) unsigned zerofill comment ' student age ',
class_no Int (2) unsigned zerofill comment ' class number ',
Foreign KEY (CLASS_NO) references class (CLASS_NO)
);
INSERT into student values (01, ' Li Bai ', ' male ', 18, 01);
INSERT into student values (02, ' Du Fu ', ' Male ', 20, 01);
INSERT into student values (03, ' Zhang Fei ', ' Male ', 32, 02);
INSERT into student values (04, ' Han Xin ', ' Male ', 26, 02);
INSERT into student values (05, ' Dragon ', ' Male ', 27, 02);
INSERT into student values (06, ' Big Joe ', ' female ', 17, 01);
INSERT into student values (07, ' Little Joe ', ' female ', 16, 01);
INSERT into student values (08, ' Little Joe ', ' female ', 16, 01);
INSERT into student values (09, ' Guan brother ', ' male ', 32, 02);
INSERT into student values (10, ' Liu Bei ', ' Male ', $, null);
ALTER TABLE student drop foreign key ' student_ibfk_1 ';


1: Sort [ORDER by]
Example: SELECT * from student;
Example: SELECT * FROM Student order by Stu_age; --sort the age of the students
Example: SELECT * FROM student ORDER by stu_age Desc; --desc is the order from the big to the small, the default is ascending
Example: SELECT * FROM Student order by stu_age ASC; --asc means from small to sort, the default is ASC
Example: SELECT * FROM Student ORDER by stu_age ASC, Stu_no desc; --If a field is not size-sensitive, you can distinguish it by the second field

Character set and proofing rules
Example: show character set; --View the character sets supported by the MySQL database
Example: Show collation;
Example: Show collation like ' gbk% ';

PS: Each character set will provide one or more proofing rules, the general collation rule naming is: Character Set _ domain name _ci|cs|bin;
CI: indicates case insensitive;
CS: denotes case-sensitive;
Bin: Indicates the use of coding comparisons; [a-z]:[97-122] [a-z]:[65-90]

CREATE TABLE T_1 (
Name varchar (20)
) Character set GBK collate gbk_chinese_ci;
INSERT into t_1 values (' C '), (' A '), (' B ');
SELECT * from T_1 order by name;

CREATE TABLE T_2 (
Name varchar (20)
) Character set GBK collate gbk_bin;
INSERT into t_2 values (' C '), (' A '), (' B ');
SELECT * from T_2 order by name;
*******************************************************************************************************

2: [Limit] limits the number of records obtained
Syntax: Limit offset, row count. Offset represents the index value (starting at 0), row count indicates the number of records to get
SELECT * from student;
Example: SELECT * FROM student limit 1, 5; --Starting from the second data to get 5 data
Example: SELECT * FROM student limit 1, 50; --If the data you want to get exceeds the total number of records, you will get all
Example: SELECT * from student limit 5; --if there is only one parameter, the same indicates the number of records to get, starting from 0
*******************************************************************************************************

3: [DISTINCT] remove duplicate records
PS: Only the field values that you want to query are exactly the same, not the same as the partial fields
Example: SELECT DISTINCT stu_name from student;
Example: SELECT DISTINCT Stu_name, stu_age from student;
Example: SELECT DISTINCT Stu_no, Stu_name, stu_age from student; --it's not going to be heavy, because there's no duplicate records.

Example: Select All Stu_name, stu_age from student; --all In contrast to distinct, all means that all records are fetched, but the default is all

MySQL single-table query (FAT teacher)