MySQL查询常用语句

MySQL常用语句

一. 数据库操作的代码演示

• 创建数据库
  create database demo;
• 判断是否存在，不存在则创建新的数据库
  create database if not exists students;
• 删除数据库
  drop database demo;
• 修改数据库字符集
  alter database demo character set utf8;
• 显示所有的数据库
  show database;
• 查询某个数据库的字符集
  show create database demo;
• 使用该数据库（指定的）
  use demo;

二. 表操作的代码演示

• 创建一个新的表:

  create table student(
  id INT,
  NAME VARCHAR(40)
  );
  

• 查看该数据库下的所有表格
  show tables;
• 查改该表的结构
  desc students;
• 修改该表的名字
  alter table students remane father;
• 修改该表的字符集
  alter table father character set utf8;
• 添加一列
  alter table father add shengao int;
• 修改列名称，类型
  alter table father change shengao tizhong int;
• 删除列
  alter table father drop tizhong;
• 删除表名
  drop table students;
• 删除表名（如果存在则删除）
  drop table if exists students;

三. 数据的增删改查代码的演示

• 添加数据（默认给所有列添加值）

  insert into students values(
  1,'zhanglei');
  

• 给指定的列添加值
  insert into students(id)values(2);

• 删除所有数据（不推荐使用，只删除数据，不删除表）
  delete from students;

• 删除表内所有数据(推荐使用，只删除数据，不删除表)
  truncate table students;

• 删除指定表的指定内容
  delete from students where name='anyu'

• 修改所有的该列值
  update students set name='我';

• 修改指定行的列的值
  update students set name="哈哈哈" where id=1;

• 修改所有的行的多个列的值(如果需要指定行的多列的值后面加where条件即可)
  update student set name="我不是",id=2;

• 查询该表的所有数据
  select * from students;

• 删除id为2的该行数据（指定列的所有行数据）
  delete from students where id=2;

• 查询去重复后的表中的所有数据
  select distinct * from students;

• 查询指定行的指定列数据
  select id,name from students where id=2;

• 查询带简单运算后的表中数据
  select id+1,name from students;

• 查询带别名，将指定列的名字修改后查询带别名的数据
  select id 学号, name 名字 from students;

• 查询组合带别名的所有学生信息
  select (id+name) as 学生信息 from students

四. 条件查询

• 查询员工的所有信息
  select * from t_employee;

• 查询所有员工的姓名和职位
  select ename,job from t_employee;

• 查询所有员工的职位(去掉重复的)
  select distinct job from t_employee;

• 查询所有员工的姓名和总金额（薪资+佣金）
  select ename 姓名,sal,comm,sal+IFNULL(comm,0) 总金额 From t_employee;

• 查询部门编号等于20的所有员工信息
  select * from e_employee where deptno=20;

• 查询职位是clear并且薪资大于1000的员工信息
  select * from t_employee where job ='clear' and sal > 1000;

• 查询薪资在2000到3000之间的所有员工信息
  select * from t_employee where sal between 2000 and 3000;

• 查询佣金是null 的所有员工信息
  select * from t_employee where comm is null;

• 查询部门编号在10 和20 中的所有员工信息
  select * from t_employee where deptno in (10,20);

• 查询员工姓名包含M的所有员工信息
  select * from t_employee where ename like '%M%';

• 查询员工中佣金大于800 并且以佣金降序排序
  select * from t_employee where sal>800 order by sal asc;

• 求女性的平均年龄
  select AVG(age) from student3 where sex='女';

• 查询平均年龄，居住地，人数，按照居住地分组
  select avg(age),address,count(address)人数 from student3 group by address;

• 查询平均年龄，居住地，人数，按照居住地分组，并筛选出人数大于1人的地区；（使用聚合函数，条件查询，分组查询）
  select avg(age),address count(address)人数 from student3 group by address having 人数>1;

• 分页查询所有信息
  select * from student3 limit 3,6;

五. 约束

5.1 非空约束 【not null, 某一列的值不能为null】

• 非空约束：

  create table stu(
  id int,
  name varchar(20) not null     -- name为非空
  );
  

• 创建表完后，添加非空约束
  alter table stu modify name varchar(20) not null;

• 删除name的非空约束
  alter table stu modify name varchar(20);

5.2 唯一约束

• 在创建表的时候，添加一个唯一约束

  create table stu(
  id int;
  phone_number varchar(20) unique  --手机号
  );
  

• 删除唯一约束
  alter table stu drop index phone_number;

• 在表创建完后，添加唯一约束
  alter table stu modify phone_number varchar(20) unique;

5.3 主键约束

• 在创建表时，添加主键约束

  create table stu(
  id int primary key,     -- 给id添加主键约束
  name varchar(20)
  ); 
  
  

• 删除主键
  alter table stu drop primary key;\

• 创建完表后，添加主键
  alter table stu modify id int primary key;

• 在创建表时，添加主键约束，并且完成主键自增长

  create table stu(
  id int primary key auto_increment,    -- 给id 增加主键约束
  name varchar(20);
  );
  

• 删除自动增长
  alter table stu modify id int;

• 添加自动增长
  alter table stu modify id int auto_increment;

• 在创建表时，可以添加外键

  create table 表名(
  ...
  外键列
  constraint 外键名称 foreign key (外键列名称) references 主表名称（主表列名称）
  );
  

• 删除外键
  alter table 表名 drop foreing key 外键名称；

• 创建表之后，添加外键
  alter table 表名 add constraint 外键名称 foreing key (外键字段名称) references 主表名称(主表列名称)；

• 添加级联操作
  alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称) on update cascade on delete cascade;

• 级联更新
  on update cascade

• 级联删除
  on delete cascade

六. 查询练习

• 查询所有员工信息按照薪资降序排列
  select * from t_employee order by sal desc;

• 查询所有员工信息先按照薪资升序排列再按照雇佣时间降序排列
  select * from t_employee order by sal asc,hiredate desc;

• 统计所有员工的个数
  select count(*) from t_employee;

• 查询部门编号等于10 的所有员工的平均薪资
  select avg(sal) from t_employee where deptno=10;

• 查询所有员工中薪资最高的员工信息
  select deptno, max(sal) from t_employee group by deptno;

• 查询每个部门的员工个数，并按照人数排序排列
  select deptno,count(*)num from t_employee group by deptno order by num desc;

• 查询每个部门平均薪资在2000以上的部门编号和平均薪资
  select deptno,avg(sal) 平均 from t_employee group by deptno having 平均 >2000 ;

• 多表查询

  • 隐式内查询

    select 
    	emp.name, emp.`gender`,dept.`name`
    from 
    	dept,emp
    where 
    	dept.`id`=emp.`id`;
    

  • 显式内查询

    select 
    	t2.name, t2.gender, t1.`name`
    from 
    	dept t1
    join 
    	emp t2
    on 
    	t1.`id` =t2.id;
    

  • 左连接

    select * 
    from 
    	emp
    left join 
    	dept
    on
    	dept.`id` =emp.`id`;
    

  • 右连接

    select *
    from 
    	emp
    right join
    	dept
    on
    	dept.`id`=emp.`id`;
    

  • 子查询

    	-- 先找出工资最高的人
    	select 
    		max(emp.`salary`)
    	from
    		emp;
    

  • 再找出该员工

    select
    	 emp.`name`,emp.`salary`
    from
    	emp
    where
    	emp.`salary`=9000;
    

  • 使用子查询一次到位

    select 
    	emp.`name`,emp.`salary`
    from 
    	emp
    where 
    	emp.`salary`=(select max(emp.`salary`)from emp);