Mysql数据库数据处理基础速成.doc

一、安装数据库可视化工具
Navicat for MySQL

二、新建一个链接

1.配置链接，确认连接成功 2.测试是否成功 3.提示成功 4.确定

三、操作数据库
1.选中要操作的数据库 2.选中查询 3.新建查询

四、操作数据库表
1.增 INSERT INTO
语法：
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );

例子
INSERT INTO `t_staff` (`staffName`, `sex`, `email`, `mobile`) VALUES ('系统管理员', NULL, '', '19911112222');
2.删 delete
语法：
DELETE FROM table_name [WHERE Clause]
例子：删除t_staff表里面的数据
delete from t_staff where staffName = ‘苗丹’
3.改 UPDATE
语法：
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

例子：
UPDATE `t_staff` SET `id`='21', `departmentId`=NULL, `userRoleId`='0', `staffCode`='dev', `password`='96e79218965eb72c92a549dd5a330112', `staffName`='系统管理员', `sex`=NULL, `email`='', `mobile`='19911112222', `phone`='', `age`=NULL, `birthday`=NULL, `idCard`='', `creatorId`=NULL, `createTime`='2017-03-14 13:49:00', `updatorId`='21', `updateTime`='2018-01-15 14:28:50', `status`='0', `token`=NULL, `serviceId`=NULL, `ts`='2018-02-26 11:24:25', `dr`='0', `lastlogintime`='2018-02-26 11:24:25', `authorizeid`='23', `authorizetime`='2017-09-08 16:51:54', `userstatus`='1', `orgtype`=NULL, `city`=NULL, `servicecount`='0', `qualification`=NULL, `qualificationaudit`=NULL, `invitermobile`=NULL, `score`=NULL, `totalmoney`=NULL, `hisscore`=NULL, `histotalmoney`=NULL, `cardmobile`=NULL, `bankname`=NULL, `bankno`=NULL, `bankcode`=NULL, `bindbankdate`=NULL WHERE (`id`='21');
4.查 select
语法：
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M] 控制查询条目数

1）查询某张表 t_staff为例
* :代表所有
如果只是单查其中某几个字段，select 后面跟字段名
select * from t_staff
2）带上条件的查询（where）
select * from t_staff where staffName = ‘苗丹’
3）查询固定的几个字段
select staffCode,staffName,mobile from t_staff where staffName = ‘苗丹’
4）控制条目数 limit
select * from t_staff LIMIT 0,10 – 从0开始，查10条数据

5.查询 where
语法：
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

6.排序 order by
语法：
SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]
例子：
select * from t_staff where staffName like '%苗%' and score >3 ORDER BY score desc

7.关联查询 left join

例子：
SELECT
*
FROM
t_staff
LEFT JOIN t_department ON t_staff.departmentId = t_department.id
WHERE
staffName LIKE '%苗%'