MySql教程

Mysql语句(复习)

本文主要是介绍Mysql语句(复习),对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

Mysql

一、操作数据库:CRUD

  1. C(Create):创建

    • 创建数据库:

      • create database 数据库名称;
    • 创建数据库,判断不存在时,再创建:

      • create datsbase if not exists 数据库名称;
    • 创建数据库,并指定字符集:

      • create database 数据库名称 character set 字符集名称;    
    • 练习:创建db4数据库,判断是否存在,并指定字符集为gbk:

      • create database if not exists db4 character set gbk;
  2. R(Retrieve):查询

    • 查询所有数据库的名称:

      • show databases;
    • 查询某个数据库的子符集:查询某个数据库的创建语句:

      • show create database 数据库名称;
  3. U(Update):修改

    • 修改数据库的字符集:

      • alter database 数据库名称 character set 字符集名称;
  4. D(Delete):删除

    • 删除数据库:

      • drop database 数据库名称;
    • 判断数据库存在,存在再删除

      • drop database if exists 数据库名称
  5. 使用数据库

    • 查询当前正在使用的数据库名称

      • select database();
    • 使用数据库:

      • use 数据库名称

二、操作表:CRUD

  1. C(Create):创建

    1. 语法:

      create table 表名(
          列名1 数据类型1,
          列名2 数据类型2,
          列名3 数据类型3,
          .......
          列名n 数据类型n   --注意:最后一行不加逗号
      );
    2. 数据库类型:

      • int(整数类型):age int

      • double(小数类型):score double(5,2)

        • (5,2)表示小数共有5位,且保留小数点后两位

      • date(日期):只包含年月日,yyyy-MM-dd

      • datetime(日期):包含年月日、时分秒,yyyy-MM-dd HH-mm-ss

      • timestamp(时间错类型):包含年月日、时分秒,yyyy-MM-dd HH-mm-ss

        • 如果将来不给这个字段赋值,或赋值为null,则默认使用当前系统时间,自动赋值

      • varchar(字符串类型):name varchar(20) 表示姓名最大20个字符

  2. R(Retrieve):查询

    • 查询某个数据库中所有表的名称:

      • show tables;
    • 查询某个表的字符集:

      • show create table 表名称;
    • 查询表结构:

      • desc 表名称;
  3. U(Update):修改

    • 修改表名:

      • alter table 表名 rename to 新表名;
    • 修改表的字符集:

      • alter table 表名 character set 字符集名;
        
    • 添加一列:

      • alter table 表名 add 列名 数据类型;
    • 删除一列:

      • alter table 表名 drop 列名;
        
    • 修改列名称和数据类型:

      • alter table 表名称 modify 列名 新数据类型; --只修改数据类型
      • alter table 表名称 change 列名 新列名 新数据类型; --同时修改列名和数据类型
  4. D(Delete):删除

    • drop table 表名称;
    • drop table if exists 表名称;

三、DML:增删改表中的数据

  1. 添加数据:

    • insert into 表名(列名1,列名2,....,列名n) values(值1,值2,....,值n);
  2. 删除数据:

    • delete from 表名 [where 条件];
    • 注意:

      • 如果要删除所有记录:

        • delete from 表名; --不推荐使用,有多少记录就会执行多少次删除操作

        • truncate table 表名; --推荐使用,先删除张表,然后创建一张一模一样的空表

  3. 修改数据:

    • update table 表名 set 列名1=值1,列名2=值2,.....[where 条件]; 
    • 注意:

      • 如果不加任何条件,则将会把表中的数据全部修改

