mysql学习笔记

mysql
发布时间 :
阅读 :
  1. mysql命令行
    1. mysql登录
    2. 登录后退出
    3. 修改提示符
    4. 常用命令
    5. 规范
    6. 创建数据库示例
    7. 查看当前服务器下的数据列表
    8. 修改数据库示例
    9. 删除数据库示例
  2. MySQL数据类型
    1. 整型
    2. 浮点型
    3. 日期类型
    4. 字符型
  3. 表
    1. USE
    2. 创建数据表
    3. 查看数据表
    4. 查看数据表结构
    5. 插入记录与查询记录
  4. 空值与非空
  5. 自动编号
  6. 主键约束
  7. 唯一约束
  8. 默认约束
  9. 再谈约束
    1. 外键约束
    2. 外键约束要求
      1. 编辑数据表的默认存储引擎
    3. 外键约束示例
    4. 外键约束的参照操作
    5. 表级约束与列级约束
    6. 修改数据表
    7. 修改列定义
    8. 修改列名称
    9. 修改数据表名称
  10. 操作数据表中的记录
    1. 插入记录
    2. 更新记录（单表更新）
    3. 删除记录（单表删除）
    4. 查询记录

mysql命令行

mysql登录

  • 查看版本

    1
    mysql -V

  • root权限登录 p密码 P端口 h主机地址 回车后输入密码或者-p后跟密码

    1
    mysql -uroot -p -P3306 -h127.0.0.1

登录后退出

1
2
3
exit;
quit;
\q;

修改提示符

  • \D完整日期 \d当前数据库 \h服务器名称\u当前用户 可以互相组合
1
mysql -uroot -proot --prompt \h
  • 登录以后
1
prompt mysql>

常用命令

  • 查版本、显示当前日期、显示当前用户

    1
    2
    3
    SELECT VERSION();
    SELECT NOW();
    SELECT USER();

规范

  • 关键字、函数名称全部大写
  • 数据库名称、表名称、字段名称全部小写
  • SQL语句必须以分号结尾

创建数据库示例

  • 最简单的版本

    1
    CREATE DATABASE t1;

  • 在未存在该数据库时才创建

    1
    CREATE DATABASE IF NOT EXISTS t1;

    此时数据库存在，所以可以用SHOW WARNINGS;查看警告信息，SHOW CREATE DATABASE t1;可以查看创建t1的命令

  • 再加入编码方式

1
CREATE DATABASE IF NOT EXIST t2 CHARACTER SET gbk;

如果想修改编码方式则参考下面的内容

查看当前服务器下的数据列表

  • 基本示例

    1
    SHOW DATABASES;

修改数据库示例

  • 修改除了没有IF NOT EXISTS 和 CREATE改为ALTER其他部分都一样，下面修改字符集

    1
    ALTER DATABASE t1 CHARACTER SET utf8;

删除数据库示例

  • 删除

    1
    DROP DATABASE IF EXISTS t1;

MySQL数据类型

整型

  • TINYINT(-128-127,0-255) 1字节
  • SMALLINT(-32768-32767,0-65535) 2字节
  • MEDIUMINT(-8388608-8388607,0-4294967295) 3字节
  • INT(-2^31-2^31-1,0-2^32-1) 4字节
  • BIGINT(-2^63–2^63-1,0-2^64-1) 8字节

浮点型

  • FLOAT[(M,D)] M表示总位数D表示小数位数（小数最大7位）
  • DOUBLE[(M,D)]

日期类型

  • YEAR 1
  • TIME 3
  • DATE 3
  • DATETIME 8
  • TIMESTAMP(时间戳) 4

字符型

  • CHAR(M) 0<=M<=255字节 定长类型，不足以空格补位
  • VARCHAR(M) L+1个字节，其中L<=M且0<=M<=65535 变长类型，有多长存多长
  • TINYTEXT L+1个字节，其中L<2^8
  • TEXT L+2个字节，其中L<2^16
  • MEDIUMTEXT 3 24
  • LONGTEXT 4 32
  • ENUM(‘value1’,’value2’,…) 1或2个字节，取决于枚举个数（最多65535个值）
  • SET(‘value1’,’value2’,…) 1 2 3 4 8字节 取决于SET成员的数目最大64个成员

表

USE

  • 打开数据库(必须已经创建或已存在)

    1
    USE t1;

创建数据表

  • 创建一个存储用户信息的表

    1
    2
    3
    4
    5
    CREATE TABLE tb1(
    	username VARCHAR(20),
    	age TINYINT UNSIGNED,
    	salary FLOAT(8,2) UNSIGHED
    );

查看数据表

  • 查看数据表列表

    1
    SHOW TABLES [FROM dbName(mysql)];

查看数据表结构

  • 查看表结构是查看列

    1
    SHOW COLUMNS FROM tb1;

