mySQL - describe, ordered by, length 등 총 함수 설명

describe city;
# 테이블 보기
desc country;
# describe 줄여서 가능

desc countrylanguage;

select * from city;
select name, District from city where 5000000 <= Population and Population <= 80000000;
#전체 출력 * 맟 columns 지정해서 where통해 특정 column 연산 필터링 가능

select * from city where CountryCode like 'USA' and Population between 1000000 and 5000000;
#연산할 때 int 변수는 between 으로 해결 

select * from city where Name in('Seoul', 'Los Angeles');
#다수의 이름 검색 시 in을 사용, and 말고 ,!!! 그리고 () 필수
select * from city where CountryCode in ('KOR','JPN','USA');

SELECT * FROM country where Continent != 'Africa';
SELECT * from country where not Continent = 'Africa' ORDER BY Continent desc;
#not을 이용한 포함이 안되는 데이터 검색식 = 총 2가지 방법이 있음( not or ! )

select * from city where Name like 'Seou_';
select * from city where Name like 'S%' and CountryCode = 'KOR';
#이름이 기억이 안나면 like 에다가 %, %는 전체글자, _는 한글자

select * from city where CountryCode = ( select CountryCode from city where Name = 'Seoul');
#country코드가 기억 안날 때 seoul에 해당하는 country코드를 검색한다고 보면 됨
select * from city where CountryCode = ( select CountryCode from city where Name = 'San Miguel' and ID = '95');
select * from city where Population > all (select Population from city where Name = 'New York');
#조건만 population으로 변경한거

select * from city order by Population desc;
select * from city order by CountryCode, Population desc;
#초기값은 asc 내림차순인 desc로 수기 변경

select * from city where CountryCode = 'KOR' order by Population desc;
select Name, SurfaceArea from country order by SurfaceArea desc;
#역시나 다른 where 함수와 병행 가능, Countrycode가 KOR인 데이터 중 Population 내림차순 정렬

select distinct CountryCode from city;
# 중복 변수 제거 = 자바의 set or map

select * from city where CountryCode = 'CAN' limit 5;
#limit 상위 %개만 표시

select CountryCode, max(Population) from city group by CountryCode order by Population desc;
#max 즉 인구수의 최대값을 각각의 countryCode에 표시 = group by로 country를 기준으로 묶어줌
select name, SurfaceArea, min(SurfaceArea) from country group by Continent;
select name, CountryCode, avg(Population) as 'Average' from city group by CountryCode;
# as ''로 원하는 별칭으로 변경할 수 있음

select CountryCode, avg(Population) as 'City Population' from city group by CountryCode;

select count(*) as 'CityN' from city;
#행의 갯수
select avg(Population) from city;
#전체 도시의 평균 인구 수

select Name, max(Population) from city group by CountryCode having max(Population) >= 5000000;
#groub by 뒤에 where을 사용할 수 없으므로 having을 대신 사용 = having 사용 시 동일한 max나 min같은 조건문을 달아줘야됨

select name, CountryCode, Population, sum(Population) from city group by CountryCode, name with rollup;
#중간결산인 rollup, name과 countrycode로 묶은 값의 인구수 총합을 중간에 출력 = with 빼먹지 말것 (with rollup)

select count(*) nationcount from (SELECT DISTINCT countrycode FROM city)T1;
#서브쿼리 = NATIONCOUNT라는 변수에 COUNTRYCODE의 총 개수를 출력. 이 때 T1이라는 별칭을 넣어야 정상작동 = T1이라는 변수가 필요함

select Name, Continent, SurfaceArea, sum(SurfaceArea) from country group by Continent, name with rollup;
# 여기서 순서가 좀 중요함, groub by 할 때 continent를 먼저 잡고 name을 잡아야 continent 기준인 이름을 출력하고 그 surface의 총합을 rollup

