MYSQL课程总结

MYSQL课程总结

create database qq;
use qq;

create table `a`(
`id` int not null,
`name` varchar(20) not null,
`sex` varchar(10)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

NOT NULL
(非空约束，规定某个字段不能为空)

UNIQUE
(唯一约束，规定某个字段在整个表中是唯一的)

PRIMARY KEY
(主键,非空且唯一)

FOREIGN KEY
(外键)

创建：
id int not null,

增加：
alter table a modify sex varchar(10) not null;

取消 not null 约束：
alter table a modify sex varchar(10) null;

取消 not null 约束，增加默认值：
alter table a modify name varchar(20) default ‘abc’ null;

表示id和姓名的组合不能重复

create table `a`(
`id` int not null,
`name` varchar(20) not null,
`sex` varchar(10),
constraint uk_id_name unique(id,name)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

添加唯一约束：
①alter table user add unique(id,name);
②alter table user add constraint uk_id_name unique(id,name);
③alter table user modify id int unique;

删除约束：
alter table user drop index uk_id_name;

列级模式：

create table `a`(
id int auto_increment primary key,
name varchar(20)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

表级模式：

create table `a`(
id int not null auto_increment,
name varchar(20),
pwd varchar(15),
constraint a_id_pk primary key(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

组合模式：

create table `a`(
id int not null,
name varchar(20),
pwd varchar(15),
constraint a_pk primary key(name,pwd)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

列级模式：
id int auto_increment primary key,

表级模式：
id int not null auto_increment,
constraint a_id_pk primary key(id)

组合模式：
constraint a_pk primary key(name,pwd)

删除主键约束：
alter table a drop primary key;

添加主键约束：
alter table a add primary key(name,pwd);

修改主键约束：
alter table a modify id int primary key;

主表：
create table dept(
dept_id int auto_increment primary key,
dept_name varchar(20)
);

从表：
create table emp(
emp_id int auto_increment primary key,
last_name varchar(15),
dept_id int
);constraint emp_dept_id_fk foreign key(dept_id) references dept(dept_id)

分组：
group by
可以使用GROUP BY子句将表中的数据分成若干组。
在SELECT 列表中所有未包含在组函数中的列都应该包含 在 GROUP BY 子句中。

HAVING 过滤分组

1. 行已经被分组。
2. 使用了组函数。
3. 满足HAVING 子句中条件的分组将被显示。

子查询：

关联：
join,on

SELECT 
bt.id,NAME,boyname 
FROM beauty bt Inner join boys b on bt.`boyfriend_id`=b.id ; 

distinct:

函数：
组函数类型
AVG()
COUNT()
MAX()
MIN()
SUM()
不能在 WHERE 子句中使用组函数。

1.作用于单列
select distinct name from A

2.作用于多列
select distinct name, id from A

3.COUNT统计
select count(*) from (select distinct xing, name from B) AS M;

4.distinct必须放在开头
select id, distinct name from A; --会提示错误，因为distinct必须放在开头

调用存储过程：
call 存储过程名(参数列表)

调用函数：
Select 函数名(参数列表)

DELIMITER $

创建语法
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体（一组合法的SQL语句）
END
DELIMITER $

IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

例：

SELECT * FROM admin;
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username,`password`) 
VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');
END $
#调用
CALL myp1()$

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END
调用语法：
SELECT 函数名(参数列表)

用户变量
变量的赋值方式一：直接赋值，方式有以下两种。
set @var1 = 2;
set @var2 := 3;
SELECT @变量名:=值;
变量的赋值方式二：将查询结果赋值给变量
select u_id into @var3 from users where u_name = ‘zhangsan’;

使用（查看变量的值）
SELECT @变量名;

局部变量
赋值（更新变量的值）
方式一：
SET 局部变量名=值;
SET 局部变量名:=值;
SELECT 局部变量名:=值;
方式二：
SELECT 字段 INTO 具备变量名
FROM 表;

使用（查看变量的值）
SELECT 局部变量名;

查看函数
SHOW CREATE FUNCTION myf3;

删除函数
DROP FUNCTION myf3;

添加新用户
①允许本地 IP访问localhost的Mysql数据库
create user ‘editest’@‘localhost’ identified by ‘editest123456’;

②允许外网IP访问数据库editest，本命令包含上面的命令，是所有的IP都可以访问该数据库
create user ‘editest’@’%’ identified by ‘editest123456’;

③用户创建完成后，刷新授权
flush privileges;

创建一个新的数据库，并使用show databases命令查看数据库是否创建OK
create database editestdb;
show databases;

将改用户editest赋权给数据库editestdb，并刷新授权

grant all privileges on `editestdb`.* to 'editest'@'localhost';
grant all privileges on `editestdb`.* to 'editest'@'%'; 
flush privileges;