MySQL学习小结——初级篇
一、列类型
数值型
? 整型
??? TINYINT
??????? TINYINT(M) UNSIGNED ZEROFILL
??????? UNSIGNED:无符号，影响存储范围
??????? M:代表宽度，(在ZEROFILL时才有意义)
??????? ZEROFILL:零填充，(如果某列是ZEROFILL，默认就是UNSIGNED)
??? SMALLINT
??? MEDIUMINT
??? INT
??? BIGINT
? 小数型
??? 浮点型
????? FLOAT(M,D)
????????? FLOAT(M,D)
????????? M:精度(总位数，不包含点)
????????? D:标度(小数位)
????????? MySQL比较特别，可以指定FLOAT的小数位，SQL SERVER和ORACLE都不支持，也应当是不能指定的
????????? e.g. FLOAT(6,2) -9999.99 9999.99
????????? 关于5的进与舍???
??? 定点型,更精确
????? DECIMAL(M,D)
字符型
? CHAR
????? CHAR(M)，定长，M代表宽度，即：可容纳的字符数，0<=M<=255
????? 实存字符N(N<=M)，则实占M个字符，空间利用率=N/M<=100%
????? 如果不够M个字符，右侧用空格补齐，当取出时将尾部空格去除，即若存储内容尾部有空格则会丢失–可用CONTACT(columnName,’!’)查看
? VARCHAR
????? VARCHAR(M)，变长，M也代表宽度，但此处代表可容纳的字节数，0<=M<=65535(ASCII)，若为UTF8,则在65535/3=21845
????? 实存字符N(N<M)，则实占N个字符+(1-2)字节，空间利用率=N/(N+(1-2))<100%
? TEXT
????? 文本类型，存储大段文本，不支持全文索引，不能添加默认值
[以下两个不符合关系型数据库的设计原则]
? ENUM
? SET
时间型
? YEAR
????? 1个字节，表示1901-2155[保留了0000,错误时的选择]
????? 如果输入2位，’00-69’表示2000-2069，’70-99’表示1970-1999
? DATE
????? 日期类型，表示’1000-01-01′-=-‘9999-12-31’
? TIME
????? 时间类型，表示’-838:59:59′-=-‘+838:59:59’，因为还可以表示两个事件之间的时间间隔
? DATETIME
????? 日期时间类型，’1000-01-01 00:00:00′-‘9999-12-31 23:59:59’
????? 注意：在开发中，很少用DATETIME来表示一个需要精确到秒的列
????? 原因：虽然方便查看，但不方便计算，因此使用INT来表示
??????????? 时间戳(是从1970-01-01 00:00:00到当前的秒数)
二、SELECT的5种子句(WHERE, GROUP BY, HAVING, ORDER BY, LIMIT)
WHERE
比较运算符
<, <=, =, >=, >, !=
IN, BETWEEN
逻辑运算符
AND, OR, NOT
模糊查询
LIKE (% => 通配任意字符; _ => 通配单个字符)
e.g. 1、… WHERE column_name LIKE ‘STH%’;
???? 2、… WHERE column_name LIKE ‘STH_’;
GROUP BY
配合5个统计函数使用
MAX, MIN, SUM, AVG, COUNT
HAVING
与WHERE对表进行查询不同，HAVING是对查询结果进行查询
;;;;;;;;;;;
; MySQL试题
CREATE TABLE stu (
name CHAR(4) DEFAULT ”,
course CHAR(4) DEFAULT ”,
score TINYINT UNSIGNED DEFAULT 0
) ENGINE INNODB CHARSET UTF8;
;;;;;;;;;;;
INSERT INTO stu VALUES
(‘张三’,’数学’,90),
(‘张三’,’语文’,50),
(‘张三’,’地理’,40),
(‘李四’,’语文’,55),
(‘李四’,’政治’,45),
(‘王五’,’政治’,30);
; 试查询两门及两门以上不及格同学的平均分
; 1、不用子查询
SELECT name,AVG(score),SUM(score<60) AS fail_num FROM stu GROUP BY name HAVING fail_num>=2;
; 2、利用子查询
SELECT name,AVG(score) FROM stu WHERE name IN (SELECT name FROM (select name,count(*) as fail_num from stu where score<60 group by name having fail_num>=2) AS tmp) GROUP BY name;
;;;;;;;;;;;
ORDER BY
ASC(DEFAULT), DESC
e.g. 1、… ORDER BY 字段1 [ASC/DESC], 字段2 [ASC/DESC]…
LIMIT [OFFSET,] N
OFFSET: 偏移量，可选，DEFAULT: 0
N: 取出条目数
e.g. 1、… LIMIT 2,3
三、子查询
;;;;;;;;;;;;
; 取出每个栏目下面最贵的商品
SELECT * FROM (SELECT goods_id,cat_id,goods_name,shop_price FROM goods ORDER BY shop_price DESC) AS tmp GROUP BY tmp.cat_id;
SELECT goods_id,cat_id,goods_name,shop_price FROM goods WHERE shop_price IN (SELECT MAX(shop_price) FROM goods GROUP BY cat_id);
;;;;;;;;;;;;
WHERE型
内层查询结果作为外层查询的比较条件
FROM型
内层查询结果供外层再次查询(将内层查询结果看成临时表，加 “AS tmp”)
EXISTS型
把外层查询结果代人到内层，看内层是否成立
e.g. SELECT * FROM category WHERE EXISTS (SELECT * FROM goods WHERE goods.cat_id = category.cat_id);
UNION型
合并查询的结果(取select结果的并集)
对于重复的行，默认去掉
如果要不去重复，可以用UNION ALL
要求：各SELECT查出的列数一致
如果子句中用了ORDER BY LIMIT，则子句需要“()”包起来，且应放到所有子句之后
如果子句中只用了ORDER BY，没有LIMIT，则ORDER BY被语法分析器优化掉，不起作用
四、连接查询
左连接，右连接，内连接
SELECT ta.列,tb.列,… FROM
ta LEFT/RIGHT/INNER JOIN tb
ON ta.列=tb.列
WHERE …
左连接与右连接可以相互转化
ta LEFT JOIN tb <===> tb RIGHT JOIN ta
内连接是左右连接的交集，包含了两张表中能相互匹配上的行
;;;;;;;;;;;;
SELECT matchID, t1.teamName AS hostTeamName, matchResult, t2.teamN
