Basic use and management of 2.MySQL databases

1.mysql Data storage structure

　1.1 Consists of "database"-"table"-"Data"

1.2 Managing databases requires SQL (Structured Query language)

The SQL language is divided into:

1 Data Query Language DQL

retrieves data from an existing database by the specified combination, conditional expression, or sort . does not change the data in the database.

Command: SELECT ... From ... WHERE ...

2 Data Manipulation Language DML

Insert, delete, modify, and manipulate tuples in existing databases

Commands: INSERT, UPDATE, DELETE

3 Data Definition Language DDL

Create, modify, or delete various objects in the database, including tables, views, indexes, and so on.

Command: Create TABLE, create VIEW, create INDEX, ALTER table,
Drop TABLE, Drop VIEW, drop INDEX

4 Data Control Language DCL

　　　　　　　used to grant or reclaim access to a database for some kind of privilege, control the time and effect of data manipulation transactions and monitor the database

Commands: GRANT, REVOKE, COMMIT, ROLLBACK

2. Querying all databases

Log in to the database from the command line and enter the first SQL statement to view the database

mysql> show databases; --4 databases displayed by default

+--------------------+
| Database |
+--------------------+
|　　Information_schema | --mysql meta data, underlying data
|　　　　　　　　　　MySQL | --mysql configuration database, which contains user information (user name and password, rights Management)
|　　Performance_schema | --mysql database software operation data, log information, performance data
|　　　　　　　　　　　Test | --Test database, empty database
+--------------------+

3, create a database

　　　　The 3.1 SQL statement is: CREATE database name;

mysql> CREATE database first; --first the database name

Query OK, 1 row affected (0.01 sec)

　　　　3.2 Setting database characters when creating a database

Mysql> CREATE DATABASE Two-you do not need to enter a semicolon at this point, because the semicolon means that the statement ends and the direct return

--default character set UTF8; --Set default character to Utf-8
Query OK, 1 row Affected (0.00 sec)

　　3.3 Viewing the default character set for a database

　　　　Mysql> show CREATE database first; --first the database name

+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| First | CREATE DATABASE ' first '/*!40100 DEFAULT CHARACTER SET gbk */|
+----------+---------------------------------------------------------------+
1 row in Set (0.00 sec)

　　　　3.4 Deleting a database

mysql> drop database; --two the database name

Query OK, 0 rows affected (0.17 sec)

　　　　3.5 Modifying the default character set for a database

mysql> ALTER DATABASE First default character set UTF8; --Modify the first database character to Utf-8

Query OK, 1 row Affected (0.00 sec)

4. Table Management

　　　　4.1 Selecting a Database　　

mysql> use first; --first the database name
Database changed

　　　　4.2 Creating a Table

Mysql> CREATE TABLE student (--student name
-Sid int,--sid is the field name number, int is the field type shaping
Sname varchar,--sname is the field name name, varchar (20) is the field type string length is 20
-Sage int--sage is the field name age, int is the field type, shaping
);
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 Viewing 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 Deleting a table

　　　　mysql> drop table student;

Query OK, 0 rows affected (0.11 sec)

4.6 Modifying a table

　　　　Mysql> ALTER TABLE student add column Sgender varchar (2); --Add a sgender field to the student table, and column can omit

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, and column can omit

Query OK, 0 rows affected (0.06 sec)
records:0 duplicates:0 warnings:0

　　　　　　

mysql> ALTER TABLE student modify sname varchar (10); --Modify the type of the Sname field 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 and change to Newsname
Query OK, 0 rows affected (0.08 sec)
records:0 duplicates:0 warnings:0

Basic use and management of 2.MySQL databases