MySQL Tutorial SQL basic operations

The basic operation of SQL is nothing more than additions and deletions (CRUD), according to the object classification of operations, can be divided into three categories, library operations, table operations and data manipulation

Library operations New Database

CREATE database [IF not EXISTS] db_name [Library options]

Where library options are used to constrain the database, divided into two options

Character Set: CHARACTER set specific character set (encoding format for data storage): Common character sets: GBK and UTF8

Proofing Set Settings: COLLATE specific proofing sets (rules for data comparisons)

--Create a database called DB, and set the character set to utf8create database IF not EXISTS db CHARACTER set UTF8;

Note that the name of the database created here cannot be used for keywords (characters already in use) or reserved words (which may be used in the future)

If you want to use keywords or reserved words, then you need to use anti-quotes (usually on the keyboard below the ESC key, to the English state output)

CREATE database IF not EXISTS ' DATABASE ' CHARACTER SET UTF8;

Of course, you can also create a database with Chinese names (not recommended)

CREATE database IF not EXISTS ' databases ' CHARACTER SET UTF8;

View Database

SHOW DATABASES [like ' pattern ']

Where pattern is the matching pattern

%: Indicates that multiple characters are matched

_: means match one character

--View all databases show DATABASES;

--To see the database beginning with information_, you need to escape the underline _ show DATABASES like ' information\_% ';

--View the database beginning with information, equivalent to information%show DATABASES like ' information_% ';

To view the creation statement for a database

SHOW CREATE DATABASE db_name

--View the database DB creation statement, show create database db;

Update Database

ALTER database db_name [Library options]

Deleting a database

DROP DATABASE [IF EXISTS] Db_name

--Delete DB database drop IF EXISTS db;

Table Operations New Data Sheet

CREATE TABLE [IF not EXISTS] tbl_name (    col_name type,    col_name type    )

The design of any one table must specify the database

Displays the specified

--Creating Student table CREATE TABLE IF not EXISTS db.student (id INT PRIMARY KEY not null,name VARCHAR (Ten), age TINYINT)

Implicitly specified

--Specify the database use db;--create student table for the IF not EXISTS student (ID INT PRIMARY KEY not null,name VARCHAR (Ten), age TINYINT)

View Data Sheet

View all Tables

SHOW [Full] TABLES [from db_name] [like ' pattern ']

Where the full modifier can display the second output column, for one table, the value of the second column is base table, and for one view, the value of the second column is view.

SHOW TABLES;

SHOW full TABLES;

View partial tables based on matching pattern

--View the table starting with S show TABLES like ' s% ';

View creation statements for a table

SHOW CREATE TABLE student;

View table Structure

DESC student;

Updating data tables

Modify Table Name

RENAME TABLE tbl_name to New_tbl_name    [, tbl_name2 to New_tbl_name2] ...

One or more tables can be renamed

--Rename the student to Teacherrename TABLE student to teacher;

New Field

There are more actions for fields, including new, modified, duplicate names, and deletions of fields

ALTER TABLE tbl_name ADD [COLUMN] col_name type [First | After Col_name]

First: Indicates the position in the inserted table

After: Indicates that the insertion is after a field, by default after the last field

--Insert the Grade field after the Name field alter TABLE class ADD COLUMN grade VARCHAR (Ten) after name;

modifying fields

ALTER TABLE tbl_name MODIFY [COLUMN] col_name type [First | After Col_name]

--Modify the length of the Grade field Alter TABLE class MODIFY COLUMN grade VARCHAR (20);

Rename Field

ALTER TABLE tbl_name Change [COLUMN] old_col_name new_col_name Type [First | After Col_name]

--The NUM field name of the modified table class is Totalalter table class change COLUMN, num total int (11);

Delete a field

ALTER TABLE tbl_name DROP [COLUMN] Col_name

--Delete the total field of the Class table ALTER TABLE class DROP COLUMN total;

Delete a data table

DROP TABLE [IF EXISTS] tbl_name [, Tbl_name] ...

You can delete one or more tables

--Delete Class table drop table IF EXISTS class;

Data manipulation New data

INSERT [into] tbl_name [(Col_name,...)] VALUES ({expr | DEFAULT},...)

The column name is not explicitly specified and needs to be consistent with the field order of the data table

--Student table inserts a data insert into student VALUES (1, ' s1 ', 20);

Specify Column Name

--Insert a data into the student table into the student (name, age) VALUES (' S2 ', 20) according to the column name and order;

View data

Update data

Update a single sheet

UPDATE [low_priority] [IGNORE] tbl_name    SET col_name1=expr1 [, col_name2=expr2 ...]    [WHERE Where_definition]

--Update the Age field value of the student table name to S2 update student SET age = The WHERE ' name ' = ' s2 ';

Update more than one table

UPDATE [low_priority] [IGNORE] table_references    SET col_name1=expr1 [, col_name2=expr2 ...]    [WHERE Where_definition]

UPDATE class, student SET Student.age = WHERE Student.classid = class.id;

Delete data

DELETE from Tbl_nam [WHERE where_definition]

--delete data with name S1 Delete from student WHERE student. ' Name ' = ' s1 '

MySQL Tutorial SQL basic operations