SQL 基本知识

作者: 微笑中的你 | 来源:发表于2018-10-16 15:49 被阅读0次

SQL 是结构化查询语言

常见的关系型数据库（RDBMS）：Oracle、DB2、MySql、SQL Server
常见的非关系型数据库： MongoDB、Redis、

create database db_name; 创建一个数据库
drop database db_name; 删除一个数据库
show databases; 查看有多少数据库
use db_name; 使用数据库

数据库关联表的集合
表类
列实体类的字段值
行一条记录
主键唯一标识
外键用于关联两个表
索引索引可快速定位

DDL 数据定义语言定义数据库，创建表等
DML 数据操作语言用来操作表中的数据
DQL 数据查询语言用来查询数据
DCL 数据控制语言用来定义访问权限和安全级别

字段的类型，对数据库优化也很重要。

创建表

create table lxf_student (
    id int,
    name varchar(20),
    age int,
    email varchar(20),
    score int
)

插入记录

插入一条完整记录
insert into lxf_student 
values (3, '张三', 20, 'phonegg@foxmail.com', 100 )

批量插入
insert into lxf_student (name, age)
values  ('张三', 20),
        ('李四',18)

更新记录

修改所有分数为50
update lxf_student
set score = 50;

修改李四的分数为100
update lxf_student
set score = 100
where name='李四';

设置李四年龄长一岁
update lxf_student
set age = age + 1
where name='李四';

更改数据库用户权限

修改数据库密码
update user set authentication_string=password('123456')
where user='root';
刷新系统表
flush privileges;

删除记录

删除指定记录
delete from lxf_student;
where id=1

删除所有记录
delete from lxf_student;

删除所有记录
truncate table lxf_student;

查询

查询所有字段
select *
from lxf_student;

查询指定字段
select name, age
from lxf_student;

限定查询
between A and B  某字段值在A和B之间的所有满足条件记录，可以为时间和数值
in (A, B....)  某字段值等于A或者等于B或者等于...的所有满足条件记录
is Null / is Not Null 某字段值为空/不为空
or  name='lisi' or age=18  满足name=lisi或者age=18的所有记录
and  name='lisi' and age=18 满足名字为lisi并且年龄为18的所有记录
Not  非

模糊查询

_下划线表示任意一个字符
select name, age
from lxf_student
where name like '___'


% 百分号便是任意多字符
select name, age
from lxf_student
where name like '%李%'

distinct 去除查询结果集中相同的记录。

查询字段值为null时

将结果集中age值为null的改为0，表中值仍为null
select distinct name, ifnull(age,0) as age
from lxf_student;

聚合函数

  count(字段) 统计个数
  sum(字段) 求和
  avg(字段) 求平均数
   max(字段) 求最大值
   min(字段) 求最小值

分组 group by

//sum查询每个部分发了多少工资
 select sum(工资) as 部门总工资  group by 部门

//group concat将每个部门的员工工资放到一个字段中，工资以逗号分隔
 select group concat(工资)   group by 部门

聚合函数 + group by
是对分组后的结果，进行聚合

group by + having..
是对分组后进行筛选

聚合行数+ group by + having
对分组后，聚合后，进行筛选

order by 字段按什么排序 DESC 降序，ASC升序（默认，ASSIC 码大的在查询结果结果列表下面）。

limit 数值限制前多少条 limit 3 limit 3,3 从第四条数据开始获取3条数据（第一个3代表下表）

sql 语句顺序

select > from > where > group by > having > order by

约束

主键 primary key 数据唯一，且不能为空，每个表要有一个主键
唯一约束 unique 数据唯一，但是可以为空
自动增长列 auto_increment 注意带有下划线
外键 foreign key 外键必须是另一个表的主键。
为空 / 不为空 null / not null
值范围约束 check （创建表时，在最后添加这个约束。例如年龄必须大于等于0：check(age >= 0)）
默认值 default (在字段后面添加 age int default 0)
create index index_name 创建索引
create unique index index_name 创建唯一索引

