MySql基礎部署以及基本使用(用于個人學習與回顧)

MySQL數據庫介紹

• ?最為著名、應用最為廣泛的開源數據庫軟件

? ? ? ? -最早隸屬于瑞典的MySQL AB公司

? ? ? ? -2008年1月，MySQL AB被SUN收購

? ? ? ? -2009年4月，SUN被Oracle收購

• 嶄新的開源分支MariaDB

  ? -為應付MySQL可能會閉源的風險而誕生

  ? - 由MySQL原作者Widenius主導開發

  ? - 與MySQL保持最大程度兼容

MySQL的特點及應用

• ?主要特點

  ? ?-適用于中小規模、關系型數據庫系統

  ? ?-支持Linux/Unix、Windows等多種操作系統

? ? ? ? ?-使用C和C++編寫，可移植性強

? ? ? ? ?-通過API支持Python/JAVA/Perl/PHP等語言

• 典型應用環境

  ? ?-LAMP平臺，與Apache HTTP Server組合

  ? ?-LNMP平臺，與Nginx組合

Mysql安裝

• 準備工作

  -停止mariadb服務

  -刪除文件 /etc/my.cnf

  -刪除數據

  -卸載軟件包

[root@proxy?~]#?systemctl?stop?mariadb
[root@proxy?~]#?rm?-rf?/etc/my.cnf
[root@proxy?~]#?rm?-rf?/var/lib/mysql/*
[root@proxy?~]#?rpm?-e?--nodeps?mariadb-server?mariadb
警告：/var/log/mariadb/mariadb.log?已另存為?/var/log/mariadb/mariadb.log.rpmsave

• 至少安裝server、client、share*包

  
  

? ? ? ?-采用-U升級安裝，可替換沖突文件

? ? ? ?- 推薦將devel安裝，用于支持其他軟件

[root@proxy?~]#?yum?-y?install?perl-Data-Dumper?perl-JSON?perl-Time-HiRes
[root@proxy?~]#?tar?-xf?mysql-5.7.17-1.el7.x86_64.rpm-bundle.tar?
[root@proxy?~]#?rm?-f?mysql-community-server-minimal-5.7.17-1.el7.x86_64.rpm?
[root@proxy?~]#?rpm?-Uvh?mysql-community-*.rpm

• 啟動MySQL數據庫服務

  -服務腳本為/usr/lib/systemd/system/mysqld.service

[root@localhost?~]#?systemctl?start?mysqld??????????????????
[root@localhost?~]#?systemctl?enable?mysqld?????????????????
[root@localhost?~]#?systemctl?status?mysqld

MySQL初始配置

• 默認的數據庫管理賬號

? ? ? ?-root,允許從localhost訪問

? ? ? ?-首次登錄密碼在安裝時隨機生成

? ? ? ?-存儲在錯誤日志文件中

[root@proxy?~]#?grep?'temporary?password'?/var/log/mysqld.log
2019-06-24T15:19:18.303935Z?1?[Note]?A?temporary?password?is?generated?for?root@localhost:?zzXdihIzU4-_
[root@proxy?~]#?mysql?-uroot?-p'zzXdihIzU4-_'

mysql>?set?global?validate_password_policy=0;?????//只驗證長度
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?set?global?validate_password_length=6;?????//修改密碼長度，默認值是8個字符
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?alter?user?user()?identified?by?"123456";??//修改登錄密碼
Query?OK,?0?rows?affected?(0.00?sec)

• 使用客戶端命令連接服務器

