首页 > 代码库 > 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基础-总结