MySQL database (database introduction | classification | basic usage | basic SQL statements for libraries, tables, and records)

Article directory

  • MySQL database
    • 1. History of evolution of data access
    • 2. Development History of Data Storage
    • 3. The nature of database
    • 4. Classification of databases
    • 5. Basic use
    • 6. Production of system services
    • 7. Introduction to SQL and nosql
    • 8. Introduction to MySQL database
      • 1. What is a database?
      • 2.The role of database
    • 10. Password related operations
    • 9. Important concepts of database
    • 11. Basic SQL statements for libraries
    • 12. Basic SQL statements for tables
    • 13. Basic SQL statements for records

MySQL database

1. History of evolution of data access

 Text file (the data storage location of each program is different)
File paths are inconsistent: one C drive and one D drive
Inconsistent data format: Lebron|123 LIke@123
Software Development Catalog Specification
Specifies the approximate storage location of the data files: DB folder
The data format is still not completely unified: for example, the json files are unified but the internal key-value pairs are different.
Database service
Unified access locations and unified data formats (completely unified)

2. Development history of data storage

 Single player game
We all know that stand-alone games can also be started and saved when there is no network.
Because its database services are all local, the database is included when downloading.
\t\t
\tOnline game
The same program on different computers. Data can be shared between
The database service is set up separately on the network (remote database service)
\t\t
Remote database service
You don’t have to worry about data security issues or server load issues
Databases have multiple servers running the same database service

3. The essence of database

 The three words database have different meanings from different perspectives.
1. Standing from the perspective of underlying principles
A database refers to a process (code running in memory) dedicated to manipulating data.
2. From the perspective of practical application
Database refers to an application with an operating interface (an interface for operating processes)
\t
Unless we make special instructions, when we mention database, we actually refer to database software.
'We also say that database software is essentially an application written based on C/S architecture'
The implication is that all programmers can theoretically write (there are already many database software on the market)

4. Classification of databases

 Relational database
1. The data is organized with a clear table structure
ID NAME GENDER data is isolated unified structure
The way a relational database accesses data can be viewed as a table. Each data is symmetrical.
(id is the id number, name is the name, gender is the gender, unified format)
\t\t
2. Database-level relationships can be established between tables
Boyfriend table Girlfriend table As long as you get a piece of data from the boyfriend table, you can get the related girlfriend table data.
\t
Relational database software MySQL PostgreSQL MariaDB Oracle Sqlite db2 sql server
MySQL: open source and the most widely used, a must-learn for database learning
PostgreSQL: open source supports secondary development
MariaDB: Open source and MySQL are written by the same author and the usage is very similar.
Oracle: Charges, extremely high security, mainly used in banks and major important institutions
sqlite: small database mainly used for local testing (the Django framework comes with this database)
\t\t
non-relational database
1. The data is organized without a clear table structure. It is organized in the form of k:v key-value pairs.
{<!-- -->'name':'jason'} {<!-- -->'username':'kevin','pwd':123}
2. Database-level relationships cannot be directly established between data.
\t\t   \t
Relational database software redis, mongoDB, memcache
redis: currently the most popular and most frequently used cache database
mongoDB: Stable database, most similar to relational and non-relational, mainly used for crawlers and big data
memcache: has been eliminated by redis (supports a string type K:V key-value pair)

5. Basic use

 1. Start the server first
2. Check the Mysql file location and Mysqld
3. Open a new Cmd window again (press Enter directly to enter guest mode with few functions)
4. Log in to MySQL with username and password -u root -p (root = username)
Mysql default administrator account user name is root and the password is the password you set during installation.
5. Exit the MySQL command (exit quit)

6. Production of system services

 1. How to solve the problem of needing to switch paths to find files every time
Add environment variable export PATH = /usr/local/mysql/bin
2. Create the MySQL server as a system service (starts when the computer is turned on and ends when it is turned off)
Open Cmd window as administrator
Execute the system service command mysqld --install
Start the server. Right-click the Task Manager service and click Start
Command to start net start mysql
Command to view system services services.msc
Close the mysql server net stop mysql
Remove system services (make sure the service is closed first)

7. Introduction to SQL and nosql

 'The server side of the database supports various languages and acts as a client'
\t
ex: Take the MySQL server as an example
MySQL client, client written in python code, client written in java code (all languages are compatible)
In order to be compatible with all types of clients there are two strategies
Server compatible
Unreasonable consumption of database server resources! ! !
Develop unified standards
SQL statement NoSQL statement
\t\t\t\t
SQL vs. NoSQL
SQL statements mean the syntax used to operate relational databases
The meaning of NOSQ statement and the syntax for operating non-relational databases
ps: SQL is sometimes used to represent relational databases. NoSQL is also used to represent non-relational databases.

8. Introduction to MySQL database

1. What is a database?

A database is "a warehouse that organizes, stores and manages data according to data structure". It is a collection of large amounts of data that is stored in a computer for a long time, is organized, shareable, and unified management. A database is a warehouse that stores data. Its storage space is very large and can store millions, tens of millions, or even hundreds of millions of pieces of data.

