【Java】 Java Project 挑战系列第10篇：Java Database

• Basic Structured Query Language (SQL)

• Java Database Connectivity (JDBC)

• SQL (Structured Query Language) performs operations on the records stored in the database, such as updating records, inserting records, deleting records, creating and modifying database tables, views, etc.

• SQL is not a database but a query language. To use it, you must install a database management system in your systems, for example, Oracle, MySQL, MongoDB, PostgreSQL, SQL Server, DB2, etc.

• Syntax of Create Use and Drop Database statement in MySQL.

CREATE DATABASE database_name;

USE database_name;

DROP DATABASE database_name;

Note: Be careful when using DROP DATABASE statement, as it will permanently delete the entire database and all its associated tables, data and other objects.

• When this query is executed successfully, then it will show "Database created successfully". You can verify whether your database is created in SQL by:

SHOW DATABASES;

This will display the name of the current database, which should match the name of the database you created.

• Syntax of Use and Rename statement in MySQL

RENAME TABLE old_table_name TO new_table_name;

ALTER DATABASE old_database_name RENAME TO new_database_name;

Note: Be careful when renaming tables or databases, as it may affect any queries or code that reference the original names.

Table is a collection of data, organized in terms of rows and columns.

To create a table in MySQL database without using a primary key, you can use the following syntax:

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  column3 datatype,
  ...
);

CREATE TABLE students (
  name varchar(50),
  age int,
  gender varchar(10)
);

To create a table in MySQL database without using a primary key, you can use the following syntax:

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  column3 datatype,
  ...
  PRIMARY KEY (one_or_more_columns)
);

CREATE TABLE students (
  id int,
  name varchar(50),
  age int,
  PRIMARY KEY (id)
);

This will create a table named "students" with three columns: "id" of type INT, "name" of type VARCHAR with a maximum length of 50 characters, and "age" of type INT. The PRIMARY KEY clause is used to specify that "id" is the primary key for the table.

Note that the primary key should be unique and not null, and should be chosen carefully based on the needs of your application. It can also be auto-incremented using the AUTO_INCREMENT keyword, which can simplify the process of generating unique primary keys for each new row in the table.

• MySQL Data Types (other types like ENUM, SET and BLOB are less popular).

1.Numeric Data Types:

• INT
• TINYINT
• SMALLINT
• MEDIUMINT
• BIGINT
• FLOAT
• DOUBLE
• DECIMAL

2.Date and Time Data Types:

• DATE
• TIME
• DATETIME
• TIMESTAMP
• YEAR

3.String Data Types:

• CHAR
• VARCHAR
• TINYTEXT
• TEXT
• MEDIUMTEXT
• LONGTEXT

4.Binary Data Types:

• BINARY
• VARBINARY
• TINYBLOB
• BLOB
• MEDIUMBLOB
• LONGBLOB

5.Other Data Types:

• ENUM
• SET

ENUM and SET are used for defining a list of allowed values for a column. ENUM can store a single value from a predefined list of values, while SET can store one or more values from a predefined list of values.

BLOB stands for Binary Large OBject, and is used for storing large amounts of binary data such as images, audio, and video files.

• The DELETE statement is used to delete rows from a table. If you want to remove a specific row from a table you should use WHERE condition.

DELETE FROM my_table WHERE id = 5;

• A truncate SQL statement is used to remove all rows (complete data) from a table. It is similar to the DELETE statement with no WHERE clause. 

Unlike the DELETE statement, it does not require a WHERE clause, and it is generally faster and more efficient for large tables. However, note that TRUNCATE cannot be undone and any data in the table will be lost permanently. For example:

TRUNCATE TABLE my_table;

It is important to note that there are some differences between DELETE and TRUNCATE.

DELETE is a DML (Data Manipulation Language) statement and can be rolled back using a transaction, while TRUNCATE is a DDL (Data Definition Language) statement and cannot be rolled back. Additionally, DELETE can have triggers associated with it that perform additional actions when a row is deleted, while TRUNCATE does not trigger any such actions.

• Inserting data directly into a table.

Let’s take an example of a table that has five records within it.

Assuming we have a table named "users" with the following columns: "id", "name", "email", "age", and "gender". We can insert five records into the table using the following INSERT statement:

INSERT INTO users (id, name, email, age, gender)
VALUES
(1, 'John Doe', 'johndoe@example.com', 25, 'Male'),
(2, 'Jane Doe', 'janedoe@example.com', 30, 'Female'),
(3, 'Bob Smith', 'bobsmith@example.com', 35, 'Male'),
(4, 'Sara Johnson', 'sarajohnson@example.com', 28, 'Female'),
(5, 'Tom Brown', 'tombrown@example.com', 40, 'Male');

This statement would insert five records into the "users" table, each with a unique "id" value, a "name", an "email", an "age", and a "gender".

Note that the column names are listed after the table name in the INSERT INTO statement, and each set of values is enclosed in parentheses and separated by commas. The order of the values in each set must match the order of the columns listed in the INSERT INTO statement.

After executing this statement, the "users" table would have five new records with the data we inserted.

• Inserting data through SELECT Statement

Suppose we have two tables named "employees" and "salaries", with the following structures:

employees
---------
id   name   department
----------------------
1    John   Sales
2    Jane   Marketing
3    Bob    Sales

salaries
--------
id   salary
------------
1    50000
2    60000
3    45000

We can insert the data from the "salaries" table into a new table named "employee_salaries" for all employees who work in the Sales department, using the following INSERT INTO SELECT statement:

INSERT INTO employee_salaries (employee_id, salary)
SELECT e.id, s.salary
FROM employees e
JOIN salaries s ON e.id = s.id
WHERE e.department = 'Sales';

