Category: My SQL

MySQL ALTER TABLE

ALTER TABLE

The ALTER TABLE command is used to change the structure of an existing table. It helps to add or delete columns, create or destroy indexes, change the type of existing columns, rename columns or the table itself. It can also be used to change the comment for the table and type of the table.

Syntax:ALTER [IGNORE] TABLE tbl_name [alter_specification [, alter_specification] …]

[partition_options]

alter_specification: table_options | ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ] | ADD [COLUMN] (col_name column_definition,…) | ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,…) [index_option] … | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,…) [index_option] … | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,…) [index_option] … | ADD FULLTEXT [INDEX|KEY] [index_name] (index_col_name,…) [index_option] … | ADD SPATIAL [INDEX|KEY] [index_name] (index_col_name,…) [index_option] … | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,…) reference_definition | ALGORITHM [=] {DEFAULT|INPLACE|COPY} | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} | CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name] | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE} | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name] | DROP [COLUMN] col_name | DROP PRIMARY KEY | DROP {INDEX|KEY} index_name | DROP FOREIGN KEY fk_symbol | DISABLE KEYS | ENABLE KEYS | RENAME [TO|AS] new_tbl_name | ORDER BY col_name [, col_name] … | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name] | DISCARD TABLESPACE | IMPORT TABLESPACE | FORCE | ADD PARTITION (partition_definition) | DROP PARTITION partition_names | TRUNCATE PARTITION {partition_names | ALL} | COALESCE PARTITION number | REORGANIZE PARTITION partition_names INTO (partition_definitions) | EXCHANGE PARTITION partition_name WITH TABLE tbl_name | ANALYZE PARTITION {partition_names | ALL} | CHECK PARTITION {partition_names | ALL} | OPTIMIZE PARTITION {partition_names | ALL} | REBUILD PARTITION {partition_names | ALL} | REPAIR PARTITION {partition_names | ALL} | REMOVE PARTITIONING index_col_name: col_name [(length)] [ASC | DESC] index_type: USING {BTREE | HASH} index_option: KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT ‘string’ table_options: table_option [[,] table_option] … (see CREATE TABLE options)

Arguments:

Name	Description
COLUMN	List of columns.
FIRST	A column can be added at a specific position within a table row, using FIRST or AFTER clause. By default, the column is added at the last. You can also use FIRST and AFTER in CHANGE or MODIFY operations to reorder columns within a table.
INDEX \| KEY	KEY is normally a synonym for INDEX
CONSTRAINT	CONSTRAINT is used to define rules to allow or restrict what values can be stored in columns.
PRIMARY KEY	A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL. If not declare MySQL declares them so implicitly. A table can have only one PRIMARY KEY.
UNIQUE	A UNIQUE index creates a constraint in which all values in the index must be distinct. An error occurs when you try to add a new row with a key value that matches an existing row.
FULLTEXT	FULLTEXT indexes are used for full-text searches. Only the MyISAM storage engine supports FULLTEXT indexes. They can be created only from CHAR, VARCHAR, and TEXT columns. Indexing always happens over the entire column; column prefix indexing is not supported and any prefix length is ignored if specified.
SPATIAL	SPATIAL indexes can be created on spatial data types. Spatial types are supported only for MyISAM tables and indexed columns must be declared as NOT NULL.
FOREIGN KEY	InnoDB and NDB tables support checking of foreign key constraints. The columns of the referenced table must always be explicitly named. Both ON DELETE and ON UPDATE functions on foreign keys.
CHECK	For other storage engines, MySQL Server parses and ignores the FOREIGN KEY and REFERENCES syntax in CREATE TABLE statements other than the InnoDB and NDB engine. The CHECK clause is parsed but ignored by all storage engines.
ALGORITHM=COPY	You needed this when you are applying ALTER TABLE in earlier versions of MySQL (< 5.6) while altering a table online. This method was used to use a temporary table while altering a table.
ALGORITHM = DEFAULT	is the same a specifying no ALGORITHM clause at all.
ALGORITHM=INPLACE	The ALGORITHM=INPLACE continue the operation inside the InnoDB storage engines by using the in-place technique, and fail which are not support this features with an error.
LOCK = DEFAULT	Permit a series of coincident events i.e. reads and writes when supported. Otherwise permit concurrent reads when supported else enforce exclusive access.
LOCK = NONE	When supported, permit concurrent reads and writes else return an error message.
LOCK =SHARED	When supported, allow concurrent reads but restrict writes. Remember that writes will be blocked even if concurrent writes are supported by the storage engine for the given ALGORITHM clause (if any) and ALTER TABLE operation. When concurrent reads are not supported an error message will be returned.
LOCK = EXCLUSIVE	This enforce exclusive access. It happens even if concurrent reads/writes are supported by the storage engine for the given ALGORITHM clause (if any) and ALTER TABLE operation.

Basic Examples

Create a table testtable as shown below:

CREATE TABLE testtable (col1 INT(11), col2 VARCHAR(15));

Copy

To rename the table from testtable to w3r1, use the following statement.

ALTER TABLE testtable RENAME w3r1;

Copy

To change column col1 from INTEGER to TINYINT NOT NULL (leaving the name the same), and to change column b from VARCHAR(15) to CHAR(25) as well as renaming it from col2 to col3, the following statement can be used.

ALTER TABLE w3r1 MODIFY col1 TINYINT NOT NULL, CHANGE col2 col3 VARCHAR(25);

Copy

To add a new TIMESTAMP column named col4, the following statement can be used.

ALTER TABLE w3r1 ADD col4 TIMESTAMP;

Copy

To add an index on column col4 and a UNIQUE index on column col1, the following statement can be used.

ALTER TABLE w3r1 ADD INDEX (col4), ADD UNIQUE (col1);

Copy

To remove column col3 from the table w3r1, the following statement can be used.

ALTER TABLE w3r1 DROP COLUMN col3;

Copy

To add a new AUTO_INCREMENT integer column named col3, the following statement can be used.

ALTER TABLE w3r1 ADD col3 INT UNSIGNED NOT NULL AUTO_INCREMENT,   
ADD PRIMARY KEY (col3);

Copy

Here in the above example, we indexed col3 (as a PRIMARY KEY) because AUTO_INCREMENT columns must be indexed, and we declare col3 as NOT NULL because primary key columns cannot be NULL.

To change the data type of col1 into BIGINT, the following statement can be used.

ALTER TABLE w3r1 MODIFY col1 BIGINT;

Copy

If you want to include the attributes UNSIGNED DEFAULT 1 and COMMENT ‘test column’, show the below statement –

ALTER TABLE w3r1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'test column';

Copy

To change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this:

ALTER TABLE w3r1 CONVERT TO CHARACTER SET latin1;

Copy

MySQL ALTER TABLE insert column

Here is the structure of newbook_mast table.

If you want to add a column ‘id’ of integer type in the table newbook_mast, the following statement can be used.

ALTER TABLE newbook_mast
ADD id  INT;

Copy

Here is the structure of the newbook_mast after add a column id .

MySQL ALTER TABLE insert column FIRST

Here is the structure of newbook_mast table.

If you want to insert a column id of integer type, as first column of the table newbook_mast, the following statement can be used.

ALTER TABLE newbook_mast
ADD id  INT  FIRST ;

Copy

Here is the structure of the newbook_mast after adding a column id at first.

MySQL ALTER TABLE to insert column AFTER a column

If you want to add two specific columns pub_name and pub_add after pub_id and dt_of_pub columns respectively, the following statement can be used.

ALTER TABLE newbook_mast
ADD pub_name  VARCHAR(35)  AFTER pub_id,
ADD pub_add   VARCHAR(50)  AFTER dt_of_pub;

Copy

Here is the structure of the newbook_mast after add two columns in specific position said above.

MySQL ALTER TABLE ADD INDEX.If you want to add an index named ‘cate_id’ on ‘cate_id’ column for the table ‘newbook_mast’, the following statement can be used.

ALTER TABLE newbook_mast
ADD INDEX cate_id(cate_id);

Copy

MySQL ALTER TABLE ADD UNIQUE INDEX

If you want to add a UNIQUE INDEX named ‘cate_id’ on ‘cate_id’ column for the table ‘newbook_mast’, the following statement can be used.

ALTER TABLE newbook_mast
ADD UNIQUE INDEX cate_id(cate_id);

Copy

Here is the unique indexes for the newbook_mast table after adding an unique index named cate_id on cate_id column.

MySQL ALTER TABLE ADD PRIMARY KEY

If you want to l create a PRIMARY KEY on invoice_no column for the table tstpurch, the following statement can be used.

ALTER TABLE tstpurch
ADD PRIMARY KEY invoice_no (invoice_no);

Copy

Here is the details of the index.

MySQL ALTER TABLE ADD FOREIGN KEY

https://www.adsensecustomsearchads.com/afs/ads?psid=5134551505&channel=AutoRsVariant&cx=r-440389826592af9d2&fexp=44759875%2C44759926%2C31080590%2C44795921%2C95322180%2C95320888%2C95321627%2C95322163%2C95323009%2C0%2C21404%2C17301383%2C71847096&client=pub-2153208817642134&r=m&sct=ID%3Df1c5d672aa31266c%3AT%3D1706354011%3ART%3D1706354011%3AS%3DALNI_MZlJMMg_q5l3r_1tPiuFzhttpHLOQ&sc_status=6&hl=en&rpbu=http%3A%2F%2Fgoogle.com&rpqp=q&type=3&rs_tt=c&oe=UTF-8&ie=UTF-8&format=r5&nocache=511706402582171&num=0&output=afd_ads&domain_name=www.w3resource.com&v=3&bsl=10&pac=0&u_his=6&u_tz=-480&dt=1706402582173&u_w=1366&u_h=768&biw=1297&bih=644&psw=1297&psh=644&frm=0&cl=600476684&uio=-&cont=autors-container-0&drt=0&jsid=csa&jsv=600476684&rurl=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Faltering-table%2Faltering-table.php&referer=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-connection.php

If you want to create a FOREIGN KEY with the combination of book_id and cate_id columns of newbook_mast table with a reference from torder table, the following statement can be used.

ALTER TABLE newbook_mast
ADD  FOREIGN KEY(book_id,cate_id)
REFERENCES 
torder(book_id,cateid);

Copy

MySQL ALTER TABLE ADD and DROP column, INDEX, PRIMARY KEY and FOREIGN KEY

If you want to modifies the structure of ‘tstpurch’ table in the following manner –

1. Drop the ‘id’ column, existing primary key, index ‘cate_id’ and foreign key ‘ord_no’.
2. Add an integer type column ‘id’ with default value 0.
3. Add column ‘chano’ which is varchar type and size 10 and don’t accept any NULL value.
4. Add a date type column ‘chadt’.
5. Add a primary key named ‘invoice_no’ on ‘invoice_no’ column.
6. Add an index named ‘cate_id’ on ‘cate_id’ column.
7. Add a foreign key in combination of two columns ‘ord_no’ and ‘book_id’ which is referred by the same primary key of ‘torder’ table.

the following statement can be used.

ALTER TABLE tstpurch
DROP id,
ADD id int NOT NULL DEFAULT 0,
ADD chano VARCHAR(10) NOT NULL,
ADD  chadt date,
DROP  PRIMARY KEY,
ADD  PRIMARY KEY invoice_no (invoice_no),
DROP  INDEX cate_id,
ADD  INDEX cate_id(cate_id),
DROP  FOREIGN KEY ord_no,
ADD  FOREIGN KEY(book_id,cate_id) REFERENCES
torder(book_id,cate_id);

Copy

January 27, 2024

MySQL CREATE INDEX
CREATE INDEX

In MySQL, an index can be created on a table when the table is created with CREATE TABLE command. Otherwise, CREATE INDEX enables to add indexes to existing tables. A multiple-column index can be created using multiple columns.

The indexes are formed by concatenating the values of the given columns.

CREATE INDEX cannot be used to create a PRIMARY KEY.

Syntax:CREATE INDEX [index name] ON [table name]([column name]);

Arguments

Name Description
index name Name of the index.
table name Name of the table.
column name Name of the column.

Example:

Code:
```
CREATE  INDEX autid ON newauthor(aut_id);
```
Copy

Explanation:

The above MySQL statement will create an INDEX on ‘aut_id’ column for ‘newauthor’ table.

MySQL create UNIQUE INDEX

Create UNIQUE INDEX

Using CREATE UNIQUE INDEX, you can create an unique index in MySQL.

Example:

Code:
```
CREATE  UNIQUE INDEX newautid ON newauthor(aut_id);
```
Copy

Explanation:

The above MySQL statement will create an UNIQUE INDEX on ‘aut_id’ column for ‘newauthor’ table.

MySQL create UNIQUE INDEX with index type

Create UNIQUE INDEX with index type

In MySQL, you can specify the type of the INDEX with CREATE INDEX command to set a type for the index.

Example:

Code:
```
CREATE  UNIQUE INDEX newautid ON newauthor(aut_id) USING BTREE;
```
Copy

Explanation:

The above MySQL statement will create an INDEX on ‘aut_id’ column for ‘newauthor’ table by an INDEX TYPE BTREE.
January 27, 2024

Name	Description
index name	Name of the index.
table name	Name of the table.
column name	Name of the column.

MySQL CONSTRAINT

CONSTRAINT

MySQL CONSTRAINT is used to define rules to allow or restrict what values can be stored in columns. The purpose of inducing constraints is to enforce the integrity of a database.

