MySQL/Syntax: Unterschied zwischen den Versionen

Version vom 2. Mai 2013, 10:42 Uhr

Connect to mysql

To login (from unix shell) use -h only if needed.

# mysql -h hostname -u root -p

Create a database on the sql server.

mysql> create database [databasename];

List all databases on the sql server.

mysql> show databases;

Switch to a database.

mysql> use [db name];

To see all the tables in the db.

mysql> show tables;

To see database's field formats.

mysql> DESCRIBE [table name];
mysql> SHOW CREATE table [table name] \G

To delete a db.

mysql> drop database [database name];

To delete a table.

mysql> drop table [table name];

Show all data in a table.

mysql> SELECT * FROM [table name];

Returns the columns and column information pertaining to the designated table.

mysql> show columns from [table name];

CREATE/COPY tables

Create Table Example 1.

mysql> CREATE TABLE [table name] (
          firstname VARCHAR(20), 
          middleinitial VARCHAR(3), 
          lastname VARCHAR(35),
          suffix VARCHAR(3),
          officeid VARCHAR(10),
          userid VARCHAR(15),
          username VARCHAR(8),
          email VARCHAR(35),
          phone VARCHAR(25), 
          groups VARCHAR(15),
          datestamp DATE,
          timestamp time,
          pgpemail VARCHAR(255)
       );

Create Table Example 2.

mysql> create table [table name] (
          personid int(50) not null auto_increment primary key,
          firstname varchar(35),
          middlename varchar(50),
          lastname varchar(50) default 'bato'
       );

Copy a table

CREATE TABLE student2 SELECT * FROM student;

SELECT's

get last insert ID

SELECT LAST_INSERT_ID();

select into OUTFILE e.g. .csv

SELECT dl . * , ht.typ, h.hersteller
INTO OUTFILE "/tmp/goober_downloads.txt"
FIELDS TERMINATED BY ','
FROM `downloads` dl
JOIN handy_typen ht ON dl.handyid = ht.handyid
JOIN handy_hersteller h ON dl.herstellerid = h.herstellerid
WHERE dldate > '2006-05-14'

select into table

mysql>  INSERT INTO tbl_temp2 (fld_id)
          SELECT tbl_temp1.fld_order_id
          FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

Show certain selected rows with the value "whatever".

mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";

Show all records containing the name "Bob" AND the phone number '3444444'.

mysql> SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';

Show all records not containing the name "Bob" AND the phone number '3444444' order by the phone_number field.

mysql> SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;

Show all records starting with the letters 'bob' AND the phone number '3444444'.

mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';

Show all records starting with the letters 'bob' AND the phone number '3444444' limit to records 1 through 5.

mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444' limit 1,5;

Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.

mysql> SELECT * FROM [table name] WHERE rec RLIKE "^a";

Show unique records.

mysql> SELECT DISTINCT [column name] FROM [table name];

Show selected records sorted in an ascending (asc) or descending (desc).

mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

Return number of rows.

mysql> SELECT COUNT(*) FROM [table name];

Sum column.

mysql> SELECT SUM(*) FROM [table name];

Join tables on common columns.

mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on
lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

SELECT DB- and/or Table-Sizes

get size of all db-tables:

SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table',
CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)," Mb") AS Size FROM INFORMATION_SCHEMA.TABLES;

get size of specific DB-table:

SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table',
CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)," MB") AS Size FROM INFORMATION_SCHEMA.TABLES 
where TABLE_SCHEMA like '%YOUR_DB_NAME%';

get size of entire DB:

SELECT CONCAT(sum(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2))," MB") AS Size 
FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA like '%YOUR_DB_NAME%' ;

INSERT's

insert multiple rows:

INSERT INTO x (a,b) VALUES ('1', 'one'), ('2', 'two'), ('3', 'three');

UPDATE / ALTER

To update info already in a table.

mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';

Delete a row(s) from a table.

mysql> DELETE from [table name] where [field name] = 'whatever';

Delete a column.

mysql> alter table [table name] drop column [column name];

Add a new column to db.

mysql> alter table [table name] add column [new column name] varchar (20);

Change column name.

mysql> alter table [table name] change [old column name] [new column name] varchar (50);

Make a unique column so you get no dupes.

mysql> alter table [table name] add unique ([column name]);

Make a column bigger.

mysql> alter table [table name] modify [column name] VARCHAR(3);

Add new column AND index over that column:

mysql> alter table [table name] add [NEW column name] INT(11) not null default 0 after [existent column name], 
       add index [NEW column name]_idx ([NEW column name]);

Add primary key to column

ALTER TABLE [table name] ADD PRIMARY KEY ([column name]);

Delete unique from table.

mysql> alter table [table name] drop index [colmn name];

