mysql增删改查

-- 表结构如下
desc students;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)      | YES  |     | 0       |                |
| age    | int(10) unsigned | YES  |     | 0       |                |
| height | decimal(3,2)     | YES  |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+


================================= 增 insert ==================================

-- 全列插入   value()中的数据个数必须和 数据表中字段顺序个数严格对应
insert into students value(1,'lufei',18,1.75);
insert into students(id,name,age,height) value(2,'nami',18,1.70);
-- 部分插入   列与值 严格对应
insert into students(name,age) value('dama',30);
-- 多行同时插入   注意values
insert into students(name,age) values('hongfa',35),('kaiduo',33);
insert into students values(9,'mingren',13,1.68),(10,'zuozhu',12,1.72);

-- 结果如下
+----+---------+------+--------+
| id | name    | age  | height |
+----+---------+------+--------+
|  1 | lufei   |   18 |   1.75 |
|  2 | nami    |   18 |   1.70 |
|  3 | dama    |   30 |   NULL |
|  4 | hongfa  |   35 |   NULL |
|  5 | kaiduo  |   33 |   NULL |
|  9 | mingren |   13 |   1.68 |
| 10 | zuozhu  |   12 |   1.72 |
+----+---------+------+--------+


================================= 改 update ==================================

update 表名 set 列1=值1,列2=值2... where 条件;
-- 全部修改  将所有人年龄修改为18
update students set age=18;
-- 部分修改  将name是'lufei'的年龄修改为1
update students set age=1 where name="lufei";
-- 部分修改  将id是2的年龄修改为2 身高修改为1.65
update students set age=2,height=1.65 where id=2;
-- where条件的更多用法参考 查

-- 结果如下
+----+---------+------+--------+
| id | name    | age  | height |
+----+---------+------+--------+
|  1 | lufei   |    1 |   1.75 |
|  2 | nami    |    2 |   1.65 |
|  3 | dama    |   18 |   NULL |
|  4 | hongfa  |   18 |   NULL |
|  5 | kaiduo  |   18 |   NULL |
|  9 | mingren |   18 |   1.68 |
| 10 | zuozhu  |   18 |   1.72 |
+----+---------+------+--------+


================================= 删 delete ==================================

-- 数据的删除操作 删除操作行 （不能说只把第一行的'lufei'删掉 这可以用update来实现）
delete from 表名 where 条件

-- 表中数据全部删除
delete from students;
-- 表中数据部分删除
delete from students where name="zuozhu";


================================= 查 select ==================================


*** 深入理解查询过程(重点) ***
1.列是变量   2.变量可以计算(数字直接计算 字符串等需借助函数)   3.where是表达式(条件判断) 值为真假

普通查询++++++++++++++++++++++++++++++++++++++++++++++++++++++
    -- 查询所有数据
    select * from students;

    -- 查询指定字段  变量可以计算
    select name,age+1 from students;

    -- 查询某行某列  条件查询
    select name,age from students where id=2;

    -- 列是变量  变量可以计算
    select name,age+1 from students where id=2;

条件查询++++++++++++++++++++++++++++++++++++++++++++++++++++++
select ..... from 表名 where .....

-- 比较运算符    =  >  >=  !=
    --查询小于18岁的 姓名 年龄信息
    select name, age from students where age<18;
    --查询不是18岁的 姓名 年龄信息
    select name, age from students where age!=18;

-- 逻辑运算符    and or not
    -- 18到28之间所有学生信息  and
    select * from students where age>18 and age<28;
    -- 18岁以上或 身高超过1.70  or
    select * from students where age>18 or height>1.70;
    -- 不在 18岁以上或者身高超过1.70这个范围的  not
    select * from students where age>18 or height>1.70;
    -- 年龄不大于18  并且身高超过1.70
    select * from students where not age>18 and height>1.70;


范围查询++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- in(1,3,8)表示在一个非连续的范围内
    -- 查询年龄为1、18的数据
    -- select name, age from students where age=1 or age=2;
    select name, age from students where age in (1, 18);

-- not in() 不非连续的范围之内
    -- 年龄不是 1、18岁的数据
    select name, age from students where age not in (1, 18)

-- between ... and ... 表示在一个连续的范围内
    -- 查询 年龄在1到18之间的数据
     select name, age from students where age between 1 and 18;

-- not between ... and ... 表示在一个连续的范围内
    -- 查询 年龄不在18到34之间的信息
    select name, age from students where age not between 1 and 18;


模糊查询++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- like 模糊匹配   % 替换一个或多个   _ 替换一个
    -- 查询姓名中 以"n"开头的名字
    select name from students where name like "n%";
    -- 查询有4个字母的名字
    select name from students where name like "____";
    -- 查询有至少有5个字母的名字
    select name from students where name like "_____%";
-- rlike 正则  暂时用不到


空判断  ++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- 判空 is null
    -- 查询身高为空的信息
    select * from students where height is null;
    -- 错误写法 null比较特殊
    select * from students where height=null;
-- 判非空 is not null
    select * from students where height is not null;