Mysql statement Quick Review Tutorial (full) _ MySQL

This article mainly introduces the Mysql statement Quick Review Tutorial (full). For more information, see Basic operations

View database

show databases;

Character set

create database day15 default character set utf8

View character set

show create database day15;

Delete

drop database day15

Modify character set

alter database day15 default character set gbk;

Use Database

USE day15;

View all tables

SHOW TABLES; 

Create a table

CREATE TABLE student(id INT,NAME VARCHAR(20),gender VARCHAR(2),age INT)

View the table structure:

DESC student;

Delete table

DROP TABLE student;

Add

-- Insert all fields. Insert into student VALUES (1, 'Zhang San', 'male', 20) in sequence; -- note that there cannot be less or more field VALUES -- insert into student VALUES (2, 'Lee 4', 'female '); -- INSERT some fields INTO student (id, NAME) VALUES (2, 'Lee 4 ');

Change

-- Modify all data (recommended) UPDATE student SET gender = 'femal'; -- Conditional modification (recommended) UPDATE student SET gender = 'male' WHERE id = 1; -- modify the student id to 1 and gender to male -- modify multiple fields. note: SET field name = value, field name = value ,.... UPDATE student SET gender = 'male', age = 30 WHERE id = 2;

Delete

-- DELETE all data (recommended) delete from student; -- Conditional delete (recommended) delete from student WHERE id = 2; -- compare with another method -- DELETE from: can be deleted from all tables. 1) can be deleted with conditions. 3) data deleted using delete from can be rolled back (transaction) -- truncate table: can be deleted from all tables. 1) cannot be deleted with conditions. 2) you can delete table data or TABLE constraints. 3) data deleted using the truncate table cannot be rolled back;

Query

