SQL mysql mysql ( mush, potato) % mysql -u mush -p mydb Enter password:****** mysql>show tables; usertable mysql> ( ) SQL (Query) : select < > from < > where < >; : create, drop, insert, delete,... ; ( ) mysql> select user_name from usertable; Suzuki Sato Tanaka mysql>. ex01.sql
1 ex01.sql ex01.sql ; user_id from (select user_id ;) user_id * select select (3+4)*7, SIN(PI()/2) ; (1) select < > from < > ; :, * user_id user_name user_pass 1001 Suzuki foo 1002 Sato baa 1003 Tanaka grr
(2) select < > from < > where < >; user_id > 1001 user_name = Suzuki (user_id = 1001 or user_id = 1002) AND,OR () order by < > : order by < > desc : 2 ex02.sql ( )
(1) ( ) ex03.sql( ) drop table if exists roomtable; create table roomtable ( room_id int unique, room_type char(2), room_capacity int(2), room_name varchar(20), last_used date, primary_key(room_id) );, (2 ) (2 ) ( 20 ) (2) describe < > Field Type Null Key Default Extra room_id int(11) PRI 0 room_type char(2) YES NULL room_capacity int(2) YES NULL room_name varchar(20) YES NULL last_used date YES NULL (primary key)
insert/delete insert into < > values < > mysql> insert into roomtable values -> (101, SG, 1, Room #101, 04-01-31 ); OK... mysql> select * from roomtable; id, delete from < > where < > (!) 3 ex3.sql (101, 1, SG, Room #101, 04-01-31 ) (102, SGL, 1, Room #102, 04-01-29 ) (103, TW, 2, Room #103, 1001) (104, TW, 2, Very Very Long Room Name, 04-02-01 ) (105, TW, 2, Room #103, 04-01-40 ) (999999999999, SU, 9, Room #105, 04-02-03 )
1. insert ex04.sql delete from roomtable ; insert into roomtable values (101, SG, 1, Room #101, 04-01-31 ), (102, TW, 2, Room #102, 04-02-02 ),... ; 2. tab( u0009 ) mysql> load data local inflie ex04.txt into table roomtable; (tab tr ) 4 ex04.jsp roomtable ex04.txt update update roomtable set last_used= 04-02-02 where room_id = 101 ;
select < > from < > [where < >] COUNT( ) : SUM( ) : MAX( ) : MIN( ) : group by select last_used, COUNT(room_id) from roomtable group by last_used; select select * from roomtable where room_capcity = (select MAX(room_capacity) from roomtable) ; ( ) create table < > select < > from... ; insert into < > select < > from... ; : room_id create table tmp select room_id, (CURDATE()-last_used) as unused from roomtable ;
( ) : where having 1: 4 select room_id, CURDATE()-last_used as unused from roomtable having unused = 4; : like, regexp 2: 10X select * from roomtable where room_id like 10_ ; 3: (A) select * from roomtable where room_name like %(A) ; _ : 1, %: (0 ) 5 ex05.sql select / / room_type total_capacity SG??? TW??? SU??? : group by 1
(1) JOIN t1 t2 Field0 Field1 Field0 Field2 v00 v01 + v00 v02 = Field0 Field1 Field2 v00 v01 v02 select f0, f1, f2 from t1, t2 where t1.f0=t2.f0; Join (2) Join : t1 inner join t2 ( : t1,t2) : t1 left join t2 on < > : t1 right join t2 on < > f0 f1 f0 f2 101 a 101 x 102 b 103 c 102 y 103 z (a, x) (a, y) (a, z) (b, x) (b, y) (b, z) (c, x) (c, y) (c, z) right left
6 ex06.sql join tmp1 tmp3, tmp2 tmp3? : t1 natural join t2 : t1 outer join t2 : t1 natural outer join t2 DB 1 : ( ) 2 : * 3 : 2 A B B A * : A B id type capacity name 105 SG 1 105 name 201 TW 2 201(B) 205 SG 1 205 type SG capacity 1
id type name grade type capacity 104 TW #104 A SG 1 105 SG #105 NULL TW 2 201 TW #201 B inner join JDBC Java DB java.sql.* ( ) Connection : DB Stetement: DB ResultSet : DB id type name grade previous() 104 TW #104 A 105 SG #105 NULL next() 201 TW #201 B
Ex01.java( ) try { Class.forName(<jdbc >) Connection con = DriverManager.getConnection(...); Statement st = con.createstatement(); ResultSet rs = st.executequery( select... ); // white (rs.next()) { // rs.getint(), rs.getstring() } // rs.close(); st.close(); con.close(); } catch (Exception e) {... } 7 Ex01.java / roomtable ( getdate )
(1) ResultSet DB Ex02.java( ) Class.forName(<jdbc >) Connection con = DriverManager.getConnection(...); Statement st = con.createstatement (ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = st.executequery( select... ); rs.first(); rs.updatestring( user_pass, newpassword ); rs.updaterow(); (2) executequery executeupdate / (insert into/delete from ) / (create/drop table ) Statement st = con.createstatement(); ResultSet rs = st.executeupdate ( drop table categorytable if exists ); Ex03.java rs.executeupdate( inser into categorytable values...); preparedstatement preparedstatement ps = con.preparedstatement( insert into categorytable values(?,?) ); ps.setstring(1, SG ); ps.setint(2,1); ps.executeupdate();
8 Ex03.java ( ) select ResultSetMetadata: getcolumncount() : getcolumnname(int) : getcolumnclassname(int) : (java ) getcolumntype(int) : (java.sql.types ) getcolumndisplaysize(int) : ( ) 1 getcolumncount()
9 Ex04.java Ex04.java Undef (java.sql.types ) JSP (1) sql setdatasource : DB query: update: sql-ex01.jsp <sql:setdatasource var= ds driver= com.mysql.jdbc.driver url= jdbc:mysql://localhost/mydb user= mush password= potato /> <sql:query var= users datasource= ${ds} > select * from usertable </sql:query>
JSP (2) rs int rs.rowcount : String[] rs.columnnames : SortedMap[] rs.rows : Object[][] rs.rowsbyindex : 2 <c:foreach> sql-ex01.jsp <table><c:foreach var= row items= ${rs.rowbyindex} > <tr><c:foreach var= col items= ${row} > <td><c:out value= ${col} /></td> </c:foreach></tr> </c:foreach></table> JSP (3) <sql:update> <sql:transaction isolation= serializable > <sql:transaction isolation= serializable datasource= ${ds} > <sql:update>drop table if exists categorytable</sql:update> <sql:update>create table... </sql:update> <sql:update>insert into... </sql:update> </sql:transaction> DB
10 sql-ex01.jsp, sql-ex02.jsp <sql:transaction>? sql-ex03.jsp SQL errorpage.jsp.