SQL 基本知识

2018-10-16  本文已影响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

约束

添加约束的方式

外键约束
可以给外键加别名，也可以不要
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;

连接方式

子查询

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

字符串函数

数值函数

日期和时间函数

流程函数

其他常用函数

事务

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

事务的四大特性（ACID）：

事务的使用

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


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

事务的并发问题

事务的隔离级别

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

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

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

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

常见权限：

-- 创建数据库用户
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的消耗。

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

索引分类

上一篇下一篇

猜你喜欢

热点阅读