MySQL Database second bomb

MySQL database operations on tables

Table records of additions and deletions to change

1. Add a table

You must have a table structure before inserting a record!

CREATE TABLE Score (
  ID int PRIMARY KEY auto_increment,
  Name VARCHAR (20),
  Chinese FLOAT (4,2),
  Math FLOAT (4,2),
  中文版 FLOAT (4,2)
)

----Inserting table records

--Inserting a data insert

INSERT [INTO] table_name (field name,...) Values (value ...);

--insert more than one data
INSERT [INTO] table_name (field name,...) Values (Value ...), #INTO to
(Value ....),
....
(Value ....);

INSERT into score (name,chinese,math,english) VALUES
(' Li Lei ', 95.5,90.5,50.5),
(' Li two eggs ', 90,66.5,45),
(' Li two dog ', 95.5,77.5,86),
(' Li Xiaohu ', 50.5,65.5,85),
(' Li Xiaohong ', 85.5,75.5,75),
(' Jian Li ', 65.5,88.5,88),
(' Li Wence ', 43.5,75.5,99),
(' Ma Dongmei ', 88.5,60.5,88);
Insert [into] tab_name set field name = value; This is also a way to insert records but clumsy!
--Modify Table records

Update Tab_name set Field1=value1,field2=value2,...... [WHERE statement]

/* The update syntax can update the columns in the original table row with the new values.
The SET clause indicates which columns to modify and which values to give.
The WHERE clause specifies which rows should be updated. If there is no WHERE clause, all rows are updated. */

--Delete Table records

Delete from Tab_name [where ...]

/*
Delete data from the entire table if you do not follow the WHERE statement
Delete can only be used to delete a row of records
Delete statement can only delete the contents of the table, cannot delete the table itself, want to delete the table, with drop
TRUNCATE table can also delete all the data in the table, the word sentence first destroys the table, and then creates a new table. Data that is deleted in this way cannot be
Recovery in a transaction. */

Delete from score where name= ' Li Lei '; #删除李雷的这条记录.

Delete from score; #删除表内所有记录

--use Truncate to delete records in the table.

Truncate from score; Delete records from the table, but this is done by deleting the entire table (including the table structure) before rebuilding the same table mechanism. This method is relatively fast for large data volume table effect is significant!

------Query Table Records

In database operations, querying table records is what we usually do and the query's conditional method needs to be implemented according to different requirements, so it is more important.

--Query syntax:

SELECT *|field1,filed2 ... From Tab_name
WHERE condition
GROUP by Field
Having screening
ORDER by Field
Limit number of bars

--(1) SELECT [DISTINCT] *|field1,field2, ... from tab_name
--where from specifies which table to filter from, * means to find all columns, or to specify a column
--The table explicitly specifies the column to find, and distinct is used to reject duplicate rows.

Example: SELECT * from score; Check all table records for this score table!

--Check the names of all the students in the table and the corresponding English scores.
Select Name,english from Score;
--Filter the repeating data in the table.
Select DISTINCT 中文版, name from score;

--(1) Check all students ' math scores and overall score
Select name Student name, Math score, chinese+math+english total from  score;
--(2)   Query the language and math scores of all students, sorted from highest to lowest
Select name Student name, Chinese+math language and math score and from score  ORDER by  Chinese+math desc;
--(3) The name of the student who inquires the highest grade
Select Name, (Ifnull (chinese,0) +ifnull (math,0) +ifnull (english,0))
                   Total score ORDER BY total DESC LIMIT 1;

------The strong point of the WHERE clause:

--Check the student's grade named XXX

SELECT * FROM score where name= ' Li Lei ';

-The where sentence can be used:

--Comparison operators:
> < >= <= <>! =
Between and 100 values from 10 to 20
In (80,90,100) value is 10 or 20 or 30
Like ' yuan% '
/*
The pattern can be either% or _,
If percent is any number of characters, such as the Tang Monk, Tang Guoqiang
If _ is a word 唐 _, only the Tang priest in accordance with. Two _ means two characters: __
*/

--Logical operators
logical operators and or not can be used directly in multiple conditions

----ORDER BY sort

Specifies the sorted column, which can be the column name in the table, or the alias specified after the SELECT statement.

--Select *|field1,field2 ... from tab_name order by field [asc| DESC]

ASC Ascending, desc Descending, where ASC is the default value the ORDER BY clause should be at the end of the SELECT statement.

Example: Query the name of the student with the highest grade

