MySQL基本命令总结

MySQL基础命令总结

1.命令提示符下MySQL的启动和关闭

net start mysql
net stop mysql

2.登录和退出

mysql -u root -p
exit

3.注释

-- （注意这里有空格） + 内容
# + 内容 （MySQL特有）

4.DDL（Data Defination Language）数据定义语言

1.操作数据库

（1）Create 创建
	普通创建数据库：create database + 数据库名称；
	先查后创建：create database if not exists + 数据库名称;
	创建数据库并指定字符集：
	create database + 数据库名称 character set + 字符集
	
（2）Retrieve查询
	查询所有数据库名称：show databases；
	查询某个数据库的字符集：show create database + 数据库名称;
	
（3）Update修改
	修改数据库的字符集：alter database + 数据库名称 character set 字符名称；
	
（4）Drop删除
	删除数据库：drop database + 数据库名称；
	判断数据库是否存在再删除：drop database if exists + 数据库名称；
	
（5）使用数据库
	查询当前正在使用的数据库名称：select database();
	使用数据库：use + 数据库名称；

2.操作表

（1）Create 创建
		create table + 表名( 
		列名1  数据类型1，
		列名2  数据类型2，
		...
		列名n  数据类型 n，
		)；
		数据类型：
		data：日期 yyyy-mm-dd
		datatime：yyyy-mm--dd HH:mm:ss
		timestamp：时间错类型     自动赋值系统时间
		varchar(n)
		double(5,2)五位两个小数位

	复制表：create talbe + 表名 like + 要复制的表名

（2）Retrieve查询
	查询某个数据库中所有表的名称：show tables;
	查询表的结构：desc + 表名；

（3）Update修改
	修改表名：alter table + 表名 rename to 新的表名;
	修改表的字符集：alter table + 表名 character set + 字符集名称;
	添加一列：alter table + 表名 add + 列名 数据类型；
	修改列名称：alter table + 表名 change 列名 新列名 新数据类型；
			alter table + 表名 modify 列名 新数据类型；
	删除列：alter table + 表名 drop + 列名;

（4）Drop删除
	删除表： drop table + 表名；
	查看表存不存在删除：drop table if exists + 表名;

5.DML增删改表中的数据

1.添加数据
insert into 表名(列名1，列名2，...列名n)  values(值1，值2，...值n);
2.删除数据
	delete from 表名 where + 条件；不加条件就会删除整个表 效率很低
	truncate table 表名；先删除表然后创建一个一模一样的空表

3.修改数据
	update 表名 set 列名 = 值1，列名2 = 值2，...列名n = 值n where + 条件；
4.查询表中的数据
	select * from + 表名;

6.DQL查询表中的记录

1.普通字段查找
	select 
		列名1,
		列名2
	from
		表名；
2.去除重复的结果集
	select distinct 列名 from 表名;
3.计算两列之和
		select 列名1，列名2，列名1+列名2 from 表名;
	如果列中的数据有NULL则如下写：将null值转换为0
		select 列名1，列名2，列名1+ifnull(列名2,0)  from 表名;
	给上述列名起一个别名：其他的列也可以起别名 且as可以省略
		select 列名1，列名2，列名1+ifnull(列名2,0) as 新的列名 from 表名;
4.条件查询
	select 
		列名1,
		列名2
	from
		表名
	where
		条件； 
		条件中判断一个值是不是null需要用is语句 不能用=，不是就用is not
5. 模糊查询
	占位符：_ ： 单个任意字符
			%：多个任意字符
	select 列名1,列名2	from 表名 where 列名 like ‘条件’；
6.排序查询
	select * from 表名 order by 列名 + 排序方式；不加排序方式就默认升序
	ASC升序    desc降序
	可以后面跟第二种排序方式，但是线排序前面一个，条件一样跟上第二个排序
7.聚合函数（所有的聚合函数的计算会排除null值）
	将一列数据作为一个整体  进行纵向的计算
	1.count计算个数
		select count(列名) from 表名;
		为了解决null的问题
		select count(ifnull(列名，0)) from 表名;
	2.max计算最大值
		select max(列名) from 表名;
	3.min计算最小值
		select min(列名) from 表名;
	4.sum计算和
		select sum(列名) from 表名;
	5.avg计算平均值
		select avg(列名) from 表名;
8.分组查询
	select + 分组后的字段或者聚合函数  from 表名 group by 列名;
	分组之前的条件添加
	select + 分组后的字段或者聚合函数  from 表名 where + 条件 group by 列名；
	分组之后的条件添加
	select + 分组后的字段或者聚合函数  from 表名 where + 条件 group by 列名 having + 
	条件；
	where后不能跟聚合函数的判断，having之后可以跟聚合函数的判断
9.分页查询 （各个数据的操作不相同）
	 select * from 表名 limit 开始位-1，一页多少数;

7.约束

1.主键约束 primary key 非空且唯一  一张表只能有一个字段为主键
	创建表时，添加主键约束：
	id int primary key
	删除主键：
	alter table 表名 drop primary key
	创建完表后添加主键
	alter table 表名 modify id int primary key;
	
	自动增长：  自动增长的时候数据只和上一级有关
	创建表时天剑自动增长：
	id int primary key auto_increment
	删除自动增长：
	alter table 表名 modify id int;
	创建完成表后添加自动增长：
	alter table 表名 modify id int auto_increment;

2.非空约束not null
	创建表时给表中的列属性添加属性 如：
	name varchar(20) not null
	删除 非空约束：
	alter table  表名 modify name varchar(20);
	创建表结束后添加约束：
	alter table  表名 modify name varchar(20) not null;
	
3.唯一约束unique   唯一值可以是null
	name varchar(20) unique
	删除唯一约束：
	alter table  表名 drop index 列名;
	创建表后添加唯一约束：
	alter table  表名 modify name varchar(20) unique;
	
