2021-08-02复习总结mysql的杂七杂八

基本数据长度（M，D）

• 后面的数字是显示数据的宽度，最大有效宽度为255。
• 显示宽度与数据大小或类型包含的值的范围无关，
• 只是当设置了zerofill的时候，当实际值长度小于M的时候，在显示的时候自动补零

整型

浮点型与定点型

Float(6,2)、Double(6,2)、Decimal(6,2)；

日期与时间类型

TIMESTAMP

DATETIME

TIME

DATE

YEAR

字符串类型

CHAR、VARCHAR、BLOB、TEXT、ENUM、SET

• CHAR和VARCHAR区别

  char如果插入的长度小于定义长度时，则用空格填充；varchar小于定义长度时，还是按实际长度存储，插入多长就存多长。

• TEXT

  TINYTEXT

  TEXT

  MEDIUMTEXT

  LONGTEXT

二进制类型包括

• BINARY、
• VARBINARY、
• BIT、
• TINYBLOB、
• BLOB、
• MEDIUMBLOB、
• LONGBLOB。

MySQL中没有Bool或Boolean类型

常用约束条件有以下这些

1. UNSIGNED ：无符号，值从0开始，无负数
2. ZEROFILL：零填充，当数据的显示长度不够的时候可以使用前补0的效果填充至指定长度,字段会自动添加UNSIGNED
3. NOT NULL：非空约束，表示该字段的值不能为空
4. DEFAULT （NULL）：表示如果插入数据时没有给该字段赋值，那么就使用默认值
5. PRIMARY KEY：主键约束，表示唯一标识，不能为空，且一个表只能有一个主键。一般都是用来约束id
6. AUTO_INCREMENT：自增长，只能用于数值列，而且配合索引使用,默认起始值从1开始，每次增长1
7. UNIQUE KEY：唯一值，表示该字段下的值不能重复，null除外。比如身份证号是一人一号的，一般都会用这个进行约束
8. FOREIGN KEY：外键约束，目的是为了保证数据的完成性和唯一性，以及实现一对一或一对多关系

SHOW DATABASES;

USE `sakila`;

SHOW tables;

USE `testdb`;

show tables;

delete `student`;

