1.查询所有数据库 mysql>show databases 2.创建数据库 mysql>create database mybase ->default character set utf8 ---指定默认字符集创建mybase数据库 3.查看数据库默认字符集 mysql>show create database mybase 4.删除数据库 mysql>drop database mybase 5.修改数据库 mysql>alter database mybase default character set gbk --修改字符集 1.查看所有表 mysql>show tables 2.创建表 mysql>create table student( -->sid int, -->sname varchar(20), -->sage int -->); 3.查看表结构 mysql>desc student; 4.删除表 mysql>drop table student; 5.修改表 1)添加字段 mysql>alter table student add column sgender varchar(2); 2)删除字段 mysql>alter table student drop column sgender; 3)修改字段类型 mysql>alter table student modify column remark varchar(100); 4)修改字段名称 mysql>alter table student change column sgender gender varchar(2); 5)修改表名称 mysql>alter table student rename to teacher; 6.增删改数据 1.1增加数据 insert into student values(1,'张三',‘男’,20);——插入所有字段,一定要依次按顺序插入 insert into student (id,name)values(2,'李四');——插入部分字段 1.2修改数据 update student set gender=‘女’; ——修改所有数据(建议少用) update student set gender='男' where id =1; ——修改id为1 的学生为男 update student set gender=‘男’,age=30 where id=2;——多个字段用逗号分开 1.3删除数据 delete from student;——删除所有数据(谨慎!) delete from student where id=2; 7.查询数据 1.1查询所有列 select * from student; 1.2查询指定列 select id,name,gender from student; 1.3查询时添加常量列 select id,name,gender,'内容' AS ‘列名’ from student; 1.4查询时合并列 select id,name,(servlet+js)AS '总成绩' from student ——合并列只能合并数值类型的字段 1.5查询时去除重复记录(distinct) select distinct gender from student; select distinct (gender) from student; 1.6条件查询 select * from student where id=2 and name='李四';——交集 select * from student where id=2 or name = '张三';——并集 select * from student where servlet >70; select * from student where js>=75 and js<=90; select * from student where js between 75 and 90;——(包前包后) 1.7判空条件 select * from student where address is null; select * from student where address=''; select * from student where address is null or address=''; 1.8模糊条件查询 select * from student where name like '李%'; 1.9聚合查询 常用的聚合函数:sum() avg() max() min() count() select sum(servlet)as 'servlet的总成绩' from student select avg(servlet)as 'servlet的平均分' from student select max(servlet)as '最高分' from student select min(servlet)as '最低分' from student select count(*) from student ——统计多少学生 select count(id )from student; count ()函数统计数量不包含null的数据 使用count统计记录,要使用不包含null值的字段 2.0 分页查询 分页查询当前页的数据的sql: SELECT * FROM student LIMIT (当前页-1)*每页显示多少条,每页显示多少条; SELECT * FROM student LIMIT 0,2;——查询第1,2条记录(第1页的数据) SELECT * FROM student LIMIT 2,2;——查询第3,4条记录(第2页的数据) 2.1查询排序 -- asc: 顺序,正序。数值:递增,字母:自然顺序(a-z) -- desc: 倒序,反序。数值:递减,字母:自然反序(z-a) SELECT * FROM student ORDER BY id ASC; SELECT * FROM student ORDER BY id; -- 默认正序 SELECT * FROM student ORDER BY id DESC;-- 反序 2.2分组查询 -- 1) 把学生按照性别分组(GROUP BY gender) -- 2) 统计每组的人数(COUNT(*)) SELECT gender,COUNT(*) FROM student GROUP BY gender; 2.3分组查询后筛选 -- 1) 查询男女的人数 -- 2)筛选出人数大于2的记录(having) --- 注意: 分组之前条件使用where关键字,分组之前条件使用having关键字 SELECT gender,COUNT(*) FROM student WHERE GROUP BY gender HAVING COUNT(*)>2; |
|