Mysql笔记.docx

1、Mysql笔记安装；基本命令create table class (stu int,name varchar(20),age int,area varchar(20);库create database show databasesdrop database testdesc tables test /查看表结构drop table testdesc testrename table a to b /重命名表库名不能改增insert into test(id,age,name) /指定列values(1,10,zhang3); /单引号set names gbk; /解决字符编码问题insert

2、 into test(age,name) values(10,张三);改update testset age=111,name=liujichaiwhere id=3;列的默认值:alter table test add age3 tinyint not null default 0;/取消null删除delete from testwhere id=2;alter table y drop column 列名/删除列查select id,name from test where id3; /select，列，where，行添加列alter table test add age1 tinyin

3、t(3) zerofill;create table class (id int primary key auto_increment,age tinyint)charset utf8;insert into test(age,name,age1)values(20,小王,3);整形tinyint 1字节smallint 2mediumint 3int 4bigint 8tinyint(M) unsigned zerofill/ 零填充，无符号浮点型 float（M,D)/m总位数，d小数位 占用4或者8个字节create table goods (name varchar(10) not n

4、ull default ,price float(6,2) not null default 0.00)charset utf8;定点型alter table goods add max float(9,2) not null default 0.0;alter table goods add min decimal(9,2) not null default 0.0;/定点型insert into goods(min,max)values(12.1111111111,13.222222222);字符型create table stu(name char(8) not null default

5、 , /定长，8个UTF-8字符，不够用空格补齐aihao varchar(10) not null default /变长，用1-2个字节来记录占用了多少个字节)charset utf8;速度上，定长速度快chartext 型字符 不能创建索引 日期类型date 日期time 时间datetime 日期时间类型year 年类型 1901-2155create table y (ya year(4) );insert into y /不指定表，默认插入所有列values(1901);year类型，输入一位输入两位00-69表示2000-206970-99表示1970-1999date型1992

6、-08-12日期时间日期create table d (dt data);time类型 hh:mm:ssinsert into y (tm) values (12:10:23);datetime类型 1989-05-06 14:32:05时间戳enum 枚举型set 集合性create table t2 (gender enum(man,girl)charset utf8;char set 例题:name: char(3)age: tinyint unsignedemail: varchar(30)tel: char(11)intro: varchar(100)salary: decimal(

7、7,2)riqi: date create table wolf ( id int primary key auto_increment, name char(3) not null default , age tinyint unsigned not null default 0, email varchar(30) not null default , tel char(11) not null default , salary decimal(7,2) not null default 2000-01-01, riqi date not null default 2012-01-01 )

8、charset utf8;增删改查：案例分析增:哪张表，哪列，值insert into wolf （name,age,email,tel,riqi)values (-）；主键值不能重复insert into wolf(name,age,tel)values(张飞,79,66011123),(关羽,88,66011124),(曹操,89,66011125); /一次可以插入多行改update wolf set id=2 name=wolf where * /修该多个值select * from wolf where 1; /这个1为逻辑值真删 ：delete 只能删除行，alter可以drop列

9、 delete from wolf where * /查： 安装ecshop 保留upload，重命名为ecshop浏览器输入路径localhost/ecshopE:MySqlECSHopuploadlocalhost/E:/MySql/Enviroment/PHPnow-1.5.4/MySQL-5.0.83/data/testE:MySqlEnviromentPHPnow-1.5.4MySQL-5.0.83datatest列类型数值型 整形 tinyint，smallint，mediumint，intbigint M，unsigned，zerofill 浮点型 定点型float（m,d) u

10、nsigned,M精度，d小数位 decimal 比float更精确字符型 char（M) 定长型，可存储的字符数 varchar(M)，日期时间类型 year 1901-2155,两位数输入法， date yy-mm-dd ，范围1000-1-19999-12-31 time HH：mm:ss 范围-838:59:59-838:59:59 datetime YY-MM-DD HH:ii:ss 1000-01-01开发中的问题 时间戳，方便计算，格式成不同的样式 建表语句 create table 表名（ 列名称 列类型 【列属性】【默认值】 ）engine 引擎名 charset 字符集；增

11、： insert into 表名 （列1，列2，列3） values （值1，值2，值3）； /如果不声明插入的列，则默认插入所有列改：update 表名 set 列1=值1，列2=值2 where 表达式；/表达式删：delete from 表名 where 表达式查：select * from 表明查询的5种子句：where 条件查询group by 分组having 筛选order，by 排序limit 限制结果条数一：whereselect 列名 from 表名 where 行；运算符 小于 |=或者 不等于in 在某集合内 between 在某范围内逻辑运算符NOT或 ！逻辑非 OR

12、或 | 逻辑或AND或& 逻辑与select id,name from wolf where id in (4,7,8);select id,name from wolf where id between 1 and 3;select id,name,tel from wolf where id = 6 | id=1;select id,name,tel from wolf where id2 & id 10 /统计A类，select *,sum(age) as hk from wolf group by class having hk 30;/查询两门及两门以上不及格同学的平均分select