select * from city join country on city.CountryCode = country.Code;
# join은 여러개의 테이블을 하나로 묶을때 사용, 말 그대로 JOIN 다만 한번에 묶으면 난리나기 때문에 on 이라는 함수로 조건을 넣어서 사용
# city의 countrycode와 country의 code가 같을 시 에만 join

select Code, country.Name, Language from country
							join countrylanguage on country.Code = countrylanguage.CountryCode
                            join city on country.Code = city.CountryCode;
#여러개의 테이블을 각각의 조건을 넣어서 하나로 합침

select length('123123123') as '길이';
select concat('123123' , 'qweqwe');
select locate('2','123');
#mySQL은 인덱스가 1부터 시작
select right('123456789', 3);
#선택한 String변수를 오른쪽부터 지정한 숫자만큼 반환
select left('123456789', 3);
#동일, 왼쪽부터
select upper('qwer'); select lower('QWER');
#대문자 & 소문자

select Name, Population from city where Population >= 1000000 order by Population desc limit 10;
select replace('My name is Tim', 'Tim', 'Kevin' );
#다음 문자 중에 > 다음 문자를 > 이 문자열로 대체

SELECT TRIM('   TEST    ');
SELECT TRIM(LEADING '#' FROM '###TEST###');
#앞쪽 지정 문자 제거, FROM 까먹지 말것 
SELECT TRIM(TRAILING '#' FROM '###TEST###');
#뒤쪽 제거

SELECT FORMAT('111111.22222',3);
#3자리씩 끊고 소수점도 3개로 끊음
SELECT FLOOR('1.5'), CEILING('1.4'), ROUND('1.4');
#순서대로 반내림, 반올림, 일반올림

SELECT ABS(-3), ROUND(RAND()*100, 0);
#RAND를 표시할 때 소수점이 DEFAULT이므로 ROUND를 같이써서 0부터 100까지의 랜덤숫자 추출

SELECT NOW();
# YYYY-MM-DD HH:MM:SS 형태로 반환
SELECT CURDATE();
# YYYY-MM-DD로 반환
SELECT CURTIME();
# 시간을 반환 HH-MM-DD
SELECT DATE(NOW());
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW()), HOUR(NOW()), MINUTE(NOW()), SECOND(NOW());
#위와 같이 현재 시간 지정해서 출력 가능

SELECT MONTHNAME(NOW());
#달 이름 출력 가능
SELECT DAYNAME(NOW());
#현재 요일 이름 출력 가능
SELECT DAYOFYEAR(NOW());
#1월 1일을 기준으로 얼마나 지났는지 확인 가능

SELECT DATE_FORMAT(NOW(), '%Y %M %D %HH %mm %SS');
#시간을 FORMAT을 줘서 출력 가능

CREATE TABLE TESTCITY AS SELECT * FROM city;
SELECT * FROM TESTCITY;
#CREATE과 AS SELECT * FROM (바꿀 이름)을 사용하여 원하는 테이블 복사

SELECT * FROM CITY WHERE ID BETWEEN 10 AND 20;

CREATE DATABASE TIM;
#CREATE DATABASE 를 사용하여 데이터베이스를 생성하고 USE를 사용하여 데이터베이스를 지정

#테이블을 오른쪽 클릭해서 수기로 만들 수 있지만 아래와 같이 지정하여 만들 수 있음
use tim;
CREATE TABLE test_Tables2(
	id int not null PRIMARY KEY,
    #Primary Key, 즉 각 행을 유일하게 식별해주는 Column = not null이라는 뜻은 꼭 입력해야한다는 듯, 따라서 not null로 지정할 수 밖에 없다
    pw int not null,

    phone int null
    #그럴 경우는 거의 없겠지만 핸드폰이 없을 수도 있으므로 not null로 지정하여 non-required로 설정
);

ALTER TABLE test_Tables2
ADD address CHAR;
#column 추가할 때 alter table 사용

ALTER TABLE test_Tables2
MODIFY pw FLOAT null;
#modify를 사용하여 column 타입 변경

