把一张表求和的结果放在另一张表里

frogandyyy 2015-03-09 03:42:29

表1： bz a01 a02 a03 a04 a05 a........ （有好多字段）
表2： a01 a01 a02 a03 a04 a05 a........ （有好多字段）

我想把表1中， sum(a01*bz)的值放在表2的a01下
sum(a02*bz)的值放在表2的a02下
。。。。。。。。。。

下面是我自己写的一段，执行的过程能看到计算出来的结果，但存放表2的时候出错
sele1
use 表1

sele2
use 表2
sum(a01*bz) to n_a01
sum(a02*bz) to n_a02
......
sele 1
repl a01 with n_a01,a02 with n_a02。。。。。。。

...全文

605 4 打赏收藏转发到动态举报

写回复

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

oldbbb 2015-03-09

打赏
举报

回复

表1与表2用一个关联字段，如ff 示例：

CREATE CURSOR 表1 (ff I, bz I, a01 I, a02 I, a03 I)
INSERT INTO 表1 VALUES (1, 2, 1, 2, 3)
INSERT INTO 表1 VALUES (1, 3, 2, 3, 4)
INSERT INTO 表1 VALUES (1, 4, 3, 4, 5)

CREATE CURSOR 表2 (ff I, a01 I, a02 I, a03 I)
INSERT INTO 表2 VALUES (1, 0, 0, 0)

SELECT 表1
SET RELATION TO ff INTO "表2"
REPLACE 表2.a01 WITH 表2.a01 + 表1.a01*表1.bz,;
                  表2.a02 WITH 表2.a02 + 表1.a02*表1.bz,;
                  表2.a03 WITH 表2.a03 + 表1.a03*表1.bz ALL 

SELECT 表2
BROWSE

lygcw9602 2015-03-09

打赏
举报

回复

数据量大INSERT INTO .......SELECT .........FROM ......速度不理想吧

都市夜猫 2015-03-09

打赏
举报

回复

直接把查询结果插到表2中不行吗？用那么多临时变量有必要吗例如： insert into 表2 (a01, a02, a03, ....., a0x) ; select sum(a01*bz), sum(a02*bz), sum(a03*bz), ... , sum(a0x*bz)

lygcw9602 2015-03-09

打赏
举报

回复

上传表：http://www.access911.net/CSDN/

