mysql 安装 和简单使用技巧1

# mysql_install_db --user=mysql -datadir=/var/lib/mysql 初始化数据

vncviewer 172.40.50.117：6000

内存 cpu 硬盘

# yum grouplist

mysql（3306）

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

1.用rpm装 使用光盘封装好的rpm包

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

..........................

[root@host ~]# rpm -q mysql-server (查看是否安装和版本号)

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

初始化 MySQL 数据库： WARNING: The host 'host.tedu.cn' could not be looked up with resolveip.

[root@host ~]# service mysqld status

mysqld (pid 21119) 正在运行...

[root@host ~]# chkconfig mysqld on

[root@host ~]# chkconfig --list mysqld

mysqld 0:关闭 1:关闭 2:启用 3:启用 4:启用 5:启用 6:关闭

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

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

/var/lib/mysql (数据库目录) 下面目录和文件用户和组都是mysql

/etc/my.cnf 主配置文件

/var/log/mysqld.log （日志文件）

/var/run/mysqld/mysqld.conf （pid路径）

[root@proxe ~]# which mysql

/usr/bin/mysql

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

mysql-5.1.73-5.el6_6.x86_64

低版本：

进程名和服务名都是mysqld

传输协议tcp

rmp包装 进程所有者和所属组是mysql这个用户

----------------------------------------------------------------------

低版本：

（自己连：）mysql -hlocalhost -uroot -p (本机登也可 mysql)

（别人练：）# mysql -h ip

-------------------------------------------------------------------------

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

2.从官网上下载安装 （先把之前rpm装的删除）

1）删除之前安装的

[root@host ~]# service mysqld stop

停止 mysqld： [确定]

[root@host ~]# rpm -e --nodeps mysql-server mysql （--nodeps忽略依赖关系 就是依赖关系包不卸载 mysql连接工具卸载掉 因为下面装的会有）

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

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

2）把官网下的解包安装

[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 （因为上面只删除了主配置文件，和mysql-server这个包 所有的依赖包还在 所以大U直接升级）

[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 (高版本mysql包名叫这）

MySQL-client-5.6.15-1.el6.x86_64

3）高版本使用：会有密码，在/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 或创建一个这样的文件在这

________________________________________________________________________________________________________

高版本：

服务名mysql

进程名mysqld（

http传输协议：tcp

rpm包装的，进程所有者和所有组都是mysql

数据库目录：/var/lib/mysql

主配置文件：/etc/my.cnf

/var/run/mysqld/mysqld.conf 主配置文件

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

数据迁移

mysql ---->mariadb

关系型数据库mysql 难度大

非关系型数据库 redis 简单 用户名密码

使用数据库服务

启动

停止

状态

连接

DDL 就是删表删库建表建库

以文件的形式存放在数据库目录下 /var/lib/mydql

DB (/var/lib/mysql)

DBS (装列数据库和操作系统)

DBMS （mysql)

E-R数据模型

字符类型：char varchar blob text

数值类型：int tinyint smallint

日期时间类型：year date time datetime

枚举类型 enmu（单选项）

set(多个)

字符：

char 255 定长 不指默认是（1）

varchar 65532 变长 一定要指

大文本类型 blob / text 不要指

数值：

整型 微小×××（-128-127 0-255）tinyint默认有符号 tinyint unsigned(无符号)

小××× 中整型 大××× 极大整型

浮点型 float(单精度） double（双）

正整数 12

负整数-12

int（2）显示宽度 默认11位， 没到这么多位会补。 zerofill,

日期时间：

year 年 YYYY 2017 --2155

date 日期 YYYYMMDD 20170223

time 时间 HHMMSS 165425

datetime / timestamp

YYYYMMDDHHMMSS

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

基本操做：

[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;

______________________________________________________________________________________________

对表的操作：

----------------------------------------

字符类型 (姓名 家庭地址)

char 定长 <=255 默认不指是1

varchar 变长 <=65532 不指报错

大文本类型

blob

text

-----------------------

mysql> create table t2 (name char, home varchar(5) );

Query OK, 0 rows affected (0.67 sec)

mysql> desc t2;

+-------+------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------+------+-----+---------+-------+

| name | char(1) | YES | | NULL | |

| home | varchar(5) | YES | | NULL | |

+-------+------------+------+-----+---------+-------+

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 |

+------+-------+

字符类型：char 最长不超过255个字符，默认不指是1，输值时不能对于指定字符但可以少于它。

varshar 最长不超65532个字符，默认不指报错，输值时不能对于指定字符但可以少于它。

数据比较大时，用blob,和text, 输入字符数据时要用双引引上。

--------------------------------------------

整数类型

--------

根据存储数值的范围又分为：

有符号 无符号

tinyint 微小整型 -128~127 0~255

smallint

MEDIUMINT

int

bigint

int（2）显示宽度 默认11位， 没到这么多位会补。 int(2) zerofill,

tinyint 默认是有符号数值为：-128--127 ，无符号数值为0--255 需要接unsigned（没有负数）

当碰到小数都四舍五入。

mysql> create table t4(

-> level tinyint);

Query OK, 0 rows affected (0.76 sec)

mysql> insert into t4 values (128);

mysql> insert into t4 values (127);

Query OK, 1 row affected (0.05 sec)

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.05 sec)

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(也可默认不写）

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.05 sec)

mysql> insert into t8 value (54);

Query OK, 1 row affected (0.03 sec)

mysql> select * from t8;

+------+

| age |

+------+

| 002 |

| 003 |

| 003 |

| 5452 |

| 054 |

【int 后接zerofill 给定显示宽度为3当没达到时会用0补齐】

float(n,m)浮点型

mysql> create table t9( age float(7,2));

Query OK, 0 rows affected (0.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 |

+----------+

数值类型的宽度与字符类型宽度的区别？

数值类型的宽度是显示宽度，不能够控制给字段赋值的大小，字段值的大小由字段类型决定。

日期时间类型 (生日 注册时间 入职时间)

年 year YYYY 2016

01-69 20XX

70-99 19XX

00 0000

日期 date YYYYMMDD 20161219

时间 time HHMMSS 144518

日期时间 （ 约会时间 ）

datetime / timestamp(当给前面设置一个时间值是，它会自动取当前值)

YYYYMMDDHHMMSS

20170214183018

datetime 与 timestamp 的区别？

当不给timestamp类型的字段赋值时，用系统当前的时间给字段赋值。

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, 20170721151515, 151615);

Query OK, 1 row affected (0.04 sec)

mysql> select * from t17;

+-------+------------+----------+---------------------+----------+

| time1 | time2 | time3 | time5 | time6

+-------+------------+----------+---------------------+----------+

| 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 );

+-------+-----------+------+-----+-------------------+-----------------------------+

| Field | Type | Null | Key | Default | Extra |

+-------+-----------+------+-----+-------------------+-----------------------------+

| time1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

| time2 | datetime | YES | | NULL | |

+-------+-----------+------+-----+-------------------+-----------------------------+

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)

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

使用时间函数获取时间给日期时间类型字段赋值?

now() 获取当前系统时间

year() 获取年份

date() 获取日期

month() 获取月份

day() 获取日期（几号）

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,now());

