--創(chuàng)建一個(gè)班級(jí)表classes
USE 1909e
CREATE TABLE classes (id int , name VARCHAR(20))
--創(chuàng)建一個(gè)學(xué)生表students
USE 1909e
CREATE TABLE students (id INT,name VARCHAR(20),birthdays date,
gender CHAR(2),score INT,class_id INT)
--在班級(jí)表中添加三條記錄
INSERT INTO classes (id,name) VALUES (1,"一年級(jí)"),(2,"二年級(jí)"),(3,"三年級(jí)")
--在學(xué)生表里添加六條記錄
INSERT INTO students (id,name,birthdays,gender,score,class_id)VALUES(1,"張三","2010-11-10","男",100,1),
(2,"李四","2012-11-10","女",75,1),(3,"王五","2014-11-10","男",80,2),
(4,"小明","2013-11-10","女",60,2),(5,"小李","2015-11-10","女",30,2),
(6,"小劉","2008-11-10","男",90,3)
--修改張三的名字為張五
UPDATE students set name="張五" where id=1
--刪除班級(jí)名字為三年級(jí)的所有學(xué)生
DELETE FROM students WHERE class_id=3
--查詢一年級(jí)分?jǐn)?shù)最高的人
SELECT MAX(score) FROM students
--查詢以小字開(kāi)頭的名字的學(xué)生
SELECT * from students WHERE name LIKE '小%'
--查詢出生日期在2008-11-10到2012-11-10的學(xué)生
SELECT * from students where birthdays BETWEEN '2008-11-10' AND'2012-11-10'
--創(chuàng)建一個(gè)分類(lèi)表cate，包含字段id亮曹，name
CREATE TABLE cate (id INT,name VARCHAR(20))
--創(chuàng)建一個(gè)商品表goods权埠，包含字段id耻讽，name，number懦胞，create_time,price,cate_id
CREATE TABLE goods (id INT,name VARCHAR(20),number INT,create_time date,price DOUBLE,cate_id INT)
--在分類(lèi)表中添加三條記錄
INSERT into cate (id,name)VALUES (1,"服裝"),(2,"玩具"),(3,"家電")
--在商品表里添加六條記錄
INSERT into goods (id,name,number,create_time,price,cate_id) VALUES (1,"羽絨服",10,"2010-11-10",1.00,1),
(2,"輕便服",30,"2012-11-10",2.00,1),(3,"汽車(chē)",40,"2014-11-10",2.00,2),
(4,"公主",50,"2013-11-10",6.00,2),(5,"橡皮泥",23,"2015-11-10",3.50,2),
(6,"電飯煲",3,"2008-11-10",3.00,3)
--把名字為羽絨服修改成棉衣
UPDATE goods set name="棉衣" WHERE name="羽絨服"
--刪除名字為電飯煲的記錄
DELETE FROM goods WHERE name="電飯煲"
--查詢以服結(jié)束的商品信息
SELECT * from goods WHERE name LIKE '%服'
--查詢創(chuàng)建日期在2008-11-10到2012-11-10的之間的商品
SELECT * from goods where create_time BETWEEN '2008-11-10' AND'2012-11-10'
--創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)cart
CREATE DATABASE cart
--使用新創(chuàng)建的數(shù)據(jù)庫(kù)建立兩張表，用戶表users：id 姓名 身高，購(gòu)物車(chē)表user_cart
USE cart
CREATE TABLE users (id int,name VARCHAR(20),gao DOUBLE)
CREATE TABLE user_cart (id INT,name VARCHAR(20),shu int,price int,shan int,user_id INT)
--向用戶表添加記錄
INSERT into users (id,name,gao) VALUES (1,"張三",1.80),(2,"李四",1.75),(3,"小明",1.34)
--在購(gòu)物車(chē)表中添加記錄
INSERT INTO user_cart (id,name,shu,price,shan,user_id) VALUES (1,"商品一",10,100,1,1),
(2,"商品二",3,89,0,1),(3,"商品三",2,30,1,1),(4,"商品四",10,50,1,2),(5,"商品五",3,10,1,2),
(6,"商品六",1,76,1,2)
--更新李四購(gòu)物車(chē)中商品五的名字為商品5
UPDATE user_cart set name="商品5" where name="商品五"
--查詢購(gòu)物車(chē)中刪除和沒(méi)刪除的總價(jià)和為多少
SELECT SUM(price) from user_cart
--刪除張三購(gòu)物車(chē)中已刪除的購(gòu)物車(chē)信息
DELETE FROM user_cart WHERE shan="0"
