您当前的位置:首页 > 计算机 > 软件应用 > 数据库 > MySQL

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

时间:02-01来源:作者:点击数:

建表

建库

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官网以及网上一些其他资料形成的,如果有其他的知识点没有涵盖或者有错误的话,欢迎留言!学如逆水行舟,不进则退。希望大家一起成长!

方便获取更多学习、工作、生活信息请关注本站微信公众号城东书院 微信服务号城东书院 微信订阅号
推荐内容
相关内容
栏目更新
栏目热门