In the history of database development, databases have experienced various stages of development, such as hierarchical databases, network databases, and relational databases. Database technology has developed rapidly in all aspects. In particular, relational databases have become the most important among current database products. Since the 1980s, almost all new database products released by database manufacturers support relational databases. Even some non-relational database products almost all support relational databases. Database interface. With the development of cloud computing and the era of big data, other distributed technologies have been introduced, and this type of database is generally called N-SQL database.

Brief summary:

1.1 Database (DB, database) is a “warehouse” that organizes, stores and manages data according to the data structure.
1.2 A database is a collection of big data that is stored together in a certain way and can be shared by multiple users and managed uniformly.
1.3 The database can perform operations such as adding, deleting, modifying, and checking (Create/Retrive/Updata/Delete) to the database through some instructions.

2. The role of database

2.1 Achieve data sharing: you can access data in the database, and you can also connect to the database through the interface and share data
2.2 Reduce data redundancy: Since data can be accessed and shared directly with the database, users are prevented from creating their own application files, reducing a large amount of duplicate data, reducing data redundancy, and maintaining data consistency.
2.3 Maintain data independence: Data independence includes logical independence (the logical structure of the database and the application program are independent of each other) and physical independence (changes in the physical structure do not affect the logical structure of the data).
2.4 Centralized control of data: The database can centrally control and manage data, and represent various organizations and the relationships between data through data models.
2.5 Data consistency and maintainability: to ensure the security and reliability of data: mainly including (security control, integrity control, concurrency control, so that multiple data can be run in the same time period to implement multiple access, and Can prevent abnormal interactions between users.)
2.6 Fault recovery: The database management system provides a set of methods to detect and repair faults in time to prevent data from being damaged. The database system can be restored as quickly as possible. Failures that occur when the database system is running may be physical or logical errors. For example, data errors caused by misoperation of the system, etc.

10. Password related operations

 1. Change password (run administrator mode directly in Cmd)
mysqladmin -u (username that needs to be modified) -p (original password) passwrod new password
The first time to modify mysqladmin -u root -p password 123 # The first time to modify the default password is not
Second modification mysqladmin -u root -p123 password 321 # For the second modification, you need to enter the last password and modify the new one.
Side method (some versions cannot be used): # You need to log in to the mysql user first set password= PASSWORD (new password);
2. Forgot password
Method 1: Directly reinstall\or directly copy other people's corresponding files
Method 2: Cmd window manager run
1> net stop mysql # Close the MySql server service
2> mysql --skip-grant-tables # Restart Mysql
3> mysql -u root -p # Password-free login (need to create a new Cmd window to run)
4> udate mysql.user set password=password (new password) where Host='localhost' and User='root';
5> quit or exit (exit the current Cmd window)
6> Go to the original first Cmd window and clear the command (Ctrl + C)
7> net start mysql start mysql service
8> mysql -u root -p Enter the new password to log in successfully

9. Important concepts of database

 The following concepts are only for beginners to understand faster and are not completely correct.
\t
data the state of things
Library (DataBase, referred to as DB) folder
Table files in folder
Record data line by line in the file
(The library table record is like a file in a folder, with content in the file)

Database management system (DataBase Management System (DBMS))
Socket software for data management, C/S architecture

Database server:
A computer running a DBMS server, which has relatively high memory and hard disk requirements.
\t\t
'''Sequence: Library >>> Table >>> Record >>> Field '''
There can be multiple tables in a database, and there can be many records and fields in a table.
\t
The SQL statement ends with a semicolon (;)
Cancel the execution of the SQL statement (\c)

show databases; View all databases
show tables; View all tables
sleect * from mysql.user; View all records in the user table

11. Basic SQL statements for libraries

 1. Add library
create database database name; (SQL keywords cannot be used)
2. View the library
show databases; (library list)
show create database library name; (single database data)
3. Modify the language of the library (basically not used, just delete and recreate)
alter database library name charset='gbk';
4. Delete the library
drop database library name;
5. Select library
use library name;

12. Basic SQL statements for tables

 Check the current library name select database();
If the custom library is not switched, the default is NULL.
Specify the use library name;

1.Add table
create table table name (field name field type, field name field type); #field name represents the unified name of the data name, type represents int str
eg:create table u1s1(id int,name varchar(32))
2. View table
show tables;
show create table table name; # (you can see the created format) table structure
describe table name;
desc table name; # (View table structure, formatted)
3. Modify the table name
alter table old table name rename new table name;
4. Delete table
drop table table name;

13. Basic SQL statements for records

 Since you want to operate records, you must have libraries and tables, otherwise the operation will not be possible.
'Record: A row of data in a table is called a record'

1. Add records
insert into table name values(data, data); #Add all fields, add single field
insert into table name values (data, data), (data, data) # Add all fields, add in batches
2. View records
select * from table name; # * means to view all field records
select field 1, field 2 from table name; # View two separate field records
ps: If there are too many fields in the table and confusion occurs, you can write \G at the end.
3. Modify records
update table name set field name = new data where filter condition; # The filter condition is the field name
4. Delete records
delete from table name; # Delete all data in the table
delete from table name where filter condition #Delete data according to conditions