Mysql Statement Quick Review Course (full) _mysql

Basic operations

View Database

<code>show databases;
</code>

Specify character Set

<code>create database day15 
default character set UTF8
</code>

View Character Set

<code>show CREATE database day15;
</code>

Delete

<code>drop Database Day15
</code>

Modify Character Set

<code>alter database day15 default character set GBK;
</code>

Working with databases

<code>use day15;
</code>

View all Tables

<code>show TABLES; 
</code>

Create a table

<code>create TABLE student (
ID int,
NAME VARCHAR (),
gender VARCHAR (2), age
INT
)
</code>

View Table structure:

<code>desc student;
</code>

Delete Table

<code>drop TABLE student;
</code>

Increase

<code>--inserts all the fields. INSERT INTO
student VALUES (1, ' John ', ' Male ',) in sequential order;
--Note that you cannot have less or more field values-
INSERT into student values (2, ' Dick ', ' female ');
--Inserts a partial field insert into
student (Id,name) VALUES (2, ' Dick ');
</code>

Change

<code>--Modify all data (recommended less)
UPDATE student SET gender= ' female ';
--with conditional modification (recommended)
UPDATE student SET gender= ' man ' WHERE id=1;
--Modify the Student ID 1, modify the gender to male
--Modify multiple fields, note: SET field name = value, field name = value,....
UPDATE student SET gender= ' men ', age=30 WHERE id=2;
</code>

By deleting

<code>--Delete all data (recommended less)
delete from student;
--With conditional deletion (recommended)
Delete from student WHERE id=2;
--and another way to compare
--delete from: You can delete 
1 from the entire table--you can delete 3 with a condition. 
data that is deleted with delete from can be rolled back (transaction)-
-TRUNCATE TABLE: You can delete the entire table 
1) can not be deleted with 
the conditions of 2) that is, you may delete the table data, you can delete the table constraint 
3 The data deleted using TRUNCATE table can not be rolled back
TRUNCATE table student;
</code>

Check

