Mysql statement Quick review Tutorial of full

Basic operation

View the database

<code>show databases;
</code>

Specified 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>

Modified character set

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

Using a database

<code>USE day15;
</code>

View all tables

<code>SHOW TABLES; 
</code>

Create a table

<code>CREATE TABLE student(
id INT,
NAME VARCHAR(20),
gender VARCHAR(2),
age INT
)
</code>

View table structure:

<code>DESC student;
</code>

Delete table

<code>DROP TABLE student;
</code>

increase

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

0

change

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

1

delete

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

2

check

<code>-- 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 ) 
--  Note:   In multi-table queries tables are often used as aliases 
SELECT id AS ' Serial number ',NAME AS ' The name ' FROM student;
-- 2.4  Add constant columns when querying 
--  Requirements:   In the query student Table to add 1 Class column, the content is" java Employment class" 
SELECT id,NAME,gender,age,'java Employment class ' AS ' grade ' FROM student;
-- 2.5  Merge columns when querying 
--  Requirements:   Query each student's servlet and jsp The total grade 
SELECT id,NAME,(servlet+jsp) AS ' Total grade ' FROM student;
--  Note: merge columns can only merge fields of numeric type 
SELECT id,(NAME+servlet) FROM student;
-- 2.6  Remove duplicate records when querying (DISTINCT)
--  Requirements:   Check the gender of the student   male   female 
SELECT DISTINCT gender FROM student;
--  On the other 1 Kind of grammar 
SELECT DISTINCT(gender) FROM student;
--  demand :  Check the student's district 
SELECT DISTINCT address FROM student;
-- 2.7  Conditions of the query (where)
-- 2.7.1  Logical conditions:  and( with ) or( or )
--  Requirements:   The query id for 2 And his name is Li 4 The students of 
SELECT * FROM student WHERE id=2 AND NAME=' li 4'; --  intersection 
--  Requirements:   The query id for 2 , or zhang by name 3 The students of 
SELECT * FROM student WHERE id=2 OR NAME=' zhang 3'; --  And set 
-- 2.7.2  Comparison conditions:  > < >= <= = <>( Is not equal to ) 
between and ( Is equivalent to >=  and  <=)
--  Requirements:   The query servlet Scores greater than 70 Points of the students 
SELECT * FROM student WHERE servlet>70;
--  Requirements:   The query jsp Score greater than or equal to 75 And is less than or equal to 90 Points of the students 
SELECT * FROM student WHERE jsp>=75 AND jsp<=90;
--  On the other 1 A grammar 
SELECT * FROM student WHERE jsp BETWEEN 75 AND 90; -- ( After the bag before package )
SELECT * FROM student WHERE gender<>' male ';
-- 2.7.3  Sentenced to air condition (null  An empty string ) :  is null / is not null / ='' / <>''
--  Requirements:   Query the student whose address is empty (including null And empty strings) 
-- null vs  An empty string 
-- null : indicates no value 
--  Empty string: valued! 
--  judge null
SELECT * FROM student WHERE address IS NULL ;
--  Determine empty string 
SELECT * FROM student WHERE address='';
SELECT * FROM student WHERE address IS NULL OR address=''; --  (including null And empty strings) 
--  Requirements:   Look up students with addresses ( Do not include null And an empty string )
SELECT * FROM student WHERE address IS NOT NULL AND address<>'';
-- 2.7.4  Fuzzy conditions:  like
--  The following substitution marks are usually used: 
-- % :  Represents any character 
-- _ :  said 1 A character 
--  Requirements:   Enquire surname 'Zhang ' The students of 
SELECT * FROM student WHERE NAME LIKE ' li %';
--  Requirements:   Enquire surname 'Li ' , whose names are only two words 
SELECT * FROM student WHERE NAME LIKE ' li _';
-- 2.8  Aggregate queries (queries that use aggregate functions) 
--  Common aggregate functions:  sum() avg() max() min() count()
--  Requirement: Check the student's servlet The total grade  (sum() : The sum function )
SELECT SUM(servlet) AS 'servlet The total grade ' FROM student;
--  Requirements:   Enquire student's servlet The average of 
SELECT AVG(servlet) AS 'servlet The average of ' FROM student;
--  demand :  Query the current servlet The highest 
SELECT MAX(servlet) AS ' The highest ' FROM student;
--  Requirements:   Query minimum score 
SELECT MIN(servlet) AS ' The lowest points ' FROM student;
--  Requirements:   Count the current number of students (count( field ))
SELECT COUNT(*) FROM student;
SELECT COUNT(id) FROM student;
--  Note: count () The number of function statistics is not included null The data of 
--  use count The number of records in the statistics table to be used is not included null The value of the field 
SELECT COUNT(age) FROM student;
SELECT * FROM student;
-- 2.9  Paging queries ( limit  The starting line , Query a few lines) 
--  Start from the 0 start 
--  Paging: The current page   How many pages are displayed per page 
--  Paged to query the data for the current page sql: 
SELECT * FROM student LIMIT 
( The current page -1)* How many pages are displayed per page , How many pages are displayed per page ;
--  Requirements:   Query the first 1,2 Article 1 Record (No 1 Page data) 
SELECT * FROM student LIMIT 0,2;
--  Query the first 3,4 Article 1 Record (No 2 Page data) 
SELECT * FROM student LIMIT 2,2;
--  Query the first 5,6 Article 1 Record (No 3 Page data) 
SELECT * FROM student LIMIT 4,2;
--  Query the first 7,8 records  ( No records are not displayed )
SELECT * FROM student LIMIT 6,2;
-- 2.10  Query sort ( order by  ) 
--  grammar   : order by  field  asc/desc
-- asc:  Order, positive order. Numeric value: increasing, alphabetic: Natural order ( a-z ) 
-- desc:  In reverse order. Values: decrement, letters: natural reverse order (z-a)
--  By default, sort by insertion order 
SELECT * FROM student;
--  Requirements:   In accordance with the id order 
SELECT * FROM student ORDER BY id ASC;
SELECT * FROM student ORDER BY id; --  The positive sequence 
SELECT * FROM student ORDER BY id DESC;--  trans 
--  Note: Multiple sort criteria 
--  Requirements:   In accordance with the servlet Positive order, according to jsp In the reverse order 
SELECT * FROM student ORDER BY servlet ASC,jsp DESC;
-- 2.11  Grouping query (group by)
--  Requirements:   Check the number of men and women 
--  Expected results: 
--  male  3
---  female  2
-- 1)  Group the students according to their sex (GROUP 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 queries 
--  Requirements:   The total number of queries is greater than 2 The gender of the 
-- 1)  Check the number of men and women 
-- 2 ) The screening number is greater than 2 The record of (having)
---  Note:   Conditional use before grouping where Keywords are grouped before conditional use having The keyword 
SELECT gender,COUNT(*) FROM student WHERE GROUP BY gender HAVING COUNT(*)>2;
</code>

