MYSQL

增

增加数据库

1
create database 数据库名;

增加表

1
2
3
4
create table 表名(
    字段名 数据类型 [约束条件],
    字段名 数据类型 [约束条件]
);

增加字段

1
ALTER TABLE 表名 ADD 新字段名 数据类型 [约束条件] [FIRST|AFTER] 已存在字段名;

[FIRST|AFTER]是位置说明，代表插入进第一位|已存在字段名后。

增加约束条件

  • 主键（此语句写在表中）

    1
    PRIMARY KEY

  • 外键约束（此语句写在表中）

    1
    CONSTRAINT 外键名 FOREIGN KEY 字段名 REFERENCES 主表名(主键名)

  • 常用约束

    添加唯一约束

    唯一约束（Unique Constraint）要求该列唯一，允许为空，但是只能有一个空值。唯一约束可以确保一列或者几列不出现重复值。

    定义部门表的部门名称唯一，SQL语句如下：关键词 UNIQUE。

    1
    2
    3
    4
    5
    CREATE TABLE t_dept(    
        id INT PRIMARY KEY,    
        name VARCHAR(22) UNIQUE,    
        location VARCHAR(50)
    )

    添加非空约束

    关键词：NOT NULL；

    例如：

    1
    CREATE TABLE t_dept(    id INT PRIMARY KEY,    name VARCHAR(22) NOT NULL,    location VARCHAR(50))

    使用默认约束

    默认约束：即给字段一个默认值。 关键词：DEFAULT；

    如果是添加字符串型默认值要使用单引号，如果是整型则不需要加任何符号；

    如果要添加的是中文默认值，则需要加上DEFAULT CHARSET=utf8;使用英文字符则不需要。

    例如：

    1
    2
    3
    4
    5
    CREATE TABLE t_emp(    
        id INT PRIMARY KEY,    
        name VARCHAR(22),    
        sex VARCHAR(2) DEFAULT '男'
    ) DEFAULT CHARSET=utf8;

增加数据

1
语法规则：INSERT INTO 表名 (字段名) VALUES (内容);

删

删除表

1
DROP TABLE table_name;

删除字段

1
ALTER TABLE 表名 DROP 字段名;

删除约束条件

  • 删除外键约束

    1
    ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;

删除数据

删除表中全部数据

1
DELETE FROM 表名;

删除表中指定数据

1
DELETE FROM 表名 WHERE 条件语句;

改

修改字段

修改字段名与数据类型

  • 修改字段名

    1
    ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型;

  • 修改数据类型

    1
    ALTER TABLE 表名 MODIFY 字段名 数据类型;

修改字段排列位置

1
ALTER TABLE 表名 MODIFY 字段1 数据类型 FIRST|AFTER 字段2;

修改数据

1
UPDATE 表名 SET 字段名1 = 内容1, 字段名2 = 内容2, 字段名3 = 内容3 WHERE 过滤条件;

查

单表查询

select语句查询

查询表中某指定字段数据

1
SELECT 字段名1, 字段名2 FROM 表名;

查询所有字段数据

1
SELECT * FROM 表名;

带IN关键字的查询

提取某指定数据，括号内表示需要的数据列表

1
SELECT 字段名 FROM 表名 WHERE 字段名 IN (n1,n2,n3,…);

排除某数据查询

1
SELECT 字段名 FROM 表名 WHERE 字段名 NOT IN (n1,n2,n3,…);

带 BETWEEN AND 的范围查询

想知道在某一范围内有多少符合条件的数据，需要用BETWEEN AND。

BETWEEN AND需要两个参数支持，一个是范围的开始值，另一个就是结束值了。如果字段值满足指定的范围查询条件，就返回这些满足条件的数据内容。

1
SELECT 字段名 FROM 表名 WHERE 字段名 BETWEEN n1 AND n2;

排除某数据查询

1
SELECT 字段名 FROM 表名 WHERE 字段名 NOT BETWEEN n1 AND n2;

带 LIKE 的字符匹配查询

使用通配符%模糊匹配数据内容

1
SELECT 字段名 FROM 表名 WHERE 字段名 LIKE ‘字符%’;

使用通配符_模糊匹配数据内容

1
SELECT 字段名 FROM 表名 WHERE 字段名 LIKE 字符_;

查询空值与去除重复结果

查询空值

1
SELECT 字段名 FROM 表名 WHERE 字段名 IS NULL;

去除重复项

1
SELECT DISTINCT 字段名 FROM 表名;

带 AND 与 OR 的多条件查询

AND关键字的多条件查询

1
SELECT 字段名 FROM 表名 WHERE 表达式1 AND 表达式2;

OR关键字的多条件查询

1
SELECT 字段名 FROM 表名 WHERE 表达式1 OR 表达式2;

对查询结果进行排序

