SELECT * FROM Vzakladke.net

Статьи об автоматизации и программировании

MySQL простые примеры (simple examples)

 

 

Add MySQL User

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' WITH GRANT OPTION;

 

MySQL - granting access permissions

-- mySQL granting DB specific privileges:
GRANT ALL PRIVILEGES on DBNAME.* TO 'user'@'%' IDENTIFIED BY 'password' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 ;   
 
-- mySQL granting generic specific privileges:
GRANT ALL PRIVILEGES on *.* TO 'user'@'%' IDENTIFIED BY 'password' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 ;


Add an user to an mysql account

GRANT ALL privileges ON dbtestname.* TO 'dbtestname'@'192.168.10.0/255.255.255.0' IDENTIFIED BY 'password';
GRANT ALL privileges ON dbtestname.* TO 'dbtestname'@'localhost' IDENTIFIED BY 'password';
FLUSH privileges;

 

Create a user that has only access to one database

CREATE USER 'username'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON `database` . * TO 'username'@'%';
FLUSH PRIVILEGES;

 

MySQL Create Table

CREATE TABLE IF NOT EXISTS sometable (
  id INT(11) NOT NULL AUTO_INCREMENT,
  title VARCHAR(45) DEFAULT '',
  start DATE DEFAULT NULL,
  end DATE DEFAULT NULL,
  description VARCHAR(200) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB

 

MySQL Add Column


ALTER TABLE sometable ADD field_image VARCHAR(100) NULL;

 

MySQL Alter Column to Add Default

ALTER TABLE somefield ALTER sometable SET DEFAULT '0';

 

Drop and Add Restraints in MySQL

SET FOREIGN_KEY_CHECKS = 0;
 
SET FOREIGN_KEY_CHECKS = 1;

 

Reset autoincrement

ALTER TABLE tablename AUTO_INCREMENT = 1

 

Show table creation syntax

SHOW CREATE TABLE sometable;

 

Show database creation syntax


SHOW CREATE DATABASE mysql;


MySQL: Unique Constraint


ALTER TABLE polls_game ADD CONSTRAINT unique_name UNIQUE(name);

 

Rename table

ALTER TABLE table_name
RENAME TO new_table_name;

 

MySQL add counter in query

SET @N = 0;
SELECT @N := @N +1 AS numero, nome,stipendio,genere FROM persone;

 

Sort results by order in list

SELECT id, startdates FROM iddt
WHERE id IN ('109k7','s3x6','sxmns','wt57')
ORDER BY FIELD(id,'109k7','s3x6','sxmns','wt57')

 

Ordering multiple SELECT statements in a UNION

(SELECT a FROM t1 WHERE a=10 AND B=1)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;

 

Differences between two Tables

SELECT 'tabelle1' Tabelle, a.* FROM (
SELECT * FROM tabelle1
MINUS
SELECT * FROM tabelle2
) t1
UNION
SELECT 'tabelle2' Tabelle, b.* FROM (
SELECT * FROM tabelle2
MINUS
SELECT * FROM tabelle1
) t2

 

Re-insert sql data from self

INSERT INTO tablename SELECT NULL, field1, field2, field3... FROM tablename

 

Update column of table

UPDATE user SET user_password = MD5(CONCAT(user_id, '-',MD5('password'))) 
WHERE user_name = 'username';

 

Insert or On Duplicate Key Update 

INSERT INTO table (id,a,b)
   VALUES (1,2,3) 
ON DUPLICATE KEY
   UPDATE a=a, b=b;

 

Search and Replace on Multiple Rows in SQL​ (MySQL Search & Replace on WP-Posts Table)

UPDATE wp_posts SET post_content = REPLACE(post_content,'replace_this','with_this');


Conditional String Replace

UPDATE `table` SET `table`.`fieldname` = REPLACE(`fieldname`, 'search_value', 'replace_with') 
WHERE `table`.`firstcondition_field` = 'firstcondition_value'
AND `table`.`secondcondition_field` = 'secondcondition_value'

 
Select between two dates

SELECT * FROM files LEFT JOIN (dates) ON files.uploaded_id = dates.id WHERE DATE BETWEEN '2009-01-01' AND '2009-12-31'

 

how to calculate last 7 days...

WHERE mydatefld >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)

 

