创建数据库-了解

  1. create database 数据库名;

创建数据表

数据表跟数据库的关系
1个数据库中可以有很多个表。表是创建在库中。
Mysql-05 数据库操作 - 图1

使用navicat 工具创建

image.png
添加列
image.png
image.png

  1. CREATE TABLE `fanmao65yyds`.`无标题`  (
  2.   `id` int(0) NULL,
  3.   `name` varchar(255) NULL,
  4.   `age` int(0) NULL,
  5.   `sex` varchar(255) NULL
  6. );

image.png

注意

  1. 数据库中的表名，命名时没有中文，都是以 英文命名。
  2. 表中的列名，命名也是以英文命名。

建表语句

基本语法

  1. create table 表名(
  2.     `id` int,
  3.   `name` varchar(255)
  4. )

数据类型

在数据库中，主要的数据库类型如下

类型 解释 举例
int 数字 整数
varchar 字符串 varchar(10) 字符长度不超过10位
decimal 带有小数点的数字 decimal(10,2)
10: 整数部分长度不超过10
2: 小数点保留2位
datetime 日期

创建表，下面是字段名，表名为 fanmao_姓名拼音。

字段名 类型 说明
id int(11) 编号
username varchar(50) 姓名
phone varchar(11) 手机号码
idcard varchar(18) 身份证号 
  1. create TABLE fanmao_zhangsan(
  2.     `id` int(11),
  3.     `username` VARCHAR(50),
  4.     `phone` VARCHAR(11),
  5.     `idcard` VARCHAR(18)
  6. );

数据类型限制

Null 可以为 Null id int(11) Null
Not Null 不能为Null id int(11) Not Null
Default 设置默认值 score default 0 score字段默认值为 0
字段名 类型 为Null 默认值 说明
id int(11) 不为空 无 编号
username varchar(50) 不为空 无 姓名
phone varchar(11) 可以为null 无 手机号码
idcard varchar(18) 不为空 无 身份证号
sex varchar(1) 不为null 男 性别 
  1. -- 删除zhangsan
  2. DROP table fanmao_zhangsan;
  4. create table fanmao_zhangsan(
  5.     `id` int(11) not null,
  6.   `username` varchar(50) not null,
  7.   `phone` varchar(11) null,
  8.   `idcard` varchar(18) not null,
  9.   `sex` varchar(1) not null default "男"
  10. );

主键 primary key

  1. -- 删除zhangsan 如果zhangsan存在；
  2. DROP table if EXISTS fanmao_zhangsan;
  4. create table fanmao_zhangsan(
  5.     `id` int(11) not null auto_increment,   -- 自动递增
  6.   `username` varchar(50) not null,
  7.   `phone` varchar(11) null,
  8.   `idcard` varchar(18) not null,
  9.   `sex` varchar(1) not null default "男",
  10.   primary key(`id`)    -- 设置主键
  11. );

添加数据

insert into 添加数据；

  1. INSERT INTO `fanmao_zhangsan`
  2. (`username`, `phone`, `idcard`)   -- 添加数据字段
  3. VALUES 
  4. ("张三", "9999", "123423");     -- 对应的字段值

修改数据

update 表名 set username=’xxxx’ where xxxx

将 fanmao_zhangsan 表中 id=1 的username改为 小明 

  1. update fanmao_zhangsan
  2. set username = "小明"
  3. where id = 1;

删除数据

delete from table

  1. DELETE FROM fanmao_zhangsan  WHERE `id` = 1

练习

  1. 创建表 emps，并添加10条数据； | 字段 | 类型 | 是否Null | Primary Key 主键 | 备注 | | —- | —- | —- | —- | —- | | id | int(11) | No | Yes | 编号 自动递增 | | username | varchar(50) | No | No | 姓名 | | salary | decimal(10,2) | No | No | 月薪 |
  1. drop table if exists emps;
  3. create table emps(
  4.     id int(11) not null auto_increment,
  5.   username varchar(50) not null, 
  6.   salary decimal(10,2) not null,
  7.   primary key(id)
  8. );
  10. -- 添加5条数据
  11. insert into emps
  12. (username, salary)
  13. values
  14. ("zhangsan",1000),
  15. ("wangdi",7800),
  16. ("liuliu",9000),
  17. ("wangwang",6600),
  18. ("zhangzhang",8000);

  1. 将月薪低于8000的员工的薪水上调10%；

    1. update emps
    2. set salary = salary*1.1
    3. where salary < 8000;

  2. 删除工资大于20000的员工；

    1. DELETE from  emps
    2. WHERE salary > 20000

  3. 统计每个月公司需要支付的薪水总额；

    1. select sum(salary) from emps;

    删除表

    有两种删除方法

  • drop table 整个表全部删掉
  • truncate table 删除表中的数据，表结构还在。 ```sql SELECT * from emps; TRUNCATE TABLE emps; — 清空表中的所有数据

DROP table if EXISTS emps; — 删掉整个表

  2. <a name="wtokD"></a>
  3. # 主键和外键
  5. ![](https://cdn.nlark.com/yuque/0/2021/jpeg/87080/1632475063861-95e7cc8c-8d22-43ae-b360-8bf02e020cf3.jpeg)<br />分别创建两个表
  6. ```sql
  7. -- 创建表
  8. create table manager(
  9.     id int(11) not null auto_increment,
  10.   name varchar(50) not null,
  11.   primary key(id)
  12. );
  15. create table emps(
  16.     id int(11) not null auto_increment,
  17.   name varchar(50) not null,
  18.   p_id int(11) not null,
  19.   primary key(id)
  20. );

