mysql的个人总结

mqsql的总结

1登录mysql数据库
mysql -uroot -proot;
2显示mysql中所有数据库
SHOW databases
3注释 单行-- 或# 多行/* */
DDL 数据定义语言 (Data Definition Language) 操作数据库和表
DML 数据操作语言 (Data Manipulation Language 增删改表
DQL 数据查询语言 (Data Query Language) 查询表
DCL 数据控制语言 (Data Control Language) 权限
4查看数据库的编码集 和某个数据库的创建语句
SHOW CREATE DATABASE 数据库名；
5创建数据库
直接创建数据库
CREATE DATABASE数据库名 ;
判断数据库是否存在再创建
CREATE DATABASE IF NOT EXISTS 数据库名；
创建数据库指定编码集
CREATE DATABASE 数据库名CHARACTER SET GBK/UTF8；
6修改数据库编码集
ALTER DATABASE 数据库名 CHARACTER SET GBK/UTF8;
7删除数据库
DROP DATABASE 数据库名
DROP DATABASE IF EXISTS数据库名
8进入数据库
USE 数据库名；
9 查询正在使用的数据库名
SELECT DATABASE();
表的操作DDL
1 查询数据库中的所有表
SHOW TABLES;
2查询表结构
DESC 表名；
3创建表
CREATE TABLE 表名(
列名 数据类型，
列名 数据类型
列名 数据类型

)；
4数据类型
Int 整数类型
Double 小数类型 double(5,2)共五位 小数两位
Date 日期类型 没有时分秒
DateTime 日期 yyyy-MM-dd-HH:mm:ss
Timestamp： 时间戳 不赋值默认当前时间
Varchar 字符串类型 carchar(20) 20个字符

5删除表
DROP TABLE IF EXISTS表名
6复制表
CREATE TABLE 新表名 LIKE 要复制的表； 
7修改表名
ALTER TABLE 表名 RENAME TO 新表名
8修改表的字符集
ALTER TABLE 表名 CHARACTER  SET utf8
9添加列
ALTER TABLE 表名 add 列名 列的类型
10 修改列名
ALTER TABLE 表名 CHANGE 旧列名 新列名 新的数据类型
11 修改列的数据类型
ALTER TABLE 表名 MODIFY  列名 列名类型
12 删除列
ALTER TABLE 表名 DROP 列名

DML修改表中数据
1添加数据
Insert into 表名(字段1，字段2，字段N) values(字段1,字段2, 字段N)
2删除表中数据
如果不加where条件 默认删除表中所有数据 有多少条就执行多少次 效率低
DELETE FROM 表名 WHERE 字段名 = 字段内容
3删除表中所有数据 （删除表中所有数据 创建新表） 效率高
TRUNCATE TABLE 表名
4修改数据 如果不加条件 将修改表中所有数据
UPDATE 表名 SET 列名1=修改值, 列名2=修改值 where 条件

DQL查询表记录
1查询表中数据
SELECT * FROM 表名
语法
Select 字段列表 from 表名列表 where 条件列表 group by 分组条件 order by 排序条件 limit 分页

2基础查询
SELECT 字段,字段 FROM 表名；
3去重结果查询
SELECT DISTINCT 字段 FROM 表名
4 ifnull 函数去除null
SELECT NAME ,english 英语, math 数学 ,math+IFNULL(english,0) as总分 FROM student3
5条件查询 where 子句跟条件
查询20 到30岁之间的学生
SELECT * FROM student where age between 20 and 30
SELECT * FROM student where age>20 and age <30
SELECT * FROM student where age>20 && age<30
查询年龄不等于20的学生
SELECT * FROM student where age<>20
查询19 20 22岁的学生
SELECT * FROM student where age =19 OR age=20 OR age =22;
SE:ECT * FROM student WHERE age=19|| age = 20|| age = 22
SELECT * FROM student where in(19,20,22);
查询表中英语字段为NULL的数据
SELECT * FROM STUDENT ENGLISH IS NULL;
查询表中英语字段不是NULL的数据
SELECT * FROM STUDENT ENGLISH IS NOT NULL
6模糊查询
_单个任意字符 %多个任意字符
查询名字中第二字为马的人
SELECT * FROEM student like(_马%)
查询名字中包含 W的人
SELECT * FROM student LIKE(%W%)
7排序查询 默认升序查询 ASC升序 DESC降序
查询英语成绩按照降序排列
SELECT * FROM STUDENT ORDER BY math DESC、
按照数学成绩排名如果数学成绩相同按照英语成绩排序
SELCET * FROM student ORDER BY MATH ASC ,ENGLISH ASC

8聚合函数 :将一个数据作为整体，进行纵向计算
聚合函数的计算排除了null数值
Count:计数
Max 最大值
Min 最小值
Sum 求和
Avg 平均值
9分组查询 group by
1分组后查询的字段 最好只是聚合函数或分组字段
2 where 和having 的区别
Where 在分组前 having 在分组后限定
Where后不可跟聚合函数 having 可以跟聚合函数

按照性别分组分别查询男女同学平均分 并计算个数

SELECT avg(ifnull(English,0)+math),count(id)FROM STUDENT group by sex

对数学高于70分的男女同学进行分组并计算平均分

SELECT avg(ifnull(English,0)+math)平均分, count(id)from student where math>70 group by sex

对英语成绩高于70的男女同学进行分组 分组后人数不高于3的不显示

SELECT sex, COUNT(id) 人数 ,AVG(IFNULL(english,0))FROM student3 WHERE English>70 GROUP BY sex HAVING 人数>3

10分页查询 limit 开始索引 ,每页查询条数 LIMIT是 方言
开始索引 = （当前页码索引-1）*每页显示条数
SELECT * FROM STUDENT LIMIT 0,3
SELECT * FROM STUDENT LIMIT 3,6
SELECT * FROM STUDENT LIMIT 6,9

MYSQL约束
1 表的约束
1主键约束 primary key 非空且唯一 一张表只能有一个为主键 表的唯一标识
1删除主键
ALTER TABLE 表名 DROP PRIMARY KEY
2添加主键
ALTER TABLE 表名 MODIFY 主键列 主键数据类型 PRIMARY KEY
3主键自增 auto_INCREMENT
删除主键自增
ALTER TABLE 表名 MODIFY I 主键列名 主键类型
添加主键自增
ALTER TABLE 表名 MODIFY 主键列名 PRIMARY KEY AUTO_ INCREMENT

2 外键约束	foregin key	
	在创建表的语句末尾添加
	CONSTRAINT 外键名称 FOREIGN KEY(外键列名)  REFERENCES 主表名称 (主表列名)
	2删除外键 
	ALTER  TABLE 表名 DROP FOREIGN KEY 外键名
	3创建表后添加外键
	ALTER TABLE 表名 ADD  CONSTRAINT 外键名称 FOREIGN KEY(外键列名) REFERENCES 主表名称(主表列名)
	4级联操作
		ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键列名) REFERENCES 主表名称(主表列名) ON UPDATE CASCADE  ON DELETE CASCADE
	级联更新 ON UPDATE CASCADE 
	级联删除 ON DELETE CASECADE