Update random dates in MYSQL table

UPDATE randomdate SET randomdate=DATE_SUB(CURDATE(),INTERVAL RAND()*365 DAY);

 

MySQL Age Calucation

DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(DOB, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(DOB, '00-%m-%d')) >= 65


Display MYSQL date as to human`s readable

SELECT DATE_FORMAT(dateColumnName, '%m/%d/%Y %h:%i %p') AS whatever

 

MSSQL where date=today

WHERE tbl.datecolumn >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AND tbl.datecolumn < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)

 

Get random record in table (using PHP)

SELECT MAX(id) FROM table
 
<?php
  $random_num = RAND(0, $max(id));
?>
 
SELECT * FROM table WHERE id >= $random_num LIMIT 1


Selecting a Random Row In MySQL

SELECT COLUMN FROM table  ORDER BY RAND()  LIMIT 1


Generate random string in MySQL

SELECT SUBSTRING(MD5(RAND()) FROM 1 FOR 6) AS randomstring


Left Joins to link three or more tables

SELECT * FROM (bdg LEFT JOIN res on bdg.bid = res.bid) 
LEFT JOIN dom on res.rid = dom.rid WHERE dom.rid IS NULL;

 

Find duplicate records

SELECT id, COUNT(*) as n
FROM my_table
GROUP BY id
HAVING n >1;

 

Duplicate Entries

SELECT    t1.name, t1.street, t1.house, GROUP_CONCAT(DISTINCT t1.id) dupes
FROM      your_table t1
JOIN      your_table t2 ON (t2.name = t1.name AND 
                            t2.street = t1.street AND 
                            t2.house = t1.house)
GROUP BY  t1.name, t1.street, t1.house
HAVING    COUNT(*) > 1;
 
CREATE TABLE your_table (
   id INT, 
   name VARCHAR(10), 
   street VARCHAR(10), 
   house VARCHAR(10)
);
 
INSERT INTO your_table VALUES (1, 'a', 'b', 'c');
INSERT INTO your_table VALUES (2, 'a', '1', 'c');
INSERT INTO your_table VALUES (3, 'a', '2', '3');
INSERT INTO your_table VALUES (4, 'a', 'b', 'c');
INSERT INTO your_table VALUES (5, 'a', 'b', 'c');
INSERT INTO your_table VALUES (6, 'c', 'd', 'e');
INSERT INTO your_table VALUES (7, 'c', 'd', 'e');
 
+------+--------+-------+-------+
| name | street | house | dupes |
+------+--------+-------+-------+
| a    | b      | c     | 1,5,4 |
| c    | d      | e     | 6,7   |
+------+--------+-------+-------+
2 rows IN SET (0.03 sec)

 

Find values that occur exactly once in table

SELECT email
FROM table
GROUP BY email
HAVING ( COUNT(email) = 1 )

 

Mysql multiple insert from select

INSERT INTO sometable (field1, field1)( SELECT field1, field2 FROM sometable)


MySQL Insert Unique Reference


-- MySQL Insert Unique Reference using MAX() to obtain latest value Alternate solution to using Composite Primary Keys which INNODB engine does not support
INSERT INTO jobs (companyid,clientref,jobid) VALUES (8,2,IFNULL((SELECT max(b.jobid)+1 FROM jobs b WHERE b.clientref=2),1) );

 

Delete Orphaned Records

-- BACKUP, BACKUP, BACKUP!! --
 
DELETE
FROM child_table
WHERE id
NOT IN
(SELECT id FROM parent_table);

 

EAN13 Check - verify's the checksum of a EAN13 barcode

SELECT attributes_ean
FROM products_attributes
WHERE
LENGTH(attributes_ean) = 13
AND
SUBSTRING((10 - ((((
SUBSTRING(attributes_ean FROM 2 FOR 1) +
SUBSTRING(attributes_ean FROM 4 FOR 1) +
SUBSTRING(attributes_ean FROM 6 FOR 1) +
SUBSTRING(attributes_ean FROM 8 FOR 1) +
SUBSTRING(attributes_ean FROM 10 FOR 1) +
SUBSTRING(attributes_ean FROM 12 FOR 1)  
)*3) + (
SUBSTRING(attributes_ean FROM 1 FOR 1) +
SUBSTRING(attributes_ean FROM 3 FOR 1) +
SUBSTRING(attributes_ean FROM 5 FOR 1) +
SUBSTRING(attributes_ean FROM 7 FOR 1) +
SUBSTRING(attributes_ean FROM 9 FOR 1) +
SUBSTRING(attributes_ean FROM 11 FOR 1)
)) MOD 10)) FROM -1 FOR 1) != SUBSTRING(attributes_ean FROM 13 FOR 1)

 

Create CSV from MySQL


SELECT a,b,c FROM my_table INTO OUTFILE '/ca.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';


Load CSV back into mySQL


LOAD DATA local INFILE 'unique.csv' INTO TABLE tablename
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(col1, col2, col3)

 

Correcting wrong character encoding in MySQL data

UPDATE table SET COLUMN=CONVERT(CONVERT(CONVERT(COLUMN USING latin1) USING BINARY) USING utf8);

 

Convert timezone to local

SELECT CONVERT_TZ(mydatefield,'US/Pacific','CET') FROM mytable;

 

Insert or update if unique key already exist

INSERT INTO `tableName`
VALUES (?,?,?,'-1',?,'0')
ON DUPLICATE KEY
UPDATE `id` = ?, `foo` = ?, `bar` = ?

 

Date formatting with LOAD DATA INFILE

LOAD DATA INFILE '/tmp/foo.dat'
INTO TABLE bar
FIELDS TEMINATED BY '|'
LINE TERMINATED BY '\n'
(col1, col2, @col3, @col4, col5)
SET
col3 = DATE_FORMAT(@col3, '%m/%d/%Y'),
col4 = DATE_FORMAT(@col4, '%Y-%m-%d')


MySql script to find invalid email addresses

SELECT * FROM people WHERE email NOT LIKE '%_@__%.__%'

 

MySQL REGEX match comma-delimitted list

WHERE id REGEXP '(,|^){$this->id}(,|$)'

 

MySQL procedure throw_error

DROP procedure IF EXISTS throw_error;
delimiter $$
CREATE PROCEDURE throw_error (errno BIGINT UNSIGNED, message VARCHAR(256))
BEGIN
SIGNAL SQLSTATE 'ERROR'
SET
    MESSAGE_TEXT = message,
    MYSQL_ERRNO = errno;
END $$
delimiter ;

 

MySQL Trigger

CREATE TRIGGER table_created BEFORE INSERT ON `table`
FOR EACH ROW SET NEW.created = NOW(), NEW.created = '0000-00-00 00:00:00';
 
CREATE TRIGGER table_updated BEFORE UPDATE ON `table`
FOR EACH ROW SET NEW.updated = NOW(), NEW.created = OLD.created;

 

MySQL tablename search information schema

SELECT table_catalog, table_schema, table_name, table_type, engine
FROM information_schema.TABLES
WHERE table_name LIKE '%name%';

 

Get the MySQL databases size from

SELECT table_schema "Data Base Name", 
sum( data_length + index_length ) / 1024 / 
1024 "Data Base Size in MB", 
sum( data_free )/ 1024 / 1024 "Free Space in MB" 
FROM information_schema.TABLES 
GROUP BY table_schema ;

 

Storing an IP address in a database table

SELECT INET_ATON('192.168.0.10') AS ipn;
SELECT INET_NTOA(3232235530) AS ipa;
INSERT INTO tbl VALUES (..., INET_ATON('192.168.0.10'), ...);

 

Select the primary key for a table

SELECT k.column_name
FROM information_schema.table_constraints t
JOIN information_schema.key_column_usage k
USING(constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY'
  AND t.table_schema='db'
  AND t.table_name='tbl';

 

Add AUTO_INCREMENT PRIMARY KEY field in a database table

ALTER TABLE table5 ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

 

Дата публикации: 2016-01-03 15:42:30

MySQL

2

Отзывы:

Ваше имя:

Ваш e-mail (необязательно):

Сообщение:

Captcha