【從零開始學習 MySql 資料庫】(1) 建表與簡單查詢

DylanAndroid發表於2019-02-13
MySql資料庫
#檢視伺服器中的資料庫
SHOW DATABASES;

#建立一個資料庫
CREATE DATABASE IF NOT EXISTS test;

#使用test資料庫
USE test;

#建表
-- 表名丌能超過 30 個字元
-- 表名、列名是自由定義的
-- 所有的 SQL 語句都是以“ ; ”結尾
CREATE TABLE user_test(
            id  int,
            password varchar(6),
            name varchar(20),
            phone varchar(11),
            email varchar(20)
)；

#修改表的某一列
alter table user_test modify email varchar(50);

#插入資料
insert into user_test values(1001,`123456`,`張三`,`13468857714`,`linglongxin24@163.com`);

#查詢資料
SELECT * from user_test;

#檢視錶結構
desc user_test;

#查詢姓名為張三的記錄
SELECT name FROM user_test where name=`張三`;

#建立部門表
CREATE TABLE dept_test(
dept_id int(2),
dept_name CHAR(20),
dept_location CHAR(20)
);

#檢視部門表結構
desc dept_test;


#給部門表插入資料
INSERT INTO dept_test VALUES(10,`developer`,`beijing`);
INSERT INTO dept_test VALUES(20,`account`,`shanghai`);
INSERT INTO dept_test VALUES(30,`sales`,`guangzhou`);
INSERT INTO dept_test VALUES(40,`operations`,`tianjin`);

#檢視部門表資料
SELECT * FROM dept_test;

#建立員工表
CREATE TABLE emp_test(
            emp_id INT(4),
            name VARCHAR(20),
            job VARCHAR(20),
            salary FLOAT(7,2),
            bonus FLOAT(7,2),
            hire_date DATE,
            manager INT(4),
            dept_test_id INT(2)
);

#檢視員工表結構
DESC emp_test;

#檢視員工表資料
SELECT * FROM EMP_TEST;


#格式化日期用DATE_FORMAT(date,format)和Oracle有區別，Oracle使用用TO_DATE(ch, fmt)
INSERT INTO emp_test VALUES(1001,`張無忌`,`Manager`,`10000`,`2000`,DATE_FORMAT(`2010-01-12`,`%y-%m-%d`),1005,10);
INSERT INTO emp_test VALUES(1002,`劉蒼松`,`Analyst`, 8000 , 1000 ,DATE_FORMAT(`2011-01-12`,`%y-%m-%d`),1001,10);
INSERT INTO emp_test VALUES(1003, `李翊` ,`Analyst`,9000 , 1000 ,DATE_FORMAT(`2010-02-11`,`%y-%m-%d`),1001,10);
INSERT INTO emp_test VALUES(1004,`郭芙蓉`,`Programmer`,5000, NULL ,DATE_FORMAT(`2010-02-11`,`%y-%m-%d`),1001,10);
INSERT INTO emp_test VALUES(1005 , `張三丰` , `President` ,15000 , NULL ,DATE_FORMAT(`2008-02-15`,`%y-%m-%d`),NULL,20);
INSERT INTO emp_test VALUES(1006 , `燕小六` , `Manager` ,5000 ,400 ,DATE_FORMAT(`2009-02-12`,`%y-%m-%d`) , 1005 , 20);
insert into emp_test values(1007 , `陸無雙` , `clerk` ,3000 , 500 , DATE_FORMAT(`2009-02-01`,`%y-%m-%d`) , 1006 , 20) ;
insert into emp_test values(1008 , `黃蓉` , `Manager` ,5000 , 500 , DATE_FORMAT(`2009-11-01`,`%y-%m-%d`) , 1005 , 30) ;
insert into emp_test values(1009 , `韋小寶` , `salesman` ,4000 , null , DATE_FORMAT(`2009-05-20`,`%y-%m-%d`) , 1008 , 30) ;
insert into emp_test values(1010 , `郭靖` , `salesman` ,4500 , 500 , DATE_FORMAT(`2009-10-10`,`%y-%m-%d`) , 1008 , 30) ;

#檢視員工表資料
SELECT * FROM emp_test;

#檢視部門表資料
SELECT * FROM dept_test;

