MySQL database basic syntax and operation

MySQL database basic syntax

DDL Operations

Create database syntax: create database database name;
View all database syntax: show databases;
Switch (use) database syntax: use + database name;
Create a table syntax: create table table name (
Field Name 1 Field Type,
Field Name 2 Field Type,
Field Name 3 Field Type,
…… ……
);
View all tables in the database: Show tables;
View the structure syntax of the table: desc table name;
Add field syntax: alter table table name add field name field type Delete field syntax: alter table table name drop field name Modify table name syntax: rename table old table name to new table name Modify field type syntax: alter table table name modify field name new field type Field rename syntax: alter table table name change old field name new field name field type

DML Operations

Insert data syntax: insert into table name (field name) value (content)
Deleting data syntax: Delete from table name where condition Modifying data syntax: update table name set field = modified content where (restriction condition)
View data syntax: select field name from table name where condition delete table syntax: drop table table name;
truncate table table name;
Deleting a Database
drop database library name;
Note:
(1) Delete only deletes the records inserted into the table but does not delete the records. (2) Truncate deletes both the data and the records, which is equivalent to dropping the table and then creating it.

constraint

Primary key constraint features: uniqueness, non-nullability. Setting primary key and auto-increment primary key: When creating a table, add the primary key after the field to be set as the primary key.
Auto-increment: When creating a table, add auto_increment after the field to be set to auto-increment.
Not null constraint: cannot be empty. Add not null in the constraint position.
Foreign key constraints:
To create a foreign key based on the primary key of the main table, add the foreign key after the field definition.
Constraint (foreign key name) foreign key (the field being constrained) references primary table name (the field constrained by the outside world)
The field used as a constraint in the main table must be the primary key of the table

DQL Operations

Basic query query all:
select * from table name to query the data of the specified column:
Select column name 1, column name 2... from table name Write which column(s) to check Which column to check in the current database View tables in other databases
Show tables in database name View the data in the table of the non-current database
Select column name from bank.user;
Where query condition relational operator: > < = != >= <=
Interval: between A and B [A,B]
AND && and
or || : or
not: non-negation
Is null: is empty
is not null : not empty
in what contains fuzzy query
Like like wildcard
_: Any character
%: any number

This is the end of this article about the basic syntax and operations of MySQL database. For more relevant MySQL database syntax content, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!