This statement would insert the salaries of all employees who work in the Sales department into the "employee_salaries" table, with each record containing an "employee_id" and a "salary". The SELECT statement selects the appropriate data from the "employees" and "salaries" tables based on the condition in the WHERE clause, and the INSERT INTO statement inserts the selected data into the "employee_salaries" table.

Note that the column names listed after the table name in the INSERT INTO statement must match the column names selected by the SELECT statement. Also, the data types of the selected columns must be compatible with the data types of the columns in the target table.

• SELECT Statement in SQL.

SELECT column1, column2, ...
FROM table_name;

Here are some of the commonly used clauses and options that can be used with the SELECT statement:

• WHERE clause: Used to filter the results of the query based on one or more conditions.
• ORDER BY clause: Used to sort the results of the query in ascending or descending order based on one or more columns.
• GROUP BY clause: Used to group the results of the query based on one or more columns and perform aggregate calculations such as SUM, AVG, COUNT, etc.
• JOIN clause: Used to combine data from two or more tables based on a common column.
• LIMIT clause: Used to limit the number of rows returned by the query.

Here's an example of a SELECT statement that uses some of these clauses:

SELECT name, age, department, COUNT(*) AS num_employees
FROM employees
WHERE department = 'Sales'
GROUP BY department
ORDER BY age DESC
LIMIT 10;

This statement selects the name, age, and department columns from the "employees" table, filters the results to only include employees in the Sales department, groups the results by department, calculates the total number of employees in each department using the COUNT function and aliases it as "num_employees", sorts the results in descending order based on age, and limits the output to the first 10 rows.

• SQL SELECT Statement with HAVING clause. The HAVING clause in a SQL SELECT statement is used to filter the results of a query based on aggregate calculations such as SUM, AVG, COUNT, etc. The syntax for a SELECT statement with a HAVING clause is as follows:

SELECT column1, column2, ...
FROM table_name
GROUP BY column1, column2, ...
HAVING condition;

Let's say you have a table called "sales" with the following columns: "region", "product", "sales_date", and "amount". Here's an example query that uses a HAVING clause to find the total sales amount for each region where the total sales amount is greater than $100,000:

SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region
HAVING SUM(amount) > 100000;

 Assuming the "sales" table has the following data:

| region   | product | sales_date | amount |
|----------|---------|------------|--------|
| West     | A       | 2022-01-01 | 50000  |
| West     | B       | 2022-02-01 | 60000  |
| East     | A       | 2022-01-01 | 80000  |
| East     | B       | 2022-02-01 | 90000  |

The query would return the following result:

| region   | total_sales |
|----------|-------------|
| West     | 110000      |
| East     | 170000      |

 

If you want to access all rows from all fields of the table, use the following SQL SELECT syntax with * asterisk sign.

SELECT *
FROM table_name;

This statement selects all columns from the table named "table_name" and returns all rows in the table.

SQL Key

• A column is called primary key that uniquely identifies each row in the table. When multiple columns are used as a primary key, it is known as composite primary key.

• Alternate key is a secondary key it can be simple to understand by an example. Let’s take an example of a student it can contain NAME, ROLL NO., ID, and CLASS. Here ROLL NO. is the primary key and the rest of the columns like NAME, ID, and CLASS are alternate keys.

SQL View

• SQL provides the concept of VIEW, which hides the complexity of the data and restricts unnecessary access to the database. It permits the users to access only a particular column rather than the whole data of the table.

• The View in the SQL is considered as the virtual table, which depends on the result-set of the predefined SQL statement.

• Like the SQL tables, Views also store data in rows and columns, but the rows do not have any physical existence in the database.

• Create View from Single Table. The Syntax is:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

In this syntax:

• view_name is the name you want to give to the view.
• column1, column2, etc. are the columns you want to select from the table.
• table_name is the name of the table you want to query.
• condition is the optional condition you want to use to filter the results.

Here's an example of how to create a view from a single table:

CREATE VIEW top_customers AS
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING total_spent > 10000;

This statement creates a view called "top_customers" based on the result of the query that selects the customer ID and the total amount spent by each customer from the "orders" table, groups the results by customer ID, calculates the total amount spent using the SUM function, and filters the results to only include customers who have spent more than $10,000 using the HAVING clause.

Now, you can use this view as if it were a table in subsequent queries, like this:

SELECT *
FROM top_customers;

This statement selects all columns from the "top_customers" view, which will return the customer IDs and total amount spent for each customer that meets the criteria specified in the view definition.

• Create View from Multiple Tables. The Syntax is:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table1
JOIN table2 ON condition
WHERE condition;

In this syntax:

• view_name is the name you want to give to the view.
• column1, column2, etc. are the columns you want to select from the tables.
• table1 and table2 are the names of the tables you want to query.
• condition is the join condition that links the two tables together.
• condition is the optional condition you want to use to filter the results.

Here's an example of how to create a view from multiple tables:

CREATE VIEW customer_orders AS
SELECT customers.customer_name, orders.order_date, order_details.product_name, order_details.quantity, order_details.unit_price
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
JOIN order_details ON orders.order_id = order_details.order_id;

This statement creates a view called "customer_orders" based on the result of the query that selects the customer name, order date, product name, quantity, and unit price from the "customers", "orders", and "order_details" tables, joins these tables together using the "customer_id" and "order_id" fields, and returns all matching records.

Now, you can use this view as if it were a table in subsequent queries, like this:

SELECT *
FROM customer_orders
WHERE customer_name = 'Acme Corporation';

This statement selects all columns from the "customer_orders" view, which will return the customer name, order date, product name, quantity, and unit price for all orders placed by the customer with the name "Acme Corporation".

• Drop a View. Example:

DROP VIEW view_name;