MySQL Study the third day

1. Change the data in the table (DML)
CREATE TABLE T_user (
ID int primary KEY auto_increment,
Name varchar () is not NULL,
Email varchar (unique)
)

Add a record to a table (must be mastered)

insert into table name [(column name 1, column Name 2 ...)] VALUES (value 1, value 2 ...);
1. Inserting a piece of data

   1.1 Specify which columns to insert
       insert into T_user (name,email) VALUES (' Tom ', ' [email  Protected] ');
* * * NOTE: The data type is of type string. You need to use single quotation marks for wrapping.
    1.2 does not specify which columns to insert, you need to specify values for each column
        INSERT INTO t_user values (null, ' Jerry ', ' [email& Nbsp;protected] ');
        INSERT INTO T_user (name,email) VALUES (' Tom ', ' [email protected] ');
=====================================================================================
show VARIABLES like '%character% '; ==> view character encoding configuration

      | character_set_client | GBK CLIENT Encoding * * *
      |
      | Character_set_results | GBK encoding of the result set * * *
      |
      | character_set_connection | UTF8 encoding for client connections
      |
      | Character_set_database | UTF8 the default encoding used by the database
      |
      | Character_set_filesystem | The encoding used when binary file system is stored

      |
      | character_set_server | UTF8 server encoding specified during installation
      |
      | Character_set_system | UTF8 internal system Code
  Conclusion: If you use the CMD command console to manipulate the database,
  Note that character_set_client and character_set_results need to be set to GBK, because our lives Make the control navigation use the GBK Code table to display Chinese.
  Use the following command settings:
  mode:
      Set CHARACTER_SET_CLIENT=GBK
      set CHARACTER_SET_RESULTS=GBK
  NOTE:
    Re-set each time you reconnect the database.
    If you are using a CMD window to manipulate the database. Change the following code table to GBK (the cmd window uses the GBK Code table).
    This practice affects only the scope of your current link.
2. Modify a record (must be mastered)

Update table name set column Name 1 = value, column Name 2 = value .... [Where Condition 1, condition 2 ...]
CREATE TABLE T_user (
ID int primary KEY auto_increment,
Name varchar () is not NULL,
Email varchar (unique)
)
2.1. Modify the record with ID 3 in the table and change the name to Rose;

Update t_user set name= ' Rose ' where id=3;
Update t_user set name= ' Rose ';
//-----------------------------------------------------------------------------------------------------
CREATE TABLE Employee (
ID INT,
NAME VARCHAR (20),
Gender VARCHAR (20),
Birthday DATE,
Entry_date date,
Job VARCHAR (30),
Salary DOUBLE,
RESUME Longtext
);
INSERT into employee VALUES (1, ' zs ', ' Male ', ' 1980-12-12 ', ' 2000-12-12 ', ' coder ', 4000,null);
INSERT into Employee VALUES (2, ' ls ', ' male ', ' 1983-10-01 ', ' 2010-12-12 ', ' master ', 7000,null);
INSERT into Employee VALUES (3, ' ww ', ' female ', ' 1985-03-08 ', ' 2008-08-08 ', ' teacher ', 2000,null);
INSERT into Employee VALUES (4, ' Wu ', ' Male ', ' 1986-05-13 ', ' 2012-12-22 ', ' hr ', 3000,null);
-Requirements
--Revise the salary of all employees to 5000 yuan.
--Change the salary of employees named "Zs" to 3000 yuan.
--Change the salary of the employee named ' ls ' to 4000 yuan and the job to CCC.

--Add Wu's salary to 1000 yuan on the original basis.
3, delete the record statement (must master)
DELETE from table name [WHERE condition];
3.1. Delete the record with the name ' Rose ' in the table.
DELETE from employee WHERE name= ' Rose ';
3.2. Delete all records in the table.
DELETE from employee;
3.3 . Use truncate to delete records in the table.
TRUNCATE TABLE employee;
Delete Delete and truncate delete (understand) What's the difference between the two?
First, both of these are deletions of records in a table.

