---

数据库操作(一)

1、创建数据库

//1、创建数据库
create database db;
//2、查看创建好的数据库的定义
show create database db;
//显示如下
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| db       | CREATE DATABASE `db` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+---------------------------------------------------------------+

2、删除数据库

//1、删除数据库  如果指定的数据库不存在，则删除错误；

DROP DATABASE db;
//1008 - Can't drop database 'db'; database doesn't exist

3、数据库引擎

MySQL提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在MySQL中，不需要再整个服务器中使用同一种存储引擎，针对具体要求，可以对每一个表使用不同的存储引擎。

MySQL 5.7支持的存储引擎有：InnoDB,MyISAM,Memory,Merge,Archive,Federated,CSV,BLACKHOLE等。

SHOW ENGINES; //显示引擎

3.1 InnoDB引擎

​ InnoDB是事务型数据库首选引擎，支持事务安全表（ACID),支持行锁定和外键，5.5版本之后默认引擎为InnoDB。

3.2 MyISAM引擎

​ MyISAM 基于ISAM的存储引擎，并对其进行了扩展。它是在Web、数据存储和其他应用环境下最常用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务。在MySQL5.5之前，MyISAM是默认引擎。

3.3 MEMORY引擎

​ MEMORY存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。

3.4总结

​ 如果要提供提交、回滚和崩溃恢复能力的事务安全（ACID兼容)能力，并要求实现并发控制，InnoDB是个很好的选择。如果数据表主要用于插入和查询记录，则MyISAM引擎能提供较高的处理效率；如果只是临时存储数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的MEMORY引擎中，MySQL中使用该引擎作为临时表。

4 数据表

4.1 创建表的种方式

4.1.1 创建表标明主键和自增

//1
CREATE TABLE `test`(
	`id` int(16) not null auto_increment primary key,
	`name` varchar(25)
);
//2
CREATE TABLE test(
	id int(16) not null auto_increment,
	name varchar(25),
    primary key (id)
);

4.1.2 创建表使用外键约束

子表的外键必须关联父表的主键，且关联字段的数据

mysql> create table test_dept(
    -> id int primary key,
    -> name varchar(25),
    -> deptId int,
    -> constraint fk_test foreign key(deptId) references test(id)
    -> );

4.1.3创建表使用唯一性约束

唯一性约束（Unique Constraint) 要求该列唯一

Unique 和 Primary 的区别： 一个表中可以有多个字段声明为Unique，但只能有一个Primary key的声明，声明为Primary key的列不允许有空值，但是声明为Unique的字段运行存在空值（NULL)。

CREATE TABLE test_unique(
	id int(16)  primary key,
	name varchar(25) unique,
);

//或者
CREATE TABLE test_unique(
	id int(16)  primary key,
	name varchar(25) ,
    constraint c_name unique(name)
);

4.1.4创建表使用默认约束

默认约束（Default Constraint）指定某列的默认值。如果男性同学较多，性别就可以默认为‘男’。如果插入一条新的记录时没有为这个字段赋值，那么系统会自动为这个字段赋值为‘男’

CREATE TABLE test_default(
	id int(16)  primary key,
	name varchar(25) default 'hu'
);

4.2 查看数据表结构

4.2.1 Describe 语句

Describe test_default;
//简写
desc test_default;

4.2.2查看表详细结构语句 show create table

show create table test_default;

4.3修改数据表

修改已存在的数据表结构，MySQL中使用关键词 alter table。

4.3.1 修改表名

//alter table <旧表名> rename to <新表名>;
alter table test_default rename to test_default_new;

4.3.2修改字段的数据类型

//alter table <表名> modify  <字段名> <新数据类型>;
alter table test_default_new modify name  varchar(255);

4.3.3修改字段的名

//alter table <表名> change <旧字段名> <新字段名> <新数据类型>;
alter table test_default_new change name namenew varchar(255);

4.3.4 添加字段

