Mysql learning day 3, mysql Day 3

Mysql learning day 3, mysql Day 3

1. add, delete, and modify data in the table (DML)
Create table t_user (
Id int primary key auto_increment,
Name varchar (20) not null,
Email varchar (20) unique
)

Add records to a table (required)

Insert into table name [(column name 1, column name 2...)] values (value 1, value 2 ...);
1. Insert a piece of data

1.1 specify columns to insert
Insert into t_user (name, email) values ('Tom ', 'Tom @ itcast.cn ');
* ** Note: if the data type is string, you must enclose it in single quotes.
1.2 if you do not specify the columns to insert, you must specify the values of each column.
Insert into t_user values (null, 'Jerry ', 'Jerry @ itcast.cn ');
Insert into t_user (name, email) values ('Tom ', 'tom2 @ itcast.cn ');
========================================================== ========================================================== =====
Show variables like '% character %'; ==> view character encoding Configuration

| Character_set_client | gbk client encoding ***
|
| Character_set_results | gbk result set encoding ***
|
| Character_set_connection | utf8 client connection code
|
| Character_set_database | default encoding used by the utf8 Database
|
| Character_set_filesystem | encoding used when the BINARY file system is stored

|
| Character_set_server | utf8 server code specified during installation
|
| Character_set_system | utf8 internal system code
Conclusion: if you use the cmd command console to operate the database,
Note that character_set_client and character_set_results must be set to GBK, because our command control airline uses the gbk code table to display Chinese characters.
Run the following command to set the parameters:
Method:
Set character_set_client = gbk
Set character_set_results = gbk
Note:
Every time you reconnect to the database, You have to reset it.
If you use the cmd window to operate the database, modify the following code table to gbk (the cmd window uses the gbk code table ).
The impact of this approach is only in your current link.
2. modify a record (required)

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 (20) not null,
Email varchar (20) unique
)
2.1 modify the record whose id is 3 and change the name to rose;

Update t_user set name = 'Rose 'where id = 3;
Update t_user set name = 'Rose ';
// Configure //-----------------------------------------------------------------------------------------------------
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', '2017-12-12 ', '2017-12-12', 'CODER', 1980, NULL );
Insert into employee VALUES (2, 'Ls', 'male', '2017-10-01 ', '2017-12-12', 'master', 1983, NULL );
Insert into employee VALUES (3, 'ww ', 'female', '2017-03-08 ', '2017-08-08', 'teacher', 1985, NULL );
Insert into employee VALUES (4, 'wu', 'male', '2017-05-13 ', '2017-12-22', 'hr', 1986, NULL );
-- Requirements
-- Change the salary of all employees to 5000 yuan.
-- Change the employee's salary with the name 'zs' to 3000 RMB.
-- Change the salary of an employee whose name is 'LS' to 4000 yuan, and change job to ccc.

-- Increase wu's salary by 1000 yuan based on the original salary.
3. Delete record statement (required)
Delete from table name [WHERE condition];
3.1 Delete the record named '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;
What is the difference between DELETE and TRUNCATE?
First, both are used to delete records in the table.

The difference is:
1. delete indicates row-by-row deletion. TRUNCATE removes the entire table, including the table structure, and re-creates the table.
2. delete DML statements. Truncate ddl statement.
3. delete deleted records can be recovered, and TRUNCATE cannot reply.
4. delete does not release space. TRUNCATE releases space.
5. TRUNCATE will commit the transaction (not yet learned)

// ------------------------- The preceding statements are used to modify and delete table records. (DML )-----------------------------------------------------------
DQL Statement (DML) query statement (required)

