antony@notes:~/data-platform$ cat "MySQL-基本語法.md"

MySQL 基本語法

2022-08-07· data-platform ·MySQL

MySQL 基本語法

使用者登入語法

[mysql dir] /bin/mysql -h $hostIP -u root -p

整理連結

SQLite 語法

大小寫敏感性：有個重要的點值得注意，SQLite 是不區分大小寫的，但也有一些命令是大小寫敏感的，比如 GLOB 和 glob 在 SQLite 的語句中有不同的含義。
注釋：SQL 注釋以兩個連續的 “-” ，以 “/*” 開始，並擴展至下一個 “*/” 字元對或直到輸入結束，以先到者為准。SQLite的注釋可以跨越多行。
SQLite 語句：所有的 SQLite 語句可以以任何關鍵字開始，如 SELECT、INSERT、UPDATE、DELETE、ALTER、DROP 等，所有的語句以分號（;）結束。

sqlite3 資料庫名 直接進入該資料庫，如果沒有就直接建立在裡面 .quit 離開，注意要有點， .database 可看目前資料庫位置 :::spoiler SQL 語法，person表 , sqlite .語法，邏輯基本的創，加，改，刪

-- sqlite3
.open 資料庫名.db -- 也可以直接在外面的時候直接創 sqlite3 資料庫名.db
-- 創建表格 create table TABLE_NAME (NAME , type())
create table person ( name varchar(20) , tel varchar(15) , sex varchar(1) , salary integer );
-- 加入資料 insert into TABLE_NAME (COLUMN1,COLUMN2) values (VALUE1,VALUE2)
insert into Table01 (name,email) values ('Jeannie','Jeannie@test.com') ;
-- 一次加入多筆
insert into test values ('11','1111111111'),('22','2222222222'),('33','3333333333');
-- 更新資料 update
update Table01 set name='Judy',email='Judy@test.com' where id='6' ;
update person set name='john' where name='name'
-- 刪除所有資料
delete from Table名 ;
-- where 加上條件
delete from person where id='1' ; 
-- 修改 schema
alter table person add nick_name varchar(10)

Select 可用來測試基本運算

SELECT 5+2;
7
SELECT 5>10;
0
SELECT 5 <> 10 ; -- <> = != 不等於
1
-- between 用法 ： 5 between 1 and 10 
select 5 between 1 and 10 -- 1

--
INSERT INTO "table" VALUES('john','0911000111','M',30000);
INSERT INTO "table" VALUES('marry','0922000222','F',25000);
INSERT INTO "table" VALUES('lee','0922000333','N',33000);
INSERT INTO "table" VALUES('wang','0944000444','M',50000);
INSERT INTO "table" VALUES('victor','0912345678','M',55000);
INSERT INTO "table" VALUES('babe','0955656566','F',46000);

SQLite 語法：前面都會+ .

.quit
.open DATABASE_NAME -- 進入目前的資料庫
.database -- 顯示目前使用的資料庫
.tables -- 顯示有的表
.schema TABLE_NAME -- 顯示 Table 架構，也可以不指定名稱
.clone NEW_DATABASE_NAME -- 將目前使用的 database 複製一份 備份
.mode -- 顯示幕前的模式，有 ascii column csv html insert line list quote tabs tcl 等
-- insert mode 會顯示當初輸入的
.header {on,off}

::: :::spoiler 習題 1.開啟 testschool.db 2.顯示目前資料庫 3.建立school table;結構為 4.. school table 新增6筆記錄如上 5.顯示 school表格所有欄位的資料 6.查詢國文(chi)及格的學生 7.查詢國文(chi)與英文(eng)都及格的學生 8.查詢數學(mat)介於 40到 66之間的學生 9.查詢國文(chi)與英文(eng)都及格或數學(mat)介於70 到 80之間的學生 10.查詢數學(mat)為70 或 40 或66 的學生 11.查詢數學(mat)不為35 或 40 或66的學生

-- 1
.open testschool.db
-- 2
.database
-- 3
CREATE TABLE school (id INTEGER PRIMARY key , name text , class text , chi integer , eng integer , mat integer);
-- 4
INSERT INTO school VALUES(1,'a1','101',50,80,70);
INSERT INTO school VALUES(2,'a2','101',60,88,77);
INSERT INTO school VALUES(3,'b1','102',65,84,35);
INSERT INTO school VALUES(4,'b2','102',88,60,40);
INSERT INTO school VALUES(5,'c1','103',45,80,35);
INSERT INTO school VALUES(6,'c2','103',90,88,66);
INSERT INTO school VALUES(7,'a3','101',100,100,100);
-- 5 
select * from school ;
-- 6
select name,chi from school where chi>=60 ;
-- 7
select name,chi,eng from school where chi>=60 and eng >=60 ;
-- 8
select name,eng from school where eng between 40 and 66 ;
-- 9
select name,chi,eng,mat from school where (chi>=60 and eng >=60 ) or mat between 70 and 80 ;
-- 10
select name,mat from school where mat == 70 or mat == 40 or mat ==66 ;
-- 另一種比較簡潔的方式
select name,mat from school where mat in (70,40,66);
-- 11
select * from school where mat not in (35,40,66);

