基于Linux的MySQL操作實例（軟件安裝区宇，mysql基本操作，mysql數(shù)據(jù)類型值戳，建表议谷、插入數(shù)據(jù)操作）
前言
關(guān)于MySQL的概念性的東西，就在這里不多說了堕虹，本篇以實例操作為主卧晓，主要進行的操作有：MySQL軟件安裝，mysql基本操作鲫凶，mysql數(shù)據(jù)類型禀崖，建表、插入數(shù)據(jù)操作螟炫。
歡迎各路大神批評指教波附，謝謝各位。
安裝軟件
- 源碼安裝昼钻，見腳本和源碼包
- rpm包安裝（需要手動安裝依賴包）
本文使用的rpm安裝包都是從MySQL官網(wǎng)下載的掸屡。
由于個人原因GitHub暫時不能上傳，我將文件放在了百度云盤然评，有需要的朋友請前往下載仅财。若是不能下載，請私信我碗淌。
MySQL5.6盏求，點擊下載
http://pan.baidu.com/s/1jIr11Sq
當使用rpm包安裝時，可能會出現(xiàn)缺乏依賴包的情況亿眠，
可以使用先安裝光盤自帶的mysql-server碎罚，mysql，yum會自動安裝依賴包等纳像，
然后啟動服務(wù)荆烈，檢查端口號，由于mysql在默認的情況下，若不加載數(shù)據(jù)庫將不能啟動服務(wù)憔购，但是通過光盤自帶的軟件包使用yum安裝時宫峦，
這些配置都會自動做好。
然后卸載（卸載時需要添加--nodeps選項玫鸟，忽略依賴關(guān)系）低版本mysql軟件包导绷，并刪除主配置文件
[root@mysqlBak mysql_rpm]# yum -y install mysql-server mysql
[root@mysqlBak mysql_rpm]# service mysqld restart
停止 mysqld： [確定]
初始化 MySQL 數(shù)據(jù)庫： WARNING: The host 'mysqlBak.wolf.cn' could not be looked up with resolveip.
……
Please report any problems with the /usr/bin/mysqlbug script!
[確定]
正在啟動 mysqld： [確定]
[root@mysqlBak mysql_rpm]# netstat -anptu | grep mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2310/mysqld
[root@stu ~]# service mysqld stop
停止 mysqld： [確定]
[root@mysqlBak mysql_rpm]# rm -rf /etc/my.cnf
[root@mysqlBak mysql_rpm]# ls /var/lib/mysql/
ibdata1 ib_logfile0 ib_logfile1 mysql test
[root@mysqlBak mysql_rpm]# rm -rf /var/lib/mysql/*
// 卸載時，注意添加忽略依賴關(guān)系選項屎飘，
[root@mysqlBak mysql_rpm]# rpm -e --nodeps mysql-server mysql
[root@mysqlBak mysql_rpm]# ls
MySQL-client-5.6.15-1.el6.x86_64.rpm MySQL-shared-5.6.15-1.el6.x86_64.rpm
MySQL-devel-5.6.15-1.el6.x86_64.rpm MySQL-shared-compat-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
//推薦使用U升級安裝诵次，可以替換沖突文件
[root@mysqlBak mysql_rpm]# rpm -Uvh MySQL-*
Preparing... ########################################### [100%]
1:MySQL-devel ########################################### [ 14%]
2:MySQL-client ########################################### [ 29%]
3:MySQL-test ########################################### [ 43%]
4:MySQL-embedded ########################################### [ 57%]
5:MySQL-shared-compat ########################################### [ 71%]
6:MySQL-shared ########################################### [ 86%]
7:MySQL-server ########################################### [100%]
///root/.mysql_secret' 生成的隨機密碼文件
A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
You will find that password in '/root/.mysql_secret'.
……
[root@mysqlBak mysql_rpm]# /etc/init.d/mysql start
Starting MySQL. SUCCESS!
[root@mysqlBak mysql_rpm]# netstat -anptu | grep mysql
tcp 0 0 :::3306 :::* LISTEN 2565/mysqld
//查看文件內(nèi)容，記錄隨機密碼
[root@mysqlBak mysql_rpm]# cat /root/.mysql_secret
# The random password set for the root user at Mon Jan 16 11:31:07 2017 (local time): Xrf4Yrok
//使用隨機密碼先進行登錄枚碗，然后進行修改密碼等操作
[root@mysqlBak mysql_rpm]# mysql -hlocalhost -u root -pXrf4Yrok
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.15
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show databases;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement //需要先設(shè)置密碼，然后進行其他操作
mysql> set password for root@"localhost"=password("123456");
Query OK, 0 rows affected (0.03 sec)
mysql> quit
Bye
//若沒有其他配置需求可不進行該操作
[root@mysqlBak mysql_rpm]# cp /usr/share/mysql/my-default.cnf /etc/my.cnf
//可以直接制定數(shù)據(jù)庫
[root@mysqlBak mysql_rpm]# mysql -u root -p123456 test
……
mysql> select database();
+------------+
| database() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
mysql> quit
Bye
//也可以不指定該數(shù)據(jù)庫
[root@mysqlBak mysql_rpm]# mysql -u root -p123456
……
mysql> select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
MySQL概述
基本信息
- 主配置文件 /etc/my.cnf
- 服務(wù)名 mysqld或mysql （根據(jù)不同版本略有不同）
- 進程名 mysqld或mysql
- 進程所有者/組 mysql/mysql
- 端口號 3306
- 傳輸協(xié)議 tcp
- 數(shù)據(jù)庫目錄 /var/lib/mysql/
數(shù)據(jù)庫基本操作流程
1.連接數(shù)據(jù)庫服務(wù)器 mysql
2.選擇庫 use 庫名铸本；
3.創(chuàng)建表
4.向表中插入記錄 insert into
5.曾刪改查等數(shù)據(jù)操作
6.斷開連接 quit
數(shù)據(jù)以文件的形式存儲在數(shù)據(jù)庫目錄下
數(shù)據(jù)庫基本使用概述
操作指令類型
- MySQL指令：環(huán)境切換肮雨，看狀態(tài)，退出等控制
- SQL指令：數(shù)據(jù)庫定義/查詢/操縱/授權(quán)語句
基本操作注意事項
- 操作指令不區(qū)分大小寫
- 每條SQL語句以箱玷；結(jié)束或分隔
- 不支持tab鍵自動補齊
- \c可廢棄當前編寫錯的操作指令
數(shù)據(jù)庫名稱命名規(guī)則
- 具有唯一性
- 區(qū)分字母大小寫
- 只能使用數(shù)字怨规、字母、“_”
- 不能是純數(shù)字
- 不要使用特殊字符和關(guān)鍵字
操作命令基本格式
show databases; 顯示已有的庫
create database 庫名锡足；
use 庫名波丰； 切換庫
select database(); 查看當前所在的庫
drop database 庫名； 刪除已有的庫
show tables; 顯示當前所在庫下已有的表
創(chuàng)建表（表存放在庫里）
create table 庫名.表名（
字段名1 類型舶得，
字段名2 類型掰烟，
字段名3 類型
）；
select 字段名列表 from 表名; 查看表記錄
desc 表名; 查看表結(jié)構(gòu)
insert into 表名 values(值1沐批，值2……);向表中插入記錄
delete from 表名纫骑； 刪除表中的所有記錄
drop table 表名； 刪除表
建表的語法格式
create table 表名(
字段名 類型（寬度） 約束條件九孩，
字段名 類型（寬度） 約束條件先馆，
字段名 類型（寬度） 約束條件
);
mysql數(shù)據(jù)庫類型概述
字符類型 (eg：姓名 家庭地址)
char 定長 255
varchar 變長 255+
65532
大文本類型
blob
text
數(shù)值類型 (eg：工資，成績躺彬，年齡煤墙，身高，體重)
數(shù)據(jù)類型截圖
整數(shù)類型
根據(jù)存儲數(shù)值的范圍又分為：
tinyint
smallint
MEDIUMINT
int
bigint浮點型
float double
單精度 雙精度
float(n,m)
double(n,m)
n 表示總位數(shù)
m 表小數(shù)位位數(shù)
整數(shù).小數(shù)
1023.77
數(shù)值類型的寬度是顯示寬度宪拥，不能夠控制給字段賦值的大小仿野，字段值的大小由字段類型決定。
-
日期時間類型 (eg：生日江解，注冊時間设预，入職時間)
年 year YYYY 2016
01-69 20XX
70-99 19XX
00 0000
日期 date YYYYMMDD 20161219
時間 time HHMMSS 144518
日期時間 （ 約會時間 ）
datetime / timestamp
YYYYMMDDHHMMSS
20170214183018datetime 與 timestamp 的區(qū)別？
當不給timestamp類型的字段賦值時犁河，用系統(tǒng)當前的時間給字段賦值鳖枕。使用時間函數(shù)獲取時間給日期時間類型字段賦值?
now() 獲取當前系統(tǒng)時間
year() 獲取年份
date() 獲取日期
month() 獲取月份
day() 獲取日期（幾號）
time() 獲取時間 枚舉類型 （eg：愛好魄梯，性別，專業(yè) ）
字段的值只能在列舉的范圍內(nèi)選擇
enum(值列表) 單選
set(值列表) 多選查看建表過程
show create table 表名宾符；
MySQL操作實例
[root@mysqlBak mysql_rpm]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
……
//查看當前數(shù)據(jù)庫
//以下都是mysql自帶數(shù)據(jù)庫酿秸，不要輕易修改系統(tǒng)自帶數(shù)據(jù)庫，以免出現(xiàn)錯誤
//mysql是授權(quán)庫魏烫，test是測試庫
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
//創(chuàng)建新的數(shù)據(jù)庫
mysql> create database user_list;
Query OK, 1 row affected (0.00 sec)
//查看當前使用的數(shù)據(jù)庫辣苏，若是在登錄mysql時指定時，當前即可看到指定的數(shù)據(jù)庫
mysql> select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
//更改當前使用的數(shù)據(jù)庫
mysql> use user_list;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| user_list |
+------------+
1 row in set (0.00 sec)
mysql> ls
-> \c
mysql> show tables;
Empty set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| user_list |
+--------------------+
5 rows in set (0.00 sec)
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables
-> ;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
28 rows in set (0.00 sec)
//切換到我們新建的數(shù)據(jù)庫哄褒，
mysql> use user_list;
Database changed
//查看當前數(shù)據(jù)庫內(nèi)的表稀蟋，結(jié)果為空
mysql> show tables;
Empty set (0.00 sec)
//使用SQL指令創(chuàng)建name_list表
mysql> create table name_list ( n_id int(10) primary key, name char(10), age int(3), sex enum("boy","girl") );
Query OK, 0 rows affected (0.36 sec)
//查看name_list表結(jié)構(gòu)
mysql> desc name_list
-> ;
+-------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------+------+-----+---------+-------+
| n_id | int(10) | NO | PRI | NULL | |
| name | char(10) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| sex | enum('boy','girl') | YES | | NULL | |
+-------+--------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
//插入數(shù)據(jù)
mysql> insert into name_list values(
-> 1,"tom",14,"boy");
Query OK, 1 row affected (0.05 sec)
//查看name_list內(nèi)全部數(shù)據(jù)
mysql> select * from name_list;
+------+------+------+------+
| n_id | name | age | sex |
+------+------+------+------+
| 1 | tom | 14 | boy |
+------+------+------+------+
1 row in set (0.01 sec)
//字符類型實例
mysql> create table t1(name varchar(256));
mysql> create table t7(name char(3),age tinyint);
mysql> insert into t7 values("jim",19);
mysql> create table t10(name char(3),age tinyint unsigned);
mysql> create table t11(name char(3), level int(3));
mysql> insert into t11 values("bob",1024);
mysql> create table t12(level1 int(3) zerofill,level2 int(7) zerofill);
mysql> create table t13 (name char(10),age tinyint(2) unsigned , pay float(7,2));
mysql> insert into t13 values("bob",21,18000.23);
//時間函數(shù)實例
mysql> create table t14 (name char(10),age tinyint(2) unsigned , pay float(7,2),s_year year,birthday date,up_class time,meetting datetime);
mysql> insert into t14 values("bob",21,18000,1991,20160718,083000,20160818180000);
mysql> insert into t14 values("tom",29,28000,now(),now(),now(),now());
mysql> select year(20170918);
+----------------+
| year(20170918) |
+----------------+
| 2017 |
+----------------+
1 row in set (0.03 sec)
mysql> select year(20190918);
+----------------+
| year(20190918) |
+----------------+
| 2019 |
+----------------+
1 row in set (0.00 sec)
mysql> select year( now() );
+---------------+
| year( now() ) |
+---------------+
| 2016 |
+---------------+
1 row in set (0.00 sec)
mysql> select day( now() );
+--------------+
| day( now() ) |
+--------------+
| 17 |
+--------------+
1 row in set (0.00 sec)
mysql> select month( now() );
+----------------+
| month( now() ) |
+----------------+
| 6 |
+----------------+
1 row in set (0.00 sec)
mysql> select time( now() );
+---------------+
| time( now() ) |
+---------------+
| 09:14:38 |
+---------------+
1 row in set (0.00 sec)
mysql> insert into t14 values("lucy",29,28000,year(20190817),now(),time(now()),now());
mysql> select sleep(2);
+----------+
| sleep(2) |
+----------+
| 0 |
+----------+
1 row in set (2.01 sec)
mysql> insert into t14(s_year)values(00);
Query OK, 1 row affected (0.07 sec)
mysql> select s_year from t14;
+--------+
| s_year |
+--------+
| 1991 |
| 2016 |
| 2019 |
| 0000 |
+--------+
4 rows in set (0.00 sec)
mysql> insert into t14(s_year)values(100);
ERROR 1264 (22003): Out of range value for column 's_year' at row 1
mysql> create table t15(time1 datetime ,time2 timestamp);
mysql> insert into t15 values(now(),now());
mysql> insert into t15(time2) values(20160617173423);
//枚舉類型實例
mysql> create table t16(
-> name char(10),
-> sex enum("boy","girl","no"),
-> likes set("moneny","girl","book","film")
);
mysql> insert into t16 values("bob","man","A,football");
mysql> desc t16;
+-------+------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| sex | enum('boy','girl','no') | YES | | NULL | |
| likes | set('moneny','girl','book','film') | YES | | NULL | |
+-------+------------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into t16 values("bob","man","A,football");
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
mysql> insert into t16 values("bob","boy","A,football");
ERROR 1265 (01000): Data truncated for column 'likes' at row 1
mysql> insert into t16 values("bob","boy","moneny,book");
Query OK, 1 row affected (0.04 sec)
mysql> select * from t16;
+------+------+-------------+
| name | sex | likes |
+------+------+-------------+
| bob | boy | moneny,book |
+------+------+-------------+
1 row in set (0.00 sec)
mysql> insert into t16 values("lucy",2,"book");
mysql> insert into db1.t16 values(null,null,null)
mysql> create table t17(
->name char(10) not null,
->age tinyint(2) unsigned default 21,
->sex enum("boy","girl","no") not null default "boy",
->likes set("moneny","girl","book","film") not null
->default "moneny,girl");
mysql> insert into t17(name)values("bob");
mysql> insert into t17 values("lucy",23,"no","book,film");
mysql> insert into t17 values(NULL,23,"no","book,film");
mysql> insert into t17 values(NULL,NULL,"no","book,film");
ERROR 1048 (23000): Column 'name' cannot be null
mysql> insert into t17 values("NULL",NULL,"no","book,film");
Query OK, 1 row affected (0.03 sec)
