003-MySQL数据库操作

DDL（Data Definition Language, 数据定义语言）

• create alter drop

DML（Data Manipulation Language, 数据操作语言）

• insert delete update

DQL（Data Query Language, 数据查询语言）

• select from where

DCL（Data Control Language, 数据控制语言）

DDL数据库操作

• 创建数据库
  • create database <数据库名>;
  • create database 数据库名称 [设置编码格式];
  • create database <数据库名> default charset gbk; 创建数据库并指定默认字符集
  • create database if not exists <数据库名> default charset=utf8;如果存在不报错
• 查询数据库
  • show databases; 显示所有数据库
  • show create database 数据库名 查看数据库的创建过程
• 删除数据库
  • drop database 数据库名;
• 选择数据库
  • use <数据库名>;
  • select database();查看正在使用的数据库（8.0以后版本需要基于select查询来获取当前数据库）

DDL数据表操作

create database if not exists db_czc default charset utf8;  
  
USE db_czc;  
select DATABASE();  
  
create table czc_1(  
    id tinyint,  
    username varchar(20),  
    passwd char(32),  
    content text  
) engine=innodb default charset utf8;  
  
show tables;  
  
desc czc_1;  
  
  
-- 添加字段  
-- alter table czc_1 add addtime date first;  
alter table czc_1 add addtime date after content;  
  
-- 修改字段名称或字段类型  
alter table czc_1 change username user varchar(40);  -- 仅修改字段类型change可以改成modify  
desc czc_1;  
  
-- 删除某个字段  
alter table czc_1 drop addtime;  
desc czc_1;  
  
-- 修改数据表名称  
rename table czc_1 to czc_2;  
  
-- 显示数据表创建过程  
show create table czc_2;  
  
-- 删除数据表  
drop table czc_2;

习题

create table customers(  
   c_num int(11),  
   c_name varchar(50),  
   c_contact varchar(50),  
   c_city  varchar(50),  
   c_birth date  
);  
  
alter table customers change c_contact c_phone varchar(50);  
desc customers;  
  
rename table customers to customers_info;  
  
alter table customers drop c_city;  
desc customers;  
  
alter table customers modify c_name varchar(70);  
  
show create table customers;  
desc customers;

DML数据操作

use db_czc;  
create table tb_user(  
   id int,  
   username varchar(20),  
   age tinyint unsigned,  
   gender enum('男','女','保密'),  
   address varchar(255)  
) engine=innodb default charset=utf8;  
desc tb_user;  
  
-- 添加数据  
insert into tb_user values (1,'刘备',34,'男','广州市天河区');  
insert into tb_user(id,username,age) values (2,'关羽',33);  
  
insert into tb_user values (3,'大乔',19,'女','上海市浦东新区'),  
                           (4,'小乔',18,'女','上海市浦东新区'),  
                           (5,'马超',26,'男','北京市昌平区');  
  
-- 修改数据  
update tb_user set gender='男',address='abc' where username='马鹏';  
update tb_user set age=age+1;  
  
-- 数据删除  
delete from tb_user where id=1;  
  
-- 清空数据表  
delete from tb_user;  -- 一条一天删除，知道数据表清空，索引（主键自增序列）不变，可以带条件删除  
truncate a;  -- 数据定义语言，直接重新创建新表，索引（主键自增序列）充值

练习

rename table tb_user to tb_user_1;  
  
create table tb_user(  
    id int(10),  
    name varchar(30),  
    pwd varchar(50),  
    create_time timestamp,  
    updata_time timestamp  
);  
-- 纠正版  
CREATE TABLE `tb_user` (  
  `id` int(10) COMMENT '主键ID，自增',  
  `name` varchar(30) COMMENT '用户名',  
  `pwd` varchar(50) COMMENT '密码',  
  `create_time` timestamp COMMENT '创建时间',  
  `update_time` timestamp COMMENT '更新时间',  
) ;  
  
create table tb_product(  
    id int(10),  
    name varchat(100),  
    price decimal(10,2),  
    category varchat(50),  
    inventory int(10)  
);  
-- 纠正版  
CREATE TABLE tb_user (  
  id INT(10) COMMENT '唯一性标识',  
  name VARCHAR(100) COMMENT '商品名称',  
  price decimal(10,2) COMMENT '商品价格',  
  category varchar(50) COMMENT '商品分类',  
  inventory int(10) COMMENT '商品库存'  
);

