基础

从系统架构的层次上看，MySQL 数据库系统从大到小依次是数据库服务器、数据库、数据表、数据表的行与列。所以在 MySQL 中，一个完整的数据存储过程总共有 4 步，分别是创建数据库、确认字段、创建数据表、插入数据。
image-20211007155810920.png
标识符命名规则：

  • 数据库名、表名不得超过30个字符，变量名限制为29个；
  • 必须只能包含 A–Z, a–z, 0–9, _共63个字符；
  • 数据库名、表名、字段名等对象名中间不要包含空格；
  • 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名；
  • 必须保证字段没有与保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用```(着重号)引起来

  • 保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了。

    库操作

    命令行登录MySQL服务器

  • 本地登录

    1. # usr_name表示用户名, usr_pwd表示用户密码, usr_pwd与-p无空格
    2. mysql -u usr_name -pusr_pwd

  • 远程登录：

    1. # host表示主机IP地址，port表示端口 
    2. mysql -h host -P port -u usr_name -pusr_pwd

  • 退出登陆

    1. exit
    2. 或
    3. quit

    创建数据库

    ```sql — 方式1:创建名为database_name的数据库 create database ; — 方式2:创建数据库并指定字符集 CREATE DATABASE 数据库名 CHARACTER SET 字符集; — 方式3:判断数据库是否已经存在，不存在则创建数据库（推荐方式） CREATE DATABASE IF NOT EXISTS 数据库名; 

  1. <a name="dydtT"></a>
  2. ## 删除数据库
  3. ```sql
  4. -- 删除名为database_name的数据库 
  5. drop database <database_name>;
  6. -- 删除已经存在的数据库(推荐)
  7. DROP DATABASE IF EXISTS 数据库名;

使用数据库

  1. -- 查看当前有哪些数据库
  2. show databases; 
  4. -- 切换/使用名为database_name的数据库 
  5. use <database_name>;
  7. -- 查看当前正在使用的数据
  8. SELECT DATABASE();
  10. -- 查看指定数据库下的所有表
  11. SHOW TABLES FROM 数据库名;
  13. -- 查看数据库的创建信息
  14. SHOW CREATE DATABASE 数据库名;

修改数据库

  1. -- 修改数据库的字符集
  2. ALTER DATABASE 数据库名 CHARACTER SET 字符集;  #比如：gbk、utf8等
  4. --修改数据库名: DATABASE不能改名。一些可视化工具可以改名，它是建新库，
  5. --把所有表复制到新库，再删旧库完成的。

执行sql脚本

  1. # 1. 先进入到某个数据库 
  2. # 2. 执行source xxx.sql命令
  3. # 注意: 执行source命令时，语句结尾不加分号，否则报错。

服务器元信息

MySQL服务器元信息
SELECT VERSION( ); 服务器版本信息
SELECT DATABASE( ); 当前数据库名 (或者返回空)
SELECT USER( ); 当前用户名
SHOW STATUS; 服务器状态
SHOW VARIABLES; 服务器配置变量

SHOW ERRORS和SHOW WARNINGS：用来显示服务器错误或警告消息。
SHOW GRANTS：用来显示授予用户(所有用户或特定用户)的安全权限。

表操作

表操作
show tables; 列出当前数据库的表
show fields from tab; 列出表tab的字段，显示表tab结构。
show columns from tab;
describe tab
desc tab;
show create table tab; 显示创建表tab_name时的SQL语句
truncate table tab; 删除表tab中的所有数据
drop table tab; 删除表tab

创建表

创建表必须具备两个条件：1. 具有CREATE TABLE权限； 2. 有相应的存储空间。

  1. --创建表语法格式
  2. CREATE TABLE [IF NOT EXISTS] 表名(
  3.     字段1 数据类型 [约束条件] [默认值],
  4.     字段2 数据类型 [约束条件] [默认值],
  5.     字段3 数据类型 [约束条件] [默认值],
  6.     ……
  7.     [表约束条件]
  8. );
  10. -- IF NOT EXISTS是可选的,加上则仅在表不存在时创建表 
  11. CREATE TABLE [IF NOT EXISTS] `runoob_tbl`( 
  12.   -- 反引号是为了区分 MySql 关键字与普通字符而引入的符号，一般表名与字段名都使用反引号。 
  13.   `runoob_id` INT UNSIGNED AUTO_INCREMENT, 
  14.   # 默认值: "runoob_test" 
  15.   `runoob_title` VARCHAR(100) NOT NULL DEFAULT "runoob_test", 
  16.   `runoob_author` VARCHAR(40) NOT NULL, 
  17.   `submission_date` DATE, PRIMARY KEY ( `runoob_id` ) 
  18. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • NULL是字段的默认属性，如果不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
  • AUTO_INCREMENT定义为自增的属性，一般用于主键，数值会自动加1。每个表只允许有一个AUTO_INCREMENT列，而且它必须被索引。
  • PRIMARY KEY关键字用于定义列为主键。 可以使用多列来定义主键，列间以逗号分隔。如果主键使用单个列，则它的值必须唯一；如果主键使用多个列，则这些列的组合值必须唯一。
  • 建表时如果某列给出了默认值(DEFAULT),则在插入数据时，该列可以不用给出值。MySQL不允许使用函数作为默认值，它只支持常量。
  • ENGINE设置存储引擎，引擎可以混合使用：不同的表可以使用不同的存储引擎。混合引擎的一个缺点是：外键不能跨引擎。
  • CHARSET设置编码。

MySQL 字段属性应该尽量设置为 NOT NULL，除非有很特别的原因去使用 NULL 值，否则应该总是保持字段为NOT NULL。
(1) 首先要搞清楚空值 **""** 和 **NULL** 的概念：

  • 空值是不占用空间的
  • MySQL中的NULL其实是占用1B空间的

(2) 数据库里严格区分NULL和空值，任何数跟NULL进行运算都是NULL；判断值是否等于NULL，不能简单用=，而要用 IS NULL、IS NOT NULL关键字或ifnull()函数。判断空字符用=''或者 <>''来进行处理。
(3) 假设表字段 col1 设为 NOT NULL, 仅仅说明该字段不能为 NULL, 也就是说只有在:
INSERT INTO table1(col1) VALUES(NULL);这种情况下数据库会报错，而
INSERT INTO table1(col1) VALUES('');不会报错。
（如果字段是自增ID，第一句不会报错，这不能说明是可以为NULL,而是数据库系统会根据ID设的缺省值填充，或者如果是自增字段就自动加一等缺省操作。）
对于timestamp数据类型：如果往这个数据类型插入的列插入NULL值，则出现的值是当前系统时间。插入空值，则会出现0000-00-00 00:00:00。
(4)在进行count()统计某列的记录数的时候，如果采用的是NULL值，系统会忽略掉对应的行，但空值是会进行统计到其中的。
(5) 含有空值的列很难进行查询优化，而且对表索引时不会存储 NULL 值的，所以如果索引的字段可以为 NULL，索引的效率会下降很多。因为它们使得索引、索引的统计信息以及比较运算更加复杂。应该用 0、一个特殊的值或者一个空串代替空值。
(6) 连接查询的时候，例如 LEFT JOIN table2，若没有记录，则查找出的 table2 字段都是 null。假如 table2 有些字段本身可以是 null，那么除非把 table2 中 not null 的字段查出来，否则就难以区分到底是没有关联记录还是其他情况。

MySQL存储引擎InnoDB与Myisam的六大区别
https://www.runoob.com/w3cnote/mysql-different-nnodb-myisam.html

删除表⭐️⭐️⭐️

  1. DROP TABLE [IF EXISTS] <tab_name>;：用来删除表全部数据和表结构，立刻释放磁盘空间，不管是 Innodb 还是 MyISAM。
  2. TRUNCATE TABLE <tab_name>;：删除表全部数据，保留表结构，立刻释放磁盘空间 ，不管是 Innodb 和 MyISAM。主要用于清空表中数据。
  3. DELETE FROM <table_name>;： 删除表全部数据，表结构不变，对于 MyISAM 会立刻释放磁盘空间，InnoDB 不会释放磁盘空间。
  4. DELETE FROM <tab_name> WHERE...：只删除表中部分数据，表结构不变，不管是 innodb 还是 MyISAM 都不会释放磁盘空间；delete from表以后未释放磁盘空间，所以下次插入数据的时候，仍然可以使用这部分空间。
    1. -- 删除student表中名字为'张三'的学生 
    2. delete from student where T_name = "张三";
    delete，drop，truncate 都有删除表的作用，区别在于：delete 是 DML 语句，操作完以后如果不想提交事务还可以回滚，truncate 和 drop 是 DDL 语句，操作完马上生效，不能回滚。执行的速度上：drop>truncate>delete。

修改表⭐️⭐️⭐️

修改表指的是修改数据库中已经存在的数据表的结构。使用ALTER TABLE可以实现：

  • 向已有的表中添加列；
  • 修改现有表中的列；
  • 删除现有表中的列；
  • 重命名现有表中的列。

    删除字段(列)

    使用ALTER命令及 DROP子句来删除创建表tab_name的col_name字段。如果数据表中只剩余一个字段则无法使用DROP来删除字段。 ```sql — 语法格式 ALTER TABLE tab_name DROP col_name;

