CREATE TABLE customer(
cusno VARCHAR(20) PRIMARY KEY AUTO_INCRENENT,
cusname VARCHAR(20)NOT NULL,
address VARCHAR(20)NOT NULL,
tel VARCHAR(50)NOT NULL,
sex VARCHAR(2),
mon INT
)
SELECT *FROM customer;
DROP TABLE customer;
DELETE FROM customer;
/插入数据/
INSERT INTO customer VALUES(‘coo1’,’杨婷’,’北京’,’010-5328953’,’女’,180000);
INSERT INTO customer VALUES(‘coo2’,’李和平’,’上海’,’021-6235965’,’男’,230000);
INSERT INTO customer VALUES(‘coo3’,’叶新’,’成都’,’024-3222781’,’男’,550000);
INSERT INTO customer VALUES(‘coo4’,’冯辰诚’,’上海’,’021-8723596’,’男’,700000);
INSERT INTO customer VALUES(‘coo5’,’张展’,’郑州’,’0371-8907654’,’男’,105000);
INSERT INTO customer VALUES(‘coo6’,’王晓丽’,’苏州’,’022-883882’,’女’,89000);
/23)将杨婷拥有资产改为188888。/
UPDATE customer SET mon=188888 WHERE cusname=’杨婷’;
/24)客户编号“C007”的姓名“王晨”，地址‘杭州’，电话‘031-8909932’，性别‘男’，拥有资产280000录入时遗漏，请编写SQL语句插入该记录。/
INSERT INTO customer VALUES(‘coo7’,’王晨’,’杭州’,’031-8909932’,’男’,280000);
/25)查询所有客户的姓名、性别、和地址/
SELECT cusname, sex, address FROM customer;
/26)查询所有客户的姓名、地址和拥有资产，要求安装资产降序排序。/
SELECT cusname ,address,mon FROM customer ORDER BY mon DESC;
/27)查询所有男性客户的客户编号、姓名、地址/
SELECT cusno,cusname,address FROM customer WHERE sex=’男’;
/28)查询来至上海的客户的姓名、性别、拥有资产/
SELECT cusname, sex,mon FROM customer WHERE address=’上海’;
/29)查询资产超过200000的女性的姓名、地址、拥有资产/
SELECT cusname,address,mon FROM customer WHERE sex=’女’ AND mon>20000;
/30)查询姓李的客户的姓名、地址、性别/
SELECT cusname,address,sex FROM customer WHERE cusname LIKE’李%’;
/31)查询客户表中客户拥有资产的平均值/
SELECT*FROM customer WHERE mon>(SELECT AVG(mon) FROM customer);
/32)查询客户表中客户一共拥有多少资产/
SELECT SUM(mon)FROM customer;
/查询客户表中客户资产最少的客户姓名、性别、地址、拥有资产/
SELECT*FROM customer WHERE mon=(SELECT MIN(mon)FROM customer );
/34)查询男性和女性分别拥有资产的总数和性别/
SELECT SUM(mon),t.sexFROM customer t WHERE sex=’男’UNION(SELECT SUM(mon),t.sexFROM customer t WHERE sex=’女’);
/35)查询姓名为2个字的用户的用户编号、姓名、地址/
SELECT cusno,cusname,address FROM customer WHERE cusname LIKE’__’;
/36)查询电话号码最后一位不是3的客户的姓名、地址、电话号码/
SELECT cusname,address,tel FROM customer WHERE tel NOT LIKE’%3’;
/37)查询客户表中男性客户比女性客户多几个/
SELECT(SELECT COUNT(sex)FROM customer WHERE sex=’男’)-(SELECT COUNT(sex)FROM customer WHERE sex=’女’);
/3838)查询资产超过所有客户平均资产的客户的姓名、性别、地址、拥有资产/
SELECT cusname,sex,address,mon FROM customer WHERE mon>(SELECT AVG(mon) FROM customer);
/39)删除上海男性客户的基本信息/
DELETE FROM customer WHERE sex=’男’;
DELETE FROM customer;