-- 2.1 query all columns SELECT * FROM student; -- 2.2 query the specified column SELECT id, NAME, gender FROM student; -- 2.3 specify the alias (as) when querying -- Note: in multi-table queries, the table alias SELECT id AS 'number', name as 'name' FROM student; -- 2.4 add a constant column during queries -- requirement: when querying the student table, add a class column with the content "java employment class" SELECT id, NAME, gender, age, 'Java employment class' AS 'year' FROM student; -- 2.5 merge query-demand: query the total servlet and jsp scores of each student. SELECT id, NAME, (servlet + jsp) AS 'total score 'FROM student; -- Note: the merge column can only merge the SELECT id of a numeric field, (NA ME + servlet) FROM student; -- 2.6 duplicate records are removed during query (DISTINCT) -- Requirement: select distinct gender FROM students gender; -- select distinct (gender) FROM student; -- Requirement: query the student's region select distinct address FROM student; -- 2.7 condition query (where) -- 2.7.1 logical condition: and (AND) or (or) -- Requirement: SELECT * FROM student WHERE id = 2 and NAME = 'Lily' for a student whose query id is 2 AND whose NAME is Li Si '; -- intersection -- Requirement: SELECT * FROM student WHERE id = 2 or name = 'zhangsan'; -- union- -2.7.2 comparison condition: ><>==<>( not equal to) between and (equivalent to >=and <=) -- requirement: SELECT * FROM students with servlet scores greater than 70 points WHERE servlet> 70; -- Requirement: query jsp scores greater than or equal to 75, SELECT * FROM students WHERE jsp> = 75 AND jsp <= 90; -- SELECT * FROM student WHERE jsp BETWEEN 75 AND 90; -- (before the package) SELECT * FROM student WHERE gender <> 'male'; -- 2.7.3 condition for determining null (null string ): is null/is not null/= ''/<>'' -- Requirement: query students whose address is null (including null and null strings) -- Null vs null string -- null: indicates no value -- null string: indicates a value! -- Determine nullSELECT * FROM student WHERE address is null; -- determine the NULL string SELECT * FROM student WHERE address = ''; SELECT * FROM student WHERE address is null or address = ''; -- (including null AND null strings) -- Requirement: query the SELECT * FROM students with addresses (excluding null and null strings) WHERE address is not null AND address <> ''; -- 2.7.4 fuzzy condition: like -- usually uses the following replacement Mark: -- %: represents any character -- _: represents a character -- requirement: query the SELECT * FROM students where name like 'Lee % '; -- Requirement: query the surname 'Lee SELECT * FROM students where name like 'Lee _ '; -- 2.8 aggregate query (query using aggregate functions) -- Common aggregate functions: sum () avg () max () min () count () -- Requirement: query the total servlet score of a student (sum (): SUM function) SELECT sum (servlet) AS 'servlet's overall score 'FROM student; -- Requirement: query the average score of a student's servlet select avg (servlet) AS 'servlet's average score' FROM student; -- requirement: query the current servlet highest score select max (servlet) AS 'highest score 'FROM student; -- Requirement: query the latency score select min (servlet) AS 'latency point' FROM student ;-- Requirement: count the number of students (COUNT (field) select count (*) FROM student; SELECT count (id) FROM student; -- Note: count () the number of function Statistics does not include null data -- the number of records in the count statistical table is used. select count (age) FROM student; SELECT * FROM student; -- 2.9 paging query (limit start line, query several rows) -- start line from 0 -- Page: number of lines displayed on each page -- SQL statement used to query data on the current page by page: SELECT * FROM student LIMIT (current page-1) * Number of items displayed on each page and number of items displayed on each page; -- Requirement: query the 1, 2 records (data on the 1st page) SELECT * FROM student LIMIT 2nd; -- query the 3 or 4 records (page data) S ELECT * FROM student LIMIT 3rd; -- query records (page data) SELECT * FROM student LIMIT; -- query records (no records are not displayed) SELECT * FROM student LIMIT 6, 2; -- 2.10 query sorting (order by) -- syntax: order by field asc/desc -- asc: order, positive order. Numeric value: Increment, letter: Natural order (a-z) -- desc: Reverse order, reverse order. Value: decrease, letter: natural reverse order (z-a) -- by default, SELECT * FROM student in the order of inserted records; -- requirement: sort BY id SELECT * FROM student order by id ASC; SELECT * FROM student order by id; -- SELECT * FROM student order by id desc by default in the forward direction -- Note: multiple sorting conditions-requirement: SELECT * FROM student order by servlet ASC, jsp DESC in reverse ORDER by servlet; -- 2.11 group query (group BY) -- requirement: query the number of men and women -- expected results: -- male 3 -- female 2 -- 1) GROUP students BY gender -- 2) COUNT the number of people in each GROUP (COUNT (*) SELECT gender, COUNT (*) FROM student group by gender; -- 2.12 filter after grouping query -- requirement: query gender whose total number is greater than 2 -- 1) query the number of men and women -- 2) filter records whose number is greater than 2 (having) --- Note: Before grouping, use the where keyword, before grouping, use the having keyword SELECT gender, COUNT (*) FROM student where group by gender having count (*)> 2;

Exercise:

CREATE TABLE student2(id INT,NAME VARCHAR(20),chinese FLOAT,english FLOAT,math FLOAT);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, 'Wang 5', 87,78, 77); insert into student2 (id, NAME, chinese, english, math) VALUES (4, 'Li yi', 88,98, 92); insert into student2 (id, NAME, chinese, english, math) VALUES (5, 'lilaicai ', 67); insert into student2 (id, NAME, chinese, english, math) VALUES (6, 'Zhang jinbao', 45); insert into student2 (id, NAME, chinese, english, math) VALUES (7, 'Huang Rong ', 30);-query information of all students in the table. SELECT * FROM student2;-query the names and English scores of all students in the table. Select name, english FROM student2;-filter the duplicate english score data in the table select distinct (english) FROM student2;-use an alias to indicate the student score. Select name as 'name', chinese AS 'China', english AS 'English ', math AS 'mate' FROM student2; -SELECT * FROM student2 where name = 'Li Yi';-SELECT * FROM student2 WHERE english score> = 90; -SELECT * FROM student2 WHERE (chinese + english + math)> 200 for all students whose total score is greater than 200;-query the english scores of all students surnamed Li. Select name, english FROM student2 where name like 'Li % '; -SELECT * FROM student2 WHERE english> 80 OR (chinese + english + math)> 200 for students who query english> 80 OR whose total score> 200-calculate the total score of each student. SELECT id, NAME, (chinese + english + math) AS 'total score 'FROM student2;-add 10 points of expertise to the total score of all students. SELECT id, NAME, (chinese + english + math + 10) AS 'total score 'FROM student2;

