澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

澳门新萄京官方网站:MySQL的常规操作,记录多一

2019-09-16 作者:www.8455.com   |   浏览(144)

 有问题欢迎留言!!!

MySQL的常规知识

MySQL的常规知识

 "权限限制访问"几乎每个程序员在做系统时都会用到。就我而言会用一下三种方法来实现:

这个系列的博文知识回顾sql知识作的记录,温故而知新。上一篇内容达不到知识分享的层级被移出园子首页,对不住各位看官,内容简单了些。下面咱就记录多一些的基础知识,薄利多销:


show

标准的SQL语句通常可分为如下的几种类型:

  1. 写一个权限管理函数,在每个代码隐藏文件中的Page_Load函数中调用。这种方法很低级,代码量很大,如果系统很大的化,那简直就是一场灾难。
  2. 撰写一个继承System.Web.UI.Page 基类的BasePage类,然后再 BasePage类继承的OnInit 方法中填写权限管理的代码。然后在系统的每个代码隐藏文件中的Page类继承BasePage类就OK了。使用这种方法虽然能够有效的解决代码重用问题,但是想想如果忘了让Page累继承BasePage类,那将会变成系统安全的一个漏洞......
  3. 用HttpModule来实现"权限限制访问"相对简单多了,也可以有效解决以上问题,以下是自己写的代码示例(VS2005),请路过的朋友不要见笑,并提出宝贵意见。

控制用户权限

• 创建用户

   CREATE USER user

   style="color: #ff0000;">IDENTIFIED BY password;

• 创建用户表空间

   ALTER USER user QUOTA UNLIMITED

   style="color: #ff0000;">ON users

• 赋予系统权限

   – CREATE SESSION(创建会话)

   – CREATE TABLE(创建表)

   – CREATE SEQUENCE(创建序列)

   – CREATE VIEW(创建视图)

   – CREATE PROCEDURE(创建过程)

   澳门新萄京官方网站 1

 

   澳门新萄京官方网站 2

 

• 赋予对象权限

   GRANT object_priv [(columns)]

   ON object

   TO {user|role|PUBLIC} style="color: #00ff00;">--PUBLIC代表数据库所有用户

   [WITH GRANT OPTION]; style="color: #00ff00;">--使用户同样具有分配权限的权利

  --分配表 EMPLOYEES 的查询权限

   GRANT select

   ON employees

   TO sue, rich;

• 回收对象权限

   REVOKE {privilege [, privilege...]|ALL}  --使用 WITH GRANT OPTION 子句所分配的权限同样被收回

   ON object

   FROM {user[, user...]|role|PUBLIC}

   [CASCADE CONSTRAINTS];

   style="color: #00ff00;">--回收对象权限举例

   REVOKE select, insert

   ON departments

   FROM scott;

 

• 查询权限

   澳门新萄京官方网站 3

  SELECT *
   FROM user_sys_privs

 目录:

style="font-family: SimSun; font-size: 24px; color: #3333ff; background-color: #ffffff;">查看数据库

show databases;

style="font-family: SimSun; font-size: 24px; color: #3366ff;">查看表

style="font-family: SimSun; font-size: 24px; color: #3366ff; background-color: #ff0000;"> style="font-family: SimSun; font-size: 18px; color: #ffff00;">查看编码格式

style="font-family: SimSun; font-size: 24px; color: #000000;"> style="font-family: SimSun; font-size: 18px;"> style="font-family: SimSun; font-size: 18px;"> style="color: #555555; font-family: 'microsoft yahei'; font-size: 15px;">show variables like 'character%';

style="font-family: SimSun; font-size: 18px; color: #ffff00; background-color: #ff0000;">存在的所有表

show tables;

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">创建表的命令

show create table table_name;

style="font-family: SimSun; font-size: 18px; color: #ffff00; background-color: #ff0000;">表的结构

show index from table_name;

style="font-family: SimSun; font-size: 18px;">澳门新萄京官方网站 4

show columns from table_name;

style="font-family: SimSun; font-size: 18px;">澳门新萄京官方网站 5

> style="color: red;">注: > > > style="color: red;">“show columns from table_name; > > > style="color: red;">” > > > style="color: red;">和“ > > > style="color: red;">desctable_name;” > > > style="color: red;">效果是一样的。

show table status like "table_name"G

style="font-family: SimSun; font-size: 18px;">澳门新萄京官方网站 6

> style="color: red;">注: > > > style="color: red;">”G” > > > style="color: red;">是为了让数据显示更清晰易懂,也可以不设置。

style="font-family: SimSun; font-size: 24px; color: #3333ff;">查看服务器

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">服务器状态

show status;

style="font-family: SimSun; font-size: 18px; background-color: #ff0000;"> > style="color: #ffcc00;">服务器配置变量

show variables;

1,DCL(Database Control Language) :数据控制语言,主要由grant和revoke关键字组成。

  • 文件->添加新项目->Visual C#->类库 

 

一、新建项目,添加引用

use

2.DDL(Database Define Language):数据定义语言,主要由Create、Drop、Alter和Truncate关键字组成。

 

多表查询

首先明确我们要做的事:

澳门新萄京官方网站 7

如上图是我们平时业务中再正常不过的了,那么这小节咱就说说连接多表查询数据。

 

内连接:合并具有同一列的两个以上的表的行, style="color: #ff0000;">结果集中不包含一个表与另一个表不匹配的行

  • 等值连接

     - 方式一:在 WHERE 子句中写入连接条件,连接 n个表,至少需要 n-1个连接条件(通用型)

        SELECT e.employee_id, e.last_name, e.department_id,

        d.department_id, d.location_id

        FROM employees e , departments d

        style="color: #ff0000;">WHERE style="color: #ff0000;">e.department_id = d.department_id;

     style="color: #666666;">- 方式二:使用JOIN…ON创建多表连接( style="color: #ff0000;">常用方式:较通用型WHERE条件,更易实现外连接(左、右、满))

        SELECT employee_id, city, department_name

        FROM employees e

        style="color: #ff0000;">JOIN departments d

        style="color: #ff0000;">ON d.department_id = e.department_id

        style="color: #ff0000;">JOIN locations l

        style="color: #ff0000;">ON d.location_id = l.location_id;

     style="color: #666666;">- 方式三:使用USING子句创建连接(局限性:好于自然连接,但若多表的连接列列名不同,此法不合适)

        SELECT e.employee_id, e.last_name, d.location_id

        FROM employees e style="color: #ff0000;">JOIN departments d

        style="color: #ff0000;">USING (department_id); style="color: #00ff00;">--JOIN和USING经常同时使用

  • 不等值连接

        SELECT e.last_name, e.salary, j.grade_level

        FROM employees e, job_grades j

        style="color: #ff0000;">WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

  • 自连接

        SELECT worker.last_name || ' works for ' || manager.last_name

        FROM style="color: #ff0000;">employees worker, employees manager

        style="color: #ff0000;">WHERE worker.manager_id = manager.employee_id;

  • 自然连接(局限性:会自动连接两个表中相同的列)

     - NATURAL JOIN 子句,会以两个表中 style="color: #ff0000;"> style="text-decoration: underline;">具有相同名字的列为条件创建 style="text-decoration: underline;">等值连接。

     - 在表中查询满足等值条件的数据。

     - 如果只是列名相同而 style="text-decoration: underline;"> style="color: #ff0000;">数据类型不同,则会产生错误。

        SELECT department_id, department_name, location_id, city

        FROM departments

        style="color: #ff0000;">NATURAL JOIN locations ;

外连接:两个表在连接过程中除了返回满足连接条件的行以外 style="color: #ff0000;">还返回左(或右)表中不满足条件的行 ,这种连接称为左(或右) 外连接。 style="color: #ff0000;">没有匹配的行时, 结果表中相应的列为空(NULL)。

  • 左外连接

     - 方式一:外连接运算符

        SELECT table1.column, table2.column

        FROM table1, table2

        WHERE table1.column = table2.column( );

     - 方式二:SQL1999语法

        SELECT e.last_name, e.department_id, d.department_name

        FROM employees e

        style="color: #ff0000;">LEFT OUTER JOIN departments d

        style="color: #ff0000;">ON (e.department_id = d.department_id) ;

  • 右外连接

     - 方式一:外连接运算符

        SELECT table1.column, table2.column

        FROM table1, table2

        WHERE table1.column style="color: #ff0000;">( ) = table2.column;

     - 方式二:SQL1999语法

        SELECT e.last_name, e.department_id, d.department_name

        FROM employees e

        style="color: #ff0000;">RIGHT OUTER JOIN departments d

        style="color: #ff0000;">ON (e.department_id = d.department_id);

  • 满外连接

        SELECT e.last_name, e.department_id, d.department_name

        FROM employees e

        style="color: #ff0000;">FULL OUTER JOIN departments d

        style="color: #ff0000;">ON (e.department_id = d.department_id);

二、创建数据库

style="font-family: SimSun; font-size: 24px; color: #3366ff;">使用数据库

use database_name;

3,DML(Database Manipulate Language):数据控制语法,主要由insert、update和delete关键字组成。

澳门新萄京官方网站 8澳门新萄京官方网站 9Code
 1 using System;
 2 using System.Data;
 3 using System.Configuration;
 4 using System.Web;
 5 using System.Web.Security;
 6 using System.Web.UI;
 7 using System.Web.UI.WebControls;
 8 using System.Web.UI.WebControls.WebParts;
 9 using System.Web.UI.HtmlControls;
10 using DataControl;
11 
12 namespace HM
13 {
14     public class UserControl : IHttpModule
15     {
16 
17         #region IHttpModule
18 
19         public void Dispose()
20         {
21 
22         }
23 
24         public void Init(HttpApplication context)
25         {
26               context.BeginRequest  = new System.EventHandler(httpApplication_BeginRequest);
27               
28         }
29 
30         #endregion
31 
32         #region Registered event handlers
33         public void httpApplication_BeginRequest(object sender, EventArgs e)
34         {
35             HttpApplication httpApplication = (HttpApplication)sender;
36 
37 
38             HttpCookie UserCookie = httpApplication.Context.Request.Cookies["UserCookie"];
39 
40 
41             //如果用户未登录就跳转到登录界面
42             if (UserCookie == null)
43             {
44 
45                 httpApplication.Context.Response.Redirect("/Portal/ProtalLogin.aspx");
46 
47             }
48 
49             //根据当前路径取得用户权限
50             DataTable dt = Authority.GetAuthorityByUserAndPath(UserCookie["UserID"], httpApplication.Context.Request.RawUrl);
51             if (dt.Rows.Count == 1)
52             {
53                 UserCookie["ISVIEW"] = dt.Rows[0]["ISVIEW"].ToString();
54                 UserCookie["ISADD"] = dt.Rows[0]["ISADD"].ToString();
55                 UserCookie["ISUPDATE"] = dt.Rows[0]["ISUPDATE"].ToString();
56                 UserCookie["ISDELETE"] = dt.Rows[0]["ISDELETE"].ToString();
57                 UserCookie["ISCONFIRM"] = dt.Rows[0]["ISCONFIRM"].ToString();
58 
59             }
60             else
61             {
62                 UserCookie["ISVIEW"] = "0";
63                 UserCookie["ISADD"] = "0";
64                 UserCookie["ISUPDATE"] = "0";
65                 UserCookie["ISDELETE"] = "0";
66                 UserCookie["ISCONFIRM"] = "0";
67 
68             }
69         }
70 
71 
72         #endregion
73      
74        if (UserCookie["ISVIEW"].ToString().Equals("0"))
75             {
76 
77                 httpApplication.Context.Response.Redirect("/Portal/ProtalLogin.aspx");
78 
79             }
80 
81     }
82 }
83 

 

三、创建表

create

4,DQL(Database Query Language):数据查询语言,主要由select关键字组成

                                                      

style="color: #333333;">SET运算符:将多个查询用SET操作符连接组成一个新的查询

• 在SELECT 列表中的列名和表达式在 style="color: #ff0000;">数量 style="color: #ff0000;">和数据类型上要 style="color: #ff0000;">相对应,所以就有了各种字段匹配

     SELECT department_id, style="color: #9b00d3;">TO_NUMBER(null) location, style="color: #0000ff;">hire_date

     FROM employees

     UNION

     SELECT department_id, style="color: #9b00d3;">location_id, style="color: #0000ff;">TO_DATE(null)

     FROM departments;

• 括号可以改变执行的顺序

• ORDER BY 子句:

    – 只能在语句的最后出现

    – 可以使用第一个查询中的列名, 别名或相对位置

• 除 UNION ALL之外,系统会自动将重复的记录删除

• 系统将第一个查询的列名显示在输出中

• 除 UNION ALL之外,系统自动按照第一个查询中的第一个列的升序排列

   澳门新萄京官方网站 10

UNION 返回两个查询的结果集的并集

   SELECT employee_id, job_id

   FROM employees

   style="color: #ff0000;">UNION

   SELECT employee_id, job_id

   FROM job_history;

   澳门新萄京官方网站 11

UNION ALL 返回两个查询的结果集的并集。对于两个结果集的重复部分,不去重

   SELECT employee_id, job_id, department_id

   FROM employees

   style="color: #ff0000;">UNION ALL

   SELECT employee_id, job_id, department_id

   FROM job_history

   ORDER BY employee_id;

   澳门新萄京官方网站 12

INTERSECT 返回两个结果集的交集

   SELECT employee_id, job_id

   FROM employees

   style="color: #ff0000;">INTERSECT

   SELECT employee_id, job_id

   FROM job_history;

   澳门新萄京官方网站 13

MINUS 返回两个结果集的差集

   SELECT employee_id,job_id

   FROM employees

   style="color: #ff0000;">MINUS

   SELECT employee_id,job_id

   FROM job_history;

   澳门新萄京官方网站 14

四、插入数据 

style="font-family: SimSun; font-size: 24px; color: #3366ff;">创建数据库

create databasedatabase_name;

style="color: #3366ff; font-family: SimSun; font-size: 24px;">创建表

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">创建非临时表

create table student_info(StuID int not null auto_increment,StuName varchar(15) not null,Telephone bigInt not null,primary key(StuID));

> style="font-family: SimSun; font-size: 18px; color: #cc0000;">注: > > > style="color: red;">1 > > > > style="color: red;">, > > > > style="color: red;">auto_increment > > > > style="color: red;">属性的对象只能是整形,在进行值插入时若不指定具体的值,或指定其为 > > > > style="color: red;">null > > > > style="color: red;">或 > > > > style="color: red;">0 > > > > style="color: red;">,则其自动赋值为当前列的最大值再加上 > > > > style="color: red;">1 > > > > style="color: red;">的值。 > > > style="color: red;">2 > > > > style="color: red;">, > > > > style="color: red;">primary key() > > > > style="color: red;">是设置主键 > > > > style="color: red;">, > > > > style="color: red;">一个表可以不止一个主键。

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">创建临时表

