MySQL知识点整理学习-表的使用

建表

建库

create database test;

建表语句

CREATE TABLE employee (
    id int NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    NAME VARCHAR (50) NOT NULL COMMENT '姓名',
    age INT DEFAULT NULL COMMENT '年龄',
    PRIMARY KEY (id)
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '雇员表';

-- 关键字说明
-- AUTO_INCREMENT 自增，但是只适用于包含int的类型，比如int，bigint,tinyint等
-- comment 字段以及表说明
-- PRIMARY KEY设置主键
-- engine设置当前表的引擎，比如INNODB(聚集性)，MyISAM(非聚集性)
-- CHARSET设置当前字符集

建表规则

①【强制】表达是与否概念的字段，必须使用is_xxx的方式命名，数据类型是unsigned tinyint（1表示是，0表示否）

②【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代表很大，因为无法进行预发布，所以字段名称需要慎重考虑。

③【强制】表名不使用复数名词。

④【强制】禁用保留字，如desc、range、match、delayed等，请参考Mysql官方保留字。

⑤【强制】主键索引名为pk字段名；唯一索引名为uk字段名；普通索引名则为idx_字段名

⑥【强制】小数类型为decimal，禁止使用float和double。

⑦【强制】如果存储的字符串的字符长度几乎相等，使用char定长字符串类型。

⑧【强制】表必备三个字段：id，gmt_created，gmt_modified。

⑨【推荐】表的命名最好是加上”业务名称_表的作用”。

⑩【推荐】库名与应用名称尽量一致。

⑪【推荐】如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。

⑫【推荐】字段允许适当冗余，以提高查询性能，但必须考虑数据一致。冗余字段应遵循：1）不是频繁修改的字段。2）不是varchar超长字段，更不能是text字段。

⑬【推荐】单表行数 超过500万行或者表单容量超过2GB，才推荐进行分库分表。

⑭【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。

⑮【参考】表字段尽量不要设置为NULL，因为设置为NULL，MySQL还需要花一到两个字节判断是否为NULL

⑯MySQL中规定一行数据不能超过 65535个字节，因此建表时要注意字段的大小设置

⑰尽量不要用text或者blob字段，这两个字段在MySQL中的存储方式与其他字段不同。（//todo）

⑱参考MySQL官网--------

⑲一定要建唯一主键，因为如果不建主键，InnoDB引擎会自动创建隐藏主键作为主键。该隐藏的主键每个表记录消耗31-35个字节

注：前面带"【】"的是从阿里规则里面摘得，后面不带的是我从MySQL官网摘得

删表

DROP TABLE test;

-- 谨慎删表，可以先备份一下以防误删
-- 注意关联表
-- 先解除外键关系

改表

之前我找mysql资料都是百度一下，看看这个的博客，看看那个的博客，堆在一起。直到有一天，我看到了MySQL官网的文档介绍，我才发现官网说的真详细，给力啊。来张截图先：

接下来说正经事儿 ，alter的用法 ，我从MySQL官网摘抄一些常用用法，并加一些说明：

①建张表

CREATE TABLE t1 (a INTEGER, b CHAR(10));

② 改表名

ALTER TABLE t1 RENAME t2;

③ 修改字段类型，字段名称

ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

④ 新加一个字段

ALTER TABLE t2 ADD d TIMESTAMP;

⑤ 添加索引以及唯一索引

ALTER TABLE t2 ADD INDEX(d), ADD UNIQUE(a);

⑥ 删除字段

ALTER TABLE t2 DROP COLUMN c;

⑦ 添加自增字段

ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL auto_increment,
 ADD PRIMARY KEY (c);

⑧ 查看建表语句

SHOW CREATE TABLE t2;

⑨ 复制表

CREATE TABLE t2 LIKE t1;
ALTER TABLE t2 ADD id INT AUTO_INCREMENT PRIMARY KEY;
INSERT INTO t2 SELECT * FROM t1 ORDER BY col1, col2;

DML

简单sql

① 新增

-- 单条插入
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3);
-- 批量插入
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3), (4,5,6), (7,8,9);

② 更新

-- 更改
update employee set name = '李四', age = 19 where name='张三';

其他复杂更新

-- 以下是从MySQL中摘的示例
-- 建张表
CREATE TABLE items (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    wholesale DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    retail DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    quantity BIGINT NOT NULL DEFAULT 0
);

-- 要降低加价幅度为30％或以上且您的存货少于100的任何商品的零售价格

-- 第一种
UPDATE items,
       (SELECT id FROM items
        WHERE id IN
            (SELECT id FROM items
             WHERE retail / wholesale >= 1.3 AND quantity < 100))
        AS discounted
SET items.retail = items.retail * 0.9
WHERE items.id = discounted.id;

-- 第二种
UPDATE items,
       (SELECT id, retail / wholesale AS markup, quantity FROM items)
       AS discounted
    SET items.retail = items.retail * 0.9
    WHERE discounted.markup >= 1.3
    AND discounted.quantity < 100
    AND items.id = discounted.id;

③ 删除

delete from employee where name = '小明';

删除除了某些数据外其他数据的处理方法（InnoDB引擎，摘自MySQL官网）

-- 根据条件，筛选出不需要删除的数据到另外一张表
INSERT INTO t_copy SELECT * FROM t WHERE ... ;

-- 重命名表，将不需要删除数据的表命名成现有表名
RENAME TABLE t TO t_old, t_copy TO t;

-- 直接删除原有表
DROP TABLE t_old;

