Table of Contents
1. Library operation
2. Table operations
Create table
View table structure
Delete table
Modify table
Home Database Mysql Tutorial What are the basic operations of MySQL?

What are the basic operations of MySQL?

May 27, 2023 am 11:19 AM
mysql

1. Library operation

Check which libraries are available by default under the current mysql database

show databases;
Copy after login

Create a library create database database_name;

create database test11;
Copy after login

Database_name naming convention for the library name :

(1) It consists of letters, numbers, underscores, @, # and ¥

(2) The first letter cannot be a number and $

(3) It cannot be Keywords of mysql database

(4) Spaces and special characters are not allowed

(5) The length is less than 128 bits

Use a library

use database_name;
Copy after login

Check which library is currently used/connected

elect database();
Copy after login

View all tables under the current library

 show tables;
Copy after login

Delete a library

drop database database_name;
Copy after login

2. Table operations

Table is a very important object in the MySQL database and is the basic element of the database. The table is composed of rows and columns and is mainly used to store data.

Example: Store data in the form of a table

Student number, name, age, grade

1 Zhang San 30 100

2 Li Si 25 90

Create table

Syntax:

create table table_name(
            列名1 数据类型,
            列名2 数据类型,
            列名3 数据类型,
            列名4 数据类型,
            ....
            列名n 数据类型
         );
Copy after login
create table mytest(
      id int,
      name varchar(20),
      age int,
      birthday date
   );
  select * from mytest;
Copy after login

Data type:

Integer int (number of digits)

Decimal type/floating point type double

String type varchar (number of digits) variable length

char (number of digits) fixed length

varchar(5) Zhang San

char(5 ) Zhang San xxx

"Zhang San"

Date type date

Create a company library company

create database company;
Copy after login

Use company library

use company;
Copy after login

Create a department table t_dept department number, department name, address

create table t_dept(
          deptno int,
          dname varchar(20),
          loc varchar(20)
       );
Copy after login

View table structure

View table structure

desc table_name;
Copy after login

View table detailed definition

 show create table table_name;
Copy after login
desc t_dept;
show create table t_dept;
show create table t_dept\G;
Copy after login
   (\G代替;能够让显示结果更美观)
Copy after login

Delete table

Delete a table

drop table table_name;
Copy after login
drop table t_dept;`
Copy after login

Modify table

①Modify table name

Syntax:

alter table old_table_name rename [to] new_table_name;

to can be omitted or not written

Example: change the table name t_dept to tab_dept

alter table t_dept rename to tab_dept;
Copy after login

will Table tab_dept is modified to dept

alter table tab_dept rename dept;
Copy after login

②Add fields to the table

By default, fields are added to the last column of the table

Syntax: alter table table_name add column name data type;

Example: Add a column to the dept table. The column name is descri, indicating description. The string type

alter table dept add descri char(10);
Copy after login

is added to the first column of the table. Field

Syntax:alter table table_name add column name data type first;

Example: Add id to the first column of the dept table, which is an integer type

alter table dept add id int first;
Copy after login

Add a new field after the specified field of the table

Syntax:alter table table_name add new column name data type after specified column name;

Example: in dept Add the age column after the deptno column in the table, indicating age, which is an integer type

alter table dept add age int(3) after deptno;
Copy after login

③Delete field

Syntax: alter table table_name drop column name;

Example: Delete the age column in the dept table

alter table dept drop age;
Copy after login

④Modify the field

1. Modify the data type of the field

Syntax: alter table table_name modify column name data type;

[Explanation: The column name is the column to be modified, and the data type is the modified data type]

Example: Change the data type of the loc column in the dept table to varchar(50).

alter table dept modify loc varchar(50);
Copy after login

2. Modify the name of the field

Syntax: alter table table_name change old column name new column name old column data type;

Example: Change the descri column name in the dept table to the description column name.

alter table dept change descri description char(10);
Copy after login

3. Modify the name and data type of the field at the same time

Syntax: alter table table_name change old column name, new column name, new column data type;

Example: Change the id name in the dept table to num, and change the data type from int to double.

 alter table dept change id num double;
Copy after login

⑤Modify the order of fields

Syntax 1:alter table table_name modify column name data type first;

[ Description: Adjust a column to the first column]

Example: Adjust the loc column in the dept table to the first column

alter table dept modify loc varchar(50) first;
Copy after login

Syntax 2:alter table table_name modify column 1 Column 1 data type after column 2;

[Description: Adjust column 1 to after column 2]

Example: Adjust the num column in the dept table to after dname

alter table dept modify num double after dname;
Copy after login

The above is the detailed content of What are the basic operations of MySQL?. For more information, please follow other related articles on the PHP Chinese website!

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

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Java Tutorial
1664
14
PHP Tutorial
1268
29
C# Tutorial
1243
24
MySQL's Role: Databases in Web Applications MySQL's Role: Databases in Web Applications Apr 17, 2025 am 12:23 AM

The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.

Laravel Introduction Example Laravel Introduction Example Apr 18, 2025 pm 12:45 PM

Laravel is a PHP framework for easy building of web applications. It provides a range of powerful features including: Installation: Install the Laravel CLI globally with Composer and create applications in the project directory. Routing: Define the relationship between the URL and the handler in routes/web.php. View: Create a view in resources/views to render the application's interface. Database Integration: Provides out-of-the-box integration with databases such as MySQL and uses migration to create and modify tables. Model and Controller: The model represents the database entity and the controller processes HTTP requests.

MySQL and phpMyAdmin: Core Features and Functions MySQL and phpMyAdmin: Core Features and Functions Apr 22, 2025 am 12:12 AM

MySQL and phpMyAdmin are powerful database management tools. 1) MySQL is used to create databases and tables, and to execute DML and SQL queries. 2) phpMyAdmin provides an intuitive interface for database management, table structure management, data operations and user permission management.

MySQL vs. Other Programming Languages: A Comparison MySQL vs. Other Programming Languages: A Comparison Apr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages ​​such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages ​​have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Solve database connection problem: a practical case of using minii/db library Solve database connection problem: a practical case of using minii/db library Apr 18, 2025 am 07:09 AM

I encountered a tricky problem when developing a small application: the need to quickly integrate a lightweight database operation library. After trying multiple libraries, I found that they either have too much functionality or are not very compatible. Eventually, I found minii/db, a simplified version based on Yii2 that solved my problem perfectly.

Laravel framework installation method Laravel framework installation method Apr 18, 2025 pm 12:54 PM

Article summary: This article provides detailed step-by-step instructions to guide readers on how to easily install the Laravel framework. Laravel is a powerful PHP framework that speeds up the development process of web applications. This tutorial covers the installation process from system requirements to configuring databases and setting up routing. By following these steps, readers can quickly and efficiently lay a solid foundation for their Laravel project.

MySQL for Beginners: Getting Started with Database Management MySQL for Beginners: Getting Started with Database Management Apr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

Solve MySQL mode problem: The experience of using the TheliaMySQLModesChecker module Solve MySQL mode problem: The experience of using the TheliaMySQLModesChecker module Apr 18, 2025 am 08:42 AM

When developing an e-commerce website using Thelia, I encountered a tricky problem: MySQL mode is not set properly, causing some features to not function properly. After some exploration, I found a module called TheliaMySQLModesChecker, which is able to automatically fix the MySQL pattern required by Thelia, completely solving my troubles.

See all articles