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
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 databaseMysql> 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
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 databasemysql> 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)
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)
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
mysql> use first; --first is the database Name
Database changed
sid is the field name number, int is the field type integer
-> 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)
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: 0mysql> 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)
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!