cmd used in sql tests （sql学习指南）

/* 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