MySQL data type and table structure

MySQL data type and table structure

Db,database
-Databases: Data collections that are organized and placed into memory according to a data model.

Dnms,database Management System
-Database management system: a large-scale service software used to manipulate and manage databases.

Dbs,database System
-Database system: Db+dbms, which refers to a computer system with database and integrated database management software.

Oracle (Oracle): Oracle Database, MySQL
Microsoft (Microsoft): SQL Server, Access
Ibm:db2
Sybase:sybase

MySQL Main features:
-Relational database
-Support Linux/unix, Windows and many other operating systems
-Written in C and C + + with strong portability
-Support python/java/perl/php and other languages via API

Application Environment:
-lamp platform, combined with Apache HTTP server
-LNMP platform, combination with Nginx

relational database service software (RDBMS): Stores data according to the prescribed results, and the data (values) can be used to correlate operations.
Example: Oracle MySQL DB2 SQL Server

Non-relational database software (NOSQL): In the form of key-value pairs, there is no association between the data (values).
Example: Redis mongdb

First, install MySQL
Yum-y Install Perl-data-dumper Perl-json perl-time-hires #依赖包
TAR-XF Mysql-5.7.17-1.el7.x86_64.rpm-bundle.tar
RPM-UVH mysql-conmmunity-*.rpm #U升级安装, can replace conflicting text files.

Second, start the service
/usr/lib/systemd/system/mysqld.service
Or
Systemctl start MySQL
Systemctl Enable MySQL

Third, Login
The default database administrator account root, which 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
[Email protected]: MATOA>AV<PSDK #随机密码

Mysql-uroot-p "MATOA>AV<PSDK"
Mysql>

Iv. Change of password
0 or low length
1 or MEDIUM (default) length, number, lowercase/uppercase, and special characters
2 or strong length, number, lowercase/uppercase and special characters, dictionary file
Connect the local server via the client tool MySQL and use ALTER user to reset the password.

Mysql>set Global validate_password_policy=0; #只验证密码长度
Mysql>set Global validate_password_length=6; #修改密码长度为6个字符, the default is 8 characters.
Mysql>alter User User () identified by "123456"; #修改登录密码
Mysql>quit #退出

mysql-uroot-p123456 #使用新密码登录

To implement permanent entry, you need to 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
Transport Protocol TCP
Process owner MySQL
Process belongs to group MySQL

Using the MySQL command:
Mysql-h Server ip-u user name-p password [database library name]

Instruction type:
MySQL command: Environment switch, see State, exit and other control
SQL directives: Database definition/Query/manipulation/authorization statements
DDL Data Definition language (create alter drop)
DML Data Manipulation language (insert Updata delect)
DCL Data Control Language (grant Revoke)
DTL Data Things language (commit rollback savepoint)

Basic precautions:
1. Directives are case-insensitive (except for passwords, variable values)
2. Each SQL instruction is ";" Ends or separates.
3.c\ can discard the currently-written error instructions.

Command:
show databases; View all libraries
Use library name; Go to the specified library
Show tables; List all tables in the current library
describe table name; View the field structure of a table
Create datebases library name; Create a specified library
Select Database (); View your current library
Drop Databas library name; Delete the specified library

CREATE table library name. Table Name (#创建指定的表
Field name 1 field type (width) constraints;
Field Name 2 field type (width) constraints;
...........
);

例建表：mysql>create   table   gamedb.stu(mysql>name    char(10),mysql>age      intmysql>);

drop table library name. Table name; Delete a table under a specified library

Insert into library name. Table name values (value list); #插入表记录

例：insert into  gamedb.stu  values("jim",21),("tom",29);

SELECT * from library name. Table name; View all records for a table
Delete from library name. Table name; Delete all records for a table

Common types of information
Numerical: Weight, height, score, salary, price, etc.
Character type: name, work unit, address, etc.
Enumeration type: hobbies, gender, etc.
Date and Time type: Date of birth, time of registration, etc.