#檢視員工姓名、月薪、年薪
SELECT name,salary,salary*12 year_salary
    FROM emp_test;

#檢視員工姓名、月薪、獎金、月實發工資
#非空判斷用IFNULL(expr1,expr2)和Oracle有區別，Oracle用NVL(expr1, expr2)
SELECT name,salary,bonus,salary+ IFNULL(bonus,0) month_salary
    FROM emp_test;
    INSERT INTO emp_test (emp_id,name) VALUES(1011,`於澤成`);

#查詢 emp_test 表 , 如果沒有職位 , 顯示`no position` , 如果有職位 , 顯示員工的職位
SELECT name,IFNULL(job,`no positon`) job
            FROM emp_test;

#查詢 emp_test 表 , 如果沒有入職時間 , 顯示為 2011 年 10 月 10 日 , 否則原樣顯示
SELECT name,IFNULL(hire_date,`2016-12-12`) hire_date
            FROM emp_test;

#連線字串用CONCAT(str1,str2,...)和Oracle有區別，Oracle用||
SELECT emp_id,CONCAT(name,` job is `,job) detail
            FROM emp_test;

#複製表
CREATE TABLE emp_test2 AS SELECT * FROM emp_test;
SELECT * FROM emp_test2;

#DISTINCT注意：distinct 必須( 只能 )跟在 select 後邊，distinct 指所有列的唯一組合
#機構中有多少種職位？
SELECT DISTINCT job FROM emp_test;
#員工分佈在哪些部門？
SELECT DISTINCT dept_test_id FROM emp_test;
#查詢每個部門不重複的職位
SELECT DISTINCT job,dept_test_id FROM emp_test;

#薪水高亍 10000 元的員工資料？
SELECT * FROM emp_test WHERE salary>10000; 

#職位是 Analyst 的員工資料？SQL 語句大小寫丌敏感 , 資料大小寫敏感
SELECT * FROM emp_test WHERE LOWER(job)=`analyst`; 

#薪水大亍 5000 並且小亍 10000 的員工資料？>=<=；between and
SELECT * FROM emp_test WHERE salary>=5000 AND salary<=10000; 
SELECT * FROM emp_test WHERE salary BETWEEN 5000 AND 10000; 

#入職時間在 2011 年的員工？
#錯誤寫法
SELECT * FROM emp_test WHERE hire_date=DATE_FORMAT(`2011`,`%y`); 
#正確寫法
SELECT * FROM emp_test WHERE hire_date BETWEEN DATE_FORMAT(`2011-01-01`,`%y-%m-%d`) AND DATE_FORMAT(`2011-12-31`,`%y-%m-%d`) ; 

#列出職位是 Manager 或者 Analyst 的員工
SELECT * FROM emp_test WHERE job IN(`Manager`,`Analyst`);

#列出職位中包含有 sales 字元的員工資料？
SELECT * FROM emp_test WHERE LOWER(job) LIKE `%sales%`

#列出職位中第二個字元是 a 的員工資料？
SELECT * FROM emp_test WHERE LOWER(job) LIKE `_a%`

#查詢資料庫中有多少個名字中包含 `EMP` 的表？
#SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME LIKE `%EMP%`;

insert into emp_test values(1012 , `text_test` , `salesman` ,4500 , 500 , DATE_FORMAT(`2009-10-10`,`%y-%m-%d`) , 1008 , 30) ;

SELECT * FROM emp_test;

#mysql-- 如果要查詢的資料中有特殊字元( 比如_或% ),
-- 在做模糊查詢時 ,
-- 需要加上符號表示轉義 , 如果是Oracle並且用 escape 短語指明轉義字元mysql不用
SELECT name FROM emp_test WHERE name LIKE `%\_%`;
DELETE FROM emp_test WHERE emp_id=1012 

#查詢哪些員工沒有獎金？
SELECT * FROM emp_test WHERE bonus IS NULL;

#薪水不在 5000 至 8000 的員工？
SELECT * FROM emp_test WHERE salary NOT BETWEEN 5000 AND 8000;

#不是部門 20 和部門 30 的員工？
SELECT * FROM emp_test WHERE dept_test_id NOT IN(20,30);複製程式碼

相關文章