The difference is:
1. Delete is a row by line tag deletion. TRUNCATE is to remove the entire table, including the table structure, and then recreate the table.
2. Delete DML statement. TRUNCATE DDL statements.
3, delete deleted records can be restored, TRUNCATE cannot reply.
4. Delete Does not free space, TRUNCATE frees space.
5, TRUNCATE will commit the transaction. (not yet learned)

-------------------------above is the addition of modified delete table record related statements (DML)-----------------------------------------------------------
A DQL statement (DML) query statement. (Must be mastered)

Grammar:
SELECT selection_list/* The name of the column to query */
From table_list/* Table name to query */
WHERE Condition/* Line Condition */
GROUP BY Grouping_columns/* Group results */
Having condition * * After grouping the row condition */
ORDER BY Sorting_columns/* Sort Results */
Limit Offset_start, row_count/* Result limit */
//---------------------------------------------------------------------------------------
CREATE table Stu (--Student table
Sidchar (6),--student number
Snamevarchar (50),--Student name
Ageint,--Age
Gendervarchar (50)--gender
);
INSERT into Stu VALUES (' s_1001 ', ' liuyi ', ' Male ');
INSERT into Stu VALUES (' s_1002 ', ' Chener ', and ' female ');
INSERT into Stu VALUES (' s_1003 ', ' Zhangsan ', up, ' Male ');
INSERT into Stu VALUES (' s_1004 ', ' liSi ', ' n ', ' female ');
INSERT into Stu VALUES (' s_1005 ', ' Wangwu ', ' Male ');
INSERT into Stu VALUES (' s_1006 ', ' Zhaoliu ', ' female ');
INSERT into Stu VALUES (' s_1007 ', ' Sunqi ', +, ' male ');
INSERT into Stu VALUES (' s_1008 ', ' Zhouba ', ' female ');
INSERT into Stu VALUES (' s_1009 ', ' wujiu ', ' Male ');
INSERT into Stu VALUES (' s_1010 ', ' Zhengshi ', 5, ' female ');
INSERT into Stu VALUES (' s_1011 ', ' xxx ', null, NULL);
//---------------------------------------------------------------
CREATE table EMP (--Employee table
Empnoint,--Employee Number
Enamevarchar (50),--Employee name
Jobvarchar (,--) work
Mgrint,--Employee's boss's number
Hiredatedate,--Date of entry
Saldecimal (7,2),--wages
Commdecimal (7,2),--bonus
deptnoint--Department Number
);
INSERT into EMP VALUES (7369, ' SMITH ', ' clerk ', 7902, ' 1980-12-17 ', 800,null,20);
INSERT into EMP VALUES (7499, ' ALLEN ', ' salesman ', 7698, ' 1981-02-20 ', 1600,300,30);
INSERT into EMP VALUES (7521, ' WARD ', ' salesman ', 7698, ' 1981-02-22 ', 1250,500,30);
INSERT into EMP VALUES (7566, ' JONES ', ' MANAGER ', 7839, ' 1981-04-02 ', 2975,null,20);
INSERT into EMP VALUES (7654, ' MARTIN ', ' salesman ', 7698, ' 1981-09-28 ', 1250,1400,30);
INSERT into EMP VALUES (7698, ' BLAKE ', ' MANAGER ', 7839, ' 1981-05-01 ', 2850,null,30);
INSERT into EMP VALUES (7782, ' CLARK ', ' MANAGER ', 7839, ' 1981-06-09 ', 2450,null,10);
INSERT into EMP VALUES (7788, ' SCOTT ', ' ANALYST ', 7566, ' 1987-04-19 ', 3000,null,20);
INSERT into EMP VALUES (7839, ' KING ', ' president ', NULL, ' 1981-11-17 ', 5000,null,10);
INSERT into EMP VALUES (7844, ' TURNER ', ' salesman ', 7698, ' 1981-09-08 ', 1500,0,30);
INSERT into EMP VALUES (7876, ' ADAMS ', ' clerk ', 7788, ' 1987-05-23 ', 1100,null,20);
INSERT into EMP VALUES (7900, ' JAMES ', ' clerk ', 7698, ' 1981-12-03 ', 950,null,30);
INSERT into EMP VALUES (7902, ' FORD ', ' ANALYST ', 7566, ' 1981-12-03 ', 3000,null,20);
INSERT into EMP VALUES (7934, ' MILLER ', ' clerk ', 7782, ' 1982-01-23 ', 1300,null,10);
1.1 Querying all columns of all rows
SELECT * from Stu;
The * number is a wildcard character. All columns are provided. The above statement is identical to the following
Select Sid,sname,age,gender from Stu;
Who is more efficient?
The following are more efficient. * Operation required.
1.2 Querying all rows specifying columns
Select Sname from Stu;
2.1 Article Query Introduction
A conditional query is a WHERE clause that is given at query time, and the following operators and keywords can be used in the WHERE clause:
? =,! =, <>, <, <=, >, >=;
? Between ... and;
? In (SET);
? is NULL;
---conditional connector
? &&;
? or; | |
? not;!
2.2 Query gender is female, and age is less than 50 records
SELECT * from Stu where gender= ' female ' and age<50;
2.3 Inquiry number is s_1001, or the name is Lisi Records
SELECT * from Stu where sid= ' s_1001 ' or sname= ' liSi ';
In the database, the SQL statements are case-insensitive, but the data is case-sensitive.
2.4 The records of the inquiry number s_1001,s_1002,s_1003
SELECT * from Stu where sid= ' s_1001 ' or sid= ' s_1002 ' or sid= ' s_1003 ';
SELECT * from Stu where SID in (' s_1001 ', ' s_1002 ', ' s_1003 ');
2.5 Inquiry number is not a record of s_1001,s_1002,s_1003
SELECT * from Stu where not (sid= ' s_1001 ' or sid= ' s_1002 ' or sid= ' s_1003 ');
SELECT * from Stu where Sid isn't in (' s_1001 ', ' s_1002 ', ' s_1003 ');
2.6 Querying for records with a null age
SELECT * from Stu where age=null;
NULL attribute: null is not equal to NULL so the judgment should be as follows:
SELECT * from Stu where-is null;
2.7 Check the student record of age 20 to 40
SELECT * from Stu where age >= and <= 40;
SELECT * from Stu where age between and 40;
2.8 Query Gender non-male student records
SELECT * from Stu where gender!= ' male ';
SELECT * from Stu where not gender= ' male ';
SELECT * from Stu where gender not in (' Male ');
2.9 Querying a student record whose name is not null
SELECT * from Stu where sname are not null;
SELECT * from Stu where isn't sname is null;
//--------------------------------------------------------------------------------------------------
where field like ' expression ';
% = Pass the wildcard with any of the characters.
_ = + Pass with a single character.
Description: After the like condition, the root fuzzy query expression, "_" ==> represents an arbitrary character
3.1 Check the student record with name 5 letters
SELECT * from Stu where sname like ' _____ ';
3.2 Query name consists of 5 letters, and the 5th letter is "I" Student record
SELECT * from Stu where sname like ' ____i ';
3.3 Querying student records with names beginning with "Z"
Description: "%" this wildcard matches characters of any length.
SELECT * from Stu where sname like ' z% ';
3.4 Check the student record of the 2nd letter "I" in the name
SELECT * from Stu where sname like ' _i% ';
3.5 Student records with the letter "a" in their name
SELECT * from Stu where sname like '%a% ';
//-----------------------------------------------------------------
4.1 Removing duplicate records
Keywords:distinct = Remove duplicate query result records.
Select Gender from Stu; A large number of duplicate records appear in ==>
Select distinct gender from Stu; + = Remove duplicate records
4.2 Viewing the sum of the employee's monthly salary and commission
Select Sal*12+comm from EMP;
Null and any numeric calculation result are null. The above notation is wrong.
Use the Ifnull (parameter 1, parameter 2) function to resolve. Determines whether the value of parameter 1 is NULL if NULL returns the value of parameter 2.
Select Sal*12 + ifnull (comm,0) from EMP;
* Does this function be common to all databases?
Not universal.
4.3 Add to Column namealiases
Select Sal*12 + ifnull (comm,0) as ' yearly Income ' from EMP;
* * Select Sal*12 + ifnull (comm,0) ' annual income ' from EMP;
Select Sal*12 + ifnull (comm,0) annual income from EMP;
//------------------------------------------------------------------------------------------------------------- -----------------
5.1 Query All student records, sorted by age ascending
ASC: Ascending
desc: Descending
SELECT * from Stu order by age ASC;
The default is ascending
SELECT * from Stu order by age;
5.2 Querying all student records, sorted by age in descending order
SELECT * from Stu ORDER by age Desc;
5.3 Query all employees, in descending order of monthly salary, if the monthly salary is the same, in ascending order by number
SELECT * from emp ORDER BY sal Desc, empno ASC;
Aggregation Functions
An aggregate function is a function used to do a longitudinal operation:
? COUNT (): counts the number of record rows for which the specified column is not null;
? Max (): Calculates the maximum value of the specified column, using string sorting operations if the specified column is a string type;
? Min (): Calculates the minimum value of the specified column and, if the specified column is a string type, uses string sorting operations;
? SUM (): Calculates the value of the specified column and, if the specified column type is not a numeric type, evaluates to 0;
? AVG (): Calculates the average of the specified column, if the specified column type is not a numeric type, the result is 0;
6.1 COUNT
Count () can be used when vertical statistics are required.
?1> Query the number of records in the EMP table:
Select COUNT (*) from EMP;
2> Query the number of people with commissions in the EMP table:
Select COUNT (*) from EMP where Comm are NOT null and comm >0;
3> the number of people earning more than 2500 monthly in the EMP table:
Select COUNT (*) from EMP where Sal > 2500;
4> statistics of monthly salary and commission of more than 2500 yuan number:
Select COUNT (*) from EMP where Sal+ifnull (comm,0) > 2500;
5> Check the number of people with a commission and the number of leaders:
Select COUNT (*) from EMP where comm > 0 and MGR are NOT null;
6.2 sum (Calculate sum) and AVG (calculate average)
Use the sum () function when vertical summation is required.
1> Check all employees monthly salary and:
Select sum (SAL) from EMP;
2> Query all employees monthly and, as well as all employee commissions and:
Select sum (SAL), SUM (comm) from EMP;
3> Check all employees monthly salary + Commission and:
Select SUM (sal+ifnull (comm,0)) from EMP;
4> statistics all employees ' average salary:
Select AVG (SAL) from EMP;
6.3 Max and Min
Check the maximum wage and minimum wage:
Select Max (sal), Min (sal) from EMP;
//------------------------------------------------------------------------------------------------------------- --------------------------
Group Queries
A GROUP BY clause is required when grouping queries are required, such as querying the payroll for each department, which means using sections to group.
?1> queries Each department's department number and each department's salary and:
Select Deptno,sum (SAL) from the EMP group by DEPTNO;
2> Query the department number for each department and the number of people in each department:
Select Deptno,count (ename) from the EMP group by DEPTNO;
3> Query the department number for each department and the number of people who pay more than 1500 per department:
Select Deptno,count (ename) from the EMP where sal>1500 group by DEPTNO;
HAVING clause
4> Query The department number of the payroll sum greater than 9000 and the salary and:
Select Deptno,sum (SAL) from EMP Group by DEPTNO have sum (SAL) >9000;
Use having to add conditions after grouping.
Where and having can all add conditions?
1.where add conditions before grouping.
2.having adds the condition after grouping.
Where is much more efficient than having. The grouping itself consumes very large resources.
----------------------------------------------The following is a pagination-related knowledge---------------------------------------------------------------- -----------------------
LIMIT (MySQL dialect) (must be mastered)
Limit is used to limit the starting row of the query result and the total number of rows.
1> Query 5 rows, starting from 0
SELECT * from emp limit 0, 5;
2> query 10 rows, starting from 3
SELECT * FROM EMP limit 3, 10;
3> If a page record is 5, how do you want to see the 3rd page record?
The first page records the starting behavior 0, altogether inquires 5 lines;
SELECT * from emp limit 0, 5;
? The second page records the starting behavior 5, altogether inquires 5 lines;
SELECT * FROM EMP limit 5, 5;
The third page records the starting behavior 10, altogether inquires 5 lines;
SELECT * FROM EMP limit 10, 5;

MySQL Study the third day