SQL基础操作
SQL是操作数据的语言
增加记录:
insert into 数据表名称(字段1,字段2,字段3....)values(值1,值2,值3.....)
查看表结构:desc 表名
inset into xxx('uid','name')value('2','dudu');
可简写为:
inset into xxx value('2','dudu'),('xx','xx');
删除记录:
delete from 数据表名称[where 条件]
where 条件如果省略就表示删除所有记录
delete from news; //删除所有的记录
delete from news where id>10; //删除id>10所有记录
delete from news where id>=10 and id<20; //删除id>10且id<20的记录
delete from news where author = ‘admin’ and id<100; //删除id<100且author = ‘admin’记录
修改记录:
update 数据表名称set 字段1=新值,字段2=新值,....[where 条件]
update news set title = ‘新标题’,content = ‘新内容’ where id=50;
update news set content = ‘新内容’ , title = ‘新标题’ where id=50;
比如update 表名 set name='dudu',age='21' where id=19;
查询记录:
select 字段列表|*from 数据表[from][order by排序][limit 限制输出]
字段字列表：查询某些字段的数据，各字段之间用逗号隔开，字段之间没有顺序。
*：表示显示所有列的数据。如：select * from news;
显示所有数据还有种办法:select * from user where 1;1就是ture
比如给age里面的年龄都加一岁:select *
select uid,name,age+1 from user; from 表名;
count()计算行数
select count(随便什么字符) from 表名;
avg()求平均函数
select avg(数据栏名) from 表名r;
sum()求总和
select sum(age) from user;
min()求最小
select min(age) from user;
max()求最大
select max(age) from user;
分组:group by
select max(字符) 这里可以加各类名字之类,这里也可以,就是句子而已,from 表名,比如这里可以加编号id group by 以什么分组
删选having
比如:某商品的价格-去批发价 as(取别名) XXXX from 表名
比如:编号,名称 as 自定义名称 from 表名 having sheng>200比如删选出差额大于200的商品
这个where是子查询
order by排序:xxxx asc从小到大desc从大道小
想其他数据也升降排序那么句子逗号隔开再写个,也可以一条解决
比如:表的条件比如编号,商品名 from 表名 where group by havibg order by limit;
limit：限制输出 一般个order by配合使用
limit N;N表示取出几条选几条
0.3表示前三 2,3表示前三到前五 最新0.1
where操作子查询:
select id,name,shop from 表名 where id这里我查的是库里最大的ID值的商品=(select id from 表名 order by id desc limit 0.1)
select id,name,shop from 表名 where id这里我查的是库里最大的ID值的商品=(select max(id) from 表名)
from操作子查询:
1.查看20-25的表内容:select id,name,shop from 表名 where id between 20 and 25;
from子查询会生成一个临时表:
有s1列为123 s2列 456 想让56改为10 12且表内容是s1 23 s2 10 12
select*from (select s1 s2*2 from 表名)as 新临时表名 where s1>2;
exists子查询;类似于判断比如我想取出的两个表里有相同商品的
select*from 表名 where exists (select from 表名.id=表名.id);
题目: 查询价格大于2000元的商品及其栏目名称 1.价格大于2000元的商品 select goods_id,goods_name,cat_id,shop_price from goods where shop_price>2000; 2.sql执行之后,循环取出cat_id; $sql = 'select goods_id,goods_name,cat_id,shop_price from goods where shop_price>2000'; $rs = mysql_query($sql); $data = array(); while($row = mysql_fetch_assoc($rs)) { $data[] = $row; } froeach ($data as $k=>$v) { $sql = 'select cat_name from category where cat_id='.$v['cat_id']; } 1条sql语句 -> N条数据 -> N条查询 我们需要一条sql语句查询出来,那有没有可能从一张表中就能查出来? 不可能,查询的字段是分别在两张表中的,肯定需要从两张表中查询出来 -> 连接查询
有两张表 ID有两项是一样的想匹配那么用内连接
select boy.hid,boy.bname,girl.hid,girl.gname from boy inner join girl on boy.hid=girl.hid;
inner join是要链接的表 on后面是条件
左连接右连接类似,只是调个方向, 1.所有男士站出来,找到自己的另一半,没有的,以 NULL 补齐.
select 表.编号,boy.bname,girl.hid,girl.gname from boy left(right) join girl on boy.hid=girl.hid;
union; 把多条查询合并成一条
select goods_id,goods_name,cat_id,shop_price from goods where cat_id=3
union
select goods_id,goods_name,cat_id,shop_price from goods where cat_id=4
比如 a表和b表有c重名的编号那么合并后c会消失那么union改为union all
id=3和4的全取出来了 合并了一条表单
unsigned 无符号 列的值从0开始不能为负
零填充zerofill 这是设置数字大小宽度tinyint(M),用于符号,编码等设置固定宽度的数字最大不能是1000000反正这个肯定不行
比如学号1-->001
alter 数据表 表名 add sn tinyint(5) zerofill;
indert into 表名 sn value(1);
创建table表:
create table 表名称
id int primary key auto_increment-->第二行我又个id列是int类型 有个主键primary key auto_increment自增长的
name char(20) charset=utf8;第二行表头名字是name 长度为20个字符 定义翻译表(有中文用utf8)
什么是主键:主键就是不能重复的比如我声明了id是主键那么我再插入1那么就报错了
我上面代码申明方式1还有第二种单独申明:id int,primary key(id)
什么是自增长:就是id数值自己1+1+1加上去
要定义自增长必须要有索引以上写在一起其实已经有索引了如果是第二种方法那么这样写:create table xxx;id int auto_increment;name char(20),key 键的名字(id)charset=utf8;这里想自增至少是个键 补充这里key是个键其实也可以加其他的自定义设为普通索引
1.某些列不插入内容,值是多少? NULL
2.not null 又是干吗的? 1)NULL 查询不便 我们查询的时候需要用 is null 或者is not null 2)NULL 的索引效率不高,会影响我们的查询速度 所以,实用中,避免列的值为 NULL
3.如何避免列的值不为null呢,这个列我没有插入具体的值 插入默认值
4.如何设置默认值? default 总结--声明列 NOT NULL default 默认值 create table t14 ( id int not null default 0, name char(10) not null default '' ); --->显示的是null为NO
插入数据 insert into t14 values (1,'listi'); insert into t14 (id) values (2); select * from t14; select * from t14 where name=''; ---->显示的是id是1 name是哪个空字符串其他没了 记得加自增长哦
MySQL的时间搓:timestamp
比如:create table dt1;
有这么几些列:id int;
time timestamp
inset into dt1(id) values(1)
decimal(M,D) 定点 1)创建 float(9,2),decimal(9,2)并测试 create table t5( f float(9,2), d decimal(9,2) ); 2)给t5表插入数据 insert into t5 values (1234567.23,1234567.23); 3)查询t5表的内容 select * from t5; 我们可以清晰的看到,f列,我们插入的数据跟显示的数据是不同的 float/double , 有精度损失 decimal 定点型,更精确 定点型,是将整数部分和小数部分用分别用数字来存储的,所以定点型更精确
char（8），8 的意思是这个字段占的字符，不是字节。存储汉字是8个字，存储英语也是8个字母。
字节宽度 0到255，可以存ascii。utf8的宽度是0到85，存的长度不够时，用空格填充，取出时，去掉右边的空格。
varchar（2000）的字节宽度 0到65535，可以存ascii。utf8的0到22000个字左右。varchar不去尾部空格。
text不支持全文索引；
text 不能设置默认值
参数：
startrow从指定的行数起，开始返回数据。
pagesize返回的记录数。
where：指查询的条件。
SELECT * FROM news WHERE id<100;
SEECT * FROM news WHERE id<100 and hits<100;
SELECT * FROM news WHERE id=100 OR hits<50;
ORDER BY：字段排序。
语法：ORDER BY 字段 [ASC|DESC]
ASC表示“升序”排列(默认)，DESC表示降序排列。
SELECT * FROM news ORDER BY id DESC; //id降序排列
SELECT * FROM news ORDER BY hits; //hits升序排列
LIMIT 0,10; //从第0行起，返回10条记录
LIMIT 10,10; //从第10行起，返回10条记录
LIMIT 20,10; //从第20行起，返回10条记录
导入SQL文件，用于测试使用
Saixinjituan.sql
注意：导入SQL文件时，要先创建数据库，然后再导入数据。
创建的数据库的字符集必须是UTF8，否则会乱码
1：删除列
ALTER TABLE 【表名字】 DROP 【列名称】
2：增加列
ALTER TABLE 【表名字】 ADD 【列名称】 INT NOT NULL COMMENT '注释说明'
3：修改列的类型信息
ALTER TABLE 【表名字】 CHANGE 【列名称】【新列名称（这里可以用和原来列同名即可）】 BIGINT NOT NULL COMMENT '注释说明'
4：重命名列
ALTER TABLE 【表名字】 CHANGE 【列名称】【新列名称】 BIGINT NOT NULL COMMENT '注释说明'
5：重命名表
ALTER TABLE 【表名字】 RENAME 【表新名字】
6：删除表中主键
Alter TABLE 【表名字】 drop primary key
7：添加主键
ALTER TABLE sj_resource_charges ADD CONSTRAINT PK_SJ_RESOURCE_CHARGES PRIMARY KEY (resid,resfromid)
8：添加索引
ALTER TABLE sj_resource_charges add index INDEX_NAME (name);
9: 添加唯一限制条件索引
ALTER TABLE sj_resource_charges add unique emp_name2(cardnumber);
10: 删除索引
alter table tablename drop index emp_name;
1 表创建完毕后,能否添加 1 个列? 删除 1 个列? 修改一个列? 新增一个列,或者删除修改一个列,这属于 DDL 操作,数据库定义语言 区分数据的增删改,插入数据是指表中的数据,不会影响到表的结构 alter table 表名 add 列名 列类型 列属性... (新列 默认在表的最后) alter table 表名 add 列名 列类型 列属性... after 列名 (新列出现指定列后) alter table 表名 add 列名 列类型 列属性... first (新列为第 1 列) alter table 表名 change 旧列名 新列名 新类型 列属性.... alter table 表名 modify 列名 新属性.... alter table 表名 drop [column] 列名 用到36课综合建表案例的sql语句,使用学生的一份建表语句 注意: 建表时的列名称一律小写 因为大小写在linux和windows下,有时候敏感有时候不敏感,所以在建表时一律小写 2.添加一个身高列,默认新增列追加在表的最后 alter table user1 add height tinyint unsigned not null default 0 desc user1; 3.我想身高列放在体重列后面 删除身高列 column 列 alter table user1 drop column height; 在增加身高列,放在weight列的后面 alter table user1 add height tinyint not null default 0 after weight; desc user1; 4.现在人的身高越来越高,255的tinyint已经不够存了 我们需要改变列类型,改成 smallint change 可以将表名一起修改了 alter table user1 change height shengao smallint not null default 0; modify 也可以修改列 modify 跟 change 有什么不同呢? modify 不可以修改列名,当我们不需要修改列名的时候,就用modify alter table user1 modify shengao tinyint not null default 0; 5.查看表的结构 desc 表名 desc user1;
视图:view 一张表有多行多列 而我不想看 我就生成一个结果集来展现
create view 视图名字 as (select语句...)
好处 权限控制 简化查询(就是说比如一列数据都+100)
就是储存你的select语句
1.查看所有表 show tables; 视图也会显示出来
2.查看表结构 desc 表名/视图名
3.查看建表过程 show create table 表名
4.查看建视图过程 show create view 视图名
5.删除表: drop table 表名
6.删除视图: drop view 视图名
7.查看库中的表信息 show table status; 如果表信息太多,看不清楚 可以加上\G 竖着显示 show table status \G \ 通过查看表的详细信息,我们也能区分是否为视图 如果是视图: Comment: VIEW 如果不是视图: Comment
8.查看某张表详细信息 show table status where name='表名';
9.改表名 rename table oldName to newName
10.清空表数据 truncate (相当于删除表,再重建) delete from 表名 truncate 表名 这两个的区别
1.查看字符集 show variables like '%charact%';
1.将客户端字符集改为 utf8; 但是我们的黑窗口是gbk编码的 插入内容,我们会发现乱码 1)建表 create table test3 ( name char(10) not null default '' )charset=utf8; 2)改客户端字符集并插入数据 set character_set_client = utf8; insert into test3 values ('中国人民解放军'); select * from test3; set character_set_client = gbk;
2.将数据库返回的数据 改为 utf8编码的 客户端是gbk编码的,返回utf8编码的数据,客户端肯定不认识 所以还是会乱码 set character_set_results = utf8; insert into test3 values ('中国人民解放军'); select * from test3; set character_set_results = gbk;
3.中间的连接器,使用utf8和gbk都是可以的 character_set_connection 因为连接器始终需要 gbk=>utf8 因为始终都需要存储成utf8 注意:连接器不能使用较小的字符集,如ascii 在转换的时候会丢失一部分编码 而丢失的这部分编码是不可逆推回来的.
4.如果 3 者都一致,可以用简写 set names gbk/utf8;
5.我们知道,谁能连接数据库,谁就可以称为客户端 有时候我们用 .php文件 去连接数据库 我们的php就是客户端,那我们的php页面,编码统一使用的utf8; 我们只需要在 .php 文件里, set names utf8; 即可保证不乱码 而有的学员是 在开发 php 的时 set names utf8; 在 命令行 黑窗口中还是 set names utf8; 这不就乱码了么,两个不同的客户端,各使各的参数就可以了
6.杜绝乱码的,检查的几个步骤 1)php 文件是 utf8 2)html 页面的 meta 信息 也是utf8 3)建表时,也是utf8 create table () charset=utf8; 4)php页面连接数据库时 set names utf8; 检查这四步,之后,如果仍然乱码,则继续检查这四步
索引就是目录 比如1-100我要查100次那么索引是1-10那么只查10次就行了
key普通索引
unique key唯一索引
索引的类型 mysql中 有以下几种索引 1)key, 普通索引 纯粹就是帮我们组织数据,提高查询速度 2)unique key, 唯一索引 起到2个作用 1)是加快你的查询速度 2)是帮你约束数据,怎么个约束法? 这个数据不可能重复了,unique 中文 唯一的意思 我们看一下key和unique key的使用 create table t22 ( name char(10), email char(20), key name(name), unique key email(email) ); key name(name) 的意思是,针对name的列,创建索引,索引的名称也叫name 我们可以在所有的列声明完毕之后,再加索引 查看t22的表结构 desc t22; 给t22表插入两条相同的数据 insert into t22 values ('lisi','lisi@qq.com'); insert into t22 values ('lisi','lisi@qq.com'); 当我们插入第二条数据的时候,会报错 这个时候,它在维护索引的时候,会检查,这个email已经存在了,就不允许我们插入这个相同email这列值,这个是 unique key 的一个特点 比如我们在创建一个网站的时候,用户名,email这两个字段是不许重复的,我们可以用unique key ,既能提高查询速度,又能起到约束的作用 3)primary key , 主键索引 一张表中,只可能有一个 primary key 我们之前已经使用过了 create table t23 ( id int unsigned auto_increment primary key ); 4)fulltext 全文索引 注意: 中文环境下,全文索引无效 为什么呢? 因为英文,每个单词是分开的,它将每个单词分为别类,建立索引 你查这行中的任意一个单词,都有可能查询到这行数据 而中文的每个字是连在一起的,它无法区分每个字 所以就导致,在中文环境下,全文索引是无效的 在中文环境下,需要 分词+索引才有效 一般用第三方解决方案,如sphinx 5)设置索引长度 设置索引长度是为了优化索引 建索引时,可以只定索引列的前一部分的内容 比如前10个字符 例: email列 后面都都是 @qq.com,@163.com, 等 @后面的内容区分度不高,都是类似的内容 那我们可不可以 将 @ 前的10个字符内容给截取下来建索引 -> 可以 create table t24 ( name char(10), email char(20), key name(name), unique key email(email(10)) ); 注意: 这个并不是限制的email长度,只能填10个字符 而是索引只取10个字符 插入大于 10个字符 长度的email insert into t24 values ('lisi','1234567892@qq.com'); 6)多列索引 有的时候,一列索引还查询不出来什么效果 多列索引,就是把2列或多列的值,看成一个整体,然后建索引 例: 我们注册国外网站的会员时,会让我们分别注册 firstname,lastname 姓和名 分开存姓名,可以方便以后查询不同姓氏的注册用户 但大部分使用还是将姓名连接在一起查询的 create table t25 ( xing char(10), ming char(10), key xm(xing,ming) ); insert into t25 values ('zhang','san'); desc t25; 查看表的详细索引信息 show index from t25; 注意: 以下两个select 多列索引是可以发挥作用的 select * from t25 where xing='zhang' and ming='san'; select * from t25 where xing='zhang'; 下面这个select 索引是不能发挥作用的 select * from t25 where ming='san'; 我们可以同 explain sql语句 来显示mysql如何使用索引来处理select语句以及连接表 explain select * from t25 where xing='zhang' and ming='san' \G explain select * from t25 where xing='zhang' \G possible_keys: xm 有可能使用到的key 是 xm key: xm explain select * from t25 where ming='san' \G possible_keys: NULL 有可能使用到的key 是 null key: xm 既然索引没有发挥作用,为什么key还是xm呢? 例: 一片文章,有标题,有内容 标题:<<静夜思>>为索引 -> 对应的内容 在第9页 1是我们看到内容在第九页,就去第9也看内容了 2是我们找到index索引标题,而不去看它的内容 从索引直接返回数据,不用走MYD文件去查询 下面这个语句可以使用上索引 因为mysql没那么傻,它会分析语义 explain select * from t25 where ming='san' and xing='zhang' \G 怎样区分它是否能使用上索引呢? 左前缀 一个数据,从左到右 abcdefg.. 我们给书建目录,目录是有顺序的 我们们前半部分能记得 abcd..后面记不得了,它可以帮我们定位大致的范围 那如果前面记不住,后面记得 ...efg ,那如何定位 前面的数据千变万化 有可能是zzzzefg,或这aaaaefg 等,根据右半部分无法定位 这个就是健使用的左前缀规则,从左往右侧查,索引还能部分发挥作用,左侧是确定的,相对好查询 右侧固定,左侧不知道,那则是千变万化,无法使用索引 详解: http://www.zixue.it/thread-11966-1-1.html 7)冗余索引 就是在某个列上,可能存在多个索引 比如 xm(xing,ming) 我们单查ming,索引不发挥作用 ming(ming) 给ming也加上索引,单独查ming的时候,也是有索引发挥作用的,那如果 xing和ming都查,对于ming则会后两个索引发挥作用 在开发中,为了提高查询速度,我们也经常会用到冗余索引 create table t26 ( xing char(10), ming char(10), key xm(xing,ming), key ming(ming) ); show index from t26 \G explain select * from t26 where ming='san' \G possible_keys: ming key: ming 冗余索引在开发中,有时候是必要的.
1.查看一张表的索引 show index from 表名 show create table 表名 查看建表语句,后面也会显示索引 2.删除索引 删除哪张表的哪个索引? 删除 alter table 表名 drop index 索引名 或 drop index 索引名 on 表名 3.添加索引 alter table 表名 add index/unique xm(xing,ming); 4.添加主键索引,不需要起名字,因为是唯一的 alter tables 表名 add primary key(id[主键名称]); 5.删除主键索引 alter table 表名 drop primary key;