MySQL CONSTRAINTS are used to limit the type of data that can be inserted into a table.

MySQL CONSTRAINTS can be classified into two types – column level and table level.

The column level constraints can apply only to one column where as table level constraints are applied to the entire table.

MySQL CONSTRAINT is declared at the time of creating a table.

MySQL CONSTRAINTs are:

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT

CONSTRAINT	DESCRIPTION
NOT NULL	In MySQL NOT NULL constraint allows to specify that a column can not contain any NULL value. MySQL NOT NULL can be used to CREATE and ALTER a table.
UNIQUE	The UNIQUE constraint in MySQL does not allow to insert a duplicate value in a column. The UNIQUE constraint maintains the uniqueness of a column in a table. More than one UNIQUE column can be used in a table.
PRIMARY KEY	A PRIMARY KEY constraint for a table enforces the table to accept unique data for a specific column and this constraint creates a unique index for accessing the table faster.
FOREIGN KEY	A FOREIGN KEY in MySQL creates a link between two tables by one specific column of both tables. The specified column in one table must be a PRIMARY KEY and referred by the column of another table known as FOREIGN KEY.
CHECK	A CHECK constraint controls the values in the associated column. The CHECK constraint determines whether the value is valid or not from a logical expression.
DEFAULT	In a MySQL table, each column must contain a value ( including a NULL). While inserting data into a table, if no value is supplied to a column, then the column gets the value set as DEFAULT.

Syntax:CREATE TABLE [table name] ([column name] [data type]([size]) [column constraint]…. [table constraint] ([[column name]……])……);

Explanation:

The above MySQL code shows how to create a table with some constraints. Constraints are applied to columns as well as tables.

You can replace table name, column name, data type and size with your own.

Contents:

MySQL CREATE TABLE with NULL CONSTRAINT

Using the default value as NOT NULL, while creating a MySQL table, it can be enforced that a column in a table is not allowed to store NULL values.

If you want to create a table ‘newauthor’ where no columns are allowed to store NULL VALUES the following statement can be used.

Example:

If you want to create a table ‘newauthor’ where no columns are allowed to store NULL VALUES the following statement can be used.

CREATE TABLE IF NOT EXISTS newauthor
(aut_id varchar(8) NOT NULL,	   
aut_name varchar(50) NOT NULL,   
country varchar(25) NOT NULL,	   
home_city varchar(25) NOT NULL );

Copy

Here in the above statement the constraint ‘NOT NULL’ have been used to exclude the NULL VALUE.

The following picture shows that the columns will not accept the NULL values.

MySQL CREATE TABLE to check values with CHECK CONSTRAINT

Adding a CHECK CONSTRAINT on a column of a table, you can limit the range of values allowed to be stored in that column.

Example:

If you want to create a table ‘newbook_mast’ with a PRIMARY KEY on ‘book _id’ column, a unique constraint on ‘isbn_no’ column and a set the no_page in such, that it would hold values more than zero only, the following statement can be used.

CREATE TABLE IF NOT EXISTS 
newbook_mast (book_id varchar(15) NOT NULL UNIQUE,          
book_name varchar(50)  ,           
isbn_no varchar(15)  NOT NULL UNIQUE  ,           
cate_id varchar(8)  ,             
aut_id varchar(8) ,             
pub_id varchar(8) ,            
dt_of_pub date ,             
pub_lang varchar(15) ,           
no_page decimal(5,0) 
CHECK(no_page>0) ,            
book_price decimal(8,2) ,             
PRIMARY KEY (book_id)               
);

Copy

Here in the above MySQL statement will create a table ‘newbook_mast’ with a PRIMARY KEY on ‘book _id’ column, unique constraint on ‘isbn_no’ column and adding CHECK(no_page>0) will set the no_page in such, that it would hold values more than zero only.

MySQL CREATE TABLE with CHECK CONSTRAINT using IN operator

https://googleads.g.doubleclick.net/pagead/ads?gdpr=0&us_privacy=1—&gpp_sid=-1&client=ca-pub-2153208817642134&output=html&h=280&adk=2413866252&adf=2879700472&pi=t.aa~a.4097345806~i.70~rp.4&w=715&fwrn=4&fwrnh=100&lmt=1706402468&num_ads=1&rafmt=1&armr=3&sem=mc&pwprc=5856759792&ad_type=text_image&format=715×280&url=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fcreating-table-advance%2Fconstraint.php&fwr=0&pra=3&rh=179&rw=715&rpe=1&resp_fmts=3&wgl=1&fa=27&uach=WyJXaW5kb3dzIiwiMTAuMC4wIiwieDg2IiwiIiwiMTA2LjAuNDk5OC40MSIsbnVsbCwwLG51bGwsIjY0IixbWyJOb3RfQSBCcmFuZCIsIjguMC4wLjAiXSxbIkNocm9taXVtIiwiMTIwLjAuNjA5OS4yMTciXSxbIk9wZXJhIiwiMTA2LjAuNDk5OC40MSJdXSwxXQ..&dt=1706402458856&bpp=14&bdt=3633&idt=14&shv=r20240122&mjsv=m202401230101&ptt=9&saldr=aa&abxe=1&cookie=ID%3D6de4a56fe4484587%3AT%3D1706354011%3ART%3D1706355507%3AS%3DALNI_MYWYkKy5gAvXyEH7W4ZN6WCTnP0sA&gpic=UID%3D00000d0223079700%3AT%3D1706354011%3ART%3D1706355507%3AS%3DALNI_MaBKEehqphuMfn0yJfqUi_NOyR70w&prev_fmts=468×80%2C304x250%2C300x600%2C300x600%2C0x0&nras=2&correlator=7103095427521&frm=20&pv=1&ga_vid=1729879426.1706400821&ga_sid=1706402458&ga_hid=1623522286&ga_fc=1&u_tz=-480&u_his=6&u_h=768&u_w=1366&u_ah=728&u_aw=1366&u_cd=24&u_sd=1&dmc=8&adx=238&ady=420&biw=1312&bih=644&scr_x=0&scr_y=0&eid=44759876%2C44759927%2C31080619%2C95320888%2C95321627%2C95322163%2C95323009&oid=2&pvsid=2497237953541308&tmod=169962545&uas=3&nvt=1&ref=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-connection.php&fc=384&brdim=0%2C0%2C0%2C0%2C1366%2C0%2C1366%2C728%2C1312%2C644&vis=1&rsz=%7C%7Cs%7C&abl=NS&fu=128&bc=31&bz=1.04&psd=W251bGwsbnVsbCxudWxsLDNd&ifi=9&uci=a!9&fsb=1&dtd=9469

MySQL CHECK CONSTRAINT can be applied to a column of a table, to set a limit for storing values within a range, along with IN operator.

Example:

If you want to create a table ‘newauthor’ with a PRIMARY KEY on a combination of two columns (aut_id,home_city) and checking a limit value for the column country are ‘USA’,’UK’ and ‘India’, the following statement can be used.

CREATE TABLE IF NOT EXISTS
newauthor(aut_id varchar(8) NOT NULL , 
aut_name varchar(50) NOT NULL,
country varchar(25) NOT NULL CHECK (country IN ('USA','UK','India')), 
home_city varchar(25) NOT NULL, 
PRIMARY KEY (aut_id,home_city));

Copy

Here in the above MySQL statement will create a table ‘newauthor’ with a PRIMARY KEY on a combination of two columns (aut_id,home_city) and the value for the column country has been limited by using IN operator.

MySQL CREATE TABLE with CHECK CONSTRAINT and LIKE operator

MySQL CHECK CONSTRAINT can be applied to a column of a table, to set a limit for storing values within a range, along with LIKE operator.

Example:

The MySQL statement stated below will create a table ‘newbook_mast’ with a PRIMARY KEY on ‘book_id’ column and a CHECK constraint to limit value to be stored for the column dt_of_pub along with LIKE operator and another CHECK constraint to column no_page (without using LIKE operator).

CHECK (dt_of_pub LIKE ‘–/–/—-‘) checks whether the format of the date to be stored is the column dt_of_pub is like ‘–/–/—-‘.

Here is the statement.

CREATE TABLE IF NOT EXISTS newbook_mast
( book_id	varchar(15) NOT NULL UNIQUE, 
book_name varchar(50) , 
isbn_no varchar(15) NOT NULL UNIQUE ,
cate_id varchar(8) , 
aut_id varchar(8) , 
pub_id varchar(8) ,
dt_of_pub date CHECK (dt_of_pub LIKE '--/--/----'), 
pub_lang varchar(15) ,
no_page decimal(5,0) CHECK(no_page>0) ,
book_price decimal(8,2) ,
PRIMARY KEY (book_id) );

Copy

MySQL CREATE TABLE with AND and OR operator and CHECK CONSTRAINT

MySQL CHECK CONSTRAINT can be applied to a column of a table, to set a limit for storing values within a range, along with AND and OR operator.

Example

The MySQL statement stated below will create a table ‘newpublisher’ with a PRIMARY KEY on ‘pub_id’ column and CHECK constraint along with AND and OR operator for country and pub_city columns.

CHECK ((country=’India’ AND pub_city=’Mumbai’) OR (country=’India’ AND pub_city=’New Delhi’)) checks whether (i)country is INDIA and pub_city is Mumbai OR (ii) country is INDIA and pub_city is New Delhi.

https://googleads.g.doubleclick.net/pagead/ads?gdpr=0&us_privacy=1—&gpp_sid=-1&client=ca-pub-2153208817642134&output=html&h=280&adk=2413866252&adf=2200033345&pi=t.aa~a.4097345806~i.104~rp.4&w=715&fwrn=4&fwrnh=100&lmt=1706402472&num_ads=1&rafmt=1&armr=3&sem=mc&pwprc=5856759792&ad_type=text_image&format=715×280&url=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fcreating-table-advance%2Fconstraint.php&fwr=0&pra=3&rh=179&rw=715&rpe=1&resp_fmts=3&wgl=1&fa=27&uach=WyJXaW5kb3dzIiwiMTAuMC4wIiwieDg2IiwiIiwiMTA2LjAuNDk5OC40MSIsbnVsbCwwLG51bGwsIjY0IixbWyJOb3RfQSBCcmFuZCIsIjguMC4wLjAiXSxbIkNocm9taXVtIiwiMTIwLjAuNjA5OS4yMTciXSxbIk9wZXJhIiwiMTA2LjAuNDk5OC40MSJdXSwxXQ..&dt=1706402458903&bpp=15&bdt=3680&idt=15&shv=r20240122&mjsv=m202401230101&ptt=9&saldr=aa&abxe=1&cookie=ID%3D6de4a56fe4484587%3AT%3D1706354011%3ART%3D1706355507%3AS%3DALNI_MYWYkKy5gAvXyEH7W4ZN6WCTnP0sA&gpic=UID%3D00000d0223079700%3AT%3D1706354011%3ART%3D1706355507%3AS%3DALNI_MaBKEehqphuMfn0yJfqUi_NOyR70w&prev_fmts=468×80%2C304x250%2C300x600%2C300x600%2C0x0%2C715x280&nras=3&correlator=7103095427521&frm=20&pv=1&ga_vid=1729879426.1706400821&ga_sid=1706402458&ga_hid=1623522286&ga_fc=1&u_tz=-480&u_his=6&u_h=768&u_w=1366&u_ah=728&u_aw=1366&u_cd=24&u_sd=1&dmc=8&adx=238&ady=5559&biw=1297&bih=644&scr_x=0&scr_y=2991&eid=44759876%2C44759927%2C31080619%2C95320888%2C95321627%2C95322163%2C95323009&oid=2&pvsid=2497237953541308&tmod=169962545&uas=1&nvt=1&ref=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-connection.php&fc=384&brdim=0%2C0%2C0%2C0%2C1366%2C0%2C1366%2C728%2C1312%2C644&vis=1&rsz=%7C%7Cs%7C&abl=NS&fu=128&bc=31&bz=1.04&psd=W251bGwsbnVsbCxudWxsLDNd&ifi=10&uci=a!a&btvi=2&fsb=1&dtd=13113

MySQL Manual says “The CHECK clause is parsed but ignored by all storage engines.”

Here is the statement.

CREATE TABLE IF NOT EXISTS newpublisher
(pub_id varchar(8) ,
pub_name varchar(50),
pub_city varchar(25) ,          
country varchar(25) ,
country_office varchar(25) ,
no_of_branch int(3),          
estd date 
CHECK ((country='India' AND pub_city='Mumbai')            
OR (country='India' AND pub_city='New Delhi')) , 
PRIMARY KEY (pub_id) );

Copy

MySQL UNIQUE CONSTRAINT

The UNIQUE constraint creates an index such that, all values in the index column must be unique. An error occurs when any body tries to add a new row with a key value that already exists in that row.

Example:

The MySQL statement stated below will create a table ‘newauthor’ with a column ‘aut_id’ which will store unique values only since UNIQUE (aut_id) is used.

CREATE TABLE IF NOT EXISTS 
newauthor(aut_id varchar(8) NOT NULL ,
aut_name varchar(50)
NOT NULL,
country varchar(25) NOT NULL,
home_city varchar(25)
NOT NULL, 
UNIQUE (aut_id));

Copy

The picture below shows the structure of the table.

Example of MySQL UNIQUE CONSTRAINT check unique value

The MySQL statement stated below will create a table ‘newauthor’ with a column ‘aut_id’ which is meant to store unique values only. Notice that UNIQUE is used within the column definition