create temporary table Student_info(StuID int not null auto_increment,StuName varchar(15) not null,Telephone bigInt not null,primary key(StuID));

> style="color: red;">注:创建临时表要用关键字 > > > style="color: red;">”temporary” > > > style="color: red;">,临时表主要是用于保存一些临时数据,临时表的数据只是在当前连接可用,当断开链接后就会, > > > style="color: red;">MySQL > > > style="color: red;">就会自动删除临时表。

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">创建复制旧表的数据和结构的新表

create table new_table select * from old_table;

> style="color: red;">注:这种方法会将旧表中的所有内容都拷贝过来,当然我们也可以用 > > > style="color: red;">delete > > > style="color: red;">、insert into > > > style="color: red;">来删除、增加。不过这种方法最不好的地方就是没有了旧表的 > > > style="color: red;">primary key > > > style="color: red;">, > > > style="color: red;">extra(auto_incremet) > > > style="color: red;">等属性。需要自己用 > > > style="color: red;">alter > > > style="color: red;">手动添加。

style="background-color: #ff0000; color: #ffcc00; font-family: SimSun; font-size: 18px;">创建复制旧表的结构的新表

create table new_table select * from old_table where 1 = 2;

> style="color: red;">注:这种方法只是复制表结构,当然旧表中的 > > > style="color: red;">primary key > > > style="color: red;">  > > > style="color: red;">和 > > > style="color: red;">auto_increment > > > style="color: red;">等属性都是复制过来了。

5,TCL(Tranlasion Control Language):事物控制语言,主要由commit、rollback和savePoint关键字组成。

  • 文件->添加新项目->Visual C#->类库:(将控件修改为能够自动感知权限的控件)

 

五、查询数据 

desc

show

 

子查询

• 在查询时基于未知的值时,应使用子查询

• 子查询(内查询) style="color: #ff0000;">在主查询之前一次执行完成

• 子查询的结果被主查询(外查询)使用

• 子查询要包含在括号内

• 将子查询放在比较条件的右侧

• style="color: #ff0000;">单行操作符对应单行子查询,多行操作符对应多行子查询(注意理解ANY和ALL的区别)

    SELECT select_list

    FROM table

    WHERE expr operator  style="color: #ff0000;">(

                                     SELECT select_list

style="color: #ff0000;">                                     FROM table

style="color: #ff0000;">                                    );

澳门新萄京官方网站 15

澳门新萄京官方网站 16

 

高级子查询

<例子说明一切!>

  1. 多列子查询

   style="color: #00ff00;">--查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id, manager_id, department_id

   --不成对比较
   select employee_id, manager_id, department_id
   from employees
   where manager_id in(
                     select manager_id
                     from employees
                     where employee_id in(141,174)
                    )
   and department_id in(
                     select department_id
                     from employees
                     where employee_id in(141,174)
                    )
   and employee_id not in(141,174)

   style="color: #0000ff;">--成对比较

   select employee_id, manager_id, department_id
   from employees
   where style="color: #ff0000;">(manager_id,department_id) in(
                     select style="color: #ff0000;">manager_id,department_id   style="color: #00ff00;">--多列子查询
                     from employees
                     where employee_id in(141,174)
                    )
   and employee_id not in(141,174)

  1. 在FROM子句中使用子查询

  style="color: #00ff00;">--返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资

   select last_name, e.department_id, salary,temp.avg_sal
   from employees e,(
                  select department_id,avg(salary) avg_sal
                  from employees
                  group by department_id
                  ) temp
   where e.department_id = temp.department_id

  1. 单列子查询 单列子查询表达式是在一行中只返回一列的子查询

  • Oracle8i 只在下列情况下可以使用, 例如:

     – SELECT 语句 (FROM 和 WHERE 子句)

     – INSERT 语句中的VALUES列表中

  • Oracle9i中单列子查询表达式可在下列情况下使用:

     – DECODE 和 CASE

     – SELECT 中除 GROUP BY 子句以外的所有子句中

  style="color: #00ff00;">--显示员工的employee_id,last_name和location。其中,若员工department_id与location_id为1800的department_id相同,则location为’Canada’,其余则为’USA’

   SELECT employee_id, last_name,

   (CASE

   WHEN department_id =

                               (SELECT department_id FROM departments

                                WHERE location_id = 1800)

   THEN 'Canada' ELSE 'USA' END) location

   FROM employees;

  1. style="color: #ff0000;">相关子查询

什么是相关?

先来举个栗子!

   style="color: #00ff00;">--查询员工的employee_id,last_name,要求按照员工的department_name排序

   SELECT employee_id, last_name

   FROM employees style="color: #0000ff;">e

   ORDER BY  (SELECT department_name

                     FROM departments d

                     style="color: #ff0000;">WHERE style="color: #0000ff;">e.department_id = d.department_id); style="color: #00ff00;">--首先看内层子查询使用了外层查询的表,并通过WHERE语句使他俩产生关系,这种就叫相关子查询

style="color: #666666;">如上语句的执行步骤是这样的:先从e表中select一条数据,然后执行子查询,子查询用外查询select的数据进行操作返回一条结果,然后外层查询再用这个结果来执行order by,这样主查询和子查询就发生了相关性!如图:

澳门新萄京官方网站 17

  1. EXISTS操作符

  • EXISTS 操作符检查在子查询中是否存在满足条件的行

  • 如果在子查询中存在满足条件的行:

     – 不在子查询中继续查找

     – 条件返回 TRUE

  • 如果在子查询中不存在满足条件的行:

     – 条件返回 FALSE

     – 继续在子查询中查找

   style="color: #00ff00;">--查询公司管理者的employee_id,last_name,job_id,department_id信息

   SELECT employee_id, last_name, job_id, department_id

   FROM employees outer

   WHERE style="color: #ff0000;">EXISTS ( SELECT 'X'

                            FROM employees

                            WHERE manager_id = outer.employee_id);

   澳门新萄京官方网站 18

   --查询departments表中,不存在于employees表中的部门的department_id和department_name

   SELECT department_id, department_name

   FROM departments d

   WHERE style="color: #ff0000;">NOT EXISTS ( SELECT 'X'

                                   FROM employees

                                   WHERE department_id = d.department_id);

   澳门新萄京官方网站 19

  1. 相关更新、删除

  使用相关子查询依据一个表中的数据更新、删除另一个表的数据

   UPDATE table1 alias1

   SET column = (SELECT expression

                         FROM table2 alias2

                         WHERE alias1.column = alias2.column);

   DELETE FROM table1 alias1

   WHERE column operator

                             (SELECT expression

                              FROM table2 alias2

                              WHERE alias1.column = alias2.column);

  1. WITH子句

   • 使用 WITH 子句, 可以避免在 SELECT 语句中重复书写相同的语句块

   • WITH 子句将该子句中的语句块执行一次并存储到用户的临时表空间中

   • 使用 WITH 子句可以提高查询效率

  --查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息

   style="color: #ff0000;">WITH
   dept_costs AS (

                          SELECT d.department_name, SUM(e.salary) AS dept_total

                          FROM employees e, departments d

                          WHERE e.department_id = d.department_id

                          GROUP BY d.department_name),

   avg_cost AS (

                        SELECT SUM(dept_total)/COUNT(*) AS dept_avg

                        FROM dept_costs)

   SELECT *

   FROM dept_costs

   WHERE dept_total >

                      (

                        SELECT dept_avg

                        FROM avg_cost)

   ORDER BY department_name;

六、删除数据 

style="font-family: SimSun; font-size: 18px; color: #3366ff;">查看表结构

style="font-family: SimSun; font-size: 18px; background-color: #ffffff;">desc table_name;

style="font-family: SimSun; font-size: 24px; color: #3333ff; background-color: #ffffff;">查看数据库

show databases;

style="font-family: SimSun; font-size: 24px; color: #3366ff;">查看表

style="font-family: SimSun; font-size: 24px; color: #3366ff; background-color: #ff0000;"> style="font-family: SimSun; font-size: 18px; color: #ffff00;">查看编码格式

style="font-family: SimSun; font-size: 24px; color: #000000;"> style="font-family: SimSun; font-size: 18px;"> style="font-family: SimSun; font-size: 18px;"> style="color: #555555; font-family: 'microsoft yahei'; font-size: 15px;">show variables like 'character%';

style="font-family: SimSun; font-size: 18px; color: #ffff00; background-color: #ff0000;">存在的所有表

show tables;

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">创建表的命令

show create table table_name;

style="font-family: SimSun; font-size: 18px; color: #ffff00; background-color: #ff0000;">表的结构

show index from table_name;

style="font-family: SimSun; font-size: 18px;">澳门新萄京官方网站 20

show columns from table_name;

style="font-family: SimSun; font-size: 18px;">澳门新萄京官方网站 21

> style="color: red;">注: > > > style="color: red;">“show columns from table_name; > > > style="color: red;">” > > > style="color: red;">和“ > > > style="color: red;">desctable_name;” > > > style="color: red;">效果是一样的。

show table status like "table_name"G

style="font-family: SimSun; font-size: 18px;">澳门新萄京官方网站 22

> style="color: red;">注: > > > style="color: red;">”G” > > > style="color: red;">是为了让数据显示更清晰易懂,也可以不设置。

style="font-family: SimSun; font-size: 24px; color: #3333ff;">查看服务器

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">服务器状态

show status;

style="font-family: SimSun; font-size: 18px; background-color: #ff0000;"> > style="color: #ffcc00;">服务器配置变量

show variables;

澳门新萄京官方网站 23澳门新萄京官方网站 24Code
 1 using System;
 2 using System.Data;
 3 using System.Configuration;
 4 using System.Collections;
 5 using System.Web;
 6 using System.Web.Security;
 7 using System.Web.UI;
 8 using System.Web.UI.WebControls;
 9 using System.Web.UI.WebControls.WebParts;
10 using System.Web.UI.HtmlControls;
11 using DataControl;
12 
13 namespace DataControl
14 {
15     public class UpdateButton : System.Web.UI.WebControls.Button
16     {
17         public UpdateButton()
18         {
19             HttpCookie UserCookie = this.Context.Request.Cookies["UserCookie"];
20             if (UserCookie != null)
21             {
22                 string IsSave = UserCookie["ISUPDATE"];
23                 if (IsSave.Equals("1"))
24                 {
25                     this.Enabled = true;
26 
27                 }
28                 else
29                 {
30                     this.Enabled = false;
31 
32                 }
33 
34             }
35 
36         }
37 
38     }
39 }
40 

 

七、运算符

drop

use

 

增删改

插入数据(注意非空约束的列)

  • 手动插入

   INSERT INTO table [(column [, column...])]

   style="color: #ff0000;">VALUES (value [, value...]);

  • 从其它表中拷贝数据(不必书写VALUES子句;子查询中的列与INSERT语句中的列一一对应;)

   INSERT INTO sales_reps( style="color: #ffc000;">id, style="color: #ffff00;">name, style="color: #00ff00;">salary, style="color: #9b00d3;">commission_pct)

   SELECT style="color: #ffc000;">employee_id, style="color: #ffff00;">last_name, style="color: #00ff00;">salary, style="color: #9b00d3;">commission_pct

   FROM employees

   WHERE job_id LIKE '%REP%';

更新数据

   style="color: #ff0000;">UPDATE table

   style="color: #ff0000;">SET column = value [, column = value, ...]

   [WHERE condition];

删除数据

   style="color: #ff0000;">DELETE style="color: #ff0000;">FROM table

   [WHERE condition];

八、like语句


我的环境配置:windows 64,VS,SQLite(点击下载),System.Data.SQLite.DLL(点击下载)。 

 

 

style="font-family: SimSun; font-size: 24px; color: #3366ff;">使用数据库

use database_name;

 

 

一、新建项目,添加引用

1.在VS中新建一个控制台应用程序,如下图

澳门新萄京官方网站 25

2.添加引用

将下载的System.Data.SQLite.DLL复制到新建项目的路径下

澳门新萄京官方网站 26

在VS中找到项目,右键选择添加引用

澳门新萄京官方网站 27

浏览到dll路径下,添加进来。

澳门新萄京官方网站 28

代码中添加

using System.Data.SQLite;

添加类库CSQLiteHelper,用于存放SQLite操作方法(此代码原文链接. )

澳门新萄京官方网站 29

 

具体代码

