MySQL基础（四）【命令大全】

Content

• 一、MySql数据库常用命令
  • （1）DDL：操作数据库、表
  • （2）DML： 增删改 【表】中数据
  • （3）DQL： 查询 【单表】中数据
  • （4）DCL：管理用户，授权（不常用）
  • （5）【多表查询】
• 二、设计数据库
  • （1）多表之间的关系
  • （2）数据库的设计范式
  • （3）事物

一、MySql数据库常用命令

1. DDL:操作数据库、表

（1）操作数据库：

1）* 创建数据库：
				* create database 数据库名称;
				* create database if not exists 数据库名称;
				* create database 数据库名称 character set 字符集名;
			    * 例子：
				* create database if not exists db4 character set gbk;

		2）* 查询所有数据库的名称:
				* show databases;
			    * 查询某个数据库的字符集:查询某个数据库的创建语句
				* show create database 数据库名称;
			 
       	3）* 修改数据库的字符集
				* alter database 数据库名称 character set 字符集名称;

		4）* 删除数据库
				* drop database 数据库名称;
				* drop database if exists 数据库名称;
				
		5） 使用数据库
			    * 查询当前正在使用的数据库名称
				* select database();
			    * 使用数据库
				* use 数据库名称;

（2）操作表

		1）创建
				* 创建表
				create table student(
					id int,
					name varchar(32),
					score double(4,1),
					birthday date,
					insert_time timestamp
				);
							* 复制表：
								* create table 表名 like 被复制的表名;	  
				
							* 注意：最后一列，不需要加逗号（,）
							* 数据库类型：
								1. int：整数类型
									* age int,
								2. double:小数类型
									* score double(5,2)
								3. date:日期，只包含年月日，yyyy-MM-dd
								4. datetime:日期，包含年月日时分秒	 yyyy-MM-dd HH:mm:ss
								5. timestamp:时间错类型	包含年月日时分秒	 yyyy-MM-dd HH:mm:ss	
									* 如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
			
								6. varchar：字符串
									* name varchar(20):姓名最大20个字符
									* zhangsan 8个字符  张三 2个字符
				

				
		2）查询
							* 查询某个数据库中所有的表名称
							* show tables;
							* 查询表结构
							* desc 表名;
							
		3） 修改
						1. 修改表名
							alter table 表名 rename to 新的表名;
						2. 修改表的字符集
							alter table 表名 character set 字符集名称;
						3. 添加一列
							alter table 表名 add 列名 数据类型;
						4. 修改列名称 类型
							alter table 表名 change 列名 新列别 新数据类型;
							alter table 表名 modify 列名 新数据类型;
						5. 删除列
							alter table 表名 drop 列名;
							
		4. D(Delete):删除
			* drop table 表名;
			* drop table  if exists 表名 ;
		
		
	注意：1.库和表中的修改和删除的关键字为：alter/drop
		 2.表中的数据修改和删除的关键字为：update/delete

注意：1.库和表中的修改和删除的关键字为：alter/drop
2.表中的数据修改和删除的关键字为：update/delete

2. DML： 增删改 【表】中数据

1）添加数据：
				* insert  into  表名  (列名1,列名2,...列名n)  values (值1,值2,...值n) ;
				* insert into 表名 values(值1,值2,...值n);
						注意：除了数字类型，其他类型需要使用引号(单双都可以)引起来
						
		2）删除数据：
				* delete from 表名 [where 条件]
				* 注意：
					1. 如果不加条件，则删除表中所有记录。
					2. 如果要删除所有记录
						1. delete from 表名; -- 不推荐使用。有多少条记录就会执行多少次删除操作
						2. TRUNCATE TABLE 表名; -- 推荐使用，效率更高 先删除表，然后再创建一张一样的表。
		3. 修改数据：
				* update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];
	
				* 注意：
					1. 如果不加任何条件，则会将表中所有记录全部修改。
					2. 库和表中的修改和删除的关键字为：alter/drop
				 	3. 表中的数据修改和删除的关键字为：update/delete

