Please enable JavaScript.
Coggle requires JavaScript to display documents.
MYSQL (table (create (創建資料表:CREATE TABLE EX: CREAT TABLE table name( …
MYSQL
table
structure
colunm
row
header
create
創建資料表:CREATE TABLE
EX:
CREAT TABLE table name(
name VARCHAR(10),
phone VARCHAR(20),
age INT
);
使用文件創建資料
source path
EX:
source D:\test.sql
READ and WHERE
(filter data)
select
from test where
條件
EX:
select
from test
where salary =8100
select
from test
where
salary =8100 or name not "andy"
update
修改資料
update
<table name>
set
<column name><條件> (<filter>)
EX:
*update
employee
set
salary = 10000 where id ="ABC"*
delete
刪除資料 delete from <database name>(where column=n)
ex:
Delete from movie where salary=1000
字串處理
連接:CONCAT、CONCAT_WS
EX:
CONCAT("a","b")="ab"
CONCAT_WD("-","a","b","c")="a-b-c"
擷取字串:SUBSTRING
EX:
slelect SUBSTRING("Hello world", 1,4)="Hell"
slelect SUBSTRING("Hello world", 7)="world"
slelect SUBSTRING("Hello world", -3)="rld"
ADVANCE:
select CONCAT(column1 ,column2,SUBSTRING(column3 ,n)) as <new_header>
取代:REPLACE
EX:selelct REPLACE("Hello world", "world","mySQL")="Hello mySQL",
反轉:REVERSE
EX:selelct REVERSE("Hello world")=dlrow olleH"",
字串長度:CHAR_LENGTH
EX:selelct CHAR_LENGTH("Hello world")=11
大小寫轉換:UPPER AND LOWER
selelct UPPER("Hello world")="HELLO WORLD:
selelct LOWER("Hello world")="hello world:
data type
numeric types
INT
FLOAT
string types
CHAR
VARCHAR()
EMUN("a","ab")
databases
slelect database();
show databases;
create database <name>;
drop database <name>;
use <database name>;