Classification of SQL

classification	explain
Data definition language	For short, DDL(Data De "nition Language) is used to define database objects: database, table, column, etc.
Data manipulation language	Referred to as DML (data management language), it is used to update the records of tables in the database.
Data query language	DQL(Data Query Language) is used to query the records of tables in the database.
Data control language	Referred to as DCL(Date Control Language), it is used to define the access permission and security level of the database and create users.

1 DDL operation database

1.1 create database

command	explain
create database database name;	Creates a database with the specified name.
create database database name character set character set;	Create a database with the specified name and specify the character set (utf-8 is generally specified)

/*
Method 1: directly specify the database name for creation. The default database character set is latin1
*/

CREATE DATABASE db1;

/*
Method 2 specifies the database name. The character set of the specified database is generally specified as utf8, which is consistent with the coding in Java
*/
CREATE DATABASE db1_1 CHARACTER SET utf8;

1.2 view / select database

command	explain
use database	Switch database
select database();	View the database currently in use
show databases;	Check which databases are available in Mysql
show create database database name;	View the definition information of a database

-- Switch database    from db1 Switch to    
db1_1 USE db1_1;

-- View the database currently in use
SELECT DATABASE();

-- see Mysql What databases are in the
SHOW DATABASES;

-- View the definition information of a database
SHOW CREATE DATABASE db1_1;

1.3 modify database

command	explain
alter database database name character set character set;	Character set modification of database

-- Database db1 Character set for    Change to    
utf8 ALTER DATABASE db1 CHARACTER SET utf8;
-- View basic information of the current database
SHOW CREATE DATABASE db1;

1.4 delete database

command	explain
drop database database name	Permanently delete a database from MySql

-- Delete a database 
DROP DATABASE db1_1;

2 DDL operation data sheet

2.1 creating tables

-- Syntax format:
CREATE TABLE Table name(
	Field name 1 field type (length),
	Field name 2 field type         
    be careful    Don't add commas in the last column 
);

sql implementation

-- Switch to database    
db1 USE db1;
-- Create table
CREATE TABLE category( 
    cid INT,
	cname VARCHAR(20) 
);

Quickly create a table with the same table structure (copy table structure)

-- grammar
create table New show    like Old table name

sql implementation

-- Create a db1 Same structure db2
CREATE TABLE db2 LIKE db1;

-- View table structure 
DESC test2;

2.2 view table

command	explain
show tables;	View all table names in the current database
desc table name;	View the structure of the data table

-- View all table names in the current database 
SHOW TABLES;
-- Displays the structure of the current data table
DESC category;
-- View the of creating a table SQL sentence
SHOW CREATE TABLE category;

2.3 delete table

command	explain
drop table name;	Delete table (permanently delete a table from the database)
drop table if exists table name;	Judge whether the table exists. If it exists, it will be deleted. If it does not exist, it will not be deleted

-- Delete directly    test1 surface
DROP TABLE test1;
-- Judge first    Delete again test2 surface
DROP TABLE IF EXISTS test2;

2.4 modification table

• Modify table name

  • -- grammar
    rename table Old table name to New table name
    
    -- take category Change the table to category1
    RENAME TABLE category TO category1;
    

• Modify character set of table

  • alter table Table name character set character set
     take category The character set of the table is modified to utf8
    alter table category character set utf8
    

• Add column to table (add)

  -- Syntax format
  alert table Table name    add Field name field type
  -- Add a new field to the classification table    Classification description    cdesc varchar(20)
  ALTER TABLE category ADD cdesc VARCHAR(20);
  

• Modify the data type or length of the column in the table (modify)

  • -- Syntax format
    alter table Table name    modify Field name    Field type
    
    -- Modify the description field of the classification table and select the type varchar(50)
    ALTER TABLE category MODIFY cdesc VARCHAR(50);
    

• Modify column name (change)

  • -- Syntax format
    alter table Table name    change Old column name    New column name    type(length);
    -- For the in the classification table desc Field is replaced, Replace with description varchar(30)
    ALTER TABLE category CHANGE cdesc description VARCHAR(30);
    

• Delete column (drop)

  • -- Syntax format
    alter table Table name    drop Listing;
    
    -- Delete in classification table description This column
    ALTER TABLE category DROP description;
    

3 DDL operation data sheet

3.1 insert data

-- Syntax format
insert into Table name    (field name 1, field name 2)...)    values(Field value 1, field value 2...);

Data needed

Table name: student
 Fields in the table:
	student ID, 	sid int
	full name,    sname varchar(20) Age,    age int
	Gender,    sex char(1)
	Address,    address varchar(40)
# Create student table
CREATE TABLE student( 
    sid INT,
	sname VARCHAR(20),
    age INT,
	sex CHAR(1), 
    address VARCHAR(40)
);

• Insertion mode I

  • -- Insert all fields and write out all field names
    INSERT INTO student (sid,sname,age,sex,address) VALUES(1,'Sun WuKong',20,'male','Huaguo Mountain');
    

• Insertion mode II

  • -- Insert all fields without writing the field name
    INSERT INTO student VALUES(2,'Gohan ',10,'male','earth');
    