澳门新萄京官方网站 30澳门新萄京官方网站 31

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 using System.Data.SQLite;
  6 using System.Data;
  7 using System.Xml;
  8 using System.Text.RegularExpressions;
  9 using System.IO;
 10 
 11 namespace CSharp_SQLite
 12 {
 13     public class CSQLiteHelper
 14     {
 15         private string _dbName = "";
 16         private SQLiteConnection _SQLiteConn = null;     //连接对象
 17         private SQLiteTransaction _SQLiteTrans = null;   //事务对象
 18         private bool _IsRunTrans = false;        //事务运行标识
 19         private string _SQLiteConnString = null; //连接字符串
 20         private bool _AutoCommit = false; //事务自动提交标识
 21 
 22         public string SQLiteConnString
 23         {
 24             set { this._SQLiteConnString = value; }
 25             get { return this._SQLiteConnString; }
 26         }
 27 
 28         public CSQLiteHelper(string dbPath)
 29         {
 30             this._dbName = dbPath;
 31             this._SQLiteConnString = "Data Source="   dbPath;
 32         }
 33 
 34         /// <summary>
 35         /// 新建数据库文件
 36         /// </summary>
 37         /// <param name="dbPath">数据库文件路径及名称</param>
 38         /// <returns>新建成功,返回true,否则返回false</returns>
 39         static public Boolean NewDbFile(string dbPath)
 40         {
 41             try
 42             {
 43                 SQLiteConnection.CreateFile(dbPath);
 44                 return true;
 45             }
 46             catch (Exception ex)
 47             {
 48                 throw new Exception("新建数据库文件"   dbPath   "失败:"   ex.Message);
 49             }
 50         }
 51 
 52 
 53         /// <summary>
 54         /// 创建表
 55         /// </summary>
 56         /// <param name="dbPath">指定数据库文件</param>
 57         /// <param name="tableName">表名称</param>
 58         static public void NewTable(string dbPath, string tableName)
 59         {
 60 
 61             SQLiteConnection sqliteConn = new SQLiteConnection("data source="   dbPath);
 62             if (sqliteConn.State != System.Data.ConnectionState.Open)
 63             {
 64                 sqliteConn.Open();
 65                 SQLiteCommand cmd = new SQLiteCommand();
 66                 cmd.Connection = sqliteConn;
 67                 cmd.CommandText = "CREATE TABLE "   tableName   "(Name varchar,Team varchar, Number varchar)";
 68                 cmd.ExecuteNonQuery();
 69             }
 70             sqliteConn.Close();
 71         }
 72         /// <summary>
 73         /// 打开当前数据库的连接
 74         /// </summary>
 75         /// <returns></returns>
 76         public Boolean OpenDb()
 77         {
 78             try
 79             {
 80                 this._SQLiteConn = new SQLiteConnection(this._SQLiteConnString);
 81                 this._SQLiteConn.Open();
 82                 return true;
 83             }
 84             catch (Exception ex)
 85             {
 86                 throw new Exception("打开数据库:"   _dbName   "的连接失败:"   ex.Message);
 87             }
 88         }
 89 
 90         /// <summary>
 91         /// 打开指定数据库的连接
 92         /// </summary>
 93         /// <param name="dbPath">数据库路径</param>
 94         /// <returns></returns>
 95         public Boolean OpenDb(string dbPath)
 96         {
 97             try
 98             {
 99                 string sqliteConnString = "Data Source="   dbPath;
100 
101                 this._SQLiteConn = new SQLiteConnection(sqliteConnString);
102                 this._dbName = dbPath;
103                 this._SQLiteConnString = sqliteConnString;
104                 this._SQLiteConn.Open();
105                 return true;
106             }
107             catch (Exception ex)
108             {
109                 throw new Exception("打开数据库:"   dbPath   "的连接失败:"   ex.Message);
110             }
111         }
112 
113         /// <summary>
114         /// 关闭数据库连接
115         /// </summary>
116         public void CloseDb()
117         {
118             if (this._SQLiteConn != null && this._SQLiteConn.State != ConnectionState.Closed)
119             {
120                 if (this._IsRunTrans && this._AutoCommit)
121                 {
122                     this.Commit();
123                 }
124                 this._SQLiteConn.Close();
125                 this._SQLiteConn = null;
126             }
127         }
128 
129         /// <summary>
130         /// 开始数据库事务
131         /// </summary>
132         public void BeginTransaction()
133         {
134             this._SQLiteConn.BeginTransaction();
135             this._IsRunTrans = true;
136         }
137 
138         /// <summary>
139         /// 开始数据库事务
140         /// </summary>
141         /// <param name="isoLevel">事务锁级别</param>
142         public void BeginTransaction(IsolationLevel isoLevel)
143         {
144             this._SQLiteConn.BeginTransaction(isoLevel);
145             this._IsRunTrans = true;
146         }
147 
148         /// <summary>
149         /// 提交当前挂起的事务
150         /// </summary>
151         public void Commit()
152         {
153             if (this._IsRunTrans)
154             {
155                 this._SQLiteTrans.Commit();
156                 this._IsRunTrans = false;
157             }
158         }
159 
160         
161     }
162 }

View Code

 

 

 此时运行会报错,

警告  所生成项目的处理器架构“MSIL”与引用“System.Data.SQLite”的处理器架构“x86”不匹配。这种不匹配可能会导致运行时失败。请考虑通过配置管理器更改您的项目的目标处理器架构,以使您的项目与引用间的处理器架构保持一致,或者为引用关联一个与您的项目的目标处理器架构相符的处理器架构。 

修改项目属性:x86。

澳门新萄京官方网站 32

再次运行,无误。


 

style="font-size: 18px;">撤销数据库

create

  • 如何调用权限管控代码(文件->新建网站)

创建管理表

style="color: #ff0000;">以下这些DDL的命令, style="color: #ff0000;">操作完后 style="color: #ff0000;">皆不可回滚!

创建表

  • 白手起家自己动手创建表

   CREATE TABLE [schema.]table

   (column datatype [DEFAULT expr][, ...]);

  • 长袖善舞咱依据他表创建新表

   CREATE TABLE table

   [(column, column...)]

   AS subquery;

     --复制现有表(包含数据)

     create table emp1 as select * from employees;

     --复制现有表结构

     create table emp2 as select * from employees where 1=2;

修改表

  • 追加新的列

   ALTER TABLE table

   style="color: #ff0000;">ADD (column datatype [DEFAULT expr]

   [, column datatype]...);

  • 修改现有的列/为新追加的列定义默认值

   ALTER TABLE table

   style="color: #ff0000;">MODIFY (column datatype [DEFAULT expr]

   [, column datatype]...);

  • 删除一个列

   ALTER TABLE table

   style="color: #ff0000;">DROP COLUMN column_name;

  • 重命名表的一个列名

   ALTER TABLE table_name style="color: #ff0000;">RENAME COLUMM old_column_name

   style="color: #ff0000;">TO new_column_name;

删除表

  • 数据和结构都被删除

  • 所有正在运行的相关事务被提交

  • 所有相关索引被删除

  • DROP TABLE 语句不能回滚

  DROP TABLE table;   

清空表

  • 删除表中所有的数据

  • 释放表的存储空间

  • TRUNCATE语句不能回滚

  • 清空表的操作也可以用DELETE语句, style="color: #ff0000;">可以回滚

  TRUNCATE TABLE table;

 二、创建数据库

 SQLite 是文件型的数据库,后缀名可以是".db3"、".db"或者“.sqlite”,甚至可以由你决定它的后缀。其中前3个类型是SQLite默认类型。

澳门新萄京官方网站 33

新建一个数据库文件,代码如下

 1         /// <summary>
 2         /// 新建数据库文件
 3         /// </summary>
 4         /// <param name="dbPath">数据库文件路径及名称</param>
 5         /// <returns>新建成功,返回true,否则返回false</returns>
 6         static public Boolean NewDbFile(string dbPath)
 7         {
 8             try
 9             {
10                 SQLiteConnection.CreateFile(dbPath);
11                 return true;
12             }
13             catch (Exception ex)
14             {
15                 throw new Exception("新建数据库文件"   dbPath   "失败:"   ex.Message);
16             }
17         }    

 


 

 

style="font-family: SimSun; font-size: 24px; color: #3366ff;">创建数据库

create database if not exists database_name;

style="color: #3366ff; font-family: SimSun; font-size: 24px;">创建表

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">创建非临时表

create table student_info(StuID int not null auto_increment,StuName varchar(15) not null,Telephone bigInt not null,primary key(StuID));

> style="font-family: SimSun; font-size: 18px; color: #cc0000;">注: > > > style="color: red;">1 > > > > style="color: red;">, > > > > style="color: red;">auto_increment > > > > style="color: red;">属性的对象只能是整形,在进行值插入时若不指定具体的值,或指定其为 > > > > style="color: red;">null > > > > style="color: red;">或 > > > > style="color: red;">0 > > > > style="color: red;">,则其自动赋值为当前列的最大值再加上 > > > > style="color: red;">1 > > > > style="color: red;">的值。 > > > style="color: red;">2 > > > > style="color: red;">, > > > > style="color: red;">primary key() > > > > style="color: red;">是设置主键 > > > > style="color: red;">, > > > > style="color: red;">一个表可以不止一个主键。

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">创建临时表

create temporary table Student_info(StuID int not null auto_increment,StuName varchar(15) not null,Telephone bigInt not null,primary key(StuID));

> style="color: red;">注:创建临时表要用关键字 > > > style="color: red;">”temporary” > > > style="color: red;">,临时表主要是用于保存一些临时数据,临时表的数据只是在当前连接可用,当断开链接后就会, > > > style="color: red;">MySQL > > > style="color: red;">就会自动删除临时表。

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">创建复制旧表的数据和结构的新表

create table new_table select * from old_table;

> style="color: red;">注:这种方法会将旧表中的所有内容都拷贝过来,当然我们也可以用 > > > style="color: red;">delete > > > style="color: red;">、insert into > > > style="color: red;">来删除、增加。不过这种方法最不好的地方就是没有了旧表的 > > > style="color: red;">primary key > > > style="color: red;">, > > > style="color: red;">extra(auto_incremet) > > > style="color: red;">等属性。需要自己用 > > > style="color: red;">alter > > > style="color: red;">手动添加。

style="background-color: #ff0000; color: #ffcc00; font-family: SimSun; font-size: 18px;">创建复制旧表的结构的新表

style="background-color: #ffffff; color: #000000; font-family: SimSun; font-size: 18px;"> > style="font-family: SimSun; font-size: 18px;">  create table new_table select * from old_table where 1 = 2;

style="background-color: #ffffff; color: #000000; font-family: SimSun; font-size: 18px;"> > style="font-family: SimSun; font-size: 18px;">     > style="font-size: 18px;"> > style="color: red;">注:这种方法只是复制表结构,当然旧表中的 > style="color: red;">primary key > style="color: red;"> 和 > style="color: red;">auto_increment > style="color: red;">等属性都是复制过来了。

style="background-color: #ff0000; color: #ffcc00; font-family: SimSun; font-size: 18px;"> > style="color: #ffcc00; font-family: SimSun; font-size: 18px;">创建表时指定引擎和编码方式

style="font-family: SimSun; font-size: 18px;">  create table new_table(id int not null,name varchar(20) not null)engine=InnoDB,default charset=UTF-8;

style="font-family: SimSun; font-size: 18px;">     > style="color: #ff0000;">注:因为默认的引擎就是InnoDB,默认的编码方式就是utf-8,所以如果不改变引擎和编码方式可以不添加engine=InnoDB,default charset=utf-8

style="font-family: SimSun; font-size: 18px; color: #00ffff; background-color: #ff0000;"> > style="font-family: SimSun; font-size: 18px;"> > style="font-family: SimSun; font-size: 18px;"> > style="font-family: SimSun; font-size: 18px;">创建表的时候,指定默认的值

style="font-family: SimSun; font-size: 18px;">  create table new_table(id int not null,name varchar(20) not null,salary int not null default 1200)engine=InnoDB,default charset=UTF8;

style="font-family: SimSun; font-size: 18px;">    注:salary int not null default 1200,表示插入所有行中salary的默认值都是1200。

       在Web.Config文件中添加调用代码:

 

三、创建表

 1         /// <summary>
 2         /// 创建表
 3         /// </summary>
 4         /// <param name="dbPath">指定数据库文件</param>
 5         /// <param name="tableName">表名称</param>
 6         static public void NewTable(string dbPath, string tableName)
 7         {
 8 
 9             SQLiteConnection sqliteConn = new SQLiteConnection("data source="   dbPath);
10             if (sqliteConn.State != System.Data.ConnectionState.Open)
11             {
12                 sqliteConn.Open();
13                 SQLiteCommand cmd = new SQLiteCommand();
14                 cmd.Connection = sqliteConn;
15                 cmd.CommandText = "CREATE TABLE "   tableName   "(Name varchar,Team varchar, Number varchar)";
16                 cmd.ExecuteNonQuery();
17             }
18             sqliteConn.Close();
19         }

 

 

 

例子:创建一个数据库文件  NBA.db3

然后创建表Stars,再创建表的列,Name,Team,Number。

 1 class Program
 2     {
 3         private static string dbPath = @"d:NBA.db3";
 4         static void Main(string[] args)
 5         {
 6             //创建一个数据库db文件
 7             CSQLiteHelper.NewDbFile(dbPath);   
 8             //创建一个表
 9             string tableName = "Stars";
10             CSQLiteHelper.NewTable(dbPath, tableName);            
11         }
12     }

 

看下效果,数据库文件NBA的表Stars中有Name,Team和Number字段

 澳门新萄京官方网站 34

 


 

drop database databasename;

 

 

澳门新萄京官方网站 35澳门新萄京官方网站 36 id="Code_Closed_Text_143609" class="cnblogs_code_Collapse" style="display: none">Code style="display: inline">
< style="color: #800000">system.web style="color: #0000ff">>
< style="color: #800000">httpModules style="color: #0000ff">>
< style="color: #800000">add  style="color: #ff0000">name style="color: #0000ff">="UserControl" style="color: #ff0000"> type style="color: #0000ff">="HM.UserControl,SQMSHttpModule" style="color: #0000ff">/>
    </ style="color: #800000">httpModules style="color: #0000ff">>
</ style="color: #800000">system.web style="color: #0000ff">>

 

在页面中调用自动感知权限的控件:

 

澳门新萄京官方网站 37澳门新萄京官方网站 38 id="Code_Closed_Text_144517" class="cnblogs_code_Collapse" style="display: none">Code style="display: inline">
style="color: #000000; background-color: #ffff00"><% style="color: #000000; background-color: #f5f5f5">@ Page Language style="color: #000000; background-color: #f5f5f5">= style="color: #800000; background-color: #f5f5f5">" style="color: #800000; background-color: #f5f5f5">C# style="color: #800000; background-color: #f5f5f5">" style="color: #000000; background-color: #f5f5f5"> AutoEventWireup style="color: #000000; background-color: #f5f5f5">= style="color: #800000; background-color: #f5f5f5">" style="color: #800000; background-color: #f5f5f5">true style="color: #800000; background-color: #f5f5f5">" style="color: #000000; background-color: #f5f5f5"> CodeFile style="color: #000000; background-color: #f5f5f5">= style="color: #800000; background-color: #f5f5f5">" style="color: #800000; background-color: #f5f5f5">Defaule.aspx.cs style="color: #800000; background-color: #f5f5f5">" style="color: #000000; background-color: #f5f5f5"> Inherits style="color: #000000; background-color: #f5f5f5">= style="color: #800000; background-color: #f5f5f5">" style="color: #800000; background-color: #f5f5f5">Defaule style="color: #800000; background-color: #f5f5f5">" style="color: #000000; background-color: #f5f5f5">  style="color: #000000; background-color: #ffff00">%> style="color: #000000">
<!-- style="color: #008000">注册权限感知控件代码 style="color: #008000">-->
style="color: #000000; background-color: #ffff00"><% style="color: #000000; background-color: #f5f5f5">@ Register TagPrefix style="color: #000000; background-color: #f5f5f5">= style="color: #800000; background-color: #f5f5f5">" style="color: #800000; background-color: #f5f5f5">Authority style="color: #800000; background-color: #f5f5f5">" style="color: #000000; background-color: #f5f5f5"> Namespace style="color: #000000; background-color: #f5f5f5">= style="color: #800000; background-color: #f5f5f5">" style="color: #800000; background-color: #f5f5f5">DataControl style="color: #800000; background-color: #f5f5f5">" style="color: #000000; background-color: #f5f5f5"> Assembly style="color: #000000; background-color: #f5f5f5">= style="color: #800000; background-color: #f5f5f5">" style="color: #800000; background-color: #f5f5f5">DataControl style="color: #800000; background-color: #f5f5f5">" style="color: #000000; background-color: #f5f5f5">  style="color: #000000; background-color: #ffff00">%> style="color: #000000">
<! style="color: #ff00ff">DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "" style="color: #0000ff">>

