MySQL知识梳理（一）

一.MySQL基本概念

1.数据库(Database，检查DB)

(1)概念：长期存放在计算机内，有组织，可共享的大量数据的集合，是一个数据“仓库”。
(2)作用：保存管理数据

2.数据库分类

(1)关系型数据库(SQL):MySQL,Oracle,SQL Server，SQLite，DB2
(2)非关系型数据库(NOSQL):Redis,MongoDB

3.数据库管理系统(DATABASE Management System)

数据管理软件

4.结构化查询语句

(1)DDL(数据定义语言)：定义和管理数据对象；Create，drop，alter
(2)DML(数据操作语言)：操作数据：insert，update，delete
(3)DQL(数据查询语言)：查询数据：select
(4)DCL(数据控制语言)：管理数据库语言：grant，commit，rollback

二.数据库操作

1.连接数据库

mysql -h 服务器主机地址 -u 用户名 -p密码
修改登录密码
UPDATE USER SET PASSWORD=PASSWORD('新密码') WHERE USER='用户名';
FLUSH PRIVILEGES;

2.创建数据库

CREATE DATABASE[IF NOT EXISTS]数据库名;

3.删除数据库

DROP DATABASE[IF EXISTS]数据库名;

4.查看数据库

SHOW DATABASES

5.使用数据库

USE 数据库名;  

三.数据表操作

1.显示表结构

DESC 表名;

2.显示表创建语句

SHOW CREATE TABLE 表名;

3.创建数据表

CREATE TABLE[IF NOT EXISTS]`表名`
(字段名 列类型[属性][索引][注释])
[表类型][表字符集][注释];

(1)列类型

   1)数值类型：tinyint,smallint，mediumint，int，bigint，float，double，decimal
   2)字符串类型：char，varchar，tinytext，text
   3)日期和时间型数值类型：date，time，datetime，timestamp，year
   4)NULL值
   注：MySQL 还支持两种复合数据类型 ENUM 和 SET,它们扩展了 SQL 规范。
   虽然这些类型在技术上是字符串类型，但是可以被视为不同的数据类型。

(2)属性

   1)UNSIGNED:无符号
   2)ZEROFILL:不足位数用0来填充
   3)AUTO_INCREMENT:自动增长
     注：a. TRUNCATE 表名 : 直接清空所有数据，下次插入数据时auto_increment字段会自动从0开始
         b. DELETE FROM tbname不同的是delete只删除数据而不重置auto_incremnet.
         c. SET @@auto_increment_increment = 10;修改自动增长的步长（全局修改）

   4)NULL 和 NOT NULL
   5)DEFAULT:默认

(3)索引

1)索引分类

     a.主键索引(PRIMARY KEY):确保数据记录的唯一性
     b.唯一索引(UNIQUE):避免同一个表中某数据列中的值重复
     c.常规索引(INDEX):快速定位特定数据
     d.全文索引(FULLTEXT):只能用于MyISAM类型的数据表;只能用于char，varchar，text数据列类型;只适合大型数据集

2)创建索引

a.方式一：创建表，声明列属性时添加上

      CREATE TABLE test1(
              id INT(4) PRIMARY KEY,
              testno VARCHAR(10)UNIQUE,
              c VARCHAR(50),
              d VARCHAR(20),
              e TEXT,
              INDEX `index_c`(c,d),#组合索引：最左前缀
              FULLTEXT(e)
      )ENGINE=MYISAM;

b.方式二：创建表，将所有列都声明完毕后，再添加索引

      CREATE TABLE test2(
              id INT(4) ,
              testno VARCHAR(10),
              c VARCHAR(50),
              d VARCHAR(20),
              e TEXT,
              PRIMARY KEY(id),
              UNIQUE KEY(testno),
              INDEX `index_c`(c,d),#组合索引：最左前缀
              FULLTEXT(e)
      )ENGINE=MYISAM;

c.方式三：先创建表，修改表添加索引:ALTER TABLE 表名 ADD 索引类型(数据列名)

      CREATE TABLE test3(
              id INT(4) ,
              testno VARCHAR(10),
              c VARCHAR(50),
              d VARCHAR(20),
              e TEXT
      )ENGINE=MYISAM;
     ALTER TABLE test3 ADD PRIMARY KEY(id);
     ALTER TABLE test3 ADD UNIQUE KEY(testno);
     ALTER TABLE test3 ADD INDEX `index_c`(c,d);
     ALTER TABLE test3 ADD FULLTEXT(e);

3)查看索引

     SHOW KEYS FROM 表名;

4)删除索引

     DROP INDEX 索引名 ON 表名;
     ALTER TABLE 表名 DROP INDEX 索引名;
     ALTER TABLE 表名 DROP PRIMARY KEY;
     eg:
        DROP INDEX studentname ON student;
        ALTER TABLE student DROP INDEX studentname;
        ALTER TABLE student DROP PRIMARY KEY;#删除主键索引

5)全文索引查询(MATCH()AGAINST())

      EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('王');

6)EXPLAIN分析语句执行的性能

    (DESC)EXPLAIN SELECT * FROM student  WHERE studentno=1012

(4)注释 COMMENT

    eg:CREATE TABLE IF NOT EXISTS `test`{
       id INT(11) UNSIGNED COMMENT'编号'
    }

(5)表类型

    1)MYISAM:ENGINE=MYISAM:节约空间及相应的速度
    2)INNODB:ENGINE=innodb：安全性，事务处理及多用户操作数据表
    3)HEAP
    4)BOB
    5)CSV

(6)表数据集

    CREATE TABLE 表名(
    )CHARSET=utf8;

4.数据表的存储位置

 MySQL数据表以文件方式存放在磁盘中
   InnoDB类型数据表只有一个*.frm文件，数据文件为上一级目录的ibdata1文件
 MyISAM类型数据表有对应三个文件
     *frm --表结构定义文件
     *.MYD--数据文件
     *.MYI--索引文件

5.修改表(ALTER TABLE)

1)修改表名

   ALTER TABLE 旧表名 RENAME AS 新表名

2)添加字段

   ALTER TABLE 表名 ADD 字段名 列类型[属性]

3)修改字段

   ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
   ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列类型[属性]

4)删除字段

   ALTER TABLE 表名 DROP 字段名

6.删除数据表

   DROP TABLE [IF EXISTS]表名