Practice:

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

4

Data constraints

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

5

Associated query

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

6

The results are displayed if the connection conditions are met and null if the connection conditions are not met

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

7

The results are displayed if the connection conditions are met and null if the connection conditions are not met

--  (note:   Right outer join: Data from the right table 1 The display will be complete! 
SELECT d.deptName,e.empName
FROM employee e
RIGHT OUTER JOIN dept d
ON d.id=e.deptId;
-- 2.4  Self-join query 
--  Needs: Check with employees and their supervisors 
--  Expected results:  
--  zhang 3 null
--  li 4  zhang 3
--  The king 5  li 4
--  Chen 6  The king 5
SELECT e.empName,b.empName
FROM employee e 
LEFT OUTER JOIN employee b
ON e.bossId=b.id;
</code>

The stored procedure

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

9

The trigger

<code>SELECT * FROM employee;
--  The log table 
CREATE TABLE test_log(
id INT PRIMARY KEY AUTO_INCREMENT,
content VARCHAR(100)
)
--  Requirements:   When inserting into the staff table 1 When a record is made, hope mysql Automatic simultaneous log table inserts data 
--  Create trigger ( add )
CREATE TRIGGER tri_empAdd AFTER INSERT ON 
employee FOR EACH ROW 
--  When inserting into the staff table 1 When the records 
INSERT INTO test_log(content) VALUES(' The staff table is inserted 1 records ');
--  Insert data 
INSERT INTO employee(id,empName,deptId) VALUES(7,' Mr. Gus ',1);
INSERT INTO employee(id,empName,deptId) VALUES(8,' Mr. Gus 2',1);
--  Create trigger ( Modify the )
CREATE TRIGGER tri_empUpd AFTER UPDATE ON employee 
FOR EACH ROW 
--  When modified to staff table 1 When the records 
INSERT INTO test_log(content) VALUES(' The staff list has been changed 1 records ');
--  Modify the 
UPDATE employee SET empName='eric' WHERE id=7;
--  Create trigger ( delete )
CREATE TRIGGER tri_empDel AFTER DELETE ON employee FOR 
EACH ROW 
--  When deleted to staff table 1 When the records 
INSERT INTO test_log(content) VALUES(' The staff list has been deleted 1 records ');
--  delete 
DELETE FROM employee WHERE id=7;
SELECT * FROM employee;
SELECT * FROM test_log;
</code>

mysql permission issues

<code> -- mysql Database permission issues: root  Have all the authority (to do anything) 
--  Access account, with only partial access ( CURD ) For example, you can only manipulate a table in a database 
--  How to modify 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 the password 
UPDATE USER SET PASSWORD=PASSWORD('123456') WHERE USER='root';
--  Authorization account ( 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';
</code>

Backup and restore

<code>- The backup 
mysqldump -u root -p day17 > c:/back.sql
- restore 
mysql -u root -p day17 < d:/back.sql
</code>