MySQL database from Xiaobai to Xiaocai 01

MySQL database from Xiaobai to Xiaocai 01


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.

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

create database lit;
create database if not exists lit;
create database if not exists lit character set utf8mb4;

Call this database; that is, use this database:

use lit;

Delete the database:Use with caution

drop database qqq;
drop database if exists qqq;

View all current databases:

show databases;

type of data

Numerical class:
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 datasizeRemarks
BIT[ (M)]M specifies the number of digits, the default is 1Binary number, M range is 1~64, storage value range is 0 to 2 M-1
TINYINT1 byteCorresponds to byte in java
SMALLINT2 bytesCorresponds to short in java
INT4 bytesCorresponding to integer in java
BIGINT8 bytesCorresponds to long in java
FLOAT(M, D)4 bytesSingle 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 bytesCorresponds to double in java
DECIMAL(M,D)Maximum M/D+2Double precision, M specifies the length, and D indicates the number of decimal places. Exact value
NUMERIC(M,D)Maximum M/D+2Same as above

String type:
commonly used VARCHAR(255), TEXT;

type of datasizeRemarks
VARCHAR (SIZE)0-65,535 bytesVariable-length character string
TEXT0-65,535 bytesLong text data
MEDIUMTEXT0-16 777 215 bytesMedium-length text data
BLOB0-65,535 bytesLong text data in binary form

Date type:

type of datasizeRemarksMeans
DATETIME8 bytesThe range is from 1000 to 9999, and time zone retrieval and conversion will not be performedYear, month, day, hour, minute, second
TIMESTAMP4 bytesThe 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. .

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:

show tables;

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:

drop table staff;
drop table if exists staff;