• I. Operating databases
  • 1.1 Create a database
  • 1.2 View the database
  • 1.3 Modification of database
  • 1.4 Delete the database
  • 1.5 Select Database
• II. Operational Tables
  • 2.1 Create tables
  • 2.2 View Table
  • 2.3 Amendment Table
  • 2.4 Delete tables
• III. CRUD Recording of Operational Table
  • 3.1 INSERT
  • 3.2 UPDATE
  • 3.3 DELETE
  • 3.4 SELECT
• IV. Backup and restore database
• 5. Multi-table design and multi-table query
  • 5.1 Foreign Key Constraints
  • 5.2 Multimeter Design
  • 5.3 Multi-table Query
• Six, others

I. Operating databases

1.1 Create a database

 
CREATE  DATABASE  [IF NOT EXISTS] db_name [create_specification [, create_specification] ...] 
    create_specification:    
      [DEFAULT] CHARACTER SET charset_name  |   [DEFAULT] COLLATE collation_name 
-- 1. ~Create a name of mydb1 Database.
    create database mydb1;
-- 2.~Create a use gbk Character set mydb2 Database.
    create database mydb2 character set gbk;
-- 3.~Create a use utf8 Character set with proofreading rules mydb3 Database.
    create database mydb3 character set utf-8 collate utf8_bin;

1.2 View the database

-- Display database statements:
SHOW DATABASES
-- Display database creation statements:
SHOW CREATE DATABASE db_name

-- 1. ~View all databases in the current database server 
show databases;
-- 2. ~Look at the one created earlier mydb2 Definition information of database
show create database mydb3;

1.3 modify database

    ALTER  DATABASE  [IF NOT EXISTS] db_name  [alter_specification [, alter_specification] ...] 
    alter_specification:    
    [DEFAULT] CHARACTER SET charset_name  |   [DEFAULT] COLLATE collation_name

-- 1. ~View the database in the server and put it in it mydb2 Character set modified to utf8
    alter database mydb2 character set utf8;

1.4 Delete the database

DROP DATABASE  [IF EXISTS]  db_name 
-- 1.  ~Delete the previously created mydb1 data base 
drop database mydb1;

1.5 Select Database

--  1. Enter the database:
USE db_name;
--2.View the currently selected database: 
SELECT DATABASE();

II. Operational Tables

2.1 Create tables

CREATE TABLE table_name
(
    field1  datatype,
    field2  datatype,
    field3  datatype,
)[character set character set] [collate Proofreading rules]

-- field: Specified column name　datatype: Specify column type

-- ~Create an employee table employee 

    CREATE TABLE employee(
        id int PRIMARY KEY AUTO_INCREMENT,
        name varchar(20) UNIQUE,
        gender bit NOT NULL,
        birthday date,
        entry_date date,
        job varchar(40),
        salary double,
        resume text
    );

2.2 View Table

-- View table structure:
DESC tab_name
-- View all tables in the current database:
SHOW TABLES;
-- View the current database table build statement 
SHOW CREATE TABLE tab_name;

2.3 Amendment Table

ALTER TABLE table_name  ADD/MODIFY/DROP/CHARACTER SET/CHANGE  (column datatype [DEFAULT expr][, column datatype]...);

-- *Modify the name of the table:
RENAME TABLE Table name TO New table name;
    
    -- 1. ~Basically add one to the staff table above image Column.
    alter table employee add image blob;
    
    -- 2.~modify job Column, make it 60 in length.
    alter table employee modify job varchar(60);
    
    -- 3. ~delete gender Column.
    alter table employee drop gender;
    
    -- 4. ~Change of table name to user. 
    rename table employee to user;
    
    -- 5. ~Modify the character set of the table to gbk
    alter table user character set gbk;
        
    -- 6. ~Column names name Modified to username
    alter table user change name username varchar(20);

2.4 Delete tables

DROP TABLE tab_name;

-- ~delete user surface
drop table user;

III. CRUD Recording of Operational Table

3.1 INSERT

INSERT INTO table [(column [, column...])] VALUES (value [, value...]);

-- The data inserted should be of the same type as the data in the field.
-- The size of the data should be within the specified range of columns, for example, a string of 80 length cannot be added to a column of 40 length.
-- stay values The data positions listed in the list must correspond to the ranking positions of the columns to be added.
-- Character and date data should be included in single quotation marks.
-- Insert null values: not specified or insert into table value(null)
-- If you want to insert all fields, you can save the column list and write the value list directly in the order of the fields in the table.

