首页  思维导图  详情

mysql数据库基础

2018-06-29 10:39:11   1  举报





AI智能生成

mysql数据库基础

数据库

作者其他创作

大纲/内容

MySQL数据简介

MySQL的优势

运行速度快

使用成本低

可移植性强

适用用户广

MySQL的存储引擎

MyISAM

MyISAM类型表文件

*.frm：表结构定义文件

*.MYD：数据文件

*.MYI：索引文件

InnoDB 支持事务处理

InnoDB类型表文件

*.frm：表结构定义文件

ibdata1文件

Memory

CSV

MySQL安装目录

主要文件夹

bin

include

lib

MySQL配置文件

my.ini：记录MySQL配置信息，可修改配置内容，如端口号，字符集等

安装版路径：C:/ProgramData/MySQL/MySQL Server 5.5/Data/

MySQL常用命令

启停数据库

net start mysql

net stop mysql

连接MySQL

mysql –h服务器主机地址 –u 用户名 –p密码

连接本机

mysql –u root – p

MySQL数据库安装

下载地址https://dev.mysql.com/downloads/mysql/

SQL（Structured Query Language）:结构化查询语言

DML

(数据操作语言) 用来操作数据库中所包含的数据
INSERT

UPDATE

DELETE

插入INSERT

语法
INTO 表名 [(字段名列表)] VALUES (值列表);

INSERT INTO `student`(`loginPwd`,`studentName`,`gradeId`,`phone`,`bornDate`)

VALUES('123','黄小平',1,'13956799999','1996-5-8');

语法

INSERT INTO 新表（字段名列表）

VALUES(值列表1),(值列表2),……,(值列表n);

INSERT INTO `subject`(`subjectName`,`classHour`,`gradeID`)

VALUES('Logic Java',220,1),('HTML',160,1),('Java OOP',230,2);

将查询结果插入新表

CREATE TABLE 新表(SELECT 字段1，字段2…… FROM 原表);

如新表已存在，不能重复创建

CREATE TABLE `phoneList`(

SELECT `studentName`,`phone`

FROM `student`);

更新UPDATE

语法

UPDATE 表名

SET 字段1=值1,字段2=值2,…,字段n=值n

[WHERE 条件];

UPDATE student SET sex = ‘女’;

UPDATE student SET address = ‘北京女子职业技术学校家政班’

WHERE address = ‘北京女子职业技术学校刺绣班’;

删除

语法

DELETE FROM 表名 [WHERE条件];

DELETE FROM student WHERE studentName = ‘王宝宝’;

TRUNCATE TABLE student;

语法

TRUNCATE TABLE 表名;

TRUNCATE语句删除后将重置自增列，表结构及其字段、约束、索引保持不变
执行速度比DELETE语句快

DDL

(数据定义语言) 用于创建和删除数据库对象等操作
CREATE

DROP

ALTER

数据库操作

创建数据库

CREATE DATABASE myschool;

查看数据库列表

SHOW databases;

选择数据库

USE myschool;

删除数据库

DROP DATABASE myschool;

表操作

创建表

CREATE TABLE `student`(

`studentNo` INT(4) NOT NULL COMMENT '学号' PRIMARY KEY,

`loginPwd` VARCHAR(20) NOT NULL COMMENT '密码',

`studentName` VARCHAR(50) NOT NULL COMMENT '学生姓名',

`sex` CHAR(2) DEFAULT '男' NOT NULL COMMENT '性别',

`gradeId` INT(4) UNSIGNED COMMENT '年级编号',

`phone` VARCHAR(50) COMMENT '联系电话',

`address` VARCHAR(255) DEFAULT '地址不详'COMMENT '地址', `bornDate` DATETIME COMMENT '出生时间',

`email` VARCHAR(50) COMMENT'邮件账号',

` identityCard` VARCHAR(18) UNIQUE KEY COMMENT '身份证号'

) COMMENT='学生表';

查看表是否存在

USE myschool;

SHOW tables;

删除表

USE myschool;

DROP TABLE IF EXISTS `student`;

修改表

