The concept of database
The concept of database: A database is a warehouse used to organize (each data is related and organized according to rules), storage and management (add, delete, modify, check)
Database Management System (DBMS): System software that realizes effective organization, management and access of data
A database is a system, a human-computer system. It consists of hardware, os, database, DBMS and database users.
Users operate the database through DBMS (various database software)
Classification of databases
With the development of the times, today’s databases can be divided into two major categories: relational databases (SQL) and non-relational databases (NO SQL).
Relational Database
The storage structure of a relational database is a two-dimensional table, which has rows and columns. Rows are called records and are information used to describe an object. A column is called a field and is an attribute used to describe an object
Common relational databases include: MYSQL, Oracle, SQL-Server, MariaDB, postgreSQL
Non-relational database
Cacheable database: redis Memcache
Indexed database: ES (Elasttic search)
Sequential database: Prometheus
Document database: MongoDB
Non-relational database: It is a storage structure of key pairs (key ——> VALUE)
Advantages: Highly concurrent reading and writing, high-efficiency storage and access to massive data, high scalability and high availability
Storage engine of mysql database
Before 5.5: MYSAM, a storage engine that does not support things and foreign keys. Suitable for reading more and writing less
The default engine after 5.6: innodb, a storage engine that can support transactions, foreign keys, and row-level locking, and supports applications with high concurrency performance.
Thing: In the database, a sequence of database operations composed of one or more operations. Either all of these operations succeed, or none of them are executed if one fails.
Reason for this operation: Ensure data consistency and integrity
Characteristics of things:
1. Atomicity (the smallest working unit of the database), either all executions are successful or none are executed. As long as there is an operation time, the entire execution sequence will be rolled back. Completed actions will also be undone
2. Consistency: Before and after things are executed, the integrity constraints of the database cannot be violated. Things can be submitted only if all constraints are met.
3. Isolation: The execution of things is isolated from each other. The execution of one thing cannot be interfered by other execution things. Isolate concurrent transactions from each other to prevent data inconsistency
4. Persistence: Once a thing submits all its modifications, it will be permanently saved in the database. Even if the system crashes, the submitted data will not be lost. Ensure that the database is always in a consistent state. Ensure database integrity and consistency.
Mysql noun
1. Database: database
2. Table: table
row: row
column: column
3. Index: index
4. View: view
5. Stored procedure: procedure
6. Trigger: trigger
7. User: user
8. Permission: privilege
mysql statement specifications
In the database system, sql is not case-sensitive, but uppercase letters are recommended. Statements are not distinguished, but table names are strictly case-sensitive.
SQL statements can be written in a single line or in multiple lines, but they all end with ; by default.
Keywords cannot span lines or be abbreviated.
Sub-statements are usually placed in the path to facilitate editing and improve readability.
Database naming convention
1. It must start with a letter and can contain data. Special characters: #_$
2. You cannot use MySQL reserved words as names: table select
3. Database name Table name User name is strictly case-sensitive
Character type of database
| Character type | Usage |
| int | 4 bytes, used to store integers and ranges Very big |
| char | Can only access fixed-length strings and is used to store fixed-length strings |
| varchar | A variable-length character type that stores variable-length strings. (You can write whatever you want without any restrictions) |
| float (m,d) |
Single precision floating point m: total length d: decimal places |
| double (m,d) | Double precision floating point number, stores floating point number. M is the total position, d represents the decimal place |
| text | Used to store large text data, documents, or character length |
| image | Binary stored images, pictures, multimedia |
| decimal (5.2) | Used to store fixed-precision decimals, where 5 represents the total number of digits and 2 represents the decimal place. |
| date | Storage date: YYYY-MM-DD |
| datetime | Storage log yyyy-mm-dd hh:mm:ss (year-month-day-hour-minute-second) |
| timestamp | Same as datetime format, can automatically update the current timestamp |
The difference between char and varchar
Char: Regardless of whether the value is defined, it will occupy a fixed length of bytes
varchar: When saving, the actual length of varchar14 is 5. The result is actual length + hidden symbols = 6
varchar saves disk space than char, but varchar reads and writes slower than char
Classification of sql statements
DDL: Data definition language, used to create data objects, create libraries, tables, and indexes.
CREATE :Create
DROP: delete
ALTER: Modify
show global variables like 'port';
show databases; View library use mysql; //Use mysql library
show tables;
describe user\G; //View vertically desc test1; //Abbreviation
show databases; create databases zzr;
create table hj (id int(4) not null,name varchar(10) not null,score decimal(5,2),passwd char(48),primary key(id));
desc hj; //View table structure
The difference between null and spaces:
Null means nothing, the object does not have any description information.
DML: Data Manipulation Language, Manage data in tables
select: view
update: update
insert: add
delect: delete Delete the table
insert into hj values(4,'ZZR',92,password('789'))
mysql> update hj set passwd=password('456') where id = 3;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> select * from hj;
+ ---- + ------ + ------- + ------------------------------- --------------- +
| id | name | score | passwd |
+ ---- + ------ + ------- + ------------------------------- --------------- +
| 1 | A | NULL | |
| 2 | Ah | 90.00 | 123 |
| 3 | AAA | 95.00 | *531E182E2F72080AB0740FE2F2D689DBE0146E04 |
| 4 | ZZR | 92.00 | *531E182E2F72080AB0740FE2F2D689DBE0146E04 |
+ ---- + ------ + ------- + ------------------------------- --------------- +
4 rows in set (0.01 sec)
mysql> delete from hj where id = 4; mysql> select * from hj;
DQL language: query data records
select * from hj where id = 3; + ---- + ------ + ------- + ------------------------------- --------------- + | id | name | score | passwd | + ---- + ------ + ------- + ------------------------------- --------------- + | 3 | AAA | 95.00 | *531E182E2F72080AB0740FE2F2D689DBE0146E04 | + ---- + ------ + ------- + ------------------------------- --------------- + 1 row in set (0.00 sec) //View the specified row
mysql> select * from hj limit 2,3; + ---- + ------ + ------- + ------------------------------- --------------- + | id | name | score | passwd | + ---- + ------ + ------- + ------------------------------- --------------- + | 3 | AAA | 95.00 | *531E182E2F72080AB0740FE2F2D689DBE0146E04 | + ---- + ------ + ------- + ------------------------------- --------------- + 1 row in set (0.00 sec) mysql> select * from hj limit 1,3; + ---- + ------ + ------- + ------------------------------- --------------- + | id | name | score | passwd | + ---- + ------ + ------- + ------------------------------- --------------- + | 2 | Ah | 90.00 | 123 | | 3 | AAA | 95.00 | *531E182E2F72080AB0740FE2F2D689DBE0146E04 | + ---- + ------ + ------- + ------------------------------- --------------- + 2 rows in set (0.00 sec) limit 2; //Indicates the first two lines
View specified columns
mysql> select distinct name from hj; + ----------- + | name | + ----------- + | A | | Ah | | AAA | | zhaozirui | | Zhao Zirui | + ----------- + 5 rows in set (0.00 sec) //View the specified column
Modify the name of the table
Modify the name of the table mysql> show tables; + --------------- + | Tables_in_zzr | + --------------- + | hejing | + --------------- + 1 row in set (0.00 sec) mysql> alter table hejing rename hj; //Format Query OK, 0 rows affected (0.00 sec) mysql> show tables; + --------------- + | Tables_in_zzr | + --------------- + | hj | + --------------- + 1 row in set (0.00 sec)
Update information
mysql> update hj set birth=date('2002-5-31') where id = 4;
//View positioning based on unique index
Modify column name
mysql> alter table hj change birth birthday datetime;
Delete column
Delete column mysql> alter table hj drop birthday;
Modify field type
mysql> alter table hj modify column passwd varchar(52); Query OK, 5 rows affected (0.01 sec)
DCL: Data Control Language, set or change database users or user permissions
GRSNT: Grant permissions
REMOVE: cancel permission
TCL: Transaction control statement, manages things in the database
commit: Confirm the submission of the transaction. Once submitted, it cannot be modified.
ROLLBACK: Things cannot be rolled back after they are submitted.
savepoint save point, can be rolled back