# mysql

**Repository Path**: wyu0430/mysql

## Basic Information

- **Project Name**: mysql
- **Description**: No description available
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2019-07-23
- **Last Updated**: 2020-12-19

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# mysql

### create
##### create database database_name
##### create table table_name(字段名称 类型)

### alter 
##### alter table table_name add 字段名称 类型
##### alter table table_name change 字段名称 new字段名称 类型  ；alert table table_name modify 字段名称 类型
##### alter table table_name drop 字段名称
##### alter TABLE 表名 ADD CONSTRAINT 约束别名 约束
##### alter table table_name rename to new_table_name
##### alter table table—_name drop primary key
##### alter table table—_name drop foreign key fk

### drop
##### drop database database_name
##### drop table table_name

### insert into
##### insert into table_name(字段名) values(值)
##### INSERT INTO 表名1(属性1,….) SELECT 表名2.属性1,…. FROM 表名2

### update
##### UPDATE 表名 SET 属性1=新值1,属性2=新值2,….[WHERE条件子句]

### delete
##### DELETE FROM 表名 [WHERE条件子句]
##### TRUNCATE TABLE 表名

### select
###### select * from table where 字段 in()
###### select * from table where 字段 between ... and ...
###### select * from table where 字段 is null
###### select * from table where 字段 like ""

### order by
###### select * from table order by 字段 desc /acs

### group by
###### select  字段,count(*) from table group by 字段

###函数
###### select distinct() from table
###### select count() from table
###### select avg() from table
###### select sum() from table
###### select min() from table
###### select max() from table
###### select group_concat() from table
###### upper(x),lower(x),length(x),char_length(x),replace(x,char1,char2),substring(x,star,[length]),lpad(x,length,char),rpad(),left(x,length),right(),concat(str1,str2),instr(str,char)
###### curdate(),curtime(),now(),last_day(x),date_add(x,interval 1 day),date_format(x,'%y%m%d &w')
###### abs(x),floor(x),cellling(x),mod(x,y),round(x),truncate(x,y),greatest(x,y,z),least(x,y,z)
###### limit

###控制流函数

if(expr,x,y)
ifnull(x,y)
nullif(x,y)

case 
when expr then x
else y
end

case x
when x1 then y1
when x2 then y2
else y3
end

###多表查询
##### 内连接
###### select t1.*,t2.* from t1 join t2 on t1.i1=t2.i2;
###### select t1.*,t2.* from t1 inner join t2 on t1.i1=t2.i2;
###### select t1.*,t2.* from t1 ,t2 where t1.i1=t2.i2;
###### SELECT * FROM emp NATURAL JOIN dept;
###### SELECT e1.ename AS '员工姓名',e2.ename AS '上级主管' FROM emp e1,emp e2 WHERE e1.mgr=e2.empno;

##### 左连接
###### select a.i1,b.i2 from t1 as a left join t2 as b on a.i1=b.i2;

##### 右连接
###### select a.i1,b.i2 from t1 as a right join t2 as b on a.i1=b.i2;

##### 建用户，赋予权限
###### 语法：CREATE USER 用户名@用户地址 IDENTIFIED BY 用户口令
###### CREATE USER zhangsan@'192.168.1.100' IDENTIFIED BY '123456';
###### DROP USER zhangsan@'192.168.1.100';
###### ALTER USER zhangsan@'%' IDENTIFIED BY '123456789
###### GRANT SELECT ON test.* TO 'c'@'localhost' IDENTIFIED BY '123456';
###### GRANT ALL ON *.* TO 'user'@'localhost' IDENTIFIED BY '123456';
###### SHOW GRANTS
###### REVOKE ALL ON *.* FROM 'user'@'localhost'; 
###### FLUSH PRIVILEGES

###### left join 返回包括左表中的所有记录和右表中联结字段相等的记录
###### right join 返回包括右表中的所有记录和左表中联结字段相等的记录
###### inner join 只返回两个表中联结字段相等的行

#### 索引
######  CREATE INDEX index_name ON table(field);
######  SHOW INDEX FROM 表名
######  DROP INDEX 索引名称 ON 表名


#### 视图
###### CREATE VIEW 视图名称 AS SELECT语句
###### CREATE VIEW view_test AS SELECT * FROM table1 WHERE id=1;
###### SHOW TABLE STATUS WHERE COMMENT='VIEW';
###### DROP VIEW 视图名称
###### ALTER VIEW 视图名称 AS SELECT语句


