MySql基礎(chǔ)部署以及基本使用(用于個(gè)人學(xué)習(xí)與回顧)

MySQL數(shù)據(jù)庫(kù)介紹

• ?最為著名、應(yīng)用最為廣泛的開(kāi)源數(shù)據(jù)庫(kù)軟件

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

? ? ? ? -2008年1月，MySQL AB被SUN收購(gòu)

? ? ? ? -2009年4月，SUN被Oracle收購(gòu)

• 嶄新的開(kāi)源分支MariaDB

  ? -為應(yīng)付MySQL可能會(huì)閉源的風(fēng)險(xiǎn)而誕生

  ? - 由MySQL原作者Widenius主導(dǎo)開(kāi)發(fā)

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

MySQL的特點(diǎn)及應(yīng)用

• ?主要特點(diǎn)

  ? ?-適用于中小規(guī)模、關(guān)系型數(shù)據(jù)庫(kù)系統(tǒng)

  ? ?-支持Linux/Unix、Windows等多種操作系統(tǒng)

? ? ? ? ?-使用C和C++編寫(xiě)，可移植性強(qiáng)

? ? ? ? ?-通過(guò)API支持Python/JAVA/Perl/PHP等語(yǔ)言

• 典型應(yīng)用環(huán)境

  ? ?-LAMP平臺(tái)，與Apache HTTP Server組合

  ? ?-LNMP平臺(tái)，與Nginx組合

Mysql安裝

• 準(zhǔn)備工作

  -停止mariadb服務(wù)

  -刪除文件 /etc/my.cnf

  -刪除數(shù)據(jù)

  -卸載軟件包

[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升級(jí)安裝，可替換沖突文件

? ? ? ?- 推薦將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

• 啟動(dòng)MySQL數(shù)據(jù)庫(kù)服務(wù)

  -服務(wù)腳本為/usr/lib/systemd/system/mysqld.service

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

MySQL初始配置

• 默認(rèn)的數(shù)據(jù)庫(kù)管理賬號(hào)

? ? ? ?-root,允許從localhost訪(fǎng)問(wèn)

? ? ? ?-首次登錄密碼在安裝時(shí)隨機(jī)生成

? ? ? ?-存儲(chǔ)在錯(cuò)誤日志文件中

[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;?????//只驗(yàn)證長(zhǎng)度
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?set?global?validate_password_length=6;?????//修改密碼長(zhǎng)度，默認(rèn)值是8個(gè)字符
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?alter?user?user()?identified?by?"123456";??//修改登錄密碼
Query?OK,?0?rows?affected?(0.00?sec)

• 使用客戶(hù)端命令連接服務(wù)器

[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服務(wù)相關(guān)參數(shù)

數(shù)據(jù)庫(kù)基本管理

• 常用SQL操作指令

  -DDL數(shù)據(jù)定義語(yǔ)言（create,alter,drop）

  -DML數(shù)據(jù)定義語(yǔ)言（insert,update,delete）

  -DCL數(shù)據(jù)定義語(yǔ)言（grant,revoke）

  -DTL數(shù)據(jù)定義語(yǔ)言（commit,rollback,savepoint）
  

• 庫(kù)管理命令

  -show databases; ? ? ? ? ? ? ? //顯示已有的庫(kù)
  

? ? ? -Use 庫(kù)名； ? ? ? ? ? ? ? ? ? ? ?? //切換庫(kù)

? ? ? -Select database(); ? ? ? ? ?? //顯示當(dāng)前所在的庫(kù)

? ? ? -Create database 庫(kù)名； ? //創(chuàng)建新庫(kù)

? ? ? -Show tables; ? ? ? ? ? ? ? ? ? //顯示已有的庫(kù)

? ? ? -Drop database 庫(kù)名； ?? //刪除庫(kù)

• 表管理命令

? ? ? -Desc 表名； ? ? ? ? ? ? ? ? ? //查看表結(jié)構(gòu)

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

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

• 記錄管理命令

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

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

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

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

• 修改表結(jié)構(gòu)
  

? ? ?? -添加新字段

1. ? ? ? ALTER TABLE 表名

2. ? ? ? ADD 字段名 類(lèi)型（寬度） 約束條件；

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)

? ? ? ?? -修改字段類(lèi)型

? ? ? ? ? ?? alter table 表名

? ? ? ? ? ?? modify 字段名 類(lèi)型（寬度）約束條件；

? ? ? ? ? ?? 可加 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 源字段名 新字段名 類(lèi)型（寬度） 約束條件；

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)

時(shí)間函數(shù)

• 無(wú)需庫(kù)、表，可直接調(diào)用

  -使用SELECT指令輸出函數(shù)結(jié)果

  
  

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)