首页 计算机科学 Mysql Mysql篇

# SQL 语言四大分类

  • Data Definition Language 简称 DDL
# 创建数据库
create database 数据库名称;
# 删除数据库
drop database 数据库名称;
# 切换数据库
use 数据库名称;
# 创建表
create table 表名(
	id int primary key auto_increment,
	name varchar(20),
	age int
);
  
# 修改表数据类型
alter table 表名 modify 列名 新数据类型;
# 例如
alter table student modify age varchar(10);
# 修改表名
alter table 表名 rename to 新表名;
# 修改表的列名称类型
alter table 表名 change 列名 新列名 新数据类型
# 修改表的字符集
alter table 表名 character set 字符集;
 
# 查看表的字符集
show create table 表名;
# 添加一列
alter table 表名 add 列名 数据类型;
# 删除一列
alter table 表名 drop 列名;
  
# 查看数据库中的所有表
show tables;
# 查询表结构
desc 表名;
# 复制表
create table 名称 like 被复制的表的名称;
# 删除表
drop table 表名称;
# 判断是否存在该表，存在再删除
drop table if exists 表名称; 
# 查看所有数据库
show databases;
# 查看单个数据库
show create database 数据库名称;
# 创建数据库
create database 名称;
# 创建数据库之前判断是否有该数据库，没有则创建
create database if not exists 数据库名称;
# 创建数据库并指定字符集
create database 名称 character set gbk或者utf8; #注意是 utf8 不是 utf-8 否则会报错
# 修改数据的字符集
alter database 数据库名称 character set 字符集;
# 删除数据库
drop database 数据库名称;
# 判断数据库存在，存在再删除
drop database if exists 数据库名称;
# 查询当前正在使用的数据库名称
select database();
# 使用数据库
use 数据库名称;
# DML 数据操纵语言
  • Data Manipulation Language 简称 DML
# 添加数据
insert into 表名(列名1,列名2,...列名N,) value(值1,值2,...值N,); #注意：列名位置需要跟值的类型的位置一致
# 删除表某一个信息
delete from 表名 [where 条件]
# 例如
delete from sutudent where id=1;
# 删除整个表
delete from 表名
# 删除整个表自动复制一个一模一样的空表
truncate table 表名;
# 修改信息
update 表名 set 列名1=值1,列名2=值2,... where 条件;
# 例如
update student set name='张三',age='25' where id=1;
# DQL 数据查询语言
  • Data Query Language 简称 DQL
# 查询语法
select 字段列表 form 表名列表 where 条件列表 group by 分组字段 having 分组之后的条件 order by 排序 limit 分页限定
# 建议使用以下格式书写
select 
	字段列表 
form 
	表名列表 
where 
	条件列表 
group by 
	分组字段 
having 
	分组之后的条件 
order by 
	排序 
limit 
	分页限定
	
# 去除重复的结果集
select distinct 字段名 from 表名;
# 占位符  %：多个任意字符 _：单个任意字符
select * from student where name like '_马%';
# 查询表
select * from 表名;
执行顺序: from->where->group by->having->order by->select
注意: group by 通常和聚合函数(avg(),count()...)一起使用,经常先使用group by关键字进行分组,然后再进行集合运算.
group by与having 一起使用,可以限制输出的结果,只有满足条件表达式的结果才会显示.
having和where的区别
两者起作用的地方不一样
Where 作用于表或视图,是表和视图的查询条件.
Having 作用于分组后的记录,用于选择满足条件的组.
# DCL 数据控制语言
  • Data Control Language 简称 DCL
# 启动 MYSQL 服务
net start mysql;
# 停止 MYSQL 服务
net stop mysql;
# 无验证密码登录 MYSQL
mysqld --skip-grant-tables i
# 创建用户
create user'用户名'@'主机名' identified by '密码';
# 添加用户:
create user '用户名'@'主机名' identified by '密码';
# 删除用户:
drop user '用户名'@'主机名';
# 修改用户密码
# 方法一
update user set password = password('新密码') where user = '用户名';
# 例如
update user set password = password( ' abc ' ) where user = 'lisi ';
# 方法二
set password for'用户名'@'主机名' = password('新密码');
# 例如
set password for 'root'@'localhost' = PASSWORD( '123');
# 查询用户:
切换到MySQL数据库
use myql;
查询user表
select * from user;
# 通配符
% 表示可以在任意主机使用用户登录数据库
# 数据库权限管理
# 查询权限
-- 查询权限
SHOW GRANTS FOR‘用户名'@'主机名;
SHOW GRANTS FOR "lisi'@"%';
# 授予权限
-- 授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
-- 给张三用户授予所有权限，在任意数据库任意表上
GRANT ALL ON *.* To 'zhangsan'@' localhost' ;
# 撤销权限
-- 撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
REVOKE UPDATE ON db3.account FROM 'lisi'@'%';
# DQL 查询语法
# 排序查询
# 排序查询语法
# order by 子句
order by  排序字段1 排序方式1,排序字段2 排序方式2; 
# 例如
select * from 表名 order by id desc; # 降序
select * from 表名 order by id asc; # 升序
# 排序方式
ASC 升序默认的
DESC 降序
# 聚合查询
-- 关键字 count max min avg sum
# 将一列看做一个整体来纵向计算
count:计算个数
max:计算最大值
min:计算最小值
avg:计算平均值
sum:计算和
# 语法
select 聚合函数(列名) from 表名;
# 聚合函数的计算会排除 NUll 值
select count(name) from student; 
select count(ifNull(列名1,值1)) from student;
# 去除该列的所有空值并返回总个数
select count(ifNull(name,null)) from student;
# 分组查询
-- 关键字 group by
-- 按照性别分组分别查询男，女同学的平均分
select sex,avg(math) from student group by sex; 
select 分组字段,聚合函数(列名) from 表名 group by 分组字段;   
-- 按照性别分组分别查询男，女同学的平均分，人数
select sex,avg(math),count(id) from student group by sex;
# 注意
分组之后查询的字段：分组字段、聚合函数
# 分页查询
关键字：limit
语法：limit 开始的索引,每页查询的条数;
公式:开始的索引 = (当前的页码-1)*每页显示的条数-- 每页显示 3 条记录
SELECT * FROM student LIMIT 0,3; -- 第 1 页
SELECT * FROM student LIMIT 3,3; -- 第 2 页
SELECT * FROM student LIMIT 6,3; -- 第 3 页
# where 和 having 的区别？
Where 在分组之前进行限定,如果不满足条件,则不参与分组
Having 在分组之后进行限定,如果不满足结果,则不会被查询出来
Where 后不可以跟聚合函数
Having 可以进行聚合函数的判断
# 四大约束关键字
  • 概念︰对表中的数据进行限定，保证数据的正确性、有效性和完整性。
