Mysql基础

@

目录

Mysql基础

什么是SQL

SQL(Structured Query Language),结构化查询语言,是一种数据库查询和程序设计语言.

SQL分类

  1. 数据定义语言(DDL Data Definition Language):创建、修改、或删除数据库中表、视图、索引等对象的操作。常用命令为：create , alter , drop
  2. 数据查询语言(DQL Data Query Languanage):按照指定的组合、条件表达式或排序 检索已存在的数据库中数据，不改变数据库中的数据，常用命令为select
  3. 数据操纵语言(Data Manipulation Language):向表中添加、删除、修改数据操作。常用命令有insert，update，delete。
  4. 数据控制语言（DCL Data Control Language）：用来授予或回收访问数据库的某种特权、控制数据操纵事务的发生时间及效果、对数据库进行监视等操作，常用命令有：GRANT，REVOKE，Commit、ROLLBACK

DDL语言

DDL操作数据库

  • create database databaseName [character set]:创建数据库，可使用character set 指定字符集编码
  • show databases：查看所有数据库
  • show create database databaseName：查看指定数据库信息
  • alter database databaseName character set utf8：设置数据库字符编码为utf-8
  • use databaseName：切换数据库
  • select database()：查看正在使用的数据库

DDL操作表

创建表格

语法：

    create table table_name(
        column_name data_type [default default_value][column constraint][comment 'comment_content'],
    ......
    [table_constraint]
    )[engine = engine_type][default charset=charset_type];

说明:
1. table_name：表名
2. column_name：字段名
3. data_type:字段类型
4. default default_value:设置默认值
5. column constraint:设置字段级约束
6. comment comment_content:设置注释
7. table_constraint:设置表级约束
8. engine:设置存储引擎
9. charset:设置字符编码

修改表格

  1. 添加字段
    alter table table_name 
    add column_name data_type 
    [default default_value]
    [column_constraint]
    [comment 'comment_content']
    [after 字段名]
  1. 修改字段
    alter table table_name
    modify column_name data_type [default default_value]
  • 注意:
    1. 字段的修改包括数据类型,大小和默认值,默认值只会影响后来插入的数据,不影响之前的数据
    2. 不能修改字段约束,字段先后顺序和注释
  1. 删除字段
    alter table table_name
    drop column_name
  • 注意:
    1. 一个表至少要保留一个字段
    2. 如果删除列是另一个表的外键,则该列无法删除
  1. 重命名表格
    rename table old to new
  2. 截断表
    truncate table table_name
  • 注意
    1. 截断表后,表中数据也一并删除
    2. drop不仅用于删除表,表中保存的数据也一并删除
  1. 删除表
    drop table table_name

DML

添加数据

数据添加有两种方式：

  1. insert into table_name [column_name1,column_name2...] values(value1, value2,...)
    • 字段列表顺序可以不按表中列的顺徐,但value必须要一一对应
    • 当字段列表不写时,则对于values里面的值必须和表中的列一一对应
    • 如果字段值是字符或者日期类型,则需要用单引号引起来
  2.  insert into table_name[column_name1, column_name2,...]
 select column_name1, column_name2...from table_name
    • insert子句中字段名列表的数目和数据类型必须和select子句中选择的字段列表的数目和数据类型相匹配。数据类型不考虑长度，但是须比查出来的对应列的长度长或相等。
    • select查询字段名可以和insert插入表对应字段名不一致

修改数据

    update table_name 
    set column1 = value1, column2=value2...
    [where]
  • 如不指定where,则会修改所有数据

删除数据

delete from table_name [where ...]

DCL之事务

什么是事务

  • Transaction（事务），一个最小的不可再分工作单元，通常对应一个完整得的业务，而这些业务需要一批DML语句共同完成，不能分开

Mysql中的事务

数据库表中,对于表中数据的修改、添加、删除，均属于DML（数据操纵语言），这类的SQL语句均需要执行commit，如果撤销则需要执行rollback。在mysql中，执行DML语句后，会默认自动执行commit操作，因此，当遇到多个DML语句构成的事务时，就必须通过set autocommit=0手动关闭自动提交，而后使得多个DML操作同时成功或失败，当失败后，使用rollback回滚到保存点或者上一次commit的位置。这样，才能确保事务的原子性不变。

事务的周期

  • 开始：执行第一个DML语句时，事务开始
  • 结束：
    • 第一种是执行commit或者rollback语句，当前事务结束
    • 另外一种是执行DDL语句或者DCL语句，事务结束
    • 数据库客户端程序退出或者数据库崩溃时，为了保障数据的一致性，也会结束事务

事务执行期间的数据变化

  1. 执行DML语句且事务没结束，如果需查看数据库中的真实情况（就是存储在硬盘中的数据），需要新建一个查询窗口，在新窗口中查询。这是由于事务在提交前，都是保存在缓存区中，以便rollback。所以，当你在当前窗口select到的都是缓冲区的数据，而非数据库中的数据。只有commit之后，数据才被写入数据库，此时也不能rollback到之前的数据了。
  2. 当一个用户执行DML语句时，如果其事务没有结束，则受影响的行被锁定，其他用户这个时候就不能改变受影响的行的数据，直到事务结束。

