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);

-- 以上都可以添加唯一约束