[root@proxy?~]#?mysql?-uroot?-p123456
mysql:?[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?3Server?version:?5.7.17?MySQL?Community?Server?(GPL)
Copyright?(c)?2000,?2016,?Oracle?and/or?its?affiliates.?All?rights?reserved.
Oracle?is?a?registered?trademark?of?Oracle?Corporation?and/or?itsaffiliates.?Other?names?may?be?trademarks?of?their?respectiveowners.
Type?'help;'?or?'\h'?for?help.?Type?'\c'?to?clear?the?current?input?statement.
mysql>

MySQL服務相關參數

數據庫基本管理

• 常用SQL操作指令

  -DDL數據定義語言（create,alter,drop）

  -DML數據定義語言（insert,update,delete）

  -DCL數據定義語言（grant,revoke）

  -DTL數據定義語言（commit,rollback,savepoint）
  

• 庫管理命令

  -show databases; ? ? ? ? ? ? ? //顯示已有的庫
  

? ? ? -Use 庫名； ? ? ? ? ? ? ? ? ? ? ?? //切換庫

? ? ? -Select database(); ? ? ? ? ?? //顯示當前所在的庫

? ? ? -Create database 庫名； ? //創建新庫

? ? ? -Show tables; ? ? ? ? ? ? ? ? ? //顯示已有的庫

? ? ? -Drop database 庫名； ?? //刪除庫

• 表管理命令

? ? ? -Desc 表名； ? ? ? ? ? ? ? ? ? //查看表結構

? ? ? -Select * from 表名； ? ? // 查看表記錄

? ? ? -Drop table 表名； ? ? ?? //刪除表

• 記錄管理命令

  -Select * from 表名； ?? //查看表記錄

  -Insert into 表名 values(值列表)； //插入表記錄

  -Update 表名 set 字段=值；? //修改表記錄
  

  -Delete from 表名； ? ? ? ? ? //刪除表記錄

• 修改表結構
  

? ? ?? -添加新字段

1. ? ? ? ALTER TABLE 表名

2. ? ? ? ADD 字段名 類型（寬度） 約束條件；

3. ? ?? 可加 AFTER 字段名；

? ? ? ? ?? 或者FIRST;

mysql>?desc?tt1;
+-------+------------+------+-----+---------+-------+
|?Field?|?Type???????|?Null?|?Key?|?Default?|?Extra?|
+-------+------------+------+-----+---------+-------+
|?id????|?int(3)?????|?NO???|?PRI?|?NULL????|???????|
|?name??|?varchar(5)?|?NO???|?????|?NULL????|???????|
|?age???|?int(3)?????|?NO???|?????|?NULL????|???????|
+-------+------------+------+-----+---------+-------+
3?rows?in?set?(0.01?sec)

mysql>?alter?table?tt1?add?interest?varchar(40);
Query?OK,?0?rows?affected?(0.08?sec)
Records:?0??Duplicates:?0??Warnings:?0

mysql>?desc?tt1;
+----------+-------------+------+-----+---------+-------+
|?Field????|?Type????????|?Null?|?Key?|?Default?|?Extra?|
+----------+-------------+------+-----+---------+-------+
|?id???????|?int(3)??????|?NO???|?PRI?|?NULL????|???????|
|?name?????|?varchar(5)??|?NO???|?????|?NULL????|???????|
|?age??????|?int(3)??????|?NO???|?????|?NULL????|???????|
|?interest?|?varchar(40)?|?YES??|?????|?NULL????|???????|
+----------+-------------+------+-----+---------+-------+
4?rows?in?set?(0.00?sec)

? ? ? ?? -修改字段類型

? ? ? ? ? ?? alter table 表名

? ? ? ? ? ?? modify 字段名 類型（寬度）約束條件；

? ? ? ? ? ?? 可加 after 字段名；

? ? ? ? ? ?? 或者 first ；

mysql>?desc?tt1;
+----------+--------------------+------+-----+---------+-------+
|?Field????|?Type???????????????|?Null?|?Key?|?Default?|?Extra?|
+----------+--------------------+------+-----+---------+-------+
|?id???????|?int(3)?????????????|?NO???|?PRI?|?NULL????|???????|
|?name?????|?varchar(5)?????????|?NO???|?????|?NULL????|???????|
|?age??????|?int(3)?????????????|?NO???|?????|?NULL????|???????|
|?gender???|?enum('boy','girl')?|?NO???|?????|?NULL????|???????|
|?interest?|?varchar(40)????????|?YES??|?????|?NULL????|???????|
+----------+--------------------+------+-----+---------+-------+
5?rows?in?set?(0.00?sec)

mysql>?alter?table?tt1?modify?name?char(6)?not?null;
Query?OK,?0?rows?affected?(0.34?sec)
Records:?0??Duplicates:?0??Warnings:?0

mysql>?desc?tt1;
+----------+--------------------+------+-----+---------+-------+
|?Field????|?Type???????????????|?Null?|?Key?|?Default?|?Extra?|
+----------+--------------------+------+-----+---------+-------+
|?id???????|?int(3)?????????????|?NO???|?PRI?|?NULL????|???????|
|?name?????|?char(6)????????????|?NO???|?????|?NULL????|???????|
|?age??????|?int(3)?????????????|?NO???|?????|?NULL????|???????|
|?gender???|?enum('boy','girl')?|?NO???|?????|?NULL????|???????|
|?interest?|?varchar(40)????????|?YES??|?????|?NULL????|???????|
+----------+--------------------+------+-----+---------+-------+
5?rows?in?set?(0.00?sec)

? ? ? ?? -修改字段名

? ? ? ? ? ?? alter table 表名

? ? ? ? ? ?? change 源字段名 新字段名 類型（寬度） 約束條件；

mysql>?desc?tt1;
+----------+--------------------+------+-----+---------+-------+
|?Field????|?Type???????????????|?Null?|?Key?|?Default?|?Extra?|
+----------+--------------------+------+-----+---------+-------+
|?id???????|?int(3)?????????????|?NO???|?PRI?|?NULL????|???????|
|?name?????|?varchar(5)?????????|?NO???|?????|?NULL????|???????|
|?age??????|?int(3)?????????????|?NO???|?????|?NULL????|???????|
|?sex??????|?enum('boy','girl')?|?YES??|?????|?NULL????|???????|
|?interest?|?varchar(40)????????|?YES??|?????|?NULL????|???????|
+----------+--------------------+------+-----+---------+-------+
5?rows?in?set?(0.00?sec)

mysql>?alter?table?tt1?change?sex?gender?enum('boy','girl')?not?null;
Query?OK,?0?rows?affected?(0.33?sec)
Records:?0??Duplicates:?0??Warnings:?0

mysql>?desc?tt1;
+----------+--------------------+------+-----+---------+-------+
|?Field????|?Type???????????????|?Null?|?Key?|?Default?|?Extra?|
+----------+--------------------+------+-----+---------+-------+
|?id???????|?int(3)?????????????|?NO???|?PRI?|?NULL????|???????|
|?name?????|?varchar(5)?????????|?NO???|?????|?NULL????|???????|
|?age??????|?int(3)?????????????|?NO???|?????|?NULL????|???????|
|?gender???|?enum('boy','girl')?|?NO???|?????|?NULL????|???????|
|?interest?|?varchar(40)????????|?YES??|?????|?NULL????|???????|
+----------+--------------------+------+-----+---------+-------+
5?rows?in?set?(0.00?sec)

? ? ? ?? -刪除字段

? ? ? ? ? ?? alter table 表名

? ? ? ? ? ?? drop 字段名；

mysql>?desc?tt1;
+----------+--------------------+------+-----+---------+-------+
|?Field????|?Type???????????????|?Null?|?Key?|?Default?|?Extra?|
+----------+--------------------+------+-----+---------+-------+
|?id???????|?int(3)?????????????|?NO???|?PRI?|?NULL????|???????|
|?name?????|?char(6)????????????|?NO???|?????|?NULL????|???????|
|?age??????|?int(3)?????????????|?NO???|?????|?NULL????|???????|
|?gender???|?enum('boy','girl')?|?NO???|?????|?NULL????|???????|
|?interest?|?varchar(40)????????|?YES??|?????|?NULL????|???????|
+----------+--------------------+------+-----+---------+-------+
5?rows?in?set?(0.00?sec)

mysql>?alter?table?tt1?drop?gender;
Query?OK,?0?rows?affected?(0.35?sec)
Records:?0??Duplicates:?0??Warnings:?0

mysql>?desc?tt1;
+----------+-------------+------+-----+---------+-------+
|?Field????|?Type????????|?Null?|?Key?|?Default?|?Extra?|
+----------+-------------+------+-----+---------+-------+
|?id???????|?int(3)??????|?NO???|?PRI?|?NULL????|???????|
|?name?????|?char(6)?????|?NO???|?????|?NULL????|???????|
|?age??????|?int(3)??????|?NO???|?????|?NULL????|???????|
|?interest?|?varchar(40)?|?YES??|?????|?NULL????|???????|
+----------+-------------+------+-----+---------+-------+
4?rows?in?set?(0.00?sec)

? ? ? ?? -修改表名

? ? ? ? ? ? ? alter table 表名

? ? ? ? ? ? ? rename 新表名；

mysql>?alter?table?tt1?rename?tt2;
Query?OK,?0?rows?affected?(0.31?sec)
mysql>?desc?tt1;
ERROR?1146?(42S02):?Table?'studb.tt1'?doesn't?exist
mysql>?desc?tt2;
+----------+-------------+------+-----+---------+-------+
|?Field????|?Type????????|?Null?|?Key?|?Default?|?Extra?|
+----------+-------------+------+-----+---------+-------+
|?id???????|?int(3)??????|?NO???|?PRI?|?NULL????|???????|
|?name?????|?char(6)?????|?NO???|?????|?NULL????|???????|
|?age??????|?int(3)??????|?NO???|?????|?NULL????|???????|
|?interest?|?varchar(40)?|?YES??|?????|?NULL????|???????|
+----------+-------------+------+-----+---------+-------+
4?rows?in?set?(0.00?sec)

時間函數

• 無需庫、表，可直接調用

  -使用SELECT指令輸出函數結果

  
  

mysql>?select?now(),sysdate(),curdate();
+---------------------+---------------------+------------+
|?now()???????????????|?sysdate()???????????|?curdate()??|
+---------------------+---------------------+------------+
|?2019-06-25?22:10:45?|?2019-06-25?22:10:45?|?2019-06-25?|
+---------------------+---------------------+------------+
1?row?in?set?(0.00?sec)

mysql>?select?date(now()),time(now());
+-------------+-------------+
|?date(now())?|?time(now())?|
+-------------+-------------+
|?2019-06-25??|?22:11:41????|
+-------------+-------------+
1?row?in?set?(0.00?sec)