MySql 091217: General Cmd

@ http://zcatt.cublog.cn

1.    Connect and Disconnect

mysql -h host -u user -p

mysql -u user -p

 

h	Hostname or IP
u	user name
p	password

 

select version(), current_date;

select now();

select user();

2. Creating DB

show databases;

 

create databases dbname;

 

use dbname

 

show tables;

 

CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),

species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

 

DESC pet;

 

LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet

LINES TERMINATED BY '\r\n';

 

INSERT INTO pet

 VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

 

 

DELETE FROM pet;

LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE pet;

 

UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

 

3.  Select sentence

SELECT what_to_select

FROM which_table

WHERE conditions_to_satisfy;

 

SELECT name, birth FROM pet ORDER BY birth;

 

SELECT name, species, birth FROM pet

 ORDER BY species, birth DESC;

 

SELECT * FROM pet WHERE name LIKE 'b%';

 

SELECT * FROM pet WHERE name LIKE '_____';

 

SELECT * FROM pet WHERE name REGEXP 'fy$';

 

SELECT * FROM pet WHERE name REGEXP '^.....$';

 

SELECT owner, COUNT(*) FROM pet GROUP BY owner;

 

SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species

 FROM pet AS p1 INNER JOIN pet AS p2

 ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';

 

 

4. Get info

SELECT DATABASE();

 

SHOW TABLES;

 

DESCRIBE pet;

 

 

5. Batch Mode

mysql> source filename;