用什么软件编写Mysql语句 |
您所在的位置:网站首页 › 写sql用什么编辑器 › 用什么软件编写Mysql语句 |
挺全的sql语句,分享给大家~ 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限定该列的值必须唯一(可以为NULL,但最多只能有一个) 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 age30; ※4、空值查询 //查询无名英雄学生的信息(VARCHAR) SELECT FROM stud WHERE sname IS NULL; //查询没有年龄信息的学生 //错:SELECT FROM stud WHERE age==NULL; SELECT * FROM stud WHERE age IS NULL; ※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 NULL; 2)AVG 统计平均分且取整(注:AVG函数只统计非NULL的数据记录) 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=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、关系查询 DROP TABLE person; CREATE TABLE person( id INT, 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:分别建实体表,并给多方的表添加外键约束 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、关联查询 //查询哪些人没有车 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; //查询每辆车的销售情况(如果有主人就显示,没有则显示NULL) 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), age INT ); CREATE TABLE ject( id VARCHAR(32) PRIMARY KEY, NAME VARCHAR(30), price NUMERIC(5,2) ); CREATE TABLE sj( studId VARCHAR(32) NOT NULL, jectId VARCHAR(32) ); //建立联合主键 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); //添加测试数据 //学生表 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 NULL; //查询哪些课没人选(右关联) 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 NULL; //左右关联可以相互转换,如把上面的代码用左关联 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 NULL 六、存储过程 //定义存储过程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、带参数的存储过程 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、有返回值的存储过程----参数与变量问题( @变量名 ,一个@为用户变量,两个@即 @@为全局的系统变量 ) DELIMITER$$ CREATE PROCEDURE p3(IN id INT, IN nm VARCHAR(30), OUT num INT ) BEGIN INSERT INTO stud(id,sname) VALUES(id,nm); SELECT COUNT(*) INTO num FROM stud; END$$ DELIMITER ; CALL p3(1016,'无名',@aa); SELECT @aa; //输出变量aa的值 七、事务处理 START TRANSACTION DELETE FROM stud WHERE id=1015; DELETE FROM stud WHERE id=1014; SELECT * FROM stud; ROLLBACK / COMMIT; ◎采用事务的java编程 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(); } |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |