20. Quick review of MySQL statements

Basic operations

View Database

show databases;

Specifying a character Set

create database day15     default character set utf8

View character Sets

show create database day15;

Delete

drop database day15

modifying character sets

alter database day15 default character set gbk;

Working with databases

USE day15;

View all Tables

SHOW TABLES;    

Create a table

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

To view the table structure:

DESC student;

Delete a table

DROP TABLE student;

Increase

-- 插入所有字段。一定依次按顺序插入INSERT INTO student VALUES(1,‘张三‘,‘男‘,20);-- 注意不能少或多字段值-- INSERT INTO student VALUES(2,‘李四‘,‘女‘);-- 插入部分字段INSERT INTO student(id,NAME) VALUES(2,‘李四‘);

Change

-- 修改所有数据（建议少用）UPDATE student SET gender=‘女‘;-- 带条件的修改（推荐使用）UPDATE student SET gender=‘男‘ WHERE id=1;     -- 修改id为1的学生，修改性别为男-- 修改多个字段,注意: SET 字段名=值,字段名=值,....UPDATE student SET gender=‘男‘,age=30 WHERE id=2;

By deleting

-- 删除所有数据（建议少用）DELETE FROM student;-- 带条件的删除(推荐使用)DELETE FROM student WHERE id=2;--  和另一种方式比较    -- delete from: 可以全表删除          1)可以带条件删除      3)使用delete from删除的数据可以回滚（事务）    -- truncate table: 可以全表删除       1）不能带条件删除     2）即可以删除表的数据，也可以删除表的约束     3）使用truncate table删除的数据不能回滚TRUNCATE TABLE student;

Check

---2.1 Query all columns SELECT * FROM student;--2.2 Query Specify column Select Id,name,gender from student;--2.3 query specify alias (AS)--note: In multiple table queries is often used for table aliases SE Lect ID as ' number ', name as ' name ' from student;--2.4 query when adding a constant column--Requirements: Add a class column when querying the student table, with the contents "Java Employment Class" select Id,name,gender,age, ' Java employment class ' as ' Grade ' from student;--2.5 query merge columns-requirements: Query the total of each student's servlet and JSP select Id,name, (servlet+jsp) as ' total ' from stude nt;--Note: Merge columns can only merge fields of numeric types Select ID, (name+servlet) remove duplicate records from student;--2.6 query (DISTINCT)--Requirements: Query the gender of the student select Distin CT Gender from student;--Another syntax select DISTINCT (gender) from student;--demand: Query the area where the student is located select DISTINCT address from student;- -2.7-piece query (where)-2.7.1 logical condition: and (with) or (or)-demand: Query ID 2, and the student named John Doe, select * from student where id=2 and Name= ' John Doe '; --Intersection--demand: Query ID 2, or student named Zhang San select * from student WHERE id=2 or name= ' Zhang San '; --2.7.2 Comparison conditions: > < >= <= = <> (not equal to) between and (equivalent to >= and <=)--demand: query servlet score Large Students in 70 points select * FROM student WHERE servlet>70;--requirements: Query JSP score is greater than or equal to 75, and less thanStudents in 90 minutes select * from student where jsp>=75 and jsp<=90;--another syntax select * FROM student WHERE JSP between and 90; --(after package) SELECT * from student WHERE gender<> ' man ';--2.7.3 empty condition (Null empty string): is null/is not null/= '/<> ' '--demand: Query students with empty addresses (both null and empty strings)--null vs empty string--null: Indicates no value--empty string: Value! --Judge Nullselect * from student where address is null;--To determine the empty string select * from student where address= '; SELECT * FROM student WHERE address is NULL OR address= '; --(including null and empty strings)--Requirements: query students with addresses (not including null and empty strings) SELECT * FROM student WHERE address was not NULL and address<> ';--2.7. 4 fuzzy Condition: like--typically uses the following replacement tags:--%: denotes any character--_: Denotes a character--demand: The student who queried the surname ' Zhang ' select * from student WHERE NAME like ' li% ';--demand: Query Last Name ' Li ', and a student with a name of only two words select * FROM student WHERE name like ' li _ ';--2.8 Aggregate query (query using aggregate function)--common aggregate function: SUM () avg () max () min () c Ount ()--demand: Query the total of the student's servlet (SUM (): Sum function) SELECT sum (servlet) as ' servlet total ' from student;--requirements: Query the average of a student's servlet select AVG (servlet) as ' servlet ' from Student;--Requirements: Query current servlet highest score Select MAX (servlet) as ' highest score ' from student;--Requirement: query min. select min (servlet) as ' lowest score ' from student; --Demand: 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 that use the Count tab, to use a field that does not contain a null value, select COUNT (age) from Student SELECT * FROM student;--2.9 paged query (limit start line, query a few rows)--Starting line from 0--page: The current page per page How many bar-paged query the current page of data Sql:select * from student LIMIT (Current page-1) * How many bars per page, how many are displayed per page;--demand: Query 1th, 2 records (data on page 1th) SELECT * FROM student LIMIT 0,2;--query 3rd, 4 records (2nd page data) SELECT * FRO M Student Limit 2,2;--query 5th, 6 Records (data on page 3rd) SELECT * FROM student limit 4,2;--query 7th, 8 records (no records not shown) SELECT * FROM student limit 6,2;--2.10 Query sort (order BY)-Syntax: ORDER BY field asc/desc--ASC: Sequential, positive order. Value: Increment, Letter: Natural order (A-Z)-desc: Reverse, reverse order. Value: Decrement, Letter: Natural Inverse (z-a)--By default, sort by insert record order SELECT * FROM student;--requirements: Sort by ID sequence select * from student order by ID ASC; SELECT * FROM student the ORDER by ID; --Default positive sequence select * FROM student ORDER by ID desc;---NOTE: Multiple sort criteria-requirements: In accordance with the servlet positive sequence, according to JReverse SP SELECT * from Student ORDER by servlet asc,jsp desc;--2.11 Packet Query (group BY)-demand: Query number of men and women-expected results:-Male 3---female 2--1) count the number of students per group (group by Gender)-2) (COUNT (*)) SELECT gender,count (*) from student GROUP by gender;--2.12 Group Post-query filtering--demand: Total number of people with a query of more than 2 gender--1) Number of men and women-2) screening of records greater than 2 (having)---Note: Before grouping conditions using the WHERE keyword, group before conditions using the HAVING keyword select gender, Count (*) from student WHERE GROUP by gender have COUNT (*) >2;

