MySQL syntax collation

I've been studying for a few weeks MySQL, Let's sort out this blog MySQL Basic syntax , The two databases that appear in this blog university and world All in my resources , You can download it for free .

Let's take a look first MySQL The three most basic paragraphs of , for instance ： I want to instructor In the table salary Greater than 80000 Find out your teacher's name

select name  --  This is the last element to be screened , Be careful , stay MySQL All the results are in the form of tables , Even if this table has only one record 
from instructor -- from Statement indicates which table to query from 
where salary > 80000; -- where A statement is equivalent to a selection statement , Qualifications , Find the required records 

The query results are shown in the figure ！ These three statements are MySQL The three most important things in , Basically, all queries are inseparable from these three . But to satisfy complex queries , There must be more statements to support .

distinct： Repeat the results

/* Usage is as follows , If I want to be on the watch instructor in , Find out the names of all departments , It can be found in dept_name in , Some systems appear more than once ,
 Therefore, the name of the Department should be de duplicated */
select distinct dept_name
from instructor; --  There are no qualifications here , So there's no need to where sentence 

* ： Indicates all keys in the current table , The so-called key is actually the row field of the table , such as instructor Tabular ID,name,dept_name etc.

/* Similar to the previous example , I want to find out instructor In the table salary Greater than 80000 Teachers of , And display all the information of these teachers */
select *
from instructor
where salary > 80000; --  In fact, it's OK not to add a semicolon , A semicolon indicates that the execution is over , The following statement does not execute 

When we have more than one screening criteria , For example, I want to find out salary Greater than 80000 Teachers of , I also want to appoint a teacher in the computer department , That is, I want to find out salary Greater than 80000 Teachers of , Now we're going to use and sentence

select *
from instructor
where salary > 80000 and dept_name = 'Comp. Sci.';
/* There are also and The statement has or sentence ,or Represents or , That is, one condition can be met . For example, I want to find out that the salary is less than 60000 Or greater than 80000 Teachers of */
select *
from instructor
where salary > 80000 or salary < 60000;

Next, start the query between multiple tables , It is also our next difficulty . First, let's add the basic concept of key , I've already said what a key is , Here's the primary key , Also called master code , A primary key is a key that uniquely identifies a record . for instance , Our student number is the only one that can confirm our identity on campus , Even if someone in the school has the same name as me , I can also separate our identities by student number . thus it can be seen , The name is not the primary key , When you encounter a duplicate name , The name can't uniquely identify a student .

This is a teaches surface ,ID The key indicates the teacher's ID,course_id Indicates the of the course id,semester Indicates the semester of the class , If I want to find out what teachers teach , And put the teacher's name and course_id Show it .

/* This statement can realize , But why can it be realized ？ That's because both tables have a common primary key ：ID, Of course teaches There is more than one master 
 key , Let's see there's a... Next to the key key, All primary keys . But we don't care about the other keys , Just focus on ID Key will do , Because this is common to both tables . this 
 I would also like to mention two table queries , In fact, it is a record of one table to traverse the records of another table , When you find one instruction Of id etc. 
 On teaches Of id, Save this record in the result table */
SELECT NAME,course_id
where instructor.`ID` = teaches.`ID`;

Pave the way, so , Let's talk about nature join： Natural join . The change operation is very simple , Is to save records with equal primary keys in two tables , If two tables have multiple identical key key , Then it is necessary to ensure that each primary key is the same before it can be saved .

/* The above example can be queried by natural connection */
SELECT NAME,course_id
FROM instructor NATURAL JOIN teaches;
/* If you want to know what a naturally connected watch looks like , I'll satisfy you */
SELECT * --  Display all keys of the result table 
FROM instructor NATURAL JOIN teaches;

We can see , The columns of the table have been significantly increased , In fact, it is to integrate the keys of two tables . If you don't fully understand natural connections , Let me give you another example , For example, we have a student list
[ ‘ Xu Xiaoming ,1 Number ’,‘ Huang Xiaoshan ,2 Number ’], The primary key is the student number . There is also a score sheet
[‘1 Number , Chinese language and literature ：87, mathematics ：98’,‘2 Number , Chinese language and literature ：94, mathematics ：82’] , The student number is also the primary key of this table . When we want to print the student form , Just connect the two tables naturally , In the process of natural connection , Will integrate the same record of learning well into one record , Finally become
[‘1 Number , Xu Xiaoming , Chinese language and literature ：87, mathematics ：98’,‘2 Number , Huang Xiaoshan , Chinese language and literature ：94, mathematics ：82’] . In fact, natural connection is an optimized version of Cartesian product , You can know Cartesian product by yourself .

We can go through select To query variables or functions

SELECT 'dd';
SELECT 10*20;
SELECT NOW(),UNIX_TIMESTAMP();

Actually, I want you to pay attention to ,select The key after the statement , The key name that will become the result , Knowing this follow-up is very helpful for our renaming operation . For example, just this example ：

concat（）： This is a function that connects two keys , The usage is similar to python Of printf

/* adopt concat Function to connect so and dept_name*/
SELECT NAME,CONCAT(NAME,' : ',dept_name)
FROM instructor;

Not just the key name , Even the records of the keys will be linked together .

as： Change the name of the key or watch

/* Take the example just now */
SELECT NAME,CONCAT(NAME,' : ',dept_name) as 'name+dept'
FROM instructor;
/* Or change the name of the watch */
SELECT NAME
FROM instructor as i
WHERE i.salary > 70000; --  Note that after renaming , To reference the key of the table , To add a reference symbol ：.

After talking about it, let's query , Let's talk about creating tables ：create table. In fact, this command is rarely used , I prefer to create tables with mouse clicks , Instead of typing code to create .

/* Create a and student A table with the same structure as a table , What is the same structure , Namely ss_1 The key in the table is student equally */
CREATE TABLE ss_1 LIKE student;

Just now in the example like, Actually like It can also be used for character matching

/*like Statement for character matching */
SELECT dept_name
FROM department
WHERE building LIKE 'Watson%'; --  Used here %, Similar to... In regular ？, Represents any number of characters . This query is to find building
--  contains Watson The record of .

order by： Sort the keys in the result table , The default is ascending , That is, the records are incremented one by one from top to bottom

/*order by  Sort by an attribute */
/* This query is to find the teacher in the Physics Department , And sort by salary */
SELECT NAME , salary
FROM instructor
WHERE dept_name = 'Physics'
ORDER BY salary;
/* Since there is ascending order , Then there is a descending order */
SELECT NAME , salary
FROM instructor
WHERE dept_name = 'Physics'
ORDER BY salary DESC; -- DESC Representation of descending order 
/* We can also sort multiple keys */
SELECT *
FROM instructor
ORDER BY salary DESC , NAME ASC; --  Here is to sort wages in descending order first , When the salary is the same , According to the English initials ASC Code values are arranged in ascending order 
--  order 

Have you ever thought about why order by To be in where After the statement ？ because order by Statement to the result table ,where Statement is followed by the result table , This is also the same as I stressed before MySQL The query results are all tables ！ Even if the table has only one key or even one record ！

between and： Select the records in the interval

/* Range , Notice the closed interval , namely [90000 , 100000]*/
SELECT NAME
FROM instructor
WHERE salary BETWEEN 90000 AND 1000000;

When we query multiple conditions for , Sometimes you can match by key

/* Be similar to python Dictionary , The elements inside correspond one by one */
SELECT NAME,course_id
FROM instructor,teaches
WHERE (instructor.`ID`,dept_name) = (teaches.`ID`,'Biology');

The next blog will focus on queries between multiple tables , This is also the top priority , One of the difficulties ！