SQL约束

-- 约束： 主键、非空、唯一、默认值  
#  
create table tb_约束(  
    id int auto_increment primary key , # 自动增长列类型必须是`整型`，自动增长列必须为键(一般是`主键`)；PRIMARY KEY 拥有自动定义的 UNIQUE 约束  
    title varchar(40) unique ,  
    author varchar(20) not null,  
    passwd varchar(100),  
    性别 enum('男', '女') default '男'  
    # 或者最后面写 primary key(id)) engine=innodb default charset=utf8;  
  
desc tb_约束;  
  
-- 删除添加数据表主键，有自动增长的情况下，需要先移除自动增长约束（修改类型int auto_increment → int）  
alter table tb_约束 modify id int;  
alter table tb_约束 drop primary key ;  
desc tb_约束;  
alter table tb_约束 add primary key(id);  
alter table tb_约束 modify id int auto_increment;  
desc tb_约束;  
  
-- 插入数据  
insert into tb_约束 values (null, 'bb', 'aa', md5('77777777'), default); # 主键可以写null，会自动生成，密码用md5方法加密存储  
insert into tb_约束(title, author, passwd) values ('cc', 'dd', md5('77777777')); # 不输入主键内容会自动生成  
# truncate 会把自动增长列重置，但是delete from不会  
  
select * from tb_约束

章测试

# 1.创建表  
# 创建员工表employee，字段如下：  
# id（员工编号），name（员工名字），gender（员工性别），salary（员工薪资）  
create table employee(  
    id int auto_increment primary key ,  
    name varchar(10),  
    gender enum('男', '女') default '男',  
    salary decimal(10, 2)  
)engine=innodb, charset=utf8;  
  
# 2.插入数据  
# 1，‘张三’，‘男’，2000  
insert into employee(name, gender, salary) values ('张三', '男', 2000);  
# 2，‘李四’，‘男’，1000  
insert into employee(name, gender, salary) values ('李四', '男', 1000);  
# 3，‘王五’，‘女’，4000  
insert into employee(name, gender, salary) values ('王五', '女', 4000);  
  
# 3.修改表数据  
# 3.1 将所有员工薪水修改为5000元  
update employee set salary=5000;  
# 3.2将姓名为张三的员工薪水修改为3000元  
update employee set salary=3000 where name='张三';  
# 3.3将姓名为李四的员工薪水修改为4000元，gener改为女  
update employee set salary=4000,gender='女' where name='李四';  
# 3.4 将王五的薪水在原有基础上增加1000元  
update employee set salary=salary+1000 where name='王五';  
  
select * from employee;

# 根据要求完成如下指令：  
# 1，创建itheima的数据库  
create database itheima;  
use itheima  
# 2，创建employees表，包含如下字段：  
# id，整数  
# name，字符串  
# age，整数  
# salary，浮点数  
# 3，使用itheima数据库  
create table employees(  
    id int(10) auto_increment primary key ,  
    name varchar(10),  
    age int(5),  
    salary double  
)engine = innodb , charset = utf8;  
  
# 4，查询itheima数据库下一共几张表  
show tables;  
  
# 5，删除employees表。  
drop table employees;

# 创建一个学生表，包含学生编号(id)、姓名(name)、性别(gender)和年龄(age)四个字段，  
create table student(  
    id int(10) auto_increment primary key ,  
    name varchar(10),  
    gender enum('男','女') default '男',  
    age int(10)  
) engine = innodb, charset = utf8;  
# id数据类型自行设置。向该表中插入三条记录，  
# 分别为：1，小明，男，18岁；2，小红，女，19岁；3，小华，男，20岁。接着进行以下修改操作：  
insert into student (name, gender, age) values ('小明','男',18),('小红','女',19),('小华','男',20);  
select * from student;  
  
# 将小明的年龄修改为20岁。  
update student set age=20 where name = '小明';  
# 将小华的性别修改为女。  
update student set gender='女' where name='小华';  
# 删除姓名为小红的记录。  
delete from student where name='小红';  
select * from student;

# 按要求完成如下操作：
# 1，创建用户表，表中涵盖如下信息。
create table if not exists user (
    id int(11) auto_increment primary key comment '自增ID',
    uid int(11) unique comment '用户ID',
    nick_name varchar(64) comment '昵称',
    achievement int(11) default 0 comment '成就值',
    level int(11) comment '用户等级',
    job varchar(32) comment '职业方向',
    register_time datetime comment '注册时间'
)engine = innodb , charset = utf8;

# 2，请在用户信息表 level 列的后面增加一列，列名为：school，最多可保存 15 个汉字；
alter table user add school varchar(30);
desc user;

# 并将 job 列的列名修改为 profession，同时 varchar 字段长度变为 10；
alter table user change job profession varchar(10);
desc user;

# achievement 列的默认值设置为 0。修改之后的结果如下：
alter table user modify achievement int(11) default 0;

# 创建表
create table if not exists computers
(
    id            int auto_increment primary key,
    name          varchar(200) not null comment '电脑的名字',
    brand         varchar(50)  not null comment '电脑的品牌',
    cpu           varchar(100) not null comment '电脑处理器CPU',
    video_card    varchar(100) null comment '电脑的显卡',
    color         varchar(50)  not null comment '电脑的颜色',
    memory        varchar(100) not null comment '电脑的运行内存',
    disk          int          not null comment '电脑的硬盘空间 单位是GB',
    weight        float        not null comment '电脑的重量,单位是千克',
    classify_type int          not null comment '电脑的分类: 1. 笔记本 2. 台机 3. 工业电脑',
    price         float        not null comment '电脑的价格'
);

# 插入测试数据
insert into computers
values (null,'拯救者R9000P','联想','R7-5800H','RTX3060','黑色',16,512,2.58,1,8099),
     (null,'华硕天选3','华硕','i7-12700H','RTX3060','蓝色',16,512,4.15,1,8699),
     (null,'宏碁新暗影骑士·擎','宏碁','i5-12500H','RTX3050','黑色',16,512,2.5,1,6569),
     (null,'外星人ALIENWARE 2022','戴尔','i7-12700H','RTX3050','黑色',16,512,3.77,1,12499),
     (null,'ROG幻16 2022','华硕','i9-12900H','RTX3080Ti','银白色',32,1024,4.15,1,21999),
     (null,'联想拯救者Y9000K 2022','联想','i7-12800HX','RTX3070Ti','黑色',32,1024,5.1,1,17999),
     (null,'戴尔（DELL）13pro灵越5320','戴尔','i7-1260P','集成显卡','银白色',16,512,2.05,1,7699),
     (null,'联想小新Air14','联想','i5-1155G7','集成显卡','粉色',8,256,2.2,1,3299),
     (null,'惠普(HP)星13','惠普','i5-1155G7','集成显卡','粉色',16,512,1.3,1,4399),
     (null,'Apple MacBook Air','苹果','M2芯片','M2集成显卡','银白色',8,256,2.37,1,9499),
     (null,'外星人（alienware）Aurora R13','戴尔','i9-12900KF','RTX-3080Ti','黑色',64,2048,24,2,39298),
     (null,'宏碁(Acer) 暗影骑士·威N93','宏碁','i5-12400F','GTX1660S','黑色',16,512,7.64,2,6299),
     (null,'联想（ThinkStation）P920','联想','xeon至强处理器','P6000','黑色',256,122888,19,3,98699),
     (null,'惠普(HP)暗影精灵8Plus','惠普','i7-12700H','RTX3080Ti','黑色',16,1024,4.99,1,14999);


# 编写SQL完成相关需求。
# 请查询CPU是i7-12700H,显卡是RTX3060的电脑。
select name from computers where cpu='i7-12700H' and video_card='RTX3060';
# 请查询CPU是i7 硬盘空间是1T的所有电脑。
select name from computers where cpu like 'i7%' and disk=1024;
# 请查询CPU是i7 或者CPU是i9的电脑信息。
select name from computers where cpu like 'i7%' or cpu like 'i9%';
# 请查询cpu是i7 或者 显卡是RTX3060的电脑。
select name from computers where cpu like 'i7%' or video_card='RTX3060';
# 请查询cpu不是i7的所有电脑。
select name from computers where not cpu like 'i7%';
# 请查询cpu是i7或者i9, 显卡是RTX3080Ti 颜色不是粉色的的笔记本电脑。
select name from computers where (cpu like 'i7%' or cpu like 'i9%') and video_card='RTX3080Ti' and not color='粉色';