[database] MySQL basic operation (basic operation ~)

mysql -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 5.7.13 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Two 、 Basic grammar ：

1、 Annotation mode ：

mysql> SELECT 1+1;     # This comment goes to the end of the line  

mysql> SELECT 1+1;    -- This comment goes to the end of the line

mysql> SELECT 1 /* This is a comment in the middle of the line */ + 1;

2、 Get help ：

「1」 Hierarchical help

mysql> ? contents      -- Be careful ：？ and contents There is a space between .

give the result as follows ：

mysql> ? contents

You asked for help about help category: "Contents"

For more information, type 'help <item>', where <item> is one of the following

categories:

   Account Management

   Administration

   Compound Statements

   Data Definition

   Data Manipulation

   Data Types

   ……

mysql> ? data types;     -- View supported data types

give the result as follows ：

mysql> ? data types;

You asked for help about help category: "Data Types"

For more information, type 'help <item>', where <item> is one of the following

topics:

   AUTO_INCREMENT

   BIGINT

   BINARY

   BIT

   BLOB

  ……

mysql> ? int    -- see int Specific introduction of types

give the result as follows ：

mysql> ? int

Name: 'INT'

Description:

INT[(M)] [UNSIGNED] [ZEROFILL]

A normal-size integer. The signed range is -2147483648 to 2147483647.

The unsigned range is 0 to 4294967295.

URL: http://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html

practice ：

see char The type and varchar Description of the type , And analyze char and varchar The storage difference between .

The order is as follows ：

mysql> ? char

Name: 'CHAR'

Description:

[NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE

collation_name]

A fixed-length string that is always right-padded with spaces to the

specified length when stored. M represents the column length in

characters. The range of M is 0 to 255. If M is omitted, the length is

1.

*Note*:

Trailing spaces are removed when CHAR values are retrieved unless the

PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.

URL: https://dev.mysql.com/doc/refman/8.0/en/string-type-syntax.html

mysql> ? varchar

Name: 'VARCHAR'

Description:

[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE

collation_name]

A variable-length string. M represents the maximum column length in

characters. The range of M is 0 to 65,535. The effective maximum length

of a VARCHAR is subject to the maximum row size (65,535 bytes, which is

shared among all columns) and the character set used. For example, utf8

characters can require up to three bytes per character, so a VARCHAR

column that uses the utf8 character set can be declared to be a maximum

of 21,844 characters. See

https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html.

MySQL stores VARCHAR values as a 1-byte or 2-byte length prefix plus

data. The length prefix indicates the number of bytes in the value. A

VARCHAR column uses one length byte if values require no more than 255

bytes, two length bytes if values may require more than 255 bytes.

*Note*:

MySQL follows the standard SQL specification, and does not remove

trailing spaces from VARCHAR values.

VARCHAR is shorthand for CHARACTER VARYING. NATIONAL VARCHAR is the

standard SQL way to define that a VARCHAR column should use some

predefined character set. MySQL uses utf8 as this predefined character

set. https://dev.mysql.com/doc/refman/8.0/en/charset-national.html.

NVARCHAR is shorthand for NATIONAL VARCHAR.

URL: https://dev.mysql.com/doc/refman/8.0/en/string-type-syntax.html

The difference between the following ：

stay MySQL in ,char and varchar Are used to store strings , The difference lies in char It has a fixed length , and varchar A variable length character type .

「2」 Quick access to help

In practice , You can quickly query by using keywords .

Execute the following command ：

mysql> ? show

mysql> ? create database;

Please write out create databases The syntax of the command ：

3、 ... and 、 Familiar with system database

1、 Displays the system database

mysql> show databases;

The results are as follows （ Write the name of the system database ）：

2、 choice mysql database

mysql> use mysql;

3、 Show mysql All tables in the database

mysql> show tables;

mysqls How many tables are there in the database ？

mysql There is... In the database 37 Different forms , adopt show tables Command to view tables in the current database

4、 see user Table structure

mysql> desc user;

user How many fields does the table have ？

Yes 6 A field

5、 adopt user Table to view the user information of the system

mysql>select user from user;

user How many users are registered in the table ？ Please write down all user names .

6、 Execute the following command , Write the results and explain their function ：

mysql> select host,user,password_last_changed from user;

result ：

function ：

7、 Command view db All users in the table （user） The query authority of （select_priv）.

command ：

Four 、 Create database

Create database Syntax ：create database dbname;

1、 Create a database using the command jxgl

The order is as follows ：

2、 stay jxgl Create the following table in the database ：

Student Table structure

establish student The table commands are as follows ：

（2）Course surface （ List of course names ） The table structure

establish Course The table commands are as follows ：

（3）SC surface （ League tables ） The table structure

establish SC The table commands are as follows ：

3、 Use show Command display jxgl All tables in the database .

The order is as follows ：

4、 Use desc Command display student Table structure ：

The order is as follows ：

give the result as follows ：

5、 ... and 、 Data import and export

1、 take jxgl Data export in database sql file

Input command line ：

cd ~/Desktop

mysqldump -u root -p jxgl> jxgl.sql  

After entering, it will let you enter MySQL Password , If you export a single table, enter the table name after the database name .

2、 Import jxgl database

（1） land mysql The server

mysql –uroot –p -- land mysql The server

mysql>create database jxgl; -- establish jxgl database

mysql> source jxgl.sql -- perform source command （ perform jxgl.sql Medium sql Code ）