MsSQL 极简教程(一)

1. 通过命令行连接到 MySQL

mysql [-h 120.0.1] [-P 3306] -u root -p 
参数:
    -h : MySQL服务所在的主机IP 
    -P : MySQL服务端口号, 默认3306 
    -u : MySQL数据库用户名 
    -p : MySQL数据库用户名对应的密码

2. SQL分类

3. 数据类型

数值类型

字符串类型

日期时间类型

4. 数据库操作

查询所有数据库

show databases ;

查询当前数据库

select database() ;

创建数据库

create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序 规则 ] ;

删除数据库

drop database [ if exists ] 数据库名 ;

切换数据库

use 数据库名 ;

5. 表操作

查询当前数据库所有表

show tables;

查看指定表结构

desc 表名 ;

查询指定表的建表语句

show create table 表名 ;

创建表结构

CREATE TABLE 表名( 
    字段1 字段1类型 [ COMMENT 字段1注释 ], 
    字段2 字段2类型 [COMMENT 字段2注释 ], 
    字段3 字段3类型 [COMMENT 字段3注释 ], 
    ...... 
    字段n 字段n类型 [COMMENT 字段n注释 ] 
) [ COMMENT 表注释 ] ;

添加字段

ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];

修改数据类型

ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);

修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];

删除字段

ALTER TABLE 表名 DROP 字段名;

修改表名

ALTER TABLE 表名 RENAME TO 新表名;

删除表

DROP TABLE IF EXISTS tb_user;

删除指定表, 并重新创建表

TRUNCATE TABLE 表名;

5. 数据操作

给指定字段添加数据

INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);

给全部字段添加数据

INSERT INTO 表名 VALUES (值1, 值2, ...);

批量添加数据

INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值 1, 值2, ...) ;

修改数据

UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ] ;

删除数据

DELETE FROM 表名 [ WHERE 条件 ] ;

6. 数据查询

基本语法

SELECT
 字段列表 
FROM
 表名列表 
WHERE
    条件列表 
GROUP BY 
    分组字段列表 
HAVING
    分组后条件列表 
ORDER BY 
    排序字段列表 
LIMIT
    分页参数

查询多个字段

SELECT 字段1, 字段2, 字段3 ... FROM 表名 ;
SELECT * FROM 表名 ;

字段设置别名

SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名;
SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名;

去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

条件查询

SELECT 字段列表 FROM 表名 WHERE 条件列表 ; 


聚合函数

SELECT 聚合函数(字段列表) FROM 表名 ;
注意 : NULL值是不参与所有聚合函数运算的。

分组查询

SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组 后过滤条件 ];

where与having区别

  • 执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤。
  • 判断条件不同:where不能对聚合函数进行判断,而having可以。

注意事项:

  • 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义。
  • 执行顺序: where > 聚合函数 > having 。
  • 支持多字段分组, 具体语法为 : group by columnA,columnB

排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;

排序方式

  • ASC : 升序(默认值)
  • DESC: 降序

注意事项:

  • 如果是升序, 可以不指定排序方式ASC ;
  • 如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序 ;

分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;

注意事项:

  • 起始索引从0开始,起始索引 = (查询页码 - 1)* 每页显示记录数。
  • 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT。
  • 如果查询的是第一页数据,起始索引可以省略,直接简写为 limit 10。

执行顺序

7. 用户控制

查询用户

select * from mysql.user;

其中 Host代表当前用户访问的主机, 如果为localhost, 仅代表只能够在当前本机访问,是不可以远程访问的。 User代表的是访问该数据库的用户名。在MySQL中需要通过Host和User来唯一标识一个用户。

创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

修改用户密码

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;

删除用户

DROP USER '用户名'@'主机名' ;

注意事项:

  • 在MySQL中需要通过用户名@主机名的方式,来唯一标识一个用户。
  • 主机名可以使用 % 通配。
  • 这类SQL开发人员操作的比较少,主要是DBA( Database Administrator 数据库管理员)使用。

8. 权限控制

查询权限

SHOW GRANTS FOR '用户名'@'主机名' ;

授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

注意事项:

  • 多个权限之间,使用逗号分隔
  • 授权时, 数据库名和表名可以使用 * 进行通配,代表所有。

