sql学习

(1)一道凊华同方的SQL面试题
已知一个表的结构为：
姓名 科目 成绩
张三 语文 20
张三 数学 30
张三 英语 50
李四 语文 70
李四 数学 60
李四 英语 90
怎样通过select语句把他变成以下结构：
姓名 语文 数学 英语
张三 20 30 50
李四 70 60 90

 

---

在Mysql上测试通过：
use test;
drop table if exists studentscore;
create table studentscore (id int auto_increment primary key,姓名 varchar (20),科目 varchar (20),成绩 varchar (20) ) default charset=utf8;

 

INSERT INTO studentscore
  (id, 姓名, 科目, 成绩)
VALUES
  (1, "张三", "语文", " 20");

INSERT INTO studentscore
  (id, 姓名, 科目, 成绩)
VALUES
  (2, "张三", "数学", " 30");

INSERT INTO studentscore
  (id, 姓名, 科目, 成绩)
VALUES
  (3, "张三", "英语", " 50");

INSERT INTO studentscore
  (id, 姓名, 科目, 成绩)
VALUES
  (4, "李四", "语文", " 70");

INSERT INTO studentscore
  (id, 姓名, 科目, 成绩)
VALUES
  (5, "李四", "数学", " 60");

INSERT INTO studentscore
  (id, 姓名, 科目, 成绩)
VALUES
  (6, "李四", "英语", " 90");

create view teststudent as
select A.姓名,A.成绩 as 语文,B.成绩 as 数学,C.成绩 as 英语
from studentscore A,studentscore B,studentscore C
where A.姓名=B.姓名 and B.姓名=C.姓名
and A.科目='语文' and B.科目='数学'
and C.科目='英语'; 

 

---

 

（2）

金蝶的一道SQL笔试题，上个周六的时候去金蝶面试，出了这样一道SQL题，共享之
给下面这样的一个表记录：
------------------------------------
购物人      商品名称     数量
A            甲          2
B            乙          4
C            丙          1
A            丁          2
B            丙          5
给出所有购入商品为两种或两种以上的购物人记录

use test;
drop table if exists product;
create table product ( id int auto_increment primary key, 购物人 varchar (20), 商品名称 varchar (20), 数量 varchar (20) );
insert into product (id, 购物人, 商品名称, 数量) values  (1,' A','甲','2');
insert into product (id, 购物人, 商品名称, 数量) values  (2,' B','乙','4');
insert into product (id, 购物人, 商品名称, 数量) values  (3,' C','丙','1');
insert into product (id, 购物人, 商品名称, 数量) values  (4,' A','丁','2');
insert into product (id, 购物人, 商品名称, 数量) values  (5,' B','丙','5');

select * from product group by 购物人 having count(商品名称)>=2;错误，因为结果是这样的：

A            甲          2
B            乙          4

select * from product where 购物人 in (select 购物人 from product group by 购物人 having count(商品名称)>=2) 正确

A            甲          2
B            乙          4
A            丁          2
B            丙          5

---

（3）mysql基本sql学习
(1)show databases like ‘my%’;
(2)CREATE DATABASE db_name;
(3)DROP DATABASE [IF EXISTS] db_name
(4)SHOW TABLES [FROM db_name] [LIKE wild]
 or SHOW COLUMNS FROM tbl_name [FROM db_name] [LIKE wild]
 or SHOW INDEX FROM tbl_name [FROM db_name]
 or SHOW TABLE STATUS [FROM db_name] [LIKE wild]
(5)CREATE TABLE tbl_name(create_definition,...) [TYPE =table_type]
(6)Alter Table语句修改表的结构
有时你可能需要改变一下现有表的结构，那么Alter Table语句将是你的合适选择。
 增加列
alter table tbl_name add col_name type
例如,给表增加一列weight
mysql>alter table pet add weight int;
 删除列
alter table tbl_name drop col_name
例如，删除列weight：
mysql>alter table pet drop weight;
 改变列
alter table tbl_name modify col_name type
例如，改变weight的类型：
mysql> alter table pet modify weight samllint;
另一种方法是：
alter table tbl_name change old_col_name col_name type
例如：
mysql> alter table pet change weight weight samllint;
 给列更名
mysql>alter table pet change weight wei;
 给表更名
alter table tbl_name rename new_tbl
例如，把pet表更名为animal
mysql>alter table pet rename animal;
(7)DROP TABLE [IF EXISTS] tbl_name [, tbl_name,...]
(8)使用insert语句插入新数据
语法：INSERT [INTO] tbl_name [(col_name,...)] VALUES (pression,...),…
      INSERT [INTO] tbl_name SET col_name=expression, .
(9) 使用Insert…Select语句插入从其他表选择的行
从其它表中录入数据，例如：
mysql>insert into tbl_name1(col1,col2) select col3,col4 from tbl_name2;
你也可以略去目的表的列列表，如果你每一列都有数据录入。
mysql>insert into tbl_name1 select col3,col4 from tbl_name2;
(10)LOAD语句批量录入数据
1、基本语法
语法：LOAD DATA [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name
(11)
SELECT 语句的语法如下：
SELECT selection_list 选择哪些列
FROM table_list 从何处选择行
WHERE primary_constraint 行必须满足什么条件
GROUP BY grouping_columns 怎样对结果分组
HAVING secondary_constraint 行必须满足的第二条件
ORDER BY sorting_columns 怎样对结果排序
LIMIT count 结果限定
(12)用update修改记录
update  table set colum='' where condition;
(13)用delete删除记录
DELETE 语句有如下格式：
DELETE FROM tbl_name WHERE 要删除的记录
(14)权限
a、使用SHOW GRANTS语句显示用户的授权
SHOW GRANTS FOR user_name
SHOW GRANTS FOR admin@localhost;
b、 GRANT priv_type (columns)
ON what
TO user IDENTIFIED BY “password”
WITH GRANT OPTION
对于表，你能指定的唯一priv_type值是SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、GRANT、INDEX和ALTER。
对于列，你能指定的唯一priv_type值是(即，当你使用一个column_list子句时)是SELECT、INSERT和UPDATE。
l columns 权限适用的列。
这是可选的，只来设置列专有的权限。如果命名多于一个列，则用逗号分开。
l what 权限应用的级别
GRANT允许系统主管在4个权限级别上授权MySQL用户的权利：
全局级别
使用ON *.*语法设置全局权限
数据库级别
ON db_name.*语法
表级别
ON tbl_name
列级别
指定一个columns子句
同时要在ON子句中指定具体
表
c、使用revoke语句撤销授权
语法：REVOKE privileges (columns) ON what FROM user; 

 

---

 

(4)sql中exists 与 in的区别

main query where exists subquery 或者 字段名 in (subquery);

exist subquery 返回的是 true or false,个人觉得与if 条件判断的作用是一样的，加入返回true，则执行先前的主查询。而in返回的是一个值的集合。所以可以用字段