Basic operation of MySQL database (add and revise) command

This article will describe the basic operations of MySQL data. Pre-installation please self-Baidu

Operating environment: Linux Ubuntu 16.04

Command line connection

• The main use of command operation in the work, requires proficiency in writing
• Open terminal, Run command

mysql -uroot -p回车后输入密码，当前设置的密码为mysql

• After successful connection, as
• Sign Out

• quit 和 exit或ctrl+d

• After successful login, enter the following command to see the effect

• 查看版本：select version();显示当前时间：select now();

Database operations

• View all databases

show databases;

• Working with databases

use 数据库名;

ps：需先使用此命令才能对数据库中的表进行操作

• View the database currently in use

select database();

• Create a database

create database 数据库名 charset=utf8;例：create database python charset=utf8;

ps：如果不加charset=utf8这个参数，在数据中的数据如果有中文时，运行会报错，千万注意！

• Deleting a database

drop database 数据库名;例：drop database python;

ps：从删库到跑路，小伙子，这个命令不应该被记住！哈哈

Data table Operations

• View all tables in the current database

show tables;

• View table Structure

desc 表名;

• Create a table
• Auto_increment indicates auto-grow

CREATE TABLE table_name (    column1 datatype Contrai, column2 datatype, column3 datatype    ,    .....    columnn datatype,     or More columns));

Example: Creating a Class Table

CREATE TABLE classes (    not  null,    name varchar);

Example: Creating a student table

CREATE TABLE students (    not  null,    name varchar (", age    tinyint Unsigned default 0,    height decimal (5,2),    gender enum (' male ', ' female ',' shemale ',' secrecy '),    cls_id int Unsigned default 0)

• Modify Tables-Add fields

alter table 表名 add 列名 类型;例：alter table students add birthday datetime;

• Modify table-Modify field: Rename version

alter table 表名 change 原名 新名 类型及约束;例：alter table students change birthday birth datetime not null;

• Modify table-Modify field: Do not rename version

alter table 表名 modify 列名 类型及约束;例：alter table students modify birth date not null;

• Modify Table-Delete field

alter table 表名 drop 列名;例：alter table students drop birthday;

• Delete a table

drop table 表名;例：drop table students;

• View creation statements for a table

show create table 表名;例：show create table classes;

Change and delete (curd)

Curd Explanation: Representative creation (create), Updates (update), read (Retrieve), and delete

Query Basic use

• Querying all Columns

select * from 表名;例：select * from classes;

• Querying a specified column
• You can use as to specify an alias for a column or table

select 列1,列2,... from 表名;例：select id,name from classes;

Increase

Format: INSERT [into] tb_name [(Col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...), (...),...

• Note: The primary key column is autogrow, but requires a placeholder for full column insertion, usually using 0 or default or null to occupy the position, and the actual data will prevail after the insert succeeds
• Full column Insert: The order of values corresponds to the order of the fields in the table

insert into 表名 values(...)例：insert into students values(0,’郭靖‘,1,‘蒙古‘,‘2016-1-2‘);

• Partial column insertion: The order of values corresponds to the given column order

insert into 表名(列1,...) values(值1,...)例：insert into students(name,hometown,birthday) values(‘黄蓉‘,‘桃花岛‘,‘2016-3-2‘);

• The above statement can insert a row of data into a table at a time, and you can insert multiple rows of data at once, which can reduce communication with the database
• Full column multiple row inserts: The order of values corresponds to the given column order

 insert  into table name values (...) ...; Example: insert into classes values (0, ' Python1 '), ( 0, ' Python2 ');         

insert into 表名(列1,...) values(值1,...),(值1,...)...;例：insert into students(name) values(‘杨康‘),(‘杨过‘),(‘小龙女‘);

Modify

Format: UPDATE tbname SET col1={expr1| DEFAULT} [, Col2={expr2|default}] ... [Where Condition judgment]

update 表名 set 列1=值1,列2=值2... where 条件例：update students set gender=0,hometown=‘北京‘ where id=5;

Delete

DELETE from Tbname [Where condition judgment]

delete from 表名 where 条件例：delete from students where id=5;

• Tombstone is essentially a modification operation

update students set isdelete=1 where id=1;

Backup

• Run the mysqldump command

mysqldump –uroot –p 数据库名 > python.sql;# 按提示输入mysql的密码

Recovery

• Connect MySQL to create a new database
• Exit the connection and execute the following command

mysql -uroot –p 新数据库名 < python.sql# 根据提示输入mysql密码

Basic operation of MySQL database (add and revise) command