本文共 6015 字，大约阅读时间需要 20 分钟。

/* login mysql with root */ mysql -u root -p /* create bank database */ create database bank /* create user ccc */ create user 'ccc'@'%' identified by ''; /* grant priviledges for ccc */ grant all on bank.* to 'ccc'@'%' /* quit or exit to windows shell */ quit exit /* login mysql with ccc -p bank */ mysql -u ccc -p bank /* if no -p bank, execute use bank */ /* get current time */ select now(); select now() from dual; /* show character sets */ show character set; /* create table person */ create table person (person_id smallint unsigned,fname varchar(20),lname varchar(20),gender enum('M', "F"),birth_date date,street varchar(30),city varchar(20),state varchar(20),country varchar(20),postal_code varchar(20),constraint pk_person primary key (person_id)); desc person +-------------+----------------------+------+-----+---------+-------+ | Field       | Type                 | Null | Key | Default | Extra | +-------------+----------------------+------+-----+---------+-------+ | person_id   | smallint(5) unsigned | NO   | PRI | NULL    |       | | fname       | varchar(20)          | YES  |     | NULL    |       | | lname       | varchar(20)          | YES  |     | NULL    |       | | gender      | enum('M','F')        | YES  |     | NULL    |       | | birth_date  | date                 | YES  |     | NULL    |       | | street      | varchar(30)          | YES  |     | NULL    |       | | city        | varchar(20)          | YES  |     | NULL    |       | | state       | varchar(20)          | YES  |     | NULL    |       | | country     | varchar(20)          | YES  |     | NULL    |       | | postal_code | varchar(20)          | YES  |     | NULL    |       | +-------------+----------------------+------+-----+---------+-------+ 10 rows in set (0.02 sec) /* create table favorite_food */ 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)); mysql> desc favorite_food; +-----------+----------------------+------+-----+---------+-------+ | Field     | Type                 | Null | Key | Default | Extra | +-----------+----------------------+------+-----+---------+-------+ | person_id | smallint(5) unsigned | NO   | PRI | NULL    |       | | food      | varchar(20)          | NO   | PRI | NULL    |       | +-----------+----------------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> show tables     -> ; +----------------+ | Tables_in_bank | +----------------+ | favorite_food  | | person         | +----------------+ 2 rows in set (0.00 sec) mysql> alter table person modify person_id smallint unsigned auto_increment; ERROR 1833 (HY000): Cannot change column 'person_id': used in a foreign key constraint 'fk_fav_food_person_id' of table 'bank.favorite_food' insert into person (person_id, fname, lname, gender, birth_date) values (1, 'William', 'Turner', 'M', '1972-05-27'); mysql> select person_id, fname, lname, birth_date from person; +-----------+---------+--------+------------+ | person_id | fname   | lname  | birth_date | +-----------+---------+--------+------------+ |         1 | William | Turner | 1972-05-27 | +-----------+---------+--------+------------+ 1 row in set (0.00 sec) select person_id, fname, lname, birth_date from person where person_id = 1; mysql> select person_id, fname, lname, birth_date     -> from person     -> where person_id = 1; +-----------+---------+--------+------------+ | person_id | fname   | lname  | birth_date | +-----------+---------+--------+------------+ |         1 | William | Turner | 1972-05-27 | +-----------+---------+--------+------------+ 1 row in set (0.00 sec) insert into favorite_food (person_id, food) values (1, 'pizza'); select * from favorite_food;   insert into favorite_food (person_id, food) values (1, 'cookies'); select * from favorite_food; insert into favorite_food (person_id, food) values (1, 'nachos'); select * from favorite_food; mysql> insert into favorite_food (person_id, food)     -> values (1, 'nachos'); Query OK, 1 row affected (0.00 sec) mysql> select * from favorite_food; +-----------+---------+ | person_id | food    | +-----------+---------+ |         1 | cookies | |         1 | nachos  | |         1 | pizza   | +-----------+---------+ 3 rows in set (0.00 sec) insert into person (person_id, fname, lname, gender, birth_date, street, city, state, country, postal_code) values (2, 'Susan', 'Smith', 'M', '1975-11-02', '23 Maple st.', 'Arlington', 'VA', 'USA', '20220'); /* show data in xml format */ C:\Users\cshanron>mysql -u ccc -p --xml bank mysql> select * from favorite_food; <?xml version="1.0"?> <resultset statement="select * from favorite_food;" xmlns:xsi="http://www.w3.org /2001/XMLSchema-instance">   <row>         <field name="person_id">1</field>         <field name="food">cookies</field>   </row>   <row>         <field name="person_id">1</field>         <field name="food">nachos</field>   </row>   <row>         <field name="person_id">1</field>         <field name="food">pizza</field>   </row> </resultset> 3 rows in set (0.00 sec) update person set street = '1225 Tremont St.', city = 'Boston', state = 'MA', country = 'USA', postal_code = '02138' where person_id = 1; mysql> select * from person     -> ; <?xml version="1.0"?> <resultset statement="select * from person;" xmlns:xsi="http://www.w3.org/2001/X MLSchema-instance">   <row>         <field name="person_id">1</field>         <field name="fname">William</field>         <field name="lname">Turner</field>         <field name="gender">M</field>         <field name="birth_date">1972-05-27</field>         <field name="street">1225 Tremont St.</field>         <field name="city">Boston</field>         <field name="state">MA</field>         <field name="country">USA</field>         <field name="postal_code">02138</field>   </row>   <row>         <field name="person_id">2</field>         <field name="fname">Susan</field>         <field name="lname">Smith</field>         <field name="gender">M</field>         <field name="birth_date">1975-11-02</field>         <field name="street">23 Maple st.</field>         <field name="city">Arlington</field>         <field name="state">VA</field>         <field name="country">USA</field>         <field name="postal_code">20220</field>   </row> </resultset> 2 rows in set (0.00 sec) /* delete one from table person */ delete from person where person_id = 2; in my local pc, sql databases and tables can be found from: C:\ProgramData\MySQL\MySQL Server 5.7\Data\bank>ls db.opt  favorite_food.frm  favorite_food.ibd  person.frm  person.ibd

转载地址：http://pbqzb.baihongyu.com/