MySQL: 6、MySQL语句

1、MySQL服务在命令行中启动与关闭

  启动:net  start   mysql 

  关闭:net  stop   mysql

 

2、MySQL命令行登入

  mysql -u 用户名 -p 密码 :使用指定用户名和密码登录当前计算机中的mysql数据库

  mysql -h 主机IP -u 用户名 -p 密码: 指定ip方式进行登录      

 

3、SQL分类:

  DDL(Date Definition Language) 数据定义语言:用来定义数据库对象:数据库、表、列

  DML(Date Manipulation Language) 数据操作语言:用来对数据库中表的记录进行更新

  DQL(Date Query Language) 数据查询语言:用来查询数据库中表的记录

  DCL(Date Control Language) 数据控制语言:用来定义数据库的访问权限和安全级别及创建用户

 

4、DDL数据定义语言:操作数据库

  1.创建数据库

    create database 数据库名;  -- 创建指定名称的数据库

    create database 数据库名 character set 字符集; -- 创建指定名称的数据库,并且指定字符集(一般指定为utf8)

  2.查看/选择数据库

    use 数据库; -- 切换数据库

    select database(); -- 查看当前正在使用的数据库

    show databases; -- 查看Mysql 中都有那些数据库

    show create database 数据库名; -- 查看一个数据库的定义信息

  3.修改数据库字符集

    alter database 数据库名 character set 字符集;

  4.删除数据库

    drop database 数据库名; -- 从MySQL中永久的删除某个数据库

   

5、DDL数据定义语言:操作表

  1. 字段常用的数据库类型:

    1)常用的数据类型:

      int     整形
      double   浮点型
      varchar  字符串型,在定义时必须指定长度,否则会报错
      char    字符串型
      date          日期类型,yyyy-MM-dd , 只有年月日,没有时分秒
        datetime    日期类型,显示为 yyyy-MM-dd HH:mm:ss
    2) char 和 varchar的区别:
     char类型是固定长度的: 根据定义的字符串长度分配足够的空间
       varchar类型是可变长度的:只使用字符串长度所需要的空间
  2. 创建表:  

    create table 表名(

      字段名1   字段类型(长度),

      字段名2  字段类型      -- 注意最后一列不需要加逗号  

    )ENGINE=InnoDB DEFAULT CHARSET = utf8; -- 如果在定义数据库的时候定义了字符集这里是可以省略的

  3.查看表:

    show tables;  -- 查看当前数据库中的所有表名

    desc 表名; -- 查看数据库表的结构

    show create table  表名;  -- 查看表的字符集

  4.删除表:

    drop  table 表名; -- 删除表(从数据库中永久删除某一张表)

    drop table if exists 表名; -- 判断表是否存在,存在就删除,不存在就不删除

  5.修改表:

    rename table 旧表名 to 新表名; -- 修改表名

    alter table 表名 character set 字符集; -- 修改表的字符集

    alter table 表名 add 字段名 字段类型; -- 修改表,向表中添加新的字段

    alter table 表名 modify 字段名 字段类型(长度); -- 修改表中的字段的数据类型或者长度

    alter table 表名 change 旧字段名 新字段名 类型(长度); -- 修改字段名

    alter table 表名 drop 字段名; -- 删除字段

 

6、DML数据操作语言: 操作表中的数据 

  1.插入数据

    insert into 表名 (字段名1,字段名2...)  values(字段值1,字段值2...);

    insert into 表名 values(字段值1,字段值2...);  -- 插入全部字段,不谢字段名

    insert into 表名 (字段名) values(字段值); -- 插入指定的字段值

    ps: varchar、char 、date 类型的值必须使用单引号或者双引号包裹

       如果要插入空值,可以忽略不写或者插入null

       如果插入指定字段的值,必须要写字段名 

  2. 更改数据

    update 表名 set  列名 = 值; -- 不加条件会将所有列名都该为该值

    update 表名 set  列名 = 值  where 条件表达式 : 字段名 = 值  ;  -- 按条件删除列名 

  3.删除数据 

    delete from 表名; -- 删除所有数据

    delete from 表名 where 字段名 = 值; -- 跟进条件删除数据 

    删除表中所有数据有两种方法:

    delete from 表名; --》 有多少条记录就执行多少次删除操作,效率低
    truncate table 表名; --》 先删除整张表,然后再重新创建一张一模一样的表,效率高 

 

7、DQL数据查询语言: 查询表中的数据 

   1. 简单查询

    select  列名 from 表名; -- 普通查询

    select  列名 as 'XX' from 表名; -- 别名查询

    select distinct 列名 from 表名; -- 去重查询

    select  ename,salary + 1000 from emp; -- 运算查询

  2. 条件查询

    select  列名 from  表名 where 条件表达式;

  3. 条件查询用到的运算符:

    1.比较运算符:

       

    2. 逻辑运算符:

     

 

 

     3. 模糊查询 :

      

 

 

 

