数据开发之MySQL

数据库基础

一、 数据库操作

1.查看数据库 show databases;
2.创建数据库 create database 库名 default charset = utf8;
3.删除数据库 drop database 库名；
4.打开数据库 use 库名；

二、数据表操作

数据库管理系统中，可以有很多数据库，每个数据库中可以有多张表。
1.查看表 show tables;
2.创建表 create table 表名(字段名1 类型，字段名2 类型) engine = innpdb default charset = utf8;

CREATE TABLE  --create table
[IF NOT EXISTS] tb_name -- 不存在才创建，存在就跳过 if not exists
(column_name1 data_type1 -- 列名和类型必选
  [ PRIMARY KEY -- 可选的约束，主键 primary key
   | FOREIGN KEY -- 外键，引用其他表的键值 foreign key
   | AUTO_INCREMENT -- 自增ID auto_increment
   | COMMENT comment -- 列注释（评论）
   | DEFAULT default_value -- 默认值
   | UNIQUE -- 唯一性约束，不允许两条记录该列值相同 unique
   | NOT NULL -- 该列非空
  ], ...
) [CHARACTER SET charset] -- 字符集编码 character
[COLLATE collate_value] -- 列排序和比较时的规则（是否区分大小写等）

create table if not exists users(
	id int not null primary key auto_increment,
	name varchar(4) not null,
	age tinyint,
	sex enum('男','女')
) engine = innodb default charset = utf8;

从另一张表的查询结果创建表：

CREATE TABLE tb_name AS SELECT * FROM tb_name_old WHERE options

从另一张表复制表结构创建表：

CREATE TABLE tb_name LIKE tb_name_old

3.删除表 drop table 表名；

drop table [if exists] 表名1[,表名2]

4.清空表数据 （3种方式）
（1）delete------ 是逐行删除速度极慢，不适合大量数据删除。

Delete from 表名 