1
SELECT 字段名 FROM 表名 ORDER BY 字段名 [ASC[DESC]];

指定排序方向

从上面的查询语句中我们并未看到有任何表示排序方向的关键字，在默认情况下，它是按升序排列的。

  • ASC 升序关键字
  • DESC 降序关键字

分组查询

分组查询的关键字是Group By，查询的是每个分组中 首次出现的一条记录。

1
SELECT 字段名 FROM 表名 GROUP BY 字段名;

使用 LIMIT 限制查询结果的数量

1
SELECT 字段名 FROM 表名 LIMIT [OFFSET,] 记录数;

参数说明：

  • 第一个参数，OFFSET，可选参数，表示偏移量，如果不指定默认值为0，表示从查询结果的第一条记录开始，若偏移量为1，则从查询结果中的第二条记录开始，以此类推。
  • 第二个参数，记录数，表示返回查询结果的条数。

连接查询

内连接查询

从表1中取出每一条记录，去表2中与所有的记录进行匹配，匹配必须是某个条件在表1中与表2中相同，最终才会保留结果，否则不保留。inner 关键字可省略不写；on 表示连接条件：条件字段就是代表相同的业务含义（如下面两张表中的 employee.dept_id 和 department.id），大多数情况下为两张表中的主外键关系。

1
表1 [inner] join 表2 on 表1.字段=表2.字段

外连接查询

  • 以某张表为主，取出里面的所有记录，然后每条与另外一张表进行连接，不管能不能匹配上条件，最终都会保留。能匹配，正确保留；不能匹配，其它表的字段都置空（null），称为外连接。
1
表1 left/right [outer]  join 表2 on 表1.字段=表2.字段

复合条件连接查询

  • 复合条件连接查询，就是在连接查询的过程中，通过添加过滤条件来限制查询结果，使查询结果更加精确。

子查询

子查询是指出现在其他SQL语句内的SELECT子句。

例如：

1
SELECT * FROM t1 WHERE col1=(SELECT col2 FROM t2);

子查询指嵌套在查询内部，且必须始终出现在圆括号内，子查询可以分为四类：

  • 标量子查询：返回单一值的标量，最简单的形式；
  • 列子查询：返回的结果集是 N 行一列；
  • 行子查询：返回的结果集是一行 N 列；
  • 表子查询：返回的结果集是 N 行 N 列。

带比较运算符的子查询

运算符 说明
> 大于
>= 大于或等于
= 等于
!= 或 <> 不等于
< 小于
<= 小于或等于
  • 带有比较运算符的子查询是指父查询和子查询使用比较运算符连接的嵌套查询；
  • 使用场景：当用户能够确切的知道内层查询返回的是单个值时,可以使用比较运算符。

关键字子查询

由于列子查询返回的结果集是 N 行一列，因此不能直接使用 = 、>、<、>=、<=、<>这些比较标量结果的操作符。在列子查询中可以使用 ALL、ANY、SOME 和 IN关键字操作符。

  • ALL关键字

    ALL必须接在一个比较运算符的后面，表示与子查询返回的所有值比较都为 TRUE则返回TRUE。

    table1表数据：

    col1
    2
    10

    table2表数据：

    col2
    5
    12
    20

    举个例子：

    1
    SELECT col1 FROM table1 WHERE col1 > ALL (SELECT col2 FROM table2)

    该查询语句不会返回任何结果，因为col1中没有比col2所有值都大的值。

  • ANY和SOME关键字

    ANY与比较操作符联合使用，表示与子查询返回的任何值比较为TRUE ，则返回TRUE。 SOME是ANY的别名，一般用的比较少。

    1
    SELECT col1 FROM table1 WHERE col1 > ANY (SELECT col2 FROM table2)

    返回的是table2的所有col2列的结果（5,12,20），然后将table1中col1的值与之进行比较，只要大于col2的任何值则为TRUE，因此查询结果为10。

  • IN关键字

    IN的意思就是指定的一个值是否在这个集合中，如果在就返回TRUE；否则就返回FALSE了，同IN(项1，项2，…)；

    IN 是 = ANY 的别名，二者相同，但 NOT IN 的别名却不是 <> ANY 而是 <> SOME。

复杂查询

光说不做假把式，去练两道题吧

聚合函数查询

  • COUNT( )函数

    统计记录的总条数。

    1
    select count(*/字段名) from 数据表 where限制;

  • SUM()函数基本使用

    SUM()函数是对数据表的某列进行求和操作。

    1
    select sum(字段名) from 数据表 where限制;

  • AVG()函数基本使用

    AVG()函数是对数据表的某列进行求平均值操作。

    1
    select avg(字段名) from 数据表 where限制;

  • MAX()函数基本使用

    MAX()函数是求某列的最大数值。

    1
    select max(字段名) from 数据表 where限制;

  • MIN()函数基本使用

    MIN()函数是求某列的最小数值。

    1
    select min(字段名) from 数据表 where限制;