8、DQL数据查询语言:操作表单 

  1、 聚合函数:

    SELECT 聚合函数(字段名) FROM 表名;

      常用的五个聚合函数:

      count(字段): 统计指定列不为null的记录记录行数

      sum(字段):计算指定列的数值和

    max(字段):计算指定列的最大值

    min(字段):计算指定列的最小值

    avg(字段):计算指定列的平均值

    ps: 统计表中的总记录数的多种方式:    

        select count(eid) from emp;   -- 使用某个字段

        select count(*) from emp;    -- 使用 *

        select count(1) from emp;    --  使用1 与 * 的效果相同

  2、排序 : ORDER BY 关键字

    单列排序  : 只按照某一个字段进行排序

      SELECT 字段名 FROM 表名 [ WHERE 字段 = 值 ] ORDER BY 字段名 [ ASC/DESC];

    组合排序:同时对多个字段进行排序,如果第一个字段相同就按照第二个字段进行排序

      SELECT 字段名 FROM 表名 [ WHERE 字段 = 值 ] ORDER BY 字段名1 [ ASC/DESC],字段名2 [ ASC/DESC];        

  3、分组查询:GROUP BY 关键字

      SELECT 分组字段/聚合函数 FROM 表名 GROUP BY 分组字段 [HAVING 条件];

    4、where 与 having的区别:

      where 进行分组前的过来,where 后面不能写聚合函数

      having进行分组后的过滤,having后面可以写聚合函数

  5、limit关键字 

      limint是限制的意思,用于限制返回的查询结果的行数,limit语法是mysql的方言,用来完成分页

      格式: SELECT 字段1,字段2... FROM 表名 LIMIT  offset,length;

      limint offset,length;关键字可以接受一个 或者 两个为0或者正整数的参数

      offset 起始行数,从0开始计数,如果省略则默认为0

      length 返回的行数

 

9、DCL数据控制语言:用来定义数据库的访问权限和安全级别,及创建用户

  1、创建用户

    格式: CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';   

    案例:

      1)创建 admin1 用户,只能在 localhost 这个服务器登录 mysql 服务器,密码为 123456

       CREATE USER 'admin1'@'localhost' IDENTIFIED BY '123456';

      2) 创建 admin2 用户可以在任何电脑上登录 mysql 服务器,密码为 123456

       CREATE USER 'admin2'@'%' IDENTIFIED BY '123456';

          ps: % 表示 用户可以在任意电脑登录 mysql服务器.

  

  2、用户授权

    格式:GRANT 权限 1, 权限 2... ON 数据库名.表名 TO '用户名'@'主机名';

    

    

     案例:

      1) 给 admin1 用户分配对 db4 数据库中 products 表的 操作权限:查询

         GRANT SELECT ON db4.products TO 'admin1'@'localhost';

      2) 给 admin2 用户分配所有权限,对所有数据库的所有表

         GRANT ALL ON *.* TO 'admin2'@'%';

  

   3、查看权限

    格式:

      SHOW GRANTS FOR '用户名'@'主机名';

    案例:

      SHOW GRANTS FOR 'root'@'localhost';

      

 

    

    4、删除用户:DROP USER '用户名'@'主机名';

      -- 删除 admin1 用户

      DROP USER 'admin1'@'localhost';

 

    5、查询用户

     选择名为 mysql的数据库, 直接查询 user表即可

     SELECT * FROM USER;

 

10、SQL约束  

  1、常见的约束 

    主键      primary key     不可以重复、唯一、非空        

    唯一    unique        某一列不允许为空

    非空    not null       不允许为空

    外键    foreign key      设置外键

    默认值约束   default        设置默认值

约束创建及删除的各种方式

  2、主键约束和唯一约束的区别:

    主键约束唯一并且不能为空,唯一约束唯一但是可以为空

    一个表中只能有一个主键,但是可以有多个唯一约束

  3、主键自增

    使用auto_increment 表示自动增长(字段类型必须是整数类型)

    默认地auto_increment 的开始值是1,但是可以通过下面这种方式设置

    方式1:

      CREATE TABLE emp(

       eid INT PRIMARY KEY

    )AUTO_INCREMENT = 100 ;

    方式2:

    ALTER  TABLE 表名 AUTO_INCREMENT = 100;

    ps: 这里需要注意的是,如果一开始你设置了主键自增的值,如果在采用方式2的话,

    方式2设置的主键自增的值必须比之前的大,否着无效

    

  3.1 delete和truncate删除表中所有数据对自增长的影响

     delete 只是删除表中所有的数据,对自增没有影响

     truncate 是将整个表删除掉,然后创建一个新的表,自增的主键从1开始

 