-- ~Use insert Statement inserts information for three employees into the table
INSERT INTO employee (id,name,gender,birthday,entry_date,job,salary,resume) VALUES (null,'Zhang Fei',1,'1999-09-09','1999-10-01','Beater',998.0,'The eldest brother's third brother,Really good at playing.');
INSERT INTO employee VALUES (null,'Guan Yu',1,'1998-08-08','1998-10-01','God of wealth',9999999.00,'The eldest brother's second brother,The company's earnings were directed at him.');
-- Insert multiple data
INSERT INTO employee VALUES (null,'Liu Bei',0,'1990-01-01','1991-01-01','ceo',100000.0,'The head of the company'),(null,'Zhao Yun',1,'2000-01-01','2001-01-01','Bodyguard',1000.0,'The eldest man is close to him');

3.2 UPDATE

UPDATE  tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]  
    
-- UPDATE The grammar can update the columns in the original table rows with new values.
-- SET The clause indicates which columns to modify and which values to give.
-- WHERE The clause specifies which rows should be updated. If not WHERE Clause updates all rows
    

-- 1. ~Modify the salary of all employees to 5000 yuan.
update employee set salary = 5000;
-- 2. ~Name is'Zhang Fei'The salary of employees was revised to 3000 yuan.
update employee set salary = 3000 where name='Zhang Fei';
-- 3. ~Name is'Guan Yu'The salary of employees was revised to 4000 yuan.,job Change to ccc. 
update employee set salary=4000,job='ccc' where name='Guan Yu';
-- 4. ~Liu Bei's salary will be increased by 1000 yuan on the original basis.
update employee set salary=salary+1000 where name='Liu Bei';

3.3 DELETE

DELETE FROM tbl_name [WHERE where_definition]    

-- If not used where Clause, all data in the table will be deleted.
-- Delete Statement cannot delete the value of a column (you can use it) update)
-- Use Delete Statement deletes only records, not the table itself. To delete a table, use drop table Sentence.
-- with insert and update Similarly, deleting records from one table will cause referential integrity problems for other tables. When modifying database data, you should always keep this potential problem in mind. Foreign key constraint
-- Deleting data from tables can also be used TRUNCATE TABLE Statement, it and delete It's different, see mysql File.

-- 1. ~Delete the name in the table as'Zhang Fei'Records.
delete from employee where name='Zhang Fei';
-- 2. ~Delete all records in the table.
delete from employee;
-- 3. ~Use truncate Delete records from tables.
truncate table employee;

3.4 SELECT

~1.Basic query
SELECT [DISTINCT] *|{column1, column2. column3..} FROM  table;
    
~Query the information of all the students in the table.
    select * from exam;
~The names of all the students in the inquiry table and their corresponding English scores.
    select name,english from exam;
~Duplicate data in filter tables
     select distinct english from exam;
~Adding 10 points to all the students'scores shows their specialty.
    select name , math+10,english+10,chinese+10 from exam;
~The total score of each student is counted.
    select name ,english+math+chinese from exam;
//Use the alias to indicate the total score of students.
    select name as Full name ,english+math+chinese as Total score from exam;
    select name Full name ,english+math+chinese Total score from exam;
select name english from exam;

~2.Use where Filtering queries with clauses
~Search for the student's performance under Zhang Fei's name
    select * from exam where name='Zhang Fei';
~Query students whose English scores are more than 90
    select * from exam where english > 90;
~All the students whose total score is more than 230
    select name Full name,math+english+chinese Total score from exam where math+english+chinese>230;
~Query English score at 80－100 Between classmates.
    select * from exam where english between 80 and 100;
~Query math score is 75,76,77 Classmate.
    select * from exam where math in(75,76,77);
~Query all the students whose surname is Zhang.
    select * from exam where name like 'Zhang%';
    select * from exam where name like 'Zhang__';
~Query Mathematical Score>70，Language score>80 Classmate.
    select * from exam where math>70 and chinese>80;

~3.Use order by Keyword sorting of query results
SELECT column1, column2. column3.. FROM table where... order by column asc|desc;
asc Ascending order -- The default is ascending order.
desc Descending order

~Sort the Chinese scores and output them.
    select name,chinese from exam order by chinese desc;
~Sort the total scores and output them in order from high to low
    select name Full name,chinese+math+english Total score from exam order by Total score desc;
~Output of Grade Ranking for Students surnamed Zhang
    select name Full name,chinese+math+english Total score from exam where name like 'Zhang%' order by Total score desc;
~4.Aggregate function
(1)Count -- Used to count the number of qualified rows
    ~How many students are there in a class?
        select count(*) from exam;
    ~How many students have statistical math scores greater than 90?
        select count(*) from exam where math>70;
    ~How many people have a total score greater than 230?
        select count(*)from exam where math+english+chinese > 230;
