二、数据库管理

一、数据创建

1. sql结束符

# 1. 查询当前mysql的时间
select now();

# 2. 查询当前账户的用户名,返回当前用户@服务端的ip   shuzhan@117.35.135.138
select user();    

# 3. 除了 ; 作为sql语句结束符,也可以使用 \G来结束,查询结果垂直显示
select user(),now(),version()\G
  • 如果输入的语句比较短,则 \G没有什么意义,如果较长,则方便查看;
    在这里插入图片描述
  • sql命令行,输入sql后则等等待结束符,因此可以将同一个sql分多行输入;
  • 如果不输入结束符,则按enter就会跳转到下一行;
    在这里插入图片描述
# 4.  \c : 输入前面的较长的sql,如果不想执行了,则 \c 来取消当前sql的执行;
select now(),version(),user()\c

# 5. 同一行输入多条指令,用  ; 隔开即可,多条sql可以一起执行
select now();select version();select user();

# 6. 关键字,函数等使用大写,    库名,表名,列名采用小写;

2. 库

# 1. 查看当前用户账户下所有的数据库
SHOW DATABASES;

# 2. 查看当前使用的数据库,   一般当前为null
SELECT DATABASE();

# 3. 选择切换数据库,选择后再可以查看当前数据库
USE lucycat; 
-- 3.1 不一定非要切换数据库,可以在当前库采用 库名.表名来调用其他库的表;
-- 3.2 每次从mysql断开重新打开,当前使用的库就是null;

# 4. 创建库
-- 4.1. 直接创建库,库名重复则报错
CREATE DATABASE shuzhan_db;
-- 4.2. 如果当前库存在,则不创建
CREATE DATABASE IF NOT EXISTS shuzhan_db;

3. 表

3.1 创建

普通创建

# 1. 列出当前数据库所有的表
SHOW TABLES;

# 2. 创建最普通的表
CREATE TABLE cnip ( name char(20),weight INT,sex ENUM('F','M'));

-- 2.1 查看表的相关信息
SHOW CREATE TABLE cnip;
--2.2. 用上述指令查看后的信息:mysql会自动默认补全如下

