MySQLとSQL入門
- 《MySQL 》
-
- mysql> create table member (
- member_id int unsigned not null auto_increment,
- primary key (member_id),
- last_name varchar(20) not null,
- first_name varchar(20) not null,
- suffix varchar(5) null,
- expriration date null,
- email varchar(100) null,
- street varchar(50) null,
- city varchar(50) null,
- state varchar(2) null,
- zip varchar(10) null,
- phone varchar(20) null,
- interests varchar(255) null
- );
- unsigned:
- not null: ，
- auto_increment：MySQL ， ，auto_increment ， member_id ，
- primary key： member_id ， ， primary key not null， member_id not null，MySQL
-
-
- mysql> desc member;
- +-------------+------------------+------+-----+---------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +-------------+------------------+------+-----+---------+----------------+
- | member_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
- | last_name | varchar(20) | NO | | NULL | |
- | first_name | varchar(20) | NO | | NULL | |
- | suffix | varchar(5) | YES | | NULL | |
- | expriration | date | YES | | NULL | |
- | email | varchar(100) | YES | | NULL | |
- | street | varchar(50) | YES | | NULL | |
- | city | varchar(50) | YES | | NULL | |
- | state | varchar(2) | YES | | NULL | |
- | zip | varchar(10) | YES | | NULL | |
- | phone | varchar(20) | YES | | NULL | |
- | interests | varchar(255) | YES | | NULL | |
- +-------------+------------------+------+-----+---------+----------------+
- 12 rows in set (0.00 sec)
-
- mysql>
- DESC == DESCRIBE == EXPLAIN == show columns form president == SHOW fields fom president
-
-
- mysql> desc member member_id;
- +-----------+------------------+------+-----+---------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +-----------+------------------+------+-----+---------+----------------+
- | member_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
- +-----------+------------------+------+-----+---------+----------------+
- 1 row in set (0.07 sec)
-
- mysql>
-
- mysql> create table student (
- name varchar(20) not null,
- sex enum('F','M') not null,
- student_id int unsigned not null auto_increment,
- primary key (student_id)
- ) engine = innodb;
-
-
- mysql> create table score
- (
- student_id int unsigned not null,
- event_id int unsigned not null,
- score int not null,
- primary key (event_id, student_id),
- index (student_id),
- foreign key (event_id) references grade_event (event_id),
- foreign key (student_id) references student (student_id)
- ) engine = innodb;
-
- mysql> create table absence
- (
- student_id int unsigned not null,
- date date not null,
- primary key (student_id, date),
- foreign key (student_id) references student (student_id)
- ) engine = innodb;
-
- mysql> insert into student values('Kyle', 'M', NULL);
- Query OK, 1 row affected (0.04 sec)
-
- mysql> insert into grade_event values('2008-09-03', 'Q', NULL);
- Query OK, 1 row affected (0.01 sec)
-
- mysql>
- // values ，
- INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
- [INTO] tbl_name [(col_name,...)]
- {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
- [ ON DUPLICATE KEY UPDATE
- col_name=expr
- [, col_name=expr] ... ]
-
- INSERT INTO tb1_name VALUES(...), (...),...;
- //
-
- mysql> insert into student values('Avery', 'F', NULL),('Nathan', 'M', NULL);
- Query OK, 2 rows affected (0.01 sec)
- Records: 2 Duplicates: 0 Warnings: 0
-
- mysql> insert into member (last_name,first_name) values('Stein','Waldo');
- mysql> insert into student (name,sex) values('Abby','F'),('Joseph', 'M');
- Query OK, 2 rows affected (0.06 sec)
- Records: 2 Duplicates: 0 Warnings: 0
-
- mysql>
-
- col_name = value set
- mysql> insert into member set last_name='Stein',first_name='Waldo';
- Query OK, 1 row affected (0.04 sec)
-
- mysql>
-
- mysql> select 2+2, 'Hello,World', version();
- +-----+-------------+----------------------+
- | 2+2 | Hello,World | version() |
- +-----+-------------+----------------------+
- | 4 | Hello,World | 5.5.20-ndb-7.2.5-log |
- +-----+-------------+----------------------+
- 1 row in set (0.00 sec)
-
- mysql>
-
- mysql> select 2+2, 'Hello,World', version() from null;
-
- order by
- mysql> select name,student_id from student order by name;
- +--------+------------+
- | name | student_id |
- +--------+------------+
- | Abby | 4 |
- | Avery | 2 |
- | Joseph | 5 |
- | Kyle | 1 |
- | Nathan | 3 |
- +--------+------------+
- 5 rows in set (0.08 sec)
-
- mysql> select name,student_id from student order by name desc;
- +--------+------------+
- | name | student_id |
- +--------+------------+
- | Nathan | 3 |
- | Kyle | 1 |
- | Joseph | 5 |
- | Avery | 2 |
- | Abby | 4 |
- +--------+------------+
- 5 rows in set (0.06 sec)
-
- mysql> select name,student_id from student order by name limit 3;
- //
- +--------+------------+
- | name | student_id |
- +--------+------------+
- | Abby | 4 |
- | Avery | 2 |
- | Joseph | 5 |
- +--------+------------+
- 3 rows in set (0.00 sec)
-
- mysql>
-
-
-
- mysql> select birth from president where last_name = 'Eisenhower';
- +------------+
- | birth |
- +------------+
- | 1890-10-14 |
- +------------+
- 1 row in set (0.00 sec)
-
-
- mysql> select * from score where score > 95;
- +------------+----------+-------+
- | student_id | event_id | score |
- +------------+----------+-------+
- | 5 | 3 | 97 |
- | 18 | 3 | 96 |
- | 1 | 6 | 100 |
- | 5 | 6 | 97 |
- | 11 | 6 | 98 |
- | 16 | 6 | 98 |
- +------------+----------+-------+
- 6 rows in set (0.05 sec)
-
- mysql> select concat(first_name,' ',last_name) as name,
- -> concat(city,' ',state) as birthplace
- -> from president;
- +-----------------------+------------------------+
- | name | birthplace |
- +-----------------------+------------------------+
- | George Washington | Wakefield VA |
- | John Adams | Braintree MA |
- | Thomas Jefferson | Albemarle County VA |
- | James Madison | Port Conway VA |
- | James Monroe | Westmoreland County VA |
- | John Quincy Adams | Braintree MA |
- | Andrew Jackson | Waxhaw settlement SC |
- | Martin Van Buren | Kinderhook NY |
- | William H. Harrison | Berkeley VA |
- | John Tyler | Greenway VA |
- | James K. Polk | Pineville NC |
- | Zachary Taylor | Orange County VA |
- | Millard Fillmore | Locke NY |
- | Franklin Pierce | Hillsboro NH |
- | James Buchanan | Mercersburg PA |
- | Abraham Lincoln | Hodgenville KY |
- | Andrew Johnson | Raleigh NC |
- | Ulysses S. Grant | Point Pleasant OH |
- | Rutherford B. Hayes | Delaware OH |
- | James A. Garfield | Orange OH |
- | Chester A. Arthur | Fairfield VT |
- | Grover Cleveland | Caldwell NJ |
- | Benjamin Harrison | North Bend OH |
- | William McKinley | Niles OH |
- | Theodore Roosevelt | New York NY |
- | William H. Taft | Cincinnati OH |
- | Woodrow Wilson | Staunton VA |
- | Warren G. Harding | Blooming Grove OH |
- | Calvin Coolidge | Plymouth Notch VT |
- | Herbert C. Hoover | West Branch IA |
- | Franklin D. Roosevelt | Hyde Park NY |
- | Harry S Truman | Lamar MO |
- | Dwight D. Eisenhower | Denison TX |
- | John F. Kennedy | Brookline MA |
- | Lyndon B. Johnson | Stonewall TX |
- | Richard M. Nixon | Yorba Linda CA |
- | Gerald R. Ford | Omaha NE |
- | James E. Carter | Plains GA |
- | Ronald W. Reagan | Tampico IL |
- | George H.W. Bush | Milton MA |
- | William J. Clinton | Hope AR |
- | George W. Bush | New Haven CT |
- +-----------------------+------------------------+
- 42 rows in set (0.25 sec)
-
- mysql>
-
- CONCAT()
- DATE_ADD()、DATE_SUB()
-
- MySQL
- ： @ =xxx
-
- mysql> select distinct state from president order by state;
- //distinct
- +-------+
- | state |
- +-------+
- | AR |
- | CA |
- | CT |
- | GA |
- | IA |
- | IL |
- | KY |
- | MA |
- | MO |
- | NC |
- | NE |
- | NH |
- | NJ |
- | NY |
- | OH |
- | PA |
- | SC |
- | TX |
- | VA |
- | VT |
- +-------+
- 20 rows in set (0.01 sec)
-
- mysql> select count(*) from president;
- +----------+
- | count(*) |
- +----------+
- | 42 |
- +----------+
- 1 row in set (0.15 sec)
-
- mysql> select sex, count(*) from student group by sex;
- +-----+----------+
- | sex | count(*) |
- +-----+----------+
- | F | 15 |
- | M | 16 |
- +-----+----------+
- 2 rows in set (0.00 sec)
-
- mysql> select state,count(*) from president group by state;
- +-------+----------+
- | state | count(*) |
- +-------+----------+
- | AR | 1 |
- | CA | 1 |
- | CT | 1 |
- | GA | 1 |
- | IA | 1 |
- | IL | 1 |
- | KY | 1 |
- | MA | 4 |
- | MO | 1 |
- | NC | 2 |
- | NE | 1 |
- | NH | 1 |
- | NJ | 1 |
- | NY | 4 |
- | OH | 7 |
- | PA | 1 |
- | SC | 1 |
- | TX | 2 |
- | VA | 8 |
- | VT | 2 |
- +-------+----------+
- 20 rows in set (0.00 sec)
-
- mysql> select state,count(*) from president group by state order by count(*) desc limit 5;
- +-------+----------+
- | state | count(*) |
- +-------+----------+
- | VA | 8 |
- | OH | 7 |
- | MA | 4 |
- | NY | 4 |
- | VT | 2 |
- +-------+----------+
- 5 rows in set (0.00 sec)
-
- mysql> select state,count(*) as count from president group by state having count > 1 order by count(*) desc limit 5;
- +-------+-------+
- | state | count |
- +-------+-------+
- | VA | 8 |
- | OH | 7 |
- | MA | 4 |
- | NY | 4 |
- | VT | 2 |
- +-------+-------+
- 5 rows in set (0.00 sec)
-
- mysql> select event_id, min(score) as minimum, max(score) as maximum, max(score)-min(score)+1 as span, sum(score) as total ,avg(score) as count from score group by event_id;
- +----------+---------+---------+------+-------+---------+
- | event_id | minimum | maximum | span | total | count |
- +----------+---------+---------+------+-------+---------+
- | 1 | 9 | 20 | 12 | 439 | 15.1379 |
- | 2 | 8 | 19 | 12 | 425 | 14.1667 |
- | 3 | 60 | 97 | 38 | 2425 | 78.2258 |
- | 4 | 7 | 20 | 14 | 379 | 14.0370 |
- | 5 | 8 | 20 | 13 | 383 | 14.1852 |
- | 6 | 62 | 100 | 39 | 2325 | 80.1724 |
- +----------+---------+---------+------+-------+---------+
- 6 rows in set (0.00 sec)
-
- mysql> select student_id,date, score, category from grade_event inner join score on grade_event.event_id=score.event_id where date='2008-09-23' limit 5;
- +------------+------------+-------+----------+
- | student_id | date | score | category |
- +------------+------------+-------+----------+
- | 1 | 2008-09-23 | 15 | Q |
- | 2 | 2008-09-23 | 12 | Q |
- | 3 | 2008-09-23 | 11 | Q |
- | 5 | 2008-09-23 | 13 | Q |
- | 6 | 2008-09-23 | 18 | Q |
- +------------+------------+-------+----------+
- 5 rows in set (0.00 sec)
-
- mysql>
-
- =======================
-
- =======================
- delete from table_name which rows to delete;
-
- mysql> delete from president where state='OH';
- Query OK, 7 rows affected (0.33 sec)
-
- mysql>
-
- mysql> update mysql.user set password='123' where user='root';
- Query OK, 2 rows affected (0.21 sec)
- Rows matched: 2 Changed: 2 Warnings: 0
-
- mysql> flush privileges;
- Query OK, 0 rows affected (0.53 sec)
-
- mysql>
-
- mysql> show columns from president;
- +------------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +------------+-------------+------+-----+---------+-------+
- | last_name | varchar(15) | NO | | NULL | |
- | first_name | varchar(15) | NO | | NULL | |
- | suffix | varchar(5) | YES | | NULL | |
- | city | varchar(20) | NO | | NULL | |
- | state | varchar(2) | NO | | NULL | |
- | birth | date | NO | | NULL | |
- | death | date | YES | | NULL | |
- +------------+-------------+------+-----+---------+-------+
- 7 rows in set (0.00 sec)
-
- mysql> desc president;
- +------------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +------------+-------------+------+-----+---------+-------+
- | last_name | varchar(15) | NO | | NULL | |
- | first_name | varchar(15) | NO | | NULL | |
- | suffix | varchar(5) | YES | | NULL | |
- | city | varchar(20) | NO | | NULL | |
- | state | varchar(2) | NO | | NULL | |
- | birth | date | NO | | NULL | |
- | death | date | YES | | NULL | |
- +------------+-------------+------+-----+---------+-------+
- 7 rows in set (0.00 sec)
-
- mysql>