2020-10-02-数据库day1笔记

数据库基本语句

show databases;//展示数据库
use school;//使用数据库
show tables;//展示表
--单行注释;
/*
*/多行注释
create database [if not exists] westos;//创建数据库
drop database [if exists] westos;

--创造表
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT'学号',
	`name`  VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT'姓名',
	`pwd`  VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT'密码',
	`sex`  VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday`  DATETIME DEFAULT NULL COMMENT'生日',
	`address` VARCHAR(100) DEFAULT NULL COMMENT'家庭住址',
	`email`  VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`)   
)ENGINE=INNODB DEFAULT CHARSET=utf8

--修改表的字段 用alter
--表重命名
ALTER TABLE `student` RENAME AS `studentnew`;
--增加表的字段
ALTER TABLE `studentnew` ADD agg INT(4);
--修改字段约束
ALTER TABLE `studentnew` MODIFY age VARCHAR(11) 
--字段重命名 并且修改字段
ALTER TABLE `studentnew` CHANGE age agenew INT(5)

ALTER TABLE `studentnew` DROP agg;

CREATE TABLE IF NOT EXISTS `grade` (
	
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT'年纪Id',
	`gradename` VARCHAR(50) NOT NULL COMMENT'年级名称',
	PRIMARY KEY(`gradeid`)

)ENGINE=INNODB DEFAULT CHARSET=utf8


--外键
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT'学号',
	`name`  VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT'姓名',
	`pwd`  VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT'密码',
	`sex`  VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday`  DATETIME DEFAULT NULL COMMENT'生日',
	`gradeid` INT(10) NOT NULL COMMENT '学生值',
	`address` VARCHAR(100) DEFAULT NULL COMMENT'家庭住址',
	`email`  VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`),
	KEY `FK_gradeid`(`gradeid`),
	CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade` (`gradeid`)
	
)ENGINE=INNODB DEFAULT CHARSET=utf8

--向表中插入对象
INSERT INTO `student`(`id`,`name`,`sex`,`gradeid`) VALUES('1','张三','女','12')

INSERT INTO `student` VALUES('2','李四','123','男','1999-07-15','13','Nanjing','1294582975@qq.com')

改变表中对象的属性
UPDATE `student` SET `name`='长江七号' WHERE `id`=2 OR 	`gradeid`=12;	

****删除****
--删除  delete不会删除自增量，而truncate会  注意delete 是from 表，而truncate table 
DELETE FROM `test`;
TRUNCATE TABLE  `test`;

**查询**
SELECT * FROM student

SELECT `studentno` ,`loginpwd` FROM `student`

--as 取别名
SELECT `studentno` AS '学生号码',`studentname` AS '学生姓名' FROM `student`

--查询并连接属性内容的值
SELECT CONCAT('姓名',studentname) AS 新名字 ,`studentno` FROM student
查询并连接 改变表

SELECT DISTINCT `studentname` FROM `student`;


--查询的同时修改数据
SELECT `studentno` ,`studentresult`+1 AS '加一分后的学生成绩'  FROM `result`;

SELECT VERSION();

SELECT 3*4 AS 'jiegou'

**--select [columns] from table**
SELECT `studentno` ,`studentresult`  FROM `result` WHERE `studentresult`>=85 AND `studentresult`<=96;
SELECT `studentno` ,`studentresult`  FROM `result` WHERE `studentresult`  BETWEEN '50' AND '85';

SELECT `studentno` FROM `result`  WHERE NOT  `studentno` ='1000';

SELECT `studentno` FROM `result`  WHERE  `studentno` !='1001';