CREATE TABLE `cnip` (
  `name` char(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `weight` int(11) DEFAULT NULL,
  `sex` enum('F','M') COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. 判断再创建:
-- 3.1. 只比较表名,并不会去比较表结构;
-- 3.2. 适用于批处理文件,如果重复表名,则会给出warnings信息,脚本可以继续执行;
CREATE TABLE IF NOT EXISTS  cnip ( name char(20),weight INT,sex ENUM('F','M'));


-- 4. 采用存储引擎创建
-- 存储引擎名字不区分大小写,如果该存储引擎在mysql中未启用,则会使用默认存储引擎;
CREATE TABLE `cnip` (
  `name` char(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `weight` int(11) DEFAULT NULL,
  `sex` enum('F','M') COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. 修改存储引擎
ALTER TABLE cnip ENGINE = MyISAM;

分区表

  • 数据量大的表,读写操作效率低,将一个大表的行数据,分配到不同的表;
-- 1.按照时间分区
-- 在var/lib/mysql/库名 目录下,会创建 phone#P#p1.ibd类似的数据保存数据文件
CREATE TABLE phone (id INT,name CHAR(20),created_time DATETIME NOT NULL)

PARTITION BY RANGE(YEAR(created_time))

(PARTITION p0 VALUES LESS THAN (2010),
PARTITION p1 VALUES LESS THAN (2011),
PARTITION p2 VALUES LESS THAN (2012),
PARTITION p3 VALUES LESS THAN (2013),
PARTITION pmax VALUES LESS THAN MAXVALUE);

--2. 如果后续数据都超过了2013,则可以对pmax分区进行再分区
ALTER TABLE phone REORGANIZE PARTITION pmax
INTO(
PARTITION p4 VALUES LESS THAN(2014),
PARTITION p5 VALUES LESS THAN(2015),
PARTITION pmax VALUES LESS THAN MAXVALUE
)

3.2 描述表

# 1. 描述一个表
DESC/DESCRIBE/EXPLAIN president;
SHOW COLUMNS/FIELDS FROM president;

DESC/DESCRIBE/EXPLAIN president '%name';       // 只描述带有name的字段
SHOW COLUMNS/FIELDS FROM president LIKE '%name';  

# 2.  查看行数据的详细信息
SHOW FULL FIELDS/COLUMNS FROM president;

3.3 删除表

# 1. 删除一个表
DROP TABLE cnip;
# 2. 删除多个表
DROP TABLE cnip,phone;
# 3. 批处理执行时候,
DROP TABLE IF EXISTS cnip;

4. 插入行数据

# 1.1. 插入一条,全列插入:必须包含所有的列数据
INSERT INTO president VALUES (1,"shu","zhan");

# 1.2. 插入多条,全列插入
#   批量插入执行效率更高
INSERT INTO president VALUES (2,"wu","wang"),(3,"si","li");

# 2.1 插入一条,指定列名插入
# 对于自增的键,如果赋值就取赋值的,不赋值则取默认的
INSERT INTO president (last_name,first_name,id) VALUES ("八","王",10);
# 2.2 插入多条,指定列名插入
INSERT INTO president (last_name,first_name,id) VALUES ("六","赵",5),("七","田",6);

# 3 set赋值方法:主键就没有给值,采取默认的即可
INSERT INTO president SET first_name = "朱", last_name = "院长";

二、常用检索

# 1. 基本检索
SELECT * FROM cnip;
SELECT name,age FROM cnip;
SELECT VERSION(), (2+2) AS sumnum, 'hello worold'; //不涉及表的查询

# 2. 条件检索,后面可以跟多个条件检索
SELECT * FROM cnip WHERE name = 'lucy' OR age = 20;

# 3. NULL值  null值比较,不能用 =   !=  >  < 来进行判断
SELECT * FROM cnip WHERE name IS NULL;
SELECT * FROM cnip WHERE name IS NOT NULL;

-- <=>: mysql提供的特殊的,用于对null值比比较的
SELECT * FROM president WHERE address <=> NULL;  
SELECT * FROM president WHERE  NOT address <=> NULL;

# 4. 排序:  more是升序排列,即ASC ,DESC为升序排列
#   多个字段排序: 先按第一个字段排序,第一个字段相同再按第二个排序
SELECT * FROM president ORDER BY first_name DESC, last_name DESC;

# 区分null值的排序: 先按照null和非null排序,再按照字段排序
# IF(ccondtion,0,1): true则为0,false 则为1
SELECT * FROM president ORDER BY IF(address IS NULL,0,1) DESC, address DESC;

# 5. 列值处理:CONCAT(): 将多个字段拼接
-- AS 可省略
SELECT CONCAT('姓名:',first_name,'---',last_name) AS name FROM president;

# 6.  模糊匹配
-- 不能匹配null值
-- % 代表0-n个字符,  英文匹配时候不区分大小写;  -只匹配一个字符;
--匹配时: 字符的一定要用‘’, 数值类型,可用可不用''
SELECT * FROM cnip WHERE content LIKE '%zte%';
SELECT * FROM cnip WHERE content LIKE 'z%e';
SELECT * FROM cnip WHERE content LIKE '_te';
SELECT * FROM cnip WHERE content LIKE '_te';

# 7. 统计功能
SELECT DISTINCT name FROM cnip;   // 去重

-- COUNT(*): 所有行     COUNT(列名):该列所有非NULL的数据;
SELECT COUNT(*),COUNT(address) FROM president;       

-- 其他函数
MIN()  MAX()  AVG()   SUM()

# 8. GROUP BY : 分组
-- 数据结果先分组为不同的几部分,再进行其他处理
-- 先按照sex分组,再统计每个分组的个数;
SELECT state, COUNT(*) FROM president GROUP BY state;
-- 先分组统计个数,再按照个数降序排序
SELECT state, COUNT(*) AS num,FROM president GROUP BY state ORDER BY num;
-- HAVING 子句: 适合用来筛选在一个列中重复出现的值
SELECT state, COUNT(*) AS num,FROM president GROUP BY state HAVING num >1 ORDER BY num;

-- 超集汇总: 分组统计后,多出一行是汇总结果
SELECT state, count(*) FROM president GROUP BY state WITH ROLLUP;

三、删除,修改

# 1. 删除: 删除所有数据
DELETE FROM cnip;
-- 指定删除: 一般删除时候,必须加where子句,避免误删
DELETE FROM cnip WHERE name = 'zhangsan';

# 2. 更新操作: 更新所有数据的name
UPDATE cnip SET name = 'sz';
-- 指定更新:更新时候,一般必须加where子句,避免误更新
UPDATE cnip SET name = 'sz' WHERE id = 4;
-- 指定跟新多列数据
UPDATE cnip SET name = 'sz', color = 'red' WHERE id = 4;
-- 更新某列的值为NULL
UPDATE cnip SET name = NULL where id = 4;
©️2020 CSDN 皮肤主题: 游动-白 设计师:上身试试 返回首页