Data constraints

-- 1.1 DEFAULT value: create table student (id INT, name varchar (20), address VARCHAR (20) DEFAULT 'Guangzhou Tianhe '-- DEFAULT value) -- When the field has no inserted value, mysql automatically assigns the default value insert into student (id, NAME) VALUES (1, 'Zhang San') to this field; -- Note: The default value of the field can be nullINSERT INTO student (id, NAME, address) VALUE (2, 'Li Si', NULL); insert into student (id, NAME, address) VALUE (3, 'Wang 5', 'Guangzhou Panyu '); -- 1.2 non-null -- requirement: the gender field must have a value (not null) create table student (id INT, name varchar (20), gender VARCHAR (2) not null -- non-empty) -- The non-empty field must be assigned an insert into student (id, NAME) VALUES (1, 'Li si '); -- non-NULL characters cannot be inserted INTO nullINSERT INTO student (id, NAME, gender) VALUES (1, 'Lily', NULL); -- 1.3 unique create table student (id int unique, -- unique name varchar (20) 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 (not empty + unique) create table student (id int primary key, -- primary key name varchar (20) insert into student (id, NAME) VALUES (1, 'Zhang San'); insert into student (id, NAME) VALUES (2, 'Zhang San'); -- insert into student (id, NAME) VALUES (1, 'Li si'); -- violates the unique constraint: duplicate entry '1' for key 'primary' -- insert into student (name) value ('Lily'); -- violation of non-null constraint: ERROR 1048 (23000 ): column 'id' cannot be null -- 1.5 auto-increment create table student (id INT (4) zerofill primary key AUTO_INCREMENT, -- Auto-increment, zero-padding name varchar (20) from 0 )) -- Auto-increment fields can be automatically inserted INTO student (NAME) VALUES ('zhangsan') without assigning VALUES; -- the auto-increment constraint cannot be affected: delete from student; -- the self-increment constraint can be affected: truncate table student; -- 1.6 foreign KEY constraint -- employee table create table employee (id int primary key, empName VARCHAR (20), deptName VARCHAR (20) -- Department name) insert into employee VALUES (1, 'Zhang San', 'software Demo'); insert into employee VALUES (2, 'Li Si', 'software Demo'); insert into employee VALUES (3, 'Wang Wu ', 'application maintenance part'); -- solves the problem of high data redundancy: place redundant fields in an independent TABLE -- design a department table create table dept (id int primary key, deptName VARCHAR (20) -- modify the employee table create table employee (id int primary key, empName VARCHAR (20), deptId INT, -- change department name to Department ID -- declare a foreign key constraint emlyee_dept_fk foreign key (deptId) REFERENCES dept (id) on update cascade on delete cascade -- on cascade update: CASCADE modification) -- problem: the record is invalid in business. an employee inserts a non-existent department data insert into employee VALUES (5, 'Chen 6', 4); -- violates the foreign key constraint: 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 data in the order: add the primary table first, add the sub-table data -- 2) when the foreign key constraint exists, modify the data sequence: first modify the sub-table and then modify the master table data -- 3) when the foreign key constraint exists, sequence of data deletion: delete the sub-table first, and then delete the data in the master table -- modify the department (the master table cannot be modified directly) UPDATE dept SET id = 4 WHERE id = 3; -- modify the employee table UPDATE employee SET deptId = 2 WHERE id = 4; -- DELETE Department delete from dept WHERE id = 2; -- DELETE employee table delete from employee WHERE deptId = 2; -- cascade modify -- directly modify the department UPDATE dept SET id = 5 WHERE id = 4; -- cascade DELETE -- directly DELETE the department delete from dept WHERE id = 1;

