MySQL基本操作

Workbench常用快捷键
1、执行整篇sql脚本：Ctrl + Shift + Enter
2、执行当前行： Ctrl + Enter
3、注释/取消注释： Ctrl + /
4、格式化sql语句： Ctrl + B

# 创建数据库
create database choose; 
show databases;
show create database choose;

# 创建数据表
use choose;
set default_storage_engine = InnoDB;
create table my_table(
today datetime,
name char(20)
);
show tables; 

# 查看表结构
desc my_table;

# 插入表记录
use choose;
insert into my_table values(now(), 'a');
insert into my_table values(now(), 'a');
insert into my_table values(now(), NULL);
insert into my_table values(now(), ' ');
select * from my_table;

use choose ;
alter table my_table engine = InnoDB;
create table second_table(
today datetime,
name char(20)
);

use choose;
create table today(
t1 datetime,
t2 timestamp
);

insert into today values(now(), now());
insert into today values(null, null);

show variables like 'time_zone';
select * from today;
set time_zone = '+12:00';
show variables like 'time_zone';

# 有问题
use choose;
create table person(
sex enum('男', '女'),
interest set('听音乐', '看电影', '购物', '旅游', '游泳', '游戏')
);
insert into person values('男', '看电影, 游泳, 听音乐');

select * from person;


# 创建选课系统

use choose;
create table teacher(
teacher_no char(10) primary key,
teacher_name char(10) not null,
teacher_contact char(20) not null
)engine = InnoDB default charset = gbk;

create table classes(
class_no int auto_increment primary key,
class_name char(20) not null unique,
department_name char(20) not null
)engine = InnoDB default charset = gbk;

create table course(
course_no int auto_increment primary key,
course_name char(10) not null,
up_limit int default 60,
description text not null,
status char(6) default '未审核',
teacher_no char(10) not null unique,
constraint course_teacher_fk foreign key(teacher_no) references teacher(teacher_no)
)engine = InnoDB default charset = gbk;

create table student(
student_no char(11) primary key,
student_name char(10) not null,
student_contact char(20) not null
class_no int,
constraint student_class_fk foreign key(class_no) references classes(class_no)
)engine = InnoDB default charset=gbk;

create table choose(
choose_no int auto_increment primary key,
student_no char(11) not null,
course_no int not null,
score tinyint unsigned,
choose_time datetime not null,
constraint choose_student_fk foreign key(student_no) references student(student_no),
constraint choose_course_fk foreign key(course_no) references course(course_no)
)engine = InnoDB default charset = gbk;

# 创建索引
create table book(
isbn char(20) primary key,
name char(100) not null,
brief_introduction text not null,
price decimal (6, 2),
publish_time date not null,
unique index isbn_unique (isbn),
index name_index (name(20)),
fulltext index brief_fulltext (name, brief_introduction),
index complex_index (price, publish_time)
)engine = MyISAM default charset = gbk;

# 在现有表上创建索引
alter table course add fulltext index description_fulltext(description);
create fulltext index description_fulltext on course (description);

 
# 插入记录
use choose;
insert into teacher values('001', '张老师', '11000000000');
insert into teacher values('002', '李老师', '12000000000');
insert into teacher values('003', '王老师', '13000000000');

# 向指定字段插入记录
use choose ;
insert into classes(class_no, class_name, department_name) values(null, '2010自动化1班','机电工程');
insert into classes(class_no, class_name, department_name) values(null, '2010自动化2班','机电工程');
insert into classes(class_no, class_name, department_name) values(null, '2010自动化3班','机电工程');

use choose;
insert into course values(null, 'java语言程序设计', default, '暂无', '已审核', '001');
insert into course values(null, 'MySQL数据库', 150, '暂无', '已审核', '002');
insert into course values(null, 'java语言程序设计', 230, '暂无', '已审核', '003');

# 批量插入多条语句
use choose;
insert into student values
('2010001', '张三', '15000000000', 1),
('2010002', '李四', '16000000000', 1),
('2010003', '王五', '17000000000', 3), 
('2010004', '马六', '18000000000', 2),
('2010005', '田七', '19000000000', 2);
select * from student;