MySQL数据库的基本操作

一、数据库的操作：

1.创建数据库

	#创建数据库
	CREATE DATABASE test1;
	
	#创建中文数据库
	CREATE DATABASE 梦见 charset='utf8';

2.删除数据库

	#删除数据库
	DROP DATABASE test;

3.修改数据库名称

MySQL修改数据库名称比较麻烦，不支持直接修改，需要通过其它方式间接达到修改数据库名称的目的。

在 MySQL 5.1.23 之前的旧版本中，我们可以使用 RENAME DATABASE 来重命名数据库，但此后版本，因为安全考虑，删掉了这一条命令。

参考方法：导出数据到一个新库

4.查询数据库

	#切换当前数据库
	USE test1;

	#查询当前使用的数据库
	SELECT DATABASE();
	SHOW TABLES;
	
	#查看创建了哪些数据库
	SHOW DATABASES;
	
	#查看包含test的数据库
	SHOW DATABASES LIKE '%test%';

二、数据表的操作

1.创建表

#创建表：包含表名称、字段名称、字段类型、约束
#在test库创建一个student表
CREATE table student1(
	sname CHAR(32), #姓名，32个字符长度
	sex set('男','女'),
	hight NUMERIC(3,2),
	age SMALLINT, #年龄，整数
	address VARCHAR(255), #地址
	love enum('抽烟','喝酒','打牌') #爱好
	)CHARSET='utf8';

插入笔记：mysql数据类型：

1、整数类型：tinyint/smallint/int/long
2、小数类型：
	float #浮点型
	numeric(m,n) #表示总长m，保留小数点后n位
	NUMERIC(3,2) #例如：1.25
3、字符类型：
	char（32）#字符串分配32字符空间，优点：速度快
	varchar(32) #可变字符串，先32再回收剩余，优点：省空间
4、其他类型：
	date
	time
	enum #枚举类型
	set #集合类型

插入笔记：数据库的约束：

-- 表中不能放完全相同的行数据，因为这样没有意义,违背了数据库1NF（第一范式）
-- 数据库的约束：
-- 1.非空约束：NOT null
-- 2.唯一约束：UNIQUE KEY
-- 3.主键约束：PRIMARY key 用数字序列号实现，表示唯一且非空，3NF每张表有一个主键约束。
-- 4.外键约束：FOREIGN KEY 
-- 5.默认值约束：DEFAULT '[key]' 
-- 6.自增长约束：auto_increment 从当前最大值开始增长

#创建一个student2表
CREATE TABLE IF NOT EXISTS student2(  #IF NOT EXISTS，不存在
	num_ID INT PRIMARY KEY auto_increment, #主键约束，自增长约束
	sname CHAR(32) NOT NULL UNIQUE KEY, #非空唯一约束
	sex char(2) DEFAULT '男' CHECK(sex in ('男','女')), # 默认值约束：男，检查check
	age int(2)  #无约束
	)CHARSET='utf8'; #避免中文乱码

#给student2表插入数据
INSERT INTO student2(sname,sex,age) VALUES('mengjian','男',20) ;
#批量插入数据
INSERT INTO student2(sname,age) VALUES ('mengjian5',11) ,('mengjian3',31) ,('mengjian4',14) ;

2.删除表

#删除表
DROP TABLE student1,stident2 ;

#删除表字段
ALTER TABLE student1
DROP COLUMN birth ,
DROP COLUMN address ;

3.修改表结构

#修改表字段,实质上是字段的增删改
#添加表字段
ALTER TABLE student1
ADD COLUMN address VARCHAR(255) AFTER age ; 

#添加多个字段，AFTER之后，FIRST第一
ALTER TABLE student1
-- ADD COLUMN ID INT(10) FIRST ,
ADD COLUMN ID INT(10) UNIQUE KEY FIRST , #UNIQUE KEY唯一值，若表中已有多个数据，添加此约束会报错
ADD COLUMN birth DATE NOT NULL default '2002-02-02' AFTER sex ; 

#修改表字段
ALTER TABLE student1
MODIFY COLUMN birth date DEFAULT '2012-12-12' ,
MODIFY COLUMN age int(2) not null DEFAULT '20' ;