//alter table <表名> add  <新字段名> <新数据类型>;
alter table test_default_new add  nameadd varchar(255) unique;

//如果想在表的第一列添加 使用关键字first
alter table test_default_new add  nameaddfirst varchar(255) first;

4.3.5 删除字段

//alter table <表名> drop  <新字段名> ;
alter table test_default_new drop  nameadd ;

4.3.6 更换表的存储引擎

//alter table <表名> engine =   <新引擎名> ;
alter table test_default_new engine = MyISAM;

4.3.7 删除表的外键约束

//alter table <表名> drop foreign key  <外键约束名> ;
alter table test_dept drop foreign key  fk_test ;

4.3.8 添加约束

//添加外键约束
// alter table  <表名> add constraint  <外键约束名> foreign key(<字段名>) refrences <表名>(<字段名>)

alter table test_dept add constraint `fk_test` foreign key(deptId) references  test(id) ;

4.4 删除表

4.4.1 删除没有关联的表

在MySQL中，使用Drop table 可以一次性删除一个或多个没有被其他表关联的数据表。

//drop table [if exists]表1，表2...

drop table if exists test;

4.4.2 删除被其他表关联的主表

数据表之间存在外键关联的情况下，如果直接删除父表，结果会显示失败。原因是直接删除，将破坏表的参照完整性。如果必须要删除，可以先删除与它关联的字表，再删除父表，只是这样同事删除了两个表中的数据。但有时需要保留子表，只需将关联的外键约束取消，然后删除父表；

4.5 案例

4.5.1创建office和employees表

office表结构

字段名	数据类型	主键	外键	非空	唯一	自增
officeCode	int(10)	是	否	是	是	否
city	varchar(50)	否	否	是	否	否
address	varchar(50)	否	否	否	否	否
country	varchar(50)	否	否	是	否	否
postalCode	varchar(50)	否	否	否	是	否

create table if not exists offices(
    officeCode int(10) primary key not null unique,
    city varchar(50) not null,
    address varchar(50) ,
    country varchar(50) not null,
    postalCode varchar(15) unique)Engine = InnoDB default charset=utf8;

employees表结构

字段名	数据类型	主键	外键	非空	唯一	自增
employeeNumber	int(11)	是	否	是	是	是
lastName	varchar(50)	否	否	是	否	否
firstName	varchar(50)	否	否	是	否	否
mobile	varchar(25)	否	否	否	是	否
officeCode	int(10)	否	是	是	否	否
jobTitle	varchar(50)	否	否	是	否	否
birth	datetime	否	否	是	否	否
note	varchar(255)	否	否	否	否	否
sex	varchar(5)	否	否	否	否	否

create table if not exists employees(
    employeeNumber int(11) primary key not null unique auto_increment,
    lastName varchar(50) not null,
    firstName varchar(50) not null,
    mobile varchar(25) unique,
    officeCode int(10) not null,
    jobTitle varchar(50) not null,
    birth datetime not null,
    note varchar(255) ,
    sex varchar(5) ,
   	constraint fk_offices foreign key(officeCode) references offices(officeCode)
)Engine = InnoDB default charset=utf8;

4.5.2修改表内容

//修改employees表中的birth字段为employee_birth
alter table employees change birth employee_birth datetime not null;

//修改sex字段的数据类型为varchar(1)
alter table employees modify sex varchar(1);

//删除字段note
alter table employees drop note;

//添加字段favorite_activity
alter table employees add favorite_activity varchar(100) after sex;

//删除外键
alter table employees drop foreign key fk_offices;

//修改引擎
alter table employees engine==myisam; //(需先删除外键，才能执行 delete or update)

//显示表的详细信息 除了desc 还可以
show create table employees;

5 MySQL数据类型和运算符

5.1数据类型

5.2 运算

5.2.1算术运算符

//创建运算表
create table temp(num int);
//插入值
insert int temp value (64);
//进行算术运算 + - * / %
select num , num + 1 ,num - 1, num * 10, num / 2,num % 2 from temp;

