Home > Database > Mysql Tutorial > body text

Basic use and management of MySQL database

巴扎黑
Release: 2017-06-23 14:00:24
Original
1114 people have browsed it

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!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template