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

The following content mainly brings you the basic analysis of managing MySQL database, the knowledge mentioned here, which is slightly different from books, is summed up by professional and technical personnel in the process of contact with users, and has a certain value of experience sharing. I hope to bring help to the majority of readers.

MySQL database

=

MySQL (RHEL5/6)

MariaDB (RHEL7)

Data migration mysql-> MariaDB

-

1.MySQL database service installation:

# rpm-Q mysql-server | | yum-y install mysql-server

# service mysqld status | start | stop

# chkconfig mysqld on

two。 Main configuration file: / etc/my.cnf

Service name / process name: mysqld

Process owner / group: mysql/mysql

Default listening port number: 3306

Transport protocol: tcp

Database directory: / var/lib/mysql/

=

3. Install the mysql software downloaded from the official website to provide database services

# / etc/init.d/mysqld stop

# rpm-e-nodeps mysql-server mysql

# rpm-Q mysql-server mysql

# rm-rf / etc/my.cnf

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

# tar-xvf MySQL-5.6.rpm.tar

# rpm-Uvh MySQL-*.rpm

# service mysql start

# netstat-untlap | grep mysql

# cat / root/.mysql_secret / / initial password location

# mysql-hlocalhost-uroot-pCxifrkkA

Mysql > set password for root@ "localhost" = password ("123"); / / password needs to be changed when entering the database for the first time

Mysql > quit

# mysql-hlocalhost-uroot-p123

Mysql > show databases

=

4. The process of storing data on a database cloud server?

4.1 Connect to the database server mysql-hlocalhost-uroot-p123

4.2 Select the library use library name

4.3Create table create table...

Insert the record insert into into the table...

4.5 query record

4.6 disconnect quit

-

* * data is stored in the database directory in the form of files

* * the library or table is stored in the database directory as a file.

=

5. Basic use of database services?

The SQL command uses rules:

Commands are not case-sensitive

\ c can discard the current miswritten operation

The command must end with;

Command does not support tab key completion

Naming rules for library names:

Have uniqueness

Case-sensitive letters

You can only use numbers and letters _

Can't be pure numbers.

Do not use special characters and keywords? *.

=

6. Basic database management commands:

# mysql [- h server-u username-p password database]

# quit or exit exit

Show databases; / / View existing libraries on the server

Select database (); / / View the current library

Show tables; / / displays the existing tables under the current library

Select * from table name; / / View all records of the table

Select field name list from table name; / / View table record

Desc table name; / / View table structure

Use library name; / / switch library

Create database library name; / / create a new database

Create table [library name.] Table name (

Field name type (width) constraint

Field name type (width) constraint

Field name type (width) constraint

); / / create a table

Insert into table name values (); / / insert records into the table

Drop database library name; / / Delete existing libraries

Drop table table name; / / Delete the table

Delete from table name; / / Delete all records in the table

=

7.mysql database type

Character type (name, professional home address)

Numerical type (salary, achievement, age, height and weight)

Date time type (birthday registration time class time appointment birth year)

The value of the enumerated type set enum (gender preference) field can only be in the enumerated norm.

7.1 character type (name, professional home address)

Char fixed length 255,

Varchar becomes longer by 65532 / / opens up storage space according to how much data is stored

Used when the large text type is greater than 65535

Blob/text

-

7.2 numerical types

Integer type (salary, achievement, age, height and weight)

According to the range of stored values, it can be divided into:

Signed and unsigned

Tinyint Mini-Integer-128-127-0-255

Smallint small integer

Integer in MEDIUMINT

Int (default 11 characters) large integer-2 ^ 31 ~ 2 ^ 31-10 ~ 2 ^ 32-1

Bigint maximal integer

-

7.3 floating point (number with decimal point)

Float (mlight n) single precision

Double (mQuery n) double precision

Integer bits. Total number of decimal places

N represents total digits

M table decimal places

Decimal, where M is the significant place and D is the decimal place.

***

Note: what is the difference between the width of a numeric type and the width of a character type?

If the value is not enough to specify the width, fill in the blanks on the left.

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

1) zerofill fill in 0 instead of blanks

2) unsigned unsigned (positive)

-

7.4.1 date time type (birthday registration time, class time appointment, year of birth)

Year (1 byte occupied) year YYYY 2016

Date (4 bytes occupied) date YYYYMMDD 20161117