（2）truncate---- 删除所有数据，保留表结构，不能撤消还原。`

TRUNCATE TABLE 表名

（3）drop-------- 删除表，数据和表结构一起删除，快速。
先复制表结构

DROP TABLE 表名

5.修改表结构
修改表名 alter table 原表名 rename as 新表名

ALTER TABLE 表名 修改的选项

1.添加字段
语法：alter table 表名 add 添加的字段信息
# 在 users 表中 追加 一个 num 字段 
alter table users add num int not null;
#增加列在某列之后
alter table user_info add school varchar(15) after level;
alter table 表名 add 增加列的名称 数据类型 位置(after level 在level 之后)
# 在表的最前面添加一个字段 
alter table users add aa int first;

2.修改字段
语法：alter table 表名 change|modify 被修改的字段信息

 - change:可以修改字段名
 - modify:不能修改字段名

#更换列的名称及数据类型
alter table user_info change job profession varchar(10);
alter table user_info change 原列名 修改列名 修改数据类型
#更改数据类型
alter table user_info modify achievement int(11) default 0;
alter table 表名 modify 修改列名称 数据类型 默认值等

3.删除字段
语法：alter table 表名 drop 被删除的字段名
alter table users drop aa;

选项集合

    { ADD COLUMN <列名> <类型>  -- 增加列
     | CHANGE COLUMN <旧列名> <新列名> <新列类型> -- 修改列名或类型
     | ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT } -- 修改/删除 列的默认值
     | MODIFY COLUMN <列名> <类型> -- 修改列类型
     | DROP COLUMN <列名> -- 删除列
     | RENAME TO <新表名> -- 修改表名
     | CHARACTER SET <字符集名> -- 修改字符集
     | COLLATE <校对规则名> } -- 修改校对规则（比较和排序时用到）

修改表中自增的值

# 在常规情况下，auto_increment 默认从1开始继续递增 
alter table users auto_increment = 1000; #从1000开始递增

修改表引擎语句

#将表引擎InnoDB改为MyISAM
alter table users engine = 'myisam' 

查看表结构 desc 表名；
查看建表语句 show create table users;

三、 数据操作 增删改查

3.1 插入

• 普通插入(全字段)：
  insert into table_name values(value1,value2,···)
• 普通插入(限定字段)：
  insert into table_name(column1,column2,···) values(value1,value2,···)
• 多条一次性插入：
  insert into table_name(column1,column2,···) values(value1_1,value1_2,···),(value2_1,value2_2,···)
• 从另一个表导入(全字段)：
  insert into table_name select * from table_name2 [where key=value]
• 从另一个表导入(限定字段)：
  insert into table_name(column1,column2,···) select * from table_name2 [where key=value]

INSERT INTO cxcyds_kjjr.sec_project (
	id,
	enterprise_id,
	ent_name,
	credit_code,
	com_year,
	img_url,
	rank,
	project_name,
	project_introduce,
	tech_innovation,
	tech_discourse,
	project_percentage,
	bs_plan,
	competitive_advantage,
	market_share,
	economic_risk,
	equity_financing,
	debt_financing,
	need_equity_financing,
	need_debt_financing,
	matching_activities,
	recom_credit_agency,
	recom_investment_agency,
	financing_roadshow,
	recom_subfund 
) 
SELECT
d.uid id, -- 参赛项目编号
d.uid enterprise_id, -- 企业id
c.ename, -- 企业名称
c.elicense, -- 统一信用代码
2021 AS com_year,-- 大赛年度
producturl,-- 图片url
gnranking,-- 大赛名次
pname,-- 参赛项目名称
techpro,-- 参赛项目介绍(1000字以内)
technological_innovation,-- 产品技术创新点(1000字以内)
technological_discuss,-- 技术成熟性及可靠性论述(500字以内)
pscale,-- 项目占去年企业收入比例
bsmode,-- 商业模式及业务拓展计划
market,-- 产品市场分析及竞争优势
shichangfene,-- 市场份额占有率
risk,-- 经济风险与对策
isfcexpgq,-- 是否有股权融资
isfcexpjq,-- 是否有债权融资
isfcneedgq,-- 是否有股权融资需求
isfcneedjq,-- 是否有债权融资需求
dqyduijiehuodong,-- 申请大赛组织的大企业对接活动(1是
isaccep,-- 申请大赛推荐信贷机构(1是
isacceptre,-- 申请大赛推荐投资机构(1是
isluyan,-- 申请大赛组织的融资路演(1是
issqgjkjcg -- 申请国家科技成果转化引导基金设立的子基金推荐(1是
FROM bsplan d 
left join member c on c.id = d.uid
WHERE pname IS NOT NULL 
	AND pname <> '' 
	and c.elicense IS NOT NULL
GROUP BY
	1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22 

3.2 删除

delete from 表名 where 字段 = 某个值；

3.3 修改

update 表名 set ··· where ···

• update 表名 set 字段 = 某个值 where 条件；
• update 表名 set 字段1 = 值1，字段2 = 值2 where 条件；
• update 表名 set 字段 = 字段 + 值 where 条件

update 与 join 结合

UPDATE cxcyds_kjjr.sec_project t1
INNER JOIN cxcyds.lanhai_bsplan t2 ON t1.id = t2.uid 
SET t1.xrongzi_t = t2.xrongzi_t,
	t1.xrongzi_a = t2.xrongzi_a,
	t1.xrongzi_b = t2.xrongzi_b,
	t1.xrongzi_c = t2.xrongzi_c,
	t1.xrongzi_qita = t2.xrongzi_qita
WHERE t1.com_year = '2022' 

四、创建索引

4.1 create方式创建索引：

create
[unique -- 唯一索引
|fulltext -- 全文索引
] index index_name on table_name -- 不指定唯一或全文时默认普通索引
(column1[(length)[desc|asc]][,column2,···]) -- 可以对多列建立组合索引

4.2 alter方式创建索引：

alter table tb_name add [unique|fulltext] [index] index_content(content)

五、删除索引

5.1 drop方式删除索引：

drop index <索引名> on <表名>

5.2 alter方式删除索引：

alter table <表名> drop index <索引名>

六、replace into和insert into的区别

replace into 跟 insert into 功能类似，不同点在于：replace into 首先尝试插入数据到表中，

• 如果发现表中已有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据；
• 否则，直接插入新数据。