MySQL specification and operation

SQL statements

SQL is an abbreviation for Structured query Language (Structured Query language). SQL is a set of operational commands built for the database and is a fully functional database language.

SQL specification

1. In a database system, SQL statements are case-insensitive, string constants are case-sensitive, recommended commands are uppercase, and table names are small thanks
2. The SQL statement can write single and multiple lines, with semicolons; Keywords cannot cross line or shorthand
3. Use spaces and indents to improve the readability of the statement; self-statements on separate lines for improved readability
4. Single-line Comment:--Multiline Comment:/**/
5. SQL statements can be wrapped
6. DML,DDL,DCL in SQL

----DML (Data Manipulation language):--    They are select, UPDATE, INSERT, DELETE, just like its name, these 4 commands are used in the database    The language in which data is manipulated------DDL (data Definition Language):-    more DDL than DML, main commands are create, alter, DROP, etc., DDL is used to define or alter tables (table)--    the structure, data types, links between tables and constraints are initialized, and most of them use the------DCL (Data Control Language) when establishing a table:--    is the database control function. Is the statement used to set or change permissions for a database user or role, including (Grant,deny,revoke, etc.)-    statements. By default, only people such as Sysadmin,dbcreator,db_owner or db_securityadmin have the power to    perform DCL

　　

Database operations

SHOW DATABASES;     --View all databases CREATE DATABASE database_name-   -creating databases Show Create DB database_name    --View CREATE DATABASE information drop databases database_name    --Delete a database     use database_name--Using a database

　　

Data table Operations

Create a table

--Syntax CREATE TABLE tab_name (            field1 type[integrity constraint],            field2 type,            ...            FIELDN type        ) [character set XXX];

Example:

--Create an employee table Employee         CREATE TABLE employee (            ID int primary key auto_increment,            name varchar),            gender Bit default 1,   --Gender char (1)  default 1   -----    or TINYINT (1)             birthday date,                   job varchar (),            salary double (4,2) unsigned,            resume text    --note that this is the last field without a comma          );/* constraint:       primary KEY ( Non-null and unique)  : A field that uniquely distinguishes the current record is called the primary key!       unique not                null       auto_increment: Used for primary key field, primary key field must be number type  */

View table Information

Desc tab_name view table structure Show columns from Tab_name  view table structure Show tables View all tables in the current database show create TABLE Tab_name    

Modify Table Structure

--(1) Add column (field) ALTER TABLE tab_name add [column] column name type [integrity constraint][first|after field name];      ALTER TABLE user add addr varchar (a) NOT null unique first/after username; #添加多个字段 ALTER TABLE users2 add addr varchar (), add age int First, add birth Varc      Har () after name;--(2) Modify a column type ALTER TABLE tab_name modify column name type [integrity constraint][first|after field name];      ALTER TABLE USERS2 modify age tinyint default 20;   ALTER TABLE USERS2 modify age int after ID;      --(3) Modify Column name ALTER TABLE TAB_NAME change [column] columns name new column name type [integrity constraint][first|after field name];      ALTER TABLE USERS2 change age-Age int default first;--(4) Deletes a list of ALTER TABLE Tab_name drop [column] names; --Think: Delete multiple columns?      Delete one and fill in one?    ALTER TABLE USERS2 Add salary float (6,2) unsigned not NULL after name, drop addr; --(5) Modify the table name rename table name to a new name;--(6) The character set used to repair the table ALTER TABLE student character set UTF8; 

Delete a table

drop table tab_name;

　　

Operation of Table Records

Increase

--<1> Insert a record: Insert [into] tab_name (field1,filed2,.......) VALUES (value1,value2,.......);　　--Example: INSERT INTO Employee_new (id,name,birthday,salary) values                     (1, ' Shuai ', ' 1992-12-30 ', 6000);       INSERT into employee_new values       (2, ' Yuan ', ' 1999-08-08 ', +);       Insert into employee_new (name,salary) values       (' Chen ',;--<2>) inserts multiple records: Insert [into] tab_name (field1 , Filed2,.......) 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　VALUES (value1,value2,.......),            (Value1,value2,.......)　　　　...                             ; --Example: INSERT INTO employee_new values      (4, ' jack1 ', ' 1993-04-20 ', ' n '),       (5, ' jack22 ', ' 1995-05-12 ', 5000);-- <3>set insert: Insert [into] tab_name set field name = value Example: INSERT INTO Employee_new set id=12,name= "Ajack";

By deleting

Delete from Tab_name [where ...]            /*                If you do not follow the WHERE statement delete data from the entire table delete            can only be used to delete a row of records delete            statement can only remove 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. */-delete the record with the name ' Alex ' in the table. Delete   from employee_new where name= ' Alex ';--Deletes all records in the table.   Delete from employee_new;--note auto_increment is not reset: ALTER TABLE employee auto_increment=1;--use Truncate to delete records from the table.   truncate TABLE emp_new;

　