# 主键约束: primary key
关键字 primary key
含义: 非空且唯一
一张表只能有一个字段为主键
主键就是表中记录的唯一标识
# 在创建表时，添加主键约束
create table stu(
	id int primary key,-- 给 id 添加主键约束
	name varchar(20)
);
  
删除主键
# 错误 alter table 表名 modify id int ;
正确删除主键
alter table 表名 drop primary key;
# 创建完表后，添加主键
alter table 表名 modify id int PRIMARY KEY;
# 自动增长
  • 自动增长通常都是和主键一起来食用
关键字：auto_increment
概念︰如果某一列是数值类型的，使用auto_increment可以来完成值得自动增长
# 在创建表时，添加主键约束，并且完成主键自增长
create table stu(
	id int primary key AUTO_INCREMENT,-- 给 id 添加主键约束和自动增长
	name varchar(20)
);
# 删除自动增长
alter table 表名 modify id int;
# 添加自动增长
alter table 表名 modify id int AUTO_INCREMENT;
# 外键约束:foreign key
  • 让表与表产生关系，从而保证数据的正确性
关键字 foreign key constraint references
# 创建表时添加外键约束
create table 表名(
	id int,
	.... #本表的字段我就不列了
	# 外键列
	constraint 外键名称,
	foreign key(外键列名称),
	references 主表名称(主表列名称)
);
注意:
  constraint 关键是是用来设置外键名称的
  foreign key 才是外键约束关键字
  references 是用来关联主表的
  
# 删除外键
alter table 表名 drop foreign key 外键名称;
# 创建表之后，添加外键 
alter table 表名 add constraint 外键名称 foreign key(外键字段名称) references 主键名称(主表列名称)
#
# 非空约束:not null
关键字：not null
# 创建表时添加非空约束
create table 表名(
	id int,
	name varchar(20) not null -- name 为非空
)
# 创建表完后，添加非空约束
alter table 表名 modify name varchar(20) not null;
# 删除 name 的非空约束
alter table 表名 modify name varchar(20);
# 唯一约束:unique
  • 某一列的值不能重复
注意：唯一约束可以有NULL值，但是只能有一条记录为null
# 在创建表时，添加唯一约束
create table 表名(
	 id int,
	  phone_number varchar(20) unique -- 手机号不可以为空
);
# 在表创建完后，添加唯一约束
alter table 表名 modify 唯一约束的列名 varchar(20) unique;
# 删除唯一约束
alter table 表名 drop index 唯一约束的列名;
# 级联操作
  • 级联操作就是表与表关联后修改某一个的表的时候另一个表的数据会同步
关键字：
	ON UPDATE/DELETE CASCADE
添加级联操作
语法:
ALTER TABLE 表名 
	ADD CONSTRAINT 外键名称 
	FOREIGN KEY(外键字段名称) 
	REFERENCES 主表名称(主表列名称) 
	ON UPDATE CASCADE ON DELETE CASCADE;
分类∶
  级联更新：ON UPDATE CASCADE
  级联删除：ON DELETE CASCADE
# 事务回滚
  • 什么是事务回滚？
  • 事务回滚就是执行多条 SQL 语句的时候，要不同时成功，否则同时失败，从而保证了数据的安全性
# 开启事务
start transction
# 回滚事务
rollback
# 提交事务
commit
# MYSQL 是自动提交事务
# 查看事务的默认提交方式:
SELECT @@autocommit; -- 1 代表自动提交，0 代表手动提交
修改默认提交方式: set @@autocommit = 0;
# 事务的四大特征∶
原子性∶是不可分割的最小操作单位，要么同时成功，要么同时失败。
持久性:当事务提交或回滚后，数据库会持久化的保存数据。
隔离性:多个事务之间。相互独立。
一致性:事务操作前后，数据总量不变事务的隔离级别(了解)
# 多表查询
# 左外链接
语法
select 字段列表 from 表1 left [outer] join 表2 on 条件
查询的是左表所有的数据以及其交集的部分
SELECT t1.*,t2.'name' FROM emp t1 LEFT JOIN dept t2 on t1.'dept_id' = t2.'id';
# 右外链接
语法
select 字段列表 from 表1 right [outer] join 表2 on 条件
查询的是右表所有的数据以及其交集的部分
SELECT t1.*,t2.'name' FROM emp t1 right JOIN dept t2 on t1.'dept_id' = t2.'id';
# 多表显式内连接
select 字段列表 from 表名1 [inner] join 表名2 on 条件
例如:
SELECT * FROM emp INNER JOIN dept oN emp.'dept_id = dept.id';
SELECT * FROM emp J0IN dept oN emp.'dept_id'= dept.'id';