#删除表字段
ALTER TABLE student1
DROP COLUMN birth ,
DROP COLUMN address ;

#修改表数据
#标准语法：update 表名称 set 字段名=新值 where 字段名=值;
UPDATE student2 SET sex='女' WHERE sname='mengjian1';

4.查询表

#查询当前数据库中的表
SHOW TABLES;
SHOW TABLES LIKE '%stu%';

#查看student表结构
DESC student; #DESCRIBE student的简写

三、数据的操作

1.增加表中数据

#给student2表插入数据
INSERT INTO student2(sname,sex,age) VALUES('mengjian','男',20) ;

#批量插入数据
INSERT INTO student2(sname,age) VALUES ('mengjian5',11) ,('mengjian3',31) ,('mengjian4',14) ;

2.修改表数据（更新数据）

#修改数据
#标准语法：update 表名称 set 字段名=新值 where 字段名=值;
UPDATE student2 SET sex='女' WHERE sname='mengjian1';

#修改多条数据
UPDATE student1 SET sex='女' ;
UPDATE student1 SET sex='男' WHERE age = 0;
UPDATE student1 set age=age+5 ; #表达式修改
UPDATE student1 set sex ='女' ,age = 6 WHERE sname = 'mengjian2' ;

3.删除表中数据

#数据的删除 DELETE FROM
DELETE FROM student2 WHERE sname='mengjian2' ;
DELETE FROM student2 WHERE sex is NULL ;
DELETE FROM student2 ;#全表删除数据

#TRUNCATE TABLE,只有一种用法，删除全表数据
TRUNCATE TABLE student1;

-- #DELETE和TRUNCATE区别
-- 1、delete可以删除部分数据；
-- 2、truncate可以删除并破坏自增长数据。

4.查询表中数据

1.单表查询（不带查询条件）

#标准语法:select ... from ...
SELECT * FROM student2 ;
SELECT sname,age FROM student2 ;

#带表达式查询
SELECT sname,age+10 FROM student2 ;
SELECT sname,if(num_ID%2=0,'双号','单号') AS '学号' FROM student2 ;
SELECT sname,sex,IFNULL(address,'中国') as '国籍' FROM student2 ;

#排序：order by ... asc/desc
SELECT * FROM student2 ORDER BY age ASC ;#正序,默认，此处ASC可省略
SELECT * FROM student2 ORDER BY age DESC ;#逆序
SELECT * FROM student2 ORDER BY num_ID ASC,age DESC ;

#limit 2,3 从第二行开始显示三行数据
SELECT * FROM student2 LIMIT 3 ;
SELECT * FROM student2 LIMIT 2,3 ;

#去重:distinct
SELECT DISTINCT age FROM student2 ;
SELECT DISTINCT sex,age FROM student2 ;# 合并去重

2.单表查询（带查询条件）

# 等值查询
SELECT * FROM student2 where sname = 'mengjian5' ;
SELECT * FROM student2 WHERE age = 20 ;
SELECT * FROM student2 WHERE age = age2 ;# 字段相等

#不等值查询： >  <  =  >=  <=  !=  <>
SELECT * FROM student2 WHERE age>15 ;
-- !=  <> 都表示不等于
SELECT * FROM student2 WHERE age<>20 ; #用于数字执行快
select * FROM student2 WHERE sex != '男' ;#用于字符执行快

# 模糊查询
SELECT * FROM student2 WHERE sname LIKE 'mengjian%' ; #匹配任意字符
SELECT * FROM student2 WHERE sname LIKE 'mengjian_' ; #表示必须匹配一个字符
SELECT * FROM student2 WHERE sname RLIKE 'mengjian.' ; # 正则RLIKE

#逻辑语句：and  or  not
SELECT * FROM student2 WHERE sex = '男' AND age < 20 ;
-- and 优先级高于 or
SELECT * FROM student2 WHERE sex = '男' AND age < 20 OR sname like 'mengjian%' AND age2 < 10;
-- 括号可以改变运算顺序
SELECT * FROM student2 WHERE sex = '男' AND (age < 20 OR sname like 'mengjian%') AND age2 < 10;
-- not
SELECT * FROM student2 WHERE sname NOT LIKE 'mengjian%' ;

