数据库管理:
1 查询所有数据库:show databases;
2 指定默认字符集创建数据库: create database hotel default character set utf8;
3 查看数据库的默认字符集:show create database hotel ;
4 删除数据库:drop database hotel ;
5 修改数据库:alter database hotel default character set gbk;
表管理:
1 创建数据表: create table user(
id int,
name varchar(20)
)
2 查看表结构:desc user;
3 删除数据表:drop table user;
4 修改表:
添加字段:alter table user add column gender varchar(2);
删除字段:alter table user drop column gender;
修改字段类型:alter table user modify column gender varchar(20);
修改字段名称:alter table user change column gender ugender varchar(2);
修改表名称:alter table user rename to student;
5 增加 删除 修改数据
增加数据,插入所有字段 一定要按照顺序依次插入
INSERT INTO student VALUES(1,'张三','男',20);
插入部分字段:
insert into user(id,name)values(2,'黎明');
修改数据
update student set gender ='男' where id=1;
修改多个字段
update student set gender ='男',age=30 where id=1;
删除数据
带条件的删除
delete from student where id=2;
查询数据(重点):
查询所有列:select * from student;
查询指定列:select id,name from student;
查询时添加常量列:select name,age,'班级' AS '班' from student;
查询时合并列(只能合并数值类型的字段):select id,name,(age+number) AS '总年龄' from student;
查询时去除重复记录:SELECT DISTINCT gender FROM student;
条件查询(where):
需求: 查询id为2,且姓名为李四的学生
SELECT * FROM student WHERE id =3 AND name='李四';
需求: 查询id为2,或姓名为张三的学生
SELECT * FROM student WHERE id=2 OR NAME='张三';
需求: 查询servlet成绩大于70分的学生
SELECT * FROM student WHERE servlet>70;
需求: 查询jsp成绩大于等于75,且小于等于90分的学生
SELECT * FROM student WHERE jsp>=75 ANDjsp<=90;
需求: 查询地址为空的学生(包括null和空字符串)
SELECT * FROM student WHERE address IS NULL;
判断空字符串:SELECT * FROM student WHERE address='';
SELECT * FROM student WHERE address IS NULLOR address=''; -- (包括null和空字符串)
需求: 查询有地址的学生(不包括null和空字符串):
SELECT * FROM student WHERE address IS NOTNULL AND address<>'';
需求: 查询姓‘张’的学生:
SELECT * FROM student WHERE NAME LIKE '李%';
需求: 查询姓‘李’,且姓名只有两个字的学生
SELECT * FROM student WHERE NAME LIKE '李_';