MySQL案例——基础查询

常用语句：

--显示数据库
SHOW DATABASES;

#在stuinfo中创建表score成绩 包含列id,name名字,grade成绩

--选择数据库
USE stuinfo;
--创建表
CREATE TABLE score(
   id INT,    -- auto_increment 自动增长
   NAME VARCHAR(20),-- varchar() 可变长度的字符串
   grade INT);

#查询表

语法：select 查询列表 from 表名;

#查询列表 可以是表中的字段、常量、表达式、函数(等同于java中的方法)
#查询的结果只是一个虚拟的表格

SELECT * FROM score;
SELECT id FROM score;
SELECT 100;
SELECT SUM() FROM score;

#插入数据

语法：
insert into 表名 (列名1,列名2)values(值1,值2);
insert into 表名 values(数据);

INSERT INTO score (NAME,grade) VALUES ('张三',40);    
INSERT INTO score VALUES(2,'李四',80);
INSERT INTO score VALUES(1,'王五',90);

#修改语句

语法：update 表名 set 列名1=值1,列名2=值2 where 条件;

UPDATE score SET id = 1 WHERE NAME='张三';

#删除数据

语法：
delete from 表名 where 条件; 
drop table 表名;
truncate table 表名;

delte、drop、truncate的区别:
  delete删除表中数据，不删除表结构    
  drop删除表结构，执行速度最快
  truncate删除数据不删除表结构 但是会释放空间

--选择数据库
USE myschool;
--如果存在要创建的表，删除它
DROP TABLE IF EXISTS student;
--创建表
CREATE TABLE student(
  studentNo INT(4) NOT NULL PRIMARY KEY COMMENT '学号',
  loginPwd VARCHAR(20) NOT NULL COMMENT '密码',
  studentName VARCHAR(50) NOT NULL COMMENT '学生姓名',
  sex CHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
  gradeId INT(4) UNSIGNED COMMENT '年级编号',
  phone VARCHAR(50) COMMENT '电话',
  address VARCHAR(255) DEFAULT '地址不详' COMMENT '地址',
  bornDate DATETIME COMMENT '出生日期',
  email VARCHAR(50) COMMENT '邮箱编号',
  identityCard VARCHAR(18) UNIQUE KEY COMMENT '身份证号'
);

若出现Error 就表示有 错误
在Error后面 找到near在...的附近 查看是哪一块出错，解决即可
注意：所有的符号必须是英文的

--查看当前默认的存储引擎
SHOW VARIABLES LIKE 'storage_engine%';

#设置表的存储引擎

语法：
CREATE TABLE 表名(
   列名 数据类型..
)ENGINE = 存储引擎(MYISAM/INNODB)

#高级查询
#修改表 对已经创建好的表进行操作

--修改表名
语法：alter table 旧表名 rename to 新表名;

#将stu这个表的名字改为teacher

ALTER TABLE stu RENAME TO teacher;
SELECT * FROM teacher;

--添加字段 语法：
alter table 表名 add 字段名 数据类型 [属性|注释];

ALTER TABLE teacher ADD id INT;

--删除字段
语法：alter table 表名 drop 字段名;

ALTER TABLE teacher DROP id;

--修改字段 语法：
alter table 表名 change 原字段名 新字段名 数据类型;

ALTER TABLE teacher CHANGE nums mumber INT;

小练习：
1、创建一个科目表subject,列名subjectNo(课程编号),
subjectName(课程名称)，classHour(学时),gradeId(年级编号)
2、将科目表subject改为course,
3、将字段subjectNo改为courseNo,
   subjectName改为courseName
4、添加字段classNum,
5、删除字段classHour

主键：是一个表中唯一标识的一条记录，不能有重复的，不允许为空

主键跟唯一约束的对比
                保证唯一性        是否允许为空        一个表中有几个
主键约束       是                        否                    最多只能有一个
唯一约束       是                        是                    可以有多个

外键：是另一个表的主键，外键可以有重复的，可以是空值，用来跟其他表建立联系用的。
外键：两个表之间的一个关联
一个表可以跟多个表有联系
作用：限制表数据的插入，必须关联的表里面有数据，才能给外键所在的表添加数据

注意事项：
1、在从表上面设计外键关系，来限制从表的取值
2、从表的外键列类型和主键的关联列一样或兼容
3、主表的关联列必须是一个主键或唯一
4、插入表的时候先插入主表(外键关联表)，再插入从表(有外键的表)
      删除数据时，先删除从表，再删除主表

--添加主键
语法：alter table 表名 add constraint 主键名 primary key 表名(主键字段)

ALTER TABLE teacher ADD CONSTRAINT `pk_teacher` PRIMARY KEY teacher(mumber);

--选择数据库
USE stuinfo;

--1、先建一个专业表，里面存放所有的专业信息
DROP TABLE IF EXISTS major;
CREATE TABLE major(
   id INT COMMENT '专业id号',
   mjname VARCHAR(20) COMMENT '专业名称'
);
INSERT INTO major VALUES (101,'UI');

SELECT * FROM major;

--给专业表添加主键
ALTER TABLE major ADD PRIMARY KEY(id);

--2、再创建一个学生表
DROP TABLE IF EXISTS student;
CREATE TABLE student(
   stuid INT COMMENT '学生ID',
   stuName VARCHAR(20) COMMENT '学生姓名',
   mj_id INT COMMENT '学生所在专业',
   FOREIGN KEY(mj_id) REFERENCES major(id)
)
INSERT INTO student VALUES (1,'彭凯',101);
INSERT INTO student VALUES (2,'王佳琪',101);
INSERT INTO student VALUES (2,'王佳琪',100000);

SELECT * FROM student;
--插入表要先插主表(外键关联的表)，再插从表(外键所在的表)
INSERT INTO major VALUES (100,'java');
SELECT * FROM student;
SELECT * FROM major;

小练习：
1、创建一张员工表employee 字段有
id整型(主键),name字符型,gender字符型,birthday日期型,
entry_date日期型,job整型(外键)
2、创建一张工种表jobs字段有
job_id整型(主键),salary小数型
'2020-11-23'
3、要求:创建主外键 插入两条数据

--添加外键 语法：
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(外键字段) REFERENCES 关联表名(关联字段)

--创建员工表 
CREATE TABLE employee(
   id INT PRIMARY KEY,
   NAME VARCHAR(20),
   gender VARCHAR(20),
   birthday DATETIME,
   entry_date DATETIME,
   job VARCHAR(10) ,
   FOREIGN KEY(job) REFERENCES jobs(job_id)
   -- 创建时直接添加外键
)
--创建工种表
CREATE TABLE jobs(
   job_id INT PRIMARY KEY,
   salary DOUBLE
)
--创建表之后再单独添加外键
ALTER TABLE employee ADD CONSTRAINT pk_job 
FOREIGN KEY(job)
REFERENCES jobs(job_id);

小练习：
1、创建一张个人信息表 PersonInfo ,包含字段stu_name姓名,sex性别,age年龄,phone电话号码,adds地址
2、用插入多条数据的语法插入3行数据
3、编写SQL语句从个人信息表中提取姓名、手机号两列数据存储到通讯录表phoneList中
4、查询新表phoneList的数据
 

#PERIOD_DIFF(201910, 201903); 返回两个时间的月份差数 结果为7

SELECT PERIOD_DIFF(CURDATE(hiredate)，CURDATE(NOW())) FROM employees;

SELECT PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'),DATE_FORMAT(hiredate ,'%Y%m')) FROM employees;