溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
# 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 | |
+--------------+----------------------------+------+-----+-------------+-------+
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
