MYSQL基本命令(３)

增删改查(crud)

(一)增

<1>插入完整行

insert into values(001,'姓名',18,'男');

<2>根据列插入数据

insert into student(id,name,age,gender) 
values(001,'姓名',18,'男');

<3>多行插入数据

insert into student(id,name,age,gender) 
values(001,'姓名',18,'男');
values(002,'姓名',19,'男');
values(002,'姓名',18,'女');

<4>讲一个表里的子列插入到另一个表里

insert into newStudent(列名,列名)　select 列名 from student;

(二)删

<１>删除一行

delete from student where userid=10010;

<2>删除表中所有行

delete from student;

(三)改

<1>把字段age的值为20

updata student SET age='20' where id=10010;

<2>改多个列

updata studata age='20' name='姓名' where id=10010;

(四)查

<1>查询单个列

select name from student;

<2>查询多个列

select age,name,phonenum from student;

<3>查询所有列

select * from student;

<4>查询不同的列

SELECT DISTINCT name FROM student;

<5>查询完全限定的表名

SELECT student.name,student.age FROM student;

数据的查询WHERE（过滤）

<1> = 等于

select name from customers where name='李逵';

<2> <> 不等于

select name from customers where name<>'李逵';

<3> != 不等于

select name from customers where name!='李逵';

<4> < 小于 (一般情况下后面会跟数字)

select name from customers where name<'李逵';

select * from customers where userid<10014;

<5> <= 小于等于

select * from customers where userid<=10014;

<6> ＞ 大于

select * from customers where userid>10014;

<7> ＞= 大于等于

select * from customers where userid>=10014;

<8>空值检查(IS NULL)

 SELECT name FROM students WHERE age IS NULL;

<9>AND操作符

  SELECT name,age FROM student WHERE name='姓名'　AND age =18;

<10>OR 操作符

SELECT name FROM studentb WHERE name='姓名' OR age=18;

<11>IN操作符

SELECT name FROM studentb WHERE id IN(001,003);

<12>NOT 操作符

SELECT name FROM studentb WHERE id NOT IN(001,003);