SQL Review one (basic knowledge)

1. What is SQL

SQL (structure query language) is a structured query language, which is the operating language of the relational database. It can be applied to all databases, such as MySQL, Oracle, SQL Server, and so on.

1.2 Syntax requirements

SQL statements can be written in single or multiple lines, ending with semicolons;

You can use spaces and indents to enhance the readability of your statement

Keywords are not case-sensitive, we recommend using uppercase

2. Classification

DDL: A data definition language used to define database objects: Databases, tables, columns, etc.

DML: Database manipulation language, used to define database records (data)

DCL: A database control language used to define access and security levels

DQL: Database query Language, used to query data

3.DDL

3.1 Basic operations

View all database names: show databases:

Switch database: Use MYDB1, switch to MYDB1 database

3.2 Operational databases

Creating the database: Create databases [if not EXISTS] mydb1;

Create a database named MYDB1 if the MYDB1 database does not exist.

Delete databases: Drop database [if exists] mydb1;

If MYDB1 exists, delete the database

Modify database encoding: Alter DB MYDB1 character set UTF8

3.3 Data types

int: integral type

Double: floating point type, double (5,2) up to five bits, which must have two decimal places

Decimal: Generics, used in the expression of money, without the problem of lack of precision

Char: fixed-length string type

VARCHAR: variable-length string type

Text: String type

BLOB: Byte type

Date: Day type, YYYY-MM-DD

Time: Date type, HH:MM:SS

Timestamp: Timestamp type

3.4 Operation table

To create a table:

CREATE TABLE Table name (

Column name data type,

Column name data type)

Create Table Stu (      sid   Char(6),      varchar,      Age     int,      varchar(ten))

View all table names in the current database: show tables;

View the CREATE statement for the specified table: Show create TABLE Stu, view the creation statement for the Stu table,

View the structure of the table: Desc stu, view the structure of the Stu table,

Delete tables: drop table stu; Delete Stu table

To modify a table:

1) Add column: Add classname column to Stu table, (add)

  altertableadd (classname varchar)

2) Modify the column type: Change the gender type of the Stu table to char (2) (Modify)

Alter Table Char(2);

3) Modify the column name: Modify the gender of the Stu table to sex (change)

Alter Table Char(2);

4) Delete column: Speak stu classname Column Delete (drop)

Alter Table drop classname;

5) Modify the name of the table: Modify the Stu table named Student (rename)

Alter table stu Rename to student;

4.DML

4.1 Inserting data

Grammar:

Insert into table name (column name 1, column Name 2, ...). Values (value 1, value 2, ...). ）

Insert  into Values (' s_1001 ','zhangsan');

Insert into table name values (value 1, value 2, ...). ）；

Insert  into Stu (' s_1002 ','lisa',+,'fe');

4.2 Modifying data

Grammar:

Update table name set column name 1= value 1, column name 2= value 2, ... [Where Condition]

UPDATEStuSETSname=' Zhangsansan ', sage=’ +', gender=' Female 'WHERESid=' s_1001 ';UPDATEStuSETSname=' LiSi ', age=’ -’WHEREAge> -  andGender=' male ';UPDATEStuSETSname=' Wangwu ', age=’ -’WHEREAge> - ORGender=' female ';UPDATEStuSETGender=' Female 'WHEREGender is NULLUPDATEStuSETAge=Age+1 WHERESname=' Zhaoliu ';

4.3 Deleting data

Syntax delete from table name [where condition]

Delete  from where sno='s_1001';

Delete all records in all tables

Delete  from Stu; truncate table Stu;

Both truncate and delete can delete all records of a table, but with different principles. The efficiency of delete is not truncate high!

Truncate actually attributes the DDL statement because it is the drop table first and then the CREATE table. and truncate deleted records cannot be rolled back, but delete deleted records can be rolled back (rollback is the knowledge of the transaction!). ）。

5.DCL

5.1 Creating a user

Syntax: Create user username @ address identified by ' password '

CREATE user user1@localhost by '123CREATEUSER [email Protected] '% by '123

The first line can only be logged on in localhost IP, and the second one may be logged in anywhere.

5.2 Authorization to the user

Syntax: Grant permissions 1, Permissions 2......on database. * To User name

Grant Create,alter,drop,insert,update,delete,select  on MYDB1. *  to user1; Grant  All  on MYDB1,* to User2

5.3 Revocation of authorization

Syntax: Revoke permissions 1, permissions 2 ... on database. * Form User Name

REVOKE CREATE,ALTER,DROP on MYDB1. *  from User1@localhost;

5.4 Viewing user rights

Syntax: Show grant for user name

 for User1@localhost;

5.5 Deleting a user

Syntax: Drop user username

DROP USER user1@localhost;

5.6 Modifying user passwords

Grammar:

Use MySQL;

UPDATE user SET Password=password (' password ') WHERE user= ' user name ' and host= ' IP ';

FLUSH privileges;

UPDATE USER SET PASSWORD=PASSWORD ('1234'WHEREUser=' User2 '  and Host=privileges;

SQL Review one (basic knowledge)