Basic MySQL content

Basic MySQL content

Create a database

Table operations must first be written into the use Database Name;

-- Create a database named inana_db and specify the database character encoding as utf8.
Create database inana_db character set utf8;
Drop database inana_db; -- delete a database named samp_db
Show databases; -- displays the Database List.
Use inana_db; -- select the created database samp_db
Show table name; -- display all table names under samp_db
Describe table name; -- display the table structure
Delete from table name; -- clear table records

 

Create a database table

You can use the create table statement to create a table. The common form of create table is Syntax: create table Name (column Declaration );

Create table table_name (
Id int AUTO_INCREMENT primary key,
Password varchar (32) not null default ''comment' user password ',
Reset_password tinyint (32) not null default 0 COMMENT 'user type: 0-Password Reset NOT required; 1-Password Reset required ',
Mobile varchar (20) not null default ''comment' cell phone ',
-- Create a unique index. Duplicate indexes are not allowed.
Unique index idx_user_mobile ('mobile ')
) CHARSET = utf8;


Description of data type attributes
NULL: The data column can contain NULL values;
Not null: The data column cannot contain NULL values;
DEFAULT: the DEFAULT value;
PRIMARY: key primary key;
AUTO_INCREMENT: auto increment, applicable to integer type;
UNSIGNED: The value type can only be positive;
Character set name: Specifies a character set;
COMMENT: Description of tables or fields;

 

Add, delete, modify, and query

SELECT statements are used to SELECT data from a table.
Syntax: SELECT column name FROM Table Name
Syntax: SELECT * FROM Table Name

The Update statement is used to modify data in a table.
Syntax: UPDATE table name SET column name = new value WHERE column name = A Value

The insert into statement is used to INSERT new rows INTO the table.
Syntax: insert into table name VALUES (value 1, value 2 ,....)
Syntax: insert into Table Name (column 1, column 2,...) VALUES (value 1, value 2 ,....)

The DELETE statement is used to DELETE rows in a table.
Syntax: delete from table name WHERE column name = Value

The WHERE clause specifies the criteria for selection.
Syntax: SELECT column name FROM table name WHERE column operator Value

The IN-operator allows us to specify multiple values IN the WHERE clause.
The IN-operator is used to specify the range. Each entry IN the range matches. The IN value rule, separated by commas (,). All values are placed IN brackets.
Syntax: SELECT "field name" FROM "table name" WHERE "field name" IN ('value 1', 'value 2 ',...);

ORDER
The statement sorts records in ascending order by default.
The order by-statement is used to sort the result set based on the specified column.
DESC-sort records in descending order.
ASC-sort records in sequence.

As-can be understood as: Used as, as, as; alias is generally to rename the column name or table name.
Syntax: select column_1 as column 1, column_2 as column 2 from table as table

JOIN: If the table has at least one match, the row is returned.
Inner join: if at least one match exists in the table, the inner join keyword returns the row.
Left join: returns all rows from the LEFT table even if no match exists in the right table.
Right join: returns all rows from the RIGHT table even if no match exists in the left table.
Full join: if one of the tables matches, the row is returned.

 

Common functions

COUNT allows us to COUNT how many pieces of data are selected in the table.
Syntax: select count ("field name") FROM "table name ";

The MAX function returns the maximum value in a column. NULL values are not included in calculation.
Syntax: select max ("field name") FROM "table name"

 

Add index

Syntax: alter table name add index name (field name)

PRIMARY key Index)
Syntax: alter table name add primary key (field name)

UNIQUE Index)
Syntax: alter table name add unique (field name)

 

Table modification after creation

Add column Syntax: alter table name add column name column data type [after Insert Location]

Alter column Syntax: alter table name change column name new data type;

Delete column Syntax: alter table Name drop column name;

Rename table Syntax: alter table name rename new table name;

Clear table data Syntax: delete from table name;

Delete the entire table Syntax: drop table name;

Delete database Syntax: drop database name;