七、MySQL 客戶端工具及SQL入門
1、課程大綱：
mysql客戶端命令介紹；
mysql獲取幫助的方法細講；
DDL陳述句之管理資料庫；
DDL陳述句之管理表與案例介紹；
DML陳述句之管理表中的資料；
SELECT 檢索資料；
2、mysql介面程式使用及SQL入門
mysql客戶端命令介紹：
? mysql： – 用于資料庫連接管理
- 將 用戶SQL 陳述句發送到服務器
? mysqladmin： – 命令列管理工具
? mysqldump： – 備份資料庫和表的內容
- 用于管理資料庫：
命令介面自帶命令
DDL:資料定義語言（create）
DCL：資料控制語言(grant,revoke)
DML：資料操作語言(update,delete,insert)
mysql 介面程式：
mysql -uroot -poldboy123 -e "show variables like '%server_id%'"
mysql>:
1,介面自帶的功能
mysql命令：
1.\h或help 或？
顯示介面命令幫助命令，
2.\G
將顯示的內容格式輸出，
3.\T或者tee
日志記錄，需要先：tee /tmp/test.log
所有mysql操作及輸出都記錄在這個檔案里，
4.\c 或者CTRL+c
陳述句后面帶\c，前面的命令不在執行，ctrl+c退出
5.\s 或 status
查看當前資料庫的基本狀態，
6.\. 或 source
用來執行外部的SQL腳本：二進制日志截取，備份出來的sql腳本
7.\ use
use 進入到某個資料庫，
2，服務器端命令（SQL結構化的查詢語言,mysql介面程式只負責接收SQL)
show 系列命令，
2、服務器端命令（SQL）
（1）SQL:結構化的查詢語言，mysql介面程式只負責接收SQL，傳送SQL.
（2）SQL種類：
? DDL：資料庫物件定義語言(create)
? DCL：資料庫控制語言（grant，revoke）
? DML:資料行操作語言（update,delete,insert)
? DQL:資料查詢語言（show，select）
DDL操作：
? 物件：f
? 庫：
? 定義什么？
? 1、庫名字
? 2、庫的基本屬性
? 如何定義？
? create database lufei;
? create shema lf;
? show databases;
mysql> help create database
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...
創建屬性：
create_specification:
字符集：[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT]
排序規則：COLLATE [=] collation_name
mysql> create database llf character set utf-8;
mysql> show create database llf;#查看建庫陳述句
drop database llf;洗掉資料庫
help 后面加命令，幫助不熟悉使用的命令，
修改字符集：
ALTER DATABASE [db_name] CHARACTER SET charset_name collation_name
mysql> alter database lf charset utf8mb4;
縮寫
mysql> show create database lf;
? 表：
? 表資料
? 表屬性（元資料）：表明，列名字，列定義（資料型別，約束，特殊列屬性）、表的索引資訊，
? 定義什么？
? 定義表的屬性？
use lufei;
create table t1(id int,name varchar(20));
mysql> use lufei;
mysql> create table t1(id int ,name varchar(20));
mysql> show tables;
mysql> show create table t1;
mysql> desc t1;
mysql> drop table t1;
修改表的定義：
修改：
（1）在表中添加一列
alter table t1 add age int;
（2）添加多列
alter table t1 add bridate datetime, add gender enum('M','F');
（3）在指定列后添加一列
alter table t1 add stu_id int after id;
（4）在表中最前添加一列
alter table t1 add sid int first;
（5）洗掉列
alter table t1 drop sid;
（6）修改列名
alter table t1 change name stu_name varchar(20);
（7）修改列屬性
alter table t1 modify stu_id varchar(20);
（8）修改表名
rename table t1 to student;
alter table student rename as stu;
DML陳述句：資料庫操作語言
? insert
update
? delete
DML陳述句：資料操作語言
insert
use lufei
create table t1 (id int ,name varchar(20));
insert into t1 values(1,'zhang3');
select * from t1;
insert into t1 values (2,'li4'),(3,'wang5'),(4,'ma6');
insert into t1(name) values ('xyz');
update
update t1 set name='zhang33' ; ----會更新表中所有行的name欄位，比較危險，
update t1 set name='zhang55' where id=1; ----update在使用時一般都會有where條件去限制，
delete
delete from t1 ; --洗掉表中所有行，比較危險，一行一行洗掉表中資料，
delete from t1 where id=2;
DDL
truncate table t1; ---在物理上洗掉表資料，速度比較快，
DQL陳述句：（資料庫查詢陳述句）
DQL:
select陳述句：
SELECT USER,PASSWORD ,HOST FROM mysql.user;
-- select 基本查詢
DESC world.city
SELECT id ,NAME FROM world.city;
SELECT * FROM world.`city`;
-- select 條件查詢 where
---- 1、查詢中國（CHN）所有的城市資訊
SELECT * FROM world.`city` WHERE countrycode='CHN';
---- 2、查詢中國（CHN）安徽省所有的城市資訊，
SELECT * FROM world.`city`
WHERE countrycode='CHN'
AND
district='anhui';
---- 3、查詢世界上人口數量在10w-20w城市資訊
SELECT * FROM world.`city`
WHERE
population BETWEEN 100000 AND 200000 ;
---- 4、中國或者日本的所有城市資訊
where字句中的IN
SELECT * FROM world.city
WHERE countrycode IN ('CHN','JPN');
---- 5、模糊查詢
SELECT * FROM world.city
WHERE countrycode LIKE 'ch%';
select 排序并限制
按照人口數量排序輸出中國的城市資訊（asc（默認升序），desc（降序））
-- select 排序并限制
---- 按照人口數量排序輸出中國的城市資訊（ASC\DESC）
SELECT * FROM world.`city` WHERE countrycode='CHN' ORDER BY population ASC;
SELECT * FROM world.`city` WHERE countrycode='CHN' ORDER BY population DESC;
---- 按照多列排序人口+省排序
SELECT * FROM world.`city` WHERE countrycode='CHN' ORDER BY id DESC ;
按照第5列進行降序排序：
SELECT * FROM city
ORDER BY 5 DESC ;
1-20
SELECT * FROM world.`city` WHERE countrycode='CHN'
ORDER BY 5 DESC LIMIT 20;
顯示11-20行
SELECT * FROM world.`city` WHERE countrycode='CHN'
ORDER BY 5 DESC LIMIT 10,10 ;
SELECT * FROM world.`city` WHERE countrycode='CHN'
ORDER BY 5 DESC LIMIT 10 OFFSET 10 ;
表連接查詢（使用where）
傳統的連接寫法（使用where）
---- 中國所有城市資訊+使用語言
SELECT NAME ,countrycode ,population FROM city
WHERE countrycode ='CHN'
SELECT countrycode ,LANGUAGE FROM countrylanguage;
SELECT ci.NAME ,ci.countrycode ,ci.population,cl.language
FROM
city AS ci , countrylanguage AS cl
WHERE ci.countrycode ='CHN'
AND
ci.CountryCode=cl.CountryCode;
SELECT NAME,ci.countrycode ,cl.language ,ci.population
FROM city ci , countrylanguage cl
WHERE
ci.countrycode='chn' AND
ci.`CountryCode`=cl.countrycode;
SELECT NAME,countrycode ,LANGUAGE ,population
FROM city NATURAL JOIN countrylanguage
WHERE population > 10000000
ORDER BY population;
SELECT NAME,countrycode ,LANGUAGE ,population
FROM city JOIN countrylanguage
USING(countrycode);
---- 查詢青島這個城市，所在的國家具體叫什么名字
DESC city
DESC country
SELECT NAME,countrycode FROM city WHERE NAME='qingdao';
SELECT NAME FROM country WHERE CODE='CHN';
--------------------------------
SELECT ci.name ,ci.countrycode,ci.population ,co.name
FROM city AS ci
JOIN
country AS co
ON ci.countrycode=co.code
AND
ci.name='qingdao';
group by +聚合函式 （avg(),max(),min(),sum())
group by +聚合函式（avg()、max()、min()、sum()）
SELECT countrycode ,SUM(population) FROM city
WHERE countrycode = 'chn'
GROUP BY countrycode;
union
用來替換 or 、in()
SELECT * FROM world.city
WHERE countrycode IN ('CHN','JPN');
改寫為：
SELECT * FROM world.city
WHERE countrycode ='CHN'
union
SELECT * FROM world.city
WHERE countrycode ='JPN';
字符集
字符集：
charset:字符集
UTF8
UTF8mb4
gbk
collation：排序規則
a-z ,A-Z 大小寫敏感
aA-zZ 小寫不敏感
show charset;
show collation;
資料庫：
服務器端字符集：
控制的是，存到mysql中時，字符集控制
客戶端字符集
控制的是用戶的輸入及顯示
系統字符集
控制的是系統相關的顯示，和一些依賴于作業系統的應用
alter database oldboy CHARACTER SET utf8 collate utf8_general_ci;
alter table t1 CHARACTER SET latin1;
注意：更改字符集時，一定要保證由小往大改，后者必須是前者的嚴格超集，生產中別隨便改，
資料型別及列屬性：
數字型別
字符型別
時間型別
列屬性
create table student(id int not null primary key AUTO_INCREMENT)；
create table student1(id int not null primary key AUTO_INCREMENT,name varchar(20))charset utf8;
create table teacher(id int not null ,name varchar(20) not null);
create table teacher1(id int not null ,name varchar(20) not null,beizhu varchar(20) not null default "ok");
primary key 主鍵：非空、唯一
unique：唯一
獲取元資料：
information_schema :
資料行之外
元資料（定義資料的資料列屬性，列名字等，狀態）
充當資料庫元資料的中央系統資訊庫：
- 模式和模式物件
- 服務器統計資訊（狀態變數，設定，連接）
采用表格式以實作靈活訪問
- 使用任意select 陳述句
是“虛擬資料庫”
- 表并非“真實”表（基表），而是“系統視圖”
- 根據當前用戶的特權動態填充表
mysql> use information_schema
mysql> show tables;
mysql> desc tables;
mysql> select table_name ,table_schema,engine from world;
顯示資料庫world中表的列的資訊：
mysql> select * from columns where table_schema='world'\G;
mysql> select table_schema,table_name from information_schema.tables where table_schema='world';
+--------------+-----------------+
| table_schema | table_name |
+--------------+-----------------+
| world | city |
| world | country |
| world | countrylanguage |
+--------------+-----------------+
批量拼接陳述句：
----
mysql> select concat('hellow');
+------------------+
| concat('hellow') |
+------------------+
| hellow |
+------------------+
1 row in set (0.01 sec)
----
實體：
mysql> select concat("mysqldump -uroot -poldboy123 ",table_schema," ",table_name," >>","/backup/",table_schema,"-",table__name,".bak.sql") from information_schema.tables where table_schema='world';
+-----------------------------------------------------------------------------------------------------------------------------+
| concat("mysqldump -uroot -poldboy123 ",table_schema," ",table_name," >>","/backup/",table_schema,"-",table_name,".bak.sql") |
+-----------------------------------------------------------------------------------------------------------------------------+
| mysqldump -uroot -poldboy123 world city >>/backup/world-city.bak.sql |
| mysqldump -uroot -poldboy123 world country >>/backup/world-country.bak.sql |
| mysqldump -uroot -poldboy123 world countrylanguage >>/backup/world-countrylanguage.bak.sql |
+-----------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
實體2：
SELECT CONCAT('CREATE TABLE ', TABLE_SCHEMA, '.',
TABLE_NAME, '_backup LIKE ', TABLE_SCHEMA, '.',
TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ‘world’;
linux命令列使用的命令：
[root@centos6-kvm3 data]# mysqlshow -uroot -poldboy123 world
元資料一般查詢陳述句：
show
show databases
show create database oldboy
show tables
show create table t1
SOHW databases：列出所有資料庫
SHOW TABLES：列出默認資料庫中的表
SHOW TABLES FROM <database_name>：列出指定資料庫中的表
SHOW COLUMNS FROM <table_name>：顯示表的列結構
SHOW INDEX FROM <table_name>：顯示表中有關索引和索引列的資訊
SHOW CHARACTER SET：顯示可用的字符集及其默認整理
SHOW COLLATION：顯示每個字符集的整理
SHOW STATUS：列出當前資料庫狀態
SHOW VARIABLES：列出資料庫中的引數定義值
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/44458.html
標籤：MySQL