三天入门MySQL---DAY 1---库和表的创建、删除、更新

已于 2022-06-04 15:24:40 修改
阅读量196 收藏
点赞数
分类专栏： 数据库 文章标签： 数据库开发 mysql
于 2022-05-19 16:14:24 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43821216/article/details/124862168
版权

目录

1.库操作

1.1新建库

1.2删除库

2.表操作

2.1新建表

2.2删除表

2.3更新表

2.4插入数据

2.5更新数据

2.6删除数据

总结

利用命令行和navicat共同演示

1.库操作

库操作包含新建、删除。

1.1新建库

CREATE DATABASE 数据库名;

1.2删除库

DROP DATABASE <库名>;

2.表操作

2.1新建表

CREATE TABLE IF NOT EXISTS <表名>(
   <列名1> <数据类型> <数据约束>,
   <列名2> <数据类型> <数据约束>,
   <列名3> <数据类型> <数据约束>,
   <列名4> <数据类型> <数据约束>,
    。。。。。。。
   PRIMARY KEY (<列名1,列名2>)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

  • ENGINE=InnoDB ，InnoDB 存储引擎（后面总结会有引擎分类）默认
  • CHARSET=utf8，编码

示例：

CREATE TABLE IF NOT EXISTS `Customers`(
	cust_id VARCHAR(255) NOT NULL COMMENT '客户id',
	cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名',
	PRIMARY KEY(cust_id)
);

2.2删除表

DROP TABLE <表名>;

2.3更新表

  • 更新表名
ALTER TABLE oldname RENAME TO newname;

  • 删除、添加

ALTER TABLE tablna DROP id;
#删除id列
ALTER TABLE tablna ADD id INT;
#增加id列

  • 修改字段类型及名称

ALTER TABLE tak MODIFY c CHAR(10);

2.4插入数据

INSERT `Customers` VALUES ('a1','andy'),('a2','ben'),('a3','tony'),('a4','tom'),('a5','an'),('a6','lee'),('a7','hex');

2.5更新数据

UPDATE Customers SET cust_id='a9' WHERE cust_id='a1';

修改后结果：

​

2.6删除数据

  • DELETE

删除某一条数据 

DELETE FROM customers WHERE cust_id='a3';

  • TRUNCATE

清空customers表的数据 

truncate customers ;

总结

  • 库操作
  • 表操作
  • 数据库引擎对比
引擎类别MyISAM InnoDB 
是否支持行级锁只支持表级支持

      是否支持事务

否支持
是否支持外键否支持

  • 下章节数据准备（我已经帮大家准备好了数据库建表语句和数据库插入语句，数据来源《Mysql必知必会》）
  • 建表语句
########################
# Create customers table
########################
CREATE TABLE customers
(
  cust_id      int       NOT NULL AUTO_INCREMENT,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

#########################
# Create orderitems table
#########################
CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;


#####################
# Create orders table
#####################
CREATE TABLE orders
(
  order_num  int      NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL ,
  cust_id    int      NOT NULL ,
  PRIMARY KEY (order_num)
) ENGINE=InnoDB;

#######################
# Create products table
#######################
CREATE TABLE products
(
  prod_id    char(10)      NOT NULL,
  vend_id    int           NOT NULL ,
  prod_name  char(255)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL ,
  prod_desc  text          NULL ,
  PRIMARY KEY(prod_id)
) ENGINE=InnoDB;

######################
# Create vendors table
######################
CREATE TABLE vendors
(
  vend_id      int      NOT NULL AUTO_INCREMENT,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL ,
  vend_city    char(50) NULL ,
  vend_state   char(5)  NULL ,
  vend_zip     char(10) NULL ,
  vend_country char(50) NULL ,
  PRIMARY KEY (vend_id)
) ENGINE=InnoDB;

###########################
# Create productnotes table
###########################
CREATE TABLE productnotes
(
  note_id    int           NOT NULL AUTO_INCREMENT,
  prod_id    char(10)      NOT NULL,
  note_date datetime       NOT NULL,
  note_text  text          NULL ,
  PRIMARY KEY(note_id),
  FULLTEXT(note_text)
) ENGINE=MyISAM;


#####################
# Define foreign keys
#####################
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);
  • 插入数据语句
########################################
# MySQL Crash Course
# http://www.forta.com/books/0672327120/
# Example table population scripts
########################################


##########################
# Populate customers table
##########################
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');


########################
# Populate vendors table
########################
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');


#########################
# Populate products table
#########################
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi-use');



#######################
# Populate orders table
#######################
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20005, '2005-09-01', 10001);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20006, '2005-09-12', 10003);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20007, '2005-09-30', 10004);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20008, '2005-10-03', 10005);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20009, '2005-10-08', 10001);


