Home Database Mysql Tutorial Summary of basic knowledge of mysql

Summary of basic knowledge of mysql

Jan 17, 2017 am 10:05 AM
mysql php+mysql database

This article brings you a summary of the basic knowledge of mysql. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

1. Startup and Exit

1. Enter MySQL:

Start MySQL Command Line Client (MySQL DOS interface), directly Just enter the password you used during installation. The prompt at this time is: mysql>

or open the terminal and enter the SQL statement:

mysql –uroot –p123

2. Exit MySQL:

quit or exit

2. Library operations

1. Create database

Command: create database
For example : Create a database named xhkdb
mysql> create database xhkdb;

2. Display all databases

Command: show databases (note: there is an s at the end)
mysql> show databases;

3. Delete database
Command: drop database
For example: delete the database named xhkdb
mysql> drop database xhkdb;

4. Connect to the database
Command: use
For example: if the xhkdb database exists, try to access it:
mysql> use xhkdb;
Screen prompt: Database changed

5. The currently selected (connected) database
mysql> select database();

6. The table information contained in the current database:
mysql> show tables; ( Note: there is an s at the end

3. Table operation, you should connect to a database before the operation
1. Create table
Command: create table

[,.. ]);

mysql> create table MyClass(
> id int(4) not null primary key auto_increment,
> name char(20) not null,
> sex int(4) not null default '0',
> degree double(16,2));
Copy after login

2. Get table structure
command : desc table name, or show columns from table name

mysql> desc MyClass;
mysql> show columns from MyClass;
Copy after login

3. Delete table
Command: drop table


For example: delete the table named MyClass
mysql> drop table MyClass;

4. Insert data
Command: insert into

[( [,..For example, insert two records into the table MyClass. These two records represent: the score of Tom named No. 1 is 96.45, and the score of Tom No. 2 is 96.45. The score of Joan, numbered 3, is 82.99, and the score of Wang, numbered 3, is 96.5.

mysql> insert into MyClass values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59);
Copy after login

5. Query the data in the table
1), Query all rows
command : select from < table name> where < expression>
For example: view all data in table MyClass
mysql> select * from MyClass;
2), Query the first few rows of data
For example: View the first 2 rows of data in the table MyClass
mysql> select * from MyClass order by id limit 0,2;

6. Delete Data in the table
Command: delete from table name where expression
For example: delete the record numbered 1 in the table MyClass
mysql> delete from MyClass where id=1;

7, Modify the data in the table:
update table name set field = new value,... where condition
mysql> update MyClass set name='Mary' where id=1;

8. Add to the table Field:
Command: alter table table name add field type other;
For example: a field passtest is added to the table MyClass, the type is int(4), the default value is 0
mysql> alter table MyClass add passtest int(4) default '0'

9. Change the table name:
Command: rename table original table name to new table name;
For example: change the name of table MyClass to YouClass
mysql> rename table MyClass to YouClass;
Update field content
update table name set field name = new content
update table name set field name = replace(field name,'old content','new Content');
Add 4 spaces in front of the article
update article set content=concat(' ',content);

4. Introduction to field types
1 . INT[(M)] type: normal size integer type
2. DOUBLE[(M,D)] [ZEROFILL] type: Normal size (double precision) floating point number type
3. DATE date type: The supported range is 1000-01-01 to 9999-12-31. MySQL displays DATE values ​​in YYYY-MM-DD format, but allows you to use strings or numbers to assign values ​​to DATE columns
4. CHAR(M) type: fixed-length string type. When stored, the right side is always filled with spaces to the specified length
5. BLOB TEXT type, the maximum length is 65535 (2^16-1) characters.
6. VARCHAR type: variable length string type

5. Database backup
1. Export the entire database
mysqldump -u username-p --default-character-set= latin1 database name> Exported file name (the default encoding of the database is latin1)
mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql

2. Export a table
mysqldump -u user name -p database name table name> exported file name
mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql

3. Export a database structure
mysqldump -u wcnc -p -d – add-drop-table smgp_apps_wcnc >d:wcnc_db.sql
-d No data –add-drop-table Add a drop table before each create statement

4. Import database
Commonly used source commands
Enter the mysql database console,
For example, mysql -u root -p
mysql>use database
Then use the source command, the following parameters are Script file (such as the .sql used here)
mysql>source d:wcnc_db.sql

Related recommendations:

mysql basic knowledge ( mysql tutorial for newbies)

Summary of the basic knowledge of mysql

PHP and MySQL basic tutorial (1)

PHP and MySQL Basic Tutorial (2)

PHP and MySQL Basic Tutorial (3)

PHP and MySQL Basic Tutorial ( 4)

mysql manual tutorial: http://www.php.cn/course/37.html

mysql video tutorial: http ://www.php.cn/course/list/51.html

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

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
1 months ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

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)

How to optimize MySQL query performance in PHP? How to optimize MySQL query performance in PHP? Jun 03, 2024 pm 08:11 PM

MySQL query performance can be optimized by building indexes that reduce lookup time from linear complexity to logarithmic complexity. Use PreparedStatements to prevent SQL injection and improve query performance. Limit query results and reduce the amount of data processed by the server. Optimize join queries, including using appropriate join types, creating indexes, and considering using subqueries. Analyze queries to identify bottlenecks; use caching to reduce database load; optimize PHP code to minimize overhead.

How to use MySQL backup and restore in PHP? How to use MySQL backup and restore in PHP? Jun 03, 2024 pm 12:19 PM

Backing up and restoring a MySQL database in PHP can be achieved by following these steps: Back up the database: Use the mysqldump command to dump the database into a SQL file. Restore database: Use the mysql command to restore the database from SQL files.

How to insert data into a MySQL table using PHP? How to insert data into a MySQL table using PHP? Jun 02, 2024 pm 02:26 PM

How to insert data into MySQL table? Connect to the database: Use mysqli to establish a connection to the database. Prepare the SQL query: Write an INSERT statement to specify the columns and values ​​to be inserted. Execute query: Use the query() method to execute the insertion query. If successful, a confirmation message will be output.

How to fix mysql_native_password not loaded errors on MySQL 8.4 How to fix mysql_native_password not loaded errors on MySQL 8.4 Dec 09, 2024 am 11:42 AM

One of the major changes introduced in MySQL 8.4 (the latest LTS release as of 2024) is that the &quot;MySQL Native Password&quot; plugin is no longer enabled by default. Further, MySQL 9.0 removes this plugin completely. This change affects PHP and other app

How to use MySQL stored procedures in PHP? How to use MySQL stored procedures in PHP? Jun 02, 2024 pm 02:13 PM

To use MySQL stored procedures in PHP: Use PDO or the MySQLi extension to connect to a MySQL database. Prepare the statement to call the stored procedure. Execute the stored procedure. Process the result set (if the stored procedure returns results). Close the database connection.

How to create a MySQL table using PHP? How to create a MySQL table using PHP? Jun 04, 2024 pm 01:57 PM

Creating a MySQL table using PHP requires the following steps: Connect to the database. Create the database if it does not exist. Select a database. Create table. Execute the query. Close the connection.

Detailed tutorial on establishing a database connection using MySQLi in PHP Detailed tutorial on establishing a database connection using MySQLi in PHP Jun 04, 2024 pm 01:42 PM

How to use MySQLi to establish a database connection in PHP: Include MySQLi extension (require_once) Create connection function (functionconnect_to_db) Call connection function ($conn=connect_to_db()) Execute query ($result=$conn->query()) Close connection ( $conn->close())

iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos Jul 18, 2024 am 05:48 AM

Apple's latest releases of iOS18, iPadOS18 and macOS Sequoia systems have added an important feature to the Photos application, designed to help users easily recover photos and videos lost or damaged due to various reasons. The new feature introduces an album called "Recovered" in the Tools section of the Photos app that will automatically appear when a user has pictures or videos on their device that are not part of their photo library. The emergence of the "Recovered" album provides a solution for photos and videos lost due to database corruption, the camera application not saving to the photo library correctly, or a third-party application managing the photo library. Users only need a few simple steps

See all articles