创建数据库
CREATE DATABASE test;
创建表
CREATE TABLE `t_book` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`book_name` varchar(20) DEFAULT NULL,
`price` decimal(6,2) DEFAULT NULL,
`auther` varchar(20) DEFAULT NULL,
`book_type_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
CREATE TABLE `t_book_type` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`book_type_name` varchar(20) DEFAULT NULL,
`book_num` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
CREATE TABLE `t_grade` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`stu_name` varchar(60) DEFAULT NULL,
`course` varchar(60) DEFAULT NULL,
`score` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
CREATE TABLE `t_price_level` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`price_level` int(11) DEFAULT NULL,
`price` float DEFAULT NULL,
`discription` varchar(300) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
CREATE TABLE `t_student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`stu_name` varchar(60) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` varchar(30) DEFAULT NULL,
`grade_name` varchar(60) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8
CREATE TABLE `t_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`test` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
表中插入数据
insert into `t_book`(`id`,`book_name`,`price`,`auther`,`book_type_id`) values (1,'Java编程思想','100.00','埃史尔',1),(2,'Java从入门到精通','80.00','李忠尉',1),(3,'三剑客','70.00','大仲马',2),(4,'生理学(第二版)','24.00','刘先国',4);
insert into `t_book_type`(`id`,`book_type_name`,`book_num`) values (1,'计算机',2),(2,'文学类',1),(3,'教育类',0),(4,'生物类',1);
insert into `t_grade`(`id`,`stu_name`,`course`,`score`) values (1,'张三','语文',91),(2,'张三','数学',90),(3,'张三','英语',87),(4,'李四','语文',79),(5,'李四','数学',95),(6,'李四','英语',80),(7,'王五','语文',77),(8,'王五','数学',81),(9,'王五','英语',89);
insert into `t_log`(`id`,`action_time`,`action_context`) values (2,'2021-12-08 09:35:43','在book表里删除了一条数据');
insert into `t_price_level`(`id`,`price_level`,`price`,`discription`) values (1,1,80,'价格贵的书'),(2,2,60,'价格适中的书'),(3,3,40,'价格便宜的书');
insert into `t_student`(`id`,`stu_name`,`age`,`sex`,`grade_name`) values (1,'张三',23,'男','一年级'),(2,'张三丰',25,'男','二年级'),(3,'李四',23,'男','一年级'),(4,'王五',22,'男','三年级'),(5,'珍妮',21,'女','一年级'),(6,'李娜',26,'女','二年级'),(7,'王峰',20,'男','三年级'),(8,'梦娜',21,'女','二年级'),(9,'小黑',22,'男','一年级'),(10,'追风',25,'男','二年级'),(11,'小小张三',21,'','二年级'),(12,'小张三',23,'男','二年级'),(13,'张三峰小',24,'','二年级');
更新表中数据
update t_book set book_name='hello world',price=200 where id=4;
删除表中数据
truncate table t_book;
delete from t_book where id=4;
删除表
drop table t_log;
查询表
select * from t_book where id=1
select id,book_name from t_book where id=1
表中添加普通索引
CREATE TABLE t_user1(
id INT,
user_name VARCHAR(20),
PASSWORD VARCHAR(20),
INDEX (user_name)
)
表中添加唯一索引
# unique index 索引名 index_user_name
CREATE TABLE t_user2(
id INT,
user_name VARCHAR(20),
PASSWORD VARCHAR(20),
UNIQUE INDEX index_user_name(user_name)
)
已存在表中添加索引
ALTER TABLE t_user1 ADD INDEX index_user_name(user_name)
ALTER TABLE t_user1 ADD UNIQUE INDEX index_user_name(user_name)
删除索引
DROP INDEX index_user_name ON t_user1