Operation of database

Linked database

mysql -u root -proot    -- Direct display of password is not recommended
mysql -uroot -p  

Exit database

exit/quit

The sql statement needs a semicolon at the end; the end
Show database version

select version();

Display time

select now();

View all databases

show databases;

DDL (data definition statement)
Create database

create database database_name;
create database database_name charset=utf8;

View the statement to create the database

Show create database database "name; -- ` ` with special characters'

View currently used databases

select database();

Use database

use database_name;

Delete database

drop database database_name;

Operation of data table

View all tables in the current database

show tables;

Create table
Auto increment means automatic growth
not null means cannot be empty
Primary key means primary key
Default default

create table data table name (field type constraint [, field type constraint]);

create table table1( 
	id int primary key not null auto_increment,
	name varchar(30) not null
);

View table structure

desc table1;--desc table name;

Create students table (ID, name, age, high, gender, cls_id)

create table students(
	id int primary key not null auto_increment,
	name varchar(30),
	age tinyint unsigned default 18,
	high decimal(5,2) unsigned,
	gender enum('male','female','secrecy') default 'secrecy',
	cls_id int 
);

Create classes table (id, name)

create table classes(
	id int primary key not null auto_increment,
	name varchar(30)
);

Create statement of view table

show create table students;--show create table Table name;

Modify table - add field

alter table students add birthdat date;--alter table name add column name type;

Modify table - modify field: do not rename version

alter table students modify birthday date default'1990-1-1';--alter table table name modify column name type constraint;

Modify table - modify field: Rename version

alter table students change birthday birth data default '1990-1-1';--alter table name change original name new name type constraint;

Modify table - delete field

alter table students drop high;--alter table Table name drop Column names;

Delete table

drop table table name;
drop database database name;

Add, delete, modify (curd)

Increase:
Full column insertion

insert into classes values(1,'Class one');--insert [into] Table name values(...)；

Insert a student information into the students table  
 +--------+-------------------------------+------+-----+------------+----------------+
 | Field  | Type                          | Null | Key | Default    | Extra          |
 +--------+-------------------------------+------+-----+------------+----------------+
 | id     | int(11)                       | NO   | PRI | NULL       | auto_increment |
 | name   | varchar(30)                   | YES  |     | NULL       |                |
 | age    | tinyint(3) unsigned           | YES  |     | 0          |                |
 |gender | enum('male ',' female ',' confidential ') | YES | confidential ||
 | cls_id | int(10) unsigned              | YES  |     | NULL       |                |
 | birth  | date                          | YES  |     | 1997-01-01 |                |
+--------+-------------------------------+------+-----+------------+----------------+

Primary key fields can use 0, null, and default to occupy

insert into table values(0,'dog',18,'male',1,'1990-1-1');
insert into table values(null,'dog',18,'male',1,'1990-1-1');
insert into table values(default,'dog',18,'male',1,'1990-1-1');

Enumeration type insertion subscript starts from 1

insert into students values(0,'cat',18,1,1,'1990-1-1'); -- select male for enumeration type

Partial insertion

-- insert into Table name(Column 1,...) values(Value 1,...)        -- Non empty field
insert into students(`gender`) values(2);
insert into students(`name`,`gender`) values('pig',2)

Multi line insertion

insert into students values
(default,'sheep',19,1,'1990-1-1')
(default,'tiger',20,2,'1991-1-1')
;

modify

--update table name set column 1 = value 1, column 2 = value 2... where condition;
Update students set name ='dog 'where name ='dog'; -- modify all without adding where

--update table name set column 1 = value 1, column 2 = value 2... where condition; 

Delete:

Physical deletion

delete from students where id = 1; -- delete from Table name where condition
delete from students;--Delete all;

If it is deleted in this way, the data will be deleted directly, which is unrecoverable!!

Logical deletion
Set a column named is_delete, where the value is 0, 1, 0 represents not deleted, and 1 represents deleted.

update students set is_delect=1 where id = 1;

Basic use of query

select de duplication option field list [as field name] from data table where [group by clause] [having clause] [order by clause] [limit clause];

Query all columns

select * from students;

Remove duplicate field query distinct whether the rows of the whole query are duplicate

select distinct name from students;

Query specified column

select name,age from students; -- select Column 1,Column 2,... from Table name;

You can use as to specify aliases for columns or tables

select name as Name,gender from students;