#### 触发器
###### CREATE TRIGGER 触发器名称 触发器时机 触发事件 ON 表名 FOR EACH ROW 触发器程序体
CREATE TRIGGER t1 AFTER INSERT ON emp FOR EACH ROW
BEGIN
	IF NEW.hiredate<'2016-01-01' THEN
	DELETE FROM emp WHERE empno=NEW.empno;
	END IF;
END;

####### SHOW TRIGGERS



#### 函数
CREATE FUNCTION 函数名称(参数1 数据类型,参数2 数据类型,…..)
	      RETURNS 返回值类型
	      函数体
函数体可以是一条SQL语句，也可以是多条SQL语句
函数体以BEGIN开始，END结束

CREATE FUNCTION get_date(d datetime)
RETURNS VARCHAR(50)
BEGIN
	RETURN DATE_FORMAT(d,'%Y年%m月%d日 %h时%i分%s秒');
END;

SELECT get_date(‘2016-11-03 10:10:10’);
####### SHOW FUNCTION STATUS;


#### 存储过程
###### CREATE PROCEDURE 存储过程名称(参数列表) 程序体
CREATE PROCEDURE pro(IN dno INT,OUT count_num INT)
BEGIN
	SELECT COUNT(empno) INTO count_num FROM emp WHERE deptno=dno;
END;

CALL pro(1,@cn);
SELECT @cn;
####### SHOW PROCEDURE STATUS;
#######  DROP PROCEDURE 存储过程名称;

#### 事务
####### 事务ACID 原子性(要么全部做完，要么全部不做) 一致性(完整性约束没有被破坏) 隔离性(事务之间彼此没有任何干扰) 持久性（不能回滚）
CREATE TABLE transaction_test( id int(5));

begin;
insert into transaction_test value(5);
commit;

begin;
insert into transaction_test value(6);
rollback;

begin;
insert into transaction_test value(6);
SAVEPOINT save_name;
insert into transaction_test value(7);
rollback to save_name;



# Mysql数据库常用知识点汇总
数据库操作
1：查看数据库
SHOW  DATABASES;
#默认数据库：
mysql    -用户权限相关数据
test     -用于用户测试数据
information_sechema   -mysql本身架构相关数据

2：创建数据库
# utf-8 编码
CREATE DATABASE 数据库名称 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
# gbk 编码
CREATE DATABASE 数据库名称 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

3：使用数据库
USE db_name;
# 可以不使用分号

4：用户管理
# 创建用户
create user '用户名'@'IP地址' identified by '密码';
# 删除用户
drop user '用户名'@'IP地址';
# 修改用户
rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';;
# 修改密码
set password for '用户名'@'IP地址' = Password('新密码')
PS：用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（不建议）

5：授权管理
# 查看权限
    show grants for '用户'@'IP地址'
# 授权
    grant  权限 on 数据库.表 to   '用户'@'IP地址'
# 取消权限
    revoke 权限 on 数据库.表 from '用户'@'IP地址'
常用权限：
all privileges   除grant外的所有权限
select              仅查权限
select,insert    查和插入权限
usage              无访问权限

对于目标数据库以及内部其他：
数据库名.*                                                 数据库中的所有
数据库名.表                                               指定数据库中某张表
数据库名.存储过程                                   指定数据库中的存储过程
*.*                                                                所有数据库

6：授权局域网内主机远程连接数据库
#百分号匹配法
    grant all on *.* to 'test'@'192.168.200.%' identified by 'test123';
#子网掩码配置法
    grant all on *.* to 'test'@'192.168.200.0/255.255.255.0' identified by 'test123';
#刷新权限
    flush privileges;
#远程登陆连接
    mysql -utest -ptest123 -h 192.168.200.96


表操作
1、创建表

# 基本语法：
create table 表名(
    列名  类型  是否可以为空  默认值  自增  主键，
    列名  类型  是否可以为空
)ENGINE=InnoDB DEFAULT CHARSET=utf8

not null                # 不可以为空
default 1              # 默认值为1
auto_increment   # 自增
primary key         # 主键
constraint 外键名 foreign key (从表字段’自己‘) references 主表(主键字段)    # 外键

是否可空，null表示空，非字符串
not null    - 不可空
null          - 可空

默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
            create table tb1(
                nid int not null defalut 2,
                num int not null
            )

自增，如果为某列设置自增列，插入数据时无需设置此列，默认将自增（表中只能有一个自增列）
            create table tb1(
                nid int not null auto_increment primary key,
                num int null
            )
            或
            create table tb1(
                nid int not null auto_increment,
                num int null,
                index(nid)
            )


2、删除表
drop table 表名

3、清空表

# 表还存在，表内容清空
delete from 表名
truncate table 表名

4、修改表

# 添加列：
        alter table 表名 add 列名 类型