另一個 1.建立data.db 其內的table名稱為 member 資料如下: name tel email john 0912345678 john@gmail.com marry 0987654321 marry@gmail.com tom 0987098765 tom@gmail.com wang 0966684281 wang@gmail.com

2.新增一sex 欄位及其資料如下 name tel email sex john 0912345678 john@gmail.com M marry 0987654321 marry@gmail.com F tom 0987098765 tom@gmail.com M wang 0966684281 wang@gmail.com M 3.新增一筆記錄如下 victor 0968765123 victor@gmail.com M 4.john 的電話改成 0986345678 5.新增一id 欄位及其資料如下 id name tel email sex 001 john 0986345678 john@gmail.com M 002 marry 0987654321 marry@gmail.com F 003 tom 0987098765 tom@gmail.com M 004 wang 0966684281 wang@gmail.com M 005 victor 0968765123 victor@gmail.comM 6.查詢總筆數 7.查詢會員為男性的筆數 8.查詢編號 003 的會員資料 9.查詢姓名victor 的電話及email 11.取出data01.db 12.刪除member內編號003的記錄 13.刪除member內所有記錄 14.刪除名稱number的table

-- 1.建立data.db 其內的table名稱為 member
CREATE TABLE member ( name varchar(20) , tel varchar(10), email varchar(100) );
-- 2.新增一sex 欄位及其資料如下
ALTER TABLE member add sex varchar(5);
--3.新增一筆記錄如下
INSERT INTO "table" VALUES('john','0912345678','john@gmail.com','M');
INSERT INTO "table" VALUES('marry','0987654321','marry@gmail.com','F');
INSERT INTO "table" VALUES('tom','0987098765','tom@gmail.com','M');
INSERT INTO "table" VALUES('wang','0966684281','wang@gmail.com','M');
INSERT INTO "table" VALUES('victor','0968765123','victor@gmail.com','M');

update member set tel='0986345678' where name='john' ;

ALTER TABLE member add id varchar(3);

update member set id="001" where name="john";
update member set id="002" where name="marry";
update member set id="003" where name="tom";
update member set id="004" where name="wang";
update member set id="005" where name="victor";

select count(*) from member;

select count(*) from member where sex='M';
 
select * from member where id='003';

select tel,email from member where name='victor';
-- 10
.clone data01.db
--12
delete from member where id="003" ;
-- 13

當從 csv 使用.import 讀入表時，記得 .mode 要切成 csv 不然欄位會怪怪

.mode csv
.import product.csv product
-- 有空白的欄位，就必須要使用 "" 刮起來
select "order id","order date" from orderlist;

select "order id","order date",orderlist."Customer ID", customer."Customer ID" from orderlist inner join customer on orderlist."Customer ID"=customer."Customer ID";

-- 來修改 product 中 price 欄位改成 integer
CREATE TABLE product1("Product ID" TEXT,"Category" TEXT,"Sub-Category" TEXT, "Product Name" TEXT,"Price" integer);
-- price > 50 這欄是會回傳結果 1或0
select price , price > 50 from product1;
-- 利用別張表的值 帶入新表
insert into product1 ("Product ID" ,"Category","Sub-Category","Product Name","Price") select
"Product ID" ,"Category","Sub-Category","Product Name","Price" from product ;
-- 以下兩個顯示的會一樣，但 product1 才是正確的數字，可以拿來做邏輯運算
select price*10 from product; -- text 欄位也可以 x10
select price*10 from product1; -- 這是 integer x10
--
.output data1.csv  -- 將接下來的顯示都丟進 data1.csv
select category , price from product1;
-- 畫面不會出現東西，可以打 .quit 去看 data1.csv
.output data2.csv
select category , price from product1 where price > 200;

select "Order ID","order date",customer."customer name",product."product name", product."price"  from orderlist inner join product on orderlist."product ID" = product."product ID" inner join customer on orderlist."customer id" = customer."customer id";

select orderlist."order id" ,product."product id", sum(price) from orderlist,product where orderlist."product id" = product."product id"  GROUP by orderlist."order id" HAVING sum(price) > 180;

::: :::spoiler 函式 group by , having ,order by,length(),Distinct ,Drop Table

-- like 用法 _ 為任意字元 % 為全部都可以
-- 以下例子：找第二個字為 a , 第四個字為 g
select * from person where name like '_a_g%';
-- 以j 為開頭
select * FROM person where name like 'j%' ;
-- 函式
abs()
coalease() ： 暫時取代 表中的 null 值
-- 把 company 欄位中若是 null 值的暫時取代成 'individual'
-- 不會改到欄位的內容 , 加上 as 把顯示的欄位變成 entity
SELECT firstname, lastname, coalesce(company, 'Individual') as entity FROM customers 