使用索引优化查询

索引是什么

数据库索引是一种提高数据库系统性能的方法。索引能让数据库服务器更快地查找和获取表中指定的行。

例如，为了方便读者快速查找书中的术语，很多书籍在最后附加了索引页，术语按字母排序，同时给出页码。这样读者可以根据术语名，快速获取页码，而不用翻阅整本书。

但是索引也给数据库系统带来了一定的开销，所以我们应该谨慎地使用它们。

例如现在有一张简单的表：

1
2
3
4
create table test(    
    id int not null,    
    content varchar(255) not null
)

在这张表中假设存了1000条数据（id依次递增且不重复），我们要查询表中id=234的数据，sql如下：

1
select * from test where id = 234;

一般情况，在没有索引下，数据库系统必须扫描整个表（一行一行地检查），才能获取到所有满足条件的行，很明显这种方法的效率是非常低的。

那么肯定有优化的方法，就是 索引 了！在id字段上添加索引，这样 MySQL 就只需要扫描一行数据就可以了。

索引的分类

索引大体可分为单列索引（普通索引，唯一索引，主键索引）、组合索引、全文索引、空间索引四类。本实训我们主要介绍单例索引和组合索引：

  • 单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引；
    • 普通索引：仅加速查询 最基本的索引，没有任何限制，是我们大多数情况下使用到的索引；
    • 唯一索引：索引列中的值必须是唯一的，但允许为空值；
    • 主键索引：是一种特殊的唯一索引，不允许有空值。
  • 组合索引：在表的多个字段上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

索引的创建

创建索引有两种方式，一种是在建表时创建，另一种是建表后创建：

  • 普通索引：

  1. 创表时创建普通索引：

    1
    CREATE table mytable( id INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username));

  2. 建表后创建普通索引：

    1
    create INDEX 索引名称 on 表名(字段名);#或者ALTER TABLE 表名 ADD INDEX 索引名称 (字段名);

  • 唯一索引：

    1
    CREATE UNIQUE INDEX 索引名称 ON 表名(字段名);#或者ALTER TABLE 表名 ADD UNIQUE (字段名);

  • 主键索引：主键索引一般在建表时创建，会设为 int 而且是 AUTO_INCREMENT自增类型的，例如一般表的id字段。

    1
    CREATE TABLE mytable (  id int(11) NOT NULL AUTO_INCREMENT,  PRIMARY KEY (id));

  • 组合索引：组合索引就是在多个字段上创建一个索引。（应用场景：当表的行数远远大于索引键的数目时，使用这种方式可以明显加快表的查询速度）

    1
    CREATE INDEX 索引名称 ON 表名(字段1,字段2，字段3);#或者ALTER TABLE 表名 ADD INDEX 索引名称(字段1，字段2，字段3);

删除索引

同样，删除索引也有两种方式。

1
2
3
4
5
#使用drop删除索引
drop index index_name on table_name;
#使用alter删除索引
alter table table_name drop index index_name;
alter table table_name drop primary key; #删除主键索引

查询表中索引

查询索引 SQL：

1
show index from 表名;

视图

视图是一种虚拟表，它可以简化复杂的查询和提供数据的安全访问方式。

创建视图

1
2
3
4
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
   [WITH [CASCADED | LOCAL] CHECK OPTION]

参数说明：

  • OR REPLACE：表示替换已有视图；
  • ALGORITHM：表示视图选择算法，默认算法是UNDEFINED(未定义的)： MySQL 自动选择要使用的算法 ；merge合并；temptable临时表；
  • column_list：可选参数，指定视图中各个属性的名词，默认情况下与select语句中查询的属性相同；
  • select_statement：表示select语句；
  • [WITH [CASCADED | LOCAL] CHECK OPTION]：表示视图在更新时保证在视图的权限范围之内；cascade是默认值，表示更新视图的时候，要满足视图和表的相关条件；local表示更新视图的时候，要满足该视图定义的一个条件即可。

操作视图

视图是逻辑表，也就是说视图不是真实的表，但操作视图和操作普通表的语法是一样的。

用户可以在视图中无条件地使用select语句查询数据。但使用insert、update和delete操作需要在创建视图时满足以下条件（满足以下条件的视图称为可更新视图）：

  • from子句中只能引用有1个表（真实表或可更新视图）；
  • 不能包含 with、distinct、group by、having、limit等子句；
  • 不能使用复合查询，即不能使用union、intersect、except等集合操作；
  • select子句的字段列表不能包含聚合、窗口函数、集合返回函数。

删除视图

1
DROP VIEW view_name;

授权及回收权限

SQLSERVER