传智播客mysql.doc

数据库服务器N多库N张表行与列1先建立数据库服务器2建立一个库3再建表Mysqld 服务器一、 连接数据库1 连接数据库所需要的工具：Mysql作为数据库服务器来运行，任何满足mysql通信规范的软件都可以作为客户端来连接服务器常用的客户端：mysql front navcat，基于web的phpMyAdmin，还有mysql自带的命令行客户端2 以命令行连接为例：注意：如果在命令行提示下出现如下提示：则说明：没有配置环境变量，导致系统找不到mysql.exe解决：1. 配置环境变量，制定mysqlbin目录2. 每次进入到mysqlbin目录，再运行mysql3 如何连接服务器服务器地址（可用ip，域名）：端口（3306）用户名密码 Mysql h localhost uUserName pPassWd -h 如果不写，则默认连localhostMysql h localhost u root p 回车 输入密码Mysql uroot p123456表格（多行多列，二位结构）通讯录表：姓名年龄手机号薪资登记表：姓名性别公司工资饭补多表成库 ，表存于库下面，一个库下边可以有多个表库存在于数据库服务器，一个服务器下面可以有多个库入门语句：1 查看当前服务器下面，有哪些库（database） show databases;2 创建一个数据库 create database DBName字符集声明，整理声明；3 删除数据库 drop database test2;4 修改数据库（只能修改字符集和整理集，简单，先自己看） 能否修改数据库的名字？答 不能！5 选择数据库在进行表，行的相关操作时，要先选择数据库 use DBName; /选择相应的数据库mysql use shop;Database changed6 如何查看一库下面的所有表show tables;7 如何删除一张表drop table tableName;8 如何修改表名 rename table oldName(旧表名) to newName（新表名）;9 查看表结构desc tableName; /查看表结构10 最简单的建表结构 create table 表名（ 列1名称 列1类型， 列2名称 列2类型（最后一列前不要加，）; 这里 先记住两种最常用列类型 int(整形)，varchar(字符串)实际操作：创建一张学籍表，有2个字段，id,name,分别代表学号和姓名 create table stu(id int,name varchar(10);11 解决字符集问题： 默认建表一般用utf8 ,而我们在windows下窗口时gbk的， 因此，需要声明字符集。 set names gbk；增删改查 例子insert into msg 增加(id，title，name，content)values(1，初来乍到，张三，刚来能不能当老大)； insert into msg （id，title，name，content） values （1，又来了，李四，你只能千年老二）；update msg 更新、改set id=2，content=偏要当老大where name=李四；插入多行 insert into msg (id，title，name，content) Values （3，3标题，刘备，雌雄双剑），（4，4标题，关羽，青龙偃月刀），（5，5标题，张飞，丈八蛇矛）；#删除delete from msg where id=2；#查select * from msg; 查询整张表内容select id，title from msg; 查询id,title这两列select id，title from msg where id2； 查id2的内容发现的一小问题语句打错以后应该退出本语句，再继续打新的语句 也可以打c，提出本语句如何破解数据库的密码：1. 通过任务管理器或者服务管理，关掉mysql（服务进程）2. 通过命令行+特殊参数开启mysqldMysqld -skip-grant-tables3此时，mysqld服务进程已经打开，并且，不需要权限检查。4. mysql uroot 无密码登陆服务器5 修改权限表 A: use mysql; B: update user set password=password(11111)where user=root; C: flush privileges;6. 通过任务管理器，或服务管理，关掉mysqld服务进程7. 再次通过服务管理，打开mysql服务即可用修改后的新密码登陆、增删改查案例过程分析：用户注册表单收集,提交数据,注册页面收集到表单的数据后,形成insert语句,user表插入该条数据,用户注册完成.前台用户中心,用户新昵称,根据新昵称和用户id,形成update语句完成昵称修改.管理员后台点击会员列表,此时,网页形成select 语句,查询出所有会员的数据,完成会员的查看.管理员后台选中某用户并且删除,捕捉此用户的id,根据用户id形成相应的delete语句,执行delete语句,完成用户的删除.增删改查基本语法学习增：insertInsert 3 问1 插入哪张表？2 插入哪几列？3 这几列分别插入什么值？Insert into 表名 insert into 表名(列1，列2列n) Values values(值1，值2.值n) (值1，值2.值n)值与列，按顺序，一一对应特殊：insert语句 允不允许不写列名 答：允许如果没有声明列名，则默认插入所有列因此，值应该与全部列，按顺序一一对应例：建一张工资登记表2 插入部分列3插入所有列注: 文中的set names gbk;是为了告诉服务器，客户端用的是gbk编码，防止乱码4 插入所有列的简单改：update语句 Update4问改哪张表？改哪几列的值?分别改为 什么值？在哪些行生效？语法：Update 表明Set列1=新值1，列2=新值2，列n=新值n.Where expr例：删：delete Delete 2问从哪张表删除数据？要删除那些行？语法：Delete from 表名 where expr例：仿ecshop 建表：1：安装ecshop(打开gd扩展)2：安装phpMyAdmin#建木瓜库Create database mugua charset utf8;#选中木苦Use mugua;#创建商品表Create table goods (Goods_id int primary key auto_increment,cat_id smallint not null default 0,goods_sn char(15) not null default ,goods_name varchar(30) not null default ,click_count mediumint unsigned not null default 0,brand_id smallint not null default 0,goods_number smallint not null default 0,market_price decimal(7,2) not null default 0.00,shop_price decimal(7,2) not null default 0.00,all_time int unsignet not null default 0)charset utf8;#把ecshop的商品数据导入到木瓜库insert into mugua.goodsselectgoods_id,cat_id,goods_sn,goods_name,click_count,brand_id,goods_number,market_price,shop_price,add_timefrom shop.goods;#建栏目表create table category (cat_id smallint primary key auto_increment,cat_name varchar(30) not null default ,parent_id smallint not null default 0)charset utf8;#把ecshop的栏目数据导入到木瓜库insert into mugua.categoryselectcat_id,cat_name,parent_idfrom shop.category;#建品牌表create table brand(brand_id smallint primary key auto_increment,brand_name varchar(30) not null default )charset utf8;#把ecshop的品牌数据导入到木瓜库insert into mugua.trandselectbrand_id,brand_namefrom shop.trand;查：select 5种子句u Where 条件查询u group by 分组u having 筛选u order by 排序u limit 限制结果条数where:各种条件查询场合常用运算符大于 !=或不等于 =等于 =小于等于 in在某集合内 between在某范围内in (值1，值2.值n),等于值1n任意一个都行（in在某集合内）例：select goods_id,cat_id,goods_name from goods where cat_id in (4,5);Between 值1 and 值2，表示在值1和值2之间(between在某范围内)例：select goods_id,goods_nmae,shop_price from goods where shop_price between 2000 and 3000;not或！ 逻辑非 or或| 逻辑或 and或& 逻辑与select goods_id,goods_name,shop_price from goods where shop_price =3000 and shop_price =500 and shop_price 像select goods_id,goods_name from goods where goods_name like 诺基亚%；select goods_id,goods_name from goods where goods_name like 诺基亚N_；%-通配任意字符_-通配单个字符group:常用语统计场合 max : 求最大 min : 求最小 sum : 求总和 avg : 求平均 count:求总行数查询每个栏目下面最贵的商品价格Select cat_id,max(shop_price) from goods group by cat_id;查询所有行Select count(*) from goods;#列名当变量看查询出本店每个商品比市场价格低多少钱？Select goods_id,goods_name,market_price shop_price from goods;查询每个栏目下面积压的货款Select cat_id,sum(shop_price * goods_number) from goods group by cat_id;#可以给列或计算结果取别名，用asSelect cat_id,sum(shop_price * goods_number) as hk from goods group by cat_id;Having:having与where异同点:having与where类似,可筛选数据 where后的表达式怎么写,having就怎么写where针对表中的列发挥作用,查询数据having针对查询结果中的列发挥作用,筛选数据#where 对表起作用 having是对结果进行筛选查询出本店价格比市场低多少钱，并且把低200元以上的商品选出来。Select goods_id,goods_name,market_price shop_price as sheng from goods having sheng 200;同上题，只不过查第3个栏目下比市场价格低200元以上的商品Select goods_id,cat_id,market_price shop_price as sheng from goods where cat_id=3 having sheng 200;查询积压货款超过2W元的栏目，以及该栏目积压的货款Select cat_id,sum(shop_price * goods_number) as hk from goods group by cat_id having hk 20000;练习：设有成绩表stu 如下：姓名科目分数张三数学90张三语文50张三地理40李四语文55李四政治45王五政治30试查询两门及两门以上不及格同学的平均分Select name ,sum(score =2;Order by：各种排序场合可以根据字段来排序根据字段可以升序排，也可以降序排列默认是升序排列可以按多字段排序， order by 列1 desc/asc,列2 desc/asc可用字段名“Desc” 来声明按降序排列显示声明升序排列，可用asc来声明栏目3下商品价格降序排列Select goods_id,cat_id,goods_name,shop_price from goods where cat_id=3 order by cat_id desc（降序）/asc(升序);limit 在语句的最后，起到限制条目的作用Limit offset,NOffset:偏移量N: 取出条目Offset,如果不写，则相当于 limit 0,N例：取出价格第4到第6高的商品Select goods_id,goods_name,shop_price from goods order by shop_price desc limit 3,3;5个子句是有顺序要求的：where,group,having,order by,limit建立一张临时表Create table g2 like goods; goods表结构导入g2Insert into g2 select * from goods order by cat_id asc,shop_price desc;导入数据Truncate g2; 清空表良好的理解模型Where 表达式，把表达式放在行中，看表达式是否为真列：理解成变量，可以运算取出结果：可以理解成一张临时表子查询：子查询就是在原有的查询语句中，嵌入新的查询,来得到我们想要的结果集。一般根据子查询的嵌入位置分为，where型子查询,from型子查询Where 型子查询:把内层查询的记过作为外层查询的比较条件 典型语法:select * from tableName where colName = (select colName from tbName where .)where colName in (select colName from tbName where .)典型题：查询最大商品，最贵商品查询出最新一行商品（以商品编号最大为最新，用子查询实现）select goods_id,goods_name from goods where goods_id=(select max(goods_id) from goods);每个栏目最贵的商品Select goods_id,cat_id,goods_name,shop_price from goods where shop_price in (select max(shop_price) from goods group by cat_id);From 型子查询:把内层的查询结果当成临时表，供外层sql再次查询给临时表加一个别名 as典型语法:select * from (select * from tableName where .) where.典型题：查询栏目下最新/最贵商品试查询两门及两门以上不及格同学的平均分(where、from子查询)select name,avg(score) from stu where name in (select name from (select name,sum(score1) as tmp) group by name;Exists 型子查询：外层sql查询所查到的行代入内层sql查询,要使内层查询能够成立 。查询可以与in型子查询互换,但效率要高.典型语法:select * from tablenamewhere exists(select * from tableName where .)典型题：查询有商品的栏目Select cat_id,cat_name from category where exists (select * from goods where goods.cat_id = category.cat_id);Union:联合作用: 把2次或多次查询结果合并起来要求:两次查询的列数一致推荐:查询的每一列，相对应得列类型也一样可以来自于多张表多次sql语句取出的列名可以不一致，此时，以第1个sql的列名为准如果不同的语句中取出的行，有完全相同的（每个列的值都相同）那么相同的行将会合并（去重复）.如果不去重复，可以加all来指定 union all如果子句中有order by,limit,须用（）,推荐放到左右子句之后，即 ：对最终合并的结果来排序。在子句中，order by 配合limit使用才有意义、如果order by 不配合limit使用，会被语法分析器优化分析时，取出. 想取第4栏目的商品，价格降序排列，还想取第5栏目的商品，价格也降序排列（select goods_id,cat_id,goods_name,shop_price from goods where cat_id = 4 order by shop_price desc） union （select goods_id,cat_id,goods_name,shop_price from goods where cat_id = 5 order by shop_price desc） order by shop_price desc;取第3个栏目价格前3高的商品和第4个栏目价格前2高的商品,union来实现(Select goods_id,cat_id,goods_name,shop_price from goods where cat_id = 3 order by shop_price desc limit 3) union (Select goods_id,cat_id,goods_name,shop_price from goods where cat_id = 4 order by shop_price desc limit 2)例：create table ta( Id char(1),Name int);create table tb(Id char(1),Name int);Insert into taValues(a,5),(b,10),(c,10),(d,10),Insert into tbValues(b,5),(c,10),(d,20),(e,99),想要的结果a ,5b ,15c ,25d ,30e ,99select id,sum(num) from (select * from ta union all select * from tb) as tmp group by id;集合相乘，笛卡尔积其实就是两个几个的完全组合问：设集合A有M个元素，M个元素各不相同 设集合B有N个元素，N个元素各不相同A*B,得到的积，有M*N个元素，不可能重复表A，有9行，表B有10行量表相乘有9*10,90行左连接，右连接，内连接左连接：leftSelect 列1列2列N fromtableA left join tableBon tableA 列 = tableB 此处表连接成一张达标，完全当成普通表看where group,having 照常写右连接：rightSelect 列1列2列N fromtableA right join tableBon tableA 列 = tableB 此处表连接成一张达标，完全当成普通表看where group,having 照常写内连接：innerSelect 列1列2列N fromtableA inner join tableBon tableA 列 = tableB 此处表连接成一张达标，完全当成普通表看where group,having 照常写左右链接：以左表为准，去右表找匹配数据，找不到匹配，用null补齐如何记忆：1. 左右连接可以互相转化2. 可以把右连接转化为左连接来使用（并用左连接代替右连接，兼容性好一些）A站在B的左边 = B站在A的右边A left join B = B right join A内连接：查询左右表都有的数据 即：不要左/右中NULL的那一部分内连接是 左，右连接的交集思考：能否查出左右连接的并集呢？答：目前不能，目前的mysql不支持外连接. Outer join可以用union来达到目的.练习题：1：goods表，category表，branda表，3表连接查询，得出如下字段Goods_idCat_idCat_nameBrand_idBrand_nameGoods_nameSelectGoods_id,goods.cat_id,cat_name,brand_id,brand_name,goods_nameFromGoods left join categoryOn goods.cat_id = category.cat_id;Left join brandOn goods.brand_id = brand.brand_id;create table m (mid int primary key auto_increment,hid int,gid int,mres varchar(10),matime date)charset utf8;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,传智联队)；#1:先把顺序搞对Select hid,mres,gid,matime from m;#2:Select hid,t1.tname as hnmae,mres,gid,t2.tname as gname,matimeFromM 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;例：要从goods,category 表取数据 然后，在两张M*N的过程中，筛选，以什么条件筛选，答 cat_id相同1：连接接上表2：连接条件报价单SelectGoods_id,goods.cat_id,cat_name,goods_name,shop_priceFromGoods left join categoryOn goods.cat_id = category.cat_id;Create table boy(Name char(3),Flower char(5)charset utf8;Insert into boyValues(林书豪,玫瑰),(刘翔,桃花),(周杰伦,茉莉花),(犀利哥,玫瑰),(刘德华,狗尾巴花);Create table girl(Name char(3),Flower char(5)charset utf8;Insert into girlValues(艾薇儿,玫瑰),(居里夫,桃花),(芙蓉姐,茉莉花),(凤姐,茉莉花),(林志玲,荷花);Select boy.*,girl.*FromBoy left join girlOn boy.flower = girl.flower;性别籍贯性别年龄简介张三安徽男23所谓建表：就是声明列的过程数据是以文件的形式放在硬盘（也有放在内存里的）列：不同的列类型占得空间不一样选列的原则：够用，又不浪费create table 表名 (列1 列属性 默认值,列2 列属性 默认值,.列n 列属性 默认值)engine = 存储引擎 charset = 字符集列类型学习Mysql三大列类型数值型整形： tinyint/smallint/mediumint/int/bigint小数型： Float(D,M),decimal(D,M)字符串型 Char(M) Varchar(M) Text 文本类型日期/时间型 Date 日期 Time 时间 Datetime 日期时间型 Year 年类型整形系列所占字节与存储范围的关系定性：占字节月多，存储范围越大下图：是具体的数字分析整形系统的可选参数：XXint(M) unsigned zerofill例：age tinyint(4) unsigned 或者 stunum smallint(6) zerofillUnsigned:代表此列为无符号类型，会影响到列的存储范围（范围从0开始）（不加unsigned,则代表该列默认是有符号类型，范围从负数开始）列可以声明默认值,而且推荐声明默认值Not null default 0;Zerofill：代表0填充，即：如果该数字不足参数M位，则自动补0，补够M位。1. 如果没有zerofill属性，单独的参数M，没有任何意义2. 如果设置某列为zerofill,则该列已经默认为unsigned,无符号类型小数型：float(M,D),decimal(M,D)float(M,D):浮点小数，代表可存储一共M位（不含小数点），其中，小数点后D位decimal(M,D)：定点小树，M,D的意义同上.区别：decimal比float精度更高，适合存储货币等要求精确的数字.见下例：Mysql字符串类型Char 定长类型Char(M),M代表宽度,(即：可容纳的字符数),0=255之间例：char(10),则能输入10个字符.Varchar 变长类型 varchar(M),M代表宽度，（即：可容纳的字符数），0=M=65535(以ascii字符为例，utf822000左右)类型：类型宽度可存字符实存字符（i=M）实占空间利用率CharMMiMi/M=100%VarcharMMii字符+（1-2）字节i/(i+1-2)9999-12-31Time类型：典型格式 hh:mm:ss时间类型：-838:59:59-838:59:59Datetime类型 典型格式：1989-05-06 14:32:08日期时间类型 范围：1000-01-01 00:00:00-9999-12-31 23:59-59注意：在开发中，很少用日期时间类型来表示一个需要的精确到秒的列原因：虽然日期时间类型能精确到秒，而且方便查看.用时间戳来表示时间戳：用int来存储是1970-01-01 00:00:00 到当前的秒数一般存注册时间，商品发布时间等，并不是用datetime存储，而是用时间戳.因此datetime虽然直观，但计算不便.而用int型 存储时间戳，方便计算，对于显示来说，也可以方便的格式化建表练习:给班级的同学建个档案表有如下信息姓名 年龄 Email手机号 简介 毕业薪水 入学日期姓名：char(3)年龄：tinyint unsignedemail:varchar(30)tel:char(11)intro:varchar(1000)salary:decimal(7,2)riqi:datecreate table php(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 1800.68,riqi date not null default 2012-03-13)charset utf8;列的增删改：回顾建表语句create table 表名 (列名称 列类型 列属性默认值, -列声明列名称 列类型 列属性默认值,列名称 列类型 列属性默认值)charset = utf8/gbk增加列：Alter table 表名 add 列声明增加的列默认是在表的最后的一列可以用after 来声明新增的列再哪一些后面Alter table 表名 add 列声明 after flower.如果新增放在最前面，怎么办？Alter table 表名 add 列声明 first例 alter table boy add height tinyint unsigned not null default 149;修改列：Alter table 表名 change 旧被改变的列名 列声明例 alter table boy change height height smallint not null default 180;删除列：Alter table 表名 drop 列名；例 alter table boy drop id;视图:view查询每个栏目最贵的商品-Select goods_id,goods_name,shop_price from goods order by cat_id asc,shop_price desc查询结果，当成一张表看如果某个查询结果出现的非常频繁，也就是，拿这个结果当做进行子查询出现的非常频繁，Create table g2 like goods;Inser into g2 select上面两句，是想保存一个查询结果到表里面，供其他查询用视图定义:视图是由查询结果形成的一张虚拟表视图的创建语法:Create view 视图名 as select 语句视图的删除语法:Drop view为什么要视图？答：1.可以简化查询2.可以进行权限控制把表的权限封闭，但是开发相应的视图权限，视图里只开放部分数据3. 大数据分表时可以用到比如表的行数超过200万行时，就会变慢可以把一张的表的数据拆成4张表来存放News表Newsid,1,2,3,4News1,news2,news3,news4表把一张表的数据分散到4张表里，分散的方法很多，最常用可以用id，取模来计算Id%4+1 = 1,2,3,4比如 $_GETid=17,17%4+1 = 2，$tableName = news.2Select * from news2 where id = 17;还可以用视图，把4张表形成一张视图Create view news as select from n1 union select from n2 union视图的修改Alter view as select xxxxxxx视图与表的关系视图是表的查询结果，自然表的数据改变了，影响视图的结果视图改变了呢？0:视图增删改也会影响表1:但是，视图并不总是能增删改的答:视图的数据与表的数据 一一对应时.可以修改对于视图insert 还应注意视图必须包含表中没有默认值的列视图的algorithmAlgorihm = merge/temptable/undefinedMerge:当引用视图时，引用视图的语句与定义视图的语句合并Temptable:当引用视图时，根据视图的创建语句建立一个临时表Undefined:未定义，自动，让系统帮你选Merge,意味着视图只是一个规则，语句规则，当查询视图时，把查询视图的语句（比如where那些）与创建时的语句where子句等合并，分析.形成一条select语句例:创建视图语句: Create view g2 as select goods_id,cat_id,goods_name,shop_price From goods order by cat_id asc,shop_price desc;查询视图的语句：Select * from g2 group by cat_id;最终执行的语句：Select goods_id,cat_id,goods_name,shop_price from goods group by cat_id order by cat_id asc,shop_price desc;而temptable是根据创建语句瞬间创建一张临时表然后查询视图的语句从该临时表查询数据Create altorethm=temptable view g2 as select goods_id,cat_id,goods_name,shop_price From good