春招学习笔记-------SQL语法及练习(更新ing)

1.基础语法

DROP CREATE ALTER TRUNCATE 都要有TABLE
(1)注释

##abc
--abc

(2)建库和使用

CREATE DATABASE mydata;
USE mydata;

(3)建表
CREATE TABLE 表名(列名 数据类型 ，…)

CREATE TABLE test(
#int类型 不为空 自增
id INT NOT NULL AUTO_INCREMENT,
#int类型 不为空 默认值为1
perm INT NOT NULL DEFAULT 1,
#可变长字符串类型 最长为45 可为空
name VARCHAR(45) NULL,
#日期类 可为空
birth DATE NULL,
#设置主键为id
PRIMARY KEY (id)
)

(4)修改表
①添加属性(列)
ALTER TABLE 表名 ADD 字段名 数据类型 [限制];

ALTER TABLE test
ADD index CHAR(20);

②删除属性(列)
ALTER TABLE 表名 DROP COLUMN 字段名;

ALTER TABLE test
DROP COLUMN index;

③删除表

DROP TABLE 表名；

(5)插入
①普通插入

INSERT INTO test(perm, name)
VALUES(1, 'xhang');
+----+------+-------+-------+
| id | perm | name  | birth |
+----+------+-------+-------+
|  1 |    1 | xhang | NULL  |
+----+------+-------+-------+
1 row in set (0.00 sec)

②插入检索出来的数据

INSERT INTO test(perm, name)
SELECT perm, name
FROM test WHERE id = 1;
+----+------+-------+-------+
| id | perm | name  | birth |
+----+------+-------+-------+
|  1 |    1 | xhang | NULL  |
|  2 |    1 | xhang | NULL  |
+----+------+-------+-------+
2 rows in set (0.00 sec)

③将一个表的内容插入到一个新表

CREATE TABLE new AS
SELECT * FROM test;
select* from new;
+----+------+-------+-------+
| id | perm | name  | birth |
+----+------+-------+-------+
|  1 |    1 | xhang | NULL  |
|  2 |    1 | xhang | NULL  |
+----+------+-------+-------+
2 rows in set (0.00 sec)

(6)更新
UPDATE 表名
SET 列名 = 值
WHERE 列名 = 值

UPDATE mytable
SET col = val
WHERE id = 1;

(7)删除

DELETE FROM test
WHERE id = 1;

TRUNCATE TABLE 可以清空表，也就是删除所有行。

TRUNCATE TABLE test;

使用更新和删除操作时一定要用 WHERE 子句，不然会把整张表的数据都破坏。
(8)查询
①DISTINCT
相同值只会出现一次。它作用于所有列，也就是说所有列的值都相同才算相同。

select* from new;
+----+------+-------+-------+
| id | perm | name  | birth |
+----+------+-------+-------+
|  1 |    1 | xhang | NULL  |
|  2 |    1 | ff    | NULL  |
+----+------+-------+-------+
select distinct name from new;
+-------+
| name  |
+-------+
| xhang |
| ff    |
+-------+
select* from test;
+----+------+-------+-------+
| id | perm | name  | birth |
+----+------+-------+-------+
|  1 |    1 | xhang | NULL  |
|  2 |    1 | xhang | NULL  |
+----+------+-------+-------+
select distinct name from test;
+-------+
| name  |
+-------+
| xhang |
+-------+

②LIMIT
限制返回的行数。可以有两个参数。
第一个参数为起始行，从 0 开始；
第二个参数为返回的总行数。

返回前 3行：

SELECT *
FROM test
LIMIT 3;

SELECT *
FROM test
LIMIT 0, 3;

返回第 2 ~ 3 行：

SELECT *
FROM test
LIMIT 1, 2;

(9)排序
ASC ：升序（默认）
DESC ：降序
可以按多个列进行排序，并且为每个列指定不同的排序方式：

SELECT *
FROM new
ORDER BY id DESC, name ASC;

(10)过滤

SELECT *
FROM test
WHERE birth IS NULL;

操作符					说明
=						等于
<						小于
>						大于
<> !=					不等于
<= !>					小于等于
>= !<					大于等于
BETWEEN AND	            在两个值之间
IS NULL	                为 NULL 值
IS NOT NULL             不为空

AND 和 OR 用于连接多个过滤条件。AND的优先级高于OR。
(11)通配符
通配符也是用在过滤语句中，但它只能用于文本字段。

% 匹配 >=0 个任意字符；

_ 匹配 ==1 个任意字符；

使用 LIKE 来进行通配符匹配。

SELECT *
FROM test
WHERE name LIKE 'f%'; -- 以f开头的任意文本

(12)计算字段
计算字段通常需要使用 AS 来取别名，否则输出的时候字段名为计算表达式。

SELECT id * perm AS free
FROM new;

SELECT id * perm  FROM new;
+-----------+
| id * perm |
+-----------+
|         1 |
|         2 |
+-----------+
SELECT id * perm AS free FROM new;
+------+
| free |
+------+
|    1 |
|    2 |
+------+

(13)函数

函 数			   说 明
AVG()			返回某列的平均值
COUNT()			返回某列的行数
MAX()			返回某列的最大值
MIN()			返回某列的最小值
SUM()			返回某列值之和

AVG() 会忽略 NULL 行。
使用 DISTINCT 可以汇总不同的值。

(14)分组
把具有相同的数据值的行放在同一组中。
可以对同一分组数据使用汇总函数进行处理，例如求分组数据的平均值等。

SELECT id, COUNT(*) AS num
FROM test
GROUP BY id;

GROUP BY 自动按分组字段进行排序，ORDER BY 也可以按汇总字段来进行排序。

SELECT id, COUNT(*) AS num
FROM test
GROUP BY id;
ORDER BY num;

WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。

SELECT id, COUNT(*) AS num
FROM test
WHERE id > 2
GROUP BY id
HAVING num >= 2;

where与having都是用来筛选的
having是用来筛选组，where是用来筛选记录。
where搜索条件在分组操作之前应用，having搜索条件在进行分组操作之后应用。
当一个查询包含了where条件和聚合函数，先执行条件过滤，再进行聚合函数，如：

SELECT SUM(score) FROM sc WHERE score > 60

先过滤出score>60的记录，再进行SUM求和
having在聚合之后进行过滤，having在分组的时候会用，对分组结果进行过滤，通常分组里面包含聚合函数，如：

SELECT sid,AVG(score) FROM sc GROUP BY sid HAVING AVG(score) >60;

如果having中包含多个条件，这些条件将通过AND、OR或NOT连接在一起，如：

SELECT sid,cid,AVG(score) FROM sc
 GROUP BY sid 
 HAVING AVG(score) >60 AND cid != '001' ; 

(15)子查询
子查询中只能返回一个字段的数据。

可以将子查询的结果作为 WHRER 语句的过滤条件：

SELECT *
FROM test1
WHERE id IN (SELECT sid
               FROM test2);