instr()
length()
trim
-- 計算表內的人數
select count(*) from person
-- 加上條件
select count(*) from school where class='102'
-- group by
select class,avg(chi) from school group by class ;
-- order by  , desc 降序
select * from school where class='101'  order by chi desc;

drop TABLE customertable;
--
select orderlist."order id" ,product."product id", sum(price) from orderlist,product where orderlist."product id" = product."product id"  GROUP by orderlist."order id" HAVING sum(price) > 180;

::: :::spoiler case 分級, output,import , inner join case 用法：包在 select 中，使用 case when then else end 的組合記得不要加逗號，因為在 select 每個,就是一個欄位

select name,chi ,case when chi>=80 then 'a grade' when chi>=60 then 'b grade' else 'c grade' end as gradechi from school;

output

.header on
.headers on
.mode csv
.output first.csv
select * from school;
.quit
-- 使用 bash 直接輸出
sqlite3 -header -csv c:/sqlite/chinook.db "select * from tracks;" > tracks.csv
-- import
-- 使用 csv 匯入的資料型態會全部都是 text
.import group.csv ingroup

--新表 , 一樣加在 testschool.db 中
CREATE TABLE data ( no text , name text , sex text , year integer,month integer );
insert into data values ('10811','a1','M',2010,4),('10821','b1','F',2011,5),('10831','c1','M',2012,6);

select school.name,school.class,data.name,data.sex,data.year from school inner join data on school.name=data.name;
-- 跟上面的結果會一樣的寫法
select school.name,school.class,data.name,data.sex,data.year from school,data where school.name = data.name ;

CREATE TABLE ordertable ( order_id varchar(10) PRIMARY key , pro_name text , count text);
CREATE TABLE customertable ( cus_id varchar(20) PRIMARY key  , cus_name varchar(20) , cus_email varchar(100) , cus_phone varchar (20) , cus_sex varchar(5) );
CREATE TABLE producttable ( pro_id varchar(10) PRIMARY key ,pro_name varchar(20)  ,pro_price integer );

::: :::spoiler 利用 insert into 搭配 select 把別張表的資料丟進去

CREATE TABLE school1 (id integer , name text , class text , chi text, eng text , mat text);
INSERT INTO school1 select * from school where class=101;

::: :::spoiler sqlite3 搭配 bash script , until do 先新增一個資料庫

sqlite3 test.db
create table test (name text,memo text);
insert into test values ("aa","m1"),("bb","m2");

echo 'select * from test;' > cmd
sqlite3 test.db < cmd # 把指令丟進去執行，會輸出 select 的結果
sqlite3 test.db < cmd >cmdout # 把結果再丟進新的檔案
cat cmdout
# 用這種導入的方式就是假設自己是在裡面操作
# 另外也可以直接接在 sqlite3 指令後面
#!/bin/bash
sqlite3 test.db  "create table n (id INTEGER PRIMARY KEY,f TEXT,l TEXT);"
sqlite3 test.db  "insert into n (f,l) values ('john','smith');"
sqlite3 test.db  "select * from n;"

#
#!/bin/bash
echo -e " 
1.新增一筆資料
2.刪除一筆資料
3.修改資料 
4.顯示全校資料
5.全校 各科總分 平均"
until [ "$ans" = "1" ] || [ "$ans" = "2" ] || [ "$ans" = "3" ]  || [ "$ans" = "4" ]  || [ "$ans" = "5" ]
do
read -p "請選擇1,2,3,4,5?" ans
case ${ans} in
1)
echo 1
read -p "請輸入姓名、班級、國文、英文、數學,5個欄位的資料,各欄位空一格  " name class chi eng mat
sqlite3 test.db  "create table school (id INTEGER PRIMARY KEY,name TEXT,class TEXT,chi INTEGER,eng INTEGER,mat INTEGER);"
sqlite3 test.db  "insert into school (name,class,chi,eng,mat) values ('${name}','${class}','${chi}','${eng}','${mat}');"
sqlite3 test.db  "select * from school;"
;;
2)
read -p "請輸入要刪除的 人名? " ans01
sqlite3 test.db " delete from school where name='${ans01}'; "
;;
3)
read -p "請輸入要編輯的 人名? " ans03
sqlite3 test.db " select * from school where name='${ans03}'; "
read -p "請輸入姓名、班級、國文、英文、數學,5個欄位的資料,各欄位空一格  " name class chi eng mat
sqlite3 test.db "update school set name='${name}',class='${class}',chi='${chi}',eng='${eng}',mat='${mat}' where name='${ans03}';"
;;
4)
echo 4
sqlite3 test.db  "select * from school;"
;;
5)
temp=$(sqlite3 test.db  "select  sum(chi),sum(eng),sum(mat),avg(chi),avg(eng),avg(mat) from school;")
echo $temp
;;
*)
echo other
exit
;;
esac
done

tags: `MySQL`

MySQL and MySQL Workbench 建置嶺東科大落地雲系統

MySQL 基本語法

MySQL 基本語法

SQLite 語法

tags: MySQL

tags: `MySQL`