TIME NOW
World current time now,
CALENDAR
Calendar monthly, yearly
login CONVERT LENGTH
login CONVERT TEMPERATURE
login DICTIONARIES, LISTS
login SCIENCE EDUCATION RELIGION
login WORK CALCULATOR
login CALCULATE LIFE

MYSQL dabatase. How to, tutorial, examples

Previous articlePage bottomNext article  ALL TOPICS

Mysql. create alter drop table index database example

 
CREATE
CREATE DATABASE specification:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...


Create specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name

CREATE DATABASE Example:
CREATE DATABASE IF NOT EXISTS test DEFAULT CHARACTER SET
utf8 DEFAULT COLLATE utf8_lithuanian_ci;

CREATE TABLE  specification:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

(create_definition,...)

[table_options]

[partition_options]


Another example:
CREATE TABLE IF NOT EXISTS table1 (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
created TIMESTAMP DEFAULT NOW())
PARTITION BY HASH (YEAR(created));
 
CREATE TABLE new_table SELECT * FROM orig_table;
 
CREATE TABLE new_table LIKE orig_table;
PRIMARY KEY

Without Primary key:
CREATE TABLE `testas`.`t_testas` (
`id` INT( 20 ) NOT NULL,
`vardas` TEXT NOT NULL ,
`gdata` DATE NOT NULL ,
`pinigai` INT NOT NULL
) ENGINE = MYISAM ;

It allows dublicated records:

1 jonas 1000lt
1 petras 1000lt
2 petras 5000lt
2 jonas 1000lt

Primary key: autoincrement - A_I, Index: primary

Main table:
CREATE TABLE `testas`.`t_testas` (
`id` INT( 20 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`vardas` TEXT NOT NULL ,
`gdata` DATE NOT NULL ,
`atlyginimas` INT NOT NULL
) ENGINE = MYISAM ;

Related table. Second table id_parent will be related to first table id:
CREATE TABLE `testas`.`t_testas2` (
`id_parent` INT( 20 ) NOT NULL ,
`id` INT( 20 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`job` TEXT NOT NULL ,
`d_date` DATE NOT NULL ,
`money` INT NOT NULL
) ENGINE = MYISAM ;
 
ALTER
ALTER DATABASE example:
1. ALTER {DATABASE | SCHEMA} [db_name]
alter_specification ...

2. ALTER {DATABASE | SCHEMA} db_name
UPGRADE DATA DIRECTORY NAME

alter_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name

Example:
ALTER SCHEMA test DEFAULT CHARACTER SET ascii DEFAULT COLLATE
ascii_general_ci;
 
ALTER TABLE specification:
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options] 

ALTER TABLE examples:
ALTER TABLE t1 CHANGE a b INTEGER;
ALTER TABLE t2 MODIFY c BIGINT NOT NULL;
ALTER TABLE t3 DROP COLUMN d;

Add index to existing table:
ALTER TABLE  `main`.`t_songs` DROP INDEX  `authorname` ,
ADD UNIQUE  `authorname` (  `name` ( 200 ) ,  `author` ( 100 ) )
 
DROP
DROP DATABASE DROP DATABASE
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

DROP example:
DROP SCHEMA IF EXISTS Test;

DROP TABLE specification:
DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]

DROP TABLE example:
DROP TABLE IF EXISTS
t1, t2, t3;
 
Previous articlePage topNext article  ALL TOPICS


Guest:
CREATE DATABASE mydb;

CREATE TABLE users (

id TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
surname VARCHAR(30) NOT NULL,
email VARCHAR(30) NOT NULL,
salary FLOAT(10) UNSIGNED NOT NULL,
PRIMARY KEY (id)

);

CREATE TABLE deals (

id TIMYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
user_id TIMYINT(3) NOT NULL,
goods VARCHAR(200) NOT NULL, kaina FLOAT(10) NOT NULL,
PRIMARY KEY (id)

);

INSERT INTO users VALUES ('1', 'Egle', 'Karalyte', ' a @ a .lt ', '1000');
INSERT INTO users VALUES ('2', 'Jonas', 'Karalius', ' Ta @ a .lt ', '2000');
INSERT INTO users VALUES ('3', 'Petras', 'Ponas', ' a @ a .lt ', 4000);
INSERT INTO users VALUES ('4', 'Simas', 'Ponas', ' a @ a .lt ', 3000);
INSERT INTO users VALUES ('5', 'Petras', 'Petraitis', '', 4000);

