MySQL使用(二)

数据库的字段类型

数值

• tinyint：1个字节
• smallint：2个字节
• mediumint：3个字节
• int：4个字节
• bigint：8个字节
• float：4个字节
• double：8个字节
• decimal：字符串形式的浮点数，多用于金融计算

字符串

• char：固定大小字符串，0~255
• varchar：可变字符串，0~65535
• tinytext：2⁸-1
• text：2¹⁶-1

时间日期

• date：YYYY-MM-DD，日期
• time：HH: MM: SS，时间
• datetime：YYYY-MM-DD HH: MM: SS
• timestamp：时间戳
• year：年份

null

• 没有值，未知
• 注意：不要使用null进行运算

数据库的字段属性

• Unsigned：无符号整数，不能为负数

• zerofill：不足的位数，使用0来填充

• auto_increment：自增，自动在上一条记录的基础上+1（默认）

  • 通常用来设计唯一的主键，必须是整数类型

• not null：非空

• default：默认值

• 常用字段：

  • id：主键
  • version：乐观锁
  • is_delete：伪删除
  • gmt_create：创建时间
  • gmt_update：修改时间

创建数据库表

CREATE TABLE IF NOT EXISTS `student` (
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT ‘学号‘,
	`name` VARCHAR(30) NOT NULL DEFAULT ‘匿名‘ COMMENT ‘姓名‘,
	`pwd` VARCHAR(20) NOT NULL DEFAULT ‘123456‘ COMMENT ‘密码‘,
	`sex` VARCHAR(2) NOT NULL DEFAULT ‘女‘ COMMENT ‘性别‘,
	`birthday` DATETIME DEFAULT NULL COMMENT ‘出生日期‘,
	`address` VARCHAR(100) DEFAULT NULL COMMENT ‘家庭住址‘,
	`email` VARCHAR(50) DEFAULT NULL COMMENT ‘邮箱‘,
	PRIMARY KEY (`id`) 
)ENGINE=INNODB DEFAULT CHARSET=utf8

数据库引擎

• MYSIAM：节约空间，速度较快
• INNODB：安全性高，事务的处理，多表多用户操作

在物理文件上的区别：

• INNODB：*.frm文件，以及上级目录下的ibdata1文件
• MYSIAM：
  • *.frm 表结构的定义文件
  • *.MYD 数据文件
  • *.MYI 索引文件

字符集编码

• CHARSET=utf8
• 在my.ini中配置默认编码
  • character-set-server=utf8
• 默认Latin1，不支持中文

修改和删除数据表字段

修改表

• 修改表名：ALTER TABLE 旧表名 RENAME AS 新表名;
• 增加字段：ALTER TABLE 表名 ADD 字段名 列属性;
• 修改字段：
  • 重命名：ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性;
  • 修改约束：ALTER TABLE 表名 MODIFY 字段名 列属性;
• 删除字段：ALTER TABLE 表名 DROP 字段名;

外键

• 在创建表的时候，增加约束

  • KEY `FK_gradeid` (`gradeid`),
    CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
    

• 在修改表的时候，增加约束

  • ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
    

• 少用物理外键，多用链表查询

DML操作数据

• insert：insert into 表名([字段1，字段2... ]) values(‘值1‘,‘值2‘,...),(‘值1‘,‘值2‘,...)

• update：update 表名 set 字段1 = 值1, 字段2 = 值2,... [where 条件]；

  • 条件操作符：=、!=、>、<、>=、<=、between..and...(闭区间)、and、or

• delete：delete from 表名 [where 条件]

  • innoDB重启后，自增列从1开始(存在内存中，断电即失)
  • MyISAM重启后，继续从上一个自增量开始

• truncate：完全清空表，表的结构和索引约束不会变

  • 自增列，计数器会归零
  • 不会影响事务

DQL查询数据（重要）

基本语法

• 查询：select 字段 from 表

• 别名：``字段 as 别名(别名不用加引号)，表 as 别名`

• 去重：distinct

• 可以使用表达式来改变显示的值

• 模糊查询：is null、is not null、between、like、in

  • like：匹配字符串，%(0到任意个字符)，_(一个字符)
  • in：a in (a1,a2,a3)

• 具体语法：(顺序不能换)

SELECT [ALL | DISTINCT]
{* | table.* | [table.field[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}];

联表查询：join

• 七种join理论：(MySQL中没有FULL JOIN)

思路：

1. 分析需求，分析查询的字段来自哪些表
2. 确定使用哪种连接查询
3. 确定交叉点(这两个表中哪个数据时相同的)

举例：

• 学号和姓名在student表中
• 科目名称和成绩在result表中
• 两者相同的是学号

SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
RIGHT JOIN `result` r
ON r.`studentno` = s.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`

• on为连接方式，where为筛选条件

自链接

• 一张表拆为两张一样的表

举例：

SELECT a.`categoryName` AS ‘父栏目‘,b.`categoryname` AS ‘子栏目‘
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`

排序

• order by 字段名 {升序ASC | 降序DESC}

分页

• limit 起始值 页面的大小

子查询

• 在where语句中嵌套子查询语句
• where field_name = (select field from ...)
• where field_name in (select field from ...)

分组过滤

• group by：通过什么字段分组
• having：分组后的过滤条件

例子：

• 查询不同课程的平均分、最高分、最低分，其中平均分大于80

SELECT subjectname,AVG(studentresult) AS 平均分,MAX(studentresult),MIN(studentresult)
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
GROUP BY r.subjectno
HAVING 平均分>80

MySQL函数

• 官网

常用函数

• 数学运算：
  • abs，ceiling(向上取整)，floor(向下取整)，rand，sign(返回一个数的符号)
• 字符串函数：
  • char_length，concat(合并)，insert，lower，upper，instr(第一次出现的位置)，replace，substr，reverse
• 时间和日期函数：
  • curdate，now，localtime，sysdate
• 系统函数：
  • user，version

聚合函数

• count，sum，avg，max，min
• count：
  • count(字段)：会忽略所有的null值，列名为主键时最快
  • count(*)：不会忽略null值，只有一个字段时最快
  • count(1)：不会忽略null值，没有主键时最快

MySQL使用(二)