插入记录与查询记录

  • 插入记录示例，如果省略列名称，需要全部赋值，还是以上面创建的用户信息表举例

    1
    INSERT tb1 VALUES('Anger', 21, 7863.25);

  • 插入记录示例,不省略列名称

    1
    INSERT tb1(username, age) VALUES('Kira', 25);

  • 简单查询示例，*匹配的是字段而不是记录，有人会说*是匹配所有记录，这是有点问题的

    1
    SELECT * FROM tb1;

空值与非空

  • NULL 字段值可以为空，设置的时候可以省略
  • NOT NULL 字段值禁止为空

自动编号

  • 自动编号，必须与主键结合使用
  • 默认情况下，起始值为1，每次的增量为1

主键约束

  • 一张数据表中只能存在一个主键

  • 主键保证记录的唯一性

  • 主键自动为NOT NULL

    1
    2
    3
    4
    5
    6
    7
    CREATE TABLE tb3(
    	id SMALLINT UNSIGHED AUTO_INCREMENT PRIMARY KEY,
    	username VARCHAR(20) NOT NULL
    );
    SHOW COLUMNS FROM tb3;
    INSERT tb3(username) VALUES('Kira');
    SELECT * FROM tb3;

唯一约束

  • 唯一约束可以保证记录的唯一性
  • 唯一约束的字段可以为空值（NULL）
  • 每张数据表可以存在多个唯一约束

默认约束

  • 当插入记录时，如果未明确为字段赋值，则会赋予默认值

    1
    2
    3
    4
    5
    6
    7
    8
    CREATE TABLE tb4(
    	id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    	username VARCHAR(20) NOT NULL UNIQUE KEY,
    	sex ENUM('1', '2', '3') DEFAULT '3'
    );
    SHOW COLUMNS FROM tb4;
    INSERT tb6(username, sex) VALUES('Tom', 1);
    INSERT tb6(username) VALUES('Rose');

再谈约束

  1. 约束保证数据的完整性和一致性
  2. 约束分为表级约束和列级约束，如果约束只针对某一个字段来约束，我们称之为列级约束，如果针对两个或两个以上字段来使用约束，则成为表级约束
  3. 约束类型包括（按功能分类）
    • NOT NULL (非空约束)
    • PRIMARY KEY(主键约束)
    • UNIQUE KEY(唯一约束)
    • DEFAULT(默认约束)
    • FOREIGN KEY(外键约束)

外键约束

  1. 保持数据完整性和一致性
  2. 实现一对一或一对多的关系

外键约束要求

  1. 父表和子表必须使用相同的存储引擎，而且禁止使用临时表
  2. 数据表的存储引擎只能为InnoDB
  3. 外键列和参照列必须具有相似的数据类型。其中数字的长度或是是否有符号位必须相同，而字符长度则可以不同
  4. 外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将会自动创建索引

PS :

  • 子表是具有外键列的表，而子表所参照的表叫父表
  • 外键列是加过FOREIGN关键字的列，而参照列则是外键列参照的列

编辑数据表的默认存储引擎

  • MySQL配置文件my.ini,修改完需重启MySQL服务

    1
    default-storage-engine=INNODB

外键约束示例

  • 省份表

    1
    2
    3
    4
    5
    CREATE TABLE provinces(
    	id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    	pname VARCAHR(20) NOT NULL
    );
    SHOW CREATE TABLE provinces;

  • 用户表,pid参照省份表（父表）的id字段

1
2
3
4
5
6
CREATE TABLE users(
  	id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  	username VARCHAR(10) NOT NULL,
  	pid BIGINT,
  	FOREIGN KEY (pid) REFERENCES provinces (id)
  );

上述操作会报错，因为父表的id与子表的pid数据类型不相似

  • 修正后

    1
    2
    3
    4
    5
    6
    CREATE TABLE users(
     	id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
     	username VARCHAR(10) NOT NULL,
     	pid SMALLINT UNSIGNED,
     	FOREIGN KEY (pid) REFERENCES provinces(id)
     );

外键约束的参照操作

