1 SQL overview

1.1 what is SQL

SQL (Structured Query Language) is a "structured query language", which is an operation language for relational databases. It can be applied to all relational databases, such as MySQL, Oracle, SQL Server, etc.

SQL standards (ANSI/ISO) include:
1. SQL-92: SQL language standard issued in 1992;
2. SQL: SQL language label issued in 1999;
3. SQL: SQL language label issued in 2003;

These standards are the same as the JDK version. There are always some syntax changes in the new version. Databases in different periods have implemented different standards.

Although SQL can be used in all relational databases, many databases also have some syntax after the standard, which we can call "dialect". For example, the LIMIT statement in MySQL is a unique dialect of MySQL, which is not supported by other databases! Of course, Oracle or SQL Server has its own dialect.

1.2 grammar requirements

1. SQL statements can be written in one or more lines, ending with semicolons;
2. You can use spaces and indents to enhance the readability of statements;
3. Keywords are not case sensitive. It is recommended to use uppercase;

2 SQL classification

DDL: data definition language, which is used to define database objects: database, table, column, etc. (database and table building).
DML: data operation language, which is used to define database records (data) for insertion, deletion and update operations.
DCL: data control language, used to set access rights and security levels.
DQL: data query language, used to query records (data).

3 how to execute SQL statements in Navicat

Double click Navicat to open MySQL80 connection.

Click "new query".

Enter the following page, where you can write SQL statements. Click Run to execute the SQL statement.

4 DDL data definition language (MySQL)

4.1 basic operation

1. View all databases

show databases;

2. Switch database

use Database name;

Switch to info database:

use info;

4.2 operation database

1. Create database

create database [if not exists] Database name;

[if not exists]: indicates that if the given "database name" does not exist, the database will be created; If the database exists, it will not be created again.

If "IF NOT EXISTS" is not added, an error will be reported when the database to be created exists.

2. Delete database

drop database [if exists] Database name;

[if exists]: if the database to be deleted exists, it will be deleted; if it does not exist, it will not be deleted.

If "IF EXISTS" is not added, an error will be reported if the database does not exist.

3. Modify the encoding format of the database

ALTER DATABASE Database name CHARACTER SET Character set name;

For example, modify the code of database mydb1 to utf8

ALTER DATABASE mydb1 CHARACTER SET utf8

Modify the code of database mydb1 to utf8. Note that all UTF-8 codes in MySQL cannot use the middle "-", that is, UTF-8 should be written as utf8.

5 data type

Mysql, like Java and C, also has data types. Data types in MySQL are mainly used on columns.

type	describe
int	integer
double	Floating point type, for example, double(5,2) represents a maximum of 5 digits, in which there must be 2 decimal places, that is, the maximum value is 999.99
decimal	Generic type. It can store both int type and double type without losing the accuracy of data
char	Fixed length string (space will be filled in when the input string is not long enough)
varchar	Fixed length string (no space will be filled when the input string is not long enough)
text	String type
blob	Byte type
date	Date type (Format: yyyy MM DD)
datetime	Date time format (Format: hh:mm:ss)
timestamp	time stamp

6 operation table

6.1 creating tables

CREATE TABLE Table name(
   Column name column type,
   Column name column type,
   ......
);

Next, take the Sales database and its table Employees as an example. First, create the table Employees in the Sales database. The data is as follows:

Listing	data type	Is it empty
number	char(6)	No
full name	char(8)	No
Gender	char(2)	No
department	varchar(16)	Yes
Telephone	varchar(20)	Yes
address	varchar(50)	Yes

Create the Employees table. The SQL statement is as follows:

CREATE TABLE Employees(
  number CHAR(6) NOT NULL,
  full name CHAR(8) NOT NULL,
  Gender CHAR(2) NOT NULL,
  department VARCHAR(16),
  Telephone VARCHAR(20),
  address VARCHAR(50)
);

6.2 view table structure

desc Table name;

Example: viewing the employees table

desc employees;

6.3 delete table

drop table Table name;

Example: delete table employees

drop table employees;

6.4 modification table

1. Add column

alter table Table name add (Column name data type(length));

Example: add a column to the employees table: age char(10)

ALTER TABLE Employees ADD (Age CHAR(10));

2. Modify the data type of the column

alter table Table name modify Column name new data type(length);

Example: for the age column in the employees table, modify char(10) to varchar(10)

ALTER TABLE Employees MODIFY Age VARCHAR(10);

3. Modify column name

