SQL Notes for 20.2

COMMAND LINE ACCESS:

mysql -u username -p
USE database ;
quit

CREATION:

CREATE TABLE tablename ( column1, column2, ... ) ;
          column: columname type attribute1 attribute2 ...
          type examples: int(11) varchar(25)
          attribute examples: primary key ... auto_increment ... not null ... default value
INSERT INTO tablename (column1, column2,...) VALUES('val1', 'val2', ...) ;

VIEWING DATA

SELECT (column1, column2,...) FROM tablename ;
SELECT * FROM tablename ;
SELECT * FROM tablename WHERE conditions
conditions: relational/logical: example: price>100
SELECT (column1, column2,...) FROM tablename1 JOIN tablename2 USING(column)
note: column must be common to both tables

MODIFYING DATA

UPDATE tablename SET column1=value1, column2=value2 ... WHERE conditions

DESTRUCTION

DROP TABLE tablename ;
ALTER TABLE tablename DROP COLUMN column ;
DELETE FROM tablename WHERE condition LIMIT n ;

CONDITIONS

relational operators, boolean operators, isnull
example: price>5000 and ! isnull(product_id)

JAVA ACCESS:

need mysql-connector-java-N-bin.jar in CLASPATH
an example:

     1      import java.sql.DriverManager;
     2      import java.sql.Connection;
     3      import java.sql.Statement;
     4      import java.sql.ResultSet;
     5
     6      public class DB {
     7            static String driverClass = "com.mysql.jdbc.Driver";
     8            static int sqlport = 3306;
     9            static String url = "jdbc:mysql://localhost:3306/arnow";
    10            static String user = "arnow";
    11            static String pswd = "monkeybiz";
    12
    13            public static void main(String[] a) throws Exception {
    14                  String sqlCommand = a[0].trim();
    15                  boolean isQuery = sqlCommand.toUpperCase().startsWith("SELECT");
    16
    17                  //Class.forName(driverClass);
    18                  com.mysql.jdbc.Driver d = new com.mysql.jdbc.Driver();
    19
    20                  Connection con = DriverManager.getConnection(url, user, pswd);
    21
    22                  Statement stmt = con.createStatement();
    23
    24                  if (isQuery) {
    25                        ResultSet results = stmt.executeQuery(sqlCommand);
    26                        while (results.next()) {
    27                              // ResultSetMetaData mdata = results.getMetaData();
    28                              String price_id = results.getString("product_id");
    29                              String name = results.getString("name");
    30                              String price = results.getString("price");
    31                              String maxdisc = results.getString("maxdisc");
    32                              System.out.println(price_id+": "+name+" -- "+price+" ("+maxdisc+")");
    33                        }
    34                  } else {
    35                        int rows = stmt.executeUpdate(sqlCommand);
    36                        System.out.println(rows+" rows affected");
    37                  }
    38            }
    39      }

TOMCAT ACCESS:

need mysql-connector-java-N-bin.jar in the WEB-INF/lib directory of your particular webapp:
cp -p /opt/cis/lib/mysql-connector-java-3.1.8-bin.jar $HOME/tomcat/webapps/MYAPP/WEB-INF/lib
be sure to import the needed package namespace:
<%@ page import="java.sql.*" %>

JSP EXAMPLE:

     1  <%@ page import="java.sql.*" %><%
     2
     3  String driverWasLoaded = "com.mysql.jdbc.Driver";
     4  if (application.getAttribute(driverWasLoaded)==null) {
     5          System.err.println("LOADING jdbc driver ");
     6          try {com.mysql.jdbc.Driver d = new com.mysql.jdbc.Driver();} catch(Exception e){
     7                  System.err.println("OUCH!!\n"+e.toString());
     8          }
     9          application.setAttribute(driverWasLoaded,driverWasLoaded);
    10          System.err.println("LOADED: JDBC driver ");
    11  }
    12  int sqlport = 3306;
    13  String url = "jdbc:mysql://localhost:3306/arnow";
    14  String user = "arnow";
    15  String pswd = "monkeybiz";
    16  String sqlqueryCommand = "select prices.product_id as id,name,price,maxdisc from prices join products using(product_id)";
    17  Connection con = DriverManager.getConnection(url, user, pswd);
    18  Statement stmt = con.createStatement();
    19
    20  ResultSet results = stmt.executeQuery(sqlqueryCommand);
    21  %>
    22  <html>
    23  <head>
    24          <title>JSP MYSQL ACCESS EXAMPLE</title>
    25  </head>
    26  <body>
    27  <br/><br/><br/>
    28  <table>
    29  <tr><th>id</th><th>name</th><th>price</th><th>maxdisc</th></tr>
    30  <%
    31  while (results.next()) {
    32          System.out.print("<tr>");
    33                  System.out.print("<td>");
    34                          System.out.print(results.getString("id"));
    35                  System.out.print("</td><td>");
    36                          System.out.print(results.getString("name"));
    37                  System.out.print("</td><td>");
    38                          System.out.print(results.getString("price"));
    39                  System.out.print("</td><td>");
    40                          System.out.print(results.getString("maxdisc"));
    41                  System.out.print("</td>");
    42          System.out.println("</tr>");
    43          out.print("<tr>");
    44                  out.print("<td>");
    45                          out.print(results.getString("id"));
    46                  out.print("</td><td>");
    47                          out.print(results.getString("name"));
    48                  out.print("</td><td>");
    49                          out.print(results.getString("price"));
    50                  out.print("</td><td>");
    51                          out.print(results.getString("maxdisc"));
    52                  out.print("</td>");
    53          out.println("</tr>");
    54  }
    55  %>
    56  </table>
    57  </body>
    58  </html>