Tips for installation and simple use of mysql 1 01/19 Update SLTechnology News&Howtos

Tips for installation and simple use of mysql 1

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

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

# mysql_install_db-user=mysql-datadir=/var/lib/mysql initialization data

Vncviewer 172.40.50.117:6000

Memory cpu hard disk

# yum grouplist

Mysql (3306)

1. Use the rpm package packaged on the CD with rpm

[root@host ~] # yum-y install mysql-server

[root@host ~] # rpm-Q mysql-server (check for installation and version number)

Mysql-server-5.1.73-5.el6_6.x86_64

[root@host ~] # yum list | grep mysql-server

Mysql-server.x86_64 5.1.73-5.el6_6 @ abc

[root@host ~] # service mysqld start

Initialize the MySQL database: WARNING: The host 'host.tedu.cn' could not be looked up with resolveip.

[root@host ~] # service mysqld status

Mysqld (pid 21119) is running...

[root@host ~] # chkconfig mysqld on

[root@host] # chkconfig-- list mysqld

Mysqld 0: off 1: off 2: enable 3: enable 4: enable 5: enable 6: close

[root@host ~] # grep mysql / etc/passwd

Mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/bash

/ var/lib/mysql (database directory) directories and files users and groups are all mysql

/ etc/my.cnf main configuration file

/ var/log/mysqld.log (log file)

/ var/run/mysqld/mysqld.conf (pid path)

[root@proxe ~] # which mysql

/ usr/bin/mysql

[root@proxe ~] # rpm-qf / usr/bin/mysql

Mysql-5.1.73-5.el6_6.x86_64

Lower version:

Both the process name and service name are mysqld

Transport protocol tcp

The rmp wrapper process owner and group is the user mysql

Lower version:

(connect by yourself:) mysql-hlocalhost-uroot-p (local login can also be mysql)

(others practice:) # mysql-h ip

two。 Download and install from the official website (first delete the previous rpm installation)

1) remove the previously installed

[root@host ~] # service mysqld stop

Stop mysqld: [OK]

[root@host] # rpm-e-- nodeps mysql-server mysql (--nodeps ignores dependencies, that is, the dependency package does not uninstall the mysql connection tool because it will be installed below)

[root@host ~] # rm-fr / etc/my.cnf

[root@host ~] # rm-fr / var/lib/mysql/*

2) unpack and install the package under the official website

[root@host ~] # tar-xvf MySQL-5.6.rpm.tar

MySQL-shared-5.6.15-1.el6.x86_64.rpm

MySQL-devel-5.6.15-1.el6.x86_64.rpm

MySQL-embedded-5.6.15-1.el6.x86_64.rpm

MySQL-test-5.6.15-1.el6.x86_64.rpm

MySQL-server-5.6.15-1.el6.x86_64.rpm

MySQL-shared-compat-5.6.15-1.el6.x86_64.rpm

MySQL-client-5.6.15-1.el6.x86_64.rpm

[root@host ~] # rpm-Uvh MySQL-*.rpm (because only the main configuration file is deleted above, and all dependent packages of the mysql-server package are still there, so large U is directly upgraded)

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

MySQL-test-5.6.15-1.el6.x86_64

MySQL-server-5.6.15-1.el6.x86_64

MySQL-client-5.6.15-1.el6.x86_64

MySQL-embedded-5.6.15-1.el6.x86_64

MySQL-shared-5.6.15-1.el6.x86_64

Perl-DBD-MySQL-4.013-3.el6.x86_64

MySQL-devel-5.6.15-1.el6.x86_64

MySQL-shared-compat-5.6.15-1.el6.x86_64

[root@host ~] # service mysql start

Starting MySQL.. SUCCESS!

[root@host ~] # netstat-anptu | grep: 3306

Tcp 0 0: 3306: * LISTEN 21509/mysqld

[root@host t] # which mysql

/ usr/bin/mysql

[root@host t] # rpm-qf / usr/bin/mysql

MySQL-client-5.6.15-1.el6.x86_64

[root@host t] # rpm-Q MySQL-client (this is the name of the high-version mysql package)

MySQL-client-5.6.15-1.el6.x86_64

3) higher version: there will be a password, which is required to enter under / root/.mysql_secret.

[root@host ~] # cat / root/.mysql_secret

# The random password set for the root user at Wed Feb 22 07:54:48 2017 (local time): 1eFb6CeJ

[root@host] # mysql-hlocalhost-uroot-p1eFb6CeJ

Mysql > set password for root@ "localhost" = password ("123")

Query OK, 0 rows affected (0.03 sec)

[root@proxe mysql] # ls

Auto.cnf ib_logfile1 performance_schema RPM_UPGRADE_HISTORY

