sql_language

发表于 | 分类于 |

创建数据库
格式 

1
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] chartset_name

范例

1
2
3
CREATE DATABASE hisdb;
CREATE DATABASE IF NOT EXISTS yourdbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
create database yourdb DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

说明

DATABASE 和SCHEMA完全相同，任选其一即可。
IF NOT EXISTS 假设创建的数据库存在，就不创建
DEFAULT CHARACTER 数据库的编码方式。

修改数据表
添加单列
1
ALTER TABLE tbl_name ADD [COLUMN] col_name column_definiton [FIRST | AFTER col_name]
FIRST表示新添加的列位于最前面，AFTER col_name表示在某一列后面，默认添加到最后一列
添加多列
1
ALTER TABLE tbl_name ADD [COLUMN] (col_name column_difinition, col_name column_difinition);
删除列
1
ALTER TABLE tbl_name DROP col_name
修改列定义
1
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER colname]
修改列名称
1
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FRIST|AFTER col_name]
修改数据表名称
1
ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name
2
RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_nme]…
自动编号
自动编号必须跟主键组合使用，默认情况下，起始值为1，增量为1.
主键
主键约束
每张数据表只能存在一个主键
主键保证记录的唯一性
主键自动为NOT NULL
1
CREATE TABLE tb2 ( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,username VARCHAR(20) NOT NULL);
唯一约束
唯一约束可以保证记录的唯一性
唯一约束的字段可以为空
每张表中可以存在多个唯一约束
1
CREATE TABLE tb3 (id SMALLINT UNSIGNED UNIQUE KEY, username VARCHAR(20) NOT NULL);
默认值
当插入记录时，如果没有明确为字段赋值，则自动赋予默认值
约束
约束保证了数据的完整性和一致性
约束分为表级约束和列级约束。表级约束表示约束两个或两个以上的字段列级约束表示约束一个字段,表级约束只能在列定义后声明
约束类型：
NOT NULL 非空约束 不存在表级约束，只有列级约束
PRIMARY KEY 主键约束
UNIQUE KEY 唯一约束
DEFAULT 默认约束 不存在表级约束，只有列级约束
POREIGN KEY 外键约束
外键约束
外键约束的条件
1、 父表和子表必须使用相同的存储引擎，而且禁止使用临时表。子表具有外键列的表，子表所参照的表称为父表。
2、 数据表的存储引擎只能为InnoDB。
3、 外键列和参照列必须具有相同的数据类型，其中数字的长度或是否有符号位必须相同，而字符串的长度则可以不同。
4、 外键列和参照列必须创建索引，如果外键列不存在索引的话MySQL将自动创建索引。参照列不会自动创建索引。
范例：
父表的创建
1
CREATE TABLE province ( id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, pname VARCHAR(20) NOT NULL);
子表的创建
1
CREATE TABLE city (id SMALLINT UNSIGNED PRIMARY KEY AUTOINCREMENT,name VARCHAR(20) NOT NULL, pid SMALLINT UNSIGNED, FOREIGN KEY (pid) REFERENCES province (id));
外键约束的参照操作1、 CASCADE：从父表删除或更新且自动删除或更新子表中匹配的行
1
CREATE TABLE city (id SMALLINT UNSIGNED PRIMARY KEY AUTOINCREMENT,name VARCHAR(20) NOT NULL, pid SMALLINT UNSIGNED, FOREIGN KEY (pid) REFERENCES province (id) ON DELETE CASCADE);
2、 SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL，如果使用该选项，必须保证子表列没有指定的NOT NULL
3、 RESTRICT：拒绝对父表的删除或更新操作
4、 NO ACTION：标准SQL的关键字，在MySQL中与RESTRICT相同
修改约束
添加约束
1
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,……)
CONSTARINT是给约束起名称，可不加
删除约束
1
ALTER TABLE tabl_name DROP {INDEX|KEY} index_name
INSERT
1
INSERT [INTO] tbl_name [(tbl_column,…)] {VALUES|VALUE} (expr|DEFAULT,…),(……)
2
INSERT [INTO] tbl_name SET col_name={expr | DEFAULT},…
3
INSERT [INTO] tbl_name [(col_name,…)] SELECT…
UPDATE 单表更新

UPDATE [LOW_PRIORITY][IGNORE] table_reference SET col_name1={expr1 |DEFAULT}[,col_name2={expr2 | DEFAULT}]..[WHERE where_condition]

简介

SQL语句主要划分为以下3种类型：

  • DDL(Data Definition Languages):数据定义语言，定义不同的数据段、数据库、表、列、索引等数据库对象。
  • DML(Data Manipulation Languages):数据操纵语句，用于添加，删除，更新和查询数据库记录，并检查数据完整性。
  • DCL(Data Control Languages):数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。

DDL

