MySQL(一)

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的产品)

数据库的基本操作

1. 创建：
   CREATE DATABASE Xchen;
2. 查看：
   SHOW DATABASES;
   SHOW CREATE DATABASE Xchen;
3. 修改：
   ALTER DATABASE Xchen CHARACTER SET gbk COLLATE gbk_bin;
4. 删除：
   DROP DATABASE Xchen;

数据表的基本操作

1. 创建：
   CREATE DATABASE Xchen;
   USE Xchen;
   CREATE TABLE Xgrade(
   id INT(11),
   name VARCHAR(20),
   grade FLOAT
   );
2. 查看：
   SHOW TABLES;
   SHOW CREATE TABLE Xgrade;
   SHOW CREATE TABLE Xgrade\G;
   DESCRIBE Xgrade;
   DESC Xgrade;
3. 修改：
   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

表的约束：防止数据表中插入错误的数据，维护数据表的完整性规则

1. 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)
   );
2. FOREIGN KEY ：
   外键约束
3. NOT NULL ：
   非空约束：值不能为NULL，可以有多个字段为NOT NULL。
   CREATE TABLE examle03(
   id INT PRIMARY KEY,
   name VARCHARA(20) NOT NULL,
   grade FLOAT
   );
4. UNIQUE ：
   唯一性约束：保证字段的唯一性，字段值不能重复出现
5. DEFAULT ：
   默认值约束：给指定字段指定默认值
   CREATE TABLE example04(
   id INT PRIMARY KET AUTO_INCREMENT,//自动增加设置
   stu_id INT UNIQUE,
   name VARCHAR(20) NOT NULL,
   grade FLOAT DEFAULT 0
   );

索引：好比新华字典的音序表，作用就是提高表中数据的查询速度。

1. 查看索引是否被使用：
   EXPLAIN SELECT * FROM example WHERE id=1\G;
2. 普通索引：INDEX/KEY
   CREATE TABLE example01(
   id INT,
   name VARCHAR(20),
   score FLOAT,
   INDEX(id)
   );
3. 唯一性索引：其字段的值必须是唯一的
   CREATE TABLE example02(
   id INT NOT NULL,
   name VARCHAR(20) NOT NULL,
   score FLOAT,
   UNIQUE INDEX unique_id(id ASC)
   );
4. 全文索引：只能在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;
5. 单列索引：
   CREATE TABLE example03(
   id INT NOT NULL,
   name VARCHAR(20) NOT NULL,
   score FLOAT,
   INDEX single_name(name(20))
   );
6. 多列索引：
   CREATE TABLE example03(
   id INT NOT NULL,
   name VARCHAR(20) NOT NULL,
   score FLOAT,
   INDEX multi_name(id,name(20))
   );
7. 空间索引：字段必须为NOT NULL
   4种空间数据类型：
   GEOMETRY/POINT/LINESTRING/POLUGON
   CREATE TABLE example04(
   id INT,
   space GEOMETRY NOT NULL,
   SPATIAL INDEX sp(space)
   ) ENGINE=MyISAM;

数据表已经存在的情况下：

1. 创建索引：
   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);
2. 修改索引：
   ALTER TABLE book INDEX index_id(bookid);
3. 删除索引：
   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 studentSET id=5,name=’boya’,grade=98;

更新数据：

UPDATE studentSET name=’caocao’,grade=50WHERE 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()BEGINSELECT * 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