Join query

-- Requirement: query employees and their departments (display employee names and department names) -- 2.1 Cross-join query (not recommended. Produce Cartesian product: 4*4 = 16, some are repeated records) SELECT empName, deptName FROM employee, dept; -- Requirement: query the employee and its Department (display employee name, department name) -- multi-table query rules: 1) determine which tables to query 2) determine which fields 3) join conditions between tables (rule: number of join conditions is the number of tables-1) -- 2.2 join query: only the results that meet the conditions are displayed (the most frequently used) SELECT empName, deptName -- 2) determine which fields FROM employee, dept -- 1) determine which tables to query WHERE employee. deptId = dept. id -- 3) JOIN condition between the table -- another syntax of the inner JOIN: SELECT empName, deptNameFROM employeeINNER JOIN deptON employee. deptId = dept. id; -- use the alias SELECT e. empName, d. deptNameFROM employee eINNER JOIN dept dON e. deptId = d. id; -- Requirement: query the employees of each department -- expected results: -- Software development department Zhang San -- Software development department Li Si -- Application Maintenance Department Wang Wu -- Operation Department Chen 6 -- General Administration Department null -- 2.2 left [outer] connection query: use the data in the left table to match the data in the right table,

If the connection condition is met, the result is displayed. if the connection condition is not met, the result is null.

-- (Note: left outer join: the data in the left table will be displayed !) SELECT d. deptName, e. empNameFROM dept dLEFT outer join employee eON d. id = e. deptId;

If the connection condition is met, the result is displayed. if the connection condition is not met, the result is null.

-- (Note: right outer join: the data in the right table will be displayed !) SELECT d. deptName, e. empNameFROM employee eRIGHT outer join dept dON d. id = e. deptId; -- 2.4 Self-connection query -- Requirement: query employees and their superiors -- expected results: -- Zhang San null -- Li Si Zhang San -- Wang Wu Li Si -- Chen Liu Wang Wu SELECT e. empName, B. empNameFROM employee e left outer join employee e. bossId = B. id;

Stored Procedure