CREATE TABLE IF NOT EXISTS 
newauthor(aut_id varchar(8) NOT NULL UNIQUE ,
aut_name varchar(50) NOT NULL,
country varchar(25) 
NOT NULL,
home_city varchar(25) NOT NULL);

Copy

The following picture shows the Structure of the Table

MySQL CREATE TABLE with DEFAULT CONSTRAINT

While creating a table, MySQL allows you assign DEFAULT CONSTRAINTS to columns. DEFAULT is used to set a default value for a column and is applied using DEFAULT default_value; where default_value is the default value set to the column.

Example:

The MySQL statement stated below will create a table ‘newpublisher’ with a PRIMARY KEY on ‘pub_id’ column, a CHECK constraint with logical operators for country and pub-city columns and a default value for pub_id, pub_name, pub_city and country columns.

https://www.adsensecustomsearchads.com/afs/ads?psid=5134551505&channel=AutoRsVariant&cx=r-440389826592af9d2&fexp=21404%2C17301374%2C17301375%2C17301383%2C71847096&client=pub-2153208817642134&r=m&sct=ID%3Df1c5d672aa31266c%3AT%3D1706354011%3ART%3D1706354011%3AS%3DALNI_MZlJMMg_q5l3r_1tPiuFzhttpHLOQ&sc_status=6&hl=en&rpbu=http%3A%2F%2Fgoogle.com&rpqp=q&type=3&rs_tt=c&oe=UTF-8&ie=UTF-8&format=r5&nocache=3101706402459184&num=0&output=afd_ads&domain_name=www.w3resource.com&v=3&bsl=10&pac=0&u_his=6&u_tz=-480&dt=1706402459187&u_w=1366&u_h=768&biw=1297&bih=644&psw=1297&psh=644&frm=0&cl=600476684&uio=-&cont=autors-container-0&drt=0&jsid=csa&jsv=600476684&rurl=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fcreating-table-advance%2Fconstraint.php&referer=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-connection.php

The MySQL statement also sets the default value white space for pub_id, pub_name, pub_city columns and ‘India’ as a default value for a country column.

Here is the statement below.

CREATE TABLE IF NOT EXISTS newpublisher
(pub_id varchar(8) NOT NULL UNIQUE DEFAULT '' ,           
pub_name varchar(50) NOT NULL  DEFAULT '' ,          
pub_city varchar(25) NOT NULL  DEFAULT '' ,          
country varchar(25) NOT NULL DEFAULT 'India',          
country_office varchar(25) , 
no_of_branch int(3),        
estd date
CHECK ((country='India' AND pub_city='Mumbai')
OR (country='India' AND pub_city='New Delhi')) ,
PRIMARY KEY (pub_id));

Copy

MySQL CREATE TABLE with AUTO INCREMENT

MySQL allows you to set AUTO_INCREMENT to a column. Doing so will increase the value of that column by 1 automatically, each time a new record is added.

Example:

The MySQL statement stated below will create a table ‘newauthor’ with a PRIMARY KEY on ‘id’ column and the ‘id’ column is an auto incremented field.

CREATE TABLE IF NOT EXISTS newauthor
(id int NOT NULL AUTO_INCREMENT,
aut_id varchar(8),   
aut_name varchar(50),
country varchar(25),
home_city varchar(25) NOT NULL,
PRIMARY KEY (id));

Copy

MySQL PRIMARY KEY CONSTRAINT

Usually, a table has a column or combination of columns that contain values used to uniquely identify each row in the table.This column or combination of columns is called PRIMARY KEY and can be created by defining a PRIMARY KEY CONSTRAINT while creating a table. A table can have only one PRIMARY KEY. A PRIMARY KEY column cannot contain NULL values.

Example:

The MySQL statement stated below will create a table ‘newauthor’ in which PRIMARY KEY set to the column aut_id.

CREATE TABLE IF NOT EXISTS
newauthor(aut_id varchar(8) NOT NULL ,   
aut_name varchar(50) NOT NULL, 
country varchar(25) NOT NULL,
home_city varchar(25) NOT NULL,         
PRIMARY KEY (aut_id));

Copy

MySQL CREATE TABLE PRIMARY KEY CONSTRAINT on single column

In this topic, we have discussed how to set a PRIMARY KEY CONSTRAINT on a column of a table.

Example:

The MySQL statement stated below will create a table ‘newauthor’ in which PRIMARY KEY set to the aut_id column. Notice that here PRIMARY KEY keyword is used within the column definition.

CREATE TABLE IF NOT EXISTS 
newauthor(aut_id varchar(8) NOT NULL  PRIMARY KEY,	   
aut_name varchar(50) NOT NULL, 
country varchar(25) 
NOT NULL, 
home_city varchar(25) NOT NULL);

Copy

MySQL CREATE TABLE PRIMARY KEY UNIQUE CONSTRAINT

In this topic, we have discussed how to set PRIMARY KEY as well as UNIQUE constraints on columns of a table while creating a table with CREATE TABLE command.

Example:

The MySQL statement stated below will create a table ‘newauthor’ in which PRIMARY KEY is set to the aut_id column and UNIQUE is set to the home_city column.

CREATE TABLE IF NOT EXISTS
newauthor(aut_id varchar(8) NOT NULL  PRIMARY KEY,
aut_name varchar(50) NOT NULL,
country varchar(25) NOT NULL, 
home_city varchar(25) NOT NULL UNIQUE);

Copy

MySQL CREATE TABLE PRIMARY KEY on multiple columns

MySQL allows you to set PRIMARY KEY on multiple columns of a table. Doing this allows you to work on multiple columns as a single entity set as PRIMARY KEY for a table.

Example:

The MySQL statement stated below will create a table ‘newauthor’ in which PRIMARY KEY is set with the combination of aut_id and home_city columns.

CREATE TABLE IF NOT EXISTS 
newauthor(aut_id varchar(8) NOT NULL ,
aut_name varchar(50) NOT NULL,
country varchar(25) NOT NULL,   
home_city varchar(25) NOT NULL, 
PRIMARY KEY (aut_id, home_city));

Copy

MySQL creating table with FOREIGN KEY CONSTRAINT

While creating (or modifying) a MySQL table, you can set a FOREIGN KEY CONSTRAINT to a column of the table. A foreign key is a column or combination of columns which can be used to set a link between the data in two tables. PRIMARY KEY of a table is linked to the FOREIGN KEY of another table to enhance data integrity.

Syntax:FOREIGN KEY [column list] REFERENCES [primary key table] ([column list]);

Arguments:

Name	Description
column list	A list of the columns on which FOREIGN KEY is to be set.
REFERENCES	Keyword.
primary key table	Table name which contains the PRIMARY KEY.
column list	A list of the columns on which PRIMARY KEY is set in the primary key table.

Example:

If you want to do the following tasks:

A new table ‘newbook_mast’ will be created.

The PRIMARY KEY for that table ‘newbook_mast’ is ‘book_id’.

The FOREIGN KEY for the table ‘newbook_mast’ is ‘aut_id’.

The ‘aut_id’ is the PRIMARY KEY for the table ‘newauthor’.

The FOREIGN KEY ‘aut_id’ for the table ‘newbook_mast’ points to the PRIMARY KEY ‘aut_id’ of the table ‘newauthor’.

That means the ‘aut_id’s which are present in the ‘newauthor’ table, only those authors will come to the ‘newbook_mast’ table.

Here is the MySQL statement below for the above tasks.

CREATE TABLE IF NOT EXISTS newbook_mast 
(book_id varchar(15) NOT NULL PRIMARY KEY,
book_name varchar(50)  ,
isbn_no varchar(15)  NOT NULL  ,
cate_id varchar(8)  , 
aut_id varchar(8) , 
pub_id varchar(8) ,          
dt_of_pub date ,
pub_lang varchar(15) ,
no_page decimal(5,0) ,         
book_price decimal(8,2) ,
FOREIGN KEY (aut_id) REFERENCES newauthor(aut_id));

Copy

MySQL CREATE TABLE with FOREIGN KEY CONSTRAINT on multiple columns

MySQL allows assigning FOREIGN KEY CONSTRAINTS on multiple columns of a table. Doing this, more than one columns of a table is set with a FOREIGN KEY CONSTRAINT referenced PRIMARY KEYs belonging to different tables.

Example:

If you want to do the following tasks:

Here is the MySQL statement below for the above tasks.

CREATE TABLE IF NOT EXISTS newpurchase
(invoice_no varchar(12) NOT NULL UNIQUE PRIMARY KEY,
invoice_dt date ,
ord_no varchar(25) ,
ord_date date ,
receive_dt date ,
book_id varchar(8) ,
book_name varchar(50) ,
pub_lang varchar(8) ,
cate_id varchar(8) ,
receive_qty int(5) ,
purch_price decimal(12,2) ,
total_cost decimal(12,2) ,
INDEX (ord_no,book_id),
FOREIGN KEY(ord_no,book_id) REFERENCES  neworder(ord_no,book_id),   
INDEX (cate_id),
FOREIGN KEY(cate_id) REFERENCES  category(cate_id));

Copy

MySQL CREATE TABLE with FOREIGN KEY CONSTRAINT on multiple tables

MySQL allows assigning FOREIGN KEY CONSTRAINTS on multiple tables. Doing this, more than one columns of a table is set with a FOREIGN KEY CONSTRAINT referenced to PRIMARY KEYs belonging to different tables.

Example:

If you want to do the following tasks :

Aa new table ‘newbook_mast’ will be created.
The PRIMARY KEY for that table ‘newbook_mast’ is ‘book_id’.
The one FOREIGN KEY for the table ‘newbook_mast’ is ‘aut_id’.
The another FOREIGN KEY for the table ‘newbook_mast’ is ‘pub_id’.
The ‘aut_id’ is the PRIMARY KEY for the table ‘newauthor’.
The ‘pub_id’ is the PRIMARY KEY for the table ‘newpublisher’.
The FOREIGN KEY ‘aut_id’ for the table ‘newbook_mast’, which points to the PRIMARY KEY ‘aut_id’ of the table ‘newauthor’.
That means the ‘aut_id’ which are present in the in the ‘nuwauthor’ table only those authors will come to the ‘newbook_mast’ table.
The another FOREIGN KEY ‘pub_id’ for the table ‘newbook_mast’ , which points to the PRIMARY KEY ‘pub_id’ of the table ‘newpublisher’.
That means the ‘pub_id’ which are present in the in the ‘newpublisher’ table only those publishers will come to the ‘newbook_mast’ table.

Here is the MySQL statement below for the above tasks.

CREATE TABLE IF NOT EXISTS 
newbook_mast (book_id varchar(15) NOT NULL PRIMARY KEY,          
book_name varchar(50)  , 
isbn_no varchar(15)  NOT NULL  , 
cate_id varchar(8), 
aut_id varchar(8) ,
pub_id varchar(8) , 
dt_of_pub date , 
pub_lang varchar(15) ,           
no_page decimal(5,0) , 
book_price decimal(8,2) ,
INDEX (aut_id), 
FOREIGN KEY(aut_id) REFERENCES newauthor(aut_id), 
INDEX(pub_id),
FOREIGN KEY(pub_id) REFERENCES newpublisher(pub_id) );

Copy

MySQL CREATE TABLE with CASCADE and RESTRICT

MySQL allows creating a table with CASCADE and RESTRICT options.

CASCADE option deletes or updates the row from the parent table (containing PRIMARY KEYs), and automatically delete or update the matching rows in the child table (containing FOREIGN KEYs).

RESTRICT option bars the removal (i.e. using delete) or modification (i..e using an update) of rows from the parent table.

Example:

If you want to do the following tasks:

A new table ‘newpurchase’ will be created.
The PRIMARY KEY for that table ‘newpurchase’ is ‘invoice_no’.
The one FOREIGN KEY for the table ‘newpurchase’ is a combination of ‘ord_no’ and ‘book_id’.
The another FOREIGN KEY for the table ‘newpurchase’ is ‘cate_id’.
The ‘ord_no’ and ‘book_id’ combination is the PRIMARY KEY for the table ‘neworder’.
The ‘cate_id’ is the PRIMARY KEY for the table ‘category’.
The FOREIGN KEY ‘ord_no’ and ‘book_id’ combination for the table ‘newpurchase’, which points to the PRIMARY KEY ‘ord_no’ and ‘book_id’ combination of the table ‘neworder’. That means the distinct (‘ord_no’ and ‘book_id’) combination which are present in the ‘neworder’ table only those unique ‘order number’ and ‘book id’ combination will come in the ‘newpurchase’ table.
The another FOREIGN KEY ‘cate_id’ for the table ‘newpurchase’ , which points to the PRIMARY KEY ‘cate_id’ of the table ‘category’. That means the ‘cate_id’ which are present in the ‘category’ table only those ‘category’ will come in the ‘newpurchase’ table.
The ON UPDATE CASCADE ensures that the records inside the child table ‘newpurchase’ always points to the PRIMARY KEY inside the parent table ‘neworder’.
If any record gets deleted/updated from the ‘neworder’ table MySQL handles the deletion/updating of the records from ‘newpurchase’ table.
ON DELETE RESTRICT prevents a record in a parent table ‘neworder’ being deleted or altered when it is still referenced from a child table ‘newpurchase’.

Here is the MySQL statement below for the above tasks.