<code>--2.1 Queries all columns SELECT * from student;
--2.2 query Specifies the column SELECT id,name,gender from student;
--2.3 Specify alias (AS) when querying--note: Multiple-table queries are frequently using the table's alias SELECT ID as ' number ', name as ' name ' from student;
--2.4 Add a constant column when querying--Requirements: Add a class column when querying the student table, "Java Employment class" Select Id,name,gender,age, ' Java Employment class ' as ' grade ' from student;
--2.5 Query Merge columns--requirements: Query each student's servlet and JSP's total score SELECT id,name, (servlet+jsp) as ' total score ' from student;
-Note: Merge columns can only merge field SELECT IDs of numeric types, (Name+servlet) from student;
--2.6 Query to remove duplicate records (DISTINCT)--Requirements: Query the gender of the students SELECT DISTINCT gender from student;
--Another syntax SELECT DISTINCT (gender) from student;
-Demand: Check the area of the student's location SELECT DISTINCT address from student; --2.7-piece query (where)--2.7.1 Logical condition: and (with) or (or)--Requirements: Query ID 2, and Dick student SELECT * from student where id=2 and name= ' Dick '; --the intersection--Requirements: A query ID of 2, or a student with a name of John SELECT * from student WHERE id=2 or name= ' John '; --and set--2.7.2 comparison conditions: > < >= <= = <> (not equal to) between and (equivalent to >= and <=)--Requirements: query servlet students who score more than 70 points Sele
CT * FROM student WHERE servlet>70; -NeedAsk: Query JSP score is greater than or equal to 75, and less than or equal to 90 points of the student SELECT * from student WHERE jsp>=75 and jsp<=90; --Another syntax SELECT * from student WHERE JSP BETWEEN 90;
--(after package) SELECT * from student WHERE gender<> ' male '; --2.7.3 (null null string): is null/is not null/= '/<> '--requirements: Students with empty query address (including null and empty strings)--null vs empty string--null: Table
Shows no value--empty string: Value!
--To determine the null SELECT * from student WHERE the IS null;
--To judge an empty string SELECT * from student WHERE address= '; SELECT * FROM Student WHERE the is NULL OR address= ';
--(including null and empty strings)--Requirements: query for students with addresses (excluding null and empty strings) SELECT * from student WHERE ' s not null and address<> '; --2.7.4 fuzzy condition: like--usually use the following substitution tag:--%: denotes any character--_: Denotes a character--demand: Query surname ' Zhang ' student SELECT * student WHERE name like ' Lee%
';
-Demand: Query surname ' Li ' with only two names of students SELECT * from student WHERE name like ' li _ '; --2.8 Aggregate queries (queries using aggregate functions)--common aggregate functions: SUM () avg () max () min () count ()--Requirements: Query the student's Servlet's total score (sum (): Sum function) SELECT sum (servlet
The total score of as ' servlet ' from student; -Requirements: Query the average sub SE of the student's servletLect avg (servlet) as ' servlet's average score ' from student;
-Requirements: Query the current servlet highest division SELECT MAX (servlet) as ' highest score ' from student;
--Requirements: minimum min. SELECT min (servlet) as ' min ' from student;
-Requirements: Statistics How many students are currently (count (field)) SELECT count (*) from student;
SELECT COUNT (ID) from student;
-Note: The count () function counts the number of data that does not contain null--the number of records using the Count table, to use fields that do not contain null values, SELECT count (age) from student;
SELECT * from student; --2.9 page query (limit start row, query a few lines)--Start row starting from 0-page: How many pages per page of the current page-page query the current page of the data sql:select * from student limit (current page-1) * How many per page display,
How many bars are displayed per page;
-Demand: Query 1th, 2 records (1th page data) SELECT * from student LIMIT 0, 2;
--Query 3rd, 4 records (Data on page 2nd) SELECT * FROM Student LIMIT 2, 2;
--Query 5th, 6 Records (data on page 3rd) SELECT * FROM Student LIMIT 4, 2;
--Query 7th, 8 Records (no record not shown) SELECT * FROM student LIMIT 6, 2; --2.10 Query sort (order BY)-Syntax: ORDER BY Field ASC/DESC-ASC: sequence, positive sequence. Value: Ascending, Letter: Natural order (A-Z)--desc: Reverse, reverse order.
Value: Descending, Letter: Natural reverse order (Z-A)-By default, SELECT * from student is sorted in the order of insertion records;
-Requirements: Sort SELECT * from student with ID ASC in order of ID; SELECT * FROM student the order by ID; --Default positive sequence SELECT *From student ORDER by ID desc;----note: Multiple sorting criteria--requirements: In the order of the servlet, SELECT * from the student by the servlet ASC,JS
P DESC; --2.11 Grouped queries (group by)--requirements: number of men and women--expected results:--Male 3---Female 2--1) statistics of the number of students per group by sex (group by gender)-2) SEL
ECT Gender,count (*) from student GROUP by gender; --2.12 Packet query after screening--demand: Query the total number of people more than 2 of the gender--1) query the number of men and women-2-to filter out the number of people greater than 2 (having)---Note: Before grouping conditions use the WHERE keyword, before grouping conditions using the HAVING keyword Selec
T Gender,count (*) from student WHERE GROUP by gender has COUNT (*) >2; </code>

Practice:

<code>create TABLE student2 (id INT, NAME VARCHAR (), Chinese float, 中文版 float, math float);
</code> INSERT into Student2 (Id,name,chinese,english,math) VALUES (1, ' Zhang Xiaoming ', 89,78,90);
INSERT into Student2 (Id,name,chinese,english,math) VALUES (2, ' Li Jin ', 67,53,95);
INSERT into Student2 (Id,name,chinese,english,math) VALUES (3, ' Harry ', 87,78,77);
INSERT into Student2 (Id,name,chinese,english,math) VALUES (4, ' Li Yi Chan ', 88,98,92);
INSERT into Student2 (Id,name,chinese,english,math) VALUES (5, ' Li Wealth ', 82,84,67);
INSERT into Student2 (Id,name,chinese,english,math) VALUES (6, ' Zhang Jinbao ', 55,85,45);
INSERT into Student2 (Id,name,chinese,english,math) VALUES (7, ' Huang Rong ', 75,65,30);
– Query the information for all students in the table.
SELECT * from Student2;
– The names of all the students in the query table and the corresponding English scores.
SELECT name,english from Student2;
– Filter the duplicate data of English scores in the table SELECT DISTINCT (中文版) from Student2;
– Use aliases to represent student scores.
SELECT name as ' name ', Chinese as ' language ', 中文版 as ' English ', Math as ' mathematics ' from Student2;
-Query the student's results with name Li Yi Chan SELECT * from Student2 WHERE name= ' Li Yi Chan '; -Query English score is greater than or equal to 90 points of the classmate SELECT * from Student2 WHere english>=90;
-Query all students with a total score greater than 200 SELECT * from Student2 WHERE (Chinese+english+math) >200;
-Check the English scores of all the students surnamed Li.
SELECT name,english from Student2 WHERE NAME like ' li% ';
-Check the >80 or >200 of students in English SELECT * from Student2 WHERE english>80 or (Chinese+english+math) to count each student's total score.
SELECT Id,name, (Chinese+english+math) as ' total score ' from Student2;
-Add 10 points to all students ' total score points. SELECT Id,name, (chinese+english+math+10) as ' total score ' from Student2;