这些操作是指在进行外键约束的情况下，在更新表的时候，子表是否也进行相应的操作

  • CASCADE

    父表更新或删除行，也会更新或删除子表中匹配的行。而且插入数据时必须先在父表中插入，然后才能在子表中插入，原因是子表是参照父表的信息，如果没有父表信息，将导致子表无法参照

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    CREATE TABLE users1(
    	id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    	username VARCHAR(20) NOT NULL,
    	pid SMALLINT UNSIGNED,
    	FOREIGN KEY (pid) REFERENCES provinces (id) ON DELETE CASCADE
    );

    SHOW CREATE TABLE users1;

    INSERT provinces(pname) VALUES('A');
    INSERT provinces(pname) VALUES('B');
    INSERT provinces(pname) VALUES('C');

    SELECT * FROM provinces;

    INSERT users1(username, pid) VALUES('Tom', 3);
    INSERT users1(username, pid) VALUES('John', 1);
    INSERT users1(username, pid) VALUES('Rose', 3);

    SELECT * FROM users1;

    DELETE FROM provinces WHERE id = 3;

    SELECT * FROM provinces;
    SELECT * FROM users1;

  • SET NULL

    父表删除和更新行时，会设置子表中的外键列为NULL，但是必须在子表外键列没有设置NOT NULL的情况下

  • RESTRICT

    拒绝对父表进行删除和更新操作

  • NO ACTION

    与RESTRICT相同

在实际操作中，很少使用物理外键约束操作，而使用逻辑外键约束操作，原因是物理外键约束只有INNODB引擎才支持，逻辑外键就是我们在定义两张表的结构的时候，我们是按照存在着某种结构的方式去定义，但是不去使用FOREIGN KEY这个关键词来定义

表级约束与列级约束

如果按照功能划分会有主键约束，非空约束，唯一约束。而如果按照参照操作数目的多少来划分，则有表级和列级约束

  • 列级约束

    对一个列创建的约束，既可以在列定义时声明，也可以在列定义后声明

  • 表级约束

    对多个列创建的约束，只可以在列定义后声明

修改数据表

数据表的修改无非就是列的增加，列的删除，约束的添加，约束的删除等等一些操作

  • 添加单列

    1
    2
    3
    4
    5
    SHOW COLUMNS FROM users1;

    ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;
    ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username;
    ALTER TABLE users1 ADD realname VARCHAR(20) NOT NULL FIRST;

  • 添加多列

    与上面的语法相似，在ADD后面加上括号，不能指定位置关系（FIRST,AFTER）

  • 删除列

    1
    2
    3
    4
    5
    ALTER TABLE users1 DROP realname;

    SHOW COLUMNS FROM users1;

    ALTER TABLE users1 DROP username, DROP age;

  • 添加主键约束(默认B tree索引)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    CREATE TABLE users2(
    	username VARCHAR(10) NOT NULL,
    	pid SMALLINT UNSIGNED
    );

    SHOW CREATE TABLE users2;

    ALTER TABLE users2 ADD id SMALLINT UNSIGNED;
    ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY (id);

  • 添加唯一约束

    1
    ALTER TABLE users2 ADD UNIQUE username;

  • 添加外键约束

    1
    ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES provinces (id);

  • 添加或删除默认约束

    1
    2
    3
    ALTER TABLE users2 ADD age SMALLINT UNSIGNED NOT NULL;
    ALTER TABLE users2 ALTER age SET DEFAULT 15;
    ALTER TABLE users2 ALTER age DROP DEFAULT;

  • 删除主键约束

    1
    ALTER TABLE users2 DROP PRIMARY KEY;

  • 删除唯一约束 \G指以网格方式呈现

    1
    2
    SHOW INDEXES FROM users2\G;
    ALTER TABLE users2 DROP INDEX username;

  • 删除外键约束

    1
    2
    SHOW CREATE TABLE users2;
    ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;

修改列定义

  • 位置或列的类型有问题，是可以修改的,由大类型修改到小类型可能造成数据丢失

    1
    2
    ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;
    SHOW COLUMNS FROM users2;

修改列名称

  • 既可以修改到列名称又可以修改到列定义

    1
    2
    ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;
    SHOW COLUMNS FROM users2;

修改数据表名称

1
2
3
ALTER TABLE users2 RENAME users3;

RENAME TABLE users3 to users2;

操作数据表中的记录

插入记录

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE users(
	id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(20) NOT NULL,
	password VARCHAR(32) NOT NULL,
	age TINYINT UNSIGNED NOT NULL DEFAULT 10,
	sex BOOLEAN 
);

INSERT users VALUES(NULL, 'Tom', '123456', 15, 1);
INSERT users VALUES(DEFAULT, 'John', '321', 15, 1);
INSERT users SET username='Ben', password='456';

更新记录（单表更新）

1
2
3
UPDATE users SET age = age + 5;
UPDATE users SET age = age - id, sex = 0; 
UPDATE users SET age = age + 10 WHERE id % 2 = 0;

删除记录（单表删除）

1
DELETE FROM users WHERE id = 6;

查询记录

1
2
3
4
SELECT id, username FROM users;
SELECT username, id FROM users;
SELECT users.id, users.username FROM users;
SELECT id AS userId, username AS uname FROM users;

( 未完待续)

转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 jaytp@qq.com

赏

©2016-2020 Yelog

Built with Hexo and 3-hexo theme

×

喜欢就点赞,疼爱就打赏