mysql笔记

2022-11-25 17:28:14

基本操作：

1. 登入数据库
mysql -uroot -proot -h 127.0.0.1

2. 查看数据库、表
show databases
   show datbles

3. 查看指令的用法
? create

4. 创建数据库
create database databaseName；

5. 查看创建过程,加\G纵向排列
show create database databaseName \G ;

6. 选择数据库
use databaseName;

7. 创建数据库
COLLATE：字符的存储方式，有三种值:
   utf8_bin:是将字符串中的每个字符用二进制数据存储，区分大小写。
       utf8_general_cs:(case sensitive):大小写敏感
       utf8_general_ci:(case insensitive):大小写不敏感

create table IF EXISTS tableName(
id int(11) primary key auto_increment,
name varchar(32) not null UNIQUE,
sex char(1) default '男'，
deptId int(11),
-- 定义主键
primary key(id),
-- 定义外键
CONSTRAINT fk_tableName_deptId FOREIGN KEY(deptId) REFERENCES tb_dep1(id) on delete cascade on update cascade, -- 删除、更新联动
-- 定义唯一健
CONSTRAINT STH UNIQUE(name)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci auto_increment=1000 --从1000开始自增

8. 查看表结构
desc tableName;

9. 外键：
为保证数据的完整性而定义：
   一个表的外键必须为主键，可以是本表，也可以使其他表。
   外键字段添加数据必须为主键中已有的字段，或者是null。
   创建外键时父子表中的字段类型必须一致,父子表的存储引擎必须都是InnoDB。

外键的联动策略：
   cascade: 主从联动
       no action: 从表限制主表

   如果一个主键是其他表的外键，则这个字段删除前必须删除外键所在的记录。

   创建外键： CONSTRAINT fk_tableName_deptId FOREIGN KEY(deptId) REFERENCES tb_dep1(id)
   alter table tableName add FOREIGN KEY(deptId) REFERENCES tb_dep1(id)

10. 修改表名
alter table oldTableNmae rename [to] newTablName

11. 修改字段类型、排列顺序
alter table tableName modify column varchar(32) [frist][after column]

12. 修改字段名
alter table tableName change oldcolumn newcolumn varchar(32)

13. 添加字段（在第一列或在某一列之后）
alter table tableName add newcolumn varchar(32) [frist][after column]

14. 删除字段
alter table tableName drop column

15. 修改表的引擎
alter table tableName engine=InnoDB;

16. 删除、添加外键约束
alter table tableName drop FOREIGN KEY(deptId) REFERENCES tb_dep1(id);
   alter table tableName add CONSTRAINT fk_tableName_deptId FOREIGN KEY(deptId) REFERENCES tb_dep1(id) on delete no action on update cascade

17. 添加主键
alter table tableName ADD primary key(id);

18. 添加唯一约束
添加唯一约束即给该字段天机了一个索引
alter table tableName add unique(id);

19. 删除约束
alter table tableName drop index indexName; -- 删除有名称索引
   drop index columnName on tableName; -- 删除没名称索引

20. 查看警告:
show warnings;

21. 查看数据库大小
select table_name,index_length,data_length from information_schema.tables where table_schema like 'schoolBD'

数据类型：

20. 整形数据
tinyint 1字节有符号 -127-127 无符号0-255
   smallint 2字节
   mediumint 3字节
   int 4字节
   bigint 8字节

   int(3)和int(11) 在没设定 zerofill 前没任何区别，设定后不足指定位数的数据会以0填充保存，如果位数超过指定位置但不超过11位，则没任何区别。

   int(11) zerofill;

21. 浮点类型与定点类型：
浮点数有两种:单精度浮点类型(float,4字节)和双精度浮点类型(double,8字节)。
   定点数：只有一种，decimal(M,N) M+2个字节。
   区别：
   1. 浮点型保存的是数据的近似值，在不指定精度时其精度与实际的计算机硬件和操作系统有关，decimal在不指定精度时，默认为(10,0).
   2. 浮点时的优势在于同样的存储空间能表示更多的数据，定点数的优势在于他以字符串形式存储，精度较高。涉及金额的计算和存储一般都使用decimal。
   1. 数据超过设定的位数时都会被截取保存，但只有decimal在截取时数据库会给出相应的警。

22. 时间类型：
YEAR： 1字节，范围：1901-2155，如果存入0~69，则保存为2000~2069，如果存入70~99，则保存1970~1999，存入00保存2000. 存入000则非法保存为0000.
   Time: 3字节范围：-838:59:59 ~ 838:59:59
   '2 10' 表示2天10小时，即58:00:00.
   '23:10' 表示23天10时，即23:10:00.
   '10' 表示10秒，即00:00:10.
           '111010' 等于11:10:10
           '237010' 是错误

   Date: 3字节范围: 1000-01-01 ~9999-12-31
   可以直接以字符串输入，有没有-都一样。甚至可以以任何符号作为分隔符。
   dateTime: 8字节范围：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
   可以直接以字符串输入，有没有-都一样。甚至可以以任何符号作为分隔符。
       70-99 表示1970-1999
       00-69 表示2000-2069


   TimeSTAMP： 4字节范围：1970-01-01 00:00:01 UTC 2038-01-19 03:14:07 UTC
   可以直接以字符串输入，有没有-都一样。甚至可以以任何符号作为分隔符。
   datetime与timestamp的区别：
   1. 存储的空间不一样。
   2. 数据表示范围不一样。
   3. timestamp存储的值会随mysql的时区变化而变化，而datatime不会。


23. 字符串类型
1. char: 固长，N个字节，末尾有空格不会被保存
   2. varchar：变长，实际长度+1个字节，末尾有空格会被保存
   3. enum:1-2字节，插入时数据只能插入枚举中已有的数据，插入数据或插入编号是等价的。 create table tableName (e enum('one','two','three'))
   4. set：最大64个成员。与enum类似，只能插入已定义的数据，不同的是se插入会自动去重和排序。create table tableName (s set('a','b','c'))

24. 数据类型选择：

1. 存储满足当前需求，尽量使用最小的字段类型。适当考虑未来的需要。
   2. 精度要求优先性：decimal>double>float,有精度和计算要求优先选择decimal
   3. 整数按照存储长度选择。一般用mediumint即可。
   4. 如果是固定长度、或存储不大的字符串，优先选择char，其效率比varchar更高，但它浪费存储空间。
   5. 如果是MYISAM存储引擎，推荐使用char，这样可以使整个表静态化，从而使数据检索更快，即用空间换时间。
   如果是Innodb存储引擎，推介使用varchar，因为Innodb数据表的存储不分固定长度和可变长度，因此使用char不一定比varchar更好，但是它更节省空间。
   6. 存储长字符串用text，存储二进制文件用blob。

25. 运算符：

1. = 与安全等于运算符<=> 区别：=不能用来比较null，<=>可以用来比较null。相等返回1，不等返回0.
2. !=与<> 没任何区别。都不能用来比较null。

26. 字母比较运算符：
1. least(0,20,30)，不能比较null， greatest(10,20,30)

27. 视图
视图是一种虚表，它并不真实存在，它依赖于原表中的数据。
创建视图可以以表为基表，也可以以视图为基表。

1. create view tableNameView as select columnName1 as 列1,columnName2 as 列2 from tableName;
2. create view tableNameView (列1，列2) as select columnName1,columnName2 from tableName;

   视图的增删改：
   1. 如果视图以单表为基表，则可以删除、新增，多表不行。
   2. 新增时基表中不在视图中的字段允许为空。
   3. 视图更新只能更新视图中的字段，不能对基表中不在视图中的字段进行操作。
   4. 如果视图中数据与基表不是一对一关系，或者定义视图时使用了聚合函数、having、group by、join等sql块会使得视图不可更新。
   5. 尽可能的不要使用视图去更新数据，它得使得更新操作更加复杂而导致失败。

   视图的三种算法：
   1. undefined: mysql优化器看着办。
   2. merge: 视图定义的sql与外层合并
   3. temptable：视图定义的sql先执行生成临时表再执行外层sql。


28. 索引

1. Innodb和MyISAM都只支持B+树，MEMORY和HEAP不仅支持B+树，还支持HASH索引。
   2. 任何定义了约束的字段，不论主键、唯一性、外键约束，都会自动创建索引。
   3. key和index关键字等价。
   4. 查看索引结构：
   show index from tableName;

码农公寓

相关文章