DROP TABLE IF EXISTS `demo01`;

#创建表

CREATE TABLE `demo01`(

`id` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,

`name` VARCHAR(8) NOT NULL

);

#修改表名

ALTER TABLE `demo01` RENAME `demo02`;

#添加字段

ALTER TABLE ` demo02 ` ADD `password` VARCHAR(32) NOT NULL;

#修改字段

ALTER TABLE ` demo02 ` CHANGE `name` `username`CHAR(10) NOT NULL;

#删除字段

ALTER TABLE ` demo02 ` DROP `password`;

#修改表名

ALTER TABLE 旧表名 RENAME [TO] 新表名;

#添加字段

ALTER TABLE 表名 ADD 字段名数据类型 [属性];

#修改字段

ALTER TABLE 表名 CHANGE 原字段名新字段名数据类型 [属性];

#删除字段

ALTER TABLE 表名 DROP 字段名;

主外键操作

添加主键

ALTER TABLE 表名 ADD CONSTRAINT 主键名

PRIMARY KEY 表名（主键字段）;

ALTER TABLE `grade` ADD CONSTRAINT `pk_grade` PRIMARY KEY `grade`(`gradeId`);

添加外键

ALTER TABLE 表名 ADD CONSTRAINT 外键名

FOREIGN KEY（外键字段）

REFERENCES 关联表名（关联字段）;

ALTER TABLE `student` ADD CONSTRAINT fk_student_grade FOREIGN KEY(`gradeId`)

REFERENCES `grade` (`gradeId`);

DQL

(数据查询语言) 用来对数据库中的数据进行查询 SELECT

查询语法

SELECT <列名|表达式|函数|常量>

FROM <表名>

[WHERE <查询条件表达式>]

[ORDER BY <排序的列名>[ASC或DESC]];

SELECT `studentNo`,`studentName`,`phone`,`address`,`bornDate`

FROM `student`

WHERE `gradeId` = 1

ORDER BY `studentNo`;

数据查询-列别名

SELECT `studentNo` AS 学生编号,`studentName` AS 学生姓名,

`address `AS 学生地址

FROM `student`

WHERE `address` <> '河南新乡‘;

数据查询-空行、常量列

SELECT `studentName` FROM `student` WHERE `email` IS NULL;

聚合函数

AVG() 返回某字段的平均值

COUNT() 返回某字段的行数

MAX() 返回某字段的最大值

MIN() 返回某字段的最小值

SUM() 返回某字段的和

字符串函数

CONCAT()

语法

CONCAT(str1, str1...strn)

作用

字符串连接

举例

SELECT CONCAT('My','S','QL');

返回：MySQL

INSERT()

语法

INSERT(str,pos,len,newstr)

作用

字符串替换

举例

SELECT INSERT('这是SQL Server数据库',3,10,'MySQL');

返回：这是MySQL数据库

LOWER()

语法

LOWER(str)

作用

将字符串转为小写

举例

SELECT LOWER('MySQL');

返回：mysql

UPPER()

语法

UPPER(str)

作用

将字符串转为大写

举例

SELECT UPPER('MySQL');

返回：MYSQL

SUBSTRING()

语法

SUBSTRING(str,num,len)

作用

字符串截取

举例

SELECT SUBSTRING( 'JavaMySQLOracle',5,5);

返回：MySQL

时间日期函数

CURDATE()

作用

获取当前日期

举例

SELECT CURDATE();

返回：2016-08-08

CURTIME()

作用

获取当前时间

举例

SELECT CURTIME();

返回：19:19:26

NOW()

作用

获取当前日期和时间

举例

SELECT NOW();

返回：2016-08-08 19:19:26

WEEK(date)

作用

返回日期date为一年中的第几周

举例

SELECT WEEK(NOW());

返回：26

YEAR(date)

作用

返回日期date的年份

举例

SELECT YEAR(NOW());

返回：2016

HOUR(time)

作用

返回时间time的小时值

举例

SELECT HOUR(NOW());

返回：9

MINUTE(time)

作用
返回时间time的分钟值

举例

