JDBC常用类和接口及增删查修
来源：互联网 发布：linux vncviewer使用 编辑：程序博客网 时间：2024/06/07 02:32
JDBC及eclipse连接数据库单例设计模式
- 查找输出数据库中的一些信息
- 包含eclipse与MySQL进行连接连接前需要先用build path导入连接包
- 注册用户
- 登录程序
- select语句来检索数据
- insert语句来插入数据
- update语句更新表中内容
- delete语句用来删除数据
- 参考文档
JDBC及eclipse连接数据库
在创建表时CREATE TABLE student( id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,name varchar(30) binary NOT NULL,sex int(1),age int(3) )在varchar后面加入binary则对表中的大小写进行限制
去官网下载MySQ软件
软件的安装
首先安装install，然后安装MySQL-Frame界面
各种执行语句,对表的直接操作，重点了解：
<code class="language-java hljs has-numbering"><span class="hljs-comment">//创建一个数据库</span>CREATE DATABASE CLAZZ<span class="hljs-comment">//创建一个表，（数值）对位数进行限制</span>CREATE TABLE student( id <span class="hljs-keyword">int</span>(<span class="hljs-number">11</span>) NOT NULL PRIMARY KEY AUTO_INCREMENT,name varchar(<span class="hljs-number">30</span>) NOT NULL,sex <span class="hljs-keyword">int</span>(<span class="hljs-number">1</span>),age <span class="hljs-keyword">int</span>(<span class="hljs-number">3</span>) )<span class="hljs-comment">//插入语句</span>insert into student (name,sex,age)values(<span class="hljs-string">'张三'</span>,<span class="hljs-number">0</span>,<span class="hljs-number">0</span>)insert into student (name,sex,age)values(<span class="hljs-string">'李三'</span>,<span class="hljs-number">1</span>,<span class="hljs-number">23</span>)<span class="hljs-comment">//修改语句</span>update student set age=<span class="hljs-number">19</span>,sex=<span class="hljs-number">2</span> where name=<span class="hljs-string">'张三'</span><span class="hljs-comment">//查询语句</span>select name,age from student where age><span class="hljs-number">20</span>select * from student<span class="hljs-comment">//删除语句</span>delete from student where name=<span class="hljs-string">'李三'</span>select name,age from student </code><ul class="pre-numbering" style="display: block;"><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li><li>9</li><li>10</li><li>11</li><li>12</li><li>13</li><li>14</li><li>15</li></ul>
查找、输出数据库中的一些信息
<code class="language-java hljs has-numbering"><span class="hljs-keyword">package</span> shujuku;<span class="hljs-keyword">import</span> java.sql.Connection;<span class="hljs-keyword">import</span> java.sql.DriverManager;<span class="hljs-keyword">import</span> java.sql.SQLException;<span class="hljs-keyword">import</span> java.sql.*;<span class="hljs-keyword">import</span> java.util.Collection;<span class="hljs-keyword">public</span> <span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">Test</span> {</span> <span class="hljs-keyword">private</span> Statement state; <span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title">main</span>(String[] args) { <span class="hljs-comment">// 链接数据的驱动</span> String driver = <span class="hljs-string">"com.mysql.jdbc.Driver"</span>; String url = <span class="hljs-string">"jdbc:mysql://localhost:3306/clazz"</span>; String user = <span class="hljs-string">"root"</span>; <span class="hljs-comment">// Java连接MySQL</span> String password = <span class="hljs-string">"654321"</span>; <span class="hljs-keyword">try</span> { Class.forName(driver);<span class="hljs-comment">// 加载驱动</span> <span class="hljs-comment">// 与数据库建立连接</span> Connection conn = DriverManager.getConnection(url, user, password); <span class="hljs-keyword">if</span> (!conn.isClosed()) { Statement state = conn.createStatement(); String createTable = <span class="hljs-string">"create TABLE IF NOT EXISTS user(id int primary key not null auto_increment, user_name varchar(30) NOT NULL,user_password varchar(30) NOT NULL)"</span>; state.execute(createTable); <span class="hljs-comment">// String insert="insert into</span> <span class="hljs-comment">// student(stu_name,sex,age)values('赵六',1,10)";</span> <span class="hljs-comment">// String delete="delete from student where stu_name='张三'";</span> <span class="hljs-comment">// state.execute(insert);</span> <span class="hljs-comment">// state.execute(delete);</span> String select = <span class="hljs-string">"select * from student"</span>; ResultSet set = state.executeQuery(select); set.first();<span class="hljs-comment">// 先把游标移动到第一位</span> <span class="hljs-keyword">while</span> (!set.isAfterLast()) {<span class="hljs-comment">// 判断游标是否在最后一个的后边</span> String name = set.getString(<span class="hljs-string">"stu_name"</span>); System.out.println(name); set.next(); } System.out.println(<span class="hljs-string">"执行完成"</span>); } <span class="hljs-keyword">else</span> { System.out.println(<span class="hljs-string">"请打开数据库"</span>); } } <span class="hljs-keyword">catch</span> (ClassNotFoundException e) { <span class="hljs-comment">// TODO Auto-generated catch block</span> e.printStackTrace(); } <span class="hljs-keyword">catch</span> (SQLException e) { <span class="hljs-comment">// TODO Auto-generated catch block</span> e.printStackTrace(); } }}</code><ul class="pre-numbering" style="display: block;"><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li><li>9</li><li>10</li><li>11</li><li>12</li><li>13</li><li>14</li><li>15</li><li>16</li><li>17</li><li>18</li><li>19</li><li>20</li><li>21</li><li>22</li><li>23</li><li>24</li><li>25</li><li>26</li><li>27</li><li>28</li><li>29</li><li>30</li><li>31</li><li>32</li><li>33</li><li>34</li><li>35</li><li>36</li><li>37</li><li>38</li><li>39</li><li>40</li><li>41</li><li>42</li><li>43</li><li>44</li><li>45</li><li>46</li><li>47</li><li>48</li><li>49</li><li>50</li><li>51</li><li>52</li><li>53</li><li>54</li><li>55</li></ul>单例设计模式
1、私有构造器
2、静态公开的方法
3、私有静态的属性
4、方法加线程锁
包含eclipse与MySQL进行连接，连接前需要先用build path导入连接包
<code class="language-java hljs has-numbering"><span class="hljs-keyword">import</span> java.sql.Connection;<span class="hljs-keyword">import</span> java.sql.DriverManager;<span class="hljs-keyword">import</span> java.sql.Statement;<span class="hljs-comment">//单例设计模式</span><span class="hljs-keyword">public</span> <span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">MysqlManager</span> {</span> <span class="hljs-comment">//私有的属性，传入构造器中</span> <span class="hljs-keyword">private</span> Statement statement; <span class="hljs-comment">//在本类中创建本类的对象</span> <span class="hljs-keyword">private</span> <span class="hljs-keyword">static</span> MysqlManager manager; <span class="hljs-comment">//public static synchronized MysqlManager newInstance(){当多线程调用时，加synchronized来控制避免多线程同时调用</span> <span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> MysqlManager <span class="hljs-title">newInstance</span>(){ <span class="hljs-keyword">if</span>(manager==<span class="hljs-keyword">null</span>){ <span class="hljs-comment">//如果没有创建对象，则创建一个，如果已经创建了就直接返回，保证只有一个类的实例</span> manager=<span class="hljs-keyword">new</span> MysqlManager(); } <span class="hljs-comment">//返回本实例的一个对象</span> <span class="hljs-keyword">return</span> manager; } <span class="hljs-comment">//创建私有的构造方法，只允许本类调用</span> <span class="hljs-keyword">private</span> <span class="hljs-title">MysqlManager</span>() { <span class="hljs-comment">//链接数据库的驱动</span> String driver=<span class="hljs-string">"com.mysql.jdbc.Driver"</span>; <span class="hljs-comment">//URL指向要访问的数据库名</span> String url=<span class="hljs-string">"jdbc:mysql://localhost:3306/clazz"</span>; <span class="hljs-comment">//MySQL配置时的用户名</span> String user=<span class="hljs-string">"root"</span>; <span class="hljs-comment">//Java连接MySQL配置时密码</span> String password=<span class="hljs-string">"654321"</span>; <span class="hljs-keyword">try</span> { Class.forName(driver); <span class="hljs-comment">// 加载驱动</span> Connection connect=DriverManager.getConnection(url, user,password);<span class="hljs-comment">//与数据库建立连接</span> <span class="hljs-keyword">if</span> (!connect.isClosed()) { statement = connect.createStatement(); <span class="hljs-comment">//创建新的table</span> String creatTable = <span class="hljs-string">"create TABLE if not exists user(id int primary key not null auto_increment,user_name varchar(20) binary NOT NULL,user_word varchar(30) NOT NULL)"</span>; <span class="hljs-comment">//执行得到一个新表</span> statement.execute(creatTable); System.out.println(<span class="hljs-string">"Success connect Mysql server!"</span>); } <span class="hljs-keyword">else</span> { System.out.println(<span class="hljs-string">"请与数据库建立连接"</span>); } } <span class="hljs-keyword">catch</span> (Exception e) { System.out.print(<span class="hljs-string">"get data error!"</span>); e.printStackTrace(); } } <span class="hljs-keyword">public</span> Statement <span class="hljs-title">getStatement</span>() { <span class="hljs-keyword">return</span> statement; } <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setStatement</span>(Statement statement) { <span class="hljs-keyword">this</span>.statement = statement; }}</code><ul class="pre-numbering" style="display: block;"><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li><li>9</li><li>10</li><li>11</li><li>12</li><li>13</li><li>14</li><li>15</li><li>16</li><li>17</li><li>18</li><li>19</li><li>20</li><li>21</li><li>22</li><li>23</li><li>24</li><li>25</li><li>26</li><li>27</li><li>28</li><li>29</li><li>30</li><li>31</li><li>32</li><li>33</li><li>34</li><li>35</li><li>36</li><li>37</li><li>38</li><li>39</li><li>40</li><li>41</li><li>42</li><li>43</li><li>44</li><li>45</li><li>46</li><li>47</li><li>48</li><li>49</li><li>50</li><li>51</li><li>52</li><li>53</li><li>54</li></ul>注册用户
<code class="language-java hljs has-numbering"><span class="hljs-keyword">package</span> com.jdbc.test;<span class="hljs-keyword">import</span> java.awt.BorderLayout;<span class="hljs-keyword">import</span> java.awt.EventQueue;<span class="hljs-keyword">import</span> javax.swing.JFrame;<span class="hljs-keyword">import</span> javax.swing.JPanel;<span class="hljs-keyword">import</span> javax.swing.border.EmptyBorder;<span class="hljs-keyword">import</span> javax.swing.JTextField;<span class="hljs-keyword">import</span> javax.swing.JButton;<span class="hljs-keyword">import</span> javax.swing.JLabel;<span class="hljs-keyword">import</span> java.awt.event.ActionListener;<span class="hljs-keyword">import</span> java.sql.ResultSet;<span class="hljs-keyword">import</span> java.sql.SQLException;<span class="hljs-keyword">import</span> java.sql.Statement;<span class="hljs-keyword">import</span> java.awt.event.ActionEvent;<span class="hljs-keyword">public</span> <span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">register</span> <span class="hljs-keyword">extends</span> <span class="hljs-title">JFrame</span> {</span> <span class="hljs-keyword">private</span> JPanel contentPane; <span class="hljs-keyword">private</span> JTextField textFielduser; <span class="hljs-keyword">private</span> JTextField textFieldpass; <span class="hljs-javadoc">/** * Launch the application. */</span> <span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title">main</span>(String[] args) { EventQueue.invokeLater(<span class="hljs-keyword">new</span> Runnable() { <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">run</span>() { <span class="hljs-keyword">try</span> { register frame = <span class="hljs-keyword">new</span> register(); frame.setVisible(<span class="hljs-keyword">true</span>); } <span class="hljs-keyword">catch</span> (Exception e) { e.printStackTrace(); } } }); } <span class="hljs-javadoc">/** * Create the frame. */</span> <span class="hljs-keyword">public</span> <span class="hljs-title">register</span>() { setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); setBounds(<span class="hljs-number">100</span>, <span class="hljs-number">100</span>, <span class="hljs-number">614</span>, <span class="hljs-number">409</span>); contentPane = <span class="hljs-keyword">new</span> JPanel(); contentPane.setBorder(<span class="hljs-keyword">new</span> EmptyBorder(<span class="hljs-number">5</span>, <span class="hljs-number">5</span>, <span class="hljs-number">5</span>, <span class="hljs-number">5</span>)); setContentPane(contentPane); contentPane.setLayout(<span class="hljs-keyword">null</span>); textFielduser = <span class="hljs-keyword">new</span> JTextField(); textFielduser.setBounds(<span class="hljs-number">203</span>, <span class="hljs-number">85</span>, <span class="hljs-number">117</span>, <span class="hljs-number">52</span>); contentPane.add(textFielduser); textFielduser.setColumns(<span class="hljs-number">10</span>); textFieldpass = <span class="hljs-keyword">new</span> JTextField(); textFieldpass.setBounds(<span class="hljs-number">203</span>, <span class="hljs-number">165</span>, <span class="hljs-number">117</span>, <span class="hljs-number">47</span>); contentPane.add(textFieldpass); textFieldpass.setColumns(<span class="hljs-number">10</span>); JButton btnNewButton_2 = <span class="hljs-keyword">new</span> JButton(<span class="hljs-string">"New button"</span>); btnNewButton_2.addActionListener(<span class="hljs-keyword">new</span> ActionListener() { <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">actionPerformed</span>(ActionEvent arg0) { <span class="hljs-comment">//加入正则表达式对密码和用户名进行判断</span> String user=textFielduser.getText(); String password=textFieldpass.getText(); Statement state=MysqlManager.newInstance().getStatement(); String sql=<span class="hljs-string">"select count(*) from user where user_name='"</span>+user+<span class="hljs-string">"'"</span>; ResultSet set; <span class="hljs-keyword">try</span> { set = state.executeQuery(sql); set.first(); <span class="hljs-comment">//set.last();//从第一行查到最后一行</span> <span class="hljs-comment">//int num=set.getRow();另一种方法，如果含有查找内容，则返回所在行数，否则返回0."查看api查看几个方法的具体介绍"</span> <span class="hljs-keyword">int</span> num=set.getInt(<span class="hljs-number">1</span>); <span class="hljs-keyword">if</span>(num><span class="hljs-number">0</span>){ System.out.println(<span class="hljs-string">"该用户已存在"</span>); }<span class="hljs-keyword">else</span>{ String register=<span class="hljs-string">"insert into user (user_name,user_word)values("</span>+user+<span class="hljs-string">","</span>+password+<span class="hljs-string">")"</span>; state.execute(register); } } <span class="hljs-keyword">catch</span> (SQLException e) { <span class="hljs-comment">// TODO Auto-generated catch block</span> e.printStackTrace(); } } }); btnNewButton_2.setBounds(<span class="hljs-number">202</span>, <span class="hljs-number">257</span>, <span class="hljs-number">93</span>, <span class="hljs-number">23</span>); contentPane.add(btnNewButton_2); JLabel label = <span class="hljs-keyword">new</span> JLabel(<span class="hljs-string">"\u5BC6\u7801"</span>); label.setBounds(<span class="hljs-number">105</span>, <span class="hljs-number">194</span>, <span class="hljs-number">54</span>, <span class="hljs-number">15</span>); contentPane.add(label); JLabel label_1 = <span class="hljs-keyword">new</span> JLabel(<span class="hljs-string">"\u7528\u6237\u540D"</span>); label_1.setBounds(<span class="hljs-number">105</span>, <span class="hljs-number">88</span>, <span class="hljs-number">54</span>, <span class="hljs-number">15</span>); contentPane.add(label_1); }}</code><ul class="pre-numbering" style="display: block;"><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li><li>9</li><li>10</li><li>11</li><li>12</li><li>13</li><li>14</li><li>15</li><li>16</li><li>17</li><li>18</li><li>19</li><li>20</li><li>21</li><li>22</li><li>23</li><li>24</li><li>25</li><li>26</li><li>27</li><li>28</li><li>29</li><li>30</li><li>31</li><li>32</li><li>33</li><li>34</li><li>35</li><li>36</li><li>37</li><li>38</li><li>39</li><li>40</li><li>41</li><li>42</li><li>43</li><li>44</li><li>45</li><li>46</li><li>47</li><li>48</li><li>49</li><li>50</li><li>51</li><li>52</li><li>53</li><li>54</li><li>55</li><li>56</li><li>57</li><li>58</li><li>59</li><li>60</li><li>61</li><li>62</li><li>63</li><li>64</li><li>65</li><li>66</li><li>67</li><li>68</li><li>69</li><li>70</li><li>71</li><li>72</li><li>73</li><li>74</li><li>75</li><li>76</li><li>77</li><li>78</li><li>79</li><li>80</li><li>81</li><li>82</li><li>83</li><li>84</li><li>85</li><li>86</li><li>87</li><li>88</li><li>89</li><li>90</li><li>91</li><li>92</li><li>93</li><li>94</li><li>95</li><li>96</li><li>97</li><li>98</li><li>99</li><li>100</li><li>101</li></ul>登录程序
<code class="language-java hljs has-numbering"><span class="hljs-keyword">package</span> com.jdbc.test;<span class="hljs-keyword">import</span> java.awt.BorderLayout;<span class="hljs-keyword">import</span> java.awt.EventQueue;<span class="hljs-keyword">import</span> javax.swing.JFrame;<span class="hljs-keyword">import</span> javax.swing.JPanel;<span class="hljs-keyword">import</span> javax.swing.border.EmptyBorder;<span class="hljs-keyword">import</span> javax.swing.JTextField;<span class="hljs-keyword">import</span> javax.swing.JButton;<span class="hljs-keyword">import</span> java.awt.event.ActionListener;<span class="hljs-keyword">import</span> java.sql.ResultSet;<span class="hljs-keyword">import</span> java.sql.SQLException;<span class="hljs-keyword">import</span> java.sql.Statement;<span class="hljs-keyword">import</span> java.awt.event.ActionEvent;<span class="hljs-keyword">public</span> <span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">Login</span> <span class="hljs-keyword">extends</span> <span class="hljs-title">JFrame</span> {</span> <span class="hljs-keyword">private</span> JPanel contentPane; <span class="hljs-keyword">private</span> JTextField textFielduser; <span class="hljs-keyword">private</span> JTextField textFieldpass; <span class="hljs-javadoc">/** * Launch the application. */</span> <span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title">main</span>(String[] args) { EventQueue.invokeLater(<span class="hljs-keyword">new</span> Runnable() { <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">run</span>() { <span class="hljs-keyword">try</span> { Login frame = <span class="hljs-keyword">new</span> Login(); frame.setVisible(<span class="hljs-keyword">true</span>); } <span class="hljs-keyword">catch</span> (Exception e) { e.printStackTrace(); } } }); } <span class="hljs-javadoc">/** * Create the frame. */</span> <span class="hljs-keyword">public</span> <span class="hljs-title">Login</span>() { setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); setBounds(<span class="hljs-number">100</span>, <span class="hljs-number">100</span>, <span class="hljs-number">450</span>, <span class="hljs-number">300</span>); contentPane = <span class="hljs-keyword">new</span> JPanel(); contentPane.setBorder(<span class="hljs-keyword">new</span> EmptyBorder(<span class="hljs-number">5</span>, <span class="hljs-number">5</span>, <span class="hljs-number">5</span>, <span class="hljs-number">5</span>)); setContentPane(contentPane); contentPane.setLayout(<span class="hljs-keyword">null</span>); textFielduser = <span class="hljs-keyword">new</span> JTextField(); textFielduser.setBounds(<span class="hljs-number">181</span>, <span class="hljs-number">59</span>, <span class="hljs-number">110</span>, <span class="hljs-number">43</span>); contentPane.add(textFielduser); textFielduser.setColumns(<span class="hljs-number">10</span>); textFieldpass = <span class="hljs-keyword">new</span> JTextField(); textFieldpass.setBounds(<span class="hljs-number">181</span>, <span class="hljs-number">134</span>, <span class="hljs-number">110</span>, <span class="hljs-number">43</span>); contentPane.add(textFieldpass); textFieldpass.setColumns(<span class="hljs-number">10</span>); JButton btnNewButton = <span class="hljs-keyword">new</span> JButton(<span class="hljs-string">"New button"</span>); btnNewButton.addActionListener(<span class="hljs-keyword">new</span> ActionListener() { <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">actionPerformed</span>(ActionEvent arg0) { String name=textFielduser.getText(); String pass=textFieldpass.getText(); Statement state=MysqlManager.newInstance().getStatement(); <span class="hljs-comment">//加入binary对大小写进行限制</span> String select=<span class="hljs-string">"select * from user where user_name='"</span>+name+<span class="hljs-string">"'and user_word='"</span>+pass+<span class="hljs-string">"'"</span>; <span class="hljs-keyword">try</span> { ResultSet set=state.executeQuery(select); set.last(); <span class="hljs-keyword">int</span> num=set.getRow(); System.out.println(num); } <span class="hljs-keyword">catch</span> (SQLException e) { <span class="hljs-comment">// TODO Auto-generated catch block</span> e.printStackTrace(); } } }); btnNewButton.setBounds(<span class="hljs-number">181</span>, <span class="hljs-number">187</span>, <span class="hljs-number">93</span>, <span class="hljs-number">23</span>); contentPane.add(btnNewButton); }}</code><ul class="pre-numbering" style="display: block;"><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li><li>9</li><li>10</li><li>11</li><li>12</li><li>13</li><li>14</li><li>15</li><li>16</li><li>17</li><li>18</li><li>19</li><li>20</li><li>21</li><li>22</li><li>23</li><li>24</li><li>25</li><li>26</li><li>27</li><li>28</li><li>29</li><li>30</li><li>31</li><li>32</li><li>33</li><li>34</li><li>35</li><li>36</li><li>37</li><li>38</li><li>39</li><li>40</li><li>41</li><li>42</li><li>43</li><li>44</li><li>45</li><li>46</li><li>47</li><li>48</li><li>49</li><li>50</li><li>51</li><li>52</li><li>53</li><li>54</li><li>55</li><li>56</li><li>57</li><li>58</li><li>59</li><li>60</li><li>61</li><li>62</li><li>63</li><li>64</li><li>65</li><li>66</li><li>67</li><li>68</li><li>69</li><li>70</li><li>71</li><li>72</li><li>73</li><li>74</li><li>75</li><li>76</li><li>77</li><li>78</li><li>79</li><li>80</li><li>81</li><li>82</li><li>83</li><li>84</li><li>85</li></ul>1.select语句来检索数据
<code class="language-java hljs has-numbering">select 所选字段列表 from 数据表名 where 条件表达式 group by 字段名 having 条件表达式（指定分组条件）order by 字段名[ASC|DESC]例子： select name,age from tb_emp where sex=<span class="hljs-string">'女'</span> order by age; </code><ul class="pre-numbering" style="display: block;"><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li></ul>
2.insert语句来插入数据
<code class="language-java hljs has-numbering">inser into 表名[(字段名<span class="hljs-number">1</span>，字段名<span class="hljs-number">2</span>)]value(属性值<span class="hljs-number">1</span>，属性名<span class="hljs-number">2</span>)例子： inser into tb_emp(<span class="hljs-number">2</span>,<span class="hljs-string">'lili'</span>,<span class="hljs-string">'女'</span>,<span class="hljs-string">'销售部'</span>);</code><ul class="pre-numbering" style="display: block;"><li>1</li><li>2</li><li>3</li><li>4</li></ul>
3.update语句更新表中内容
<code class="language-java hljs has-numbering">update 数据表名 set 字段名=新的字段名 where 条件表达式例子： update tb_emp set age=<span class="hljs-number">24</span> where id=<span class="hljs-number">2</span>;</code><ul class="pre-numbering" style="display: block;"><li>1</li><li>2</li><li>3</li></ul>
4.delete语句用来删除数据
<code class="language-java hljs has-numbering">delete from 数据表名 where 条件表达式例子： delete from tb_emp where id=<span class="hljs-number">1024</span>;</code><ul class="pre-numbering" style="display: block;"><li>1</li><li>2</li><li>3</li></ul>
参考文档
<code class="language-mysql hljs sql has-numbering">1、说明：创建数据库<span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">DATABASE</span> <span class="hljs-keyword">database</span>-name<span class="hljs-number">2</span>、说明：删除数据库<span class="hljs-keyword">drop</span> <span class="hljs-keyword">database</span> dbname<span class="hljs-number">3</span>、说明：备份<span class="hljs-keyword">sql</span> server--- 创建 备份数据的 deviceUSE master<span class="hljs-keyword">EXEC</span> sp_addumpdevice <span class="hljs-string">'disk'</span>, <span class="hljs-string">'testBack'</span>, <span class="hljs-string">'c:\mssql7backup\MyNwind_1.dat'</span>--- 开始 备份BACKUP <span class="hljs-keyword">DATABASE</span> pubs <span class="hljs-keyword">TO</span> testBack<span class="hljs-number">4</span>、说明：创建新表<span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> tabname(col1 type1 [<span class="hljs-keyword">not</span> <span class="hljs-keyword">null</span>] [<span class="hljs-keyword">primary</span> <span class="hljs-keyword">key</span>],col2 type2 [<span class="hljs-keyword">not</span> <span class="hljs-keyword">null</span>],..)根据已有的表创建新表：A：<span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> tab_new <span class="hljs-keyword">like</span> tab_old (使用旧表创建新表)B：<span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> tab_new <span class="hljs-keyword">as</span> <span class="hljs-keyword">select</span> col1,col2… <span class="hljs-keyword">from</span> tab_old definition <span class="hljs-keyword">only</span><span class="hljs-number">5</span>、说明：删除新表<span class="hljs-keyword">drop</span> <span class="hljs-keyword">table</span> tabname<span class="hljs-number">6</span>、说明：增加一个列<span class="hljs-keyword">Alter</span> <span class="hljs-keyword">table</span> tabname <span class="hljs-keyword">add</span> <span class="hljs-keyword">column</span> col type注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加<span class="hljs-keyword">varchar</span>类型的长度。<span class="hljs-number">7</span>、说明：添加主键： <span class="hljs-keyword">Alter</span> <span class="hljs-keyword">table</span> tabname <span class="hljs-keyword">add</span> <span class="hljs-keyword">primary</span> <span class="hljs-keyword">key</span>(col)说明：删除主键： <span class="hljs-keyword">Alter</span> <span class="hljs-keyword">table</span> tabname <span class="hljs-keyword">drop</span> <span class="hljs-keyword">primary</span> <span class="hljs-keyword">key</span>(col)<span class="hljs-number">8</span>、说明：创建索引：<span class="hljs-keyword">create</span> [<span class="hljs-keyword">unique</span>] index idxname <span class="hljs-keyword">on</span> tabname(col….)删除索引：<span class="hljs-keyword">drop</span> index idxname注：索引是不可更改的，想更改必须删除重新建。<span class="hljs-number">9</span>、说明：创建视图：<span class="hljs-keyword">create</span> <span class="hljs-keyword">view</span> viewname <span class="hljs-keyword">as</span> <span class="hljs-keyword">select</span> statement删除视图：<span class="hljs-keyword">drop</span> <span class="hljs-keyword">view</span> viewname<span class="hljs-number">10</span>、说明：几个简单的基本的<span class="hljs-keyword">sql</span>语句选择：<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> table1 <span class="hljs-keyword">where</span> 范围插入：<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> table1(field1,field2) <span class="hljs-keyword">values</span>(value1,value2)删除：<span class="hljs-keyword">delete</span> <span class="hljs-keyword">from</span> table1 <span class="hljs-keyword">where</span> 范围更新：<span class="hljs-keyword">update</span> table1 <span class="hljs-keyword">set</span> field1=value1 <span class="hljs-keyword">where</span> 范围查找：<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> table1 <span class="hljs-keyword">where</span> field1 <span class="hljs-keyword">like</span> ’%value1%’ ---<span class="hljs-keyword">like</span>的语法很精妙，查资料!排序：<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> table1 <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> field1,field2 [<span class="hljs-keyword">desc</span>]总数：<span class="hljs-keyword">select</span> <span class="hljs-aggregate">count</span> <span class="hljs-keyword">as</span> totalcount <span class="hljs-keyword">from</span> table1求和：<span class="hljs-keyword">select</span> <span class="hljs-aggregate">sum</span>(field1) <span class="hljs-keyword">as</span> sumvalue <span class="hljs-keyword">from</span> table1平均：<span class="hljs-keyword">select</span> <span class="hljs-aggregate">avg</span>(field1) <span class="hljs-keyword">as</span> avgvalue <span class="hljs-keyword">from</span> table1最大：<span class="hljs-keyword">select</span> <span class="hljs-aggregate">max</span>(field1) <span class="hljs-keyword">as</span> maxvalue <span class="hljs-keyword">from</span> table1最小：<span class="hljs-keyword">select</span> <span class="hljs-aggregate">min</span>(field1) <span class="hljs-keyword">as</span> minvalue <span class="hljs-keyword">from</span> table1<span class="hljs-number">11</span>、说明：几个高级查询运算词A： <span class="hljs-keyword">UNION</span> 运算符<span class="hljs-keyword">UNION</span> 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 <span class="hljs-keyword">ALL</span> 随 <span class="hljs-keyword">UNION</span> 一起使用时（即 <span class="hljs-keyword">UNION</span> <span class="hljs-keyword">ALL</span>），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。B： <span class="hljs-keyword">EXCEPT</span> 运算符<span class="hljs-keyword">EXCEPT</span> 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 <span class="hljs-keyword">ALL</span> 随 <span class="hljs-keyword">EXCEPT</span> 一起使用时 (<span class="hljs-keyword">EXCEPT</span> <span class="hljs-keyword">ALL</span>)，不消除重复行。C： <span class="hljs-keyword">INTERSECT</span> 运算符<span class="hljs-keyword">INTERSECT</span> 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 <span class="hljs-keyword">ALL</span> 随 <span class="hljs-keyword">INTERSECT</span> 一起使用时 (<span class="hljs-keyword">INTERSECT</span> <span class="hljs-keyword">ALL</span>)，不消除重复行。注：使用运算词的几个查询结果行必须是一致的。<span class="hljs-number">12</span>、说明：使用外连接A、<span class="hljs-keyword">left</span> （<span class="hljs-keyword">outer</span>） <span class="hljs-keyword">join</span>：左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。<span class="hljs-keyword">SQL</span>: <span class="hljs-keyword">select</span> a.a, a.b, a.c, b.c, b.d, b.f <span class="hljs-keyword">from</span> a <span class="hljs-keyword">LEFT</span> OUT <span class="hljs-keyword">JOIN</span> b <span class="hljs-keyword">ON</span> a.a = b.cB：<span class="hljs-keyword">right</span> （<span class="hljs-keyword">outer</span>） <span class="hljs-keyword">join</span>:右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。C：<span class="hljs-keyword">full</span>/<span class="hljs-keyword">cross</span> （<span class="hljs-keyword">outer</span>） <span class="hljs-keyword">join</span>：全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。<span class="hljs-number">12</span>、分组:<span class="hljs-keyword">Group</span> <span class="hljs-keyword">by</span>: 一张表，一旦分组完成后，查询后只能得到组相关的信息。 组相关的信息：（统计信息） <span class="hljs-aggregate">count</span>,<span class="hljs-aggregate">sum</span>,<span class="hljs-aggregate">max</span>,<span class="hljs-aggregate">min</span>,<span class="hljs-aggregate">avg</span> 分组的标准) 在SQLServer中分组时：不能以text,ntext,image类型的字段作为分组依据 在selecte统计函数中的字段，不能和普通的字段放在一起；<span class="hljs-number">13</span>、对数据库进行操作： 分离数据库： sp_detach_db;</span> 附加数据库：sp_attach_db 后接表明，附加需要完整的路径名14.如何修改数据库的名称:sp_renamedb 'old_name', 'new_name'二、提升1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)法一：<span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">into</span> b <span class="hljs-keyword">from</span> a <span class="hljs-keyword">where</span> <span class="hljs-number">1</span><><span class="hljs-number">1</span>（仅用于SQlServer）法二：<span class="hljs-keyword">select</span> top <span class="hljs-number">0</span> * <span class="hljs-keyword">into</span> b <span class="hljs-keyword">from</span> a<span class="hljs-number">2</span>、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> b(a, b, c) <span class="hljs-keyword">select</span> d,e,f <span class="hljs-keyword">from</span> b;</span>3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)<span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> b(a, b, c) <span class="hljs-keyword">select</span> d,e,f <span class="hljs-keyword">from</span> b <span class="hljs-keyword">in</span> ‘具体数据库’ <span class="hljs-keyword">where</span> 条件例子：..<span class="hljs-keyword">from</span> b <span class="hljs-keyword">in</span> <span class="hljs-string">'"&Server.MapPath(".")&"\data.mdb" &"'</span> <span class="hljs-keyword">where</span>..<span class="hljs-number">4</span>、说明：子查询(表名<span class="hljs-number">1</span>：a 表名<span class="hljs-number">2</span>：b)<span class="hljs-keyword">select</span> a,b,c <span class="hljs-keyword">from</span> a <span class="hljs-keyword">where</span> a <span class="hljs-keyword">IN</span> (<span class="hljs-keyword">select</span> d <span class="hljs-keyword">from</span> b ) 或者: <span class="hljs-keyword">select</span> a,b,c <span class="hljs-keyword">from</span> a <span class="hljs-keyword">where</span> a <span class="hljs-keyword">IN</span> (<span class="hljs-number">1</span>,<span class="hljs-number">2</span>,<span class="hljs-number">3</span>)<span class="hljs-number">5</span>、说明：显示文章、提交人和最后回复时间<span class="hljs-keyword">select</span> a.title,a.username,b.adddate <span class="hljs-keyword">from</span> <span class="hljs-keyword">table</span> a,(<span class="hljs-keyword">select</span> <span class="hljs-aggregate">max</span>(adddate) adddate <span class="hljs-keyword">from</span> <span class="hljs-keyword">table</span> <span class="hljs-keyword">where</span> <span class="hljs-keyword">table</span>.title=a.title) b<span class="hljs-number">6</span>、说明：外连接查询(表名<span class="hljs-number">1</span>：a 表名<span class="hljs-number">2</span>：b)<span class="hljs-keyword">select</span> a.a, a.b, a.c, b.c, b.d, b.f <span class="hljs-keyword">from</span> a <span class="hljs-keyword">LEFT</span> OUT <span class="hljs-keyword">JOIN</span> b <span class="hljs-keyword">ON</span> a.a = b.c<span class="hljs-number">7</span>、说明：在线视图查询(表名<span class="hljs-number">1</span>：a )<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> (<span class="hljs-keyword">SELECT</span> a,b,c <span class="hljs-keyword">FROM</span> a) T <span class="hljs-keyword">where</span> t.a > <span class="hljs-number">1</span>;</span>8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括<span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> table1 <span class="hljs-keyword">where</span> <span class="hljs-keyword">time</span> between time1 <span class="hljs-keyword">and</span> time2<span class="hljs-keyword">select</span> a,b,c, <span class="hljs-keyword">from</span> table1 <span class="hljs-keyword">where</span> a <span class="hljs-keyword">not</span> between 数值<span class="hljs-number">1</span> <span class="hljs-keyword">and</span> 数值<span class="hljs-number">2</span><span class="hljs-number">9</span>、说明：<span class="hljs-keyword">in</span> 的使用方法<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> table1 <span class="hljs-keyword">where</span> a [<span class="hljs-keyword">not</span>] <span class="hljs-keyword">in</span> (‘值<span class="hljs-number">1</span>’,’值<span class="hljs-number">2</span>’,’值<span class="hljs-number">4</span>’,’值<span class="hljs-number">6</span>’)<span class="hljs-number">10</span>、说明：两张关联表，删除主表中已经在副表中没有的信息<span class="hljs-keyword">delete</span> <span class="hljs-keyword">from</span> table1 <span class="hljs-keyword">where</span> <span class="hljs-keyword">not</span> <span class="hljs-keyword">exists</span> ( <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> table2 <span class="hljs-keyword">where</span> table1.field1=table2.field1 )<span class="hljs-number">11</span>、说明：四表联查问题：<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> a <span class="hljs-keyword">left</span> <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> b <span class="hljs-keyword">on</span> a.a=b.b <span class="hljs-keyword">right</span> <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> c <span class="hljs-keyword">on</span> a.a=c.c <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> d <span class="hljs-keyword">on</span> a.a=d.d <span class="hljs-keyword">where</span> .....<span class="hljs-number">12</span>、说明：日程安排提前五分钟提醒<span class="hljs-keyword">SQL</span>: <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> 日程安排 <span class="hljs-keyword">where</span> datediff(<span class="hljs-string">'minute'</span>,f开始时间,getdate())><span class="hljs-number">5</span><span class="hljs-number">13</span>、说明：一条<span class="hljs-keyword">sql</span> 语句搞定数据库分页<span class="hljs-keyword">select</span> top <span class="hljs-number">10</span> b.* <span class="hljs-keyword">from</span> (<span class="hljs-keyword">select</span> top <span class="hljs-number">20</span> 主键字段,排序字段 <span class="hljs-keyword">from</span> 表名 <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> 排序字段 <span class="hljs-keyword">desc</span>) a,表名 b <span class="hljs-keyword">where</span> b.主键字段 = a.主键字段 <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> a.排序字段具体实现：关于数据库分页： <span class="hljs-keyword">declare</span> @<span class="hljs-keyword">start</span> <span class="hljs-keyword">int</span>,@<span class="hljs-keyword">end</span> <span class="hljs-keyword">int</span> @<span class="hljs-keyword">sql</span> nvarchar(<span class="hljs-number">600</span>) <span class="hljs-keyword">set</span> @<span class="hljs-keyword">sql</span>=’<span class="hljs-keyword">select</span> top’+str(@<span class="hljs-keyword">end</span>-@<span class="hljs-keyword">start</span>+<span class="hljs-number">1</span>)+’+<span class="hljs-keyword">from</span> T <span class="hljs-keyword">where</span> rid <span class="hljs-keyword">not</span> <span class="hljs-keyword">in</span>(<span class="hljs-keyword">select</span> top’+str(@str-<span class="hljs-number">1</span>)+’Rid <span class="hljs-keyword">from</span> T <span class="hljs-keyword">where</span> Rid>-<span class="hljs-number">1</span>)’ <span class="hljs-keyword">exec</span> sp_executesql @<span class="hljs-keyword">sql</span>注意：在top后不能直接跟一个变量，所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列，如果top后还有具体的字段，这样做是非常有好处的。因为这样可以避免 top的字段如果是逻辑索引的，查询的结果后实际表中的不一致（逻辑索引中的数据有可能和数据表中的不一致，而查询时如果处在索引则首先查询索引）<span class="hljs-number">14</span>、说明：前<span class="hljs-number">10</span>条记录<span class="hljs-keyword">select</span> top <span class="hljs-number">10</span> * form table1 <span class="hljs-keyword">where</span> 范围<span class="hljs-number">15</span>、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)<span class="hljs-keyword">select</span> a,b,c <span class="hljs-keyword">from</span> tablename ta <span class="hljs-keyword">where</span> a=(<span class="hljs-keyword">select</span> <span class="hljs-aggregate">max</span>(a) <span class="hljs-keyword">from</span> tablename tb <span class="hljs-keyword">where</span> tb.b=ta.b)<span class="hljs-number">16</span>、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表(<span class="hljs-keyword">select</span> a <span class="hljs-keyword">from</span> tableA ) <span class="hljs-keyword">except</span> (<span class="hljs-keyword">select</span> a <span class="hljs-keyword">from</span> tableB) <span class="hljs-keyword">except</span> (<span class="hljs-keyword">select</span> a <span class="hljs-keyword">from</span> tableC)<span class="hljs-number">17</span>、说明：随机取出<span class="hljs-number">10</span>条数据<span class="hljs-keyword">select</span> top <span class="hljs-number">10</span> * <span class="hljs-keyword">from</span> tablename <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> newid()<span class="hljs-number">18</span>、说明：随机选择记录<span class="hljs-keyword">select</span> newid()<span class="hljs-number">19</span>、说明：删除重复记录<span class="hljs-number">1</span>),<span class="hljs-keyword">delete</span> <span class="hljs-keyword">from</span> tablename <span class="hljs-keyword">where</span> id <span class="hljs-keyword">not</span> <span class="hljs-keyword">in</span> (<span class="hljs-keyword">select</span> <span class="hljs-aggregate">max</span>(id) <span class="hljs-keyword">from</span> tablename <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> col1,col2,...)<span class="hljs-number">2</span>),<span class="hljs-keyword">select</span> <span class="hljs-keyword">distinct</span> * <span class="hljs-keyword">into</span> temp <span class="hljs-keyword">from</span> tablename <span class="hljs-keyword">delete</span> <span class="hljs-keyword">from</span> tablename <span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> tablename <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> temp评价： 这种操作牵连大量的数据的移动，这种做法不适合大容量但数据操作<span class="hljs-number">3</span>),例如：在一个外部表中导入数据，由于某些原因第一次只导入了一部分，但很难判断具体位置，这样只有在下一次全部导入，这样也就产生好多重复的字段，怎样删除重复字段<span class="hljs-keyword">alter</span> <span class="hljs-keyword">table</span> tablename--添加一个自增列<span class="hljs-keyword">add</span> column_b <span class="hljs-keyword">int</span> <span class="hljs-keyword">identity</span>(<span class="hljs-number">1</span>,<span class="hljs-number">1</span>) <span class="hljs-keyword">delete</span> <span class="hljs-keyword">from</span> tablename <span class="hljs-keyword">where</span> column_b <span class="hljs-keyword">not</span> <span class="hljs-keyword">in</span>(<span class="hljs-keyword">select</span> <span class="hljs-aggregate">max</span>(column_b) <span class="hljs-keyword">from</span> tablename <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> column1,column2,...)<span class="hljs-keyword">alter</span> <span class="hljs-keyword">table</span> tablename <span class="hljs-keyword">drop</span> <span class="hljs-keyword">column</span> column_b<span class="hljs-number">20</span>、说明：列出数据库里所有的表名<span class="hljs-keyword">select</span> name <span class="hljs-keyword">from</span> sysobjects <span class="hljs-keyword">where</span> type=<span class="hljs-string">'U'</span> // U代表用户<span class="hljs-number">21</span>、说明：列出表里的所有的列名<span class="hljs-keyword">select</span> name <span class="hljs-keyword">from</span> syscolumns <span class="hljs-keyword">where</span> id=object_id(<span class="hljs-string">'TableName'</span>)<span class="hljs-number">22</span>、说明：列示type、vender、pcs字段，以type字段排列，<span class="hljs-keyword">case</span>可以方便地实现多重选择，类似<span class="hljs-keyword">select</span> 中的<span class="hljs-keyword">case</span>。<span class="hljs-keyword">select</span> type,<span class="hljs-aggregate">sum</span>(<span class="hljs-keyword">case</span> vender <span class="hljs-keyword">when</span> <span class="hljs-string">'A'</span> <span class="hljs-keyword">then</span> pcs <span class="hljs-keyword">else</span> <span class="hljs-number">0</span> <span class="hljs-keyword">end</span>),<span class="hljs-aggregate">sum</span>(<span class="hljs-keyword">case</span> vender <span class="hljs-keyword">when</span> <span class="hljs-string">'C'</span> <span class="hljs-keyword">then</span> pcs <span class="hljs-keyword">else</span> <span class="hljs-number">0</span> <span class="hljs-keyword">end</span>),<span class="hljs-aggregate">sum</span>(<span class="hljs-keyword">case</span> vender <span class="hljs-keyword">when</span> <span class="hljs-string">'B'</span> <span class="hljs-keyword">then</span> pcs <span class="hljs-keyword">else</span> <span class="hljs-number">0</span> <span class="hljs-keyword">end</span>) <span class="hljs-keyword">FROM</span> tablename <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> type显示结果：type vender pcs电脑 A <span class="hljs-number">1</span>电脑 A <span class="hljs-number">1</span>光盘 B <span class="hljs-number">2</span>光盘 A <span class="hljs-number">2</span>手机 B <span class="hljs-number">3</span>手机 C <span class="hljs-number">3</span><span class="hljs-number">23</span>、说明：初始化表table1<span class="hljs-keyword">TRUNCATE</span> <span class="hljs-keyword">TABLE</span> table1<span class="hljs-number">24</span>、说明：选择从<span class="hljs-number">10</span>到<span class="hljs-number">15</span>的记录<span class="hljs-keyword">select</span> top <span class="hljs-number">5</span> * <span class="hljs-keyword">from</span> (<span class="hljs-keyword">select</span> top <span class="hljs-number">15</span> * <span class="hljs-keyword">from</span> <span class="hljs-keyword">table</span> <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> id <span class="hljs-keyword">asc</span>) table_别名 <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> id <span class="hljs-keyword">desc</span>三、技巧<span class="hljs-number">1</span>、<span class="hljs-number">1</span>=<span class="hljs-number">1</span>，<span class="hljs-number">1</span>=<span class="hljs-number">2</span>的使用，在<span class="hljs-keyword">SQL</span>语句组合时用的较多“<span class="hljs-keyword">where</span> <span class="hljs-number">1</span>=<span class="hljs-number">1</span>” 是表示选择全部 “<span class="hljs-keyword">where</span> <span class="hljs-number">1</span>=<span class="hljs-number">2</span>”全部不选，如：<span class="hljs-keyword">if</span> @strWhere !=<span class="hljs-string">''</span><span class="hljs-keyword">begin</span><span class="hljs-keyword">set</span> @strSQL = <span class="hljs-string">'select count(*) as Total from ['</span> + @tblName + <span class="hljs-string">'] where '</span> + @strWhere<span class="hljs-keyword">end</span><span class="hljs-keyword">else</span><span class="hljs-keyword">begin</span><span class="hljs-keyword">set</span> @strSQL = <span class="hljs-string">'select count(*) as Total from ['</span> + @tblName + <span class="hljs-string">']'</span><span class="hljs-keyword">end</span>我们可以直接写成错误！未找到目录项。<span class="hljs-keyword">set</span> @strSQL = <span class="hljs-string">'select count(*) as Total from ['</span> + @tblName + <span class="hljs-string">'] where 1=1 安定 '</span>+ @strWhere <span class="hljs-number">2</span>、收缩数据库--重建索引DBCC REINDEXDBCC INDEXDEFRAG--收缩数据和日志DBCC SHRINKDBDBCC SHRINKFILE<span class="hljs-number">3</span>、压缩数据库dbcc shrinkdatabase(dbname)<span class="hljs-number">4</span>、转移数据库给新用户以已存在用户权限<span class="hljs-keyword">exec</span> sp_change_users_login <span class="hljs-string">'update_one'</span>,<span class="hljs-string">'newname'</span>,<span class="hljs-string">'oldname'</span><span class="hljs-keyword">go</span><span class="hljs-number">5</span>、检查备份集RESTORE VERIFYONLY <span class="hljs-keyword">from</span> disk=<span class="hljs-string">'E:\dvbbs.bak'</span><span class="hljs-number">6</span>、修复数据库<span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">DATABASE</span> [dvbbs] <span class="hljs-keyword">SET</span> SINGLE_USER<span class="hljs-keyword">GO</span>DBCC CHECKDB(<span class="hljs-string">'dvbbs'</span>,repair_allow_data_loss) <span class="hljs-keyword">WITH</span> TABLOCK<span class="hljs-keyword">GO</span><span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">DATABASE</span> [dvbbs] <span class="hljs-keyword">SET</span> MULTI_USER<span class="hljs-keyword">GO</span><span class="hljs-number">7</span>、日志清除<span class="hljs-keyword">SET</span> NOCOUNT <span class="hljs-keyword">ON</span><span class="hljs-keyword">DECLARE</span> @LogicalFileName sysname, @MaxMinutes <span class="hljs-keyword">INT</span>, @NewSize <span class="hljs-keyword">INT</span>USE tablename -- 要操作的数据库名<span class="hljs-keyword">SELECT</span> @LogicalFileName = <span class="hljs-string">'tablename_log'</span>, -- 日志文件名@MaxMinutes = <span class="hljs-number">10</span>, -- Limit <span class="hljs-keyword">on</span> <span class="hljs-keyword">time</span> allowed <span class="hljs-keyword">to</span> wrap log. @NewSize = <span class="hljs-number">1</span> -- 你想设定的日志文件的大小(M)Setup / initialize<span class="hljs-keyword">DECLARE</span> @OriginalSize <span class="hljs-keyword">int</span><span class="hljs-keyword">SELECT</span> @OriginalSize = <span class="hljs-keyword">size</span> <span class="hljs-keyword">FROM</span> sysfiles <span class="hljs-keyword">WHERE</span> name = @LogicalFileName<span class="hljs-keyword">SELECT</span> <span class="hljs-string">'Original Size of '</span> + db_name() + <span class="hljs-string">' LOG is '</span> + CONVERT(<span class="hljs-keyword">VARCHAR</span>(<span class="hljs-number">30</span>),@OriginalSize) + <span class="hljs-string">' 8K pages or '</span> + CONVERT(<span class="hljs-keyword">VARCHAR</span>(<span class="hljs-number">30</span>),(@OriginalSize*<span class="hljs-number">8</span>/<span class="hljs-number">1024</span>)) + <span class="hljs-string">'MB'</span> <span class="hljs-keyword">FROM</span> sysfiles <span class="hljs-keyword">WHERE</span> name = @LogicalFileName<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> DummyTrans (DummyColumn <span class="hljs-keyword">char</span> (<span class="hljs-number">8000</span>) <span class="hljs-keyword">not</span> <span class="hljs-keyword">null</span>)<span class="hljs-keyword">DECLARE</span> @Counter <span class="hljs-keyword">INT</span>, @StartTime DATETIME, @TruncLog <span class="hljs-keyword">VARCHAR</span>(<span class="hljs-number">255</span>)<span class="hljs-keyword">SELECT</span> @StartTime = GETDATE(), @TruncLog = <span class="hljs-string">'BACKUP LOG '</span> + db_name() + <span class="hljs-string">' WITH TRUNCATE_ONLY'</span>DBCC SHRINKFILE (@LogicalFileName, @NewSize)<span class="hljs-keyword">EXEC</span> (@TruncLog)-- Wrap the log <span class="hljs-keyword">if</span> necessary.WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- <span class="hljs-keyword">time</span> has <span class="hljs-keyword">not</span> expired <span class="hljs-keyword">AND</span> @OriginalSize = (<span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">size</span> <span class="hljs-keyword">FROM</span> sysfiles <span class="hljs-keyword">WHERE</span> name = @LogicalFileName) <span class="hljs-keyword">AND</span> (@OriginalSize * <span class="hljs-number">8</span> /<span class="hljs-number">1024</span>) > @NewSize <span class="hljs-keyword">BEGIN</span> -- <span class="hljs-keyword">Outer</span> loop.<span class="hljs-keyword">SELECT</span> @Counter = <span class="hljs-number">0</span> WHILE ((@Counter < @OriginalSize / <span class="hljs-number">16</span>) <span class="hljs-keyword">AND</span> (@Counter < <span class="hljs-number">50000</span>)) <span class="hljs-keyword">BEGIN</span> -- <span class="hljs-keyword">update</span> <span class="hljs-keyword">INSERT</span> DummyTrans <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'Fill Log'</span>) <span class="hljs-keyword">DELETE</span> DummyTrans <span class="hljs-keyword">SELECT</span> @Counter = @Counter + <span class="hljs-number">1</span> <span class="hljs-keyword">END</span> <span class="hljs-keyword">EXEC</span> (@TruncLog) <span class="hljs-keyword">END</span><span class="hljs-keyword">SELECT</span> <span class="hljs-string">'Final Size of '</span> + db_name() + <span class="hljs-string">' LOG is '</span> + CONVERT(<span class="hljs-keyword">VARCHAR</span>(<span class="hljs-number">30</span>),<span class="hljs-keyword">size</span>) + <span class="hljs-string">' 8K pages or '</span> + CONVERT(<span class="hljs-keyword">VARCHAR</span>(<span class="hljs-number">30</span>),(<span class="hljs-keyword">size</span>*<span class="hljs-number">8</span>/<span class="hljs-number">1024</span>)) + <span class="hljs-string">'MB'</span> <span class="hljs-keyword">FROM</span> sysfiles <span class="hljs-keyword">WHERE</span> name = @LogicalFileName<span class="hljs-keyword">DROP</span> <span class="hljs-keyword">TABLE</span> DummyTrans<span class="hljs-keyword">SET</span> NOCOUNT OFF<span class="hljs-number">8</span>、说明：更改某个表<span class="hljs-keyword">exec</span> sp_changeobjectowner <span class="hljs-string">'tablename'</span>,<span class="hljs-string">'dbo'</span><span class="hljs-number">9</span>、存储更改全部表<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">PROCEDURE</span> dbo.User_ChangeObjectOwnerBatch@OldOwner <span class="hljs-keyword">as</span> NVARCHAR(<span class="hljs-number">128</span>),@NewOwner <span class="hljs-keyword">as</span> NVARCHAR(<span class="hljs-number">128</span>)<span class="hljs-keyword">AS</span><span class="hljs-keyword">DECLARE</span> @Name <span class="hljs-keyword">as</span> NVARCHAR(<span class="hljs-number">128</span>)<span class="hljs-keyword">DECLARE</span> @Owner <span class="hljs-keyword">as</span> NVARCHAR(<span class="hljs-number">128</span>)<span class="hljs-keyword">DECLARE</span> @OwnerName <span class="hljs-keyword">as</span> NVARCHAR(<span class="hljs-number">128</span>)<span class="hljs-keyword">DECLARE</span> curObject <span class="hljs-keyword">CURSOR</span> <span class="hljs-keyword">FOR</span><span class="hljs-keyword">select</span> <span class="hljs-string">'Name'</span> = name, <span class="hljs-string">'Owner'</span> = user_name(uid)<span class="hljs-keyword">from</span> sysobjects<span class="hljs-keyword">where</span> user_name(uid)=@OldOwner<span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> name<span class="hljs-keyword">OPEN</span> curObject<span class="hljs-keyword">FETCH</span> <span class="hljs-keyword">NEXT</span> <span class="hljs-keyword">FROM</span> curObject <span class="hljs-keyword">INTO</span> @Name, @OwnerWHILE(@@FETCH_STATUS=<span class="hljs-number">0</span>)<span class="hljs-keyword">BEGIN</span> <span class="hljs-keyword">if</span> @Owner=@OldOwner<span class="hljs-keyword">begin</span> <span class="hljs-keyword">set</span> @OwnerName = @OldOwner + <span class="hljs-string">'.'</span> + rtrim(@Name) <span class="hljs-keyword">exec</span> sp_changeobjectowner @OwnerName, @NewOwner<span class="hljs-keyword">end</span>-- <span class="hljs-keyword">select</span> @name,@NewOwner,@OldOwner<span class="hljs-keyword">FETCH</span> <span class="hljs-keyword">NEXT</span> <span class="hljs-keyword">FROM</span> curObject <span class="hljs-keyword">INTO</span> @Name, @Owner<span class="hljs-keyword">END</span><span class="hljs-keyword">close</span> curObject<span class="hljs-keyword">deallocate</span> curObject<span class="hljs-keyword">GO</span><span class="hljs-number">10</span>、<span class="hljs-keyword">SQL</span> SERVER中直接循环写入数据<span class="hljs-keyword">declare</span> @i <span class="hljs-keyword">int</span><span class="hljs-keyword">set</span> @i=<span class="hljs-number">1</span>while @i<<span class="hljs-number">30</span><span class="hljs-keyword">begin</span> <span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test (userid) <span class="hljs-keyword">values</span>(@i) <span class="hljs-keyword">set</span> @i=@i+<span class="hljs-number">1</span><span class="hljs-keyword">end</span>案例：有如下表，要求就裱中所有沒有及格的成績，在每次增長<span class="hljs-number">0.1</span>的基礎上，使他們剛好及格: Name score Zhangshan <span class="hljs-number">80</span> Lishi <span class="hljs-number">59</span> Wangwu <span class="hljs-number">50</span> Songquan <span class="hljs-number">69</span>while((<span class="hljs-keyword">select</span> <span class="hljs-aggregate">min</span>(score) <span class="hljs-keyword">from</span> tb_table)<<span class="hljs-number">60</span>)<span class="hljs-keyword">begin</span><span class="hljs-keyword">update</span> tb_table <span class="hljs-keyword">set</span> score =score*<span class="hljs-number">1.01</span><span class="hljs-keyword">where</span> score<<span class="hljs-number">60</span><span class="hljs-keyword">if</span> (<span class="hljs-keyword">select</span> <span class="hljs-aggregate">min</span>(score) <span class="hljs-keyword">from</span> tb_table)><span class="hljs-number">60</span> break <span class="hljs-keyword">else</span> <span class="hljs-keyword">continue</span><span class="hljs-keyword">end</span>数据开发-经典<span class="hljs-number">1.</span>按姓氏笔画排序:<span class="hljs-keyword">Select</span> * <span class="hljs-keyword">From</span> TableName <span class="hljs-keyword">Order</span> <span class="hljs-keyword">By</span> CustomerName <span class="hljs-keyword">Collate</span> Chinese_PRC_Stroke_ci_as //从少到多<span class="hljs-number">2.</span>数据库加密:<span class="hljs-keyword">select</span> encrypt(<span class="hljs-string">'原始密码'</span>)<span class="hljs-keyword">select</span> pwdencrypt(<span class="hljs-string">'原始密码'</span>)<span class="hljs-keyword">select</span> pwdcompare(<span class="hljs-string">'原始密码'</span>,<span class="hljs-string">'加密后密码'</span>) = <span class="hljs-number">1</span>--相同；否则不相同 encrypt(<span class="hljs-string">'原始密码'</span>)<span class="hljs-keyword">select</span> pwdencrypt(<span class="hljs-string">'原始密码'</span>)<span class="hljs-keyword">select</span> pwdcompare(<span class="hljs-string">'原始密码'</span>,<span class="hljs-string">'加密后密码'</span>) = <span class="hljs-number">1</span>--相同；否则不相同<span class="hljs-number">3.</span>取回表中字段:<span class="hljs-keyword">declare</span> @list <span class="hljs-keyword">varchar</span>(<span class="hljs-number">1000</span>),@<span class="hljs-keyword">sql</span> nvarchar(<span class="hljs-number">1000</span>)<span class="hljs-keyword">select</span> @list=@list+<span class="hljs-string">','</span>+b.name <span class="hljs-keyword">from</span> sysobjects a,syscolumns b <span class="hljs-keyword">where</span> a.id=b.id <span class="hljs-keyword">and</span> a.name=<span class="hljs-string">'表A'</span><span class="hljs-keyword">set</span> @<span class="hljs-keyword">sql</span>=<span class="hljs-string">'select '</span>+<span class="hljs-keyword">right</span>(@list,len(@list)-<span class="hljs-number">1</span>)+<span class="hljs-string">' from 表A'</span><span class="hljs-keyword">exec</span> (@<span class="hljs-keyword">sql</span>)<span class="hljs-number">4.</span>查看硬盘分区:<span class="hljs-keyword">EXEC</span> master..xp_fixeddrives<span class="hljs-number">5.</span>比较A,B表是否相等:<span class="hljs-keyword">if</span> (<span class="hljs-keyword">select</span> checksum_agg(binary_checksum(*)) <span class="hljs-keyword">from</span> A) = (<span class="hljs-keyword">select</span> checksum_agg(binary_checksum(*)) <span class="hljs-keyword">from</span> B)print <span class="hljs-string">'相等'</span><span class="hljs-keyword">else</span>print <span class="hljs-string">'不相等'</span><span class="hljs-number">6.</span>杀掉所有的事件探察器进程:<span class="hljs-keyword">DECLARE</span> hcforeach <span class="hljs-keyword">CURSOR</span> <span class="hljs-keyword">GLOBAL</span> <span class="hljs-keyword">FOR</span> <span class="hljs-keyword">SELECT</span> <span class="hljs-string">'kill '</span>+RTRIM(spid) <span class="hljs-keyword">FROM</span> master.dbo.sysprocesses<span class="hljs-keyword">WHERE</span> program_name <span class="hljs-keyword">IN</span>(<span class="hljs-string">'SQL profiler'</span>,N<span class="hljs-string">'SQL 事件探查器'</span>)<span class="hljs-keyword">EXEC</span> sp_msforeach_worker <span class="hljs-string">'?'</span><span class="hljs-number">7.</span>记录搜索:开头到N条记录<span class="hljs-keyword">Select</span> Top N * <span class="hljs-keyword">From</span> 表-------------------------------N到M条记录(要有主索引ID)<span class="hljs-keyword">Select</span> Top M-N * <span class="hljs-keyword">From</span> 表 <span class="hljs-keyword">Where</span> ID <span class="hljs-keyword">in</span> (<span class="hljs-keyword">Select</span> Top M ID <span class="hljs-keyword">From</span> 表) <span class="hljs-keyword">Order</span> <span class="hljs-keyword">by</span> ID <span class="hljs-keyword">Desc</span>----------------------------------N到结尾记录<span class="hljs-keyword">Select</span> Top N * <span class="hljs-keyword">From</span> 表 <span class="hljs-keyword">Order</span> <span class="hljs-keyword">by</span> ID <span class="hljs-keyword">Desc</span>案例例如<span class="hljs-number">1</span>：一张表有一万多条记录，表的第一个字段 RecID 是自增长字段， 写一个<span class="hljs-keyword">SQL</span>语句，找出表的第<span class="hljs-number">31</span>到第<span class="hljs-number">40</span>个记录。 <span class="hljs-keyword">select</span> top <span class="hljs-number">10</span> recid <span class="hljs-keyword">from</span> A <span class="hljs-keyword">where</span> recid <span class="hljs-keyword">not</span> <span class="hljs-keyword">in</span>(<span class="hljs-keyword">select</span> top <span class="hljs-number">30</span> recid <span class="hljs-keyword">from</span> A)分析：如果这样写会产生某些问题，如果recid在表中存在逻辑索引。 <span class="hljs-keyword">select</span> top <span class="hljs-number">10</span> recid <span class="hljs-keyword">from</span> A <span class="hljs-keyword">where</span>……是从索引中查找，而后面的<span class="hljs-keyword">select</span> top <span class="hljs-number">30</span> recid <span class="hljs-keyword">from</span> A则在数据表中查找，这样由于索引中的顺序有可能和数据表中的不一致，这样就导致查询到的不是本来的欲得到的数据。解决方案<span class="hljs-number">1</span>， 用<span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> <span class="hljs-keyword">select</span> top <span class="hljs-number">30</span> recid <span class="hljs-keyword">from</span> A <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> ricid 如果该字段不是自增长，就会出现问题<span class="hljs-number">2</span>， 在那个子查询中也加条件：<span class="hljs-keyword">select</span> top <span class="hljs-number">30</span> recid <span class="hljs-keyword">from</span> A <span class="hljs-keyword">where</span> recid>-<span class="hljs-number">1</span>例<span class="hljs-number">2</span>：查询表中的最后以条记录，并不知道这个表共有多少数据,以及表结构。<span class="hljs-keyword">set</span> @s = <span class="hljs-string">'select top 1 * from T where pid not in (select top '</span> + str(@<span class="hljs-aggregate">count</span>-<span class="hljs-number">1</span>) + <span class="hljs-string">' pid from T)'</span>print @s <span class="hljs-keyword">exec</span> sp_executesql @s<span class="hljs-number">9</span>：获取当前数据库中的所有用户表<span class="hljs-keyword">select</span> Name <span class="hljs-keyword">from</span> sysobjects <span class="hljs-keyword">where</span> xtype=<span class="hljs-string">'u'</span> <span class="hljs-keyword">and</span> status>=<span class="hljs-number">0</span><span class="hljs-number">10</span>：获取某一个表的所有字段<span class="hljs-keyword">select</span> name <span class="hljs-keyword">from</span> syscolumns <span class="hljs-keyword">where</span> id=object_id(<span class="hljs-string">'表名'</span>)<span class="hljs-keyword">select</span> name <span class="hljs-keyword">from</span> syscolumns <span class="hljs-keyword">where</span> id <span class="hljs-keyword">in</span> (<span class="hljs-keyword">select</span> id <span class="hljs-keyword">from</span> sysobjects <span class="hljs-keyword">where</span> type = <span class="hljs-string">'u'</span> <span class="hljs-keyword">and</span> name = <span class="hljs-string">'表名'</span>)两种方式的效果相同<span class="hljs-number">11</span>：查看与某一个表相关的视图、存储过程、函数<span class="hljs-keyword">select</span> a.* <span class="hljs-keyword">from</span> sysobjects a, syscomments b <span class="hljs-keyword">where</span> a.id = b.id <span class="hljs-keyword">and</span> b.text <span class="hljs-keyword">like</span> <span class="hljs-string">'%表名%'</span><span class="hljs-number">12</span>：查看当前数据库中所有存储过程<span class="hljs-keyword">select</span> name <span class="hljs-keyword">as</span> 存储过程名称 <span class="hljs-keyword">from</span> sysobjects <span class="hljs-keyword">where</span> xtype=<span class="hljs-string">'P'</span><span class="hljs-number">13</span>：查询用户创建的所有数据库<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> master..sysdatabases D <span class="hljs-keyword">where</span> sid <span class="hljs-keyword">not</span> <span class="hljs-keyword">in</span>(<span class="hljs-keyword">select</span> sid <span class="hljs-keyword">from</span> master..syslogins <span class="hljs-keyword">where</span> name=<span class="hljs-string">'sa'</span>)或者<span class="hljs-keyword">select</span> dbid, name <span class="hljs-keyword">AS</span> DB_NAME <span class="hljs-keyword">from</span> master..sysdatabases <span class="hljs-keyword">where</span> sid <> <span class="hljs-number">0x01</span><span class="hljs-number">14</span>：查询某一个表的字段和数据类型<span class="hljs-keyword">select</span> column_name,data_type <span class="hljs-keyword">from</span> information_schema.columns<span class="hljs-keyword">where</span> table_name = <span class="hljs-string">'表名'</span><span class="hljs-number">15</span>：不同服务器数据库之间的数据操作--创建链接服务器<span class="hljs-keyword">exec</span> sp_addlinkedserver <span class="hljs-string">'ITSV '</span>, <span class="hljs-string">' '</span>, <span class="hljs-string">'SQLOLEDB '</span>, <span class="hljs-string">'远程服务器名或ip地址 '</span><span class="hljs-keyword">exec</span> sp_addlinkedsrvlogin <span class="hljs-string">'ITSV '</span>, <span class="hljs-string">'false '</span>,<span class="hljs-keyword">null</span>, <span class="hljs-string">'用户名 '</span>, <span class="hljs-string">'密码 '</span>--查询示例<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> ITSV.数据库名.dbo.表名--导入示例<span class="hljs-keyword">select</span> * <span class="hljs-keyword">into</span> 表 <span class="hljs-keyword">from</span> ITSV.数据库名.dbo.表名--以后不再使用时删除链接服务器<span class="hljs-keyword">exec</span> sp_dropserver <span class="hljs-string">'ITSV '</span>, <span class="hljs-string">'droplogins '</span>--连接远程/局域网数据(openrowset/openquery/opendatasource)--<span class="hljs-number">1</span>、openrowset--查询示例<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> openrowset( <span class="hljs-string">'SQLOLEDB '</span>, <span class="hljs-string">'sql服务器名 '</span>;</span> '用户名 '; '密码 ',数据库名.dbo.表名)<span class="hljs-comment">--生成本地表</span><span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">into</span> 表 <span class="hljs-keyword">from</span> openrowset( <span class="hljs-string">'SQLOLEDB '</span>, <span class="hljs-string">'sql服务器名 '</span>;</span> '用户名 '; '密码 ',数据库名.dbo.表名)<span class="hljs-comment">--把本地表导入远程表</span><span class="hljs-operator"><span class="hljs-keyword">insert</span> openrowset( <span class="hljs-string">'SQLOLEDB '</span>, <span class="hljs-string">'sql服务器名 '</span>;</span> '用户名 '; '密码 ',数据库名.dbo.表名)<span class="hljs-operator"><span class="hljs-keyword">select</span> *<span class="hljs-keyword">from</span> 本地表--更新本地表<span class="hljs-keyword">update</span> b<span class="hljs-keyword">set</span> b.列A=a.列A <span class="hljs-keyword">from</span> openrowset( <span class="hljs-string">'SQLOLEDB '</span>, <span class="hljs-string">'sql服务器名 '</span>;</span> '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 bon a.column1=b.column1<span class="hljs-comment">--openquery用法需要创建一个连接</span><span class="hljs-comment">--首先创建一个连接创建链接服务器</span>exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '<span class="hljs-comment">--查询</span><span class="hljs-operator"><span class="hljs-keyword">select</span> *<span class="hljs-keyword">FROM</span> openquery(ITSV, <span class="hljs-string">'SELECT * FROM 数据库.dbo.表名 '</span>)--把本地表导入远程表<span class="hljs-keyword">insert</span> openquery(ITSV, <span class="hljs-string">'SELECT * FROM 数据库.dbo.表名 '</span>)<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> 本地表--更新本地表<span class="hljs-keyword">update</span> b<span class="hljs-keyword">set</span> b.列B=a.列B<span class="hljs-keyword">FROM</span> openquery(ITSV, <span class="hljs-string">'SELECT * FROM 数据库.dbo.表名 '</span>) <span class="hljs-keyword">as</span> a <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> 本地表 b <span class="hljs-keyword">on</span> a.列A=b.列A--<span class="hljs-number">3</span>、opendatasource/openrowset<span class="hljs-keyword">SELECT</span> *<span class="hljs-keyword">FROM</span> opendatasource( <span class="hljs-string">'SQLOLEDB '</span>, <span class="hljs-string">'Data Source=ip/ServerName;User ID=登陆名;Password=密码 '</span> ).test.dbo.roy_ta--把本地表导入远程表<span class="hljs-keyword">insert</span> opendatasource( <span class="hljs-string">'SQLOLEDB '</span>, <span class="hljs-string">'Data Source=ip/ServerName;User ID=登陆名;Password=密码 '</span>).数据库.dbo.表名<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> 本地表 <span class="hljs-keyword">SQL</span> Server基本函数<span class="hljs-keyword">SQL</span> Server基本函数<span class="hljs-number">1.</span>字符串函数 长度与分析用<span class="hljs-number">1</span>,datalength(Char_expr) 返回字符串包含字符数,但不包含后面的空格<span class="hljs-number">2</span>,substring(expression,<span class="hljs-keyword">start</span>,length) 取子串，字符串的下标是从“<span class="hljs-number">1</span>”，<span class="hljs-keyword">start</span>为起始位置，length为字符串长度，实际应用中以len(expression)取得其长度<span class="hljs-number">3</span>,<span class="hljs-keyword">right</span>(char_expr,int_expr) 返回字符串右边第int_expr个字符，还用<span class="hljs-keyword">left</span>于之相反<span class="hljs-number">4</span>,isnull( check_expression , replacement_value )如果check_expression為空，則返回replacement_value的值，不為空，就返回check_expression字符操作类<span class="hljs-number">5</span>,Sp_addtype 自定義數據類型例如：<span class="hljs-keyword">EXEC</span> sp_addtype birthday, datetime, <span class="hljs-string">'NULL'</span><span class="hljs-number">6</span>,<span class="hljs-keyword">set</span> nocount {<span class="hljs-keyword">on</span>|off}使返回的结果中不包含有关受 Transact-<span class="hljs-keyword">SQL</span> 语句影响的行数的信息。如果存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。<span class="hljs-keyword">SET</span> NOCOUNT 设置是在执行或运行时设置，而不是在分析时设置。<span class="hljs-keyword">SET</span> NOCOUNT 为 <span class="hljs-keyword">ON</span> 时，不返回计数（表示受 Transact-<span class="hljs-keyword">SQL</span> 语句影响的行数）。<span class="hljs-keyword">SET</span> NOCOUNT 为 OFF 时，返回计数常识在<span class="hljs-keyword">SQL</span>查询中：<span class="hljs-keyword">from</span>后最多可以跟多少张表或视图：<span class="hljs-number">256</span>在<span class="hljs-keyword">SQL</span>语句中出现 <span class="hljs-keyword">Order</span> <span class="hljs-keyword">by</span>,查询时，先排序，后取在<span class="hljs-keyword">SQL</span>中，一个字段的最大容量是<span class="hljs-number">8000</span>，而对于nvarchar(<span class="hljs-number">4000</span>),由于nvarchar是Unicode码。 SQLServer2000同步复制技术实现步骤一、 预备工作<span class="hljs-number">1.</span>发布服务器,订阅服务器都创建一个同名的windows用户,并设置相同的密码,做为发布快照文件夹的有效访问用户--管理工具--计算机管理--用户和组--右键用户--新建用户--建立一个隶属于administrator组的登陆windows的用户（SynUser）<span class="hljs-number">2.</span>在发布服务器上,新建一个共享目录,做为发布的快照文件的存放目录,操作:我的电脑--D:\ 新建一个目录,名为: PUB--右键这个新建的目录--属性--共享--选择<span class="hljs-string">"共享该文件夹"</span>--通过<span class="hljs-string">"权限"</span>按纽来设置具体的用户权限,保证第一步中创建的用户(SynUser) 具有对该文件夹的所有权限--确定<span class="hljs-number">3.</span>设置<span class="hljs-keyword">SQL</span>代理(SQLSERVERAGENT)服务的启动用户(发布/订阅服务器均做此设置)开始--程序--管理工具--服务--右键SQLSERVERAGENT--属性--登陆--选择<span class="hljs-string">"此账户"</span>--输入或者选择第一步中创建的windows登录用户名（SynUser）--<span class="hljs-string">"密码"</span>中输入该用户的密码<span class="hljs-number">4.</span>设置<span class="hljs-keyword">SQL</span> Server身份验证模式,解决连接时的权限问题(发布/订阅服务器均做此设置)企业管理器--右键<span class="hljs-keyword">SQL</span>实例--属性--安全性--身份验证--选择<span class="hljs-string">"SQL Server 和 Windows"</span>--确定<span class="hljs-number">5.</span>在发布服务器和订阅服务器上互相注册企业管理器--右键<span class="hljs-keyword">SQL</span> Server组--新建<span class="hljs-keyword">SQL</span> Server注册...--下一步--可用的服务器中,输入你要注册的远程服务器名 --添加--下一步--连接使用,选择第二个<span class="hljs-string">"SQL Server身份验证"</span>--下一步--输入用户名和密码（SynUser）--下一步--选择<span class="hljs-keyword">SQL</span> Server组,也可以创建一个新组--下一步--完成<span class="hljs-number">6.</span>对于只能用IP,不能用计算机名的,为其注册服务器别名（此步在实施中没用到） (在连接端配置,比如,在订阅服务器上配置的话,服务器名称中输入的是发布服务器的IP)开始--程序--Microsoft <span class="hljs-keyword">SQL</span> Server--客户端网络实用工具--别名--添加--网络库选择<span class="hljs-string">"tcp/ip"</span>--服务器别名输入<span class="hljs-keyword">SQL</span>服务器名--连接参数--服务器名称中输入<span class="hljs-keyword">SQL</span>服务器ip地址--如果你修改了<span class="hljs-keyword">SQL</span>的端口,取消选择<span class="hljs-string">"动态决定端口"</span>,并输入对应的端口号二、 正式配置<span class="hljs-number">1</span>、配置发布服务器打开企业管理器，在发布服务器（B、C、D）上执行以下步骤:(<span class="hljs-number">1</span>) 从[工具]下拉菜单的[复制]子菜单中选择[配置发布、订阅服务器和分发]出现配置发布和分发向导(<span class="hljs-number">2</span>) [下一步] 选择分发服务器 可以选择把发布服务器自己作为分发服务器或者其他<span class="hljs-keyword">sql</span>的服务器（选择自己）(<span class="hljs-number">3</span>) [下一步] 设置快照文件夹采用默认\\servername\Pub(<span class="hljs-number">4</span>) [下一步] 自定义配置可以选择:是,让我设置分发数据库属性启用发布服务器或设置发布设置否,使用下列默认设置（推荐）(<span class="hljs-number">5</span>) [下一步] 设置分发数据库名称和位置 采用默认值(<span class="hljs-number">6</span>) [下一步] 启用发布服务器 选择作为发布的服务器(<span class="hljs-number">7</span>) [下一步] 选择需要发布的数据库和发布类型(<span class="hljs-number">8</span>) [下一步] 选择注册订阅服务器(<span class="hljs-number">9</span>) [下一步] 完成配置<span class="hljs-number">2</span>、创建出版物发布服务器B、C、D上(<span class="hljs-number">1</span>)从[工具]菜单的[复制]子菜单中选择[创建和管理发布]命令(<span class="hljs-number">2</span>)选择要创建出版物的数据库，然后单击[创建发布](<span class="hljs-number">3</span>)在[创建发布向导]的提示对话框中单击[下一步]系统就会弹出一个对话框。对话框上的内容是复制的三个类型。我们现在选第一个也就是默认的快照发布(其他两个大家可以去看看帮助)(<span class="hljs-number">4</span>)单击[下一步]系统要求指定可以订阅该发布的数据库服务器类型,SQLSERVER允许在不同的数据库如 orACLE或ACCESS之间进行数据复制。但是在这里我们选择运行<span class="hljs-string">"SQL SERVER 2000"</span>的数据库服务器(<span class="hljs-number">5</span>)单击[下一步]系统就弹出一个定义文章的对话框也就是选择要出版的表注意: 如果前面选择了事务发布 则再这一步中只能选择带有主键的表(<span class="hljs-number">6</span>)选择发布名称和描述(<span class="hljs-number">7</span>)自定义发布属性 向导提供的选择:是 我将自定义数据筛选,启用匿名订阅和或其他自定义属性否 根据指定方式创建发布 （建议采用自定义的方式）(<span class="hljs-number">8</span>)[下一步] 选择筛选发布的方式(<span class="hljs-number">9</span>)[下一步] 可以选择是否允许匿名订阅<span class="hljs-number">1</span>)如果选择署名订阅,则需要在发布服务器上添加订阅服务器方法: [工具]->[复制]->[配置发布、订阅服务器和分发的属性]->[订阅服务器] 中添加否则在订阅服务器上请求订阅时会出现的提示:改发布不允许匿名订阅如果仍然需要匿名订阅则用以下解决办法[企业管理器]->[复制]->[发布内容]->[属性]->[订阅选项] 选择允许匿名请求订阅<span class="hljs-number">2</span>)如果选择匿名订阅,则配置订阅服务器时不会出现以上提示(<span class="hljs-number">10</span>)[下一步] 设置快照 代理程序调度(<span class="hljs-number">11</span>)[下一步] 完成配置当完成出版物的创建后创建出版物的数据库也就变成了一个共享数据库有数据srv1.库名..author有字段:id,name,phone,srv2.库名..author有字段:id,name,telphone,adress要求：srv1.库名..author增加记录则srv1.库名..author记录增加srv1.库名..author的phone字段更新，则srv1.库名..author对应字段telphone更新--*/--大致的处理步骤--<span class="hljs-number">1.</span>在 srv1 上创建连接服务器,以便在 srv1 中操作 srv2,实现同步<span class="hljs-keyword">exec</span> sp_addlinkedserver <span class="hljs-string">'srv2'</span>,<span class="hljs-string">''</span>,<span class="hljs-string">'SQLOLEDB'</span>,<span class="hljs-string">'srv2的sql实例名或ip'</span><span class="hljs-keyword">exec</span> sp_addlinkedsrvlogin <span class="hljs-string">'srv2'</span>,<span class="hljs-string">'false'</span>,<span class="hljs-keyword">null</span>,<span class="hljs-string">'用户名'</span>,<span class="hljs-string">'密码'</span><span class="hljs-keyword">go</span>--<span class="hljs-number">2.</span>在 srv1 和 srv2 这两台电脑中,启动 msdtc(分布式事务处理服务),并且设置为自动启动。我的电脑--控制面板--管理工具--服务--右键 Distributed <span class="hljs-keyword">Transaction</span> Coordinator--属性--启动--并将启动类型设置为自动启动<span class="hljs-keyword">go</span>--然后创建一个作业定时调用上面的同步处理存储过程就行了企业管理器--管理--<span class="hljs-keyword">SQL</span> Server代理--右键作业--新建作业--<span class="hljs-string">"常规"</span>项中输入作业名称--<span class="hljs-string">"步骤"</span>项--新建--<span class="hljs-string">"步骤名"</span>中输入步骤名--<span class="hljs-string">"类型"</span>中选择<span class="hljs-string">"Transact-SQL 脚本(TSQL)"</span>--<span class="hljs-string">"数据库"</span>选择执行命令的数据库--<span class="hljs-string">"命令"</span>中输入要执行的语句: <span class="hljs-keyword">exec</span> p_process--确定--<span class="hljs-string">"调度"</span>项--新建调度--<span class="hljs-string">"名称"</span>中输入调度名称--<span class="hljs-string">"调度类型"</span>中选择你的作业执行安排--如果选择<span class="hljs-string">"反复出现"</span>--点<span class="hljs-string">"更改"</span>来设置你的时间安排然后将<span class="hljs-keyword">SQL</span> Agent服务启动,并设置为自动启动,否则你的作业不会被执行设置方法:我的电脑--控制面板--管理工具--服务--右键 SQLSERVERAGENT--属性--启动类型--选择<span class="hljs-string">"自动启动"</span>--确定.--<span class="hljs-number">3.</span>实现同步处理的方法<span class="hljs-number">2</span>,定时同步--在srv1中创建如下的同步处理存储过程<span class="hljs-keyword">create</span> proc p_process<span class="hljs-keyword">as</span>--更新修改过的数据<span class="hljs-keyword">update</span> b <span class="hljs-keyword">set</span> name=i.name,telphone=i.telphone<span class="hljs-keyword">from</span> srv2.库名.dbo.author b,author i<span class="hljs-keyword">where</span> b.id=i.id <span class="hljs-keyword">and</span>(b.name <> i.name <span class="hljs-keyword">or</span> b.telphone <> i.telphone)--插入新增的数据<span class="hljs-keyword">insert</span> srv2.库名.dbo.author(id,name,telphone)<span class="hljs-keyword">select</span> id,name,telphone <span class="hljs-keyword">from</span> author i<span class="hljs-keyword">where</span> <span class="hljs-keyword">not</span> <span class="hljs-keyword">exists</span>(<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> srv2.库名.dbo.author <span class="hljs-keyword">where</span> id=i.id)--删除已经删除的数据(如果需要的话)<span class="hljs-keyword">delete</span> b<span class="hljs-keyword">from</span> srv2.库名.dbo.author b<span class="hljs-keyword">where</span> <span class="hljs-keyword">not</span> <span class="hljs-keyword">exists</span>(<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> author <span class="hljs-keyword">where</span> id=b.id)<span class="hljs-keyword">go</span></span></code> 0 0
- JDBC常用类和接口及增删查修
- JDBC常用类和接口及增删查修
- JDBC常用类和接口及基本操作示例
- JDBC常用类和接口
- JDBC常用接口和类
- JDBC 增删改查工具类 和测试类
- Jdbc 增删改查 工具类和测试类
- JDBC主要接口和类简介及常用JDBC连接code
- JDBC安装和增删改查基础知识
- JDBC常用的类和接口详解
- JDBC常用的类和接口
- java增删改查JDBC封装类
- jdbc增删改查的工具类
- Jdbc 增删改查
- jdbc增删改查
- JDBC 增删改查
- jdbc增删改查
- jdbc增删查改
- （八） CXF 整合Spring--调用WS服务
- Email--Send
- C++第2次实验（函数题）
- hdu2157 how many ways 矩阵
- 做个“脸皮厚”的人
- JDBC常用类和接口及增删查修
- UIKit之UITableView的详细使用
- onItemClick和onItemLongClick同时触发解决方案
- ios本地添加提醒事件
- MFC自定义资源
- CSS笔记
- 网站性能优化之黄金守则
- android中viewPager双层嵌套问题，子viewpager无法滑动和滑动父级viewpager后子viewpager不显示内容
- oracle索引导致的查询记录为0的案例
