[카테고리:] mysql

difference on “ON” and “WHERE” when OUTER JOIN

  1. WHERE  -> SELECT only the rows satisfy the WHERE condition
  2. ON -> JOIN only the rows satisfy the ON condition

-> Therefore when you OUTER JOIN,

SELECT * FROM A RIGHT JOIN B ON  A.Product_index = B.index_ WHERE B.otherIndex = 1227616;
# results only rows with otherIndex = 1227616

#same with
SELECT * FROM A INNER JOIN B ON A.Product_index = B.index_ AND B.otherIndex = 1227616;

SELECT * FROM A RIGHT JOIN B ON A.Product_index = B.index_ AND B.otherIndex = 1227616 WHERE B.otherIndex = 1227616;

-----------------------------------------------------------------
#different with
SELECT * FROM A RIGHT JOIN B ON A.Product_index = B.index_ AND B.otherIndex = 1227616;
# results all rows of B because it's RIGHT JOIN
# join only the rows with otherIndex = 1227616

run mysql(homebrew, mac)

We’ve installed your MySQL database without a root password. To secure it run:

    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:

    mysql -uroot

To have launchd start mysql now and restart at login:

  brew services start mysql

Or, if you don’t want/need a background service you can just run:

  mysql.server start

key

If Key is empty, the column either is not indexed or is indexed only as a secondary column in a multiple-column, non-unique index.
If Key is PRI, the column is a PRIMARY KEY or is one of the columns in a multiple-column PRIMARY KEY.
If Key is UNI, the column is the first column of a unique-valued index that cannot contain NULL values.
If Key is MUL, multiple occurrences of a given value are allowed within the column. The column is the first column of a non-unique index or a unique-valued index that can contain NULL values.
Key
empty
PRI
UNI NULL X unique-valued index
MUL NULL multiple occurrences of a given value