ALTER TABLE test_Tables2
DROP address;
# DROP을 사용하여 address(특정 column)를 삭제

INSERT INTO dbtable (num, id, hiredate, deptno, sal)
#INSERT INTO = 해당 column대로
VALUES (11, 'smith', '2011-05-14', 20, 3400);
#다음의 값을 넣음

INSERT INTO dbtable VALUES (12, 'Tim', '2011-09-20', 30, 3000);
#만약 넣는 값이 전체의 column이고 같은 순서라면 위의 column 명칭을 생략할 수 있음

UPDATE dbtable SET deptno = 21 WHERE num = '12';
#업데이트 시 update를 사용하고 꼭 행을 지정해야함. 안그러면 전체 열이 바뀌어버림

DELETE FROM dbtable WHERE num = '16';
#꼭 where로 조건을 넣어야 함. 안 그러면 전체 삭제, rollback으로 되돌리기 가능, 휴지통에 보관 중과 같은 개념

TRUNCATE TABLE dbtable2;
#테이블 삭제 = 테이블은 삭제하지 않고 데이터만 삭제, delete과는 달리 용량도 줄어들고 인덱스 등도 모두 삭제, 복구 불가

DROP TABLE dbtable2;
#데이터 + 테이블 전체를 전부 삭제 = 절대 되돌리기 불가능!!!

DROP DATABASE tim;
#데이터베이스 전체를 삭제 = 되돌리기 불가능

#Tranaction 명령들
#--ROLLBACK
#--COMMIT

SELECT * FROM dbtable WHERE deptno is NULL;
#값이 null인 경우만 가져오는 것

SELECT* FROM dbtable WHERE deptno is not NULL;
#영어 그대로 null이 아닌 경우만 출력

SELECT * from dbtable ORDER BY sal desc LIMIT 3;


#인덱스를 사용하여 검색을 빠르게 할 수 있음.
#다만 수정이 되는 테이블을 인덱싱 하게 되면 테이블 처리 속도가 느려지므로 검색용도의 테이블만 인덱스를 사용하는 것이 좋음 

CREATE INDEX pwIDX on test_tables2(pw);
# pwIDX라는 인덱스를 test_tables2테이블에서 pw를 대상으로 생성

show INDEX from test_tables2;
#생성된 인덱스를 test_tables2에서 보여줌

ALTER TABLE test_table
ADD FULLTEXT IDX(NAME);
#FULLTEXT로 지정하는 이유는 INDEX로 검색을 할 때 다른 인덱스와는 달리 매우 빠르게 테이블의 모든 텍스트 컬럼을 검색

drop INDEX pwIDX on test_tables2;
#index를 삭제


#전체 테이블을 다 보여주기엔 너무 방대한 양이 있을 수 있기 떄문에 가상테이블을 하나 만들어서 필요한 부분만 보여줄 수 있음. = 쿼리 재사용 가능
#가상의 테이블이기 때문에 수정은 불가능하고 VIEW, 즉 보기만 가능하다. = 삽입, 삭제에 많은 제한사항이 있음.
#가상의 테이블에 여러가지 테이블이나 VIEW를 하나로 볼 수 있지만 정의된 VIEW는 변경이 불가능하고 인덱스를 지정할 수 없다.

CREATE VIEW testView as SELECT num, id from dbtable;
#testView라는 이름을 가지고있는 dbtable의 num과 id의 column값을 출력한다.

SELECT * FROM testView;
#생성한 view를 호출

ALTER VIEW testView as SELECT num, id, hiredate FROM dbtable;
#이미 호출한 VIEW를 수정 - ALTER사용

DROP VIEW testView;
#VIEW를 삭제

#여러 조건을 줘서 cityView를 생성
CREATE VIEW cityView as
SELECT city.name, country.SurfaceArea, countrylanguage.language FROM city 
					JOIN country on city.countrycode = country.code
                    JOIN countrylanguage on  city.countrycode = countrylanguage.countrycode
                    WHERE city.countrycode = 'KOR';
                    
