学生表:create table student(
id int primary key,
name varchar(10) not null,
age int check(age between 14 and 30),
sex char(4) not null
)
一、插入记录:
insert into 表名(列名1,列名2...) values(值1,值2...);
insert into student values(1,李明',23,'男');
二、删除记录:
delete from 表名 [where 条件];
delect from student where id=2;
三、修改记录
update 表名 set 列名1 = 值1,列名2= 值2,... where 条件;
update student set name='梅梅' where id=1;
四、SQL的查询语句:
1、select 列名 from 表名 [where 条件];
select name,sex from student where id=1;
1.1:查询表中的所有记录
select * from 表名; ---> * 是通配符,表示所有列
select * from student;
1.2:查询表中的部分列
select 列名1,列名2... from 表名;
select name ,age from student;
1.3:按条件查询-->带where子句
select 列名 from 表名 where 条件;
select name from student where sex='男';
1.4:在条件用到的运算符:
(1) =、!=、<>、<、<=、>、>=
select * from student where age>=5 and age<=18;
(2)BETWEEN…AND
select * from student where age between 5 and 18;
(3)or -->或,给定的条件中有一个满足即可
(4)and --> 与,给定的条件必须同时满足
(5)not --> 取反
select * from student where not sex='男';
(6)IS NULL -->查询列中的数据是否为NULL
select * from student where age is NULL;
(7)IN(set) --->选择在范围内的数据
select * from student where age in(22);
(8)not in(set)
select * from student where age not in(23,33);
二、在mysql中执行.sql脚本文件:
mysql> source 文件名