//如果运算没有意义 返回值为null;
select num / 0 from temp;

5.2.2 比较运算符

比较运算符

运算符	作用
=	等于（不能用于判断null null=null返回null）
<=>	安全等于（可用于比较null，两个null<=>null返回1）
<>(!=)	不等于（不能用于判断null null<>null返回null）
<=	小于等于（不能用于判断null null<=null返回null）
>=	大于等于（不能用于判断null null>=null返回null）
>	大于（不能用于判断null null>null返回null）
is null	是null
is not null	不是null
least	存在有两个或多个参数时返回最小值
greatest	存在有两个或多个参数时返回最大值
between and	判断一个值是否落在两个值之间
isnull	于 is null相同
in	判断一个值是in列表中的任意一个值
not in	判断一个值不是in列表中的任意一个值
like	通配符匹配
regexp	正则表达式匹配

// num 等于 1 大于 1 小于 1 不等于1
select num = 1,num>1,num<1,num<>1 from temp;

//between and 
select 2 between 1 and 66;

//least: least (值1，值2，值3....)
select least(1,2,3,4,5),least(4,2,3,4,5);	

//greatest(v1,v2,v3...)
select greatest(1,2,3,4,5),greatest(4,2,3,4,5);	

// in 和 not in
select 2 in (1,2,3,4), 4 not in (1,2);

//like  
//'%' 匹配任何数目的字符，甚至包括零字符； 
//'_' 只能匹配一个字符

select 'stud' like '%s%';

//正则表达式： regexp :  
//	'^'匹配以该字符后面的字符开头的字符串
//	'$'匹配以该字符后面的字符结尾的字符串
//	'.'匹配任何一个单字符
//	'[...]'匹配在方括号内的任何字符 例如 [abc]匹配a、b或c
//	'*'匹配零个或多个在它前面的字符 例如 x* 匹配 xxxx xx x 

select 'ssky' regexp '^s';
select 'ssky' regexp '$y';
select 'ssky' regexp '.*';
select 'ssky' regexp '[a-z]*';

5.2.3 逻辑运算符

运算符	作用
not 或者 ！	逻辑非
and 或者 &&	逻辑与
or 或者 ||	逻辑或
XOR	逻辑异或

select not 1>0, 1>0 and 2>0, 1<0 or 2>0, 1>0 xor 2>0;

5.2.4 位运算符

运算符	作用
|	位或
&	位与
^	位异或
<<	位左移
>>	位右移
~	位取反，反转左右比特

select 1|1 ,1&1, 1^1, 1<<1,1>>1,~1;

6 MySQL 函数

6.1 数学函数

函数名	作用
abs(x)	x绝对值
pi()	返回 pi 默认 6位小数
sqrt(x)	二次方根
mod(x,y)	x对y取余
ceil(x)	返回不小于x的最小整数,返回值返回一个bigint
ceiling(x)	返回不小于x的最小整数
floor(x)	返回不大于x的最大整数值，返回值返回一个bigint
rand()	rand 返回一个0-1范围的随机数
rand(x)	x为种子，作用在同一个种子下的数据数相同
round(x)	返回最接近于参数x的整数，对x进行四舍五入
round(x,y)	返回接近于x的数，其保留到小数的y位，若y为负数，则保留x值到小数点左边y位
truncate(x,y)	对x进行截取操作，操作结果保留小数点后面指定y位
sign(x)	符号函数
pow(x,y),power(x,y),exp(x)
log(x),log10(x)
radians(x),degrees(x)
sin(X),asin(x),cos(x),acos(x)

6.2 字符串函数

字符串函数主要用来处理数据库中的字符串数据。

函数名	作用
char_length(str)	返回字符数
concat(s1,s2,…)	合并字符串，如果有任意一个字符串为null 则结果返回null
concat_ws(wstr,s1,s2,…)	合并字符串通过wstr字符串进行连接，如果有任意一个字符串为null 则结果返回null
insert(s1,x,len,s2)	s1字符串 x位置到x+len位置被s2替换
lower(str)，lcase(str)	返回全部小写字符串
upper(str),ucase(str)	返回全部大写字符串

