常用的SQL语句

常用的SQL语句


DDL

DDL是操作是数据库和表的SQL语句。

1. 操作数据库

  1. 创建

    • 创建数据库

      • CREATE DATABASE 数据库名称;
    • 创建数据库,判断不存在,再创建

      • CREATE DATABASE IF NOT EXISTS 数据库名称;
    • 创建数据库,并指定字符集

      • CREATE DATABASE 数据库名称 character set 字符集名;
    • 创建数据库,判断不存在,再创建,并指定字符集

      • CREATE DATABASE IF NOT EXISTS 数据库名称 character set 字符集名;
  2. 查询

    • 查询所有数据库的名称

      • SHOW DATABASES;
    • 查询某个数据库的字符集,查询某个数据库的查询语句

      • SHOW CREATE DATABASE 数据库名称;
  3. 修改

    • 修改数据库的字符集
      • ALTER DATABASE 数据库名称 character set 字符集名;
  4. 删除

    • 删除数据库

      • DROP DATABASE 数据库名称;
    • 判断数据库存在,再删除

      • DROP DATABASE IF EXISTS 数据库名称;
  5. 使用数据库

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

      • SELECT DATABASE();
    • 使用数据库

      • USE 数据库名称;

2. 操作表

  1. 创建

    • 创建表

      CREATE TABLE 表名称(
          列名1,数据类型1,
          列名2,数据类型2,
          列名3,数据类型3,
          .......
          列名n,数据类型n
      );
    • 注意最后一列后面不用加逗号

    • SQL中常用的数据类型

      1. int:整数类型
      2. double:小数类型
      3. date:日期,只包含年月日,yyyy-MM-dd
      4. datetime:日期,包含年月日时分秒,yyyy-MM-dd HH:mm:ss
      5. timestamp:时间戳类型,包含年月日时分秒,不赋值时可以默认添加当前系统时间
      6. varchar:字符串
    • 复制表

      • CREATE TABLE 表名 LIKE 被复制的表名;
  2. 查询

    • 查询某个数据库中所有表的名称
      • SHOW TABLES;
    • 查询表结构
      • DESC 表名称;
  3. 修改

    • 修改表名

      • ALTER TABLE 表名称 RENAME TO 新的表名;
    • 修改表的字符集

      • ALTER TABLE 表名称 character set 字符集名;
    • 添加一列

      • ALTER TABLE 表名称 ADD 列名 数据类型;
    • 修改列名和类型

      • ALTER TABLE 表名称 CHANGE 列名 新列名 新数据类型;
      • ALTER TABLE 表名称 MODIFY 列名 新数据类型;
    • 删除列

      • ALTER TABLE 表名称 DROP 列名;
  4. 删除

    • 删除表

      • DROP TABLE 表名称;
    • 判断表存在,再删除

      • DROP TABLE IF EXISTS 表名称;

DML

DML是增删改数据库的表中的数据的SQL语句。

  1. 添加数据

    • INSERT INTO 表名(列名1,列名2,列名3,…..列名n) VALUES(值1,值2,值3,…..值n);

    • 注意:

    1. 列名和值要一一对应。

    2. 如果表名后,不定义列名,则默认给所有列添加值。

      • INSERT INTO 表名VALUES(值1,值2,值3,…..值n);
    3. 除了数字类型,其他类型需要使用引号(单双引号都可以)引起来。

  2. 删除数据

    • DELETE FROM 表名 [WHERE 条件];

    • 注意:如果不加条件,则删除表中所有记录。

    • 如果要删除表中所有记录,不推荐使用DELETE FROM 表名;,可以使用TRUNCATE TABLE 表名;

  3. 修改数据

    • UPDATE 表名 SET 列名1=值1, 列名2=值2,….列名n=值n [WHERE 条件];

    • 注意:如果不加任何条件,则会将表中所有记录修改。


DQL

DQL是查询表中记录的SQL语句。

  • SELECT * FROM 表名;

语法:

SELECT
    字段列表
FROM
    表名列表
WHERE
    条件列表
GROUP BY
    分组字段
HAVING
    分组之后的条件