Change

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. */    Update employee_new set birthday= "1989-10-24" WHERE id=1;    ---will increase the salary of yuan by 1000 yuan on the original basis.    Update employee_new set salary=salary+4000 where name= ' yuan ';

　　

Check

--Query syntax:   SELECT *|field1,filed2   ... From Tab_name                  WHERE condition                  GROUP by field have                  filter                  ORDER by field limit                  number of bars

Prepare the table:

---prepare table for   CREATE table Examresult (   ID INT PRIMARY KEY  auto_increment,   name VARCHAR (),   JS DOUBLE,   Django DOUBLE,   OpenStack DOUBLE); Insert data into examresult values  (1, "Yuan", 98,98,98), (                                                   2, "C1", 35,98,67), and (                                                   3, ")--- C2 ", 59,59,62),                                                   (4," C3 ", 88,89,82),                                                   (5," C4 ", 88,98,67),                                                   (6," C5 ", 86,100,55);

　　

Inquire

--(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, distinct is used to reject duplicate rows.                    --Query the information of all students in the table.                    select * from Examresult;                    --Check the names of all the students in the table and the corresponding English scores.                    select Name,js from Examresult;                    --Filter the repeating data in the 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 as name, Js+django+openstack as total from Examresult;                Select Name,js+django+openstack Total from Examresult;

　　

Where statement filter query

--Query the student whose name is XXX            select * from Examresult where name= ' yuan ';            --Query for students with English score greater than 90            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 10 and 20 in                        (80,90,100) values is 10 or 20 or the                        like ' yuan% '/                        *                         % means any number of characters, such as Tang's Monk, Tang Guoqiang                        _ 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

　　

Order by sort

--Specify 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.    
　　　　

--The output after the JS score is sorted.              SELECT * from Examresult order by JS;
--sort from high to low, show 4 strips
SELECT * from Examresult ORDER by JS limit 4;

　　
GROUP BY group Query

Prepare table

CREATE TABLE order_menu (             ID INT PRIMARY KEY auto_increment,             product_name VARCHAR), Price             FLOAT (6,2),             born_date Date,             class VARCHAR ()                                ); INSERT into Order_menu (product_name,price,born_date,class) VALUES                                             ("Apple", 20,20170612, "fruit"),                                             ("Banana", 80,20170602, "fruit"),                                             ("Kettle", 120,20170612, "appliance"),                                             ("quilt", 70,20170612, "bedding"),                                             ("Sound", 420,20170612, "Electrical"),                                             ("Sheets", 55,20170612, "bedding"),                                             ("Strawberry", 34,20170612, "fruit");

　

--note that each group will only display the first record when grouped by grouping criteria-the group by         sentence, which can then be followed by multiple column names, or with the HAVING clause to filter the results of GROUP by.                    ---Exercise: grouping The shopping table by class name displays the sum of the prices of each group of items                       select Class,sum from Order_menu group by class;                    ---Practice: Group items by class name and display each group of goods with a sum of more than 150 goods                       Select Class,sum from Order_menu Group by class have                                                        sum (price) >150;                   /* have                   and where both can be further filtered query results, the difference is:                     <1>where statement can only be used in the filter before grouping, having can be used in the filter 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            SELECT id,group_concat (name), Group_concat (JS) from Examresult GROUP by ID;

　　

Aggregation functions

--<1> all records in the statistics table-count (column name): Number of statistical rows--How many students are there in a class?                    First find out all the students, and then use Count package on 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;--Note: Count (*) counts all rows;            Count (field) does not count null values. --SUM (column name): Statistics The contents of the rows satisfying the conditions and--Statistics a class JS total?                        First find out all the JS scores, and then use SUM package on the Select JS as JS total from Examresult;                    Select sum (JS) as JS total from Examresult;                               --statistics of a class of each section of the total of select sum (JS) as JS total, sum (Django) as Django Total,                    SUM (OpenStack) as OpenStack from Examresult;                    --Statistics A class JS score average select sum (JS)/count (*) from Examresult; -Note: Sum is only for valueseffect, otherwise it will error.     


 　　　　　　　　 

AVG (column name):                    ---------------average class JS? First find out all the JS points, and then use AVG package.                        Select AVG (JS) from Examresult;

--Max, Min                    --To find class highest and lowest points (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)

　　

Limit record Bar number limits

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

　　

Regular match

SELECT * FROM employee WHERE emp_name REGEXP ' ^y ';  --Start with y select * FROM employee WHERE emp_name REGEXP ' y$ ';  --End With Y select * FROM employee WHERE emp_name REGEXP ' m{2} ';  --  There are two m

　　

MySQL specification and operation