首页 > 代码库 > mysql基础-总结
mysql基础-总结
/************************************************mysql数据库查看相关信息的语句*****************************************/
SELECT VERSION();-- 查看数据库的版本号
SELECT USER(),CURRENT_USER();-- 产看当前用户
SELECT DATABASE();-- 查看当前正在使用的数据库
SELECT CURDATE(),CURRENT_DATE();-- 查看当前日期
SELECT CURTIME(),CURRENT_TIME();-- 查看当前的时间
SELECT NOW(),CURRENT_TIMESTAMP();-- 查看当前的时间戳
SHOW CHARSET;-- 查看mysql支持的所有的字符编码
SHOW CHARSET LIKE ‘s%‘; -- 查看以s开头的字符编码
SHOW VARIABLES LIKE ‘char%‘;-- 查看变量名为char开头的变量
SHOW ENGINES;-- 查看数据库支持的所有引擎
SHOW DATABASES;-- 查看所有的数据库
SHOW TABLES;-- 查看所有的表
SHOW CREATE DATABASE aa;-- 查看标准建库语句
SHOW CREATE TABLE aa;-- 查看标准的建表语句
SHOW FULL COLUMNS FROM student;-- 查看表的所有字段信息
DESC student;-- 查看表结构[DESCRIBE的缩写:DESC
/***************************************************创建数据库语句******************************************************/
CREATE DATABASE aa;
CREATE DATABASE `aa`;-- 创建数据库，名字可以是关键字.主要作用:增强兼容性
CREATE DATABASE IF NOT EXISTS bb DEFAULT CHARSET=utf8;
CREATE DATABASE `aa` /*!40100 DEFAULT CHARACTER SET utf8 */;
CREATE DATABASE `aa` /*!40100 DEFAULT CHARACTER SET gbk */;
CREATE DATABASE `aa` /*!40100 DEFAULT CHARACTER SET gb2312 */;
USE aa;-- 选择数据库
DROP DATABASE IF EXISTS aa;-- 删除数据库
/**********************************************************数据表相关语句********************************************/
CREATE TABLE IF NOT EXISTS student(
列名，类型，修饰，约束
sid INT(10) UNSIGNED ZEROFILL PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(10) NOT NULL,
sgender ENUM(‘男‘,‘女‘,‘保密‘) DEFAULT ‘男‘
)ENGINE=MYISAM DEFAULT CHARSET=utf8;-- 标准建表语句
CREATE TABLE bb LIKE student;-- 快速建表，以student表为模板建立相同结构相同约束的表，无数据
CREATE TABLE aa SELECT * FROM student;-- 快速建表 以student查询的数据新建一个表，没有约束
DROP TABLE IF EXISTS aa;-- 删除一个表
DROP TABLE IF EXISTS aa,bb,cc;-- 删除多个表
RENAME TABLE aa TO bb;-- 修改表名称
ALTER TABLE aa RENAME bb; -- 修改表名称
ALTER TABLE aa RENAME TO bb;-- 修改表名称
ALTER TABLE student CHANGE sname snames VARCHAR(20);-- 修改表的列名和类型
ALTER TABLE student CHANGE sid sid INT(10);
ALTER TABLE student DROP PRIMARY KEY;-- 删除主键(前提是当前主键没有自增字段，如果有自增字段先执行上面的语句，清除auto_increment字段)
ALTER TABLE student ADD CONSTRAINT pk PRIMARY KEY(sid);-- 添加主键
ALTER TABLE student ADD COLUMN sage;-- 在表末尾添加一列
ALTER TABLE student ADD COLUMN saddress AFTER sanme;-- 在某一字段后面添加一列
ALTER TABLE student ADD COLUMN sscore FIRST;-- 在开头添加一列
ALTER TABLE student DROP COLUMN score;-- 删除一列
ALTER TABLE stu ADD CONSTRAINT fk FOREIGN KEY (stid) REFERENCES tec(tid);-- 添加外键约束(字段必须存在，且类型和修饰相同)
ALTER TABLE stu ADD CONSTRAINT fk FOREIGN KEY (stid) REFERENCES tec(tid) ON DELETE SET NULL;-- 删除外键关联时，相关的内容设为空
ALTER TABLE stu ADD CONSTRAINT fk FOREIGN KEY (stid) REFERENCES tec(tid) ON DELETE CASCADE;-- 删除外键关联时，删除相关的数据
ALTER TABLE stu ADD CONSTRAINT fk FOREIGN KEY (stid) REFERENCES tec(tid) ON UPDATE CASCADE;-- 更新外键关联时，更新相关的内容
ALTER TABLE stu ADD CONSTRAINT fk FOREIGN KEY (stid) REFERENCES tec(tid) ON DELETE SET NULL ON UPDATE CASCADE;-- 删除设空，修改更新
/*
上面的ON DELETE SET NULL：外键设为空
上面的ON DELETE/UPDATE CASCADE：外键级联
注意：使用外键约束时，数据表的存储引擎为INNODB
*/
ALTER TABLE stu DROP FOREIGN KEY fk;-- 删除外键
/***********************************************************索引操作***************************************************/
-- 一般情况下，建立表时如果建立pk(主键)、fk(外键)、uk(唯一约束键) 数据库系统会自动的建立索引，索引的作用：数据量很大时可以提高检索速度
CREATE INDEX myindex ON stu(sscore DESC);-- 创建索引
ALTER TABLE stu ADD INDEX myindex(sscore DESC);-- 创建索引
DROP INDEX myindex ON stu;-- 删除索引
ALTER TABLE stu DROP INDEX myindex;-- 删除索引
/******************************************************mysql的数据类型************************************************/
-- 数值类型
BIT -- 位类型(只能存0和1)
TINYINT -- 短整型 范围为有符号数：-128~127，无符号数：0~255
SMALLINT -- 小整型 范围为有符号数：-32768~32767，无符号数：0~65535
MEDIUMINT -- 中整型 范围为有符号数：-8388608~8388607，无符号数：0~16777215
INT -- 整型 范围为有符号数：-2147483648~2147483647，无符号数：0~4294967295
BIGINT -- 大整型 范围为有符号数：-9223372036854775808~9223372036854775807，无符号数：0~18446744073709551615
-- 精度小数位类型
NUMERIC(p,s)-- p代表数值总长度，s表示保留的小数位，若p=s，则只能存小于1的小树
DECIMAL(p,s)-- p代表数值总长度，s表示保留的小数位，若p=s，则只能存小于1的小树
-- 字符类型
CHAR(n) -- 定长字符串，n<=255，可以保存n个字符
VARCHAR(n) -- 变长字符串,n<=65535
TEXT -- 文本类型，最大存储65535个字符
MEDIUMTEXT -- 中等文本类型，可以存储16777215个字符
LONGTEXT -- 长文本类型最大可以存4GB
ENUM(‘男‘,‘女‘,‘保密‘)-- 枚举类型取其中一个
-- 二进制文件类型
LONGBLOB -- 主要存储二进制文件，最大可以是4G
-- 日期类型
DATE -- 日期类型 只能保存 年月日 非法的为:0000-00-00
DATETIME -- 日期时间类型 保存年月日 时分秒
TIME -- 时间类型 保存 18:30:35 时分秒
TIMESTAMP -- 时间戳类型
CREATE TABLE t1(
sname VARCHAR(10),
stime TIMESTAMP DEFAULT CURRENT_TIMESTAMP() -- 若没有设置默认时间时，更新数据，时间修改为修改数据时的时间
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
CREATE TABLE t1(
sname VARCHAR(10),
stime DATETIME DEFAULT CURRENT_TIMESTAMP() -- 时间总是为插入数据时的设置的时间或插入数据时的时间
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
/*************************************************约束相关的关键字****************************************************/
-- NOT NULL 非空约束
-- DEFAULT 默认值约束，若指定值，则不再采用默认值
-- ENUM(1,2) 枚举，只能去里面的值或null，不能使其他值
-- PRIMARY KEY 主键 在一个表内主键有且只能有一个，不能重复
CREATE TABLE t1(
tid INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
tname VARCHAR(20)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
CREATE TABLE t2(
tid INT UNSIGNED NOT NULL AUTO_INCREMENT,
tname VARCHAR(20),
PRIMARY KEY(tid)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
CREATE TABLE t3(
tid INT UNSIGNED NOT NULL AUTO_INCREMENT,
tname VARCHAR(20),
PRIMARY KEY(tid,tname) -- 建立复合主键，一个主键管理两个表
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
CREATE TABLE t4(
tid INT UNSIGNED NOT NULL,
tname VARCHAR(20)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
ALTER TABLE t4 ADD CONSTRAINT pk PRIMARY KEY(tid);
-- 以上都可以建立主键，但最后一种不是自增主键
-- FOREIGN KEY 外键约束,两个表存储引擎采用InnoDB，并且一个表内的外键是另一个表的主键或唯一约束键
-- 建立教师表
CREATE TABLE teacher(
tid SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
tname VARCHAR(20)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 建立学生表 PK[主键]、FK[外键]，一个表的外键指向另一个表的主键或唯一约束键，在一个表内外键可以没有，有必须正确
CREATE TABLE student(
sid INT UNSIGNED ZEROFILL NOT NULL PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20) NOT NULL,
sscore INT,
stid SMALLINT UNSIGNED,
-- constraint fk foreign key(stid) references teacher(tid)
-- CONSTRAINT fk FOREIGN KEY(stid) REFERENCES teacher(tid) on delete set null
-- CONSTRAINT fk FOREIGN KEY(stid) REFERENCES teacher(tid) ON DELETE cascade
-- CONSTRAINT fk FOREIGN KEY(stid) REFERENCES teacher(tid) ON update CASCADE
CONSTRAINT fk FOREIGN KEY(stid) REFERENCES teacher(tid) ON DELETE SET NULL ON UPDATE CASCADE
)ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO teacher VALUES(NULL,‘张老师‘);
INSERT INTO teacher VALUES(NULL,‘刘老师‘);
INSERT INTO teacher VALUES(NULL,‘李老师‘);
INSERT INTO teacher VALUES(NULL,‘黄老师‘);
SELECT * FROM teacher;
INSERT INTO student VALUES(NULL,‘诸葛亮‘,70,3);
INSERT INTO student VALUES(NULL,‘大乔‘,75,4);
INSERT INTO student VALUES(NULL,‘张飞‘,80,1);
INSERT INTO student VALUES(NULL,‘关羽‘,95,2);
INSERT INTO student VALUES(NULL,‘刘备‘,56,2);
INSERT INTO student VALUES(NULL,‘曹操‘,37,3);
INSERT INTO student VALUES(NULL,‘姚盖‘,76,1);
INSERT INTO student VALUES(NULL,‘孙权‘,89,4);
SELECT * FROM student;
-- 关联的教师表，当设置主键没有(ON DELETE SET NULL,ON DELETE CASCADE,ON UPDATE CASCADE)时，下面这几条语句执行出错，违反外键约束：
INSERT INTO student VALUES(NULL,‘小乔‘,‘25‘,10);
DELETE FROM teacher WHERE tid = 3;
UPDATE teacher SET tid = 5 WHERE tid = 2;
DROP TABLE teacher;
-- 关联的教师表，当设置主键有(ON DELETE SET NULL,ON DELETE CASCADE,ON UPDATE CASCADE)时,表的级联性更强:
CONSTRAINT fk FOREIGN KEY(stid) REFERENCES teacher(tid) ON DELETE SET NULL -- 删除关联时，相关内容设为空
CONSTRAINT fk FOREIGN KEY(stid) REFERENCES teacher(tid) ON DELETE CASCADE -- 删除关联时，删除相关内容
CONSTRAINT fk FOREIGN KEY(stid) REFERENCES teacher(tid) ON UPDATE CASCADE -- 修改关联时，更新相关内容
CONSTRAINT fk FOREIGN KEY(stid) REFERENCES teacher(tid) ON DELETE SET NULL ON UPDATE CASCADE -- 删除设空，修改更新
DROP TABLE student;
DROP TABLE teacher;
TRUNCATE student;-- 截断表内数据，重新插入主键从开始自增
TRUNCATE teacher;
-- UNIQUE KEY唯一约束
CREATE TABLE t5(
tid INT UNSIGNED ZEROFILL NOT NULL PRIMARY KEY AUTO_INCREMENT,
tname VARCHAR(20),
UNIQUE KEY(tname)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
CREATE TABLE t6(
tid INT UNSIGNED ZEROFILL NOT NULL PRIMARY KEY AUTO_INCREMENT,
tname VARCHAR(20) UNIQUE KEY
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
CREATE TABLE t7(
tid INT UNSIGNED ZEROFILL NOT NULL PRIMARY KEY AUTO_INCREMENT,
tname VARCHAR(20)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
ALTER TABLE t7 ADD UNIQUE KEY(tname);
-- 以上都可以添加唯一约束
本文出自 “夜风” 博客，请务必保留此出处http://jiangkun08.blog.51cto.com/6266992/1547241
mysql基础-总结