11、数据库事务 

  1、MySQL两种方式进行事务提交操作:

    - 自动提交事务

      mysql 默认每条 DML(增删改)语句都是一个单独的事务,每条语句都会自动开启一个事务

      语句执行完毕自动提交事务,mysql默认开启自动提交事务

    - 手动提交事务

     开启事务: start transaction 或者 begin

     提交事务: commit

     回滚事务: rollback

      - 查看提交状态

     show variables like 'autocommit';

    - 修改提交状态

     set @@autocommit = off; 

     on: 自动提交  off:手动提交

  2、事务的四大特性: ACID

    原子性: 每个事务都是一个整体,不可再拆分,事务中所有的sql语句要么都执行成功,要么都执行失败

    一致性:事务在执行前数据库的状态与执行后数据库的状态要保持一直,如:转账前2个人总金额是2000,

        那么转账后2个人总金额也是2000

    隔离性: 事务与事务之间不应该相互影响,执行时保持隔离的状态

    持久性: 一旦事务执行成功,对数据库的修改是持久的,就算计算机关闭,数据也是要保存下来的

  3、数据并发访问会产生的问题

     一个数据库有可能拥有多个访问客户端,这些客户端都可以并发方式访问数据库,数据库的相同数据可

    被多个事务同时访问,如果不采取隔离措施,就会导致各种问题,破坏数据的完整性。

     并发访问会产生的问题:

    脏读:一个事务读取到另一个事务中尚未提交的数据

    不可重复读:一个事务中两次读取的数据内容不一致

         例如事务a读取一个数据2次, 此时事务b也访问该数据,并且在事务a两次读取数据之间进行了修改

         导致事务a在多次读取到的数据不一样。这种就叫做不可重复读

    幻读:幻读是指同一个事务内多次查询返回的结果集不一样,例如事务a对表中的数据进行修改,这种修改涉及到表

       中的全部数据。同时,第二个事务也修改了这个表中的数据,这种修改是向表中插入一行新的数据,那么,

       操作第一个事务的用户就会发现表中还有没有修改的数据行,就好像发生了幻觉一样,这就叫做幻读  

    不可重复读和幻读的区别:

       不可重复读的重点是修改,同样的条件,你读取过的数据,再次读取出来发现值不一样

       幻读的重点在于新增或者删除,同样的条件,第一次喝第二次读取出来的记录数不一样

  4、事务的四种隔离级别

    

 

 

    注意: serializable串行化可以彻底解决幻读,但是事务只能排队执行,严重影响效率,数据库不会使用这种隔离级别

    查看隔离级别:

      select  @@tx_isolation;

     设置事务隔离级别,需要退出mysql重新登录后才能看到隔离级别的变化

      set global transaction isolation level 级别名称;

 

12、多表

  1、创建外键约束

    1)新建表时添加外键

    CREATE TABLE employee(

      eid INT PRIMARY KEY AUTO_INCREMENT,

      ename VARCHAR(20),

      age INT,

      dept_id INT,

      -- 添加外键约束

      CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)

    );  

    [CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)

   2) 已有表添加外键

    ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);

   3) 删除外键约束

    alter table 从表 drop foreign key 外键约束名称;

   4)添加数据库时,应该先添加主表中的数据,删除数据时,应该先删除从表中的数据    

     5)  级联删除

        - 如果想实现删除主表数据的同时,也删除掉从表的数据,可以使用级联删除操作: ON DELETE CASCADE

           CREATE TABLE employee(

          eid INT PRIMARY KEY AUTO_INCREMENT,

          ename VARCHAR(20),

          age INT,

          dept_id INT,

          CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id),

          ON DELETE CASCADE --  添加级联删除

           ); 

 

  2、表与表之间的关系:   

     一对多关系:最常见的关系,例如班级对学生,部门对员工

     多对多关系:学生对应课程,用户对应角色

     一对一关系:使用较少,因为一对一关系可以合成为一张表 

 

  3、多表查询

    1)内连接查询:通过指定的条件去匹配两张表中的数据,匹配上就显示,匹配不上就不显示

      隐式内连接:form字据后面写多个表名,使用where指定连接条件的,这种方式就是隐式内连接

            select 字段名 from 左表,右表  where 连接条件;

      显示内连接:使用 inner join  ... on 这种方式就是显式内连接

            select 字段名 from 左表 [ inner ] join 右表 on  条件 [where 条件];

      2)外链接查询:

       左外连接:以左表为基准,匹配右表中的数据,如果匹配得上,就展示匹配到的数据

            如果匹配不到,左表中的数据正常显示,右表的数据展示为null

            select 字段名  from 左表  left [outer] join 右表 on 条件;

       右外连接:以右表为基准,匹配坐标中的数据,如果能匹配到,就展示匹配到的数据

            如果匹配不到,右表中的数据正常展示,左表的数据展示为null

            select 字段名 from 右表 right [outer] join 右表 on 条件;

 

  4、子查询 

    1、子查询分类

      where 型子查询:将子查询的结果作为父查询的比较条件

              select 查询字段 from 表 where 字段=(子查询);

      from 型子查询 : 将子查询的结果作为一张表提供给父层查询使用,子查询的 结果作为一张表 时,

                   一定要起一个别名,否则无法访问表中的字段

              select 查询字段 from (子查询)表别名  where 条件;

      exists型子查询:子查询的结果是单列多行,类型于一个数组,父层查询使用 IN 函数,包含子查询的结果 

              select 查询字段 from 表 where 字段 in (子查询);

 

13、  索引

     

posted @ 2020-10-23 14:06  逃不掉的热爱  阅读(182)  评论(0编辑  收藏  举报