MySQL & MariaDB 기본 명령어

DB 접속

mysql -u [계정ID] -p;

mysql -u root -p;

 

 

있는 데이터 사용하기

USE mydb;

 

테이블의 구조 확인하기 dept는 테이블이름

-- DESC 테이블 구조를 확인하는 명령어
DESC dept

 

테이블의 전체 데이터 보기 ( * 이 전체를 의미)

SELECT * FROM emp;

 

 

테이블에 필요한 데이터 보기 

- where 어디에 조건식 줄지 써주고 and, or, in으로 조건식 넣어주기

SELECT empno, ename FROM emp WHERE empno > 7700 AND empno <7800;

SELECT * FROM emp WHERE job = 'CLERK' OR job = 'MANAGER';

SELECT * FROM emp WHERE ename IN('allen', 'ward', 'martin', 'adams');

-- BETWEEN은  dbms 중에서 오라클하고 mysql등만 있음
SELECT customerNumber, customerName
FROM customers
WHERE creditLimit BETWEEN 21000 AND 59700;

 

 

- SUBSTR() 함수

substr(필드명, 첫번째 컬럼, 개수) 프로그램언어는 첫번째가 항상 0 이었음 그러나 데이터베이스는 1이다.
문자열을 잘라낸다, 필드나 수식뒤에 새로운 필드명을 부여할 수 있다. as 필드명, as는 생략가능 이 기능을 alias라고 한다. (별명)

-- 입사일이 1980년대인 사람
SELECT * FROM emp WHERE SUBSTR(hiredate, 1, 4) = '1980';

SELECT SUBSTR(hiredate, 1, 4) AS YEAR, SUBSTR(hiredate, 6,2) AS MONTH,
SUBSTR(hiredate,9,2) AS DAY
FROM emp;

- 문자열 ( %, LIKE절)

-- 문자열의 경우에 s로 시작하는  %와일드 카드는 like연산자랑
-- s로 시작
SELECT * FROM emp
WHERE ename LIKE 'S%';

-- 중간에 s로 포함하는
SELECT * FROM emp
WHERE ename LIKE '%S%';

-- R로 끝나는
SELECT * FROM emp
WHERE ename LIKE '%R';

 

 

 

 

테이블 null값 확인

ifnull (mariadb인경우, mssql은 isnull임)

-- 연봉 + comm (커미션)
-- null 연산불가능한, 수학으로 따지면 무한대, null값에는 연산을 수행할 수 없다.
-- ifnull(필드명, 값)- 해당필드의 값이 null이 아니면 해당필드값을 반환, mssqul에서는 isnull
-- null이면 값을 반환
SELECT empno, ename, sal+ifnull(comm,0) AS pay FROM emp;

 

 

 

테이블에 데이터 넣을때

INSERT INTO dept(deptno, dname) VALUES(50, '홍보부');
INSERT INTO dept VALUES(60, '개발1부','부산');

 

 

 

PRIMARY KEY 추가, 삭제 emp는 테이블 이름임, empno는 열의 이름, 각자 테이블 db로 하면 됨

ALTER TABLE emp ADD PRIMARY KEY (empno);

ALTER TABLE emp DROP PRIMARY KEY;

 

foreign key 설정하기

ALTER TABLE emp ADD FOREIGN KEY(deptno) REFERENCES dept(deptno);

 

 

데이터 새로 만들기

CREATE TABLE company
( 
	id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
	company_name VARCHAR(100),
	company_phone VARCHAR(20),
	company_email VARCHAR(40)
);
INSERT INTO company (company_name, company_phone, company_email ) 
VALUES('Tom''s company', '010-0000-0000','tom@naver.com');

SELECT * FROM company;

결과값

 

중복된 데이터 제거하고 테이블 보기 (orders 테이블 STATS열에 중복된 제거 제거)

-- distinct 중복값 제거해서 결과값 보여줌
SELECT distinct STATUS FROM orders;

 

테이블 복사

-- 테이블 복사 명령어 따로 없고 서브쿼리를 이용해서 테이블 만들기
-- 1. 구조복사 1=0는 where 조건절이 언제가 false라서 데이터 안도고 구조만 복사
-- primary key나 foreign key등은 안 온다.
CREATE TABLE emp2 AS SELECT * FROM emp WHERE 1=0;
SELECT * FROM emp2;
DESC emp2;

-- 2. 전부복사
CREATE TABLE emp3 AS SELECT * FROM emp;

-- 3. 특정필드만 특정 데이터만 복사
CREATE TABLE emp4 AS SELECT empno, ename, sal FROM emp
WHERE deptno IN (10, 30);
SELECT * FROM emp4;

-- primary key 주고나서
ALTER TABLE emp ADD CONSTRAINT PK_EMP PRIMARY KEY (empno);
SELECT * FROM emp;
DESC emp;

CREATE TABLE emp5 AS SELECT * FROM emp;
DESC emp5; -- primary key는 복사되지 않는다.

 

테이블 변경 UPDATE

UPDATE emp SET gender='F';

UPDATE emp SET gender='M' WHERE empno IN (7369, 7521, 7782, 7839,7566, 8000, 8001, 8002);

 

 

테이블 일부 가져오기 LIMIT( customers 테이블이 있고, 거기서 일부만 추출 )

-- 데이터 10개만 가져온다.
SELECT * FROM customers LIMIT 10;
-- 9번째 레코드부터 5개 가져오기
SELECT * FROM customers LIMIT 9,5;

-- 고객번호 desc 정렬을 해서 limit를 가져온다, paging 할때 많이 씀
SELECT * FROM customers ORDER BY customerNumber DESC
LIMIT 0, 10;

SELECT * FROM customers ORDER BY customerNumber DESC
LIMIT 10, 10;

SELECT * FROM customers ORDER BY customerNumber DESC
LIMIT 20, 10;

 

테이블 건수세기 COUNT

-- empno 필드에 null이 있으면 카운트에서 빠진다.
SELECT COUNT(empno) FROM emp;
SELECT COUNT(deptno) FROM emp;
SELECT COUNT(comm) FROM emp;

-- *로하면 제일 많은걸로 가지고옴
SELECT COUNT(*) FROM emp;

 

테이블 그룹함수

-- 그룹함수가 max, min, sum, avg, count 있음
SELECT MAX(sal), MIN(sal), SUM(sal), AVG(sal), COUNT(*) FROM emp;

 

 

 

테이블 GROUP BY

-- 부서별로 인원이 몇명씩 있나 확인
SELECT deptno, COUNT(*)
FROM emp
GROUP BY deptno;

-- sal은 데이터 개수만큼 나오고 deptno는 그룹별로 나옴, GROUP BY 뒤에 있는 값이 SELECT 뒤에 나와줘야함
-- 밑에 쿼리는 원래 에러 나와야 하는데 오라클 이런데서
-- 마리아db는 뭔가 나옴, 원래는 밑에 밑에 쿼리 처럼 조인으로 해야함
SELECT sal, deptno, MAX(sal),MIN(sal)
FROM emp
GROUP BY deptno;


SELECT empno, ename, sal, maxsal, minsal
FROM emp A
LEFT OUTER JOIN(
	SELECT deptno, MAX(sal) maxsal ,MIN(sal) minsal
	FROM emp
	GROUP BY deptno
)B ON A.deptno = B.deptno;