【学习总结】SQL的学习-2-sql操作

参考链接

目录

=======================================================

建库和建表

语法

  • 建库：CREATE DATABASE 数据库名;

    • 示例：mysql> create database learning;

  • 建表：CREATE TABLE table_name (column_name column_type);

    • 示例：

操作

  • 本部分常用命令小结

    • 登录MySQL：mysql -u root -p+回车+输入密码
    • 查看已有数据库：show databases;
    • 选择要操作的数据库：use 数据库名;，例如use learning;
    • 查看当前操作的数据库：select database();
    • 查看数据库中已有的表：show tables;
    • 查看某个表的详细信息：desc 表名;，例如desc houses;

  • step1：登录MySQL并查看已有数据库

  • step2：建库并查看

  • step3：建表并查看

=======================================================

数据库查询语言DQL

概述

  • Data Query Language
  • 基本结构：select子句，from子句，where子句组成的查询块；
    • SELECT <字段>
    • FROM <表或视图名>
    • WHERE <查询条件>
    • 注：视图view：虚拟的表，只能查询，不能删除或修改，操作语法同表table。

查询语法

  • SELECT column_list/*要查询的列名称*/

  • FROM table_list /*要查询的表名称*/ -- 可能从多张表查询，然后合并输出

  • WHERE condition /*行条件*/

  • GROUP BY grouping_columns /*对结果分组*/

  • HAVING condition /*分组后的行条件*/

  • ORDER BY sorting_columns /*对结果排序*/

  • LIMIT offset_set, row_coun t /*结果限定显示行数*/ -- 数据太多时，只显示指定部分的数据

  • ;

  • 注意：注释放置的位置任意。

合并字段语法

  • concat函数：concat(字段名1 "分隔符", 字段名2)

    • 将字段名1和字段名2通过分隔符连接后输出
    • 示例：select concat(name, "|", house_location) from houses;

具体操作





=======================================================

数据操纵语言DML

概述

  • Data Manipulation Language
  • 包括：插入INSERT，删除DELETE，修改UPDATE

插入 INSERT

  • 法1：键入每个需要插入的数值

    • 语法：INSERT INTO table_name (field1,field2,...,fieldN) VALUES (value1,value2,...,valueN);

    • 示例：insert into houses (name,house_location,purchaseing_year) values ('甲','天河',1997);

    • 注意：后面的数值要与前面的字段名一一对应好，否则就插入到错误的位置了。

  • 法2：从其他表中提取数据后插入到本表中

    • 语法：INSERT INTO table_name select column1,...columnN from table_name2 WHERE XXX;

    • 示例：INSERT INTO houses2 select name,house_location,purchaseing_year from houses WHERE name = 'xx';

    • 注意：后面的部分仍然是"select-from-where"组合

删除 DELETE

  • 方法：选取特定表中符合条件的数据并删除

    • 语法：DELETE FROM table_name WHERE xxx;

    • 示例：delete from houses;

  • delete后，数据是否已经删除了呢？

更新 UPDATE

  • 方法：更新指定字段的数据为新的数据，可以使用where指定限制条件

    • 语法：UPDATE table_name SET field1=new-value1,field2=new-value2 WHERE xxx;

    • 示例：update houses set name='甲1';

具体操作

  • 插入示例1：

  • 插入示例2：


  • 删除示例：

  • 更新示例：

=======================================================

数据定义语句DDL

概述

  • Data Definition Language
  • 影响对象的定义和存储
  • 包括：创建CREATE，修改ALTER，清除TRUNCATE，删除DROP

创建 CREATE

  • 概述：新建数据库；数据库下新建表结构

    • 建库：CREATE DATABASE 数据库名;

    • 建表：CREATE TABLE table_name (column_name column_type);

    • 查询库下的表：show tables;

    • 查看表结构：desc table_name;

修改 ALTER

  • 删除，添加或修改表结构的字段(注：与数据操纵语言区别：修改的是结构，不是具体数值)

    • 删除某表的指定字段：ALTER TABLE table_name DROP field_i;

    • 添加字段到某表-法1-添加到第一个：ALTER TABLE table_name ADD field_i field_type INT FIRST;

    • 添加字段到某表-法2-添加到某字段后：ALTER TABLE table_name ADD field_i field_type INT AFTER field_j;

  • 修改字段类型及名称

    • 修改字段类型：ALTER TABLE table_name MODIFY field_name type;

    • 修改字段名称：ALTER TABLE table_name CHANGE old_field_name new_fiele_name;

    • 同时修改字段名和字段类型：ALTER TABLE table_name CHANGE old_field_name new_fiele_name type;

  • 修改表名

    • 修改指定表名为新的表名：ALTER TABLE table_name RENAME TO new_name;

