“This is my fourth day of participating in the First Challenge 2022. For more details: First Challenge 2022.”

preface

! [insert picture description here] (img – blog. Csdnimg. Cn / 97 c95409997… =700x)

Introduction to related concepts of MySQL

MySQL is a Relational Database (Relational Database Management System). This so-called “Relational” can be understood as the concept of “tables”. A Relational Database consists of one or several tables.

  • Header: The name of each column.
  • Row: A collection of data of the same data type.
  • Line (COL) : Each line describes specific information about a person or thing.
  • Value: Specific information about a row. Each value must be of the same data type as the column.
  • Key: A method used to identify a particular person or object in a table whose value is unique in the current column.

MySQL > Configure MySQL on Windows

For example, download mysql-noinstall-5.1.69-win32.zipOfficial Download page

2.1 MySQL installation and configuration procedure

Unzip mysql-noinstall-5.1.69-win32.zip to the installation location, such as C:\Program Files.

Ini configuration file, rename it to my.ini, open it for editing, add the following code to both [client] and [mysqld] :

Default - character - set = GBK;Copy the code

MYSQL_HOME = MYSQL_HOME = MYSQL_HOME = MYSQL_HOME

C: \ Program Files \ mysql - 5.1.69 - win32Copy the code

Add to the Path variable of the environment variable

; %MYSQL_HOME%\bin;Copy the code

Open the Windows command prompt, go to the DOS command window, go to the bin directory of the MySQL installation directory, and run the following command:

mysqld --install MySQL --defaults-file="my.ini"
Copy the code

If “Service successfully installed.” is displayed, it indicates that the system succeeded.

Note:

  1. Run the following command in the MySQL installation bin directory:Mysqld -- install....
  2. If something goes wrongInstall/Remove of the Service Denied“, solution: Under Windows 7, start search CMD, open cmd.exe program, select “open as administrator” can be.

2.2 Starting, stopping, and uninstalling MySQL services

To start, stop, and uninstall MySQL, run the following code at the Windows command prompt:

  • The startup code is as follows:
net start MySQL
Copy the code
  • The stop code is as follows:
net stop MySQL
Copy the code
  • The uninstallation code is as follows:
sc delete MySQL
Copy the code

MySQL script basic components

Similar to conventional scripting languages, MySQL has a set of rules for the use of characters, words, and special symbols. MySQL performs database operations by executing SQL scripts that consist of one or more MySQL statements (SQL statements + extension statements). The script file name extension is.sql. Under the console, the MySQL client can also execute statements singly without saving them as.sql files.

Identifiers: Identifiers are used to name objects such as databases, tables, columns, variables, and so on for reference elsewhere in the script. The MySQL identifiers naming rules are a bit more verbose. Here we use the universal naming rules: Identifiers consist of letters, numbers, or underscores (_), and the first character must be a letter or underscore.

Identifiers are case-sensitive depending on the current operating system. They are insensitive under Windows, but they are case-sensitive on most Linux/Unix systems.

Keywords: There are many keywords in MySQL, which are not listed here. Keywords have their own meanings and should not be used as identifiers.

Statements: a MySQL statement is the basic unit of a MySQL script. Each statement can perform a specific operation. It is composed of SQL standard statements and MySQL extension statements.

Functions: MySQL functions are used to implement some advanced functions of database operations. These functions are roughly divided into the following categories: string functions, mathematical functions, date and time functions, search functions, encryption functions, and information functions.

Data types in MySQL

MySQL has three broad categories of data types: number, date/time, and string. These three categories are divided into more detailed subtypes.

4.1. Numeric types

  • Integer: tinyInt, SmallInt, mediumInt, int, bigInt
  • Floating point numbers: float, double, real, decimal

4.2. Date and time

Date, time, datetime, timestamp, year

4.3. String type

  • The value can be char or varchar
  • Text: tinytext, text, mediumtext, longtext
  • Binary (used to store pictures, music, etc.) : Tinyblob, BLOb, mediumblob, longblob

5. Use MySQL database

5.1. Log in to MySQL

When the MySQL service is running, you can log in to the MySQL database using the MySQL client tool. First, open the command prompt and enter the following name:

mysql -H the hostname-U a username-p
Copy the code

Grammar description:

-h: This command is used to specify the host name of the MySQL client to log in to. This parameter can be omitted when you log in to the current host. -u: indicates the user name to log in to. -p: tells the server that a password will be used to log in. You can omit this option if the password is empty.

For example, to log in to the MySQL database installed on the local host, enter the following code on the cli and press Enter to confirm:

mysql -u root -p
Copy the code

If the installation is correct and MySQL is running, you will get the following response:

Enter password:
Copy the code

