mysql的基本运用(一)

创建数据库

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