总结aaa

1. DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等；
2. DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）；
3. DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；
4. DQL（Data Query Language）：数据查询语言，用来查询记录（数据）。

CREATE TABLE  Course

          (Cno       CHAR(4)  PRIMARY  KEY,

          Cname  CHAR(40),            

           Cpno     CHAR(4),                                      

            Ccredit  SMALLINT,

            FOREIGN KEY (Cpno) REFERENCES  Course(Cno)

          ) charset utf8;

1. 查看当前数据库中所有表名称：SHOW TABLES；　
2. 查看指定表的创建语句：SHOW CREATE TABLE emp，查看emp表的创建语句；
3. 查看表结构：DESC emp，查看emp表结构；
4. 删除表：DROP TABLE emp，删除emp表；
5. 修改表：

1. 修改之添加列：给stu表添加classname列：

ALTER TABLE stu ADD (classname varchar(100));

1. 修改之修改列类型：修改stu表的gender列类型为CHAR(2)：

ALTER TABLE stu MODIFY gender CHAR(2);

1. 修改之修改列名：修改stu表的gender列名为sex：

ALTER TABLE stu change gender sex CHAR(2);

1. 修改之删除列：删除stu表的classname列：

ALTER TABLE stu DROP classname;

1. 修改之修改表名称：修改stu表名称为student：

ALTER TABLE stu RENAME TO student;

INSERT INTO stu(sid, sname,age,gender) VALUES('s_1001', 'zhangSan', 23, 'male');

UPDATE stu SET sname=’liSi’, age=’20’ WHERE age>50 AND gender=’male’;

DELETE FROM stu WHERE sid=’s_1001’003B

创建用户

CREATE USER user1@localhost IDENTIFIED BY ‘123’;

给用户撤销授权

REVOKE CREATE,ALTER,DROP ON mydb1.* FROM user1@localhost;

查看用户权限

SHOW GRANTS FOR user1@localhost;

删除用户

DROP USER user1@localhost;

修改用户密码

UPDATE USER SET PASSWORD=PASSWORD('1234') WHERE User='user2' and Host=’localhost’;

FLUSH PRIVILEGES;

查询

查询学号（不）为S_1001，S_1002，S_1003的记录

SELECT * FROM stu

WHERE sid  （NOT） IN ('S_1001','S_1002','S_1003');

查询姓名由5个字母构成的学生记录

SELECT *

FROM stu

WHERE sname LIKE '_____';

模糊查询必须使用LIKE关键字。其中 “_”匹配任意一个字母，5个“_”表示5个任意字母。

查询姓名中第2个字母为“i”的学生记录

SELECT *

FROM stu

WHERE sname LIKE '_i%';

去重

SELECT DISTINCT sal FROM emp;

给列名添加别名

在上面查询中出现列名为sal+IFNULL(comm,0)，这很不美观，现在我们给这一列给出一个别名，为total：

SELECT *, sal+IFNULL(comm,0) AS total FROM emp;

排序

5.1　查询所有学生记录，按年龄升序排序

SELECT *

FROM stu

ORDER BY sage ASC;

或者

SELECT *

FROM stu

ORDER BY sage;

查询所有学生记录，按年龄降序排序

SELECT *

FROM stu

ORDER BY age DESC;

COUNT（人数）

1. 统计月薪与佣金之和大于2500元的人数：

SELECT COUNT(*) AS cnt FROM emp WHERE sal+IFNULL(comm,0) > 2500;

SUM（求和）

1. 查询所有雇员月薪和，以及所有雇员佣金和：

SELECT SUM(sal), SUM(comm) FROM emp;

分组查询

1. 查询每个部门的部门编号以及每个部门工资大于1500的人数：

SELECT deptno,COUNT(*)

FROM emp

WHERE sal>1500

GROUP BY deptno;

HAVING子句

1. 查询工资总和大于9000的部门编号以及工资和：

SELECT deptno, SUM(sal)

FROM emp

GROUP BY deptno

HAVING SUM(sal) > 9000;

（having 中用函数）

主键

1. 创建表时设置主键自增长（主键必须是整型才可以自增长）：不可再设置not null和unique

CREATE TABLE stu(

sid INT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(20),

age INT,

gender VARCHAR(10)

);

1. 修改表时设置主键自增长：

ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;

1. 修改表时删除主键自增长：

ALTER TABLE stu CHANGE sid sid INT;

外键

1. 创建t_section表，指定sid为主键列，u_id为相对t_user表的uid列的外键：