另外，MySQL官网对于MyISAM引擎删除做了如下说明（我看英文官网，大致能看懂MySQL的说明，形成以下的理解）：

因为MyISAM引擎的结构是索引和数据分开存储的，所以做删除操作时，MyISAM并不是删除数据，而是删除索引，原来的数据还是占着位置，如果以后插入数据正好处在同样索引位置，则会替换原有数据。但是如果插入数据和原有索引不同，则原有数据会一直存储在磁盘上，造成空间浪费。比如：创建自增表；插入很多行，每行数据的索引都比上个要大；删除索引值比较低的数据块等。因此，MyISAM删除数据之后，希望使用OPTIMIZE来回收优化表空间位置，执行语句：

OPTIMIZE table table_name;

④ 查询

完全匹配查询

select * from employee where name = '李四';

模糊查询

select * from  employee where name like '%李%';

多条件查询

select * from  employee where name like '%李%' and age = 18;

分页查询

-- 从第一条开始，向后查询10条
select * from employee where name like '%李%' limit 1,10;

-- 查询前5条
SELECT * FROM tbl LIMIT 5;

多表查询

SELECT t1.name, t2.salary FROM employee t1, info t2
  WHERE t1.name = t2.name;

排序查询

SELECT college, region, seed FROM tournament
  ORDER BY region, seed;

SELECT college, region AS r, seed AS s FROM tournament
  ORDER BY r, s;
-- 默认第一个字段是1，以此类推，但是不推荐这样使用。
SELECT college, region, seed FROM tournament
  ORDER BY 2, 3;

拼接字段值

-- 将last_name和first_name拼接起来，中间使用‘,’间隔
SELECT CONCAT(last_name,', ',first_name) AS full_name
  FROM mytable ORDER BY full_name;

多表查询

SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
  WHERE t1.name = t2.name;

复杂sql

①inner...join/left...join/right...join

SELECT * FROM table1, table2;

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;

SELECT * FROM table1 LEFT JOIN table2 USING (id);
-- using 代表里面的id是共有的字段去比较，相当于table1.id=table2.id

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
  LEFT JOIN table3 ON table2.id = table3.id;

② union

简单联合查询

SELECT a FROM t1  where a < 10
UNION
SELECT a FROM t2  where a < 20

默认使用union是将重复数据去重的，union和union distinct用法相同，union all是不去重，查询所有

union all

SELECT a FROM t1
UNION ALL
SELECT a FROM t2

union和order by、limit结合使用

对每个union子查询里进行排序限制

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

对查询结果集进行排序、行数限制

(SELECT a FROM t1 WHERE a=10 AND B=1)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;

order by和limit的位置不同，对结果的影响也不同。

查询多个表，结果按照表顺序排序，添加一个常量字段，用来标记顺序，最后使用order by 当前常量字段对结果进行排序

(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
UNION
(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col;

如果在上面按照表排序进行排序之后，还要进行表内排序，可以在order by sort_col之后再加上其他字段

(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
UNION
(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col, col1a;

union中不能使用聚合函数

③ 子查询

示例

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

ANY关键字，代表任意一个，比如下面查询的含义是：从t1表中找出存储于t2表中的数据

SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);

用在否定中，比如下面查询的含义是：从t1表中找出不存在于t2表的数据。同时，any可以替换成some，但是some不常用

SELECT s1 FROM t1 WHERE s1 <> ANY  (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2);

ALL关键字，代表所有，比如下面的查询的含义是：从t1表中找出比t2表所有数据都大的数据

SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);

用在否定中时，可以起到和not in相同的作用。下面查询的含义是：从t1中找出不存在于t2表的所有数据

SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

子查询可以多行比较

SELECT * FROM t1
  WHERE (col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);

④ 临时表

我看了一下临时表相关资料，MySQL中临时表，按需创建并且自动销毁，对于纯内存的数据特别适合，但为了避免内存不可控，实际上不仅仅有内存临时表，还有磁盘临时表。大致内容如下：

临时表包括外部临时表和内部临时表。外部临时表是通过语句create temporary table...创建的临时表，临时表只在本会话有效，会话断开后，临时表数据会自动清理。内部临时表主要有两类，一类是information_schema中临时表，另一类是会话执行查询时，如果执行计划中包含有“Using temporary”时，会产生临时表。

MySQL中临时表的核心特征，按需创建并且自动销毁，对于纯内存的数据特别适合，但为了避免内存不可控，实际上不仅仅有内存临时表，还有磁盘临时表

创建外部临时表

CREATE TEMPORARY TABLE salary_analy(
	depart_name VARCHAR(10),
	date_year INT,
	date_month TINYINT,
	total_salary DECIMAL(12,2),
	avg_salary DECIMAL(7,2)
);

删除临时表

DROP TEMPORARY TABLE IF EXISTS salary_analy;

内部临时表

第一个是union去重时，上面的内容也说过，直接使用union会将结果集去重。临时表在这里的作用是，将union连在一起的查询语句结果存储在临时表中

select * from t1_normal union select * from t1_normal; 

第二个是group by 时，会将结果集放在临时表，之后分组排序。

select c1,count(*) as count from t1_normal group by c1;

这次对表的学习暂时到这儿，下次总结学习函数与运算符。这篇文章我参考MySQL官网以及网上一些其他资料形成的，如果有其他的知识点没有涵盖或者有错误的话，欢迎留言！学如逆水行舟，不进则退。希望大家一起成长！