SELECT MINUTE(NOW());

返回：43

DATEDIFF(date1,date2)

作用

返回日期参数date1和date2之间相隔的天数

举例

SELECT DATEDIFF(NOW(),

'2008-8-8');

返回：2881

ADDDATE(date,n)

作用

计算日期参数date加上n天后的日期

举例

SELECT ADDDATE(NOW(),5);

返回：2016-09-02 09:37:07

数学函数

CEIL(x)

返回大于或等于数值x的最小整数

SELECT CEIL(2.3)

返回：3

FLOOR(x)

返回小于或等于数值x的最大整数

SELECT FLOOR(2.3)

返回：2

RAND()

返回0~1间的随机数

SELECT RAND()

返回：0.5525468583708134

排序 ORDER BY

默认升序 asc

降序desc

LIMIT子

LIMIT 起始位置,行数

分页limit 语句写在所有语句的末尾

子查询

子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询

将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个

IN子查询

子查询返回不止1个值时，

使用in

NOT IN子查询

EXISTS子查询

子查询有返回结果： EXISTS子查询结果为TRUE

子查询无返回结果： EXISTS子查询结果为FALSE,

外层查询不执行

NOT EXISTS子查询

分组查询

GROUP BY子句

用来分组 WHERE 子句的输出

HAVING子句

用来从分组的结果中筛选行

多表连接查询

内连接(INNER JOIN)

外连接

左外连接 (LEFT JOIN)

右外连接 (RIGHT JOIN)

DCL

(数据控制语言) 用来控制数据库组件的存取许可、存取权限等
GRANT

COMMIT

ROLLBACK.

远程登录授权

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY 'ok' WITH GRANT OPTION;

FLUSH PRIVILEGES;

MySQL数据类型

数值类型

TINYINT 非常小的数据有符值： -27 ~ 27-1

无符号值：0 ~ 28-1 1字节

SMALLINT 较小的数据有符值： -215 ~ 215-1

无符号值： 0 ~ 216-1 2字节

MEDIUMINT 中等大小的数据有符值： -223 ~ 223-1

无符号值： 0 ~ 224-1 3字节

INT 标准整数有符值： -231 ~ 231-1

无符号值：0 ~ 232-1 4字节

BIGINT 较大的整数有符值： -263 ~263-1

无符号值：0 ~264-1 8字节

FLOAT 单精度浮点数 ±1.1754351e -38 4字节

DOUBLE 双精度浮点数 ±2.2250738585072014e -308 8字节

DECIMAL 字符串形式的浮点数 Decimal（M，D） M+2个字节

字符类型

CHAR[(M)] 定长字符串 M字节

VARCHAR[(M)] 可变字符串可变长度

TINYTEXT 微型文本串 0~28–1字节

TEXT 文本串 0~216–1字节

日期类型

DATE YYYY-MM-DD，日期格式 1000-01-01~ 9999-12-31

DATETIME YY-MM-DD hh:mm:ss: 1000-01-01 00：00：00 ~9999-12-31 23：59：59

TIME hh:mm:ss: -835:59:59 ~ 838:59:59

TIMESTAMP YYYYMMDDHHMMSS 1970年某时刻~2038年某时刻，精度为1秒

YEAR YYYY格式的年份 1901~2155

MySQL字段的约束

非空约束 NOT NULL 字段不允许为空

默认约束 DEFAULT 赋予某字段默认值

唯一约束 UNIQUE KEY(UK) 设置字段的值是唯一的

允许为空，但只能有一个空值

主键约束 PRIMARY KEY(PK) 设置该字段为表的主键

可唯一标识该表记录

外键约束 FOREIGN KEY(FK) 用于在两表之间建立关系，

需要指定引用主表的哪一字段

自动增长 AUTO_INCREMENT 设置该列为自增字段

默认每条自增1

通常用于设置主键

事务、存储过程

事务

MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

事务用来管理 insert,update,delete 语句

ACID

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务控制语句

BEGIN或START TRANSACTION；显式地开启一个事务；

COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；

ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

子主题

数据库设计

子主题