添加约束的方式

在创建表时添加约束 create table table_name (id int primary key)
在创建表之后，修改添加约束: alter table table_name add constraint primary key

外键约束

可以给外键加别名，也可以不要
create table t_1(
  id int primary key,
  name varchar(30),
  age int
);
create table t_2 (
  sid int,
  score int,
  constraint fk_name foreign key(sid) references t2(id)
);

//删除该约束
ALTER TABLE t_2 DROP FOREIGN KEY fk_name;
//修改表 添加约束
alter table t_2 add constraint fk_name foreign key(sid) references t_2(id);

表与表的关系

一对一
一对多
多对多（需要创建一个中间关系表，减少数据冗余）

多表查询

合并结果集
连接查询
子查询
自连接

合并结果集

就是把连个select语句查询的结果合并。
方式：* union 合并时去除了重复记录 select * from a union select * from b
* union all 合并时不去除重复记录 select * from a union all select * from b

连接查询也叫跨表查询

笛卡儿积？如 A{a,b} B{1,2,3}, 那么笛卡儿积为：{(a,1),(a,2),(b,1),(b,2),(b,3)}.
同时查询两张表（有关系的表），出现的就是笛卡儿积。

那么多表查询怎么保证数据正确性？查询时主外键保持一致。
select * from student as a , score as b where a.id = b.sid;

连接方式

内链接
1、等值连接
select * from A as a inner join B as b on a.id = b.aid;
2、非等值连接
3、自连接
外连接
**1、左外连接 left outer join 可简写为 left join (左边表符合条件的全部，右边取出满足on条件的)
**2、右外连接 right outer join 可简写为 right join (右边表符合条件的全部，右边取出满足on条件的)
自然连接 select * from table_a natural join table_b 查询相同字段值相等的记录。

子查询

什么是子查询，就是select 中包含select，where 后或者 from 后面
** where后面后面查询的结果作为前面查询的条件
** from 后面把查询出的结果作为一个新表

字符串函数

concat(...) 将多个字段连接
insert(str,i,len,in) 将str字符中第i位置开始长度为len的字符串替换为in
left(str,n) , right(str, n) 返回左边n个字符，返回右边n个字符
LPAD（str,len,s）, RPAD(str,len,s)
SELECT LPAD("my",9,'ab'); 结果为abababamy
SELECT LPAD("my",3,'abcd'); 结果为amy
SELECT RPAD("my",9,'ab');结果为myabababa
SELECT RPAD("my",3,'abcd');结果为mya
LTRIM（s）/ RTRIM（s）去除s左边/右边空格
TRIM 去除左右两边的空格
REPEAT（s,n）将s重复n次
REPLACE(s,c,a) 将字符串s中c全部替换为a
SUBSTR(s,i,len) 从s中第i个位置截取len个字符

数值函数

ABS(x) 返回x的绝对值
CEIL(x) 向上取整。如3.3返回4
FLOOR(x) 向下取整。如3.7 返回3
MOD(x,y)取模返回x/y.
RAND() 返回0-1之间的小数

日期和时间函数

CURDATE() 返回当前日期：年月日
CURTIME() 返回当前时间: 时分秒
NOW() 返回当前日期和时间
UNIX_TIMESTAMP 返回时间戳
FROM_UNIXTIME(t) 将时间戳转换为日期
WEEK(date) 返回周值
YEAR(date) 返回年值
HOUR(time) 返回小时值
MINUTE(time) 返回分钟值
DATE_FORMAT(date,format) 格式化时间为fromat形式
DATE_ADD(date, INTERVAL n X) n可以为任意整数，X可以为year, day, month,week 时间的加减
DATEDIFF('2018-11-01', NOW()) 和当前时间相差多少天

流程函数

IF(v,a,b) 如果v为真，返回 a,否则返回b
IFNULL(f,a) 如果f不为null 怎返回f，否则返回a
CASE WHEN f THEN a ELSE b END 如果f为真返回a,否则返回b