SELECT * FROM cityView;

 

describe city;
# 테이블 보기
desc country;
# describe 줄여서 가능

desc countrylanguage;

select * from city;
select name, District from city where 5000000 <= Population and Population <= 80000000;
#전체 출력 * 맟 columns 지정해서 where통해 특정 column 연산 필터링 가능

select * from city where CountryCode like 'USA' and Population between 1000000 and 5000000;
#연산할 때 int 변수는 between 으로 해결 

select * from city where Name in('Seoul', 'Los Angeles');
#다수의 이름 검색 시 in을 사용, and 말고 ,!!! 그리고 () 필수
select * from city where CountryCode in ('KOR','JPN','USA');

SELECT * FROM country where Continent != 'Africa';
SELECT * from country where not Continent = 'Africa' ORDER BY Continent desc;
#not을 이용한 포함이 안되는 데이터 검색식 = 총 2가지 방법이 있음( not or ! )

select * from city where Name like 'Seou_';
select * from city where Name like 'S%' and CountryCode = 'KOR';
#이름이 기억이 안나면 like 에다가 %, %는 전체글자, _는 한글자

select * from city where CountryCode = ( select CountryCode from city where Name = 'Seoul');
#country코드가 기억 안날 때 seoul에 해당하는 country코드를 검색한다고 보면 됨
select * from city where CountryCode = ( select CountryCode from city where Name = 'San Miguel' and ID = '95');
select * from city where Population > all (select Population from city where Name = 'New York');
#조건만 population으로 변경한거

select * from city order by Population desc;
select * from city order by CountryCode, Population desc;
#초기값은 asc 내림차순인 desc로 수기 변경

select * from city where CountryCode = 'KOR' order by Population desc;
select Name, SurfaceArea from country order by SurfaceArea desc;
#역시나 다른 where 함수와 병행 가능, Countrycode가 KOR인 데이터 중 Population 내림차순 정렬

select distinct CountryCode from city;
# 중복 변수 제거 = 자바의 set or map

select * from city where CountryCode = 'CAN' limit 5;
#limit 상위 %개만 표시

select CountryCode, max(Population) from city group by CountryCode order by Population desc;
#max 즉 인구수의 최대값을 각각의 countryCode에 표시 = group by로 country를 기준으로 묶어줌
select name, SurfaceArea, min(SurfaceArea) from country group by Continent;
select name, CountryCode, avg(Population) as 'Average' from city group by CountryCode;
# as ''로 원하는 별칭으로 변경할 수 있음

select CountryCode, avg(Population) as 'City Population' from city group by CountryCode;

select count(*) as 'CityN' from city;
#행의 갯수
select avg(Population) from city;
#전체 도시의 평균 인구 수

select Name, max(Population) from city group by CountryCode having max(Population) >= 5000000;
#groub by 뒤에 where을 사용할 수 없으므로 having을 대신 사용 = having 사용 시 동일한 max나 min같은 조건문을 달아줘야됨

select name, CountryCode, Population, sum(Population) from city group by CountryCode, name with rollup;
#중간결산인 rollup, name과 countrycode로 묶은 값의 인구수 총합을 중간에 출력 = with 빼먹지 말것 (with rollup)

select count(*) nationcount from (SELECT DISTINCT countrycode FROM city)T1;
#서브쿼리 = NATIONCOUNT라는 변수에 COUNTRYCODE의 총 개수를 출력. 이 때 T1이라는 별칭을 넣어야 정상작동 = T1이라는 변수가 필요함

select Name, Continent, SurfaceArea, sum(SurfaceArea) from country group by Continent, name with rollup;
# 여기서 순서가 좀 중요함, groub by 할 때 continent를 먼저 잡고 name을 잡아야 continent 기준인 이름을 출력하고 그 surface의 총합을 rollup

