mySQL - DML

13 Jun 2021 in Programming on SQL

SELECT

SELECT * FROM DEPARTMENT;
select name, hiredate from employee where hiredate
select name, deptno from employee where deptno = 30;
select name, deptno from employee where deptno in (10, 30);
select name, job from employee where name like '%A%';
SELECT UPPER('SEoul'), UCASE('seOUL');
SELECT LOWER('SEoul'), LCASE('seOUL');
SELECT SUBSTRING('Happy Day',3,2); --세번째 문자부터 두개, 결과로 pp 반환
SELECT LPAD('hi',5,'?'),LPAD('joe',7,'*');
SELECT LTRIM(' hello '), RTRIM(' hello ');
SELECT TRIM(' hi '),TRIM(BOTH 'x' FROM 'xxxhixxx');
SELECT ABS(2), ABS(-2);
SELECT MOD(234,10), 253 % 7, MOD(29,9);
select cast(now() as date);
select cast(1-2 as unsigned);
SELECT AVG(salary) , SUM(salary)FROM employee WHERE deptno = 30;

INSERT

insert into ROLE (role_id, description) values ( 200, 'CEO');

UPDATE

• where 절 꼭 써주기. 안그러면 모두 description=’CTO’ 로 된다.

update ROLE set description = 'CTO'where role_id = 200;

DELETE

delete from ROLE where role_id = 200;