3 非空约束	not null	
	1建表时添加非空约束
	CREATE TABLE 表名(
			Id int not null,
			NAME VARCHAR(20)
			)
	2创建表后修改非空约束
	ALTER TABLE 表名 MODIFY NAME  VARCHAR(20) NOT NULL

4 唯一约束	unique 

唯一约束可以多个null
1 删除 唯一约束
ALTER TABLE 表名 DROP INDEX 被约束的列名
2创建表添加唯一约束
CREATE TABLE 表名(
NAME VARCHAR(20),
PHONE int UNIQUE
3添加唯一约束
ALTER TABLE 表名 MODIFY NAME VARCHAR(20) UNIQUE

MYSQL的数据库设计
1一对多
	在多的一方建立外键 来指向单一表的主键
2 多对一， 一对多
3 多对对

多对多关系需要第三张中间表 中间表至少包含两个外键字段分别指向两张表的主键
4一对一

范式
 第一范式  每一列都是不可分割的原子数据

第二范式 函数依赖 完全依赖 部分依赖 传递性 消除部分依赖 (消除主属性的码间依赖）
第三范式 消除传递依赖

数据库备份与还原
	1命令行方式
		备份
			Mysqldump -u user -p password  数据库名 > 保存路径
		还原
			创建数据库执行SQL 
				Source +保存路径执行
	2 图形化界面

多表查询
1内链接查询
1隐式内连接 where 子句清除内部条件
例：查询 dept 和emp 表中 要求显示部门 姓名 性别
SELECT
E1.NAME,
D1.NAME,
E1.GENDER

FROM 
	DEPT D1,
	EMP E1
WHERE
	D1.id = E1.ID
	2显示内连接 INNER可省略
SELECT * FROM 表1  别名 INNER JOIN 表2 别名 ON 连接条件

2外连接查询
	1左外连接 查询左表所有数据及其与交集部分  OUTER可以省略
	SELECT * FORM  A表  A表别名 LEFT OUTER JOIN B表 B表别名 ON 条件
	2 右外连接	查询右表所有数据及其与交集部分  OUTER可以省略

SELECT * FORM A表 A表别名 RIGHT OUTER JOIN B表 B表别名 ON 条件

3子查询 SELECT 嵌套
	1子查询单行单列作为条件使用
		查询某表的最大值返回给另一个表
SELECT * FROM 表名 WHERE (SELECT MAX() FROM表名)
	2子查询的结果是多行单列的
SELECT *  FROM 表名(in（多行单列的字查询）)
	3子查询的结果是多行多列的(子查询表作为查询结果)
SELECT * FROM  	表一  别名，子查询表 别名  whiere  查询条件

事务
四大特征
1原子性
2隔离性
3一致性 操作前后数据总量不变
4持久性 事务提交后 数据会持久保存
隔离级别
多个事务同时操作一批数据会发生下面结果
1 脏读 一个事务读到了另一个事务没有提交的数据
2不可重复读(虚读) 同一个事物中读到的数据不一样
3 幻读 一个事务操作DML 另一个事务添加了一条数据 第一个事务查询不到自己的修改
1Read uncommitted 读未提交 脏读 不可重复读 幻读
2 Read committed 读已提交 不可重复度读 幻读 ORALCE默认
3 Repeatable Read 可重复度 幻读 MYSQL默认
4 Serializable 串行化 可解决所有问题

查看隔离级别
	SELECT @tx_isolation
设置隔离级别
	SET GAOBAL TRANSEACTION ISOLATION LEVEL 级别字符串

ORACLE默认手动提交
MySQL 增删改 默认自动提交
手动提交 START TRANSACTION
自动默认提交

开启事务
START TRANSACTION
回滚事务
ROLLBACK
提交事务
COMMIT

查询事务默认提交方式
SELECT @@AUTOCOMMIT
设置事务自动手动提交
SET @@AUTOCOMMIT 0 /1 1代表自动 0代表手动

MYSQL权限管理 DCL
查询用户
切换到mYSQL数据库
USE MYSQL
查询usel表 % 表示所有 所有主机
SELECT * FROM USER
添加用户 % 表示所有 所有主机
CREATE USER “用户名” @ “主机名” IDENTFIED BY “密码“
删除用户
DROP USER “用户名”@”主机名”
修改用户密码
UPDATE USER SET PASSWORD =PASSWORD(“密码”) WHERE USER =”用户名

SET PASSWORD FORM “用户”@ “主机名” = PASSWORD(“新密码”)
忘记ROOT密码
1CMD中执行 net stop mysql停止 mysql 需要管理员权限
2使用无验证方式启动mysql mysqld –skip –grant-table
3新启动cmd窗口直接 mysq 回车修改密码
4 杀死 mysqld 进程

权限管理
查询权限
Show GRANTS FOR “用户名”@“主机名”
添加权限 ALL代表所有全权限 *.*代表所有表
GRANT 权限列表 ON 数据库名.表名 TO “用户名”@“主机名“
撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM “用户名”@“主机名”

Mysql在windows下不区分大小写