Shulou(Shulou.com)05/31 Report--

This article is about analyzing the basic usage of MySQL database in detail. Xiaobian thinks it is quite practical, so share it with everyone for reference. Let's follow Xiaobian and have a look.

directory

Library related content

Detailed operation of table

data type

enumeration and collection

Storage engine (understand)

Library related content

MySQL Some default libraries

information_schema: virtual library, does not occupy disk space, stores some parameters after the database is started, such as user table information, column information, permission information, character information, etc.

performance_schema: MySQL 5.5 starts to add a database: mainly used to collect database server performance parameters, record various events, locks and other phenomena that occur when processing query requests

mysql: authorization library, mainly storing the permission information of system users

test: test database automatically created by MySQL database system

ps: Some MySQL may not have test library, such as the author's 8.0, which is used for sys instead of test library.

Needless to say, database creation (mentioned in the previous article), understand the naming rules when creating databases:

It can consist of letters, numbers, underscores,@,#,$,

case-sensitive

uniqueness

You cannot use keywords such as create select

Numbers cannot be used alone.

Up to 128 bits

Usually the name is letters, numbers, underscores, such as @#$above is not recommended to use, if we connect the library through the code later, the symbols inside may conflict with the syntax of the code.

Detailed operation of table

Create constraints for tables (more on this in the next chapter)

create table student( id int not null, name varchar(10) not null #Last field cannot use comma);

The above operation indicates that when the id and name fields of the student table are constrained to insert values, they cannot be empty.

insert student values(null,'jack');

ERROR 1048 (23000): Column 'id' cannot be null

Tell us id cannot be empty

Complementary actions to change tables

Constraints are optional, depending on their own requirements for the table.

modify the table name

alter table name rename New table name;

Add field

alter table name add field name data type constraints (added as required);#add multiple fields alter table name add field name 1 data type,add field name 2 data type;#add field alter table name add field data type first at the beginning;#add field alter table name add field data type after a field;

remove fields

alter table name drop field

modify field

#modify the type or constraint of a field alter table name modify new data type new constraint;#modify the entire field alter table name change old field name new field name new field data type;#modify field name alter table name rename column original field name to new field name;

replicated table

When we query the table through select, we are presented with a virtual table, that is, the contents stored in memory, which cannot be saved. We can get the table data we want by copying.

Create a table demo

insert student values(1,'jack'),(2,'tom'),(3,'jams'),(4,'rous');

We need to save the contents of records with id greater than 2 to a new table

create table new_studnet select * from student where id > 2;

Or we just want to copy the data structure of the table, everything except records

create table new_student2 select * from student where 0 > 1;

This determination is always false, indicating that the table's records cannot be copied, but its data structure can be copied

data type

1. Integer type: TINYINT SMALLLINT MEDIUMINT BIGINT

Function: store age, grade, id, various numbers, etc.

Different integer types store different ranges of values.

For example, when we select int to create a field, the table will take up 4 bytes more. We need to save space by choosing integer types based on the range of values we store.

Float types: float, double, decimal (can be written as dec)

Function: Store salary, height, weight, physical parameters, etc.

float(255,30): integer can support up to 255 digits, and supports decimal numbers within 30 digits

double(255,30): integers up to 255 and decimals up to 30 digits

dec(65,30): integers up to 60 and decimals up to 30

A single-precision floating-point number (not an exact decimal value), where m is the total number of digits and d is the number after the decimal point. Maximum value of m is 255 and maximum value of d is 30

So what is the difference between these three floating-point types? The answer is precision.

Example: Create 3 tables of different float types

create table f1(id float(255,30));create table f2(id double(255,30));create table f3(id dec(66,30));

insert values

insert f1 values(1.111111111111111111111111); #31 decimal points after 1insert f2 values(1.111111111111111111111);insert f3 values(1.11111111111111111);

effect

Decaimal stores exact values because it stores strings internally.

Usually we use float is enough, if the requirement is higher, we can use double, if the precision requirement is particularly high, then we use decimal, but decimal integer length is not as good as float and double.

Date Type: Date Time Timestamp Year

Function: Store user registration time, article publication time, employee entry time, birth time, expiration time, etc.

When we create a table field, we can specify what the date passed in a field is, and the following options are available:

date # 1000-01-01/9999-12-31time # -838: 59:59/838:59:59year #support 1901/2155datetime #datetime 1000-01-01 00:00/9999-12-31 23:59:59timestamp #datetime 1970-01-01 00:00:00/2037 sometime

Example: Creating a table

create table info( id int, name varchar(10), birth date, class_time time, reg_time datetime, born_year year);

insert records

nsert info values( 1, 'jack', '1999-01-01', '08:30:00', '2020-01-01 10:15:00', '1999');

MySQL provides two kinds of date and time can be provided to us, so what is the difference between them?

Difference between datetime and timestamp

First take up space: datetime takes up 8 bytes, timestamp takes up 4 bytes

In version 5.x and above, after changing the table, using timestamp can automatically fill in the current system time for us. The author's 8.0 cannot automatically fill in the system time, and the effect presented by datetime is the same, but the upper limit of time is different. If we want to achieve this effect, we can add the following parameters when creating

create table d2( id int, name varchar(10), now timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

When we add data, we can add fields based on the first two, and let the last one be automatically supplemented.

# insert d2 values(1,'jack'); misspelling, because this must set values for all fields insert d2(id,name) values(1,'jack'); #correct spelling

4. String type: char, varchar

char: simple and rude, waste of space, fast access speed

Character length range: 0-255 (a Chinese is a character, 3 bytes of utf8 code)

When storing char values, spaces are filled to the right to satisfy the length

For example: specify the length of 10, save>10 characters will report an error, save

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.