< style="color: #800000">html  style="color: #ff0000">xmlns style="color: #0000ff">="" style="color: #ff0000">  style="color: #0000ff">>
< style="color: #800000">head  style="color: #ff0000">runat style="color: #0000ff">="server" style="color: #0000ff">>
    < style="color: #800000">title style="color: #0000ff">></ style="color: #800000">title style="color: #0000ff">>
</ style="color: #800000">head style="color: #0000ff">>
< style="color: #800000">body style="color: #0000ff">>
    < style="color: #800000">form  style="color: #ff0000">id style="color: #0000ff">="form1" style="color: #ff0000"> runat style="color: #0000ff">="server" style="color: #0000ff">>
    < style="color: #800000">div  style="color: #0000ff">>
    < style="color: #800000">Authority:UpdateButton  style="color: #ff0000">ID style="color: #0000ff">="btnChange" style="color: #ff0000"> runat style="color: #0000ff">="server" style="color: #ff0000"> Text style="color: #0000ff">="Change The System Description" style="color: #ff0000">   style="color: #0000ff">/>

    </ style="color: #800000">div style="color: #0000ff">>
    </ style="color: #800000">form style="color: #0000ff">>
</ style="color: #800000">body style="color: #0000ff">>
</ style="color: #800000">html style="color: #0000ff">>

 

Notices: 请不要在有登录页的模块中调用HttpModule,否则会在登录页面造成跳转死循环。

事务管理 COMMIT ROLLBACK SAVEPOINT

style="color: #666666;">首先来看看事务:一组逻辑操作单元,使数据从一种状态变换为另一种状态。由以下部分组成:

   • 一个或多个DML 语句

   • 一个 DDL语句

   • 一个 DCL语句

一图胜千言!

澳门新萄京官方网站 39

提交或回滚前的数据状态

   • 改变前的数据状态是可以恢复的

   • 执行 DML 操作的用户可以通过 SELECT 语句查询之前的修正

   • style="color: #ff0000;">其他用户不能看到当前用户所做的改变,直到当前用户结束事务

   • DML语句所涉及到的行被锁定, 其他用户不能操作

提交后的数据状态

   • 数据的改变已经被保存到数据库中

   • 改变前的数据已经丢失

   • 所有用户可以看到结果

   • style="color: #ff0000;">锁被释放,其他用户可以操作涉及到的数据

   • 所有保存点被释放

四、插入数据

 接下来,使用SQLite 的 INSERT INTO 语句用于向数据库的某个表中添加新的数据行。

先在Main()方法中创建一些数据。

1 List<object[]> starsDatas = new List<object[]>();
2 starsDatas.Add(new object[] { "Garnett", "Timberwolves", "21" });
3 starsDatas.Add(new object[] { "Jordan", "Bulls", "23" });
4 starsDatas.Add(new object[] { "Kobe", "Lakers", "24" });
5 starsDatas.Add(new object[] { "James", "Cavaliers", "23" });
6 starsDatas.Add(new object[] { "Tracy", "Rockets", "1" });
7 starsDatas.Add(new object[] { "Carter", "Nets", "15" });

将数据插入到表单中

 1 private static void AddStars(List<object[]> stars)
 2 {
 3     CSQLiteHelper sqlHelper = new CSQLiteHelper(dbPath);
 4     sqlHelper.OpenDbConn();
 5     string commandStr = "insert into Stars(Name,Team,Number) values(@name,@team,@number)";
 6     foreach (var item in stars)
 7     {
 8         if (isExist("Name", item[0].ToString()))
 9         {
10             Console.WriteLine(item[0]   "的数据已存在!");
11             continue;
12         }
13         else
14         {
15             sqlHelper.ExecuteNonQuery(commandStr, item);
16             Console.WriteLine(item[0]   "的数据已保存!");
17             Console.ReadKey();
18         }
19     }
20     sqlHelper.CloseDbConn();
21     Console.ReadKey();
22             
23 }

 

效果如下:

澳门新萄京官方网站 40

 


 

 

 

 

五、查询数据

SQLite 的 SELECT 语句用于从 SQLite 数据库表中获取数据,以结果表的形式返回数据。这些结果表也被称为结果集。

一个简单的例子:查询一个球星所在的球队。

输入球星的名字,输出球队。

 1 private static void Team(string name)
 2 {
 3     CSQLiteHelper sqliteHelper = new CSQLiteHelper(dbPath);
 4     sqliteHelper.OpenDbConn();
 5     string commandText = @"select * from Stars where Name ='"   name "'";            
 6     DataTable dt = sqliteHelper.Query(commandText).Tables[0];
 7     if (dt.Rows.Count == 0)
 8     {
 9         Console.WriteLine("查无此人!");
10         Console.ReadLine();
11         sqliteHelper.CloseDbConn();
12         return;
13     }
14     string team = dt.Rows[0]["Team"].ToString();           
15     sqliteHelper.CloseDbConn();
16     Console.WriteLine(name   "--"   team);
17     Console.ReadKey();
18 }

 

 

1 static void Main(string[] args)
2 {
3     Console.WriteLine("请输入一个Star:");
4     string name = Console.ReadLine();
5     Team(name);
6 }

 

效果如下

澳门新萄京官方网站 41

 


 

style="font-size: 18px;">撤销表

drop table if exists table_name;

style="font-family: SimSun; font-size: 18px; color: #ff0000;">注:dropexists的用法是不一样的,drop是永久删除表,而delete只是删除表中的数据。

 

约束 style="color: #ff0000;">CONSTRAINT

• NOT NULL(非空,只能是列级约束) 对INSERT语句有影响

   create table emp4(
   id number(10) constraint style="color: #ff0000;">emp2_id_nn style="color: #ff0000;">not null, --命名约束,见名知意
   name varchar2(20) not null, --系统默认命名 SYS_Cn
   salary number(10,2)
   )

 

• UNIQUE(唯一) style="color: #ff0000;">UNIQUE约束可以插入NULL值

   create table emp3(
   --列级约束
   id number(10) constraint emp3_id_uk style="color: #ff0000;">unique,
   name varchar2(20) constraint emp3_name_nn not null,
   email varchar2(30),
   salary number(10,2),
   --表级约束 在所有列定义完后加个逗号写约束
   constraint emp3_email_uk unique(email)
   )

 

• PRIMARY KEY(主键) 非空且唯一

   create table emp1(
   id number(10),
   name varchar2(20) constraint emp1_name_nn not null,
   email varchar2(20),
   salary number(10,2),
   constraint emp1_email_uk unique(email),
   constraint emp1_id_pk primary key(id)
   )

 

• FOREIGN KEY(外键) 外键是联系两张表,在子表(emp2)中插入数据时外键字段的值必须在父表(departments)中存在

   create table emp2(
   id number(10),
   name varchar2(20) constraint emp2_name_nn not null,
   email varchar2(20),
   salary number(10,2),
   department_id number(10),
   constraint emp2_email_uk unique(email),
   constraint emp2_id_pk primary key(id),
   constraint emp2_dept_id_fk foreign key(department_id) references departments(department_id)
   )

   - ON DELETE CASCADE(级联删除):当父表中的列被删除的时候,子表中相对应的列也被删除(想成某个部门被砍了,那么下面的员工也一把被砍掉了)

   - style="color: #ffc000;">ON SELETE SET NULL(级联置空):子表中相对应的列置空

 

• CHECK 定义每一行必须满足的条件

   create table emp2(
   id number(10) constraint emp2_id_pk primary key,
   name varchar2(20) constraint emp2_name_nn not null,
   salary number(10) constraint emp2_salary_ck check(salary > 1500 and salary < 30000),
   dept_id number(10),
   constraint emp2_dept_id_fk foreign key(dept_id) references dept1(id) on delete cascade
   )

 

添加删除约束

• 添加或删除约束,但是 style="color: #ff0000;">不能修改约束

   ALTER TABLE employees

   style="color: #ff0000;">ADD CONSTRAINT emp_manager_fk

   FOREIGN KEY(manager_id)

   REFERENCES employees(employee_id);

 

   ALTER TABLE employees

   style="color: #ff0000;">DROP CONSTRAINT emp_manager_fk;

 

• 有效化或 style="color: #ff0000;">无效化约束

   ALTER TABLE employees

   DISABLE CONSTRAINT emp_emp_id_pk;

 

   style="color: #00ff00;">--当定义或激活UNIQUE 或 PRIMARY KEY 约束时系统会自动创建 style="color: #ff0000;">UNIQUE 或 PRIMARY KEY索引

   ALTER TABLE employees

   ENABLE CONSTRAINT emp_emp_id_pk;

 

• 添加 NOT NULL 约束要使用 MODIFY 语句

   ALTER TABLE emp

   style="color: #ff0000;">MODIFY(emp_name varchar2(50) style="color: #ff0000;"> NOT NULL);

 

查询约束

   SELECT constraint_name, constraint_type,

   search_condition

   FROM style="color: #ff0000;">user_constraints

   WHERE table_name = 'EMPLOYEES';

   澳门新萄京官方网站 42

   --查询定义约束的列

   SELECT constraint_name, column_name

   FROM style="color: #ff0000;">user_cons_columns

   WHERE table_name = 'EMPLOYEES';

   澳门新萄京官方网站 43

 六、删除数据

SQLite 的 DELETE 语句用于删除表中已有的记录。可以使用带有 WHERE 子句的 DELETE 查询来删除选定行,否则所有的记录都会被删除。

1 private static void Delete(string name)
2 {
3     CSQLiteHelper sqliteHelper = new CSQLiteHelper(dbPath);
4     sqliteHelper.OpenDbConn();
5     string commandtext = "delete from Stars where Name = '"   name   "'";
6     sqliteHelper.ExecuteNonQuery(commandtext);
7     sqliteHelper.CloseDbConn();
8     Console.WriteLine(name   "被删除!");
9 }

注意:delete语句与select语句不同,delete后直接跟from,不能写成:

"delete * from Stars where [condition];

 调用一下

Console.WriteLine("删除球星:");
string starName = Console.ReadLine();
Delete(starName);

删除前:

澳门新萄京官方网站 44

删除后:

澳门新萄京官方网站 45

 

 

 

 

 

 七、运算符

运算符是一个保留字或字符,主要用于 SQLite 语句的 WHERE 子句中执行操作,如比较和算术运算。

运算符用于指定 SQLite 语句中的条件,并在语句中连接多个条件。

可以直接在SQLite语句中加入运算符,= - * /  %  > < = >=  <= 等等

 参考  SQLite运算符|菜鸟教程

例:

下面代码是首先插入一些NBA球员的信息,然后筛选出球衣号码大于10的球员:

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Data.SQLite;
 4 using System.Data;
 5 using System.IO;
 6 using System.Data.Common;
 7 using System.Diagnostics;
 8 
 9 
10 namespace CSharp_SQLite
11 {
12     class Program
13     {
14 
15         static void Main(string[] args)
16         {
17             string dbPath = "NBAStars.nba";
18             Stopwatch timer = new Stopwatch();
19             timer.Start();
20             CSQLiteHelper sqlhelper = new CSQLiteHelper(dbPath);
21             sqlhelper.OpenDbConn();
22             sqlhelper.BeginTransaction();
23             sqlhelper.ExecuteNonQuery("delete from Stars");
24             #region 球员信息
25             sqlhelper.ExecuteNonQuery("insert into Stars (Name,Team,Number) values('Michael Jordan','Bulls','23')");
26             sqlhelper.ExecuteNonQuery("insert into Stars (Name,Team,Number) values('James Harden','Rockets','13')");
27             sqlhelper.ExecuteNonQuery("insert into Stars (Name,Team,Number) values('Lebron James','Cavaliers','23')");
28             sqlhelper.ExecuteNonQuery("insert into Stars (Name,Team,Number) values('Tracy Mcgrady','Rockets','1')");
29             sqlhelper.ExecuteNonQuery("insert into Stars (Name,Team,Number) values('Yao Ming','Rockets','11')");
30             sqlhelper.ExecuteNonQuery("insert into Stars (Name,Team,Number) values('Kevin Garnett','Timberwolves','21')");
31             sqlhelper.ExecuteNonQuery("insert into Stars (Name,Team,Number) values('Tim Duncan','Supers','21')");
32             sqlhelper.ExecuteNonQuery("insert into Stars (Name,Team,Number) values('Vince Carter','Nets','15')");
33             sqlhelper.ExecuteNonQuery("insert into Stars (Name,Team,Number) values('Michael Carter Williams','76ers','10')");
34             #endregion
35             sqlhelper.Commit();
36             sqlhelper.CloseDbConn();
37             timer.Stop();
38             Console.WriteLine("初始化数据库表单用时:"   timer.Elapsed);
39 
40 
41             CSQLiteHelper sqlHelper = new CSQLiteHelper(dbPath);
42             sqlHelper.OpenDbConn();
43             sqlHelper.BeginTransaction();
44             DataTable dt = sqlHelper.Query("select Name, Number from Stars where  Number > 10").Tables[0];
45             sqlHelper.Commit();
46             sqlHelper.CloseDbConn();
47             Console.WriteLine("球衣号码大于10的球员有:");
48             for (int i = 0; i < dt.Rows.Count; i  )
49             {
50                 Console.WriteLine(dt.Rows[i]["Name"]   "t"   dt.Rows[i]["Number"]);
51             }
52             Console.ReadLine();
53         }
54 
55     }
56 
57 }

 

 

 结果如下:

 澳门新萄京官方网站 46

若想输出这些球员的球衣号码,则应该在select语句修改为

  DataTable dt = sqlHelper.Query("select Name, Number from Stars where Number > 10").Tables[0]; 

 将输出修改为

 Console.WriteLine(dt.Rows[i]["Name"] "t"

  • dt.Rows[i]["Number"]); 

结果如下:

澳门新萄京官方网站 47

注:select语句中尽量不要使用select * from,那样会影响效率。

 


 

delete

 

视图 style="color: #ff0000;">View

视图是干嘛使的?

   • 视图是一种虚表。

   • 视图建立在已有表的基础上, 视图赖以建立的这些表称为 style="color: #ff0000;">基表。

   • 向视图提供数据内容的语句为 SELECT 语句, 可以 style="color: #ff0000;">将视图理解为存储起来的 SELECT 语句

   • 视图向用户提供基表数据的另一种表现形式

 