— 示例 ALTER TABLE dept80 DROP COLUMN job_id; 

  1. <a name="bQlvP"></a>
  2. ### 添加字段(列)
  3. `ADD`子句来向数据表中添加列，如在表tab_name中添加col_name字段，并定义数据类,col_name字段自动添加到数据表字段的末尾。
  4. ```sql
  5. -- 语法格式
  6. ALTER TABLE tab_name ADD col_name INT;
  8. -- 示例
  9. ALTER TABLE dept80 ADD job_id varchar(15);

如果需要指定新增字段的位置，可以使用MySQL提供的关键字FIRST(设定位第一列)， AFTER字段名(设定位于某个字段之后)。 在执行成功后，使用SHOW COLUMNS FROM tab_name查看表结构的变化：

  1. -- FIRST 和 AFTER 关键字可用于 ADD 与 MODIFY 子句。
  2. ALTER TABLE tab_name ADD first_col INT FIRST; 
  3. ALTER TABLE tab_name ADD second_col INT AFTER first_col; 
  4. SHOW COLUMNS FROM tab_name;

修改字段(列)

修改列可以修改一个列的数据类型、长度、默认值和位置。修改字段时可以指定该字段是否包含值或者是否设置默认值。如果不设置默认,则MySQL自动设置该字段默认为NULL。语法格式如下：

  1. ALTER TABLE 表名 MODIFY 字段名1 字段类型 [DEFAULT 默认值] [FIRST|AFTER 字段名2];
  3. -- 示例
  4. ALTER TABLE    dept80 MODIFY last_name VARCHAR(30);
  5. ALTER TABLE    dept80 MODIFY salary double(9,2) NOT NULL default 1000;

使用 CHANGE子句修改列名, 在CHANGE关键字之后，紧跟着的是要修改的字段名，然后指定新字段名及类型。语法格式如下：

  1. -- 修改表tab_name中col_name字段的名字为new_col_name,类型为BIGINT 
  2. ALTER TABLE 表名 CHANGE  列名 新列名 新数据类型;
  4. --示例
  5. ALTER TABLE  dept80 CHANGE department_name dept_name varchar(15);

修改字段(列)的默认值

  1. -- 修改tab_name表中col_name的默认值为1000.
  2. ALTER TABLE tab_name ALTER col_name SET DEFAULT 1000; 
  3. -- 查看tab_name的表结构 
  4. SHOW COLUMNS FROM tab_name;
  5. -- 删除tab_name表中col_name列的默认值, 删除默认值后, 默认值为NULL.
  6. ALTER TABLE tab_name ALTER col_name DROP DEFAULT;

修改存储引擎

  1. -- 修改存储引擎为myisam 
  2. ALTER TABLE tab_name engine=myisam;
  3. -- 查看表tab_name的类型  
  4. SHOW TABLE STATUS FROM 'tab_name';

添加/删除外键约束

  1. -- 给表tab_name添加外键约束
  2. ALTER TABLE tab_name ADD foreign key key_name; 
  3. -- 删除tab_name表中的外键约束 key_name 
  4. ALTER TABLE tab_name DROP foreign key key_name;

重命名表

  1. -- 方式1
  2. -- 将表tab_name重命名为new_tab_name
  3. RENAME TABLE tab_name TO new_tab_name; 
  4. -- 重命名多个表 
  5. RENAME TABLE 
  6.   tab_name1 TO new_tab_name1,
  7.   tab_name2 TO new_tab_name2,
  8.   ... 
  9.   tab_namen TO new_tab_namen; 
  11.  -- 方式2
  12. ALTER table dept RENAME [TO] detail_dept;  -- [TO]可以省略

复制表⭐️⭐️⭐️

复制完整表

完整的复制一个表，包括表结构、索引、默认值、存储引擎等，有以下两种方法：

  1. 通过SHOW CREATE TABLE获取建表时的完整表结构；然后复制其SQL语句修改表名并执行； 如果想拷贝表的数据可以使用INSERT INTO... SELECT 语句来实现。 ``sql -- 查看建表old_tab时的完整结构 SHOW CREATE TABLE old_tab Table: old_tab Create Table: CREATE TABLEold_tab(runoob_idint(11) NOT NULL auto_increment,runoob_titlevarchar(100) NOT NULL default '',runoob_authorvarchar(40) NOT NULL default '',submission_datedate default NULL, PRIMARY KEY (runoob_id), UNIQUE KEYAUTHOR_INDEX(runoob_author`) ) ENGINE=InnoDB

