mysql增删改查操作

mysql 增删改查操作

数据库mysql

默认端口：3306

常见约束： 1.

1. 主键 ：primary key  建议主键设为id

2.非空 ：not null   此字段不予许为空

3.唯一 ：unique    此字段的值不允许重复

4. 默认 ：default    当不填写字段对应的值时会使用默认值，如果填写已填    写为准

5. 外键 ：foreign key 对关系字段进行约束 当为关系字段填写值时, 会到关联的表中查询此值是否存在, 如果存在则填写成功, 如果不存在则填写失败并抛出异常.

6. unsigned  无符号的

常见数据类型：

                    整数：int ， bit

                    小数：decimal

                    字符串：varchar ，char

                    日期时间：data ， time， datatime

                    枚举类型：enum

查看mysql版本：

 select version（）；

显示时间：

  select now（）；

数据库操作：

查看所有数据库

show databases；

查看当前使用的数据库

   select database（）；

使用数据库

    use 数据库名；

创建数据库

          create database 数据库名charset=utf8；

删除数据库

          drop database 数据库名；

表操作：

查看数据库中所有表

          show tables；

查看表结构

          desc 表名；

创建表：

          提示：auto_increment 表示自动增长

          create table 表名（

		字段名称 数据类型  可选的约束条件，
			……，）

                 例: 创建班级表

                        create table classes（id int unsigned auto_increment primary key  not null,
                        name varchar(10)）

查看表的创建语句-详细过程

          show create table 表名；   

删除表：

          drop table 表名；

修改表：alter table

添加表字段

   alter table 表名 add 列名 类型；

修改字段

   重命名版

          alter table 表名 change 原名 新名 类型 约束；

   不重命名版

          alter table 表名 modify 字段名 类型 约束；

          删除字段：

                 alter table 表名 drop 字段名（也就是列名）；

表查询：

查询所有列

select * from 表名；

查询指定列

   select 列1，列2 from 表名；

   例：查询 班级的id 和名字

          select id，name from classes；

增加表内数据

          instert into 表名 values（…）

          例： 给学生表添加数据

                 insert into students values（1，‘张三’，‘蒙古’）

   部分列插入：

          insert into 表名（列1，…）value(值1，…)

          例：

                 Instert into students （name，gender）value （‘张三’，‘男’）

   多行插入

   	insert into 表名（列1，…）value(值1，…),(值1，…)…;

修改数据

   update 表名 set  列1=值1，列2=值2，…where 条件

删除

      物理删除：

          delete from 表名 where 条件

          例：

                 delete from students where id=5；

      逻辑删除：（本质就是修改操作）

          update students set isdelete=1 where id=5

进阶：

as 给表起别名

   例：查询学生表中的数据

          select s.id, s.name, s.age from students as s;

distinct消除重复行

   例：查询表中的性别会看到很多重复数据 为了的到不重复数据就用到了distinct

          select distinct gender from students

where条件查询

 select * from 表名 where 条件判断

where后面支持多种运算符，进行条件的处理

l 比较运算符： = ，>, >= , <, <=, !=或<>

例：查询id>3的学生名字 

	select name from students where id>3

l 逻辑运算符 and, or, not

例：查询年龄小于18的 或者 id小于18的学生

        select * from students where age<18 or id<18

l 模糊查询 like, % 表示任意多个字符， _表示一个字符

例：查询姓黄并且名为一个字的同学  或者名为静的同学

  select * from students where name like ‘黄_’ or ‘%静’

l 范围查询

in 在什么里面;    between…and… 在什么和什么之间

例：查询编号是1或3或8的学生

	select * from students where id in（1,3,8）

例：查询编号为3至8的学生

	select * from students where id between 3 and 8

l 空判断

null；is not null 不为空 ； is null 为空

例：查询填写了身高的男生

	select * from students where height is not null and gender=1

排序：

  order by 排序

  asc 从小到大排序 升序（默认就是asc）

  desc 从大到小排序 降序

  例：查询未删除男生信息，按年龄降序

select * from students where is_delete=0 and gender=1 order by age desc

分页：

select * from 表名 limit start，count 
（从start开始，获取count条数据。）

聚合函数：

 总数：count

       例;查询学生总数

            selete count(*) from students;

 最大值：max

       例：查询未删除的学生编号最大的

            select max(id) from students is_delete=0

 最小值：min

       例：查询未删除的学生编号最小的

            select min(id) from students is_delete=0

 求和：sum

       例：查询男生的总年龄
	   select sum(age) from students gender=1;

 平均值：avg  

       例：查询未删除女生的平均年龄

 	select avg(age) from students where is_delete= 0 and gender=2;
round(avg(),2)   表示取到的平均值avg（）保留2位小数

分组：group by

       例：以性别分组
            select gender from students group by gender
      
       group by +group_concat()
       例：查询以性别分组并查出所有每个性别包括的id
     	    select gender group_concat(id) from students group by gender;

group by +聚合函数

       例：以性别分组并查出每个性别的平均年龄

      select gender avg(age) from students group by gender;

group by+having（having和where类似 但having只能用于group by而where是用来过滤表数据的）

       例：以性别分组 查询每个性别人数大于2的 并显示数量

        select gender count(*) from students group by gender having count(*)>2;

group by+with rollup （在分组结果最后一行新增一行，完成汇总显示）

select gender,group_concat(age) from students group by gender with rollup;

+----------+---------------------------------------------+

| gender   | group_concat(age)                           |

+----------+---------------------------------------------+

|  男      | 29,59,36,27,12                              |

| 女       | 18,18,38,18,25,12,34                        |

| 中性     | 33                                          |

| 保密     | 28                                          |

| NULL     | 29,59,36,27,12,18,18,38,18,25,12,34,33,28   |

+--------+-----------------------------------------------+

连接

内连接 inner join…on

 	 例：使用内连接查询班级表与学生表

  		select * from students inner join classes on students.cls_id=classes.id

左连接 left join…on

左(外)连接查询：查询的结果为两个表匹配到的数据和左表特有的数据，对于右表中不存在的数据使用null填充

例：使用左连接查询班级表与学生表

  select * from students left join classes on students.cls_id =classes.id;

右连接 right join …on

右(外)连接查询：查询的结果为两个表匹配到的数据和右表特有的数据，对于左表中不存在的数据使用null填充

例：使用左连接查询班级表与学生表

select * from students right join classes on students.cls_id =classes.id;

自连接

与多表连接一样

例1：查询省的名称为“山西省”的所有城市

	Select city.* from areas as city inner join areas as province on 
  city.pid=province.aid where province.atitle=’山西省’

子查询：

在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句,外部那个select语句则称为主查询.

  例：查询班级大于平均年龄的学生

  select * from students where age>(select avg(age) from students)

  例：查询所有学生在班的所有班级名字

	select name from classes where id in (select cls_id from students)

  例：查找班级年龄最大和身高最高的学生

select * from students where (height,age) = (select max(height),max(age) from students);