MySQL operations on records in a table

add: Insert Record

CREATE Table Employee_new (
ID INT PRIMARY KEY auto_increment,
Name VARCHAR () is not NULL UNIQUE,
Birthday VARCHAR (20),
Salary FLOAT (7,2)
) CHARACTER SET UTF8;

INSERT into Employee_new (name, birthday, salary) VALUES (' Yuan ', ' 1990-09-09 ', 9000),
(' Alex ', ' 1989-08-08 ', 3000),
(' Xialv ', ' 1988-07-07 ', 1000),
(' Alvin1 ', ' 1993-04-20 ', 3000),
(' alvin2 ', ' 1995-05-12 ', 5000);

INSERT into employee_new SET id=12,name= "alvin3";
Note: The table data three, the ID is three-way, and suddenly inserted a id=7, then the next time as the primary key word growth of the ID will grow from a few? (Starting from 7)

Deleting: Deleting table records
DELETE from Employee_new WHERE id=12;
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 deleted in this way cannot be
Recovery in a transaction.
DELETE from Employee_new WHERE name= "Alex";
Delete from employee_new; remove all records from the table
Truncate employee_new; Delete all records in a table
Change: Modify the Record table

UPDATE employee_new SET birthday= ' 1989-10-24 ' WHERE id=1;
UPDATE employee_new SET salary=salary+1000 WHERE name= "Yuan";

The update syntax can update columns in existing table rows with new values
SET words indicate which columns to modify and which values to give
The WHERE clause specifies which rows should be updated, and if there is no WHERE clause, all rows are updated


To query an expression:

SELECT *|field1,field2,... From Tab_name
WHERE condition
GROUP by Field
Having screening
Order By Field sort DESC (decrement) ASC (increment)
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.

--Query the information of all students in the table
SELECT * from Examresult;
--The names of all students in the enquiry form and the corresponding English scores
SELECT Name,js from Examresult;
--Filter repeating data in a table
SELECT DISTINCT js,name from Examresult;
(2) SELECT can also use expressions, and can use: field as Alias, or field alias.
--Add 10 extra-long points to all student scores.
SELECT name,js+10,django+10,openstack+10 from Examresult;
--count each student's total score.
SELECT Name,js+django+openstack from Examresult;
--use aliases to represent student totals.
SELECT name,js+django+openstack total score from Examresult;
(3) Use the WHERE clause to filter the query
--Check the student's grade named XXX
SELECT * from Examresult WHERE name= ' yuan ';
--Query students with English scores greater than 90 points
SELECT Id,name,js from Examresult WHERE js>90;
--Query all students with a total score greater than 200
Select Name,js+django+openstack as Total from
Examresult where js+django+openstack>200;
-The where sentence can be used:
Comparison operators:
> < >= <=! =
Between and 100 values from 80 to 100
In (80,90,100) value is 80 or 90 or 100
Like ' yuan% ' matches the beginning of yuan, and% represents any number of characters, _ denotes one character. Two characters with two underscores __
--Logical operators
logical operators and OR not can be used directly in multiple conditions
(4) Order by 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
--Sorting the total score from high to low order output
Select Name, (Ifnull (js,0) +ifnull (django,0) +ifnull (database,0))
Total from Examresult order by total desc;
--ifnull (js,0) If JS is empty, it is recorded as 0

--The output of the ranking of students with surname Li
SELECT name, (Ifnull (js,0) +ifnull (django,0) +ifnull (openstack,0)) Total from Examresult
WHERE name like ' a% '
ORDER by Total DESC;

(5) Group by group query:
--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.

* * Pay attention to attention!
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) from the AA group by ID;
--Group by ID, print the value of the Name field on one line, comma delimited (delimited)
------+--------------+--------+------------+--------------+
| ID | Product_Name | Price | Born_date | Class |
+------+--------------+--------+------------+--------------+
| 1 |  Apple | 20.00 | 2017-06-12 | Fruit |
| 1 |  Banana | 80.00 | 2017-01-02 | Fruit |
| 2 | Kettle | 120.00 | 2017-06-12 | Electrical Appliances |
| 0 |  Quilt Cover | 70.00 | 2017-01-12 | Bedding |
| 2 | Audio | 420.00 | 2017-01-12 | Electrical Appliances |
| 0 |  Bed Linen | 55.00 | 2017-06-12 | Bedding |
| 1 |  Strawberry | 34.00 | 2017-01-12 | Fruit |
+------+--------------+--------+------------+--------------+
Select Id,group_concat (name) from the AA group by ID;
+------+----------------------------+
| ID | Group_concat (product_name) |
+------+----------------------------+
| 0 | Quilt cover, Bed linen |
| 1 | Apples, bananas, strawberries |
| 2 | Kettle, Sound |
+------+----------------------------+

Select Id,group_concat (product_name), Group_concat (born_date) from the Order_menu group by ID;
+------+----------------------------+----------------------------------+
| ID | Group_concat (product_name) | Group_concat (born_date) |
+------+----------------------------+----------------------------------+
| 0 | Quilt cover, Bed linen | 2017-01-12, 2017-06-12 |
| 1 | Apples, bananas, strawberries | 2017-06-12,2017-01-02,2017-01-12 |
| 2 | Kettle, Sound | 2017-06-12, 2017-01-12 |
+------+----------------------------+----------------------------------+

(6) Aggregate functions: General and group queries with use
<1> all records in the statistics table:
--count (column name): Number of rows counted
eg:--How many people have a total score of more than 280?
SELECT COUNT (name) from student WHERE (Ifnull (js,0) +ifnull (django,0) +ifnull (datebase,0)) >280;
--Note: Count (*) counts all rows, count (field) does not count null values

<2> statistics The contents of rows that meet the criteria and
--SUM (column name): Statistics The contents of rows that meet the criteria and
--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.
<3> averaging
--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)))

<4>max () and 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)) min. from Examresult;
-Note: null and all number calculations are NULL, so you need to convert NULL to 0 with Ifnull. Ifnull (js,0)

(7) Key points:
SELECT from WHERE the GROUP by has an ORDER by
--mysql Order of execution when executing SQL statements:
--from where select group by have order by
--Analysis:
Select JS as JS score from Examresult where JS score >70; ----Not successful
Select JS as JS score from Examresult have JS score >90; ---success
(8) Limit
SELECT * from Examresult limit 1;
SELECT * from Examresult limit 2,5;--skips the first two bars showing the next five records
SELECT * from Examresult limit 2, 2;

(9) Query with regular expressions
SELECT * FROM employee WHERE emp_name REGEXP ' ^yu ';

SELECT * FROM employee WHERE emp_name REGEXP ' yun$ ';

SELECT * FROM employee WHERE emp_name REGEXP ' m{2} ';

MySQL operations on records in a table