Shulou(Shulou.com)06/01 Report--

The following mainly brings you the basic sentences commonly used in MySQL. I hope these contents can bring you practical use. This is also the main purpose of this article that I edit the basic sentences commonly used in MySQL. All right, don't talk too much nonsense, let's just read the following.

First, check the database. There are three ways to view a database

1. Enter the database and view it directly.

Mysql > show databases

After viewing, you will see four system databases by default, of which

Nformation_schema: metadata for all databases is saved. Note: the data that describes the data is called metadata. For example: folder attribute information, permissions, size, file name, file type, and so on.

Performance_schema: a new performance optimization engine after MySQL version 5.5, which is mainly used to collect performance parameters of database CVM. Note that MySQL cannot be created by the user with a storage engine of performance_schema. If it is compiled and installed, it must be created independently during initialization.

Mysql: belongs to the system library, which stores account and permission information. The administrator can manually change the mysql.user table to set the user's password.

Sys database: is used to quickly understand the metadata information of the system, because there are a large number of views and stored procedures in the library, so as to achieve quick view.

two。 Display in rows

Mysql > show databases\ G

This display mode is more humanized in the case of more databases.\ G parameters can be used in most command sets, such as show create, show select.

3. View in shell

Mysql-e is followed directly by a SQL statement, a non-interactive approach that is usually used in scripts.

Example: [root@Centos4 ~] # mysql-e 'show databases'-uroot-p123456 / / followed by a user name and password, access with this identity.

Second, create a database

Syntax: create database database name

The syntax for creating a database is very simple, but you need to note the following four points:

1. In the file system, the access to the database is represented as a directory, so the name of the database must be consistent with the system constraint directory.

two。 Comply with directory constraints

3. The database name cannot exceed 64 characters. Names wrapped with special characters must be enclosed by reverse apostrophes.

4. Duplicate names are not allowed in the database

Example 1:mysql > create database test01

Query OK, 1 row affected (0.00 sec)

Example 2:mysql > create database `test- 02`

Query OK, 1 row affected (0.00 sec)

Check the directory structure (the location of my mysql data is as follows)

Select, view and switch databases

1. When you need to switch databases, you can use the use command + database name

Mysql > use test01

Database changed

two。 When you need to check your current location, you need to use the database () function.

Mysql > select database ()

+-+

| | database () |

+-+

| | test01 |

+-+

1 row in set (0.00 sec)

3. We can also select the database we need to access directly in the shell interface.

[root@Centos4] # mysql-uroot-p123456 test01

/ / the above shows the current time, current user, and current database

Delete the database

1. Because the database test-02 contains special characters, it needs to be enclosed in reverse apostrophes.

Mysql > drop database `test- 02`

Query OK, 0 rows affected (0.00 sec)

Of course, the above methods are not recommended in the production environment, you can use the mv command to move database files directly to prevent erroneous deletion.

[root@Centos4] # mv / usr/local/mysql/data/test01 / tmp / / subject to your actual database location

two。 You can also use the IF EXISTS clause to avoid deleting error messages that occur when the database does not exist (commonly used in shell scripts)

No error message is generated regardless of whether the database exists or not. The above indicates that if the test-02 database exists, it is deleted.

Of course, the IF EXISTS clause can also be used to create a table, and if not exists means to create it if it doesn't exist.

V. about the operation of the table

1. Create table create

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

two。 View table show tables

3. View table structure

You can use the desc command to view the structure of the table (individual properties of the column)

4. See which commands are executed to create the table

5. You can specify the storage engine and default character set for the table when you create the table

Mysql > create table T2 (id int (30), name char (20), age int (10)) engine=innodb default charset=utf8

Query OK, 0 rows affected (0.00 sec)

/ / means to create a table called T2. The default engine is innodb character set and utf-8.

6. Delete tabl

The syntax is relatively simple, drop table table name

Mysql > drop table T2

Query OK, 0 rows affected (0.00 sec)

7. Prohibit pre-reading of table information

There will be a prompt before converting the database is not prohibited.

Mysql > use mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

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

[root@cong11] # mysql-uroot-p123456-A

Mysql > use mysql

Database changed # found that there was no prompt.

8. Modify the table name alter

Syntax: alter table table name rename new table name

9. Modify the field type in the table

Syntax: alter table table name modify the name of the field to modify and the type to modify.

10. Modify field types and field names in the table

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

Note: the difference between change and modify is that change needs to rename the column to change the type of column, while modify can change the type without renaming at this time.

11. Add a new column to the table

Syntax: alter table table name add field type

twelve。 Add a field after the specified column

Syntax: alter table table name add new field field type after old field

13. Delete fields from the table

Syntax: alter table table name drop field name

6. Insert field insert

Syntax: insert into table name (column name 1, column 2, column name 3.) Values (value 1, value 2, value 3)

Note: not writing column names means adding values for all column names.

You can also insert multiple data in one statement, separated by commas

7. Record select in the query table

Syntax: select * from database. Table name; the # # * sign indicates that all columns are viewed, and since there is no where clause followed, all rows are displayed.

1.

two。 More content in the table can be displayed in rows using\ G representation.

3. Query the contents of a column in a table. Multiple columns are separated by commas.

Delete data from the table (delete)

Syntax: delete from table name [where] column name = value; (if you do not add the where clause, all rows will be deleted)

1.

two。 To delete a row whose age is empty, you need to add the column name is null; after the where clause. If it is not empty, it is the column name is not null:

9. Update records

Syntax update table name set column name = 'new value' [where] + condition

If you do not add a where clause delete condition, the values of all age columns are updated. Similarly, multiple records are updated with one statement, separated by commas.

10. MySQL query sorting

Syntax: select column name. From table name order by column name asc or desc; ascending / descending (default ascending)

Use and and or for multi-conditional query

As shown above, and denotes and or indicates or.

For the above about MySQL which commonly used basic sentences, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.