[MySQL] Database and Table Operations

I. Database operations

1. Creation of databases

Grammar:CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification] ...]

	create_specification:
	[DEFAULT] CHARACTER SET charset_name
	[DEFAULT] COLLATE collation_name

Description:

• Upper case for keywords, mysql is not case sensitive, so you can also use lower case
• [] is optional
• CHARACTER SET: Specifies the character set used by the database.
• COLLATE: Specifies the checksum rules for the database character set

Suppose now we now need to create a database named d1, first let’s view the database, view the database:show databases;

Here’s how to start creating the d1 database:create database d1;

As above, the d1 database is created.

Note: When we create a database without specifying the character set and checksum rules, the system uses the default character set:utf8The calibration rules are:utf8_ general_ ci.

• Creates a file that uses theutf8 d2 database for the character set:create database d2 charset=utf8;

• Create a d3 database using the utf8 character set with proofreading rules:create database d3 charset=utf8 collate utf8_general_ci;

Created well as follows:

As we also said earlier, creating a database is really a matter of creating a database in theLinux Create a directory under it, which will not be repeated here.

2. Character sets and checking rules

When we create a database, there are two code sets:

1. Database Coding Set – – – The set of codes used by the database to store data in the future;
2. Database Checksets – – The encoding used to support databases, for field comparisons, and essentially the encoding format used to read data from a database;

So whatever operation the database does on the data, it must ensure that the operation and the code must be coded the same.

The character set mainly controls what language is used. For exampleutf8 Then you can use Chinese.

(1) View the system’s default character set and checksum rules

show variables like 'character_set_database';  # Default character set
		show variables like 'collation_database';      # Inspection rules

Below:

(2) Check the character set supported by the database

		show charset;

(3) Check the character set checking rules supported by the database

		show collation;

(4) Impact of checking rules on databases

• not distinguishing capitals from lower case letters

Create a database that checks that the rules useutf8_ general_ ci (Case insensitive, i.e., no strict matching during testing, no distinction between upper and lower case letters)

		create database test1 collate utf8_general_ci;

Subsequently we need to use this database:use test1

Then we create a table for this database and insert some data, the syntax for creating the table and inserting it we will not introduce later; as follows:

Next, we look at the insertion results for this table. Note that the table’s checksum method is not case-sensitive for matching; so we look at the whole table first:select * from for_test;

Next we sift through thea This character:select * from for_test where name='a';

We can see that the database is matchinga This character is not case sensitive and is shown to us in both upper and lower case.

• distinguishing capitals from lower case letters

Create a database that checks that the rules useutf8_ bin (case-sensitive, checksums are case-sensitive according to strict matching)

We created it as above, as shown below:

Next we look at the data in that table:

Next we sift through thea Characters:

As above, since the test rule for this database isutf8_ binThe case-sensitive approach is used for strict matching, so the filtered charactera It’s a character.a.

3. Manipulating the database

(1) Viewing the database

		show databases;

(2) Show created statements

show create database Indicates the database name.

Example:

Description:

• MySQL recommends that we use capitalization for keywords, but it is not required;
• The database name is back-quoted “ to prevent the use of a database name that happens to be a keyword.
• / * !40100 default…* / This is not a comment, it means that if the current mysql version is greater than version 4.01, this sentence is executed;

(3) Modification of the database

Grammar:

		ALTER DATABASE db_name
		[alter_spacification [,alter_spacification]...]

alter_spacification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name

Description: Modification of the database mainly refers to the modification of the database character set, checksum rules.

Let’s assume that thetest1 The character set of the database is changed togbk：alter database test1 charset=gbk;

as shown abovetest1 The character set of the database is then changed togbk.

4. Database deletion

Grammar:

		DROP DATABASE [IF EXISTS] db_ name;

For example, let’s delete thetest2 Database:drop database test2;

Below:

As shown above.test2 The database is then deleted.

The result after performing the deletion:

• The corresponding database is not visible within the database
• The corresponding database folder is deleted, cascading deletion, and all the data tables inside are deleted.

5. Backup and recovery

(1) Backup database

Before backing up the database we first need to exit themysql.

Grammar:

mysqldump -P3306 -u root -p password -B database name > file path to database backup storage

