MySQL笔记

mysql

I.服务器启动

• cmd–>service.msc 打开服务器窗口
• net start mysql 启动mysql服务
• net stop mysql 关闭mysql服务

II.登陆

• mysql -uroot -proot 输入密码
• mysql -hip -uroot -p 输入连接目标的密码

III.退出

• exit

• quit
  IV.语句（以;结尾）

• show databases; 查看数据库

• – 后可注释

• #后可注释

DDL
i.操作数据库CRUD

• create创建
  create database 名称；
  create database if not exists 名称；
• retrieve查询
  show databases；
  show create databases 名称；
• update修改
  alter database 名称 character set 字符集名称; //修改字符集
• delete删除
  drop database 名称; //删除数据库
  drop database if exists 名称; //判断存在并删除数据库
• 使用数据库
  select database(); //查询当前正在使用的数据库名称
  use 数据库名称;

ii.操作表

• create创建
  create table 表名(
  列名1 数据类型1,
  列名2 数据类型2，
  .
  .
  列名n 数据结构n
  ); //注意最后一行无逗号
  name varchar(20)
• retrieve查询
  show tables;
  desc 表名; //查询表结构
• update修改
  alter table 表名 rename to 新表名;
  alter table 表名 character set 新字符集名称;
  alter table 表名 change 列名 新列名 新数据类型 ;
  alter table 表名 modify 列名 新数据类型;
  alter table 表名 add 列名 新数据类型;
  alter table 表名 drop 列名;
• delete删除
  drop table 表名;
  drop table if exists 表名;

#DML

• insert into 表名 (列名1,…,列名n) values (?,…,?);

• delete from 表名 where 条件;

• TRUNCATE TABLE 表名; //删除表，创建一张新表（同名）

• update 表名 set 列名 = 值,列名 = 值 … where 条件;

#DQL

• 查询
  select * from 表名;
  select distinct 列名 from 表名; //除重
  列名1 + ifnull(列名2,0) 新列名 //如果列名2为null，则用逗号后代替（0）
  //因为null做运算结果全为null
  select * from 表名 where 列名 is 值;
  select * from 表名 where 列名 in (值1,值2,值3);
  select * from 表名 where 条件 and 条件;
  select * from 表名 where 条件 or 条件;
  select * from 表名 where 列名 like ‘马%’; //第一个字为“马”
  select * from 表名 where 列名 like “_化%”; //第二个字为“化”
  select * from 表名 where 列名 like ‘%马%’; //含“马”

  • 排序查询(select * from 表名 +)
    order by 列名 ASC; //升序
    order by 列名 DESC; //降序
    order by 列名 ASC，列名 ASC，列名 ASC;
    //存在多个排序时，若第一条的值相同，才会使用第二条排序

  • 聚合函数
    select count(列名) from 表名; //不计算null
    select max(列名) from 表名;
    select min(列名) from 表名;
    select sum(列名) from 表名;
    select avg(列名) from 表名;

  • 分组查询
    select 列名1 , sum(列名2) from 表名 group by 列名1;
    select 列名1 , sum(列名2) from 表名 where 条件 group by 列名1 having 条件;
    //where和having区别：
    1.where在分组前，having在分组后（组内单独判断）
    2.having可用聚合函数，where不行

  • 分页查询(只可在mysql使用下列语句 )
    select * from 表名 limit 0,3; //从0号条开始，读取3条

  • 约束

    • 分类
      非空约束 not null
      唯一约束 unique
      主键约束 primary key （只可有一列）
      外键约束 foreign key （关联表）

    alter table 表名 modify 列名 新数据类型 not null; //列内值不可为null
    alter table 表名 modify 列名 新数据类型 unique; //列内值不可重复
    alter table 表名 modify 列名 新数据类型 primary key; //列内值非空且唯一

    create table 表名(
    …
    外键列,
    constraint 外键名 foreign key (外键列) references 外键表名称(此外键表主键列名称)
    )； ^
    alter table 表名 add constraint 外键名 foreign key (外键列) references
    外键表名称(主表列名称);

    //取消语句为 alter table 表名 modify 列名 新数据类型;
    #//主键类取消：alter table 表名 drop primary key;
    #//外键类取消：alter table 表名 drop foreign key 外键名;（取消关联）

    • 级联操作
      alter table 表名 add constraint 外键名 foreign key (外键列) references
      外键表名称(主表列名称) on update cascade on delete cascade;
      //级联删除将同时删除相关所有记录
    • 自动增长
      auto_increment //只与上一条记录有关（增加1）
      //取消：alter table 表名 modify 列名 新数据类型; ###不会取消主键

• 数据库设计
  多的一方添加外键；//类比分类
  中间表：两个字段分别作为外键指向两个目标表

• 多表查询

  • 内连接查询
    i.隐式内连接
    select * from 表名1 t1,表名2 t2 where 条件;
    ^ ^ 表名替代
    ii.显式内连接
    select * from 表名1 [inner] join 表名2 on 表名1.外键列 = 表名2.主表列
  • 外连接查询
    select * from 表名1 left [outer] join 表名2 on 条件;
    //查询结果为左表所有数据及其交集
  • 子查询
    例：select * from 表 where 表.列名 = (select max (列名) from 表);
    //括号内为子查询
    i.子查询结果单行单列
    < > = >= <=
    ii.子查询结果单列多行
    例：select * from 表1 where 列1 in (select id from 表2 where 条件)
    iii.子查询结果多行多列
    例：select * from 表1 t1,(子查询) t2 where 条件;
    //当作虚拟表

#事务
出现异常则回滚

• 开启事务
  start transaction;
• 执行没有问题，提交事务
  commit;
• 出问题了，回滚
  rollback;
• 查看事务提交方式
  select @@autocommit; //1为自动，0为手动
  set @@autocommit = 0; //改为手动
• 四大特征
  原子性：同时成功同时失败
  持久性：提交或回滚后，持久化保存数据
  隔离性：多个事务互相独立
  一致性：操作前后数据总量不变
• 隔离级别
  (read uncommitted/read committed/repeatable read/serializable )
  查询：select @@tx_isolation
  设置：select global transaction islation level 级别字符串;

#DCL
管理用户授权（DBA：数据库管理员）

• 管理用户

  • 查询用户
    select * from user;

  • 创建用户
    create user ‘用户名’@‘主机名’ identified by ‘密码’;

  • 删除用户
    drop user ‘用户名’@‘主机名’;

    ###特殊主机名：【localhost：本机】【%：任意】

  • 修改用户密码
    set password for ‘’@’’ = password(‘新密码’);
    update user set password = password(‘新密码’) where user = ‘用户名’;

  • 忘记root密码
    1.cmd --> net stop mysql
    2.mysqld --skip-grand- table
    3.打开新的cmd，输入mysql回车
    4.use mysql
    5.update user set password = password(‘新密码’) where user = ‘root’;
    6.关闭两个窗口
    7.打开任务管理器，手动结束mysqld.exe进程
    8.启动mysql服务
    9.使用新密码登陆

• 管理授权
  1.查询权限
  show grants for ‘用户名’@‘主机名’;
  2.授予权限
  grant 权限 on 数据库名.表名 to ‘用户名’@‘主机名’;
  3.撤销权限
  revoke 权限 on 数据库名.表名 from ‘用户名’@‘主机名’;