INSERT INTO deals VALUES ('', '1', 'bread', 10 );
INSERT INTO deals VALUES ('', '1', 'milk', 11);
INSERT INTO deals VALUES ('', '2', 'metal', 12);
INSERT INTO deals VALUES ('', '2', 'wool', 10);
INSERT INTO deals VALUES ('', '2', 'wool', 10);
INSERT INTO deals VALUES ('', '3', 'wool', 10);


2016-04-04 03:57:35
Guest:
SELECT `name` FROM `users` WHERE `id` = 2;

SELECT * FROM `users` WHERE `id` > 2;

SELECT DISTINCT `goods` FROM `deals`;

SELECT * FROM `users` WHERE `email` = '';

ASC, DESC
SELECT * FROM `users` ORDER BY `name` DESC;

SELECT * FROM `users` ORDER BY `salary` LIMIT 1;

SELECT * FROM `users` ORDER BY RAND() LIMIT 1;

SELECT CONCAT(`name`, " " ,`surname`) AS `user` FROM `users`;

SELECT * FROM `users` WHERE `salary` >= 2000 OR `salary` <= 3000;
SELECT * FROM `users`;

SELECT * FROM `users` WHERE `salary` <= 2000 OR `salary` >= 3000;

SELECT * FROM `users` WHERE `salary` <= 2000 AND `salary` >= 3000;

SELECT * FROM `users` WHERE `salary` >= 2000 AND `salary` <= 3000;

SELECT * FROM `users` WHERE `name` NOT LIKE 'petras';

SELECT * FROM `users` WHERE BINARY `name` NOT LIKE 'petras';

SELECT * FROM `users`
WHERE `name` LIKE 'petras' OR `name` LIKE 'simas'

SELECT * FROM `users`
WHERE `name` NOT LIKE 'petras' OR `name` NOT LIKE 'simas';
SELECT * FROM `users`;

SELECT * FROM `users`
WHERE `name` LIKE 'petras' AND `name` LIKE 'simas'

SELECT * FROM `users`
WHERE `name` NOT LIKE 'petras' AND `name` NOT LIKE 'simas'

SELECT `name` FROM `users` WHERE `name` LIKE "S%";

SELECT Count(*) as `count` FROM `users`;

SELECT Count( * ) AS `count`
FROM `users` WHERE `salary` > 1999

SELECT Avg( `salary` ) AS `average_salary`
FROM `users`


2016-04-11 04:00:07
Guest:
SELECT `user_id`, sum(`kaina`) as `Common`
FROM `deals`
GROUP BY `user_id`


SELECT `user_id`, sum(`kaina`) as `Common`
FROM `deals`
WHERE `user_id` <=2
GROUP BY `user_id`

SELECT `user_id`, sum(`kaina`) as `Common`
FROM `deals`
GROUP BY `user_id`
HAVING `Common`  >=30

SELECT `goods`, count(*) as `Count` 
FROM `deals` 
GROUP BY `goods`

SELECT `goods`, count(*) as `Count` 
FROM `deals`
WHERE 
`goods` NOT LIKE "bread"  
AND 
`goods` NOT LIKE "milk"
GROUP BY `goods`

SELECT `user_id`,`goods`, count(*) as `Count`
FROM `deals`
GROUP BY `user_id`, `goods`

3 simple queries SELECT; 
every of them has minimum 3 WHERE criterias for different fields

Example: 4 criterias:
SELECT * FROM `results` 
WHERE `ID_quiz` >= 1 AND `ID_quiz` <=3 AND `ID_user` = 1 AND `Mark` < 5
----
SELECT * FROM `technical_equipment` 
WHERE `price` >= 1000 AND `company` = 'hp' AND `classroom` = '10'
----

2 SELECT with one GROUP BY and operations (AVG, COUNT, MIN, MAX, AVG, SUM) as well as 1 WHERE. 
2 SELECT with  2 GROUP BY and operations (AVG, COUNT, MIN, MAX, AVG, SUM). 

Example:
SELECT a, b, COUNT(a) FROM tbl GROUP BY a, b

1 SELECT with 2 GROUP BY, 1 HAVING and operations (AVG, COUNT, MIN, MAX, AVG, SUM),