Practice:

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, ' Harry ', 87,78,77);
INSERT into Student2 (Id,name,chinese,english,math) VALUES (4, ' Lee ', 88,98,92);
INSERT into Student2 (Id,name,chinese,english,math) VALUES (5, ' disparities 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;

– Check the names of all students in the table and the corresponding English scores.
SELECT name,english from Student2;

– Filter duplicate data for 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 ' maths ' from Student2;

– Search for student's grades with name Lee
SELECT * from Student2 WHERE name= ' Lee ';

– Search for students with English scores greater than or equal to 90 points
SELECT * from Student2 WHERE english>=90;

– Search for all students with a total score greater than 200
SELECT * from Student2 WHERE (Chinese+english+math) >200;

– Check the English scores of all students surnamed Li.
SELECT name,english from Student2 WHERE NAME like ' li% ';

– Check the English >80 or the total >200 students
SELECT * from Student2 WHERE english>80 OR (chinese+english+math) >200

– The total score of each student is counted.
SELECT Id,name, (Chinese+english+math) as ' total ' from Student2;

– Add 10 extra-long points to all students ' total scores.
SELECT Id,name, (chinese+english+math+10) as ' total ' from Student2;

Data constraints

---1.1 default value Create TABLE student (ID INT, NAME varchar, address varchar () default ' Guangzhou Tianhe ' – defaults)--When the field does not have a value inserted , MySQL automatically assigns default values to the field insert into student (Id,name) VALUES (1, ' Zhang San ');--note: The Default value field is allowed for Nullinsert into student (Id,name, Address) value (2, ' John Doe ', NULL); INSERT into student (id,name,address) value (3, ' Harry ', ' Guangzhou Panyu ');--1.2 Non-empty--demand: Gender field must have a value (not null) CREATE TABLE student (id int,name varchar), gender varchar (2) Not NULL--non-null)--Non-empty field must be assigned insert INTO S Tudent (Id,name) VALUES (1, ' John Doe ');--Non-null characters cannot be inserted nullinsert into student (Id,name,gender) VALUES (1, ' John Doe ', 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,nam     E) VALUES (1, ' Lisi ');    --ERROR 1062 (23000): Duplicate entry ' 1 ' for key ' ID '--1.4 primary KEY (NOT NULL + unique) CREATE TABLE student (id INT PRIMARY key,--primary key  NAME VARCHAR 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, ' John Doe '); -- Unique constraint violation: Duplicate entry ' 1 ' for key ' PRIMARY '--insert into student (name) value (' John Doe '); --violation of non-null constraint: ERROR 1048 (23000): Column ' id ' cannot be null--1.5 self-growth create TABLE student (ID INT (4) zerofill PRIMARY KEY AUT O_increment,--self-growth, starting from 0 Zerofill 0 fills NAME VARCHAR (20))--The self-growth field can be unassigned, auto-increment insert into student (NAME) VALUES (' Zhang San ');--Can't Shadow Ring self-growth constraint delete from student;--can affect self-growth constraints truncate TABLE student;--1.6 FOREIGN KEY constraint--Employee table CREATE TABLE employee (ID INT PRIMARY key  , EmpName varchar, deptname varchar (20) – Department name) insert into employee VALUES (1, ' Zhang San ', ' Software Development Department '); INSERT INTO employee VALUES (2, ' John Doe ', ' Software Development Department '); INSERT into employee VALUES (3, ' Harry ', ' Application Maintenance ')--to solve the problem of high data redundancy: placing redundant fields in a separate table--design a separate department table create Table Dept (id int PRIMARY key,deptname VARCHAR (20))--Modify the Employee Table CREATE TABLE employee (ID int PRIMARY KEY, empname varcha  R (), DeptID INT,--Change Department name to department ID--Declare a FOREIGN KEY constraint CONSTRAINT emlyee_dept_fk FOREIGN KEY (deptid) REFERENCES Dept (ID) on Update CASCADE on DELETE CASCADE--On CASCADE update: Cascade modified)--Problem: The record is not legal in business, employees insert a nonexistent department data insert into employee VALUES (5, ' Chen VI ', 4);  --Violation of 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 the order of the data: First add the Main table, then add the secondary table data--2) When you have a FOREIGN key constraint, 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 data order: first delete the secondary table, then delete the main table data-Modify Cannot modify the main table directly) 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 Modify (Modify)--Directly Modify Department Update Dept SET id=5 WHERE id=4;--Cascade Delete--Directly delete department delet E from dept WHERE Id=1;