```sql

create databse dbname;
drop database dbname;

– 创建表

CREATE TABLE tablename(
column_name_1 column_type constraints,
column_name_2 column_type constraints);
DESC tablename; – 查询表定义

–查看创建表的 sql 语句，\G 按照字段竖向排列，方便显示。
show create table emp \G;
DROP TABLE tablename;

– 修改表字段类型，first、after 修改字段显示的顺序。

ALTER TABLE tablename modify [COLUMN] column_definition[FIRST|AFTER col_name];

– 增加表字段

ALTER TABLE tablename add [ COLUMN ] column_definition [ FIRST | AFTER col_name];
– 删除表字段
ALTER TABLE tablename DROP [COLUMN] col_name;
– 修改字段名，可以同时修改字段类型
ALTER TABLE tablename change[COLUMN] old_col_name column_definition [ FIRST | AFTER col_name];
– 修改表名
ALTER TABLE tablename RENAME [TO] new_tablename;

```
## DML

```sql
INSERT INTO tablename (field1,field2,…,fieldn) VALUES (value1,value2,…valuen);
– 一次插入多条记录
INSERT INTO tablename (field1,field2,…,fieldn) VALUES
(value1,value2,…valuen),
(value1,value2,…valuen),
(value1,value2,…valuen);

UPDATE tablename SET field1=value1,field2=value2 [WHERE CONDITION];
– 更新多个表的记录
UPDATE t1, t2,…,tn set t1.field1, expr1, tn.fieldn=exprn [WHERE CONDITION];

– 示例

update emp a, dept b set a.sal = a.sal * b.deptno, b.deptname = a.ename where a.deptno = b.deptno;

DELETE FROM tablename [WHERE CONDITION];
– 删除多个表的记录，不论单表还是多表，不加 where 就是删除所有记录。
DELETE t1,t2,..,tn FROM t1,t2,…,tn [WHERE CONDITION];

– 示例

delete a, b from emp a, dept b where a.deptno = b.deptno and a.deptno =3;

SELECT * FROM tablename [WHERE CONDITION]

– 排序
SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC|ASC],filed2[DESC|ASC]];

– 去重

SELECT distinct colname from tablename;

– 限制 offset_start起始偏移量，row_count行数
SELECT…[LIMIT offset_start,row_count];
– 聚合
SELECT [field1,fiedl2,…,fieldn] fun_name

FROM tablename

[WHERE where_contition]
[GROUP BY field1,field2,…,fieldn

[WITH ROLLUP]]

[HAVING where_contition]

```

fun_name: 表示聚合函数，例如,sum(),count(),max(),min()。

GROUP BY: 分类聚合字段 。

WITH ROLLUP: 表示可否对分类的结果进行汇总 ，会多处一行来，用于统计聚合函数所在列的总和。

HAVING:对分类后的结果再进行条件过滤。

```sql

select deptno, count(1) from emp group by deptno with rollup;

select deptno, count(1) from emp group by deptno having count(1) > 1;

### 表连接
* 内连接, 选出两张表中互相匹配的记录。
* 外连接, 会选出其他不匹配的记录。
* 左连接: 包含左边所有表的记录，甚至是右边表中没有和它匹配的记录。
* 右连接: 包含右边所有表的记录，甚至是左边表中没有和它匹配的记录。

```sql

select ename, deptname from emp left join dept on emp.deptno = dept.deptno;

select ename, deptname from dept right join emp on dept.deptno = emp.deptno;

```

### 子查询
用于子查询的关键字有 in、=、!=、exists、not exists。
```sql

select * from emp where deptno=(select deptno from dept limit 1);

```

* MySQL 4.1以前的版本不支持子查询。

* 表连接在很多情况下用于优化子查询。
### 记录联合
将多个表的查询结果一并展示出来。 UNION和UNION ALL 的区别是，UNION会去除重复记录。

```sql
SELECT * FROM t1
UNION|UNION ALL
SELECT * FROM t2
…
UNION|UNION ALL
SELECT * FROM tn;

```

## DCL
```sql

– 创建用户 zl，且对 sqkila 表具有 insert、select 权限，密码为 123

grant select, insert on sakila.* to ‘zl’@’localhost’ identified by ‘123’;

– 移除 insert 权限

revoke insert on sakila.* from ‘zl’@’localhost’;

```

## 帮助的使用

```shell

? contents # 查看帮助可以提供什么

? data types # 查看支持哪些数据类型

? show # 查看 show 命令都能看些什么东西

? create table # 查看创建表的语法

```

## 查询元数据

MySQL 5.0之后提供了新的数据库information_schema，用来记录 MySQL 中的元数据信息。元数据指的是数据的数据，比如表名、列名、列类型、索引名等信息。

它比较特殊，它是一个虚拟数据库，物理上并不存在相关的目录和文件。

```sql

– 删除数据库 test1下所有前缀为 tmp 的表

select concat(‘drop table test1.’, table_name, ‘;’) from tables where table_schema = ‘test1’ and table_name like ‘tmp%’;

– 将数据库 test1下所有存储引擎为 myisam 的表改成 innodb。

select concat(‘alter table test1.’, table_name , ‘ engine=innodb;’) from tables where table_schema = ‘test1’ and engine = ‘MyISAM’;

```