CREATE TABLE t_section(

sid INT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(30),

u_id INT,

Constraint fk_t_user foreign key(u_id) referen ces t_user(uid)

CONSTRAINT fk_t_user FOREIGN KEY(u_id) REFERENCES t_user(uid)

);

1. 修改t_section表，指定u_id为相对t_user表的uid列的外键：

ALTER TABLE t_section

ADD CONSTRAINT fk_t_user

FOREIGN KEY(u_id)

REFERENCES t_user(uid);

1. 修改t_section表，删除u_id的外键约束：

ALTER TABLE t_section

DROP FOREIGN KEY fk_t_user;

连接查询会产生笛卡尔积

使用主外键关系做为条件来去除无用信息

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

还可以为表指定别名，然后在引用列时使用别名即可。

SELECT e.ename,e.sal,e.comm,d.dname FROM emp AS e,dept AS d WHERE e.deptno=d.deptno;

内连接

上面的连接语句就是内连接，但它不是SQL标准中的查询方式，可以理解为方言！SQL标准的内连接为：

SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;

外连接（左连接、右连接）

外连接的特点：查询出的结果存在不满足条件的可能。

左连接：

SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;

右连接

右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。例如在dept表中的40部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出40部门，但相应的员工信息为NULL。

SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;

自然连接

大家也都知道，连接查询会产生无用笛卡尔积，我们通常使用主外键关系等式来去除它。而自然连接无需你去给出主外键等式，它会自动找到这一等式：

1. 两张连接的表中名称和类型完成一致的列作为条件，例如emp和dept表都存在deptno列，并且类型一致，所以会被自然连接找到！

当然自然连接还有其他的查找条件的方式，但其他方式都可能存在问题！(natural-自然的);

SELECT * FROM emp NATURAL JOIN dept; SELECT * FROM emp NATURAL LEFT JOIN dept; SELECT * FROM emp NATURAL RIGHT JOIN dept;

子查询

子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。

1. 子查询出现的位置：

• where后，作为条件的一部分；
• from后，作为被查询的一条表；

1. 当子查询出现在where后作为条件时，还可以使用如下关键字：

• any
• all

1. 子查询结果集的形式：

• 单行单列（用于条件）
• 单行多列（用于条件）
• 多行单列（用于条件）
• 多行多列（用于表）

练习：

1. 工资高于甘宁的员工。

分析：

查询条件：工资>甘宁工资，其中甘宁工资需要一条子查询。

第一步：查询甘宁的工资

SELECT sal FROM emp WHERE ename='甘宁'

第二步：查询高于甘宁工资的员工

SELECT * FROM emp WHERE sal > (${第一步})

结果：

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='甘宁')

1. 子查询作为条件
2. 子查询形式为单行单列

1. 工资高于30部门所有人的员工信息

分析：

查询条件：工资高于30部门所有人工资，其中30部门所有人工资是子查询。高于所有需要使用all关键字。

第一步：查询30部门所有人工资

SELECT sal FROM emp WHERE deptno=30;

第二步：查询高于30部门所有人工资的员工信息

SELECT * FROM emp WHERE sal > ALL (${第一步})

结果：

SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)

1. 子查询作为条件
2. 子查询形式为多行单列（当子查询结果集形式为多行单列时可以使用ALL或ANY关键字）

1. 查询工作和工资与殷天正完全相同的员工信息

分析：

查询条件：工作和工资与殷天正完全相同，这是子查询

第一步：查询出殷天正的工作和工资

SELECT job,sal FROM emp WHERE ename='殷天正'

第二步：查询出与殷天正工作和工资相同的人

SELECT * FROM emp WHERE (job,sal) IN (${第一步})

结果：

SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename='殷天正')

1. 子查询作为条件
2. 子查询形式为单行多列

1. 查询员工编号为1006的员工名称、员工工资、部门名称、部门地址

分析：

查询列：员工名称、员工工资、部门名称、部门地址

查询表：emp和dept，分析得出，不需要外连接（外连接的特性：某一行（或某些行）记录上会出现一半有值，一半为NULL值）

条件：员工编号为1006

第一步：去除多表，只查一张表，这里去除部门表，只查员工表

SELECT ename, sal FROM emp e WHERE empno=1006

第二步：让第一步与dept做内连接查询，添加主外键条件去除无用笛卡尔积

SELECT e.ename, e.sal, d.dname, d.loc  FROM emp e, dept d WHERE e.deptno=d.deptno AND empno=1006