Time (3 bytes) time HHMMSS 172358

Datetime (8 bytes occupied) date time YYYYMMDDHHMMSS

Range: 1000-01-01-> 9999-12-31

Timestamp (4 bytes) date time YYYYMMDDHHMMSS

Range: 1970-01-01-> 2038-01-19

Exercise:

Mysql > create table T16 (name char (10), age tinyint (2) zerofill unsigned, money float (7) 2), s_year year, birthday date, up_class time, meeting datetime)

Mysql > insert into t16 values ("zhangbaoli", 21Jing 20000Jing 1998 20161221pr 083000Jet 20161218203000)

***

Note:

1): what is the difference between datetime and timestamp?

When the TIMESTAMP field is not assigned, it is automatically assigned to the current system time, while the DATETIME field is assigned to NULL.

2): year year processing?

When only 2 is used to assign a number, 01-69 is regarded as 2000-99 and 70-99 as 1970-1999.

Exercise:

Mysql > create table T17 (time1 datetime,time2 timestamp)

Mysql > insert into t17 values (20161117174928)

Mysql > insert into T17 (time1) values (20181117174929)

Mysql > insert into T17 (time2) values (20151117174929)

-

7.4.2 use the time function to obtain time to assign values to the date-time type field?

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

Year () gets the year

Date () get date

Month () gets the month

Day () acquisition date (date)

Time () get time

Sleep (N) hibernate for N seconds

Sysdate () get time

Curdate () gets the current system time

Curtime () gets the current system time

Exercise:

Mysql > select now ()

Mysql > select year (now ())

Mysql > select year (20191224)

Mysql > select date (now ())

Mysql > create table T15 (name char (10), age tinyint (2) unsigned,pay float (7Magne2), up_class time,birthday date,s_year year

Meetting datetime)

Mysql > insert into t15 values ("lili", 21, 18800.88, 093000, 20171008, 1995)

Mysql > insert into t15 values ("jerry", 28800.88 jerry, now (), now (), now ())

Mysql > insert into t15 values ("tom", 21 Magi 18800.88 time (20171224201818), date (20171224201818), year (now ()), now ()

-

7.5 the value of the enumerated type set enum (gender preference) field can only be in the enumerated norm

Selection within the perimeter:

Enum (value 1, value 2, value N) can only select one value within the range enumerated

Set (value 1, value 2, value N) can select one or more values within the range enumerated

Exercise:

Mysql > create table T21 (name char (10), age tinyint (2) unsigned,pay float (7Power2), birthday date,sex enum ("boy", "girl", "no"), likes set ("book", "film", "game", "it"))

Mysql > insert into T21 values ("bob", 21, 21000, 20161118, "boy", "book,it")

Mysql > insert into T21 values ("alic", 23, 11000, 20161218, "man", "book,it,football")

Mysql > insert into T21 values ("lucy", 18pyrrine 28000pr 20160918pr 2, "film,game")

=

Constraints: (limits how to assign values to fields)

Function: when inserting a new record into the table, limit how to assign values to the field. If you do not set constraints when creating the table, use the default settings of mysql.

Null allows null values to be assigned the default setting

Not null does not allow null values to be assigned

Key index type

Default sets the default, which defaults to null

Field additional settings: the value of the field automatically increases the field description information

Exercise:

Mysql > create table T24 (name char (10) not null, age tinyint (2) unsigned default 24, pay float (7default 2), birthday date,sex enum ("boy", "girl", "no") default "boy", likes set ("book", "film", "game", "it") default "film,game")

Mysql > insert into T24 values (null,23,11000,20161218, "man", "book,it")

Mysql > insert into T24 values ("null", 23, 11000, 20161218, "man", "book,it")

Mysql > insert into t24 values (", 23, 11000, 20161218," man "," book,it ")

Mysql > insert into T24 (name,age) vlaues ("lilei", 29)

Mysql > insert into T24 (name,pay,birthday) values ("jerry", 12000 and 20161127)

Mysql > insert into T24 (name,age,pay,birthday) values ("jerry", 31, 12000 and 20161127)

=

For the above basic analysis on the management of MySQL database, if you have more information, you can continue to follow our industry promotion. If you need professional solutions, you can contact the pre-sale and after-sale ones on the official website. I hope this article can bring you some knowledge updates.

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.