mysql笔记

数据库笔记 一、数据库的常见命令 1、mysql服务器的启动方式 ● 通过mysql的command client 输入密码，启动成功。如果采用该方式不能登录，可以采用第二种方式启动。 ● 在windows的命令行窗口启动mysql的服务 首先要找到mysql的安装路径，设置path。 Mysql.exe服务所在的目录 Set path命令 Set path=C:\Program Files\MySQL\MySQL Server 5.1\bin;   在命令行上设置path，这种设置方式只对当前窗口生效。要想对其他所有窗口都生效，可以采用如下方式设置path环境变量。 在桌面上：“我的电脑”——“属性”——出现如下窗口 在该窗口中选择：“高级”——“环境变量”。弹出如下窗口（环境变量）。 可以采用新建path环境变量的方式（如果不存在path环境变量的话）。 注意：一个path可以有多个值（就是多个不同的路径，每个路径使用英文输入法的分号“;”）。 在windows的命令行下输入mysql 使用指定的身份登录 2、常见命令 1）、查看当前账户下的所有database show databases;   2）使用指定database use test;   3）创建一个database create database mydb;   4）删除指定的database drop database mydb;   5）显示指定database中所存在的表 show tables;   6）在指定的database中创建表 Create table xs.test( name varchar(10) not null, Age int(3) );   注意：如果要创建的表是在当前database下，则database和引导符可以省略。 7）删除表 drop table test;   8）查看表的结构 Desc test;   9）查看表的创建语句 show create table test;   3、数据库设计第一范式：字段不可分 数据库中的表是为了以更好的格式对数据进行存储和管理的。比如有如下数据要存储，如何设计该表，存储数据。 信息：张三今年28岁，于1983年5月26日出生于浙江省嘉兴市，是一个不折不扣的好学生。 如果按如下方式设计表： create table student( name varchar(10), age int(3), info varchar(800) );   如果使用该表结构存储学生信心。出了可以具体的查到学生的姓名和年龄，其他信息都是模糊的。不具体。比如学生的出生日期和出生地和学生的详细信息都混在一起了（info列）。这样不利于查询。违背了数据库设计的第一范式（每列都是不可分的）。 按照第一范式的要求，可以将表设计为如下格式： create table student( name varchar(10), age int(3), birthday date, province varchar(20), city varchar(20), info varchar(800) );   这个表的结构就比上面的表的设计要详细，和利于查阅管理。 二、数据库的增删改查语句 1、增加记录：insert into 表名称(列名称) values(每列对应的具体值); 以student表为例： create table student( name varchar(10), age int(3), birthday date, province varchar(20), city varchar(20), info varchar(800) );   向该表录入一条语句： insert into student(name,age,birthday,province,city,info) values('汤少雄',21,'1989-9-9','湖南','长沙','好人呢！！！');   上面的语句是为该表中的每一列都赋值了。那么像这种情况，可以省略表名称之后的列名称集合。上面的语句还可以这样写： insert into student values('罗志雄',21,'1989-9-9','湖南','长沙','好人呢！！！');   如果允许列非空。我们也可以只为指定的列赋值，比如： insert into student(name,age) values('尹钊',21);   2、删除记录：delete from 表名称 [条件] Delete from student where name = ‘尹钊’;   3、修改表中的记录：update 表名 set 列1=新值 , 列2=新值 [条件] update student set birthday='1988-8-8', province='湖南' where name = '尹钊';   4、查询记录：select 列名1 [[as ] 别名称], 列名2  from 表名称 [条件] select name , age from student;   注意：如果要想查看表中的每一列。可以使用通配符“*” select * from student;   使用别名： select name as '姓名' , age '年龄' from student;   5、常见的数据库中出现的关键字 名称 介绍 Where 表示引出条件的关键字 And 连接两个条件、且的关系 Or 连接两个条件、或的关系 Not 对条件取反 Between and 范围的取值，闭区间取值 In 在指定的范围内任意取值匹配 Is 也是一个boolean类型的结果表达式：is null | is not null null 表示该列的值不确定，不是没用或空的意思 Like 在进行模糊查询的时候使用，经常和“_”、“%”联合使用 Order by 对查询结果进行排序的时候使用，和ASC（升序）、DESC（降序）联合使用 Group by 分组语句，对表中的记录进行分组 Having 对分组语句查询得到的结果进行过滤 As 对表的列或表名称起别名的一个关键字 Outer join 外连接查询 Left outer join 左外连接查询 Right outer join 右外连接查询 Union all 全连接查询 Distinct 去除重复的结果，重复的结果只保留一个     6、简单查询 1、在student表中查询name等于“尹钊”的记录 select * from student where name='尹钊';   注意：因为name这一列的值不是要求唯一的。那么这条语句的查询结果又可能是多条。 2、在student表中查找age大于15的记录 select * from student where age>15;   3、在student表中查找age小于15的记录 select * from student where age<15;   4、在student表中查找birthday位于“1980-08-08”和“1989-09-09”出生的记录 select * from student where birthday<='1989-9-9' and birthday>='1980-8-8';   注意：在对日期格式的数据进行值比较时，要将日期值用单引号引起来。 5、在student表中查找age大于60 或者age小于18 select * from student where age>=60 or age<18;   6、在student表中查找age取值范围在18至60之间的记录 select * from student where age between 18 and 60;   注意：位于between and之间的值是可以取到的。也就是说会以闭区间匹配。 7、在student表中查找age等于32岁、18岁的记录 select * from student where age=32 or age = 18; select * from student where age in (18,32);   8、在student表中查找city为null（不确定）的记录 select * from student where city=null;错误的写法 select * from student where city is null;要使用is关键字   9、在student表中查找city不是null值的记录 select * from student where city is not null;   10、在student表中查找age不是18和32的记录 select * from student where age not in (18,32);   11、在student表中查找name以字符“h”开头的记录 select * from student where name='h%';这样写是错误的。 select * from student where name like 'h%';这样写才是对的。   12、在student表中查找name的第二个字符是“x”的记录 select * from student where name like '_x%';   注意：“_”通配一个字符，而“%”通配多个字符。在mysql数据库中对于值的大小写是不敏感的。 13、对student表中的记录按照birthday进行降序查找 select * from student order by birthday desc;   注意：如果是按照升序排，可以使用asc。也可以不写。默认就是升序。 7、数据库设计第二范式：避免数据冗余 现在有如下信息需要保存到数据库中：学生张三（18岁，来自湖南省）在来到华点软件学院一个月了。到目前为止他学习了javaSe的课程，这门课程是java的基础课程。一定要学好才行。很重要。还有其他的一些同学。请设计表存储数据。 按照数据库设计第一范式的要求，可以对以上数据进行类型提取：姓名、年龄、地址、学生介绍、课程名称、课程介绍。因此表的结构如下： create table student_object( name varchar(10), age int(3), address varchar(200), sinfo varchar(500), oname varchar(40), oinfo varchar(500) ); 测试数据： insert into student_object values('zs',15,'anhui','good boy.','javaSe','so good......'); insert into student_object values('ls',15,'zhejiang','good boy.','javaSe','so good......'); insert into student_object values('ww',16,'anhui','good boy.','javaWeb','so good......'); insert into student_object values('zl',22,'hunan','good boy.','javaScript','so good......'); insert into student_object values('zb',23,'hunan','good boy.','javaScript','so good......');   然后执行查询： Select * from student_object;   注意：以上表随然设计完了。但是发行很多数据在该表中不断的重复出现，这在数据库中叫做数据冗余。还有一个问题就是：学院可能开了很多门课程。但是目学生只学了部分课程，那么学生此时没有学习的课程就没有办法在数据库中体现了。显然是不合理的。因此第一范式已经不能解决问题了。由此引出第二范式： 第二范式：一个事物对应一个表。关联表之间采用外键进行关联。而且不允许在表中出现冗余数据。 重新对以上数据进行表的设计： #每门课程都应该有一个唯一的课程编号。 create table object( ono varchar(3), oname varchar(50), oinfo varchar(500) ); #学生表中的ono的值应该由Object表中的ono决定。也就是说：student中的ono的值只能#从object中的ono取。 create table student( sname varchar(10), age int(3), address varchar(200), sinfo varchar(500), ono varchar(3) );   向object表中录入测试数据： insert into object values('001','javaSe','so good!!!!'); insert into object values('002','javaScript','so good!!!!'); insert into object values('002','javaWeb','so good!!!!');   在录入测试数据时发现：两门不同的课程，但是它们的课程编号是相同的。这不符合要求。怎么办呢？这是一种逻辑错误。我们应该想一种办法：让ono这一列的值唯一。那么我们可以采用唯一约束（unique约束）。 重新修改表的结构： #每门课程都应该有一个唯一的课程编号。 create table object( ono varchar(3) unique, oname varchar(50), oinfo varchar(500) );   录入测试数据： insert into object values('001','javaSe','so good!!!!'); insert into object values('002','javaScript','so good!!!!'); insert into object values('002','javaWeb','so good!!!!');   向student表中录入测试数据： insert into student values('zs',22,'anhui','sssss','001'); insert into student values('ls',23,'anhui','sssss2','001'); insert into student values('ww',23,'zhejiang','sssss3','002'); insert into student values('zl',25,'zhejiang','sssss4','003'); insert into student values('zb',35,'zhejiang','sssss5','007');   这个结果很荒唐（student表中ono没有参照object表中的ono取值）。也属于逻辑错误。怎样可以保证student表中的ono参照object表中的ono取值呢？我们可以使用外键约束（foreign key约束）。 重新定义student表： create table student( sname varchar(10), age int(3), address varchar(200), sinfo varchar(500), ono varchar(3) ); alter table student add foreign key(ono) references object(ono); 添加外键约束   录入测试数据： insert into student values('zs',22,'anhui','sssss','001'); insert into student values('ls',23,'anhui','sssss2','001'); insert into student values('ww',23,'zhejiang','sssss3','002'); insert into student values('zl',25,'zhejiang','sssss4','003'); insert into student values('zb',35,'zhejiang','sssss5','007'); 错了。 #该记录违背了外键约束，因为007这个课程编码在object表中不存在对应的记录。 insert into student values(null,'zs',22,'anhui','sssss','002'); insert into student values(null,'zs',22,'anhui','sssss','003');   8、约束 约束类型 功能 Not null 非空约束 Unique 唯一性约束 Foreign key 外键约束 Primary key 主键约束（具备了unique的功能）     主键约束： ● 该列的值是唯一的 ● 主键列应该是一个没有任何业务含义的列 ● 主键列除了没有业务含义之外，该列习惯用整型数据充当 ● 整型的主键列，还习惯是自增长的列 修改上面的表的结构： create table object( id int auto_increment primary key, ono varchar(3) unique, oname varchar(50), oinfo varchar(500) ); create table student( id int auto_increment primary key, sname varchar(10), age int(3), address varchar(200), sinfo varchar(500), ono varchar(3) ); alter table student add foreign key(ono) references object(ono);   测试数据： insert into object values(1,'001','javaSe','so good!!!!'); insert into object values(2,'002','javaScript','so good!!!!'); insert into object values(0,'003','javaWeb','so good!!!!'); insert into object values(null,'004','flex','so good!!!!'); insert into object values(8,'005','php','so good!!!!'); insert into object values(null,'006','asp','so good!!!!');   外键约束： ● 在主表和从表中使用 ● 由主表定义，从表引用，也就是说：从表中该列的值一定要在主表中取，如果主表中不存在该值，则无法引用 9、对第七节的表结构进行研究 注意，object表中不会出现冗余数据了。但是，student表又出现了冗余数据。显然在第七节的表的结构设计是错误的。 图中显示：5、6条记录和第一条记录发生了数据冗余。 修改第七节的表结构：经分析得出，要创建一个中间关系表。就可以避免student和object表的数据冗余问题了。 create table object( id int auto_increment primary key, ono varchar(3) unique, oname varchar(50), oinfo varchar(500) ); create table student( id int auto_increment primary key, sno varchar(3) unique, sname varchar(10), age int(3), address varchar(200), sinfo varchar(500) ); create table sorefere( id int auto_increment primary key, sno varchar(3), ono varchar(3) ); alter table sorefere add foreign key(sno) references student(sno); alter table sorefere add foreign key(ono) references object(ono);   注意：从表引用主表中的列，那么该列的值一定要唯一，也就是说：该列要么是unique约束，或者是主键约束（primary key）。 注意：现在要想添加成绩列，如何做？ 只需要该中间关系表即可： create table sorefere( id int auto_increment primary key, sno varchar(3), ono varchar(3), grade numeric(5,2) ); alter table sorefere add foreign key(sno) references student(sno); alter table sorefere add foreign key(ono) references object(ono); insert into sorefere values(null,'001','001',25); insert into sorefere values(null,'001','001',15.666666); insert into sorefere values(null,'001','001',99.466666);   10、修改表结构 现有表： create table demo( naem int );   1）为指定表添加一列：表示年龄的age alter table demo add column age int;   alter table demo add age int after name; 在指定的列之后添加一列。 alter table demo add age2 int first; 将要添加的列，作为第一列添加。   2）修改列名称：发现上面的表中姓名列拼写错误 alter table demo change naem name varchar(10);   3）修改指定列的长度：比如上面表中age的长度最多3位即可 alter table demo modify age int(3);   注意：change 和 modify 的区别是：在使用change的时候，原来的列名称也要写上，而使用modify的时候，原来的列名称可以不写。说明，modify主要是修改列的数据类型或长度的。 4）删除列：比如删除上表中的age列 alter table demo drop column age; column 不可省去！！！   注意：在添加、修改时，关键字“column”可以省略不写。即：Alter table 表名称 drop|add [column] 列名称。 三、mysql操作手册的使用 1、数据类型 1）日期类型 ● Date 表示的是日期 ● Datetime 表示的是日期和时间 ● Time 表示的是时间，没有日期 ● Timestamp 表示的是日期和时间，默认值是当前系统的日期时间，且和时区关联 2）文本类型 ● Char 取值范围是[0,255] ● Varchar 取值范围是[0,65535] ● Text 文本的最大长度是65535个字符 3）逻辑类型 ● Boolean 可以存放的是1 or 0。. 2、内置函数 1）日期、时间函数 ● 当前日期函数 curdate() 获取的就是当前服务器的日期 ● now() 表示的是当前的日期和时间 ● sysdate() 获取当前系统的日期和时间 2）对日期、时间格式化输出的函数 ● date_format 对日期和时间进行格式化输出 example ：select date_format('2011-8-17 11:07:1','%d-%m-%y'); 结果是：17-08-11 select date_format('2011-8-17 11:07:1','%d-%m-%Y'); 结果是：17-08-2011 模板字符的定义及意义参考： 3）自增长列的最大值 ● last_insert_id() 获取当前表的自增长列的当前最大值 3）字符串处理函数 ● concat 字符连接函数 select concat('我的名字是：',firstname,lastname) as info from demo2; ● trim 去除空格的函数 select trim(firstname) from demo2; ● substring 截取字串的函数 select substring('abcdefg',2); 结果是：bcdefg ● lpad|rpad 字符填充函数 SELECT LPAD('hi',4,'??'); 结果 ：'??hi' 4）对数字进行格式化的函数 ● format  例子：select format(456218569322,0);  结果是：456,218,569,322 select format(456218569322,2);  结果是：456,218,569,322.00 5）数据加密函数 ● password 可以对数据进行加密 例子： create table user( uname varchar(8) not null, psword varchar(900) not null ); insert into user values('zs123','666666'); insert into user values('ls123',password('888888'));   Ls123加密之后的数据图 注意：还有很多内置函数，可以参考手册。 四、数据库的事务 在mysql数据库中，事务默认的是自动提交。我们可以通过set autocommit = 0; 命令使事务的提交方式变为手动提交。 例子： create table demo7( name varchar(10), age int(3) ); Set autocommit = 0; 手动提交事务 Begin; 开始事务 Insert into demo7 values(‘aaaa’,25); 执行insert语句 Commit；手动提交事务，目的是使insert的内容持久化到数据库的表中。也可以使用rollback对事务进行回滚。   事务的几条原则：也就是ACID原则 ● 原子性 ● 一致性 ● 隔离性 ● 稳定性 五、高级查询 1、分组查询和聚合函数的使用 样例表： create table st( id int auto_increment primary key, name varchar(10), age int, classname varchar(10), java numeric(5,2), php numeric(5,2), flex numeric(5,2) ); insert into st values(null,'zs',18,'27班',50,41,78); insert into st values(null,'lsi',20,'27班',90,41,62); insert into st values(null,'wwu',18,'28班',10,80,20); insert into st values(null,'zs',18,'28班',88,46,32); insert into st values(null,'zouba',23,'28班',98,68,78); insert into st values(null,'zouqi',23,'27班',98,99,78);   1）查询java成绩高于60分的学生，显示其详细信息 select * from st where java>60;   2）查询java成绩最高的学生，显示该生的name和classname select name, classname, java from st where java = (select max(java) from st);   3）统计各班人数 ● 首先要会统计总记录数 select count(id) from st; ● 其次要会分组 select classname from st group by classname; 按班级分组 select count(*) , classname from st group by classname;   4）查询各班java成绩的总分 ● 对某列求和 ● 分组 select sum(java), classname from st group by classname;   5）查询各班java成绩最高分的学生，显示name、java、classname列信息 select name, s.classname, java from st s, (select max(java) as hjava, classname as classname from st group by classname) as tmp where s.java = tmp.hjava and s.classname = tmp.classname;   样例表2： create table object( id int auto_increment primary key, ono varchar(3) unique, oname varchar(50), oinfo varchar(500) ); create table student( id int auto_increment primary key, sno varchar(3) unique, sname varchar(10), age int(3), address varchar(200), sinfo varchar(500) ); create table sorefere( sno varchar(3), ono varchar(3), grade numeric(5,2) ); alter table sorefere add foreign key(sno) references student(sno); alter table sorefere add foreign key(ono) references object(ono); alter table sorefere add primary key(sno,ono);   6）查询平均成绩高于78分的学生，显示name和其平均成绩 select st.name, tmp.avggrade from student st, ( select sno, avg(grade) avggrade from sorefere group by sno having avg(grade) > 78 ) tmp where st.sno = tmp.sno;   样表： create table object( id int auto_increment primary key, ono varchar(3) unique, oname varchar(50), oinfo varchar(500) ); create table classroom( id int auto_increment primary key, cno varchar(10) unique, cname varchar(10) ); create table student( id int auto_increment primary key, sno varchar(3) unique, sname varchar(10), age int(3), address varchar(200), sinfo varchar(500), cno varchar(10) ); create table sorefere( sno varchar(3), ono varchar(3), grade numeric(5,2) ); alter table sorefere add foreign key(sno) references student(sno); alter table sorefere add foreign key(ono) references object(ono); alter table sorefere add primary key(sno,ono); alter table student add foreign key(cno) references classroom(cno);   7）查询各班总分数高于200的班级 select cm.cname, tmp2.sgrade from classroom cm, (select sum(tmp.grade) sgrade, tmp.cno cno from (select cno, grade from student st, sorefere sf where st.sno = sf.sno) tmp group by tmp.cno) tmp2 where cm.cno = tmp2.cno;   8）查询各班学生的总分数高于150分的学生 select st.sname, tmp.sgrade from student st, (select sno, sum(grade) sgrade from sorefere group by sno having sum(grade)>150) tmp where st.sno = tmp.sno;   2、外连接查询 测试数据： insert into classroom values(null,'001','27班'); insert into classroom values(null,'002','28班'); insert into classroom values(null,'003','29班'); insert into student values(null,'001','zs',25,'ddddd','dfddfd','001'); insert into student values(null,'002','zs2',25,'ddddd','dfddfd','001'); insert into student values(null,'003','zb',25,'ddddd','dfddfd','002'); insert into student values(null,'004','zq',25,'ddddd','dfddfd','001');   9）查询各班学生总人数 select cm.cname, tmp.sums from classroom cm left outer join (select cno, count(sname) sums from student group by cno) tmp on cm.cno = tmp.cno;   如何将null值替换为指定的值：可以使用ifnull函数 select cm.cname, ifnull(tmp.sums,0) from classroom cm left outer join (select cno, count(sname) sums from student group by cno) tmp on cm.cno = tmp.cno;   例子：左外连接和右外连接 create table demo1( name varchar(20) ); insert into demo1 values('a'); insert into demo1 values('b'); insert into demo1 values('c'); insert into demo1 values('d'); create table demo2( name varchar(20) ); insert into demo2 values('a'); insert into demo2 values('b'); insert into demo2 values('c');   内连接查询的结果： select d1.name, d2.name from demo1 d1, demo2 d2 where d1.name = d2.name;   左外连接查询 select d1.name, d2.name from demo1 d1 left outer join demo2 d2 on d1.name = d2.name;   右外连接查询： select d1.name, d2.name from demo1 d1 right outer join demo2 d2 on d1.name = d2.name;   3、联合查询 联合查询一定要保证每个结果集的列数，对应列的数据类型一定要匹配。 10）查询001班的学生总数和005班的学生总数 select count(sname), cno from student st where st.cno = '001' union all select count(sname), cno from student st where st.cno = '003';   注意：union 和 union all 的使用区别 select sname, age from student where id = 1 union all select sname, age from student where id = 2;   select sname, age from student where id = 1 union select sname, age from student where id = 2;   根据查询结果得出：union all 不会去除重复的记录，而union 会去除重复的记录，对于重复的记录只保留一条。和关键字distinct类似。