數據庫（MySQL）練習
- 一、練習
- 1.15練習
- 練習
- 二、注意事項
- 2.1 第四天
一、練習
1.15練習
win11安裝配置MySQL超詳細教程: https://baijiahao.baidu.com/s?id=1786910666566008458&wfr=spider&for=pc
準備工作：
mysql -uroot -p #以管理員身份登錄
mysql> select user(); #查看當前登錄賬戶，登錄方式
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)mysql> show databases; #查看當下賬戶的所有庫
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb1_test |
| mydb2_stuinfo |
| mydb3_employee |
| mydb4_product |
| mydb5_sales |
| mydb_temp1 |
| mysql |
| performance_schema |
| sys |
| temp1 |
+--------------------+
11 rows in set (0.00 sec)mysql> create database mydb6_product; #創建新庫 mydb6_product
Query OK, 1 row affected (0.02 sec)mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb1_test |
| mydb2_stuinfo |
| mydb3_employee |
| mydb4_product |
| mydb5_sales |
| mydb6_product |
| mydb_temp1 |
| mysql |
| performance_schema |
| sys |
| temp1 |
+--------------------+
12 rows in set (0.00 sec)
mysql> use mydb6_product; #轉換當前登錄庫
Database changed
mysql> select database();
+---------------+
| database() |
+---------------+
| mydb6_product |
+---------------+
1 row in set (0.00 sec)
employees表：
主鍵：primary key
不能為空：not null
設置默認值：default’***'
mysql> create table employees(id int primary key , name varchar(50) not null ,age int ,gender varchar(10) not null default'unknown' ,salary float);
Query OK, 0 rows affected (0.04 sec)mysql> desc employees;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(50) | NO | | NULL | |
| age | int | YES | | NULL | |
| gender | varchar(10) | NO | | unknown | |
| salary | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.02 sec)
orders表
mysql> create table orders(id int primary key , name varchar(100) not null ,price float,
quantity int ,category varchar(50));
Query OK, 0 rows affected (0.03 sec)mysql> desc orders;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(100) | NO | | NULL | |
| price | float | YES | | NULL | |
| quantity | int | YES | | NULL | |
| category | varchar(50) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
invoices表：
主鍵自增長：primary key auto_increment
外鍵關聯到orders表的id列：,foreign key(order_id) references orders(id) 注意用逗號分隔后再開始寫外鍵關聯
日期型：date
要求數據大于0：check(數據>0)
mysql> create table invoices(number int primary key auto_increment, order_id int ,foreign key(order_id) references orders(id) ,in_date date,total_amount float check(total_amount>0));
Query OK, 0 rows affected (0.03 sec)mysql> desc invoices;
+--------------+-------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------+------+-----+---------+----------------+
| number | int | NO | PRI | NULL | auto_increment |
| order_id | int | YES | MUL | NULL | |
| in_date | date | YES | | NULL | |
| total_amount | float | YES | | NULL | |
+--------------+-------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
3張表如下:
mysql> show tables;
+-------------------------+
| Tables_in_mydb6_product |
+-------------------------+
| employees |
| invoices |
| orders |
+-------------------------+
3 rows in set (0.01 sec)
練習
二、注意事項
2.1 第四天
日期需要以字符串形式添加 eg.‘2000-10-1’
mysql> insert into student2 value(1001 , '李成峰','男','2000-10-1');
Query OK, 1 row affected (0.01 sec)mysql> select * from student2;
+------+--------+------+------------+
| id | name | sex | birthday |
+------+--------+------+------------+
| 1001 | 李成峰 | 男 | 2000-10-01 |
+------+--------+------+------------+
1 row in set (0.00 sec)
delete一定要帶where，
mysql> select * from student2;
+------+--------+------+------------+
| id | name | sex | birthday |
+------+--------+------+------------+
| 1001 | 李成峰 | 男 | 2000-10-01 |
| 1002 | 薛佳尹 | 女 | 2012-12-12 |
| 1003 | 孫德勝 | 男 | 1998-12-31 |
| 1004 | 鳳飛飛 | 女 | 2009-03-15 |
| 1005 | 尹志平 | 男 | 1235-09-01 |
+------+--------+------+------------+
5 rows in set (0.00 sec)mysql> delete from student2 where id=1005; #單條刪除
Query OK, 1 row affected (0.02 sec)mysql> select * from student2;
+------+--------+------+------------+
| id | name | sex | birthday |
+------+--------+------+------------+
| 1001 | 李成峰 | 男 | 2000-10-01 |
| 1002 | 薛佳尹 | 女 | 2012-12-12 |
| 1003 | 孫德勝 | 男 | 1998-12-31 |
| 1004 | 鳳飛飛 | 女 | 2009-03-15 |
+------+--------+------+------------+
4 rows in set (0.00 sec)
delete from student2不加where，刪除全表內容，表結構還存在
drop 刪除包括表結構
刪除之前可開啟一個事務，方便回溯
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
開啟一個事務，方便回溯mysql> delete from student2 ;
Query OK, 4 rows affected (0.00 sec)mysql> select * from student2;
Empty set (0.00 sec)mysql> desc student2;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(30) | NO | | NULL | |
| sex | char(2) | YES | | 女 | |
| birthday | date | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)mysql> rollback;
Query OK, 0 rows affected (0.00 sec)mysql> select * from student2;
+------+--------+------+------------+
| id | name | sex | birthday |
+------+--------+------+------------+
| 1001 | 李成峰 | 男 | 2000-10-01 |
| 1002 | 薛佳尹 | 女 | 2012-12-12 |
| 1003 | 孫德勝 | 男 | 1998-12-31 |
| 1004 | 鳳飛飛 | 女 | 2009-03-15 |
+------+--------+------+------------+
4 rows in set (0.00 sec)
