DDL 数据定义
CREATE 创建
DROP 删除
RENAME 重命名
ALTER 更改
DQL 数据查询
WHERE 条件子句
DML 数据操作
其他常用SQL
USE 选择
FLUSH 刷新
GRANT 用户和权限操作
笛卡尔积
内联和外联
概念
SQL
分为四大类。分别是:
DDL
数据定义语句, 用于创建、修改和删除数据库内的数据结构。DQL
数据查询语句,用于查询数据。DML
数据操作语句,用于对数据的增,删,改。DCL
数据控制语句,用于对数据的访问控制。
DDL 数据定义
CREATE
语句,用来创建库,表和索引。ALTER
语句,用来添加列,删除列,更改列的数据类型,添加主键,重命名表等等更改现有的表结构操作。RENAME
语句,用来重命名表。DROP
语句,用来删除库,表和索引。
CREATE 创建
mysql create table 语法详解
MySQL使用CREATE INDEX创建索引
// 创建库
CREATE DATABASE [IF NOT EXISTS] databaseName;
// 创建表,用于MyISAM和InnoDB引擎
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tableName(
columnName 类型(长度) [属性] [索引] COMMENT '字段描述',
...
PRIMARY KEY (`columnName`),
KEY keyName(`columnName1`,`columnName2`...),
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
// 根据已有的表创建表
CREATE TABLE tableName like oldTable;
// 根据查询结果创建表
CREATE TABLE tableName AS SELECT * FROM oldTable;
// 创建索引,unique唯一索引,fulltext全文索引,spatial空间索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX indexName ON tableName(columnName[size] [ASC|DESC]);
DROP 删除
删除库:DROP DATABASE databaseName;
删除表:DROP TABLE tableName;
删除索引:DROP INDEX indexName ON tableName;
RENAME 重命名
重命名表:RENAME TABLE oldTable TO tableName;
ALTER 更改
ALTER TABLE
语句可用来添加列,删除列,更改列的数据类型,添加主键,重命名表等等更改现有的表结构操作。
- 修改列:
ALTER TABLE tableName CHANGE COLUMN columnName columnName 类型(长度) [属性] [索引];
- 修改列属性:
ALTER TABLE tableName MODIFY columnName 类型(长度) [属性] [索引];
- 新增列:
ALTER TABLE tableName ADD COLUMN columnName 类型(长度) [属性] [索引] [after|before] columnName1;
- 删除列:
ALTER TABLE tableName DROP COLUMN columnName;
- 重命名表:
ALTER TABLE tableName RENAME tableName1;
- 删除表的主键:
ALTER TABLE tableName DROP PRIMARY KEY;
- 新增主键:
ALTER TABLE tableName ADD CONSTRAINT PK_SJ_RESOURCE_CHARGES PRIMARY KEY (columnName1,columnName2...);
- 添加索引:
ALTER TABLE tableName ADD INDEX indexName(columnName1,...);
- 添加唯一限制条件索引:
ALTER TABLE tableName ADD UNIQUE indexName(columnName1,...);
- 删除索引:
ALTER TABLE tableName DROP INDEX indexName;
DQL 数据查询
SELECT
column_1, column_2, ...
FROM
table_1
[INNER | LEFT |RIGHT] JOIN table_2 ON conditions
WHERE
conditions
GROUP BY column_1
HAVING group_conditions
ORDER BY column_1
LIMIT offset, length;
SELECT
语句由以下列表中所述的几个子句组成:
SELECT
之后是逗号分隔列或星号(*
)的列表,表示要返回所有列。FROM
指定要查询数据的表或视图。JOIN
根据某些连接条件从其他表中获取数据。WHERE
过滤结果集中的行。GROUP BY
将一组行组合成小分组,并对每个小分组应用聚合函数。HAVING过滤器基于GROUP BY
子句定义的小分组。ORDER BY
指定用于排序的列的列表。LIMIT
限制返回行的数量。
语句中的SELECT
和FROM
语句是必须的,其他部分是可选的。
WHERE 条件子句
BETWEEN AND
一般用于指定数值和日期的范围 expr [NOT] BETWEEN begin_expr AND end_expr;
LIKE MySQL
提供两个通配符,用于与LIKE
运算符一起使用,它们分别是:百分比符号%
和下划线_
。
百分比(%
)通配符允许匹配任何字符串的零个或多个字符。
下划线(_
)通配符允许匹配任何单个字符。 value [NOT] LIKE 'value1%';
IS NULL value [NOT] IS NULL
IN value [NOT] IN ('value1','value2','value3')
DML 数据操作
- 新增数据,
INSERT INTO tableName(key1, key2, key3...) VALUES(value1, value2, value3...);
- 修改数据,
UPDATE tableName SET key1 = value1 WHERE key2 = value2;
- 删除数据,
DELETE FROM tableName WHERE key1 = value1
新增多条数据: INSERT INTO tableName(key1, key2, key3...) VALUES(value1, value2, value3...),( value1, value2, value3... )...
修改多条数据:
UPDATE categories
SET display_order = CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END
WHERE id IN (1,2,3)
其他常用SQL
USE 选择
选择库,USE databaseName;
FLUSH 刷新
刷新表,FLUSH tableName;
GRANT 用户和权限操作
GRANT 语句很重要 不仅可以创建用户,也可以修改用户密码,还可以设置用户权限所以grant语句是mysql中一个非常重要的语句
- 创建普通新用户:
GRANT SELECT ON *.* TO 'userName'@'localhost' IDENTIFIED BY 'userName';
- 授权:
GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'userName'@'localhost' IDENTIFIED BY 'userName';
- 给用户所有库的所有权限:
GRANT ALL PRIVILEGES ON *.* TO 'userName'@'%' IDENTIFIED BY 'userName' WITH GRANT OPTION;
- 查看所有用户:
SELECT * FROM mysql.user;
- root用户修改自己的密码:
SET PASSWORD = PASSWORD(123456);
- root修改普通用户的密码:
SET PASSWORD 'userName'@'localhost' = PASSWORD('newPassword')
- 收回权限:
REVOKE SELECT,INSERT,UPDATE,DELETE ON *.* FROM 'userName'@'localhost';
- 收回所有权限:
REVOKE ALL PRIVILEGES ON *.* FROM userName; REVOKE ALL PRIVILEGES ON userName.* FROM userName;
- 收回授权权限:
REVOKE GRANT OPTION ON *.* FROM userName;
笛卡尔积
两个集合的乘积,又称为直积。
select * form table1,table2;
在查询中要尽量避免笛卡尔积,因为会产生大量的多余的数据。可以使用内联和外联查询来避免。
内联和外联
inner join
内联,返回两表的交集。 left join
左关联,左并集。返回左表的查询出的所有数据。 right join
右关联,右并集。返回右表的查询出的所有数据。