第二步中的dept表表示所有行所有列的一张完整的表，这里可以把dept替换成所有行，但只有dname和loc列的表，这需要子查询。

第三步：查询dept表中dname和loc两列，因为deptno会被作为条件，用来去除无用笛卡尔积，所以需要查询它。

SELECT dname,loc,deptno FROM dept;

第四步：替换第二步中的dept

SELECT e.ename, e.sal, d.dname, d.loc FROM emp e, (SELECT dname,loc,deptno FROM dept) d WHERE e.deptno=d.deptno AND e.empno=1006

1. 子查询作为表
2. 子查询形式为多行多列

索引

create unique index index8_id on example1(course_id,address);

删除索引

drop index index18_space on example11;

mysql触发器：

触发器trgger类似于javascript 事件...

作用:当对表进行 insert,update,delete 操作时[指定触发]

触发类型:insert;update;delete

触发器执行时间:before;after

创建触发器:

delimiter //

create trigger 触发器名

[before|after] [insert|update|delete]

on 表名

for each row

begin

sql 语句;

sql 语句;

end //

delimiter ;

删除触发器 :drop trigger 触发器名;

修改定界符:

delimiter ;  

delimiter //

示例:

create table t1(id int);

create table t2(name varchar(20));

insert into t1 values(1),(2),(3);

insert into t2 values('tom');

给t2表添加update触发器,

触发器工作--->将t1删除了;

delimiter //

 create trigger tri_t2_update

 before update

 on t2

 for each row

 begin

   delete from t1;

 end //

delimiter ;

示例:

创建触发器 t2 delete

作用:向t1 表添加一条记录

delimiter //

  create trigger tri_t2_delete

  after delete

  on t2

  for each row

  begin

     insert into t1 values(1000);

  end //

delimiter ;

示例:

天天新闻网 news

1:创建触发器:添加新闻---->

2:对新闻类别表更新 articleNums 加一;

delimiter //

 create trigger tri_newsarticles_insert1

 after insert

 on newsArticles

 for each row

 begin

  update newstypes set articleNums=articleNums+1

   where id = (

   select typeId from newsarticles where id =

   (select max(id) from newsarticles));

 end //

delimiter ;

触发器里关键 NEW OLD

delimiter //

 create trigger tri_newsarticles_insert2

 after insert

 on newsArticles

 for each row

 begin

  update newstypes set articleNums=articleNums+1

  where id = NEW.typeId;

 end //

delimiter ;

create table t_user

(id int,name varchar(20));

insert into t_user values(1,'tom');

create table t_bank_user

(id int,name varchar(20));

delimiter //

 create trigger tri_user_delete

 before delete

 on t_user

 for each row

 begin

   insert into t_bank_user values(OLD.id,OLD.name);

 end //

delimiter ;

查看触发器

information_schema库-->triggers

select trigger_name,event_object_schema,

       event_object_table

from information_schema.triggers

where event_object_schema='库';

select trigger_name,event_object_schema,

       event_object_table

from information_schema.triggers

where event_object_schema='news';

show create trigger '触发器名';

show create trigger tri_my \G;

建议：少用 .. 耗资源

存储过程

存储过程与触发器区别

1:procedure 必须由用户来显示调用

2:trigger是对应进行insert,update,delete时

  由系统自动调用

3:trigger 资源消耗大一些

4:trigger 针对表

5:proceudre 依赖于数据库

4:索引 <-->重点

 1:定义变量

  declare 变量名 数据类型;

  declare 变量名 类据类型 default 值;

  注意:定义变量的代码必须出现开始位置

  delimiter //

   create procedure hello3()

   begin

      declare a int;

      declare b varchar(20);

      declare c int;

      declare age int default 20;

      declare userName varchar(20) default 'tom';

      select age;

   end //

  delimiter ;

