MySQL series: basic use of MySQL

Time:2020-2-21

Basic operation of database

In MySQL database, for a MySQL example, multiple databases can be included.

After connecting to MySQL, we can view the existing databases through show databases. There are already some libraries, among which information ﹣ u schema, auth, MySQL and performance ﹣ schema are built-in (different versions are slightly different).

MySQL series: basic use of MySQL

How to create your own database? Use the create database database database name; OK.

Create database database name;

Here we have successfully created the MySQL database, and then we show databases; again, you can see that MySQL is already in the list.

MySQL series: basic use of MySQL

After building this database, I don’t think it’s good to get a name. I want to change it to MySQL study, because there is no table in it, so I want to delete and rebuild it (if there is a table, it’s still a little complicated to change the database name, so it’s necessary to be careful when naming it).

Drop database database database name is used to delete database; statement

Drop database database name;

Of course, deleting a library is a dangerous action. In order to avoid running away from deleting a library, two things must be done before deleting a library: one is to confirm that you have no fever, and the other is to confirm that you are right. My library has just been built here, so it’s confirmed.

Then we created the MySQL study database. Now we want to use this database. What should we do?

We need to switch to this database first and use the use database name; statement.

Use database name;

After switching to the corresponding library, we can use show tables; to view the tables in the library. Because the one just created is still hot, there is nothing in it.

MySQL series: basic use of MySQL

Basic operation of table

Let’s create the first table. Use the CREATE TABLE statement, and of course, you need to define the column properties of the table.

Create table table name(
    Column name 1 data type [column's properties],
    Column name 2 data type [column's properties],
    ...
    Column name n data type [column properties]
);

For example, we create the simplest user table, which contains ID of type int and name column of varchar (5).

CREATE TABLE user (
    id         INT,
    name    VARCHAR(5)
);

MySQL series: basic use of MySQL

For the created table, we can use the following command to view the details.

DESC table name;
Description table name;
Explain table name;
Show columns from table name;
Show fields from table name;

MySQL series: basic use of MySQL

For the name of the database, it is not convenient to modify. What about the table? It is easy to change the table name. Use the alter table table table name rename to the new table name; just

Alter table table name rename to new table name;

MySQL series: basic use of MySQL

This changes the table name to user? Info. What if you want to delete the table? Use drop table table table name;

Drop table table name;

MySQL series: basic use of MySQL

Basic operation of columns

After deletion, I silently created the user table back, because I still need to use it.

If we want to add column properties to the user table, what do we do?

Alter table table name add column column column name data type [column properties];

Add a new age column.

MySQL series: basic use of MySQL

If you want to modify a column, you can use the following statement. Modify only supports modifying column properties, while change supports modifying column names and column properties.

#Modify column properties
Alter table table name modify column name new data type [new attribute];
#Modify column name
Alter table name change old column name new column name new data type [new property];

For example, age, I want to change it to user u age, and the length only needs to be defined as 2 bits.

MySQL series: basic use of MySQL

To delete a column, since there are add, modify and change, there should be drop. That’s right.

Alter table table name DROP column name;

MySQL series: basic use of MySQL

Basic operation of data

With a table, you can also operate the basic columns, but what we usually call “add, delete, modify and query” is that there is no data, so we need to have some data.

Using insert to insert data, when a column is not specified, it is inserted in the order of the columns, and the value of the column must contain the full number of columns, while the declaration column is much freer

Insert into table name values (value of column 1, value of column 2,...);
Insert into table name (column 1, column 2,...) values (column 1 value, column 2 value,...);

For example, insert data in the user table.

INSERT INTO user VALUES(1,'zs');
INSERT INTO user(id,name) VALUES(2,'ls');
 INSERT INTO user(name,id) VALUES('ww',3);

MySQL series: basic use of MySQL

As for the query, I’m sure you’ve seen that using the select statement, select * from table name is the most basic query, * means to view all column properties. Generally, the query needs to be filtered with the conditions such as where, and the operation is more complex. Later, we will go further.

Select column 1, column 2,... From table name [where condition];

To modify the data, we use the update statement. The essence of update is to modify the data that meets the conditions, that is, to query out the corresponding data first, and then make changes to it. Therefore, general queries also need to be combined with where statements. Statements without where will change all the data in the table, which should be paid attention to in daily life.

Update table name set column 1 = new value 1 of column, column 2 = new value 2 of column,... [where condition]

MySQL series: basic use of MySQL

As for deletion, use the delete statement, which is also a dangerous statement. Pay attention to take where with you, unless you really want to delete all the data, or you have a fever for a while.

Delete from table name [where condition];

MySQL series: basic use of MySQL

At this point, the basic use of MySQL is almost OK. Of course, there are some complex statements. We will continue to learn later.

Recommended Today

The selector returned by ngrx store createselector performs one-step debugging of fetching logic

Test source code: import { Component } from ‘@angular/core’; import { createSelector } from ‘@ngrx/store’; export interface State { counter1: number; counter2: number; } export const selectCounter1 = (state: State) => state.counter1; export const selectCounter2 = (state: State) => state.counter2; export const selectTotal = createSelector( selectCounter1, selectCounter2, (counter1, counter2) => counter1 + counter2 ); // […]