CREATE TABLE IF NOT EXISTS newpurchase
(invoice_no varchar(12) NOT NULL UNIQUE PRIMARY KEY,     
invoice_dt date ,  
ord_no varchar(25) ,    
ord_date date ,    
receive_dt date ,      
book_id varchar(8) , 
book_name varchar(50) ,
pub_lang varchar(8) , 
cate_id varchar(8) , 
receive_qty int(5) , 
purch_price decimal(12,2) ,
total_cost decimal(12,2) , 
INDEX (ord_no,book_id),
FOREIGN KEY(ord_no,book_id) REFERENCES         
neworder(ord_no,book_id)
ON UPDATE CASCADE ON DELETE RESTRICT,  
INDEX (cate_id),
FOREIGN KEY(cate_id) REFERENCES category(cate_id))

Copy

MySQL CREATE TABLE with SET NULL

MySQL allows you to create a table with SET NULL option. Doing so will delete or update the row from the parent table, and set the foreign key column or columns in the child table to NULL.

You can use SET NULL for DELETE as well as UPDATE.

If SET NULL is used, you should not set NOT NULL options to the columns of the child table (containing FOREIGN KEYS).

Example:

If you want to do the following tasks:

The another FOREIGN KEY ‘cate_id’ for the table ‘newpurchase’ , which points to the PRIMARY KEY ‘cate_id’ of the table ‘category’. That means the ‘cate_id’ which are present in the ‘category’ table only those ‘category’ will come in the ‘newpurchase’ table.
ON UPDATE SET NULL recurses to update the ‘newpurchase’ table it has already updated during the cascade, it acts like RESTRICT. It prevents infinite loops resulting from cascaded updates.

Here is the MySQL statement below for the above tasks.

CREATE TABLE IF NOT EXISTS newpurchase
(invoice_no varchar(12) NOT NULL UNIQUE PRIMARY KEY,       
invoice_dt date , 
ord_no varchar(25) ,
ord_date date ,
receive_dt date ,      
book_id varchar(8) ,
book_name varchar(50) ,
pub_lang varchar(8) ,     
cate_id varchar(8) , 
receive_qty int(5) , 
purch_price decimal(12,2) ,   
total_cost decimal(12,2) , 
INDEX (ord_no,book_id),
FOREIGN KEY(ord_no,book_id) REFERENCES neworder
(ord_no,book_id)
ON UPDATE CASCADE ON DELETE 
SET NULL,
INDEX (cate_id),
FOREIGN KEY(cate_id) REFERENCES category(cate_id));

Copy

MySQL CREATE TABLE with NO ACTION

NO ACTION option in MySQL is equivalent to RESTRICT.

Example:

If you want to do the following tasks:

Aa new table ‘newpurchase’ will be created.
The PRIMARY KEY for that table ‘newpurchase’ is ‘invoice_no’.
The one FOREIGN KEY for the table ‘newpurchase’ is a combination of ‘ord_no’ and ‘book_id’.
The another FOREIGN KEY for the table ‘newpurchase’ is ‘cate_id’.
The ‘ord_no’ and ‘book_id’ combination is the PRIMARY KEY for the table ‘neworder’.
The ‘cate_id’ is the PRIMARY KEY for the table ‘category’.
The FOREIGN KEY ‘ord_no’ and ‘book_id’ combination for the table ‘newpurchase’, which points to the PRIMARY KEY ‘ord_no’ and ‘book_id’ combination of the table ‘neworder’. That means the distinct (‘ord_no’ and ‘book_id’) combination which are present in the ‘neworder’ table only those unique ‘order number’ and ‘book id’ combination will come in the ‘newpurchase’ table.
The another FOREIGN KEY ‘cate_id’ for the table ‘newpurchase’ , which points to the PRIMARY KEY ‘cate_id’ of the table ‘category’. That means the ‘cate_id’ which are present in the ‘category’ table only those ‘category’ will come in the ‘newpurchase’ table.
The ON DELETE NO ACTION are preventing a record in a parent ‘neworder’ being deleted or altered when it is still referenced from a child table ‘newpurchase’ .

Here is the MySQL statement below for the above tasks.

CREATE TABLE IF NOT EXISTS 
newpurchase (invoice_no varchar(12) NOT NULL UNIQUE PRIMARY KEY,        
invoice_dt date ,
ord_no varchar(25) ,
ord_date date ,
receive_dt date ,       
book_id varchar(8) ,
book_name varchar(50) , 
pub_lang varchar(8) ,
cate_id varchar(8) , 
receive_qty int(5) , 
purch_price decimal(12,2) ,
total_cost decimal(12,2) ,
INDEX (ord_no,book_id),
FOREIGN KEY(ord_no,book_id) REFERENCES         
neworder(ord_no,book_id)
ON UPDATE CASCADE ON DELETE NO ACTION,       
INDEX (cate_id),
FOREIGN KEY(cate_id) REFERENCES category(cate_id));

January 27, 2024

MySQL Create Database
What is a database?

When an amount of data is stored in an organized way, that is called a database.
In computers, a database is managed by a software called Database Management System.

What is a table?

A table is a set of data values. These values are organized using vertical columns and horizontal rows. Columns are identified by their names.

Contents:
Pictorial representation of a database with tables

MySQL create database

In MySQL, CREATE DATABASE statement creates a database with the given name. To use this statement, you must have the CREATE privilege for the database. You will get an error if the database exists and you did not specify IF NOT EXISTS clause.

Limits on Number of Databases: MySQL has no limit on the number of databases. The underlying file system may have a limit on the number of directories.

CREATE SCHEMA is a synonym for CREATE DATABASE.

Syntax:CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] database_name [create_specification] … create_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name

Where
- database_name is the name of the new database.
- Rules for valid database names are given in MySQL language structure “Schema Object Names” section.
- create_specification options specify database characteristics.
- The CHARACTER SET clause specifies the default database character set.
Example:

The following statement creates ‘bookinfo’ database.
```
CREATE DATABASE bookinfo;
```
Copy

The database names are case sensitive under Unix but this restriction does not apply in Windows. This is also true for table names. The best practice is to use same letter case for creating a database as well as a table.

Note: A database which has just been created is not current database. The user must have to instruct to make it the current database. A database needs to be created only once but a user must have to select it each time he intends to work with that database.

MySQL: Setting the Default Database

MySQL use statement is used to change the database from default to the given database.

Syntax:

use [database_name];

MySQL show database

SHOW statement displays a list of currently existing databases on the server.

Syntax:

SHOW [expression];

Example:

The following MySQL statement will show the current database.
```
SHOW databases;
```
Copy

The list of databases shown bellow by the statement may be different to the other user’s machine. SHOW DATABASES does not show databases for those you don’t have SHOW DATABASES privilege.

Sample Output:MySQL> show databases; +——————–+ | Database | +——————–+ | information_schema | | bookinfo | | MySQL | | test | +——————–+ 4 rows in set (0.01 sec)

MySQL select database

MySQL select database statement is used to see which database is currently selected.

Syntax:

SELECT [expression];

Example:

The following MySQL statement will show the current database.
```
SELECT DATABASE();
```
Copy

Sample Output:MySQL> select database(); +————+ | database() | +————+ | bookinfo | +————+ 1 row in set (0.03 sec)

MySQL show tables statement

MySQL ‘show tables’ statement displays a list of the tables in the database in use. If there is no table in the database, it returns empty rows.

Syntax:

SHOW [expression];

Example:

The following statement displays the list of tables in the database ‘bookinfo’.
```
SHOW tables; 
```
Copy

Sample Output:MySQL> show tables; +——————–+ | Tables_in_bookinfo | +——————–+ | author | | book_mast | | category | | despatch | | newpublisher | | order | | publisher | | purchase | | tempdate | | testtable | +——————–+ 10 rows in set (0.03 sec)

MySQL SHOW CREATE DATABASE

Shows the CREATE DATABASE statement that creates the given database. If the SHOW statement includes an IF NOT EXISTS clause, the output to includes such a clause. SHOW CREATE SCHEMA is a synonym for SHOW CREATE DATABASE.

Syntax:SHOW CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

Example:

The following statement shows the create database statement of ‘world’ database.
```
SHOW create database world;
```
Copy

Sample Output:MySQL> show create database world; +———-+——————————————————————+ | Database | Create Database | +———-+——————————————————————+ | world | CREATE DATABASE world /*!40100 DEFAULT CHARACTER SET latin1 */ | +———-+——————————————————————+ 1 row in set (0.00 sec)

Find MySQL database size

Following query show you the database size in MySQL.

Sample Output:MySQL> SELECT table_schema “Database”, SUM(data_length + index_length)/1024/1024 “Size in MB” FROM information_schema.TABLES GROUP BY table_schema; +——————–+————-+ | Database | Size in MB | +——————–+————-+ | bupf | 20.09464169 | | hr | 0.28685379 | | information_schema | 0.00976563 | | mucemppf | 4.50534534 | | MySQL | 2.43705654 | | performance_schema | 0.00000000 | | sakila | 6.57598877 | | sample | 0.73437500 | | test | 0.06250000 | | tutorial | 0.02406311 | | world | 0.43582153 | +——————–+————-+ 11 rows in set (0.17 sec)

Find all the tables in a MySQL database with specific column names in them

The following statement shows all the tables in ‘hr’ database with columns ‘name’ or ‘department_id’. At first, see the tables in ‘hr’ database.

Sample Output:MySQL> USE hr; Database changed MySQL> SHOW TABLES; +—————–+ | Tables_in_hr | +—————–+ | account | | alluser | | departments | | emp_details | | job_history | | jobs | | locations | | log_emp_details | | my_v1 | | my_v2 | | my_v3 | | my_view | | new_view | | regions | | user | +—————–+ 22 rows in set (0.00 sec)

Sample Output:MySQL> SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN (‘department_id’, ‘name’) AND TABLE_SCHEMA=’hr’; +————-+ | TABLE_NAME | +————-+ | departments | | job_history | | my_v2 | | my_v3 | | my_view | | user | +————-+ 7 rows in set (0.04 sec)
January 27, 2024
MySQL Connection
Connecting to MySQL

Before you perform any operations, you need to connect to MySQL server and select a MySQL database.

Connecting to MySQL server from command line

Command:MySQL -h HostName -u UserName -p

Parameters:

Name Description
-h Keyword, followed by HOSTNAME.
HostName MySQL server name
-u Keyword, followed by USERNAME.
UserName MySQL user name
-p Asks you to enter a password.

As soon as you enter this command, it asks you to provide a password for the user you mentioned. Supplying the appropriate password would allow you to connect to MySQL server.

Connecting to MySQL database from command line

Command:use DatabaseName;

Where DatabaseName is the database you want to select.

Disconnecting or closing MySQL server from command line

Command:exit

This command must be executed from the MySQL prompt.

Connecting to MySQL server and database using PHP
```
<?php
$host="localhost";
$username="root";
$password="";
$db_name="bookinfo";
$con=MySQL_connect("$host", "$username", "$password")or die("cannot connect");
MySQL_select_db("$db_name")or die("cannot select DB");
?>
```
Copy

Replace the values of the $host, $username, $password and $db_name according to your own setup. Notice that we have also selected the database with PHP, which is a must before you can fetch data from a MySQL database.

Disconnecting or closing a MySQL connection using PHP
```
<?php
MySQL_close($con);
?>
```
Copy

Where $con=MySQL_connect(“$host”, “$username”, “$password”)or die(“cannot connect”), shown in the previous example.

Note: It will be convenient for you if you keep the script you require to connect to and disconnect from MySQL in a PHP file and then include that file in each PHP file you are using to perform various operations on the database.

Using MySQL persistent connection with PHP

Description:

1. MySQL persistent connection is a connection which first tries to find if any identical (i.e. with the same hostname, username, and password) exists. If so, then commands followed will use that connection. If such a connection does not exist, it would create one.

2. MySQL persistent connection does not need a MySQL_close().

PHP code:
```
<?php
$host="localhost";
$username="root";
$password="";
$db_name="bookinfo";
$con=MySQL_pconnect("$host", "$username", "$password")or die("cannot connect"); MySQL_select_db("$db_name")or die("cannot select DB");
?>
```
January 27, 2024
MySQL Create Database
What is a database?

When an amount of data is stored in an organized way, that is called a database.
In computers, a database is managed by a software called Database Management System.

What is a table?

A table is a set of data values. These values are organized using vertical columns and horizontal rows. Columns are identified by their names.

Contents:
Pictorial representation of a database with tables

MySQL create database

In MySQL, CREATE DATABASE statement creates a database with the given name. To use this statement, you must have the CREATE privilege for the database. You will get an error if the database exists and you did not specify IF NOT EXISTS clause.

Limits on Number of Databases: MySQL has no limit on the number of databases. The underlying file system may have a limit on the number of directories.

CREATE SCHEMA is a synonym for CREATE DATABASE.

Syntax:CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] database_name [create_specification] … create_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name

Where
- database_name is the name of the new database.
- Rules for valid database names are given in MySQL language structure “Schema Object Names” section.
- create_specification options specify database characteristics.
- The CHARACTER SET clause specifies the default database character set.
Example:

The following statement creates ‘bookinfo’ database.
```
CREATE DATABASE bookinfo;
```
Copy

The database names are case sensitive under Unix but this restriction does not apply in Windows. This is also true for table names. The best practice is to use same letter case for creating a database as well as a table.

Note: A database which has just been created is not current database. The user must have to instruct to make it the current database. A database needs to be created only once but a user must have to select it each time he intends to work with that database.

MySQL: Setting the Default Database

MySQL use statement is used to change the database from default to the given database.

Syntax:

use [database_name];

