sql-白红宇

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;