What is the basic operation of MySQL to manage database, data table and data 04/05 Update SLTechnology News&Howtos

What is the basic operation of MySQL to manage database, data table and data

2025-04-05 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

This article mainly explains "what is the basic operation of MySQL managing database, data table and data". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let the editor take you to learn "what is the basic operation of MySQL to manage databases, data tables and data"?

Note: MySQL database commands are not case sensitive, but if you want to use the tab key automatic completion command on the Mac side, you must use uppercase.

1. Database management

1.1 create create database

Mysql > create database test;Query OK, 1 row affected (0.50 sec)

1.2 show view all databases

1.3 alter modifies the database

The alter command modifies the database code:

Generally speaking, the character set we set uses UTF8. If you find that the database encoding is not UTF8, you can use this statement to change the database encoding:

Mysql > alter database test character set utf8;Query OK, 1 row affected (0.01 sec)

1.4 use uses the database

Mysql > use test;Database changed

1.5 View the database currently in use

Mysql > select database (); +-+ | database () | +-+ | test | +-+ 1 row in set (0.00 sec)

1.6 drop delete database

Mysql > drop database test;Query OK, 0 rows affected (0.00 sec)

2. Database table management

Let's first create a database for later use:

Mysql > create database test;Query OK, 1 row affected (0.06 sec)

After creation, use the use command to enter the database:

Mysql > use test;Database changed

2.1 create create Table

Mysql > create table test (id int auto_increment primary key,-> name varchar (20) not null,-> age int not null,-> birthday datetime); Query OK, 0 rows affected (0.14 sec)

2.2 show display table

Mysql > show tables;+-+ | Tables_in_test | +-+ | test | +-+ 1 row in set (0.00 sec)

2.3 desc View Table structure

Mysql > desc test +-+ | Field | Type | Null | Key | Default | Extra | + -+-+ | id | int (11) | NO | PRI | NULL | auto_increment | | name | varchar (20) | NO | | NULL | | age | int (11) | NO | | NULL | | birthday | datetime | YES | | NULL | | +-| -+-+ 4 rows in set (0.01sec)

2.4 alter modify table structure (add, delete, change)

2.4.1 add adds fields (columns) to the table

Mysql > alter table test add star bool;Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0

Note: Boolean types are automatically converted to tinyint (1) types in MySQL

Look at it with desc to get:

2.4.2 modify modify Table Field (column)

Mysql > alter table test modify star int;Query OK, 0 rows affected (0.43 sec) Records: 0 Duplicates: 0 Warnings: 0

Note: int can specify a length or not. If it is not specified, the default length is int (11). After modification, use desc to view it again:

2.4.3 drop delete table fields (columns)

Mysql > alter table test drop column star;Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0

Looking at the table structure, you can see that there is no star field:

2.4.4 rename rename table name

Mysql > rename table test to people;Query OK, 0 rows affected (0.01 sec)

Look at the table again and find that the name of the table has changed:

Mysql > show tables;+-+ | Tables_in_test | +-+ | people | +-+ 1 row in set (0.00 sec)

2.5 create creates a new table using existing data

Mysql > create table newpeople select * from people;Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0

The tables that exist in the database are now available:

3. Operation and management of data

3.1 increase data (increase)

Mysql > insert into people values (null,'Anny',22,'1990-09-09'); Query OK, 1 row affected (0.00 sec)

Query with select:

Mysql > select * from people +-+ | id | name | age | birthday | +-+ | 1 | Anny | 22 | 1990-09-09 00:00:00 | +-+-- -+-+ 1 row in set (0.00 sec)

3.2 Delete data (delete)

Mysql > delete from people where name='Anny';Query OK, 1 row affected (0.00 sec)

Then use select to query and find the data that has not been deleted:

Mysql > select * from people +-+ | id | name | age | birthday | +-+ | 2 | Lisa | 20 | 1992-09-09 00:00:00 | +-+-- -+-+ 1 row in set (0.00 sec)

3.3 modify data (change)

Mysql > update people set name='Anny' where name='Lisa';Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

If you check the data again, you can see that the name of Lisa has changed to anny:

Mysql > select * from people +-+ | id | name | age | birthday | +-+ | 2 | Anny | 20 | 1992-09-09 00:00:00 | +-+-- -+-+ 1 row in set (0.00 sec)

3.4 query data (check)

The simplest is the following statement:

Mysql > select * from people +-+ | id | name | age | birthday | +-+ | 2 | Anny | 20 | 1992-09-09 00:00:00 | +-+-- -+-+ 1 row in set (0.00 sec)

* indicates all fields, or you can specify field queries, as follows:

Mysql > select name from people;+-+ | name | +-+ | Anny | +-+ 1 row in set (0.00 sec)

4. Manage the view

4.1 create a view

Mysql > create view people_view (name,age) as select name,age from people;Query OK, 0 rows affected (0.00 sec)

After the creation is successful, you can view the view:

Mysql > select * from people_view;+-+-+ | name | age | +-+-+ | Anny | 20 | +-+-+ 1 row in set (0.00 sec)

You can use desc to view view fields:

4.2 replace the view

Create or replace the original view:

Mysql > create or replace view people_view (id,name,age) as select id,name,age from people;Query OK, 0 rows affected (0.00 sec)

View an existing view:

Mysql > select * from people_view;+----+ | id | name | age | +-- + | 2 | Anny | 20 | 3 | Lisa | 22 | +-+ 2 rows in set (0.00 sec)

4.3 Operation View

When the view changes, the corresponding table will also change, that is, to manipulate the view is to manipulate the table, and we insert a piece of data into the view:

Mysql > insert into people_view values ('Lisa',22); Query OK, 1 row affected (0.08 sec)

After the insertion is successful, we look at the view data:

Mysql > select * from people_view;+-+-+ | name | age | +-+-+ | Anny | 20 | | Lisa | 22 | +-+-+ 2 rows in set (0.00 sec)

You can see that it is already in the view, and then look at our tabular data:

Mysql > select * from people +-+ | id | name | age | birthday | +-+ | 2 | Anny | 20 | 1992-09-09 00:00:00 | | 3 | Lisa | 22 | NULL | +-+- -+ 2 rows in set (0.00 sec)

It is found that there is also corresponding data.

4.4 Delete the view:

Mysql > drop view people_view;Query OK, 0 rows affected (0.00 sec) so far, I believe you have a deeper understanding of "what is the basic operation of MySQL to manage databases, data tables and data". Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.