WordPress.com



?????? SQL ??????????????? SQL ????????? by natsu?????????????????????????????????????????????????????????????????????????????????????????????? ????????????????????????? ?????????????? PAGE ??????? COPY and PASTE ?????? ???????????????? tab ????????? ???????????????????????????????????????? SQL ??????????? case sensitive (??????? ???????????????????) ???????????????????????????? ; (semi-colon)??????? Commandline???????????????????? cmd ?????????$ mysql -u root -p?????????? password ????????????????????mysql >?(?????????????????????????)????????????????mysql > quitmysql > show databases; ????? databases ??????????????????????mysql > use?<???? database> ?????????????? database ?????mysql > SELECT database(); ?? database ??????????????????mysql > show tables; ????????????????????????????????? database ??? use????? DATABASEmysql > create database? <????database>; ???? create database world;????? tablemysql > create table <????table> (<??????????> <??????????>, ... ); ???? create table human (name VARCHAR(20), birth DATE, sex CHAR(1));?????????? ????VARCHAR(n) - ?????????? string ??????? linked list ????????????????????????????????????????CHAR(n) - ?????????? string ??????? array ?????????????????????????????????????INT - ?????????DATE - ?????????????????? SQL ????????????? ???????????? YYYY-MM-DD ????????????????????????????????mysql > describe <????table>;?????????????????? table1.?????????? load data ?????????????????????? ??? default ??????????????????? tab ????????????????????????????????????? NULL ??????? \N ??? mysql > load data local infile ‘natsu.txt’ into table pet;2.INSERT ????????????? ?????????????????????? ????????????????????? ???? mysql > INSERT INTO pet?VALUES (‘natsusencho’, ‘1992-03-25’, ‘M’);3. *?? SQL script ??????????????????? sql ???????????????? source ???????? ???? ???????????????????????????????????????????????????????????????????? ?????????????????????????? run ??????????????? ---- file natsu.sql ---- CREATE TABLE IF NOT EXISTS human (? ? ?? name ? VARCHAR(20),? ? ?? birth DATE,? sex CHAR(1) ); INSERT INTO human VALUES??? ?? ( 'NatsuSencho', ? '1992-03-25', 'M'),?? ?? ( 'Slime', ? '1999-03-03', NULL ),? ( ‘HeyFemale’ , ‘1993-12-25’ , ‘F’); ----- file natsu.sql ----- ??????????????????????????????????? mysql > source natsu.sql; ???????????? world ??????????????? 3 ??? create table IF NOT EXISTS human ????? IF NOT EXISTS ??????????????? table ?????????????? table ??? ???????????????????????????????????????? ????? table ????????? mysql > DELETE FROM <????table>; ??????????????????????????????????????????????? mysql > UPDATE <????table>? SET <??????????> = <??????????> WHERE <?????????????>; ???? UPDATE human SET name = ‘HeyGirl’ WHERE name = ‘HeyFemale’;??????????????????????????????? SELECT <??????????????> FROM ? <????table> WHERE <?????????????>??????????????????????????????? human ????????????? SELECT name FROM ? human WHERE sex = ‘M’;?????????????????????????????? human [* ??????????] SELECT * FROM ? human;???????????????????????????????????????????????????????????? ????????????? AND ?????OR ???? < ??????????> ??????? <= ??????????????????? >= ?????????????????? <> ?????????? UNION ????? 2 ?????????????????????????????UNION ALL ????? 2 ??????????????????????????????? INTERSECT ???????????????DISTINCT ???????????????????????????? SELECT DISTINCT sex FROM ? human;ORDER BY ???????????????? ??????????????????????????????????????? (descending order)???? SELECT * FROM ? human ORDER BY name;?????????????????????? (descending order)???? SELECT * FROM ? human ORDER BY name DESC;???????????????????????????????????? SELECT * FROM ? human ORDER BY name , sex DESC ;????????????????????? ascending ????????????????????? descending ????????????????????????????? ?????? DATE ???? string ?????????????????????? YYYY-MM-DD ?????????? DATE ??????????????????????????????? ASCIICURDATE() ?????? function ?????????????????????????????? DATE (YYYY-MM-DD)YEAR(<??????????date>) ???????????????????????????????? (YYYY)MONTH(<??????????date>) ??????????????????????????????????? (MM)DAY(<??????????date>)? ????????????????????????????????? (DD)RIGHT(<??????????string>, <???????????>) ??????????????????????????????????????????????? string ????? ????????????????????LEFT(<??????????string>, <???????????>) ??????????????????????????????????????????????? string ????? ?????????????????????????????ex1. ??????????????????????? YEAR( CURDATE() )ex2. ????????????????????????????? RIGHT( CURDATE(),5 ) [5 ???????????????????????????? YYYY-MM-DD ??????? ?MM-DD ??]???????????? NULL ?????????? ????????? xxx IS NOT NULL ???????????????????????????????????? SELECT * FROM ? human WHERE sex IS NOT NULL;???????????????????????????????? ??????????? select ????????????????????????????????????? ?????????????? AS ???? ???? SELECT name AS ‘NAME-SURNAME’ FROM ? human;COUNT ??????????? + GROUP BY ??????????? COUNT ????????????????????????????? ??????????? GROUP BY ??????????????????????????????????????????????? SELECT <?????> COUNT(*) FROM <????table> WHERE <????????> GROUP BY <??????????????????>??????????????????????????????? SELECT sex , COUNT(*) FROM ? human GROUP BY sex;SET ?????????????? SET @<??????????> = <???> ????? SET @A1 = ‘Natsu Sencho’; SET @A2 = ‘1999-09-09’;???????????? JOIN ??? JOIN ??????????????????????????????????????????????????????????????? ??? JOIN ?? 2 ?????? 1. INNER JOIN 2. OUTER JOIN? |--- LEFT JOIN |--- RIGHT JOININNER JOIN ?????? JOIN ????????????? NULL ???????????????????????????????????????????? 2 ??????? Ltable ??? ?Rtable ???? JOIN ?????????????????????????? id -- JOIN ?????? ON SELECT * FROM Ltable INNER JOIN Rtable ON Ltable.id = Rtable.id; -- ???? JOIN ?????? USING SELECT * FROM Ltable INNER JOIN Rtable USING (id); ?????????????????????????? Column ??????????????? NATURAL JOIN ??? ?????????????????????? id ???????????????????????????????????? ?????? Natural Join ????????????? SELECT * FROM Ltable NATURAL JOIN Rtable;OUTER JOINLEFT JOIN ?????? JOIN ??????????????????????????? ???????????????????????????????????????????? SELECT * FROM Ltable LEFT JOIN Rtable ON Ltable.id = Rtable.id;RIGHT JOIN ?????? JOIN ?????????????????????????? ??????????????????????????????????????????? SELECT * FROM Ltable RIGHT JOIN Rtable ON Ltable.id = Rtable.id;????????????? JOIN ???????????????????? Cartesian Product ??????????????????????????????????????????????????????????????????????????????????? ?????????????????? ???????????????????????????????????CREATE?????database, tableINSERT?????????UPDATE????????????SELECT???????????????????FROM????????WHERE???????????????COUNT(*)????????????????????GROUP BY??????????????ORDER BY???????????????????JOIN???????????DISTINCT?????????????????????AS?????????????????????????SET???????????CURDATE()??????????????YEAR()??MONTH()?????DAY()???RIGHT()??????????????LEFT()???????????????*?????????????? : ?????????????????????? Databases ?????????????? 2012 ???????????????? ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download