Library, table, record, related operations (1)

Database configuration

# The purpose of unified configuration through the configuration file: unified management of the server (mysqld) and client (client)
# If the encoding of mysqld (server) is configured as utf8, then the default encoding of the created database is utf8< br />
# Configuration process
# 1) In the mysql installation root directory, create a configuration file: my.ini
# The configuration file name under mac is my.cnf
< br /># 2) Set the content of the configuration file and save it
[mysqld] # Server configuration
port=3306 # You can modify the database default port (if the database port is occupied by other software)
character- set-server=utf8 # Encoding format
collation-server=utf8_general_ci # Sorting method (default follows encoding format)

[client] # mysql's own client is called [mysql], configure [client] is configured with [mysql] and other existing clients, such as Navicat visualization client
default-character-set=utf8 # encoding format

# 3) Restart the database service

database modification information

# modify character encoding
mysql>: alter database database name charset=encoding format;

User operation: focus

# Assign a user with the database operation authority to a specific database
mysql>: grant permissions on the database. Table to Username@'hostname' identified by'password';

# 1) all: all permissions
# 2) oldboy.*: all tables in the oldboy database
# 3 )[Email Protected]'localhost': This machine can be logged in by the oldboy user
# 4) identified by'Oldboy123': the password is Oldboy123 
eg>: grant all on oldboy.* to [email protected]'localhost' identified by'Oldboy123';

# 1) select,delete,update,insert,drop,alter: Specified permissions
# 2) oldboy.*: all tables in the oldboy database
# 3) [email protected]'%': Any machine can log in through the oldboy user
# 4) identified by 'Oldboy123': The password is Oldboy123
eg>: grant select,delete,update,insert,drop,alter on oldboy.* to [emailprotected]'%' identified by'Oldboy123';

# Revoke authority
mysql>: revoke authority 1, authority 2,... on database name. Table name from username@'hostname';
# Ban local oldboy users from the oldboy database Drop permissions for all tables
eg:> revoke drop on oldboy.* from [email protected]'localhost';

# Delete user
drop user username@'host Name';

table modification

# modify table name
mysql>: alter table old table rename new table;
 
# Modify field name
mysql>: alter table table name change old field and new field type (length);

# modify field attributes
mysql>: alter table Table name modify field new type (length);

complete table creation syntax

# length and constraints can be omitted in some cases 
mysql>: create table table name (
attribute name 1 type (length) constraint,
...
attribute name n type (length) Constraints
) engine=engine default charset=utf8;

The engine of the database table: the way of driving the data-database optimization

# Prerequisite: The engine is required to build tables and is provided for table use, not a database

# Show all engines
mysql> show engines; 
 
# innodb (default): support transactions, row-level locks, foreign keys
mysql>: create table t11(id int)engine=innodb;

# myisam: query efficiency Better than innodb, when you do not need to support transactions, row-level locks, foreign keys, you can optimize the database by setting myisam
mysql>: create table t12(id int)engine=myisam;

# blackhole: blackhole, the data stored in will disappear (it can be understood that no data is stored)
mysql>: create table t13(id int)engine=blackhole;

# memory: table structure It is stored on the hard disk, but the table data is all stored in the memory
mysql>: create table t14(id int)engine=memory;

database mode

# After mysql 5.7, the default is safe mode

# mysql 5.6 version
sql_model=no_engine_substitution # Non-security, default
sql_model=strict_trans_tables # Security性

# View the current database mode:
show variables like "%sql_mode%"; #% matches 0~n arbitrary characters => Fuzzy query

# Temporarily set to safe mode, the service will be reset after restarting
mysql>: set global sql_mode="strict_trans_tables"; # In the root user login state
# After setting, quit disconnects the database After connecting (the server does not restart), it will enter the safe mode.

# In safe mode, warning statements executed by SQL in non-safe mode will throw exceptions
eg>: create table t1(name char(2));
eg>: insert into t1 values ​​("ab") # Normal
eg>: insert into t1 values ​​("owen") # Error Data too long for column'name' at row 1

Data types supported by mysql

Integer

''' type 
tinyint: 1 byte, value range -128~127, default length 4
smallint: 2 bytes, value range -32768 ~ 32767, default length 6
mediumint: 3 words Section
int: 4 bytes -2147483648~2147483647
bigint: 8 bytes
'''
''' constraint
unsigned: unsigned
 zerofill: 0 fill
'''

# Build a table
mysql>: create table tb1(x tinyint, y smallint, z int(6));
 
# Insert data
mysql>: insert into tb1 values(128, 32768, 32768); # Result: 127, 32767, 32768