MySQL show database

SHOW statement displays a list of currently existing databases on the server.

Syntax:

SHOW [expression];

Example:

The following MySQL statement will show the current database.
```
SHOW databases;
```
Copy

The list of databases shown bellow by the statement may be different to the other user’s machine. SHOW DATABASES does not show databases for those you don’t have SHOW DATABASES privilege.

Sample Output:MySQL> show databases; +——————–+ | Database | +——————–+ | information_schema | | bookinfo | | MySQL | | test | +——————–+ 4 rows in set (0.01 sec)

MySQL select database

MySQL select database statement is used to see which database is currently selected.

Syntax:

SELECT [expression];

Example:

The following MySQL statement will show the current database.
```
SELECT DATABASE();
```
Copy

Sample Output:MySQL> select database(); +————+ | database() | +————+ | bookinfo | +————+ 1 row in set (0.03 sec)

MySQL show tables statement

MySQL ‘show tables’ statement displays a list of the tables in the database in use. If there is no table in the database, it returns empty rows.

Syntax:

SHOW [expression];

Example:

The following statement displays the list of tables in the database ‘bookinfo’.
```
SHOW tables; 
```
Copy

Sample Output:MySQL> show tables; +——————–+ | Tables_in_bookinfo | +——————–+ | author | | book_mast | | category | | despatch | | newpublisher | | order | | publisher | | purchase | | tempdate | | testtable | +——————–+ 10 rows in set (0.03 sec)

MySQL SHOW CREATE DATABASE

Shows the CREATE DATABASE statement that creates the given database. If the SHOW statement includes an IF NOT EXISTS clause, the output to includes such a clause. SHOW CREATE SCHEMA is a synonym for SHOW CREATE DATABASE.

Syntax:SHOW CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

Example:

The following statement shows the create database statement of ‘world’ database.
```
SHOW create database world;
```
Copy

Sample Output:MySQL> show create database world; +———-+——————————————————————+ | Database | Create Database | +———-+——————————————————————+ | world | CREATE DATABASE world /*!40100 DEFAULT CHARACTER SET latin1 */ | +———-+——————————————————————+ 1 row in set (0.00 sec)

Find MySQL database size

Following query show you the database size in MySQL.

Sample Output:MySQL> SELECT table_schema “Database”, SUM(data_length + index_length)/1024/1024 “Size in MB” FROM information_schema.TABLES GROUP BY table_schema; +——————–+————-+ | Database | Size in MB | +——————–+————-+ | bupf | 20.09464169 | | hr | 0.28685379 | | information_schema | 0.00976563 | | mucemppf | 4.50534534 | | MySQL | 2.43705654 | | performance_schema | 0.00000000 | | sakila | 6.57598877 | | sample | 0.73437500 | | test | 0.06250000 | | tutorial | 0.02406311 | | world | 0.43582153 | +——————–+————-+ 11 rows in set (0.17 sec)

Find all the tables in a MySQL database with specific column names in them

The following statement shows all the tables in ‘hr’ database with columns ‘name’ or ‘department_id’. At first, see the tables in ‘hr’ database.

Sample Output:MySQL> USE hr; Database changed MySQL> SHOW TABLES; +—————–+ | Tables_in_hr | +—————–+ | account | | alluser | | departments | | emp_details | | job_history | | jobs | | locations | | log_emp_details | | my_v1 | | my_v2 | | my_v3 | | my_view | | new_view | | regions | | user | +—————–+ 22 rows in set (0.00 sec)

Sample Output:MySQL> SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN (‘department_id’, ‘name’) AND TABLE_SCHEMA=’hr’; +————-+ | TABLE_NAME | +————-+ | departments | | job_history | | my_v2 | | my_v3 | | my_view | | user | +————-+ 7 rows in set (0.04 sec)
January 27, 2024
MySQL Create Database
What is a database?

When an amount of data is stored in an organized way, that is called a database.
In computers, a database is managed by a software called Database Management System.

What is a table?

A table is a set of data values. These values are organized using vertical columns and horizontal rows. Columns are identified by their names.

Contents:
Pictorial representation of a database with tables

MySQL create database

In MySQL, CREATE DATABASE statement creates a database with the given name. To use this statement, you must have the CREATE privilege for the database. You will get an error if the database exists and you did not specify IF NOT EXISTS clause.

Limits on Number of Databases: MySQL has no limit on the number of databases. The underlying file system may have a limit on the number of directories.

CREATE SCHEMA is a synonym for CREATE DATABASE.

Syntax:CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] database_name [create_specification] … create_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name

Where
- database_name is the name of the new database.
- Rules for valid database names are given in MySQL language structure “Schema Object Names” section.
- create_specification options specify database characteristics.
- The CHARACTER SET clause specifies the default database character set.
Example:

The following statement creates ‘bookinfo’ database.
```
CREATE DATABASE bookinfo;
```
Copy

The database names are case sensitive under Unix but this restriction does not apply in Windows. This is also true for table names. The best practice is to use same letter case for creating a database as well as a table.

Note: A database which has just been created is not current database. The user must have to instruct to make it the current database. A database needs to be created only once but a user must have to select it each time he intends to work with that database.

MySQL: Setting the Default Database

MySQL use statement is used to change the database from default to the given database.

Syntax:

use [database_name];

MySQL show database

SHOW statement displays a list of currently existing databases on the server.

Syntax:

SHOW [expression];

Example:

The following MySQL statement will show the current database.
```
SHOW databases;
```
Copy

The list of databases shown bellow by the statement may be different to the other user’s machine. SHOW DATABASES does not show databases for those you don’t have SHOW DATABASES privilege.

Sample Output:MySQL> show databases; +——————–+ | Database | +——————–+ | information_schema | | bookinfo | | MySQL | | test | +——————–+ 4 rows in set (0.01 sec)

MySQL select database

MySQL select database statement is used to see which database is currently selected.

Syntax:

SELECT [expression];

Example:

The following MySQL statement will show the current database.
```
SELECT DATABASE();
```
Copy

Sample Output:MySQL> select database(); +————+ | database() | +————+ | bookinfo | +————+ 1 row in set (0.03 sec)

MySQL show tables statement

MySQL ‘show tables’ statement displays a list of the tables in the database in use. If there is no table in the database, it returns empty rows.

Syntax:

SHOW [expression];

Example:

The following statement displays the list of tables in the database ‘bookinfo’.
```
SHOW tables; 
```
Copy

Sample Output:MySQL> show tables; +——————–+ | Tables_in_bookinfo | +——————–+ | author | | book_mast | | category | | despatch | | newpublisher | | order | | publisher | | purchase | | tempdate | | testtable | +——————–+ 10 rows in set (0.03 sec)

MySQL SHOW CREATE DATABASE

Shows the CREATE DATABASE statement that creates the given database. If the SHOW statement includes an IF NOT EXISTS clause, the output to includes such a clause. SHOW CREATE SCHEMA is a synonym for SHOW CREATE DATABASE.

Syntax:SHOW CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

Example:

The following statement shows the create database statement of ‘world’ database.
```
SHOW create database world;
```
Copy

Sample Output:MySQL> show create database world; +———-+——————————————————————+ | Database | Create Database | +———-+——————————————————————+ | world | CREATE DATABASE world /*!40100 DEFAULT CHARACTER SET latin1 */ | +———-+——————————————————————+ 1 row in set (0.00 sec)

Find MySQL database size

Following query show you the database size in MySQL.

Sample Output:MySQL> SELECT table_schema “Database”, SUM(data_length + index_length)/1024/1024 “Size in MB” FROM information_schema.TABLES GROUP BY table_schema; +——————–+————-+ | Database | Size in MB | +——————–+————-+ | bupf | 20.09464169 | | hr | 0.28685379 | | information_schema | 0.00976563 | | mucemppf | 4.50534534 | | MySQL | 2.43705654 | | performance_schema | 0.00000000 | | sakila | 6.57598877 | | sample | 0.73437500 | | test | 0.06250000 | | tutorial | 0.02406311 | | world | 0.43582153 | +——————–+————-+ 11 rows in set (0.17 sec)

Find all the tables in a MySQL database with specific column names in them

The following statement shows all the tables in ‘hr’ database with columns ‘name’ or ‘department_id’. At first, see the tables in ‘hr’ database.

Sample Output:MySQL> USE hr; Database changed MySQL> SHOW TABLES; +—————–+ | Tables_in_hr | +—————–+ | account | | alluser | | departments | | emp_details | | job_history | | jobs | | locations | | log_emp_details | | my_v1 | | my_v2 | | my_v3 | | my_view | | new_view | | regions | | user | +—————–+ 22 rows in set (0.00 sec)

Sample Output:MySQL> SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN (‘department_id’, ‘name’) AND TABLE_SCHEMA=’hr’; +————-+ | TABLE_NAME | +————-+ | departments | | job_history | | my_v2 | | my_v3 | | my_view | | user | +————-+ 7 rows in set (0.04 sec)
January 27, 2024
MySQL Connection
Connecting to MySQL

Before you perform any operations, you need to connect to MySQL server and select a MySQL database.

Connecting to MySQL server from command line

Command:MySQL -h HostName -u UserName -p

Parameters:

Name Description
-h Keyword, followed by HOSTNAME.
HostName MySQL server name
-u Keyword, followed by USERNAME.
UserName MySQL user name
-p Asks you to enter a password.

As soon as you enter this command, it asks you to provide a password for the user you mentioned. Supplying the appropriate password would allow you to connect to MySQL server.

Connecting to MySQL database from command line

Command:use DatabaseName;

Where DatabaseName is the database you want to select.

Disconnecting or closing MySQL server from command line

Command:exit

This command must be executed from the MySQL prompt.

Connecting to MySQL server and database using PHP
```
<?php
$host="localhost";
$username="root";
$password="";
$db_name="bookinfo";
$con=MySQL_connect("$host", "$username", "$password")or die("cannot connect");
MySQL_select_db("$db_name")or die("cannot select DB");
?>
```
Copy

Replace the values of the $host, $username, $password and $db_name according to your own setup. Notice that we have also selected the database with PHP, which is a must before you can fetch data from a MySQL database.

Disconnecting or closing a MySQL connection using PHP
```
<?php
MySQL_close($con);
?>
```
Copy

Where $con=MySQL_connect(“$host”, “$username”, “$password”)or die(“cannot connect”), shown in the previous example.

https://www.adsensecustomsearchads.com/afs/ads?psid=5134551505&channel=AutoRsVariant&cx=r-440389826592af9d2&fexp=21404%2C17301374%2C17301375%2C17301383%2C71847096&client=pub-2153208817642134&r=m&sct=ID%3Df1c5d672aa31266c%3AT%3D1706354011%3ART%3D1706354011%3AS%3DALNI_MZlJMMg_q5l3r_1tPiuFzhttpHLOQ&sc_status=6&hl=en&rpbu=http%3A%2F%2Fgoogle.com&rpqp=q&type=3&rs_tt=c&oe=UTF-8&ie=UTF-8&format=r5&nocache=8531706402036696&num=0&output=afd_ads&domain_name=www.w3resource.com&v=3&bsl=10&pac=0&u_his=5&u_tz=-480&dt=1706402036701&u_w=1366&u_h=768&biw=1297&bih=644&psw=1297&psh=644&frm=0&cl=600476684&uio=-&cont=autors-container-0&drt=0&jsid=csa&jsv=600476684&rurl=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-connection.php&referer=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-language-structure.php

Note: It will be convenient for you if you keep the script you require to connect to and disconnect from MySQL in a PHP file and then include that file in each PHP file you are using to perform various operations on the database.

Using MySQL persistent connection with PHP

Description:

1. MySQL persistent connection is a connection which first tries to find if any identical (i.e. with the same hostname, username, and password) exists. If so, then commands followed will use that connection. If such a connection does not exist, it would create one.

2. MySQL persistent connection does not need a MySQL_close().

PHP code:
```
<?php
$host="localhost";
$username="root";
$password="";
$db_name="bookinfo";
$con=MySQL_pconnect("$host", "$username", "$password")or die("cannot connect"); MySQL_select_db("$db_name")or die("cannot select DB");
?>
```
January 27, 2024

Name	Description
-h	Keyword, followed by HOSTNAME.
HostName	MySQL server name
-u	Keyword, followed by USERNAME.
UserName	MySQL user name
-p	Asks you to enter a password.

Name	Description
-h	Keyword, followed by HOSTNAME.
HostName	MySQL server name
-u	Keyword, followed by USERNAME.
UserName	MySQL user name
-p	Asks you to enter a password.

MySQL Data Types

What is data type

1. A data type specifies a particular type of data, such as integer, floating-point, Boolean etc.

2. A data type also specifies the possible values for that type, the operations that can be performed on that type and the way the values of that type are stored.

MySQL data types

MySQL supports a number of SQL standard data types in various categories. MySQL has Numeric Types, the DATETIME, DATE, and TIMESTAMP Types and String Types. Data types are discussed on this page are based on MySQL community server 5.6

MySQL Numeric Types

MySQL supports all standard SQL numeric data types which include INTEGER, SMALLINT, DECIMAL, and NUMERIC. It also supports the approximate numeric data types (FLOAT, REAL, and DOUBLE PRECISION). The keyword INT is a synonym for INTEGER, and the keywords DEC and FIXED are synonyms for DECIMAL. DOUBLE is a synonym for DOUBLE PRECISION (a nonstandard extension). REAL is a synonym for DOUBLE PRECISION (a nonstandard variation) unless the REAL_AS_FLOAT SQL mode is enabled. The BIT data type stores bit-field values and is supported for MyISAM, MEMORY, InnoDB, and NDB tables.