6.3日期与时间函数

函数名	作用
curdate(),current_date()	'yyyy-mm-dd’返回日期
curtime(),current_time()	'hh:mm:ss’返回时间
current_timestamp(),localtime(),now(),sysdate()	当前日期和时间’yyyy-mm-dd’+‘hh:mm:ss’
unix_timestamp(date)	时间戳函数 到秒级
from_unixtime(timestamp)	时间戳转换为普通时间格式
dayname(date)	返回 Wednesday。。。
dayofweek(date)	返回 1表示周日 2周一

6.4 条件函数

1. if (expr , v1 ,v2), 如果表达式 是true 则if 的返回值是 v1 否则是v2;
2. ifnull(v1,v2) 如果v1是null 返回v2 如果v1,v2都不是null 返回v1;
3. case expr when v1 then r1 [when v2 then r2] [else rn] end; //类似switch
   1. case 2 when 1 then ‘one’ when 2 then ‘two’ else ‘more’ end;
   2. case when 1< 0 then ‘true’ else ‘false’ end;

6.5系统信息函数

1. version() 查看版本
2. connection_id() 查看链接数
3. show processlist; 查看当前用户的链接信息

6.6 加/解密函数

1. 加密
   1. password(str) 加密不可逆
   2. md5(str)
   3. encode(pwd,str)
2. 解密
   1. decode(encode(pwd,str),str)

7 查询数据

7.1基本查询

select [字段1 ，字段2 ，…] from [表或视图] where [查询条件]

7.2 单表查询

select * from temp;
select * from temp where num = 64;
//in 关键字
select * from temp where num in(1,9,64) order by num desc;
//between and 关键字
select * from temp where num between 1 and 63 order by num desc;
// like 关键字
select * from temp where num like '%6%' order by num desc;
// 查询null值
select * from temp where num is null
// and 多条件查询
select * from temp where num>1 and num>2 order by num desc;
// or 多条件查询
select * from temp where num>1 or num>2 order by num desc;
// distinct 消除重复
select distinct * from temp where num>1 or num>2 order by num desc;
// 多列排序 先进行num排序 再进行n排序
select * from temp where num>1 or num>2 order by num,n asc;
// 分组查询 [group by 字段] [having <条件表达式>]
select num ,count(*) as total from temp group by num;
+-----+-------+
| num | total |
+-----+-------+
|   6 |     2 |
|  64 |     1 |
+-----+-------+
select num ,group_concat(n) as n from temp group by num;
+-----+-----+
| num | n   |
+-----+-----+
|   6 | 3,2 |
|  64 | 1   |
+-----+-----+
//having 过滤分组
select num ,group_concat(n) as n from temp group by num having count(n)>1;

//limit 限制查询结果
select num ,group_concat(n) as n from temp group by num having count(n)>0 limit 2;

//聚合函数查询 avg()平均数 count() 总行数 max()最大值 min()最小值 sum()某列求和
select count(*) as count, max(n) as max, min(num) as min ,sum(n) as sum from temp;
+-------+-----+-----+-----+
| count | max | min | sum |
+-------+-----+-----+-----+
|     3 |   3 |   6 | 6   |
+-------+-----+-----+-----+

7.2 连接查询

连接是关系数据库模型的主要特点，连接查询是关系数据中最主要的查询，主要包括内连接和外连接等，通过连接运算符可以实现多个表的查询。

7.2.1内连接查询

//普通方法
select temp.num , temp1.n from 
                            
                            版权声明：本文来源CSDN，感谢博主原创文章，遵循 CC 4.0 by-sa 版权协议，转载请附上原文出处链接和本声明。

                            原文链接：https://blog.csdn.net/weixin_40944904/article/details/108794659

                            站方申明：本站部分内容来自社区用户分享，若涉及侵权，请联系站方删除。