mysql基本操作

mysql基本操作

查看与创建数据库：

• show databases; //查看数据库
• Set names gbk;//换编码
• create databases stu;// 创建数据库 use stu;
• Create table student(id int,name varchar(10));//创建表
• desc student; //查看表结构
• create database student character set utf8;//创建表时设置编码
• show create database student;//查看数据库编码
• show create table stu;//查看表结构编码
  插入和添加数据：
• insert into student values(2,’zly’);//插入数据
• alter table student add age int(10);//添加age字段
• insert into student set id = 5, name = ‘boya’;//指定某个字段添加数据
  查找数据：
• select *from student //查看表student的数据
• select sname form student where sno=”1”;//查找学号为1的同学的姓名
• select *from student order by id limit 0,2;//查询前两行数据信息
• select *from score where degree between 60 and 80;查询60~80之间的学生信息
• select * from score where degree in(85,86,88);//
  查询Score表中成绩为85，86或88的记录。
• select * from student where class=‘95031’ or
  ssex=‘女’;//查询Student表中“95031”班或性别为“女”的同学记录。
• select *from student order by sno asc;//按学号升序
• select *from student order by sno desc;//按学号降序
• select count(*) from student;//查询表student中的人数
  ----->分组查找
• select *from student group by ssex;//分组查询，只会显示各组别的第一条数据，意义不大
• select ssex,group_concat(sname) from student group by ssex;//利用group_concat()函数进行查询。

+------+--------------------+
| sex  | group_concat(name) |
+------+--------------------+
| 女   | 李四               |
| 男   | 张三,王五，李明     |
+------+--------------------+

• select sex,group_concat(age) from employee group by sex;

+------+-------------------+
| sex  | group_concat(age) |
+------+-------------------+
| 女   | 24                |
| 男   | 26,25,15          |
+------+-------------------+

• select sex,avg(age) from employee group by sex;
  分别统计性别为男/女的人年龄平均值

+------+----------+
| sex  | avg(age) |
+------+----------+
| 女   |  24.0000 |
| 男   |  22.0000 |
+------+----------+

• select sex,count(sex) from employee group by sex;

分别统计性别为男/女的人的个数

+------+------------+
| sex  | count(sex) |
+------+------------+
| 女   |          1 |
| 男   |          3 |
+------+------------+

• select sex,count(age) from employee group by sex with rollup;
  with rollup的作用是：在最后新增一行，来记录当前列里所有记录的总和

+------+------------+
| sex  | count(age) |
+------+------------+
| 女   |          1 |
| 男   |          3 |
| NULL |          4 |
+------+------------+

• select sex,count(sex) from employee group by sex having count(sex)>2;
  (1) having 条件表达式：用来分组查询后指定一些条件来输出查询结果
  (2) having作用和where一样，但having只能用于group by

+------+------------+
| sex  | count(sex) |
+------+------------+
| 男   |          3 |
+------+------------+

修改数据：

• rename table stu to student;//修改表名
• alter table student change name sname varchar(20);//修改字段名
• alter table student modify sname char(30);//修改字段数据类型
• update student set grade = 100 where id < 4 ;//修改数据
• update student set grade = 80 ;//修改全部数据
  删除数据：
• drop database stu;//删除数据库
• drop table student;//删除表
• alter table student drop age;//删除字段
• select * from student where id = 11 ;//删除id字段值为11的记录
• delete from student ;//删除表中所有数据
  等值连接：
• select student.sno,sname from score,student where score.sno=student.sno;//等值连接
• select student.sno,sname,degree from//用join实现等值连接
  score join student on student.sno=score.sno;
• select student.sno,sname,degree,cname from score,student,course
  where score.sno=student.sno and score.cno= course.cno;//多表等值连接