1. Getting started with MySQL and SQL

This article is some of the scattered notes in the first chapter of MySQL technology insider. 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: negative values are not allowed. Not NULL: data must be filled in. auto_increment cannot be empty. special attributes of MySQL indicate that sequence numbers are stored in the data column. How auto_increment works is, if the value of member_id is not filled out, a number is automatically generated and assigned to the primary key of this series: Create an index for the data column of member_id to speed up searching, at the same time, this value must be unique, and any primary key must not be null. If not null is ignored in the member_id definition, MySQL will be automatically added to MySQL> DESC member; + ------------- + bytes + ------ + ----- + --------- + ---------------- + | 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. All of these are mysql> DESC member member_id; + ----------- + bytes + ------ + ----- + --------- + ---------------- + | FIELD | type | null | key | default | extra | + ----------- + ---------------- + ------ + ----- + --------- + ---------------- + | member_id | int (10) unsigned | no | pri | null | auto_increment | + ----------- + ------------------ + ------ + ----- + --------- + ------------------ + 1 row in SET (0.07 Sec) 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 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 ('2017-09-03 ', 'Q', null); query OK, 1 row affected (2008 Sec) mysql> // the corresponding values must be prepared for all data columns of the data table in parentheses after the keyword values, these values must also be in the same order as the data columns stored in the data table. 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 (...), (...),...; // you can insert multiple statements at a time. 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 the set clause assigns a value to the data column mysql> insert into member set last_name = 'stein ', first_name = 'waldo '; query OK, 1 row affected (0.04 Sec) 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> select 2 + 2, 'Hello, world', version () from NULL; order by sorting 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; // limit the number of output rows + -------- + ------------ + | Name | student_id | + -------- + ------------ + | Abby | 4 | Avery | 2 | Joseph | 5 | + -------- + ------------ + 3 rows in SET (0.00 Sec) mysql> Information Retrieval 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; + California + | Name | birthplace | + California + ---------------------- + | 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 Taylor | 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. garfield | orange Oh | Chester. arthur | Phil 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 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 | ly1_ 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 using date_add (), date_sub () MySQL custom variable assignment: @ variable name = xxxmysql> select distinct state from President Order by State; // distinct deduplication + ------- + | 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 = '2017-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 and update the current row ========== ================== Delete from table_name which rows to delete; mysql> Delete from President where State = 'oh'; query OK, 7 rows affected (0.33 Sec) mysql> Update MySQL. user SET Password = '000000' where user = 'root'; query OK, 2 rows affected (123 Sec) rows matched: 2 changed: 2 Warnings: 0 mysql> flush privileges; query OK, 0 rows affected (0.53 Sec) 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>