Database MySQL Part2

Operation of Table Records

Increase

1. Insert a record

Syntax: Insert [into] tab_name (field1,filed2,.......) VALUES (value1,value2,.......);

Insert into employee_new (id,name,birthday,salary) values                (1, ' Yuan ', ' 1990-09-09 ', 9000); INSERT INTO employee_new Values  (2, ' Alex ', ' 1989-08-08 ', +); insert into employee_new (name,salary) values  (' Xialv ', 1000) ;　　　　　　　　

2. Insert more than one record

Syntax: Insert [into] tab_name (field1,filed2,.......) VALUES (value1,value2,.......),

(Value1,value2,.......)

...;

Insert into employee_new (id,name,birthday,salary) values
(4, ' alvin1 ', ' 1993-04-20 ', 3000),
　　　　  (5, ' alvin2 ', ' 1995-05-12 ', 5000);

3. Insert by value

Syntax: Insert [into] tab_name set field name = value

INSERT INTO Employee_new set id=12,name= "Alvin3";

By deleting

Syntax: 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 manner cannot be recovered in a transaction.

1. Delete all records

Delete from tab_nametruncate table Tab_name #推荐

Note: auto_increment is not reset, clear table record after use command: ALTER TABLE tab_name auto_increment=1 reset Table Auto-increment

2. Delete the specified record

#删除表中名称为 ' Alex ' record. Delete from employee_new where name= ' Alex ';

Change

Syntax: 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.

#id为1的记录生日改成1989 -10-24update employee_new Set birthday= "1989-10-24" WHERE id=1; #将yuan的薪水在原有基础上增加1000元. Update employee_new set salary=salary+4000 where name= ' yuan ';

Check (emphasis)

Grammar:

SELECT *|field1,filed2 ... From Tab_name

WHERE condition

GROUP by Field

Having screening

ORDER by Field

Limit number of bars

Prepare table records:

#创建表CREATE TABLE Examresult (   ID INT PRIMARY KEY  auto_increment,   name VARCHAR (),   JS DOUBLE,   Django Double,   OpenStack double); #插入数据INSERT into Examresult VALUES  (1, "Yuan", 98,98,98),                               (2, "Xialv", 35,98,67),                               (3, "Alex", 59,59,62), (                               4, "Wusir", 88,89,82), (                               5, "Alvin", 88,98,67), (                               6, "Yuan", 86,100,55);

1. Simple query

#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 # table that explicitly specifies the column to find, 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 table and the corresponding English scores. Select Name,js from examresult;--to filter the repeating data in the table. Select DISTINCT JS, name from Examresult; #select can also use expressions, and you can use: field as aliases or: field Aliases--add 10 extra-long points to all student scores. Select name,js+10,django+10,openstack+10 from examresult;--statistics each student's total score. Select Name,js+django+openstack from examresult;--uses aliases to represent student totals. Select name as name, Js+django+openstack as total from Examresult;select name,js+django+openstack total from Examresult;

2. WHERE clause: conditional filtering

--Query the student whose name is XXX. SELECT * from Examresult where name= ' yuan ';--query English score more than 90 points of students select Id,name,js from Examresult where js> 90;--query for all students with a total score greater than 200, select Name,js+django+openstack as score from Examresult where js+django+openstack>200;-- The WHERE clause can be used:       --comparison operators:              > < >= <= <>! =              between values between 80 and 1000 in              (80,90,100) value is 80 or 90 or the              like ' yuan% ' # pattern can be a% or _, if it is a% of any number of characters, this such as the Tang Monk, Tang Guoqiang If _ is a word 唐 _, only the Tang Monk in accordance with. Two _ means two characters: __       --logical operators              can use logical operators and or not directly in multiple conditions

3. ORDER BY clause: 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.              --Sort the output after the JS score. SELECT * from Examresult the ORDER by js;--to sort by the output of select name in order from high to Low (Ifnull (js,0) +ifnull (django,0) +ifnull (database,0)) Total score from Examresult order by total desc;--The student's grade of surname Li output select name, (Ifnull (js,0) +ifnull (django,0) +ifnull (openstack,0)) Total from Examresult where name like ' a% ' ORDER by total desc;

Note: Ifnull when the result of the output has NULL, use 0 completion, or other data, such as a string

4. GROUP BY clause: Group display

Prepare table records

#创建表CREATE TABLE Shopping (    ID INT PRIMARY KEY auto_increment,     product_name VARCHAR), Price     FLOAT (6, 2), C3/>born_date date,     class VARCHAR); #插入表记录INSERT into shopping (product_name, Price, Born_date, Class) VALUES (' Apple ', 20, 20170612, ' fruit '),    (' Bananas ', 80, 20170112, ' fruit '),    (' refrigerators ', 1200, 20170112, ' appliances '),    (' quilt ', 70, 20170612, ' bedding '),    (' Sound ', 420, 20170612, ' electrical '),    (' computer ', 4200, 20170112, ' electrical '),    (' Peaches ', 50, 20170112, ' fruit '),    (' sheets ', 55, 2 0170112, ' bedding '),    (' Strawberry ', 34, 20170612, ' fruit ');

Example

#注, grouped by group criteria, each group will only display the first record #group by, followed by multiple column names, or with the HAVING clause to filter the results of GROUP by. --Filter By Location field, Fifth field class group Select * from shopping group by 5;--to display the sum of the prices of each set of items (sum is an aggregate function) after grouping the shopping table by class name Select Class,sum (value) from Shopping GROUP by class;--the shopping table by class name to show the total price of each group of goods more than 150 of the goods select Class,sum (prices) from the shopping group by class have sum (pr ICE) >150; #having and where both can be further filtered by the query results, the difference is: #<1>where statements can only be used in the filter before grouping, having a filter that can be used after grouping;#<2> Where the where statement can be replaced with the #<3>having can be used in the aggregation function, where it does not work. --Group_concat () function: Look at the results yourself Select Id,group_concat (product_name), Group_concat (price) from the shopping GROUP by ID;

5. Aggregation function

Count (column name): Number of rows counted--how many students are there in a class? First find out all the students, and then use Count package on the SELECT COUNT (*) from examresult;--Statistics JS scores more than 70 students how many? Select COUNT (JS) from Examresult where 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; #注意: Count (*)     All rows are counted; Count (field) does not count null values. SUM (column name): Statistics The contents of rows that meet the criteria and--Statistics a class JS total? First find all the JS results, and then use the sum package on the Select JS as JS total from Examresult;select sum (JS) as JS total from examresult;--statistics A class of each section of the total select sum (J S) as JS overall, sum (Django) as Django Total, sum (OpenStack) as OpenStack from examresult;--statistics The sum of the scores of a class section select SUM (ifnull (js,0) +ifnull (django,0) +ifnull (database,0)) as total score from examresult;--statistics 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): Average--to find a class JS average score? First find out all the JS points, and then use AVG package. Select AVG (ifnull (js,0)) from examresult;--for a class score average of select AVG ((Ifnull (js,0) +ifnull (django,0) +ifnull (database,0)) ) from Examresult; Max, min: Ask for maximum and minimum--class highest and lowest (range of values 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;- -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 item is a product? SELECT MAX from Order_menu; #注意: null and all number calculations are NULL, so you need to convert NULL to 0 with Ifnull! #--ifnull (js,0)

6. Limit clause: Limits the number of record bars displayed

SELECT * from Examresult limit 1;          #显示第一行记录 SELECT * from Examresult limit 2,5;       #跳过前两条显示接下来的五条纪录 SELECT * from Examresult limit 2,2;        #跳过前两条显示接下来的两条记录

7. 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} ';

Database MySQL Part2