MySQL database from Xiaobai to Xiaocai 01
- Related knowledge (understand)
- Related operations of the library
- type of data
- Table related operations
- Related exercises (to be added)
Related knowledge (understand)
Database is a type of software (program), which is different from the data structure we have learned before, which is a subject. However, many data structures are also used internally in the database software. The purpose of learning data structure is to organize data
. The function of the database is to organize data, which is usually stored on disk. The function provided by software such as databases is the management of data (addition, deletion, modification, and checking).
Von Neumann is the grandfather of the computer industry.
Memory: fast access speed, small space, high cost, if the power is off, the data in the memory will be lost, commonly known as
external storage : access speed is full, large space, low cost, commonly known as memory, if the power is off, external Storage data retention, disks, hard disks, including U disks, CDs, etc. are all external
storage. The access speed difference between memory and external storage is 3-4 orders of magnitude, which is thousands of times
1s=1000ms, 1ms=1000us, 1us= 1000ns,1ns=1000ps The
time to read data from the memory once in a computer is: ps-ns level, the
time to read a disk: ns-us level, the
time to operate a mysql: ms-s level,
so the memory can be greatly improved Speed
In actual development, the database often needs to store a lot of data, and it is easy to reach the bottleneck of system performance.
For any company, data is the lifeblood, and the company's data is mainly stored and managed through the database.
SQL is a programming language, and MySQL is a specific database, and the database is generally operated through the SQL language.
MySQL is a typical representative of software such as databases, while Oracle is the first brother of databases, SQL Server is the database software developed by Microsoft, and SQLite is the most widely used database. SQLite is built in every mobile phone, as well as Redis and HBase. , MongoDB, MariaDB, etc. These are all database software.
Databases are divided into relational databases and non-relational databases. Relational databases have strict constraints on data, and they all use a structure like a table to store data. Non-relational databases have less strict constraints on data, and they usually store data through a structure like documents. MySQL is a relational database, while Redis, Hbase, and MongoDB are all non-relational databases.
Server: The end that passively receives the request is called the server, and the responding
client: The end that actively sends the request is called the client. The request
text: the file/data contains only characters.
Binary: It may contain other content. The way to
distinguish between binary and text is Open the file with Notepad, if it is understandable (without garbled characters), it is a text file.
When SQL is written on the MySQL client, the SQL is first sent from the client to the server, and the server receives the SQL for analysis and execution, completes the data insertion, and informs the result, whether the operation is successful or not, and how many rows are affected.
Related operations of the library
Create a database named lit: (it will prompt OK when the creation is successful, 1 row affected; when it fails, it will prompt error) The library name of the database cannot be repeated
--SQL里使用两个减号来备注，lit为数据库库名 create database lit; --这样也可以创建，如果已有该名数据库，则发出警告，但不会报错，如果有改名数据库，则不创建。 create database if not exists lit; --如果系统没有lit的数据库，则创建一个使用utf8mb4字符集库名为lit的数据库，如果有则不创建 create database if not exists lit character set utf8mb4;
Call this database; that is, use this database:
--使用名为lit的数据库 use lit;
Delete the database:Use with caution
--删除名为qqq的数据库 drop database qqq; --删除名为qqq的数据库，如果没有qqq的数据库则不删除 drop database if exists qqq;
View all current databases:
--查看当前所有数据库 show databases;
type of data
The numeric type can be designated as unsigned unsigned, which means that no negative number is taken. 1 byte bytes = 8 bit, but unsigned is generally not used.
Commonly used numeric types are: BIT[(M)], INT, DECIMAL(M,D)
|type of data||size||Remarks|
|BIT[ (M)]||M specifies the number of digits, the default is 1||Binary number, M range is 1~64, storage value range is 0 to 2 M-1|
|TINYINT||1 byte||Corresponds to byte in java|
|SMALLINT||2 bytes||Corresponds to short in java|
|INT||4 bytes||Corresponding to integer in java|
|BIGINT||8 bytes||Corresponds to long in java|
|FLOAT(M, D)||4 bytes||Single precision, M specifies the length, D specifies the number of decimal places, the precision will be lost, corresponding to float in java|
|DOUBLE(M,D)||8 bytes||Corresponds to double in java|
|DECIMAL(M,D)||Maximum M/D+2||Double precision, M specifies the length, and D indicates the number of decimal places. Exact value|
|NUMERIC(M,D)||Maximum M/D+2||Same as above|
commonly used VARCHAR(255), TEXT;
|type of data||size||Remarks|
|VARCHAR (SIZE)||0-65,535 bytes||Variable-length character string|
|TEXT||0-65,535 bytes||Long text data|
|MEDIUMTEXT||0-16 777 215 bytes||Medium-length text data|
|BLOB||0-65,535 bytes||Long text data in binary form|
|type of data||size||Remarks||Means|
|DATETIME||8 bytes||The range is from 1000 to 9999, and time zone retrieval and conversion will not be performed||Year, month, day, hour, minute, second|
|TIMESTAMP||4 bytes||The range is from 1970 to 2038, and the current time zone is automatically retrieved and converted.||Timestamp is a way to store and express time in a computer. It calculates the time difference from 1970:1:1:00:00:00.|
The reason for using timestamp: It takes up a small space and is convenient for calculation. Timestamps are often used when recording logs.
Log: log println reflects the working status of the program through the log. If there are many logs, you need to record the time corresponding to the current log, so that you can know the running status of the entire program in various time periods through this time. .
Table related operations
When you need to manipulate the data tables in the database, you need to use the database first:
-- 使用库名为lit的数据库 use lit；
View all tables in the current library:
Create a table: For
example, create an employee table with employee id name age remarks
--先写表中属性名，再写类型 --comment写在属性类型之后为备注 comment后加单引号表示备注的内容； create table staff( id int comment 'id', name varchar(20), age int, comment varchar(20) );
ps: In the database, keywords in MySQL cannot be used as table names/column names. If you have to indicate the name of the column, use backticks (the key above the TAB).
Check the structure of the table. The existing attributes in the table will be displayed in the form of a table. There will be information such as the type and attribute name of each attribute:
field is the field, which is the attribute name of the attribute (for example, the name in the student table is student The attribute name of the name attribute in the table)
Type is the type, that is, the attribute type of the attribute (for example, name is the string varchar type).
Null is empty, indicating whether the attribute can be empty (for example, the name is not empty, but similar to remarks) The attribute of can be empty)
Key is the key. This column will only be displayed if the primary key or foreign key is used, otherwise it will not be displayed.
Default is the default value, which means that when inserting, if there is no content, it means that the inserted value is the default value.
Extra is expansion,
--查看 名为staff的表的结构 --desc: description desc staff;
Delete a table:
--在当前数据库中删除表名为staff的表 drop table staff; --如果当前数据库存在名为staff的表，则删除。 drop table if exists staff;