MySQL使用之一_数据库、数据表、数据记录基本操作、CRUD

本系列为MySQL入门，不进行理论和系统介绍，唯一目标是熟练SQL语法，至少能通过MySQL实现Excel中的各种查询功能。本系列只关注功能的实现，不关注代码效率。

一个问题：MySQL每个命令必须以分号结尾，平时在navicat中写SQL时，觉得这很麻烦，但是仔细想一下，如果在命令行中写SQL，一个长SQL中间怎么换行？正是由于每个命令必须以分号结尾，所以命令行中是可以直接换行的，直到最后输入分号，才会将前面的命令作为整体执行，这时分号就显得很方便了！！！

一 数据库操作

主键、外键、索引、联合主键
0.键是一种关系，字段是一种实体
1.一张表可以不创建主键
2.主键是表中记录的标识符，具有唯一性，SQL会自动为作为主键的字段建立索引，主键索引；
3.外键是指，主表的B字段对应于从表的主键字段，则称B字段是主表的外键；若为主表B字段创建从表主键中没有的值，SQL会自动提示并禁止为B字段创建该值；若改变从表主键某值的表示，主表B字段中对应值会同步发生变化，是为级联操作；
4.索引，是对表中字段建立索引，索引不要求字段值唯一，未建立索引时SQL查询是逐行比对，建立索引后则根据索引查询；
5.外键，由外键关系关联起来的字段必须都建立索引，即主表的B字段、从表的主键字段，都要先建立索引才能创建外键关系；
6.级联操作，如4中说述，由外键关系关联的主从表中的字段的同步操作；级联更新、级联删除；
7.联合主键，表中多个字段联合起来作为主键；由于作为主键的字段的值必须唯一，而实际中有可能A字段的值不唯一，A+B字段的值唯一，则可用A、B字段作为联合主键；如A字段为手机品牌名，B字段为手机型号名，这种情况；
10.SQL语句代码实现：

数据库操作分为：DDL、DML、DCL

https://www.cnblogs.com/kawashibara/p/8961646.html

DDL：Data Definition Language，数据定义语言；操作对象为表，如：CREATE、DROP、ALTER；
DML：Data Manipulation Language，数据操控语言；操作对象为记录，如：INSERT、DELETE、UPDATE、SELECT；
DML：Data Control Language，数据控制语言；操作对象为用户权限，如：GRANT、REVOKE；

二 数据表操作

2.1 创建表 CREATE

创建表
见教程：

根据查询结果创建表
CREATE TABLE emptest2 as select id,name from emptest;

https://zhidao.baidu.com/question/1303011227781846619.html
创建表时可能会报错：“No database selected”，是因为没有选择数据库，需要先通过"use database_name;"选择数据库；

2.2 删除表 DROP

DROP TABLE emptest;
DROP TABLE if exist emptest;

2.3 修改表 ALTER

https://blog.csdn.net/qustdjx/article/details/8875827

ALTER TABLE table_name ADD/DROP/MODIFY COLUMN/INDEX [....]; // [....]部分的写法和CREATE中相同

修改表名：
alter table emptest rename to/as testemp;

修改列：增加、删除、修改
ALTER TABLE testemp ADD COLUMN age INT NOT NULL; // 不要COLUMN关键字也许，但还是写上吧，保持ALTER操作的结构规律
ALTER TABLE testemp DROP COLUMN age;
ALTER TABLE testemp MODIFY(name VARCHAR(30));

2.4 查看表 DESC

DESC employee;

2.5 索引

https://www.runoob.com/mysql/mysql-index.html

索引类型：
PRIMARY：主键索引
UNIQUE：唯一性索引
INDEX：普通索引
FULLTEXT：全文索引

通过ALTER关键字给字段增加或者删除索引：虽然CREATE也能创建索引，但还是使用ALTER吧

ALTER TABLE table_name ADD INDEX/UNIQUE/FULLTEXT (`name`(10)); // 设置索引
ALTER TABLE table_name DROP INDEX/UNIQUE/FULLTEXT(`name`(10)); // 删除索引

ALTER TABLE table_name MODIFY `name` INT NOT NULL; // 设为主键前，先设置为非空
ALTER TABLE table_name PRIMARY KEY(`name`); // 设置为主键
ALTER TABLE table_name DROP PRIMARY KEY; // 删除主键

2.6 补充：

创建表最少设定

CREATE TABLE `test_table`(
   `id` INT,
   `name` VARCHAR(20)
);

