Shulou(Shulou.com)05/31 Report--

This article introduces you how to execute the mysql command line script, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Command line connection

The command operation mode is mainly used in the work, which requires skillful writing.

Open the terminal and run the command

Mysql-uroot-p

Enter the password after entering enter. The password currently set is mysql.

After a successful connection, the following figure

Log out

Quit and exit

Or

Ctrl+d

After logging in successfully, enter the following command to view the effect

View version: select version ()

Show current time: select now ()

Modify the input prompt

Prompt python > 1\ D full date\ U user

Database

View all databases

Show databases

Using the database

Use database name

View the database currently in use

Select database ()

Create a database

Create database database name charset=utf8

Example:

Create database python charset=utf8

Delete database

Drop database database name

Example:

Drop database python

Data sheet

View all tables in the current database

Show tables

Create a tabl

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 (5dag2), gender enum ('male', 'female', 'shemale', 'secrecy'), cls_id int unsigned default 0)

Modify Table-add Field

Alter table table name add column name type

Example:

Alter table students add birthday datetime

Modify Table-modify Fields: renamed version

Alter table table name change original name new name type and constraint

Example:

Alter table students change birthday birth datetime not null

Modify table-modify fields: do not rename version

Alter table table name modify column name types 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 tabl

Drop table table name

Example:

Drop table students

View the creation statement of the table

Show create table table name

Example:

Show create table classes

Add, delete, modify and search (curd)

Interpretation of curd: stands for creating (Create), updating (Update), reading (Retrieve) and deleting (Delete)

Basic usage of query

Query all columns

Select * from table name

Example:

Select * from classes

Query the specified column

You can use as to specify aliases for columns or tables

Select column 1, column 2. From table name

Example:

Select id,name from classes

Increase

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

Note: the primary key column grows automatically, but it needs to occupy a space when inserting all the columns. Usually use 0 or default or null to occupy the space. After the insertion is successful, the actual data shall prevail.

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

Insert into table name values (...)

Example:

Insert into students values (0Jing Guo Jing, 1pm Mongolia, 2016-1-2')

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

The name of the insert into table (column 1.) Values (worth 1pm...)

Example:

Insert into students (name,hometown,birthday) values ('Huang Rong', 'Peach Blossom Island', '2016-3-2')

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

The insert into table name is values (...), (...).

Example:

Insert into classes values (0Magnum python 1'), (0Magnetium python 2'); insert into table name (column 1Jing...) Values (worth 1pm), (worth 1pm...)

Example:

Insert into students (name) values ('Yang Kang'), ('Yang Guo'), ('Xiao Longnu')

Modify

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

Update table name set column 1 = value 1, column 2 = value 2. Where condition

Example:

Update students set gender=0,hometown=' Beijing 'where id=5

Delete

DELETE FROM tbname [where condition judgment] 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 password for mysql when prompted

Restore

Connect to mysql and create a new database

To exit the connection, execute the following command

Mysql-uroot-p new database name < python.sql# enter the mysql password according to the prompt on how to execute the mysql command line script is shared here, I hope the above content can be helpful to you, you can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.