MYSQL常用命令 1.导出整个数据库 mysqldump -u 用名 -p –default-character-set=latin1 数据库名 > 导出的文件名(数据库默认编码是latin1) mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql 2.导出一个表 mysqldump -u 用户名 -p 数据库名表名> 导出的文件名 mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql 3.导出一个数据库结构 mysqldump -u wcnc -p -d –add-drop-table smgp_apps_wcnc >d:wcnc_db.sql -d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table 4.导入数据库 A:常用source 命令进入mysql数据库控制台，如mysql -u root -p mysql>use 数据库然后使用source命令，后面参数为脚本文件(如这里用到的.sql) mysql>source wcnc_db.sql B:使用mysqldump命令 mysqldump -u username -p dbname < filename.sql C:使用mysql命令 mysql -u username -p -D dbname 2、退出MySQL：quit或exit 二、库操作 1、创建数据库命令：create database 例如：建立一个名为xhkdb的数据库 mysql> create database xhkdb; 2、显示所有的数据库命令：show databases （注意：最后有个s） mysql> show databases; 3、删除数据库命令：drop database 例如：删除名为 xhkdb的数据库 mysql> drop database xhkdb; 4、连接数据库命令：use 例如：如果xhkdb数据库存在，尝试存取它： mysql> use xhkdb; 屏幕提示：Database changed 5、查看当前使用的数据库 mysql> select database(); 6、当前数据库包含的表信息： mysql> show tables; （注意：最后有个s）三、表操作，操作之前应连接某个数据库 1、建表命令：create table ( [,.. ]); mysql> create table MyClass( > id int(4) not null primary key auto_increment, > name char(20) not null, > sex int(4) not null default ’′, > degree double(16,2)); 2、获取表结构命令：desc 表名，或者show columns from 表名 mysql>DESCRIBE MyClass mysql> desc MyClass; mysql> show columns from MyClass; 3、删除表命令：drop table 例如：删除表名为 MyClass 的表 mysql> drop table MyClass; 4、插入数据命令：insert into [( [,.. ])] values ( 值 )[, ( 值n )] 例如，往表 MyClass中插入二条记录, 这二条记录表示：编号为的名为Tom的成绩为.45, 编号为的名为Joan 的成绩为.99，编号为的名为Wang 的成绩为.5. mysql>insert into MyClass values(1,’Tom’,96.45),(2,’Joan’,82.99), (2,’Wang’, 96.59); 5、查询表中的数据 1)、查询所有行命令：select from where 例如：查看表 MyClass 中所有数据 mysql> select * from MyClass; 2）、查询前几行数据例如：查看表 MyClass 中前行数据 mysql> select * from MyClass order by id limit 0,2; 或者： mysql> select * from MyClass limit 0,2; 6、删除表中数据命令：delete from 表名 where 表达式例如：删除表 MyClass中编号为的记录 mysql> delete from MyClass where id=1; 7、修改表中数据：update 表名 set 字段=新值,…where 条件 mysql> update MyClass set name=’Mary’where id=1; 7、在表中增加字段：命令：alter table 表名 add字段类型其他; 例如：在表MyClass中添加了一个字段passtest，类型为int(4)，默认值为 mysql> alter table MyClass add passtest int(4) default ’′ 8、更改表名：命令：rename table 原表名 to 新表名; 例如：在表MyClass名字更改为YouClass mysql> rename table MyClass to YouClass; 更新字段内容 update 表名 set 字段名 = 新内容 update 表名 set 字段名 = replace(字段名,’旧内容’,'新内容’) 文章前面加入个空格 update article set content=concat(‘　　’,content); 字段类型 1．INT[(M)] 型：正常大小整数类型 2．DOUBLE[(M,D)] [ZEROFILL] 型：正常大小(双精密)浮点数字类型 3．DATE 日期类型：支持的范围是-01-01到-12-31。MySQL以YYYY-MM-DD格式来显示DATE值，但是允许你使用字符串或数字把值赋给DATE列 4．CHAR(M) 型：定长字符串类型，当存储时，总是是用空格填满右边到指定的长度 5．BLOB TEXT类型，最大长度为(2^16-1)个字符。 6．VARCHAR型：变长字符串类型 5.导入数据库表　　（）创建.sql文件　　（）先产生一个库如auction.c:mysqlbin>mysqladmin -u root -p creat auction，会提示输入密码，然后成功创建。　　（）导入auction.sql文件　　c:mysqlbin>mysql -u root -p auction grant select,insert,delete,create,drop 　　on *.* (或test.*/user.*/..) 　　to 用户名@localhost 　　identified by ‘密码’；　　如：新建一个用户帐号以便可以访问数据库，需要进行如下操作：　　mysql> grant usage 　　-> ON test.* 　　-> TO testuser@localhost; 　　Query OK, 0 rows affected (0.15 sec) 　　此后就创建了一个新用户叫：testuser，这个用户只能从localhost连接到数据库并可以连接到test 数据库。下一步，我们必须指定testuser这个用户可以执行哪些操作：　　mysql> GRANT select, insert, delete,update 　　-> ON test.* 　　-> TO testuser@localhost; 　　Query OK, 0 rows affected (0.00 sec) 　　此操作使testuser能够在每一个test数据库中的表执行SELECT，INSERT和DELETE以及UPDATE查询操作。现在我们结束操作并退出MySQL客户程序：　　mysql> exit 　　Bye9! 1:使用SHOW语句找出在服务器上当前存在什么数据库： mysql> SHOW DATABASES; 2:2、创建一个数据库MYSQLDATA mysql> Create DATABASE MYSQLDATA; 3:选择你所创建的数据库 mysql> USE MYSQLDATA; (按回车键出现Database changed 时说明操作成功！) 4:查看现在的数据库中存在什么表 mysql> SHOW TABLES; 5:创建一个数据库表 mysql> Create TABLE MYTABLE (name VARCHAR(20), sex CHAR(1)); 6:显示表的结构： mysql> DESCRIBE MYTABLE; 7:往表中加入记录 mysql> insert into MYTABLE values (“hyq”,”M”); 8:用文本方式将数据装入数据库表中（例如D:/mysql.txt） mysql> LOAD DATA LOCAL INFILE “D:/mysql.txt”INTO TABLE MYTABLE; 9:导入.sql文件命令（例如D:/mysql.sql） mysql>use database; mysql>source d:/mysql.sql; 10:删除表 mysql>drop TABLE MYTABLE; 11:清空表 mysql>delete from MYTABLE; 12:更新表中数据 mysql>update MYTABLE set sex=”f”where name=’hyq’; 以下是无意中在网络看到的使用MySql的管理心得, 在windows中MySql以服务形式存在，在使用前应确保此服务已经启动，未启动可用net start mysql命令启动。而Linux中启动时可用“/etc/rc.d/init.d/mysqld start”命令，注意启动者应具有管理员权限。刚安装好的MySql包含一个含空密码的root帐户和一个匿名帐户，这是很大的安全隐患，对于一些重要的应用我们应将安全性尽可能提高，在这里应把匿名帐户删除、root帐户设置密码，可用如下命令进行： use mysql; delete from User where User=”"; update User set Password=PASSWORD(‘newpassword’) where User=’root’; 如果要对用户所用的登录终端进行限制，可以更新User表中相应用户的Host字段，在进行了以上更改后应重新启动数据库服务，此时登录时可用如下类似命令： mysql -uroot -p; mysql -uroot -pnewpassword; mysql mydb -uroot -p; mysql mydb -uroot -pnewpassword; 上面命令参数是常用参数的一部分，详细情况可参考文档。此处的mydb是要登录的数据库的名称。在进行开发和实际应用中，用户不应该只用root用户进行连接数据库，虽然使用root用户进行测试时很方便，但会给系统带来重大安全隐患，也不利于管理技术的提高。我们给一个应用中使用的用户赋予最恰当的数据库权限。如一个只进行数据插入的用户不应赋予其删除数据的权限。MySql的用户管理是通过User表来实现的，添加新用户常用的方法有两个，一是在User表插入相应的数据行，同时设置相应的权限；二是通过GRANT命令创建具有某种权限的用户。其中GRANT的常用用法如下： grant all on mydb.* to NewUserName@HostName identified by “password”; grant usage on *.* to NewUserName@HostName identified by “password”; grant select,insert,update on mydb.* to NewUserName@HostName identified by “password”; grant update,delete on mydb.TestTable to NewUserName@HostName identified by “password”; 若要给此用户赋予他在相应对象上的权限的管理能力，可在GRANT后面添加WITH GRANT OPTION选项。而对于用插入User表添加的用户，Password字段应用PASSWORD 函数进行更新加密，以防不轨之人窃看密码。对于那些已经不用的用户应给予清除，权限过界的用户应及时回收权限，回收权限可以通过更新User表相应字段，也可以使用REVOKE操作。下面给出本人从其它资料(www.cn-java.com)获得的对常用权限的解释：全局管理权限： FILE: 在MySQL服务器上读写文件。 PROCESS: 显示或杀死属于其它用户的服务线程。 RELOAD: 重载访问控制表，刷新日志等。 SHUTDOWN: 关闭MySQL服务。数据库/数据表/数据列权限： Alter: 修改已存在的数据表(例如增加/删除列)和索引。 Create: 建立新的数据库或数据表。 Delete: 删除表的记录。 Drop: 删除数据表或数据库。 INDEX: 建立或删除索引。 Insert: 增加表的记录。 Select: 显示/搜索表的记录。 Update: 修改表中已存在的记录。特别的权限： ALL: 允许做任何事(和root一样)。 USAGE: 只允许登录–其它什么也不允许做。 ——————— MYSQL常用命令有很多朋友虽然安装好了mysql但却不知如何使用它。在这篇文章中我们就从连接MYSQL、修改密码、增加用户等方面来学习一些MYSQL的常用命令。　　有很多朋友虽然安装好了mysql但却不知如何使用它。在这篇文章中我们就从连接MYSQL、修改密码、增加用户等方面来学习一些MYSQL的常用命令。　　　一、连接MYSQL　　　格式：mysql -h主机地址-u用户名－p用户密码　　　　、例：连接到本机上的MYSQL 　　首先在打开DOS窗口，然后进入目录mysqlbin，再键入命令mysql -uroot -p，回车后提示你输密码，如果刚安装好MYSQL，超级用户root是没有密码的，故直接回车即可进入到MYSQL中了，MYSQL的提示符是：mysql> 　　　　、例：连接到远程主机上的MYSQL 　　假设远程主机的IP为：.110.110.110，用户名为root,密码为abcd123。则键入以下命令：　　　　　mysql -h110.110.110.110 -uroot -pabcd123 　　　　（注:u与root可以不用加空格，其它也一样）　　　　、退出MYSQL命令：exit （回车）　　二、修改密码　　　　格式：mysqladmin -u用户名-p旧密码password 新密码　　　、例：给root加个密码ab12。首先在DOS下进入目录mysqlbin，然后键入以下命令　　　　mysqladmin -uroot -password ab12 　　　　注：因为开始时root没有密码，所以-p旧密码一项就可以省略了。　　　　、例：再将root的密码改为djg345 　　mysqladmin -uroot -pab12 password djg345 MYSQL常用命令（下）　　一、操作技巧　　、如果你打命令时，回车后发现忘记加分号，你无须重打一遍命令，只要打个分号回车就可以了。也就是说你可以把一个完整的命令分成几行来打，完后用分号作结束标志就OK。　　、你可以使用光标上下键调出以前的命令。但以前我用过的一个MYSQL旧版本不支持。我现在用的是mysql-3.23.27-beta-win。　　二、显示命令　　、显示数据库列表。　　show databases; 　　刚开始时才两个数据库：mysql和test。mysql库很重要它里面有MYSQL的系统信息，我们改密码和新增用户，实际上就是用这个库进行操作。　　、显示库中的数据表：　　use mysql；／／打开库，学过FOXBASE的一定不会陌生吧　　show tables; 　　、显示数据表的结构：　　describe 表名; 　　、建库：　　create database 库名; 　　、建表：　　use 库名；　　create table 表名(字段设定列表)；　　、删库和删表: 　　drop database 库名; 　　drop table 表名；　　、将表中记录清空：　　delete from 表名; 　　、显示表中的记录：　　select * from 表名; 三、一个建库和建表以及插入数据的实例　　drop database if exists school; //如果存在SCHOOL则删除　　create database school; //建立库SCHOOL 　　use school; //打开库SCHOOL 　　create table teacher //建立表TEACHER 　　( 　　id int(3) auto_increment not null primary key, 　　name char(10) not null, 　　address varchar(50) default ‘深圳’, 　　year date 　　); //建表结束　　//以下为插入字段　　insert into teacher values(”,’glchengang’,'深圳一中’,’-10-10′); 　　insert into teacher values(”,’jack’,'深圳一中’,’-12-23′); 　　注：在建表中（）将ID设为长度为的数字字段:int(3)并让它每个记录自动加一:auto_increment并不能为空:not null而且让他成为主字段primary key 　　（）将NAME设为长度为的字符字段　　（）将ADDRESS设为长度的字符字段，而且缺省值为深圳。varchar和char有什么区别呢，只有等以后的文章再说了。　　（）将YEAR设为日期字段。　　如果你在mysql提示符键入上面的命令也可以，但不方便调试。你可以将以上命令原样写入一个文本文件中假设为school.sql，然后复制到c:\下，并在DOS状态进入目录\mysql\bin，然后键入以下命令：　　mysql -uroot -p密码school.bbb 　　注释:将数据库school备份到school.bbb文件，school.bbb是一个文本文件，文件名任取，打开看看你会有新发现。一.SELECT语句的完整语法为： SELECT[ALL|DISTINCT|DISTINCTROW|TOP] {*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]} FROM tableexpression[,…][IN externaldatabase] [WHERE…] [GROUP BY…] [HAVING…] [ORDER BY…] [WITH OWNERACCESS OPTION] 说明：用中括号([])括起来的部分表示是可选的，用大括号({})括起来的部分是表示必须从中选择其中的一个。 1 FROM子句 FROM 子句指定了SELECT语句中字段的来源。FROM子句后面是包含一个或多个的表达式(由逗号分开)，其中的表达式可为单一表名称、已保存的查询或由INNER JOIN、LEFT JOIN 或RIGHT JOIN 得到的复合结果。如果表或查询存储在外部数据库，在IN 子句之后指明其完整路径。例：下列SQL语句返回所有有定单的客户： SELECT OrderID,Customer.customerID FROM Orders Customers WHERE Orders.CustomerID=Customers.CustomeersID 2 ALL、DISTINCT、DISTINCTROW、TOP谓词 (1) ALL 返回满足SQL语句条件的所有记录。如果没有指明这个谓词，默认为ALL。例：SELECT ALL FirstName,LastName FROM Employees (2) DISTINCT 如果有多个记录的选择字段的数据相同，只返回一个。 (3) DISTINCTROW 如果有重复的记录，只返回一个 (4) TOP显示查询头尾若干记录。也可返回记录的百分比，这是要用TOP N PERCENT子句（其中N 表示百分比）例：返回%定货额最大的定单 SELECT TOP 5 PERCENT* FROM [ Order Details] ORDER BY UnitPrice*Quantity*(1-Discount) DESC 3 用AS 子句为字段取别名如果想为返回的列取一个新的标题，或者，经过对字段的计算或总结之后，产生了一个新的值，希望把它放到一个新的列里显示，则用AS保留。例：返回FirstName字段取别名为NickName SELECT FirstName AS NickName ,LastName ,City FROM Employees 例：返回新的一列显示库存价值 SELECT ProductName ,UnitPrice ,UnitsInStock ,UnitPrice*UnitsInStock AS valueInStock FROM Products 二.WHERE 子句指定查询条件 1 比较运算符比较运算符含义 = 等于 > 大于 = 大于等于 <= 小于等于不等于 !> 不大于 !#1/1/96# AND OrderDate#96-1-1# 也可以表示为： WHERE OrderDate>Datevalue(‘/1/96’) 使用NOT 表达式求反。例：查看年月日以后的定单 WHERE Not OrderDateQuantity 另一种方法是用Microsof JET SQL 独有的JNNER JOIN 语法： FROM table1 INNER JOIN table2 ON table1.field1 comparision table2.field2 其中comparision 就是前面WHERE子句用到的比较运算符。 SELECT FirstName,lastName,OrderID,CustomerID,OrderDate FROM Employees INNER JOIN Orders ON Employees.EmployeeID=Orders.EmployeeID 注意： INNER JOIN不能连接Memo OLE Object Single Double 数据类型字段。在一个JOIN语句中连接多个ON子句语法： SELECT fields FROM table1 INNER JOIN table2 ON table1.field1 compopr table2.field1 AND ON table1.field2 compopr table2.field2 OR ON table1.field3 compopr table2.field3 也可以 SELECT fields FROM table1 INNER JOIN （table2 INNER JOIN [( ]table3 [INNER JOER] [( ]tablex[INNER JOIN] ON table1.field1 compopr table2.field1 ON table1.field2 compopr table2.field2 ON table1.field3 compopr table2.field3 外部连接返回更多记录，在结果中保留不匹配的记录，不管存不存在满足条件的记录都要返回另一侧的所有记录。 FROM table [LEFT|RIGHT]JOIN table2 ON table1.field1comparision table.field2 用左连接来建立外部连接，在表达式的左边的表会显示其所有的数据例：不管有没有定货量，返回所有商品 SELECT ProductName ,OrderID FROM Products LEFT JOIN Orders ON Products.PrductsID=Orders.ProductID 右连接与左连接的差别在于：不管左侧表里有没有匹配的记录，它都从左侧表中返回所有记录。例：如果想了解客户的信息，并统计各个地区的客户分布，这时可以用一个右连接，即使某个地区没有客户，也要返回客户信息。空值不会相互匹配，可以通过外连接才能测试被连接的某个表的字段是否有空值。 SELECT * FROM talbe1 LEFT JOIN table2 ON table1.a=table2.c 1 连接查询中使用Iif函数实现以值显示空值 Iif表达式：Iif(IsNull(Amount,0,Amout) 例：无论定货大于或小于￥，都要返回一个标志。 Iif([Amount]>50,?Big order?,?Small order?) 五. 分组和总结查询结果在SQL的语法里，GROUP BY和HAVING子句用来对数据进行汇总。GROUP BY子句指明了按照哪几个字段来分组，而将记录分组后，用HAVING子句过滤这些记录。 GROUP BY 子句的语法 SELECT fidldlist FROM table WHERE criteria [GROUP BY groupfieldlist [HAVING groupcriteria]] 注：Microsoft Jet数据库Jet 不能对备注或OLE对象字段分组。 GROUP BY字段中的Null值以备分组但是不能被省略。在任何SQL合计函数中不计算Null值。 GROUP BY子句后最多可以带有十个字段，排序优先级按从左到右的顺序排列。例：在‘WA’地区的雇员表中按头衔分组后，找出具有同等头衔的雇员数目大于人的所有头衔。 SELECT Title ,Count(Title) as Total FROM Employees WHERE Region = ‘WA’ GROUP BY Title HAVING Count(Title)>1 JET SQL 中的聚积函数聚集函数意义 SUM ( ) 求和 AVG ( ) 平均值 COUNT ( ) 表达式中记录的数目 COUNT (* ) 计算记录的数目 MAX 最大值 MIN 最小值 VAR 方差 STDEV 标准误差 FIRST 第一个值 LAST 最后一个值六. 用Parameters声明创建参数查询 Parameters声明的语法: PARAMETERS name datatype[,name datatype[, …]] 其中name 是参数的标志符,可以通过标志符引用参数. Datatype说明参数的数据类型. 使用时要把PARAMETERS 声明置于任何其他语句之前. 例: PARAMETERS[Low price] Currency,[Beginning date]datatime SELECT OrderID ,OrderAmount FROM Orders WHERE OrderAMount>[low price] AND OrderDate>=[Beginning date] 七. 功能查询所谓功能查询,实际上是一种操作查询,它可以对数据库进行快速高效的操作.它以选择查询为目的,挑选出符合条件的数据,再对数据进行批处理.功能查询包括更新查询,删除查询,添加查询,和生成表查询. 1 更新查询 UPDATE子句可以同时更改一个或多个表中的数据.它也可以同时更改多个字段的值. 更新查询语法: UPDATE 表名 SET 新值 WHERE 准则例:英国客户的定货量增加%,货运量增加% UPDATE OEDERS SET OrderAmount = OrderAmount *1.1 Freight = Freight*1.03 WHERE ShipCountry = ‘UK’ 2 删除查询 DELETE子句可以使用户删除大量的过时的或冗于的数据. 注:删除查询的对象是整个记录. DELETE子句的语法: DELETE [表名.*] FROM 来源表 WHERE 准则例: 要删除所有年前的定单 DELETE * FROM Orders WHERE OrderData<#94-1-1# 3 追加查询 INSERT子句可以将一个或一组记录追加到一个或多个表的尾部. INTO 子句指定接受新记录的表 valueS 关键字指定新记录所包含的数据值. INSERT 子句的语法: INSETR INTO 目的表或查询(字段,字段,…) valueS(数值,数值,…) 例:增加一个客户 INSERT INTO Employees(FirstName,LastName,title) valueS(‘Harry’,’Washington’,’Trainee’) 4 生成表查询可以一次性地把所有满足条件的记录拷贝到一张新表中.通常制作记录的备份或副本或作为报表的基础. SELECT INTO子句用来创建生成表查询语法: SELECT 字段,字段,… INTO 新表[IN 外部数据库] FROM 来源数据库 WHERE 准则例:为定单制作一个存档备份 SELECT * INTO OrdersArchive FROM Orders 八. 联合查询 UNION运算可以把多个查询的结果合并到一个结果集里显示. UNION运算的一般语法: [表]查询UNION [ALL]查询UNION … 例:返回巴西所有供给商和客户的名字和城市 SELECT CompanyName,City FROM Suppliers WHERE Country = ‘Brazil’ UNION SELECT CompanyName,City FROM Customers WHERE Country = ‘Brazil’ 注: 缺省的情况下,UNION子句不返回重复的记录.如果想显示所有记录,可以加ALL选项 UNION运算要求查询具有相同数目的字段.但是,字段数据类型不必相同. 每一个查询参数中可以使用GROUP BY 子句或HAVING 子句进行分组.要想以指定的顺序来显示返回的数据,可以在最后一个查询的尾部使用OREER BY子句. 九. 交叉查询交叉查询可以对数据进行总和,平均,计数或其他总和计算法的计算,这些数据通过两种信息进行分组:一个显示在表的左部,另一个显示在表的顶部. Microsoft Jet SQL 用TRANSFROM语句创建交叉表查询语法: TRANSFORM aggfunction SELECT 语句 GROUP BY 子句 PIVOT pivotfield[IN(value1 [,value2[,…]]) ] Aggfounction指SQL聚积函数, SELECT语句选择作为标题的的字段, GROUP BY 分组说明： Pivotfield 在查询结果集中创建列标题时用的字段或表达式,用可选的IN子句限制它的取值. value代表创建列标题的固定值. 例:显示在年里每一季度每一位员工所接的定单的数目: TRANSFORM Count(OrderID) SELECT FirstName&’’&LastName; AS FullName FROM Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID WHERE DatePart(“yyyy”,OrderDate)= ‘’ GROUP BY FirstName&’’&LastName; ORDER BY FirstName&’’&LastName; POVOT DatePart(“q”,OrderDate)&’季度’ 十.子查询子查询可以理解为套查询.子查询是一个SELECT语句. 1 表达式的值与子查询返回的单一值做比较语法: 表达式comparision [ANY|ALL|SOME](子查询) 说明： ANY 和SOME谓词是同义词,与比较运算符(=,,,=)一起使用.返回一个布尔值True或False.ANY的意思是,表达式与子查询返回的一系列的值逐一比较,只要其中的一次比较产生True结果,ANY测试的返回True值(既WHERE子句的结果),对应于该表达式的当前记录将进入主查询的结果中.ALL测试则要求表达式与子查询返回的一系列的值的比较都产生True结果,才回返回True值. 例:主查询返回单价比任何一个折扣大于等于%的产品的单价要高的所有产品 SELECT * FROM Products WHERE UnitPrice>ANY (SELECT UnitPrice FROM[Order Details] WHERE Discount>0.25) 2 检查表达式的值是否匹配子查询返回的一组值的某个值语法: [NOT]IN(子查询) 例:返回库存价值大于等于的产品. SELECT ProductName FROM Products WHERE ProductID IN (SELECT PrdoctID FROM [Order DEtails] WHERE UnitPrice*Quantity>= 1000) 3检测子查询是否返回任何记录语法: [NOT]EXISTS (子查询) 例:用EXISTS检索英国的客户 SELECT ComPanyName,ContactName FROM Orders WHERE EXISTS (SELECT * FROM Customers WHERE Country = ‘UK’AND Customers.CustomerID= Orders.CustomerID)

数据库操作语句大全(sql) 一、基础 1、说明：创建数据库 CREATE DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份sql server --- 创建备份数据的 device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' --- 开始备份 BACKUP DATABASE pubs TO testBack 4、说明：创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表) B：create table tab_new as select col1,col2… from tab_old definition only 5、说明：删除新表 drop table tabname 6、说明：增加一个列 Alter table tabname add column col type 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 7、说明：添加主键： Alter table tabname add primary key(col) 说明：删除主键： Alter table tabname drop primary key(col) 8、说明：创建索引：create [unique] index idxname on tabname(col….) 删除索引：drop index idxname 注：索引是不可更改的，想更改必须删除重新建。 9、说明：创建视图：create view viewname as select statement 删除视图：drop view viewname 10、说明：几个简单的基本的sql语句选择：select * from table1 where 范围插入：insert into table1(field1,field2) values(value1,value2) 删除：delete from table1 where 范围更新：update table1 set field1=value1 where 范围查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料! 排序：select * from table1 order by field1,field2 [desc] 总数：select count as totalcount from table1 求和：select sum(field1) as sumvalue from table1 平均：select avg(field1) as avgvalue from table1 最大：select max(field1) as maxvalue from table1 最小：select min(field1) as minvalue from table1 11、说明：几个高级查询运算词 A： UNION 运算符 UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 B： EXCEPT 运算符 EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。 C： INTERSECT 运算符 INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。注：使用运算词的几个查询结果行必须是一致的。 12、说明：使用外连接 A、left （outer） join：左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。 SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c B：right （outer） join: 右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。 C：full/cross （outer） join：全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。 12、分组:Group by: 一张表，一旦分组完成后，查询后只能得到组相关的信息。组相关的信息：（统计信息） count,sum,max,min,avg 分组的标准) 在SQLServer中分组时：不能以text,ntext,image类型的字段作为分组依据在selecte统计函数中的字段，不能和普通的字段放在一起； 13、对数据库进行操作：分离数据库： sp_detach_db; 附加数据库：sp_attach_db 后接表明，附加需要完整的路径名 14.如何修改数据库的名称: sp_renamedb 'old_name', 'new_name' 二、提升 1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用) 法一：select * into b from a where 11（仅用于SQlServer）法二：select top 0 * into b from a 2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用) insert into b(a, b, c) select d,e,f from b; 3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. 4、说明：子查询(表名1：a 表名2：b) select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3) 5、说明：显示文章、提交人和最后回复时间 select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b 6、说明：外连接查询(表名1：a 表名2：b) select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 7、说明：在线视图查询(表名1：a ) select * from (SELECT a,b,c FROM a) T where t.a > 1; 8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括 select * from table1 where time between time1 and time2 select a,b,c, from table1 where a not between 数值1 and 数值2 9、说明：in 的使用方法 select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’) 10、说明：两张关联表，删除主表中已经在副表中没有的信息 delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) 11、说明：四表联查问题： select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where ..... 12、说明：日程安排提前五分钟提醒 SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 13、说明：一条sql 语句搞定数据库分页 select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段具体实现：关于数据库分页： declare @start int,@end int @sql nvarchar(600) set @sql=’select top’+str(@end-@start+1)+’+from T where rid not in(select top’+str(@str-1)+’Rid from T where Rid>-1)’ exec sp_executesql @sql 注意：在top后不能直接跟一个变量，所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列，如果top后还有具体的字段，这样做是非常有好处的。因为这样可以避免 top的字段如果是逻辑索引的，查询的结果后实际表中的不一致（逻辑索引中的数据有可能和数据表中的不一致，而查询时如果处在索引则首先查询索引） 14、说明：前10条记录 select top 10 * form table1 where 范围 15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) 16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 (select a from tableA ) except (select a from tableB) except (select a from tableC) 17、说明：随机取出10条数据 select top 10 * from tablename order by newid() 18、说明：随机选择记录 select newid() 19、说明：删除重复记录 1),delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) 2),select distinct * into temp from tablename delete from tablename insert into tablename select * from temp 评价：这种操作牵连大量的数据的移动，这种做法不适合大容量但数据操作 3),例如：在一个外部表中导入数据，由于某些原因第一次只导入了一部分，但很难判断具体位置，这样只有在下一次全部导入，这样也就产生好多重复的字段，怎样删除重复字段 alter table tablename --添加一个自增列 add column_b int identity(1,1) delete from tablename where column_b not in( select max(column_b) from tablename group by column1,column2,...) alter table tablename drop column column_b 20、说明：列出数据库里所有的表名 select name from sysobjects where type='U' // U代表用户 21、说明：列出表里的所有的列名 select name from syscolumns where id=object_id('TableName') 22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。 select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type 显示结果： type vender pcs 电脑 A 1 电脑 A 1 光盘 B 2 光盘 A 2 手机 B 3 手机 C 3 23、说明：初始化表table1 TRUNCATE TABLE table1 24、说明：选择从10到15的记录 select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc 三、技巧 1、1=1，1=2的使用，在SQL语句组合时用的较多 “where 1=1” 是表示选择全部 “where 1=2”全部不选，如： if @strWhere !='' begin set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere end else begin set @strSQL = 'select count(*) as Total from [' + @tblName + ']' end 我们可以直接写成错误！未找到目录项。 set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere 2、收缩数据库 --重建索引 DBCC REINDEX DBCC INDEXDEFRAG --收缩数据和日志 DBCC SHRINKDB DBCC SHRINKFILE 3、压缩数据库 dbcc shrinkdatabase(dbname) 4、转移数据库给新用户以已存在用户权限 exec sp_change_users_login 'update_one','newname','oldname' go 5、检查备份集 RESTORE VERIFYONLY from disk='E:\dvbbs.bak' 6、修复数据库 ALTER DATABASE [dvbbs] SET SINGLE_USER GO DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK GO ALTER DATABASE [dvbbs] SET MULTI_USER GO 7、日志清除 SET NOCOUNT ON DECLARE @LogicalFileName sysname, @MaxMinutes INT, @NewSize INT USE tablename -- 要操作的数据库名 SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 @MaxMinutes = 10, -- Limit on time allowed to wrap log. @NewSize = 1 -- 你想设定的日志文件的大小(M) Setup / initialize DECLARE @OriginalSize int SELECT @OriginalSize = size FROM sysfiles WHERE name = @LogicalFileName SELECT 'Original Size of ' + db_name() + ' LOG is ' + CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' FROM sysfiles WHERE name = @LogicalFileName CREATE TABLE DummyTrans (DummyColumn char (8000) not null) DECLARE @Counter INT, @StartTime DATETIME, @TruncLog VARCHAR(255) SELECT @StartTime = GETDATE(), @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' DBCC SHRINKFILE (@LogicalFileName, @NewSize) EXEC (@TruncLog) -- Wrap the log if necessary. WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) AND (@OriginalSize * 8 /1024) > @NewSize BEGIN -- Outer loop. SELECT @Counter = 0 WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) BEGIN -- update INSERT DummyTrans VALUES ('Fill Log') DELETE DummyTrans SELECT @Counter = @Counter + 1 END EXEC (@TruncLog) END SELECT 'Final Size of ' + db_name() + ' LOG is ' + CONVERT(VARCHAR(30),size) + ' 8K pages or ' + CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' FROM sysfiles WHERE name = @LogicalFileName DROP TABLE DummyTrans SET NOCOUNT OFF 8、说明：更改某个表 exec sp_changeobjectowner 'tablename','dbo' 9、存储更改全部表 CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch @OldOwner as NVARCHAR(128), @NewOwner as NVARCHAR(128) AS DECLARE @Name as NVARCHAR(128) DECLARE @Owner as NVARCHAR(128) DECLARE @OwnerName as NVARCHAR(128) DECLARE curObject CURSOR FOR select 'Name' = name, 'Owner' = user_name(uid) from sysobjects where user_name(uid)=@OldOwner order by name OPEN curObject FETCH NEXT FROM curObject INTO @Name, @Owner WHILE(@@FETCH_STATUS=0) BEGIN if @Owner=@OldOwner begin set @OwnerName = @OldOwner + '.' + rtrim(@Name) exec sp_changeobjectowner @OwnerName, @NewOwner end -- select @name,@NewOwner,@OldOwner FETCH NEXT FROM curObject INTO @Name, @Owner END close curObject deallocate curObject GO 10、SQL SERVER中直接循环写入数据 declare @i int set @i=1 while @i<30 begin insert into test (userid) values(@i) set @i=@i+1 end 案例：有如下表，要求就裱中所有沒有及格的成績，在每次增長0.1的基礎上，使他們剛好及格: Name score Zhangshan 80 Lishi 59 Wangwu 50 Songquan 69 while((select min(score) from tb_table)<60) begin update tb_table set score =score*1.01 where score60 break else continue end 数据开发-经典 1.按姓氏笔画排序: Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //从少到多 2.数据库加密: select encrypt('原始密码') select pwdencrypt('原始密码') select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同 encrypt('原始密码') select pwdencrypt('原始密码') select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同 3.取回表中字段: declare @list varchar(1000), @sql nvarchar(1000) select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表A' set @sql='select '+right(@list,len(@list)-1)+' from 表A' exec (@sql) 4.查看硬盘分区: EXEC master..xp_fixeddrives 5.比较A,B表是否相等: if (select checksum_agg(binary_checksum(*)) from A) = (select checksum_agg(binary_checksum(*)) from B) print '相等' else print '不相等' 6.杀掉所有的事件探察器进程: DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM master.dbo.sysprocesses WHERE program_name IN('SQL profiler',N'SQL 事件探查器') EXEC sp_msforeach_worker '?' 7.记录搜索: 开头到N条记录 Select Top N * From 表 ------------------------------- N到M条记录(要有主索引ID) Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc ---------------------------------- N到结尾记录 Select Top N * From 表 Order by ID Desc 案例例如1：一张表有一万多条记录，表的第一个字段 RecID 是自增长字段，写一个SQL语句，找出表的第31到第40个记录。 select top 10 recid from A where recid not in(select top 30 recid from A) 分析：如果这样写会产生某些问题，如果recid在表中存在逻辑索引。 select top 10 recid from A where……是从索引中查找，而后面的select top 30 recid from A则在数据表中查找，这样由于索引中的顺序有可能和数据表中的不一致，这样就导致查询到的不是本来的欲得到的数据。解决方案 1，用order by select top 30 recid from A order by ricid 如果该字段不是自增长，就会出现问题 2，在那个子查询中也加条件：select top 30 recid from A where recid>-1 例2：查询表中的最后以条记录，并不知道这个表共有多少数据,以及表结构。 set @s = 'select top 1 * from T where pid not in (select top ' + str(@count-1) + ' pid from T)' print @s exec sp_executesql @s 9：获取当前数据库中的所有用户表 select Name from sysobjects where xtype='u' and status>=0 10：获取某一个表的所有字段 select name from syscolumns where id=object_id('表名') select name from syscolumns where id in (select id from sysobjects where type = 'u' and name = '表名') 两种方式的效果相同 11：查看与某一个表相关的视图、存储过程、函数 select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%' 12：查看当前数据库中所有存储过程 select name as 存储过程名称 from sysobjects where xtype='P' 13：查询用户创建的所有数据库 select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa') 或者 select dbid, name AS DB_NAME from master..sysdatabases where sid 0x01 14：查询某一个表的字段和数据类型 select column_name,data_type from information_schema.columns where table_name = '表名' 15：不同服务器数据库之间的数据操作 --创建链接服务器 exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 ' exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 ' --查询示例 select * from ITSV.数据库名.dbo.表名 --导入示例 select * into 表 from ITSV.数据库名.dbo.表名 --以后不再使用时删除链接服务器 exec sp_dropserver 'ITSV ', 'droplogins ' --连接远程/局域网数据(openrowset/openquery/opendatasource) --1、openrowset --查询示例 select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) --生成本地表 select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) --把本地表导入远程表 insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) select *from 本地表 --更新本地表 update b set b.列A=a.列A from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b on a.column1=b.column1 --openquery用法需要创建一个连接 --首先创建一个连接创建链接服务器 exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 ' --查询 select * FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') --把本地表导入远程表 insert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') select * from 本地表 --更新本地表 update b set b.列B=a.列B FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') as a inner join 本地表 b on a.列A=b.列A --3、opendatasource/openrowset SELECT * FROM opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta --把本地表导入远程表 insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名 select * from 本地表 SQL Server基本函数 SQL Server基本函数 1.字符串函数长度与分析用 1,datalength(Char_expr) 返回字符串包含字符数,但不包含后面的空格 2,substring(expression,start,length) 取子串，字符串的下标是从“1”，start为起始位置，length为字符串长度，实际应用中以len(expression)取得其长度 3,right(char_expr,int_expr) 返回字符串右边第int_expr个字符，还用left于之相反 4,isnull( check_expression , replacement_value )如果check_expression為空，則返回replacement_value的值，不為空，就返回check_expression字符操作类 5,Sp_addtype 自定義數據類型例如：EXEC sp_addtype birthday, datetime, 'NULL' 6,set nocount {on|off} 使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。如果存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。SET NOCOUNT 设置是在执行或运行时设置，而不是在分析时设置。 SET NOCOUNT 为 ON 时，不返回计数（表示受 Transact-SQL 语句影响的行数）。 SET NOCOUNT 为 OFF 时，返回计数常识在SQL查询中：from后最多可以跟多少张表或视图：256 在SQL语句中出现 Order by,查询时，先排序，后取在SQL中，一个字段的最大容量是8000，而对于nvarchar(4000),由于nvarchar是Unicode码。 SQLServer2000同步复制技术实现步骤一、预备工作 1.发布服务器,订阅服务器都创建一个同名的windows用户,并设置相同的密码,做为发布快照文件夹的有效访问用户 --管理工具 --计算机管理 --用户和组 --右键用户 --新建用户 --建立一个隶属于administrator组的登陆windows的用户（SynUser） 2.在发布服务器上,新建一个共享目录,做为发布的快照文件的存放目录,操作: 我的电脑--D:\ 新建一个目录,名为: PUB --右键这个新建的目录 --属性--共享 --选择"共享该文件夹" --通过"权限"按纽来设置具体的用户权限,保证第一步中创建的用户(SynUser) 具有对该文件夹的所有权限 --确定 3.设置SQL代理(SQLSERVERAGENT)服务的启动用户(发布/订阅服务器均做此设置) 开始--程序--管理工具--服务 --右键SQLSERVERAGENT --属性--登陆--选择"此账户" --输入或者选择第一步中创建的windows登录用户名（SynUser） --"密码"中输入该用户的密码 4.设置SQL Server身份验证模式,解决连接时的权限问题(发布/订阅服务器均做此设置) 企业管理器 --右键SQL实例--属性 --安全性--身份验证 --选择"SQL Server 和 Windows" --确定 5.在发布服务器和订阅服务器上互相注册企业管理器 --右键SQL Server组 --新建SQL Server注册... --下一步--可用的服务器中,输入你要注册的远程服务器名 --添加 --下一步--连接使用,选择第二个"SQL Server身份验证" --下一步--输入用户名和密码（SynUser） --下一步--选择SQL Server组,也可以创建一个新组 --下一步--完成 6.对于只能用IP,不能用计算机名的,为其注册服务器别名（此步在实施中没用到） (在连接端配置,比如,在订阅服务器上配置的话,服务器名称中输入的是发布服务器的IP) 开始--程序--Microsoft SQL Server--客户端网络实用工具 --别名--添加 --网络库选择"tcp/ip"--服务器别名输入SQL服务器名 --连接参数--服务器名称中输入SQL服务器ip地址 --如果你修改了SQL的端口,取消选择"动态决定端口",并输入对应的端口号二、正式配置 1、配置发布服务器打开企业管理器，在发布服务器（B、C、D）上执行以下步骤: (1) 从[工具]下拉菜单的[复制]子菜单中选择[配置发布、订阅服务器和分发]出现配置发布和分发向导 (2) [下一步] 选择分发服务器可以选择把发布服务器自己作为分发服务器或者其他sql的服务器（选择自己） (3) [下一步] 设置快照文件夹采用默认\\servername\Pub (4) [下一步] 自定义配置可以选择:是,让我设置分发数据库属性启用发布服务器或设置发布设置否,使用下列默认设置（推荐） (5) [下一步] 设置分发数据库名称和位置采用默认值 (6) [下一步] 启用发布服务器选择作为发布的服务器 (7) [下一步] 选择需要发布的数据库和发布类型 (8) [下一步] 选择注册订阅服务器 (9) [下一步] 完成配置 2、创建出版物发布服务器B、C、D上 (1)从[工具]菜单的[复制]子菜单中选择[创建和管理发布]命令 (2)选择要创建出版物的数据库，然后单击[创建发布] (3)在[创建发布向导]的提示对话框中单击[下一步]系统就会弹出一个对话框。对话框上的内容是复制的三个类型。我们现在选第一个也就是默认的快照发布(其他两个大家可以去看看帮助) (4)单击[下一步]系统要求指定可以订阅该发布的数据库服务器类型, SQLSERVER允许在不同的数据库如 orACLE或ACCESS之间进行数据复制。但是在这里我们选择运行"SQL SERVER 2000"的数据库服务器 (5)单击[下一步]系统就弹出一个定义文章的对话框也就是选择要出版的表注意: 如果前面选择了事务发布则再这一步中只能选择带有主键的表 (6)选择发布名称和描述 (7)自定义发布属性向导提供的选择: 是我将自定义数据筛选,启用匿名订阅和或其他自定义属性否根据指定方式创建发布（建议采用自定义的方式） (8)[下一步] 选择筛选发布的方式 (9)[下一步] 可以选择是否允许匿名订阅 1)如果选择署名订阅,则需要在发布服务器上添加订阅服务器方法: [工具]->[复制]->[配置发布、订阅服务器和分发的属性]->[订阅服务器] 中添加否则在订阅服务器上请求订阅时会出现的提示:改发布不允许匿名订阅如果仍然需要匿名订阅则用以下解决办法 [企业管理器]->[复制]->[发布内容]->[属性]->[订阅选项] 选择允许匿名请求订阅 2)如果选择匿名订阅,则配置订阅服务器时不会出现以上提示 (10)[下一步] 设置快照代理程序调度 (11)[下一步] 完成配置当完成出版物的创建后创建出版物的数据库也就变成了一个共享数据库有数据 srv1.库名..author有字段:id,name,phone, srv2.库名..author有字段:id,name,telphone,adress 要求： srv1.库名..author增加记录则srv1.库名..author记录增加 srv1.库名..author的phone字段更新，则srv1.库名..author对应字段telphone更新 --*/ --大致的处理步骤 --1.在 srv1 上创建连接服务器,以便在 srv1 中操作 srv2,实现同步 exec sp_addlinkedserver 'srv2','','SQLOLEDB','srv2的sql实例名或ip' exec sp_addlinkedsrvlogin 'srv2','false',null,'用户名','密码' go --2.在 srv1 和 srv2 这两台电脑中,启动 msdtc(分布式事务处理服务),并且设置为自动启动。我的电脑--控制面板--管理工具--服务--右键 Distributed Transaction Coordinator--属性--启动--并将启动类型设置为自动启动 go --然后创建一个作业定时调用上面的同步处理存储过程就行了企业管理器 --管理 --SQL Server代理 --右键作业 --新建作业 --"常规"项中输入作业名称 --"步骤"项 --新建 --"步骤名"中输入步骤名 --"类型"中选择"Transact-SQL 脚本(TSQL)" --"数据库"选择执行命令的数据库 --"命令"中输入要执行的语句: exec p_process --确定 --"调度"项 --新建调度 --"名称"中输入调度名称 --"调度类型"中选择你的作业执行安排 --如果选择"反复出现" --点"更改"来设置你的时间安排然后将SQL Agent服务启动,并设置为自动启动,否则你的作业不会被执行设置方法: 我的电脑--控制面板--管理工具--服务--右键 SQLSERVERAGENT--属性--启动类型--选择"自动启动"--确定. --3.实现同步处理的方法2,定时同步 --在srv1中创建如下的同步处理存储过程 create proc p_process as --更新修改过的数据 update b set name=i.name,telphone=i.telphone from srv2.库名.dbo.author b,author i where b.id=i.id and (b.name i.name or b.telphone i.telphone) --插入新增的数据 insert srv2.库名.dbo.author(id,name,telphone) select id,name,telphone from author i where not exists( select * from srv2.库名.dbo.author where id=i.id) --删除已经删除的数据(如果需要的话) delete b from srv2.库名.dbo.author b where not exists( select * from author where id=b.id) go

非常实用不下后悔，word文档格式总共18页一、基础 1、说明：创建数据库 CREATE DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份sql server --- 创建备份数据的 device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' --- 开始备份 BACKUP DATABASE pubs TO testBack 4、说明：创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表) B：create table tab_new as select col1,col2… from tab_old definition only 5、说明：删除新表 drop table tabname 6、说明：增加一个列 Alter table tabname add column col type 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 7、说明：添加主键： Alter table tabname add primary key(col) 说明：删除主键： Alter table tabname drop primary key(col) 8、说明：创建索引：create [unique] index idxname on tabname(col….) 删除索引：drop index idxname 注：索引是不可更改的，想更改必须删除重新建。 9、说明：创建视图：create view viewname as select statement 删除视图：drop view viewname 10、说明：几个简单的基本的sql语句选择：select * from table1 where 范围插入：insert into table1(field1,field2) values(value1,value2) 删除：delete from table1 where 范围更新：update table1 set field1=value1 where 范围查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料! 排序：select * from table1 order by field1,field2 [desc] 总数：select count as totalcount from table1 求和：select sum(field1) as sumvalue from table1 平均：select avg(field1) as avgvalue from table1 最大：select max(field1) as maxvalue from table1 最小：select min(field1) as minvalue from table1 11、说明：几个高级查询运算词 A： UNION 运算符 UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 B： EXCEPT 运算符 EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。 C： INTERSECT 运算符 INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。注：使用运算词的几个查询结果行必须是一致的。 12、说明：使用外连接 A、left （outer） join：左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。 SQL: select a.a, a.b, a.c, b.c, b.d

先登陆服务器： telnet 192.168.0.23 公帐号： openlab-open123 tarena-tarena 再进入SQL：sqlplus sd0807/sd0807 帐号：sd0807－密码同样公帐号：openlab-open123 设置环境变量： ORACLE_SID=oral10g\ --变局部变量 export ORACLE_SID --变全局变量 unset ORACLE_SID --卸载环境变量 ORACLE_HOME=... --安装路径；直接用一句语句也可以，如下 export ORACLE_HOME=/oracledata/.../bin: 一、注意事项：大小写不敏感，即不区分大小写。提倡关键字大写，便于阅读和调式。 “！”在SQL环境下执行Unix命令。 SQL语句是由简单的英语单词构成；这些英语单词称为关键字/保留字，不做它用。SQL由多个关键字构成。 SQL语句由子句构成，有些子句是必须的，有些是可选的。在处理SQL语句时，其中所有的空格都被忽略(空格只用来分开单词，连续多个空格当一个用)。 SQL语句可以在一行上写出，建议多行写出，便于阅读和调试。多条SQL语句必须以分号分隔。多数DBMS不需要在单条SQL语句后加分号，但特定的DBMS可能必须在单条SQL语句后加分号。 SQL语句的最后一句要以 “；”号结束二、写子句顺序 Select column,group_function From table [Where condition] [Group by group_by_expression] [Having group_condition] …… [Order by column]; --最后三、常用简单语句： clear screen：清屏 edit：编辑刚才的一句。 desc/describe：(列出所有列名称) 用法： DESCRIBE [schema.]object[@db_link] dual：亚表，临时用。如：desc dual；／from dual； rollback：回溯，回溯到上次操作前的状态，把这次事务操作作废，只有一次(DDL和DCL语句会自动提交，不能回溯)。可以用commit语句提交，这样就回溯不回了。 set pause on\off ：设置分屏(设置不分屏) set pause "please put an enter key" 且 set pause on：设置带有提示的分屏 oerr ora 904 ：查看错误 set head off ：去掉表头 set feed off ：去掉表尾保存在oracle数据库中的所有操作细节： spool oracleday01.txt ：开始记录 spool off ：开始保存细节四、SELECT语句：选择操作、投影操作。 select：从一个或多个表中检索一个或多个数据列。包含信息：想选择什么表，从什么地方选择。必须要有From子句。(最常用) 当从多张表里查询的时候，会产生笛卡尔积；可用条件过滤它。当两个表有相同字段时必须加前缀，列名前需加表名和“.”，如“s_emp.id”。 1、用法：SELECT columns,prod2,prod3<列> FROM Table1,table2<表名> 分号结束如： select id from s_emp; select last_name,name from s_emp,s_dept where s_emp.dept_id=s_dept.id;--列表每人所在部门 SELECT * FROM Products; --检索所有列。数据太多时，最好别使用上句，会使DBMS降低检索和应用程序的性能。(*通配符) 2、对数据类型的列可进行运算(如加减乘除)。 3、对列起别名：有直接起别名，加AS起别名，用双引号起别名等三种方法 (单引号，引起字符串；双引号，引起别名。起别名有符号，或者区分大小写时，必须用双引号) 多表查询时，可给表起别名。(给列起别名，列<空格>列别名；给表起别名，表<空格>表别名；)。如：Select first_name EMPLOYEES, 12*(salary+100) AS MONEY, manager_id "ID1" From s_emp E; 4、字段的拼接,可用双竖线(双竖线只能用于select语句里)。不同的DBMS可能使用不同的操作符;拼接的字段同样可以起别名。如：Select first_name ||' '|| last_name || ', '|| title "Employees" From s_emp; 排他锁：Select id,salary From s_emp where id=1 For Update; 可以阻止他人并发的修改，直到你解锁。如果已有锁则自动退出：Select id,salary From s_emp where id=1 For Update NoWait; FOR UPDATE ：可以再加 OF 精确到某格。如： ... For Update OF salary ... 注意要解锁。五、ORDER BY 子句，排序 Order by：按某排序列表(默认升序 asc，由低到高；可加 desc，改成降序由高到低) 检索返回数据的顺序没有特殊意义，为了明确地排序用 SELECT 语句检索出的数据，可使用 ORDER BY 子句。 ORDER BY 子句取一个或多个列的名字。对空值，按无穷大处理(升序中，空值排最后；降序中排最前)。 1、用法：Select prod_id,prod_price,prod_name From Products Order By prod_price,prod_name; (从左到右执行排序，先排price) ORDER BY子句中使用的列将是为显示所选择的列，但是实际上并不一定要这样，用非检索的列排序数据是完全合法的。为了按多个列排序，列名之间用逗号分开。 2、支持按相对列位置进行排序。输入 SELECT prod_id,prod_price,prod_name FROM Products ORDER BY 2，3 --(2指price，3指name) 3、升序、降序。默认是升序(asc，从小到大排序)，想降序时用desc。如：SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price DESC; 注意：DESC 关键字只应用到直接位于其前面的列名。如果想在多个列上进行排序，必须对每个列指定DESC关键字。升序是默认的，可不写，但降序必须写。六、WHERE子句，选择、过滤其后只能跟逻辑语句，返回值只有ture或false 如： select last_name,salary from s_emp where salary＝1000;--找出工资1000的人 WHERE子句操作符： 1、逻辑比较运算符＝等于！= 不等于，还有(<> ^= 这两个同样表示不等于) > 大于 >= 大于等于 < 小于 <= 小于等于 2、SQL 比较运算符 between…and… ：在两者之间。(BETWEEN 小值 AND 大值) 如：select last_name,salary from s_emp where salary between 1000 and 1500； --工资1000到1500的人，包括1000和1500。 in(列表)：在列表里面的。如：select last_name,dept_id from s_emp where dept_id in(41,42);第41、42部门的人 like ：包含某内容的。模糊查询可以利用通配符创建比较特定数据的搜索模式，通配符只能用于文本，非文本数据类型不能使用通配符。通配符在搜索模式中任意位置使用，并且可以使用多个通配符。通配符%表示任何字符出现任意次数；还能代表搜索模式中给定位置的0个或多个字符。下划线匹配单个任意字符。如：select table_name from user_tables where table_name like 'S\_%' escape'\'; ' 找出“S_“开头的,由于下划线有任意字符的含义，故需另外定义转移符。但习惯用“\”，为方便其他程序员阅读和检测，一般不改用其他的。 like 'M%'：M开头的 like '_a%'：第二个字符是a的 like '%a%'所有含a的 (“_”表示一个任意字符；“%”表示任意多个任意字符。) 单引号里面的内容，大小写敏感。单引号用来限定字符串，如果将值与串类型的列进行比较，则需要限定引号；用来与数值列进行比较时，不用引号。 is null：是空。(NULL表示不包含值。与空格、0是不同的。) 如：SELECT prod_name,prod_price FROM Products WHERE prod_price IS NULL; 七、高级检索(逻辑运算符)：通常我们需要根据多个条件检索数据。可以使用AND或OR、NOT等连接相关的条件计算次序可以通过圆括号()来明确地分组。不要过分依赖默认计算次序，使用圆括号()没有坏处，它能消除二义性。 and：条件与如 SELECT prod_id,prod_price,prod_name FROM Products WHERE prod_price<4 AND vend_id=‘DELL’ or：条件或 (注： and 的优先级比 or 更高，改变优先级可用括号) 如 SELECT prod_id,prod_price,prod_name FROM Products WHERE prod_price<4 OR vend_id=‘DELL’ not：条件非。否定它之后所跟的任何条件否定的SQL 比较运算符： NOT BETWEEN； NOT IN； NOT LIKE； IS NOT NULL： (注意，按英语习惯用 is not，而不是 not is) NOT 与 IN 在一起使用时，NOT 是找出与条件列表不匹配的行。 IN 列表里有 NULL 时不处理，不影响结果；用 NOT IN 时，有 NULL 则出错，必须排除空值再运算。 in ：选择列表的条件使用IN操作符的优点：在长的选项清单时,语法直观；计算的次序容易管理；比 OR 操作符清单执行更快；最大优点是可以包含其他 SELECT 语句，使用能够动态地建立 WHERE 子句。如 SELECT prod_id,prod_price,prod_name FROM Products WHERE vend_id IN(‘DELL’,’RBER’,’TTSR’); 八、单行函数：函数一般在数据上执行，它给数据的转换和处理提供了方便。不同的DBMS提供的函数不同。函数可能会带来系统的不可移植性(可移植性:所编写的代码可以在多个系统上运行)。加入注释是一个使用函数的好习惯。大多数SQL实现支持以下类型的函数：文本处理，算术运算，日期和时间，数值处理。 Null：空值空值当成无穷大处理，所有空值参与的运算皆为空。空值与空值并不相等，因为空值不能直接运算。如：prod_price="" 这种写法是错的(不要受到corejava的影响) prod_price=NULL 这种写法是错的(不要受到corejava的影响) prod_price IS NULL 这种写法才是对的 NVL：处理空值，把空值转化为指定值。可转化为日期、字符、数值等三种(注意：转化时，两参数必须要同类型) 如：NVL(date, '01-JAN-95') NVL(title,'NO Title Yet') NVL(salary,0) 错误写法： Select last_name,title,salary*commission_pct/100 COMM From s_emp;--没提成的人没法显示工资正确写法： Select last_name,title,salary*NVL(commission_pct,0)/100 COMM From s_emp;--把提成是空值的转化为0 DISTINCT：过滤重复把重复的行过滤掉；多个字段组合时，只排除组合重复的。 DISTINCT必须使用列名，不能使用计算或者表达式。所有的聚合函数都可以使用。如果指定列名，则DISTINCT只能用于COUNT(列名)，DISTINCT不能用于COUNT(*)。如：Select Distinct name From s_dept; Select Distinct dept_id,title From s_emp; 文本处理： TRIM()/LTRIM()/RTIRM()：去空格。只能去掉头和尾的空格，中间的不理。 trim(' heo Are fdou ') --> heo Are fdou 输入：select trim(' heo Are fdou ') from dual; -->：heo Are fdou LOWER：转小写 lower('SQL Course') --> sql course UPPER：转大写 upper(' SQL Course') --－>SQL COURSE INITCAP：首字母转大写，其余转小写 initcap(SQL Course') '--> Sql Course CONCAT：合成。双竖线只能在select语句里面用，这个可用于任何语句。 Concat('Good','String') --> GoodString SUBSTR：截取。 Substr('String', 1 ,3) --> Str 第一个数字“1”，表示从第几个开始截取；若要从倒数第几个开始，用负数，如“－2”表示倒数第2个。上式中第2个数字“3”表示截取多少个。 LENGTH：统计长度。 Length('String') --> 6 NVL：转换空值日期和时间处理： Oracle日期格式：DD-MMM-YYYY (D代表日期date，M代表月month，Y代表年year) 如：SELECT prod_name (DAY表示完整的星期几，DY显示星期的前三个字母) FROM Products WHERE prod_time BETWEEN to_date(’01-JAN-2008’) AND to_date(’31-DEC-2008’); 日期可以进行加减，默认单位是1天。日期与日期可以相减，得出天数；日期与日期但不能相加。 sysdate －> 系统的当天 Months_Between('01-Sep-95','11-Jan-94') --> 19.774194 相差多少个月,Between里面也可以填函数。 Add_months('11-Jan-94',6) --> 11-Jul-94 增加多少个月 Next_day('01-Sep-95','Friday') --> '08-Sep-95' 下一个星期五。其中的'Friday'可用6替代，因为星期日＝1 Last_day('01-Sep-95') --> '30-Sep-95' 这个月的最后一天数值处理：可以运用于代数，三角，几何 ROUND：四舍五入 Round(45.925,2) －> 45.93 Round(45.925,0) －> 46 Round(45.925,-1) －> 50 逗号前一个数是要处理的数据源，后一个参数表示保留多少位小数。后一参数是负数时，表示舍去小数点前的几位，例3是舍去个位及其后的。不写后一参数时，默认不保留小数。 TRUNC：舍去末位。直接舍去，不会进位。 Trung(45.925,2) －> 45.92 Trung(45.925,2) －> 45.92 Trung(45.925,2) －> 45.92 日期的舍取：常用的数值处理函数有： ABS() 绝对值 ABS(-5741.5854) --> 5741.5854 PI() 圆周率注意：oracle中不支持 PI()函数；MYSql 支持PI()函数。 SIN() 正统值 Oracle还支持COS()、ASIN()、ACOS()函数 SQRT() 平方根转化： TO_CHAR(number,'fmt')：把数值转换成字符串显示数字的命令 9：正常显示数字； 0：显示包括0的数值形式，空位强制补0； $：以美元符号显示货币； L：按当前环境显示相关的货币符号； . 和，：在固定位置出现“.”点和“，”逗号；不够位时，四舍五入。例题：SQL> select 'Order'||To_char(id)|| 2 'was filled for a total of' 3 ||To_char(total,'fm$9,999,999') 4 from s_ord 5 where ship_date ='21-SEP-92'; TO_NUMBER(char)：把字符转换成数字九、链接内链接：严格匹配两表的记录。外链接分左链接和右链接: 会使用一方表中的所有记录去和另一格表中的记录按条件匹配，空值也会匹配，这个表中的所有记录都会显示，数据库会模拟出记录去和那些不匹配的记录匹配。左链接加号在右面如：有 TABLE1 TABLE2 1的一条记录在2里面没有匹配上，那么1里面的记录保留 2的一条记录在1里面没有匹配上，那么2丢弃右链接正好相反 --例题：哪些人是领导。 select distinct b.id,b.last_name manager from s_emp a,s_emp b where a.manager_id=b.id(+); 左右顺序有区别，这是另外新建一个表，要显示的是第二个表格的内容。 +放在没有匹配行的表一侧，令表格能完整显示出来。标准写法：内连接用INNER，左连接用LEFT，右连接用RIGHT。 select distinct b.id,b.last_name manager from s_emp a LEFT join s_emp b ON a.manager_id=b.id; 十、组函数：分组允许将数据分为多个逻辑组，以便能对每个组进行聚集计算。 Group：分组 Group by：分组。(默认按升序对所分的组排序；想要降序要用 order by)可以包括任意数目的列。如果嵌入了分组，数据将在最后规定的分组上进行汇总。 GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式，但不能是聚集函数。 *如果在SELECT 中使用表达式，则必须在GROUP BY子句中指定相同的表达式，不能使用别名。除聚合计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL，它们将分为一组。 Having：过滤。分组之后，不能再用where，要用having 选择过滤。Having不能单独存在，必须跟在group by后面。 WHERE在数据分组前进行过滤，HAVING在数据分组后过滤。可以在SQL中同时使用 WHERE和HAVING，先执行WHERE，再执行HAVING。聚合函数： AVG：平均值 (忽略值为NULL的行，但不能用 AVG(*)) COUNT：计数 (Count(列)不计算空值；但 COUNT(*)表示统计表中所有行数，包含空值) MAX：最大值 (忽略列值为 NULL 的行。但有些DBMS还允许返回文本列中的最大值，在作用于文本数据时，如果数据按照相应的列排序，则 MAX()返回最后一行。) MIN：最小值 (忽略值为 NULL 的行。不能用 MIN(*)。一般是找出数值或者日期值的最小值。但有些DBMS还允许返回文本列中的最小值，这时返回文本最前一行) SUM：求和 (忽略值为 NULL 的值。SUM 不能作用于字符串类型，而 MAX()，MIN()函数能。也不能 SUM(*)) 子查询：查询语句的嵌套可以用于任意select 语句里面，但子查询不能出现 order by。子查询总是从内向外处理。作为子查询的SELECT 语句只能查询单个列，企图检索多个列，将会错误。如：找出工资最低的人select min(last_name),min(salary) from s_emp; 或者用子查询select last_name,salary from s_emp where salary=(select min(salary) from s_emp); E-R图：属性: E(Entity) -R(Relationship) * (Mandatory marked 强制的) 强制的非空属性 o (Optional marked 可选的) 可选属性(可以有值也可以没有) #* (Primary marked ) 表示此属性唯一且非空约束：针对表中的字段进行定义的。 PK：primary key (主键约束，PK=UK+NN)保证实体的完整性，保证记录的唯一主键约束，唯一且非空，并且每一个表中只能有一个主键，有两个字段联合作为主键，只有两个字段放在一起唯一标识记录，叫做联合主键(Composite Primary Key)。 FK：foreign key (外建约束)保证引用的完整性,外键约束,外键的取值是受另外一张表中的主键或唯一值的约束,不能够取其他值, 只能够引用主键会唯一键的值，被引用的表，叫做parent table(父表)，引用方的表叫做child table(子表); child table(子表)，要想创建子表，就要先创建父表，后创建子表，记录的插入也是如此，先父表后子表，删除记录，要先删除子表记录，后删除父表记录，要修改记录，如果要修改父表的记录要保证没有被子表引用。要删表时，要先删子表，后删除父表。 U：unique key(唯一键 UK)，值为唯一，不能重复。在有唯一性约束的列，可以有多个空值，因为空值不相等。 NN：NOT NULL，不能为空。 index(索引)是数据库特有的一类对象，实际应用中一定要考虑索引，view(示图) 数量关系: 一对一关系多对一关系一对多关系多对多关系范式：好处：降低数据冗余；减少完整性问题；标识实体，关系和表第一范式(First normal form：1Nf)，每一个属性说一件事情。所有的属性都必须是单值，也就是属性只表示单一的意义。 (记录可以重复，会有大量冗余，没有任何限制) 第二范式(2N范式)，最少有一个属性要求唯一且非空PK，其他跟他有关联(记录不可重复，但是数据可能会出现冗余)。第三范式(3N范式)，非主属性只能依赖于主属性,不能依赖于其他非主属性。(解决数据冗余问题,不能存在推理能得出的数据) 一般情况会做到第三范式。创建表： Create Table 表名 (字段名1 类型(数据长度)(default ...) 约束条件, 字段名2 类型(数据长度) 约束条件 ); 建表的名称：必须字母开头；最多30字符；只能使用“A～Z、a～z、0～9、_、$、#”；同一目录下不能有同名的表；表名不能跟关键字、特殊含意字符同样。如：create table number_1 (n1 number(2,4), n2 number(3,-1), n3 number); create table t_sd0808(id number(12) primary key,name varchar(30) not null); MySQL的： create table student (oid int primary key, ACTNO varchar(20) not null unique, BALANCE double); --MySQL的number类型分小类了，Oracle只有number，且MySQL的数值型不用定大小 Oracle的： create table t_ad (oid number(15) primary key, ACTNO varchar(20) not null unique,BALANCE number(20)); INSERT：插入(或添加)行到数据库表中的关键字。插入方式有以下几种：插入完整的行；插入行的一部分；插入某些查询的结果。对于INSERT操作，可能需要客户机/服务器的DBMS中的特定的安全权限。插入行(方式一) INSERT INTO products VALUES(2008,’TV’,222.22,’US’); 依赖于表中定义的顺序，不提倡使用。有空值时需要自己补上。插入行(方式二) INSERT INTO products(id,name,price,vend_name) VALUES(2008,’TV’,222.22,’US’); 依赖于逻辑顺序，会自动补上空值，提倡使用。插入检索出的数据：可以插入多条行到数据库表中 INSERT INTO products(*,*,*,*) SELECT *,*,*,* FROM products_copy; 如果这个表为空，则没有行被插入，不会产生错误，因为操作是合法的。可以使用WHERE加以行过滤。复制表：将一个表的内容复制到一个全新的表(在运行中创建，开始可以不存在) CREATE TABLE 新表名 AS SELECT * FROM 表名; INSERT INTO 与 CREATE TABLE AS SELECT 不同，前者是导入数据，而后者是导入表。任何SELECT选项和子句都可以使用，包括WHERE和GROUP BY。可利用联接从多个表插入数据。不管从多少个表中检索数据，数据都只能插入到单个表中。更新数据 UPDATE 语句需要提供以下信息：要更新的表；列名和新值；确定要更新的哪些行的过滤条件。 UPDATE 表名 SET vend_name = ‘HP’, prod_name = ‘NEWCOMPUTER’ WHERE vend_name = ‘IBM’; --UPDATE 语句中可以使用子查询，使得能用SELECT语句检索出的数据更新列数据。也可以将一个列值更新为 NULL。删除数据 DELETE 语句 DELETE FROM products WHERE prod_name = ‘COMPUTER’; 全行删除，不要省略WHERE，注意安全。 DELETE不需要列名或通配符。删除整行而不是删除列。DELETE是删除表的内容而不是删除表。如果想从表中删除所有内容，可以使用TRUNCATE TABLE语句(清空表格)，它更快。数字字典表： Sequence:排列。存储物理地址 Index:索引。依附于表，为提高检索速度。 View:视图。看到表的一部分数据。限制数据访问。简化查询。数据独立性。本质上是一个sql查询语句。 Create[or Relace][Force|noForce] View 视图名 [(alias[,alias]…)] 别名列表 As subquery [With Check Option [Constraint ……]] [With Read Only] 注意：有些DBMS不允许分组或排序视图，不能有 Order by 语句。可以有 Select 语句。删除视图： DROP VIEW 视图名 Rownum:纬列。内存里排序的前N个。在where语句中，可以用＝1，和<=N 或 N。因为这是内存读取，没有1就丢弃再新建1。只能从1开始。需要从中间开始时，需二重子rownum语句需取别名。经典应用： Top-n Analysis (求前N名或最后N名) Select [查询列表], Rownum From (Select [查询列表(要对应)] From 表 Order by Top-N_字段) Where Rownum <= N 分页显示： --取工资第5～10名的员工(二重子rownum语句，取别名) select rn,id,last_name,salary From ( select id,last_name,salary,Rownum rn From (Select id,last_name,salary from s_emp order by salary desc) where rownum <= 10) where rn between 5 and 10; Union：合并表 Select … Union Select… 把两个Select语句的表合并。要求两表的字段数目和类型按顺序对应。合并后的表，自动过滤重复的行。 Intersect：交。同上例，把两个Select表相交。 Minus：减。把相交的内容减去。 not exists 除运算。添加字段(列)： Alter Table 表名 Add (column dataype [Default expr][Not Null] [,column datatype]…); 添加有非空限制的字段时，要加Default语句字段名字不可以直接改名，需要添加新字段，再复制旧字段后删除旧字段。添加约束： Alter Table 表名 Add [CONSTRAINT constraint] type (column); 添加非空约束时，要用Modify语句。查看约束名时，可以违反约束再看出错提示；或者查看约束字典desc user_constraints 减少字段： Alter Table 表名 Drop (column [,column]…); 删除约束： Alter Table 表名 Drop CONSTRAINT column; 或： Alter Table 表名 Drop Primary Key Cascade; 暂时关闭约束，并非删除： Alter Table 表名 Disable CONSTRAINT column Cascade; 打开刚才关闭的约束： Alter Table 表名 Enable CONSTRAINTcolumn; 修改字段： Alter Table 表名 Modify (column dataype [Default expr][Not Null] [,column datatype]…); 修改字段的类型、大小、约束、非空限制、空值转换。删除表：会删除表的所有数据，所有索引也会删除，约束条件也删除，不可以roll back恢复。 Drop Table 表名 [Cascade Constraints]; 加 [Cascade Constraints] 把子表的约束条件也删除；但只加 [Cascade]会把子表也删除。改表名： Rename 原表名 To 新表名; 清空表格： TRUNCATE TABLE 表名; 相比Delete,Truncate Table清空很快，但不可恢复。清空后释放内存。 Delete 删除后可以roll back。清空后不释放内存。

Ø 常用查询 MySQL结束符是“；”结束。 1、显示所有数据库 show databases; 2、删除数据库 drop database dbName; 3、创建数据库 create database [if not exists] dbName; 中括号部分可选的，判断该数据不存在就创建 4、切换、使用指定数据库 use dbName; 5、显示当前使用数据库所有的表对象 show tables； 6、显示表结构describe（desc） desc tableName; 7、创建一张表 create table user ( --int 整型 uId int, --小数 uPrice decimal, --普通长度文本，default设置默认值 uName varchar(255) default ‘zhangsan’, --超长文本 uRemark text, --图片 uPhoto blob, --日期 uBirthday datetime ); 8、子查询建表方法部分列名匹配模式： create table userInfo ( name varchar(20), sex char ) as select name, sex from user; 上面的列名和子查询的列名以及类型要对应全部列名模式： create table userInfo as select * from user; 直接将整个表的类型和数据备份到新表userInfo中 9、添加表字段添加单列 alter table user add tel varchar(11) default ‘02012345678’; 添加多列 alter table user add ( photo blob, birthday date ); 上面就同时增加了多列字段 10、修改表字段修改tel列 alter table user modify tel varchar(15) default ‘02087654321’; 修改tel列的位置，在第一列显示 alter table user modify tel varchar(15) default '02087654321' first; 修改tel列的位置，在指定列之后显示 alter table user modify tel varchar(15) default '02087654321' after age; 注意：alter modify不支持一次修改多个列，但是Oracle支持多列修改但是MySQL可以通过多个modify的方式完成： alter table user modify tel varchar(15) default '02087654321' first, modify name varchar(20) after tel; 11、删除指定字段 alter table user drop photo; 12、重命名表数据表重命名 alter table user rename to users; 字段重命名 alter table users change name u_name varchar(10); alter table users change sex u_sex varchar(10) after u_name; 如果需要改变列名建议使用change，如果需要改变数据类型和显示位置可以使用modify 13、删除表 drop table users; drop删除表会删除表结构，表对象将不存在数据中；数据也不会存在；表内的对象也不存在，如：索引、视图、约束； truncate删除表 truncate都被当成DDL出来，truncate的作用就是删除该表里的全部数据，保留表结构。相当于DDL中的delete语句，但是truncate比delete语句的速度要快得多。但是truncate不能带条件删除指定数据，只会删除所有的数据。如果删除的表有外键，删除的速度类似于delete。但新版本的MySQL中truncate的速度比delete速度快。 Ø 约束 MySQL中约束保存在information_schema数据库的table_constraints中，可以通过该表查询约束信息；约束主要完成对数据的检验，保证数据库数据的完整性；如果有相互依赖数据，保证该数据不被删除。常用五类约束： not null：非空约束，指定某列不为空 unique：唯一约束，指定某列和几列组合的数据不能重复 primary key：主键约束，指定某列的数据不能重复、唯一 foreign key：外键，指定该列记录属于主表中的一条记录，参照另一条数据 check：检查，指定一个表达式，用于检验指定数据 MySQL不支持check约束，但可以使用check约束，而没有任何效果；根据约束数据列限制，约束可分为：单列约束：每个约束只约束一列多列约束：每个约束约束多列数据 MySQL中约束保存在information_schema数据库的table_constraints中，可以通过该表查询约束信息； 1、 not null约束非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。 Null类型特征：所有的类型的值都可以是null，包括int、float等数据类型空字符串“”是不等于null，0也不等于null create table temp( id int not null, name varchar(255) not null default ‘abc’, sex char null ) 上面的table加上了非空约束，也可以用alter来修改或增加非空约束增加非空约束 alter table temp modify sex varchar(2) not null; 取消非空约束 alter table temp modify sex varchar(2) null; 取消非空约束，增加默认值 alter table temp modify sex varchar(2) default ‘abc’ null; 2、 unique 唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。虽然唯一约束不允许出现重复的值，但是可以为多个null 同一个表可以有多个唯一约束，多个列组合的约束。在创建唯一约束的时候，如果不给唯一约束名称，就默认和列名相同。唯一约束不仅可以在一个表内创建，而且可以同时多表创建组合唯一约束。 MySQL会给唯一约束的列上默认创建一个唯一索引； create table temp ( id int not null, name varchar(25), password varchar(16), --使用表级约束语法， constraint uk_name_pwd unique(name, password) ); 表示用户名和密码组合不能重复添加唯一约束 alter table temp add unique(name, password); alter table temp modify name varchar(25) unique; 删除约束 alter table temp drop index name; 3、 primary key 主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值；如果的多列组合的主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别上创建。MySQL的主键名总是PRIMARY，当创建主键约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。列模式： create table temp( /*主键约束*/ id int primary key, name varchar(25) ); create table temp2( id int not null, name varchar(25), pwd varchar(15), constraint pk_temp_id primary key(id) ); 组合模式： create table temp2( id int not null, name varchar(25), pwd varchar(15), constraint pk_temp_id primary key(name, pwd) ); alter删除主键约束 alter table temp drop primary key; alter添加主键 alter table temp add primary key(name, pwd); alter修改列为主键 alter table temp modify id int primary key; 设置主键自增 create table temp( id int auto_increment primary key, name varchar(20), pwd varchar(16) ); auto_increment自增模式，设置自增后在插入数据的时候就不需要给该列插入值了。 4、 foreign key 约束外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。也就是说从表的外键值必须在主表中能找到或者为空。当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据。还有一种就是级联删除子表数据。注意：外键约束的参照列，在主表中引用的只能是主键或唯一键约束的列，假定引用的主表列不是唯一的记录，那么从表引用的数据就不确定记录的位置。同一个表可以有多个外键约束。创建外键约束：主表 create table classes( id int auto_increment primary key, name varchar(20) ); 从表 create table student( id int auto_increment, name varchar(22), constraint pk_id primary key(id), classes_id int references classes(id) ); 通常先建主表，然后再建从表，这样从表的参照引用的表才存在。表级别创建外键约束： create table student( id int auto_increment primary key, name varchar(25), classes_id int, foreign key(classes_id) references classes(id) ); 上面的创建外键的方法没有指定约束名称，系统会默认给外键约束分配外键约束名称，命名为student_ibfk_n，其中student是表名，n是当前约束从1开始的整数。指定约束名称： create table student( id int auto_increment primary key, name varchar(25), classes_id int, /*指定约束名称*/ constraint fk_classes_id foreign key(classes_id) references classes(id) ); 多列外键组合，必须用表级别约束语法： create table classes( id int, name varchar(20), number int, primary key(name, number) ); create table student( id int auto_increment primary key, name varchar(20), classes_name varchar(20), classes_number int, /*表级别联合外键*/ foreign key(classes_name, classes_number) references classes(name, number) ); 删除外键约束： alter table student drop foreign key student_ibfk_1; alter table student drop foreign key fk_student_id; 增加外键约束 alter table student add foreign key(classes_name, classes_number) referencesclasses(name, number); 自引用、自关联（递归表、树状表） create table tree( id int auto_increment primary key, name varchar(50), parent_id int, foreign key(parent_id) references tree(id) ); 级联删除：删除主表的数据时，关联的从表数据也删除，则需要在建立外键约束的后面增加on deletecascade 或on delete set null，前者是级联删除，后者是将从表的关联列的值设置为null。 create table student( id int auto_increment primary key, name varchar(20), classes_name varchar(20), classes_number int, /*表级别联合外键*/ foreign key(classes_name, classes_number) references classes(name, number) on deletecascade ); 5、 check约束 MySQL可以使用check约束，但check约束对数据验证没有任何作用。 create table temp( id int auto_increment, name varchar(20), age int, primary key(id), /*check约束*/ check(age > 20) ); 上面check约束要求age必须大于0，但没有任何作用。但是创建table的时候没有任何错误或警告。 Ø 索引索引是存放在模式（schema）中的一个数据库对象，索引的作用就是提高对表的检索查询速度，索引是通过快速访问的方法来进行快速定位数据，从而减少了对磁盘的读写操作。索引是数据库的一个对象，它不能独立存在，必须对某个表对象进行依赖。提示：索引保存在information_schema数据库里的STATISTICS表中。创建索引方式：自动：当表上定义主键约束、唯一、外键约束时，该表会被系统自动添加上索引。手动：手动在相关表或列上增加索引，提高查询速度。删除索引方式：自动：当表对象被删除时，该表上的索引自动被删除手动：手动删除指定表对象的相关列上的索引索引类似于书籍的目录，可以快速定位到相关的数据，一个表可以有多个索引。创建索引： create index idx_temp_name on temp(name); 组合索引： create index idx_temp_name$pwd on temp(name, pwd); 删除索引： drop index idx_temp_name on temp; Ø 视图视图就是一个表或多个表的查询结果，它是一张虚拟的表，因为它并不能存储数据。视图的作用、优点：限制对数据的访问让复杂查询变得简单提供数据的独立性可以完成对相同数据的不同显示创建、修改视图 create or replace view view_temp as select name, age from temp; 通常我们并不对视图的数据做修改操作，因为视图是一张虚拟的表，它并不存储实际数据。如果想让视图不被修改，可以用with check option来完成限制。 create or replace view view_temp as select * from temp with check option; 修改视图： alter view view_temp as select id, name from temp; 删除视图： drop view view_temp; 显示创建语法： show create view v_temp; Ø DML语句 DML主要针对数据库表对象的数据而言的，一般DML完成：插入新数据修改已添加的数据删除不需要的数据 1、 insert into 插入语句 insert into temp values(null, ‘jack’, 25); 主键自增可以不插入，所以用null代替指定列 insert into temp(name, age) values(‘jack’, 22); 在表面后面带括号，括号中写列名，values中写指定列名的值即可。当省略列名就表示插入全部数据，注意插入值的顺序和列的顺序需要保持一致。 Set方式插入，也可以指定列 insert into temp set id = 7, name = 'jason'; MySQL中外键的table的外键引用列可以插入数据可以为null，不参照主表的数据。使用子查询插入数据 insert into temp(name) select name from classes; 多行插入 insert into temp values(null, ‘jack’, 22), (null, ‘jackson’ 23); 2、 update 修改语句 update主要完成对数据的修改操作，可以修改一条或多条数据。修改多条或指定条件的数据，需要用where条件来完成。修改所有数据 update temp set name = ‘jack2’; 所有的数据的name会被修改，如果修改多列用“,”分开 update temp set name = ‘jack’, age = 22; 修改指定条件的记录需要用where update temp set name = ‘jack’ where age > 22; 3、 delete 删除语句删除table中的数据，可以删除所有，带条件可以删除指定的记录。删除所有数据 delete from temp; 删除指定条件数据 delete from temp where age > 20; Ø select 查询、function 函数 select查询语句用得最广泛、功能也最丰富。可以完成单条记录、多条记录、单表、多表、子查询等。 1、查询某张表所有数据 select * from temp; *代表所有列，temp代表表名，不带条件就查询所有数据 2、查询指定列和条件的数据 select name, age from temp where age = 22; 查询name和age这两列，age 等于22的数据。 3、对查询的数据进行运算操作 select age + 2, age / 2, age – 2, age * 2 from temp where age – 2 > 22; 4、 concat函数，字符串连接 select concat(name, ‘-eco’) from temp; concat和null进行连接，会导致连接后的数据成为null 5、 as 对列重命名 select name as ‘名称’ from temp; as也可以省略不写，效果一样如果重命名的列名出现特殊字符，如“‘”单引号，那就需要用双引号引在外面 select name as “名’称” from temp; 6、也可以给table去别名 select t.name Name from temp as t; 7、查询常量类似于SQL Server select 5 + 2; select concat('a', 'bbb'); 8、 distinct 去掉重复数据 select distinct id from temp; 多列将是组合的重复数据 select distinct id, age from temp; 9、 where 条件查询大于>、大于等于>=、小于<、小于等于<=、等于=、不等于<> 都可以出现在where语句中 select * from t where a > 2 or a >= 3 or a < 5 or a <= 6 or a = 7 or a <> 0; 10、 and 并且 select * from temp where age > 20 and name = ‘jack’; 查询名称等于jack并且年龄大于20的 11、 or 或者满足一个即可 select * from tmep where name = ‘jack’ or name = ‘jackson’; 12、 between v and v2 大于等于v且小于等于v2 select * form temp where age between 20 and 25; 13、 in 查询可以多个条件类似于or select * from temp where id in (1, 2, 3); 查询id在括号中出现的数据 14、 like 模糊查询查询name以j开头的 select * from temp where name like ‘j%’; 查询name包含k的 select * from temp where name like ‘%k%’; escape转义 select * from temp where name like ‘/_%’ escape ‘/’; 指定/为转义字符，上面的就可以查询name中包含“_”的数据 15、 is null、is not null 查询为null的数据 select * from temp where name is null; 查询不为null的数据 select * from temp where name is not null; 16、 not select * from temp where not (age > 20); 取小于等于20的数据 select * from temp where id not in(1, 2); 17、 order by 排序，有desc、asc升序、降序 select * from temp order by id; 默认desc排序 select * from temp order by id asc; 多列组合 select * from temp order by id, age; Ø function 函数函数的作用比较大，一般多用在select查询语句和where条件语句之后。按照函数返回的结果，可以分为：多行函数和单行函数；所谓的单行函数就是将每条数据进行独立的计算，然后每条数据得到一条结果。如：字符串函数；而多行函数，就是多条记录同时计算，得到最终只有一条结果记录。如：sum、avg等多行函数也称为聚集函数、分组函数，主要用于完成一些统计功能。MySQL的单行函数有如下特征：单行函数的参数可以是变量、常量或数据列。单行函数可以接受多个参数，但返回一个值。单行函数就是它会对每一行单独起作用，每一行（可能包含多个参数）返回一个结果。单行函数可以改变参数的数据类型。单行函数支持嵌套使用：内层函数的返回值是外层函数的参数。单行函数可以分为：类型转换函数；位函数；流程控制语句；加密解密函数；信息函数单行函数 1、 char_length字符长度 select char_length(tel) from user; 2、 sin函数 select sin(age) from user; select sin(1.57); 3、添加日期函数 select date_add('2010-06-21', interval 2 month); interval是一个关键字，2 month是2个月的意思，2是数值，month是单位 select addDate('2011-05-28', 2); 在前面的日期上加上后面的天数 4、获取当前系统时间、日期 select curdate(); select curtime(); 5、加密函数 select md5('zhangsan'); 6、 Null 处理函数 select ifnull(birthday, 'is null birthday') from user; 如果birthday为null，就返回后面的字符串 select nullif(age, 245) from user; 如果age等于245就返回null，不等就返回age select isnull(birthday) from user; 判断birthday是否为null select if(isnull(birthday), 'birthday is null', 'birthday not is null') from user; 如果birthday为null或是0就返回birthday is null，否则就返回birthday not is null；类似于三目运算符 7、 case 流程函数 case函数是一个流程控制函数，可以接受多个参数，但最终只会返回一个结果。 select name, age, (case sex when 1 then '男' when 0 then '女' else '火星人' end ) sex from user; 组函数组函数就是多行函数，组函数是完成一行或多行结果集的运算，最后返回一个结果，而不是每条记录返回一个结果。 1、 avg平均值运算 select avg(age) from user; select avg(distinct age) from user; 2、 count 记录条数统计 select count(*), count(age), count(distinct age) from user; 3、 max 最大值 select max(age), max(distinct age) from user; 4、 min 最小值 select min(age), min(distinct age) from user; 5、 sum 求和、聚和 select sum(age), sum(distinct age) from user; select sum(ifnull(age, 0)) from user; 6、 group by 分组 select count(*), sex from user group by sex; select count(*) from user group by age; select * from user group by sex, age; 7、 having进行条件过滤不能在where子句中过滤组，where子句仅用于过滤行。过滤group by需要having 不能在where子句中用组函数，having中才能用组函数 select count(*) from user group by sex having sex <> 2; Ø 多表查询和子查询数据库的查询功能最为丰富，很多时候需要用到查询完成一些事物，而且不是单纯的对一个表进行操作。而是对多个表进行联合查询， MySQL中多表连接查询有两种规范，较早的SQL92规范支持，如下几种表连接查询：等值连接非等值连接外连接广义笛卡尔积 SQL99规则提供了可读性更好的多表连接语法，并提供了更多类型的连接查询，SQL99支持如下几种多表连接查询：交叉连接自然连接使用using子句的连接使用on子句连接全部连接或者左右外连接 SQL92的连接查询 SQL92的连接查询语法比较简单，多将多个table放置在from关键字之后，多个table用“，”隔开；连接的条件放在where条件之后，与查询条件直接用and逻辑运算符进行连接。如果条件中使用的是相等，则称为等值连接，相反则称为非等值，如果没有任何条件则称为广义笛卡尔积。广义笛卡尔积：select s.*, c.* from student s, classes c; 等值：select s.*, c.* from student s, classes c where s.cid = c.id; 非等值：select s.*, c.* from student s, classes c where s.cid <> c.id; select s.*, c.name classes from classes c, student s where c.id = s.classes_id ands.name is not null; SQL99连接查询 1、交叉连接cross join，类似于SQL92的笛卡尔积查询，无需条件。如： select s.*, c.name from student s cross join classes c; 2、自然连接 natural join查询，无需条件，默认条件是将2个table中的相同字段作为连接条件，如果没有相同字段，查询的结果就是空。 select s.*, c.name from student s natural join classes c; 3、using子句连接查询：using的子句可以是一列或多列，显示的指定两个表中同名列作为连接条件。如果用natural join的连接查询，会把所有的相同字段作为连接查询。而using可以指定相同列及个数。 select s.*, c.name from student s join classes c using(id); 4、 join … on连接查询，查询条件在on中完成，每个on语句只能指定一个条件。 select s.*, c.name from student s join classes c on s.classes_id = c.id; 5、左右外连接：3种外连接，left [outer] join、right [outer] join，连接条件都是通过用on子句来指定，条件可以等值、非等值。 select s.*, c.name from student s left join classes c on s.classes_id = c.id; select s.*, c.name from student s right join classes c on s.classes_id = c.id; 子查询子查询就是指在查询语句中嵌套另一个查询，子查询可以支持多层嵌套。子查询可以出现在2个位置： from关键字之后，被当做一个表来进行查询，这种用法被称为行内视图，因为该子查询的实质就是一个临时视图出现在where条件之后作为过滤条件的值子查询注意点：子查询用括号括起来，特别情况下需要起一个临时名称子查询当做临时表时（在from之后的子查询），可以为该子查询起别名，尤其是要作为前缀来限定数据列名时子查询用作过滤条件时，将子查询放在比较运算符的右边，提供可读性子查询作为过滤条件时，单行子查询使用单行运算符，多行子查询用多行运算符将from后面的子查询当做一个table来用： select * from (select id, name from classes) s where s.id in (1, 2); 当做条件来用： select * from student s where s.classes_id in (select id from classes); select * from student s where s.classes_id = any (select id from classes); select * from student s where s.classes_id > any (select id from classes); Ø 操作符和函数 1、 boolean只判断 select 1 is true, 0 is false, null is unknown; select 1 is not unknown, 0 is not unknown, null is not unknown; 2、 coalesce函数，返回第一个非null的值 select coalesce(null, 1); select coalesce(1, 1); select coalesce(null, 1); select coalesce(null, null); 3、当有2个或多个参数时，返回最大的那个参数值 select greatest(2, 3); select greatest(2, 3, 1, 9, 55, 23); select greatest('D', 'A', 'B'); 4、 Least函数，返回最小值，如果有null就返回null值 select least(2, 0); select least(2, 0, null); select least(2, 10, 22.2, 35.1, 1.1); 5、控制流函数 select case 1 when 1 then 'is 1' when 2 then 'is 2' else 'none' end; select case when 1 > 2 then 'yes' else 'no' end; 6、 ascii字符串函数 select ascii('A'); select ascii('1'); 7、二进制函数 select bin(22); 8、返回二进制字符串长度 select bit_length(11); 9、 char将值转换成字符，小数取整四舍五入 select char(65); select char(65.4); select char(65.5); select char(65.6); select char(65, 66, 67.4, 68.5, 69.6, '55.5', '97.3'); 10、 using改变字符集 select charset(char(0*65)), charset(char(0*65 using utf8)); 11、得到字符长度char_length,character_length select char_length('abc'); select character_length('eft'); 12、 compress压缩字符串、uncompress解压缩 select compress('abcedf'); select uncompress(compress('abcedf')); 13、 concat_ws分隔字符串 select concat_ws('#', 'first', 'second', 'last'); select concat_ws('#', 'first', 'second', null, 'last'); Ø 事务处理动作开始事务：start transaction 提交事务：commit 回滚事务：rollback 设置自动提交：set autocommit 1 | 0 atuoCommit系统默认是1立即提交模式；如果要手动控制事务，需要设置set autoCommit 0; 这样我们就可以用commit、rollback来控制事务了。在一段语句块中禁用autocommit 而不是set autocommit start transaction; select @result := avg(age) from temp; update temp set age = @result where id = 2; select * from temp where id = 2;//值被改变 rollback;//回滚 select * from temp where id = 2;//变回来了在此期间只有遇到commit、rollback，start Transaction的禁用autocommit才会结束。然后就恢复到原来的autocommit模式；不能回滚的语句有些语句不能被回滚。通常，这些语句包括数据定义语言（DDL）语句，比如创建或取消数据库的语句，和创建、取消或更改表或存储的子程序的语句。您在设计事务时，不应包含这类语句。如果您在事务的前部中发布了一个不能被回滚的语句，则后部的其它语句会发生错误，在这些情况下，通过发布ROLLBACK语句不能回滚事务的全部效果。一些操作也会隐式的提交事务如alter、create、drop、rename table、lock table、set autocommit、starttransaction、truncate table 等等, 在事务中出现这些语句也会提交事务的事务不能嵌套事务事务的保存点 Savepoint pointName/Rollback to savepoint pointName 一个事务可以设置多个保存点，rollback可以回滚到指定的保存点，恢复保存点后面的操作。如果有后面的保存点和前面的同名，则删除前面的保存点。 Release savepoint会删除一个保存点，如果在一段事务中执行commit或rollback，则事务结束，所以保存点删除。 Set Transaction设计数据库隔离级别 SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } 本语句用于设置事务隔离等级，用于下一个事务，或者用于当前会话。在默认情况下，SET TRANSACTION会为下一个事务（还未开始）设置隔离等级。如果您使用GLOBAL关键词，则语句会设置全局性的默认事务等级，用于从该点以后创建的所有新连接。原有的连接不受影响。使用SESSION关键测可以设置默认事务等级，用于对当前连接执行的所有将来事务。默认的等级是REPEATABLE READ全局隔离等级。 Ø 注释 select 1+1; # 单行注释 select 1+1; -- 单行注释 select 1 /* 多行注释 */ + 1; Ø 基本数据类型操作字符串 select 'hello', '"hello"', '""hello""', 'hel''lo', '/'hello'; select "hello", "'hello'", "''hello''", "hel""lo", "/"hello"; /n换行 select 'This/nIs/nFour/nLines'; /转义 select 'hello / world!'; select 'hello /world!'; select 'hello // world!'; select 'hello /' world!'; Ø 设置数据库mode模式 SET sql_mode='ANSI_QUOTES'; create table t(a int); create table "tt"(a int); create table "t""t"(a int); craate talbe tab("a""b" int); Ø 用户变量 set @num1 = 0, @num2 = 2, @result = 0; select @result := (@num1 := 5) + @num2 := 3, @num1, @num2, @result; Ø 存储过程创建存储过程： delimiter // create procedure get(out result int) begin select max(age) into result from temp; end// 调用存储过程： call get(@temp); 查询结果： select @temp; 删除存储过程： drop procedure get; 查看存储过程创建语句： show create procedure get; select…into 可以完成单行记录的赋值： create procedure getRecord(sid int) begin declare v_name varchar(20) default 'jason'; declare v_age int; declare v_sex bit; select name, age, sex into v_name, v_age, v_sex from temp where id = sid; select v_name, v_age, v_sex; end; call getRecord(1); Ø 函数函数类似于存储过程，只是调用方式不同例如：select max(age) from temp; 创建函数： create function addAge(age int) returns int return age + 5; 使用函数： select addAge(age) from temp; 删除函数： drop function if exists addAge; drop function addAge; 显示创建语法： show create function addAge; Ø 游标声明游标：declare cur_Name cursor for select name from temp; 打开游标：open cur_Name; Fetch游标：fetch cur_Name into @temp; 关闭游标：close cur_Name; 示例： CREATE PROCEDURE cur_show() BEGIN DECLARE done INT DEFAULT 0; DECLARE v_id, v_age INT; DECLARE v_name varchar(20); DECLARE cur_temp CURSOR FOR SELECT id, name, age FROM temp; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur_temp; REPEAT FETCH cur_temp INTO v_id, v_name, v_age; IF NOT done THEN IF isnull(v_name) THEN update temp set name = concat('test-json', v_id) where id = v_id; ELSEIF isnull(v_age) THEN update temp set age = 22 where id = v_id; END IF; END IF; UNTIL done END REPEAT; CLOSE cur_temp; END Ø 触发器触发器分为insert、update、delete三种触发器事件类型还有after、before触发时间创建触发器： create trigger trg_temp_ins before insert on temp for each row begin insert into temp_log values(NEW.id, NEW.name); end// 删除触发器： drop trigger trg_temp_ins

2,723

社区成员

29,110

社区内容

发帖

与我相关

我的任务

社区管理员

加入社区

近7日
近30日
至今

加载中

查看更多榜单

社区公告

暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章