Load a CSV file into a table.

mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);

SHOW

some SHOW statements

show create table statement

SHOW CREATE TABLE [table] \G

show variables set by *.cnf or daemon-parameter

SHOW VARIABLES;

or

SHOW VARIABLES LIKE 'query_cache_%';

show binary logs to list available binlog-files

SHOW {BINARY | MASTER} LOGS

show contents of bin-logs

SHOW BINLOG EVENTS
  [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

Clustering/BinLogs

Delete/Purge master logs (BinLogs) by date:

purge_date=`date -d "8 days ago" "+%Y-%m-%d %H:%M:%S"`
mysql> PURGE MASTER LOGS BEFORE '${purge_date}';

Delete/Purge master logs (BinLogs) by filename:

PURGE BINARY LOGS TO 'mysql-bin.010';

Users, Passwords and Rights

Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));
mysql> flush privileges;

Change a users password from unix shell.
Better use 'GRANT'-syntax

# mysqladmin -u username -h hostname.blah.org -p password 'new-password'

Change a users password from MySQL prompt. Login as root. Set the password. Update privs.

# mysql -u root -p
mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
mysql> flush privileges;

Set a root password if there is no root password.

# mysqladmin -u root password [new_super_secure_password]

Update a root password.

# mysqladmin -u root -p oldpassword newpassword

Allow the user "bob" to connect to the server from localhost using the password "passwd". Login as root. Switch to the MySQL db. Give privs. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by 'passwd';
mysql> flush privileges;

Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES
('%','databasename','username','Y','Y','Y','Y','Y','N');
mysql> flush privileges;

or

mysql> grant all privileges on databasename.* to username@localhost identified by 'passwd' [WITH GRANT OPTION];
mysql> flush privileges;

or, if you have an allready encrypted/hashed password

mysql> grant all privileges on databasename.* to username@localhost identified by PASSWORD '*5005AAD8663A55CE7F024C008B5810A5B50D77EE' 
       [WITH GRANT OPTION];
mysql> flush privileges;

to generate a 'NEW'-style hashed password with php:

php -a
Interactive shell

php > print "*" . strtoupper(sha1(sha1("Passwort", true))) . "\n";
*5005AAD8663A55CE7F024C008B5810A5B50D77EE

or use PASSWORD-function to set password for user

SET PASSWORD FOR openser@'%'=PASSWORD('123456');
SET PASSWORD FOR openser@localhost=PASSWORD('123456');

setze rechte

GRANT ALL PRIVILEGES ON asterisk.* TO asteriskuser@localhost IDENTIFIED BY 'amp109';

setze rechte inkl. grant-recht
heisst der user darf rechte vergeben...

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

revoke rights

REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'host;

To delete some users completely (to revoke 'USAGE', too) you will have to use 'DROP USER user'

DROP USER username, username2, ...;

Backup/Dump/Copy

Backup/Dump or copy data

structure AND data

Dump all databases for backup. Backup file is sql commands to recreate all db's.

# mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

Dump one database for backup.

# mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql

Dump a table from a database.

# mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup.

# mysql -u username -ppassword databasename < /tmp/databasename.sql

structure only

# mysqldump -u username -ppassword --no-data --databases databasename >/tmp/databasename.structure.sql

COPY databases

Copy database on the same host

mysql> create database [DBname of COPY]
mysql> mysqldump [orig. DBname] | mysql [DBname of COPY]

use -h option to mysqldump or mysql to pull/push db from/to a remote host

you can prevent using golang if you use percona-playback package which is precompiled
for debian squeeze+ available...

mysql-production # /usr/sbin/tcpdump -i INTERFACE \
  "port 3306 and tcp[1] & 7 == 2 and tcp[3] & 7 == 2" \
  -s 65535 -x -n -q -tttt | \
  nc testdb.domain.tld 20000

testdb # nc -l 20000 | pt-query-digest --type tcpdump \
 --no-report --print | grep -v "SET NAMES utf8" | \
 go run mysql_query_multiplexer.go -db-user=username \
 -db-password=password -db-host=localhost \
 -db-charset=utf8mb4 -log "/var/tmp/shard.log" \
 -db-name=etsy_shard -threads=8

hotcopy

see: LVM

Optimizer stuff

mysql> explain extended select ...;
mysql> show warnings \G

Shows what the optimizer actually did. In this example, An IN was converted to EXISTS

Misc

some misc things, mostly administrative...

get/calc sizes of columns

SELECT table_name AS "Table", 
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" 
FROM information_schema.TABLES 
WHERE table_schema = "$DB_NAME"
 AND table_name = "$TABLE_NAME";

check limits of integer-columns

check limits of integer-columns inside MySQL.
Attention(!): needs IOPS and time.

