MySql 基础知识

1. 启用 mysql 的远程访问功能
   默认情况下 mysql 只支持本机访问，不支持远程访问。
   # 登录 mysql
   mysql -hlocalhost -uroot -p123456a?
   
   # 使用 mysql 库
   use mysql
   
   # 修改 root 用户密码
   update mysql.user set  password\=password('newpwd') where user='root';
   
   # 跳过密码校验直接登录
   mysqld --skip-grant-tables
   
   # 查询 user 表
   select user,host from user;
	 +------+-----------+
	 | user | host      |
	 +------+-----------+
	 | root | localhost |
	 +------+-----------+
     
   # 更新 user 表
   update user set host='%' where user='root';
   
   # 应用更改
   flush privileges;
   
   # 退出
   quit
   
   # 重新登录
   mysql -h10.166.42.238 -uroot -p123456a?

2. 查看 mysql 帮助 
   # 查看帮助命令
   help 
   
   # 查看数据类型
   help data types;
   
   # 查看 show 命令
   help show; 

3. show 命令
   # 查看字符集
   show character set;
   
   # 查看现有数据库
   show databases;   
   select schema_name from information_schema.schemata;
   
   # 查看表
   show tables;
   select table_name from information_schema.tables where table_schema='mysql';
   
4. 创建数据库     
   # 创建数据库
   create database if not exists test character set = utf8;
   
   # 使用数据库
   use test;
   
   # 查看创建数据库的 sql
   show create database test;
   
   # 删除数据库
   drop database if exists test;

5. 创建表
   # 创建表
	CREATE TABLE EMP(
		EMPNO    INT(4),         -- 员工编号
		ENAME    VARCHAR(10),    -- 姓名
		JOB      VARCHAR(9),     -- 工作
		MGR      INT(4),         -- 上级编号
		HIREDATE DATE,           -- 入职日期
		SAL      FLOAT(7,2),     -- 工资
		COMM     FLOAT(7,2),     -- 奖金
		DEPTNO   INT(2)          -- 部门编号
	);
     
    # 导入数据
    load data local infile 'd:/temp/emp.txt' into table emp lines terminated by '\r\n';
    
    # d:/temp/emp.txt，其中各列数据以 Tab 健分隔，NULL 值用 \N 代替
	7369	SMITH	CLERK	7902	1980-12-17	800	\N	20
	7499	ALLEN	SALESMAN	7698	1981-2-20	1600	300	30
	7521	WARD	SALESMAN	7698	1981-2-22	1250	500	30
	7566	JONES	MANAGER	7839	1981-4-2	2975	\N	20
	7654	MARTIN	SALESMAN	7698	1981-9-28	1250	1400	30
	7698	BLAKE	MANAGER	7839	1981-5-1	2850	\N	30
	7782	CLARK	MANAGER	7839	1981-6-9	2450	\N	10
	7788	SCOTT	ANALYST	7566	1987-7-13	3000	\N	20
	7839	KING	PRESIDENT	\N	1981-11-17	5000	\N	10
	7844	TURNER	SALESMAN	7698	1981-9-8	1500	0	30
	7876	ADAMS	CLERK	7788	1987-7-13	1100	\N	20
	7900	JAMES	CLERK	7698	1981-12-3	950	\N	30
	7902	FORD	ANALYST	7566	1981-12-3	3000	\N	20
	7934	MILLER	CLERK	7782	1982-1-23	1300	\N	10
    
    # 创建一个和表 emp 具有相同结构的表 emp_new1(不需要原表的数据)
    create table emp_new1 like emp;
    
    # 将表 emp 中的数据全部插入到表 emp_new1 中
    insert into emp_new1 select * from emp;
    
    # 将 emp 中 EMPNO 为 7900 的行插入到表 emp_new1 中，同时将 EMPNO 改为 8000
    insert into emp_new1
      select '8000',ename,job,mgr,hiredate,sal,comm,deptno from emp where empno=7900;
      
    # 创建一个和表 emp 具有相同结构的表 emp_new2, 同时需要携带原表的数据
    create table emp_new2 select * from emp;
    
    # 查看建表 sql
    show create table emp_new2;

    # 重命名表
    rename table emp_new2 to emp_new1;

6. 事物
    # 数据库事物的四个基本性质ACID
    # 原子性 Atomicity
    事物中包含的操作要么都做，要么都不做
    # 一致性 Consistency
    事物开始以前数据库处于一致性的状态，事物结束后数据库也必须处于一致性状态，例如银行转账。
    # 隔离性 Isolation
    系统必须保证事物不受其他并发事物的影响
    # 持久性 Durability
    一旦事物成功完成，它对数据库的改变必须是永久的。

    # 设置不提交事物
    set autocommit=0;

    # 删除一条记录
    delete from emp where empno=7900;

    # 打开一个新的窗口
    mysql -hlocalhost -uroot -p
    use test
    select * from emp;                   -- 此时发现数据并没有删除

    # 在原窗口中提交事物
    commit;

    # 在原窗口中创建表 dept
	CREATE TABLE DEPT (
		DEPTNO INT,
		DNAME VARCHAR(14) ,
		LOC VARCHAR(13) 
	) ENGINE = MyISAM;

    # 在原窗口中插入数据
    INSERT INTO DEPT VALUES(10,'ACCOUNTING','NEW YORK');
    INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS'), (30,'SALES','CHICAGO');

    # 在新窗口中查询 dept
    select * from dept;                  -- 此时数据已经有了，因为 dept 不是事务表
    
    # 将表 dept 改为事物表
    alter table dept engine = InnoDB;

7. 查询
    # 添加一个自增列
    alter table emp_new1 add id int not null auto_increment, add primary key(id);

    # 集合函数
    select max(sal) from emp;

    # 集合与分组
    select job,max(sal) from emp;              -- 错误的 sql, 没有分组
    select job,max(sal) from emp group by job; -- 正确的 sql

    # 分页 select * from tableName limit offset,rows;
    # 选择前10行
    select * from emp limit 0,10;

    # 添加行号
    select (@row:=@row+1) as row, emp.* from (select @row:=0) a, emp order by empno desc;

8. 性能分析
    # 性能分析
    # type [all:全表扫描, ref:多行匹配, const:通过索引一个找到]
    # key [primary:使用主键]
    explain select empno,ename from emp_new1 where empno=7782;

9. 备份和恢复
    # 备份表数据
    select * into outfile 'd:/temp/test.txt' from emp;
    # 恢复表数据
    load data infile 'd:/temp/test.txt' replace into table emp;

    # 备份表结构及数据
    mysqldump -hlocalhost -uroot -p123456a? test emp > d:/temp/backup_emp.sql
    # 恢复表结构及数据
    mysql -hlocalhost -uroot -p123456a? test < d:/temp/backup_emp.sql
    
    # 备份数据库
    mysqldump -hlocalhost -uroot -p123456a? test > d:/temp/backup_emp.sql
    # 恢复数据库
    mysql -hlocalhost -uroot -p123456a? test < d:/temp/backup_emp.sql
    
10. 修改 cmd 中乱码
    set names gbk;