data constraint

<code>--1.1 default CREATE TABLE student (id INT, NAME VARCHAR (), address VARCHAR default ' Guangzhou Tianhe '-default value)--when field
When no value is inserted, MySQL automatically assigns the field the default value insert into student (Id,name) VALUES (1, ' John ');
Note: The default value field allows null INSERT into student (id,name,address) value (2, ' dick ', null);
INSERT into student (id,name,address) VALUE (3, ' Harry ', ' Guangzhou Panyu '); --1.2 non-null--Requirement: Gender field must have a value (not null) CREATE TABLE student (id INT, NAME VARCHAR (), Gender VARCHAR (2) NOT null-non-empty)-
-Non-null fields must be assigned INSERT into student (Id,name) VALUES (1, ' Dick ');
Non-null characters cannot insert null INSERT INTO student (Id,name,gender) VALUES (1, ' dick ', null);
--1.3 Unique CREATE TABLE student (ID INT unique,--unique NAME VARCHAR ()) INSERT into student (Id,name) VALUES (1, ' ZS '); 
INSERT into student (Id,name) VALUES (1, ' Lisi '); --ERROR 1062 (23000): Duplicate entry ' 1 ' for key ' ID '--1.4 primary KEY (Non-null + unique) CREATE TABLE student (id INT PRIMARY key,--primary key N
AME VARCHAR INSERT into student (Id,name) VALUES (1, ' John ');
INSERT into student (Id,name) VALUES (2, ' John '); --INSERT into student (ID, NAME) VALUES (1, ' Dick '); 
--Violation of UNIQUE constraint: Duplicate entry ' 1 ' for key ' PRIMARY '--inserts into student (name) value (' Dick '); --Violation of non-null constraints: ERROR 1048 (23000): Column ' id ' cannot be null--1.5 self-growth CREATE TABLE student (ID INT (4) Zerofill PRIMARY KEY
Auto_increment,--since growth, starting at 0 Zerofill 0 populate NAME VARCHAR (20))--Self-growing field can be unassigned, automatically incrementing INSERT into student (NAME) VALUES (' John ');
--cannot affect the self-growth constraint DELETE from student;
TRUNCATE TABLE student can be influenced by self-growth constraint; --1.6 FOREIGN KEY constraint-Employee Table CREATE table employee (ID INT PRIMARY key, EmpName VARCHAR (), Deptname VARCHAR (20)--department name) INSERT I
NTO Employee VALUES (1, ' John ', ' Software Development Department ');
INSERT into Employee VALUES (2, ' Dick ', ' Software Development Department ');
INSERT into Employee VALUES (3, ' Harry ', ' Application Maintenance Department '); --To solve the problem of high data redundancy: Put the redundant fields in a separate table--design a separate Department table CREATE TABLE dept (ID INT PRIMARY KEY, Deptname VARCHAR (20))--Modify employee table Create TA BLE Employee (ID INT PRIMARY key, EmpName VARCHAR, deptid INT,--Change Department name to department ID-declares a FOREIGN key constraint CONSTRAINT EMLYEE_DEPT_FK for Eign KEY (DeptID) REFERENCES Dept (ID) on UPDATE CASCADE on DELETE CASCADE--ON CASCADE UPDATE: Cascading Modifications--problem: This record is not legal, the employee inserted a nonexistent department data insert into the employee VALUES (5, ' Chen Six ', 4); --Violation of FOREIGN KEY constraints: Cannot add or update a child row:a FOREIGN KEY constraint fails (' DAY16 '. ' Employee ', REFERENCES ' dept ' (' ID ') )--1 when there is a foreign key constraint, add the order of the data: first add the primary table, and then add the secondary table data-2 when the foreign key constraints, modify the order of the data: first modify the secondary table, and then modify the main table data-3 when the foreign key constraints, delete the order of the data: first delete the secondary table, and then delete the main table data--Modify department (not
Directly modify the main table) UPDATE dept SET id=4 WHERE id=3;
--First Modify employee table UPDATE employee SET deptid=2 WHERE id=4;
--Delete Department delete from Dept WHERE id=2;
--First Delete employee table delete from employee WHERE deptid=2;
--Cascade Modification (modification)--Directly Modify Department UPDATE Dept SET id=5 WHERE id=4;
--Cascade Delete--deletes the department delete directly from Dept WHERE Id=1; </code>

Association query

<code>--Requirements: Query employees and their departments (Display employee name, department name)
--2.1 Cross connection query (not recommended). Produce Cartesian product phenomena:
4 * 4=16, some are duplicate records)
SELECT empname,deptname from employee,dept;
-Requirements: Query employees and their departments (Display employee name, department name)
--Multiple table query rules:
1 Determine which table 2 is queried which 
of the 3) table to join conditions (law: Number of Join conditions is table number-1)
--2.2 connection query: Only the results that meet the conditions will be displayed (most frequently)
SELECT Empname,deptname-2 determines which fields from
employee,dept-1 determine which tables where employee.deptid=dept.id are queried-
3 The Join condition between table and table-
-another syntax for inner joins
SELECT empname,deptname from
employee
INNER JOIN Dept
on Employee.deptid=dept.id;
--Use alias
SELECT e.empname,d.deptname from
employee e
INNER JOIN dept D on
E.deptid=d.id;
--Demand: Query employees in each department-
-expected results:
--Software Development department John
--Software development department Dick
--Application Maintenance Department Harry--
Secretary Chen VI
--General office null
-- 2.2 Left [out] connection query: Use the data from the table on the left to match the data on the right table,

