mysql

• SELECT    : SELECT column_a
• FROM          FROM table_a
• WHERE      : WHERE column_a > '3'
• SHOW        : SHOW TABLES
• AS              :别名：

• SELECT dog_guid, created_at AS "time stamp"
  FROM complete_tests AS tests

• DISTINCT   ：remove duplicate rows

• SELECT DISTINCT breed
  FROM dogs;

• ORDER BY    : sort the output of your query

• 

• SELECT DISTINCT breed
  FROM dogs 
  ORDER BY breed

上面例子是：alphabetical order

降序：

SELECT DISTINCT user_guid, (median_ITI_minutes * 60) AS median_ITI_sec
FROM dogs 
ORDER BY median_ITI_sec DESC
LIMIT 5

 多规则排序：ASC

 

SELECT DISTINCT user_guid, state, membership_type
FROM users
WHERE country="US"
ORDER BY state ASC, membership_type ASC

SELECT DISTINCT user_guid, state, membership_type
FROM users
WHERE country="US" AND state IS NOT NULL and membership_type IS NOT NULL
ORDER BY state ASC, membership_type ASC

•   Export your query results to a text file

 

 

breed_list = %sql SELECT DISTINCT breed FROM dogs ORDER BY breed;

生成csv文件：

breed_list.csv('breed_list.csv') 

替换：

%%sql
SELECT DISTINCT breed,
REPLACE(breed,'-','') AS breed_fixed
FROM dogs
ORDER BY breed_fixed

或者：

 

SELECT DISTINCT breed, TRIM(LEADING '-' FROM breed) AS breed_fixed
FROM dogs
ORDER BY breed_fixed

 加载数据：

%load_ext sql
%sql mysql://studentuser:studentpw@mysqlserver/dognitiondb
%sql USE dognitiondb