MySQL-基础SQL

MySQL基础—单表操作

  这学期正好做数据库课设使用MySQL，在学过PostgreSQL后觉得MySQL语法也较友好，在Mactonish系统终端以及DataGrip共同开发，也算监督我学习了，学习路径来自ithema

1. 数据库管理

1.1 数据库操作

• 终端登录数据库命令：mysql -u root -p

• 查看当前所有数据库：show databases;

  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | mysql              |
  | performance_schema |
  | sys                |
  +--------------------+
  

• 创建数据库：create database 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

  mysql> create database day01 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
  Query OK, 1 row affected, 2 warnings (0.00 sec)
  
  mysql> show databases;
  +--------------------+
  | Database           |
  +--------------------+
  | day01              |
  | information_schema |
  | mysql              |
  | performance_schema |
  | sys                |
  +--------------------+
  

• 删除数据库：drop databases 数据库名;

• 进入数据（进入文件）：use 数据库;

• 查询当前数据库select database();

1.2 DDL-表操作

• 查询当前数据库所有表

  show tables;
  

• 查询表结构

  desc 表名
  

• 查询指定表的建表语句

  show create table 表名;
  

• DDL-表操作-创建

  create table 表名(
  					字段1  字段1类型[comment 字段1注释],
  					字段2  字段2类型[comment 字段2注释],
  					字段3  字段3类型[comment 字段3注释],
  					...
  					字段n  字段n类型[comment 字段n注释]
  )[comment 表注释];
  
  mysql> create table tb_user(
      -> id int comment '编号',
      -> name varchar(50) comment '姓名',
      -> age int comment '年龄',
      -> gender varchar(1) comment '性别'
      -> )comment '用户表';
  Query OK, 0 rows affected (0.02 sec)
  

• DDL-表操作-数据类型

  int | integer
  float
  double(,)
  varchar()
  date YYYY-MM-DD(时间格式)
  time HH:MM:SS
  datetime YYYY-MM-DD HH:MM:SS
  

• DDL-表操作-修改&删除

  添加字段

  alter table 表名 add 字段名 类型(长度) [comment 注释][约束];
  

  修改数据类型

  alter table 表名 modify 字段名 新数据类型(长度);
  

  修改字段名和字段类型

  alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释][约束];
  

  修改表名

  alter table 表名 rename to 新表名;
  

  删除表

  drop table[if exists] 表名;
  

  删除指定表，并重新创建该表

  truncate table 表名;
  

1.3 DML-表操作

• DML-添加数据

  给指定字段添加数据:

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

  给全部字段添加数据：

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

  批量添加数据：

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

• DML-修改数据

  update 表名 set 字段名1=值1,字段名2=值2,...[where 条件];
  

• DML-删除数据

  delete from 表名 [where 条件];
  

1.4 DQL-基本查询

• DQL-基本查询

  查询多个字段

  select 字段1,字段2,字段3,... from 表名;
  select * from 表名;
  

  设置别名

  select 字段1 [as 别名1],字段2 [as 别名2],... from 表名;
  

  去除重复记录

  select distinct 字段列表 from 表名;
  

• DQL-条件查询

  select 字段列表 from 表名 where 条件列表;
  

  条件:

  常用的比较运算符如下:

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或 !=	不等于
between…and…	在某个范围之内（含最小、最大值）
in(…)	在in之后的列表中的值，多选一
like 占位符	模糊匹配（_匹配单个字符，%匹配多个字符)
is null	是NULL

​ 常用的逻辑运算符如下：

逻辑运算符	功能
and 或 &&	并且（多个条件成立）
or 或 ||	或者（多个条件任意一个成立）
not 或 ！	非，不是

• DQL-聚合函数

  • 介绍

    将一列数据作为一个整体，进行纵向计算

  • 常用聚合函数

  • 语法

    select 聚合函数(字段列表) from 表名;
    

  注：表中数据为null不参与聚合函数运算

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

• DQL-分组查询

  • 语法

    select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件]
    

  • where与having区别

    • 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组

    • 之后对结果进行过滤。

    • 判断条件不同：where不能对聚合函数进行判断，而having可以。

  注意：执行顺序：where > 聚合函数 > having

• DQL-排序查询

  • 语法

    select 字段列表 from 表名 order by 字段1 排序方式1,字段2,排序方式2;
    

  • 排序方式

    • asc：升序（默认排序）
    • desc：降序

• DQL-分页查询

  • 语法

    select 字段列表 from 表名 limit 起始索引,查询记录数;
    

  注意

  • 起始索引从0开始，起始索引 = (查询页码 - 1)*每页显示记录数

1.5 DCL-用户管理

• DCL-管理用户

  • 查询用户

    use mysql; //直接在datagrip里的mysql中的user表
    select * from user;
    

  • 创建用户

    create user '用户名'@'主机名' identified by '密码'; 
    

    若想在任意主机都访问数据库：

    create user '用户名'@'%' identified by '密码'; 
    

  • 修改密码

    alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
    

  • 删除用户

    drop user '用户名'@'主机名';
    

• DCL-权限控制

  权限	说明
  all, all privileges	所有权限
  select	查询数据
  insert	插入数据
  update	更新数据
  delete	删除数据
  alter	更改数据
  drop	修改表
  create	创建数据库/表/视图
  delete	删除数据库/表/视图

  • 查询权限

    show grants for '用户名'@'主机名';
    

  • 授予权限

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

  • 撤销权限

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