2. Basic Use and Management of MySQL databases; 2. mysql Database Management

2. Basic Use and Management of MySQL databases; 2. mysql Database Management
1. mysql Data Storage Structure

1.1 composed of "Database" -- "table" -- "data"

1.2 SQL is required for database management (Structured Query Language)

The SQL language is divided:

1. Data Query Language DQL

Searches existing database data by specified combination, condition expression, or sort without changing the database data.

Command: SELECT... FROM... WHERE...

2. Data manipulation language DML

Inserts, deletes, and modifies existing databases.

Command: INSERT, UPDATE, DELETE

3. Data Definition Language DDL

Creates, modifies, or deletes various objects in the database, including tables, views, and indexes.

Command: create table, create view, create index, alter table,
Drop table, drop view, DROP INDEX

4. Data Control Language DCL

It is used to grant or revoke some privileges to access the database, control the time and Effect of data operation transactions, and monitor the database.

Command: GRANT, REVOKE, COMMIT, ROLLBACK

2. query all databases

Log on to the database through the command line and enter the first SQL statement to view the database

Mysql> show databases; -- four databases are displayed by default.

+ -------------------- +
| Database |
+ -------------------- +
| Information_schema | -- mysql metadata, basic data
| Mysql | -- mysql configuration database, which contains user information (user name and password, permission management)
| Performance_schema | -- mysql database software running data, log information, and performance data
| Test | -- test Database, empty Database
+ -------------------- +

3. Create a database

3.1 The SQL statement is: create database name;

Mysql> create database first; -- first is the database name

Query OK, 1 row affected (0.01 sec)

　　　　3.2 set database characters when creating a database

Mysql> create database two -- you do not need to enter a semicolon at this time, because the semicolon indicates that the statement is over and press Enter.

-> Default character set utf8; -- set the default character to UTF-8.
Query OK, 1 row affected (0.00 sec)

　　3.3 view the default Character Set of the database

　　　　Mysql> show create database first; -- first is the database name

+ ---------- + --------------------------------------------------------------- +
| Database | Create Database |
+ ---------- + --------------------------------------------------------------- +
| First | create database 'first '/*! 40100 DEFAULT CHARACTER SETGbk*/|
+ ---------- + --------------------------------------------------------------- +
1 row in set (0.00 sec)

　　　　3.4 delete a database

Mysql> drop database two; -- two is the database name

Query OK, 0 rows affected (0.17 sec)

3.5 modify the default Character Set of the database

Mysql> alter database first default character set utf8; -- modify the character of the first database to UTF-8

Query OK, 1 row affected (0.00 sec)

4. Table Management

4.1 select database

Mysql> use first; -- first indicates the database name.
Database changed

4.2 create a table

Mysql> create table student (-- student is the table name
-> Sid int, -- sid is the field name number, and int Is the field type Integer
-> Sname varchar (20), -- sname is the field name, And varchar (20) is the field type. the string length is 20.
-> Sage int -- sage indicates the age of the field name, int indicates the field type, and integer indicates the age of the field name.
-> );
Query OK, 0 rows affected (0.14 sec)

4.3 view all tables

　　　　Mysql> show tables;

+ ----------------- +
| Tables_in_first |
+ ----------------- +
| Student |
+ ----------------- +
1 row in set (0.00 sec)

　　　　　4.4 view the table structure of a table

Mysql> desc student; -- student table name

+ ------- + ------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + ------------- + ------ + ----- + --------- + ------- +
| Sid | int (11) | YES | NULL |
| Sname | varchar (20) | YES | NULL |
| Sage | int (11) | YES | NULL |
+ ------- + ------------- + ------ + ----- + --------- + ------- +
3 rows in set (0.05 sec)

4.5 delete a table

Mysql> drop table student;

Query OK, 0 rows affected (0.11 sec)

4.6 modify a table

Mysql> alter table student add column sgender varchar (2); -- add an sgender field to the student table. column can be omitted.

Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

　　　　

Mysql> alter table student drop sgender; -- delete the sgender field in the student table. column can be omitted.

Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

　　　　　　

Mysql> alter table student modify sname varchar (10); -- modify the sname field type in the student table to varchar (10)
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0

　　　　　　

Mysql> alter table student change sname newsname varchar (20); -- modify the name of the sname field in the student table to newsname
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0