Correlation Query

--Demand: query employees and their departments (Show employee name, department name)--2.1 Cross-connect query (not recommended. Produce a Cartesian product: 4 * 4=16, some are repeating records) SELECT empname,deptname from employee,dept;--requirements: query employees and their departments (Show employee name, department name)-Multi-Table query rules: 1) determine  Which tables 2 are consulted) determine which fields 3) connection condition between table and table (rule: Number of join conditions is number of tables-1)--2.2 connection query: Only results that meet the criteria are displayed (most frequently used) SELECT Empname,deptname-- 2) determine which fields from Employee,dept--1) Determine which tables are queried where employee.deptid=dept.id--3) The Join condition between table and table--another syntax for inner joins select EMPNAME,DEPTN Amefrom Employeeinner join Depton employee.deptid=dept.id;--use alias Select E.empname,d.deptnamefrom Employee EINNER JOIN Dept DON e.deptid=d.id;--Requirements: Query employees in each department--expected results:--Software Development Department Zhang San--Software Development Department John Doe--Application Maintenance Department Harry--Secretary Chen Liu--General office nul L--2.2 left [outer] connection query: Use the data on the left table to match the data on the right table, if the result of matching join condition shows, if the connection condition is not met, then null-(note: Left outer connection: The data of the left table must be finished!) ) SELECT D.deptname,e.empnamefrom Dept dleft OUTER JOIN Employee EON D.id=e.deptid, if the result of a matching join condition is displayed, NULL is displayed if the join condition is not met-- (Note: Right outer connection: the data of the right table will definitely be displayed!) ) SELECT D.deptname,e.empname from employee e right OUTER JOIN dept D on d.id=e.deptid;--2.4 self-connect query-demand: Query employees and their superiors-expected results:--Zhang San null--Li Shizhang--Harry John Doe--Chen Liu Harry select e.empname,b.empnamefrom employee E left OUTER JOIN employee BON E.bossid=b.id;

Stored Procedures

