SQL关键字是不区分大小写(SELECT,FROM,WHERE等),但往往写在全部大写。通常区分大小写的表和列名称是Linux上的默认值,不区分大小写在Windows上是默认值,
1. SQL三种注释方法
##注释
SELECT *
FROM mytable; -- 注释
/* 注释1
注释2 */
2. 数据库的创建与使用
CREATE DATABASE test;
USE test;
3. 创建表
创建表内容的具体格式:“字段名” 数据类型 属性(是否为空) 索引(自增等)
CREATE TABLE mytable(
# int 类型,不为空,自增
id INT NOT NULL AUTO_INCREMENT,
# int 类型,不可为空,默认值为 1,不为空
col1 INT NOT NULL DEFAULT 1,
# 变长字符串类型,最长为 45 个字符,可以为空
col2 VARCHAR(45) NULL,
# 日期类型,可为空
col3 DATE NULL,
# 设置主键为 id
PRIMARY KEY (`id`));
4. 修改表
4.1、增加列
ALTER TABLE mytable.
ADD col CHAR(20);
4.2、删除列
ALTER TABLE mytable
DROP COLUMN col;
4.3、删除表
DROP TABLE mytable;
5. 插入
5.1、普通插入
在表格中插入新值
语法:
INSERT INTO 表名称 VALUES (值1, 值2,....)
例子:
在整个表中增加内容
INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')
指定所要插入数据的列
语法:
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
例子:
在mytable表中的第一列和第二列中分别增加val1, val2
INSERT INTO mytable(col1,col2) VALUES (val1, val2);
5.2、插入检索出来的数据
语法:将表2中*内容插入到表1中对应的列。
INSERT INTO table_name1(列1,列2,...) SELECT * FROM table_name2
例子:
INSERT INTO mytable1(col1, col2) SELECT col1, col2 FROM mytable2;
5.3、将一个表的内容插入到一个新表
CREATE TABLE newtable AS SELECT * FROM mytable;
6. 更新
Update 语句用于修改表中的数据。
最初的表格示例如下:
6.1、更新某一行中的一列
语法:
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
例子:
为 lastname 是 "Wilson" 的人添加 firstname:
UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'
结果如下:
6.2、更新某一行中的若干列
我们会修改地址(address),并添加城市名称(city):
UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing' WHERE LastName = 'Wilson'
结果如下:
7、删除
使用更新和删除操作时一定要用 WHERE 子句,不然会把整张表的数据都破坏。可以先用 SELECT 语句进行测试,防止错误删除。
7.1、删除某一行内容
DELETE 语句用于删除表中的行
语法:
DELETE FROM 表名称 WHERE 列名称 = 值
例子:
DELETE FROM mytable WHERE id = 1;
7.2、 删除所有行,即清空表
- TRUNCATE删除整个列表
TRUNCATE TABLE mytable;
- DELETE删除所有行,这意味着表的结构、属性和索引都是完整的
DELETE FROM table_name
或者
DELETE * FROM table_name
8、查询
8.1、DISTINCT
DISTINCT 用于返回唯一不同的值
原始表:
语法:
SELECT DISTINCT 列名称 FROM 表名称
例子:
从 "Company" 列中选取所有的值,我们需要使用 SELECT 语句,包含重复值。
SELECT Company FROM Orders;
从 Company" 列中仅选取唯一不同的值,我们需要使用 SELECT DISTINCT 语句。
SELECT DISTINCT Company FROM Orders;
只用SELECT的结果:
加上DISTINCT的结果:“W3School” 仅被列出了一次。
8.2、LIMIT
LIMIT 限制返回的行数。可以有两个参数,第一个参数为起始行,从 0 开始,0为第0行;第二个参数为返回的总行数。
语法:
SELECT * FROM 表名 LIMIT 第几行数据开始查,查几条数据;
或者:默认从首行开始。
SELECT * FROM 表名 LIMIT 总行数(默认从0开始取);
例子:
返回前 5 行:
SELECT * FROM mytable LIMIT 5;
SELECT * FROM mytable LIMIT 0, 5;
从第二行开始取,取第 3 ~ 5 行:
SELECT * FROM mytable LIMIT 2, 3;
9、排序
ORDER BY 语句用于对结果集进行排序。
两个关键字:
ASC :升序(默认)
DESC :降序
可以按多个列进行排序,并且为每个列指定不同的排序方式:
SELECT * FROM mytable ORDER BY col1 DESC, col2 ASC;
10、过滤
使用的语句:
SELECT * FROM 表名 WHERE 条件;
应该注意到,NULL 与 0、空字符串都不同。
AND 和 OR 用于连接多个过滤条件。优先处理 AND,当一个过滤表达式涉及到多个 AND 和 OR 时,可以使用 () 来决定优先级,使得优先级关系更清晰。
IN 操作符用于匹配一组值,其后也可以接一个 SELECT 子句,从而匹配子查询得到的一组值。
NOT 操作符用于否定一个条件。
例子:
SELECT prod_name, prod_price FROM Products WHERE prod_price < 10;
SELECT * FROM mytable WHERE col IS NULL;
11、通配符
通配符也是用在过滤语句中,但它只能用于文本字段。
SQL 通配符必须与 LIKE 运算符一起使用。
- 例子1:从上面的 “Persons” 表中选取居住在以 “Ne” 开始的城市里的人
SELECT * FROM Persons WHERE City LIKE 'Ne%'
2. 例子 2:从 “Persons” 表中选取居住在包含 “lond” 的城市里的人
SELECT * FROM Persons WHERE City LIKE '%lond%'
3. 例子3;从上面的 “Persons” 表中选取名字的第一个字符之后是 “eorge” 的人:
SELECT * FROM Persons WHERE FirstName LIKE '_eorge'
更多具体例子见链接:SQL基础知识: https://www.w3school.com.cn/sql/sql_wildcards.asp
12、计算字段
数据的转换和格式化,
计算字段通常需要使用 AS 来取新的名字,否则输出的时候字段名为计算表达式。
SELECT col1 * col2 AS alias FROM mytable;
CONCAT() 用于连接两个字段。许多数据库会使用空格把一个值填充为列宽,因此连接的结果会出现一些不必要的空格,使用 TRIM() 可以去除首尾空格。
SELECT CONCAT(TRIM(col1), '(', TRIM(col2), ')') AS concat_col FROM mytable;
13、 函数
各个 DBMS 的函数都是不相同的,因此不可移植,以下主要是 MySQL 的函数。
13.1、汇总
AVG() 会忽略 NULL 行。使用 DISTINCT 可以汇总不同的值。
SELECT AVG(DISTINCT col1) AS avg_col FROM mytable;
COUNT(*) 函数返回表中的记录数:
语法:
SELECT COUNT(*) FROM table_name
例子:
返回表中总行数
SELECT COUNT(*) AS NumberOfOrders FROM Orders
13.2、文本处理
其中, SOUNDEX() 可以将一个字符串转换为描述其语音表示的字母数字模式。
SELECT * FROM mytable WHERE SOUNDEX(col1) = SOUNDEX('apple')
13.3、日期和时间处理
日期格式:YYYY-MM-DD
时间格式:HH:MM:SS
mysql> SELECT NOW();
2018-4-14 20:25:11
13.4、数值处理
14、分组
GROUP BY 是分组,是分组,是分组,分组并不是去重,而是分组。将查询结果按一个或多个进行分组,字段值相同的为一组。
原始表:
14.1、简单的GROUP BY分组
在分组时,select后面跟的的字段一般都会出现在 group by 后
语法:
SELECT 类别, SUM(数量) AS 新名字 FROM A GROUP BY 类别
例子:
按类别分组,并且将每一类的数量加起来,返回每一类数量的总和,返回结果第一列为类别名,第二列为AS后出现的新命名。
SELECT 类别, SUM(数量) AS 数量之和 FROM A GROUP BY 类别
SELECT gender,GROUP_CONCAT(`name`) from employee GROUP BY gender
SELECT name,gender from employee GROUP BY gender,name
-- 先按gender分组,再按姓名分组...
14.2、GROUP BY 和 ORDER BY
GROUP BY 自动按分组字段进行排序,ORDER BY 也可以按汇总字段来进行排序。
select 类别, sum(数量) AS 数量之和 from A group by 类别 order by sum(数量) desc
具体的分组示例参考: https://blog.csdn.net/dongapple/article/details/80923040.
15、子查询
子查询就是将一个查询(子查询)的结果作为另一个查询(主查询)的数据来源或判断条件的查询。常见的子查询有WHERE子查询,HAVING子查询,FROM子查询,SELECT子查询,EXISTS子查询,子查询要使用小括号();
子查询具体介绍: https://www.cnblogs.com/fzxey/p/10896244.html.
16、连接
连接用于连接多个表,使用 JOIN 关键字,并且条件语句使用 ON 而不是 WHERE。
连接可以替换子查询,并且比子查询的效率一般会更快。
原表:
16.1、内连接
1.1.等值连接:在连接条件中使用等于号(=)运算符比较被连接列的列值,其查询结果中列出被连接表中的所有列,包括其中的重复列。
1.2.不等值连接:在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。
1.3.自然连接:在连接条件中使用等于(=)运算符比较被连接列的列值,但它使用选择列表指出查询结果集合中所包括的列,并删除连接表中的重复列。
内连接可以使用下面两种方式,其中第二种方式的inner可以省略。
表示当两个表的sutid列数值相等时,分别取出相等行的所有内容,并且合并为一个表格。
select *
from book as a,stu as b
where a.sutid = b.stuid
select *
from book as a
inner join stu as b
on a.sutid = b.stuid
结果如下:
16.2、外连接
2.1.左联接:是以左表为基准,将a.stuid = b.stuid的数据进行连接,然后将左表没有的对应项显示,右表的列为NULL
select * from book as a left join stu as b on a.sutid = b.stuid
2.2.右连接:是以右表为基准,将a.stuid = b.stuid的数据进行连接,然以将右表没有的对应项显示,左表的列为NULL。
select * from book as a right join stu as b on a.sutid = b.stuid
2.3.全连接:完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。
select * from book as a full outer join stu as b on a.sutid = b.stuid
16.3、交叉连接
交叉连接:交叉联接返回左表中的所有行,左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。
select * from book as a cross join stu as b order by a.id
17、组合查询
使用 UNION 来组合两个查询,如果第一个查询返回 M 行,第二个查询返回 N 行,那么组合查询的结果一般为 M+N 行。
每个查询必须包含相同的列、表达式和聚集函数。
默认会去除相同行,如果需要保留相同行,使用 UNION ALL。
UNION语法:
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
UNION ALL语法:
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
原始表:
- UNION
列出所有在中国和美国的不同的雇员名:
SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA
2. UNION ALL
SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA
结果:
18、视图
视图是虚拟的表,本身不包含数据,也就不能对其进行索引操作。
对视图的操作和对普通表的操作一样。
语法:
CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
视图 "Current Product List" 会从 Products 表列出所有正在使用的产品。
CREATE VIEW [Current Product List] AS SELECT ProductID,ProductName FROM Products WHERE Discontinued=No
19、存储过程
20、游标
21、触发器
22、事务管理
23、字符集
24、权限管理
MySQL 的账户信息保存在 mysql 这个数据库中。
USE mysql;
SELECT user FROM user;
24.1、创建账户
新创建的账户没有任何权限
CREATE USER myuser IDENTIFIED BY 'mypassword';
24.2、修改账户名
RENAME USER myuser TO newuser;
24.3、删除账户
DROP USER myuser;
24.4、查看权限
SHOW GRANTS FOR myuser;
24.5、授予权限
账户用 username@host 的形式定义,username@% 使用的是默认主机名。
GRANT SELECT, INSERT ON mydatabase.* TO myuser;
24.6、删除权限
GRANT 和 REVOKE 可在几个层次上控制访问权限:
整个服务器,使用 GRANT ALL 和 REVOKE ALL;
整个数据库,使用 ON database.*;
特定的表,使用 ON database.table;
特定的列;
特定的存储过程
REVOKE SELECT, INSERT ON mydatabase.* FROM myuser;
24.7、更改密码
必须使用 Password() 函数进行加密
SET PASSWROD FOR myuser = Password('new_password');