事务的四大特性（ACID）

  • 原子性（Atomicity）：事务是一个不可分割的整体，事务开始之后，要么全部成功，要么全部失败，不可能部分成功，部分失败。如果失败只会回滚到开始之前的状态。
  • 一致性（Consistency）：事务必须使数据库从一个一致性状态转换到另一个一致性状态，即一个事务执行之前和执行之后必须处于一致性状态。
    • 这个不好理解，拿转账来说，用户A和用户B他们的钱加起来一共5000，那么，不管A与B之前怎么转账，事务结束之后他们的钱加起来仍然为5000，这就是事务的一致性。
  • 隔离性（Isolation）：多个用户并发访问数据库时，不能被其他事务干扰，多个并发事务之间要相互隔离。
  • 持久性（Durability）：一个事务一旦被提交，对数据库的所有更新将被保存到数据库中，不能回滚。

约束

  • Mysql数据库通过约束(constraint)防止无效的数据进入到表中,以保护数据的实体完整性
  • 约束分为字段级别约束和表级别约束
    • 字段级约束:只为单个字段添加约束
    • 表级约束:只为一个或多个字段添加约束
  • 常见约束:
    • primary key:主键约束,约束字段唯一,非空
    • not null:非空
    • unique:指定字段值必须唯一,unique约束允许输入null,且可以接受多个null
    • auto_increment:自动增长,类型必须为整数类型,且必须为主键
    • check:mysql不支持check约束
    • foreign key：外键约束

DQL数据查询语言

基本语法格式

select ... from ... [where...][group by...][having...][order by...]

Mysql中查询的执行过程

  1. 查询表肯定是从表开始,所以先执行**from**
  2. where:筛选出有效数据
  3. group by:对数据进行分组
  4. 对分组后的数据进行统计,如sum,count
  5. having:对统计后的结果进行筛选
  6. 使用select选择显示的结果
  7. order by:排序

where

where主要是对表中的数据进行筛选和约束,得到我们想要操作的数据,主要使用的条件表达式如下:
1. 关系表达式:>,>=, <,<=, =,!=, <>,<=>(对于null也能使用的等号)
2. 多条件筛选: 可以使用and,or,between...and....
3. 集合操作:in, not in
4. 模糊查询:like
- 通配符%:匹配0个或者多个字符
- 占位符_:匹配一个字符
- 转义:如需要匹配%,_这种已经有特殊含义的字符,可以使用\进行转义,like '%\%%'匹配字段中含有%的字段,还可以使用escape关键字指定转义字符,例如like '%A%%' escape 'A'同样可以达到上面的效果。

group by

主要是对被查询的表格分组,例如我们要统计学生表中男女的人数,就可以对性别进行分组,select count(id) from student group by sex。
所有组函数操作的数据都是以组为单位的,如若省略group by,则整个表默认为一组,使用group by进行分组后,组函数的操作就是对分组后的数据进行统计

having

对分组后的数据再次过滤，用法类似于where

order by

对最终的结果排序输出，默认升序（ASC），DESC为降序

函数

数据库种的函数，与Java种方法类似，都是指完成一定功能的代码的集合。根据函数操作数据行，可以将SQL函数分为单行函数和多行函数

单行函数

单行函数是指仅对单条数据种的列进行操作，并返回一个结果。

  • length(column_name|str):返回存储长度
  • char_length(column_name|str):返回字符个数
  • contact(column_name1|str1,column_name2...):连接多个字符串
  • contact_ws(separator,column...):连接多个字符串,中间用separator隔开
  • trim([{both|leading|trailing}[remstr] from]str):返回去掉str源字符串两端、前缀或者后缀字符串remstr；不指定both、leading、trailling则默认为both，不指定remstr，则去掉两端的空格
    • select trim(both 's' from ' sdsds')：结果为' sdsd'只去除右端的s，因为左端不是s开始，而是空格，中间的s也不去除
  • substr(str,pos,[len]):从指定pos位置截取字符串,下标从1开始
  • replace(str,from_str,to_str):将源字符串str种所有from_str,替换为to_str并返回替换后的字符串
  • reverse(str):反转字符串
  • strcmp(expr1,expr2):字符串比较,大于返回1,小于返回-1,等于返回0
  • truncate(x,y):返回x截断后的结果,该值有y位小数
  • now():获取当前日期+时间
  • date_format(date,format):格式化日期
  • datediff(date1,date2):返回日期相隔天数
  • convert(value,type):将value转换为type类型,type可以是:char,date,time,datetime,signed(整型),decimal(浮点型)
  • if(expr1,expr2,expr3):expr1为true,返回expr2,否则返回expr3
  • ifnull(expr1,expr2):expr1为null,返回expr1,否则返回expr2

多行函数

多行函数可以操作成组的多条数据，每组返回一个结果，所以又称为组函数。

常用组函数

  • sum():求和
  • count():计数
  • avg():平均值
  • max(),min():最大/小值
  • stddev():标准差
  • variance():方差
posted @ 2021-03-31 21:11  Venking-  阅读(82)  评论(0)    收藏  举报