insert into t15 values("jerry",29,28800.88,now(),now(),now(),now());

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

枚举类型 （爱好 性别 专业 ）

字段的值只能在列举的范围内选择

enum(值列表) 单选

set(值列表) 多选

create table t177(

name char(10),

sex enum(0,1),

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 );

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

做一个学生表：

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

mysql> desc student

-> ;

+-----------+----------------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+----------------------------+------+-----+---------+-------+

| name | char(10) | YES | | NULL | |

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

| level | float(3,2) | YES | | NULL | |

| pay | float(7,2) | YES | | NULL | |

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

| likes | set('book','music','film') | YES | | NULL | |

| homeadder | char(10) | YES | | NULL | |

| phone | bigint(20) | 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)

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

修改表结构

alter table 表名 执行动作；

mysql> desc student;

+-----------+----------------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+----------------------------+------+-----+---------+-------+

| name | char(10) | YES | | NULL | |

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

| level | float(3,2) | YES | | NULL | |

| pay | float(7,2) | YES | | NULL | |

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

| likes | set('book','music','film') | YES | | NULL | |

| homeadder | char(10) | YES | | NULL | |

| phone | bigint(20) | YES | | NULL | |

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

+-----------+----------------------------+------+-----+---------+-------+

9 rows in set (0.00 sec)

________________

add 添加新字段

add 字段名 类型(宽度)；

add 字段名 类型(宽度) 约束条件 ；

add 字段名 类型(宽度) 约束条件 first；

add 字段名 类型(宽度) 约束条件 after 字段名；

mysql> alter table student

-> add

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

-> add

-> class_uptime time not null default 083000

-> ;

mysql> mysql> select * from student;

+---------+------------+------+-------+----------+------+------------+-----------+-------------+------+--------------+

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

+---------+------------+------+-------+----------+------+------------+-----------+-------------+------+--------------+

| 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;

+---------+-------------+------------+------+-------+----------+------+------------+-----------+-------------+------+--------------+

| class | university | name | age | level | pay | sex | likes | homeadder | phone | num | class_uptime |

+---------+-------------+------------+------+-------+----------+------+------------+-----------+-------------+------+--------------+

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

+---------+-------------+------------+------+---

++++++++++++++++

drop 删除字段

drop 字段名

alter table t1 drop name，drop sex;

mysql> alter table student drop name,drop sex;

mysql> select * from student;

+---------+-------------+------+-------+----------+------------+-----------+-------------+------+--------------+

| class | university | age | level | pay | likes | homeadder | phone | num | class_uptime |

+---------+-------------+------+-------+----------+------------+-----------+-------------+------+--------------+

| 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;

+---------+-------------+------+-----+-------+----------+------------+-----------+-------------+------+--------------+

| class | university | age | sex | level | pay | likes | homeadder | phone | num | class_uptime |

+---------+-------------+------+-----+-------+----------+------------+-----------+-------------+------+--------------+

| 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;

+---------+-------------+------+-----+-------+----------+------------+-----------+-------------+------+--------------+

| class | university | age | sex | level | pay | likes | homeadder | phone | num | class_uptime |

+---------+-------------+------+-----+-------+----------+------------+-----------+-------------+------+--------------+

| 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;

+--------------+----------------------------+------+-----+-------------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------------+----------------------------+------+-----+-------------+-------+

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

| university | char(20) | YES | | nongyedaxue | |

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

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

| level | float(3,2) | YES | | NULL | |

| pay | float(7,2) | YES | | NULL | |

| likes | set('book','music','film') | YES | | NULL | |

| homeadder | char(10) | YES | | NULL | |

| phone | bigint(20) | YES | | NULL | |

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

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

+--------------+----------------------------+------+-----+-------------+-------+