×
Clear all filters including search bar
Valeri Tandilashvili's MySQL Notes
Results: 175
Adding AUTO_INCREMENT to any varchar column
Adding 
AUTO_INCREMENT
to any 
varchar
column first requires the column to be converted into 
int
CREATE TABLE note (
    title varchar(50),
    description varchar(50),
    PRIMARY KEY(title)
);
INSERT INTO `note` (`title`, `description`) VALUES ('first', 'value'), ('second', 'value');
Let's add 
AUTO_INCREMENT
to 
title
column. We are not able to add 
AUTO_INCREMENT
without changing it's type to 
int
ALTER TABLE note MODIFY title int NOT NULL AUTO_INCREMENT;
Note: In such cases If the table contains rows with string values, (for 
AUTO INCREMENT
field) the string values will be replaced with numbers starting with 1
Add AUTO_INCREMENT to an existing column
Let's first create 
note
table without any column to have 
AUTO_INCREMENT
CREATE TABLE note (
    id int,
    title varchar(50),
    description varchar(50),
    PRIMARY KEY(id, title)
);
After running the query above, we can add 
AUTO_INCREMENT
feature to the existing column 
id
ALTER TABLE note MODIFY id int NOT NULL AUTO_INCREMENT;
If the column is not 
primary key
initially, we can modify the query, not to cause any error when adding 
AUTO_INCREMENT
to it
CREATE TABLE note (
    id int,
    title varchar(50),
    description varchar(50)
);
Adding 
AUTO_INCREMENT
feature to the column, that is not selected as a 
primary key
ALTER TABLE note MODIFY id int NOT NULL PRIMARY KEY AUTO_INCREMENT
Unable to add AUTO INCREMENT to more than one column in the same table
The query below will generate an error because we can not have several columns with 
AUTO_INCREMENT
feature
CREATE TABLE note (
    id int AUTO_INCREMENT,
    note_id int AUTO_INCREMENT,
    title varchar(50),
    description varchar(50),
    PRIMARY KEY(id, note_id)
);
The query generates the following error:
#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key
Multiple columns can be primary key of the table at the same time
We can have several primary keys in the same table
CREATE TABLE note (
    id int AUTO_INCREMENT,
    note_id int,
    title varchar(50),
    description varchar(50),
    PRIMARY KEY(id, note_id, title)
);
Unable to add AUTO INCREMENT without selecting the field as a PRIMARY KEY
It's not possible to add 
AUTO_INCREMENT
feature to a column that is not selected as a primary key. If we want to add the feature to a column, we must make it primary key
CREATE TABLE note (
    id int AUTO_INCREMENT,
    title varchar(50),
    description varchar(50)
);
After running the above query, the following error will be generated:
#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key
The correct syntax of the query after adding primary key to the column 
id
:
CREATE TABLE note (
    id int AUTO_INCREMENT,
    title varchar(50),
    description varchar(50),
    PRIMARY KEY (id)
);
IF EXISTS / IF NOT EXISTS
Deletes the table 
notes
if exists. It will not occur an error if it does not exist, because we are using 
IF EXISTS
keyword
DROP TABLE IF EXISTS notes
The same goes when creating a new table. It will not cause an error if a table with the same name already exists. If the table does not exist, it will create one with the specified fields:
CREATE TABLE IF NOT EXISTS students  (
  `id` int(11) NOT NULL,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL
) ENGINE=InnoDB;
Create table based on another table with renamed fields
Creates new table 
st
and copies only several fields from 
students
table
CREATE TABLE st AS
SELECT 
    id, 
    first_name AS name,
    last_name AS surname
FROM students
Selects highest grade using user defined variable 
USE university; -- we are looking at the database called: university

SET @grade = (SELECT MAX(points) FROM students);
SELECT @grade AS 'The highest grade'
User defined variables
Describes variable 
grt
and assigns it 
Hello
SET @grt = 'Hello';
SELECT @grt AS greeting
SHOW VARIABLES
Lists all the system variables with their values
SHOW VARIABLES
Results: 175