-- 表结构如下
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.70select* 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;
-- 表结构如下desc students;+--------+------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+------------------+------+-----+---------+----------------+| id | int(10) uns