Shulou(Shulou.com)06/01 Report--

MySQL data types and table structures

DB,DataBase

- Database: A collection of data organized according to a data model and stored in memory.

DNMS,DataBase Management System

- Database management system: a large service software used to manipulate and manage databases.

DBS,DataBase System

Database system: DB+DBMS, refers to a computer system with database and integrated database management software.

Oracle: Oracle Database, MySQL

Microsoft: SQL Server, Access

IBM: DB2

Sybase: Sybase

MySQL's main features:

- Relational database

- Support Linux/Unix, Windows and other operating systems

- Written in C and C++, highly portable

- Supports Python/Java/Perl/PHP via API

Application environment:

LAMP platform, combined with Apache HTTP Server

-LNMP platform, combined with Nginx

Relational database service software (RDBMS): Store data according to specified results, and the data (values) can be associated with each other.

Oracle MySQL DB2 SQL Server

Non-relational database software (NOSQL): In the form of key-value pairs, one-to-one correspondence, no correlation between data (values).

Example: Redis montdb

1. Install MySQL

yum -y install perl-Data-Dumper Perl-JSON perl-Time-HiRes #dependency packages

tar -xf mysql-5.7.17-1.el7.x86_64.rpm-bundle.tar

rpm -Uvh mysql-unanimity-*.rpm #U Upgrade installation, replace conflict text file.

II. Start-up service

/usr/lib/systemd/system/mysqld.service

or

systemctl start mysql

systemctl enable mysql

III. Login

The default database administrator account root allows access from localhost.

The first login password is randomly generated during installation and stored in the error log file.

grep 'temporary password' /var/log/mysql.log

.....@ localhost: matoa>AV

4. Change password

0 or LOW Length

1 or MEDIUM(default) Length, numbers, lowercase/uppercase, and special characters

2 or STRONGLength, numbers, lowercase/uppercase and special characters, dictionary files

Connect to the local server via the client tool mysql and reset the password using alter user.

mysql>set global validate_password_policy=0; #Verify password length only

mysql>set global validate_password_length=6; #Modify password length to 6 characters, default is 8 characters.

mysql>alter user() identified by "123456";

mysql>quit #quit

mysql -uroot -p123456 #Log in with new password

To make it permanent, modify the configuration file:

vim /etc/my.cnf

.....

validate_password_policy=0

validate_password_length=6

Service related documents

/etc/my.cnf configuration file

/var/lib/mysql database directory

Default port 3306

Process name mysqld

Transmission Protocol TCP

process owner mysql

Process group mysql

Use the mysql command:

mysql -h server ip -u username-p password [database library name]

Command Type:

MySQL command: environment switch, see status, exit and other controls

SQL directives: database definition/query/manipulation/authorization statements

DDL Data Definition Language (create alter drop)

DML (insert updata delect)

DCL Data Control Language (grant revoke)

DTL (commit rollback savepoint)

Basic considerations:

1. Instructions are case-insensitive (except passwords, variable values)

2. Each SQL instruction is terminated or separated by a ";".

3.c\The currently incorrectly written instruction can be discarded.

Command:

show databases; View all libraries

use library name; access to specified library

show tables; Lists all tables in the current library

describe table name; view table field structure

create datebases library name; create specified library

select database(); View the current database

drop databas library name; delete specified library

create table name. Table name ( #Create specified table

Field Name 1 Field Type (Width) Constraints;

Field Name 2 Field Type (Width) Constraints;

...........

);

Example: mysql>create table gamedb.stu(mysql>name char(10),mysql>age intmysql>);

Drop table library name. Table name; delete tables under specified library

insert into library name. table name values(list of values); #insert table record

Example: insert into gamedb.stu values("jim",21),("tom",29);

select * from Library Name. Table name; view all records of table

delete from library name. Table name; delete all records of table

Common types of information

Numerical type: weight, height, grade, salary, price, etc.

Character type: name, work unit, address, etc.

Enumeration type: hobbies, gender, etc.

Date Time Type: Date of birth, registration time, etc.

1. numeric

Type Size Range (Signed) Range (Unsigned) Purpose

TINYINT 1 byte-128 ° 127 0~255 tiny integer

SMALLINT 2 bytes-32768 ° 32767 0~65535 Small integer

MEDUIMINT 3 bytes-integer in (2^23)~(2^23)-1 0~(2^24)-1

INT 4 bytes-(2^31)~(2^31)-1 0~(2^32)-1 Large integer

