SQL基础操作

1.链接服务器

• 从命令行中连接mysql服务器的简单实例:

[root@host]# mysql -u root -p
#使用root账户直接使用密码password(没错，就是和-p之间无空格),直接登录到tutorial数据库
mysql -u root -ppassword tutorial;

• 退出 mysql> 命令提示窗口可以使用 exit 命令

mysql> exit 

• 创建用户，并给用户赋予数据库的权限

//创建用户
CREATE USER 'r&d'@'host' IDENTIFIED BY 'mysql';
//授权
grant all privileges on tutorial.*  to 'r&d'@'localhost' identified by 'xyz';

• 使用创建的用户帐号登录

mysql -u learnersql -p

2.服务器端mysql服务

• 启动：

sudo service mysql start

查看状态:

sudo service mysql status

停止服务:

sudo service mysql stop

3.基本操作

• 创建数据库

CREATE DATABASE 数据库名;

• 选择数据库

USE 数据库名;

• 操作数据表

#增加某一列
ALTER TABLE CORPORATION  ADD PROFITS FLOAT;

#删除某一列
ALTER TABLE Persons DROP COLUMN DateOfBirth;

#修改某一列的属性
ALTER TABLE person MODIFY gender ENUM('M','F');

#删除数据表
DROP TABLE table_name ;

#查看数据表中的列信息
DESC Tab_name

• 操作数据库

DROP DATABASE 数据库名称;

#查看数据库中的表信息
SHOW TABLES

• 创建数据表

#CREATE TABLE table_name (column_name column_type);
CREATE TABLE Student(
   ID   INT NOT NULL AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE  INT NOT NULL,
   //GENDER CHAR(1) CHECK (GENDER IN ('M','F'))
   GENDER ENUM('M','F')//设置约束
   // PRIMARY KEY (ID)
   CONSTRAINT pk_Student PRIMARY KEY(ID);//设置主键
);

• 创建复合主键数据表

CREATE TABLE favorite_food 
( person_id SMALLINT UNSIGNED AUTO_INCREMENT,
food VARCHAR(20),
CONSTRAINT pk_favorite_food PRIMARY KEY (person_id,food),   
CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id)
REFERENCES person (person_id)
);

• 增删改查CRUD(Create Delete Read Update)

#增
INSERT INTO Person (NAME,AGE) VALUES ('Liu Xijun',32);

#删
DELETE FROM Person WHERE ID=1;

#改
UPDATE Person SET age=31 WHERE ID=2;

#查
SELECT * FROM Person limit 1;

• 存储过程

  --- 创建存储过程
  create procedure procedureName()
  begin
  
  end
  --- 调用存储过程
  call procedureName;
  --- 删除存储过程
  drop procedure if exists procedureName;
  

• 按条件查询：过滤

  • 任意字符匹配

  符号或函数	含义
  _	正好1个字符
  %	任意个，包括0个
  RIGHT(string,3)=‘xyz’	判断string后三个字符是否为’xyz’
  LEFT(string,3) LIKE ‘A_B’	开头的三个字母是否以A开头B结尾
  col_name REGEXP '^[AB]'	支持正则表达式，是否以AB开头
  CONCAT(string1,string2)	连接两个字符串

  • NULL值.两点：两个NULL值不相等，表达式可以为NULL,但不能=NULL。判断条件：IS NULL,!=6 OR IS NULL查询防止漏查。

• 连接(Join)
  left join、right join、inner join.

