×
Clear all filters including search bar
Valeri Tandilashvili's Personal Professional Blog
Results: 1024
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
System variables
System variables start with two 
at signs
- 
@
. Shows base directory of MySQL
SELECT @@basedir
Shows whether query uses cache or not
SELECT @@have_query_cache
Shows what size does query cache have
SELECT @@query_cache_size
... Shows complete list of system variables with their values
SHOW VARIABLES
Complete list of system variables using 
phpmyadmin
: phpmyadmin/server_variables.php
Results: 1024