BIGINT 8 bytes-(2^63)~(2^63)-1 0~(2^64)-1 Maximum integer

FLOAT 4-byte single-precision floating point number

DOUBLE 8-byte double-precision floating point number

DECIMAL to DECIMAL(M,D), where M is significant digits, D is decimal digits, M should be greater than D, occupying M+2 characters

1.1 integer

When UNSIGED is used, the corresponding field holds only positive numbers (unsigned).

When the value is not enough for the specified width, the default is to fill in spaces on the left.

Width is only the display width, and the size of the stored data value is determined by the type.

When using the keyword ZEROFILL, fill in zeros instead of spaces.

When the value is out of range, an error is reported.

1.2 floating-point

Definition format: float(total width, decimal places)

When the field value does not match the type, the field value is treated as 0.

When values are out of range, only max/min values are saved.

Floating-point type: divided into single precision and double precision according to the range of stored values

Single precision float (n, m)

double(n, m)

n represents the total number of digits

m represents the number of decimal places

2. Character types

2.1 Fixed length: char (number of characters)

Maximum length 255 characters.

If the specified number of characters is not enough, the default is to fill in spaces on the right.

Data cannot be written when the number of characters exceeds.

2.2 variable length: varchar (number of characters)

Allocate storage space according to actual data size.

Data cannot be written when the number of characters exceeds.

Disadvantages: Every time you have to calculate, it consumes resources.

2.3 Large text type: text/blob

Used when the number of characters is greater than 655535.

Example: mysql>create database ku;mysql>create table ku.biao(>name) char(7) ,>age tinyint(3) unsigend,>high float(3,2) >);mysql>desc ku.biao;Fied Type Null key Default Extra Field Name Field Type Empty index type default value description information

3. datetime type

3.1 Date Time, DATETIME

It takes 8 bytes.

1000-01-01 00:00: 00.00000 ~ 9999-12-31 23:59: 59.99999

Example: 2017-12-31 23:59:59

3.2 Date Time, TIMESTAMP

It takes 4 bytes.

1970-01-01 00:00: 00.00000 ~ 2038-01-19 03:14: 07.99999

Example: 2017-12-31 23:59:59

3.3 Date, DATE

It takes 4 bytes.

Range: 0001-01-01 ~ 9999-12-31

3.4 year, YEAR

It takes 1 byte.

Range: 1901~2155

3.5 time, TIME

It takes 3 bytes.

Format:HH:MM:SS

With regard to the datetime field, when the TIMESTAMP field is not assigned, the current system time is automatically assigned, and the DATETIME field defaults to NULL.

Year processing, the default 4 digits, when only 2 digits assigned, 01~69 as 2000~2069, and 70~99 as 1970~1999.

Example: mysql>create table ku.xiao2 (>name varchar(8),>starty year,> birthday date, >abc time>);

Time function:

now() Gets the system date when this function is called

sysdate execution time dynamically obtains system time

sleep(N) sleep N seconds

curdate() Gets the current system time

curtime() Gets the current system time

month() Gets the month of the specified time

date() Gets the date in the specified time

time() Gets the time in the specified time

Examples: mysql>select now();mysql>select date(now());mysql>select time(now());

4. Enumeration types

4.1 Select a single value from a set of given values, ENUM.

Format: enum(value 1, value 2, value N)

4.2 Select one or more values from a set of given values, SET.

Format: set(value 1, value 2, value N)

Example: mysql>create table ku.biao3(>name char(5),>gen enum("boy","girl"),>likes set("book","music","pingpang","sleep")>);

constraints

Null allowed, default setting

NOT NULL is not allowed

key index type

Default Set default value, default is NULL

Example: mysql>create table ku.tbiao5(>name varchar(4) not null,>gen enum("boy","girl") default "boy",>age int(3) not null default 22,>);mysql>desc ku.biao5;

Modify a table's fields

Basic usage:

ALTER TABLE table names perform actions;

Perform actions:

1. add new field

ADD field name type (width) constraint;

ADD field name type (width) constraint FIRST; //add field to front

ADD field name type (width) constraint AFTER field name 2; //add field after field 2

2. Modify field type

MODIFY field name type (width) constraint;

3. Modify field name

CHANGE Source Field Name New Field Name Type (Width) Constraint;

4. Delete specified fields

DROP field name;

Example: mysql>alter table ku.biao5 add >kuki varchar(5) not null ;

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.