2:变量赋值;

  set 变量名= 值;

  declare 变量名 数据类型 default 值;

  select 字段 into 变量 from 表名 where 条件;

  select 字段 into 变量,字段 into 变量 from 表名

  select 聚合函数(字段) into 变量 from 表名

  delimiter //

   create procedure hello4()

   begin

     declare a int;

     declare b varchar(30);

     declare c varchar(100);

     set a = 10;

     set b = 'beijing';

     select ename into c from emp where empno = 7369;

     select c;

   end //

  delimiter ;

 3:运算符

   算术运算符 + - * /

   逻辑运算符 and or not

  delimiter //

    create procedure hello5()

    begin

       declare a int default 20;

       declare b int default 30;

       declare sum int;

       set sum = a + b;

       select sum;

    end //

  delimiter ;

 4:判断语句

   if(){} php

  if 条件 then

    sql语句;

  end if;

  if 条件 then

   sql;

  else

   sql;

  end if;

  if 条件 then

   sql

  elseif 条件 then

   sql

  elseif 条件 then

   sql  

  else

    sql

  end if;

  delimiter //

   create procedure hello6()

   begin

     declare age int default 20;

     if age = 10 then

      select 'ok';

     else

      select 'error';

     end if;

   end //

  delimiter ;

 5:循环语句

   while 条件 do

     sql;

   end while;

   delimiter //

     create procedure hello7()

     begin

      declare i int default 0;

      while i<5 do

        select i;

        set i = i + 1;

      end while;

     end //

   delimiter ;

练习:1+2+3+100 结果;

   delimiter //

     create procedure hello8()

     begin

      declare rs int default 0;

      declare i int default 1;

      while i <= 100 do

       set rs = rs + i;

       set i = i + 1;

      end while;

      select rs;

     end //

   delimiter ;

repeat

  sql;

until 条件 end repeat;

delimiter //

  create procedure hello9()

  begin

     declare i int default 0;

     repeat

      select i;

      set i = i + 1;

     until i>5 end repeat;

  end //

delimiter ;

标记:loop

  sql

  if 条件 then

    leave 标记;

  end if;

end loop;

delimiter //

  create procedure hello10()

  begin

    declare i int default 0;

    haha:loop

      select i;

      set i = i + 1;

      if i > 5 then

        leave haha;

      end if;

    end loop;

  end //

delimiter ;

练习:loop 1+2+100  偶数相

delimiter //

  create procedure hello12()

  begin

    declare i int default 1;

    declare rs int default 0;

    haha:loop

      if i%2=0 then

       set rs = rs + i;

      end if;

      set i = i + 1;

      if i > 100 then

        leave haha;

      end if;

    end loop;

    select rs;

  end //

delimiter ;

  

强调:例子-->

解决问题:存储过程什么在软件项目才会使用;

1:如果<-项目过于复杂->不可以用sql

  完成操作才考虑使用<-存储过程->完成操作;

东莞-->鞋厂

例子:  添加新闻,同时新闻分类表中数量加一

参数: in out

delimiter //

  create procedure hello13(j int)

  begin

    declare i int default 0;

    set i = i + j;

    select i;

  end //

delimiter ;

1:insert into newsarticles();//新闻类别

2:update newsTypes  set articlesNums = ;

delimiter //

  create procedure addNews(v_content varchar(2000),v_title varchar(50),v_typeId int,v_userName varchar(20),v_writer varchar(20),v_source varchar(20),v_imagepath varchar(300))

  begin

    insert into newsArticles(content,title,typeId,userName,writer,source,imagepath)values(v_content,v_title,v_typeId,v_userName,v_writer,v_source,v_imagepath);

    update newsTypes set articleNums=articleNums+1 where id = v_typeId;

  end //

delimiter ;

call addNews('abc','aaa',1,'tom','tom','inet','images/1.jpg');

练习: 删除新闻;

1:新闻所有评论删除

2:新闻删除

3:新闻类别数量-1

4:用户表数量-1 ????

delimiter //

  create procedure delNews(v_id int)

  begin

  declare tId int;

  delete from reviews where articleId = v_id;

  select typeId into tId from newsArticles where id = v_id;

  update newsTypes set articleNums=articleNums-1 where id = tId ;

  delete from newsArticles where id = v_id;

  end //

delimiter ;

call delNews(26);

演示php调用程序

news09 test.php

$sql = "call delNews(25)";

$db = new DBUtils();

$db->update($sql);

存储过程

定义变量 declare i int;

         declare j int default 0;

赋值

        set i = 10;

        select 字段 into j from t where 条件

运算符  +-*/% and or not

if 条件 then

 sql;

end if;

whlie 条件 do

sql;

end while;

repeat

sql

until 结束条件 end repeat;

haha:loop

   if 结束条件 then

      leave haha;

   end if;

end loop;

示例:添加新闻

示例:删除新闻

参数:

in  输入 默认

out 输出

delimiter //

 create procedure hello11(i int,in userName varchar(20))

  begin

    select i,userName;

  end //

delimiter ;

call hello11(12,'tom');

