MySQL/Syntax

Aus SchnallIchNet
< MySQL(Weitergeleitet von Mysql/Syntax)
Wechseln zu: Navigation, Suche

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;
Achtung.jpeg To delete some users completely (to revoke 'USAGE', too) you will have to use 'DROP USER user' 
DROP USER username, username2, ...;


replicate User/Password to 2nd server

while read line; do 
   usr=`echo $line | awk '{print $1}'`; 
   mysql -B -N -e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') AS query FROM user" mysql | \
      grep  "$usr" | mysql | sed -e "s/Grants for/-- Grants for/g"; 
   echo; echo; 
done </tmp/mysqlusers.txt [> /bla/grants.sql]

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


replay to 2nd host

replay queries to a 2nd host...
see: http://codeascraft.etsy.com/2013/03/19/the-perils-of-sql_mode/


percona playback

see: http://www.percona.com/doc/percona-playback/index.html


percona toolkit

you need golang
you need mysql_query_multiplexer.go

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 [where table_schema = "OneOfMyDatabases"];

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:

  1. FLOOR (RAND() * (SELECT count(*) FROM myTable)) num - produce an integer value between 0 to the max rows this table has
  2. (SELECT @num:=0) a – initialize the User Defined Variable a to zero
  3. (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:

  1. create a backup of you database-files (*.frm, *.MYD, *.MYI)
  2. myisamchk -r
    to recover
  3. if previous step failed: 
    myisamchk --safe-recover [tabelle]
  4. if that all does not work refer to: Mysql-Page
Von „https://schnallich.net/index.php?title=MySQL/Syntax&oldid=1365“