The CREATE TABLE statement is used to create a new table in a database; 1) specify the table name, 2) define columns with names, data types (e.g., INT, VARCHAR, DATE), and optional constraints (e.g., PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, AUTO_INCREMENT); 3) ensure syntax aligns with the specific database system being used, as features like AUTO_INCREMENT or DEFAULT expressions may vary across systems.
The CREATE TABLE statement in SQL is used to define a new table in a database. The basic syntax is as follows:
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
column3 datatype constraints,
...
);Key Components:
table_name: The name of the table you want to create.column1, column2, ...: Names of the columns in the table.datatype: Specifies the type of data the column can hold (e.g.,INT,VARCHAR(50),DATE,BOOLEAN).constraints(optional): Rules applied to the columns, such asPRIMARY KEY,NOT NULL,UNIQUE,DEFAULT, orCHECK.
Example:
CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
hire_date DATE DEFAULT (CURRENT_DATE),
salary DECIMAL(10,2)
);Common Data Types (may vary slightly by database system):
-
INT– Integer -
VARCHAR(n)– Variable-length string up to n characters -
CHAR(n)– Fixed-length string -
TEXT– Long text -
DATE– Date (YYYY-MM-DD) -
DATETIMEorTIMESTAMP– Date and time -
BOOLEAN– True or false -
DECIMAL(p,s)– Exact numeric value with precision and scale
Common Constraints:
-
PRIMARY KEY– Uniquely identifies each row -
NOT NULL– Column cannot have NULL values -
UNIQUE– All values in the column are different -
DEFAULT value– Sets a default value for the column -
AUTO_INCREMENT(MySQL) orIDENTITY(SQL Server) – Auto-generates a number for each row
Note: Syntax details like
AUTO_INCREMENT,IDENTITY, orDEFAULTexpressions may vary between database systems (e.g., PostgreSQL, MySQL, SQL Server, SQLite).
So, while the general structure remains consistent, always check your specific database documentation for supported data types and constraint syntax.
Basically, just define your columns with proper types and constraints, and you're good to go.
The above is the detailed content of What is the syntax for the CREATE TABLE statement in SQL?. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undress AI Tool
Undress images for free
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
The Purpose of SQL: Interacting with MySQL Databases
Apr 18, 2025 am 12:12 AM
SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.
How to use AWS Glue crawler with Amazon Athena
Apr 09, 2025 pm 03:09 PM
As a data professional, you need to process large amounts of data from various sources. This can pose challenges to data management and analysis. Fortunately, two AWS services can help: AWS Glue and Amazon Athena.
MySQL and SQL: Essential Skills for Developers
Apr 10, 2025 am 09:30 AM
MySQL and SQL are essential skills for developers. 1.MySQL is an open source relational database management system, and SQL is the standard language used to manage and operate databases. 2.MySQL supports multiple storage engines through efficient data storage and retrieval functions, and SQL completes complex data operations through simple statements. 3. Examples of usage include basic queries and advanced queries, such as filtering and sorting by condition. 4. Common errors include syntax errors and performance issues, which can be optimized by checking SQL statements and using EXPLAIN commands. 5. Performance optimization techniques include using indexes, avoiding full table scanning, optimizing JOIN operations and improving code readability.
MySQL: A Practical Application of SQL
May 08, 2025 am 12:12 AM
MySQL is popular because of its excellent performance and ease of use and maintenance. 1. Create database and tables: Use the CREATEDATABASE and CREATETABLE commands. 2. Insert and query data: operate data through INSERTINTO and SELECT statements. 3. Optimize query: Use indexes and EXPLAIN statements to improve performance.
SQL vs. MySQL: Clarifying the Relationship Between the Two
Apr 24, 2025 am 12:02 AM
SQL is a standard language for managing relational databases, while MySQL is a database management system that uses SQL. SQL defines ways to interact with a database, including CRUD operations, while MySQL implements the SQL standard and provides additional features such as stored procedures and triggers.
SQL: The Language, MySQL: The Database Management System
Apr 21, 2025 am 12:05 AM
The relationship between SQL and MySQL is: SQL is a language used to manage and operate databases, while MySQL is a database management system that supports SQL. 1.SQL allows CRUD operations and advanced queries of data. 2.MySQL provides indexing, transactions and locking mechanisms to improve performance and security. 3. Optimizing MySQL performance requires attention to query optimization, database design and monitoring and maintenance.
SQL and phpMyAdmin: A Beginner's Guide
Apr 16, 2025 am 12:02 AM
Beginners can learn SQL and phpMyAdmin from scratch. 1) Create database and tables: Create a new database in phpMyAdmin and create tables using SQL commands. 2) Execute basic query: Use SELECT statement to query data from the table. 3) Optimization and best practices: Create indexes, avoid SELECT*, use transactions, and regularly back up databases.
SQL and MySQL: Understanding the Relationship
Apr 16, 2025 am 12:14 AM
The relationship between SQL and MySQL is the relationship between standard languages ??and specific implementations. 1.SQL is a standard language used to manage and operate relational databases, allowing data addition, deletion, modification and query. 2.MySQL is a specific database management system that uses SQL as its operating language and provides efficient data storage and management.