Integer types

SQL standard integer types INTEGER (or INT) and SMALLINT are supported by MySQL. As an extension to the standard, MySQL also supports the integer types TINYINT, MEDIUMINT, and BIGINT. Following table shows the required storage and range (maximum and minimum value for signed and unsigned integer) for each integer type.

Type	Length in Bytes	Minimum Value (Signed)	Maximum Value (Signed)	Maximum Value (Unsigned)
TINYINT	1	-128	127	255
SMALLINT	2	-32768	32767	65535
MEDIUMINT	3	-8388608	8388607 to	16777215
INT	4	-2147483648	2147483647	4294967295
BIGINT	8	-9223372036854775808	92233720368 54775807	184467440737 09551615

Floating-Point Types

The FLOAT and DOUBLE types represent approximate numeric data values. MySQL uses four bytes for single-precision values and eight bytes for double-precision values.

Types	Description
FLOAT	A precision from 0 to 23 results in a four-byte single-precision FLOAT column
DOUBLE	A precision from 24 to 53 results in an eight-byte double-precision DOUBLE column.

MySQL allows a nonstandard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here values can be stored up to M digits in total where D represents the decimal point. For example, a column defined as FLOAT(8,5) will look like -999.99999. MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001.

Following table shows the required storage and range (maximum and minimum value for signed and unsigned integer) for each floating-point type.

Type	Length in Bytes	Minimum Value (Signed)	Maximum Value (Signed)	Minimum Value (Unsigned)	Maximum Value (Unsigned)
FLOAT	4	-3.402823466E+38	-1.175494351E-38	1.175494351E-38	3.402823466E+38
DOUBLE	8	-1.7976931348623 157E+ 308	-2.22507385850720 14E- 308	0, and 2.22507385850720 14E- 308	1.797693134862315 7E+ 308

Fixed-Point Types

Fixed-Point data types are used to preserve exact precision, for example with currency data. In MySQL DECIMAL and NUMERIC types store exact numeric data values. MySQL 5.6 stores DECIMAL values in binary format.

In standard SQL the syntax DECIMAL(5,2) (where 5 is the precision and 2 is the scale. ) be able to store any value with five digits and two decimals. Therefore the value range will be from -999.99 to 999.99. The syntax DECIMAL(M) is equivalent to DECIMAL(M,0). Similarly, the syntax DECIMAL is equivalent to DECIMAL(M,0). MySQL supports both of these variant forms of DECIMAL syntax. The default value of M is 10. If the scale is 0, DECIMAL values contain no decimal point or fractional part.
The maximum number of digits for DECIMAL is 65, but the actual range for a given DECIMAL column can be constrained by the precision or scale for a given column.

Bit Value Types

The BIT data type is used to store bit-field values. A type of BIT(N) enables storage of N-bit values. N can range from 1 to 64.
To specify bit values, b’value’ notation can be used. value is a binary value written using zeros and ones. For example, b’111′ and b’10000000′ represent 7 and 128, respectively

Numeric type attributes

MySQL supports an extension for optionally specifying the display width of integer data types in parentheses following the base keyword for the type

Types	Description
TYPE(N)	Where N is an integer and display width of the type is upto N digits.
ZEROFILL	The default padding of spaces is replaced with zeros. So, for a column INT(3) ZEROFILL, 7 is displayed as 007.

MySQL Date and Time Types

The date and time types represent DATE, TIME, DATETIME, TIMESTAMP, and YEAR. Each type has a range of valid values, as well as a “zero” value.

DATETIME, DATE, and TIMESTAMP Types

Types	Description	Display Format	Range
DATETIME	Use when you need values containing both date and time information.	YYYY-MM-DD HH:MM:SS	‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.
DATE	Use when you need only date information.	YYYY-MM-DD	‘1000-01-01’ to ‘9999-12-31’.
TIMESTAMP	Values are converted from the current time zone to UTC while storing and converted back from UTC to the current time zone when retrieved.	YYYY-MM-DD HH:MM:SS	‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC

Time Type

MySQL fetches and displays TIME values in ‘HH:MM:SS’ format or ‘HHH:MM:SS’ format The range of. TIME values from ‘-838:59:59’ to ‘838:59:59’. The hours part may be rather large because not only the TIME type can be used to represent the time of day, i.e. less than 24 hours, but also the passed time or a time of interval between two events.

The TIME values in MySQL can be recognized in many different formats, some of which can include a trailing fractional seconds part in up to 6 digits microseconds precision. The range for TIME values is ‘-838:59:59.000000’ to ‘838:59:59.000000’.

https://googleads.g.doubleclick.net/pagead/ads?gdpr=0&us_privacy=1—&gpp_sid=-1&client=ca-pub-2153208817642134&output=html&h=280&adk=2413866252&adf=982939432&pi=t.aa~a.4097345806~i.81~rp.4&w=715&fwrn=4&fwrnh=100&lmt=1706401995&num_ads=1&rafmt=1&armr=3&sem=mc&pwprc=5856759792&ad_type=text_image&format=715×280&url=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-data-types.php&fwr=0&pra=3&rh=179&rw=715&rpe=1&resp_fmts=3&wgl=1&fa=27&uach=WyJXaW5kb3dzIiwiMTAuMC4wIiwieDg2IiwiIiwiMTA2LjAuNDk5OC40MSIsbnVsbCwwLG51bGwsIjY0IixbWyJOb3RfQSBCcmFuZCIsIjguMC4wLjAiXSxbIkNocm9taXVtIiwiMTIwLjAuNjA5OS4yMTciXSxbIk9wZXJhIiwiMTA2LjAuNDk5OC40MSJdXSwxXQ..&dt=1706401987483&bpp=7&bdt=4181&idt=7&shv=r20240122&mjsv=m202401250101&ptt=9&saldr=aa&abxe=1&cookie=ID%3D6de4a56fe4484587%3AT%3D1706354011%3ART%3D1706355169%3AS%3DALNI_MYWYkKy5gAvXyEH7W4ZN6WCTnP0sA&gpic=UID%3D00000d0223079700%3AT%3D1706354011%3ART%3D1706355169%3AS%3DALNI_MaBKEehqphuMfn0yJfqUi_NOyR70w&prev_fmts=468×80%2C304x250%2C300x600%2C300x600%2C0x0%2C1297x644&nras=3&correlator=2016379968657&frm=20&pv=1&ga_vid=1729879426.1706400821&ga_sid=1706401986&ga_hid=999776910&ga_fc=1&u_tz=-480&u_his=6&u_h=768&u_w=1366&u_ah=728&u_aw=1366&u_cd=24&u_sd=1&dmc=8&adx=238&ady=3606&biw=1297&bih=644&scr_x=0&scr_y=1059&eid=44759875%2C44759926%2C44759837%2C31079265%2C31080589%2C31080591%2C42531705%2C44809531%2C31080697%2C95322329%2C95320888%2C95321627%2C95322163%2C95323009&oid=2&pvsid=2869847537756348&tmod=1726638472&uas=3&nvt=1&ref=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-language-structure.php&fc=384&brdim=0%2C0%2C0%2C0%2C1366%2C0%2C1366%2C728%2C1312%2C644&vis=1&rsz=%7C%7Cs%7C&abl=NS&fu=128&bc=31&bz=1.04&psd=W251bGwsbnVsbCxudWxsLDNd&ifi=9&uci=a!9&btvi=2&fsb=1&dtd=7722

MySQL explains abbreviated TIME values with colons as the time of the day. Suppose ’09:10′ means ’09:10:00′, not ’00:09:10′. MySQL understands the abbreviated values without colons as that, the two rightmost digits represent seconds. For example, we think of ‘0910’ and 0910 as meaning ’09:10:00′, i.e. 10 minutes after 9 o’clock but the reality is MySQL understand them as ’00:09:10′, i.e. 9 minutes and 10 seconds. So, be careful about using abbreviated time in MySQL.

By default, the values of time that lie outside the TIME are converted to the valid range of time values. For example, ‘-930:00:00’ and ‘930:00:00’ are converted to ‘-838:59:59’ and ‘838:59:59’. Invalid TIME values are converted to ’00:00:00′, because ’00:00:00′ is itself a valid TIME value in MySQL.

Year Type

The YEAR type is a 1-byte type used to represent year values. It can be declared as YEAR(2) or YEAR(4) to specify a display width of two or four characters. If no width is given the default is four characters

https://www.adsensecustomsearchads.com/afs/ads?psid=5134551505&channel=AutoRsVariant&cx=r-440389826592af9d2&fexp=44759875%2C44759926%2C44759837%2C31079265%2C31080589%2C31080591%2C42531705%2C44809531%2C31080697%2C95322329%2C95320888%2C95321627%2C95322163%2C95323009%2C0%2C21404%2C17301371%2C17301372%2C17301383%2C71847096&client=pub-2153208817642134&r=m&sct=ID%3Df1c5d672aa31266c%3AT%3D1706354011%3ART%3D1706354011%3AS%3DALNI_MZlJMMg_q5l3r_1tPiuFzhttpHLOQ&sc_status=6&hl=en&rpbu=http%3A%2F%2Fgoogle.com&rpqp=q&type=3&rs_tt=c&oe=UTF-8&ie=UTF-8&format=r5&nocache=5141706401987684&num=0&output=afd_ads&domain_name=www.w3resource.com&v=3&bsl=10&pac=0&u_his=5&u_tz=-480&dt=1706401987688&u_w=1366&u_h=768&biw=1297&bih=644&psw=1297&psh=644&frm=0&cl=600476684&uio=-&cont=autors-container-0&drt=0&jsid=csa&jsv=600476684&rurl=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-data-types.php&referer=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-language-structure.php

YEAR(4) and YEAR(2) have different display format but have the same range of values.
For 4-digit format, MySQL displays YEAR values in YYYY format, with a range of 1901 to 2155, or 0000.
For 2-digit format, MySQL displays only the last two (least significant) digits; for example, 70 (1970 or 2070) or 69 (2069).

You can specify YEAR values in a variety of formats:

String length	Range
4-digit string	‘1901’ to ‘2155’.
4-digit number	1901 to 2155.
1- or 2-digit string	‘0’ to ’99’. Values in the ranges ‘0’ to ’69’ and ’70’ to ’99’ are converted to YEAR values in the ranges 2000 to 2069 and 1970 to 1999.
1- or 2-digit number	1 to 99. Values in the ranges 1 to 69 and 70 to 99 are converted to YEAR values in the ranges 2001 to 2069 and 1970 to 1999.

String Types

The string types are CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET.

CHAR and VARCHAR Types

The CHAR and VARCHAR types are similar but differ in the way they are stored and retrieved. They also differ in maximum length and in whether trailing spaces are retained.

Types	Description	Display Format	Range in characters
CHAR	Contains non-binary strings. Length is fixed as you declare while creating a table. When stored, they are right-padded with spaces to the specified length.	Trailing spaces are removed.	The length can be any value from 0 to 255.
VARCHAR	Contains non-binary strings. Columns are variable-length strings.	As stored.	A value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions.

BINARY and VARBINARY Types

The BINARY and VARBINARY types are similar to CHAR and VARCHAR, except that they contain binary strings rather than nonbinary strings.

Types	Description	Range in bytes
BINARY	Contains binary strings.	0 to 255
VARBINARY	Contains binary strings.	A value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions.

BLOB and TEXT Types

A BLOB is a binary large object that can hold a variable amount of data. There are four types of BLOB, TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. These differ only in the maximum length of the values they can hold.
The four TEXT types are TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These correspond to the four BLOB types and have the same maximum lengths and storage requirements.

Types	Description	Categories	Range
BLOB	Large binary object that containing a variable amount of data. Values are treated as binary strings.You don’t need to specify length while creating a column.	TINYBLOB	Maximum length of 255 characters.
		MEDIUMBLOB	Maximum length of 16777215 characters.
		LONGBLOB	Maximum length of 4294967295 characters
TEXT	Values are treated as character strings having a character set.	TINYBLOB	Maximum length of 255 characters.
		MEDIUMBLOB	Maximum length of 16777215 characters.
		LONGBLOB	Maximum length of 4294967295 characters

ENUM Types

A string object whose value is chosen from a list of values given at the time of table creation. For example –

CREATE TABLE length (     length ENUM('small', 'medium', 'large') );

Copy

SET Types

A string object having zero or more comma separated values (maximum 64). Values are chosen from a list of values given at the time of table creation.

Difference between MySQL Datetime and Timestamp data Types

