catalogue
2. Database and data table management
3. Simple query and data operation
4. Backup and restore database
Import: - MySQL basic usage
In the information society, fully and effectively managing and utilizing all kinds of information resources is the prerequisite for scientific research and decision-making management. Database technology is the core part of various information systems such as management information system, office automation system and decision support system. It is an important technical means for scientific research and decision management.
Objectives of this lesson:
1. Master database related concepts, and be able to quickly install MySQL database server and client on Linux system (key)
2. Be able to design data tables and create corresponding databases and tables (key and difficult points) according to software business
3. Be able to conduct simple query and data operation according to specific business requirements (key points)
4. Be able to quickly back up and restore the database, and develop the good habit of backing up the database
1. Database foundation
Overview of database foundation
Data management mainly goes through the following processes:
Manual management stage: the application manages data, which is not saved, shared or independent.
File management stage: file system management data and data can be saved for a long time, but it has poor sharing, large redundancy and poor independence.
Data management stage: the database system manages data, with complex data structure, small redundancy, easy expansion, high independence and unified data control.
Characteristics of database:
Data structure
Realize data sharing
Reduce data redundancy
Data independence
Database type (by data model characteristics)
network data model
Hierarchical database
Relational database
Mesh database: mesh data model with record type as node is adopted
Hierarchical database: uses a hierarchical model to simulate things organized hierarchically in the real world
• relational database: it uses two-dimensional table structure to organize and manage data, and specifies the dependencies of data in and between tables
A relational database is a collection of related tables and other database objects. For relational databases, relationships are synonymous with tables.
A table is composed of rows and columns (similar to the structure of a two-dimensional array).
A column contains a set of named attributes (also known as fields).
A row contains a set of records, and each row contains one record.
The intersection of rows and columns is called data item, which indicates the value of the attribute corresponding to a column on a row, also known as field value.
Columns need to define data types, such as integer or character data.
Data structure diagram of relational database:
Common databases
MySQL is an open source relational database management system (RDBMS) developed by MySQL AB company in Sweden. At present, it belongs to Oracle's products.
MySQL database system uses the most commonly used database management language - Structured Query Language (SQL) for database management.
Since MySQL is open source, anyone can download it under the license of the General Public License and modify it according to personalized needs.
MySQL has attracted much attention because of its speed, reliability and adaptability.
SQL language is mainly a language used to operate relational database, which is called structured query statement
SQL statements are mainly divided into:
DQL: data query language, used to query data, such as select
DML: data operation language, which can add, modify and delete data, such as insert, udpate and delete
TPL: transaction processing language, which processes transactions, including begin transaction, commit and rollback
DCL: data control language for authorization and permission recovery, such as grant and revoke
DDL: data definition language for database and table management, such as create and drop
CCL: pointer control language, which controls the pointer to complete the operation of the table, such as declare cursor
MySQL features:
It is written in C and C + +, and tested with a variety of compilers to ensure the portability of the source code
It fully supports the GROUP BY and ORDER BY clauses of SQL, and supports aggregate functions (COUNT(), COUNT(DISTINCT), AVG(), STD(), SUM(), MAX() and MIN()). You can mix tables from different databases in the same query.
It provides API s for many programming languages, such as C, C + +, Python, Java, Perl, PHP, Eiffel, Ruby, etc
Supports multiple storage engines
MySQL software adopts the dual authorization policy, which is divided into community version and commercial version. Due to its small size, fast speed and low overall cost of ownership, especially the open source, MySQL is generally selected as the website database for the development of small and medium-sized websites
2. Database and data table management
Connect to database
mysql -u account - P password - h host address - P port
mysql -uroot -pmysql
View database version
select version();
Displays the current time
select now();
View all databases:
show databases;
Create database
create database database name charset=utf8;
Note: when creating a library, you must specify the code utf8. There is no - in utf8, which is a little different from the code written in pyhton
Switch database:
use database name
View which database is currently in use
select database();
Delete database
drop database database name;
Data table management - data table design
Data table design includes the design of ER diagram, table primary key, field, data type, constraint and relationship between tables
E-R (entity relationship) model, i.e. entity relationship model, is mainly used to define the storage requirements of data. This model has been widely used in relational database design. E-R model consists of three basic elements: entity, attribute and relationship.
Primary Key
The database table requires that each row record in the table must be unique, that is, two identical records are not allowed in the same table.
When designing a database, in order to ensure the "uniqueness" of records, the most common and recommended method is to define a primary key for the table.
Primary keys in database tables have the following two characteristics:
The primary key of a table can be composed of one field or multiple fields (this is called a composite primary key).
The value of the primary key in the database table is unique and cannot be NULL. When the primary key in the database table is composed of multiple fields, the value of each field cannot be NULL.
Relationship between entities and Foreign Key
There is a one-to-one relationship between the class entity and the head teacher entity, a one to many relationship between the class entity and the student entity, and a many to many relationship between the student entity and the curriculum entity.
The relationship between entities can be represented by foreign keys. If a field a in table a corresponds to the primary key B of table B, field a is called the foreign key of table a. At this time, the value of field a stored in table a is also the value of primary key B of table B.
Constraint
A constraint is a mandatory rule defined on a table. When a constraint is defined for a table, all SQL operations on the table must meet the constraint rules, otherwise the operation will fail.
Constraint type:
| constraint | explain |
| NOT NULL | Non NULL constraint, specifying that all row data of a column cannot contain null values |
| UNIQUE | The uniqueness constraint specifies that all row data of a column or a combination of columns must be unique |
| PRIMARY KEY | Primary key constraint is a mandatory dependency relationship established on columns and reference columns |
| FOREIGN KEY | Foreign key constraint, a mandatory dependency on columns and reference columns |
| CHECK | Checking constraints, specifying a condition on the column that must be met |
Data table management - create table
View tables in the current database
show tables;
Create table
create table PythonDB( id int unsigned auto_increment primary key not null, name varchar(10) not null, is_delete bit(1) not null default 0 );
Create student table
create table students( id int auto_increment primary key not null, name varchar(10) not null, gender bit(1) default 0, hometown varchar(40) default "" ) ;
Comment comment: when creating a table, if there are many fields, you can add comments to the fields to prevent forgetting what data is stored in the fields.
create table students( id int auto_increment primary key not null comment 'Primary key', name varchar(10) not null comment 'Student name', gender bit(1) default 0 comment 'Gender', hometown varchar(40) default "" comment 'Home address' ) ;
View the sql statement that created the table
show create table name;
Add field
alter table name add column name type;
Add a hobby field to students
mysql> desc students; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | gender | bit(1) | YES | | b'0' | | | hometown | varchar(40) | YES | | | | +----------+-------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) mysql> alter table students add hobby varchar(100); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc students; +----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | gender | bit(1) | YES | | b'0' | | | hometown | varchar(40) | YES | | | | | hobby | varchar(100) | YES | | NULL | | +----------+--------------+------+-----+---------+----------------+
Delete field
alter table name drop field name;
Delete the hobby field from the students table
mysql> desc students; +----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | gender | bit(1) | YES | | b'0' | | | hometown | varchar(40) | YES | | | | | hobby | varchar(100) | YES | | NULL | | +----------+--------------+------+-----+---------+----------------+ 5 rows in set (0.02 sec) mysql> alter table students drop hobby; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc students; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | gender | bit(1) | YES | | b'0' | | | hometown | varchar(40) | YES | | | | +----------+-------------+------+-----+---------+----------------+ 4 rows in set (0.02 sec)
Modify field
The first is to modify the type and constraint without modifying the field name
alter table name modify column name type and constraint;
mysql> desc students; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | gender | bit(1) | YES | | b'0' | | | hometown | varchar(40) | YES | | | | +----------+-------------+------+-----+---------+----------------+ 4 rows in set (0.02 sec) mysql> alter table students modify hometown varchar(70) not null; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc students; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | gender | bit(1) | YES | | b'0' | | | hometown | varchar(70) | NO | | NULL | | +----------+-------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec)
Second, you need to modify the field name
alter table name change original name, new name type and constraint;
mysql> desc students; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | gender | bit(1) | YES | | b'0' | | | hometown | varchar(70) | NO | | NULL | | +----------+-------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) mysql> alter table students change gender sex bit(1); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc students; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | sex | bit(1) | YES | | NULL | | | hometown | varchar(70) | NO | | NULL | | +----------+-------------+------+-----+---------+----------------+ 4 rows in set (0.02 sec)
Data table management - delete table
drop table name;
Delete student table students
mysql> drop table students; Query OK, 0 rows affected (0.00 sec) mysql> show tables; Empty set (0.00 sec)
Graphical interface operation database
Download and install the graphical interface tool Navicat
Navicat website: https://www.navicat.com.cn/
mysql has just installed the root account. By default, you can only log in locally, not on other machines. Before using Navicat to connect, modify the user login permission of mysql on the command line client;
Modification steps:
(1) mysql -uroot -p123456 connect to database
(2) use msyql to enter mysql database
(3)select host,user from user; What permissions does the account have
(4) Change the root login permission so that all hosts can log in
grant all privileges on *.* to 'root'@'%';
(5) Bind address of annotation configuration file
Comment out the bind address in the / etc/mysql/mysql.conf.d/mysqld.cnf configuration file
Connecting to MySQL using Navicat
Open the installed Navicat client and click Connect - > mysql to fill in the account and password. The host address is the ubuntu ip address where you installed msyql. Click Connect test to pop up the successful connection, indicating that Navicat has connected to mysql. Click OK.
After successful connection, you can see all databases
Create database
Right click the blank space in the left column and click "new database"
Fill in the database name and coding format in the pop-up box and select utf-8
Create data table
Select a table and click new table
Create a class table
For the id field, it needs to be set to int type, unsigned, auto growth, primary key and non empty
After creating a table, you can also edit the table
Open table, design table, delete table
3. Simple query and data operation
Basic query statement
select * from table name;
select * from students; Query all contents in the students table
Specify field query: select field 1, field 2 from table name;
For example, I just want to see the id and name columns
select id,name from students;
SELECT id,NAME from student;
Operation results
insert data
Full column insert
insert into table name values (...)
mysql> desc students; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | gender | bit(1) | YES | | b'0' | | | hometown | varchar(40) | YES | | | | +----------+-------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) mysql> insert into students values(0,'Lau Andy',0,'Hong Kong, China'); Query OK, 1 row affected (0.00 sec) mysql> select * from student; ERROR 1146 (42S02): Table 'studb.student' doesn't exist mysql> select * from students; +----+--------+--------+----------+ | id | name | gender | hometown | +----+--------+--------+----------+ | 1 | Lau Andy | | Hong Kong, China | +----+--------+--------+----------+ 1 row in set (0.00 sec)
Partial insertion
insert into table name [field 1, field 2] values (value 1, value 2);
Full column multi row insert
Insert multiple lines. The contents of each line are written in a small bracket, and multiple lines are separated by commas.
insert into table name values (...), (...), (...);
Partial column multi row insertion
insert into table name (field 1, field 2) values (..), (..);
Modify data
update table name set field = xxx where field = xxx;
update students set hometown = 'Zhuhai' where id= 5;
Delete data
Delete Cheng Yaojin with id 3
delete from students where id =3;
4. Backup and restore database
Backup database
Back up the data of all tables in the database
mysqldump – uroot – p database name > python.sql;
ysqldump -uroot -p python >python.sql
Prompt for password, mysql password
Back up the data of a data table in the database
mysqldump – uroot – p database name Data table name > class.sql;
msyqldump -uroot -p python class > class.sql
You must manually create a library before restoring the database
mysql -uroot – p new database name < python.sql
GUI backup and restore database
Back up the database and dump the SQL file to back up the database
Back up the database and dump the SQL file to back up the database
Restore database
Create a new database and run the backed up SQL file
Knowledge summary
Database foundation
Overview of database foundation
Introduction to MySQL database
MySQL database installation
Database and data table management
Database management
Data sheet management
Graphical interface operation database
Simple query and data operation
Basic query statement
insert data
Modify data
Delete data
Backup and restore database
Backup database
Restore database
GUI backup and restore database
Homework after class
1. Please write out the syntax format of full column insertion and partial insertion.
Insert into student values('value 1 ','... ') The number of values must match the number of columns
Insert into student ('column 1 ','... ') values('value 1', '...') The number of values must be the same as the number of columns
Consistent quantity
Practical exercises after class
1. Using the navicat graphical interface tool, create a database named test with the coding format of utf-8, and create a table goods table in the test library. Field needs id,name,price,is_delete. Insert some data randomly into the table
2. Use navicat to back up the database in question 1.
3. Delete the database just created from the command line.
4. Repeat the operation of the first question on the command line, create libraries, tables, and insert some data.
5. Insert more data in the goods table, modify, delete, insert and other sql statements in the exercise class.
6. Back up the test database by command.
mysql> create database test charset='utf8';
Query OK, 1 row affected
mysql> use test;
Database changed
mysql> create table goods(
-> id int auto_increment primary key not null,
-> name varchar(50) not null
-> );
Query OK, 0 rows affected
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| goods |
+----------------+
1 row in set
mysql> insert into goods values(0,'soybean'),(0,'Corn'),(0,'peanut'),(0,'Wheat')；
-> ;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '；' at line 1
mysql> insert into goods values(0,'soybean'),(0,'Corn'),(0,'peanut'),(0,'Wheat');
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from goods;
+----+------+
| id | name |
+----+------+
| 1 | soybean |
| 2 | Corn |
| 3 | peanut |
| 4 | Wheat |
+----+------+
4 rows in set
mysql> update table goods set name='Purple sweet potato' where id=4;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table goods set name='Purple sweet potato' where id=4' at line 1
mysql> update
goods set name='Purple sweet potato' where id=4;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from goods;
+----+------+
| id | name |
+----+------+
| 1 | soybean |
| 2 | Corn |
| 3 | peanut |
| 4 | Purple sweet potato |
+----+------+
4 rows in set
mysql> delete from goods where id=4;
Query OK, 1 row affected
mysql> select * from goods;
+----+------+
| id | name |
+----+------+
| 1 | soybean |
| 2 | Corn |
| 3 | peanut |
+----+------+
3 rows in set