其他常用函数

SELECT DATABASE(); 返回当前数据库名称
VERSION(); 返回当前mysql版本
USER(); 返回当前登录用户
PASSWORD(s); 加密
MD5(s); md5加密

事务

不可分割的操作，每条sql都是一个事务，只对 DML语句有效。

事务的四大特性（ACID）：

原子性 要么成功要么失败
一致性 数据保持一致
隔离性 隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
持久性 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的。

事务的使用

START TRANSACTION;（开始事务）
相应的增删改语句
COMMIT; (提交事务)


START TRANSACTION;
相应的增删改语句
ROLLBACK;（回滚事务）

事务的并发问题

脏读就是可以读取到事务未完成时的数据
不可重复读
重复读
幻读
对应关系

事务的隔离级别

read uncommitted 易产生脏读，不可重复读，幻读
read comitted 易产生不可重复读，幻读
repeatable read (MySQL默认) 易产生幻读
serializable （等级最高，比较消耗性能，一般不用）

-- 查看事务的隔离级别 --
SELECT @@global.tx_isolation,@@tx_isolation;

-- 设置事务的隔离级别 --
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

权限（对数据库的操作权限）

限制数据库用户能做什么，比如禁止删除数据库，防止一些人删库跑路。

常见权限：

create 创建数据库或表
drop 删除数据库或表
alter 更改表权限
delete 删除数据权限
index 索引权限
insert 插入权限
select 查询权限
update 更新权限
create view 创建视图
execute 执行存储过程

-- 创建数据库用户
CREATE USER 'tt'@'localhost' IDENTIFIED by '123456';
-- 删除数据库用户
DROP USER 'tt'@'localhost';

-- 用户权限 *.* 所有数据库的所有表，最后一行=管理其他用户
GRANT ALL PRIVILEGES ON *.* TO tt@localhost  
IDENTIFIED by '123456' 
WITH GRANT OPTION;

视图

视图是虚拟表，是select查询的结果集。

视图是基于表的，可以和表一样进行增删改查

视图作用：

安全性
查询性能提高
提高了数据的独立性

视图sql

CREATE VIEW emp_salary_view 
AS (SELECT * FROM emp WHERE emp.salary > 2000);

CREATE OR REPLACE VIEW emp_salary_view
AS (SELECT * FROM emp);



CREATE [ALGORITHM] = {UNDEFINED|MERGE|TEMPTABLE}
VIEW VIEW_NAME
AS SELECT ....语句
[WITH [CASCADED|LOCAL] CHECK OPTION];


ALGORITHM: MERGE 处理方式为替换式，更新是图片表中数据时，原表中数据也会更新。
                    TEMPTABLE 不可更改原表中的数据
                    UNDEFINED

WITH CHECK OPTION: 更新视图view中数据时，必须符合select中的where条件

LOCAL ，CASCADED(默认)


CREATE ALGORITHM = MERGE
VIEW v_emp_s
AS (SELECT emp.ename,emp.salary FROM emp );


CREATE VIEW v_emp_s
AS (SELECT * FROM emp WHERE emp.salary > 2000)
WITH CHECK OPTION;

-- 替换式
SELECT * FROM (SELECT * FROM emp WHERE emp.salary > 2000) AS t;


-- 具化式
(SELECT * FROM emp WHERE emp.salary > 2000) AS temp;
SELECT * FROM temp;



    视图不可更新：
    DISTINCT
    GROUP BY
    HAVING
    UNION
    FROM 多表
    SELECT 中引用了不可更新视图
    视图中的数据不是来自基表，就不能直接修改

存储过程

存储过程：完成特定功能sql语句集

优点：封装
批量处理
统一接口，确保数据安全

相对于Oracle来说，MySQL使用较少

DELIMITER 符号修改sql语句分割符号

创建与使用

CREATE PROCEDURE show_emp()
BEGIN
    SELECT * FROM emp;
END

CALL show_emp();