• Insertion mode III

  • -- Inserts the value of the specified field
    INSERT INTO category (cname) VALUES('Baigujing');
    

3.2 change data

-- Unconditional modification
update Table name    set Listing = value

-- Conditional modification 
update Table name    set Listing  = value   [where Conditional expressions: field names    = value]

-- Change all gender to female without conditions
UPDATE student SET sex = 'female';

-- Conditional modification will sid For 3 students, the gender is changed to male
UPDATE student SET sex = 'male' WHERE sid = 3;

-- Modifying multiple columns at once will sid For 2 students, the age is changed to 20 and the address is changed to Beijing
UPDATE student SET age = 20,address = 'Beijing' WHERE sid = 2;

3.3 deleting data

-- Delete all data
delete from Table name

-- Delete data under specified conditions
delete from Table name  [where Field name = value]

-- delete sid Data for 2
delete from student where sid = 2

-- Delete all data
DELETE FROM student;

If you want to delete all data in the table, there are two ways

• delete from table name; not recommended. Delete as many records as possible. Inefficient
• truncate table: recommended as like as two peas. First, delete the whole form and then create a duplicate table.

3 data in DQL query table

3.1 required data

#Create employee table
 Table name    emp
 Fields in the table:
	eid staff id，int
	ename full name, varchar
	sex Gender, char
	salary Salary, double
	hire_date Entry time,
	date dept_name 
	Department name, varchar
#Create employee tablecreate table emp(
eid INT,
	ename VARCHAR(20), sex CHAR(1),
	salary DOUBLE,
	hire_date DATE,
	dept_name VARCHAR(20) 
);


#Add data
INSERT INTO emp VALUES(1,'Sun WuKong','male',7200,'2013-02-04','Teaching Department');
INSERT INTO emp VALUES(2,'Zhu Bajie','male',3600,'2010-12-02','Teaching Department');
INSERT INTO emp VALUES(3,'Tang Monk','male',9000,'2008-08-08','Teaching Department');
INSERT INTO emp VALUES(4,'Baigujing','female',5000,'2015-10-07','Marketing Department');
INSERT INTO emp VALUES(5,'spider goblin','female',5000,'2011-03-14','Marketing Department');
INSERT INTO emp VALUES(6,'Jade rabbit essence','female',200,'2000-03-14','Marketing Department');
INSERT INTO emp VALUES(7,'Lin Daiyu','female',10000,'2019-10-07','Finance Department');
INSERT INTO emp VALUES(8,'Huang Rong','female',3500,'2011-09-14','Finance Department');
INSERT INTO emp VALUES(9,'Wu Chengen','male',20000,'2000-03-14',NULL);
INSERT INTO emp VALUES(10,'Gohan ','male', 10,'2020-03-14',Finance Department);
INSERT INTO emp VALUES(11,'Bugs Bunny','female', 300,'2010-03-14',Finance Department);

3.2 simple query

-- select Listing    from Table name

-- query emp All data in
select * from emp; -- use    * Represents all columns

-- query emp All records in the table are displayed only id and name field
SELECT eid,ename FROM emp;

-- Query all employee information and change the column name to Chinese
# Use the AS keyword to list aliases for
SELECT
eid AS 'number', ename AS 'full name' , sex AS 'Gender', salary AS 'salary',
hire_date 'Entry time', -- AS Can be omitted 
dept_name 'Department name'
FROM emp;

-- How many departments are there in total

SELECT DISTINCT dept_name FROM emp; -- use distinct keyword,Remove duplicate department information 

-- Pay all employees +1000 Display element
select ename, salary + 1000 from emp;

3.3 query criteria

-- Syntax format
select Listing  from Table name  where Conditional expression  -- Take out each data in the table first,The data that meets the conditions is returned,Filter out those who are not satisfied

-- Query the employee information whose name is Huang Rong
SELECT * FROM emp WHERE ename = 'Huang Rong';

-- Query employee information with salary price of 5000
SELECT * FROM emp WHERE salary = 5000;

-- Query the information of all employees whose salary price is not 5000
SELECT * FROM emp WHERE salary != 5000;
SELECT * FROM emp WHERE salary <> 5000;

-- Query all employees whose salary price is 3600 or 7200 or 20000
SELECT * FROM emp WHERE salary = 3600 OR salary = 7200 OR salary = 20000;
SELECT * FROM emp WHERE salary IN(3600,7200,20000);

Wildcard query

wildcard	explain
%	Represents matching any number of strings,
_	Indicates a match of one character

# Query all employee information with the word "fine"
SELECT * FROM emp WHERE ename LIKE '%essence%';

# Query all employee information starting with 'sun'
SELECT * FROM emp WHERE ename LIKE 'Sun%';

# Query all employee information with the second word 'rabbit'
SELECT * FROM emp WHERE ename LIKE '_rabbit%';

# Query employee information without Department
SELECT * FROM emp WHERE dept_name IS NULL;

# Query employee information of Department
SELECT * FROM emp WHERE dept_name IS NOT NULL;