MySQL installation and basic Application 01/16 Update SLTechnology News&Howtos

MySQL installation and basic Application

2025-01-16 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

MySQL

Build MySQL server-> CPU, memory, hard disk (storage)

Install MySQL

Yum-y install perl-Data-Dumper perl-JSON perl-Time-HiRes / / install dependency files

Rpm-Uvh mysql-community-*.rpm / / Source package installation

Rpm-qa | grep-I mysql / / View the post-installation status

Configuration file

Ls / etc/my.cnf

Start the service

Systemctl status mysqld

Ps-C mysqld

Netstat-utnalp | grep: 3306

Data directory syst

Ls / var/lib/mysql/

Grep mysql / etc/passwd View the owner's group

Modify the table

Modify MySQL password

# ls / var/log/mysqld.log

# grep password / varlog/mysqld.log View the initialization password of the local database

# rpm-qf / usr/bin/mysql

# mysql-hlocalhost-uroot-p 'password'

Mysql > set global validate_password_policy=0; / / change password to verify length only

Query OK, 0 rows affected (0.00 sec)

Mysql > set global validate_password_length=6; / / modify password length to 6

Query OK, 0 rows affected (0.00 sec)

Mysql > alter user user () identified by "123456" / / change login password

Use permanent Profil

Vim / etc/my.cnf

Validate_password_policy=0

Validate_password_length=6

The process of storing data on a database server

1. Connect to the database server

The client provides its own connection tools (graphics command line)

-- use mysql on the command line

2. Create a library (folder)

Create a library

-- Library names are available with numbers, letters, and underscores

-- can not be pure numbers, keywords, special symbols

Create database library name

View existing libraries

Show databases

Delete Library

Drop database library name

Switching library

Use library name

Check the existing tables in the library

Show tables; / / table, equivalent to system files

View the current library

Select database ()

3. Create a table (file)

Create table library name. Table name (

Field name character type

Field name numeric type

. Name char (10)

. Age int

)

Insert table record

Insert into library name. Table name values (values list)

View table structure

Describe table name

View table records

Select * from library name. Table name

Delete table record

Delete from library name. Table name

Delete tabl

Drop table

Mysql data type

What are the supported data types?

-- numerical type: weight, height, performance, salary

-- character type: name, work unit, mailing address

Enumerated: hobbies, gender

Date and time type: date of birth, time of registration

Numeric types: integer, floating point

Depending on the range integer type of the stored value, the type is:

Tinyint smallint mediumint int bigint

Unsigned unsigned

Floating-point type: divided according to the range of stored values

Single precision (nMagne m) double precision (nMagne m)

N represents total digits

M represents the number of decimal places

Pay float (5par 2)

Up to 999.99

Minimum-999.99

Mysql > create table T1 (id tinyint unsigned zerofill)

Query OK, 0 rows affected (0.40 sec)

Mysql > desc T1

+-- +

+-- +

| | id | tinyint (3) unsigned zerofill | YES | | NULL |

+-- +

1 row in set (0.00 sec)

Mysql > create table T2 (pay float (7penny 2))

Query OK, 0 rows affected (0.46 sec)

Mysql > desc T2

+-+ +

+-+ +

| | pay | float (7Page2) | YES | | NULL | |

+-+ +

1 row in set (0.00 sec)

Mysql > create table T4 (

-> age float (7 and 2)

-> high float (3pr 2)

->)

Query OK, 0 rows affected (0.36 sec)

Mysql > desc T4

+-+ +

+-+ +

| | age | float (7Page2) | YES | | NULL | |

+-+ +

2 rows in set (0.00 sec)

Insert into T4 values (11211 dint 1.82)

# # #

Character type

-fixed length: char (number of characters)

Maximum length of 255 characters

Fill in the space on the right if it is not enough to specify the number of characters.

Unable to write data when the number of characters exceeds

-varchar (number of characters)

Allocate storage space according to the actual size of the data

Unable to write data when the number of characters exceeds

Large text type: text/blob

Large number of characters and used for 65535 storage

Mysql > create table T8 (

-> name char (10)

-> class char (7)

Address char (15)

> mail varchar (30)

->)

Mysql > desc T8

+-+ +

+-+ +

+-+ +

4 rows in set (0.00 sec)

Mysql > insert into T8 values ("jim", "nsd1709", "beijing", "123456@qq.com")

Query OK, 1 row affected (0.04 sec)

Mysql > select * from T8

