mysql的学习,多练习才是最重要的。
关注我的云原生社区:云原生社区
关注我的英语社区:从零开始学英语
一个电商网站通常由多个模块组成,每个模块负责不同的功能和任务。常见模块如下:
以用户模块为例,创建一个名为"User"的数据库表 ,可能包含:
MySQL [(none)]> create database store;
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> use store
CREATE TABLE User (
UserID INT PRIMARY KEY,
Username VARCHAR(50) NOT NULL,
Password VARCHAR(100) NOT NULL,
Email VARCHAR(100) NOT NULL,
Phone VARCHAR(20),
FirstName VARCHAR(50),
LastName VARCHAR(50),
Address VARCHAR(100),
City VARCHAR(50),
PostalCode VARCHAR(20),
Country VARCHAR(50),
Gender ENUM('Male', 'Female', 'Not available'),
Birthdate DATE,
Avatar VARCHAR(200),
RegistrationDate DATETIME,
LastLoginDate DATETIME,
IsActive BOOLEAN,
IsAdmin BOOLEAN
);
INSERT INTO User (UserID, Username, Password, Email, Phone, FirstName, LastName, Address, City, PostalCode, Country, Gender, Birthdate, Avatar, RegistrationDate, LastLoginDate, IsActive, IsAdmin)
VALUES
(1, 'johnsmith', 'password123', 'john@example.com', '1234567890', 'John', 'Smith', '123 Main St', 'New York', '10001', 'USA', 'Male', '1990-01-01', 'https://example.com/avatar1.jpg', '2023-06-01 10:00:00', '2023-06-02 15:00:00', 1, 0),
(2, 'janedoe', 'password456', 'jane@example.com', '0987654321', 'Jane', 'Doe', '456 Elm St', 'Los Angeles', '90001', 'USA', 'Female', '1995-03-15', 'https://example.com/avatar2.jpg', '2023-06-03 12:00:00', '2023-06-05 09:30:00', 1, 0),
(3, 'mikebrown', 'password789', 'mike@example.com', '9876543210', 'Mike', 'Brown', '789 Oak St', 'Chicago', '60601', 'USA', 'Male', '1988-07-10', 'https://example.com/avatar3.jpg', '2023-06-08 09:00:00', '2023-06-09 16:45:00', 1, 1),
(4, 'sarahwilson', 'passwordabc', 'sarah@example.com', '0123456789', 'Sarah', 'Wilson', '987 Pine St', 'San Francisco', '94101', 'USA', 'Female', '1992-12-25', 'https://example.com/avatar4.jpg', '2023-06-10 14:30:00', '2023-06-12 11:20:00', 1, 0);
MySQL [store]> select UserID,FirstName,LastName from User;
+--------+-----------+----------+
| UserID | FirstName | LastName |
+--------+-----------+----------+
| 1 | John | Smith |
| 2 | Jane | Doe |
| 3 | Mike | Brown |
| 4 | Sarah | Wilson |
+--------+-----------+----------+
4 rows in set (0.00 sec)
CREATE TABLE ShoppingCart (
CartID INT PRIMARY KEY AUTO_INCREMENT,
UserID INT NOT NULL,
ProductID INT NOT NULL,
Quantity INT NOT NULL,
Price DECIMAL(10, 2) NOT NULL,
CreatedAt DATETIME
);
INSERT INTO ShoppingCart (UserID, ProductID, Quantity, Price, CreatedAt)
VALUES
(1, 1001, 2, 39.99, '2023-06-15 10:00:00'),
(2, 1002, 1, 19.99, '2023-06-15 10:30:00'),
(3, 1001, 3, 39.99, '2023-06-15 11:00:00'),
(4, 1003, 1, 49.99, '2023-06-15 12:00:00');
MySQL [store]> select * from ShoppingCart;
+--------+--------+-----------+----------+-------+---------------------+
| CartID | UserID | ProductID | Quantity | Price | CreatedAt |
+--------+--------+-----------+----------+-------+---------------------+
| 1 | 1 | 1001 | 2 | 39.99 | 2023-06-15 10:00:00 |
| 2 | 2 | 1002 | 1 | 19.99 | 2023-06-15 10:30:00 |
| 3 | 3 | 1001 | 3 | 39.99 | 2023-06-15 11:00:00 |
| 4 | 4 | 1003 | 1 | 49.99 | 2023-06-15 12:00:00 |
+--------+--------+-----------+----------+-------+---------------------+
4 rows in set (0.00 sec)
INSERT INTO User (UserID, Username, Password, Email, Phone, FirstName, LastName, Address, City, PostalCode, Country, Gender, Birthdate, Avatar, RegistrationDate, LastLoginDate, IsActive, IsAdmin)
VALUES
(5, 'mufenggrow', 'password123', 'mufenggrow@example.com', '1234567890', 'Mufeng', 'Grow', '456 Maple St', 'London', 'SW1A 1AA', 'UK', 'Male', '1990-05-20', 'https://example.com/avatar5.jpg', '2023-06-18 09:00:00', '2023-06-18 09:00:00', 1, 0);
drop table if exists students;
create table students (
studentNo varchar(10) primary key,
name varchar(10),
sex varchar(1),
hometown varchar(20),
age tinyint(4),
class varchar(10),
card varchar(20)
)
insert into students values
('001', '王昭君', '女', '北京', '20', '1班', '340322199001247654'),
('002', '诸葛亮', '男', '上海', '18', '2班', '340322199002242354'),
('003', '张飞', '男', '南京', '24', '3班', '340322199003247654'),
('004', '白起', '男', '安徽', '22', '4班', '340322199005247654'),
('005', '大乔', '女', '天津', '19', '3班', '340322199004247654'),
('006', '孙尚香', '女', '河北', '18', '1班', '340322199006247654'),
('007', '百里玄策', '男', '山西', '20', '2班', '340322199007247654'),
('008', '小乔', '女', '河南', '15', '3班', null),
('009', '百里守约', '男', '湖南', '21', '1班', ''),
('010', '妲己', '女', '广东', '26', '2班', '340322199607247654'),
('011', '李白', '男', '北京', '30', '4班', '340322199005267754'),
('012', '孙膑', '男', '新疆', '26', '3班', '340322199000297655');
4 创建表,插入数据
[root@leaner mysql001]# mysql -pSmile-123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| aa |
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.01 sec)
mysql> use test;
Database changed
mysql> create table students(studentNo varchar(5),name varchar(30),sex varchar(5), hometown varchar(100), age int, class varchar(10), card varchar(30));
mysql> insert into students values('001', '王昭君', '女', '北京', 20, '1班', '340322199001247654'),
('002', '诸葛亮', '男', '上海', 18, '2班', '340322199002242354'),
('003', '张飞', '男', '南京', 24, '3班', '340322199003247654'),
('004', '白起', '男', '安徽', 22, '4班', '340322199005247654'),
('005', '大乔', '女', '天津', 19, '3班', '340322199004247654'),
('006', '孙尚香', '女', '河北', 18, '1班', '340322199006247654'), ('007', '百里玄策', '男', '山西', 20, '2班', '340322199007247654'),
('008', '小乔', '女', '河南', 15, '3班', null),
('009', '百里守约', '男', '湖南', 21, '1班', ''),
('010', '妲己', '女', '广东', 26, '2班', '340322199607247654'),
('011', '李白', '男', '北京', 30, '4班', '340322199005267754'),
('012', '孙膑', '男', '新疆', 26, '3班', '340322199000297655');
mysql> select * from students;
+-----------+--------------+------+----------+------+-------+--------------------+
| studentNo | name | sex | hometown | age | class | card |
+-----------+--------------+------+----------+------+-------+--------------------+
| 001 | 王昭君 | 女 | 北京 | 20 | 1班 | 340322199001247654 |
| 002 | 诸葛亮 | 男 | 上海 | 18 | 2班 | 340322199002242354 |
| 003 | 张飞 | 男 | 南京 | 24 | 3班 | 340322199003247654 |
| 004 | 白起 | 男 | 安徽 | 22 | 4班 | 340322199005247654 |
| 005 | 大乔 | 女 | 天津 | 19 | 3班 | 340322199004247654 |
| 006 | 孙尚香 | 女 | 河北 | 18 | 1班 | 340322199006247654 |
| 007 | 百里玄策 | 男 | 山西 | 20 | 2班 | 340322199007247654 |
| 008 | 小乔 | 女 | 河南 | 15 | 3班 | NULL |
| 009 | 百里守约 | 男 | 湖南 | 21 | 1班 | |
| 010 | 妲己 | 女 | 广东 | 26 | 2班 | 340322199607247654 |
| 011 | 李白 | 男 | 北京 | 30 | 4班 | 340322199005267754 |
| 012 | 孙膑 | 男 | 新疆 | 26 | 3班 | 340322199000297655 |
+-----------+--------------+------+----------+------+-------+--------------------+
12 rows in set (0.00 sec)
mysql>
题目:
# 查询小乔的年龄
mysql> select name,age from students where name="小乔";
+--------+------+
| name | age |
+--------+------+
| 小乔 | 15 |
+--------+------+
1 row in set (0.00 sec)
# 查询20岁以下的学生
mysql> select * from students where age < 20;
+-----------+-----------+------+----------+------+-------+--------------------+
| studentNo | name | sex | hometown | age | class | card |
+-----------+-----------+------+----------+------+-------+--------------------+
| 002 | 诸葛亮 | 男 | 上海 | 18 | 2班 | 340322199002242354 |
| 005 | 大乔 | 女 | 天津 | 19 | 3班 | 340322199004247654 |
| 006 | 孙尚香 | 女 | 河北 | 18 | 1班 | 340322199006247654 |
| 008 | 小乔 | 女 | 河南 | 15 | 3班 | NULL |
+-----------+-----------+------+----------+------+-------+--------------------+
4 rows in set (0.00 sec)
# 查询家乡不在北京的学生
mysql> select * from students where hometown <> "北京";
+-----------+--------------+------+----------+------+-------+--------------------+
| studentNo | name | sex | hometown | age | class | card |
+-----------+--------------+------+----------+------+-------+--------------------+
| 002 | 诸葛亮 | 男 | 上海 | 18 | 2班 | 340322199002242354 |
| 003 | 张飞 | 男 | 南京 | 24 | 3班 | 340322199003247654 |
| 004 | 白起 | 男 | 安徽 | 22 | 4班 | 340322199005247654 |
| 005 | 大乔 | 女 | 天津 | 19 | 3班 | 340322199004247654 |
| 006 | 孙尚香 | 女 | 河北 | 18 | 1班 | 340322199006247654 |
| 007 | 百里玄策 | 男 | 山西 | 20 | 2班 | 340322199007247654 |
| 008 | 小乔 | 女 | 河南 | 15 | 3班 | NULL |
| 009 | 百里守约 | 男 | 湖南 | 21 | 1班 | |
| 010 | 妲己 | 女 | 广东 | 26 | 2班 | 340322199607247654 |
| 012 | 孙膑 | 男 | 新疆 | 26 | 3班 | 340322199000297655 |
+-----------+--------------+------+----------+------+-------+--------------------+
10 rows in set (0.00 sec)
# 查询学号为007的学生身份证号
mysql> select studentNo,card from students where studentNo = '007';
+-----------+--------------------+
| studentNo | card |
+-----------+--------------------+
| 007 | 340322199007247654 |
+-----------+--------------------+
1 row in set (0.00 sec)
# 查询1班的上海的学生
mysql> select * from students where hometown = "北京" and class="1班";
+-----------+-----------+------+----------+------+-------+--------------------+
| studentNo | name | sex | hometown | age | class | card |
+-----------+-----------+------+----------+------+-------+--------------------+
| 001 | 王昭君 | 女 | 北京 | 20 | 1班 | 340322199001247654 |
+-----------+-----------+------+----------+------+-------+--------------------+
1 row in set (0.00 sec)
# 查询姓孙的学生
mysql> select * from students where name like '孙%';
+-----------+-----------+------+----------+------+-------+--------------------+
| studentNo | name | sex | hometown | age | class | card |
+-----------+-----------+------+----------+------+-------+--------------------+
| 006 | 孙尚香 | 女 | 河北 | 18 | 1班 | 340322199006247654 |
| 012 | 孙膑 | 男 | 新疆 | 26 | 3班 | 340322199000297655 |
+-----------+-----------+------+----------+------+-------+--------------------+
2 rows in set (0.00 sec)
# 查询非20岁的学生
mysql> select * from students where age <> 20;
+-----------+--------------+------+----------+------+-------+--------------------+
| studentNo | name | sex | hometown | age | class | card |
+-----------+--------------+------+----------+------+-------+--------------------+
| 002 | 诸葛亮 | 男 | 上海 | 18 | 2班 | 340322199002242354 |
| 003 | 张飞 | 男 | 南京 | 24 | 3班 | 340322199003247654 |
| 004 | 白起 | 男 | 安徽 | 22 | 4班 | 340322199005247654 |
| 005 | 大乔 | 女 | 天津 | 19 | 3班 | 340322199004247654 |
| 006 | 孙尚香 | 女 | 河北 | 18 | 1班 | 340322199006247654 |
| 008 | 小乔 | 女 | 河南 | 15 | 3班 | NULL |
| 009 | 百里守约 | 男 | 湖南 | 21 | 1班 | |
| 010 | 妲己 | 女 | 广东 | 26 | 2班 | 340322199607247654 |
| 011 | 李白 | 男 | 北京 | 30 | 4班 | 340322199005267754 |
| 012 | 孙膑 | 男 | 新疆 | 26 | 3班 | 340322199000297655 |
+-----------+--------------+------+----------+------+-------+--------------------+
10 rows in set (0.00 sec)
# 查询所有学生信息,年龄从小到大排序
mysql> select * from students order by age;
+-----------+--------------+------+----------+------+-------+--------------------+
| studentNo | name | sex | hometown | age | class | card |
+-----------+--------------+------+----------+------+-------+--------------------+
| 008 | 小乔 | 女 | 河南 | 15 | 3班 | NULL |
| 002 | 诸葛亮 | 男 | 上海 | 18 | 2班 | 340322199002242354 |
| 006 | 孙尚香 | 女 | 河北 | 18 | 1班 | 340322199006247654 |
| 005 | 大乔 | 女 | 天津 | 19 | 3班 | 340322199004247654 |
| 001 | 王昭君 | 女 | 北京 | 20 | 1班 | 340322199001247654 |
| 007 | 百里玄策 | 男 | 山西 | 20 | 2班 | 340322199007247654 |
| 009 | 百里守约 | 男 | 湖南 | 21 | 1班 | |
| 004 | 白起 | 男 | 安徽 | 22 | 4班 | 340322199005247654 |
| 003 | 张飞 | 男 | 南京 | 24 | 3班 | 340322199003247654 |
| 010 | 妲己 | 女 | 广东 | 26 | 2班 | 340322199607247654 |
| 012 | 孙膑 | 男 | 新疆 | 26 | 3班 | 340322199000297655 |
| 011 | 李白 | 男 | 北京 | 30 | 4班 | 340322199005267754 |
+-----------+--------------+------+----------+------+-------+--------------------+
12 rows in set (0.00 sec)
# 查询所有学生信息,按年龄从大到小排序,年龄相同时,再按学号从小到大排序
mysql> select * from students order by age desc,studentNo;
+-----------+--------------+------+----------+------+-------+--------------------+
| studentNo | name | sex | hometown | age | class | card |
+-----------+--------------+------+----------+------+-------+--------------------+
| 011 | 李白 | 男 | 北京 | 30 | 4班 | 340322199005267754 |
| 010 | 妲己 | 女 | 广东 | 26 | 2班 | 340322199607247654 |
| 012 | 孙膑 | 男 | 新疆 | 26 | 3班 | 340322199000297655 |
| 003 | 张飞 | 男 | 南京 | 24 | 3班 | 340322199003247654 |
| 004 | 白起 | 男 | 安徽 | 22 | 4班 | 340322199005247654 |
| 009 | 百里守约 | 男 | 湖南 | 21 | 1班 | |
| 001 | 王昭君 | 女 | 北京 | 20 | 1班 | 340322199001247654 |
| 007 | 百里玄策 | 男 | 山西 | 20 | 2班 | 340322199007247654 |
| 005 | 大乔 | 女 | 天津 | 19 | 3班 | 340322199004247654 |
| 002 | 诸葛亮 | 男 | 上海 | 18 | 2班 | 340322199002242354 |
| 006 | 孙尚香 | 女 | 河北 | 18 | 1班 | 340322199006247654 |
| 008 | 小乔 | 女 | 河南 | 15 | 3班 | NULL |
+-----------+--------------+------+----------+------+-------+--------------------+
12 rows in set (0.00 s
接下来到你了,给你几个题目,参考本文的内容写出答案哦
SQL语句不难,就是写起来比较麻烦,看到这里了,练习做了吗。在评论区写下你的答案哦。