1.数据库和表的基本操作
1)创建数据库:
create database dbname;
2)在数据库中创建一个表:
create table worker(nid INT UNIQUE,name VARCHAR(20),address VARCHAR(200),salary float,level int);
3)在表中添加一条记录:
insert to worker value(100,"Tom","GuangZhou",8000.0,2);
4)在表中删除一条记录:
delete from worker where nid=100;
5)更新一条记录:
update worker set level=3,salary=10000.0 where nid=50;
6)删除表:
drop table worker;
7)删除数据库:
delete database dbname;
2.查看表结构
1)查看表结构命令:
desc table_name;
2)查看表的主键:
用desc命令后,看Key那一栏,如果Key是PRI,那么该列是主键的组成部分。
3.group by分组
1)
mysql 数据库有选课表learn(student_id int, course_id int),字段分别表示学号和课程编号,现在想获取每个学生所选课程的个数信息的语句:
select student_id, count(course_id) from learn group by student_id
先按学生分组,再用count统计个数。
2)
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负
如果要生成下列结果, 该如何写sql语句?
日期 胜 负
2005-05-09 2 2
2005-05-10 1 2
SELECT the_date,
(SELECT COUNT(*) FROM T AS T1 WHERE T1.the_date=T.the_date AND T1.the_type='胜') AS 胜,
(SELECT COUNT(*) FROM T AS T1 WHERE T1.the_date=T.the_date AND T1.the_type='负') AS 负
FROM T GROUP BY the_date
3)
表student
- +----+------+------+-------+---------+
- | id | name | sex | score | dept |
- +----+------+------+-------+---------+
- | 1 | a | 1 | 90 | dev |
- | 2 | b | 1 | 90 | dev |
- | 3 | b | 0 | 88 | design |
- | 4 | c | 0 | 60 | sales |
- | 5 | c | 0 | 89 | sales |
- | 6 | d | 1 | 100 | product |
- +----+------+------+-------+---------+
思路:
首先,按照部门进行分组,group by dept
然后,找到最大的分数,max(score)
然后,新增加一列max,max(score) as max
最后,按name排序输出, order by name
select *, max(score) as max group by dept order by name
如果要求分数最高的必须是女生(sex=1),可以按下面来写:
a.分组前先用where来筛选
select *, max(score) as max where sex='1' group by dept order by name
b.先进行分组,在进行聚合函数运算(max),运算完后用having来筛选组
select *, max(score) as max group by dept having sex='1' order by name
如果要选出不重复的部门,可以这样写:
select distinct dept from student;
如果还要列出其他信息,必须把distinct放在开头
select distinct dept, name from student;
但是这样还是会出现重复的部门,需要加上group by dept
select distinct dept, name from student group by dept;