13、 name,countselect 姓名,avg(分数) from stu group by 姓名;/列名不要用单引号，会出错select *,sum(分数 60) from stu group by 姓名;select *,sum(分数 =2四：order byselect *,age from wolf order by age;select * from wolf where class=A order by age;/默认升序排列，select * from wolf where class=A order by age desc; /desc说明，用倒叙排列select * from

14、 wolf where class=A order by age asc; /指明升序排列select * from wolf where class=A order by age,id desc; /第一排序标准，第二排序标准五：limit取价格最高的三个商品limit放在最后有限制的作用，limitoffset,N /偏移量，取出条目select * from wolf order by age desc limit 3，3;/排序向后偏移3位，取三个数六i：附加：物种子句的使用陷阱组内排序和组代表排序如果有多种语句，按where,group by,having,order by,limi

15、t 顺序写，否则语法错误create table goods like wolf;/表的列复制insert into goods select * from wolf order by age desc;/内容的传递truncate goods /清空表良好的理解模型where 表达式，把表达式放在行中，看表达式是否为真列，理解成变量，可以运算取出结果，可以理解成一张临时表select *,age from wolf group by class order by age desc;/错误，代表进行排序 select id,name,max(age) from wolf group by cl

16、ass;七.子查询where型子查询from型子查询exsts型子查询1.where 型(把内层的查询结果作为外层查询的比较条件）select * from wolf order by age desc limit 1;/求最大age select id,name,class from wolf where age=(select max(age) from wolf);2.from 型：把内层的查询结果当成临时表，供外层再次查询select id,name,age from wolf where age in (select max(age) from wolf group by class

17、);select max(id),age from (select id,name,age from wolf where age in (select max(age) from wolf group by class) as tmp;/把（）内当作一张表select 姓名,count(*) from stu where 分数60 group by 姓名;select 姓名 from (select 姓名,count(*) as gk from stu where 分数=2) as tmp;3.exists型把外层的查询结果拿到内层，看内层是否成立mysql select id,name,a

18、ge from wolf where exists (select * from wolf where age 20 );链接：union一.select union 用法 union联合合并查询结果链接查询 左链接 右链接 内链接列的增加与删除与修改select * from wolf where age60 or age 60 union select * from wolf where age select id,num from (select * from ta union select * from tb) as tmp group by id;union 用的时候会自动合并重复的

19、行mysql select * from ta union all select * from tb;/同样的行也会显示mysql (select * from wolf where class=A order by age desc limit 2) union (select * from wolf order by id desc limit 2);mysql (select * from wolf where class=A order by age desc limit 2) union (select * from wolf order by id desc limit 2) or

20、der by id;mysql (select * from wolf where class=A order by age desc limit 2) union (select * from wolf order by id desc limit 2) order by id desc limit 2;在子句中order by 配合limit 使用才有意义，如果order by 不配合limit使用，会被语法分析器分析时去掉二.链接查询左链接 右链接集合 set 集合的特性：无序性、唯一性、理论上讲：不可能存在完全相同的两个行，但是表中可以有完全相同的两行因为，表内部有一个rowid (不

21、可能重复）select * from ta,tb;/ta和tb的排列组合1.连接接上表2.连接条件on join如果字段名重复，添加表前缀select ta.id,ta.num from tb left join ta on ta.id=tb.id;create table boy (name char(3),flower char(5);insert into boyvalues(林书豪,玫瑰),(刘翔,桃花),(周杰伦,茉莉花),(犀利哥,荷花);(刘德华,狗尾巴花);create table girl(name char(3),flower char(5);insert into gir

22、l values(艾薇儿,玫瑰),(居里,桃花),(芙蓉,茉莉花),(凤姐,茉莉花),(林志玲,荷花);找女友的活动select boy.*,girl.* from boy left join girl on boy.flower = girl.flower;左链接：以左表为准，去右表中找匹配数据，找不到用null补齐右链接：以右表为准，去左表中找匹配数据，找不到用null补齐boy left jion girl girl right join boy;推荐使用左链接代替使用右链接三：内链接（排除单身）查询左右表都有的数据select boy.*,girl.* from boy inner j

23、oin girl on boy.flower = girl.flower;左链接和右链接的交集（内链接）左链接和右链接的并集（mysql不能）外链接 三表链接查询select boy.*,girl.* from boy inner join girl on boy.flower = girl.flower left join wolf wolf.id= tb.id(有共同项目，才能链接）create table m(mid int primary key auto_increment,hid int,gid int,mres varchar(5),matime date)charset utf

24、8;insert into m(hid,gid,mres,matime)values(1,2,2:0,2006-05-21),(2,3,1:2,2006-06-21),(3,1,2:5,2006-06-25),(2,1,3:2,2006-07-21);create table t (tid int,tname varchar(10)charset utf8;insert into tvalues(1,国安),(2,申花),(3,传智);t+-+-+| tid | tname |+-+-+| 1 | 国安 | 2 | 申花 | 3 | 传智 |+-+-+m+-+-+-+-+-+| mid | h

25、id | gid | mres | matime |+-+-+-+-+-+| 1 | 1 | 2 | 2:0 | 2006-05-21 | 2 | 2 | 3 | 1:2 | 2006-06-21 | 3 | 3 | 1 | 2:5 | 2006-06-25 | 4 | 2 | 1 | 3:2 | 2006-07-21 |+-+-+-+-+-+ select hid,mres,gid,matime from m; select hid,tname,mres,gid,matime from m left join t on m.hid = t.tid;select hid,t1.tname as hname,mres,gid,t2.tname as gname,matime fromm left join t as t1on m.hid=t1.tidleft join t as t2on m.gid=t2.tidwhere matime between 2006-06-01 and 2006-07-01;附加：列的增加和修改charset = utf8/gbk/增加的列默认在最后面一：增加列可以用after 声明在哪一列后面alter table boy add age tinyint un