###########################
# Populate orderitems table
###########################
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'ANV01', 10, 5.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'ANV02', 3, 9.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 3, 'TNT2', 5, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 4, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'JP2000', 1, 55);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'TNT2', 100, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'FC', 50, 2.50);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'OL1', 1, 8.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'SLING', 1, 4.49);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 4, 'ANV03', 1, 14.99);

#############################
# Populate productnotes table
#############################
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(101, 'TNT2', '2005-08-17',
'Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(102, 'OL1', '2005-08-18',
'Can shipped full, refills not available.
Need to order new can if refill needed.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(103, 'SAFE', '2005-08-18',
'Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(104, 'FC', '2005-08-19',
'Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(105, 'TNT2', '2005-08-20',
'Included fuses are short and have been known to detonate too quickly for some customers.
Longer fuses are available (item FU1) and should be recommended.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(106, 'TNT2', '2005-08-22',
'Matches not included, recommend purchase of matches or detonator (item DTNTR).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(107, 'SAFE', '2005-08-23',
'Please note that no returns will be accepted if safe opened using explosives.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(108, 'ANV01', '2005-08-25',
'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(109, 'ANV03', '2005-09-01',
'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(110, 'FC', '2005-09-01',
'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(111, 'SLING', '2005-09-02',
'Shipped unassembled, requires common tools (including oversized hammer).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(112, 'SAFE', '2005-09-02',
'Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(113, 'ANV01', '2005-09-05',
'Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(114, 'SAFE', '2005-09-07',
'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor.'
);

Never Hanoi
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL：-数据库和表的创建与管理PPT课件.ppt
11-23
MySQL：-数据库和表的创建与管理PPT课件.ppt
《MySQL必知必会》建表脚本create.sql和填充数据脚本populate.sql
科技向善
11-13 657
自己检测初始化数据脚本： -- 第1个表，供应商表 DESC vendors; -- 第2个表，产品表 DESC products; -- 第3个表，顾客信息表 DESC customers; -- 第4个表，订单表 DESC orders; -- 第5个表，订单条目表 DESC orderitems; -- 第6个表，产品注释表 DESC productnotes; SELECT COUNT(1) FROM vendors; SELECT * FROM vendors; SELECT CO
mysql populate 操作_《MySQL必知必会》学习笔记（1-8）
weixin_32155269的博客
02-17 329
《MySQL必知必会》学习笔记 目录本篇内容了解SQL、MySQL简介、使用MySQL、检索数据 、排序检索数据、过滤数据、用通配符进行过滤MySQL安装 自行谷歌《MySQL 必知必会》创建样例表(1)创建一个新的数据源create database crashcourse; (这里使用书中的数据源名称)(2)选择新的数据源use crashcourse;(3)执行creat.sql脚本sou...
SQL Server数据库创建数据库、用户、表和插入数据（Transact-SQL）
weixin_46831482的博客
04-29 8665
SQLServer建数据库并、创建用户、创建模式、建表并插入数据 一、建数据库并创建用户 首先使用 刷新左侧“对象资源管理器”，刷新后新建的数据库会出现在数据库列表中 接下来就该创建数据库的用户，步骤如下： 1.首先，点击“us1”左侧的“+”，会出现隐藏的内容，点击刚刚显示出来的内容中的“安全性”左侧的“+”号 2.选中“安全性”下方的“用户”，右击用户，选中“新建用户”
MySQL必知必会——使用MySQL命令行实用程序 创建样例表（create.sql和populate.sql）
weixin_44649143的博客
09-23 1334
步骤如下: 首先进入mysql必知必会这本书中提供的网址http://www.forta.com/books/0672327120/，下载SQL脚本文件。解压该压缩包，得到两个sql文件，分别是create.sql和populate.sql。 创建一个新的数据源。 （我选择了和书中相同的名字） 保证选择新的数据源。 用第三章中的USE命令。 执行create.sql脚本。 source 后面跟...
mysql必知必会，使用命令行创建数据源,执行create.sql脚本文件，用populate.sql文件填充各个新表
hukangqiang的博客
02-12 4559
**使用命令行实用程序创建数据源** 当前在学习Ben Forta的MySQL必知必会，下载了提供的数据，需要新建一个数据源，使用命令行实现如下： 1.打开CMD; 2.mysql -h 127.20.xx.x -u root -p 对应的含义： mysql是登录数据库的命令，-h 后面跟服务器的IP，由于本示例MySql服务器安装在本地，因此IP地址为本机IP地址，这一数值取决于个
mysql-operator：在Kubernetes中创建，操作和扩展自愈MySQL集群
02-02
产品特点MySQL Operator提供以下核心功能： 轻松创建和删除Kubernetes中的高可用性MySQL InnoDB集群自动化数据库备份，故障检测和恢复将自动备份安排为群集定义的一部分创建“按需”备份。 使用备份还原数据库要求...
mysql-connector-java-8.0.22.jar
08-14
包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-...
PHP_MySQL教程-第三天 基本函数第1/2页
10-30
PHP_MySQL教程-第三天 基本函数
mysql5.7-ocp-888-题库与解析
09-21
内含两份MySQL 5.7 OCP（1Z0-888）MySQL 5.7 Database Administrator题库，前段时间考证自用。亲测大约有90%的考试题量，通过考试足以。
MySQL_create.sql
11-09
MySQL_create.sql
3天入门MySQL.pdf
05-10
很多朋友最开始学习数据库的时候，很难理解数据库的作用。理不清楚，数据库与我们现实生活、虚拟生 活到底有什么样的关系。
3天入门MySQL
02-13
3天入门MySQL...............................................................................................................................
create_Sql
05-29
博文链接：https://ayong2010.iteye.com/blog/921059
MySQL必知必会中样例表的创建
Rocket,Qian的博客
07-20 269
MySQL必知必会中样例表的创建1.创建表course2.执行create.sql脚本3.填充表4.使用表 MySQL必知必会数据表下载地址 1.创建表course create database `course`; 我遇到的问题是course没有加反引号，一直报错，这个表名需要加反引号。 2.执行create.sql脚本 source C:/Users/apple/Documents/mysql_scripts/create.sql; 3.填充表 source C:/Users/apple/Do
三天入门MySQL---DAY 3--子查询、联结表、组合查询
weixin_43821216的博客
06-12 139
序言：上次课程我们学习了数据汇总与分组，这次我们进行数据库进阶学习子查询、联结表、组合查询。
MySQL必知必会-4导入项目SQL脚本
zoroday的专栏
02-01 6553
SQL脚本在前言中有下载地址。 1、解压mysql_scripts，会有两个文件分别为create.sql和populate.sql，导入的顺序不能乱，首先是create.sql，再次populate.sql 2、运行Navicat Premium，右键连接名，选择“新建数据库”。   3、选择字符集，如图所示，数据库名自定义。
mysql必知必会样例表populate.sql
mysqltest的专栏
08-09 985
######################################## # MySQL Crash Course # http://www.forta.com/books/0672327120/ # Example table population scripts ######################################## ##############
mysql-connector-java和mysql-connector-j的区别
最新发布
09-06
mysql-connector-java和mysql-connector-j是同一个MySQL官方提供的JDBC驱动程序，它用于连接MySQL数据库和Java应用程序。mysql-connector-java是MySQL Connector/J的完整名称，其中“J”表示Java，是指这是一个Java...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值