Select Name, (Ifnull (chinese,0) +ifnull (math,0) +ifnull (english,0))
                   Total score ORDER BY total DESC LIMIT 1;

---GROUP by group
Prepare the table structure and record first.

CREATE TABLE Order_menu (
ID INT PRIMARY KEY auto_increment,
Product_Name VARCHAR (20),
Price FLOAT (6,2),
Born_date date,
Class VARCHAR (20)
);

INSERT into Order_menu (product_name,price,born_date,class) VALUES
("Apple", 20,20170612, "fruit"),
("Banana", 80,20170602, "fruit"),
("Kettle", 120,20170612, "electric"),
("Quilt", 70,20170612, "bedding"),
("Sound", 420,20170612, "electrical"),
("Bed linen", 55,20170612, "bedding"),
("Strawberry", 34,20170612, "fruit");

--note that each group will only show the first record when grouped by grouping criteria

The group by sentence can then be followed by multiple column names, or with the HAVING clause, to filter the results of GROUP by.

--Filter By Location field
SELECT * FROM Order_menu Group by 5;

--Exercise: Display the sum of the prices of each group of items by grouping them by class name
Select Class,sum from Order_menu group by class;

---Practice: Display items with a total of more than 150 of each set of items, grouped by class name
Select Class,sum (Price) from Order_menu Group by class
Have SUM (price) >150;

/*
Having and where both can further filter the results of the query, the differences are:
<1>where statements can only be used for filtering before grouping, having the ability to filter after grouping;
<2> where the where statement can be replaced with having
Aggregate functions can be used in <3>having, where they do not.
*/

--Group_concat () function
SELECT Id,group_concat (name), Group_concat (JS) from the Examresult GROUP by ID;

----Aggregation Functions

--<1> all records in the statistics table

--count (column name): Number of rows counted
--How many students are there in a class? Find all the students first, then use the Count package.
Select COUNT (*) from Examresult;
--The number of students who count JS scores more than 70?
Select COUNT (JS) from Examresult where js>70;
--What is the number of people with a total score greater than 280?
Select count (name) from Examresult
Where (Ifnull (js,0) +ifnull (django,0) +ifnull (openstack,0)) >280;
--Note: Count (*) counts all rows; Count (field) does not count null values.

--SUM (column name): Statistics The contents of rows that meet the criteria and
--Statistic A class JS total? First find out all the JS scores, and then use the sum package
Select JS as JS total from Examresult;
Select sum (JS) as JS total from Examresult;
--statistics on the total scores of each section of a class
Select sum (JS) as JS total,
Sum (Django) as Django Total,
SUM (OpenStack) as OpenStack from Examresult;

--statistics on the total scores of a class of subjects
Select SUM (ifnull (js,0) +ifnull (django,0) +ifnull (database,0))
As total from Examresult;
--Statistics of a class JS score average
Select SUM (JS)/count (*) from Examresult;
-Note: Sum works only on numeric values, otherwise it will be an error.

--AVG (column name):
--Ask a class JS average score? First find out all the JS points, and then use AVG package.
Select AVG (ifnull (js,0)) from Examresult;
--The average score of a class total
Select AVG ((Ifnull (js,0) +ifnull (django,0) +ifnull (database,0)))
From Examresult;
--Max, Min
--the highest and lowest score of class (the numerical range is particularly useful in statistics)
Select Max ((Ifnull (js,0) +ifnull (django,0) +ifnull (openstack,0)))
Highest score from Examresult;
Select Min ((Ifnull (js,0) +ifnull (django,0) +ifnull (openstack,0)))
Minimum score from Examresult;
----the name and price of the product with the highest price in the shopping table
---SELECT ID, MAX (price) from Order_menu;--id and high-priced items is a commodity?

SELECT MAX from Order_menu;

-Note: null and all number calculations are NULL, so you need to convert NULL to 0 with Ifnull!
-------ifnull (js,0)

----Limit number of query bars  

SELECT * from Examresult limit 1;

SELECT * from Examresult limit 2, 5; --Skip the first two entries to show the next five records

SELECT * from Examresult limit 2, 2;

---Regular expressions match the data you want

SELECT * from-employee where Emp_name REGEXP ' ^yu '; #查询以yu开头的数据SELECT * from employee where Emp_name REGEXP ' yun$ '; #查询以yun结 Tail Data SELECT * FROM employee WHERE emp_name REGEXP ' m{2} '; query for data conforming to two m.

MySQL Database second bomb