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]表名