1. DISTINCT用法
#SELECT DISTINCT store_name FROM db.MY WHERE Sales>1000;
2.AND OR 用法
#SELECT store_name,Sales FROM db.MY WHERE Sales>1000 OR (Sales<500 AND Sales>275);
3.IN用法
#SELECT * FROM db.MY WHERE store_name IN ('Los Angeles','wangzai');
4.Between and 用法
#SELECT * FROM db.MY WHERE date BETWEEN 'Jan-07-1999' AND 'Jan-08-1999';
5.LIKE用法
#SELECT * FROM db.MY WHERE store_name LIKE '%ge%';
6.ORDER BY用法
#SELECT * FROM db.MY ORDER BY Sales; #SELECT store_name,Sales,date from db.MY ORDER BY 2 DESC;
7.SUM用法
#SELECT SUM(Sales) FROM db.MY;
8.COUNT用法
#SELECT COUNT(store_name) FROM db.MY WHERE store_name is NOT NULL; #SELECT COUNT(DISTINCT store_name) FROM db.MY WHERE store_name is NOT NULL;
9.GROUP BY 用法
#SELECT store_name,SUM(Sales) FROM db.MY GROUP BY store_name;
10.HAVING 用法
#SELECT store_name,SUM(Sales) FROM db.MY GROUP BY store_name HAVING SUM(Sales)>1500;
11.ALIAS 别名用法
#SELECT A1.store_name Store,SUM(A1.Sales) "Total Sales" FROM db.MY A1 GROUP BY Store;
11.表格链接 用法
#SELECT A1.region_name REGION,SUM(A2.Sales) SALES FROM db.Geography A1,db.MY A2
#WHERE A1.store_name1=A2.store_name GROUP BY A1.region_name;
12.CONCAT 连接字符串用法
#SELECT CONCAT(region_name,store_name1) FROM db.Geography WHERE store_name1='Boston';
12.SUBSTR 抓取部分字符 用法
#SELECT SUBSTR(store_name1,3) FROM Geography WHERE store_name1='Los Angeles'; #SELECT SUBSTR(store_name1,2,4) FROM Geography WHERE store_name1='San Diego';
13.创建表
#CREATE TABLE Customer (Sid INTEGER UNIQUE, First_Name char(50) NOT NULL, Last_Name char(50));
14.给表中的字段添加UNIQUE 唯一值
#ALTER TABLE Customer add UNIQUE key(`Sid`);
15.创建表的同时创建主键
#CREATE TABLE Customer1 (Sid integer, Last_Name VARCHAR(30), First_Name VARCHAR(30), PRIMARY KEY (Sid));
16.给表中字段增加主键
#ALTER TABLE Customer ADD PRIMARY KEY (Sid);
17.创建视图
#CREATE VIEW V_REGION_SALES
AS SELECT A1.region_name REGION,SUM(A2.Sales) SALES FROM db.Geography A1,db.MY A2
WHERE A1.store_name1=A2.store_name GROUP BY A1.region_name;
20.查询视图
#SELECT * FROM V_REGION_SALEs;
21.创建索引
#CREATE INDEX IDX_Customer_Location on customer(City,Country);
22.给表中添加字段
#ALTER TABLE customer add Gender char(1);
23.修改表中字段的名字
#ALTER TABLE customer CHANGE Address Addr char(50);
24.修改表中字段的数据类型
#ALTER TABLE customer MODIFY Addr char(30);
25.删除表中某个字段
#ALTER TABLE customer DROP Gender;
26.mysql查询最后一条语句
#select id,name from uc_user order by id desc limit 0,1;
27. 其中记录 NULL 表示所有人的登录次数。我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：
#SELECT coalesce(name, 'Total_sum'), SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
28.修改表名
#ALTER TABLE wangzai RENAME to doubi;
29.把本地数据插入到mysql表中
LOAD DATA LOCAL INFILE “/root/1.txt” INTO TABLE test;