视图有哪些好处?

   • 控制数据访问

   • style="color: #ff0000;">简化查询(可以关联多张表创建视图)

   • 对视图的操作会同步到基表中

   • 避免重复访问相同的数据

 

创建修改和删除视图

  create or replace view empview(id, style="color: #c0504d;">name, style="color: #00ff00;">salary, style="color: #9b00d3;">dept_name) style="color: #00ff00;">--同创建表,字段要一一对应,或此处不写明也可
   as
   select employee_id, style="color: #c0504d;">first_name||' '||last_name, style="color: #00ff00;">salary, style="color: #9b00d3;">department_name style="color: #00ff00;">--给视图的列定义了别名,在选择视图的中列时用别名
   from employees e, departments d
   where e.department_id = d.department_id

   style="color: #ff0000;">with read only style="color: #00ff00;">--可以使用 WITH READ ONLY 选项屏蔽对视图的DML操作

 

   style="color: #666666;">drop view empview;  style="color: #00ff00;">--删除视图,只是删除视图的定义,并不会删除基表的数据

 

区别简单视图和 style="color: #ff0000;">复杂视图 style="color: #ff0000;">→ 是否使用了聚合函数

   create or replace view empview3
   as
   select d.department_name name, style="color: #ff0000;">MIN(e.salary) minsal, MAX(e.salary) maxsal, AVG(e.salary) avgsal style="color: #00ff00;">--使用局和函数的列必须取别名
   from employees e,departments d
   where e.department_id = d.department_id
   group by d.department_name style="color: #00ff00;">--使用了分组函数即为复杂视图

 

style="color: #ff0000;">视图中使用DML的规定,原因也很简单,想想就清楚

• 可以在简单视图中执行 DML 操作

• 当视图定义中包含以下元素之一时不能使用delete:

   – 组函数

   – GROUP BY 子句

   – DISTINCT 关键字

   – ROWNUM 伪列

• 当视图定义中包含以下元素之一时不能使用update:

   – 组函数

   – GROUP BY子句

   – DISTINCT 关键字

   – ROWNUM 伪列

   – 列的定义为表达式

• 当视图定义中包含以下元素之一时不能使insert:

   – 组函数

   – GROUP BY 子句

   – DISTINCT 关键字

   – ROWNUM 伪列

   – 列的定义为表达式

   – 表中非空的列在视图定义中未包括

八、like语句

SQLite 的 LIKE 运算符是用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配,LIKE 运算符将返回真(true),也就是 1。这里有两个通配符与 LIKE 运算符一起使用:

  •  百分号 %
  • 下划线 _

 百分号(%)代表零个、一个或多个数字或字符。下划线(_)代表一个单一的数字或字符。这些符号可以被组合使用。

style="font-family: SimSun; font-size: 18px; color: #3366ff;">删除表数据

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">删除表的所有数据

delete from table_name;

style="color: yellow; background: red;">删除指定表中的数据

delete from table_name where column_name > value;

desc

 

语法

% 和 _ 的基本语法如下:

SELECT column_list 
FROM table_name
WHERE column LIKE 'XXXX%'

or 

SELECT column_list 
FROM table_name
WHERE column LIKE '%XXXX%'

or

SELECT column_list 
FROM table_name
WHERE column LIKE 'XXXX_'

or

SELECT column_list 
FROM table_name
WHERE column LIKE '_XXXX'

or

SELECT column_list 
FROM table_name
WHERE column LIKE '_XXXX_'

您可以使用 AND 或 OR 运算符来结合 N 个数量的条件。在这里,XXXX 可以是任何数字或字符串值。

我们在球员中筛选出名字中包含James的球员

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Data.SQLite;
 4 using System.Data;
 5 using System.IO;
 6 using System.Data.Common;
 7 using System.Diagnostics;
 8 
 9 
10 namespace CSharp_SQLite
11 {
12     class Program
13     {
14 
15         static void Main(string[] args)
16         {
17             string dbPath = "NBAStars.nba";
18             Stopwatch timer = new Stopwatch();
19             timer.Start();
20             CSQLiteHelper sqlhelper = new CSQLiteHelper(dbPath);
21             sqlhelper.OpenDbConn();
22             sqlhelper.BeginTransaction();
23             sqlhelper.ExecuteNonQuery("delete from Stars");
24             #region 球员信息
25             sqlhelper.ExecuteNonQuery("insert into Stars (Name,Team,Number) values('Michael Jordan','Bulls','23')");
26             sqlhelper.ExecuteNonQuery("insert into Stars (Name,Team,Number) values('James Harden','Rockets','13')");
27             sqlhelper.ExecuteNonQuery("insert into Stars (Name,Team,Number) values('Lebron James','Cavaliers','23')");
28             sqlhelper.ExecuteNonQuery("insert into Stars (Name,Team,Number) values('Tracy Mcgrady','Rockets','1')");
29             sqlhelper.ExecuteNonQuery("insert into Stars (Name,Team,Number) values('Yao Ming','Rockets','11')");
30             sqlhelper.ExecuteNonQuery("insert into Stars (Name,Team,Number) values('Kevin Garnett','Timberwolves','21')");
31             sqlhelper.ExecuteNonQuery("insert into Stars (Name,Team,Number) values('Tim Duncan','Supers','21')");
32             sqlhelper.ExecuteNonQuery("insert into Stars (Name,Team,Number) values('Vince Carter','Nets','15')");
33             sqlhelper.ExecuteNonQuery("insert into Stars (Name,Team,Number) values('Michael Carter Williams','76ers','10')");
34             #endregion
35             sqlhelper.Commit();
36             sqlhelper.CloseDbConn();
37             timer.Stop();
38             Console.WriteLine("初始化数据库表单用时:"   timer.Elapsed);
39 
40 
41             CSQLiteHelper sqlHelper = new CSQLiteHelper(dbPath);
42             sqlHelper.OpenDbConn();
43             sqlHelper.BeginTransaction();
44             DataTable dt = sqlHelper.Query("select Name, Number from Stars where  Name like '%James%'").Tables[0];
45             sqlHelper.Commit();
46             sqlHelper.CloseDbConn();
47             Console.WriteLine("名字中包含James的球员有:");
48             for (int i = 0; i < dt.Rows.Count; i  )
49             {
50                 Console.WriteLine(dt.Rows[i]["Name"]   "t"   dt.Rows[i]["Number"]);
51             }
52             Console.ReadLine();
53         }
54 
55     }
56 
57 }

 

其中第44行代码 DataTable dt = sqlHelper.Query("select Name, Number from Stars where Name like '%James%'").Tables[0]; 

%James%表示前面和后面包含零个、一个或多个数字或字符,也就是包含名字内James

运行结果如下

澳门新萄京官方网站 48

 

前言:

这一段来自SQLite官网

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactionalSQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private. SQLite is the most widely deployed database in the world with more applications than we can count, including several high-profile projects.

 

SQLite 是一个软件库,实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。SQLite 源代码不受版权限制。SQLite 是在世界上最广泛部署的 SQL 数据库引擎。SQLite是世界上应用最广泛的数据库,拥有着不计其数的应用,包括备受瞩目的项目。

 

 


 

 

 


 

 

style="background-color: #0000ff; color: #ffff00;">作者:自恋狂学长 style="background-color: #0000ff; color: #ffff00;">

style="background-color: #0000ff; color: #ffff00;">出处: style="background-color: #0000ff; color: #ffff00;">http://www.cnblogs.com/zhaoliankun/

style="background-color: #0000ff; color: #ffff00;">本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。

 

 

 


 

insert

style="font-family: SimSun; font-size: 18px; color: #3366ff;">查看表结构

style="font-family: SimSun; font-size: 18px; background-color: #ffffff;">desc table_name;

使用“ style="color: #ff0000;">TOP-N style="color: #333333;">”分析

这个咱一步步来解释:

首先老板说了,把公司中工资前十名的信息给他。

好!咱写出这样的语句:

select employee_id,first_name||' '||last_name name,salary,hire_date
from employees
order by salary desc

style="color: #666666;">给老板的是全公司员工的数据,这不合适,不能让他去数前十个吧!

澳门新萄京官方网站 49

那咱干不下去啦?不,事情总有解决方案!咱先引入一个新的概念: style="color: #ff0000;">伪列 ROWNUM style="color: #333333;">,它是啥呢,如图:

澳门新萄京官方网站 50

这个就叫伪列,它与一张表中数据挂钩!

有了这个那简单了!

   select style="color: #ff0000;">rownum,employee_id,first_name||' '||last_name name,salary,hire_date
   from employees
   where rownum <=10
   order by salary desc

澳门新萄京官方网站 51

但是!发现结果好像有点不对啊!这分明就不是工资最高的前十个人嘛!你特么在逗我!

那么原因呢上面我也说了这个ROWNUM是和表中数据对应的,每变更一条数据时ROWNUM都唯一对应着一条数据。

那咱就利用这个概念,既然我SELECT查询的结果集也有ROWNUM,那我就查询出排好序的数据,按结果集的ROWNUM我就可以找到前10个人啦!

   select employee_id,name,salary,hire_date
   from (  style="color: #00ff00;">--此处用到子查询
            select employee_id,first_name||' '||last_name name,salary,hire_date
            from employees
            order by salary desc
          )
   where rownum <= 10

   澳门新萄京官方网站 52

澳门新萄京官方网站 53,咱大功告成!

这时候老板又说了,你给我找到40~50名工资的有哪些人我要做报表。

接到任务很兴奋啊!这简单,把上面那个改改!

   select rownum,employee_id,name,salary,hire_date
   from (
            select employee_id,first_name||' '||last_name name,salary,hire_date
            from employees
            order by salary desc
          )
   where rownum <= 50 and rownum >= 40

   澳门新萄京官方网站 54

看到结果的我眼泪掉下来!澳门新萄京官方网站 55这特么数据呢!再一看表中是有数据的呀!

后来咱知道了,对 ROWNUM 只能使用 < 或 <=, 而用 =, >, >= 都将不能返回任何数据!

你说你咋恁多事呢!

那咱就得想其它办法啦,怎么做呢?

既然对ROWNUM这个特有的列我们不能使用=, >, >=,但是一般的列咱行啊!那咱就复制这个列呗!

   select *
   from(
            select rownum rn,employee_id,name,salary,hire_date style="color: #00ff00;">--通过将ROWNUM这个特有列取别名跟数据库关键字区别开
            from (
                      select employee_id,first_name||' '||last_name name,salary,hire_date
                      from employees
                      order by salary desc
                    )
          )
   where rn <= 50 and rn >= 40

   澳门新萄京官方网站 56

老板!你是不是要这个!

style="font-family: SimSun; font-size: 18px; color: #3366ff;">插入数据

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">插入完整元组

insert into Student_info values (10,'宋文',123456789);

style="background-color: #ff0000; color: #ffcc00; font-family: SimSun; font-size: 18px;">插入非完整元组