# Conclusion: The length of the integer is determined by The occupied byte (value range) is determined, and the length can be customized, but it does not affect the occupied byte (value range)
# The length of all integer variables is generally omitted.
Conclusion> : create table tb1(x tinyint, y smallint, z int);


# Integer constraints
mysql>: create table tb2(x tinyint unsigned); # 0~ 255
mysql>: insert into tb2 values(256), (-1); # 255, 0
 
# 0Fill constraints
mysql>: create table tb3(x tinyint unsigned zerofill);
mysql>: insert into tb3 values(10); # 010

Floating point

# Test floating point type in safe mode

''' type
float(M, D): 4 Byte, 3.4E–38～3.4E+38
double(M, D): 8 bytes, 1.7E–308～1.7E+308
decimal(M, D): where the byte is On the basis of the large value of M and D, +2, in fact, the value of M+2 is the number of bytes occupied by the decimal field.
'''
'''Width:
Limit the storage width
(M, D) => M is the number of digits, D is the decimal place, and M must be greater than or equal to D
float(255, 30): the lowest precision, most commonly used
double(255, 30): High precision and many places
decimal(65, 30): string storage, full precision
'''

# Create table:
mysql>: create table tb4 (age float(256, 30)); # Display width out of range for column'age' (max = 255)
mysql>: create table tb5 (age float(255, 31)); # Too big scale 31 specified for column'age'. Maximum is 30.
mysql>: create table tb5 (age float(65, 30)); # In a reasonable range of values
 
mysql> : create table t12 (x float(255, 30));
mysql>: create table t13 (x double(255, 30));
mysql>: create table t14 (x decimal(65, 30));
# 1.111111164093017600000000000000 
my sql>: insert into t12 values(1.11111111111111111119); 
# 1.111111111111111200000000000000
mysql>: insert into t13 values(1.11111111111111111119);
# 1.111111111111111111190000000000
mysql>: insert into t14 values( 1.11111111111111111119);

# Important: Length and Decimal Place Analysis
# Error, the total length M must be greater than or equal to Decimal Place D
mysql>: create table t14 (x decimal(2 , 3));

# Can store -0.999 ~ 0.999, the decimal places of over-length will be rounded off, 0.9994 can be stored, that is, 0.999, 0.9995 can not be stored
mysql>: create table t14 (x decimal(3, 3)); # The integer digits are 3-3, so the maximum is 0

# It can store -9.999 ~ 9.999, and the decimal places of excessive length will be rounded, 9.994 It can be stored, that is, 9.999, and 9.995 can not be stored.
mysql>: create table t14 (x decimal(4, 3)); # The integer digits are 4-3, so the maximum is 9

# Can store -99.999 ~ 99.999, the decimal places of excessive length will be rounded, 99.9994 can be stored, that is, 99.999, 99.9995 can not be stored
mysql>: create table t14 (x decimal(5, 3)); # integer Bit 5-3, so the maximum is 99

String: database optimization-char efficiency is higher than varchar

< pre>”’Type
char: fixed length, always use the set length to store data
varchar: variable length, within the set length range, variable length storage data
”’
”’Width
Limit storage width
char(4): store “a” ” ab” “abc” “abcd” all use 4 lengths, “abcde” can only store the first 4 digits (error in safe mode)
varchar(4): store “a” “ab” “abc” “abcd “Use 1, 2, 3, 4 length storage respectively, “abcde” can only store the first 4 digits (error in safe mode)

char is stored as a fixed length, if the data length changes greatly, Usually it takes more space, but access to data is based on a fixed-length operation, which is efficient. When varchar stores data, it will first calculate the length of the data to be stored, and dynamically change the length to store the data, so it is generally more space-saving, but the calculation is It takes time, so the efficiency is low.

The data length information calculated by varchar also needs to open up space for storage. It is stored in the data header (before the data starts), and it also consumes 1~2 additional words. Section
So if the data is of fixed length, or fluctuates in a small range, char will not take up more space than char, and the efficiency is high.
”’

# Build a table :
mysql>: create table ts1 (s1 char(4), s2 varchar(4));
mysql>: insert into ts1 values(‘adcde’,’xyzabc’); #’adcd’ ,’xyza’

time

'''Type
year: yyyy(1901/2155)
date: yyyy-MM- dd(1000-01-01/9999-12-31)
time: HH:mm:ss
datetime: yyyy-MM-dd HH:mm:ss(1000-01-01 00:00 :00/9999-12-31 23:59:59)
timestamp: yyyy-MM-dd HH:mm:ss(1970-01-01 00:00:00/2038-01-19 ??) 
'''

# Create table:
mysql>: create table td1 (my_year year, my_date date, my_time time);
mysql>: insert into td1 values(1666, '8888-8-8', '8:8:8'); # Time needs to be in value access