ORDER BY
    排序
LIMIT
    分页限定

1. 基础查询

  1. 多个字段的查询

    • SELECT 字段名1,字段名2… FROM 表名;
  2. 去除重复

    • SELECT DISTINCT 字段名1,字段名2… FROM 表名;
  3. 计算列

    • 一般可以使用四则运算计算一些列的值。(一般只会进行数值型的计算)
    • IFNULL(表达式1,表达式2):NULL参与的运算,计算结果都为NULL。
      • 表达式1:哪个字段需要判断是否为NULL。
      • 表达式2:如果该字段为NULL后的替换值。
  4. 起别名

    • SELECT 字段名1 AS 别名 FROM 表名;

    • 注意:AS也可以省略,用一个空格代替。

2. 条件查询

  1. WHERE子句后跟条件。

  2. 运算符

    • < > >= <= <> =
    • BETWEEN…AND
    • IN(集合)
    • LIKE:模糊查询
      • 占位符:
        • _:单个任意字符。
        • %:多个任意字符。
    • IS NULL
    • AND 或 &&
    • OR 或 ||
    • NOT 或 !

3. 排序查询

  • 语法

    • ORDER BY 排序字段1 排序方式1,排序字段2 排序方式2….
  • 排序方式

    • ASC:升序,默认的。

    • DESC:降序。

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

4. 聚合函数

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

注意:聚合函数的计算会排除NULL值

解决方案:1.选择不包含空的列(主键)进行计算。2.IFNULL函数。

  1. COUNT;计算个数。
    • SELECT COUNT(字段名) FROM 表名;
  2. MAX;计算最大值。
    • SELECT MAX(字段名) FROM 表名;
  3. MIN;计算最小值。
    • SELECT MIN(字段名) FROM 表名;
  4. SUM:计算和。
    • SELECT SUM(字段名) FROM 表名;
  5. AVG;计算平均值。
    • SELECT AVG(字段名) FROM 表名;

5. 分组查询

  • 语法:

    • GROUP BY 分组字段;
    • SELECT 分组字段, 聚合函数1,…. FROM 表名 GROUP BY 分组字段;
  • 注意:1.分组之后查询的字段只能是分组字段或者聚合函数。2.在分组之前可以用WHERE进行条件限定且后面不可以跟聚合函数判断,如果要在分组之后进行条件限定需要用HAVING且后面可以跟聚合函数判断。

    SELECT 分组字段, 聚合函数1,.... FROM 表名 WHERE 条件 GROUP BY 分组字段 HAVING 条件;

6. 分页查询

  • 语法:
    • LIMIT 开始的索引,每页查询的条数;
    • SELECT * FROM 表名 LIMIT 开始的索引, 每页查询的条数;

LIMIT是一个“方言”,只能在MySQL中用。


DCL

1.管理用户

  1. 添加用户:

    • CREATE USER ‘用户名‘@’主机名’ IDENTIFIED BY ‘密码’;
  2. 删除用户:

    • DROP USER ‘用户名‘@’主机名’;
  3. 修改用户密码:

    • UPDATE USER SET PASSWORD = PASSWORD(‘新密码’) WHERE USER = ‘用户名’;

    • SET PASSWORD FOR ‘用户名‘@’主机名’ = PASSWORD(‘新密码’);

    • MySQL中忘记了root用户的密码怎么办?

      1. cmd –> net stop mysql80 停止mysql服务
      2. 使用无验证方式启动mysql服务:mysqld –skip-grant-tables
      3. 打开新的cmd窗口,直接输入mysql命令,敲回车,就可以登陆成功
      4. USE mysql;
      5. UPDATE USER SET PASSWORD = PASSWORD(‘新密码’) WHERE USER = ‘root’;
      6. 关闭两个窗口
      7. 打开任务管理器,手动关闭mysqld.exe的进程
      8. 启动mysql服务
      9. 使用新密码登录
  4. 查询用户:

    1. 切换到mysql数据库。USE mysql;
    2. 查询user表。SELECT * FROM user;
    • 通配符:%表示可以在任意主机使用用户登录数据库。