建立外键关联

根据业务关系
表 emps 依赖 表manger
在 emps 创建外键关联

  1. ALTER TABLE `emps`   -- emps 依赖 manager
  2. ADD CONSTRAINT       -- 添加外键（固定写法）
  3. `manageid`           -- 外键名（可以随意起名，但是需要注意：不能跟其他外键名重名）
  4. FOREIGN KEY (`p_id`) -- emps中 p_id  依赖 manger中的 id
  5. REFERENCES `manager` (`id`); -- 引用 manager 中的id
  • alter table 固定写法 emps 根据业务关系
  • ADD CONSTRAINT 固定写法
  • manageid 可变 外键命名
  • FOREIGN KEY 外键 固定写法 p_id 表中字段

整个关系
image.png

添加外键的作用

在添加数据的时候，可以有效的保证数据的完整性。
image.png

练习

存在三张表
Mysql-05 数据库操作 - 图8

  1. 创建上面三个表 ```sql drop table if exists stu; drop table if exists course; drop table if exists sc;

create table stu( sid int(10) not null auto_increment, sname varchar(50) not null, primary key(sid) );

create table course( cid int(10) not null auto_increment, cname varchar(50) not null, primary key(cid) );

create table sc( id int(10) not null auto_increment, sid int(10) not null, cid int(10) not null, score int(10), primary key(id) );

  2. 2. 建立外键关联;  成绩表依赖 学生表和课程表
  4. 需要在 成绩表上创建关联
  5. ```sql
  6. -- sc 到stu 的外键关联
  7. ALTER TABLE `sc`   -- emps 依赖 manager
  8. ADD CONSTRAINT       -- 添加外键（固定写法）
  9. `sc_stu_sid`           -- 外键名（可以随意起名，但是需要注意：不能跟其他外键名重名）
  10. FOREIGN KEY (`sid`) -- emps中 p_id  依赖 manger中的 id
  11. REFERENCES `stu` (`sid`); -- 引用 manager 中的id 
  15. -- sc 到 course 的外键关联
  17. ALTER TABLE `sc`   -- emps 依赖 manager
  18. ADD CONSTRAINT       -- 添加外键（固定写法）
  19. `sc_course_cid`           -- 外键名（可以随意起名，但是需要注意：不能跟其他外键名重名）
  20. FOREIGN KEY (`cid`) -- emps中 p_id  依赖 manger中的 id
  21. REFERENCES `course` (`cid`); -- 引用 manager 中的id
  1. 添加数据 顺序
    1. 添加学生，课程；
    2. 再添加成绩；
  2. 请给上面3个表分别添加数据； ```sql INSERT INTO wangdi_stu (sname) VALUES (“zhangsan”),(“sili”),(“sufga”),(“sfjaofs”);

INSERT INTO wangdi_course (cname) VALUES (“数学”),(“语文”),(“英语”),(“体育”);

select *from wangdi_course;

select * from wangdi_sc;

INSERT INTO wangdi_sc (sid,cid,score) VALUES (“1”,”1”,77),(“2”,”1”,”80”),(“3”,”1”,”90”), (“1”,”2”,88),(“2”,”2”,”90”),(“3”,”2”,”80”), (“1”,”3”,10),(“2”,”3”,”10”),(“3”,”3”,”66”), (“1”,”4”,99),(“2”,”4”,”66”),(“3”,”4”,”20”);

  2. 5. 查询语文成绩大于80分或者数学成绩大于70分的同学；
  3. ```sql
  4. -- 查询语文成绩大于80分或者数学成绩大于70分的同学；
  5. select wangdi_sc.sid,wangdi_sc.cid,score,cname,sname from wangdi_sc
  6. inner join wangdi_course on wangdi_course.cid = wangdi_sc.cid
  7. inner join wangdi_stu on wangdi_sc.sid = wangdi_stu.sid
  8. where cname= "语文" and score > 80 or cname ="数学" and score > 70;
  11. -- -- 查询语文成绩大于80分或者数学成绩大于70分的同学学号sid，姓名sname 不要有重复数据；

  1. 统计每个同学的总分，平均分；

    1. select wangdi_sc.sid,sum(score),avg(score),sname from wangdi_sc
    2. inner join wangdi_stu 
    3. on wangdi_sc.sid = wangdi_stu.sid
    4. GROUP BY wangdi_sc.sid

  2. 查询班级总分排名前三名的学生；

    1. select wangdi_sc.sid,sum(score),sname from wangdi_sc
    2. inner join wangdi_stu 
    3. on wangdi_sc.sid = wangdi_stu.sid
    4. GROUP BY wangdi_sc.sid
    5. having 
    6. sum(score) >= (select distinct(总分)   from (select sid,sum(score) 总分 from wangdi_sc group by sid order by sum(score) ) a 
    7. order by 总分 desc limit 2,1)

  3. 查询每科都及格的同学信息；

    1. select sname from wangdi_stu inner join wangdi_sc on wangdi_stu.sid=wangdi_sc.sid group by wangdi_sc.sid having min(score)>60