示例:

out rs int  输出存储过程内部赋值;

            执行存储过程获取输出变量值;

delimiter //

 create procedure hello13(out rs int,a int,b int)

  begin

   set rs = a + b;

  end //

delimiter ;

call hello13(@rs,10,20);

select @rs;

示例:删除新闻分类,获取新闻类另有多少条新闻被删除;

    1 国内新闻

    9 获取9

delimiter //

  create procedure delNewsType(v_tid int,out rs int)

  begin

     select articleNums into rs from newstypes where id = v_tid;

     delete from newsarticles where typeId = v_tid;

     delete from newstypes where id = v_tid;

  end //

delimiter ;

row_count();上面sql影响记录数

delimiter //

  create procedure delNewsType1(v_tid int,out rs int)

  begin

     delete from newsarticles where typeId = v_tid;

     set rs = row_count();

     delete from newstypes where id = v_tid;

  end //

delimiter ;

delimiter //

  create procedure delNewsType1(v_tid int,out rs int)

  begin

     delete from newsarticles where typeId = v_tid;

     set rs = row_count();

     delete from newstypes where id = v_tid;

  end //

delimiter ;

存储过程总结

1:创建存储过程

2:删除存储过程

3:调用存储过程

4:if for set

5:参数:输入参数 in/输出参数 out

6:查看存储过程

  mysql.proc;

7:php   $sql = "call hello(12)";

建议:项目复杂使用简单sql无法查询出;

事务 <--重点{项目用，面试考}-->

事件:'多条不可分割sql语句.称为一个事务'

作用:保证数据完整性，正确性;

解析:sql语句 不可分割

     

     insert

     update

     insert

示例:借钱

注意:mysql 表存储必须是 innodb

特点:原子性，一致性，永久性，持久性;

常用事务指令

start transaction;  开启事务

begin;              开启事务

commit;             提交事务[结束事务]

rollback;           回滚事务[结束事务]

savepoint;          保存点

操作:更新操作 insert update delete

-->

start transaction;

 insert

 update

 update

commit;//提交事务，前三sql永久生效

rollback;//回滚,,回退事务开始前状态

技巧:commit;rollback不能连续使用;

通过示例演示事务;

create table bank(

  id int,

  name varchar(20),

  p  int

)engine=innodb;

insert into bank values(1,'yao',50);

insert into bank values(2,'tom',0);

1:php        操作事务

2:存储过程操作事务

news09 test.php

delimiter //

  create procedure up1()

  begin

     declare a int;

     declare b int;

     declare rs int;

     start transaction;

     update bank set p = p + 50 where id = 1;

     set a = row_count();

     update bank set p = p - 50 where id = 2;

     set b = row_count();

     set rs = a + b;

     if rs = 2 then

      commit;

     else

      rollback;

     end if;      

  end //

delimiter ;

事务:保存点

start transaction

select * from test;

update bank p  = p - 50 where id = 1;

savepoint haha;//..

delete from bbs;

select * from t;

rollback to haha;

有一些特殊语句-->执行直接提交 commit;

1:truncate table 表名;

2:create

3:drop

4:alter

总结:

事务:多条不可分割sql语句

作用:保存数据完整性，正确性

特点:原子性，一致性，永久性，持久性

innodb

start transaction

begin

commit

rollback;

savepoint 保存点

最好方式 mysql_query("begin"); commit;

1:表引擎不是innodb

2:出错

DBUtils.class.php

//-------------------------

问题：面试问题->myisam引擎保证数据完整性

解决:锁

表存储引擎

innodb:支持事务，外键,

myisam:不支持事务，不支外键[查询速度]

memory:所有数据保存内存->查询，更新

create table t(

)enging=innodb;

create table t(

id int

)enging=myisam;

begin

 insert

 delete

commit;

方法提供事务完整性，正确性;

myisam

语法:

加锁

lock tables 表名 read|write

解锁

unlock tables;

图示:

create table r1(

 id int,

 name varchar(20),

 p int

)engine=myisam;

insert into r1 values(1,'tom',100);

insert into r1 values(2,'jerry',0);

lock tables r1 write;

update r1 set p = p - 100 where id  = 1;

update r1 set p = p + 100 where id  = 2;

unlock tables;

创建读锁:

1:其它用可以对表读操作 select ,不能作更新操作;

2:自己不能对表insert,update,delete

lock tables r1 read;

select * from r1;

unlock tables;

加读锁目的：多次读取内容内容不会改变;