# 空值处理 is null /is not null
SELECT * FROM student2 WHERE address is  NULL ;
SELECT * FROM student2 WHERE address is NOT NULL ;
 
#区间查询：between ... and ...
SELECT * FROM student2 WHERE age BETWEEN 10 and 15 ;

#集合查询：in / not in
SELECT  * FROM student2 WHERE age in (8,11,14) ;
SELECT * FROM student2 WHERE (sex,age) IN (('女',11),('男',20));

3.分组查询

MySQL函数:	https://www.cnblogs.com/kissdodog/p/4168721.html
			-- 数学函数；
			-- 字符串函数；
			-- 日期和时间函数；
			-- 条件判断函数；
			-- 系统信息函数；
			-- 加密函数；
			-- 格式化函数；

#在select中出现的五个常用函数
--1、 count() 求个数，不计算空值
SELECT COUNT(sname) as '学生个数' FROM grade ;
SELECT COUNT(*) as '统计行数' FROM grade ;

-- 2、max() 求最大值
-- 3、min() 求最小值
SELECT MAX(yuwen) AS '语文最高成绩' ,MIN(yuwen) AS '语文最低成绩' FROM grade ;

-- 4、sum() 求和（只能是数字），不计算空值
SELECT SUM(yuwen) as '语文成绩求和' from grade ;

-- 5、avg() 求平均值，不计算空值
SELECT avg(yuwen) as '语文成绩平均值' from grade ;

-- 如果计算存在空值，用加减乘除运算还是空值，灵活运用ifnull
SELECT shuxue+yuwen,shuxue+IFNULL(yuwen,0) FROM grade ; #yuwen含有空值

# 分组：group by
-- GROUP_CONCAT 函数表示查询组成员
SELECT sex ,GROUP_CONCAT(sname),GROUP_CONCAT(age)  FROM student2 GROUP BY sex ;

-- having 二次筛选，在分组后筛选，注意分组后每个组为一个单独体
-- -- --区别：where针对于普通字段筛选，having是聚合函数筛选
SELECT class_name,COUNT(sname)
FROM class 
GROUP BY class_name
HAVING COUNT(sname)<5 ; # 求人数小于5的班级

4.多表查询

#多表连接查询
SELECT up.* ,qd.* ,qpd.*
FROM user_profile AS up,question_detail AS qd,question_practice_detail AS qpd
WHERE up.device_id = qpd.device_id AND qd.question_id = qpd.question_id AND qpd.date>'2021-06-01'
ORDER BY up.device_id ASC;

# 用join ... on ...实现多表连接查询
SELECT up.* ,qd.* ,qpd.*
FROM user_profile AS up JOIN question_practice_detail AS qpd ON up.device_id=qpd.device_id JOIN question_detail AS qd ON qpd.question_id=qd.question_id 
WHERE qpd.date>'2021-06-01' 
ORDER BY up.device_id ASC ;

# 自身表连接,场景例如：领导关系、部门关系、亲属关系
SELECT a.*,b.*
FROM family a,family b 
WHERE a.pid = b.fid ;

# 不等值连接
SELECT gd.sname, gd.yuwen, lv.lv
FROM grade AS gd JOIN lv 
WHERE gd.yuwen BETWEEN lv.min AND lv.max ;

#左连接和右连接（都属于外链接）
-- 内连接 inner join 
-- 外链接 outer join : left join / [outer] right join
# 主要取决于以那个表为基准
# RIGHT JOIN
SELECT class.sname,class.class_name,student2.sname,student2.sex
from class RIGHT JOIN student2 on class.sname = student2.sname ;

# LEFT JOIN
SELECT class.sname,class.class_name,student2.sname,student2.sex
from class LEFT JOIN student2 on class.sname = student2.sname ;	

5.子查询

# 子查询（嵌套查询）in / any
SELECT * FROM student1 WHERE sname IN (SELECT sname FROM student2 ) ;
SELECT * FROM student1 WHERE sname = ANY (SELECT sname FROM student2 ) ;

# 组合条件
SELECT * FROM student1 WHERE (sex,age) IN (SELECT sex,age FROM student2);

四、用户管理

视图、索引、存储过程