MYSQL基础知识小结
一、数据库特点:结构化,无有害,无重复;
二、数据库优点:按一定的数据模型组织,描述和储存;可为各种用户共享,冗余度小,节省储存空间易扩展,编写有关数据应用程序。
三、常用Dos操作指令:
安装数据库:mysqld -install,开启/关闭数据库:start mysql/net stop,监听端口信息:netstat -a,登陆数据库:mysql -uroot -p,显示默认数据库:use dbname,显示所有数据库:show databases,显示默认数据库中的所有表:show tables,放弃正在输入的指令:\c,显示命令清单:\h,退出mysql程序:\q,查看mysql服务器状态信息:\s,mysql版本信息:SELECT version(),打开表结构:desc table。
四、数据库语法组成:
1.DDM(Data Definition Language数据库定义语言):create table,drop table,alter table等
2.DCL(Data Control Language数据控制语言):grant,revoke等;
3.DML(Data Manipulation Language数据操作语言)查询SELECT、插入insert、update修改、删除delete;
五、MYSQL三种常用的数据类型:文本:char,varchar,text;数字,日期和时间类型;
六、常见的完整性约束:
primary key 主键;unique 唯一性约束;not null非空约束;auto_increment整数列自增;默认值:default;
七、常用操作
- 显示表结构:DESC 表名;
- 删除表操作:drop table 表名
- 除数据库操作:drop database 数据库名
- 更改表结构操作:alter table 表名 action;(action可以是以下操作)
- add 列名 建表语句[first/after]--在表中添加列,制定其位置;
- add primary key (列名)-- 添加一个主键,如果主键已存在,会报错;
- add foreign key (列名) reference 表名 （列名）;-- 为表添加一个外键;
- alter列名 set default 默认值;-- 更改指定列的默认值
- drop 列名 -- 删除一列
- drop primary key -- 删除主键
- engine 类型名 -- 改变表的类型
- rename as 新表名 -- 改变表名
- change 旧列名 新列名 [first /after]-- 更改列的类型和名称
- modify 和change相同;
八、运算符
- 比较运算符:<>,BETWEEN ,IN,<=>,BINARY（区分大小写）
- 位运算符:<< :a<<b 将a向左移动b个二进制位,a*2的b次方;与>>相反
九、集函数
1.记数函数: count(列名)计算元素的个数
2.求和函数: sum(列名) 对某一列的值求和,但属性必须是整型
3.计算平均值:avg(列名)对某一列的值计算平均值
4.求最大值: max(列名)找出某一列的最大值
5.求最小值: min(列名)找出某一列的最小值
十、增删改查基本操作:
1.创建数据库school并设定类型utf-8;
create database school character set utf8;
2.删除数据库school
drop database school
3.创建学生表
create table student(
id int not null auto_increment primary key,//添加id列,并设定int型自增,主键
sname varchar(20) not nul
);
4.添加列 sex
alter table student add column sex char(2) not null after name;
5.删除列 sex
alter table student drop column sex;
6.修改列名,类型
alter table student change sex ssex bit not null;
7.数据库插入数据
insert into student (name,sex,age) values ('张三','男',18);
8.更新表数据
update student set sex='女' where id = 3;
9.删除表中数据
delete FROM student where id = 2;
10.查询所有信息
SELECT * FROM student;
11.外键关联
alter table score add constraint FK_score foreign key(id)reference student (sid)
12.复制表
create table sscore SELECT * FROM score;
13.复制表结构
create table sscore SELECT * FROM score where 0=1;
14.更换表名
alter table 表名 rename 新表名;
15.设置默认值（之前字段内容不会改变）
alter table student alter sage set default'18';
16.创建索引:
- create index IX_customer on customer(cusname)(给customer的cusname创建一个索引)
- alter table customer add index IX_customer(cusname)(给创建好的表中添加一个索引)
十一、查询记录操作
- FROM子句:指定查询数据的表
- where子句:查询数据的过滤条件
- group by子句:对where子句的查询进行分组
- having子句:对分组后进行条件限制
- order by子句:对查询进行排序操作 DESC,ASC;
- limit对查询目录进行限制数目.
十二、经典查询:
1.查询学生的详细记录:SELECT * FROM student;
2.查询信息系、美术系和计算机系学生的姓名和性别
- SELECT sname,ssex FROM student where sdept in (‘信息系','美术','计算机系');
- SELECT sname,ssex FROM student where sdept='信息系' or sdept='美术' or sdept='计 算机系'
3.查询选修了课程的学生人数 :SELECT count(*) FROM score;
4.查询年龄在20-23（包括20,23）的学生姓名
SELECT sname FROM student where sage between 20 and 23;
5.查询年龄最大的三个学生的姓名和年龄,或第四五个学生
SELECT sname,sage FROM student order by sage desc limit3(limit 3,2);
6.查询学生平均成绩在70分以上的同学的姓名和平均成绩
SELECT s.sname,avg(s2.score)FROM student s,score s2 where s.id = s2.sid groupby s2.score having avg(s2.score)>70;
7.查询所有同学的学号,姓名,选课书,总成绩;
SELECT t.id,t.sname,COUNT(s.cid),SUM(s.score)FROM student t, scores s WHEREt.id = s.sid GROUP BY t.id;
8.查询姓“李”的老师的个数
SELECT COUNT(*) FROM teacher WHERE tname LIKE '李%';
9.查询所有课程成绩小于60分,同学的学号、姓名
SELECT t.id,t.sname FROM student t,scores s WHERE t.id=s.sid AND s.score<60;
10.查询所有,课程成绩小于60分的同学的学号、姓名;
SELECT t.id,t.sname FROM student t,scores s WHERE t.id =s.sid GROUP BYs.score<60;
11.查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序
排列
SELECT c.cname,AVG(s.score)FROM course c,scores s WHERE c.id=s.`cid`GROUP BYs.cid ORDER BY AVG(s.`score`),s.`cid` DESC;
12.查询任何一门课程成绩在70分以上的姓名、课程名称和分数;(只要有一个就可以)
SELECT c.cname,s.score FROM course c,scores s WHERE c.id = s.`cid` ANDs.`score`<60;