select * from city join country on city.CountryCode = country.Code;
# join은 여러개의 테이블을 하나로 묶을때 사용, 말 그대로 JOIN 다만 한번에 묶으면 난리나기 때문에 on 이라는 함수로 조건을 넣어서 사용
# city의 countrycode와 country의 code가 같을 시 에만 join

select Code, country.Name, Language from country
join countrylanguage on country.Code = countrylanguage.CountryCode
                            join city on country.Code = city.CountryCode;
#여러개의 테이블을 각각의 조건을 넣어서 하나로 합침

select length('123123123') as '길이';
select concat('123123' , 'qweqwe');
select locate('2','123');
#mySQL은 인덱스가 1부터 시작
select right('123456789', 3);
#선택한 String변수를 오른쪽부터 지정한 숫자만큼 반환
select left('123456789', 3);
#동일, 왼쪽부터
select upper('qwer'); select lower('QWER');
#대문자 & 소문자

select Name, Population from city where Population >= 1000000 order by Population desc limit 10;
select replace('My name is Tim', 'Tim', 'Kevin' );
#다음 문자 중에 > 다음 문자를 > 이 문자열로 대체

SELECT TRIM('   TEST    ');
SELECT TRIM(LEADING '#' FROM '###TEST###');
#앞쪽 지정 문자 제거, FROM 까먹지 말것 
SELECT TRIM(TRAILING '#' FROM '###TEST###');
#뒤쪽 제거

SELECT FORMAT('111111.22222',3);
#3자리씩 끊고 소수점도 3개로 끊음
SELECT FLOOR('1.5'), CEILING('1.4'), ROUND('1.4');
#순서대로 반내림, 반올림, 일반올림

SELECT ABS(-3), ROUND(RAND()*100, 0);
#RAND를 표시할 때 소수점이 DEFAULT이므로 ROUND를 같이써서 0부터 100까지의 랜덤숫자 추출

SELECT NOW();
# YYYY-MM-DD HH:MM:SS 형태로 반환
SELECT CURDATE();
# YYYY-MM-DD로 반환
SELECT CURTIME();
# 시간을 반환 HH-MM-DD
SELECT DATE(NOW());
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW()), HOUR(NOW()), MINUTE(NOW()), SECOND(NOW());
#위와 같이 현재 시간 지정해서 출력 가능

SELECT MONTHNAME(NOW());
#달 이름 출력 가능
SELECT DAYNAME(NOW());
#현재 요일 이름 출력 가능
SELECT DAYOFYEAR(NOW());
#1월 1일을 기준으로 얼마나 지났는지 확인 가능

SELECT DATE_FORMAT(NOW(), '%Y %M %D %HH %mm %SS');
#시간을 FORMAT을 줘서 출력 가능

CREATE TABLE TESTCITY AS SELECT * FROM city;
SELECT * FROM TESTCITY;
#CREATE과 AS SELECT * FROM (바꿀 이름)을 사용하여 원하는 테이블 복사

SELECT * FROM CITY WHERE ID BETWEEN 10 AND 20;

CREATE DATABASE TIM;
#CREATE DATABASE 를 사용하여 데이터베이스를 생성하고 USE를 사용하여 데이터베이스를 지정

#테이블을 오른쪽 클릭해서 수기로 만들 수 있지만 아래와 같이 지정하여 만들 수 있음
use tim;
CREATE TABLE test_Tables2(
id int not null PRIMARY KEY,
    #Primary Key, 즉 각 행을 유일하게 식별해주는 Column = not null이라는 뜻은 꼭 입력해야한다는 듯, 따라서 not null로 지정할 수 밖에 없다
    pw int not null,

    phone int null
    #그럴 경우는 거의 없겠지만 핸드폰이 없을 수도 있으므로 not null로 지정하여 non-required로 설정
);

ALTER TABLE test_Tables2
ADD address CHAR;
#column 추가할 때 alter table 사용

ALTER TABLE test_Tables2
MODIFY pw FLOAT null;
#modify를 사용하여 column 타입 변경

ALTER TABLE test_Tables2
DROP address;
# DROP을 사용하여 address(특정 column)를 삭제