https://googleads.g.doubleclick.net/pagead/ads?gdpr=0&us_privacy=1—&gpp_sid=-1&client=ca-pub-2153208817642134&output=html&h=280&adk=2413866252&adf=2583798797&pi=t.aa~a.4097345806~i.137~rp.4&w=715&fwrn=4&fwrnh=100&lmt=1706401996&num_ads=1&rafmt=1&armr=3&sem=mc&pwprc=5856759792&ad_type=text_image&format=715×280&url=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-data-types.php&fwr=0&pra=3&rh=179&rw=715&rpe=1&resp_fmts=3&wgl=1&fa=27&uach=WyJXaW5kb3dzIiwiMTAuMC4wIiwieDg2IiwiIiwiMTA2LjAuNDk5OC40MSIsbnVsbCwwLG51bGwsIjY0IixbWyJOb3RfQSBCcmFuZCIsIjguMC4wLjAiXSxbIkNocm9taXVtIiwiMTIwLjAuNjA5OS4yMTciXSxbIk9wZXJhIiwiMTA2LjAuNDk5OC40MSJdXSwxXQ..&dt=1706401987511&bpp=10&bdt=4210&idt=10&shv=r20240122&mjsv=m202401250101&ptt=9&saldr=aa&abxe=1&cookie=ID%3D6de4a56fe4484587%3AT%3D1706354011%3ART%3D1706355169%3AS%3DALNI_MYWYkKy5gAvXyEH7W4ZN6WCTnP0sA&gpic=UID%3D00000d0223079700%3AT%3D1706354011%3ART%3D1706355169%3AS%3DALNI_MaBKEehqphuMfn0yJfqUi_NOyR70w&prev_fmts=468×80%2C304x250%2C300x600%2C300x600%2C0x0%2C1297x644%2C715x280&nras=4&correlator=2016379968657&frm=20&pv=1&ga_vid=1729879426.1706400821&ga_sid=1706401986&ga_hid=999776910&ga_fc=1&u_tz=-480&u_his=6&u_h=768&u_w=1366&u_ah=728&u_aw=1366&u_cd=24&u_sd=1&dmc=8&adx=238&ady=6737&biw=1297&bih=644&scr_x=0&scr_y=4181&eid=44759875%2C44759926%2C44759837%2C31079265%2C31080589%2C31080591%2C42531705%2C44809531%2C31080697%2C95322329%2C95320888%2C95321627%2C95322163%2C95323009&oid=2&pvsid=2869847537756348&tmod=1726638472&uas=3&nvt=1&ref=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-language-structure.php&fc=384&brdim=0%2C0%2C0%2C0%2C1366%2C0%2C1366%2C728%2C1312%2C644&vis=1&rsz=%7C%7Cs%7C&abl=NS&fu=128&bc=31&bz=1.04&psd=W251bGwsbnVsbCxudWxsLDNd&ifi=10&uci=a!a&btvi=3&fsb=1&dtd=9074

The DATETIME type is used when you need values containing both date and time information. MySQL retrieves and displays DATETIME values in ‘YYYY-MM-DD HH:MM:SS’ format. The supported range is 1000-01-01 00:00:00′ to ‘9999-12-31 23:59:59’.

The TIMESTAMP data type is also used when you need values containing both date and time information. TIMESTAMP has a range of ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC

The major difference between DATETIME and TIMESTAMP is that TIMESTAMP values are converted from the current time zone to UTC while storing, and converted back from UTC to the current time zone when retrieved. The datetime data type value is unchanged.

See the following example :

The following command displays current time zone information :

Let create a table with two fields udatetime (data type -> datetime) utimestamp (data type -> timestamp) and insert one record with now() (returns the current date and time) as a value in both fields.

MySQL> create table tempdate (udatetime datetime, utimestamp timestamp);
Query OK, 0 rows affected (0.32 sec)

Copy

MySQL> insert into tempdate values ((now()), (now())); 
Query OK, 1 row affected (0.05 sec)

Copy

Now see the records;

At this point, the datetime and timestamp data types have same values.

In the above output, both the fields have same values. Let change the timezone and see the result.

MySQL> SET TIME_ZONE ='Europe/Paris';
Query OK, 0 rows affected (0.00 sec)

Copy

Now execute the following command :

Sample Output:MySQL> select * from tempdate; +———————+———————+ | udatetime | utimestamp | +———————+———————+ | 2013-06-29 16:55:18 | 2013-06-29 13:25:18 | +———————+———————+ 1 row in set (0.00 sec)

The above output shows that udatetime (data type is DATETIME) is unchanged whereas utimestamp (data type is TIMESTAMP) is changed as the new timezone is set to ‘Europe/Paris’.

Summary : MySQL Data Types

January 27, 2024

MySQL Language Structure

Language Structure

This page discusses the syntactical and structural rules for writing the following elements of MySQL statements.

Content:

Literal values (string, numbers etc.)

The terms literal refer to a fixed data value. MySQL evaluates seven types of literal values numeric, character string, date and time, hexadecimal, boolean, bit-field, and NULL Values

Numeric Literals

Numeric literal notation is used to specify fixed and floating-point numbers. Floating-point numbers use ‘.’ as a decimal separator. Both types of numbers may be preceded by ‘+’ or ‘-‘ to indicate a positive or negative numbers. You can use the integer notation in expressions, conditions, SQL functions, and SQL statements. The examples of integer is as follows :

Valid integers :

0
1254
-256

Valid floating-point numbers :

132.45
12.00
-21032.6309e+10

String Literals

A string is a sequence of bytes or characters, enclosed within a single quote (‘w3resource’) or double quote (“w3resource”). You can use string literal notation in expressions, conditions, SQL functions, and SQL statements. Here are some examples :

‘MySQL Tutorial’
“SQL Tutorial”

Note : If ANSI_QUOTES SQL mode is enabled, you cannot use double quotation marks to quote literal strings, because it is interpreted as an identifier.

A binary string is a string of bytes that has no character set or collation whereas a nonbinary string is a string of characters that has a character set and collation. For both types of strings, comparisons are based on the numeric values of the string unit.

https://www.adsensecustomsearchads.com/afs/ads?psid=5134551505&channel=AutoRsVariant&cx=r-440389826592af9d2&fexp=44759876%2C44759927%2C31080533%2C31080696%2C95322182%2C95320888%2C95321627%2C95322163%2C95323009%2C21065725%2C0%2C21404%2C17301383%2C71847096&client=pub-2153208817642134&r=m&sct=ID%3Df1c5d672aa31266c%3AT%3D1706354011%3ART%3D1706354011%3AS%3DALNI_MZlJMMg_q5l3r_1tPiuFzhttpHLOQ&sc_status=6&hl=en&rpbu=http%3A%2F%2Fgoogle.com&rpqp=q&type=3&rs_tt=c&oe=UTF-8&ie=UTF-8&format=r5&nocache=581706401235003&num=0&output=afd_ads&domain_name=www.w3resource.com&v=3&bsl=10&pac=0&u_his=4&u_tz=-480&dt=1706401235004&u_w=1366&u_h=768&biw=1297&bih=644&psw=1297&psh=644&frm=0&cl=600476684&uio=-&cont=autors-container-0&drt=0&jsid=csa&jsv=600476684&rurl=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-language-structure.php&referer=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-storage-engines.php

https://googleads.g.doubleclick.net/pagead/ads?gdpr=0&us_privacy=1—&gpp_sid=-1&client=ca-pub-2153208817642134&output=html&h=280&adk=2413866252&adf=1662646095&pi=t.aa~a.4097345806~i.39~rp.4&w=715&fwrn=4&fwrnh=100&lmt=1706401235&num_ads=1&rafmt=1&armr=3&sem=mc&pwprc=5856759792&ad_type=text_image&format=715×280&url=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-language-structure.php&fwr=0&pra=3&rh=179&rw=715&rpe=1&resp_fmts=3&wgl=1&fa=27&uach=WyJXaW5kb3dzIiwiMTAuMC4wIiwieDg2IiwiIiwiMTA2LjAuNDk5OC40MSIsbnVsbCwwLG51bGwsIjY0IixbWyJOb3RfQSBCcmFuZCIsIjguMC4wLjAiXSxbIkNocm9taXVtIiwiMTIwLjAuNjA5OS4yMTciXSxbIk9wZXJhIiwiMTA2LjAuNDk5OC40MSJdXSwxXQ..&dt=1706401235170&bpp=11&bdt=4215&idt=-M&shv=r20240122&mjsv=m202401240101&ptt=9&saldr=aa&abxe=1&cookie=ID%3D6de4a56fe4484587%3AT%3D1706354011%3ART%3D1706354346%3AS%3DALNI_MYWYkKy5gAvXyEH7W4ZN6WCTnP0sA&gpic=UID%3D00000d0223079700%3AT%3D1706354011%3ART%3D1706354346%3AS%3DALNI_MaBKEehqphuMfn0yJfqUi_NOyR70w&prev_fmts=468×80%2C304x250%2C300x600%2C300x600%2C0x0&nras=2&correlator=2173377011315&frm=20&pv=1&ga_vid=1729879426.1706400821&ga_sid=1706401233&ga_hid=595946858&ga_fc=1&u_tz=-480&u_his=4&u_h=768&u_w=1366&u_ah=728&u_aw=1366&u_cd=24&u_sd=1&dmc=8&adx=238&ady=1658&biw=1297&bih=644&scr_x=0&scr_y=0&eid=44759876%2C44759927%2C31080533%2C31080696%2C95322182%2C95320888%2C95321627%2C95322163%2C95323009%2C21065725&oid=2&pvsid=3614641493843009&tmod=469698561&uas=3&nvt=1&ref=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-storage-engines.php&fc=384&brdim=0%2C0%2C0%2C0%2C1366%2C0%2C1366%2C728%2C1312%2C644&vis=1&rsz=%7C%7Cs%7C&abl=NS&fu=128&bc=31&bz=1.04&psd=W251bGwsbnVsbCxudWxsLDNd&ifi=9&uci=a!9&btvi=2&fsb=1&dtd=428

Within a string, certain sequences have special meaning unless the NO_BACKSLASH_ESCAPES SQL mode (Disable the use of the backslash character (“\”) as an escape character within strings) is enabled. Each of these sequences begins with a backslash (“\”), known as the escape character. MySQL recognizes the escape sequences shown in the following table.

Special Character Escape Sequences

Escape Sequence	Character represented by Sequence
\0	An ASCII NUL (0x00) character.
\’	A single quote (“‘”) character.
\”	A double quote (“””) character.
\b	A backspace character.
\n	A newline (linefeed) character.
\r	A carriage return character.
\t	A tab character.
\Z	ASCII 26 (Control+Z). See note following the table.
\\	A backslash (“\”) character.
\%	A “%” character. See note following the table.
\_	A “_” character. See note following the table.

Here are some examples :MySQL> SELECT ‘w3r’, ‘”w3r”‘, “‘w3r’”, ‘””w3r””‘, ‘w3”resource’, ‘\’w3r’, ‘\”w3r’; +—–+——-+——-+———+————-+——+——+ | w3r | “w3r” | ‘w3r’ | “”w3r”” | w3’resource | ‘w3r | “w3r | +—–+——-+——-+———+————-+——+——+ | w3r | “w3r” | ‘w3r’ | “”w3r”” | w3’resource | ‘w3r | “w3r | +—–+——-+——-+———+————-+——+——+ 1 row in set (0.00 sec) MySQL> SELECT ‘The\nQuick\nBrown\nFox’; +———————+ | The Quick Brown Fox | +———————+ | The Quick Brown Fox | +———————+ 1 row in set (0.00 sec)

Date Time Literals

You can specify a date and time values in several formates, such as numbers or as quoted strings, depending on the exact type of the value and other factors. MySQL interprets ‘2017-08-22’, ‘20170822’, and 20170822 as a date.

MySQL recognizes DATE values in these formats :

As a string in either ‘YYYY-MM-DD’ or ‘YY-MM-DD’ format. You can use any punctuation character as the delimiter between date parts. For example, ‘2014-10-30’, ‘2014/10/30’, ‘2014^10^30, and ‘2014@10@30’ are equivalent.
As a string with no delimiters in either ‘YYYYMMDD’ or ‘YYMMDD’ format, provided that the string maintains valid date format. For example, ‘20080623’ and ‘080623’ are interpreted as ‘2008-06-23’, but ‘071432’ is illegal (it has invalid month and day parts) and becomes ‘0000-00-00’.
As a number in either YYYYMMDD or YYMMDD format provided that the number makes sense as a date. For example, 19841105 and 841105 are interpreted as ‘1984-11-05’.

MySQL recognizes DATETIME and TIMESTAMP values in these formats :

As a string in either ‘YYYY-MM-DD HH:MM:SS’ or ‘YY-MM-DD HH:MM:SS’ format. You can use any punctuation character as the delimiter between date parts. For example, ‘2014-12-31 11:30:45’, ‘2014^12^31 11+30+45’, ‘2014/12/31 11*30*45’, and ‘2014@12@31 11^30^45’ are equivalent. The date and time parts can be separated by T rather than space. For example, ‘2014-11-30 11:30:45’ ‘2014-11-30T11:30:45’ are equivalent.
As a string with no delimiters in either ‘YYYYMMDDHHMMSS’ or ‘YYMMDDHHMMSS’ format, provided that the string maintains valid date format. For example, ‘20080623091528” is interpreted as ‘2008-06-23 09:15:28’, but ‘071122129015’ is illegal (it has an invalid minute part) and becomes ‘0000-00-00 00:00:00’.
As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format provided that the number maintains as a valid date. For example, 19860805132800 and 860805132800 are interpreted as ‘1986-08-05 13:28:00’.

MySQL recognizes TIME values in these formats :

As a string in ‘D HH:MM:SS’ format. You can also use one of the following “relaxed” syntaxes: ‘HH:MM:SS’, ‘HH:MM’, ‘D HH:MM’, ‘D HH’, or ‘SS’. Here D represents days and can have a value from 0 to 34.
As a string with no delimiters in ‘HHMMSS’ format, provided that it makes sense at a time. For example, ‘101112’ is understood as ’10:11:12′, but ‘109712’ is illegal (it has an invalid minute part) and becomes ’00:00:00′.
As a number in HHMMSS format provided that it makes sense at a time. For example, 101112 is understood as ’10:11:12′. The following alternative formats are also understood: SS, MMSS, or HHMMSS.

