Mysql 操作

1. 建立和断开链接 建立链接

shell> mysql -h localhost -u user -p

断开链接

shell> QUIT

或者Control+D.

2. query语句

mysql> SELECT VERSION(), CURRENT_DATE;
+-----------+--------------+
| VERSION() | CURRENT_DATE |
+-----------+--------------+
| 5.8.0-m17 | 2015-12-21   |
+-----------+--------------+
1 row in set (0.02 sec)
mysql> SELECT USER();

3. 创建数据库，数据表

mysql> CREATE DATABASE menagerie;
mysql> USE menagerie;
mysql> SHOW TABLES;
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
       species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
mysql> SHOW TABLES;
mysql> DESCRIBE pet;
mysql> INSERT INTO pet
       VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

4. 删除修改查询table

mysql> DELETE FROM pet;
mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';
mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
       OR (species = 'dog' AND sex = 'f');
mysql> SELECT name, birth FROM pet;
mysql> SELECT name, birth FROM pet ORDER BY birth;
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
mysql> SELECT name, birth, CURDATE(),
       TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
       FROM pet;
mysql> SELECT name, birth, CURDATE(),
       TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
       FROM pet;
mysql> ALTER TABLE name CHANGE column old_name new_name column_definition;
mysql> ALTER TABle name RENAME column old_name to new_name;

5. 正则匹配

mysql> SELECT * FROM pet WHERE name LIKE 'b%';
mysql> SELECT * FROM pet WHERE name LIKE '_____';
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b');
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.....$');
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.{5}$');

6. 计算rows

mysql> SELECT COUNT(*) FROM pet;
mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
mysql> SELECT species, sex, COUNT(*) FROM pet
       WHERE species = 'dog' OR species = 'cat'
       GROUP BY species, sex;
mysql> SELECT species, sex, COUNT(*) FROM pet
       WHERE sex IS NOT NULL
       GROUP BY species, sex;

7. 链接表

mysql> SELECT pet.name,
       TIMESTAMPDIFF(YEAR,birth,date) AS age,
       remark
       FROM pet INNER JOIN event
         ON pet.name = event.name
       WHERE event.type = 'litter';

8. 批处理

shell> mysql < batch-file
C:\> mysql -e "source batch-file"
shell> mysql -h host -u user -p < batch-file
Enter password: ********

mysql> source filename;
mysql> \. filename

9. UNION

mysql> SELECT field1_index, field2_index
    FROM test_table WHERE field1_index = '1'
UNION
SELECT field1_index, field2_index
    FROM test_table WHERE field2_index = '1';

10. 其他

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;
ALTER TABLE tbl AUTO_INCREMENT = 100;

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;