Miscellany

  • shell> mysql -h hostname -u usernamme -p myDB
    • mysql> \c -- Cancels command.
    • mysql> quit
  • mysql> show databases;
    • mysql> create database myDB;
    • mysql> use myDB;
  • mysql> show tables;
    • mysql> create table t2 (col1 int, col2 char);
    • mysql> create table typical_table( t_ID int not null auto_increment primary key, t_str varchar(64), t_strb text, t_dtm datetime, t_int int, t_flt float )
    • mysql> describe t2;
    • mysql> show create table t2;
    • mysql> drop table t2;
  • mysql> alter table t1 rename t2;
    • mysql> alter table t2 modify a tinyint not null; -- Changes definition of column a.
    • mysql> alter table t2 change b c char(20); -- Changes definition and name of column b.
    • mysql> alter table t2 drop column c;
    • mysql> alter table t2 add col1 int first, add col3 text after col2; -- The default location of added columns is last.
  • mysql> load data local infile 'pathAndFile' into table table lines terminated by '\r\n'; Where each line is a record with fields separated by tabs, and NULLs are represented as literal '\N'. The lines terminated portion is just for Windows.
    • mysql> INSERT INTO t2 (ID, name) VALUES (1,'dog') , (2,'cat'); -- You can insert multiple rows with a single statement.
  • mysql> insert into t2 values(1,'hi');
    • mysql> insert into t2 values(null,79); -- insert null for auto_increment columns
    • mysql> select last_insert_id(); -- to get latest ID
      mysql> select @@identity; -- to get latest ID as of MySQL 3.23.25
    • mysql> ALTER TABLE t2 AUTO_INCREMENT=5000; -- resets the initial auto_increment value --unless that column has already has a value greater than what you reset to.
  • mysql> select now(), curdate(), curtime(), utc_timestamp();
    • mysql> SELECT CURDATE(), (YEAR(CURDATE())-YEAR(birth)) - (RIGHT(CURDATE(),5)<RIGHT(birth,5)); -- Show curdate and age if birth is a date field.
    • mysql> select year(curdate()), month(curdate());
    • mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
      mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
  • mysql> select @@version
  • mysql> load data local infile '/pat/source.txt' into table t1 lines terminated by '\r\n'; -- source files have 1 record per line, tabs between columns, null indicated literally by "\N".

Links

Links that lead to off-site pages about MySQL.



GeorgeHernandez.comSome rights reserved