MySQL database basic operations (table structure)

The relevant configuration file is automatically generated after the package start service

Initial root secret is generated automatically on first run
Initial password find grep-i in the service log file Password/var/log/mysql.log

Go in, MySQL.

Mysql-hlocalhost-uroot-p Initial Password

Set global validate_password_policy=0;
#修改密码默认等级为0
#0为长度, 1 is the letter length symbol, 2 is the letter length symbol, the dictionary file

Set global validate_password_length=6;
#修改密码默认长度要求为6

alter user [email protected] "localhost" identified by ' 123456 ';
#修改密码

command-line directives are temporarily valid, write commands to the configuration file/etc/my.cnf Permanent
Restart the service after writing is complete to take effect

################################################################
noun explanation
Db,database
A database, organized and placed into a data collection of memory according to a data model
Dbms,database Management System
Database management system, large server software for operating and managing databases
DBS, DataBase System
Server with database, database management system

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

Classification of SQL commands

DDL Data Definition Language
(Create Alter DROP)
DML Data Manipulation language
(Insert update Delete)
DCL Data Control Language
(Grant Revoke)
DTL Data Things language
(Commit rollback savepoint)

###################################################################
2 Creating a library
Create database name;
#创建库
show databases;
#查看已有库
drop database name;
#删除库
Select Database ();
#查看当前所在的库
Use library name;
#进入库

##################################################################
Build table
CREATE table library name. Table Name (
Field Name Type (width) constraints,
Field Name Type (width) constraints,
...
...
);
CREATE TABLE Gamedb.stu (
Name char (x), #字符 (10)
Age int #数值
);
Insert into library name. Table name values (value list);
#插入表记录
SELECT * from library name. Table name;
#查看表记录
Delete from library name. Table name;
#删除表记录
DESC Library name. Table name;
#查看表结构
drop table library name. Table name;
#删除表

##################################################################
MySQL data type
Numeric: shaped, floating-point
Integral type: According to the range of the stored value of the integer type is also divided into:
Type tinyint smallintmediumintintbigint
Use small medium Large size

Size 1 bytes 2 bytes 3 bytes 4 bytes 8 bytes

are divided into signed unsigned, the range is:

Signed -128~27-32768~2767-2^23~2^23-1 -2^31~2^31-1 -2^63~2^63-1

unsigned 0~255 0~65535 0~2^24-1 0~2^32-1 0~2^64-1

When created with the unsigned adornment, the corresponding field only holds positive numbers

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

Fill in 0 instead of space when using Zerofill

#例如: Age int (3)

Insert in to a values (3)

The database is displayed as 003

Error when value is out of range

Floating point: According to the storage range is divided into single-and double-precision:
Single precision Float (n,m) 4 bytes
Double (n,m) 8 bytes

#n表示总位数, M represents the number of digits in the decimal place

#例如: Float (5,2) max 999.99 min to 999.99

The width of a numeric type is the size at which the display width cannot be limited to the assignment of the field, and the size is determined by the field type.

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

Character type:

Fixed length: char (number of characters)

#最大长度255字符, not enough to specify the number of characters on the right with a space, the number of characters broken beyond the data can not be written.

Variable length: varchar

#按数据实际大小分配存储空间, the data cannot be written when the number of characters is exceeded.

Large text type: Text/blob

#字符数大于65535存储时使用.

The use of char in the actual production environment is relatively large because varchar consumes a portion of the CPU resources to calculate the storage size.

Date-Time Type
Year YYYY 2017

#year默认用4为数字表示, 01~69 is considered 2000~2069 when only 2 digits are assigned

70~99 when the 1970~1999

Date YYYYMMDD20171220
Time HHMMSS155145
Date Time
DateTime YYYYMMDDHHMMSS
Timestamp YYYYMMDDHHMMSS
DateTime is displayed as empty when no value is assigned to him
Timestamp when no value is assigned to him, the current time of the system is used

###################################################################
Now ()
#当前系统的时间
Year ()
#获取数据中的年
Day ()
#获取数据中的天
Date ()
#获取数据中的日期
Time ()
#获取数据中的时间

###################################################################
Enum type: The value of the field can only be selected within the enumerated range
Field name enum (Value list)
#单选, select a single value from the given collection of values.
Field Name set (Value list)
#多选, select one or more values from the given collection of values.

###################################################################
Set constraints on a field: how the action limit assigns a value to a field.
Null is allowed to be null not allowed after type is not Null
Default setting defaults, the default is null type followed by the default

###################################################################
Modify Table Structure
mysql> ALTER TABLE name execution action;

Add field Name Type (width) constraint,
#添加新字段, the default is last.
Add field Name Type (width) constraint first;
#所有字段的前面
Add field Name Type (width) constraint after field name;
#在什么字段后面添加
Drop field name;
#删除字段
Modify field type (width) constraints
#修改字段类型
Change original field name new field name Type (width) constraint
#修改字段名
ALTER TABLE name rename new table name;
#修改表名

MySQL database basic operations (table structure)