MySQL Basics (i)

####################################################################################
What is a database:
Db,database
Database: A data set that is organized and placed into memory according to a data model.
Dbms,database Management System
--"Database management system: a large-scale service software for manipulating and managing databases."
Dbs,database System
--"Database management system: Db+dbms, refers to a computer system with a database and integrated database management software."

Common database software?

relational database software service software (RDBMS):
Oracle (Oracle): Oracle (Commercial software), Mysql
Microsoft (Microsoft): SQL Server (Commercial software)
IBM:DB2 (Commercial software)
Non-relational database service software (NoSQL): (key-values Key-value pair type)
Redis MongoDB

Features of MySQL:
--"applicable to small and medium-sized, relational database
--"cross-platform
--"Support python/java/perl/php and other languages via API

Application Environment:
LAMP LNMP

#####################################################################################################

First, build the database server:
Pack: Mysql
Rpm-qa | Grep-i MySQL

To modify a configuration file:
#ls/etc/my.cnf//master configuration file
#ls Storage directory for/VAR/LIB/MYSQLD//databases
#cat/var/log/mysqld.log//day to File

Start the service:
#systemctl Start mysqld

To view the service health status:
#netstat-LNPT | grep:3306//Port number: 3306
#ps-C mysqld
#systemctl Status Mysqld

Log in to the database using the initial password:
#grep Password/var/log/mysqld.log
#mysql-hlocalhost-uroot-p Password//Initial password if you have special characters, you should use single-citation

Second, after entering the database
Modify Login Password:
#show databases;//Prompt to change password to use
#alter user [email protected] identified by123456;//prompt password does not meet security requirements
#set Global validate_password_policy=0; Set validation length only
#set Global validate_password_length=6; Set length default value is 8 characters
#alter user [email protected] identified by123456//Setup succeeded
#quit//exit

Verify:
#mysql-hlocalhost-uroot-p123456//Login successful.

To permanently set the password Authentication policy:
#vim/etc/my.cnf
[MySQL]
Validate_password_policy=0
Validate_password_length=6

#####################################################################################

Third, the use of SQL command rules:
--"command is case insensitive (except for variables, passwords)
--"Each command ends with a semicolon
--"Does not support tab completion
---"\c can discard the current editing error operation command

Iv. Basic management of the database:
Library Management:
#mysql-uroot-p123456
#show databases; View existing libraries
#create database library name;//Create Library
#drop database name;//Delete Library
#use library name;//Use Library
#select database ();//view your current library
#show tables;//view existing tables in the current library

Build table:
CREATE table library name. Table Name (
Field Name Type (width) constraints,
Field Name Type (width) constraints,
...
);
CREATE TABLE Gamedb.stuinfo (
Name Char (10),
Age int
);

To view the table structure:
DESC Library name. Table name;

Insert Table record:
Insert into library name. Table name values (value list);
INSERT into Gamedb.stuinfo values ("DCC", 23);

View table records:
SELECT * from library name. Table name;

To delete a table record:
Delete from library name. Table name;//delete all records in table, but table is still

To delete a table:
Drop library name. Table Name

；

#####################################################################################################

V. MySQL data type

What types of data are supported?
Numeric type character type Date Time type enum type

1, Numeric type: integer, floating-point type
The range integer type that stores the values is divided into:
Tinyint 1-byte -128~127 (signed) 0~255 (unsigned)
SmallInt 2 bytes-32768-32767 0~65535
Mediumint 3 bytes
int 4 bytes
BigInt 8 bytes
Rule: N- -1,n with unsigned range 2 indicates bits, 1 bytes is 8 bits
Depending on the range of stored values floating point types are divided into:
Single precision Float (n,m) 4 bytes
Double-precision Float (n,m) 8 bytes
where n represents the total number of digits
M represents the number of decimal digits

CREATE TABLE T1 (
Pay float (5,2)
）；

Inserte into T1 values (199.99);//total number of digits is 5 digits, 2 digits after decimal point

Attention:
Use unsigned to indicate the use of unsigned numbers
Automatically complements decimal digits when an integer is entered
Width is only the display width, and the size of the stored value is determined by the type
Value out of range, error

￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥

2. Character Type:
Fixed length: char (number of characters)
--"Maximum length 255
--"Not enough to specify the number of characters on the right with a space
--"The number of characters exceeds the setting and cannot be written

Variable length: varchar (number of characters)
--"Allocate storage space according to actual size
--"The number of characters exceeds the setting and cannot be written

Large text type: Text/blob
--use when the number of characters is more than 65535 stored

￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥

3. Date and Time type:
Year YYYY 2017
Date YYYYMMDD 20171220
Time HHMMSS 155145

Date Time
DateTime YYYYMMDDHHMMSS
Timestamp YYYYMMDDHHMMSS

What is the difference between datetime and timestamp?
Automatically assigns a value to the current system time when no value is assigned to the timestamp field
When a datetime field is not assigned a value, the default assignment is null

Use a 2-digit number to assign a value to the year field?
01~69 to 2000~2069
70~99 to 1970~1999

Use the time function to assign a value to a date-time Type field.
Now (): Current Time year (): Month of current time month (): Current time
Day (): Days of the current time date (): Date of the current time (): Hour of the current time

￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥

4. Enumeration type:
Enum: Take a single value format from a given set of values: Enum (value 1, value 2, value N)
Set: Takes one or more value formats from a given collection of values: Set (value 1, value 2, value N)

#########################################################################################

Vi. Conditions of constraint:
Null: Allow null, default setting
NOT NULL: bit null is not allowed
Key: Index Type
Default: Set defaults, default to NULL

CREATE TABLE t3 (
Name Char (ten) NOT NULL,//field name is Name, type is character type, width is 10, setting cannot be null
Age tinyint unsigned default 23//field named Age, type is a small integer, unsigned (cannot be negative), defaults to 23
);

desc t3;//view table structure
Insert into t3 (name) VALUES ("DCC");//INSERT, assign a value to the Name field only
select * from T3;//result, the Age field takes the default value of 23

####################################################################################

Seven: Modify table structure:
ALTER TABLE name execution action;

1. Add a new field:
Add field type (width);
The Add field type (width) constraint condition;
Add field type (width) constraint first;//INSERT INTO column one
Add field type (width) constraint after field name;//after inserting into the specified field

2. Delete fields:
Drop field name;

3. Modify the field type:
Modify field type (width) constraints;

4. Modify the field name:
Change field the new field type (width) constraint;//Modify to maintain the original type (width) constraint condition.

Modify Table Name:
ALTER TABLE name rename new table name;

################################################################################################

MySQL Basics (i)