• 集合操作

  • union:对要合并的查询结果进行去重合并(需一定条件,如列的类型相同等）
  • intersect: 取交集，优先级最高
  • except: A EXCEPT B，取A中除去与B相交的部分。

• 处理字符串数字或临时数据的生成转换和操作

  SQL本身无相关指令，都是数据库服务器本身内建函数。

  • 字符串类型

  类型	描述	oracle情况
  CHAR	定长字符串，CHAR(30)	最大2000字节
  VARCHAR	变长字符串	4000字节
  TEXT/CLOB	大长度的变长字符串	128TB的文档

  VARCHAR:超出设置的长度将报错，服务器按需分配，不会因设置最大的字符串长度而浪费资源。

  • 查看SQL服务器工作模式

  SELECT @@session.sql_mode
  

  • 设置mode

  SET sql_mode='ansi'
  

  • 单引号转义

  ‘It's cool!' ---> 'It''s cool'
  

  • 对查询结果进行转义

  quote(column_name)
  

  • 根据ASCII码数值生成字符

  SELECT CHAR(65,66)//A,B
  

  • 连接字符串

  SELECT CONCAT('A',CHAR(66),'c')//ABC
  

  • 根据字符值查ASCII码值

  SELECT ASCII('A')//65
  

  • 查看字符串长度

  SELECT LENGTH('ABCD');//4
  

  • 查看子字符串在原始字符串中的位置

  SELECT POSITION('R'IN'STRING');//3,从1开始计位，Oracle中使用instr()函数
  

  • 在字符串中插入替换字符串

  #insert
  select insert('this is fine.',5,0,' story');//this story is fine.
  select insert('this is fine.',5,1,',story,');//this,story,is fine.
  //四个参数:原始字符串，起始位置，替换长度，要插入的字符串
  # replace,mysql和oracle都支持
  select replace('good world!','good','hello');//hello world!
  select substring('morning',2,3);//orn,string,pos,length
  

• 数值类型的常用操作

函数名	描述	支持
Acos(x)	反cos
Asin(x)	反sin
Atan(x)	反tan
Cos(x)
Sin(x)
Tan(x)
Cot(x)	正切的倒数
Exp(x)	自然数的x次方
Ln(x)	以自然数为底x的对数
Sqrt(x)	平方根
MOD(4,2)	2,求余数	Mysql中可对小数取余
POW(2,4)	16,x的y次方
—精度控制—	—精度控制—	—精度控制—
CEIL(1.2)	2,向上取正
FLOOR（1.2）	1,向下取正
ROUND(2.5)	3，四舍五入
ROUND(2,584,2)	2.58,小数后保留2位小数
TRUNCATE(2.1234,2)	2.12,保留2位非四舍五入
ROUND和TRUNCATE	第2个参数可为负，小数点左侧截几位
SIGN(x)	正1零0负-1
ABS(x)	绝对值

• 日期类型数据操作

  • 常用时区设置

  //Mysql查看时区设置
  select @global.time_zone, @@session.time_zone;
  

  • 常用的日期时间格式

  类型	默认格式
  Date	YYYY-MM-DD
  Datetime	YYYY-MM-DD HH:MI:SS
  Timestamp	YYYY-MM-DD HH:MI:SS
  Time	HHH:MI:SS

  • 常用函数

  >>SELECT CAST('2019-09-12' AS DATE) date_field, CAST('108:17:57' AS TIME) time_field;//将字符串转换成对应的日期类型Date,Time;
  //Mysql中cast函数对数据分隔符要求宽松，/，-等都OK
  >>>SELECT CAST('123499' AS UNSIGNED INTEGER);//123499
  //将字符串转换为数字
  //str_to_date,根据个是字符串的返回，Date\Time类型
  //cast函数不支持的字符串转换成日期函数
  >>>SELECT STR_TO_DATE('September 17, 2018','%M %d,%Y');
  //系统内建函数返回当前日期或时间的字符串
  mysql> SELECT CURRENT_DATE() date,CURRENT_TIME() time,CURRENT_TIMESTAMP timestamp;
  //date_add,为指定的日期增加一段时间（如年月日),并返回新的日期
  >>>SELECT DATE_ADD(CURRENT_DATE(),INTERVAL 5 DAY);
  //last_day取当月最后一天
  >>>select last_day('2019-10-23')//2019-10-31
  //dayname函数返回日期是星期几
  >>>SELECT DAYNAME('2019-10-27');//Sunday
  //extract从日期字符串中提取感兴趣的时间间隔类型
  >>>SELECT EXTRACT(YEAR FROM '2019-10-28 14:26:54');//2019
  //获取时间间隔
  >>>select datediff('2019-01-21','2019-10-27');279
  

• 聚合和分组

  • 分组与筛选

  //对查询结果进行分组,因分组还未创建不能使用where进行筛选
  select person_id from favorite_food group by person_id having count(person_id)>3;
  

  • 常用聚合函数

  函数	描述
  MAX()	返回集合中的最大值
  MIN()	返回集合中的最小值
  AVG	返回集合中的平均值
  SUM	返回集合中所有值的和
  COUNT	返回集合中值的个数

  • 隐式分组：包含查询的所有行

  • 显示分组：使用聚集函数时，显示使用GROUP BY 进行分组，在分组上使用聚集函数。

  • 只计数某个列的不同值

  select count(distinct(person_id)) from favorite_food;
  

  • 使用表达式作为聚集函数参数

  select avg(person_id-1) from favorite_food;
  

  • 聚集函数对Null值的处理：COUNT(valaue),MAX等忽视null值，COUNT(*)会加1,因统计的是表格中的行数;
  • group by 多列

  //每年级每班有多少人，班下面还可以有小组
  select grade,class,sum(student_num) from students group by grade,class
  

  • with rollup

  //同时算出每年级每班有多少人 及每年级总共有多少人
  select grade,class,sum(student_num) from students group by grade,class with rollup;
  

  • with cube，可计算每年级总共有多少人，每个班（1,2班各个年级同班名的学生数和，假如他们都在一个班上课）有多少人。
  • <> ALL 不等于值集中的所有值,<ALL小于值集中的所有值
  • ANY与值集中的每个比较，有一个成立就为真

  select * from students
   where student_num > 
   any(
       select
       	student_num
       from students
       where student_num>14);
  

• 查询

  • 非关联子查询
    子查询独立于包含子句，可以被单独执行，并可检验结果
  • 关联子查询
    关联子查询依附于包含语句并引用其一列或多列，为每一候选行执行一次。
    • EXISTS，where exists检验子查询能否返回至少一行
    • NOT EXISTS，关联子查询返回零行
    • 关联子查询也广泛用于Delete和Update语句中。

• 子查询总结

  • 返回的结果可以是单列单行，多列多行，单列多行
  • 可以独立于包含子句，（非关联子查询)
  • 可以引用包含语句中的一行或多行，（关联子查询）
  • 可以用于条件中，包括比较运算符及in,not in,exists和not exists等
  • 可以出现在select,update,delete,insert语句中
  • 其结果集可与其他表或子查询结果连接
  • 可以用子查询结果来填充表或查询结果集中的一列
  • 可用于查询中的select\from\where\having\order by 子句