insert into Student_info(StuName,Telephone) values ('小吴,'123456789');

> style="font-family: SimSun; font-size: 18px;">注:在决定插入元组数据中的属性个数时,主要依据表中属性的完整性来决定。

style="font-family: SimSun; font-size: 18px; color: #3366ff;">复制表数据

style="font-family: SimSun; font-size: 18px; color: #3366ff;"> > style="color: #ffcc00; font-family: SimSun; font-size: 18px; background-color: #ff0000;">复制旧表的数据到新表

insert into 新表 select * from 旧表

> style="font-size: 18px;">注: > style="font-size: 18px;">这里两个表的结构要求要一样 > style="color: red;">这里只是复制数据,旧表的 > > > style="color: red;">primary key > > > style="color: red;">和 > > > style="color: red;">auto_increment > > > style="color: red;">还是需要自己添加。下面的方法也可以得到两个完全一样的表 > style="color: red;">方法一:首先用“ > > > style="color: red;">creat table  > > > style="color: red;">新表 like  > > > style="color: red;">旧表”将旧表的结构复制过来,然后使用“ > > > style="color: red;">insert into > > > style="color: red;">新表 select from 旧表 > > > style="color: red;">”将表中的数据复制过去。 > style="color: red;">方法二: > > > style="color: red;">show create table > > > style="color: red;">旧表*命令得到创建表的命令,复制粘贴再更改名称最后运行,得到一个数据结构和属性一样的新表 > > > style="color: red;">( > > > style="color: red;">除名称 > > > style="color: red;">) > > > style="color: red;">,再用这里 > > > style="color: red;">insert > > > style="color: red;">命令,将数据复制过去,就可以得到完全一样的表了。

style="background-color: #ff0000; color: #ffcc00; font-family: SimSun; font-size: 18px;">复制旧表的部分数据到新表

insert into 新表 (字段1,字段2......)select 字段1,,字段2,.....from 旧表

> style="font-family: SimSun; font-size: 18px;">注: > style="font-family: SimSun; font-size: 18px;">1,这里两个表的匹配字段数据类型要一致。 > style="color: red;">2,这里只是复制数据,旧表的 > > > style="color: red;">primary key > > > style="color: red;">和 > > > style="color: red;">auto_increment > > > style="color: red;">还是需要自己添加。

style="font-family: SimSun; font-size: 18px; color: #3366ff;">insert ignore into 和 insert into 的区别

INSERT IGNORE INTO与INSERT INTO的区别就是INSERT IGNORE会忽略数据库中已经存在的数据,如果数据库没有数据,就插入新的数据,如果有数据的话就跳过这条数据。INSERT IGNORE INTO当插入数据时,在设置了记录的唯一性后,如果插入重复数据,将不返回错误,只以警告形式返回。 而REPLACE INTO into如果存在primary 或 unique相同的记录,则先删除掉。再插入新记录。

drop

 

select

 

序列 style="color: #ff0000;">SEQUENCE style="color: #333333;">:可供多个用户用来产生 style="color: #ff0000;">唯一数值 style="color: #333333;">的数据库对象

• 自动提供唯一的数值

• 共享对象

• 主要用于提供主键值,唯一且非空

• 将序列值装入内存可以提高访问效率

创建序列

   CREATE style="color: #ff0000;">SEQUENCE sequence

   [INCREMENT BY n] --每次增长的数值

   [START WITH n] --从哪个值开始

   [{MAXVALUE n | style="text-decoration: underline;">NOMAXVALUE}]

   [{MINVALUE n | style="text-decoration: underline;">NOMINVALUE}]

   [{CYCLE | style="text-decoration: underline;">NOCYCLE}] --是否需要循环

   [{CACHE n | NOCACHE}]; --是否缓存登录

查询序列

   SELECT sequence_name, min_value, max_value, increment_by, last_number

   FROM style="color: #ff0000;">user_sequences style="color: #ffc000;">; style="color: #00ff00;">--如果指定NOCACHE 选项,则列LAST_NUMBER 显示序列中下一个有效的值

使用序列

   INSERT INTO departments(department_id, department_name, location_id)

   VALUES ( style="color: #ff0000;">dept_deptid_seq.NEXTVAL, 'Support', 2500);

   • NEXTVAL 返回序列中下一个有效的值,任何用户都可以引用

   • CURRVAL 中存放序列的当前值

   • NEXTVAL 应在 CURRVAL 之前指定,否则会报CURRVAL 尚未在此会话中定义的错误

   • 将序列值装入内存可提高访问效率

   • 序列在下列情况下出现裂缝:

      – 回滚 ROLLBACK

      – 系统异常

      – 多个表同时使用同一序列

   • 如果不将序列的值装入内存(NOCACHE), 可使用表 USER_SEQUENCES 查看序列当前的有效值

修改序列

   style="color: #ff0000;">ALTER style="color: #ff0000;">SEQUENCE dept_deptid_seq

   INCREMENT BY 20

   MAXVALUE 999999

   NOCACHE

   NOCYCLE;

   • 必须是序列的拥有者或对序列有 ALTER 权限

   • 只有将来的序列值会被改变

   • style="color: #ff0000;">改变序列的初始值只能通过删除序列之后重建序列的方法实现

删除序列

   DROP SEQUENCE dept_deptid_seq;

   • 使用 DROP SEQUENCE 语句删除序列

   • 删除之后,序列不能再次被引用

style="font-family: SimSun; font-size: 18px; color: #3366ff;">查看当前日期

select current_date;

style="font-family: SimSun; font-size: 18px;">澳门新萄京官方网站 57

style="font-family: SimSun; font-size: 18px; color: #3366ff;">查看服务器版本

select version();

style="font-family: SimSun; font-size: 18px;">澳门新萄京官方网站 58

style="font-family: SimSun; font-size: 18px; color: #3366ff;">查看当前数据库名称

select database();

style="font-family: SimSun; font-size: 18px; color: #3333ff;">查看当前用户名

select user();

style="font-family: SimSun; font-size: 18px; color: #3333ff;">查询表中数据的语句

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">查询表中指定字段无重复的数据

select distinct column_name from table_name wherecondition;

> style="font-family: SimSun; font-size: 18px;">注: > style="color: red;">1 > > > style="color: red;">, > > > style="color: red;">distinct > > > style="color: red;">用于显示的数据内容不存在重复 > style="color: red;">2 > > > style="color: red;">, > > > style="color: red;">* > > > style="color: red;">表示所有数据 > style="color: red;">3 > > > style="color: red;">, > > > style="color: red;">condition > > > style="color: red;">可以用大于、小于等

style="font-size: 18px; color: #3333ff;">查询数据之动态指定条件语句

 比如有两个表,分别是table1和table2,在table1中有一个id1属性,在table2中有一个id2属性。如果要查询符合id2中的id1的数据,不需要分两句书写,可以直接将其写到一起,比如:

select table1.id1 from table1 where id1=(select table2.id2 from table2 where id2=1);

必须注意:在条件语句中的得出table2的数据只能是一个明确的数值,不能是一个数组。

连接查询

style="font-size: 18px; color: #ffff00; background-color: #ff0000;">语法:

select 属性 from 表1,表2 where (表1.属性 = 表2.属性);

注:“表1.属性 = 表2.属性” 是很重要的,它表示两个表连接的条件。

style="font-size: 18px; color: #ffff00; background-color: #ff0000;">简单连接查询

select * from student_info,test_table where( StuID >2) and(StuID < 5) and (student_info.StuID = test_table.test_StuID);

> style="color: red;">注:查询Student_info表中StuIDtestable表中tesStuID的值相同的所有数据,并且满足Student_info中的StuID在2到5的条件。

style="font-size: 18px;">澳门新萄京官方网站 59

style="font-size: 18px;">澳门新萄京官方网站 60

style="font-size: 18px;">撤销数据库

 

 

 

索引 style="color: #ff0000;">INDEX

• 一种独立于表的模式对象, 可以存储在与表不同的磁盘或表空间中

• 索引被删除或损坏, style="color: #ff0000;">不会对表产生影响, 其影响的只是查询的速度

• 索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 style="color: #ff0000;">Oracle 管理系统决定何时使用索引。 style="color: #ff0000;">用户不用在查询语句中指定使用哪个索引

• 在 style="color: #ff0000;">删除一个表时,所有基于该表的索引会自动被删除

• 通过指针加速 Oracle 服务器的查询速度

• 通过快速定位数据的方法,减少磁盘 I/O

• 索引加速查询,但是插入删除操作会变慢,因为要维护索引表

创建索引

   • 自动创建: 在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引

   • 手动创建: 用户可以在其它列上创建非唯一的索引,以加速查询

      style="color: #ff0000;">CREATE INDEX index

      style="color: #ff0000;">ON table (column[, column]...);

   • 以下情况可以创建索引:

      – 列中数据值分布范围很广

      – 列经常在 WHERE 子句或连接条件中出现

      – 表经常被访问而且数据量很大,访问的数据大概占数据总量的2%到4%

   • 什么时候不要创建索引

      – 表很小

      – 列不经常作为连接条件或出现在WHERE子句中

      – 查询的数据大于2%到4%

      – 表经常更新

查询索引

   SELECT ic.index_name, ic.column_name, ic.column_position col_pos,ix.uniqueness

   FROM style="color: #ff0000;">user_indexes ix, style="color: #ff0000;">user_ind_columns ic

   WHERE ic.index_name = ix.index_name AND ic.table_name = 'EMPLOYEES';

删除索引

   DROP INDEX index;

   • 只有索引的拥有者或拥有DROP ANY INDEX 权限的用户才可以删除索引

   • 删除操作是不可回滚的

自连接查询

style="font-size: 14px; color: #000000; background-color: #ff0000;"> style="font-size: 18px; color: #ffff33;">语法:

  使用别名

drop database databasename;

 

例如:

 

同义词 style="color: #ff0000;">SYNONYM

• 方便访问其它用户的对象

• 缩短对象名字的长度

创建同义词

   style="color: #ff0000;">CREATE style="color: #ff0000;">[PUBLIC] style="color: #ff0000;">SYNONYM synonym

   style="color: #ff0000;">FOR object; style="color: #00ff00;">–object可以使表、视图等

删除同义词

   DROP SYNONYM style="color: #ffc000;">d_sum;

澳门新萄京官方网站 61

打印这个表中所有的id,name,但是这个name对应的id至少有一次出现在manager_id中(如果把id看成员工编号,manager_id看成员工上司的id号,那么请打印所有的管理层)。

select distinct z.id,z.name from zilianjie z,zilianjie j where j.manager_id=z.id;

 

超连接查询

style="font-size: 18px; color: #ffff33; background-color: #ff0000;">语法:

内部连接:select 属性1,属性2....... from 表1 inner join 表2 on 表1.属性 = 表2.属性

左连接: select属性1,属性2....... from 表1 left join 表2 on 表1.属性 = 表2.属性

右连接 : select 属性1,属性2....... from 表1 left join 表2 on 表1.属性 = 表2.属性

内部连接

inner join 形式的连接称为内部连接,也可以写为join。内部连接表示只有满足条件的记录才会显示到查询结果中

select * from student_info inner join test_table on student_info.StuID = test_table.test_StuID;

这个语句等价于select * from student_info,test_table where(student_info.StuID =test_table.test_StuID);

style="background-color: #ffffff;">澳门新萄京官方网站 62

style="background-color: #ffffff;">澳门新萄京官方网站 63

 

 

左连接

left join 形式的连接称为左连接。查询结果包含join左侧表中的所有记录以及右侧表满足条件的记录

右连接

right join 形式的连接称为右连接。查询结果包含join右侧表中的所有记录以及左侧表中满足条件的记录

并运算查询

语法

union表示并运算

style="background-color: #ff0000; color: #ffff33; font-size: 18px; font-family: SimSun;">实例

style="background-color: #ffffff;">将两个表中满足条件的数据显示

select StuID,StuName,Telephone from student_info where StuName = "第三个名称"

union

select test_StuID,test_Name from test_table where test_StuID= 4;

style="background-color: #ffffff;">澳门新萄京官方网站 64

style="font-family: SimSun; font-size: 18px; color: #ff9966;">注:这里两个表需要查询出来的属性数目要一致,否则就会出现“ERROR 1222 (21000): The used SELECT statements have a different number of columns”的错误。

style="font-size: 18px;">撤销表

drop table if exists table_name;

style="font-family: SimSun; font-size: 18px; color: #ff0000;">注:dropexists的用法是不一样的,drop是永久删除表,而delete只是删除表中的数据。

update

 

style="font-family: SimSun; font-size: 18px; color: #3333ff;">更新部分数据

update table_name set StuName = "易建联" where StuID = 1;

style="font-family: SimSun; font-size: 18px;">澳门新萄京官方网站 65

style="font-family: SimSun; font-size: 18px; color: #3333ff;">更新整列的值

update table_name set Telephone= Telephone 1;

style="font-family: SimSun; font-size: 18px;">澳门新萄京官方网站 66

delete

alter

style="font-family: SimSun; font-size: 18px; color: #3366ff;">删除表数据

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">删除表的所有数据

delete from table_name;

style="color: yellow; background: red;">删除指定表中的数据

delete from table_name where column_name > value;

style="font-family: SimSun; font-size: 18px; color: #3333ff;">更改表

style="font-family: SimSun; font-size: 18px; color: #ffff00; background-color: #ff0000;">在表中增加一个属性

alter table 表 add column 属性 数据类型 [列的完整性];

style="background-color: #ff0000; color: #ffcc00; font-family: SimSun; font-size: 18px;">改变表中某个属性

alter table 表 change 属性 属性 数据类型 [列的完整性];

style="background-color: #ff0000; color: #ffff00; font-family: SimSun; font-size: 18px;">撤销表中的某个属性

alter table 表 drop 属性;

style="background-color: #ff0000; color: #ffff33; font-family: SimSun; font-size: 18px;">更改表的名称

alter table 旧表名 rename 新表名;

style="background-color: #ff0000; color: #ffff33; font-family: SimSun; font-size: 18px;">撤销和更改表的主键

alter table 表 drop primary key,add primary key(属性);

style="background-color: #ff0000; color: #ffff33; font-family: SimSun; font-size: 18px;">在第一行添加属性

alter table table_name add I [….]first;

> style="color: red;">注: > style="color: red;">这里的 > > > style="color: red;">first > > > style="color: red;">表示在第一行添加属性 > > > style="color: red;">I > style="color: red;">[….] > > > style="color: red;">用于表示列的完整性 > style="color: red;">比如: > style="color: red;">alter table userinfo add userAge varchar(2) not null first;

style="font-family: SimSun; font-size: 18px; color: #ffff33; background-color: #ff0000;">在属性I后面添加J属性

alter table table_name add I […] after J;

> style="font-family: SimSun; font-size: 18px;">注: > style="color: red;">[…] > > > style="color: red;">用于表示列的完整性 > style="color: red;">这里的 > > > style="color: red;">I[…] after J > > > style="color: red;">表示在 > > > style="color: red;">J > > > style="color: red;">后面添加属性 > > > style="color: red;">I

style="background-color: #ff0000; color: #ffff33; font-family: SimSun; font-size: 18px;">删除字段I的默认值

alter table table_name alter I drop default;

> style="color: red;">注:使用 ALTER命令及 > > > style="color: red;"> DROP > > > style="color: red;">子句来删除字段的默认值

style="background-color: #ff0000; color: #ffff00; font-family: SimSun; font-size: 18px;">更改表的引擎

alter table table_name engine=engineName;

> style="font-family: SimSun; font-size: 18px;">注:将数据表的引擎更改为指定的引擎。

style="background-color: #ff0000; color: #ffff00; font-family: SimSun; font-size: 18px;">修改表序列的起始值

alter table table_name auto_increment=value;

> style="font-size: 18px;">注: > style="color: red;">更改表的序列起始值为 > > > style="color: red;">value > > > style="color: red;">。 > style="color: red;">这里的 > > > style="color: red;">value > > > style="color: red;">依据设定 > > > style="color: red;">auto_increment > > > style="color: red;">的列的数据类型而定,如 > > > style="color: red;">int > > > style="color: red;">则可以为整数,如 > > > style="color: red;">varchar(4) > > > style="color: red;">可以为字符串

 

视图

truncate

style="font-family: SimSun; font-size: 18px; color: #3333ff;">创建视图

style="font-family: SimSun; font-size: 18px; color: #ffff00; background-color: #ff0000;">创建视图语法:

create view 视图名[字段名1,字段名2,.....] as 查询语句

style="background-color: #ff0000; color: #ffff00; font-family: SimSun; font-size: 18px;">从单个表中派生视图

如:

create view v1 as select StuID,StuName from Student_info;

style="font-family: SimSun; font-size: 18px;">澳门新萄京官方网站 67

style="color: #ffff00; font-family: SimSun; font-size: 18px; background-color: #ff0000;">从多个表中派生视图

如:

create view v2 as select * from student_info,test_table where student_info.StuID = test_table.test_StuID;

style="font-family: SimSun; font-size: 18px;">澳门新萄京官方网站 68

style="font-family: SimSun; font-size: 18px;">澳门新萄京官方网站 69

style="background-color: #ff0000; color: #ffff00; font-family: SimSun; font-size: 18px;">视图虚字段

如:

create view v3 as select StuName,Telephone as oritel,telephone 1 as retel from student_info;

> style="font-size: 18px; color: #ff6600;">注:这里重新创建了一个视图,有三个属性分别为StuName、oritel、retel,其中oritel属性就是Telephone,retel属性是Telephone 1的值。因为这里的oritel和retel属性在原表中是没有的,需要通过计算而来的,所有被成为虚字段。

style="font-size: 18px;">澳门新萄京官方网站 70

style="font-family: SimSun; font-size: 18px; color: #3333ff;">删除视图

style="font-family: SimSun; font-size: 18px; color: #ffff00; background-color: #ff0000;">删除视图语法:

drop view 视图名

  • > style="font-family: SimSun; font-size: 18px;">如:*

drop view v3;

style="color: #3333ff; font-family: SimSun; font-size: 18px;">视图的说明

style="font-family: SimSun; font-size: 18px;">MySQL中的视图是可更新的,任何对原表的更改都会反应到视图中,或者任何对视图的更改都会反应到原表上。

 

正则表达式

style="font-family: SimSun; font-size: 18px; color: #3366ff;">删除表数据

style="font-family: SimSun; font-size: 18px; color: #3333ff;">查找指定的数据

style="font-family: SimSun; font-size: 18px; color: #ffff00; background-color: #ff0000;">查找name字段中以'st'为开头的所有数据:

select name from table_name where name regexp'^st';

style="font-family: SimSun; font-size: 18px; color: #ffff33; background-color: #ff0000;">查找name字段中以'ok'为结尾的所有数据:

SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

style="font-family: SimSun; font-size: 18px; color: #ffff33; background-color: #ff0000;">查找name字段中包含'mar'字符串的所有数据:

SELECT name FROM person_tbl WHERE name REGEXP 'mar';

style="font-family: SimSun; font-size: 18px; color: #ffff33; background-color: #ff0000;">查找name字段中以元音字符开头且以'ok'字符串结尾的所有数据:

SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]. ok$';

