單表數(shù)據(jù)一

create database test9;

1.在test9中創(chuàng)建一張表 user （整形id、字符串（50）username、字符串（32）password）

use test9;
create table `user`(
  id int primary key auto_increment ,       #主鍵 + 自動(dòng)增長(zhǎng)
  username varchar(50),
  `password` varchar(32)
);

2.向user表中插入3條數(shù)據(jù)

insert into `user`(username,`password`) values('jack','111');
insert into `user`(username,`password`) values('rose','222');
insert into `user`(username,`password`) values('tom','333');

3.查詢user表中的所有數(shù)據(jù)

select * from `user`;

4.更新user表的第二條數(shù)據(jù)的密碼為666666

update `user` set `password` = '666666' where id = 2;

5.刪除user表的第一條數(shù)據(jù)

delete from `user` where id = 1;

6.計(jì)算user表中的總條數(shù)

select count(*) from `user`;

單表數(shù)據(jù)二

create table product(
    pid int primary key,
    pname varchar(20),
    price double,
    category_id varchar(32)
);

INSERT INTO product(pid,pname,price,category_id) VALUES(1,'聯(lián)想',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(2,'海爾',3000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');

INSERT INTO product(pid,pname,price,category_id) VALUES(4,'JACK JONES',800,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(5,'真維斯',200,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(7,'勁霸',2000,'c002');

INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈兒',800,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(10,'面霸',5,'c003');

INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你棗',56,'c004');
INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香飄飄奶茶',1,'c005');

INSERT INTO product(pid,pname,price,category_id) VALUES(13,'果9',1,NULL);

查詢所有商品

SELECT * FROM product ;

查詢商品名和商品價(jià)格

SELECT pname,price FROM product;

別名查詢,使用as 關(guān)鍵字

SELECT pname pn ,price pr FROM product;

去掉重復(fù)值

SELECT DISTINCT price FROM product 

查詢結(jié)果是表達(dá)式(運(yùn)算查詢):將所有商品價(jià)格+10進(jìn)行顯示

SELECT pid,pname,price+10,category_id FROM product ;

查詢商品名稱為“花花公子”的商品所有信息：

SELECT * FROM product WHERE pname='花花公子'

查詢價(jià)格為800商品

SELECT * FROM product WHERE price='800'

查詢價(jià)格不是800的所有商品

SELECT * FROM product WHERE price !='800'

查詢商品價(jià)格大于60元的所有商品信息

SELECT * FROM product WHERE price>60

查詢商品價(jià)格在200到1000之間所有商品

SELECT * FROM product WHERE price>='200' AND price<='1000'

查詢商品價(jià)格是200或800的所有商品

SELECT * FROM product WHERE price='200' OR price='800'

查詢含有'霸'字的所有商品

SELECT * FROM product WHERE pname LIKE '%霸%'

查詢以'香'開頭的所有商品

SELECT * FROM product WHERE pname LIKE '%香%%'

查詢第二個(gè)字為'想'的所有商品

SELECT * FROM product WHERE pname LIKE '%%想%'

商品沒有分類的商品

SELECT * FROM product WHERE category_id IS NULL

查詢有分類的商品

SELECT * FROM product WHERE category_id IS NOT NULL

1.鏈接查詢.png