mysql command line script execution example

This article uses an example to illustrate the execution of MySQL command line scripts. Share with you for your reference, the details are as follows:

Command line connection

The main operation mode is command operation, which requires proficiency in writing

Open the terminal and run the command

mysql -uroot -p

Enter the password after pressing Enter. The current password is mysql.

After the connection is successful, the following figure is shown

Log out

quit and exit

or

ctrl+d

After successful login, enter the following command to view the effect

Check the version: select version();

Display the current time: select now();

Modify the input prompt

prompt python>
1
\D Full date\U User

database

View All Databases

show databases;

Using Database

use database name;

View the currently used database

select database();

Create a database

create database database name charset=utf8;

example:

create database python charset=utf8;

Deleting a Database

drop database database name;

example:

drop database python;

Data Sheet

View all tables in the current database

show tables;

Create Table

auto_increment means automatic growth

CREATE TABLE table_name(
  Column1 datatype contrai,
  column2 datatype,
  column3 datatype,
  .....
  columnN datatype,
  PRIMARY KEY (one or more columns)
);

Example: Create a class table

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

Example: Create a student table

create table students(
  id int unsigned primary key auto_increment not null,
  name varchar(20) default '',
  age tinyint unsigned default 0,
  height decimal(5,2),
  gender enum('male','female','shemale','confidential'),
  cls_id int unsigned default 0
)

Modify table - add fields

alter table table name add column name type;

example:

alter table students add birthday datetime;

Modify table - modify field: rename version

alter table table name change original name new name type and constraints;

example:

alter table students change birthday birth datetime not null;

Modify table - modify field: do not rename version

alter table table name modify column name type and constraints;

example:

alter table students modify birth date not null;

Modify table - delete field

alter table table name drop column name;

example:

alter table students drop birthday;

Delete a table

drop table table name;

example:

drop table students;

View the table creation statement

show create table table name;

example:

show create table classes;

Add, delete, modify and check (curd)

Explanation of curd: stands for Create, Update, Retrieve and Delete

Basic query usage

Query all columns

select * from table name;

example:

select * from classes;

Query the specified column

You can use as to specify an alias for a column or table.

select column1, column2,... from tablename;

example:

select id,name from classes;

Increase

Format: INSERT [INTO] tb_name [(col_name,…)] {VALUES | VALUE} ({expr | DEFAULT},…),(…),…

Note: The primary key column is automatically incremented, but a placeholder is required when the full column is inserted. Usually 0, default, or null is used as a placeholder. After successful insertion, the actual data will prevail.

Full column insert: the order of values ​​corresponds to the order of fields in the table

insert into table name values(...)

example:

insert into students values(0,'Guo Jing',1,'Mongolia','2016-1-2');

Partial column insertion: the order of values ​​corresponds to the order of the columns given

insert into table name (column 1,...) values ​​(value 1,...)

example:

insert into students(name,hometown,birthday) values('Huang Rong','Taohua Island','2016-3-2');

The above statement can insert one row of data into the table at a time, or multiple rows of data at a time, which can reduce communication with the database.

Full column multi-row insert: the order of values ​​corresponds to the order of the given columns

insert into table name values(...),(...)...;

example:

insert into classes values(0,'python1'),(0,'python2');

insert into table name (column 1,...) values ​​(value 1,...), (value 1,...)...;

example:

insert into students(name) values('杨康'),('杨国'),('小龙女');

Revise

Format: UPDATE tbname SET col1={expr1|DEFAULT} [,col2={expr2|default}]…[where condition]

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

example:

update students set gender=0,hometown='北京' where id=5;

delete

DELETE FROM tbname [where condition]

delete from table name where condition

example:

delete from students where id=5;

Logical deletion is essentially a modification operation

update students set isdelete=1 where id=1;

Backup

Run the mysqldump command

mysqldump –uroot –p database name> python.sql;

 
# Enter the mysql password as prompted

recover

Connect to mysql, create a new database and exit the connection, execute the following command

mysql -uroot –p new database name < python.sql

# Enter the mysql password as prompted

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.