style="font-family: SimSun; font-size: 18px; color: #ffff33; background-color: #ff0000;">查找name字段中以元音字符开头或者以'ok'字符串结尾的所有数据:

SELECT name FROM person_tbl WHERE nameREGEXP '^[aeiou]|ok$';

 

NULL运算符

style="font-family: SimSun; font-size: 18px; color: #ffcc00;">删除表的所有数据

style="font-family: SimSun; font-size: 18px; color: #3366ff;">说明:

IS NULL: 当列的值是NULL,此运算符返回true。

IS NOT NULL: 当列的值不为NULL, 运算符返回true。

<=>: 比较操作符(不同于=运算符),当比较的的两个值为NULL时返回true。

style="font-family: SimSun; font-size: 18px; color: #3333ff;">例子:

比如:

select * from table where column_name is null;

style="font-family: SimSun; font-size: 18px;">注:将会显示column_name为null的所有数据,不能使用select * from table where column_name = null。

 

 

truncate table_name;

select * from table where column_nameis not null;

style="font-family: SimSun; font-size: 18px;">注:将会显示column_name不为空的所有数据,不能使用select * from table where column_name != null。

 

select null <=> null;

style="font-family: SimSun; font-size: 18px;">注:将会显示1。

 

select null <=>"NotEmpty";

style="font-family: SimSun; font-size: 18px;">注:将会显示0。

 

SQL注入

 

style="font-family: SimSun; font-size: 18px; color: #3333ff;">sql注入是什么

style="font-family: SimSun; font-size: 18px;">就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串,最终达到欺骗服务器执行恶意的SQL命令。

style="color: #3333ff; font-family: SimSun; font-size: 18px;">举例

style="font-family: SimSun; font-size: 18px;">MySQL特点,执行语句的时候如果出现这样的语句将会不报错,并且继续执行,

select *澳门新萄京官方网站, from table_name where user =value;show tables;

style="font-family: SimSun; font-size: 18px;">这样回车就是两个语句,这里是会执行两个语句的,如果在表单中查询用户的信息,那么在表单输入中的用户名一栏写入,

'jame;delete from user;'

style="font-family: SimSun; font-size: 18px;">那么最后就会删除user表。

 

 

  注:truncate和delete的区别,对于大部分数据库而言,truncate都被当成DDL处理,truncate被称为“截断”某个表-它的作用是删除该表里的全部数据,但保留数据结构。相当于DML中的delete命令,truncate的速度要快得多,而且truncate不像delete可以删除指定的记录,truncate只能删除表中的全部记录。MySQL对truncate的处理比较特殊-如果使用非InnoDB的存储引擎,truncate要比delete快;如果使用InnoDB的存储引擎,在MySQL5.0.3之前,truncate和delete完全一样,在5.0.3之后,truncate比delete要快,但如果该表被外键约束所参照,truncate又变成delete操作。在5.0.13之后,快速truncate总是可用的,即比delete的速度要快。在使用

事务

insert

一般来说,事务是必须满足4个条件(ACID):Atomicity(原子性)、Consistency(稳定性)、Isolation(隔离性)、Durability(可靠性)

style="font-family: SimSun; font-size: 18px; color: #3366ff;">插入数据

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">插入完整元组

insert into Student_info values (10,'宋文',123456789);

style="background-color: #ff0000; color: #ffcc00; font-family: SimSun; font-size: 18px;">插入非完整元组

insert into Student_info(StuName,Telephone) values ('小吴,'123456789');

> style="font-family: SimSun; font-size: 18px;">注:在决定插入元组数据中的属性个数时,主要依据表中属性的完整性来决定。

style="font-family: SimSun; font-size: 18px; color: #3366ff;">复制表数据

style="font-family: SimSun; font-size: 18px; color: #3366ff;"> > style="color: #ffcc00; font-family: SimSun; font-size: 18px; background-color: #ff0000;">复制旧表的数据到新表

insert into 新表 select * from 旧表

> style="font-size: 18px;">注: > style="font-size: 18px;">这里两个表的结构要求要一样 > style="color: red;">这里只是复制数据,旧表的 > > > style="color: red;">primary key > > > style="color: red;">和 > > > style="color: red;">auto_increment > > > style="color: red;">还是需要自己添加。下面的方法也可以得到两个完全一样的表 > style="color: red;">方法一:首先用“ > > > style="color: red;">creat table  > > > style="color: red;">新表 like  > > > style="color: red;">旧表”将旧表的结构复制过来,然后使用“ > > > style="color: red;">insert into > > > style="color: red;">新表 select from 旧表 > > > style="color: red;">”将表中的数据复制过去。 > style="color: red;">方法二: > > > style="color: red;">show create table > > > style="color: red;">旧表*命令得到创建表的命令,复制粘贴再更改名称最后运行,得到一个数据结构和属性一样的新表 > > > style="color: red;">( > > > style="color: red;">除名称 > > > style="color: red;">) > > > style="color: red;">,再用这里 > > > style="color: red;">insert > > > style="color: red;">命令,将数据复制过去,就可以得到完全一样的表了。

style="background-color: #ff0000; color: #ffcc00; font-family: SimSun; font-size: 18px;">复制旧表的部分数据到新表

insert into 新表 (字段1,字段2......)select 字段1,,字段2,.....from 旧表

> style="font-family: SimSun; font-size: 18px;">注: > style="font-family: SimSun; font-size: 18px;">1,这里两个表的匹配字段数据类型要一致。 > style="color: red;">2,这里只是复制数据,旧表的 > > > style="color: red;">primary key > > > style="color: red;">和 > > > style="color: red;">auto_increment > > > style="color: red;">还是需要自己添加。

style="font-family: SimSun; font-size: 18px; color: #3366ff;">insert ignore into 和 insert into 的区别

INSERT IGNORE INTO与INSERT INTO的区别就是INSERT IGNORE会忽略数据库中已经存在的数据,如果数据库没有数据,就插入新的数据,如果有数据的话就跳过这条数据。INSERT IGNORE INTO当插入数据时,在设置了记录的唯一性后,如果插入重复数据,将不返回错误,只以警告形式返回。 而REPLACE INTO into如果存在primary 或 unique相同的记录,则先删除掉。再插入新记录。

1、事务的原子性:一组事务,要么成功;要么撤回。

select

2、稳定性 : 有非法数据(外键约束之类),事务撤回。

style="font-family: SimSun; font-size: 18px; color: #3366ff;">查看当前日期

select current_date;

style="font-family: SimSun; font-size: 18px;">澳门新萄京官方网站 71

style="font-family: SimSun; font-size: 18px; color: #3366ff;">查看服务器版本

select version();

style="font-family: SimSun; font-size: 18px;">澳门新萄京官方网站 72

style="font-family: SimSun; font-size: 18px; color: #3366ff;">查看当前数据库名称

select database();

style="font-family: SimSun; font-size: 18px; color: #3333ff;">查看当前用户名

select user();

style="font-family: SimSun; font-size: 18px; color: #3333ff;">查询表中数据的语句

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">查询表中指定字段无重复的数据

select distinct column_name from table_name wherecondition;

> style="font-family: SimSun; font-size: 18px;">注: > style="color: red;">1 > > > style="color: red;">, > > > style="color: red;">distinct > > > style="color: red;">用于显示的数据内容不存在重复 > style="color: red;">2 > > > style="color: red;">, > > > style="color: red;">* > > > style="color: red;">表示所有数据 > style="color: red;">3 > > > style="color: red;">, > > > style="color: red;">condition > > > style="color: red;">可以用大于、小于等

style="font-size: 18px; color: #3333ff;">查询数据之动态指定条件语句

 比如有两个表,分别是table1和table2,在table1中有一个id1属性,在table2中有一个id2属性。如果要查询符合id2中的id1的数据,不需要分两句书写,可以直接将其写到一起,比如:

select table1.id1 from table1 where id1=(select table2.id2 from table2 where id2=1);

必须注意:在条件语句中的得出table2的数据只能是一个明确的数值,不能是一个数组。

连接查询

style="font-size: 18px; color: #ffff00; background-color: #ff0000;">语法:

select 属性 from 表1,表2 where (表1.属性 = 表2.属性);

注:“表1.属性 = 表2.属性” 是很重要的,它表示两个表连接的条件。

style="font-size: 18px; color: #ffff00; background-color: #ff0000;">简单连接查询

select * from student_info,test_table where( StuID >2) and(StuID < 5) and (student_info.StuID = test_table.test_StuID);

> style="color: red;">注:查询Student_info表中StuIDtestable表中tesStuID的值相同的所有数据,并且满足Student_info中的StuID在2到5的条件。

style="font-size: 18px;">澳门新萄京官方网站 73

style="font-size: 18px;">澳门新萄京官方网站 74

3、隔离性:事务独立运行。一个事务处理后的结果,影响了其他事务,那么其他事务会撤回。事务的100%隔离,需要牺牲速度。

 

4、可靠性:软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得, innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。

自连接查询

style="font-size: 14px; color: #000000; background-color: #ff0000;"> style="font-size: 18px; color: #ffff33;">语法:

  使用别名

 

例如:

双索引

澳门新萄京官方网站 75

打印这个表中所有的id,name,但是这个name对应的id至少有一次出现在manager_id中(如果把id看成员工编号,manager_id看成员工上司的id号,那么请打印所有的管理层)。

select distinct z.id,z.name from zilianjie z,zilianjie j where j.manager_id=z.id;

 

超连接查询

style="font-size: 18px; color: #ffff33; background-color: #ff0000;">语法:

内部连接:select 属性1,属性2....... from 表1 inner join 表2 on 表1.属性 = 表2.属性

左连接: select属性1,属性2....... from 表1 left join 表2 on 表1.属性 = 表2.属性

右连接 : select 属性1,属性2....... from 表1 left join 表2 on 表1.属性 = 表2.属性

内部连接

inner join 形式的连接称为内部连接,也可以写为join。内部连接表示只有满足条件的记录才会显示到查询结果中

select * from student_info inner join test_table on student_info.StuID = test_table.test_StuID;

这个语句等价于select * from student_info,test_table where(student_info.StuID =test_table.test_StuID);

style="background-color: #ffffff;">澳门新萄京官方网站 76

style="background-color: #ffffff;">澳门新萄京官方网站 77

 

 

左连接

left join 形式的连接称为左连接。查询结果包含join左侧表中的所有记录以及右侧表满足条件的记录

右连接

right join 形式的连接称为右连接。查询结果包含join右侧表中的所有记录以及左侧表中满足条件的记录

并运算查询

语法

union表示并运算

style="background-color: #ff0000; color: #ffff33; font-size: 18px; font-family: SimSun;">实例

style="background-color: #ffffff;">将两个表中满足条件的数据显示

select StuID,StuName,Telephone from student_info where StuName = "第三个名称"

union

select test_StuID,test_Name from test_table where test_StuID= 4;

style="background-color: #ffffff;">澳门新萄京官方网站 78

style="font-family: SimSun; font-size: 18px; color: #ff9966;">注:这里两个表需要查询出来的属性数目要一致,否则就会出现“ERROR 1222 (21000): The used SELECT statements have a different number of columns”的错误。

primary key(column1,column2);

update

设置了双索引的话,那么满足两个数据都重复的数据不会存入,有一个数据重复则可以存储。

style="font-family: SimSun; font-size: 18px; color: #3333ff;">更新部分数据

update table_name set StuName = "易建联" where StuID = 1;

style="font-family: SimSun; font-size: 18px;">澳门新萄京官方网站 79

style="font-family: SimSun; font-size: 18px; color: #3333ff;">更新整列的值

update table_name set Telephone= Telephone 1;

style="font-family: SimSun; font-size: 18px;">澳门新萄京官方网站 80

澳门新萄京官方网站 81

alter

order by

style="font-family: SimSun; font-size: 18px; color: #3333ff;">更改表

style="font-family: SimSun; font-size: 18px; color: #ffff00; background-color: #ff0000;">在表中增加一个属性

alter table 表 add column 属性 数据类型 [列的完整性];

style="background-color: #ff0000; color: #ffcc00; font-family: SimSun; font-size: 18px;">改变表中某个属性

alter table 表 change 属性 属性 数据类型 [列的完整性];

alter table 表 modify 属性 数据类型 [列的完整性];

style="font-family: SimSun; font-size: 18px;">注:change可以改变属性名称,但是modify只能在原来的属性上进行修改。

style="background-color: #ff0000; color: #ffff00; font-family: SimSun; font-size: 18px;">撤销表中的某个属性

alter table 表 drop 属性;

style="background-color: #ff0000; color: #ffff33; font-family: SimSun; font-size: 18px;">更改表的名称

alter table 旧表名 rename 新表名;

style="background-color: #ff0000; color: #ffff33; font-family: SimSun; font-size: 18px;">撤销和更改表的主键

alter table 表 drop primary key,add primary key(属性);

style="background-color: #ff0000; color: #ffff33; font-family: SimSun; font-size: 18px;">在第一行添加属性

alter table table_name add I [….]first;

> style="color: red;">注: > style="color: red;">这里的 > > > style="color: red;">first > > > style="color: red;">表示在第一行添加属性 > > > style="color: red;">I > style="color: red;">[….] > > > style="color: red;">用于表示列的完整性 > style="color: red;">比如: > style="color: red;">alter table userinfo add userAge varchar(2) not null first;

style="font-family: SimSun; font-size: 18px; color: #ffff33; background-color: #ff0000;">在属性I后面添加J属性

alter table table_name add I […] after J;

> style="font-family: SimSun; font-size: 18px;">注: > style="color: red;">[…] > > > style="color: red;">用于表示列的完整性 > style="color: red;">这里的 > > > style="color: red;">I[…] after J > > > style="color: red;">表示在 > > > style="color: red;">J > > > style="color: red;">后面添加属性 > > > style="color: red;">I

style="background-color: #ff0000; color: #ffff33; font-family: SimSun; font-size: 18px;">删除字段I的默认值

alter table table_name alter I drop default;

> style="color: red;">注:使用 ALTER命令及 > > > style="color: red;"> DROP > > > style="color: red;">子句来删除字段的默认值

style="background-color: #ff0000; color: #ffff00; font-family: SimSun; font-size: 18px;">更改表的引擎

alter table table_name engine=engineName;

> style="font-family: SimSun; font-size: 18px;">注:将数据表的引擎更改为指定的引擎。

style="background-color: #ff0000; color: #ffff00; font-family: SimSun; font-size: 18px;">修改表序列的起始值

alter table table_name auto_increment=value;

> style="font-size: 18px;">注: > style="color: red;">更改表的序列起始值为 > > > style="color: red;">value > > > style="color: red;">。 > style="color: red;">这里的 > > > style="color: red;">value > > > style="color: red;">依据设定 > > > style="color: red;">auto_increment > > > style="color: red;">的列的数据类型而定,如 > > > style="color: red;">int > > > style="color: red;">则可以为整数,如 > > > style="color: red;">varchar(4) > > > style="color: red;">可以为字符串

语法:

视图

order by 属性1,属性2,...... [asc,desc];

style="font-family: SimSun; font-size: 18px; color: #3333ff;">创建视图

style="font-family: SimSun; font-size: 18px; color: #ffff00; background-color: #ff0000;">创建或替换视图语法:

create or replace view 视图名[字段名1,字段名2,.....] as 查询语句

style="background-color: #ff0000; color: #ffff00; font-family: SimSun; font-size: 18px;">从单个表中派生视图

如:

create view v1 as select StuID,StuName from Student_info;

style="font-family: SimSun; font-size: 18px;">澳门新萄京官方网站 82

style="color: #ffff00; font-family: SimSun; font-size: 18px; background-color: #ff0000;">从多个表中派生视图

如:

create view v2 as select * from student_info,test_table where student_info.StuID = test_table.test_StuID;

style="font-family: SimSun; font-size: 18px;">澳门新萄京官方网站 83

style="font-family: SimSun; font-size: 18px;">澳门新萄京官方网站 84

style="background-color: #ff0000; color: #ffff00; font-family: SimSun; font-size: 18px;">视图虚字段

如:

create view v3 as select StuName,Telephone as oritel,telephone 1 as retel from student_info;

> style="font-size: 18px; color: #ff6600;">注:这里重新创建了一个视图,有三个属性分别为StuName、oritel、retel,其中oritel属性就是Telephone,retel属性是Telephone 1的值。因为这里的oritel和retel属性在原表中是没有的,需要通过计算而来的,所有被成为虚字段。

style="font-size: 18px;">澳门新萄京官方网站 85

style="font-family: SimSun; font-size: 18px; color: #3333ff;">删除视图

style="font-family: SimSun; font-size: 18px; color: #ffff00; background-color: #ff0000;">删除视图语法:

drop view 视图名

  • > style="font-family: SimSun; font-size: 18px;">如:*

drop view v3;

style="color: #3333ff; font-family: SimSun; font-size: 18px;">视图的说明

style="font-family: SimSun; font-size: 18px;">MySQL中的视图是可更新的,任何对原表的更改都会反应到视图中,或者任何对视图的更改都会反应到原表上。

注:asc为升序(默认为升序)

澳门新萄京官方网站:MySQL的常规操作,记录多一点。正则表达式

desc为降序

style="font-family: SimSun; font-size: 18px; color: #3333ff;">查找指定的数据

style="font-family: SimSun; font-size: 18px; color: #ffff00; background-color: #ff0000;">查找name字段中以'st'为开头的所有数据:

select name from table_name where name regexp'^st';

style="font-family: SimSun; font-size: 18px; color: #ffff33; background-color: #ff0000;">查找name字段中以'ok'为结尾的所有数据:

SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

style="font-family: SimSun; font-size: 18px; color: #ffff33; background-color: #ff0000;">查找name字段中包含'mar'字符串的所有数据:

SELECT name FROM person_tbl WHERE name REGEXP 'mar';

style="font-family: SimSun; font-size: 18px; color: #ffff33; background-color: #ff0000;">查找name字段中以元音字符开头且以'ok'字符串结尾的所有数据:

SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]. ok$';