create table if not exists `student`(
	`id` INT(3) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`age` INT(3) DEFAULT NULL COMMENT '年龄',
	`name` varchar(20) NOT NULL DEFAULT 'niming' COMMENT '姓名',
	`pwd` varchar(20) NOT NULL DEFAULT '123456'COMMENT '密码',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` varchar(100) DEFAULT NULL COMMENT'家庭住址',
	PRIMARY Key(`id`)	
)ENGINE=Innodb default charset=utf8;

select * from `student`;

INSERT INTO `student` value(201803469, 21, "SImon", "123123", NULL, "hubeihuanggang");

-- DESC 后面只能接表名
DESC `student`;

create table `aq1`(
 `id` int 
)engine=Innodb default charset=utf8mb4;


create table `aq2`(
 `id` int 
)engine=MyISAM default charset=utf8mb4;

show create DATABASE `testdb`;

操作数据库-》操作数据表-操作数据项

1. 数据库操作

• create database if not exists testdb
• drop database if exists testdb
• use testdb
• show databases;
• show create database school:查看字符集编码

2、数据表操作

• 增 create table if not exists teacher();

• 删 drop table if exists teacher

• 清空表（）

  • DELETE（不指定条件，则清空表）
  • TRUNCATE（推荐）
  • 区别
    • 相同 : 都能删除数据 , 不删除表结构 , 但TRUNCATE速度更快
    • 不同 :
      • 使用TRUNCATE TABLE 重新设置AUTO_INCREMENT计数器
      • 使用TRUNCATE TABLE不会对事务有影响 （事务后面会说）

• 改结构 alter

  • 改表名：alter table 旧表名 rename as 新表名

    • alter table readerreader rename as reader;
      

  • 添加字段：alter table 表名 add 字段名 约束

    • alter table reader add rdAge int NOT NULL comment '读者年龄';
      

  • 修改字段：

    • alter table 表名 modify 字段名

  • 修改约束：-- 创建外键方式二 : 创建子表完毕后,修改子表添加外键
    ALTER TABLE student
    ADD CONSTRAINT FK_gradeid FOREIGN KEY (gradeid) REFERENCES grade (gradeid);

  • 删除字段 : ALTER TABLE 表名 DROP 字段名

    • alter table reader drop rddage;
      

• 改内容 update：UPDATE 表名 SET column_name=value [,column_name2=value2,…] [WHERE condition];

• 删内容delete

• 加内容insert：

  • INSERT INTO 表名[(字段1,字段2,字段3,…)] VALUES(‘值1’,‘值2’,‘值3’)
  • INSERT INTO 表名 values(’’,’’,’’,’’,’’)括号里面的数据要与表中的字段一一对应

• 查看表结构 DESC + 表名==(DESC 只能对表名用)==

• 查看表定义：show create table student

• 查select

select

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
    [left | right | inner join table_name2]  -- 联合查询
    [WHERE ...]  -- 指定结果需满足的条件
    [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
    [HAVING]  -- 过滤分组的记录必须满足的次要条件
    [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
    [LIMIT {[offset,]row_count | row_countOFFSET offset}];
    --  指定查询的记录从哪条至哪条

select rdName 用户名, rdDept 用户院系, bkName 用户借阅的书籍名称, bkPress 书记出版社, bkPrice 书籍价格 from reader r 
left join  borrow br 
on r.rdID = br.rdID 
inner join book bk 
on br.bkID = bk.bkID;

±----------±----------------------±----------------------±-------------------±-------------+
| 用户名 | 用户院系 | 用户借阅的书籍名称 | 书记出版社 | 书籍价格
±----------±----------------------±----------------------±-------------------±-------------+
| 王桃群 | 计算机科学学院 | 古代汉语 | 中华书局 | 20.40 |
| 连笑脸 | 管理学院 | 当代教育心理学 | 北京师范大学 | 37.20 |
| 徐淼 | 物理学院 | 当代教育心理学 | 北京师范大学 | 37.20 |
| 孙小妹 | 英语学院 | 古代汉语 | 中华书局 | 20.40 |
±----------±----------------------±----------------------±-------------------±-------------+

where子句

运算符：=, !=, >, <,>=, <=, BETWEEN AND, AND, OR

AS 子句作为别名（AS 可以省略不写）

作用：

• 可给数据列取一个新别名
• 可给表取一个新别名
• 可把经计算或总结的结果用另一个新名称来代替

DISTINCT关键字的使用

like关键字：% _ 有特殊字符的话要用/进行转义

in关键字：在什么之内：

select * from student where sId in (201803469,201803470) and sex = "男";
--查找出这些学号中性别为男的学生信息

 select * from reader where rdID in ("rd2017001", "rd2017002", "rd2017003") and rdType = 2;

联表查询

• 左连接
• inner链接
• 右连接

use mysqldesc user;grant select(zno) on specialty to 'Simon'@'localhost';use qqq;delimiter ##create PROCEDURE getnamebysno(In xh char(10), out name char(20))Begin select sname into name from student where sno = xh;END ##delimiter ; set @nameq = null;call getsnamebysno(201803469, @nameq);select @nameq;create procedure getsnamebysno(In xh char(10), out name char(20))BEGIN select sname into name from student where sno = xh;ENDcall getallstudent();create USER `Simon`@`localhost` identified by '123123';insert into specialty value (MD5(1010), 'xx');select host, user from mysql.`user`; create table `specialty`(	`zno` varchar(5) NOT NULL primary key,	`zname` varchar(50) Not nULL)engine=InnoDB charset = utf8mb4;create table `course`(	`cno` varchar(8) NOT NULL primary key,	`cname` varchar(50) NOT NULL,	`ccredit` int(11) NOT NULL,	`cdept` varchar(20) NOT NULL)engine=InnoDB charset = utf8mb4;create table `student`(	`sno` varchar(10) NOT NULL PRIMARY KEY,	`sname` varchar(20) NOT NULL,	`ssex` varchar(2),	`sbirth` Date NOT NULL,	`sclass` varchar(11) NOT NULL,	`zno` varchar(4) NOT NULL,	constraint `aa` foreign key (zno) references specialty(zno))engine = InnoDB charset = utf8mb4;drop table `sc`;	--超过一个主键	create table `sc`(	`sno` varchar(10) NOT NULL,	`cno` varchar(8) NOT NULL,	`grade` float(4) NOT NULL,	primary key (`sno`, `cno`),	constraint `bb` foreign key (`sno`) references `student`(`sno`),	constraint `cc` foreign key (`cno`) references `course`(`cno`))engine = InnoDB charset = utf8mb4;添加外键alter table scadd foreign key cno references course(cno); select sno 学号,grade 成绩 from `sc` where grade between 87 and 100; having zno = 1407  limit 0,2select * from `student` GROUP BY `ssex`;select all* from student group by zno having zno in (1214,1407,1102);set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';select version();select @@sql_mode;

引擎的区别

• * . frm – 表结构定义文件

• * . MYD – 数据文件 ( data )

• * . MYI – 索引文件 ( index )

• InnoDB类型数据表只有一个 *.frm文件 , 以及上一级目录的ibdata1文件

• MyISAM类型数据表对应三个文件 :

create table `specialty`(	`zno` varchar(5) NOT NULL primary key,	`zname` varchar(50) Not nULL)engine=InnoDB charset = utf8mb4;create table `course`(	`cno` varchar(8) NOT NULL primary key,	`cname` varchar(50) NOT NULL,	`ccredit` int(11) NOT NULL,	`cdept` varchar(20) NOT NULL)engine=InnoDB charset = utf8mb4;create table `student`(	`sno` varchar(10) NOT NULL PRIMARY KEY,	`sname` varchar(20) NOT NULL,	`ssex` varchar(2),	`sbirth` Date NOT NULL,	`sclass` varchar(11) NOT NULL,	`zno` varchar(4) NOT NULL,	constraint `aa` foreign key (zno) references specialty(zno))engine = InnoDB charset = utf8mb4;drop table `sc`;	--超过一个主键	create table `sc`(	`sno` varchar(10) NOT NULL,	`cno` varchar(8) NOT NULL,	`grade` float(4) NOT NULL,	primary key (`sno`, `cno`),	constraint `bb` foreign key (`sno`) references `student`(`sno`),	constraint `cc` foreign key (`cno`) references `course`(`cno`))engine = InnoDB charset = utf8mb4;添加外键alter table scadd foreign key cno references course(cno); 

建外键注意事项

建外键

• 在建表的时候在结尾添加一个或多个外键

• 或者表建完后，用以下语追加外键

• alter table scadd foreign key cno references course(cno); 
  

注意

• 每张表的字符集编码charset和collate要一样，要么为utf8要么为utf8mb4
• 字段的类型和字长必须一致
• 如果原本是utf8后来改为了utf8mb4，这只是表级的编码改了，列级没有改，还需要将列级也修改一下

分组查询GROUP BY

每次只能查出该属性的所有类别的从前到后的一组数据，

比如说学生表中只有5种专业，哪怕有100个学生，group by查询也只能查到5条记录，这五条记录是在学生表最前面的记录。