清除 TRUNCATE

  • 概述：清除表

    • TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同，均用来删除表中的全部行，但 TURCATE TABLE 速度更快，且使用的系统和事务日志资源少。

    • TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。

    • 对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。

    • TRUNCATE TABLE 不能用于参与了索引视图的表。

    • 如果有 ROLLBACK 语句， DELETE 操作将被撤销(autocommit-off时)，但 TRUNCATE 不会撤销。

  • 小结：

    • TRUNCATE更快，不改变表结构，不会rollback，不能用于由 FOREIGN KEY 约束引用的表，不能用于不能用于参与了索引视图的表。

    • delete更慢，不改变表结构，可以回滚。

    • 注意：delete删除需要from -- delete from table_name;， 而truncate直接加表名即可 -- truncate table_name;

删除 DROP

  • 删除数据库和表

    • 删除数据库：drop database <数据库名>;

    • 删除表：drop table <表名>;

具体操作

  • 创建：同前"建库建表"的操作，此处略。

  • 修改表字段

  • 修改字段类型及名称

  • 修改表名

  • 删除表


  • truncate和delete的对比



  • 删除数据库和表


=======================================================

数据控制语言DCL

概述

  • Data Control Language
  • DCL用来授予或回收访问数据库的某种特权。

授权 GRANT

  • 数据库/数据表/数据列权限：

    • alter：修改已存在的数据表(例如增加/删除列)和索引
    • create：建立新的数据库或数据表
    • delete：删除表的记录
    • drop：删除数据表或数据库
    • index：建立或删除索引
    • insert：增加表的记录
    • select：显示/搜索表的记录
    • update：修改表中已存在的记录

  • 语法

    • grant权限on数据库对象to用户

    • 示例1：grant select on learning.* to user@'%';

    • 示例2：grant insert on learning.* to user@'%';

    • %: 百分号表示所有的IP地址都可以使用这样一种权限。

  • 特别的权限：

    • ALL：允许做任何事(和root一样)

    • USAGE：只允许登录，其他什么也不允许做(默认享有)

  • grant管理数据库的权限



  • 具体操作

    • 新建用户并赋予权限和密码：CREATE USER john1@'%'IDENTIFIED BY '123';

      • 注：%表示用户可以从任意IP地址登录，'123'表示登录密码。
      • 注：并不是所有用户都有权限再建用户的

    • 首先，需要对用户赋予权限

      • grand select,insert,update,delete on learning.* to john1@'%';

    • 查询用户的权限

      • show grand for john1;

回退 ROLLBACK

  • 作用：

    • 回滚命令使数据库状态回到上次最后提交的状态

  • 语法：

    • ROLLBACK;

提交 COMMIT

  • 概述

    • 在数据库的插入、删除和修改操作时，只有当事务提交到数据库时才算完成。
    • 在事务提交前，只有操作数据库的这个人才有权看到所做的事情，别人只有在最后提交完成后才可以看到。

  • 分类

    • 显式提交：用commit命令直接完成的提交。

      • 格式：COMMIT;

    • 隐式提交：用SQL命令间接完成的提交。

      • 隐式提交命令包括：ALTER, AUDIT, COMMENT, CREATE, DISCONNECT, DROP, EXIT, GRANT, NOAUDIT, QUIT, REVOKE, RENAME;

    • 自动提交：autocommit为on时，插入、删除、修改语句执行后，系统将自动提交

      • 关闭自动提交后，操作之后需要手动commit之后，才能在其他cmd窗口中同步修改。
      • 注：set命令本身是否自动提交？需要重新登录。
      • 小结：前面对比delete和truncate的rollback时需要关闭自动提交，因为rollback回滚到的是最后一次commit的状态。自动提交开启时，删除自动提交，因此回滚后仍是删除状态，导致回滚失败。

END

posted @ 2020-04-21 11:48  anliux  阅读(345)  评论(0)  编辑  收藏  举报