(2)SUM -- A specified column for summing qualified records
    ~Statistics of the total math scores of a class?
        select sum(math) from exam;
    ~Statistics of the total scores of Chinese, English and Mathematics in a class
        select sum(math),sum(english),sum(chinese) from exam;
    ~Statistics of the total scores of Chinese, English and Mathematics in a class
        select sum(ifnull(chinese,0)+ifnull(english,0)+ifnull(math,0)) from exam;
        //When performing a calculation, the whole structure of the calculation is null as long as null is involved in the calculation.
        //In this case, the ifnull function can be used for processing.
    ~Statistical average score of a class's language achievement
        select sum(chinese)/count(*) Average score of Chinese from exam;
(3)AVG -- The average value of the specified column used to calculate eligible records
    ~Find a math average score for a class?
        select avg(math) from exam;
    ~Find an average of the total score of a class?
        select avg(ifnull(chinese,0)+ifnull(english,0)+ifnull(math,0)) from exam;

(4)MAX/MIN -- Used to obtain the maximum and minimum values of all qualified record specified columns
    ~Finding the highest and lowest grade in a class
        select max(ifnull(chinese,0)+ifnull(english,0)+ifnull(math,0)) from exam;
        select min(ifnull(chinese,0)+ifnull(english,0)+ifnull(math,0)) from exam;
~5.Group query
~After classifying the items in the order form, the total price of each category is displayed.
    select product,sum(price) from orders group by product;
~Consult and purchase several types of goods, each with a total price of more than 100
    select product Trade name,sum(price)Total commodity price from orders group by product having sum(price)>100;
    
where Clause and having Differences in Clauses:
    where Clauses are filtered before grouping having The clauses are filtered after grouping
    having Aggregation functions can be used in Clauses,where Can't be used in Clauses
    //In many cases, where the where clause is used, the having clause can be used instead.

~Query the name of goods whose unit price is less than 100 and whose total price is more than 150
    select product from orders where price<100 group by product having sum(price)>150;
    
    

~~sql Sentence Writing Order: 
select from where groupby having orderby
~~sql Statement execution order:
from where select group by having order by   

IV. Backup and restore database

Mode 1:
Backup: mysqldump-u root-p dbName > c:/1.sql under cmd window
 Recovery: Mode 1: mysql-u root-p dbName under cmd window < c:/1.sql
 Mode 2:
Under the mysql command, source c:/1.sql
 It should be noted that the recovery data can only recover the data itself, but the database can not be recovered. It needs to create the data before recovery.

5. Multi-table design and multi-table query

5.1 Foreign Key Constraints

CREATE TABLE dept(
        id int PRIMARY KEY AUTO_INCREMENT,
        name varchar(20)
    );

INSERT INTO dept values(null,'Finance Department'),(null,'Ministry of Personnel'),(null,'Sales Department'),(null,'Administration Department');

create table emp(
    id int PRIMARY KEY AUTO_INCREMENT,
    name varchar(20),
    dept_id int,
    FOREIGN  KEY (dept_id) REFERENCES dept(id)
 );
 
INSERT INTO emp values(null,'Xiao Ming',1),(null,'Harry Potter',2),(null,'Li Si',3),(null,'Zhang San',3);

5.2 Multimeter Design

• One-to-many: The primary key of one side saved by one side in the multiple side serves as the foreign key.
• One-to-one: Keep the other party's primary key as a foreign key on either side
• Many-to-many: Create a third-party relational table and save the primary key of the two tables as the foreign key to preserve their corresponding relationships

5.3 Multi-table Query

• Cartesian product query: Cartesian product query is the result of multiplying the records of two tables. If the left table has n records and the right table has m records, Cartesian product query has n*m records, which often contains many wrong data, so this query method is not often used.

select * from dept,emp;

• Internal join query: Query is that the left table and the right table can find records corresponding to the record.

select * from dept,emp where dept.id = emp.dept_id;
select * from dept inner join emp on dept.id=emp.dept_id;

• External Connection Query:

  • Left External Connection Query: Adding records that the left table has but the right table does not have on the basis of the inner join

  select * from dept left join emp on dept.id=emp.dept_id;
  

  • Right External Connection Query: Adding records with right table but not with left table on the basis of inner join

  select * from dept right join emp on dept.id=emp.dept_id;
  

  • Total External Connection Query: On the basis of internal connection, add records that are in the left table but not in the right table and records that are in the right table and not in the left table.

  select * from dept full join emp on dept.id=emp.dept_id; 
  -- mysql External connection is not supported
  
  -- have access to union Keyword Analog External Connection:
  
  select * from dept left join emp on dept.id = emp.dept_id
  
  union
  
  select * from dept right join emp on dept.id = emp.dept_id;
  

Six, others

• Modification prompt in MySQL
  • The first way: modify after login

MySQL default prompt is >
prompt [MySQL prompt]

MySQL prompt

• The second way is to modify it at login time.

mysql -uroot -proot -prompt [MySQL prompt]

• Classification of SQL Language
  
• SQL language is divided into four categories: data query language DQL, data manipulation language DML, data definition language DDL, data control language DCL.