INSERT INTO dbtable (num, id, hiredate, deptno, sal)
#INSERT INTO = 해당 column대로
VALUES (11, 'smith', '2011-05-14', 20, 3400);
#다음의 값을 넣음

INSERT INTO dbtable VALUES (12, 'Tim', '2011-09-20', 30, 3000);
#만약 넣는 값이 전체의 column이고 같은 순서라면 위의 column 명칭을 생략할 수 있음

UPDATE dbtable SET deptno = 21 WHERE num = '12';
#업데이트 시 update를 사용하고 꼭 행을 지정해야함. 안그러면 전체 열이 바뀌어버림

DELETE FROM dbtable WHERE num = '16';
#꼭 where로 조건을 넣어야 함. 안 그러면 전체 삭제, rollback으로 되돌리기 가능, 휴지통에 보관 중과 같은 개념

TRUNCATE TABLE dbtable2;
#테이블 삭제 = 테이블은 삭제하지 않고 데이터만 삭제, delete과는 달리 용량도 줄어들고 인덱스 등도 모두 삭제, 복구 불가

DROP TABLE dbtable2;
#데이터 + 테이블 전체를 전부 삭제 = 절대 되돌리기 불가능!!!

DROP DATABASE tim;
#데이터베이스 전체를 삭제 = 되돌리기 불가능

#Tranaction 명령들
#--ROLLBACK
#--COMMIT

SELECT * FROM dbtable WHERE deptno is NULL;
#값이 null인 경우만 가져오는 것

SELECT* FROM dbtable WHERE deptno is not NULL;
#영어 그대로 null이 아닌 경우만 출력

SELECT * from dbtable ORDER BY sal desc LIMIT 3;


#인덱스를 사용하여 검색을 빠르게 할 수 있음.
#다만 수정이 되는 테이블을 인덱싱 하게 되면 테이블 처리 속도가 느려지므로 검색용도의 테이블만 인덱스를 사용하는 것이 좋음 

CREATE INDEX pwIDX on test_tables2(pw);
# pwIDX라는 인덱스를 test_tables2테이블에서 pw를 대상으로 생성

show INDEX from test_tables2;
#생성된 인덱스를 test_tables2에서 보여줌

ALTER TABLE test_table
ADD FULLTEXT IDX(NAME);
#FULLTEXT로 지정하는 이유는 INDEX로 검색을 할 때 다른 인덱스와는 달리 매우 빠르게 테이블의 모든 텍스트 컬럼을 검색

drop INDEX pwIDX on test_tables2;
#index를 삭제


#전체 테이블을 다 보여주기엔 너무 방대한 양이 있을 수 있기 떄문에 가상테이블을 하나 만들어서 필요한 부분만 보여줄 수 있음. = 쿼리 재사용 가능
#가상의 테이블이기 때문에 수정은 불가능하고 VIEW, 즉 보기만 가능하다. = 삽입, 삭제에 많은 제한사항이 있음.
#가상의 테이블에 여러가지 테이블이나 VIEW를 하나로 볼 수 있지만 정의된 VIEW는 변경이 불가능하고 인덱스를 지정할 수 없다.

CREATE VIEW testView as SELECT num, id from dbtable;
#testView라는 이름을 가지고있는 dbtable의 num과 id의 column값을 출력한다.

SELECT * FROM testView;
#생성한 view를 호출

ALTER VIEW testView as SELECT num, id, hiredate FROM dbtable;
#이미 호출한 VIEW를 수정 - ALTER사용

DROP VIEW testView;
#VIEW를 삭제

#여러 조건을 줘서 cityView를 생성
CREATE VIEW cityView as
SELECT city.name, country.SurfaceArea, countrylanguage.language FROM city 
JOIN country on city.countrycode = country.code
                    JOIN countrylanguage on  city.countrycode = countrylanguage.countrycode
                    WHERE city.countrycode = 'KOR';
                    
SELECT * FROM cityView;