四、DQL:查询表中记录

  1. 语法:

    select    字段列表
    from      表名列表
    while     条件列表
    group by  分组列表
    having    分组之后的条件
    order by  排序
    limit     分页限定
  2. 基础查询:

    1. 多个字段的查询:

      • select 字段名1,字段名2.... from 表名;
    2. 去除重复:

      • select distinct 字段名 from 表名;
    3. 计算列:一般可以使用四则运算计算一些列的值(一般只会进行数值型的运算)

      • select name,math,english,ifnull(math,0)+ifnull(english,0) from 表名;
      • 注意:

        • null参与的运算,计算结果都为null

        • 解决办法:ifnull(english,0):意为如果english的值为null,则其值为0

    4. 起别名:as(也可省略,用空格代替)

      • select name,math (as) 数学,english (as) 英语,math+eglish (as) 总分 from 表名;
  3. 条件查询:

    1. 语法:where 条件

    2. 运算符:

      • between.......and......

      • in(集合)

      • is (not) null

      • and或&& or或|| not或!

      • like:模糊查询

        • 占位符:_:单个任意字符 %:零个或多个任意字符

        • select * from stu where like "马%"   --查询姓马的人的信息
          select * from stu where like "_化%"   --查询名字中第二个字是化的人
          select * from stu where like "___"   --查询名字是三个字的人
  4. 查询排序:

    1. 语法:

      • order by 排序字段1 排序方式1 , 排序字段2 排序方式2 ......
    2. 排序方式:

      • ASC:升序(默认)

      • DESC:降序

    • 注意:

      • 如果有多个排序条件,则当前面的条件值一样时,才会判断第二条件

      • select * from stu order by math ASC , english DESC;

      • 按照数学成绩升序排,如果数学成绩一样,则按照英语成绩降序排

  5. 聚合函数:

    • count:计算个数

    • max:计算最大值

    • min:计算最小值

    • sum:计算和

    • avg:计算平均数

      注意:

      • 聚合函数的计算,排除null值

      • 解决方案:1. 选择不包含空的列进行计算:主键,count(*) 2. ifnull(english,0)

  6. 分组查询:

    1. 语法:

      • group by 分组字段

  7. 分页查询:

    1. 语法:

      • limit 开始的索引 , 每页查询的条数

    2. 公式:开始的索引 = (当前页码 - 1) * 每页查询的条数

    3. eg:

      --每页显示三条记录
      select * from stu limit 0,3; --第一页
      select * from stu limit 3,3; --第二页
      select * from stu limit 6,3; --第三页

五、约束

  1. 非空约束:not null,值不能为null

    1. 创建表时添加约束:

      • create table stu(
            id int,
            name varchar(20) not null; 
        )
    2. 创建表后添加约束:

      • alter table stu modify name varchar(20) not null;
    3. 删除非空约束:

      • alter table stu modify name varchar(20);
  2. 唯一约束:unique,值不能重复

    1. 创建表时添加约束:略

    2. 创建表后添加唯一约束:

      • alter table stu modify phone_num varchar(20) unique;
    3. 删除唯一约束:

      • alter table stu drop index phone_num;

      注意:MySQL中,唯一约束限定的列的值可以有多个null值

  3. 主键约束:primary key,非空且唯一

    1. 创建表时添加约束:略

    2. 创建表后添加约束:

      • alter table stu modify id int primary key;
    3. 删除主键约束:

      • alter table stu drop peimary key;
    4. 自动增长:

      1. 在创建表时添加主键约束,并完成主键自动增长:略

      2. 在创建表后添加主键自动增长:

        • alter table stu modify id int auto_increment;
      3. 删除自动增长:

        • alter table stu modify id int; --这样并不会删除主键,删除主键用drop
  4. 外键约束:foreign key,让表与表产生关联,从而保证数据的正确性

    1. 在创建表时添加外键:

      create table 表名(
          .....
          外键列
          constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
      );
    2. 创建表后添加外键:

      • alter table employee add constraint emp_dep_id foreign key dep_id references department(id); 
    3. 删除外键:

      • alter table employee drop foreign key emp_dep_id;
    4. 级联操作:

      • alter table employee add constraint emp_dep_id foreign key (dep_id) references department(id) on update cascade on delete cascade;
      • on update cascade:级联更新:如果更新主表列名称,则随机更新外键列名称

      • on delete cascade:级联删除:如果删除主表列名称,则随机删除外键列名称