2.权限管理

  1. 查询权限:

    • SHOW GRANTS FOR ‘用户名‘@’主机名’;
  2. 授予权限

    • GRANT 权限列表 ON 数据库名.表名 TO ‘用户名‘@’主机名’;
    • GRANT ALL ON . TO ‘用户名‘@’主机名’; 给一个用户授予对任意数据库中任意表进行任意操作的权限。
  3. 撤销权限

    • REVOKE 权限列表 ON 数据库名.表名 FROM ‘用户名‘@’主机名’;

约束

对表中的数据进行限定,保证数据的正确性、有效性和完整性。

分类:

  1. 主键约束:primary key
  2. 非空约束:not null
  3. 唯一约束:unique
  4. 外键约束:foreign key

1.非空约束

某一列的值不能为NULL。

  • 创建表时添加非空约束

    CREATE TABLE 表名(
        字段名1 数据类型1,
        字段名2 数据类型2 NOT NULL,
        .....
    );
  • 创建表完后添加非空约束

    ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;

  • 删除非空约束

    ALTER TABLE 表名 字段名 数据类型;

2.唯一约束

某一列的值不能重复。

  • 创建表时,添加唯一约束

    CREATE TABLE 表名(
        字段名1 数据类型1,
        字段名2 数据类型2 UNIQUE,
        .....
    );
  • 删除唯一约束

    ALTER TABLE 表名 DROP INDEX 字段名;

  • 在表创建完后,添加唯一约束

    ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE;

3.主键约束

某一列的值非空且唯一,一张表只能有一个字段为主键,就是表中记里的唯一标识。

  • 创建表时,添加主键约束

    CREATE TABLE 表名(
        字段名1 数据类型1,
        字段名2 数据类型2 PRIMARY KEY,
        .....
    );
  • 删除主键

    ALTER TABLE 表名 DROP PRIMARY KEY;

  • 创建完表后,添加主键

    ALTER TABLE 表名 MODIFY 字段名 数据类型 PRMARY KEY;

  • 自动增长

    • 如果某一列是数值类型,使用AUTO_INCREMENT可以完成值自动增长,一般可以配合数值类型的主键使用。

    • 注意:自动增长的值为最后一条记录自动增长字段的值加一

    • 创建表时,添加主键,并且完成自动增长

      CREATE TABLE 表名(
          字段名1 数据类型1,
          字段名2 数据类型2 PRIMARY KEY AUTO_INCREMENT,
          .....
      );
    • 删除自动增长

      ALTER TABLE 表名 MODIFY 字段名 数据类型;

    • 添加自动增长

      ALTER TABLE 表名 MODIFY 字段名 数据类型 AUTO_INCREMENT;

4.外键约束

让表与表产生关系,从而保证数据的正确性,外键可以为NULL,但是不能是主表中不存在的值。

  • 创建表时,添加外键

    CREATE TABLE 表名(
        字段名1 数据类型1,
        字段名2 数据类型2,
        .....
        CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称)
    );
  • 删除外键

    ALTER TABLE 表名称 DROP FOREIGN KEY 外键名称;

  • 创建表之后,添加外键

    ALTER TABLE 表名称 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称);

  • 级联操作

    可以在外键关联的主表中的列进行操作时,外键中的值自动更新。

    • 级联更新

      • 在添加外键语句后加上ON UPDATE CASCADE

        ALTER TABLE 表名称 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE;

    • 级联删除

      • 在添加外键语句后加上ON DELETE CASCADE

        ALTER TABLE 表名称 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称) ON DELETE CASCADE;

级联更新与级联删除可以同时存在。


命令行操作数据库备份和还原

  • 备份:MYSQLDUMP -u用户名 -p密码 数据库名称 > 保存的路径;

  • 还原:

    1. 登录数据库
    2. 创建数据库
    3. 使用数据库
    4. 使用文件。SOURCE 文件路径

打赏
  • 版权声明: 本博客所有文章除特别声明外,均采用 Apache License 2.0 许可协议。转载请注明出处!

请我喝杯咖啡吧~

支付宝
微信