Dead work

Install MySQL on windows and Ubuntu. Specific installation steps can be queried by csdn forum.

Command-line scripts

Operation of database

• Connect to the database

Note: The sql statement ends with a semicolon.
mysql -uroot -p;
Enter the password to enter the database.

• Exit the database

qiut，exit，ctrl + d；

• View all databases

show databases;

• Using databases

use database name;

• View the database currently in use

select database();

• Display database version

select version();

• Create a database

create database demo charset=utf8;
You need to know the character set. Note that it's not utf-8.

• View database creation statements

show create database demo;

• Delete the database

drop database demo;

• View Help Documents

How to use the help document in sql?
Use? Get help information
 Functions; view the help documentation for functions

Operation of Data Table

• View all tables in the current database

show tables;

• Create table

auto_increment represents automatic growth
 Create a student's data table (id name age high gender cls_id)
create table data table name (field name type constraints [, field type constraints]);
Multiple constraints in no sequence
 enum denotes enumerated men: the original value will have an enumerated value (starting from 1)
Do not add commas to the last field
 Unsigned means unsigned (only positive, no negative)

• Create students tables

create table students (
    id int unsigned primary key auto_increment not null,
    name varchar(15) not null,
    age tinyint unsigned default 18,
    height decimal(5,2) default 0,
    gender enum("male","female","neutral","secrecy") default "secrecy",
    cls_id int unsigned default 0,
    is_delete bit default 0
);

• Insert data (to be used later)

insert into students values
(0,'Xiao Ming',18,180.00,2,1,0),
(0,'Small moon',18,180.00,2,2,1),
(0,'Eddie Peng',29,185.00,1,1,0),
(0,'Lau Andy',59,175.00,1,2,1),
(0,'Huang Rong',38,160.00,2,1,0),
(0,'Miss Luo Yu Feng',28,150.00,4,2,1),
(0,'Joey Wong',18,172.00,2,1,1),
(0,'Jay Chou',36,NULL,1,1,0),
(0,'Cheng Kun',27,181.00,1,2,0),
(0,'Liu Yifei',25,166.00,2,2,0),
(0,'Venus',33,162.00,3,3,1),
(0,'Static fragrance',12,180.00,2,4,0),
(0,'Guo Jing',12,170.00,1,4,0),
(0,'Zhou Jie',34,176.00,2,5,0);

• View statements that create tables

show create table students;

• View table structure

desc students;

• Modify table structure
  
  1. • Modify tables - add fields

alter table Table name add Column type/constraint;
alter table students add birthday datetime default "2011-11-11 11:11:11";

1. • Modify tables - Modify fields: No renaming

alter table Table name modify Listing Types and Constraints;
alter table students modify birthday date default "2011-11-11";

1. • Modify Table-Modify Field: Renamed Version

alter table Table name change Types and constraints of original and new lists;
alter table students change birthday birth date default "2011-11-11";

• Modify tables - delete fields

alter table students birth;

• ###### Delete tables

drop table students;

curd

• ###### Increase insert
• • Full column insertion values correspond to the order of fields in the table
    
    1. • insert into table name values (value 1,... )
    2. • Placement operations for primary key fields: 0, NULL, Default
    3. • Subscripts enumerated in sql default to start from 1
    4. • Full column insertion is rarely used in practical development: if the table structure is modified (add or delete fields), sql statements inserted in full column will be wrong

insert into students values (0,"Little Joe",18,180.00,"female",2);
insert into students values (NULL,"Little Joe",18,180.00,"female",2);
insert into students values (NULL,"Big Joe",18,180.00,2,2);
//Error: insert into students values (Default,"Yu Ji",18,180.00,10,2,10);

• Specifies column insertion

insert into Table name (column1,...) values(value1,...)
insert into students (name, gender, cls_id) values ("Luban",1,1);

• Multi-line Insert Batch Insert

insert into Table name(column1,...) values (value1,...),(value1,...),...
insert into students (name, gender, cls_id) value ('wasp', 'female', '2'),('vae', 'male', '1');

• ###### Delete
  
  1. • Physical deletion

DELETE FROM tbname [where Conditional judgement]
delete from students where id = 5;

1. • Logical deletion:

Identify whether a record has been deleted
update students set is_delete = 1 where id = 4;
Query which students have not been deleted
select * from students where is_delete = 0;
 ```
-###### Amendment

where indicates the scope of the modification
update table name set column 1 = value 1, column 2 = value 2... where condition
Full table updates, do not use whole table updates
update students set age = 20;
Specified Scope Update
update students set age = 20 where id = 3;
In sql, equality is represented by an equal sign
"`