MySQL数据库常用操作

1. 数据库服务器(软件)

(1). 数据库软件特点：所有其它软件所没有

①. 永久海量存储数据

②. 高速查询

(2). 数据库服务器分类

①. 网状数据库

②. 树型数据库

③. 关系型数据库

④. NOSQL数据库:Redis

2. 什么是关系型数据库

关系型数据库以横行竖列的方式保存数据的数据库

关系型数据库层次: 数据库软件(mysql)=>库=>表=>行=>列

3. mysql之父: 马丁，启源于MYSQL AB(瑞典)=>SUN=>Oracle

(1). 主版本: 企业版(收费)社区版(免费)

(2). 小版本:5.0 、5.1 、5.2 、5.4 、5.5 、5.6 、5.7

如果开发全新网站项目用新版本 ，如果二次开发选旧版本

4. 软件下载

(1). https://www.oracle.com /官网

https://www.mysql.com/downloads/

(2). 搜狐镜像

http://mirrors.sohu.com/ 网址

mysql windows(64/32)(zip/exe)

mysql-5.6.34-win32.msi (32)

5. 使用软件包XAMPP(包含:mysql/php/apache/..)

(1). 双击xampp图标

(2). 点击 apache [start]启动、点击 mysql [start]启动

(3). 点击 右侧[shell]

(4). 输入命令mysql -uroot -p

①. #mysql 指令专门进入数据库系统

②. #-u 指定用户名

③. #root root是mysql软件最高级别用户

④. #-p root用户密码

(5). 退出软件#exit或点击右上角[X]

6. SQL(结构化查询语言)专用于(增删改查)数据库中数据语言，使用sql两种方式:

(1). 交互模式(学习)

一行一行执行指令用户输入一个sql指令,mysql执行一个指令

(2). 脚本模式(项目)

把所有sql指令保存在一个.sql文件中，一次执行在mysql中执行

7. SQL语句的分类

(1). DDL=>数据定义指令

CREATE/DROP/ALTER/TUNCATE

(2). DML=>数据操作指令

INSERT/UPDATE/DELETE

(3). DQL=>数据查询指令

SELECT

(4). DCL=>权限指令

GRANT/REVOKE

8. CREATE 指令功能创建(库)创建表

(1). 创建数据表之前应先创建库

①. 创建库

语法：CREATE DATABASE shop

②. 查看库是否创建成功

语法：SHOW DATABASES

(2). 创建库之后创建表

①. 使用库创建表

语法：use shop

②. 创建表

语法：CREATE TABLE 表名(

列名1 列类型,

列名2 列类型,

);

列数据类型(常用列类型)

int 整型(年龄) 范围-21亿~21亿

varchar(10) 字符串(10个字符)

double(10,2) 浮点(小数)总长10位其中2位小数

datetime 日期和时间

boolean 布尔类型

bigint 大整型

分析表商品表

A. 商品编号 int 1 2 3 5

B. 商品名称 varchar(20)

C. 商品时间 datetime

D. 商品价格 double(10,2)

③. 查看表是否创建成功

语法：SHOW TABLES

表格创建实例如下

use shop

CREATE TABLE product(

id INT,

name VARCHAR(20),

ctime DATETIME,

price DOUBLE(10,2)

);

SHOW TABLES

(3). 向创建好的表中添加数据

表名：student

id no name age

1 wsl001 张至东 19

2 wsl002 张小龙 18

3 wsl003 求伯君 17

①. 向所有列添加数据

语法：INSERT INTO 表名 VALUES(列值1,列值2,列值3,列值4...);

INSERT INTO student VALUES(1,'wsl001','赵大旭',19);

INSERT INTO student VALUES(2,'wsl002','张小东',18);

INSERT INTO student VALUES(3,'wsl003','李文华',17);

②. 向部分列添加数据

语法：INSERT INTO 表名(列名1，列名2) VALUES (列值1,列值2);

示例：INSERT INTO student (id,no) VALUES (4,'wsl004');

③. 日期类型 '2017-04-12' 字符串

注意：now()函数(当前日期时间) ：

INSERT INTO product VALUES (10,'book','2010-10-10',100);

INSERT INTO product VALUES (20,'js','2017-04-12',99);