Hexadecimal Literals

MySQL supports hexadecimal values, written using X’val’, x’val’, or 0xval format, where val contains hexadecimal digits (0..9, A..F). Lettercase of the digits does not matter. Here are some examples :MySQL> SELECT X’773372736F75726365′; +———————–+ | X’773372736F75726365′ | +———————–+ | w3rsource | +———————–+ 1 row in set (0.01 sec) MySQL> SELECT 0xC8+0; +——–+ | 0xC8+0 | +——–+ | 200 | +——–+ 1 row in set (0.05 sec) MySQL>SELECT 0x555341; +———-+ | 0x555341 | +———-+ | USA | +———-+ 1 row in set (0.00 sec)

Boolean Literals

The constants TRUE and FALSE evaluate to 1 and 0, respectively. The constant names can be written both upper and lower case letters. See the following examples :MySQL> SELECT TRUE, true; +——+——+ | TRUE | TRUE | +——+——+ | 1 | 1 | +——+——+ 1 row in set (0.00 sec) MySQL> SELECT FALSE, false; +——-+——-+ | FALSE | FALSE | +——-+——-+ | 0 | 0 | +——-+——-+ 1 row in set (0.00 sec)

Bit-Field Literals

Bit-field values can be written using b’value’ or 0bvalue notation. value is a binary value written using zeros and ones.

NULL Values

The NULL value means “no data.” NULL can be written in any lettercase.

Schema Object Names

A schema is a collection of logical structures of data or schema objects. A schema is owned by a database user. In MySQL, some objects including database, table, column, alias, view, stored procedure, partition, tablespace, and other object names are known as identifiers. An identifier may be quoted or unquoted. If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it.
Identifiers are converted to Unicode internally. They may contain these characters :

Following characters are permitted in unquoted identifiers :
- ASCII : [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore)
- Extended : U+0080 .. U+FFFF
Following characters are permitted in quoted identifiers include the full Unicode Basic Multilingual Plane (BMP), except U+0000 :
- ASCII: U+0001 .. U+007F
- Extended: U+0080 .. U+FFFF
ASCII NUL (U+0000) and supplementary characters (U+10000 and higher) are not permitted in quoted or unquoted identifiers.
Identifiers may begin with a digit but unless quoted may not consist solely of digits.
The database, table, and column names cannot end with space characters.

The following table shows the maximum length for each type of identifier

Identifier	Maximum Length (characters)
Database	64
Table	64
Column	64
Index	64
Constraint	64
Stored Procedure or Function	64
Trigger	64
View	64
Event	64
Tablespace	64
Server	64
Log File Group	64
Alias	256 (see exception)
Compound Statement Label	16

Identifier Qualifiers

MySQL allows names that consist of a single identifier or multiple identifiers. The components of a multiple-part name must be separated by period (“.”) characters. The initial parts of a multiple-part name act as qualifiers that affect the context within which the final identifier is interpreted.

In MySQL, you can refer to a table column using any of the following forms.

Column Reference Meaning col_name The column col_name from whichever table used in the statement contains a column of that name. tbl_name.col_name The column col_name from table tbl_name of the default database. db_name.tbl_name.col_name The column col_name from table tbl_name of the database db_name.

The qualifier character is a separate token and needs not be contiguous with the associated identifiers. For example, tbl_name.col_name and tbl_name . col_name are equivalent.

Identifier Case Sensitivity

In MySQL database, table, and trigger names are not case sensitive in Windows, but are case sensitive in most varieties of Unix. In Mac OS X, which is Unix-based but uses a default file system type (HFS+) that is not case sensitive. Column, index, stored routine, and event names are not case sensitive on any platform, nor are column aliases, but logfile groups are case sensitive. This differs from standard SQL. By default, table aliases are case sensitive on Unix, but not so on Windows or Mac OS X.

Mapping of Identifiers to File Names

There is a correspondence between database and table identifiers and names in the file system. MySQL represents each database as a directory in the data directory, and each table by one or more files in the appropriate database directory. For the table format files (.FRM), the data is always stored in this structure and location.

For the data and index files, the exact representation on disk is storage engine specific. These files may be stored in the same location as the FRM files, or the information may be stored in a separate file. InnoDB data is stored in the InnoDB data files. If you are using tablespaces with InnoDB, then the specific tablespace files you create are used instead.

Any character is legal in database or table identifiers except ASCII NUL (0x00). MySQL encodes any characters that are problematic in the corresponding file system objects when it creates database directories or table files:

Basic Latin letters (a..zA..Z), digits (0..9) and underscore (_) are encoded as is. Consequently, their case sensitivity directly depends on file system features.
All other national letters from alphabets that have uppercase/lowercase mapping are encoded as shown in the following table. Values in the Code Range column are UCS-2 values.

Mapping of Identifiers to File Names

MySQL 5.6 supports built-in (native) functions, user-defined functions (UDFs), and stored functions.

Built-In Function Name Parsing:

The parser uses default rules for parsing names of built-in functions. These rules can be changed by enabling the IGNORE_SPACE SQL mode. When the parser encounters a word that is the name of a built-in function, it must determine whether the name signifies a function call or is instead a nonexpression reference to an identifier such as a table or column name

Function Name Resolution : The following rules describe how the server resolves references to function names for function creation and invocation :

Built-in functions and user-defined functions An error occurs if you try to create a UDF with the same name as a built-in function.
Built-in functions and stored functions. It is possible to create a stored function with the same name as a built-in function, but to invoke the stored function it is necessary to qualify it with a schema name.
User-defined functions and stored functions share the same namespace, so you cannot create a UDF and a stored function with the same name.

MySQL: User-Defined Variables

In MySQL, you can store a value in a user-defined variable in one statement and later you can refer in another statement. This enables you to pass the values from one statement to another. User-defined variables are session-specific, therefore these variables are private to a particular user and another user can not see or use these. All variables for a given client session are automatically freed when that client exits.

Syntax : @var_name
var_name consists of alphanumeric characters, “.”, “_”, and “$”. A user variable name can contain other characters if you quote it as a string or identifier (for example, @’my-var’, @”my-var”, or @my-var).
Note : User variable names are not case sensitive in MySQL 5.0 and up.

One way to set a user-defined variable is by issuing a SET statement :SET @var_name = expr [, @var_name = expr] …

Example :MySQL> SET @x = 10; @y = 20; @z: = 30; Query OK, 0 rows affected (0.07 sec) MySQL> SELECT @x, @y, @z; +——+——+——+ | @x | @y | @z | +——+——+——+ | 10 | 20 | 30 | +——+——+——+ 1 row in set (0.00 sec)

For more details read variables in Stored Programs.

MySQL: Expression Syntax

The following rules define expression syntax in MySQL. The grammar shown here is based on that given in the sql/sql_yacc.yy file of MySQL source distributions.

expr :

expr OR expr
expr || expr
expr XOR expr
expr AND expr
expr && expr
NOT expr
! expr
boolean_primary IS [NOT] {TRUE | FALSE | UNKNOWN}
boolean_primary

boolean_primary :

boolean_primary IS [NOT] NULL
boolean_primary <=> predicate
boolean_primary comparison_operator predicate
boolean_primary comparison_operator {ALL | ANY} (subquery)
predicate

comparison_operator :

= | >= | > | <= | < | <> | !=

predicate :

bit_expr [NOT] IN (subquery)
bit_expr [NOT] IN (expr [, expr] …)
bit_expr [NOT] BETWEEN bit_expr AND predicate
bit_expr SOUNDS LIKE bit_expr
bit_expr [NOT] LIKE simple_expr [ESCAPE simple_expr]
bit_expr [NOT] REGEXP bit_expr
bit_expr

gbit_expr :

bit_expr | bit_expr
bit_expr & bit_expr
bit_expr << bit_expr
bit_expr >> bit_expr
bit_expr + bit_expr
bit_expr – bit_expr
bit_expr * bit_expr
bit_expr / bit_expr
bit_expr DIV bit_expr
bit_expr MOD bit_expr
bit_expr % bit_expr
bit_expr ^ bit_expr
bit_expr + interval_expr
bit_expr – interval_expr
simple_expr

simple_expr :

literal
identifier
function_call
simple_expr COLLATE collation_name
param_marker
variable
simple_expr || simple_expr
+ simple_expr
– simple_expr
~ simple_expr
! simple_expr
BINARY simple_expr
(expr [, expr] …)
ROW (expr, expr [, expr] …)
(subquery)
EXISTS (subquery)
{identifier expr}
match_expr
case_expr
interval_expr

MySQL Comment Syntax

MySQL supports three comment styles :

“#” character to the end of the line.
“–” sequence to the end of the line.
/* Text here */ sequence, as in the C programming language

See the following examples :MySQL> SELECT 1+1; # Single line comment MySQL> SELECT 1+1; — Single line comment MySQL> SELECT 1 /* In-line comment*/ + 1; MySQL> SELECT 1+ /* this is a multiple-line comment */ 1;

MySQL Reserved Words

Certain words such as SELECT, DELETE, or BIGINT are reserved and require special treatment for use as identifiers such as table and column names. The following table lists MySQL reserved wordS.

Reserved Words in MySQL 5.6

ACCESSIBLE	ADD	ALL
ALTER	ANALYZE	AND
AS	ASC	ASENSITIVE
BEFORE	BETWEEN	BIGINT
BINARY	BLOB	BOTH
BY	CALL	CASCADE
CASE	CHANGE	CHAR
CHARACTER	CHECK	COLLATE
COLUMN	CONDITION	CONSTRAINT
CONTINUE	CONVERT	CREATE
CROSS	CURRENT_DATE	CURRENT_TIME
CURRENT_TIMESTAMP	CURRENT_USER	CURSOR
DATABASE	DATABASES	DAY_HOUR
DAY_MICROSECOND	DAY_MINUTE	DAY_SECOND
DEC	DECIMAL	DECLARE
DEFAULT	DELAYED	DELETE
DESC	DESCRIBE	DETERMINISTIC
DISTINCT	DISTINCTROW	DIV
DOUBLE	DROP	DUAL
EACH	ELSE	ELSEIF
ENCLOSED	ESCAPED	EXISTS
EXIT	EXPLAIN	FALSE
FETCH	FLOAT	FLOAT4
FLOAT8	FOR	FORCE
FOREIGN	FROM	FULLTEXT
GENERAL	GET	GRANT
GROUP	HAVING	HIGH_PRIORITY
HOUR_MICROSECOND	HOUR_MINUTE	HOUR_SECOND
IF	IGNORE	IGNORE_SERVER_IDS
IN	INDEX	INFILE
INNER	INOUT	INSENSITIVE
INSERT	INT	INT1
INT2	INT3	INT4
INT8	INTEGER	INTERVAL
INTO	IO_AFTER_GTIDS	IO_BEFORE_GTIDS
IS	ITERATE	JOIN
KEY	KEYS	KILL
LEADING	LEAVE	LEFT
LIKE	LIMIT	LINEAR
LINES	LOAD	LOCALTIME
LOCALTIMESTAMP	LOCK	LONG
LONGBLOB	LONGTEXT	LOOP
LOW_PRIORITY	MASTER_BIND	MASTER_HEARTBEAT_PERIOD
MASTER_SSL_VERIFY_SERVER_CERT	MATCH	MAXVALUE
MEDIUMBLOB	MEDIUMINT	MEDIUMTEXT
MIDDLEINT	MINUTE_MICROSECOND	MINUTE_SECOND
MOD	MODIFIES	NATURAL
NOT	NO_WRITE_TO_BINLOG	NULL
NUMERIC	ON	ONE_SHOT
OPTIMIZE	OPTION	OPTIONALLY
OR	ORDER	OUT
OUTER	OUTFILE	PARTITION
PRECISION	PRIMARY	PROCEDURE
PURGE	RANGE	READ
READS	READ_WRITE	REAL
REFERENCES	REGEXP	RELEASE
RENAME	REPEAT	REPLACE
REQUIRE	RESIGNAL	RESTRICT
RETURN	REVOKE	RIGHT
RLIKE	SCHEMA	SCHEMAS
SECOND_MICROSECOND	SELECT	SENSITIVE
SEPARATOR	SET	SHOW
SIGNAL	SLOW	SMALLINT
SPATIAL	SPECIFIC	SQL
SQLEXCEPTION	SQLSTATE	SQLWARNING
SQL_AFTER_GTIDS	SQL_BEFORE_GTIDS	SQL_BIG_RESULT
SQL_CALC_FOUND_ROWS	SQL_SMALL_RESULT	SSL
STARTING	STRAIGHT_JOIN	TABLE
TERMINATED	THEN	TINYBLOB
TINYINT	TINYTEXT	TO
TRAILING	TRIGGER	TRUE
UNDO	UNION	UNIQUE
UNLOCK	UNSIGNED	UPDATE
USAGE	USE	USING
UTC_DATE	UTC_TIME	UTC_TIMESTAMP
VALUES	VARBINARY	VARCHAR
VARCHARACTER	VARYING	WHEN
WHERE	WHILE	WITH
WRITE	XOR	YEAR_MONTH
ZEROFILL

January 27, 2024

Category: My SQL

ALTER TABLE

CREATE INDEX

CONSTRAINT

What is a database?

Connecting to MySQL

What is a database?

What is a database?

Connecting to MySQL

What is data type

Language Structure