基础

从系统架构的层次上看,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. 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;
  3. -- 切换/使用名为database_name的数据库
  4. use <database_name>;
  5. -- 查看当前正在使用的数据
  6. SELECT DATABASE();
  7. -- 查看指定数据库下的所有表
  8. SHOW TABLES FROM 数据库名;
  9. -- 查看数据库的创建信息
  10. SHOW CREATE DATABASE 数据库名;

修改数据库

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

执行sql脚本

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

服务器元信息

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

SHOW ERRORSSHOW 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. );
  9. -- IF NOT EXISTS是可选的,加上则仅在表不存在时创建表
  10. CREATE TABLE [IF NOT EXISTS] `runoob_tbl`(
  11. -- 反引号是为了区分 MySql 关键字与普通字符而引入的符号,一般表名与字段名都使用反引号。
  12. `runoob_id` INT UNSIGNED AUTO_INCREMENT,
  13. # 默认值: "runoob_test"
  14. `runoob_title` VARCHAR(100) NOT NULL DEFAULT "runoob_test",
  15. `runoob_author` VARCHAR(40) NOT NULL,
  16. `submission_date` DATE, PRIMARY KEY ( `runoob_id` )
  17. ) 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 NULLIS 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;
  7. -- 示例
  8. 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];
  2. -- 示例
  3. ALTER TABLE dept80 MODIFY last_name VARCHAR(30);
  4. ALTER TABLE dept80 MODIFY salary double(9,2) NOT NULL default 1000;

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

  1. -- 修改表tab_namecol_name字段的名字为new_col_name,类型为BIGINT
  2. ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
  3. --示例
  4. 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;
  10. -- 方式2
  11. 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;

  1. 2. `like`方法:该方法能将一个表的结果复制生成一个一模一样的新表,包括复制旧表的备注、索引、主键外键、存储引擎等。
  2. ```sql
  3. -- 复制表结构, 不拷贝数据
  4. CREATE TABLE target_table LIKE source_table;
  5. -- 拷贝表中数据(记录)
  6. 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;