注意：
1.表名、字段名要用反引号，不能使用双引号或单引号。
2.INT可以不写多少位，VARCHAR必须要指明多少位；
3.最后一个字段末尾不能加逗号；

其他设定

https://www.runoob.com/mysql/mysql-create-tables.html

1.先判断表是否存在，不存在才创建；或者，若存在则删除。
2.设定字段值长度以及是否可为空；
3.设定主键、自增长；
4.设定表的存储引擎和编码方式。

以下字段名可以不带``，只有字段名和MySQL关键字相同时，才必须要带；

CREATE TABLE IF NOT EXISTS `runoob_tbl`(		# 不存在则创建
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

也可以加上：
DROP TABLE IF EXISTS `potential_type`;			# 存在则删除

创建表时：
设置字段：`id` INT NOT NULL AUTO_INCREMENT
设置主键：PRIMARY KEY(`id`)
设置索引：UNIQUE idx_name(`name`(10)) // 索引类型 索引名(字段名(索引长度))

Navicat图形界面创建表

1.打开数据库，右键数据库，选择新建查询；
2.写SQL语句，并运行；
3.右键表单列表，选择刷新；（不刷新不显示）

三 数据记录操作

3.1 基本的CRUD

https://www.cnblogs.com/leslie12956/p/11826779.html

插入、删除、更新操作后都需要提交事务，如果在命令行中操作需要COMMIT，在navicat中不需要。

3.1.1 插入记录 INSERT

INSERT INTO testemp (id,name,gender) VALUES (1,'张三','M');
COMMIT;

3.1.2 删除记录 DELETE

DELETE FROM testemp WHERE id=2;
COMMIT;

清空表
DELETE FROM testemp;
COMMIT;

3.1.3 修改记录 UPDATE

注意： 删除记录某个字段的值，是UPDATE而不是DELETE；

UPDATE testemp SET id=2 WHERE name='张三';
COMMIT;

3.1.4 查找记录 SELECT

SELECT id,name FROM testemp WHERE name='张三';

注意： SELECT 后必须接 FROM

3.2 子句

3.2.1 SELECT 子句：子查询

将查询结果作为中间表或中间值，然后进一步操作

SELECT * FROM (SELECT id,name FROM testemp WHERE name='张三') t1 WHERE id=1;

SELECT子句返回结果为一个中间表，要对中间表继续查询时，中间表必须起别名，这里为t1。

UPDATE testtemp SET testtemp.name = (SELECT name FROM testtemp2 WHERE testtemp2.gender = testtemp.gender LIMIT 1) WHERE testtemp.id = '1';

SELECT子句返回结果为单个值，不需要别名，可以赋值给字段。

注意：

1. SELECT子句必须用小括号框住；
2. 外层表的字段能够在内层中作为参数使用；

3.2.2 WHERE 子句：条件查询

参考：MySQL_WHERE子句、UNION、DISTINCT、ORDER、LIMIT、窗口函数

3.2.3 GROUP BY 子句：分组查询

参考：MySQL_GROUP BY

单表操作时，很多效果就是通过GROUP BY来实现的。

3.2.4 ORDER BY 子句：排序

参考：MySQL_WHERE子句、UNION、DISTINCT、ORDER、LIMIT、窗口函数

3.2.5 UNION 子句：联表查询，行操作

参考：MySQL_WHERE子句、UNION、DISTINCT、ORDER、LIMIT、窗口函数

3.2.6 JOIN 子句：联表查询，列操作

参考：MySQL_WHERE子句、UNION、DISTINCT、ORDER、LIMIT、窗口函数

3.2.7 LIMIT 子句：截取结果条数

参考：MySQL_JOIN

3.3 函数

3.3.1 GROUP_CONCAT、CONCAT

GROUP_CONCAT是将组内多行数据合并。

SELECT GROUP_CONCAT(testtemp.id) FROM testtemp GROUP BY testtemp.name WHERE testtemp.gender = 'male';

CONCAT、CONCAT_WS是将常量或多列数据合并。CONCAT_WS表示with seperator，可以加入分隔符。

INSERT INTO test (id,name) VALUES(1,CONCAT('a','b'));
SELECT CONCAT_WS(':',test.id,test.name) FROM test;

注意：
1.MySQL中聚合函数（GROUP BY 函数）要通过SELECT调用，其他函数不一定；
2.聚合函数的参数可以是常数，也可以是FROM表中的字段。