Mysql （二）Mysql 数据库表增删改查
一、表操作1、【增加】
create table DBname.table_name(id int, namevarchar(255))engine=MyISAM charset=gbk;
2、【删除】
删除表
drop table table_name;
3、【修改】
修改表选项
alter table table_name engine=myisamcharset=utf8;//修改字符集
rename table Old_tablename to new_tablename;//修改表名
4、【查询】
show tables like '%_name'; //查看表
show create table table_name //查看建表语句
desc table_name //查看表结构 （describe）
show variables like 'character_set%';//展示以character_set开头变量
二、表字段操作
1、【增加】
增加表字段
alter table table_name add column 字段定义 //增加字段
alter table table_name add column> alter table table_name add column snvarchar(10) first;//在最形始增加sn字段、使用关键字first
2、【删除】
删除字段
alter table table_name drop columncolumn_name;
alter table table_name drop columnheight;//删除表table_name中height字段
3、【修改】
修改已有字段（修改字段定义）
alter table table_name modify columncolumn_name 新的定义;
alter table table_name modify column sn intafter name; //修改sn字段
修改字段
alter table table_name change column 原字段名 新字段名新字段定义;
alter table table_name change column snnew_sn varchar(30) after age;//修改字段
4、【查询】
查看表和表结构
show tables like '%_name'; //查看表
show create table table_name //查看建表语句
desc table_name //查看表结构 （describe）
三、表数据操作
1、【增加】
insert into 表名（字段列表） values(与字段相对的值列表);
insert into table_name('name','age','height')values(ssw,22,180);
2、【删除】
delete from 表名 where 条件;(删除是不可逆的)
delete 时、支持order by和limit来限制删除数据记录
delete from table_name where> delete from tb_student orderby heightdesclimit 3;
truncate tb_student;//清空表
3、【修改】
update 表名 set 字段=新值,字段n=新值nwhere 条件;（也可用order by 和limit限制）
update table_nameset name='php' where> 4、【查询】
select [字段表达式列表]from 表名
select 字段列表 from 表名 (*表示所有字段)
select * from table_name where>
select name from table_name where> select 加法逻辑关系别名(as)//select 运算
select 1+1; select 10>20; select 1 or 0;select 1+1 as a;
select * from tb1,tb2;//多表查询
select tb1.id as s_id, tb2.* from tb1,tb2;//字段别名多表查询
select tb1.idass_> where子句、查询条件子句：
关系：> >= <
selsect * from tb1 where>
select * fromtb1 where >
select * fromtb1 where >
select * fromtb1 where >
select * fromtb1 where > groupby 字段：分组查询
对查询结果（已经通过where子句过滤之后的数据），按照某个字段，进行分组！
合计函数：
count(): 统计记录数、可以是*和字段名
sum(字段表达式): 统让和、对某个字段求和、
avg(): 平均值
max(字段表达式)：最大值
min(字段表达式)：最小值
group_concat(字段表达式)：组内连接字符串
select count(*),idfromtb1 where 1groub by>
select sum(money),class_id from tb1groubby>
select avg(money),class_id from tb1groubby> select concat(‘It’ ,’is’,’test’);
分组排序：
ASC: 升序
DESC：降序
Selectcount(*),class_id from tb1 groupby > 多字段分组：
Select count(*),class_id,class_namefrom tb1 groub by > Having子句：条件子句、功能和where类似
Select *fromtb1 wheremoney>300;
Select *fromtb1 havingmoney>300;
Having和where区别：having的结果一定是 where 已经过滤之后的结果!having对结果进行二次处理
Select avg(age),class_idfromtb1 where1group by class_idhavingavg(age)>16;
Order by 排序子句
Order by字段名 ,[字段名,]//对结果进行排序的语句！可对多个字段排序
Select * fromtb1 orderby > 原则是，先按照第一个字段进行排序，如果字段值相同，则采用第二个，以此类推
Select class_idfromtb1 groupby >
Limit 子句：限制结果记录的子句、limitstart (起始位置),> Select *fromtb1 limit1,3;
执行顺序：字段表达式，from子句，where子句，group by子句，having子句，order by子句，limit子句
子查询语句：
select max(height)fromtb1;
select *fromtb1 where >
select *fromtb1 where > 子查询的两种分类依据：
依据子查询出现的位置！
where型子查询， 出现在where子句内！
from 型子查询， 出现在from子句内！
依据子查询的返回数据的格式！
标量子查询，返回值是一个数据，称之为标量子查询！
列子查询，返回一个列，
行子查询，返回一个行，
表子查询，返回的是一个二维表
Select * from tb1 where> 需要，先用一个查询，得到身高排序结果，再将该结果分组
Select* from (select * from tb1 order byheight desc) group by>
Select* from (select * from tb1 order byheight desc) as tmpgroup by> 列子查询:
Select * fromtb1 where sex=’girl’and > Select * fromtb1 where (height,money)=(selectmax(height),max(money)from tb1);
//找到，高富，最高并且最有钱！
exists型子查询:
select * from tb1 whereexists(select * from tb2where tb1.class_id = tb2.id);
连接查询，join:
连接多个表记录之间的连接！from 表名1join 表名2on 连接条件
Selectname,class_id,agefromtb_xue jointb_banon tb_xue.class_id=tb_ban.id;
需要不单从 学生表获取数据，还需要从 班级表获得数据
内连接，innerjoin
mysql默认的连接就是 inner join
select stu_name,class_id,class_name fromselect_student inner join select_class onselect_student.class_id=select_class.id;(可省略inner)
外连接，left join，right join
Select stu_name,class_id,class_name from select_student left join select_classon select_student.class_id = select_class.id;
join关键字前面的（左边的）左表，join关键字后边的（右边的）右表！
左外：如果出现左表记录连接不上右表记录的，左表记录会出现正在最终的连接结果内！而右表记录相应设置成NULL。
右外：如果出现右表记录连接不上左表记录的，右表记录会出现正在最终的连接结果内！而左表记录相应设置成NULL。
交叉连接，cross join: 相当于是 没有条件的内连接
自然连接，natural join: mysql，自动判断连接条件，帮助我们完成连接！
Select stu_name,class_namefromselect_class naturaljoinselect_student;
而自然连接也分内连接与外连接！
自然内连接：natural join
自然左外：natural left join
自然右外：natual right join
总结：
最终的效果只有：内，左外，右外！
交叉，特殊的内!
自然，相当于自动判断连接条件，完成内，左外，右外！
连接条件，on，using:
on，后面使用一个连接条件表达式！
using(连接字段)，要求使用同名字段进行连接！
Select class_name,stu_namefromtb_class innerjointb_student on tb_class.class_id=tb_student.class_id;
Select class_name,stu_namefromtb_class innerjoin tb_studentusing(class_id);
union查询，联合查询:
将多个查询的结果，并列到一个结果集合内！
(select stu_name,heightfromtb_student wheresex=’girl’order by height asc limit1000) union(selectstu_name ,heightfrom tb_studentwheresex=’box’ order by> union 的连接的两个子句，不要求实同表，只要求，列的数量相同！
union会在联合时：主动去掉相同的记录：此时，可以使用 all关键字加以修正：
select 1=1 unionall select 2;
select语句的选项:
distinct，取消相同的记录
select class_idfromtb_student;
select all >
select distinct > 四、编码
1、建库，建表，建字段 设置（数据库中的数据的编码）
2、PHP作为mysql服务器的客户端，设置的客户端编码和连接编码（set names gbk/utf8）
3、设置php返回给浏览器数据的编码，（Content-Type,header(),）
4、PHP文件本身保存的编码（文件编码，通过文本编辑器设置）
五、视图
视图：就是通过一条查询语句得到一个张虚拟表！因此，视图就是 select语句的结果
作用：简化查询的业务逻辑，隐藏真实的表结构。
语法：create view视图名字as 查询语句
Create viewview_nameas select*from tb_studentass leftjointb_class using(class_id);
Select * from view_namewhere id=22;
//取得每个班级最高的学生信息
Create viewview_studentas select*from view_studentorderby height desc;
Select * fromview_studentgroup by > 六、事务
事务：一组 SQL 的集合，要不集体都执行成功，要不集体都失败，
语法：
开启事务：starttransaction (可简写begin)
提交：commit(如果sql成功、则提交、将sql的执行结果保存到数据库里)
回滚：rollback (如果sql失败、则回滚、将sql的执行结果退到事务开始之前)
注：无论回滚还是提交，都会关闭该事务！（需要再次开启，才能使用）事务，只针对当前的连接生效！
事例：
Start transaction;
Update tb_studentsetmoney=money+1000where id=10;
Commit;
七、数据库备份
1、备份单个表
select 字段列表into outfile文件地址 from 表名 where 其他的select子句
select *intooutfile ‘d:/b.txt’
fields terminatedby ‘,’
lines terminatedby‘\r\n’
from tb_student;
还原:loaddatainfile filenameintotb_name;
2、备份一个库
Mysqldump -h127.0.0.1-P3306-uroot -p> d:/data_back.sql;//备份库
Mysqldump -h127.0.0.1-P3306 -uroot -p data_nametb_studnet >d:/tb_back.sql;//备份表
3、还原数据库
Mysql -h127.0.0.1-P3306-uroot -pdata_name sourced:/data_back.sql;(登录后还原)
3、忘记root密码
mysqld服务器程序，有一个选项，跳过权限认证选项！客户端登陆不用密码
my.ini里面要有这个选项、没有就手动填加 添加skip-grant-tables（改好密码就删除这段）
第一步：
重新开启mysqld服务、直接mysql就能进去了。
cmd>mysql
第二步：
更新mysql.user表root用户的password字段
Update mysql.usersetpassword=password(‘123456’)where user=’root’; 这个不行就用下面这个
update mysql.user set authentication_string=password('**') where user='**'; 第三步：
重启mysqld
八、PHP操作mysql服务器
php作为mysql服务器的客服端
php--链接认认证--发送sql--执行sql、生成结果--处理结果--关闭连接
1、【连接认证】
$_host = 'localhost';
$_port = '3306';
$_user = 'root';
$_pass = 'rootpass';
if(!$_link = mysql_connect("$_host:$_port",$_user, $_pass)){
die('连接失败！');
}
2、【向mysql服务器发送sql】
$_sql = 'show databases';
if(!$_result = mysql_query($_sql, $_link)){
echo'sql执行失败';
}
3、【处理返回的数据】
$_row = mysql_fetch_assoc($_result);
var_dump($_row);
结果集取出来的函数：
mysql_fetch_assoc() 函数从结果集中取得一行作为关联数组。
mysql_fetch_row() 函数从结果集中取得一行作为索引数组。
mysql_fetch_array() 函数从结果集中取得一行作为关联数组，或数字数组，或二者兼有
mysql_num_rows()函数返回结果集中行的数目。
注：任何有结果的sql操作，返回的都是结果集！结果集，就是一个二维表的结构！是一行行的记录组成！
4、【释放释源】
mysql_free_result($_result);结果集
mysql_close($_link);连接资源
页:
[1]