SQL

最新推荐文章于 2024-08-26 13:11:57 发布

弗里曼的小伙伴

最新推荐文章于 2024-08-26 13:11:57 发布

阅读量733

点赞数

分类专栏： DataBase 文章标签：数据库 SQL MYSQL 事务 select

本文链接：https://blog.csdn.net/sf2gis2/article/details/50998426

版权

DataBase 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

SQL

sf2gis@163.com

2015年8月26日

2015年11月19日添加登录远程数据库

2015年12月1日添加备份和恢复数据库

1 目标：控制数据库、操作数据。

一些数据库厂商认为数据库不属于SQL的范围，因此单独定义了一些控制数据库的命令。如postgresql（参见：postgresql\postgresql.docx）。

2 原理：简单的二维表控制语言。

3 方法：控制数据库，创建、删除、备份等。

数据库的操作通过客户端控制服务器完成。不同的数据库具有不同的服务器和客户端。以MySQL为例。其客户端为mysql程序，服务器为mysqld服务。详情参阅help说明。

3.1 登陆本地数据库：mysql–u 用户名 –p

输入密码后进入服务器管理。

3.2 登陆远程数据库：mysql–h 远程IP –u 用户名 –p密码

参考：http://www.cnblogs.com/muzongyan/archive/2010/08/13/1799016.html

mysql -h192.168.1.100 -u cs -p123456

3.3 查看数据库：showdatabases;

3.4 设置当前数据库：use数据库名;

3.5 查看当前数据库：selectdatabase();

3.6 查看数据库服务器版本等信息：select version（）。

3.7 创建数据库：createdatabase 数据库名

3.8 删除数据库：dropdatebase 库名

3.9 备份数据库：mysqldump–u 用户名 –p 数据库 > sql文件.sql

参考：http://www.cnblogs.com/yuwensong/p/3955834.html

mysqldump -u root-p testpd > mysql-re.sql

//mysql-re.sql

-- MySQL dump10.13 Distrib 5.1.73, forredhat-linux-gnu (x86_64)

-- Host:localhost Database: testpd

--------------------------------------------------------

-- Server version 5.1.73

/*!40101 SET@OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET@OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET@OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMESutf8 */;

/*!40103 SET@OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SETTIME_ZONE='+00:00' */;

/*!40014 SET@OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET@OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET@OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET@OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

-- Table structurefor table `major`

DROP TABLE IFEXISTS `major`;

/*!40101 SET@saved_cs_client =@@character_set_client */;

/*!40101 SETcharacter_set_client = utf8 */;

CREATE TABLE`major` (

`MajorID` int(11) NOT NULL AUTO_INCREMENT,

`Name` varbinary(20) NOT NULL,

`Remark` varbinary(120) DEFAULT NULL,

PRIMARY KEY (`MajorID`)

) ENGINE=MyISAMAUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client= @saved_cs_client */;

-- Dumping datafor table `major`

LOCK TABLES`major` WRITE;

/*!40000 ALTERTABLE `major` DISABLE KEYS */;

INSERT INTO`major` VALUES (1,'xx','remark xx'),(2,'xx2','remark xx2');

/*!40000 ALTERTABLE `major` ENABLE KEYS */;

UNLOCK TABLES;

-- Table structurefor table `student`

DROP TABLE IFEXISTS `student`;

/*!40101 SET@saved_cs_client =@@character_set_client */;

/*!40101 SETcharacter_set_client = utf8 */;

CREATE TABLE`student` (

`StudentID` int(11) NOT NULL AUTO_INCREMENT,

`MajorID` int(11) DEFAULT NULL,

`Name` varbinary(20) NOT NULL,

`Sex` bit(1) NOT NULL,

`Age` int(11) DEFAULT NULL,

`Remark` varbinary(120) DEFAULT NULL,

PRIMARY KEY (`StudentID`),

KEY `FK_Reference_1` (`MajorID`)

) ENGINE=MyISAMDEFAULT CHARSET=latin1 COMMENT='this is student table';

/*!40101 SETcharacter_set_client = @saved_cs_client */;

-- Dumping datafor table `student`

LOCK TABLES`student` WRITE;

/*!40000 ALTERTABLE `student` DISABLE KEYS */;

/*!40000 ALTERTABLE `student` ENABLE KEYS */;

UNLOCK TABLES;

/*!40103 SETTIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SETSQL_MODE=@OLD_SQL_MODE */;

