Basic use and management of MySQL database

1.mysql data storage structure

　 1.1 Composed of "database" - "table" - "data"

1.2 Management The database needs to use SQL (Structured Query Language)

SQL language is divided into:

1 Data Query Language DQL

Retrieve existing data in the database according to the specified combination, conditional expression or sorting, Does not change the data in the database.

Command: SELECT…FROM…WHERE…

2 Data Manipulation Language DML

Right Perform operations such as inserting, deleting, and modifying tuples in an existing database

Commands: INSERT, UPDATE, DELETE

3 Data Definition Language DDL　

　　　　 Create, modify or delete various objects in the database, including tables, views, indexes, etc.

command: Create Table, Create View, Create Index, Alter Table,
Drop Table, Drop View, Drop Index

# 4 Data control language DCL

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　

is used to grant or revoke certain privileges to access the database,control the occurrence time and effect of data manipulation transactions, Monitor the database

Commands: GRANT, REVOKE, COMMIT, ROLLBACK2. Query all Database

Log in 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
　　　　| -Operating data, log information, performance data of mysql database software
　　　　| +

3, create database

　　

3.1

sql statement is: create database Database name;

　mysql> create database first ; --first is the database nameQuery OK, 1 row affected (0.01 sec)

3.2 Set the database characters when creating the database

　　　mysql> create database two　　　--There is no need to enter a semicolon at this time, because the semicolon means the end of the statement, just press Enter　　　　-> default character set utf8;　　--Set the default character For utf-8

　Query OK, 1 row affected (0.00 sec)

　

　3.3 Check 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 SET

gbk
*/ |
　　 +----------+-------------------- --------------------------------------------------+
　　1 row in set (0.00 sec)
　　　

3.4 Delete 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; OK, 1 row affected (0.00 sec)　

4.Table management

　　

4.1 Select database

　　　　mysql> use first;　--first is the database Name

Database changed

sid is the field name number, int is the field type integer

​​ ​ ​ ​ -> sname varchar(20), --sname is the field name, varchar(20) is the field type, the string length is 20

​ ​ ​ ​ ​ ​ -> sage int　　　　　　--sage is the field name age, int is the field type, integer 　　　　--> );　　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 table

　　
mysql> drop table student;

　　Query OK, 0 rows affected (0.11 sec)

　　 4.6 Modify table

　　

mysql> ; alter table student add column sgender varchar(2); --Add a 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 type of the sname field in the student table for 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

The above is the detailed content of Basic use and management of MySQL database. For more information, please follow other related articles on the PHP Chinese website!