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

1 set up a database server

2 MYSQL data type

3 modify the table structure

1 set up a database server

1.1 in the presence of mariadb:

# systemctl status mariadb

# systemctl stop mariadb

# rpm-e-nodeps mariadb-libs # No dependency unloading

# rm-rf / etc/my.cnf

# rm-rf / var/lib/mysql/*

1.2 when newly installed

[root@ser51 ~] # yum-y install perl-Data-Dumper perl-JSON

[root@ser51 ~] # rpm-Uvh mysql-community-*.rpm

# perform an upgrade regardless of whether the software exists or not

[root@ser51 ~] # rpm-qa | grep-I mysql

1.3 start the service

Service name: mysqld

Port number: 3306

Main configuration file: / etc/my.cnf

Database directory: / var/lib/mysql starts the service, and files are generated by data initialization

Log file: / var/log/mysqld.log

Service script: / usr/lib/systemd/system/mysqld.service

1.4 connect to the database

[root@ser51 ~] # which mysql

/ usr/bin/mysql

[root@ser51 ~] # rpm-qf / usr/bin/mysql # asks which package the file belongs to

Mysql-community-client-5.7.17-1.el7.x86_64

[root@ser50] # mysql-u root-p password

[root@ser50 ~] # mysql-h server-u user name-p password database

View the initial login password:

[root@ser51 ~] # grep password / var/log/mysqld.log

Login:

[root@ser51 ~] # mysql-uroot-pendant 5transferwG) DluZ) # Q' # Special symbols with single quotation

1.5 temporary login takes effect

You need to reset your password to view database information the first time you log in

Set the global password authentication policy:

Mysql > set global validate_password_policy=0

Set the password length of the global variable:

Mysql > set global validate_password_length=6

Mysql > alter user root@ "localhost" identified by "123456" # change password

1.6 Global permanent effect

[root@ser51 ~] # vim / etc/my.cnf

[mysqld]

Validate_password_policy=0

Validate_password_length=6

1.7 Database usage commands

SQL directive:

DDL: data definition language

DML: data manipulation language

DCL: data control language

DTL: data transaction language

Mysql > select database (); # View the current library:

Mysql > create database library name; # create database

Mysql > use library name; # switch database

Mysql > system ls / var/lib/mysql # do not exit the operation linux command

Mysql > drop database database name; # Delete database

Create a tabl

Mysql > create table library name. Table name (field name 1 field type (width) constraint,.)

Mysql > alter table table name add field type (width); # add table field

Mysql > drop table library name. Table name # Delete table

Mysql > describe Table name # View fields in the table (desc)

Mysql > insert into table name values ()

Mysql > select * from table name

Mysql > update table name set field = "new value" where field = "value"

2 MYSQL data type

2.1 Numeric type

Tinyint 1 byte-128 '127 0' 255

Int 4 byt

Float 4-byte float (mQuery n) m: total places n: decimal places

Double 8 byt

2.2 character types

Fixed length: the default width of char (255) is 1.

Fill in spaces on the right when not enough to specify the number of characters (it will waste disk space)

Variable length: varchar allocates storage space according to the actual size of the data (it consumes CPU resources and asks for the amount of storage space needed each time)

Large text type: text/blob

The difference between the width of a numeric type and that of a character type:

Data cannot be written if the character type exceeds the character type

The width of the numeric type is the display width, and the size of the assignment to the field cannot be controlled. The size is determined by the type itself.

Case: width of int type

Mysql > create table db1.t10 (id int zerofill,pay int (3) zerofill)

# fill with 0 if there are not enough bytes

Mysql > insert into T10 values (25Penny 25)

Mysql > select * from T10

+-+ +

| | id | pay |

+-+ +

| | 0000000025 | 0.25 |

+-+ +

2.3 date and time type

Year: year YYYY

Date: date YYYYMMDD

Time: time HHMMSS

Date time: datetime/timestamp YYYYMMDDHHMMSS

Datetime occupies 8 bytes

Timestamp occupies 4 bytes

Difference: when no separate values are assigned to the two, timestamp is automatically assigned to the current system time, while datetime defaults to NULL

Case study:

Create table T15 (

Meetting datetime

Partty timestamp

);

Insert into T15 values (now (), now ())

Insert into T15 (meetting) values (20171020091828)

Insert into T15 (partty) values (20191020091828)

Select * from T15

2.4 time function

Now () gets the system date and time when this function is called

Year () date () time () day () month ()

Mysql > select date (now ()); # functions can be nested

Year processing of Year: 4 digits are used by default

When the number is 2 digits, 01'69 is 2000 '2069 70' 99 is 1970'99.

2.5 enumerated types

Fields can only be selected within the specified range

Format:

Field name enum (value 1, 2, value N) radio

Field name set (value 1, value 2, value N) multiple selection

Case study:

Create table T21 (name char (10), sex enum ("boy", "girl")

Likes set ("film", "game", "book", "food"))

Mysql > insert into T21 values ("tom", "boy", "film,game")

Mysql > insert into T21 values ("hai", 2, "film,game"); # can be expressed as a number

2.6 constraint condition

Purpose: restrict how fields are assigned

NULL: empty, not set to empty by default NOT NULL: null is not allowed

Key: index type

Default: default settin

Extra: extra settin

Case: view constraints (desc)

Mysql > create table T22 (name char (10) not null)

-> sex enum ("boy", "girl") default "boy"

-> age tinyint (2) unsigned not null default 18

-> likes set ("game", "food", "book", "music") not null

Default "game,book")

Mysql > insert into T22 (name) values ("tom")

# No value is written in other fields, use default

Mysql > insert into T22 values ("jom", 1Jing 20, "game,music")

Note: the difference between "null" and "" and null

Mysql > insert into T22 (name) values ("null")

Query OK, 1 row affected (0.04 sec)

Mysql > insert into T22 (name) values (null)

ERROR 1048 (23000): Column 'name' cannot be null

Mysql > insert into T22 (name) values (")

Query OK, 1 row affected (0.03 sec)

3 modify the table structure

Alter table library. Table execution action

3.1 add a new field

Add field name type (width)

Add field name type (width) constraint

The add field name type (width) constraint first; # is added in the first

Add field name type (width) constraint after field name; # in. Add later

Add field name type (width), add field name type (width); # add multiple

Case study:

Mysql > alter table T21 add mail char (20); # added at the end of the field by default

Mysql > alter table T21 add id int (4) not null default 0001 first

Mysql > alter table T21 add class int (4) not null default 1710 after name

3.2 Delete existing fields

Drop field name

3.3 modify field type

Modify field name type (width) constraint

3.4 modify field name

Change original field name new field name type (width) constraint

3.5 modify the table name

Alter table old table name rename (to) new table name

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.