数据库增删改查命令

一、创建库

mysql -u root -p        --连接数据库
show databases;         --显示所有数据库
create database school; --创建数据库school
use school;             --切换到school数据库
show tables;            --显示这个数据库下的所有表

二、创建表

create table student(
	ID char(10) primary key,
	SID char(15) not null;
	SNA varchar(8) not null;
	SEX bit,
	AGE tinyint,
	TEL varchar(11)
);

三、修改表

增加列
alter table student add SCR char(3);

查看表结构：
desc student;
describe student;
show columns from student;

修改表结构
alter table student modify SEX char(8);
alter table student modify ID int(11) auto_increment;
alter table student modify SID char(15) not null primary key;

四、插数据

INSERT INTO student(SID,SNA,SEX,AGE,TEL,SCR) VALUES(101,'赵一','1','21','13601825214','88');
INSERT INTO student(SID,SNA,SEX,AGE,TEL,SCR) VALUES(102,'钱二','0','20','13601821234','97');
INSERT INTO student(SID,SNA,SEX,AGE,TEL,SCR) VALUES(103,'张三','1','24','13601825678','64');
INSERT INTO student(SID,SNA,SEX,AGE,TEL,SCR) VALUES(104,'李四','0','18','13601824523','79');
INSERT INTO student(SID,SNA,SEX,AGE,TEL,SCR) VALUES(105,'王五','1','16','13601820965','93');
INSERT INTO student(SID,SNA,SEX,AGE,TEL,SCR) VALUES(106,'孙六','1','33','13601828888','67');

五、更新表

update student set SEX='女';                          --把所有人的性别改为女
update student set AGE=AGE-1;                        --所有人的年龄都减少一岁
update student set TEL='15537656525' where ID='1';   --把ID=1的这名学生的电话号码改为15537656525

六、删除表

delete from student where SNA='张三';     --删除张三这一行记录
delete from student;                      --删除所有记录
drop table student;                       --删除表

七、基础查询
1、查询所有

select * from student;

2、查询特定字段

select SID,SNA,SCR from student;

3、查询特定条件

select * from student where SNA='赵一';

select * from student where AGE > '20';

4、like 与 not like

select * from student where SNA like '张%';

select * from student where SNA not like '张%';

5、or 、and 、in

select * from student where SNA='张三' or SCR='88';

select * from student where SNA='张三' and AGE>'20';

select * from student where SNA in ('张三','李四');

6、between and

select * from student where SCR between 90 and 100;

select * from student where AGE not between 20 and 24;

7、NULL 与 NOT NULL

select * from student where REM is NULL;
select * from student where REM is NOT NULL;

8、order by
正序排列：

select SID,SNA,SEX,AGE,TEL from student order by AGE asc;

倒序排列：

select SID,SNA,SEX,AGE,TEL from student order by AGE desc;

9、distinct（去重查询）

select distinct SCR from student;

10、as（别名查询）

select SID as '学号',SNA as '姓名',SEX as '性别',AGE as '年龄',TEL as '电话',SCR as '分数',REM as '备注' from student;

11、limit（限制查询）

select * from student limit 3;