在创建表时,在某些情况下,我们需要自动生成/增加列的值(例如ID)。各种数据库以不同的方式支持此功能。
在MySQL数据库中,您可以使用以下语法声明列自动递增。
CREATE TABLE table_name( ID INT PRIMARY KEY AUTO_INCREMENT, column_name1 data_type1, column_name2 data_type2, column_name3 data_type3, column_name4 data_type4, ............ ........... );
MySQL查询创建具有自动递增列的表。
CREATE TABLE Sales( ID INT PRIMARY KEY AUTO_INCREMENT, ProductName VARCHAR (20) NOT NULL, CustomerName VARCHAR (20) NOT NULL, DispatchDate date, DeliveryTime timestamp, Price INT, Location varchar(20) );
以下JDBC程序建立与MYSQL数据库的连接,并创建一个具有自动递增列的查询。
import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class AutoIncrementedColumns_Oracle { public static void main(String args[]) throws SQLException { //注册驱动程序 DriverManager.registerDriver(new com.mysql.jdbc.Driver()); //获得连接 String mysqlUrl = "jdbc:mysql://localhost/mydatabase"; Connection con = DriverManager.getConnection(mysqlUrl, "root", "password"); System.out.println("Connection established......"); //创建语句 Statement stmt = con.createStatement(); //使用自动递增的值创建销售表 String createQuery = "CREATE TABLE Sales(" + "ID INT PRIMARY KEY AUTO_INCREMENT, " + "ProductName VARCHAR (20) NOT NULL, " + "CustomerName VARCHAR (20) NOT NULL, " + "DispatchDate date, " + "DeliveryTime timestamp, " + "Price INT, " + "Location varchar(20))"; //执行查询 stmt.execute(createQuery); System.out.println("Table created......"); } }
输出结果
Connection established...... Table created......
MYSQL中的DESCRIBE命令提供了一个表的描述,您可以使用它来验证具有自动递增列的表的创建,如下所示-
mysql> describe sales; +--------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | ProductName | varchar(20) | NO | | NULL | | | CustomerName | varchar(20) | NO | | NULL | | | DispatchDate | date | YES | | NULL | | | DeliveryTime | time | YES | | NULL | | | Price | int(11) | YES | | NULL | | | Location | varchar(20) | YES | | NULL | | +--------------+-------------+------+-----+---------+----------------+ 7 rows in set (0.00 sec)