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

参考链接

• 【学习总结】SQL的学习-汇总

目录

• 建库和建表
  • 语法
  • 操作
• 数据库查询语言DQL
  • 概述
  • 查询语法
  • 合并字段语法
  • 具体操作
• 数据操纵语言DML
  • 概述
  • 插入 INSERT
  • 删除 DELETE
  • 更新 UPDATE
  • 具体操作
• 数据定义语句DDL
  • 概述
  • 创建 CREATE
  • 修改 ALTER
  • 清除 TRUNCATE
  • 删除 DROP
  • 具体操作
• 数据控制语言DCL
  • 概述
  • 授权 GRANT
  • 回退 ROLLBACK
  • 提交 COMMIT

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

建库和建表

语法

• 建库：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