-- Declare the Terminator -- CREATE a stored procedure delimiter $ create procedure pro_test () BEGIN -- multiple SQL statements can be written; SELECT * FROM employee; END $ -- execute the stored PROCEDURE CALL pro_test (); -- 3.1 stored PROCEDURE with input parameters -- Requirement: input the id of an employee and query the employee information DELIMITER $ create procedure pro_findById (IN eid INT) -- IN: input parameter BEGINSELECT * FROM employee WHERE id = eid; END $ -- CALL pro_findById (4 ); -- 3.2 Stored PROCEDURE with output parameters DELIMITER $ create procedure pro_testOut (OUT str VARCHAR (20) -- OUT: output parameter BEGIN -- Assign the parameter SET str = 'helljava'; END $ -- delete the stored procedure drop procedure pro_testOut; -- call -- how to accept the value of the returned parameter ?? -- *** Mysql variable *** -- global variable (built-in variable): built-in variable of mysql database (all connections work) -- View all global variables: show variables -- view a global variable: select @ variable name -- modify global variable: set variable name = new value -- character_set_client: the encoding of data received by the mysql server -- character_set_results: encoding of mysql server output data-session variable: only exists in a connection between the current client and the database server. If the connection is disconnected, all session variables are lost! -- Define session variables: set @ variable = value -- View session variables: select @ variable -- local variables: variables used in stored procedures are called local variables. As long as the stored procedure is executed, local variables are lost !! -- 1) define a session variable name; 2) use the name session variable to receive the return value of the stored procedure CALL pro_testOut (@ NAME); -- view the variable value SELECT @ NAME; -- 3.3 stored procedure delimiter $ create procedure pro_testInOut (INOUT n INT) -- INOUT: input and output parameter BEGIN -- view the variable SELECT n; SET n = 500; END $ -- call set @ n = 10; CALL pro_testInOut (@ n); SELECT @ n; -- 3.4 stored procedure with conditional judgment -- Requirement: enter an integer if 1, "Monday" is returned. if 2, "Tuesday" is returned. if 3, "Wednesday" is returned ". For other numbers, "error input" is returned. DELIMITER $ create procedure pro_testIf (IN num INT, OUT str VARCHAR (20) BEGINIF num = 1 THENSET str = 'Monday '; ELSEIF num = 2 THENSET str = 'tues'; ELSEIF num = 3 THENSET str = 'wedday'; ELSESET str = 'input error'; end if; END $ CALL pro_testIf (4, @ str); SELECT @ str; -- 3.5 stored procedure with the cyclic function -- Requirement: enter an integer and sum it. For example, input 100, count 1-and DELIMITER $ create procedure pro_testWhile (IN num INT, OUT result INT) BEGIN -- define a local variable DECLARE I INT DEFAULT 1; DECLARE vsum int default 0; WHILE I <= num DOSET vsum = vsum + I; SET I = I + 1; END WHILE; SET result = vsum; END $ DROP PROCEDURE pro_testWhile; CALL pro_testWhile (100, @ result); SELECT @ result; -- 3.6 assign a value to the variable (INTO) DELIMITER $ create procedure pro_findById2 (IN eid INT, OUT vname VARCHAR (20) BEGINSELECT empName INTO vname FROM employee WHERE id = eid; END $ CALL pro_findById2 (1, @ NAME); SELECT @ NAME; -- exercise: if the average English score of a student is less than or equal to 70, the output is 'average '. if the average English score of a student is greater than 70 and less than or equal to 90, if the average English score of a student is greater than 90, the output is 'excellent 'DELIMITER $ create procedure pro_testAvg (OUT str VARCHAR (20) BEGIN -- defines local variables, average receiving score DECLARE savg DOUBLE; -- calculate the english square score select avg (english) INTO savg FROM student2; IF savg <= 70 THENSET str = 'general '; ELSEIF savg> 70 AND savg <= 90 THENSET str = 'excellent '; ELSESET str = 'excellent'; end if; END $ CALL pro_testAvg (@ str); SELECT @ str;

Trigger

SELECT * FROM employee; -- create table test_log (id int primary key AUTO_INCREMENT, content VARCHAR (100) -- Requirement: when a record is inserted into the employee TABLE, you want mysql to automatically INSERT data to the log table at the same time -- create trigger (add) create trigger tri_empAdd after insert on employee for each row -- insert into test_log (content) when inserting a record INTO the employee table) VALUES ('employee table inserted with a record '); -- INSERT data insert into employee (id, empName, deptId) VALUES (7, 'daguse', 1 ); insert into employee (id, empName, deptId) VALUES (8, 'zagos 2', 1); -- Create a trigger (Modify) create trigger tri_empUpd after update on employee for each row -- insert into test_log (content) VALUES ('employee table modified record ') when modifying a record to employee table '); -- modify UPDATE employee SET empName = 'Eric 'WHERE id = 7; -- Create a trigger (delete) create trigger tri_empDel after delete on employee for each row -- insert into test_log (content) VALUES ('employee table deleted record ') when deleting a record from employee table '); -- delete from employee WHERE id = 7; SELECT * FROM employee; SELECT * FROM test_log;

Mysql permission problems

-- Mysql database permission problem: root: has all permissions (can do anything) -- permission account, only has some permissions (CURD) for example, only a table in a database can be operated. how can I modify the mysql User password? -- Password: md5 encryption function (one-way encryption) select password ('root'); -- * 81F5E21E35407D884A6CD4A731AEBFB6AF209E1B -- mysql database, user configuration: USER table USE mysql; SELECT * FROM user; -- change password update user set password = PASSWORD ('20140901') where user = 'root'; -- assign permission account (select insert delete update drop create all) grant select on day16.employee TO 'Eric '@ 'localhost' identified by '000000'; grant delete on day16.employee TO 'Eric' @ 'localhost' identified by '2016 ';

Backup and restoration

-Backup mysqldump-u root-p day17> c:/back. SQL-restore mysql-u root-p day17 <d:/back. SQL