Ibdata1 mysql proxe.tedu.cn.err RPM_UPGRADE_MARKER-LAST

Ib_logfile0 mysql.sock proxe.tedu.cn.pid test

[root@proxe mysql] # mysql-hlocalhost-uroot-pHYwSHnwN

Warning: Using a password on the command line interface can be insecure.

ERROR 2002 (HY000): Can't connect to local MySQL server through socket'/ tmp/mysql.sock' (2)

[root@proxe mysql] # ln-s / var/lib/mysql/mysql.sock / tmp/mysql.sock or create such a file here

High version:

Service name mysql

Process name mysqld (

Http Transport Protocol: tcp

Wrapped by rpm, the process owner and all groups are mysql

Database directory: / var/lib/mysql

Main configuration file: / etc/my.cnf

/ var/run/mysqld/mysqld.conf main configuration file

Data migration

Mysql-> mariadb

It is difficult to use relational database mysql.

Non-relational database redis simple username password

Using database services

Start

Stop it

Status

Connect

DDL is to delete table, delete database, build table, build database.

In the form of a file in the database directory / var/lib/mydql

DB (/ var/lib/mysql)

DBS (installs database and operating system)

DBMS (mysql)

Emurr data model

Character type: char varchar blob text

Numeric type: int tinyint smallint

Date time type: year datetime datetime

Enumerated type enmu (single option)

Set (multiple)

Characters:

Char 255fixed length does not mean default is (1)

The length of varchar 65532 must refer to

Large text type blob / text do not refer to

Numerical value:

Integer tiny × × (- 128-127 0-255) tinyint default signed tinyint unsigned (unsigned)

Small × × medium integer type large × × maximum integer type

Floating-point float (single precision) double (double)

Positive integer 12

Negative integer-12

Int (2) shows that the width defaults to 11 bits, which will be filled if not so many bits. Zerofill

Date and time:

Year year YYYY 2017-2155

Date date YYYYMMDD 20170223

Time time HHMMSS 165425

Datetime / timestamp

YYYYMMDDHHMMSS

Basic exercises:

[root@host] # mysql-uroot-p123

Mysql > show databases

Mysql > use yy

Mysql > select database ()

Mysql > create database y

Mysql > create table T1 (

-> name char (4)

->)

Mysql > desc T1

Mysql > insert into T1

-> values

-> ("tom"

->)

Mysql > select * from T1

Mysql > delete from T1

Mysql > drop table T1

Mysql > drop database y

Operation on the table:

Character type (name, home address)

Char fixed length desc T2

+-+ +

+-+ +

+-+ +

2 rows in set (0.00 sec)

Mysql > insert into T2

-> values

-> ("tom", "zhangh")

ERROR 1406 (22001): Data too long for column 'name' at row 1

Mysql > insert into T2 values ("t", "zhangh")

ERROR 1406 (22001): Data too long for column 'home' at row 1

Mysql > insert into T2 values ("t", "zhang")

Query OK, 1 row affected (0.11 sec)

Mysql > select * from T2

+-+ +

| | name | home |

+-+ +

| | t | zhang |

+-+ +

Character type: the maximum length of char is no more than 255characters. The default is not 1. You cannot enter a value for a specified character but can be less than it.

The maximum length of varshar is not more than 65532 characters. By default, it does not mean to report an error. You cannot enter a value for a specified character but can be less than it.

When the data is relatively large, use blob, and text, and use double quotation when entering character data.

Integer type

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

Signed and unsigned

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

Smallint

MEDIUMINT

Int

Bigint

Int (2) shows that the width defaults to 11 bits, which will be filled if not so many bits. Int (2) zerofill

By default, the signed value of tinyint is-128 murmur127, and the unsigned value is 0, which requires unsigned (no negative number).

When it comes to decimals, they are rounded up.

Mysql > create table T4 (

-> level tinyint)

Query OK, 0 rows affected (0.76 sec)

Mysql > insert into T4 values

Query OK, 1 row affected (0.05sec)

Mysql > insert into T4 values (- 170)

ERROR 1264 (22003): Out of range value for column 'level' at row 1

Mysql > insert into T4 values (17.54)

Query OK, 1 row affected (0.05sec)

Mysql > insert into T4 values (17.44)

Query OK, 1 row affected (0.08 sec)

Mysql > select * from T4

+-+

| | level |

+-+

| | 127 |

| | 18 |

| | 17 |

+-+

3 rows in set (0.00 sec)

Mysql > create table T7 (age int (3) zerofill); int (may not be written by default)

Mysql > insert into T8 value (1.5)

Query OK, 1 row affected (0.08 sec)

Mysql > insert into T8 value (2.8)

Query OK, 1 row affected (0.08 sec)

Mysql > insert into T8 value (3.2)

Query OK, 1 row affected (0.10 sec)

Mysql > insert into T8 value (5452)

Query OK, 1 row affected (0.05sec)

Mysql > insert into T8 value (54)

Query OK, 1 row affected (0.03 sec)

Mysql > select * from T8

+-+

| | age |

+-+

| | 002 |

| | 003 |

| | 5452 |

| | 054 |

[int followed by zerofill given display width of 3 will be filled with 0 if it is not reached]

Float (nMagne m) floating point type

Mysql > create table T9 (age float (7penny 2))

Query OK, 0 rows affected (.56 sec)

Mysql > insert into T9 value (12345.1)

Query OK, 1 row affected (0.04 sec)

Mysql > insert into T9 value (12345)

Query OK, 1 row affected (0.03 sec)

Mysql > insert into T9 value (15)

Query OK, 1 row affected (0.03 sec)

Mysql > insert into T9 value (15.215)

Query OK, 1 row affected (0.23 sec)

Mysql > insert into T9 value (- 15.215)

Query OK, 1 row affected (0.03 sec)

Mysql > select * from T9

+-+

| | age |

+-+

| | 12345.10 |

| | 12345.00 |

| | 15.00 |

| | 15.21 |

| |-15.21 |

+-+

What is the difference between the width of a numeric type and that of a character type?

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.

Date time type (birthday registration time entry time)

Year YYYY 2016

01-69 20XX

70-99 19XX

00 0000

Date date YYYYMMDD 20161219

Time time HHMMSS 144518

Date time (appointment time)

Datetime / timestamp (when the previous time value is set, it will automatically take the current value)

YYYYMMDDHHMMSS

20170214183018

The difference between datetime and timestamp?

When a field of type timestamp is not assigned a value is assigned to the field with the current time of the system.

Create table T16 (

Time1 timestamp

Time2 datetime

);

+ =

Mysql > create table T17 (time1 year, time2 date, time3 time, time5 datetime, time6 time)

Query OK, 0 rows affected (0.70 sec)

Mysql > insert into T17 value (2017, 20160721, 141223, 20170721151515151615)

Query OK, 1 row affected (0.04 sec)

Mysql > select * from T17

+-+

+-+

| | 2017 | 2016-07-21 | 14:12:23 | 2017-07-21 15:15:15 | 15:16:15 |

+-+

1 row in set (0.00 sec)

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

+-- +

+-- +

+-- +

Mysql > insert into T19 values (20171219103003 20160521022223)

Query OK, 1 row affected (0.03 sec)

Mysql > select * from T19

+-+ +

| | time1 | time2 |

+-+ +

| | 2017-12-19 10:30:03 | 2016-05-21 02:22:23 |

+-+ +

1 row in set (0.00 sec)

Mysql > insert into T19 (time1) value (20190512131452)

Query OK, 1 row affected (0.11 sec)

Mysql > select * from T19

+-+ +

| | time1 | time2 |

+-+ +

| | 2017-12-19 10:30:03 | 2016-05-21 02:22:23 |

| | 2019-05-12 13:14:52 | NULL |

Mysql > create table T20 (name char (10), age int, tim1 time, tim2 date)

Mysql > insert into T20 (name,tim1) value ("tom", 151516)

Mysql > select * from T20

+-+

| | name | age | tim1 | tim2 | |

+-+

| | tom | NULL | 15:15:16 | NULL |

+-+

1 row in set (0.00 sec)

+ + +

Use the time function to get the time to assign a value to the date-time type field?

Now () gets the current system time

Year () gets the year

Date () get date

Month () gets the month

Day () acquisition date (date)

Time () get time

Select now ()

Select year (now ())

Select month (now ())

Select day (now ())

Aelect date (now ())

Select year (20191224)

Select date (now ())

+ +

Mysql > select now ()

+-+

| | now () |

+-+

| | 2017-02-22 18:39:23 |

+-+

1 row in set (0.00 sec)

Mysql > select year (now ())

+-+

| | year (now ()) |

+-+

| | 2017 |

+-+

1 row in set (0.00 sec)

Mysql > select year (20191224)

+-+

| | year (20191224) | |

+-+

| | 2019 |

+-+

1 row in set (0.00 sec)

Mysql > select date (now ())

+-+

| | date (now ()) |

+-+

| | 2017-02-22 | |

+-+

1 row in set (0.00 sec)

Mysql > select month (now ())

+-+

| | month (now ()) |

+-+

| | 2 |

+-+

1 row in set (0.00 sec)

Mysql > select day (now ())

+-+

| | day (now ()) |

+-+

| | 22 |

+-+

1 row in set (0.00 sec)

Mysql > select date (now ())

+-+

| | date (now ()) |

+-+

| | 2017-02-22 | |

+-+

1 row in set (0.00 sec)

+ +

Insert into T15 values ("lili", 21, 18800.88, 093000, 20171008, 1995)

Insert into T15 values ("jerry", 28800.88 now (), now (), now ())

Insert into T15 values ("tom", 21Jing 18800.88 time (20171224201818), date (20171224201818), year (now ()), now ()

Enumerated types (gender-loving majors)

The value of a field can only be selected within the range of enumerations

Enum (values list) radio

Set (values list) multiple selection

Create table t177 (

Name char (10)

Sex enum (0Phone1)

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

);

Create table T17 (

Name char (10)

Sex enum ("boy", "girl", "no")

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

);

Desc t17

Insert into T17 values ("bob", "boy", "woman,game")

Insert into T17 values ("bob", "boy", "book,game")

Insert into T17 values ("alic", 3, "game")

Select * from T17

Create table T5 (name text)

Create table T5 (name blob)

Make a student form:

Mysql > create table student (name char (10), age tinyint unsigned, level float (3), pay float (7), sex enum ("man", "woman"), likes set ("book", "music", "film"), homeadder char (10), phone bigint,num int (2) zerofill)

Mysql > desc student

+-- +

+-- +

| | age | tinyint (3) unsigned | YES | | NULL |

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

| | sex | enum ('man','woman') | YES | | NULL |

| | num | int (2) unsigned zerofill | YES | | NULL |

+-- +

9 rows in set (0.00 sec)

Mysql > insert into student value ("zhangsbing", 25, 1.711, 21203.222, 1, "book,music", "shanghai", 18500202211,2)

Mysql > mysql > select * from student

+-+

| | name | age | level | pay | sex | likes | homeadder | phone | num |

+-+

| | zhangsbing | 25 | 1.71 | 21203.22 | man | book,music | shanghai | 18500202211 | 02 |

+-+

1 row in set (0.00 sec)

Modify table structure

Alter table table name execute action

Mysql > desc student

+-- +

+-- +

| | age | tinyint (3) unsigned | YES | | NULL |

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

| | sex | enum ('man','woman') | YES | | NULL |

| | num | int (2) unsigned zerofill | YES | | NULL |

+-- +

9 rows in set (0.00 sec)

________________

Add add a new field

Add field name type (width)

Add field name type (width) constraint

Add field name type (width) constraint first

Add field name type (width) constraint after field name

Mysql > alter table student

-> add

-> class char (7) default "NSD1611" first

-> add

-> class_uptime time not null default 083000

Mysql > mysql > select * from student

+-+

+-+

| | NSD1611 | zhangsbing | 25 | 1.71 | 21203.22 | man | book,music | shanghai | 18500202211 | 02 | 08:30:00 |

+-+

1 row in set (0.00 sec)

Mysql > alter table student add university char (20) default "nongyedaxue" after class

Mysql > select * from student

+-+

+-+

| | NSD1611 | nongyedaxue | zhangsbing | 25 | 1.71 | 21203.22 | man | book,music | shanghai | 18500202211 | 02 | 08:30:00 |

Drop delete field

Drop field name

Alter table t1 drop name,drop sex

Mysql > alter table student drop name,drop sex

Mysql > select * from student

+-+

+-+

| | NSD1611 | nongyedaxue | 25 | 1.71 | 21203.22 | book,music | shanghai | 18500202211 | 02 | 08:30:00 |

+-+

Mysql > alter table student add sex enum ("boy", "girl") not null default "boy" after age

Mysql > mysql > select * from student

+-+

+-+

| | NSD1611 | nongyedaxue | 25 | boy | 1.71 | 21203.22 | book,music | shanghai | 18500202211 | 02 | 08:30:00 |

Mysql > alter table student

-> modify

-> sex enum ("boy", "girl", "no") not null default "no"

Mysql > mysql > select * from student

+-+

+-+

| | NSD1611 | nongyedaxue | 25 | boy | 1.71 | 21203.22 | book,music | shanghai | 18500202211 | 02 | 08:30:00 |

+-+

1 row in set (0.00 sec)

Mysql > desc student

+-- +

+-- +

| | class | char (7) | YES | | NSD1611 |

| | age | tinyint (3) unsigned | YES | | NULL |

| | sex | enum ('boy','girl','no') | NO | | no |

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

| | num | int (2) unsigned zerofill | YES | | NULL |

| | class_uptime | time | NO | | 08:30:00 | |

+-- +

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.