• 连接join

  • 左外连接(left outer join)仅包含第一个表的所有行，仅仅包含第二个表中那些匹配的行。
  • 使用子查询可减少使用连接的数量
  • 右外连接（RIGHT OUTER JOIN）仅包含第一表中满足条件行第二表中所有行。
  • INNER JOIN,满足条件的第1第2表中的行
  • CROSS JOIN交叉连接，结果MN行 = 第1表M行x第2表N行。
  • NATURAL JOIN(不需连接条件不推荐使用)，自然连接，依赖多表交叉时的相同列名来推断正确的连接条件。

• 条件逻辑

  • 随着遇到的数据不同，采取不同的执行方式
  • CASE表达式
    • 查找型

    CASE 
        WHEN C1 THEN E1
        WHEN C2 THEN E2
        [ELSE ED]
    END
    

    • 简单型

    CASE V0
        WHEN V1 THEN E1
        WHEN V2 THEN E2
        [ELSE ED]
    END
    

    只能判断V1与V0是否相等的条件，不如查找型强大灵活
  • 逻辑表达式常用场景
    • 结果集变换，列变行
    • 选择性聚合
    • 存在性检查
    • 除零错误
    • 有条件更新
    • NULL值处理

• 事务

  • MYSQL关闭自动提交模式，关闭后一次会话内对数据库所做的更改如不手动提交，会话结束后不会被保存。

  SET AUTOCOMMIT=0
  

  • ALTER TABLE指令将会将当前事务提交，并启动一个新事务。
  • 查看表使用的存储引擎

  SHOW TABLE STATUS LIKE TABLE_NAME \G;
  

  • 锁，数据库被并发访问时的资源控制策略，表锁页锁行锁。
  • 常用事务操作：

  --- 创建事务
  START TRANSACTION;
  UPDATE TABLE_NAME (COL_NAME) VALUES (VALUE1) WHERE;
  -- 创建保存点
  SAVEPOINT POINT_NAME1;
  DELETE FROM TABLE_NAME WHERE COND;
  ---创建保存点
  SAVEPOINT POINT_NAME2;
  --- 回滚
  ROLLBACK TO SAVEPOINT POINT_NAME1;
  --- 提交本次事务
  COMMIT;
  

• 索引

  • 如同使用索引在图书馆检索书籍，在书中检索内容一样，数据库中的索引用于定位表中的行，与普通的数据表不同，索引是一种以特定的顺序保存的专用表。不过，索引并不包含实体中的所有数据，索引保存的是用于定位某行的那些列（作为查询条件）及描述这些行的物理信息。因此，索引就是为了便捷化检索表中行和列的子集，而不用检索表中的每行。
  • 查看表中的索引

  SHOW INDEX FROM TABLE_NAME \G;
  

  • 为已存在的表添加索引

  ALTER TABLE TABLE_NAME ADD INDEX INDEX_NAME (COL_NAME);
  

  • 创建唯一索引,之后试图向表中插入该列重复的数据时会报错。

  ALTER TABLE TABLE_NAME ADD UNIQUE INDEX_NAME (COL_NAME);
  

  • 删除已存在的索引

  ALTER TABLE TABLE_NAME DROP INDEX INDEX_NAME;
  

  • 常用索引类型
    • B树索引，保持平衡，不需要查询更多层次
    • 位图索引，低基数(每列只有几个可选值)大量重复数据时适用，类似于独热编码，对应位为1其余位为0
    • 文本索引，数据库在存储文档时，允许用户在文档中查找单词或者短语。
  • 查看索引是如何使用的

  --- 查询优化器如何处理SQL语句
  EXPLAIN SELECT * FROM TABLE_NAME WHERE COOND1 \G;
  

  • 过多过少的使用索引
    • 每个索引事实上都是一个表。每次对表添加或删除数据行时，表中的所有索引都要被修改;当更新行时，受到影响的列的任何索引也必须被修改。所以，索引过多，会拖慢服务器处理任务的速度。
    • 索引占用磁盘空间;需要花费精力去管理;

