This is the 11th day of my participation in Gwen Challenge

Author: Cola

Source: Coke’s path to data analysis

Please contact authorization for reprinting (wechat ID: data_COLA)

One, database creation, view and delete

01 Creating a database

As mentioned in the previous section, we can think of an Excel workbook as a database, and the sheets in the workbook are just tables. How do we create a workbook in Excel? To create a database named test, use the create command:

Create database Table name;Copy the code

If OK is displayed, the command is successfully executed.

02 Checking the database

When we want to view an Excel worksheet, we just double click to open it. In the database, we use the show command

show databases; Mysql > select * from database; Show tables; View all tables in the selected databaseCopy the code

Because we just created the test library, there is no table in it, it is empty.

03 Deleting a Database

Delete a workbook from Excel by right clicking. In the database we use the drop command.

Drop database Specifies the database name. Delete a databaseCopy the code

The message “OK” is displayed.

Create, view, modify and delete tables

01 Table establishment

In Excel, we create a table named student, and we need to determine the column name and numeric type. For example, we create 5 columns of data in this table. Snum is the student id, and only numeric data can be filled in. Ssex is the gender of the student, which is also character-only data.

In the database, we use the create Table command to create a table. The idea is the same as in Excel: first determine the table name, then determine the column name and column data type, and separate the columns with commas.

Create table name (column name 1 column 1 data type, column name 2 column 2 data type,... Column name n column n data type);Copy the code

Data types have to be mentioned here. In Excel, you have numeric and character data, and the special date-time data is actually numeric. In a database, the data types are pretty much the same: numeric, character, and date-time. In the above example, int is numeric, vARCHar is character, and datetime is datetime.

Numeric data can only store numeric data such as integers and decimals, and can be divided into integer, floating point, and digit types.

Character types store string data,

Date-time data stores date and time format values.

Don’t worry too much about these numeric types

02 table view

Create table student (desc)

Desc table name; # see tableCopy the code

You can see the four column fields we created and their data types. Note that this is just creating a table, not populating it with row records.

03 Table modification

The alter command is used to modify a table.

1) Change the table name

Alter table table_name rename table_name;Copy the code

2) Change the column field name

Alter table alter table name change old column name new column name data type;Copy the code

3) Add column fields

Alter table name add column name data type;Copy the code

Add an saddress column.

4) Drop the column field

Alter table table_name drop column;Copy the code

Drop table 04

Drop table table name;Copy the code

Insert row records

Using the insert command

Insert into values(1, 2, 3, 4);Copy the code

The sequence of the Values after Values must be the same as that of the table fields. The character Values must be quoted (‘ ‘). Numeric Values can be entered directly and are separated by commas (,). Insert a record

[Insert multiple records] : separated by commas

Let’s query the record we just inserted.

We see that we already have data in this table.