style="font-family: SimSun; font-size: 18px; color: #ffff33; background-color: #ff0000;">查找name字段中以元音字符开头或者以'ok'字符串结尾的所有数据:

SELECT name FROM person_tbl WHERE nameREGEXP '^[aeiou]|ok$';

  当有多个属性值进行排序的时候,按照先后顺序进行排序。

NULL运算符

:

style="font-family: SimSun; font-size: 18px; color: #3366ff;">说明:

IS NULL: 当列的值是NULL,此运算符返回true。

IS NOT NULL: 当列的值不为NULL, 运算符返回true。

<=>: 比较操作符(不同于=运算符),当比较的的两个值为NULL时返回true。

style="font-family: SimSun; font-size: 18px; color: #3333ff;">例子:

比如:

select * from table where column_name is null;

style="font-family: SimSun; font-size: 18px;">注:将会显示column_name为null的所有数据,不能使用select * from table where column_name = null。

select * from Student_info order byStuID,StuName desc;

 

先按照StuID进行降序排序,再按照StuName进行升序排序。

select * from table where column_nameis not null;

style="font-family: SimSun; font-size: 18px;">注:将会显示column_name不为空的所有数据,不能使用select * from table where column_name != null。

 

select null <=> null;

style="font-family: SimSun; font-size: 18px;">注:将会显示1。

 

select null <=>"NotEmpty";

style="font-family: SimSun; font-size: 18px;">注:将会显示0。

 

SQL注入

删除某个数据库下的所有表

style="font-family: SimSun; font-size: 18px; color: #3333ff;">sql注入是什么

style="font-family: SimSun; font-size: 18px;">就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串,最终达到欺骗服务器执行恶意的SQL命令。

style="color: #3333ff; font-family: SimSun; font-size: 18px;">举例

style="font-family: SimSun; font-size: 18px;">MySQL特点,执行语句的时候如果出现这样的语句将会不报错,并且继续执行,

select * from table_name where user =value;show tables;

style="font-family: SimSun; font-size: 18px;">这样回车就是两个语句,这里是会执行两个语句的,如果在表单中查询用户的信息,那么在表单输入中的用户名一栏写入,

'jame;delete from user;'

style="font-family: SimSun; font-size: 18px;">那么最后就会删除user表。

SELECT concat('DROP TABLE IF EXISTS ', table_name, ';')

 

FROM information_schema.tables

事务

WHERE table_schema = 'mydb';

一般来说,事务是必须满足4个条件(ACID):Atomicity(原子性)、Consistency(稳定性)、Isolation(隔离性)、Durability(可靠性)

将mydb换成需要删除的数据库就可以了。

1、事务的原子性:一组事务,要么成功;要么撤回。

 

2、稳定性 : 有非法数据(外键约束之类),事务撤回。

binary

3、隔离性:事务独立运行。一个事务处理后的结果,影响了其他事务,那么其他事务会撤回。事务的100%隔离,需要牺牲速度。

binary用于可以用于区分大小写

4、可靠性:软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得, innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。

MySQL中本来是不区分大小写的,

 

比如:“select from table_name where name = "Jame";*”可以搜索出table_name 表中Jame、jame、jAme等数据,

双索引

但是“select from table where binary name ="Jame";*”就只能搜索出Jame数据。

primary key(column1,column2);

addcslashes() 

设置了双索引的话,那么满足两个数据都重复的数据不会存入,有一个数据重复则可以存储。

 

澳门新萄京官方网站 86

addcslashes() 函数在指定的字符前添加反斜杠。

order by

参数    描述

语法:

string 必需。规定要检查的字符串。

order by 属性1,属性2,...... [asc,desc];

characters     可选。规定受 addcslashes() 影响的字符或字符范围。

注:asc为升序(默认为升序)

 

desc为降序

select coalesce(a,b,c)

  当有多个属性值进行排序的时候,按照先后顺序进行排序。

参数说明:如果a==null,则选择b;如果b==null,则选择c;如果a!=null,则选择a;如果a b c 都为null ,则返回为null(没意义)。

:

如:

select * from Student_info order byStuID,StuName desc;

创建一个employee_tbl表:

先按照StuID进行降序排序,再按照StuName进行升序排序。

 

 

CREATE TABLE `employee_tbl`(`id`int(11) NOT NULL,`name`char(10) NOT NULL DEFAULT '',`date` datetime NOT NULL,`singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

删除某个数据库下的所有表

向表中插入数据:

SELECT concat('DROP TABLE IF EXISTS ', table_name, ';')

 

FROM information_schema.tables

INSERT INTO `employee_tbl` VALUES ('1','小明','2016-04-22 15:25:33','1'),('2','小王','2016-04-20 15:25:47','3'),('3','小丽','2016-04-19 15:26:02','2'),('4','小王','2016-04-07 15:26:14','4'),('5','小明','2016-04-11 15:26:40','4'),('6','小明','2016-04-04 15:26:54','2');

WHERE table_schema = 'mydb';

查询表中数据:

将mydb换成需要删除的数据库就可以了。

 

 

mysql> SELECT * FROM employee_tbl; ---- -------- --------------------- -------- | id | name   | date                | singin | ---- -------- --------------------- -------- |1|小明|2016-04-2215:25:33|1||2|小王|2016-04-2015:25:47|3||3|小丽|2016-04-1915:26:02|2||4|小王|2016-04-0715:26:14|4||5|小明|2016-04-1115:26:40|4||6|小明|2016-04-0415:26:54|2| ---- -------- --------------------- -------- 

binary

 

binary用于可以用于区分大小写

 

MySQL中本来是不区分大小写的,

接下来我们使用 GROUP BY 语句 将数据表按名字进行分组,并统计每个人有多少条记录:

比如:“select from table_name where name = "Jame";*”可以搜索出table_name 表中Jame、jame、jAme等数据,

mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name; -------- ---------- | name   | COUNT(*)| -------- ---------- |小丽|1||小明|3||小王|2| -------- ---------- 3 rows inset(0.01 sec)

但是“select from table where binary name ="Jame";*”就只能搜索出Jame数据。

使用 WITH ROLLUP

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT…)。

例如我们将以上的数据表按名字进行分组,再统计每个人登录的次数:

mysql> SELECT name, SUM(singin)as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP; -------- -------------- | name   | singin_count | -------- -------------- |小丽|2||小明|7||小王|7|| NULL   |16| -------- -------------- 4 rows inset(0.00 sec)

如果不使用With Rollup则结果不会出现NULL字段

澳门新萄京官方网站 87

其中记录 NULL 表示所有人的登录次数。

我们可以使用 coalesce 来设置一个可以取代 NUll 的名称,coalesce 语法:

select coalesce(a,b,c);

参数说明:如果a==null,则选择b;如果b==null,则选择c;如果a!=null,则选择a;如果a b c 都为null ,则返回为null(没意义)。

以下实例中如果名字为空我们使用总数代替:

 

mysql> SELECT coalesce(name,'总数'), SUM(singin)as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP; -------------------------- -------------- | coalesce(name,'总数')| singin_count | -------------------------- -------------- |小丽|2||小明|7||小王|7||总数|16| -------------------------- -------------- 4 rows inset(0.01 sec)

 

参考文章连接:

 

addcslashes() 

 

addcslashes() 函数在指定的字符前添加反斜杠。

参数    描述

string 必需。规定要检查的字符串。

characters     可选。规定受 addcslashes() 影响的字符或字符范围。

 

select coalesce(a,b,c)

参数说明:如果a==null,则选择b;如果b==null,则选择c;如果a!=null,则选择a;如果a b c 都为null ,则返回为null(没意义)。

如:

创建一个employee_tbl表:

 

CREATE TABLE `employee_tbl`(`id`int(11) NOT NULL,`name`char(10) NOT NULL DEFAULT '',`date` datetime NOT NULL,`singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

向表中插入数据:

 

INSERT INTO `employee_tbl` VALUES ('1','小明','2016-04-22 15:25:33','1'),('2','小王','2016-04-20 15:25:47','3'),('3','小丽','2016-04-19 15:26:02','2'),('4','小王','2016-04-07 15:26:14','4'),('5','小明','2016-04-11 15:26:40','4'),('6','小明','2016-04-04 15:26:54','2');

查询表中数据:

 

mysql> SELECT * FROM employee_tbl; ---- -------- --------------------- -------- | id | name   | date                | singin | ---- -------- --------------------- -------- |1|小明|2016-04-2215:25:33|1||2|小王|2016-04-2015:25:47|3||3|小丽|2016-04-1915:26:02|2||4|小王|2016-04-0715:26:14|4||5|小明|2016-04-1115:26:40|4||6|小明|2016-04-0415:26:54|2| ---- -------- --------------------- -------- 

 

 

接下来我们使用 GROUP BY 语句 将数据表按名字进行分组,并统计每个人有多少条记录:

mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name; -------- ---------- | name   | COUNT(*)| -------- ---------- |小丽|1||小明|3||小王|2| -------- ---------- 3 rows inset(0.01 sec)

使用 WITH ROLLUP

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT…)。

例如我们将以上的数据表按名字进行分组,再统计每个人登录的次数:

mysql> SELECT name, SUM(singin)as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP; -------- -------------- | name   | singin_count | -------- -------------- |小丽|2||小明|7||小王|7|| NULL   |16| -------- -------------- 4 rows inset(0.00 sec)

如果不使用With Rollup则结果不会出现NULL字段

澳门新萄京官方网站 88

其中记录 NULL 表示所有人的登录次数。

我们可以使用 coalesce 来设置一个可以取代 NUll 的名称,coalesce 语法:

select coalesce(a,b,c);

参数说明:如果a==null,则选择b;如果b==null,则选择c;如果a!=null,则选择a;如果a b c 都为null ,则返回为null(没意义)。

以下实例中如果名字为空我们使用总数代替:

 

mysql> SELECT coalesce(name,'总数'), SUM(singin)as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP; -------------------------- -------------- | coalesce(name,'总数')| singin_count | -------------------------- -------------- |小丽|2||小明|7||小王|7||总数|16| -------------------------- -------------- 4 rows inset(0.01 sec)

 

参考文章连接:

 

本文由澳门新萄京官方网站发布于www.8455.com,转载请注明出处:澳门新萄京官方网站:MySQL的常规操作,记录多一

关键词: