Basic CRUD#
Importing the Database#
Code:
Login
mysql -u root -p 123456
Create Database
CREATE DATABASE book7 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Delete Database
DROP DATABASE book7;
Import Database
mysql -u [username] -p [database] < E:\books7.sql
mysql -u root -p book7 < E:\books7.sql
Modifying for Compatibility with Older Versions of MySQL#
This error usually occurs when you are trying to transfer a database that uses the new utf8mb4_0900_ai_ci collation from MySQL 8.0 to an older version of MySQL or MariaDB server.
To resolve this issue, you need to change the collation to one supported by the older version. Follow these steps:
- Open the
E:\books7.sqlfile and search forutf8mb4_0900_ai_ci. - Replace all occurrences of this collation with
utf8mb4_general_ci, which is supported by older versions of MySQL and MariaDB. - Save the modified
E:\books7.sqlfile. - Import the file into the MySQL database again using the following command:
Checking the Import#
Structure of the book Table#
To delete a column from a table, use the ALTER TABLE command and specify the name of the column to be deleted. Here's an example:
ALTER TABLE table_name DROP COLUMN column_name;
In the above command, replace table_name with the name of your table and column_name with the name of the column you want to delete.
ALTER TABLE book DROP COLUMN borrower_id;
ALTER TABLE book DROP COLUMN borrow_time;
Inserting Book Information#
INSERT INTO book(id, name, price, upload_time) VALUES(1, 'F*cking Databases', 999, NOW());
Inserting Multiple Books#
INSERT INTO book(name, price, upload_time)
VALUES
('F*cking Databases', 998, NOW()),
('F*cking Database Homework', 997, NOW()),
('Database Requirements', 996, NOW());
Exporting the Database#
mysqldump -u username -p dbname > backup.sql
mysqldump -u root -p book7 > E:\book8.sql
Exported Content