入门MySQL常用操作笔记

1.数据库操作：
  1.create
     *create database 数据库名
  2.retrieve
     * show databases；查看数据库
     * show create database 数据库名；查询已创建的数据库名
  3.update
     * alter database 数据库名 character set 字符集名称；
  4.delete
     * drop database 数据库名；
     * drop database 数据库名 if exists 数据库名；
  5.use
     *select database（）；查询正在使用的数据库名称
     * use 数据库名称；
2.操作表：
  1.create
    *create table 表名(列名1 数据类型，是否可以为空，列名2…）
  2.retrieve
    *show 表名；
    *desc 表名；
  3.update
    * alter table 表名 rename to 新的表名;     修改表名
    * alter table 表名 character set utf-8;    修改成 utf8的字符集
    * alter table 表名 add 列名 数据类型 after 列名x；    添加一列    add 列名   …，add 列名,add…添加多列,after 列名x  添加到列名x之后
    * alter table 表名 change 列名 修改后的列名 新数据类型,修改列名和后面字符类型等
    * alter table 表名 modify 列名 新数据类型        #用于修改列名，change范围币modify大还可以修改列名
    * alter table 表名 drop 列名1.drop列名2…；
  4.delete
    * drop table 表名；
    * drop table  if exists 表名;
3.增删改表的数据
  1.添加数据
 insert into 表名(列名1，列名2…列名n) values(值1，值2…值n）；
 insert into 表名 values(第一列值，第二列…第n列)如过不想给值就写null;
  2.删除数据
 delete from 表名 where id=1；  不加条件全表数据删除
 truncate table 表名；删除整个表，再创建一个一模一样的表
 
  3.修改数据
 update 表名 set 列名1=值1，列名2=值2[where…]
 update 表名 set 字段名=’’  清空整个字段名
 4.查询表数据
  1.语法：
 select 字段列表
 from 表名列表
 where 条件列表
 group by 分组字段
 having 分组之后的条件
 order by 排序
 limit 分页限定
  2.基础查询：
 (1).  select * from 表名 ；查询所有表数据
 (2).  select distinct 列名 from 表名;       去除重复的查询
 (3).  select 列名1，列名2，列名1+列名2 from 表名;求出1，2列和
        select 列名1，列名2，列名1+ ifnull(列名2，0）。。。;  如果有空值替换为0
        select 列名 1，列名2，列名1+ ifnull(列名2，0） as 总分 from 表名;
  3.条件查询:
 (1). where 子句后跟条件
 (2). 运算符
  >、<、 <=、 >= 、=、<?
  between …and
  in(集合)
  like
  is null
  and 或&&
  or 或||
  not 或!|
 (3)SELECT *from 表名 where 列名x  is NUll；  列名x为空的信息
     select *from 表名 where 列名x not is null；列名x不为空的信息
 (4)  模糊查询：like
   占位符：
    _单个任意字符
    %多个任意字符
  4.排序查询：
 SELECT *FROM　表名 ORDER　BY　列名；
 SELECT *FROM　表名 ORDER　BY　列名　ＤＥＳＣ；　降序排列
 SELECT *FROM　表名 ORDER　BY　列名１，列名２；如果列名１一样，按列名二排
  5.聚合函数： 注意：！！会排除null的值
 1.count
  SELECT COUNT(列名) FROM 表名;
  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 表名;
  6.分组查询:
 SELECT 分组的列名x,聚合函数(要计算的列名) FROM 表名 GROUP BY 分组的列名x;
 SELECT 分组的列名x,聚合函数(要计算的列名) FROM 表名 WHERE 条件 GROUP BY 分组的列名x;
 SELECT 分组的列名x,聚合函数(要计算的列名) FROM 表名 GROUP BY 分组的列名x HAVING 聚合函数条件;
 SELECT Cno，COUNT(Sno) AS number FROM Score WHERE Degree>80 GROUP BY Cno HAVING number;
 where和having区别：
   1.where 在分组前限定条件，如果不满足，则不参与分组
      having在分组后限定，如果不满足，则不会被查询出来
   2.where后不可以跟聚合函数，having可以跟聚合函数
  7.分页查询:
 1. limit
  SELECT *FROM Score LIMIT 0，3；第一页前三条  0表示从第几列开始不包括0，3表示显示的条数。
  约束：
 1.primary key
 2.not null
 3. unique
 4.foreign key
8.一对多情况
 在多的一方建立外键，指向一的一方
   多对多情况：
 建立中间表，至少两列数据，分别为两张表的主键。
   一对一情况：
 在任意一表上建立指向另一张表的主键。
 CREATE　TABLE　tab_favorite(
  rid INT, #路线
  DATE DATETIME，
  uid INT, #用户id
  PRIMARY KEY(rid,uid), #创建联合主键
  FOREIGN KEY(rid) PEFERENCES tab_route(rid),
  FOREIGN KEY(uid) PEFERENCES tab_user(uid)
  );
9.数据库范式 ：
 一般只需要保证3NF(数据库每一列不可分割，消除非主属性对主码的部分函数依赖，消除传递依赖）
10.数据库备份与还原:
  1.命令：
  备份：mysqldump -u用户名 -p密码  数据库的名称 > 保存的路径
  还原：先创建一个数据库并使用数据库，   source 保存的路径。
  2.图形化工具:
   点击备份就行
11.多表查询：
 内连接查询
  隐式内连接
  SELECT
    Score.Degree,Student.Sname
  FROM
    Student,Score
  WHERE
    Student.Sno=Score.Sno;
  显式内连接
  SELECT
    Score.Degree,Student.Sname
  FROM
    Student JOIN Score
  ON
    Student.Sno=Score.Sno;
 外连接查询
  左外连接：
   SELECT
     字段列表
   FROM
     表1 LEFT JOIN 表2
   ON 
     条件;
  右外连接：
   SELECT
     字段列表
   FROM
     表1 RIGHT JOIN 表2
   ON 
     条件;
 子查询:
  1.单行单列查询
  SELECT Sno FROM Score WHERE Degree=(SELECT MAX(Degree) FROM Score);
  2.  多行单列   IN(x,y)   相当于列名=x or 列名=y。
  3. 把第一个查询的作为表1，第二个查询的作文表2，结合查询
12.事务
 1.事务开启：
  START TRANSACTION；
 2.事务回滚：
  ROLLBACK
 3.事务提交：
  COMMIT
13.事务设置：
 自动和手动提交，MySQL默认自动提交
 SET @@autocommit = 0； 1为自动提交 0为手动提交，必须有commit提交语句
  Oracle默认手动提交，必须有commit
14.事务的四大特征：
 1.原子性：不可分割最小操作单位，要么同时成功，要么同时失败
 2.持久性：事务一旦提交，数据将会持久更新
 3.隔离性：多个事务之间相互独立（事务实际上有相互的影响)
 4.一致性：事务操作前后，数据总量不变。
15.用户管理系统：
 1.添加删除用户:
 在数据库自带的mysql数据库中user表是所有存在的用户
 user 表中 Host 为%号时为通配符，意思是可以匹配任意主机
 CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY’密码’;
 DROP USER ‘用户名’@‘主机名’ 
 2.修改用户密码：
 UPDATE USER SET PASSWORD =PASSWORD(新密码) WHERE USER=‘用户名’;
 SET PASSWORD FOR ‘用户名’@‘主机’ = PASSWORD(‘新密码’);
 3.忘记root密码？
  1.以管理员身份运行 cmd
   net stop mysql
  2. mysqld --skip-grant-tables
  3.打开一个新的cmd 直接输 mysql就能登陆了 然后更改密码
  4.任务管理器关掉cmd    
  5.以管理员方式打开CMD   net start mysql
 4.权限管理：
  1.查询权限
   SHOW GRANTS FOR ‘用户名’@‘主机名’;
  2.授予权限
   GRANT  SELECT,UPDATE,… ON 数据库名.表名 TO  ‘用户名’@‘主机名’;
   GRANT  ALL ON . TO  ‘用户名’@‘主机名’;      授予所有权限
  3.撤销权限
   revoke 权限列表 ON 数据库名.表名 FROM  ‘用户名’@‘主机名’;