程序员常用SQL语句大全

1、SQL语句大全一、创建和删除数据库1、创建用户/创建用户且置密码，在MySQL中行，但在Oracle中行 -必须在超级管理员身份下操作create user hncu identified by 12342、创建数据库/创建数据库 并手动指定编码格式/错，因为根据手册的查询，数据库名应该在 EXISTS后面CREATE DATABASE hncu IF NOT EXISTS DEFAULT CHARACTER SET utf8;/改正：CREATE DATABASE IF NOT EXISTS hncu DEFAULT CHARACTER SET utf8;3、删除数据库DROP DATABA

2、SE mydb2; /删除数据库/查询(显示数据库)SHOW DATABASES; /*注：用Tab键补全代码，类似MyEclipse中的Alt+/ */二、数据库编码问题1、指定数据库编码方法1：可以在MySQL的配置文件如my.ini中指定：default-character-set = GBK 或default-character-set = utf8方法2：在创建数据库时自己指定，如：CREATE DATABASE IF NOT EXISTS hncu DEFAULT CHARACTER SET utf8;相比较而言，对我们来讲，第2种方法更好。一是配置文件是平台，不能随意改，而且通常

3、是由DBA来做的，我们没权设置，甚至MySQL都不在我们的电脑中。二是只要我们自己在创建数据库时自己指定编码，无论平台默认配置的是什么编码，对我们没有影响，一句话不依赖你的平台-可移置及兼容性好。2、查看数据库编码SHOW VARIABLES; /查询系统中的所有变量SHOW VARIABLES WHERE variable_name LIKE character%; /查询系统中所有的编码方面的参数查询结果：character_set_client utf8 /客户端编码 (不同的客户端显示的可能不一样，如cmd窗口和SQLyong)character_set_connection utf8

4、 /客户端连接数据库时用的编码character_set_database gbk /数据库的默认编码(一般来讲，这是由my.ini配置文件定的。这是没进数据库时的通用编码)character_set_filesystem binary /这是数据库自己存储数据文件时用的编码，跟我们关系不大character_set_results utf8 /查询之后的结果集的编码character_set_server gbk /MySQL数据库服务器自己的编码 iso8859-1在这里称Latin1character_set_system utf8character_set_dir MySQL安装目录

5、sharecharsets对我们来讲，为了不出现乱码，必须保证client和connection的编码一致，否则就会乱码。character_set_database 数据库的编码必须要能够支持中文，否则输入中文有问题的3、设置（修改）数据编码/如何设置指定的编码set character_set_client=gbk;SET character_set_client=gbk;SHOW VARIABLES WHERE variable_name LIKE character%;SELECT * FROM stud;/原来的数据显示正常INSERT INTO stud VALUES(1011,城

6、院,20,88,数计学院);SELECT * FROM stud;/刚刚插入的那条记录，是乱码 /因为我们这里client是gbk，而connection是utf8，不一致了SET character_set_connection=gbk;/已经把client和connection设成gbk，一致了INSERT INTO stud VALUES(1012,城院2,20,88,数计学院);SELECT * FROM stud;/显示刚刚插入的那条记录，还是乱码。因为数据库表stud的编码是utf8，而我们客户端与它的连接都是gbk综上，client、connection、我们所访问的数据库的编码

7、 这三者都要一致且应该是支持中文的编码上面只能保证添加到数据库中的汉字不会出现乱码。如果读取出来，还要看results、所访问的数据库的编码和我们java代码中的解码是否一致。三、创建表格/创建表格CREATE TABLE stud(id INT PRIMARY KEY,sname VARCHAR(30) ,age INT);-varchar(20) -可变的char数组，类似Java当中的Stringchar(20) -固定长度的char数组/SQL语言 全部忽略大小写-大小写不敏感-四、对表格操作USE hncu;SHOW TABLES;/查询(显示)表格DESC stud; /查看数据表

8、student的表结构1、往数据表中插入数据INSERT INTO stud VALUES( 1003,Rose,22 );/插入记录，当数据不全时，要指定列名INSERT INTO stud(id,sname,age,score) VALUES(1010,李小明,25,90);/未指定列名的方式赋值时，必须要给全，默认值不给也不行INSERT INTO a(id,sname) VALUES(1,Tom); /错：UNIQUE限定该列的值必须唯一(可以为，但最多只能有一个)INSERT INTO a(id,sname) VALUES(2,Tom); /对，性别未赋，则用默认/性能优化：指定列名