alter table Table name change Original column name new column name data type(length);

Example: change the age of the column name in the employees table to age

ALTER TABLE employees CHANGE Age age CHAR(2);

4. Delete column

alter table Table name drop Listing;

Example: delete a column in the employees table

ALTER TABLE employees DROP Age;

5. Modify table name

alter table Original table name rename to New table name;

Example: rename the employees table to employee

ALTER TABLE employees RENAME TO employee;

7 DML data operation language

7.1 inserting data

1. Insert data for all columns by default

insert into Table name values(Value 1,Value 2,Value 3......);

Because the column to be inserted is not specified, it means that the values of all columns are inserted in the order of the columns when the table is created (Note: all string type data must be enclosed in single quotes)

Example: inserting data for the employee table

INSERT INTO employee VALUES('e_001','one','male','Finance Department','111','Beijing');

2. Inserts data into the specified column
Premise: columns without inserted data are allowed to be null

insert into Table name(Column name 1,Column name 2,Column name 3,...) values(Value 1,Value 2,Value 3,...);

Example: insert data for employee

INSERT INTO employee(number,full name,Gender,department) VALUES('e_002','two','female','Finance Department');

7.2 modifying data

update Table name set Column name 1=Value 1,Listing=Value 2... [where condition];

Note: the method to judge whether the column is empty is null

Example: modify the second data in the employee table

UPDATE employee SET Telephone='222',address='Shanghai' WHERE number='e_002';

7.3 deleting data

1. Delete records that meet the conditions

delete from Table name where condition;

Example: delete the data numbered "e_001" in the table

DELETE FROM employee WHERE number='e_001';

2. Delete all records:

--Mode 1
TRUNCATE TABLE Table name;
--Mode 2
delete from Table name;

Although TRUNCATE and DELETE can DELETE all records of the table, they have different principles. DELETE is not as efficient as TRUNCATE!

TRUNCATE is actually a DDL statement because it first drops table and then creates table. Moreover, records deleted by TRUNCATE cannot be rolled back, but records deleted by DELETE can be rolled back.

8 DCL data control language

8.1 creating users

--The address is a local address or 127.0.0.1. 
create user 'user name'@address IDENTIFIED  BY 'password';
--Or it can be written in the following form
CREATE USER 'user name'@'%' IDENTIFIED BY 'password';
--here%Is a wildcard, indicating that users will be created no matter what address

Example: create user1 user with password 123

CREATE USER 'user1'@localhost IDENTIFIED BY '123';

After creation, you can enter cmd to open a command prompt window to verify whether you can log in to the database.

8.2 authorization to users

If you only create a user, the user can only log in to the database and cannot perform any operations. Therefore, you need to authorize the user.

GRANT Authority 1,Authority 2... ON database.*  TO 'user name'@address;

The types of permissions mainly include:

jurisdiction	explain
CREATE	establish
ALTER	modify
DROP	Delete (library, table)
INSERT	insert
UPDATE	to update
DELETE	Delete (record)
SELECT	choice

Granting full permissions to users can be written as:

--In mode 1, the following permissions can also be selected and written to indicate partial authorization
GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON database.* TO user@localhost;
--Mode 2
GRANT ALL ON database.* TO user@localhost;

Example: granting create permission to user1

GRANT CREATE ON sales.* TO user1@localhost;

In the command prompt window, use user1 to create a table s1.

create table s1(id int,name varchar(20));

Look at the sales database again. There is s1 such a table:

If the user1 user is used to view the structure of the table, an error will be reported because there is no permission:

desc s1;

8.3 viewing user permissions

SHOW GRANTS FOR 'user name'@address;

Example: view the permissions of user1 user

SHOW GRANTS FOR 'user1'@localhost;

8.4 display all users

SELECT user from mysql.user;

Example: display all users in the database

8.5 revocation of authorization

REVOKE Authority 1, ... , jurisdiction n ON database.* FROM 'user name'@address;

Example: revoke the create permission of user1 user

REVOKE CREATE ON sales.* FROM 'user1'@localhost;

Next, show the permissions of user1: there is only one permission:

SHOW GRANTS FOR user1@localhost;

8.6 modifying user password

alter user 'user name'@localhost identified by 'New password';

Example: change the password of user user1 to 121212

ALTER USER 'user1'@localhost IDENTIFIED BY '121212';

8.7 delete user

drop user 'user name'@address;

Example: delete user1 user

DROP USER 'user1'@localhost;