本文是筆者閱讀《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>