9、的方式性能更好！2、查询数据SELECT * FROM stud;/查询表中所有的数据SELECT sname,age FROM stud; /只查询(显示)表中的指定列3、删除数据DELETE FROM stud WHERE age=30; /删除年龄为30的表记录4、更新数据UPDATE stud SET sname=杰克 WHERE sname=Jack; /更改数据5、更改表结构ALTER TABLE (添加一列)ALTER TABLE stud ADD COLUMN score NUMERIC(4,2);建立联合主键ALTER TABLE sj ADD CONSTRAINT sj_p

10、k PRIMARY KEY(studId,jectId);/添加外键约束1(为sj表的studId字段添加外键student(id)约束)ALTER TABLE sj ADD CONSTRAINT sj_fk1 FOREIGN KEY(studId) REFERENCES student(id);/添加外键约束2(为sj表的jectId字段添加外键ject(id)约束)ALTER TABLE sj ADD CONSTRAINT sj_fk2 FOREIGN KEY(jectId) REFERENCES ject(id);6、创建视图CREATE VIEW studView AS SELECT

11、* FROM stud WHERE score=60;SELECT * FROM studview;五、对表查询操作中的那些事INSERT INTO stud VALUES(1004,张三,38,60);INSERT INTO stud VALUES(1005,王三,30,60);INSERT INTO stud VALUES(1006,王五,30,60);INSERT INTO stud VALUES(1007,王五六,30,80);1、范围查询/查询年龄在24-26之间的学生信息1）连续区间内的查询SELECT * FROM stud WHERE age=24 AND age30;4、空值

12、查询/查询无名英雄学生的信息(VARCHAR)SELECT * FROM stud WHERE sname IS ;/查询没有年龄信息的学生/错：SELECT * FROM stud WHERE age=;SELECT * FROM stud WHERE age IS ;5、聚合函数1）COUNT 统计表格的行数SELECT COUNT(*) AS TEMPTABLE FROM stud;/as temptable 含义：就是将查询出的结果（表格的行数）另命名为：temptableSELECT COUNT(1) AS TEMPTABLE FROM stud;/统计有年龄值的学生人数SELECT

13、 COUNT(age) AS TEMPTABLE FROM stud;/统计有年龄值且有分数值的学生人数SELECT COUNT(age) AS TEMPTABLE FROM stud WHERE score IS NOT ;2）AVG 统计平均分且取整(注：AVG函数只统计非的数据记录)SELECT ROUND(AVG(score) FROM stud; /这种方式一般不用，因为列名是自动生成的，我们在程序中不好访问SELECT ROUND(AVG(score) AS averageScore FROM stud;3）SUM 分数求和SELECT SUM(score) AS ss FROM

14、stud;4）MAX年龄最大值SELECT MAX(age) AS maxAge FROM stud;6 、WHERE子句+ IN子句/查询年龄最小的那个人的名字SELECT sname FROM stud WHERE age=(SELECT MIN(age) FROM stud);SELECT sname FROM stud WHERE age IN(SELECT MIN(age) FROM stud);7、排序SELECT * FROM stud GROUP BY age ASC; /不重复排序(即年龄相同的，只显示第一个0SELECT * FROM stud GROUP BY age A

15、SC; /显示出所有年龄段SELECT * FROM stud ORDER BY age ASC; /普通排序-升序SELECT * FROM stud ORDER BY age DESC; /普通排序-降序8、distinct（不重复的值）SELECT DISTINCT sname,age FROM stud GROUP BY age DESC;9、EXISTS 判断括号内的内容是否存在-注意，下面的例子，只要存在年龄为26的学生，就会输出所有数据SELECT * FROM stud WHERE EXISTS( SELECT * FROM stud WHERE age=26 );10、演示分

16、组ALTER TABLE stud ADD COLUMN dept VARCHAR(20);UPDATE stud SET dept=信息学院 WHERE score=65;UPDATE stud SET dept=通信学院 WHERE score=60;UPDATE stud SET dept=土木学院 WHERE score=2 ) ORDER BY age DESC;/练练别名SELECT * FROM stud AS xs WHERE age IN ( SELECT age FROM xs GROUP BY age HAVING COUNT(age)=2 ) ORDER BY age

17、DESC;/需求2：不但具有同龄人，而且年龄大于等于30的学生/法1SELECT * FROM stud WHERE age IN ( SELECT age FROM stud GROUP BY age HAVING COUNT(age)=2 AND age=30 ) ORDER BY age DESC;/法2SELECT * FROM stud WHERE age=30 AND age IN ( SELECT age FROM stud GROUP BY age HAVING COUNT(age)=2 ) ORDER BY age DESC;14、固定搭配SELECT * FROM + WH

18、ERE + ORDER BY(要放在最后)GROUP BY + HAVING15、关系查询-NAME VARCHAR(10),sex CHAR(1),wife INT,husband INT);INSERT INTO person VALUES(1,小花,0,0,3);INSERT INTO person VALUES(2,玉芬,0,0,4);INSERT INTO person VALUES(3,张三,1,1,0);INSERT INTO person VALUES(4,李四,1,2,0);INSERT INTO person VALUES(5,王五,1,0,0);1）一对一关系的操作：查出

19、每对夫妻的姓名CREATE VIEW w AS SELECT * FROM person WHERE sex=0;CREATE VIEW m AS SELECT * FROM person WHERE sex=1;/不利用表与表之间的关系SELECT w.NAME AS 妻子, m.NAME AS 丈夫 FROM w,m WHERE w.husband=m.id AND m.wife=w.id;/现在更先进的方式：利用表间的关系SELECT w.NAME AS 妻子, m.NAME AS 丈夫 FROM w INNER JOIN m ON w.husband=m.id AND m.wife=w

20、.id;SELECT * FROM person;2）一对多的关系 代码演示/步骤1：画E-R图/步骤2:分别建实体表，并给多方的表添加外键约束CREATE TABLE person2(id VARCHAR(32) PRIMARY KEY,pname VARCHAR(30),sex CHAR(1);CREATE TABLE car(id VARCHAR(32) PRIMARY KEY,cname VARCHAR(30),price NUMERIC(10,2),pid VARCHAR(32),CONSTRAINT car_fk FOREIGN KEY(pid) REFERENCES person

21、2(id);DROP TABLE car;/步骤3:为两个表添加测试数据/实体表1INSERT INTO person2(id,pname,sex) VALUES(P001,Jack,1);INSERT INTO person2(id,pname,sex) VALUES(P002,Tom,1);INSERT INTO person2(id,pname,sex) VALUES(P003,Rose,0);INSERT INTO person2(id,pname,sex) VALUES(P004,Mary,0);INSERT INTO person2(id,pname,sex) VALUES(P00

22、5,Mike,1);SELECT * FROM person2;/实体表2INSERT INTO car(id,cname,price,pid) VALUES(C001,BMW,123.5,P001);INSERT INTO car(id,cname,price,pid) VALUES(C002,Benz,123.5,P001);INSERT INTO car(id,cname,price,pid) VALUES(C003,BMW,223.5,P001);INSERT INTO car(id,cname,price,pid) VALUES(C011,BMW,83.5,P003);INSERT

23、INTO car(id,cname,price,pid) VALUES(C012,Benz,100,P003);INSERT INTO car(id,cname,price,pid) VALUES(C013,Audi,223.5,P003);INSERT INTO car(id,cname,price,pid) VALUES(C021,BMW,88.5,P004);INSERT INTO car(id,cname,price,pid) VALUES(C022,QQ,10,P004);INSERT INTO car(id,cname,price,pid) VALUES(C023,Audi,73,

24、P005);INSERT INTO car(id,cname,price) VALUES(C033,Audi,1000);/该句代码执行错误，因为编号为P006的人在Person2表中不存在，这就是参照完整性INSERT INTO car(id,cname,price,pid) VALUES(C033,Audi,1000,P006);SELECT * FROM car;/查询：哪些人有什么样的车 (用表名.列名的形式访问列，如果列名不重复，可以省略表名)/利用一方的主键和“多方”的外键进行关联SELECT person2.pname,ame FROM person2,car WHE

25、RE person2.id=car.pid;/查询Jack有什么车SELECT person2.pname,ame FROM person2,car WHERE person2.id=car.pid AND person2.pname=Jack ;/查询哪些人有两辆以上的车SELECT person2.pname,COUNT(pname) AS 车数量 FROM person2,car WHERE person2.id=car.pid GROUP BY pname HAVING COUNT(pname)=2 ORDER BY 车数量;SELECT * FROM person2 WH

26、ERE id IN ( SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)=2 );16、关联查询/查询哪些人没有车SELECT * FROM person2 WHERE id NOT IN( SELECT pid FROM car );/用左关联(LEFT JOIN)来查询：哪些人有什么样的车(没车的也是一种情况，要显示)SELECT person2.pname,ame,car.price FROM person2 LEFT JOIN car ON person2.id=car.pid ORDER BY person2.id;/用

27、内关联(INNER JOIN)来查询：哪些人有什么样的车(没车的不显示)SELECT person2.pname,ame,car.price FROM person2 INNER JOIN car ON person2.id=car.pid ORDER BY person2.id;/查询每辆车的销售情况(如果有主人就显示，没有则显示)SELECT person2.pname,ame,car.price FROM person2 RIGHT JOIN car ON person2.id=car.pid ORDER BY person2.id;（+在左边时 右关联 ，+右边

28、时 左关联）-DELETE FROM person2 WHERE id=P005;CREATE TABLE student(id VARCHAR(32) PRIMARY KEY,NAME VARCHAR(30),CREATE TABLE ject(price NUMERIC(5,2);CREATE TABLE sj(studId VARCHAR(32) NOT ,jectId VARCHAR(32);/建立联合主键/添加测试数据/学生表INSERT INTO student(id,NAME,age) VALUES(S001,Jack,25);INSERT INTO student(id,NAM

29、E,age) VALUES(S002,Tom,24);INSERT INTO student(id,NAME,age) VALUES(S003,张三,23);INSERT INTO student(id,NAME,age) VALUES(S004,李四,24);INSERT INTO student(id,NAME,age) VALUES(S005,Rose,25);SELECT * FROM student;/课程表INSERT INTO ject(id,NAME,price) VALUES(J001,Java,25);INSERT INTO ject(id,NAME,price) VALU

30、ES(J002,MySQL,30);INSERT INTO ject(id,NAME,price) VALUES(J003,Oracle,55.9);INSERT INTO ject(id,NAME,price) VALUES(J004,软件工程,20.25);INSERT INTO ject(id,NAME,price) VALUES(J005,WEB开发,125);SELECT * FROM ject;/选课表INSERT INTO sj(studId,jectId) VALUES(S001,J001);INSERT INTO sj(studId,jectId) VALUES(S001,J

31、002);INSERT INTO sj(studId,jectId) VALUES(S001,J003);INSERT INTO sj(studId,jectId) VALUES(S002,J001);INSERT INTO sj(studId,jectId) VALUES(S002,J003);INSERT INTO sj(studId,jectId) VALUES(S003,J001);INSERT INTO sj(studId,jectId) VALUES(S003,J002);INSERT INTO sj(studId,jectId) VALUES(S004,J003);INSERT

32、INTO sj(studId,jectId) VALUES(S005,J001);SELECT * FROM sj;应用测试/查询哪些人选了哪些课 -要求显示：人名，课程名/采用的是92标准SELECT ,ject.NAME FROM student,ject,sj WHERE student.id=sj.studId AND sj.jectId=ject.id;/采用96标准SELECT ,ject.NAME FROM studentINNER JOIN sj ON student.id=sj.studIdINNER JOIN ject ON sj.jectId=ject.id;/查询哪些人没有选课(左关联)SELECT ,ject.NAME FROM studentLEFT JOIN sj ON student.id=sj.studIdLEFT JOIN ject ON sj.jectId=ject.id WHERE ject.NAME IS ;/查询哪些课没人选(右关联)SELECT ,ject.NAME FROM studentRIGHT JOIN sj ON student.id=sj.studIdRIGHT JOIN ject ON sj.jectId=je