3. DQL： 查询 【单表】中数据

	1） 语法：
				select
					字段列表
				from
					表名列表
				where
					条件列表
				group by
					分组字段
				having
					分组之后的条件
				order by
					排序
				limit
					分页限定
				
		2）基础查询：select  *  from tableName;
			 		select  distinct *  from tableName;   //distinct-取出查询的重复数据
	
	    3）条件查询
	   		 where 子句后跟的条件
		    	* > 、< 、<= 、>= 、= 、<>
				* BETWEEN...AND  
				* IN( 集合) 
				* LIKE：模糊查询
					* 占位符：
						* _:单个任意字符
						* %：多个任意字符
				* IS NULL  
				* and  或 &&
				* or  或 || 
				* not  或 !
			
			例子：
			-- 查询年龄等于20岁
				SELECT * FROM student WHERE age = 20;
			-- 查询英语成绩为null
				SELECT * FROM student WHERE english IS NULL;
			-- 查询姓名中包含德的人
				SELECT * FROM student WHERE NAME LIKE '%德%';
			-- 查询姓名第二个字是化的人
				SELECT * FROM student WHERE NAME LIKE "_化%";

更多查询方式，见MySQL基础二中的【排序、聚合、分组、分页查询】

4. DCL：管理用户，授权（不常用）

1. 管理用户
			1. 添加用户：
				* 语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
			2. 删除用户：
				* 语法：DROP USER '用户名'@'主机名';
			3. 修改用户密码：
				
				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中忘记了root用户的密码？
					1. cmd -- > net stop mysql 停止mysql服务
						* 需要管理员运行该cmd

					2. 使用无验证方式启动mysql服务： mysqld --skip-grant-tables
					3. 打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功
					4. use mysql;
					5. update user set password = password('你的新密码') where user = 'root';
					6. 关闭两个窗口
					7. 打开任务管理器，手动结束mysqld.exe 的进程
					8. 启动mysql服务
					9. 使用新密码登录。
			4. 查询用户：
				-- 1. 切换到mysql数据库
					USE myql;
				-- 2. 查询user表
					SELECT * FROM USER;
				
				* 通配符： % 表示可以在任意主机使用用户登录数据库

		2. 权限管理：
			1. 查询权限：
				-- 查询权限
				SHOW GRANTS FOR '用户名'@'主机名';
				SHOW GRANTS FOR 'lisi'@'%';

			2. 授予权限：
				-- 授予权限
				grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
				-- 给张三用户授予所有权限，在任意数据库任意表上
				
				GRANT ALL ON *.* TO 'zhangsan'@'localhost';
			3. 撤销权限：
				-- 撤销权限：
				revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
				REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';

5. 【多表】查询

* 查询语法：
    select
        列名列表
    from
        表名列表
    where...

	1)	内连接查询
				隐式内连接：使用where条件消除无用数据
			            -- 查询所有员工信息和对应的部门信息
			            SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
			    显式内连接：
		        	    * 语法： select 字段列表 from 表名1 [inner] join 表名2 on 条件
		                * SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;   
		                * SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`; 
	2)	外连接查询
				 左外连接：
				            * 语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；
				            * 查询的是左表所有数据以及其交集部分。
				 右外连接：
				            * 语法：select 字段列表 from 表1 right [outer] join 表2 on 条件；
				            * 查询的是右表所有数据以及其交集部分。
			                SELECT  * FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;
	3)	子查询
				* 概念：查询中嵌套查询，称嵌套查询为子查询。
				* -- 一条sql就完成这个操作。子查询
         		   SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);

二、设计数据库

1. 多表之间的关系

a)	一对一 （人和身份证）
b)	一对多 （部门和员工）
c)	多对一 （学生和课程）

2. 数据库的设计范式

a)	第一范式（1NF）：每一列都是不可分割的原子数据项
b)	第二范式（2NF）：在1NF的基础上非码属性必须完全依赖于码
c)	第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖
）

3. 【事物】

1. 概念：
        *  如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

2. 操作：
        1. 开启事务： start transaction;
        2. 回滚：rollback;
        3. 提交：commit;

3. 事务的四大特征：
	    1. 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
	    2. 持久性：当事务提交或回滚后，数据库会持久化的保存数据。
	    3. 隔离性：多个事务之间，相互独立。
	    4. 一致性：事务操作前后，数据总量不变
    
4.事物的隔离级别：（了解即可）
 		1. read uncommitted：读未提交
            * 产生的问题：脏读、不可重复读、幻读
        2. read committed：读已提交 （Oracle）
            * 产生的问题：不可重复读、幻读
        3. repeatable read：可重复读 （MySQL默认）
            * 产生的问题：幻读
        4. serializable：串行化
            * 可以解决所有的问题