If the password exists, enter the password to log in. If the password does not exist, press Enter to log in. By default, user root does not have a password. After successful login, you will see the following prompt:

Welecome to the MySQL monitor...
Copy the code

Then the command prompt will wait for the command input with mysql> and a blinking cursor. Enter exit or quit to log out.

5.2 create a database

To create a database, use the create DATABASE statement. The basic form of the create command is:

createDatabase Database name [other options];Copy the code

For example, we need to create a database named samp_db as follows:

create database samp_db character set gbk;
Copy the code

Note: To facilitate the display of Chinese at the command prompt, the database character encoding is specified as GBK through character Set GBK at creation time.

The following response is received upon successful creation:

Query OK, 1 row affected(0.02 sec)
Copy the code

Note: MySQL statements start with semicolons (;) At the end of a statement, if you do not add a semicolon to the end of a statement, the command prompt will prompt you to continue typing (there are some exceptions, but semicolons are always valid).

5.3. Select the database to operate

To operate on a database, you must select the database first. Otherwise, the following error will be displayed:

ERROR 1046(3D000): No database selected
Copy the code

5.3.1. Specify the use of the database when logging in to the database

The basic form is as follows:

mysql -D The selected database name-H the hostname-U a username-p
Copy the code

For example, select samp_DB from the database you just created.

mysql -D samp_db -u root -p
Copy the code

5.3.2 Use the use statement after login

The basic form is as follows:

Use database name;Copy the code

Note: The use statement may not include a semicolon.

For example, run the use samp_db command to select the newly created database. After the selection is successful, the following message is displayed:

Database changed
Copy the code

Create a database table

Use the CREATE TABLE statement to create a table. The basic form is as follows:

create tableTable name (column declaration);Copy the code

Select * from students where id, name, sex, age, tel; select * from students where id, name, age, tel;

create table students(
	id int unsigned not null auto_increment primary key,
	name char(8) not null,
	sex char(4) not null,
	age tinyint unsigned not null,
	tel char(13) null default"-");Copy the code

Note: Some of the longer statements are prone to error typing at the command prompt, so we can use any text editor to enter the statement and save it to a.sql file, and execute the script through file redirection at the command prompt.

New students may not understand the above SQL statement, we further analyze as follows:

Create table tablename(columns) is the command to create a database table. The name of the column and the data type of the column are completed in parentheses. Five columns are declared in parentheses. Id, name, sex, age, and TEL are the names of each column, followed by the description of the data type. The column descriptions are separated by commas (,).

Id int unsigned not NULL auto_increment primary key int unsigned not null auto_increment primary key

  • “Id” is the name of the column.
  • “Int” specifies that the type of the column is int(the value ranges from -8388608 to 8388607). We use “unsigned” to indicate that the type is unsigned. The value of the column ranges from 0 to 16777215.
  • “Not null” indicates that the value of this column cannot be null and must be specified. If this attribute is not specified, the value can be null by default.
  • The value of “auto_increment” is used in integer columns. If the column is NULL, MySQL will automatically generate a value with a unique identifier larger than the existing value. There can be only one such value in each table and the column must be an indexed column.
  • “Primary key” indicates that this column is the primary key of the table. The value of this column must be unique. MySQL will automatically index this column.
  • The char(8) below indicates that the stored character length is 8, the tinyint value ranges from -127 to 128, and the default attribute specifies the default value when the column value is null.

5.5. View the created table

Use the show tables command to view the names of tables that have been created.

To check which tables are created by the samp_DB database, run the following code:

show tables;
Copy the code

5.6. View details about the created tables

Use the describe table name command to view details of the tables that have been created.

For example, to view the details of the STUDENTS table, run the following code:

describe students;
Copy the code

5.7. View the created database

You can use the show databases command to view which databases have been created as follows:

show database;
Copy the code

MySQL > create MySQL database

6.1 Insert data into a table

Insert statements can be used to insert one or more rows of data into a database table in the basic form:

insert [into] table name [(column name1And the column name2And the column name3,...). ]values(value1And the value2And the value3,...). ;Copy the code

Note: the contents in [] are optional.

To insert a record into the STUDENTS table in samp_DB, run the following command:

insert into students values(NULL"Wang Gang "," Male ",20, "13811371377");
Copy the code

Press Enter to confirm. If the following code is displayed, data is inserted successfully:

 Query Ok, 1 row affected (0.05 sec)
Copy the code

If the database fails to be inserted, check whether the database you want to operate has been selected.

Sometimes we need to insert only part of the data, or not in the order of the columns, we can use the form like this:

insert into students (name, sex, age) values(" Sun Lihua ", "Female ",21);
Copy the code

6.2 Query the data in the table

