Summary of basic SQL statements in MySQL database

This article uses examples to describe the basic SQL statements of the Mysql database. Share with you for your reference, the details are as follows:

Basic SQL Statements

1. Login, logout and shortcut keys:

(1) Shortcut keys:

————Quickly return to the beginning of the line
ctrl + a

————Back to the end of the line
ctrl + e

————Clear screen
ctrl + l

----Finish
ctrl + c + space

(2) Connect to the database:

————Show password

mysql -uroot -pmysql

————Do not show password

mysql -uroot -p 
Enter password

(3) Exit the database:

quit/exit/ctrl + d

(4) Some commands:

————Show database version

select version();

————Display time

select now();

2. Database operations:

(1) Create:

————Create a database

create database database name charset=utf8;

(2) Usage:

————Using the database

use database name

(3) View:

————View the currently used database

select database();

————View all databases

show databases;

————View the statement to create a database

show create database database name;

(4) Delete:

————Delete the database

drop database database name;

(5) Database backup and recovery (understanding):

————Backup

mysqldump -uroot -p database name> xxx.sql

----recover

mysql -uroot -p new database name < xxx.sql

3. Table structure operation:

(1) View:

————View all tables in the current database

show tables;

————View the table structure

desc table name

————View the table creation statement

show create table table name;

(2) Create:

————Create table

create table table name( 
Field name data type constraints, 
…….. 
…….. 
); 
(auto_increment: automatic growth)

(3) Modifications:

————Add fields

alter table table name add field name type constraint;

————Modify fields (not rename)

alter table table name modify field name type constraint;

————Modify field (rename)

alter table table name change old field name new field name type constraint;

————Delete field

alter table table name drop field name;

(4) Delete:

————Delete table:

drop table table name;

4. Table data operation:

(1) View:

————View all fields

select * from table name;

————View the specified field

select field name, field name from table name;

————View by conditions

select * from table name where condition;

————Give the field an alias to view

select field name as alias from table name;

(2) Addition:

————Full column insert

insert into table name values ​​(record);

————Partial insertion

insert into table name (field name, field name) values ​​(record);

————Multiple row inserts

insert into table name values ​​(record 1), (record 2)…;

(3) Delete:

————Physical deletion

delete from table name where condition;

————Logical deletion

alter table table name add delete information field name bit dafault 0; 
update table name set delete information field name = 1 where condition;

(4) Modifications:

————All changes

update table name set modification information;

————Modify according to conditions

update table name set modify information where condition;

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.