Summary of basic usage commands for MySQL database

MySQL database is a common relational database

What are the core elements of a relational database?

Primary key: Special field to uniquely identify the uniqueness of a record
Fields: Data columns
Records: Data rows
Data table: Collection of data rows
Databases: collections of data tables

Commands to install, start, stop, and restart the MySQL server

Installation: sudo apt-get install Mysql-server
Start: sudo service MySQL start
# See if MySQL service is present in the process PS Ajx|grep MySQL
Stop: sudo service mysql stop
Restart: sudo service mysql restart

Operation of MySQL Database

1. Connect to the database

Mysql-u Root-pmysql

Do not show password connection

[Email protected]:~/desktop$ mysql-u root-p

Enter Password:mysql

Exit Database

Ctrl+l: Clear Command screen

Quit\exit

2. Create a database
Create database name Charset=utf8;
# # # When you create a database, always remember to solve the coding problem

3. View the database currently in use: select databases ();
View so database: show databases;
View all tables in the current database: show Tabes;
View all columns in the current table: SELECT * from table name;
View table structure: DESC table name;

4.1) The type of data to be used to create the table:
Integers: int,bit #int unsigned: unsigned shaping #tinyint unsigned: unsigned shaping (but smaller in size, typically for age) #.bit is a bit data type with a length of 1 bytes; int is an integral type; bit is actually a bool type, only 0 and 1,int is a 4-byte integer
Decimal: Decimal #decimal (5,2) represents five digits, two decimal places
String: Varchar,char #varchar: Variable string
DateTime: Date, Time, datetime
Enum type: enum
Primary key: Primary key
Auto Grow (Increase): Atuo_increment
Defaults: Default
Cannot be empty: NOT NULL
FOREIGN key: FOREIGN key
Inch
2) Table format:CREATE table data table name (ID unsigned primary key auto-grow (increment) cannot be empty; Name variable string (number/range) default value '; age unsigned default value 0;height decimal; Gender enumeration default value; foreign key None Symbol shaping default value);
Example: CREATE table t_students (ID int unsigned primary key auto_increment not null,name varchar (TEN) Default ' ', Age tinyint uns igned default 0,height decimal (5,2), gender enum (' Male ', ' female ', ' neutral ', ' confidential ') default ' secrecy ', cls_id int unsigned default 0);

5. View the statement that created the database: Show Create library name
View the statement that created the table: Show create TABLE table name

6. Using the database: Use database name
Delete database: Drop database name
Delete tables: drop table table name
Delete a table--delete a field (column) ALTER TABLE name drop column name

7. Changes to the table:

1) Modify table-Add field Kouhao (class slogan)
ALTER TABLE name add column name type and constraint;
ALTER TABLE t_classes add Kouhao varchar () NOT NULL default ' life is short, I use Python ';

2) Modify table-Modify field: Rename version
ALTER TABLE name change formerly known as new name type and constraints;
ALTER TABLE t_classes change Kouhao logo varchar (20);

3) Modify table-Modify field: Do not rename version
ALTER TABLE name modify column name type and constraint;
ALTER TABLE t_classes Modify logo varchar () NOT NULL default ' life is short, I use Python ';

8. Data deletion and modification (curd)
Curd Explanation: Representative creation (create), Updates (update), read (Retrieve), and delete

1) Increase

1. Full column Insertion
Insert [into] table name values (...) #into可用可不用
Primary key fields can be placeholders with 0 null default
such as: Inserting a class into the classes table
INSERT into t_classes values (0, ' python02 ');

INSERT into t_classes values (0, ' python01 ');

such as: Inserting a student information into the students table (ID,NAME,AGE,HEIGHT,GENDER,CLS_ID)
Insert t_students values (NULL, ' Big Joe ', 23,165.12, ' Male ', 1);

Insert t_students values (null, ' Li Bai ', 23,180.12, ' female ', 1);

2. Partial insertion
Insert into table name (column 1,...) VALUES (value 1,...)
Insert into t_students (Name,gender) VALUES (' Zhang Fei ', 1); #这里张飞后面的一是创建表时, enumeration parameter order for list sex column objects

3. Multi-row insertion
Insert into table name (Name,gender) VALUES ("Xiao Zhang 1", 1), ("Xiao Zhang 2", 2);
Insert into t_students (Name,gender) VALUES (' Xiao Wang ', 2), (' King ', 3);

2) Modify
Update table name set column 1= value 1, column 2= value 2 ... where condition;

1. All modifications
Update t_students set height=188.88;

2. Modification by condition
Update t_students set gender= ' female ' where id=1;

3. Modify multiple values on a per-condition basis
Update students Set gender = "", name = "XXX";
Update t_students set height=165.60,gender=1 where id=3;

3) Query

1. Querying all Columns
SELECT * from table name;
SELECT * from T_students;

2. Specify criteria Query
SELECT * from t_students where name= ' Li Bai ';

3. Querying a specified column
Select column 1, column 2,... from table name;
Select Name,age from T_students;

4) Delete

1. Physical deletion #删除后不可恢复
Delete from table name where condition
Delete from t_students where id=4;

2. Tombstone #对要删除的对象做标记, recoverable (use a field to indicate whether the message is no longer available)
You need to add a isdelete field bit type to the students table for tombstone
Isdelete=1 is to represent the deletion tag; is_delete=0 is to restore #用二进制0和1表示
Update t_students set isdelete=1 where id=5;

Summary of basic usage commands for MySQL database