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

This article will explain in detail the example analysis of the basic grammar of MySQL. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Let's first take a look at the three most basic sentences of MySQL, for example: I want to find out the names of teachers with a salary greater than 80000 in the following instructor table

Related free learning recommendation: mysql video tutorial

Select name-- this is the last filtered element. Note that in MySQL, all the results are in the form of a table, even if there is only one record in this table-- from statement indicates which table to query where salary > 800000;-- where statement is equivalent to a select statement, qualification, to find the desired record.

The query results are as shown in the picture! These three statements are the three most important items in MySQL, and basically all queries are inseparable from them. But if you want to satisfy complex queries, you must have more statements to support them.

Distinct: the usage of deduplicating / * for the result is as follows. If I want to find out the names of all the departments in the above table instructor, I can find that some of the systems appear more than once in dept_name, so remove the duplicates of the name of the system * / select distinct dept_namefrom instructor -- there is no qualification here, so there is no where statement *: indicates all the keys in the current table. The so-called keys are actually the row fields of the table, such as the ID,name,dept_name of the instructor table / * similar to the previous example. I want to find teachers in the instructor table whose salary is greater than 80000 and display all the information of these teachers * / select * from instructorwhere salary > 80000 Actually, you don't have to add a semicolon. The semicolon indicates that the execution ends here, and the following statement is not executed. When we filter more than one condition, for example, I want to find teachers whose salary is greater than 80000, I also want to specify teachers in the computer department, that is, I want to find teachers in the computer department whose salary is greater than 80000. At this time, we need to use the and statement select * from instructorwhere salary > 80000 and dept_name = 'Comp. Sci.';/* also has an and statement, there is an or statement, or represents or, that is, a condition is satisfied. For example, I want to find teachers whose salary is less than 60000 or more than 80000 * / select * from instructorwhere salary > 80000 or salary

< 60000;接下来开始进行多个表之间的查询，也是我们接下来的难点。先来补充一下键的基本概念，之前已经说了什么是键，这里说一下主键，也叫主码，主键表示能唯一确定某一条记录的键。举个例子，我们的学号就是唯一能确定我们在校园的身份，就算学校中有人与我重名，我也能通过学号将我们身份分开。由此可见，名字不是主键，当遇到重名，名字就不能唯一确定某个学生了。 这是teaches表，ID键表示老师的ID，course_id表示课程的id，semester表示开课的学期，假如我想找出教师都教了什么课，并把教师名字和course_id显示出来。 /*这条语句可以实现，但是请问为什么可以实现呢？那是因为两个表都有共同的主键：ID，当然teaches不止这一个主键，我们看键旁边有个key，都是主键。但是我们不用管其他键，只要关注ID键就可以了，因为这是两个表中共有的。这里我还要特意提一下两个表查询，其实是一个表的一个记录去遍历另外一个表的记录，当找到某一条instruction的id等于teaches的id，就将这条记录保存到结果表中*/SELECT NAME,course_idwhere instructor.`ID` = teaches.`ID`; 铺垫了这么就，我们来讲一下nature join：自然连接。改操作很简单，就是把两个表中的主键相等的记录保存，如果两个表有多个相同的key键，那么就要保证每个相同主键相同才能保存。 /*上述例子完全可以用自然连接来查询*/SELECT NAME,course_idFROM instructor NATURAL JOIN teaches;/*如果你想知道自然连接后的表长啥样，我满足你*/SELECT * -- 显示结果表的所有键FROM instructor NATURAL JOIN teaches; 我们可以看到，表的列明显增加了，其实就是将两个表的键整合在一起。如果你还不能完全理解自然连接，我再举个例子，比如我们有个学生表 [ '徐小明 ，1号'，'黄小珊 ，2号']，其中主键是学号。还有一个成绩表 ['1号，语文：87，数学：98'，'2号，语文：94，数学：82'] ，其中学号也是这个表的主键。当我们想打印学生表，只要将两个表自然连接即可，自然连接过程中，会将同一个学好的记录整合成一个记录，最后变成 ['1号，徐小明，语文：87，数学：98'，'2号，黄小珊，语文：94，数学：82'] 。其实自然连接就是笛卡尔积的优化版，大家可以自行了解笛卡尔积。 我们可以通过select来查询变量或者函数SELECT 'dd';SELECT 10*20;SELECT NOW(),UNIX_TIMESTAMP(); 这里其实想让大家注意，select语句后面的键，会成为结果的键名，知道这个后续对于我们的改名操作有很大帮助。比如刚刚这个例子：

Concat (): this is a function that connects two keys, using printf/* similar to python to connect with dept_name*/SELECT NAME,CONCAT (NAME,':', dept_name) FROM instructor through the concat function.

Not only the key name, but also the records of the key are connected together.

As: rename the key or table / * for example, in the example * / SELECT NAME,CONCAT (NAME,':', dept_name) as' name+dept'FROM instructor;/* or rename the table * / SELECT NAMEFROM instructor as iWHERE i.salary > 7000;-- Note that after renaming, if you want to reference the key of the table, add the reference symbol:.

Having talked about querying like this, here's how to create a table: create table. In fact, this command is rarely used, and I prefer to create a table with a mouse click rather than typing code to create it. / * create a table with the same structure as the student table. What is the same structure, that is, the key in the ss_ 1 table is the same as student * / CREATE TABLE ss_1 LIKE student;. Like has just appeared in the example. In fact, like can also be used for character matching / * like statements for character matching * / SELECT dept_nameFROM departmentWHERE building LIKE 'Watson%';--% is used here, which is similar to that in the rule? That represents any number of characters The purpose of this query is to find out the records in building-- that contain Watson. Order by: sort the keys in the result table. The default is ascending order, that is, records are increased one by one from top to bottom / * order by is sorted by a certain attribute * / / * this query is to find teachers in the physics department and sort them by salary * / SELECT NAME, salaryFROM instructorWHERE dept_name = 'Physics'ORDER BY salary;/*. Since there is ascending order, there is descending order * / SELECT NAME, salaryFROM instructorWHERE dept_name =' Physics'ORDER BY salary DESC. -- DESC indicates descending order / * We can also sort multiple keys * / SELECT * FROM instructorORDER BY salary DESC, NAME ASC;-- here is the descending order of wages, and when the wages are the same, sort by the ascending order of the ASC code value of the first letter of the English alphabet.

Have you ever wondered why order by comes after the where statement? Because the order by statement is for the result table, the result table is not available until after the where statement, which is similar to my previous emphasis on MySQL query results that everything is a table! Even if this watch has only one key or even one record!

Between and: select the range of records / * within the interval. Note that it is a closed interval, that is, [90000, 100000] * / SELECT NAMEFROM instructorWHERE salary BETWEEN 90000 AND 1000000. When we query multiple conditions, we can sometimes match / * a dictionary similar to python by key, and the elements in it correspond to * / SELECT NAME,course_idFROM instructor,teachesWHERE one by one. Dept_name) = (teaches.`ID`, 'Biology') This is the end of this article on "sample Analysis of MySQL basic Grammar". 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.