/*!40014 SETFOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SETUNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SETCHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SETCHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SETCOLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SETSQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completedon 2015-12-01 14:28:49

3.10 恢复数据库：sourcemysql.sql

Windows:创建数据库，然后进入数据库后，使用source加载sql脚本文件。

Linux：直接使用source加载sql脚本文件。

source/home/sf/test/database/stu_maj_mysql.sql

参考：http://www.cnblogs.com/kenkofox/archive/2011/01/14/1935422.html

3.11 创建用户：grantall privileges on . to ‘用户名’@‘连接类型’identified by ‘密码’ with grantoption

3.12 删除用户：dropuser 用户名。

3.13 初始化密码：mysqladmin–u 用户名 password ‘新密码’

3.14 修改密码：set password for ‘用户名’@’连接类型’=password(‘新密码’);

参考：html://jingyan.baidu.com/m/article/0320e2c198ad5f1b87507bc8.html

4 方法：操作数据，数据定义（DCL:数据表的CRUD）、数据操作（DML：CRUD）、数据控制（DCL：事务等）。

4.1 数据定义语言:DDL

用来控制数据库的结构定义;

createuser –U username –P newuser

4.1.1创建表:createtable 表名（列名类型（长度），…）

4.1.2查看所有表：showtables

4.1.3查看表结构:describe表名。

4.1.4填充表：insert，load

4.1.4.1 从本地文本批量插入：load data local infile ‘路径’ into table 表名。

值之间使用tab分隔。

注意：如果在windows上使用，则需要使用\r\n作为结束符（linux使用默认的\r）。

lines terminated by ‘\r\n’。

postgresql中使用如下方法：

\copy tablename from ‘filepath’ using delimiters ‘seprator’ with NULL as ‘nullString’

4.1.4.2 单条插入：insert into 表名【(列名，列名)】 values(列值，列值……)

注意:

1. 使用单引号包围字符串;

2. \转义一些特殊字符;

3. 空值为NULL,不带单引号;

4.1.4.3 自动增加的字段，使用明确的列名和对应值插入，省略自动增加字段。

mysql> insert intomajor(Name,Remark) values('xx','remark xx');

参考：

http://zhidao.baidu.com/link?url=UsBBiBfy6gGBz8cSqOcTYzZgJFUnNDpVtymLxJdFNrWgNhKnC7XAbKbLIgQ-ajxY3jq_mEi8lrRLYz51TWih1K

4.1.4.4 从查询结果批量插入：insert into 表名【列名……】 select语句

注意:插入时,一般要设置一个中间过渡表,使其具有与目的表的列,并增加一个bool型的更新标志,只有正确的加入的数据才被插入原始表;然后将这些数据删除;

4.1.5复制表结构:createtable 新表 like 旧表

4.1.6复制表:复制表结构+批量查询插入。

4.1.7删除表: droptable 表名

4.1.8删除数据:条件删除，整表删除。

4.1.8.1 条件删除：delete from tablename where…;

delete from employee whereid=18;

4.1.8.2 整表删除：truncate:永久删除,不可恢复,高效;

truncate Table tabname;

4.1.9Serial字段控制

Serial这种序列字段在PgAdminIII中被定义为序列,以表名-列名-seq命名;

当前值currval(‘seq名称’);

生成下一个值nextval(‘seq名称’);

设置值setval(‘seq名称’,value);

4.1.10更新数据

update tabname setcolname=value where …;

注意:更新之前查看where的条件对应多少行数据;

4.1.11使用其它表的数据更新数据

update tabname setcolname=value from othertab where …;

4.2 数据操作语言：DML

数据的CRUD。

4.2.1重名命列：select 列名【as】新列名,… from tabName;

注意：新列名可以使用双引号或省略,不能使用单引号。

4.2.2排序：orderby 列名【DESC】

默认是升序，DESC表示降序。

可以使用多个列进行排序，按先后顺序进行分类排序。

4.2.3select between:域查询

select * fromtablename where colname between low and high;

对于字符串则以空白填充不足的字符数;

4.2.4模糊查询:like‘通配符’，regexp ‘正则表达式’

%:代表任意字符串;

_:代表单个任意字符;

4.2.5限制输出:limit

limit:限制数目;

offset:开始位置;

4.2.6null测试

使用 is或is not;普通比较使用=;

4.2.7时间日期操作

当前日期时间：now()。

当前日期：curdate()。

日期提取：year(),month(),dayofmonth()，right()。

4.2.8SQL92 Join ON新方法:只是where语句的分担模式

select * from tab1join tab2 on tab1.col1=tab2.col1;

4.2.9聚集函数

使用聚焦函数时,可以使用group by和having子句;

group by:分组;

having:目标列中的条件;

4.2.10子查询

对于返回单个值的select语句,可以与常量一样使用;

对于返回多个值的select语句,使用in进行范围设定;

4.2.11类型转换:cast(expressAs type)或者：：

参考：http://stackoverflow.com/questions/15537709/what-does-do-in-postgresql

http://www.postgresql.org/docs/current/static/sql-expressions.html

4.3 DCL:数据控制语言

用来进行数据库访问控制;

参考：http://bbs.csdn.net/topics/350175424/

http://www.jb51.net/article/32246.htm

http://www.th7.cn/db/mysql/201505/103989.shtml

http://blog.chinaunix.net/uid-20577907-id-462940.html

http://blog.csdn.net/wangpeng047/article/details/22882537

注意：MySQL默认引擎是MyISAM不支持事务（高性能），切换到innoDB才能够支持事务。

注意：MySQL默认是自动提交事务的，设置为手动提交才能支持回滚。

注意：Spring只对RuntimeException进行回滚。

查看当前表的详细信息：show create table 表名。

show create table employee;

设置表的引擎:alter table 表名 type 引擎名。

alter table employee typeinnoDB;

设置事务的提交方式：set autocommit=0/1;//0=手动，1=自动。

set autocommit=0;

5 方法：实用函数

6 方法：自定义扩展函数

7 附：mysql5.1帮助文件