- INSERT 문
123456INSERT INTO DEPARTMENT VALUES(5, '연구', '');INSERT INTO tmember(no, dept, name, sex)VALUES(201812345, '스마트미디어학과', '홍길동', '남');"
- DELETE 문
12345DELETE FROM DEPARTMENTWHERE DEPTNO = 4;DELETE FROM tmemberWHERE dept='사회복지학과';
- UPDATE 문
12345678910111213UPDATE EMPLOYEESETDNO = 3,SALARY = SALARY * 1.05WHEREEMPNO = 2106;UPDATE tmemberSETdept='스마트미디어학과'WHEREno=201892016;
- SELECT 문
1234567891011121314151617181920212223242526272829SELECT * FROM DEPARTMENT;SELECT DEPTNO, DEPTNAME FROM DEPARTMENT;SELECT TITLE FROM EMPLOYEE;SELECT DISTINCT TITLE FROM EMPLOYEE;SELECT * FROM EMPLOYEE WHERE DNO = 2;SELECT * FROM EMPLOYEE WHERE DNO <> 2;SELECT * FROM EMPLOYEE WHERE EMPNAME LIKE '이%';SELECT FLOOR FROM DEPARTMENT WHERE DEPTNAME='영업' OR DEPTNAME='개발';SELECT * FROM EMPLOYEE WHERE SALARY BETWEEN 3000000 AND 4500000;SELECT * FROM EMPLOYEE WHERE SALARY >= 3000000 AND SALARY <= 4500000;SELECT * FROM EMPLOYEE WHERE DNO IN (1, 3);SELECT * FROM EMPLOYEE WHERE DNO NOT IN (1, 3);SELECT EMPNAME, SALARY, SALARY * 1.1 AS NEWSALARY FROM EMPLOYEE;SELECT * FROM EMPLOYEE WHERE MANAGER IS NULL;SELECT * FROM EMPLOYEE WHERE MANAGER IS NOT NULL;SELECT * FROM EMPLOYEE WHERE DNO = 2 ORDER BY SALARY;SELECT * FROM EMPLOYEE WHERE DNO = 2 ORDER BY SALARY DESC;SELECT * FROM EMPLOYEE WHERE DNO = 2 ORDER BY SALARY, NAME;SELECT AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL FROM EMPLOYEE;SELECT DNO, AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL FROM EMPLOYEE GROUP BY DNO;SELECT DNO, AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL FROM EMPLOYEE GROUP BY DNO HAVING AVG(SALARY) >= 2500000;
- CREATE 문
12345678910111213141516171819202122232425262728293031323334CREATE TABLE tmember (no INTEGER NOT NULL,dept TEXT DEFAULT '스마트미디어학과',name TEXT NOT NULL,sex TEXT DEFAULT '여' CHECK(SEX in ('남','여')),hp TEXT UNIQUE,post INTEGER,PRIMARY KEY(no));CREATE TABLE tbook (no INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,title TEXT,author TEXT,publ TEXT,yea INTEGER,isbn TEXT,symb TEXT);CREATE TABLE tlend (bno INTEGER,mno INTEGER,cdate TEXT DEFAULT CURRENT_TIMESTAMP,days INTEGER,PRIMARY KEY(cdate),FOREIGN KEY(bno) REFERENCES tbook(no)on update CASCADEon delete CASCADE,FOREIGN KEY(mno) REFERENCES tmember(no)on update CASCADEon delete set NULL);
- DROP 문
1DROP TABLE employee;
- JOIN 문
12345678910SELECT tlend.mno as hakbun,tmember.name as name,tbook.title as title,tlend.cdate,tlend.daysFROM tlend, tbook, tmemberWHEREtlend.bno = tbook.noANDtlend.mno = tmember.no;
- 오라클 명령
123456789101112SELECT * FROM dba_users; <-- DBA계정만 사용 가능SELECT * FROM all_users; <-- 모든 계정에서 사용 가능SELECT * FROM tab; <-- 테이블 목록 보기DESC DEPARTMENT;CREATE USER KIM IDENTIFIED BY blueskyDEFAULT TABLESPACE usersTEMPORARY TABLESPACE temp;grant connect, resource, create session, create view to KIM;
assets 사전 insert DB
2019. 12. 9. 00:31
Programing/Android
String sql = "";
//DB 존재하면 삭제
try {
db.execSQL("drop table if exists '" + DB_NAME + "'");
} catch (Exception e) {
Log.e("MYCAT", "DROP SQL ERROR " + e);
}
//DB 생성
try {
sql = "CREATE TABLE `" + TABLE_NAME + "` (" +
"`_no` TEXT," +
"`name` TEXT," +
"`jang` TEXT," +
"`jeol` TEXT," +
"`content` TEXT);";
Log.e("MYCAT", sql);
db.execSQL(sql);
} catch (Exception e) {
Log.e("MYCAT", "SQL : " + sql + "CREATE ERROR " + e);
}
//이하 assets 폴더에 넣은 파일 가져와 insert
try {
AssetManager assetManager = context.getAssets();
BufferedReader reader = new BufferedReader(new InputStreamReader(assetManager.open("bibleInsert.dat"), "utf-8"));
String buf = "";
while ((buf = reader.readLine()) != null) {
String[] tokens = buf.split("\\|"); //구분자
if (tokens != null && tokens.length > 4) {
sql = "INSERT INTO `" + TABLE_NAME + "` (`_no`, `name`, `jang`, `jeol`, `content`) VALUES('" +
tokens[0] +
"','" + tokens[1] + "'" +
",'" + tokens[2] +
"','" + tokens[3] +
"','"+tokens[4]+
"');";
Log.e("MYTAG", "INSERT BASE " + sql);
Log.v("MYCAT","토큰 "+tokens[0]);
db.execSQL(sql);
}
}
reader.close();
} catch (Exception e) {
Log.e("MYCAT", "SQL : " + sql + "TABLE INSERT BASE ERROR " + e);
}
assets에 저장한 형식
1|홍길동|23|2002|오징어심리학
.
.
.
'Programing > Android' 카테고리의 다른 글
| Android HTML, 외부주소에서 JSON 파싱해 가져오기 (0) | 2019.12.28 |
|---|---|
| Android 리스트뷰 (0) | 2019.12.28 |
| Adapter, GridView와 ListView 적용법 (0) | 2019.11.25 |
| FCM 앱 알람 (0) | 2019.11.02 |
| 커스텀 토스트, 커스텀 다이얼로그 (0) | 2019.10.23 |