Basic operation of MySQL

Basic operation of data: CRUD (Create, Retrieve, Update, Delete Classified according to the object of operation, basic SQL operations can be divided into:
library operations, table operations (modify fields, foreign keys, add and delete tables, etc.), and data operations.

Library operations

New database
Basic syntax:
create database database name [library Options];
Library options: used to constrain the database, divided into two options
Charset/character set Specific character set (coding format of data storage) —Common character set: GBK and UTF8
Proofreading set setting: collate specific proofreading set (data comparison rules)
Note: The database name cannot be a keyword or a reserved word.

Delete database
Basic syntax:
drop database database name;

Execute the drop command DBMS What happened?
1. The specified database was deleted in the database system.
2. In the corresponding database storage folder, the folder corresponding to the database name was deleted in cascade (subdirectories and files were also deleted)

View all databases:
Basic syntax:
show databases;

View the database of the specified pattern, fuzzy query:
Basic syntax:
show databases like’pattern’;

‘pattern’ is the matching pattern ：
%: means matching multiple characters
_: means matching a single character
show databases like’info_%’;
show databases like’info\_%’;
is not the same
Note: If you want to match the underscore _ to escape the’\_’

View the database creation statement:
Basic Syntax:
show create database database name;

Update database:
Only to modify the database character set or collation set , The higher version of mysql does not support modifying the database name
Basic syntax:
alter database database name [library option];
Library option: used to constrain the database, divided into two options
charset/character set [=] Character set
collate [=] Proofreading set

Table operation

New data table
Explicitly specify the database to which the table belongs, and create the table under the specified database
create table [if not exists] database. Table name (
……
)

< p>Implicitly specify the database, you need to use a certain database environment first, and the table creation statement will automatically create the table in the specified database Next
use mydatabase;
create table [if not exists] table name (
……
)

Basic syntax:
create table [if not exists] table name (
field name data type[other options],
field name data type[other options],
…….
field name data type[other options ]
) [Table options];
Table options: used to control the attributes of the table
Character set setting: charset/character set Specific character set (coding format for data storage) —Common character set : GBK and UTF8
Proofreading set setting: collate specific proofreading set (data comparison rules)
Storage engine setting: engine specific engine (innodb is used by default, and myisam is also available)

Table SQL command DBMS what happened
1. A specified data table will be created in the corresponding database system
2. In the database folder, the structure file of the corresponding table will be generated (related to the storage engine xxxx table name.frm file or xxxx table name.idb file)

Delete data table
Basic syntax:
Delete one or more data tables: drop table name 1, table name 2, … table name n;

What happens after the instruction to delete the data table is executed?
1. In the specified database, the specified table is deleted.
2. In the folder corresponding to the database, the file corresponding to the table (related to the storage engine) will also be deleted.

View data table
Basic syntax:
View all data tables: show tables;
View specific mode names The data table: show tables like’pattern’;
View table creation statement: show create table Table name[\g | \G];
View table structure statement (View field information in the table):
desc/describe table name;
show columns from table name;
see the same result.

Modify data table
Basic syntax:
Rename table: rename table old table name to new table name;< br>Modify table option: alter table table name table option[=] value;
modify table character set: alter table charset GBK;
modify storage engine: alter table engine myisam;

Modify data table fields:
Basic syntax:
Add new fields: alter table table name add [column] field name data type [column attribute] [location ];
Location: The new field can be stored in any position in the table
first: The first position
after: Field name: After which field to add a new word,
No position command, The default is to add a new field at the end of the table.

Modify field: alter table table name modify field name data type [attribute] [location];
attribute: whether it is empty;

modify field name: alter table table name change old field name new field name data type [column attribute] [location];

Delete field: alter table table name drop field name;

data operation

New data:
Basic syntax:
Method 1: Insert data into the fields of the whole table, no need to specify the field list,
but It is required that the type of each data inserted in a record is consistent with the type order of the design fields in the table.
Single quotation marks are recommended for all non-numeric data.
Insert multiple rows of records at the same time.
insert into table name values(‘field 1 value’,’field value’, ……) [,(‘field 1 value’,’field value’, ……) ,. …..];

Insert multiple records.
insert into table name value(‘field 1 value’,’field value’, ……);

Method 2: Insert data into some fields of the table, you need to specify the field list,
But it is required that the type of each data inserted in a record is consistent with the type order of the design fields in the table.
Single quotation marks are recommended for all non-numeric data.
Insert multiple rows of records at the same time.
insert into table name (field 1, field 2, …, field n) values(‘field 1 value’,’field value’, ……) [,(‘field 1 Value’,’Field Value’, ……) ,……];

Query data:
Basic syntax:
Query all records in the table: select * from table name [where condition];
Query records in a specific field of the table: select field 1 [, field 2, …, field n] from table name [where condition];

Update data:
Basic syntax:
Update a row/specific Condition data of a field of multiple rows: update table name set field = value [where condition];
Update multiple field data of a row/specific condition multiple rows: update table name set field 1 = value 1 [, field 2 = value 2,…, field n = value n] [where condition];

Confusion: 13:53:27 update student set sex =’male’ where name =’John ‘Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect. 0.000 sec
update The where condition is used for precise search of the primary key.

Delete data: Basic syntax: delete all data items in the table: delete from table name; delete specific data items in the table: delete from table name [where condition];