INSERT INTO product VALUES (30,'css',now(),98);

④. 验证:

语法：SELECT * FROM student ;

(4). 创建时应注意以下事项

①. 库名/表名/列名使用英文不要数字或特殊字符开头，中文与空格无法使用

②. 通常sql指令大写，库名、表名、列名小写

③. 字符串类型列加单引号

④. 小心中文/(英文)单引号

⑤. 小心日期写法

⑥. SQL指令结束使用分号

⑦. 出错处理:如何处理常见错误，语法错误:SQL syntax

9. 删除记录(行)(恢复很困难)

语法：DELETE FROM 表名 WHERE 条件;

条件： = <><= != <>

示例：DELETE FROM product WHERE id = 10;

DELETE FROM product WHERE price = 99.99;

DELETE FROM product WHERE name = 'book';

10. 更新记录(行)

语法：UPDATE 表名 SET 列名1=新值1,列名2=新值2 WHERE 条件;

示例：UPDATE student SET age = 16 , name='大大，WHERE id = 1;

11. 查询记录(行)

语法：SELECT 列名 FROM 表名

FROM=>表名

WHERE=>条件

ORDER BY 列名 ASC=>升序

ORDER BY 列名 DESC=>降序

示例：SELECT * FROM 表名;

SELECT id , name , sal , sal*1.2 FROM user;

12. 组函数

(1). COUNT(列名)

统计满足条件几列

SELECT COUNT(*) FROM student_table;

(2). MAX(列名)

计算满足条件最大值

(3). MIN(列名)

计算满足条件最小值

(4). SUM(列名)

计算满足条件求和

(5). AVG(列名)

计算满足条件平均值

13. SQL语句综合练习

(1). 删除数据库lenovo如果存在的话

DROP DATABASE IF EXISTS lenovo;

(2). 创建数据库lenovo编码设置utf8

CREATE DATABASE lenovo;

DEFAULT CHARACTER SET utf8;

注意:html/css/js/php utf-8，mysql 中为utf8

(3). 进入lenovo库中：USE lenovo;

(4). 创建部门表 dept (编号,部门名称,该部门雇员人数)

CREATE TABLE dept(

Id INT,

name VARCHAR(20),

empcount INT

);

(5). 加三行记录 dept

INSERT INTO dept VALUES(1,'account',1);

INSERT INTO dept VALUES(2,'manager',2);

INSERT INTO dept VALUES(3,'software',3);

(6). 创建员工信息表 emp(编号,雇员名称,性别,工资,生日,所属部门编号)

CREATE TABLE emp(

Id INT,

name VARCHAR(20),

sex VARCHAR(1),

sal DOUBLE(10,2),

rdate DATETIME,

did INT

);

(7). 添加六行记录 emp

INSERT INTO emp VALUES(1,'大大','M',1000001,'2010-01-01',1);

INSERT INTO emp VALUES(2,'小明','M',1000002,'2010-01-01',2);

INSERT INTO emp VALUES(3,'老高','M',1000003,'2010-01-01',2);

INSERT INTO emp VALUES(4,'小李','M',1000004,'2010-01-01',3);

INSERT INTO emp VALUES(5,'周星','M',1000005,'2010-01-01',3);

INSERT INTO emp VALUES(6,'daxu6','M',1000006,'2010-01-01',3);

(8). 试着删除一个员工记录，并且修改对应部门人数

DELETE FROM emp WHERE id=6;

UPDATE dept SET empcount = 2 WHERE id = 3;

(9). 查询雇员表中所有记录

SELECT * FROM emp;

(10). 查询雇员表,编号和姓名

SELECT id, name FROM emp;

(11). 查询雇员表所有记录，工资降20%结果

SELECT sal*0.8 FROM emp;

(12). 查询雇员表所有记录，条件工资大100002

SELECT * FROM emp WHERE sal > 100002;

(13). 查询雇员表所有记录，条件入职时间2010-10-10

SELECT * FROM emp WHERE rdate = '2010-01-01';

(14). 查询雇员表所有记录，按工资升序和降序分别排序

SELECT * FROM emp ORDER BY sal;

SELECT * FROM emp ORDER BY sal DESC;

