linux下mysql数据库 模型, 管理表 索引

SHOW [SESSION] VARIABLES  #查看会话变量

MySQL服务器变量按生效时间分为两类：动态和静态

动态：可即时修改

           静态：写在配置文件中,通过参数传递给mysqld

动态调整参数的生效方式：

全局变量：对当前会话无效，只对新建立会话有效；

会话变量：即时生效，但只对当前会话有效；

1.8.服务器变量：@@变量名

显示:SELECT @@global.sql_mode;

              SELECT @@session.sql_mode;

设定:SET GLOBAL|SESSION 变量名=‘value‘

2.管理表:

2.1.创建数据库:

CREATE DATABASE IF NOT EXISTS students CHARACTER SET ‘gbk‘ COLLATE ‘gbk_chinese_ci‘;

2.2.删除数据库:

DROP DATABASE [IF EXISTS] db_name

2.3.创建表:

CREATE TABLE [IF NOT EXISTS] tb_name (col_name col_defination, constraint )

2.3.1.直接创建一张空表:

CREATE TABLE tb1 (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, Name CHAR(20) NOT     NULL, Age TINYINT NOT NULL)  ENGINE [=] engine_name

CREATE TABLE tb2 (id INT UNSIGNED NOT NULL AUTO_INCREMENT, Name CHAR(20) NOT NULL, Age    TINYINT NOT NULL, PRIMARY KEY(id),UNIQUE KEY(name),INDEX(age))

CREATE TABLE courses(ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Course  VARCHAR(50) NOT NULL);

2.3.2.插入表数据:

INSERT INTO courses (Course) values (‘CCNA‘),(‘CCNP‘),(‘RHCA‘);

2.3.3.显示表的索引:

SHOW INDEXES FROM tb_name;

2.4.从其它表中查询出数据,并以之创建新表:(注意此类方法表格式定义与源表不同)

CREATE TABLE courses_new SELECT * FROM courses WHERE ID <=2;

2.5.以其它表为模板创建一个空表；(此类方法表格式定义与源表一模一样)

CREATE TABLE Courses_good LIKE courses;

3.索引

3.1.添加索引:

ALTER TABLE Courses_good ADD UNIQUE KEY (Course);

3.2.修改字段名称:

ALTER TABLE courses_good MODIFY CID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE courses_good CHANGE Couse Course VARCHAR(50) NOT NULL;

3.3.增加字段名称:

ALTER TABLE courses_good ADD Startdate date default ‘2016-06-29‘;

3.4.修改表名称:

ALTER TABLE courses_good RENAME TO courses_new;

RENAME TABLE courses_new TO courses_good;

3.5.删除表:

DROP TABLE courses_new;

3.6.表之间建立关系:

CREATE TABLE classroom (SID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Name VARCHAR(30), CID INT NOT NULL);

INSERT INTO classromm (Name,CID) VALUES (‘Jerry‘,3),(‘tom‘,1);

SELECT * FROM classromm;

SELECT * FROM courses;

SELECT Name,Course FROM classroom,courses WHERE classroom.CID=courses.ID;

在没有引用外键情况下:classroom表可任意插入数据如下:

INSERT INTO classroom (Name,CID) VALUES (‘Willow‘,6);

DELETE FROM classroom WHERE CID=6;

引用外键情况下:classroom表只能对照引用表存在的数据进行插入,不能任意插入数据

外键必须是支持事务的存储引擎,如InnoDB,否则会报错

外键引用字段类型参数也需一致

ALTER TABLE courses ENGINE=InnoDB;

ALTER TABLE classroom ADD FOREIGN KEY foreign_cid (CID) REFERENCES courses(ID);

SHOW INDEXES FROM classroom;

此时对classroom表再插入引用的courses表中没有的数据，将会报错

INSERT INTO classroom (Name,CID) VALUES (‘Willow‘,6); 

3.7.创建索引:

CREATE INDEX index_on_name ON classroom (Name) USING BTREE;

CREATE INDEX index_on_name ON classroom (Name(5) DESC) USING BTREE;

3.8.删除索引:

DROP INDEX index_on_name ON classroom;

本文出自 “夏维柳” 博客，请务必保留此出处http://willow.blog.51cto.com/6574604/1794091

linux下mysql数据库 模型, 管理表 索引

标签：linux下mysql数据库 模型   管理表 索引