1. Numerical type
Type size range (signed) range (unsigned) use
TINYINT 1 byte -128~127 0~255 Tiny integer
SMALLINT 2 bytes -32768~32767 0~65535 Small integer
Meduimint 3 bytes-(2^23) ~ (2^23)-1 0~ (2^24)-1 integers
INT 4 bytes-(2^31) ~ (2^31)-1 0~ (2^32)-1 large integers
BIGINT 8 Bytes-(2^63) ~ (2^63)-1 0~ (2^64)-1 maximum integers
Float 4-byte single-precision floating-point number
Double 8-byte dual-precision floating-point number
Decimal pairs of decimal (m,d), where M is the significant number of digits, D is the number of decimal digits, and m should be greater than D, occupying m+2 characters

1.1 Integer type
When you use the unsiged modifier, the corresponding field only holds a positive number (unsigned).
When the value is not sufficient to specify the width, the default is to fill in the left fill.
Width is only the display width, and the size of the stored data value is determined by type.
When using keyword Zerofill, fill in 0 instead of space.
Error when the value is out of range.

1.2 Floating-point type
Definition Format: Float (total width, number of decimal digits)
When the field value does not match the type, the field value is treated as 0.
When the value goes out of range, only the maximum/minimum value is saved.
Floating point type: Divided into single-precision double-precision according to the range of stored values
Single precision float (n,m)
Dual-precision double (n,m)
n indicates the total number of digits
M represents the number of decimal digits

2. Character types
2.1 Fixed Length: char (number of characters)
The maximum length is 255 characters.
Not enough to specify the number of characters by default on the right with a space.
Cannot write data when the number of characters is exceeded.

2.2 Variable length: varchar (number of characters)
Allocates storage space by the actual size of the data.
Cannot write data when the number of characters is exceeded.
Cons: Calculate every time, consume resources.

2.3 Large text type: Text/blob
Used when the number of characters is greater than 655535 storage.

例：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字段名 字段类型     是否为空 索引类型    默认值   描述信息  

3. Date and Time type
3.1 Date Time, datetime
Occupies 8 bytes.
Scope: 1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999
Example: 2017-12-31 23:59:59

3.2 Date and time, TIMESTAMP
Occupies 4 bytes.
Scope: 1970-01-01 00:00:00.000000 ~ 2038-01-19 03:14:07.999999
Example: 2017-12-31 23:59:59

3.3 Dates, date
Occupies 4 bytes.
Range: 0001-01-01 ~ 9999-12-31

3.4 Years, Year
Occupies 1 bytes.
Range: 1901~2155

3.5 Times, time
Occupies 3 bytes.
Format: HH:MM:SS

About datetime fields, when a value is not assigned to the timestamp field, the current system time is automatically assigned, and the DateTime field default value is null.
The processing of year years, by default, is represented by a 4-digit number, and when assigned with a value of 2 digits, 01~69 is considered 2000~2069, and 70~99 is considered 1970~1999.

例：mysql>create table  ku.biao2(>name  varchar(8),>starty  year,>birthy  date, >abc     time>);

Time function:
Now () Gets the system date when this function is called
Sysdate () Execution time dynamically acquiring system time
Sleep (n) sleep n seconds
Curdate () Gets the current system time
Curtime () Gets the current system moment
Month () Gets the months in the specified time
Date () Gets the day of the specified time
Time () Gets the moment in the specified period

例：mysql>select now();mysql>select date(now());mysql>select time(now());

4. Enumeration type
4.1 Select a single value from the given collection of values, ENUM.
Format: enum (value 1, value 2, value N)

4.2 Select one or more values from the given collection of values, set.
Format: Set (value 1, value 2, value N)

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

Constraint conditions
NULL allows NULL, default setting
Not NULL is not allowed to be empty
Key index Type
Default set defaults, default is NULL

例：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 name Execution action;

To perform an action:
1. Add a new field
The ADD field name type (width) constraint condition;

ADD field Name Type (width) constraint first; Add fields to the front

ADD field Name Type (width) constraint after field name 2; Add field to field 2 after

2. Modify the field type
MODIFY field Name Type (width) constraints;

3. Modify the field name
Change Source field name new field name Type (width) constraint condition;

4. Delete the specified field
DROP field name;

例：mysql>alter table ku.biao5 add >kuki   

MySQL data type and table structure