Where the password part we can leave out of the command line, the command line will prompt us for it when we execute this command.

For example, let’s puttest1 The library is backed up to a file:mysqldump -P3306 -uroot -p -B test1 > /home/lmy/test1.sql

At this point, you can open to see the contents of the test1.sql file, in fact, the entire creation of our database, build tables, import data statements are loaded into this file.

Next we go tomysql in it, delete this database:

As shown above.test1 The library is then deleted by us, and we restore it next.

(2) Reduction

Grammar:

source Path to the database backup file.

We are inmysql Enter the command in thesource /home/lmy/test1.sql; can be found in themysql recovery processtest1 Coop:

(3) Expansion

What if the backup is not of the entire database, but of one of the tables? The practice is as follows:

mysqldump -uroot -p database name table name 1 table name 2 > path to backup file

If multiple databases are backed up at the same time. as follows:

mysqldump -uroot -p -B database name 1 database name 2 ... > Database storage path

If a database is backed up without the-B parameter, when restoring the database, you need to create an empty database first, then use the database, then use thesource to restore.

6. Viewing connections

Viewing connections tells us which users are currently connected to ourMySQLIf you find out that a user is not our normal login, it is very likely that our database has been invaded. In the future, when you find your database slower, you can use this command to check the database connection.

Grammar:

		show processlist;

Example:

II. Table operations

1. Creation of tables

Grammar:

CREATE TABLE table_name (
			field1 datatype,
			field2 datatype,
			field3 datatype
		) character set character set collate checksum rule engine storage engine.

The database needs to be specified before the table can be created, i.e. used:use database. Create tables for this database.

Note that we will not cover the database data types for now, but will do so later.

Description:

• field Indicates the column name
• datatype Indicates the type of column
• character set, if no character set is specified, the character set of the database will prevail.
• collate Checksum rule, if no checksum rule is specified, the checksum rule of the database will prevail.

For example, let’s create ausers table, which stores the user’sid、user ID、cryptographic、birthdays：

create table users(
		    -> id int,
		    -> name varchar(20) comment 'User name ',
		    -> password char(20) comment 'Password ',
		    -> birther date comment 'Birthday'
		    -> ) character set utf8 engine MyISAM;

Note: Different storage engines have different files for creating tables.users The table storage engine isMyISAM , there are three different files in the database directory that we can go into and look at:cd /var/lib/mysql/d1, respectively:

where they are denoted respectively:

• users.frm: table structure
• users.MYD: Table data
• users.MYI: table indexes

(indicates contrast)db.opt is the corresponding character set and checking rule for that database.

2. Viewing table

Above we have created ausers table, at which point we can see what tables are available in that database:show tables;

3. Viewing the table structure

Grammar:desc indicates.

For example, check out theusers The structure of the table:

The above is the table in the structure of the introduction, we will later describe the function of each column in detail.

4. Modification table

In the actual development of the project, often modify the structure of a table, such as field name, field size, field type, table character set type, table storage engine and so on. We also have the need to add fields, delete fields, etc.; then we need to modify the table.

ALTER TABLE tablename ADD (column datatype [DEFAULT expr][,column
		datatype]...) ; # Add
		
		ALTER TABLE tablename MODIfy (column datatype [DEFAULT expr][,column
		datatype]...) ; # Modify
		
		ALTER TABLE tablename DROP (column);  # Delete

Example:

beforehandusers table to add two records:

	mysql> insert into users values(1, 'a', 'b', '2000-01-01'), 
	    -> (2, 'c', 'd', '2000-01-02');

• Add a field to the users table to hold the image path:alter table users add assets varchar(100) comment 'image path' after birther;

After inserting the new field, we look at the data in the original table and there is no effect on the data in the original table:

• Modify name to change its length to 60: alter table users modify name varchar(60);

• Delete the password column: alter table users drop password;

We look at the data in the table again and see thatpassword All the data in this column is missing:

So be careful about deleting fields, the deleted field and its corresponding column data are gone.

• Modify the table name to employee: alter table users rename to employee; whichto Can be omitted

• Change the name column to xingming: alter table employee change name xingming varchar(60); The new field needs to be fully defined

5. Deletion of tables

Grammar:DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...

Example:

Check again: