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

How to achieve MySQL sentence analysis, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

There are three ways to view the database:

First, go directly to the database to view

Mysql > show databases

The 1.intformation_schema database holds all the information of the MySQL server. Such as database name, database table, blue data type, access rights and so on.

The new performance-optimized engine added in the 2.performance_schemaMySQL5.5 version is mainly used to collect the performance parameters of the database server. MySQL users cannot create tables whose storage engine is performance_schema.

3.mysql is a database that stores two account information, permission information and so on.

4.sysmysql5.7 adds sys system database, which can quickly understand the metadata of the system. Metadata is the data about data information. Such as database name or table name, data type of column, or access rights, etc.

Second, it is displayed in rows:

Mysql > show databases\ G

Third, check out the following in shell:

[root@localhost] # mysql-e'show databases'-uroot-p123456

Create the database:

Syntax: create database database name

The creation of a database doctrine: the mysql datastore represents the mysql database in a catalog manner. Therefore, the database name must be consistent with the directory name of the system constraint, special symbols are not allowed in the file and directory name, and the letters in the mysql database name will be deleted automatically.

[root@localhost ~] # ls / data/mysql/data/

In accordance with the directory constraint, the database name cannot exceed 64 characters. Names that contain special characters or all numbers or reserved words must be enclosed in backquotes (which are not displayed when viewing the database). The database cannot have the same name.

Database names with special symbols, enclosed in backquotes, can be created.

Database names with special symbols are not enclosed in backquotes and cannot be created

No backquotation marks are displayed when viewing the database.

The newly created database is stored in the database directory. (databases with special symbols are displayed in the directory according to the current system default algorithm)

View the database directory:

Enter the database to be operated

Mysql > use HA-test

View the location of the current database

Mysql > select database ()

Enter the database on the command line:

[root@localhost] # mysql-uroot-p HA-test

Check the time of login, the user logged in, the database logged in

Mysql > select now (), usr (), database ()

Delete the database:

Mysql > drop database HA-test

View the database

Msyql > show databases

In the production environment, the mv command is used on the command line to move the library under the database directory to another directory, that is, the library is deleted from the mysql database and backed up.

To restore the HA database, move the HA library to the database directory from / tmp

Use the if exists sentence to avoid error messages when deleting data that does not exist (commonly used in shell scripts

If you do not use if exists, you will get an error):

Mysql > drop database if exists HA-test; # delete if present

You can also use the

Mysql > create database if not exists HA; # create if it doesn't exist

About the operation of the table:

Create the table create:

Syntax: create table table name (field name type)

Mysql > use HA

Mysql > create table se (id int 20), name char 40, age int)

If you do not enter the database, create the table:

Syntax: create table database name. Table name (field name type)

Msyql > create table HA.se (id int 20), name char 40, age int)

View information about the table:

Mysql > use HA

Mysql > show tables

View the table structure:

Use the desc command to view the table structure (desc table name). You can enter the desc database name without entering the database for viewing. Table name.

Mysql > use HA

Mysql > desc se

View the table without entering the library: mysql > desc HA.se

You can also view the table structure with the following command, and you can do one.

Mysql > explain mysql.user

Mysql > show columns from mysql.user

Mysql > show fields from mysql.user

Mysql > show columns from mysql.user like'% user'

See which commands are executed to create the table:

Mysql > show create table se\ G

Specify the default storage engine and character set:

If you do not specify the storage engine and character set, the storage engine and character set used when you installed mysql are used by default.

Mysql > create table student2 (id int (20), name char (40), age int) ENGINE=InnoDB (or other storage engine) DEFAULT CHARSET=utf8 (or other character set)

Delete the table:

Mysql > drop table table name.

Prohibit pre-reading table information:

There will be a prompt message if there is no prohibition on preloading and changing the database (neither prohibition nor prohibition does not affect the operation)

To solve this problem, you can add the parameter-A when logging in to mysql again.

[root@localhost] # mysql-uroot-p-A

Modify the table name alter:

Syntax: alter table table name rename new table name

Modify the field type in the table:

Syntax: alter table table name modify modify field name modify type

View students table structure

Mysql > desc students

Mysql > alter table studens modify id int (10)

Modify the field type and field name in the table:

Syntax: alter table table name change source field name new field name new field type

Mysql > alter table studens change name gname char (20)

Note: the difference between change and modify:

Change can either rename the column or change the column type. Modify can only change the column type, not rename the column.

Add fields to the table:

Syntax: alter table table name add field type.

Adding fields to the table does not mean that you can add field values at will. For example, enum is an enumerated type, and he can only enter values defined by the administrator.

View table structure

Enter something other than m or w in the sex line to report an error:

Enter m or w and there will be no error:

Insert multiple records at the same time: mysql > insert into studens values (1), (2) (2)

Select is recorded in the query table:

Syntax: select (or field name of the query) from table name; the # sign indicates all records in the query table

Query all records in the table

When there are many records in the table, you can use\ G to view

Query only the contents of a field in the table

Mysql > select gname from studens

View tables in other libraries or do not view tables on this database

Syntax: select field from database name. Table name; # is equivalent to entering the use database first, and then looking at the table contents

Delete the record in the table:

Delete the specified row: syntax delete from table name where condition to delete

For example: delete the row with id 1 in the studens table

Mysql > select id from studens

Mysql > delete from studens where id=1

Mysql > select id from studens

Delete empty rows: syntax delete from table name where condition is null

For example: delete a line whose age is empty

Mysql > delete from studens where age is null

Update the record:

Syntax: update table name set condition where condition

For example: update the record age with id 2 in the table to 30

Mysql > update studens set age='30' where id=2

Update the values of all columns in the update table are updated

For example: update all gname in the table to zhangliu

Mysql > update studens set gname='zhangliu'

After reading the above, have you mastered the method of how to implement MySQL sentence analysis? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.