SELECT
  TABLE_SCHEMA,
  TABLE_NAME,
  COLUMN_NAME,
  DATA_TYPE,
  COLUMN_TYPE,
  IF(
    LOCATE('unsigned', COLUMN_TYPE) > 0,
    1,
    0
  ) AS IS_UNSIGNED,
  (
    CASE DATA_TYPE
      WHEN 'tinyint' THEN 255
      WHEN 'smallint' THEN 65535
      WHEN 'mediumint' THEN 16777215
      WHEN 'int' THEN 4294967295
      WHEN 'bigint' THEN 18446744073709551615
    END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1)
  ) AS MAX_VALUE,
  AUTO_INCREMENT,
  AUTO_INCREMENT / (
    CASE DATA_TYPE
      WHEN 'tinyint' THEN 255
      WHEN 'smallint' THEN 65535
      WHEN 'mediumint' THEN 16777215
      WHEN 'int' THEN 4294967295
      WHEN 'bigint' THEN 18446744073709551615
    END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1)
  ) AS AUTO_INCREMENT_RATIO
FROM
  INFORMATION_SCHEMA.COLUMNS
  INNER JOIN INFORMATION_SCHEMA.TABLES USING (TABLE_SCHEMA, TABLE_NAME)
WHERE
  TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA', 'performance_schema')
  AND EXTRA='auto_increment'
;

get tables 'last update' timestamp

select table_schema,table_name,update_time from information_schema.tables;

Random SELECT

My solution to this problem is using User Defined Variables. Let’s say we want X random rows from a table myTable.

SELECT myTable.* FROM (SELECT FLOOR (RAND() * (SELECTcount(*) FROM myTable)) num ,@num:=@num+1 from (SELECT @num:=0) a , myTable LIMIT X) b , 
myTable WHERE b.num=myTable.id;

I will try to explain some of the parts:

FLOOR (RAND() * (SELECT count(*) FROM myTable)) num - produce an integer value between 0 to the max rows this table has
(SELECT @num:=0) a – initialize the User Defined Variable a to zero
(SELECT FLOOR (RAND() * (SELECTcount(*) FROM myTable)) num ,@num:=@num+1 from (SELECT @num:=0) a , myTable LIMIT X) b – produces X random integer numbers

If you need it with range, say X random rows from a table myTable with a range from S to T you can write it like this:

SELECT myTable.* FROM (SELECT FLOOR (S + RAND() * (T – S)) num ,@num:=@num+1 FROM (SELECT @num:=0) a , myTable LIMIT X) b ,  myTable 
WHERE b.num=myTable.id;

Some caution:
because this query uses User Defined Variable (@num) which are connection specific, you can NOT use this MySQL query with a system that uses connection pooling or persistent connections.

If there are some holes in the table you can fix it by:

SELECT (SELECT myTable2.id from myTable myTable2 where myTable2.id>=b.num LIMIT 1) c 
FROM (SELECT FLOOR(RAND() * (SELECTcount(*) 
FROM myTable)) num ,@num:=@num+1 
FROM (SELECT @num:=0) a , myTable LIMIT X) b ;

To improve performance you can combine this solution with a left join:

SELECT IF(c.id is null, (SELECT c2.id FROM myTable c2 WHERE c2.id > b.num LIMIT 1), c.id) d 
FROM (SELECT FLOOR(RAND() * (SELECTcount(*) FROM myTable)) num ,@num:=@num+1 
FROM (SELECT @num:=0) a , myTable LIMIT X) b LEFT JOIN myTable c ON( c.id=b.num);

fragmented tables

SELECT COUNT(TABLE_NAME) 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') 
AND Data_free > 0 AND NOT ENGINE='MEMORY';

anzahl fragmentierter tabellen auslesen

SELECT concat(TABLE_SCHEMA, ".", TABLE_NAME)
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') 
AND Data_free > 0 AND NOT ENGINE='MEMORY';

namen fragmentierter tabellen auslesen

OPTIMIZE TABLE db.table1, db.table2, db2.table1, ...;

defragmentieren ;-)

Crashed Tables

CHECK TABLE [tabelle]

and

REPAIR TABLE [tabelle]

if that does not do the job try:

mysqladmin shutdown          # (or use init-script)
myisamchk *.MYI

or

myisamchk -e *.MYI           # if you have more time.

perhaps use -s (silent) option

after that first run:

myisamchk -r -q [tabelle]

to try an quick recover

if that didn't do the job:

create a backup of you database-files (*.frm, *.MYD, *.MYI)
```
myisamchk -r
```
to recover
if previous step failed:
```
myisamchk --safe-recover [tabelle]
```
if that all does not work refer to: Mysql-Page