MySql基础操作

一、数据库操作

操作	代码
创建数据库	create database 数据库名；
选择数据库	use 数据库名称;
删除数据库	drop database 数据库名
查看数据库	show database;

二、数据表操作

1、创建表

# 中括号中内容是可写可不写
create table [if not exists] `表名` (#中括号内是判断表是否存在，如果不存在就创建
    `字段名1` 数据类型 [字段属性] [完整性约束条件] [索引] [注释],
    `字段名2` 数据类型 [字段属性] [完整性约束条件] [索引] [注释],
    `字段名3` 数据类型 [字段属性] [完整性约束条件] [索引] [注释],
    ……………………
    `字段名n` 数据类型 [字段属性] [完整性约束条件] [索引] [注释]
)[表类型] [表字符集] [注释]；

2、数据类型

数据类型的一些修饰
unsigned 标识为无符号数
zerofill 位数不足以0填充
字符串类型

日期类型

3、完整性约束

关联关系：表和表之间的相互关联的条件

完整性约束是为了保证数据库

• 要求用户对数据的操作符合特定要求
• 不满足要求的，数据库将拒绝用户操作
• 可靠性 + 准确性 = 数据完整性
• 创建表：保证数据完整性 = 实施完整性约束

四种完整性约束

• 域完整性：字段(列)值的要求，如：要求该列只能是整形、性别只能是男或者女等。
• 实体完整性：基于一行数据，如：要求数据不能重复等。
• 自定义完整性：插入数据时是否满足自定义条件才能录入数据(可能是多个表的要求)
• 引用完整性：一个表引用其他表数据时保证数据范围正确等。

设置字符集编码

create table [if not exists] 表名（
    # 代码
）charset = 字符集名;
# utf8mb4 ： most bytes 4，mb4是utf8的超集，完全兼容utf8，能够使用四个字节存储更多的字符

查看当前服务器当前默认字符集：show global variables like '%character_set%';

例：

creat table test(
    `id` int(4) primary key,
    `name` varchar(25) not null,
    `age` int
)charset = utf8mb4 engine=InnoDB comment '这是注释';

4、查看表

查看表是否存在：show tables;
查看表定义： describe 表名;
避免DOS窗口乱码 ，可执行 SET NAMES gbk; 或 SET NAMES utf-8;

5、删除表

drop table [if exists] 表名;

6、修改表

#修改表中自增列，从10开始
alter table `表名` auto_increment=10;
#修改表名 
alter table `旧表名` rename [to] `新表名`;
#增加字段
alter table `表名`  add `字段名` 数据类型[属性];
#修改字段
alter table `表名` change `原字段名` `新字段名` 数据类型 [属性];
#删除字段
alter table `表名` drop `字段名`;
#添加主键
alter table `表名` add constraint `主键名` primary key `表名` (`主键字段`); 
或在创建表的时候 primary key(`字段名`);
#添加外键
alter table `表名` add constraint `外键名` foreign key (`外键字段`) references `关联表名` (`关联字段`);
或在创建表的时候 foreign key(`字段名`) references `表名` (`关联字段`);

7、存储引擎

• 存储引擎是处理不同类型SQL操作组件
• InnoDB是默认的、最通用的存储引擎

#查看所支持的引擎
show engines; 
#查看数据库默认使用的引擎
show variables like '%storage_engine%';
#修改表的存储引擎
alter table `表名` engine=引擎; 
#查看某个表当前使用的存储引擎
select engine from 数据库.表名 where 条件;

注意:
1、如果需要提交、回滚、崩溃恢复能力的事务安全功能，并要求实现并发控制，InnoDB是不二之选
2、如果表主要用来插入和查询数据，MyISAM能提供较高处理效率
3、如果只是临时存放数据，量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎
4、如果只有insert和select操作，可以选择Archive，支持高并发的插入操作，但本身不是事务安全的
5、使用哪一种引擎需要灵活选择，一个数据库中的表可以设置不同的存储引擎以满足各种性能和实际需求.

三、数据操作

1、插入

#插入单条数据
insert into 表名 (字段) value (值);
#插入多条数据
insert into 表名 (字段) value (值1),(值2),(值3), ... ,(值n);

2、删除

delete from 表名 [where 条件];	  	#该操作指挥清楚指定数据
truncate table 表名;           		#会清楚自动标识等起始数据
alter table 表名 drop foreign 外键名;	#删除外键

3、更新

update 表名 set 字段1=值1,字段2=值2,...,字段n=值n where 条件;

4、查询