六、多表查询

  1. 内连接查询:

    1. 隐式内连接:使用where条件消除无用语句

      • select
            t1.name,  --员工表的名字
            t1.gender, --员工表的性别
            t2.name  -部门表的名字
        from
            emp t1,
            dept t2
        where 
            t1.'dept_id' = t2.'id';
    2. 显式内连接

      • select * from emp [inner] join dept on emp.'dept_id' = dept.'id';
  2. 外连接查询:

    1. 左外连接:

      • select 字段列表 from 表名1 left [outer] join 表名2 on 条件;
        -- 查询的是左表所有数据以及交集的部分
    2. 右外连接:

      • select 字段列表 from 表名1 right [outer] join 表名2 on 条件;
        -- 查询的是右表所有数据以及交集的部分
  3. 子查询:

    1. 子查询的结果是单行单列的:

      select avg(salary) from emp;           --3000
      select * from emp where salary < 3000;
      结合:
      select * from emp where salary < (select avg(salary) from emp);
    2. 子查询的结果是多行单列的:

      select id from dept where name='财务部' or name='市场部';    --2,3
      select * from emp where dept_id = 2 or dept_id = 3;
      结合:
      select * from emp where dept_id = (select id from dept where name='财务部' or name='市场部');
    3. 子查询的结果是多行多列的:

      • -- 子查询可以作为一张虚拟表

        -- 查询员工入职日期是2011-11-11之后的员工信息和部门信息
        ​
        -- 子查询:
        select * from  dept t1,(select * from emp where emp.'join_date' > '2011-11-11') t2 where t1.'id' = t2.'dept_id';
        ​
        --内连接:
        select * from emp t1,dept t2 where t1.'dept_id' = t2.'id' and emp.'join_date' > '2011-11-11';

七、事务

  1. 事务的基本介绍:

    1. 概念:

      • 如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败

    2. 操作:

      1. 开启事务:start transaction

      2. 回滚:rollback(发现出现了问题,回滚事务)

      3. 提交:commit(发现执行没有问题,提交事务)

    3. 事务提交的两种方式:

      1. 自动提交:

        • MySQL数据库默认是自动提交的,一条DML(增删改)语句会自动提交一次事务

      2. 手动提交:

        • Oracle数据库默认是手动提交的,需要先开启事务再手动提交

      3. 修改事务的默认提交方式:

        • 查看事务的默认提交方式:

          • select @@autocommit; --1代表自动提交 ,二代表手动提交

        • 修改默认提交方式:

          • set @@autocommit = 0;

  2. 事务的四大特征:

    1. 原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败

    2. 持久性:当事务提交或回滚后,数据库会持久化地保存数据

    3. 隔离性:多个事物之间,相互独立

    4. 一致性:事务操作后,数据总量不变

八、数据库的备份与还原

  1. 备份:mysqldump -u用户名 -p密码 数据库名称 > 保存的路径

  2. 还原:登录数据库 > 创键数据库 > 使用数据库 > 执行文件 (source文件路径)

九、DCL

  1. 管理用户:

    1. 添加用户:

      • create user '用户名'@'主机名' identified by '密码';
    2. 删除用户:

      • drop user '用户名'@'主机名';
    3. 查询用户:

      • 1. 切换到MySQL数据库:use mysql;
        2. 查询user表:select * from user;
    4. 修改用户密码:

      • update user set password = password('新密码') where user = '用户名';
        set passwprd for '用户名'@'主机名' = password('新密码');
  2. 权限:

    1. 查询权限:

      • show grants for '用户名'@'主机名';
    2. 授予权限:

      • grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
    3. 撤销权限

      • revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

这篇关于Mysql语句(复习)的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!