Syntax:
SELECT selection_list/* Name of the column to be queried */
FROM table_list/* Name of the table to be queried */
WHERE condition/* row condition */
Group by grouping_columns/* grouping results */
HAVING condition/* row condition After grouping */
Order by sorting_columns/* sort results */
LIMIT offset_start, row_count/* result LIMIT */
// Configure //---------------------------------------------------------------------------------------
Create table stu (-- Student TABLE
SidCHAR (6), -- Student ID
SnameVARCHAR (50), -- Student name
AgeINT, -- Age
GenderVARCHAR (50) -- Gender
);
Insert into stu VALUES ('s _ 1001 ', 'liuyi', 35, 'male ');
Insert into stu VALUES ('s _ 1002 ', 'chener', 15, 'female ');
Insert into stu VALUES ('s _ 1003 ', 'hangsan', 95, 'male ');
Insert into stu VALUES ('s _ 1004 ', 'lisi', 65, 'female ');
Insert into stu VALUES ('s _ 1005 ', 'wangw', 55, 'male ');
Insert into stu VALUES ('s _ 1006 ', 'zhaoliu', 75, 'female ');
Insert into stu VALUES ('s _ 1007 ', 'sunqi', 25, 'male ');
Insert into stu VALUES ('s _ 1008 ', 'zhouba', 45, 'female ');
Insert into stu VALUES ('s _ 1009 ', 'wujiu', 85, '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 ID
EnameVARCHAR (50), -- employee name
JobVARCHAR (50), -- work
MgrINT, -- employee Superior Number
HiredateDATE, -- entry date
SalDECIMAL (), -- Salary
CommDECIMAL (), -- bonus
DeptnoINT -- department ID
);
Insert into emp VALUES (7369, 'Smith ', 'cler', 7902, '2017-12-17', 1980, NULL, 20 );
Insert into emp VALUES (7499, 'allen ', 'salesman', 7698, '2017-02-20', 1981, 30 );
Insert into emp VALUES (7521, 'ward ', 'salesman', 7698, '2017-02-22', 1981, 30 );
Insert into emp VALUES (7566, 'Jones ', 'manager', 7839, '2017-04-02', 1981, NULL, 20 );
Insert into emp VALUES (7654, 'martin ', 'salesman', 7698, '2017-09-28', 1250,1400, 30 );
Insert into emp VALUES (7698, 'bucke', 'manager', 7839, '2017-05-01 ', 1981, NULL, 30 );
Insert into emp VALUES (7782, 'clark', 'manager', 7839, '2017-06-09 ', 1981, NULL, 10 );
Insert into emp VALUES (7788, 'Scott ', 'analyst', 7566, '2017-04-19', 1987, NULL, 20 );
Insert into emp VALUES (7839, 'King', 'President ', NULL, '2017-11-17', 1981, NULL, 10 );
Insert into emp VALUES (7844, 'turner ', 'salesman', 7698, '2017-09-08', 1981, 30 );
Insert into emp VALUES (7876, 'adams', 'cler', 7788, '2017-05-23 ', 1987, NULL, 20 );
Insert into emp VALUES (7900, 'James ', 'cler', 7698, '2017-12-03', 1981, NULL, 30 );
Insert into emp VALUES (7902, 'Ford ', 'analyst', 7566, '2017-12-03', 1981, NULL, 20 );
Insert into emp VALUES (7934, 'miller ', 'cler', 7782, '2017-01-23', 1982, NULL, 10 );
1.1 query all columns in all rows
Select * from stu;
* Is a wildcard. All columns are wildcard. The preceding statement is identical to the following
Select sid, sname, age, gender from stu;
Who is more efficient?
The efficiency below is higher. * calculation is required.
1.2 query specified columns of all rows
Select sname from stu;
2.1 Introduction to conditional Query
The WHERE clause is provided during the query. The following operators and keywords can be used in the WHERE clause:
? = ,! =, <>, <, <=,>,> =;
? BETWEEN... AND;
? IN (SET );
? Is null;
// --- Conditional Connector
? AND ;&&
? OR; |
? NOT ;!
2.2 query records whose gender is female and whose age is less than 50
Select * from stu where gender = 'female' and age <50;
2.3 query records whose student ID is S_1001 or whose name is liSi
Select * from stu where sid ='s _ 1001 'or sname = 'lisi ';
In the database, SQL statements are case-insensitive, but data is case-sensitive.
2.4 query records with student IDs S_1001, S_1002, and 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 query records with student IDs not S_1001, S_1002, and S_1003
Select * from stu where not (sid ='s _ 1001 'or sid ='s _ 1002' or sid ='s _ 1003 ');
Select * from stu where sid not in ('s _ 1001 ','s _ 1002','s _ 1003 ');
2.6 query records whose age is null
Select * from stu where age = null;
The feature of null: null is not equal to null. Therefore, the following statement should be used for determination:
Select * from stu where age is null;
2.7 query student records between 20 and 40
Select * from stu where age> = 20 and age <= 40;
Select * from stu where age between 20 and 40;
2.8 Query records of non-male and gender students
Select * from stu where gender! = 'Male ';
Select * from stu where not gender = 'male ';
Select * from stu where gender not in ('male ');
2.9 query student records whose names are not null
Select * from stu where sname is not null;
Select * from stu where not sname is null;
// Configure //--------------------------------------------------------------------------------------------------
Where field like 'expression ';
% => Wildcard character.
_ => Wildcard character.
Note: After the LIKE condition, the root fuzzy query expression "_" => represents any character.
3.1 query the student records whose names consist of five letters
Select * from stu where sname like '_____';
3.2 query the student records whose names consist of five letters and whose 5th letters are "I"
Select * from stu where sname like '____ I ';
3.3 query student records whose names start with "z"
Note: "%" This wildcard matches any length of characters.
Select * from stu where sname like 'z % ';
3.4 query the student records with 2nd letters "I" in the name
Select * from stu where sname like '_ I % ';
3.5 query student records whose names contain the letter ""
Select * from stu where sname like '% a % ';
//-----------------------------------------------------------------
4.1 remove duplicate records
Keywords: distinct => remove duplicate Query Result Records.
Select gender from stu ;=> a large number of repeated records appear
Select distinct gender from stu; => remove duplicate records
4.2 view the sum of the employee's monthly salary and Commission
Select sal * 12 + comm from emp;
Null and any number calculation result are null. The above statement is incorrect.
Use the IFNULL (parameter 1, parameter 2) function to determine whether the value of parameter 1 is null. If it is null, return the value of parameter 2.
Select sal * 12 + IFNULL (comm, 0) from emp;
* Is this function common in all databases?
Not universal.
4.3 add an alias to a column name
Select sal * 12 + IFNULL (comm, 0) as 'annual generation' from emp;
** Select sal * 12 + IFNULL (comm, 0) 'annual generation' from emp;
Select sal * 12 + IFNULL (comm, 0) annual income from emp;
// Configure //------------------------------------------------------------------------------------------------------------------------------
5.1 query all student records in ascending order of age
Asc: ascending
Desc: Descending Order
Select * from stu order by age asc;
The default value is ascending.
Select * from stu order by age;
5.2 query all student records in descending order of age
Select * from stu order by age desc;
5.3 query all employees, sorted by monthly salary in descending order. If the monthly salary is the same, sorted by serial number in ascending order.
Select * from emp order by sal desc, empno asc;
Aggregate functions
Aggregate functions are used for vertical operations:
? COUNT (): COUNT the number of records in a specified column that is not NULL;
? MAX (): calculates the maximum value of a specified column. If the specified column is of the string type, the string sorting operation is used;
? MIN (): calculates the minimum value of a specified column. If the specified column is of the string type, the string sorting operation is used;
? SUM (): calculates the value and value of the specified column. If the specified column type is not a value type, the calculation result is 0;
? AVG (): calculates the average value of a specified column. If the specified column type is not a numerical value, the calculation result is 0;
6.1 COUNT
You can use COUNT () for vertical statistics ().
? 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 is not null and comm> 0;
3> query the number of people whose monthly salary is greater than 2500 in the emp table:
Select count (*) from emp where sal> 2500;
4> count the total number of people with a monthly salary and commission of more than 2500 yuan:
Select count (*) from emp where sal + IFNULL (comm, 0)> 2500;
5> query the number of people with commissions and leaders:
Select count (*) from emp where comm> 0 and mgr is not null;
6.2 SUM (SUM) and AVG (average)
Use the sum () function when vertical summation is required.
1> query the monthly salary and:
Select sum (sal) from emp;
2> query the monthly salary and commission of all employees and:
Select sum (sal), sum (comm) from emp;
3> query monthly salary + Commission and:
Select sum (sal + IFNULL (comm, 0) from emp;
4> average salaries of all employees:
Select avg (sal) from emp;
6.3 MAX and MIN
? Query the maximum wage and minimum wage:
Select max (sal), min (sal) from emp;
// Certificate //---------------------------------------------------------------------------------------------------------------------------------------
Group Query
When grouping query is required, you need to use the group by clause. For example, you need to query the salaries and values of each department.
? 1> query the Department numbers of each department and the salaries and salaries of each department:
Select deptno, sum (sal) from emp group by deptno;
2> query the Department numbers of each department and the number of people in each department:
Select deptno, count (ename) from emp group by deptno;
3> query the Department numbers of each department and the number of employees whose salaries are greater than 1500:
Select deptno, count (ename) from emp where sal> 1500 group by deptno;
HAVING clause
4> query the number of the department whose total salary is greater than 9000 and the sum of salary and:
Select deptno, sum (sal) from emp group by deptno having sum (sal)> 9000;
Use having to add conditions after the group.
Can the where and having conditions be added?
1. Add a condition before grouping where.
2. Add a condition after the having group.
The efficiency of where is much higher than having. Grouping consumes a lot of resources.
// ---------------------------------------------- The following is the page-related knowledge ---------------------------------------------------------------------------------------
LIMIT (MySQL dialect) (required)
LIMIT is used to LIMIT the start row of the query result and the total number of rows.
1> query five rows of records, starting from 0
Select * from emp limit 0, 5;
2> Query 10 rows of records, starting from 3
Select * from emp limit 3, 10;
3> if there are five records on one page, how should I check the 3rd page records?
? The starting behavior of the first page is 0, and a total of five rows are queried;
Select * from emp limit 0, 5;
? The starting behavior of the record on the second page is 5, and a total of 5 rows are queried;
Select * from emp limit 5, 5;
? The starting behavior 10 is recorded on the third page, and a total of five rows are queried;
Select * from emp limit 10, 5;