In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
MySQL software installation and database foundation, for your reference, the details are as follows
I. Task
Task one
MySQL Software installation and Database Foundation
Task time
Please finish it by 22:00 on February 26th and sign in in the comments in this article. Those who have not clocked in within the time limit will be refunded.
Learning content
1. Software installation and server setup
Tutorial MySQL installation | Rookie tutorial
two。 (optional, but strongly recommended) use the graphical interface software Navicat for SQL
Planet provides Navicat for SQL
Easy steps:
Extract the file and copy the key
Open the navicat.exe in the folder
User name is optional, enter key, and then connect to the database
Enter the password and change the connection name to what you like.
The rest of you explore how to create databases, tables, etc., in navicat.
3. Basic knowledge of database
Database definition
Relational database
Two-dimensional table
OK
Column
Primary key
Foreign key
4.MySQL database management system
Database
Data sheet
View
Stored procedure
Software installation and server setup
System environment: win10 home version
2.1 MySQL installation
2.1.1 download the Windows version of the MySQL installation package on the MySQL official website
Address: MySQL download
Click to download Download and the following interface will pop up. Click No thanks, just start my download
2.1.2 after downloading, extract the zip package to the directory you want to install. My path is as follows:
2.1.3 configure environment variables:
Enter the computer-Properties-Advanced system Settings-Environment variable and add the path to the MySQL decompression.
2.1.4 next configure the configuration file for MySQL
Open the folder D:\ software\ Mysql\ mysql-8.0.15-winx64\ mysql-8.0.15-winx64 that you just extracted, create a configuration file as my.ini, and edit the following basic information about my.ini configuration:
[mysql] default-character-set= UTF8 [mysqld] port = 3306basedir=D:\ software\ Mysql\ mysql-8.0.15-winx64\ mysql-8.0.15-winx64datadir=D:\ software\ Mysql\ mysql-8.0.15-winx64\ mysql-8.0.15-winx64\ datacharacter-set-server=utf8default-storage-engine=INNODB
1. Next, let's start the MySQL database:
Open the cmd command line tool as an administrator and change directories:
D:cd D:\ software\ Mysql\ mysql-8.0.15-winx64\ mysql-8.0.15-winx64\ bin
Enter the initialization command:
Mysqld-initialize-insecure-user=mysql
Generate the data directory under the D:\ software\ Mysql\ mysql-8.0.15-winx64\ mysql-8.0.15-winx64\ bin directory
Start and enter the following command:
Net start mysql
This proves that the connection has been successful.
2.2 MySQL reset password
2.2.1 Log in to MySQL
Enter:
Mysql-u root-p
Because the password has not been set before, the password is empty, so you can enter directly:
2.2.2 query user password
Query user password command:
Mysql > select host,user,authentication_string from mysql.user
Host: ip that allows users to log in
User: the user name of the current database
Authentication_string: user password
If there is no password, the root line should be empty.
2.2.3 set (or modify) the root user password:
Note: the password field and the password () function have been discarded after MySQL 5.7.9
Do not set the password in the following form:
Use mysql; update user set authentication_string= "newpassword" where user= "root"
This sets the newpassword value under the authentication_string field of the root user in the user table
The steps to correctly change the root password are:
1. If there is something under the authentication_string field of the current root user, set it to empty first, and skip to step 2 if not.
Use mysql; update user set authentication_string='' where user='root'
two。 Use ALTER to change the root user password by:
Use mysql;ALTER user 'root'@'localhost' IDENTIFIED BY' New password; FLUSH PRIVILEGES
At this point, the local MySQL created by yourself has been created!
Just reconnect!
Third, use the graphical interface software Navicat for SQL
Navicat for SQL installation package has been saved to Baidu network disk: navicat+for+mysql10.0.11 simplified Chinese
1. Extract the file and copy the key
two。 Open the navicat.exe in the folder
3. User name is optional, enter key, and then connect to the database
4. Enter the password (if MySQL has reset the password before, just fill in the newly set password here), and change the connection name to what you like.
Problem: the connection failed here, as shown in the figure
Solution:
The problem here is actually the ALTER user 'root'@'localhost' IDENTIFIED BY' new password that MySQL used to set the password; for strong encryption, the connection will fail; if you use the second encryption method, ALTER user 'root'@'localhost' IDENTIFIED WITH mysql_native_password by' new password, you can connect directly.
Successful connection!
At this point, the installation of the required software and the configuration of the environment are all completed, and you can happily roam the world of MySQL.
IV. Basic knowledge of database
4.1 Database (database) definition
In the case of the book SQL must know (4th Edition), from the perspective of SQL, a database is a collection of data stored in some organized way, a container for organized data (usually a file or set of files).
Note: people often use the term database to refer to the database software they use, which is incorrect and leads to a lot of confusion. To be exact, database software should be called database management system (DBMS). A database is a container created and manipulated through DBMS, and exactly what it is and what its form is, all kinds of databases are different.
4.2 Relational database
The most typical data structure of relational database is table, which is a data organization composed of two-dimensional tables and their relationships.
Advantages:
1. Easy to maintain: all use table structure and consistent format
two。 Easy to use: SQL language is universal and can be used for complex queries
3. Complex operations: support for SQL, which can be used for very complex queries between one table and multiple tables.
Disadvantages:
4. The reading and writing performance is poor, especially the efficient reading and writing of massive data.
5. Fixed watch structure, slightly less flexible
6. High concurrent read and write requirements, for traditional relational databases, the hard disk Ipicuro is a big bottleneck.
4.3 two-dimensional table
A table is a structured file that can be used to store a particular type of data. Tables can hold customer lists, product catalogs, or other lists of information. A table is a structured list of a particular type of data.
The data stored in the table is the same type of data or list. The customer list and the order list should never be stored in the same database table, otherwise future retrieval and access will be difficult. You should create two tables, one for each list.
Each table in the database has a name to identify itself. This name is unique, that is, no other table in the database has the same name. Although you cannot use the same table name twice in the same database, you can use the same table name in different databases.
A schema can be used to describe a specific table in a database, or to describe the entire database (the relationship with the tables in it). A schema is information about the layout and characteristics of databases and tables.
4.4 Lin
The data in the table is stored in rows, and each saved record is stored in its own row.
You may hear users refer to rows as database records (record). Most of these two terms can be used interchangeably, but technically, line is the correct term.
4.5 columns
A table consists of columns. The column stores information about a part of the table. A column is a field in a table. All tables consist of one or more columns.
Each column in the database has a corresponding data type. The data type (datatype) defines which types of data the column can store. The data type defines the type of data that can be stored in the column (for example, to prevent character values from being entered in numeric fields).
Data types and their names are one of the main reasons for SQL incompatibility.
4.6 Primary key
Each row in the table should have one column (or several columns) that can uniquely identify itself. The customer number can be used for the customer table, and the order ID can be used for the order table. Employee tables can use employee ID or employee social security number.
A primary key (primary key) is a column (or set of columns) whose values uniquely identify each row in the table. Without a primary key, it is extremely difficult to update or delete specific rows in the table because you cannot guarantee that only the relevant rows are involved in the operation.
Tip: you should always define the primary key
Although primary keys are not always required, most database designers ensure that each table they create has a primary key for future data manipulation and management.
Any column in the table can be used as a primary key, as long as it meets the following conditions:
No two rows have the same primary key value; each row must have a primary key value (NULL values are not allowed in the primary key column); values in the primary key column are not allowed to be modified or updated; and primary key values cannot be reused (if a row is deleted from the table, its primary key cannot be assigned to a future new row).
The primary key is usually defined on one column of the table, but it is not necessary to do so, and multiple columns can be used as the primary key together. When using multiple columns as primary keys, the above conditions must be applied to all columns, and the combination of all column values must be unique (but the values of a single column can not be unique).
4.7 Foreign key
A foreign key is a column in a table whose value must be listed in the primary key of another table. Foreign keys are an extremely important part of ensuring referential integrity.
Foreign keys help prevent accidental deletions. After defining a foreign key, DBMS does not allow you to delete rows that have associated rows in another table. For example, a customer with an associated order cannot be deleted. The only way to delete the customer is to delete the related order first (which means deleting the related order item as well). Because a series of deletions are required, the use of foreign keys can prevent accidental deletion of data.
5. MySQL database management system
5.1Database (Database, DB)
A database is a warehouse for storing data, which is organized and stored according to a certain data structure (data structure refers to the organizational form of data or the relationship between data). We can manage the data in the database through a variety of methods provided by the database. A simpler image understanding is that the database is the same as the warehouse where sundries are stored in our lives, except that the things we store are different.
5.2 data sheet (table)
Data table is a very important object in relational database, which is the basis of other objects, and also a collection of a series of two-dimensional arrays, which is used to store and manipulate the logical structure of data.
According to the classification of information, a database may contain several data tables, each table is composed of rows and columns, recording one piece of data, the data table is added a row, each column is composed of field names and field data sets, columns are called fields, and each column has its own multiple attributes, such as whether null, default value, length, type, storage coding, comments, and so on.
5.3 the database system has three main components
1. Database System: the place where data is stored.
two。 Database management system (Database Management System,DBMS): software for users to manage databases.
3. Database application (Database Application): a supplement to the software used to manage the database to improve the processing power of the database system.
5.4 View
A view is a virtual table with the same function as a physical table. You can add, modify, check, and manipulate a view, which is usually a subset of rows or columns with one or more tables. Changes to the view do not affect the underlying table. It makes it easier for us to get data than multi-table queries.
Views are generally used in the following two scenarios:
Visitors do not want to get the information of the entire table, but only expose some fields to the visitors, so create a virtual table, that is, the view.
The data of the query comes from different tables, and the querier wants to query in a unified way, so that a view can be established to combine the query results of multiple tables, and the querier only needs to obtain the data directly from the view. there is no need to consider the differences caused by the data coming from different tables.
Note: this view is created in the database, not in code.
5.5 stored procedures (Stored Procedure)
Stored procedures are supported in MySQL version 5. 0.
A stored procedure is one or more SQL statements saved for later use. They can be thought of as batch files, although their role is not limited to batch processing. A stored procedure is a database object that stores complex programs in a database for external programs to call. The idea of stored procedure is very simple, which is the code encapsulation and reuse at the level of database SQL language.
Advantages:
Stored procedures can be encapsulated and hide complex business logic. Stored procedures can return values and accept parameters. Stored procedures cannot be run using SELECT instructions because they are subroutines, unlike viewing tables, data tables, or user-defined functions. Stored procedures can be used for data validation, enforcing business logic, and so on.
Disadvantages:
Stored procedures are often customized on specific databases because of the different programming languages supported. When switching to another vendor's database system, you need to rewrite the original stored procedure. The performance tuning and writing of stored procedures are limited by a variety of database systems.
Wonderful topic sharing:
Installation tutorials for different versions of mysql
Installation tutorials for each version of mysql5.7
Installation tutorials for each version of mysql5.6
Installation tutorials for each version of mysql8.0
The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.