MySQL基础学习

• 查看数据库

  show databases;
  

• 切换数据库

  use master;
  

• 查看数据库中已建的表

  show tables;
  

• 查看表的相关信息

  describe pms_project;
  

• 创建表

  create table pms_project
      （
          id int unsigned not null auto_increment primary key,
          name char(8) not null,
          sex char(4) not null,
          age tinyint unsigned not null,
          tel char(13) null default "-"
      );
  
  create table pms_type 
      (
          id int unsigned not null auto_increment primary key,
          name char(8) not null,
          date datetime not null,
          singin tinyint(4) not null default '0' comment '登录次数'
      );
  

• 查询表信息

  select * from pms_project;
  

• 向表中插入数据

  # pms_project表数据
  insert into pms_project (id,name,sex,age,tel) values (06,"小科","男",26,"15000158000");
  
  # pms_type表数据
  INSERT INTO pms_type VALUES ('1', '小明', '2016-04-22 15:25:33', '1'), ('2', '小王', '2016-04-20 15:25:47', '3'), ('3', '小丽', '2016-04-19 15:26:02', '2'), ('4', '小王', '2016-04-07 15:26:14', '4'), ('5', '小明', '2016-04-11 15:26:40', '4'), ('6', '小明', '2016-04-04 15:26:54', '2');
  

• 更新表数据

  update pms_project set tel="16000168000" where name="小科";
  

• 像表中增加一列数据

  alter table pms_project add column nativeplace char(8) not null;
  

• 将nativeplace移动到sex之后

  alter table pms_project modify nativeplace char(8) after sex;
  

• 排序

  # 升序
  select * from pms_project order by age ASC;
  
  # 降序
  select * from pms_project order by age DESC;
  

• 分组

  # 将数据表按名字分组
  GROUP BY 语法
  select name, count(*) from pms_type group by name;
  
  
  # 使用 WITH ROLLUP
  # 数据表按名字进行分组，再统计每个人登录的次数
  select name, sum(singin) as singin_count from pms_type group by name with rollup;
  

• 按指定条件查询

  # where 关键词
  # 用法：select 列名称 from 表名称 where 条件;
  select * from pms_project where sex="女";
  # where 加其他运算符
  select * from pms_project where age > 23;          #查询年龄在23岁以上的所有人信息
  select * from pms_project where name like "%科%";  #查询名字中带有 "王" 字的所有人信息
  select * from pms_project where id<5 and age>20    #查询id小于5且年龄大于20的所有人信息
  

• MySQL连接

  # INNER JOIN 内连接
  select  a.id, a.name, b.date from pms_project a INNER JOIN pms_type b ON a.name = b.name;
  # WHERE 语句
  select  a.id, a.name, b.date from pms_project a INNER JOIN pms_type b where a.name = b.name;
  
  # LEFT JOIN 左连接
  select  a.id, a.name, b.date from pms_project a LEFT JOIN pms_type b on a.name = b.name;
  
  # RIGHT JOIN 右连接
  select  a.id, a.name, b.date from pms_project a RIGHT JOIN pms_type b on a.name = b.name;