MySQL keyword usage:
Discard the input command: \ c
Exit the SQL program: \ q
View the MySQL server status: \ s
Domain Integrity: primary key; foreign key;
Create a database: Create Database database name;
Create Table and column name: Create Table Name (
Column name 1 data type constraints,
Column name 2 data type constraints,
Column name 3 data type constraints,
Example:
-> ID int (10) Not null primary key,
-> Name varchar (20) not null );
Unsigned does not allow negative numbers in space. It can double the space and can only be used for numeric type.
For example
Id int (10) unsigned not null primary key auto_increment
Primary Key and auto-Increment
Id int (10) Not null primary key auto_increment
Add a field to the name
Alert table name add field name type OTHER
Modify Field Type
Alter table table name change column name Type
Change column name
Alter table table name change original column name changed column name
View fields
Desc table name
Clear table: delete from table name;
Delete table: Drop table [if exists] Table Name;
Delete Database: drop database [if exists] database name;
Add a record to the table: insert into Table Name (column name 1, column name 2) values (**,**);
Add multiple records to the table: insert into <Table Name> (column name 1, column name 2)
Values (**,**),
(**,**),
(**,**),
(**,**);
Change Record operation: Update table name set column name = Update value where update condition;
For example, update student set sname = "Tom" where sname = "Alex ";
Delete record operation: delete from table name where Delete Condition
Example: delete from student where sage <18;
Query record operation: Select column name from table name where query condition order by column name ASC/DESC;
Example: Select * from student;
Use the set function: Count (column name) to calculate the number of elements
Sun (column name) sums the values of a column, but must be an integer
AVG (column name) calculates the average value of a column's values.
Max (column name) to find the maximum value of a column
Min (column name) to find the minimum value of a column
Example: 1. query the total number of students
Select count (*) from student;
2. query the number of students of Elective Courses
Select count (distinct studentid) from SC;
3. query the average score of students of course 1
Select AVG (grade) from SC where courseid = 1;
4. query the highest score and lowest score of Students in course 1.
Select max (grade) as 'highest score ', min (grade) as 'lowest score'
From SC where courseid = 1;
5. query the average score of each student
Select studentid, AVG (grade) as 'average score 'from SC
Group by studentid;
6. query if the average score of a student is over 70
Select studentid, AVG (grade) as 'average score 'from SC
Group by studentid having AVG (grade)> 70;
, Connect to database
/**
*
*/
Import java. SQL .*;
/**
* @ Author Administrator
*
*/
Public class KK {
/**
* @ Param ARGs
*/
Public static void main (string [] ARGs ){
// Todo automatically generates method stubs
Try {
Connection conn = drivermanager. getconnection ("JDBC: mysql: // localhost/myschool", "root", "123456 ");
Statement stmt = conn. createstatement ();
Resultset rs1_stmt.exe cutequery ("select * from user ");
While (Rs. Next ()){
Int id = Rs. getint ("ID ");
String name = Rs. getstring ("name ");
Int score = Rs. getint ("score ");
System. Out. println ("student ID:" + ID + ", name:" + name + ", score" + score );
}
} Catch (exception e ){
// Todo: handle exception
System. Out. println ("fault" + E. tostring ());
}
}
}
<% @ Page Language = "Java" Import = "Java. util. *" pageencoding = "UTF-8" Import = "Java. SQL. *" %>
<%
String Path = request. getcontextpath ();
String basepath = request. getscheme () + ": //" + request. getservername () + ":" + request. getserverport () + path + "/";
%>
<! Doctype HTML public "-// W3C // dtd html 4.01 transitional // en">
<HTML>
<Head>
<Base href = "<% = basepath %>">
<Title> my JSP 'login. jsp 'starting page </title>
<Meta http-equiv = "Pragma" content = "no-Cache">
<Meta http-equiv = "cache-control" content = "no-Cache">
<Meta http-equiv = "expires" content = "0">
<Meta http-equiv = "keywords" content = "keyword1, keyword2, keyword3">
<Meta http-equiv = "Description" content = "this is my page">
<! --
<LINK rel = "stylesheet" type = "text/CSS" href = "styles.css">
-->
</Head>
<Body>
<Center>
<Form action = "mainbody. jsp", method = "Post">
<Label> User name: </label> <input type = "text" name = "user"/>
<Label> password: </label> <input type = "password" name = "PWD"/>
<Input type = "Submit" value = "login"/>
</Form>
</Center>
<%
String user1 = "";
String pwd1 = "";
Try {
String users = request. getparameter ("user ");
String PWDs = request. getparameter ("PWD ");
Class. forname ("org. gjt. Mm. MySQL. Driver ");
Connection conn = drivermanager. getconnection ("JDBC: mysql: // localhost/myqq", "root", "123456 ");
Statement stmt = conn. createstatement ();
Resultset rs1_stmt.exe cutequery ("select * from Stu ");
While (Rs. Next ()){
User1 = Rs. getstring ("user ");
Pwd1 = Rs. getstring ("PWD ");
}
If (! Users. Equals (user1) |! PWDs. Equals (pwd1 )){
Out. Print ("enter the correct user name or password ");
}
If (users. Equals (user1) & PWDs. Equals (pwd1 )){
Response. sendredirect ("mainbody. jsp ");
}
} Catch (exception ex ){
System. Out. Print ("system error" + ex. getmessage ());
}
%>
</Body>
</Html>
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
