Shulou(Shulou.com)05/31 Report--

This article will explain in detail the example analysis of DQL query language in SQL. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

DQL

DQL:data Query language data query language

Format: select [select] field 1, field 2 from table name where control condition

(distinct: when displaying the result, add distinct in front of the column field whether to remove the repeated column and remove the duplicate column.)

Student table

(1) query all the information in the table

SELECT * FROM student

(2) query the names of all students in the table and their corresponding English scores

SELECT name,english FROM student

Note: some fields can be displayed. If which column of data is displayed, you can write the field name directly.

(3) filter the repeated math scores in the table

SELECT DISTINCT math FROM student

(4) create a student class to add attribute id,name,sex,chinese,English,math

And randomly add 5 attributes.

Select * from student;- queries for information on students whose English is between 70 and 75-select * from student where english BETWEEN 70 AND 75-search for students whose language scores are 80 or 82 or 90-select * from student where chinese IN (80,82 or 90);-query the scores of all students with the initials l-select * from student where name like "1%";-query students with math greater than 80 and Chinese greater than 80-select * from student where math > 80 and chinese > 90 -output after sorting mathematics scores (default ascending order ASC)-output after select * from student order by math;- sorting mathematics scores (descending order DESC)-SELECT * FROM student order by math DESC;- specifies multiple fields to sort, sort by the first field first, and sort by the second field if the same-SELECT * FROM student ORDER BY math DESC,chinese DESC -after WHERE, you can add ORDER BY-- SELECT * from student where name like "% l" ORDER BY math DESC;- to display the first three lines SELECT * from student LIMIT 2 in the student table-display the 3rd to 5th lines in the student table, SELECT * from student LIMIT 2, 3;-2 indicates the offset, 3 indicates the number of rows displayed

Appendix: operators frequently used by ① in where

Note: logical operator precedence not > and > or

* ② select | {column1 | expression, column2 | expression, … } from table;select column as alias from table

Note:

Expression: mysql supports expression addition, subtraction, multiplication and division

As: indicates an alias for a column; and as can be omitted

-Association (1 to N)

Create table customer (id int PRIMARY KEY auto_increment, name varchar (20) not null, adress varchar (20) not null); create table orders (order_num varchar (20) PRIMARY KEY, price FLOAT not NULL, customer_id int,-- constraint cus_ord_fk foreign key (customer_id) REFERENCES customer (id) associated with customer); insert into customer (name,adress) values ("zs", "Beijing"); insert into customer (name,adress) values ("ls", "Shanghai") SELECT * from customer;INSERT INTO orders values (010, 30.5); INSERT INTO orders values (011, 60.5); INSERT INTO orders values (012, 120.5); SELECT * from orders

Primary key and unique ID

Unique unique identification

Primary key primary key (auto_increment setting auto-grow)-- UNIQUE table constraint uniqueness identification-- PRIMARY KEY primary key CREATE TABLE T4 (id INT PRIMARY KEY auto_increment, NAME VARCHAR (20) NOT NULL, gender CHAR (5) NOT NULL, idCard VARCHAR (20) UNIQUE-UNIQUE uniqueness identification); desc T4 NOT NULL insert into T4 (name,gender,idCard) VALUE ("zs", "man", "110"); insert into T4 (name,gender,idCard) VALUE ("ls", "woman", "112") This is the end of this article on "sample Analysis of DQL query language in SQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.