MySQL查询常用语句

Myth丶恋晨 2022-01-14 10:11 362阅读 0赞

转载 https://blog.csdn.net/qq\_37128049/article/details/87924063

# 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);