6.2.1. Query all data

Select statement is used to obtain data from the database according to certain query rules. The basic form is:

selectColumn namefromTable name [query condition];Copy the code

Select * from students; select * from students; select * from students;

select name, age from students;
Copy the code

The result is as follows:

mysql> select name, age from students;
+--------+-----+
| name   | age |
+--------+-----+
|Wang Gang|  20 |
|Li-hua sun|  21 |
|Yong-heng wang|  23 |
|Kiki tay|  19 |
|Garrel Dr|  22 |
|Zhang Weipeng|  21 |
+--------+-----+
6 rows in set (0.00 sec)
mysql>
Copy the code

We can also use the wildcard * to query all the contents of the table as follows:

select * from students;
Copy the code

6.2.2 Query data according to specific conditions

The WHERE keyword is used to specify query conditions. The basic form is:

selectColumn namefromThe name of the tablewhereConditions;Copy the code

For example, to query information about students whose gender is female, enter the following query statement:

select * from students where sex="Female";Copy the code

The WHERE clause supports not only the “where column name = value “query form, but also general comparison operators such as =, >, <, >=, <,! = and some extension operators are [not] null, in, like, and so on. You can also use A combination of OR and AND for query criteria.

Select * from students where age > 21; select * from students where age > 21;

select * from students where age > 21;
Copy the code

Select * from students where name = ‘king’;

select * from students where name likeKing "% %";Copy the code

Select * from students where id < 5 and age > 20; select * from students where id < 5 and age > 20;

select * from students where id<5 and age>20;
Copy the code

6.3 Update the data in the table

Update statements can be used to modify data in a table in the form of:

The update table namesetColumn name=The new valuewhereUpdate conditions;Copy the code

Select * from students where id = 5 and id = “-“;

update students set tel=default where id=5;
Copy the code

For example, increase the age of all students in the students table by 1.

update students set age=age+1;
Copy the code

For example, change the name of the mobile phone number 13288097888 to “Zhang Weipeng” and the age to 19. The code is as follows:

update students set name="Zhang Weipeng ", age=19 where tel="13288097888";
Copy the code

6.4. Delete data from a table

The delete statement is used to delete data from a table.

delete fromThe name of the tablewhereDelete condition;Copy the code

Select * from students where id = 2;

delete from students where id=2;
Copy the code

Select * from students where age < 21; delete from students where age < 21;

delete from students where age<20;
Copy the code

Delete all data from the STUDENTS table as follows:

delete from students;
Copy the code

7, create table modification

The ALTER TABLE statement is used to modify a table after it is created.

Add a column to a table

The basic form is as follows:

alter tableThe name of the tableaddColumn name column data type [after insertion position];Copy the code

Append address to the end of the STUDENTS table

alter table students add address char(60);
Copy the code

Insert birthday after age; insert birthday after age;

alter table students add birthday date after age;
Copy the code

7.2. Modify a column of a table

The basic form is as follows:

alter tableTable name change column name new column name new data type;Copy the code

Rename table TEL to TELphone;

alter table students change tel telphone char(13) default "-";
Copy the code

Alter TABLE STUDENTS alter table name char(16);

alter table students change name name char(16) not null;
Copy the code

7.3 Delete a column from a table

The basic form is as follows:

alter tableThe name of the tabledropColumn names;Copy the code

Delete the birthday column from the STUDENTS table as

alter table students drop birthday;
Copy the code

Rename a table

The basic form is as follows:

alter tableTable name rename Specifies the name of a new table.Copy the code

Rename the STUDENTS table to workmates as follows:

alter table students rename workmates;
Copy the code

7.5. Delete the entire table

The basic form is as follows:

drop tableThe name of the table.Copy the code

Delete the workmates table as follows:

drop table workmates;
Copy the code

7.6. Delete the entire database

The basic form is as follows:

dropDatabase Specifies the database name.Copy the code

Mysql > alter database samp_db;

drop database samp_db;
Copy the code

Eight, the appendix

8.1. Change the password of user root

By default, the root user does not have a password, and there are many ways to reset the root password.

Mysqladmin: Open the command prompt interface, go to the mysql\bin directory in DOS, and run the following command:

mysqladmin -u root -P password New passwordCopy the code

Enter the old password. If the old password is empty, press Enter to confirm the password change.

8.2. Visual management tools are recommended

Visual management tools MySQL Workbench, Navicat Premium.

conclusion

! [insert picture description here] (img – blog. Csdnimg. Cn/f817face539… =300x)

I am White Deer, a tireless programmer ape. Hope this article can be beneficial to you, welcome everyone’s one key three even! If you have any other questions, suggestions or additions, please leave a comment at the bottom of this article. Thank you for your support!