程序员必备sql语句大全

一、创建和删除数据库

1、创建用户
//创建用户且置密码,在MySQL中行,但在Oracle中行 —-必须在超级管理员身份下操作
create user hncu identified by ‘1234’
2、创建数据库
//创建数据库 并手动指定编码格式
//错,因为根据手册的查询,数据库名应该在 EXISTS后面
CREATE DATABASE hncu IF NOT EXISTS DEFAULT CHARACTER SET ‘utf8’;
//改正:
CREATE DATABASE IF NOT EXISTS hncu DEFAULT CHARACTER SET ‘utf8’;
3、删除数据库
DROP DATABASE 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种方法更好。一是配置文件是平台,不能随意改,而且通常是由DBA来做的,我们没权设置,甚至MySQL都不在我们的电脑中。二是只
要我们自己在创建数据库时自己指定编码,无论平台默认配置的是什么编码,对我们没有影响,一句话不依赖你的平台—-可移置及兼容性好。
2、查看数据库编码
SHOW VARIABLES; //查询系统中的所有变量
SHOW VARIABLES WHERE variable_name LIKE ‘character%’; //查询系统中所有的编码方面的参数
查询结果:
character_set_client utf8 //客户端编码 (不同的客户端显示的可能不一样,如cmd窗口和SQLyong)
character_set_connection utf8 //客户端连接数据库时用的编码
character_set_database gbk //数据库的默认编码(一般来讲,这是由my.ini配置文件定的。这是没进数据库时的通用编码)
character_set_filesystem binary //这是数据库自己存储数据文件时用的编码,跟我们关系不大
character_set_results utf8 //查询之后的结果集的编码
character_set_server gbk //MySQL数据库服务器自己的编码 iso8859-1在这里称Latin1
character_set_system utf8
character_set_dir MySQL安装目录 \share\charsets
对我们来讲,为了不出现乱码,必须保证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,’城院’,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、我们所访问的数据库的编码 这三者都要一致且应该是支持中文的编码
上面只能保证添加到数据库中的汉字不会出现乱码。
如果读取出来,还要看results、所访问的数据库的编码和我们java代码中的解码是否一致。
三、创建表格

//创建表格
CREATE TABLE stud(
id INT PRIMARY KEY,
sname VARCHAR(30) ,
age INT);
———————————————————————–varchar(20) —-可变的char数组,类似Java当中的String
char(20) —-固定长度的char数组
//SQL语言 全部忽略大小写—-大小写不敏感
———————————————————————–
四、对表格操作

USE hncu;
SHOW TABLES;//查询(显示)表格
DESC stud; //查看数据表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’); //对,性别未赋,则用默认
//性能优化:指定列名的方式性能更好!
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_pk 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 * 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 age<27; 2)用BETWEEN是左右都包含,如下功能是[24,26] SELECT * FROM stud WHERE age BETWEEN 24 AND 26; 3)离散的多个 SELECT * FROM stud WHERE age=24 OR age=38; SELECT * FROM stud WHERE age IN(24,26,30); ※2、模糊查询LIKE 参数:%(任意匹配), _(匹配一个) //查询姓“王”的学生信息 SELECT * FROM stud WHERE sname LIKE ‘王%’ //查询姓“王”且名为单字的学生信息 SELECT * FROM stud WHERE sname LIKE ‘王_’; //查询姓“王”且名为双字的学生信息 SELECT * FROM stud WHERE sname LIKE ‘王__’; //查询姓名中包含“五”字的学生信息 SELECT * FROM stud WHERE sname LIKE ‘%五%’; ※3、范围查询和模糊查询联合 //查询姓名中包含“五”字 且 年龄大于30 的学生信息 SELECT * FROM stud WHERE sname LIKE ‘%五%’ AND age>30;
※4、空值查询
//查询无名英雄学生的信息(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 含义:就是将查询出的结果(表格的行数)另命名为:temptable
SELECT COUNT(1) AS TEMPTABLE FROM stud;
//统计有年龄值的学生人数
SELECT 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 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; //不重复排序(即年龄相同的,只显示第一个0
SELECT * FROM stud GROUP BY age ASC; //显示出所有年龄段
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、演示分组
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<60; SELECT * FROM stud; //分组计算 //按学院计算平均分(每个学员的平均分) SELECT dept,AVG(score) AS ‘学院平均分’ FROM stud GROUP BY dept; ※11、字符串处理函数 SELECT * FROM stud WHERE sname=’AAA’; SELECT * FROM stud WHERE TRIM(sname)=’AAA’;//去掉左右的空格 SELECT * FROM stud WHERE LTRIM(RTRIM(sname))=’AAA’;//和上面等价 UPDATE stud SET dept=’数计学院’ WHERE id=1011; SELECT LEFT(TRIM(sname),2) FROM stud; //取去掉空格后的左起2个字符 SELECT REVERSE(TRIM(sname)) FROM stud; CREATE TABLE person( id INT, sname VARCHAR(30) , age INT ); ALTER TABLE person ADD CONSTRAINT person_pk PRIMARY KEY(id);//更灵活 DROP TABLE person; sname VARCHAR(30) NOT , age INT ); //用NOT 限制非空输入 ※12、性别字段(例如:数据库存0、1 而显示出来为男、女) 一般不定义成BOOLEAN型,因为有的数据库不支持,为考虑兼容,通常用CHAR(1) CREATE TABLE a( id INT UNIQUE, sname VARCHAR(10), sex CHAR(1) DEFAULT ‘0’ ); //显示性别 (真实值与显示值之间的转换) SELECT * FROM a; SELECT id,sname,(CASE sex WHEN ‘0’ THEN ‘女’ WHEN ‘1’ THEN ‘男’ ELSE ” END) xb FROM a; SELECT id,sname,(CASE sex WHEN ‘0’ THEN ‘女’ WHEN ‘1’ THEN ‘男’ ELSE ” END) AS xb FROM a; SELECT id,sname,(CASE WHEN sex=’0′ THEN ‘女’ WHEN sex=’1′ THEN ‘男’ ELSE ” END) AS xb FROM a; ※13、无关子查询 //需求:具有同龄人的学生 SELECT * FROM stud; SELECT * FROM stud WHERE age IN ( SELECT age FROM stud GROUP BY age HAVING COUNT(age)>=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 DESC;
//需求2:不但具有同龄人,而且年龄大于等于30的学生
//法1
SELECT * FROM stud WHERE age IN ( SELECT age FROM stud GROUP BY age HAVING COUNT(age)>=2 AND age>=30 ) ORDER BY age DESC;
//法2
SELECT * 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 + WHERE + ORDER BY(要放在最后)
GROUP BY + HAVING
※15、关系查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
—————————————–
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)一对一关系的操作:查出每对夫妻的姓名
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.id;
SELECT * FROM person;

2)一对多的关系 代码演示
//步骤1:画E-R图
//步骤2:分别建实体表,并给多方的表添加外键约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
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 person2(id)
);
DROP TABLE car;
//步骤3:为两个表添加测试数据
//实体表1
INSERT 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(‘P005′,’Mike’,’1′);
SELECT * FROM person2;
////实体表2
INSERT 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 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,’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,car.cname FROM person2,car WHERE person2.id=car.pid;
//查询Jack有什么车
SELECT person2.pname,car.cname 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 WHERE id IN ( SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)>=2 );

※16、关联查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
//查询哪些人没有车
SELECT * FROM person2 WHERE id NOT IN( SELECT pid FROM car );
//用左关联(LEFT JOIN)来查询:哪些人有什么样的车(没车的也是一种情况,要显示)
SELECT person2.pname,car.cname,car.price FROM person2 LEFT JOIN car ON person2.id=car.pid ORDER BY person2.id;
//用内关联(INNER JOIN)来查询:哪些人有什么样的车(没车的不显示)
SELECT person2.pname,car.cname,car.price FROM person2 INNER JOIN car ON person2.id=car.pid ORDER BY person2.id;
//查询每辆车的销售情况(如果有主人就显示,没有则显示)
SELECT person2.pname,car.cname,car.price FROM person2 RIGHT JOIN car ON person2.id=car.pid ORDER BY person2.id;
(+在左边时 右关联 ,+右边时 左关联)
————————————————–
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,NAME,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) VALUES(‘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′,’J002’);
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 INTO sj(studId,jectId) VALUES(‘S005′,’J001’);
SELECT * FROM sj;

应用测试
//查询哪些人选了哪些课 —-要求显示:人名,课程名
//采用的是92标准
SELECT student.name,ject.NAME FROM student,ject,sj WHERE student.id=sj.studId AND sj.jectId=ject.id;
//采用96标准
SELECT student.name,ject.NAME FROM student
INNER JOIN sj ON student.id=sj.studId
INNER JOIN ject ON sj.jectId=ject.id;
//查询哪些人没有选课(左关联)
SELECT student.name,ject.NAME FROM student
LEFT JOIN sj ON student.id=sj.studId
LEFT JOIN ject ON sj.jectId=ject.id WHERE ject.NAME IS ;
//查询哪些课没人选(右关联)
SELECT student.name,ject.NAME FROM student
RIGHT JOIN sj ON student.id=sj.studId
RIGHT JOIN ject ON sj.jectId=ject.id WHERE student.NAME IS ;
//左右关联可以相互转换,如把上面的代码用左关联
SELECT student.name,ject.NAME FROM jec
LEFT JOIN sj ON ject.id=sj.jectId
LEFT JOIN student ON sj.studId=student.id WHERE student.NAME IS
六、存储过程

//定义存储过程p1 // “DELIMITER
”当作结束标志(否则默认是把“;“号当作结束标记),这样存储过程中的语句结束符”;”就不会当作过程的结束
记。
DELIMITER$$
CREATE PROCEDURE p1
BEGIN
SELECT * FROM stud;
INSERT INTO stud(id,sname,age,score,dept) VALUES(1014,’刘三丰’,33,55,’通信学院’);
END$$
DELIMITER; //把结束标记还原回来
CALL p1; //调用存储过程p1
1、带参数的存储过程

1
2
3
4
5
6
7
8
DELIMITER$$
CREATE PROCEDURE p2(IN id INT, IN nm VARCHAR(30) )
BEGIN
INSERT INTO stud(id,sname) VALUES(id,nm);
END$$
DELIMITER ;
DROP PROCEDURE p2;
CALL p2(1015,’吊丝’);

2、有返回值的存储过程—-参数与变量问题( @变量名 ,一个@为用户变量,两个@即 @@为全局的系统变量 )

1
2
3
4
5
DELIMITER$$
CREATE PROCEDURE p3(IN id INT, IN nm VARCHAR(30), OUT num INT )
SELECT COUNT(*) INTO num FROM stud;
CALL p3(1016,’无名’,@aa);
SELECT @aa; //输出变量aa的值

七、事务处理

1
2
3
4
5
START TRANSACTION
DELETE FROM stud WHERE id=1015;
DELETE FROM stud WHERE id=1014;
SELECT * FROM stud;
ROLLBACK / COMMIT;

◎采用事务的java编程

1
2
3
4
5
6
7
8
try{
st.execute(“START TRANSACTION;”);
st.execute(“DELETE FROM stud WHERE id=1015;”);
st.execute(“DELETE FROM stud WHERE id=1014;”);
….
st.execute(” commit; “);
}catch(Exception e){
rollback;}
严正声明:本站只出售html页面效果模板,不提供任何类型的网站内容数据,模板仅供学习交流使用,不得用于任何商业以及触犯国家法律法规的用途,违者需自行承担全部责任,与本站无以及模板设计作者无关,本站以及模板作者不承担任何连带责任!!!!
关爱邦 | 网站源码程序下载_免费商业源码分享! » 程序员必备sql语句大全

发表评论

提供最优质的资源集合

立即查看 了解详情