4.外键约束foreign key    注意添加外键约束的过程中表的创建顺序要注意
	创建表时添加外键：外键列关联另一个表的主键列
	外键列  int,
	constraint 自己定义的外键名称 foreign key (外键列) references 表2名称(表2主键列);
	删除外键：
	alter table 表名 drop foreign key 外键名;
	创建表之后添加外键：
	alter table 表名 add constraint 自己定义的外键名称 foreign key (外键列) references 表2名称(表2主键列);

	级联：
	添加级联的操作：
	 alter table 表名 add constraint 自己定义的外键名称 foreign key (外键列) references 表2名称(表2主键列) on update cascade;  -- 级联更新
	 alter table 表名 add constraint 自己定义的外键名称 foreign key (外键列) references 表2名称(表2主键列) on delete cascade；--级联删除   弊端有些大

8.数据库设计

多对一：
在多的一方建立外键，指向一的一方的主键。
多对多：
需要建立中间表，中间表最少包含两个字段，这两个字段作为第三章表的外键指向两张表的主键。
一对一：一般不会用  直接用一张表就解决了
一对一关系的实现，可以在任意一方添加唯一外键指向另一方的主键。

9.范式

第一范式 1NF ：每一列都是不可分割的原子数据项
第二范式 2NF： 在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
	函数依赖：A--->B如果通过A属性（属性组）的值，可以确定唯一B属性的值。则称B依赖于A
		
第三范式 3NF： 在2NF基础上，任何非指数型不依赖于其他非主属性（在2NF基础上消除传递依赖）

10.数据库的备份和还原

备份：mysqldump -u 用户名 -p 密码 数据库名称 > 保存的路径
还原：
	1.登录数据库2.创建数据库3.使用数据库4.source 文件路径

11.多表查询

1.隐式内连接
select 
	* 
from 
	表1，
	表2 
where 
	表1.'外键名' =  表2.‘主键名’;
2.显示内连接：
select * from 表1 inner join 表2 on  表1.'外键名' =  表2.‘主键名’;-- inner可以省略
3.左外连接查询：
查询的是左表所有数据以及其交集部分。
select 字段列表 from 表1 left outer join 表2 on 条件;

3.右外连接查询：
查询的是右表所有数据以及其交集部分。
select 字段列表 from 表1  right outer join 表2 on 条件;

4.子查询
子查询地结果是单行单列的
	select之中嵌套另一个select
	select * from 表12 where 表1.'属性' = (select max(列名) from 表1);
	上面从等号开始和之后的条件视情况更改
多行单列
	子查询作为条件，使用运算符in来判断 
	select id from dept where name = '财务部' or name = '市场部';
	select * from emp where dept_id in (select id from dept where name = '财务部' or name = '市场部');
多行多列
	子查询可以作为一个虚拟表
	查询员工入职日期是2011-11-11之后的员工信息和部门信息
	子查询
	select * from dept t1,(select * from emp where emp.'join_date' > '2011-11-11') t2 where t1.id = t2.dept_id;

12.事务

1.事务的基本概念
	1.概念 如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
	2.操作
		1.开启事务：start transaction；手动提交不会立即更改数据 必须要提交事务后才会生效
		2.回滚：rollback；回到事务开启时的状态
		3.提交：commit；
		4.MySQL数据库中事务默认自动提交 Oracle默认是手动提交
			修改事务的默认提交方式
			查看事务的默认提交方式：select @@autocommit;
			修改默认提交方式：set @@autocommit = 0；
	3.事务的四大特征
		1.原子性：是不可分割的最小操作单位，要么同时成功要么同时失败
		2.持久性：当事务提交或者回滚后，数据库会持久化地保存数据
		3.隔离性：多个事物之间相互独立
		4.一致性：事务操作前后数据的总量不变
	4.事务的隔离级别（了解）
		多个事务之间是隔离的相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
		存在的问题：
			1.脏读：一个事务，读取到另一个事务中没有提交的数据
			2.不可重复度（虚读）：在同一个事务中，两次读到的数据不一样
			3.幻读：一个事务操作（DML）数据表中所有记录，另一个数据添加了一条数据，则第一个事务查询不到自己的修改
		隔离级别：（级别从小到大但是效率越来越低）
			1.read uncommitted：读未提交
				产生的问题：脏读、不可重复读、幻读
			2.read committed：读已提交（Oracle默认）
				产生的问题：不可重复度、幻读
			3.repeatable read：可重复读（MySQL默认）
				产生的问题：幻读
			4.serializable：串行化
				可以解决所有问题

		数据库查询隔离级别：
			select @@tx_isolation;
		数据库设置隔离级别：
			set global transaction isolation level 级别字符串;

13.DCL管理用户授权

1.管理用户
	1.查询用户
		找到存放用户的表
		select * from 表;
	2.创建用户
		create user ‘用户名’@‘主机名’ identified by '密码';
	3.删除用户
		drop user  ‘用户名’@‘主机名’；
	4.修改密码
	update user set password = password('新密码') where user = '用户名';

	set password for '用户名'@‘主机名’ = password('新密码');
	mysql中忘记root用户的密码
		1.管理员身份启动cmd 输入 net stop mysql
		2.使用无验证方式启动mysql服务：mysqld --skip-grant-tables
			此后打开一个新的cmd窗口然后输入mysql可以无验证登录数据库
			update user set password = password('新密码') where user = '用户名';
			然后任务管理器手动关闭mysqld服务
			然后在新的cmd窗口中登录mysql数据库
			
2.权限管理
	1.查询权限：
		show grants for '用户名'@'主机名';
	2.授予权限：
		grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
	3.撤销权限
		revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';