# 删除列：
        alter table 表名 drop column 列名
# 修改列：
        alter table 表名 modify column 列名 类型;  -- 类型
        alter table 表名 change 原列名 新列名 类型; -- 列名，类型

# 添加主键：
        alter table 表名 add primary key(列名);
# 删除主键：
        alter table 表名 drop primary key;
        alter table 表名  modify  列名 int, drop primary key;

# 添加外键：
        alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);
# 删除外键：
        alter table 表名 drop foreign key 外键名称

# 修改默认值：
      ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
# 删除默认值：
      ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
# 更改表名
         rename table 原表名 to 新表名;

增删改表的字段
#增加表字段，altertable法。
1>    语法： altertable 表名 add 字段 类型 其他；
eg:给student表添加一个sex列：alter table student add sex char(4);

eg:给student表添加一列name，且该字段在sex的前面：alter table student add name vacrhar(20)  after sex;

eg:给student表添加一列class，且该字段位于表的第一列：alter table student add class varchar(20)  first;

#更改表名字，rename法：
2>语法：rename  table 原表名  to  新表名；
eg:把AAA表改名为BBB表：rename table AAA to BBB；


#删除表
1>    语法：drop table <表名>；
eg：删除掉student表：drop table student；


表内容操作
1、增
语法：insert into 表 (列名,列名...) values (值,值,值...)
# 插入单条数据
        insert into 表 (列名,列名...) values (值,值,值...)
# 插入多条数据
       insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)
# 插入另一条语句的查询结果
        insert into 表 (列名,列名...) select 列名,列名... from 表

2、删
语法：delete from 表
delete from 表;
delete from 表 where id＝1;

3、改
语法：update 表 set name ＝ 'nick' where id>1
update 表 set name ＝ 'nick' where id>1

4、查
语法：select * from 表
select * from 表
select * from 表 where id > 1
select nid,name,gender as gg from 表 where id > 1
# as 做别名

5、条件
语法：select * from 表 where id > 1

6、通配符
语法：select * from 表 where name like '_n%'

7、限制
语法：select * from 表 limit 9,5;
eg: select * from 表名    limit 5；            #查询表中的前五行数据
     select * from 表名   limit  9,5；        #从第九行开始的五行数据
     select * from  表名  limit 5 offset 9；#从第九行开始的五行数据

8、排序
语法：select * from 表 order by 列1 desc,列2 asc

9、分组
语法：select num from 表 group by num
注：group by 必须在where之后，order by之前

10、连表
语法：inner join . on、left join . on、right join . on

11、组合
语法：union、union all





## mysql 三范式(一般知识点,记住即可)

#### 概述

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。但是有些时候一昧的追求范式减少冗余，反而会降低数据读写的效率，这个时候就要反范式，利用空间来换时间。

#### 分类

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。所以这里就只记录三范式相关的知识。

三范式：

	1NF:字段不可分;
	 
	2NF:有主键，非主键字段依赖主键; 
	
	3NF:非主键字段不能相互依赖; 

解释: 

	1NF:原子性 字段不可再分,否则就不是关系数据库; 
	
	2NF:唯一性 一个表只说明一个事物; 
	
	3NF:每列都与主键有直接关系，不存在传递依赖; 
	
	
#### 第一范式（1NF）

即表的列的具有原子性,不可再分解，即列的信息，不能分解, 只要数据库是关系型数据库(mysql/oracle/db2/informix/sysbase/sql server)，就自动的满足1NF。数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。如果实体中的某个属性有多个值时，必须拆分为不同的属性 。通俗理解即一个字段只存储一项信息。


#### 第二范式（2NF）

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要我们设计一个主键来实现(这里的主键不包含业务逻辑)。

即满足第一范式前提，当存在多个主键的时候，才会发生不符合第二范式的情况。比如有两个主键，不能存在这样的属性，它只依赖于其中一个主键，这就是不符合第二范式。通俗理解是任意一个字段都只依赖表中的同一个字段。（涉及到表的拆分）

#### 第三范式（3NF）

满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主键字段。就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放(能尽量外键join就用外键join)。很多时候，我们为了满足第三范式往往会把一张表分成多张表。

即满足第二范式前提，如果某一属性依赖于其他非主键属性，而其他非主键属性又依赖于主键，那么这个属性就是间接依赖于主键，这被称作传递依赖于主属性。 通俗解释就是一张表最多只存两层同类型信息。

#### 反三范式

没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，提高读性能，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，减少了查询时的关联，提高查询效率，因为在数据库的操作中查询的比例要远远大于DML的比例。但是反范式化一定要适度，并且在原本已满足三范式的基础上再做调整的。