-- 查看所有存储过程
SHOW PROCEDURE STATUS;

-- 查看指定数据库存储过程
SHOW PROCEDURE STATUS WHERE db='lxf_time';

-- 查看指定存储过程
SHOW CREATE PROCEDURE show_emp;

-- 删除指定存储过程
DROP PROCEDURE show_emp;

-- 声明变量 DECLARE name type DEFAULT value;
CREATE PROCEDURE test()
BEGIN
    DECLARE res VARCHAR(50) DEFAULT '';
    DECLARE x,y INT DEFAULT 0;
    
        -- 修改默认变量值
    SET x = 3;
    SET y = 4;
    DECLARE avgRes DOUBLE DEFAULT 0;
        -- 查询值作为变量值
    SELECT AVG(salary) INTO avgRes FROM emp;

END;

CALL test();



-- 存储过程参数基表类型 IN OUT INOUT
-- IN 可接受
-- OUT 可输出
--  INOUT 可接受可输出
-- 根据传入的名称，获取对应的信息

CREATE PROCEDURE getInfoByName(in name VARCHAR(255))
BEGIN

SELECT * FROM emp WHERE ename = name;

END;

CALL getInfoByName('鲁班');

-- 通过名称，返回薪资
CREATE PROCEDURE getSalary(IN n VARCHAR(255), OUT s INT)

BEGIN
    SELECT emp.salary INTO s FROM emp WHERE ename = n;
END;

CALL getSalary('鲁班', @s);

SELECT @s;
SELECT @s FROM DUAL;


CREATE PROCEDURE test(INOUT num INT, IN inc INT)
BEGIN
    SET num = num + inc;
END;

SET @num1 = 20;
CALL test(@num1,10);
SELECT @num1;

自定义函数

-- 自定义函数

-- 随机生成一个指定个数的字符串 函数

CREATE FUNCTION randStr(n INT) RETURNS VARCHAR(255)
BEGIN
    -- 声明一个str 52个字母
    DECLARE str VARCHAR(255) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    -- 当前是第几个字符
    DECLARE i INT DEFAULT 0;
    -- 生成的结果
    DECLARE res_str VARCHAR(255) DEFAULT '';
    
    -- 使用white循环
    WHILE i < n DO
        -- 随机生成一个字符
        -- FLOOR(1+RAND()*52)
        -- SUBSTR(str,FLOOR(1+RAND()*52),1);
        -- CONCAT( res_str, SUBSTR(str,FLOOR(1+RAND()*52),1) );
        SET res_str = CONCAT( res_str, SUBSTR(str,FLOOR(1+RAND()*52),1) );
        SET i = i + 1;
    END WHILE;

    RETURN res_str;
    
END;

-- 调用
SELECT randStr(5);

使用存储过程插入随机数据


-- 使用存储过程插入千万条记录
CREATE PROCEDURE insert_qw(IN startNum INT, IN max_num INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    -- 默认情况下自动提交sql
    SET autocommit = 0; -- 设置不自动提交
    
    REPEAT
        SET i = i + 1;
        INSERT INTO qw VALUES(startNum+i,randStr(5),FLOOR(16+RAND()*30));
    UNTIL i = max_num END REPEAT;
    
    COMMIT; -- 整体提交sql
    
END;

CALL insert_qw(100, 1000000);

索引

用来快速查找。
不使用索引时，扫描整张表之后，才找到相应的记录。

优点：
提高数据检索效率，降低数据库的IO成本；
通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

缺点：
实际上索引页是一张表，该表保存了主键和索引字段，并指向了实体表的记录，索引列也是占用空间的。虽然提高了效率，但同时会降低表的更新速度，如插入，修改，删除。

索引分类

单值索引一个索引只包含单个列，一个表可以有多列索引
唯一索引索引列的值必须唯一，单允许有空
复合索引一个索引包含多个列
全文索引只有在MyISAM引擎上才能使用，只能在char varchar text字段类型上使用
空间索引是对空间数据类型的字段建立的索引