MySQL常用命令

创建数据库
mysql -u root -p
create database bank;
grant all privileges on bank.* to 'lrngsql'@'localhost' identified by 'xyz';
quit;

登录数据库
mysql -u lrngsql -p
use bank;
或者 mysql -u lrngsql -p bank;
清除cmd命令行：cls

查看数据库中可用的表：show tables;

导入SQL
source g:\temp\LearningSQLExample.sql

建表
CREATE TABLE person
(person_id SMALLINT UNSIGNED,
fname VARCHAR(20),
lname VARCHAR(20),
gender ENUM('M' , 'F'),
birth_date DATE,
street VARCHAR(20),
city VARCHAR(20),
state VARCHAR(20),
country VARCHAR(20),
postal_code VARCHAR(20),
CONSTRAINT pk_person PRIMARY KEY (person_id)
);

ENUM('M' , 'F'), --枚举类型
CONSTRAINT pk_person PRIMARY KEY (person_id) --主键约束，它被创建在person_id列上并被命名为pk_person.
创建完成以后可以使用describe person;来检查表定义

 CREATE TABLE favorite_food
      (person_id SMALLINT UNSIGNED,
       food VARCHAR(20),
       CONSTRAINT pk_favorite_food PRIMARY KEY (person_id, food),
       CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id) REFERENCES person (person_id)
      );

一个人可能有多种喜爱的食物，因此主键包含两列。
外键约束，它限制了favorite表中person_id列的值只能够来自person表。

插入数据
生成数字型主键数据，打开主键列的自增（auto-increment）特性。
先锁定要修改的表：
LOCK TABLES
favorite_food WRITE,
person WRITE;
删除外键：
ALTER TABLE favorite_food
DROP FOREIGN KEY fk_fav_food_person_id,
MODIFY person_id SMALLINT UNSIGNED;
打开自增特性：
ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;
重新生成外键：
ALTER TABLE favorite_food
ADD CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id) REFERENCES person (person_id);
解锁：
UNLOCK TABLES;

插入数据
INSERT INTO person
(person_id, fname, lname, gender, birth_date)
VALUES (null, 'William', 'Turner', 'M', '1972-05-27');
INSERT INTO favorite_food
(person_id, food)
VALUES (1, 'pizza');
INSERT INTO favorite_food
(person_id, food)
VALUES (1, 'cookies');
INSERT INTO favorite_food
(person_id, food)
VALUES (1, 'nachos');
INSERT INTO person
(person_id, fname, lname, gender, birth_date, street, city, state, country, postal_code)
VALUES (null, 'Susan', 'Smith', 'F', '1975-11-02', '23 Maple St. ', 'Arlington', 'VA', 'USA', '20220');

更新数据：
UPDATE person
SET street = '1225 Tremont St. ',
city = 'Boston',
state = 'MA',
country = 'USA',
postal_code = '02138'
WHERE person_id = 1;

删除数据：
DELETE FROM person
WHERE person_id = 2;

删除表：
DROP TABLES favorite_food;
DROP TABLES person;