sql学习之1-create、select 

1、mysql创建表
CREATE TABLE test_tab (
id INT NOT NULL,
name VARCHAR(10),
age INT,
PRIMARY KEY (id)
);

2、sqlserver/oracle创建表
CREATE TABLE test_tab (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(10),
age INT
);

 3、除去重复数据DISTINCT

SELECT DISTINCT 列名称 FROM 表名称
如下数据：
CompanyOrderNumber
IBM 3532
W3School 2356
Apple 4698
W3School 6953

 

SELECT Company FROM Orders

结果如下：

Company
IBM
W3School
Apple
W3School

 

SELECT DISTINCT Company FROM Orders

结果如下：

Company
IBM
W3School
Apple

4、对查询结果集的列进行重命名AS，连接多个查询UNION

SELECT 
    'bptest',
     COUNT(*)
FROM
    bptest
UNION 
SELECT 
    'test_tab',
     COUNT(*)
FROM
    test_tab;

查询结果：

+----------+----------+
| bptest | COUNT(*) |
+----------+----------+
| bptest | 2 |
| test_tab | 10000000 |
+----------+----------+

修改后：

SELECT 
    'bptest' AS table_name,
     COUNT(*) AS table_row
FROM
    bptest
UNION 
SELECT 
    'test_tab' AS table_name,
     COUNT(*) AS table_row
FROM
    test_tab;

查询结果：

+------------+-----------+
| table_name | table_row |
+------------+-----------+
| bptest | 2 |
| test_tab | 10000000 |
+------------+-----------+

posted @ 2018-12-03 14:59  正在努力的BOY  阅读(1193)  评论(0)  编辑  收藏  举报