9. 函数

字符串函数

数值函数

日期函数

date_add:增加指定的时间间隔

select date_add(now(), INTERVAL 70 YEAR );

流程函数


示例

select 
    id, 
    name, 
    (case when math >= 85 then '优秀' when math >=60 then '及格' else '不及格' end ) '数学', 
    (case when english >= 85 then '优秀' when english >=60 then '及格' else '不及格' end ) '英语', 
    (case when chinese >= 85 then '优秀' when chinese >=60 then '及格' else '不及格' end ) '语文' 
from score;

10. 约束

其它约束

注意:约束是作用于表中字段上的,可以在创建表/修改表的时候添加约束。

演示

CREATE TABLE tb_user( 
    id int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识', 
    name varchar(10) NOT NULL UNIQUE COMMENT '姓名' , 
    age int check (age > 0 && age <= 120) COMMENT '年龄' , 
    status char(1) default '1' COMMENT '状态', 
    gender char(1) COMMENT '性别' 
);

外键约束

添加外键

CREATE TABLE 表名( 
    字段名 数据类型, 
    ... 
    [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) 
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ;

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

删除/更新行为

在删除父表数据时产生的约束行为,我们就称为删除/更新行为

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) 
ON UPDATE CASCADE ON DELETE CASCADE;

11. 多表查询

内连接

内连接查询的是两张表交集部分的数据。

SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;
一旦为表起了别名,就不能再使用表名来指定对应的字段了,此时只能够使用别名来指定字段。

外连接

左外连接

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;

左外连接相当于查询表1(左表)的所有数据,当然也包含表1和表2交集部分的数据。

右外连接

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;

右外连接相当于查询表2(右表)的所有数据,当然也包含表1和表2交集部分的数据。

注意事项:

    左外连接和右外连接是可以相互替换的,只需要调整在连接查询时SQL中,表结构的先后顺序就可以了。而我们在日常开发使用时,更偏向于左外连接。

自连接

自连接查询,顾名思义,就是自己连接自己,也就是把一张表连接查询多次。

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

而对于自连接查询,可以是内连接查询,也可以是外连接查询。

注意事项:
    在自连接查询中,必须要为表起别名,要不然我们不清楚所指定的条件、返回的字段,到底是哪一张表的字段。

联合查询

SELECT 字段列表 FROM 表A ... 
UNION [ ALL ] 
SELECT 字段列表 FROM 表B ....;
  • 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
  • union all 会将全部的数据直接合并在一起,union 会对合并之后的数据去重

子查询

标量子查询

子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式,这种子查询称为标量子查询。

常用的操作符:= <> > >= < <=

select * from emp where dept_id = (select id from dept where name = '销售部');

列子查询

子查询返回的结果是一列(可以是多行),这种子查询称为列子查询。

select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');

行子查询

子查询返回的结果是一行(可以是多列),这种子查询称为行子查询。

常用的操作符:= 、<> 、IN 、NOT IN

select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');

表子查询

子查询返回的结果是多行多列,这种子查询称为表子查询。
常用的操作符:IN

select * from emp where (job,salary) in 
( select job, salary from emp where name = '鹿杖客' or name = '宋远桥' );

12. 事务

查看/设置事务提交方式

SELECT @@autocommit ; 
SET @@autocommit = 0 ;
注意:上述的这种方式,我们是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提交, 此时我们执行的DML语句都不会提交, 需要手动的执行commit进行提交。

开启事务

START TRANSACTION 或 BEGIN ;

提交事务

COMMIT;

回滚事务

ROLLBACK;

事务四大特性

  • 原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
  • 一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态。
  • 隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
  • 持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。

上述就是事务的四大特性,简称ACID。

并发事务问题

赃读:一个事务读到另外一个事务还没有提交的数据。

不可重复读:一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读。

幻读:一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了 "幻影"。

事务隔离级别

查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION;

设置事务隔离级别

SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | 
REPEATABLE READ | SERIALIZABLE }
注意:事务隔离级别越高,数据越安全,但是性能越低~~~~
作者:沉着的小熊猫原文地址:https://segmentfault.com/a/1190000042068602

%s 个评论

要回复文章请先登录注册