Summarize the basic content of MySQ

Create database

To operate the table, you need to first enter the database use database name;

--Create a database named inana_db , the database character encoding is specified as utf8
create database inana_db character set utf8;
drop database inana_db; -- Delete the library named samp_db
show databases;  — Display the database list.
use inana_db; --Select the created database samp_db
show table name; --Show all table names under samp_db
describe table name ; -- Display the structure of the data table
delete from table name; -- Clear the records in the table

Create database table

Use the create table statement to complete the creation of the table. The common form of create table: syntax: create table 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 - no password reset required; 1 - password reset required',
mobile varchar(20) NOT NULL DEFAULT '' COMMENT 'Mobile phone ',
-- Create a unique index, no duplicates allowed
## UNIQUE INDEX idx_user_mobile(`mobile`)
)CHARSET=utf8;

Data type attribute explanation
NULL: data column can contain NULL value;
NOT NULL: data column NULL values ​​are not allowed;
DEFAULT: default value;
PRIMARY: KEY primary key;
AUTO_INCREMENT: automatic increment, suitable for integer types;
UNSIGNED: index value type can only be positive;
CHARACTER SET name: Specify a character set;
COMMENT: Description of the table or field;

Add, delete, modify and check

SELECT statement is used to select data from a table. Syntax: SELECT column name FROM table name
Syntax: SELECT * FROM table name

Update statement is used to modify the data in the table. Syntax: UPDATE table name SET column name = new value WHERE column name = some 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, ....)

DELETE statement is used to delete rows from a table. Syntax: DELETE FROM table name WHERE column name = value

The WHERE clause is used to specify the selection criteria. Syntax: SELECT column name FROM table name WHERE column operator value

IN - Operator allows us to specify multiple values ​​in the WHERE clause.
IN - The operator is used to specify a range, and each item in the range is matched. IN value rules, separated by commas, all placed in brackets. Syntax: SELECT "Field name"FROM "Table name"WHERE "Field name" IN ('Value one', 'Value two', ...);

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

as - can be understood as: used as, as, as; alias, usually renaming the column name or table name. Syntax: select column_1 as column 1,column_2 as column 2 from table as table

JOIN: Returns rows if there is at least one match in the table
INNER JOIN: The INNER JOIN keyword returns rows if there is at least one match in the table.
LEFT JOIN: Return all rows from the left table even if there is no match in the right table
RIGHT JOIN: Return all rows from the right table even if there is no match in the left table Return all rows
FULL JOIN: As long as there is a match in one of the tables, return the rows

Common functions

COUNT allows us to count how many pieces of information are selected in the table.
Syntax: SELECT COUNT("Field name") FROM "Table name";

##MAX The function returns the maximum value in a column. NULL values ​​are not included in the calculation. Syntax: SELECT MAX("Field name") FROM "Table name"

Add indexindex

Syntax: ALTER TABLE table name ADD INDEX index name (field name)

Primary key index(PRIMARY key )Syntax: ALTER TABLE table name ADD PRIMARY KEY (field name)

##Unique index(UNIQUE)Syntax ：ALTER TABLE table name ADD UNIQUE (field name)

Modification of the table after creation

Add columns Syntax: alter table table name add column name column data type [after insertion position]

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

Delete column Syntax: alter table table name drop column name;

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

Clear table data Syntax: delete from table name;

Delete the entire table Syntax: drop table table name;

Delete database Syntax: drop database database name;

The above is the detailed content of Summarize the basic content of MySQ. For more information, please follow other related articles on the PHP Chinese website!