+-+

+-+

| | jim | nsd1709 | beijing | 123456@qq.com | |

+-+

1 row in set (0.00 sec)

Date time type:

Year YYYY 2017

Date date YYYYMMDD 20171220

Time time HHMMSS 155302

Date and time:

Datetime YYYYMMDDHHMMSS

Timestamp YYYYMMDDHHMMSS

Mysql > create table T9 (

-> name char (10)

-> age tinyint

-> s_year year

-> uptime time

-> birthday date

-> party datetime

->)

Query OK, 0 rows affected (0.37 sec)

Mysql > desc T9

+-+ +

+-+ +

| | age | tinyint (4) | YES | | NULL |

+-+ +

6 rows in set (0.00 sec)

Mysql > insert into T9 values ("Tom", 1992, 073000, 19920221122020, 20180131122100)

Query OK, 1 row affected, 1 warning (0.04 sec)

Mysql > select * from T9

+-+ +

+-+ +

| | Tom | 24 | 1992 | 07:30:00 | 1992-02-21 | 2018-01-31 12:21:00 |

+-+ +

1 row in set (0.00 sec)

# # #

Time function

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

Dynamically get the system date and time when sysdate () executes

Sleep (N) hibernate for N seconds

Curdate () gets the current system date

Curtime () gets the current system moment

Month () gets the month in the specified time

Date () gets the date in the specified time

Time () gets the time in the specified time

Mysql > select from T9

+-+ +

+-+ +

| | Tom | 24 | 1992 | 07:30:00 | 1992-02-21 | 2018-01-31 12:21:00 |

| | Jerry | 25 | 1991 | 06:50:55 | 1991-08-19 | 2018-01-31 12:21:00 |

+-+ +

2 rows in set (0.00 sec)

Mysql > insert into T9 values ("kenji", 19 year (now ()), time (now ()), date (now ()), now ())

Query OK, 1 row affected (0.04 sec)

Mysql > select from T9

+-+ +

+-+ +

| | Tom | 24 | 1992 | 07:30:00 | 1992-02-21 | 2018-01-31 12:21:00 |

| | Jerry | 25 | 1991 | 06:50:55 | 1991-08-19 | 2018-01-31 12:21:00 |

| | kenji | 19 | 2017 | 03:55:12 | 2017-12-20 | 2017-12-20 03:55:12 |

+-+ +

3 rows in set (0.00 sec)

# # #

Enumeration type: the value of a field can only be selected within the range of the list

The field name enum (values list) can only select one value, which can be selected by a number when assigning a value.

Field name set (values list) multiple selection

Mysql > create table T12 (name char (10), sex enum ("boy", "girl"), yourlikes set ("book", "film", "game", "study"))

Query OK, 0 rows affected (0.43 sec)

Mysql > desc T12

+-- +

+-- +

| | sex | enum ('boy','girl') | YES | | NULL |

+-- +

3 rows in set (0.00 sec)

Mysql > insert into T12 values ("ZhouMing", "boy", "book,film")

Query OK, 1 row affected (0.04 sec)

Mysql > select * from T12

+-+

+-+

+-+

1 row in set (0.00 sec)

# # #

Constraints: action limit assignment

-- Null is allowed to be empty. Default setting

-- NO NULL cannot be empty

Key index type

Default sets the default, which defaults to NULL

Mysql > create table T13 (name char (10) not null, sex enum ('man','woman') not null default "man", age tinyint not null default 23)

Query OK, 0 rows affected (0.37 sec)

Mysql > desc T13

+-+ +

+-+ +

| | sex | enum ('man','woman') | NO | | man |

| | age | tinyint (4) | NO | | 23 | |

+-+ +

3 rows in set (0.00 sec)

Mysql > insert into T13 (name) values ("chihiro")

Query OK, 1 row affected (0.04 sec)

Mysql > select * from T13

+-+

| | name | sex | age | |

+-+

| | chihiro | man | 23 | |

+-+

1 row in set (0.00 sec)

# # #

Modify table structure

Mysql > alter table table name execute action

Add a new field

-add field (width) constraint

-add field (width) constraint first; / / added to the front of the table

-add field (width) constraint after field name; / / added after the specified field name

Delete a field

-drop field name

Modify field type

-modify field type (width) constraint; / / cannot be modified to a type that conflicts with an existing value

Modify field name

-change source field name new field name type (width) constraint

Modify table name

Alter table source table name rename 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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.