(15). 查询雇员表所有记录，编号等于1并且姓名等于'大大'

SELECT * FROM emp WHERE id = 1 AND name = '大大';

(16). 查询出所有部门表记录 dept

SELECT * FROM dept;

(17). 查询出所有雇员表记录 emp

SELECT * FROM emp;

(18). 查询出10号部门所有员工记录

SELECT * FROM emp WHERE did = 10;

(19). 查询出10号部门所有员工数量

SELECT count(id) FROM emp，WHERE did = 10;

(20). 查询出10号部门所有员工工资总和

SELECT sum(sal) FROM emp WHERE did = 10;

(21). 查询雇员表名称等于'小明'所有信息

SELECT * FROM emp WHERE name='小明';

(22). 查询雇员表所有工资大于1000000员工信息

SELECT * FROM emp WHERE sal > 1000000;

(23). 查询所有财务部(account)员工信息

SELECT did FROM dept　WHERE name = 'account';

SELECT * FROM emp　WHERE did = 1;

以上可简写为:

SELECT * FROM emp WHERE did = (SELECT id FROM dept WHERE name = 'account');

(24). 查询所有财务部员工平均工资

SELECT avg(sal) FROM emp WHERE did = (SELECT id FROM dept WHERE name = 'account');

(25). 查询财务部性别等于M员工记录

SELECT * FROM emp WHERE did = (SELECT id FROM dept WHERE name = 'account')　AND sex = 'M';

(26). 查询财务部姓名以d开头员工记录

SELECT * FROM emp WHERE did = (SELECT id FROM dept WHERE name = 'account')　AND name LIKE 'd%';

%为通配符 表示0个或多个字符串

14. 多表查询语法

(1). 几张表，表名(user / news)

(2). 表别名　user=>u / news=> n

(3). 表之间关系 = != <><=

通过id关联 u.id=n.uid

(4). 示例(注意拼接与空格)

$sql = " SELECT n.title,n.ctime,u.uname"; 　

$sql .=" FROM u,n"; 　　

$sql .=" WHERE u.id=n.uid"; 　　　

15. mySQL分页

SELECT * FROM product LIMIT start,count

start 启动记录行 0

count 查询几行记录 8

(1). SELECT * FROM product LIMIT 0,8;

(2). SELECT * FROM product LIMIT 8,8;

(3). SELECT * FROM product LIMIT 16,8;

页数 启始记录

1＝＝>0 (1-1)*8=0

2＝＝>8 (2-1)*8=8

3＝＝>16 (3-1)*8=16

n＝＝> (n-1)*8

16. GROUP 聚类

(1). SELECT * FROM student_table GROUP BY class;

查出student_table表中按class来分组的数据

(2). SELECT class FROM student_table GROUP BY class;

查出student_table表中按class来分组的数据中class字段值

(3). SELECT class ,COUNT(class) FROM student_table GROUP BY class;

查出student_table表中按class来分组的数据的class字段值，统计每个class重复的次数。

(4). SELECT class ,AVG(score) FROM student_table GROUP BY class;

查出student_table表中按class来分组的数据的class字段值，计算每个class中所有人数的平均成绩score。

(5). SELECT class,MAX(score),MIN(score) FROM student_table GROUP BY class;

查出student_table表中按class来分组的数据的class字段值，计算每个班的最高分数和最低分数。

(6). SELECT name ,SUM(price) FROM sales_table GROUP BY name ORDER BY SUM(price) ASC

查出sales_table 表中按name 来分组的数据的name 字段值，计算每个name组 的总价格，并以总价格进行升序排列

17. 子句之间的顺序

WHERE=>GROUP=>ORDER=>LIMIT

示例如下：

SELECT class,COUNT(class) FROM student_table

WHERE score>60

GROUP BY class

ORDER BY COUNT(class) DESC

LIMIT 2;

18. 问题：窗口中文不正确原因是什么？

库/表中的字体是utf8而窗口是gbk

解决:SET NAMES GBK;(临时将文字转换gbk显示)

操作步骤:

(1). 将所有指令写脚本文件中

(2). 打开mysql执行指令窗口

(3). 输入二个命令:

SET NAMES utf8;

SOURCE d:/tao/ajax/day/database.sql