• 约束

  • 常用约束类型

    • 主键约束

      标志一列或多列，并保证其值在表中的唯一性

    • 外键约束

      限制一列或多列的值必须包含在另一个表的外键中

    • 唯一约束

      限制一列或多列的值，保证其在表内的唯一性

    • 检查约束

      限制一列的可用值范围

  • 创建主键

  --- 建表时指定
  CONSTRAINT PK_NAME PRIMARY KEY (COL_NAME);
  --- 为已存在的表设置主键
  ALTER TABLE TABLE_NAME ADD CONSTRAINT PK_NAME PRIMARY KEY (COL_NAME);
  --- 删除只需要把ADD改为DROP
  

  • 创建外键

  --- 建表时指定
  CONSTRAINT FK_NAME FOREIGN KEY (COL_NAME)
  REFER ENCES PRIMARY_TABLE_NAME (COL_NAME);
  --- 为已存在的表设置外键
  ALTER TABLE TABLE_NAME ADD 
  CONSTRAINT FK_NAME FOREIGN KEY (COL_NAME)
  REFERENCES PRIMARY_TABLE_NAME (COL_NAME);
  --- ON UPDATE CASCADE 改变主表外键的值，使用此外键的子表的值也会改变
  --- ON DELETE CASCADE 级联删除，删除主表中的外键行，使用此外键的子表中的相应数据也会被删除。
  --- 删除外键
  ALTER TABLE TABLE_NAME
  DROP FOREIGN KEY FK_NAME
  

  • 外键约束MYSQL会生成索引，而Oracle不生产

• 视图

  • SQL结果集构成的虚拟表
  • 创建视图

  CREATE VIEW VIEW_NAME (
      COL_NAME1,
      COL_NAME2,
      COL_NAME3
      )
  AS 
      SELECT 
      COL_NAME1,
      COL_NAME2,
      COL_NAME3
  FROM
      TABLE_NAME;
  

  • 将已存在的视图转成表：

  CREATE TABLE TABLE_NAME
  AS 
  SELECT * FROM VIEW_NAME;
  

  • 修改已存在的视图

  CREATE OR REPLACE VIEW VIEW_NAME
  (COL_NAME1,
  COL_NAME2)
  AS ...
  

  • 使用视图的好处：
    保护数据隐私,为终端用户屏蔽复杂性，数据聚合，连接拆分分区的数据。
  • 可更新试图的条件(MYSQL)
    • 没有使用聚合函数
    • 没有使用GROUP BY 和 HAVING 子句
    • SELECT或FROM子句中不存在子查询,并且WHERE子句中的任何子查询都不引用FROM中的表;
    • 视图中没有使用UNION，UNION ALL和DISTINCT
    • FROM子句中包括不止一个表或可更新试图 且只使用内联接
    • 包含导出列的视图不能用于插入数据

• 元数据

  • 除了存储用户插入数据库中的所有数据，数据库服务器也需要创建数据库对象(表、试图、索引等)。
  • 元数据本质是关于数据的数据。
  • MYSQL通过information_schema数据库来提供元数据。
  • Oralcle中通过user_tables，all_constraints及dba_来提供元数据的信息
  • 可通过SQL查询检索模式对象相关的信息。
  • 利用元数据中存放的表，列，主键和索引信息生成创建表的语句
  • 部署验证，部署脚本运行后，查询某个数据库模式中每个表的列数索引数及主键约束数。
  • 编程语言通常包括与关系数据库连接的能力，但在语法中不包括SQL语句，SQL语句以字符串形式提交。提交字符串给数据库引擎而不是使用它的SQL接口通常被称为动态SQL执行。
  • MYSQL为执行动态SQL准备的语句

  --- SET设置变量
  SET @QRY = 'SELECT * FROM TABLE_NAME';
  --- 将sql语句提交给数据库引擎，解析安全见和优化
  PREPARE MY_DYN_SQL FROM @QRY;
  --- 执行sql语句
  EXECUTE MY_DYN_SQL;
  --- 关闭语句，释放执行中所使用的数据库资源
  DEALLOCATE PREPARE MY_DYN_SQL;