— 复制上面的SQL语句，修改表名为new_tab并执行 CREATE TABLE new_tab (
runoob_id int(11) NOT NULL auto_increment, runoob_title varchar(100) NOT NULL default ‘’, runoob_author varchar(40) NOT NULL default ‘’, submission_date date default NULL, PRIMARY KEY (runoob_id), UNIQUE KEY AUTHOR_INDEX (runoob_author) ) ENGINE=InnoDB; — 复制旧表old_tab的记录到新表new_tab INSERT INTO new_tab ( runoob_id, , old_tab runoob_title, runoob_author, submission_date) SELECT runoob_id,runoob_title, runoob_author,submission_date FROM old_tab; 

  2. 2. `like`方法：该方法能将一个表的结果复制生成一个一模一样的新表，包括复制旧表的备注、索引、主键外键、存储引擎等。
  3. ```sql
  4. -- 复制表结构, 不拷贝数据 
  5. CREATE TABLE target_table LIKE source_table;
  7. -- 拷贝表中数据(记录) 
  8. INSERT INTO target_table SELECT * FROM source_table;

  1. 复制表结构及记录⭐️⭐️

    1. CREATE TABLE new_tab SELECT * FROM old_tab;

    复制部份表

  2. 拷贝表中的一些字段，可以在建新表时对拷贝字段进行改名

    1. CREATE TABLE new_admin AS 
    2. (     
    3. SELECT id, username AS uname, password AS pass 
    4. FROM admin  -- 此处结尾无";"
    5. );

    （2）拷贝部分数据

    1. CREATE TABLE new_admin AS 
    2. (     
    3. SELECT * FROM admin WHERE LEFT(username,1) = 's' 
    4. )

    （3）创建新表的同时定义表中的字段信息:

    1. CREATE TABLE new_admin 
    2. (     
    3. id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY 
    4. ) 
    5. AS 
    6. (     
    7. SELECT * FROM admin 
    8. )

临时表

MySQL临时表在需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。
使用 SHOW TABLES命令无法查看临时表。默认情况下断开数据库连接临时表会自动销毁，也可以通过drop table 命令来手动删除临时表。

  1. -- TEMPORARY关键字标识临时表 
  2. CREATE TEMPORARY TABLE SalesSummary ( 
  3. product_name VARCHAR(50) NOT NULL, 
  4. total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00, 
  5. avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00, 
  6. total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 
  7. ); 
  8. -- 插入数据 
  9. INSERT INTO SalesSummary 
  10. (product_name, total_sales, avg_unit_price, total_units_sold) 
  11. VALUES ('cucumber', 100.25, 90, 2); 
  12. -- 查询数据 
  13. SELECT * FROM SalesSummary; 
  14. -- 删除临时表 
  15. DROP TABLE SalesSummary;