If the result of a join condition is displayed, NULL is displayed if the join condition is not met

--(Note: Left OUTER join: The data on the left table will definitely complete the display!)
SELECT d.deptname,e.empname from
Dept D-Left
OUTER JOIN employee E on
D.id=e.deptid;

If the result of a join condition is displayed, NULL is displayed if the join condition is not met

--(Note: Right outer connection: the data on the right table will definitely complete the display!)
SELECT d.deptname,e.empname from
employee e right
OUTER JOIN Dept D on
D.id=e.deptid;
--2.4 Self-connection query-
-demand: query employees and their superiors--
expected results:--John Null--Li Shizhang--
Harry Dick-
-Chen Liu Harry
SELECT E.emp Name,b.empname from
employee E left 
OUTER JOIN employee B on
e.bossid=b.id;
</code>

Stored Procedures

<code>--declaration Terminator--Create a stored procedure DELIMITER $ create PROCEDURE pro_test () BEGIN-You can write multiple SQL statements;
SELECT * from employee;
End $--Execute stored procedure call Pro_test ();  --3.1 stored procedures with input parameters-requirements: Incoming ID of an employee, query employee information DELIMITER $ CREATE PROCEDURE Pro_findbyid (in Eid INT)--in: input parameter BEGIN SELECT *
From employee WHERE Id=eid;
End $--Invoke call Pro_findbyid (4); --3.2 stored procedures with output parameters DELIMITER $ CREATE PROCEDURE pro_testout (out str VARCHAR)--out: Output parameter BEGIN--assign value to parameter SET str= ' HELLJ
Ava ';
End $--delete stored procedure drop PROCEDURE pro_testout;
--call--How do I accept the value of the return parameter?? --***mysql's variable----global variable (built-in variable): MySQL database built-in variables (all connections work)--View all global variables: Show variables--View a global variable: SELECT @@ 变量 Name--Modify global change Quantity: Set Variable name = new value-Character_set_client:mysql server's receive data encoding--Character_set_results:mysql the server output data--session variable:
Only one connection exists between the current client and the server side of the database.
If the connection is disconnected, then the session variables are all lost! --Define Session variables: SET @ variable = value--View Session variable: SELECT @ variable--local variable: A variable used in a stored procedure is called a local variable.
Local variables are lost as long as the stored procedure is finished!!
--1 defines a session variable name, 2) using the name session variable to receive the return value of the stored procedure call Pro_testout (@NAME);
--View variable value SELECT @NAME; --3.3 with input and output parametersStored procedure DELIMITER $ CREATE PROCEDURE pro_testinout (INOUT n INT)--INOUT: Input Output parameter BEGIN--view variable SELECT n;
SET n = 500;
End $--call SET @n=10;
Call Pro_testinout (@n);
SELECT @n;
--3.4 stored procedures with conditional judgment--Requirements: Enter an integer, if 1, then return "Monday", if 2, return "Tuesday", if 3, return "Wednesday".
Other numbers, return "error input";
DELIMITER $ CREATE PROCEDURE pro_testif (in num int,out str VARCHAR ()) BEGIN IF num=1 THEN SET str= ' Monday ';
ELSEIF num=2 THEN SET str= ' Tuesday ';
ELSEIF num=3 THEN SET str= ' Wednesday ';
ELSE SET str= ' input error ';
End IF;
End $ Call Pro_testif (4, @str);
SELECT @str; --3.5 stored procedures with cyclic function-requirements: Enter an integer, sum. For example, enter 100, statistics 1-100 and DELIMITER $ CREATE PROCEDURE pro_testwhile (in num int,out result INT) BEGIN--Define a local variable DECLARE i INT D
Efault 1;
DECLARE vsum INT DEFAULT 0;
While i<=num do SET vsum = vsum+i;
SET i=i+1;
End while;
SET result=vsum;
End $ DROP PROCEDURE pro_testwhile;
Call Pro_testwhile (@result);
SELECT @result; --3.6 Using the result of a query to assign a value to a variable (into) DELIMITER $ CREATE PROCEDURE pro_findbyid2 (in Eid int,out vname VARCHAR ()) BEGIN SELECT Empna Me into VName FROM employee WHERE Id=eid;
End $ Call Pro_findbyid2 (1, @NAME);
SELECT @NAME; --Practice: Write a stored procedure if the student's English average is less than or equal to 70 points, then the output ' general ' if the student's English average is more than 70 points, and is less than 90 points, then the output ' good ' if the student's English average score is greater than 90 points, then output ' excellent ' DELIMITER
PROCEDURE Pro_testavg (out str VARCHAR) BEGIN-defines local variables and receives an average of DECLARE savg DOUBLE;
--Calculates the English square division SELECT AVG (中文版) into the savg from Student2;
IF savg<=70 THEN SET str= ' General ';
ELSEIF savg>70 and savg<=90 THEN SET str= ' good ';
ELSE SET str= ' excellent ';
End IF;
End $ call Pro_testavg (@str);
SELECT @str; </code>

Trigger

<code>select * from employee;
--Log table
CREATE table Test_log (
ID INT PRIMARY KEY auto_increment,
content VARCHAR
)
--Requirements: When you insert a record into the employee table, you want MySQL to automatically insert data into the log table at the same time-
-Create a trigger (add) TRIGGER Tri_empadd after inserts on the 
employee for Each ROW 
-inserts into
test_log (content) VALUES (' Employee table inserts a record ') when inserting a record into the employee table;
Insert data into
employee (Id,empname,deptid) VALUES (7, ' Zagus ', 1);
INSERT into employee (Id,empname,deptid) VALUES (8, ' Zagus 2 ', 1);
--creating triggers (modifying) Create
TRIGGER tri_empupd after UPDATE in employee for each 
ROW 
--
INSERT I When you modify a record to the employee table NTO test_log (content) VALUES (' Employee table modifies a record ');
--Modify
UPDATE employee SET empname= ' Eric ' WHERE id=7;
--Creating a trigger (delete) Create
TRIGGER Tri_empdel after delete in employee for 
every ROW--
INSERT I When you delete a record to the employee table NTO test_log (content) VALUES (' Employee table deletes a record ');
--Deletes delete from
employee WHERE id=7;
SELECT * from employee;
SELECT * from Test_log;
</code>

MySQL Permissions issues

<code>-MySQL Database permissions problem: Root: Have all permissions (can do anything)-
permission account, only partial permissions (curd) For example, you can only manipulate a table in a database-
how do you modify the MySQL user password?
--PASSWORD:MD5 encryption function (one-way encryption)
SELECT PASSWORD (' root '); 
--*81f5e21e35407d884a6cd4a731aebfb6af209e1b-
MySQL database, User configuration: Users table use
MySQL;
SELECT * from USER;
--Modify password
UPDATE USER SET password=password (' 123456 ') WHERE user= ' root ';
--Assign Rights account (select Insert Delete update drop create All)
GRANT Select on Day16.employee to ' Eric ' @ ' localhost ' identifie D by ' 123456 ';
GRANT DELETE on Day16.employee to ' Eric ' @ ' localhost ' identified by ' 123456 ';
</code>

Backup and restore

<code>-backup
mysqldump-u root-p day17 > C:/back.sql
-Restore
mysql-u root-p day17 < d:/back.sql
   </code>