MySQL

From JWik
Jump to navigationJump to search

Queries

CREATE TABLE

CREATE TABLE example_autoincrement (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(100),
    display_id BIGINT ,
    last_refresh DATETIME,
);
   Query OK, 0 rows affected (0.01 sec)

INSERT INTO

   mysql> INSERT INTO example_autoincrement (data)
   ->      VALUES ('Hello world');
   Query OK, 1 row affected (0.01 sec)

UPDATE

UPDATE t1 SET col1 = col1 + 1;
UPDATE t1 SET col1 = col1 + 1, col2 = col1;
UPDATE items, month SET items.price=month.price WHERE items.id=month.id;

CREATE INDEX

CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,... );
CREATE INDEX index_name ON table_name ( column1, column2,... );

ALTER TABLE

ALTER TABLE t2 ADD last_name varchar(40) NOT NULL;
ALTER TABLE t2 ADD i INT NOT NULL;;
ALTER TABLE t2 ADD d TIMESTAMP NOT NULL;
ALTER TABLE t2 ADD d DATETIME NULL;
ALTER TABLE t2 DROP COLUMN c;
ALTER TABLE t2 RENAME t1;
ALTER TABLE t2 ADD last_name varchar(40) NOT NULL AFTER contact_id;
ALTER TABLE t2 MODIFY last_name varchar(50) NULL;
ALTER TABLE t2 CHANGE old_column_name new_column_name INT;

DELETE

DELETE FROM tutorials_tbl WHERE tutorial_id=3;
SET SQL_SAFE_UPDATES = 0;
DELETE FROM some_table WHERE not_id_column = 1;

Administration/Usage

Connect

Connection to the server

mysql -u root -p --port=6309 -h localhost

Administration

Database creation

CREATE DATABASE database_name;

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'pass1';

FLUSH PRIVILEGES;

Grant user rights

GRANT SELECT,INSERT,UPDATE,DELETE,DROP,CREATE,ALTER,INDEX ON database_name.* TO 'user1'@'localhost';

GRANT ALL ON database_name.* TO 'user1'@'localhost';

FLUSH PRIVILEGES;

Password change

(mysql database) UPDATE user SET Password=PASSWORD('new_pass') WHERE User='username';

(mysql database) UPDATE user SET User='username_new' WHERE User='username_old';

Open the database for non-localhost users

CREATE USER 'user1'@'%' IDENTIFIED BY 'pass1'; GRANT SELECT,INSERT,UPDATE,DELETE,DROP,CREATE,ALTER ON database.* TO 'user1'@'%';

Show users / list users

SELECT user, host, password FROM mysql.user;

Making diagrams

dbdesigner4 - untested, but seems nice

Show DB structure

TOAD


Tricks

Showing table columns ordered by name

Showing table columns ordered by name:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'tablename'
ORDER BY column_name;

Showing only name, type and schema:

SELECT column_name, column_type, table_schema
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'tablename'
ORDER BY column_name;

mysqldump

Създаване на dump

mysqldump -u reports --password=password --host=192.168.30.150 --triggers --routines --events --set-gtid-purged=OFF reports > reports-2016-03-01.sql

Въвеждане на dump

mysql -u root --password=root reports < reports-2016-03-01.sql