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

What are the mysql statements in the database foundation? aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Database and database software:

Database is a data container, which can be understood as a file, and database software is a database management system.

Provide users with operations on database files

Schama (mode):

Information about the layout and characteristics of databases and tables

Column (column):

A table consists of columns in which information about a part of the table is stored.

Line (row):

The data in the table is stored by row

Primary key:

Each row in the table should have a column that uniquely identifies itself, called the primary key

The primary key can consist of a single column or multiple columns

SQL:

Structured query language for communicating with databases

Query database and table commands:

SHOW DATABASES

SHOW TABLES

SHOW COLUMNS FROM COLLATIONS; / / Show all columns in COLLATIONS

Auto_increment / / automatically increments, each time a row is added, the value is automatically increased by one

SELECT prod_name FROM products; / / retrieve prod_name from prducts

SELECT prod_id,prod_name FROM products; / / retrieve multiple columns

SELECT DISTINCT vend_id from products; / / returns different values in a column (filter out the same values)

SELECT prod_name FROM products LIMIT 5; / / limit output up to 5 lines

SELECT prod_name FROM products LIMIT 4pr 5; / / limit output up to 5 lines from line 4

SELECT products.prod_name FROM products; / / fully qualified

SELECT products.prod_name FROM crashcourse.products; / / fully qualified (columns and tables)

SELECT prod_name FROM products ORDER BY prod_name; / / sort

SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price,prod_name; / / sort multiple columns (reorder prod_name if there are duplicates in prod_price)

SELECT prod_name FROM products ORDER BY prod_name DESC/ESC; / / sort (ascending or descending)

SELECT prod_id,prod_price,prod_name FROM products WHERE prod_price = 2.5; / / filter using where (= "betown, etc.)

SELECT prod_id,prod_price,prod_name FROM products WHERE prod_price BETWEEN 5 AND 10 Placement / in the middle

SELECT prod_id,prod_price,prod_name FROM products WHERE prod_price vend_id = 1003 AND prod_price=2

Using ORDER BY to sort groups

SELECT order_num,SUM (quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM (quantity*item_price) > = 50 ORDER BY ordertotal

Subqueries can be used to reduce the number of statements in a query

For example:

SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'; / / return result 20005pjm 20007

SELECT cust_id FROM orders WHERE order_num IN (20005pm 20007)

SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2')

Calculated fields use subqueries:

SELECT cust_name,cust_state, (SELECT COUNT (*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name

Connection:

(primary key and foreign key)

The purpose of dividing tables is to better store and improve scalability, but the join method has to be adopted when using select queries.

SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id'

Table aliases can reference the same table more than once in a single SELECT statement

SELECT prod_id,prod_name FROM products WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR')

Self-connection mode:

SELECT p1. Proddistribuid. FROM products AS p1. Products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR'

External connection

Joins contain rows that have no associated rows in the related table, and this type of join becomes an external join

SELECT customers.cust_id,orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id

Left outward connection and right outward connection

The result set of a left-out join includes all rows of the left table specified in the LEFT OUTER clause, not just the rows that are matched by the join

RIGHT JOIN and RIGHT OUTER JOIN

A right-to-outward join is a reverse join of a left join, which returns all rows of the right table

FULL JOIN or FULL OUTER JOIN

The full outer join returns all rows of the left and right tables, and when one row does not match in another table, the

The selected table column contains null values, and if there are matching rows between tables, the entire result set contains the data values of the base table.

Joins with aggregate functions

SELECT customers.cust_name,customers.cust_id,COUNT (orders.order_num) AS num_ord FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id

This SELECT statement uses INNER JOIN to associate the customers and orders tables with each other. The GROUP BY clause groups data by customer, so the function calls the COUNT (order.order_num) order technology for each customer and returns it as a num_ord.

Aggregate functions can also be easily used with other joins.

SELECT customer.cust_name,customer.cust_id,COUNT (order.oder_num) AS num_ord FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id

Combined query:

Multiple queries (multiple SELECT statements) and return the results as a single query result set

Application scenarios for combined queries:

Return data with similar structure from different tables in a single query

Execute multiple queries against a single table and return data by a single query

If you use WHERE:

SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price

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.