--declaration Terminator--Create stored procedure delimiter $CREATE PROCEDURE pro_test () begin--can write multiple SQL statements; SELECT * from employee; END $--Execute Stored procedure call Pro_test ();--3.1 stored procedure with input parameters-requirements: Pass in an employee ID, query 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) – Out: Output parameter Number begin-Assigning a parameter to set str= ' Helljava '; END $--Delete stored procedure drop PROCEDURE pro_testout;--call--how to accept the value of the return parameter??    --***mysql variable ******--global variable (built-in variable): MySQL database built-in variables (all connections function)--View all global variables: Show variables--View a global variable: select @@ 变量 Name --Modify global variables: Set Variable name = new value--Character_set_client:mysql server's received data encoding--CHARACTER_SET_RESULTS:MYSQL server output data encoding-session variable: only exists in a single connection between the current client and the database server side.    If the connection is broken, then all session variables are lost! --Define Session variables: SET @ variable = value--View Session variable: SELECT @ variable--local variable: The variable used in the stored procedure is called a local variable. Local variables are lost as long as the stored procedure is executed!! --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 variable value select @NAME;--3.3 stored procedure with input and output parameters 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 procedure with conditional judgment-demand: Enter an integer, if 1, return "Monday", if 2, return "Tuesday", if 3, return "Wednesday". Other numbers, return "error input";D elimiter $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 functions-requirements: Enter an integer and sum. For example, input 100, statistic 1-100, and delimiter $CREATE PROCEDURE pro_testwhile (in num int,out result INT) BEGIN--Define a local variable DECLARE i in    T DEFAULT 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 Use the result of the query to assign a value to the variable (into) DELIMITER $CREATE PROCEDURE Pro_findbyid2 (in Eid int,out vname VARCHAR()) BEGIN SELECT EmpName into VName from employee WHERE Id=eid; END $CALL Pro_findbyid2 (1, @NAME); SELECT @NAME;--Practice: Write a stored procedure if the average student's English score is less than or equal to 70, then output ' general ' if the student's English average score is greater than 70, and less than or equal to 90 points, then output ' good ' if the student's English average score is greater than 90 points, then output ' excellent ' D      Elimiter $CREATE PROCEDURE pro_testavg (out str VARCHAR) BEGIN--Define local variables and receive an average of DECLARE savg DOUBLE;      --Calculating English square divide SELECT AVG (中文版) into 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;

Trigger

SELECT * FROM employee;-- 日志表CREATE TABLE test_log(    id INT PRIMARY KEY AUTO_INCREMENT,    content VARCHAR(100))-- 需求： 当向员工表插入一条记录时，希望mysql自动同时往日志表插入数据-- 创建触发器(添加)CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW        -- 当往员工表插入一条记录时 INSERT INTO test_log(content) VALUES(‘员工表插入了一条记录‘);-- 插入数据INSERT INTO employee(id,empName,deptId) VALUES(7,‘扎古斯‘,1);INSERT INTO employee(id,empName,deptId) VALUES(8,‘扎古斯2‘,1);-- 创建触发器(修改)CREATE TRIGGER tri_empUpd AFTER UPDATE ON employee FOR EACH ROW    -- 当往员工表修改一条记录时 INSERT INTO test_log(content) VALUES(‘员工表修改了一条记录‘); -- 修改 UPDATE employee SET empName=‘eric‘ WHERE id=7;-- 创建触发器(删除)CREATE TRIGGER tri_empDel AFTER DELETE ON employee FOR EACH ROW        -- 当往员工表删除一条记录时 INSERT INTO test_log(content) VALUES(‘员工表删除了一条记录‘); -- 删除 DELETE FROM employee WHERE id=7; SELECT * FROM employee; SELECT * FROM test_log;

MySQL Permissions issues

 -- mysql数据库权限问题：root ：拥有所有权限（可以干任何事情） -- 权限账户，只拥有部分权限（CURD）例如，只能操作某个数据库的某张表 -- 如何修改mysql的用户密码？ -- password: md5加密函数(单向加密) SELECT PASSWORD(‘root‘);  -- *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B--  mysql数据库，用户配置 : user表USE mysql;SELECT * FROM USER;-- 修改密码UPDATE USER SET PASSWORD=PASSWORD(‘123456‘) WHERE USER=‘root‘;-- 分配权限账户（select insert delete update drop create all）GRANT SELECT ON day16.employee TO ‘eric‘@‘localhost‘ IDENTIFIED BY ‘123456‘;GRANT DELETE ON day16.employee TO ‘eric‘@‘localhost‘ IDENTIFIED BY ‘123456‘;

Backup and restore

-备份mysqldump -u root -p day17 > c:/back.sql-恢复mysql -u root -p day17 < d:/back.sql

20. Quick review of MySQL statements