MySQL database from Xiaobai to Xiaocai 01

MySQL database from Xiaobai to Xiaocai 01

database

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

--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

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:

--在当前数据库中删除表名为staff的表
drop table staff;
--如果当前数据库存在名为staff的表,则删除。
drop table if exists staff;