Mysql: DQL(Data Query Language)

Learning Materials:

www.bilibili.com/video/BV1NJ…

Learn data preparation – Execute the following SQL statement or download the SQL file using the database visualization file import: password: IV5w:

Link: pan.baidu.com/s/1oNJqc9BN… Password: iv5w

create database if not exists `school`;
Create a school database
use `school`;Create student table
drop table if exists `student`;
create table `student`(
`studentno` int(4) not null comment 'student id',
`loginpwd` varchar(20) default null,
`studentname` varchar(20) default null comment 'Student name',
`sex` tinyint(1) default null comment 'Sex, 0 or 1',
`gradeid` int(11) default null comment 'Grade Number',
`phone` varchar(50) not null comment 'Contact number, allowed to be empty',
`address` varchar(255) not null comment 'Address, allowed to be null',
`borndate` datetime default null comment 'Time of birth',
`email` varchar (50) not null comment 'Email account allowed to be empty',
`identitycard` varchar(18) default null comment 'Id Number'.primary key (`studentno`),
unique key `identitycard`(`identitycard`),
key `email` (`email`)
)engine=myisam default charset=utf8;

Create a grade table
drop table if exists `grade`;
create table `grade`(
	`gradeid` int(11) not null auto_increment comment 'Grade Number',
  `gradename` varchar(50) not null comment 'Grade Name'.primary key (`gradeid`)
) engine=innodb auto_increment = 6 default charset = utf8;

Create a subject table
drop table if exists `subject`;
create table `subject`(
	`subjectno`int(11) not null auto_increment comment 'Course Number',
    `subjectname` varchar(50) default null comment 'Course Name',
    `classhour` int(4) default null comment 'school',
    `gradeid` int(4) default null comment 'Grade Number'.primary key (`subjectno`)
)engine = innodb auto_increment = 19 default charset = utf8;

Create a score table
drop table if exists `result`;
create table `result`(
	`studentno` int(4) not null comment 'student id',
    `subjectno` int(4) not null comment 'Course Number',
    `examdate` datetime not null comment 'Test Date',
    `studentresult` int (4) not null comment 'Exam results',
    key `subjectno` (`subjectno`)
)engine = innodb default charset = utf8;


Insert student data and add the rest
insert into `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
values
(1000.'123456'.'zhang wei'.0.2.'13800001234'.'Beijing Chaoyang'.'1980-1-1'.'[email protected]'.'123456198001011234'),
(1001.'123456'.'zhao jiang'.1.3.'13800002222'.'Shenzhen, Guangdong'.'1990-1-1'.'[email protected]'.'123456199001011233');

-- Insert score data -- Insert score data -- insert score data -- insert score data
insert into `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
values
(1000.1.'the 2013-11-11 16:00:00'.85),
(1000.2.'the 2013-11-12 16:00:00'.70),
(1000.3.'the 2013-11-11 09:00:00'.68),
(1000.4.'the 2013-11-13 16:00:00'.98),
(1000.5.'the 2013-11-14 16:00:00'.58);

Insert grade data
insert into `grade` (`gradeid`,`gradename`) values(1.'one'), (2.'second'), (3.'three'), (4.'senior'), (5.'Preparatory class');


-- Insert subject data
insert into `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values
(1.'Advanced Mathematics -1'.110.1),
(2.'Advanced Mathematics -2'.110.2),
(3.'Advanced Mathematics -3'.100.3),
(4.'Advanced Mathematics -4'.130.4),
(5.'C语言-1'.110.1),
(6.'C语言-2'.110.2),
(7.'C语言-3'.100.3),
(8.'C语言-4'.130.4),
(9.'Java Programming -1'.110.1),
(10.'Java Programming -2'.110.2),
(11.'Java Programming 3'.100.3),
(12.'Java Programming 4'.130.4),
(13.'Database Structure-1'.110.1),
(14.'Database Structure-2'.110.2),
(15.'Database structure-3'.100.3),
(16.'Database Structure -4'.130.4),
(17.'c # basis'.130.1);
Copy the code

Common query statements:

-- DQL 

-- 1. Query all students
select * from student;

-- 2. Check all students' names and phone numbers
SELECT `studentname`, `phone` FROM student;

-- 3. Use of aliases
SELECT s.studentname FROM student AS s;

-- 4. Concat functions
SELECT CONCAT('s name,,`studentname`) FROM student;

-- 5. Repeat the query
SELECT DISTINCT `studentNo` FROM result;

-- 6. Query the system version
SELECT VERSION();

-- 7. Evaluate the expression
SELECT 100%3 AS'expression result';-- 8. Query the self-growing step
SELECT @@auto_increment_increment;

-- 9. Results after students score extra points in the exam
SELECT `studentNo`,`studentResult` + 1 AS '➕ points' FROM result;

Select * from '95' where '95' = '100'
SELECT * FROM result WHERE `studentResult` BETWEEN 95 AND 100;
SELECT * FROM result WHERE `studentResult`> =95 AND `studentResult`< =100;
SELECT * FROM result WHERE `studentResult` IN (95.96.97.98.99.100);

-- 11. Fuzzy query, Mr. Zhang
SELECT * FROM student WHERE studentname LIKE 'a %';

-- 12. Check if the address is empty
SELECT * FROM student WHERE address IS NULL;

SQL > select * from page; select * from page
SELECT * FROM result LIMIT 2.3;

-- 14. Sort query DESC in reverse order
SELECT * FROM result ORDER BY `studentResult` DESC;

-- 15. Grouping
SELECT * FROM result GROUP BY `studentNo`;

Join table -- join table: if there is at least one match, return row. On join table, where condition
SELECT s.studentNo, studentName, `subjectNO`,StudentResult  FROM student AS s 
join result as r where s.studentno = r.studentno;

-- 17. Right link: All values are returned from the right table, even if student does not match the right table
SELECT s.studentNo, studentName, `subjectNO`,StudentResult  FROM student AS s 
RIGHT JOIN result as r ON s.studentno = r.studentno;

-- 18. Left link: All values are returned from the left table, even if there is no match in the right table. The student left the table
SELECT s.studentNo, studentName, `subjectNO`,StudentResult  FROM student AS s 
LEFT JOIN result as r ON s.studentno = r.studentno;

-- 19. Split the self-join into two tables
SELECT a.categoryName AS 'Primary classification', b.categoryName AS 'Secondary classification' FROM `category` AS a, `category` AS b 
WHERE a.categoryName = b.categoryName;

-- 20. Aggregate function: Max min sum avg
SELECT MAX(`studentResult`) FROM result;

-- 21. Subquery
SELECT * FROM result WHERE studentNo IN (
SELECT `subjectNo` FROM `subject`
);

-- Common functions; Get the current time
SELECT NOW();
Copy the code