select 列名|表达式|函数|常量 from 表名 where 条件 order by 排序列名 [ASC或DESC];
#可使用as来给字段或者表格起别名
select stuId as '学号' from student as S;

• Exists 子查询：子查询结果为true则正常运行，若为false则外层查询不再进行
• 分组查询：select 列名 from 表名 where 条件 group by 列1,列2,…,列n having 条件
• 内连接（两表交集）：select from 表1 inner join 表2 on [条件];
• 左外连接：select from 表1 left join 表2 on [条件];
• 右外连接：select from 表1 right join 表2 on [条件];
• 全连接： select 列1,列2,...,列n from 表1 union [all] select 列1,列2,...,列n from 表2;

5、事务

基本代码

start transaction;#开始事务
#代码块
rollback;#回滚事务
commit;#提交事务

事务隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read uncommitted）	是	是	是
不可重复读（readcommitted）	否	是	是
可重复读（repeatable read）	否	否	是
串行化（serializable）	否	否	否

# 查看当前会话隔离级别
select @@tx_isolation;
# 设置当前会话隔离级别
set session transaction isolation level repeatable read;
# 查看系统当前隔离级别
select @@global.tx_isolation;
# 设置系统当前隔离级别
set global transaction isolation level repeatable read;

四、用户与权限操作

1、创建用户：create user '用户名'@'IP地址或主机地址' identified BY '密码';
2、修改用户名：rename user '用户名'@'IP地址或主机地址' to '修改后名称'@'修改后主机地址';
3、删除用户：drop user '用户名'@'IP地址或主机地址';
4、分配权限：grant 权限列表 on 数据库[.表] to '用户名'@'IP地址或主机地址' [with grant option];
5、查看用户权限：show grants for '用户名'@'IP地址或主机地址'; show grants;
6、撤销用户权限：revoke 权限 on 数据库[.表] form '用户名'@'IP地址或主机地址';
7、应用并刷新：flush privileges;

五、常用函数

1、聚合函数

内置函数	作用
AVG	返回字段平均值
COUNT	返回字段行数（非空）
MAX	返回字段最大值
MIN	返回字段最小值
SUM	返回字段和

2、字符串函数

函数	作用
concat(str1,str2,str3,…,strn)	字符串拼接
insert(str,pos,len,newstr)	字符串替换(pos是起始点，len是替换长度)
lower(str)	将字符串转换为小写
upper(str)	将字符串转换为大写
substring(str,pos,len)	字符串提取

3、日期函数

函数名	作用
curdate()	返回当前日期
curtime()	返回当前时间
now()	返回当前日期和时间
week(date)	返回日期date是一年的第几周
year(data)	返回日期data的年份
hour(time)	返回时间time的小时值
minute(time)	返回时间time的分钟值
datediff(date1,date2)	返回相隔天数(date1-data2)
timestampdiff(type,data1,data2)	返回两日期相差多少type，type可以是year、month、day、hour等（date2 - date1）
adddate(data,n)	返回日期date加上n天后的日期
date_add(data,interval ‘n’ type)	返回日期ate加上n type后的第日期(n可为负，type是year，daymonth等)

4、数学函数

函数名	作用
ceil(x)	返回大于等于x的最小整数
floor(x)	返回小于等于x的最大整数
rand()	返回0~1之间的随机数
round(x,d)	返回对x进行四舍五入保留d位小数，d可负

5、流程函数

①if(value,v1,v2) 如果value是真，返回v1，否则返回v2
②ifnull(v1,v2) 如果v1不为空，返回v1，否则返回v2

6、其他常用函数

select拼接使用

函数名	作用
database()	返回当前数据库名
version()	返回当前数据库版本
user()	返回当前登录用户名
password(str)	对str进行加密
MD5()	返回str的MD5值

六、一些补充

1、比较运算符和操作符

比较运算	符号
等于	=
不等于	<>或or
大于、大于等于	>、>=
小于、小于等于	<、<=

操作符
① like：模糊匹配
%代表任意多个字符
_代表一个字符
② between - and：闭区间
③is null：空; is not null：非空;

2、limit子句

select 字段名列表 from 表名或试图 where 条件 group by 分组的字段名 order by 排序的字段名 [ASC或DESC] limit [位置偏移量m] 行数n;
#位置偏移量即从第m+1个数开始，展示n行。

3、子查询

例子

#查询与张三相同登录密码的人的信息
select * from StuInfo
where LoginPwd = (select LonginPwd from StuInfo where StuName = '张三');

将子查询与比较运算符联合使用，子查询返回值不大于1；
如果将 = 改为 in 可返回多条记录。

4、查看最后一次插入的自增数据(id)

select last_insert_id();