2016-04-18 04:11:45
Guest:
SELECT users.name, deals.goods
FROM users, deals
WHERE users.id = deals.user_id;

SELECT DISTINCT users.name, deals.goods
FROM users, deals
WHERE users.id = deals.user_id
ORDER BY deals.goods;

###
###
###

DELETE QUERY

DELETE FROM table WHERE condition
 
PavyzdÅžiui:
DELETE FROM users WHERE name = 'Jonas';

DELETE FROM users ;

DELETE FROM users WHERE name LIKE '%';

DELETE FROM users WHERE name LIKE '{$_GET['name']}%';


###
###
###

UPDATE QUERY

UPDATE table SET field1 = value1, field2 = value2 i WHERE condition

UPDATE `table` SET `field1` = `field1` + value, `field2` = `field2` + value2 i WHERE condition

###
###
###

SELECT * FROM `users` ORDER BY `salary` desc;

ALTER TABLE `users` ADD `order` INT NOT NULL AFTER `salary`;

SET @x = 0;
UPDATE `users` SET `order` = (@x:[email protected]+1) ORDER BY `salary` desc;

SELECT * FROM `users` ORDER BY `users`.`order` DESC;

###
###
###

SELECT users.id as userid, users.name, deals.goods
FROM users, deals
WHERE users.id = deals.user_id;

UPDATE `users`, `deals`
SET `users`.`salary` = `users`.`salary` +1
WHERE
`users`.`id` = `deals`.`user_id`;

UPDATE `users`, `deals`
SET `users`.`salary` = `users`.`salary` * 10
WHERE
`users`.`id` = `deals`.`user_id`
AND
`deals`.`goods` = 'milk';

2 queries at home


2016-04-25 04:01:31
User:
###
###
###

LCASE(), UCASE()

UPDATE `users`
SET `name` = CONCAT(UCASE(LEFT(`name`, 1)), LCASE(SUBSTRING(`name`, 2))),
`surname` = CONCAT(UCASE(LEFT(`surname`, 1)), LCASE(SUBSTRING(`surname`, 2)))

SELECT `name`, length(`name`) FROM `users` WHERE 1 ORDER BY LENGHT(`name`) desc;

SELECT * FROM `users` WHERE LENGTH(`name`) > 5 ORDER BY length(`name`) desc;

SELECT * FROM `users`WHERE `surname` LIKE '"%"';

UPDATE `users` SET `surname`= REPLACE (`surname`, '"', '') WHERE `surname` LIKE '"%"';

###
### DATA FIELDS OPTMIZATION
###

SELECT YEAR(`date`), MONTH(`date`), WEEK(`date`,0), DAY(`date`), `date` from `deals`;

SELECT `id`,
YEAR(`date`) as `y`,
MONTH(`date`) as `d`,
WEEK(`date`,0) as `w`,
DAY(`date`) as `day`,
`date`
from `deals`

SELECT `id`,
YEAR(`date`) as `y`,
MONTH(`date`) as `m`,
WEEK(`date`,0) as `w`,
DAY(`date`) as `day`,
`date`
from `deals`
where `y` = 2011

SELECT `id`,
YEAR(`date`) as `y`,
MONTH(`date`) as `m`,
WEEK(`date`,0) as `w`,
DAY(`date`) as `day`,
`date`
from `deals`
where YEAR(`date`) = 2011

ALTER TABLE
`deals`
ADD `y` SMALLINT NOT NULL AFTER `user_id`,
ADD `m` SMALLINT NOT NULL AFTER `y`,
ADD `w` SMALLINT NOT NULL AFTER `m`,
ADD `day` SMALLINT NOT NULL AFTER `w`;

UPDATE `deals`
SET
`y` = YEAR(`date`), `m` = MONTH(`date`),
`w` = WEEK(`date`,0), `day` = DAY(`date`)

SELECT * FROM `deals` WHERE `y` = 2016

 


2016-05-02 03:56:00

 Use username: Guest, Anonymous, Programmer






QUOTES:
I meant to write about death, only life came breaking in as usual.
Virginia Woolf, Diary, 17 February 1922
A man who has not passed through the inferno of his passions has never overcome them.
Carl Jung
Friends are generally of the same sex, for when men and women agree, it is only in the conclusions their reasons are always different.
George Santayana