MySQL数据库
By Xchen 20160624
数据库:
- 按照数据结构来组织、存储、管理数据的仓库,可以看作是电子的文件柜。
数据库分类:
- 文件数据库
- 网络数据库
- 关系数据库:SQL语言
- 未来趋势:NOSQL(Mongodb、redis)
SQL语言包括
- 数据定义语言DDL:
CREATE
ALTER
DROP- 数据操作语言DML:
INSERT
UPDATE
DELETE- 数据查询语言DQL:
SELECT- 数据控制语言DCL:
GRANT
REVOKE
COMMIT
ROLLBACK
常见的数据库产品:
- Oracle(甲骨文公司)
- SQL Server(微软公司)
- DB2(IBM公司)
- MongoDB(10gen公司)
- MySQL(瑞典MySQL AB公司开发,现在是oracle的产品)
数据库的基本操作
- 创建:
CREATE DATABASE Xchen;- 查看:
SHOW DATABASES;
SHOW CREATE DATABASE Xchen;- 修改:
ALTER DATABASE Xchen CHARACTER SET gbk COLLATE gbk_bin;- 删除:
DROP DATABASE Xchen;
数据表的基本操作
- 创建:
CREATE DATABASE Xchen;
USE Xchen;
CREATE TABLE Xgrade(
id INT(11),
name VARCHAR(20),
grade FLOAT
);- 查看:
SHOW TABLES;
SHOW CREATE TABLE Xgrade;
SHOW CREATE TABLE Xgrade\G;
DESCRIBE Xgrade;
DESC Xgrade;- 修改:
ALTER TABLE xgrade RENAME Xgrade;
ALTER TABLE Xgrade CHANGE name username VARCHAR(20);
ALTER TABLE Xgrade MODIFY id INT(20);
ALTER TABLE Xgrade ADD age INT(20);
DROP TABLE Xgrade age;
ALTER TABLE Xgrade MODIFY username VARCHAR(20) FIRST;
ALTER TABLE Xgrade MODIFY id INT(20) AFTER grade;
DROP TABLE Xgrade;
数据库数据类型
- 整型:
TINYINT-1B
SMALLINT-2B
MEDIUMINT-3B
INT-4B
BIGINT-8B- 浮点型
FLOAT-4B
DOUBLE-8B
DECIMAL(M,D)-(M+2)B保留D位小数- 日期时间类型:
YEAR
DATE
TIME
DATETIME
TIMESTAMP- 字符串和二进制类型:
CHAR
VARCHAR
BINARY
VARBINARY
BOLB
TEXT
ENUM
SET
BIT
表的约束:防止数据表中插入错误的数据,维护数据表的完整性规则
- PRIMARY KEY :
主键约束:唯一标识表中记录,好比身份证,只能有一个主键,值不能重复且不能为NULL。
CREATE TABLE example01(
id INT PRIMARY KEY,
name VARCHAR(20),
grade FLOAT
);
CREATE TABLE example02(
id INT,
name VARCHAR(20),
grade FLOAT,
PRIMARY KEY(id,name)
);- FOREIGN KEY :
外键约束- NOT NULL :
非空约束:值不能为NULL,可以有多个字段为NOT NULL。
CREATE TABLE examle03(
id INT PRIMARY KEY,
name VARCHARA(20) NOT NULL,
grade FLOAT
);- UNIQUE :
唯一性约束:保证字段的唯一性,字段值不能重复出现- DEFAULT :
默认值约束:给指定字段指定默认值
CREATE TABLE example04(
id INT PRIMARY KET AUTO_INCREMENT,//自动增加设置
stu_id INT UNIQUE,
name VARCHAR(20) NOT NULL,
grade FLOAT DEFAULT 0
);
索引:好比新华字典的音序表,作用就是提高表中数据的查询速度。
- 查看索引是否被使用:
EXPLAIN SELECT * FROM example WHERE id=1\G;- 普通索引:INDEX/KEY
CREATE TABLE example01(
id INT,
name VARCHAR(20),
score FLOAT,
INDEX(id)
);- 唯一性索引:其字段的值必须是唯一的
CREATE TABLE example02(
id INT NOT NULL,
name VARCHAR(20) NOT NULL,
score FLOAT,
UNIQUE INDEX unique_id(id ASC)
);- 全文索引:只能在CHAR/VARCHAR/TEXT类型的字段上使用,只有MyISAM引擎支持
CREATE TABLE example03(
id INT NOT NULL,
name VARCHAR(20) NOT NULL,
score FLOAT,
FULLTEXT INDEX fulltext_name(name)
)ENGINE=MyISAM;- 单列索引:
CREATE TABLE example03(
id INT NOT NULL,
name VARCHAR(20) NOT NULL,
score FLOAT,
INDEX single_name(name(20))
);- 多列索引:
CREATE TABLE example03(
id INT NOT NULL,
name VARCHAR(20) NOT NULL,
score FLOAT,
INDEX multi_name(id,name(20))
);- 空间索引:字段必须为NOT NULL
4种空间数据类型:
GEOMETRY/POINT/LINESTRING/POLUGON
CREATE TABLE example04(
id INT,
space GEOMETRY NOT NULL,
SPATIAL INDEX sp(space)
) ENGINE=MyISAM;
数据表已经存在的情况下:
- 创建索引:
CREATE INDEX index_id ON book(bookid);
CREATE UNIQUE INDEX unique_id ON book(bookid);
CREATE INDEX single_id ON book(bookid);
CREATE INDEX multi_id ON book(bookid,name);
CREATE FULLTEXT INDEX fulltext_info ON book(bookinfo);- 修改索引:
ALTER TABLE book INDEX index_id(bookid);- 删除索引:
ALTER TABLE book DROP INDEX index_idl;
DROP INDEX index_id book;
添加数据:
CREATE DATABASE chapter03;
USE chapter03;
CREATE TABLE student(
id INT(4),
name VARCHAR(20) NOT NULL,
grade FLOAT
);
INSERT INTO student(id,name,grade)
VALUES(1,’zhangsan’,98);
SELECT * FROM student;
另外下面的方式也可以添加数据:
INSERT INTO student
SET id=5,name=’boya’,grade=98;
更新数据:
UPDATE student
SET name=’caocao’,grade=50
WHERE id=1;
删除数据:
DELETE FROM student//部分或全部删除
WHERE id=11;
DELETE FROM student;
TRUNCATE TABLE student;//全部删除
单表查询:
SELECT id,name,grade,gender FROM student;
SELECT * FROM student;
WHERE id>4;
WHERE id IN(1,2,3);
WHERE 字段名 [NOT] BRTWEEN 值1 AND 值2;
WHERE 字段名 IS [NOT] NULL;
SELECT DISTINCT gender FROM student;//指定字段值不能重复
WHERE 字段名 [NOT] LIKE ‘匹配字符串’; 百分号% 下划线_
WHERE 表达式1 AND 表达式2…
WHERE 表达式1 OR 表达式2…//AND的优先级大于OR
聚合函数:针对指定列数据
COUNT() //返回某列行数
SUM() //返回某列值的和
AVG() //返回某列值得平均值
MAX() //返回某列的最大值
MIN() //返回某列的最小值
对查询结果排序:
ORDER BY grade ASC/DESC;
GROUP BY grade;
和聚合函数一起使用:
SELECT COUNT(*),gender FROM student GROUP BY gender;
SELECT * FROM student LIMIT 4,4;
函数:
数学函数 | 字符串函数 | 条件判断函数 | 日期和时间函数 | 加密函数 |
---|---|---|---|---|
ABS(x) | LENGTH() | IF() | CURDATE() | MD5() |
SQRT(x) | CANCAT() | IFNULL() | CURTIME() | ENCODE() |
MOD(x) | TRIM() | CASE | SYSDATE() | DECODE() |
CEILING(x) | REPLACE() | THEN | TIME_TO_SEC() | |
FLOOR(x) | SUBSTRING() | ADDDATE() | ||
ROUND(x) | REVERSE() | SBUDATE() | ||
TRUNCATE(x) | LOCATE() | DATE_FORMAT() | ||
SIGN(x) |
为表和字段取别名:
SELECT * FROM student AS s WHERE s.gender=’女’;
SELECT name AS stu_name FROM student;
多表操作:
外键:引用另一个表的一列或多列。被引用表的列应该具有主键约束或唯一性约束;作用是建立和加强表数据之间的连接。
ALTER TABLE student ADD CONSTRAINT FK_ID FOREIGN KEY(gid) REFERENCES grade(id);
ALTER TABLE student DROP FOREIGN KEY FK_ID;
连接查询:
交叉连接
SELECT * FROM 表1 CROSS JOIN表1;
内连接
SELECT * FROM 表1 INNER JOIN 表2 ON 表1.关系字段=表2.关系字段;
外连接
分为左连接和右连接
子查询:
WHERE did IN()
WHERE EXISTS()
WHERE did>ANY()
WHERE did>ALL()
事务与存储过程
START TRANSACTION;开启事务
COMMIT;提交事务
ROLLBACK;回滚事务
事务隔离级别:级别从低到高
1. READ UNCOMMITED//读未提交
2. READ COMMITED//读提交
3. REPEATABLE READ//可重复读
4. SERIALIZABLE//可串行化
设置隔离级别:
SET SESSION TRANSACTION ISOLATION LEVER READ UNCOMMITED;
相关概念:
脏读:一个事务读取了另一个事务未提交的数据。
不可重复读:事务中两次查询结果不一致。原因是两次查询过程中其他事务更新了操作。
幻读:一个事务内两次查询中数据条数不一致。
存储过程:一条或多条SQL语句的集合。
CREATE PROCEDURE Pro()
BEGIN
SELECT * FROM student;
END;
CALL Pro();
DROP PROCEDURE Pro;
定义变量:
DELARE myvariable INT DEFAULT 100;
SET myvariable=60;
视图:一种虚拟表
CREATE VIEW view_stu AS SELECT math,chinese,math+chinese FROM student;
数据库的备份
mysqldump –uroot –p2976819 chapter08>c:/backup/chapter08.sql
数据库的还原:
mysql –uroot –p2976819 chapter08<c:/backup/chapter08.sql