mysql>: create table td2 (my_datetime datetime, my_timestamp timestamp); 
mysql>: insert into td2 values('2040-1-1 1:1:1', '2040-1-1 1:1:1'); # The time needs to be in the value access
mysql>: insert into td2(my_datetime) values('2040-1-1 1:1:1'); # timestamp does not copy the current time of the system
 
# datetime: 8 bytes, can be null
# timestamp: 4 bytes, with a default value of CURRENT_TIMESTAMP

enumeration and collection h4>

# Enumeration and collection: provide options for a certain field-enumeration can only be single-selected (1), and collections can be multiple-selected (0-n)

# Build Table
# enum, set default value is NULL
mysql>: create table tc1 (name varchar(20), sex enum('male','female','wow'), hobbies set(' Male','Female','Wow'));
mysql>: insert into tc1 values('ruakei','Wow','Unknown'); 
 
# enum、 set manually set the default values ​​of'male' and'wow'
mysql>: create table tc2 (name varchar(20), sex enum('male','female','wow') default'male', hobbies set('male','female','wow') default'wow');
mysql>: insert into tc2 values('ruakei','wow wow','unknown'); 
 mysql>: insert into tc2(name) values('ruakei');
 
# Assigning wrong values ​​to the two fields of sex and hobbies, the system defaults to filling with empty strings (non-safe mode), safe mode Throw an exception
# If the other fields are out of the two fields sex and hobbies For assignment, these two fields will have default values.

# Note: To assign a value to a set type field, use a string, which is used inside the string, separate multiple options, and cannot Add spaces and other extra characters
mysql>: insert into tc2 values('ruakei_1','female','male, female, wow');

constraint

"""
primary key: primary key, unique identification, the table will have it, not set as the default to find the first non-empty, unique field, if not identified, create a hidden field
foreign key : Foreign key
unique: unique data, the value of this field needs to be unique and cannot be repeated

auto_increment: self-increment, can only be added to the int type field of the key, as an auxiliary modification , Only one auto-increment field can be set in a table

not null: not null-for some fields, such as the user name at the time of registration, the gender of the person born, etc., the fields under these requirements are only Cannot be set to Null, it must be assigned a value
default: default value-when assigning a value to a field with an unexpected default value, the field with the default value will be assigned a default value

unsigned: Unsigned-the stored number starts from 0
zerofill: 0 fill-when the data length is less than the length of the value range when storing an integer, it will be filled with 0 on the left of the number
"""

# not null and default limit
# Can’t be empty, x without default value must be assigned
# y and z have default values ​​when they are not assigned. After setting the value, use the default Value
mysql>: create table td1 (x int not null, y int default 0, z int default 100);

# Error, auto_increment must be set to the key field
mysql >: create table td2 (x int auto_increment);
# Error, auto_increment must be set to the int field
mysql>: create table td2 (x char(4) auto_increment);
# Error, The auto_increment field appears at most once
mysql >: create table td2 (x int unique auto_increment, y int unique auto_increment);

# Correct, primary key and unique key analysis
# x primary key: when no primary key is set, first A unique auto-increment key will be automatically promoted to the primary key
mysql>: create table td21 (x int unique auto_increment, y int unique);
# y primary key: the first one when no primary key is set The unique auto-increment key will be automatically promoted to the primary key
mysql>: create table td22 (x int unique, y int unique auto_increment);
# x primary key: the primary key is set, the primary key is not set Auto-increment, that auto-increment can be set on the unique key
mysql>: create table td23 (x int primary key, y int unique auto_increment);
# x primary key: setting the primary key is setting Yes, the primary key is auto-incremented, and there can only be one auto-increment field, so the unique key can no longer be auto-incremented.
mysql>: create table td24 (x int primary key auto_increment, y int unique);
# Default primary key: If no primary key is set, and there is no unique auto-increment key, the system will add an implicit primary key (invisible) by default.
mysql>: create table td25 (x int unique, y int unique);< br />
# Unique key: to ensure that a field, data cannot be repeated
# Primary key: is the unique identification of a record (can be understood as the number of the data)

# Union unique 
# When the port is different, the ip can be the same. When the ip is different, the port can be the same. Both are legal.
# When both the ip and the port are the same, it means duplicate data and is illegal.
mysql>: create table tu1 (ip char(16), port int, unique(ip, port));

# It can also be set as a joint primary key, the reason is the same as the joint unique
mysql>: create table tu2 (ip char(16), port int, primary key(ip, port));
# sql can be written in multiple lines
mysql>: 
create table t22(
 ip char(16),
 port int,
 primary key(ip,port)
);