目录

基础

创建表

联合主键

修改表

插入

更新

删除

查询

DISTINCT

LIMIT

排序

过滤

通配符

计算字段

函数

分组查询

子查询

连接查询

组合查询

视图

存储过程

游标

临时表

触发器

权限管理

参考资料

 

基础

SQL三种注释方式

  1. #注释
  2. --注释
  3. /*注释*/

创建数据库 create database 数据库名;

删除数据库 drop database 数据库名;

显示所有数据库 show databases;

创建表

#表名student
create table student (
#id int型 不能为空 可以自增
id int not null auto_increment,
#name varchar型 不能为空
name varchar(10) not null,
#birthday date型 可以为空
birthday date null,
#有默认值的非空字段
age int not null default 1;
#设置主键
primary key(id));

 

查看表结构   desc 表名;

查看建表语句 show create table 表名;

 

联合主键

当有两个或多个主键时叫做联合主键。联合主键有以下几点作用

  1. 因为主键是不能重复的，例如学生表，设置学生名字为主键，万一有两个人重名就不可以了，这时设置两个主键，第二个主键设为生日，当名字相同时，生日不同也可以。
  2. 如果出现上面那种情况，可以用联合主键，就不用添加id这种无意义的列作为主键。

创建联合主键的方式

create table student1 (
 id int not null auto_increment,
 name varchar(10) not null,
 birthday date null,
 age int not null dafault 1;
 primary key(id,name));

primary key定义主键，两个主键之间用逗号隔开。

修改表

修改表名 alter table 旧表名 rename to 新表名;

修改字段类型 alter table 表名 modify 字段名 新类型;

修改字段名 alter table 表名 change 旧字段名 新字段名 新字段类型;

                   例子：alter table student3 change name namea varchar(20) not null;

增加字段 alter table 表名 add 新字段 first|after 字段名;

                   例子：alter table student3 add sex varchar(2) not null after namea;

删除字段 alter table 表名 drop 字段名;

删除表  drop table 表名;

 

插入

普通插入 insert into 表名 values (value1,value2...);

               这种插入方法字段必须一一对应，自增字段用null填。

               insert into 表名(col1,col2) values (value1,value2);

               只插入指定字段，但设置为不为空的字段必填，除非非空字段设置了默认值。

插入多条数据  insert into 表名(列1，列2...) values (值1，值2...),(值1，值2...)...; 

插入检索出来的数据 insert into 表名1 (col1,col2) select col1,col2 from 表名2;

               这个是将表二的数据全部插入，如果想插入指定语句可以加上where条件。

创建新表插入旧表数据 create table 新表名 as select * from 旧表名;

 

更新

更新数据 update 表名 set 属性名1=取值1,属性名2=取值2,属性名n=取值n where 条件表达式;

 

删除

删除数据 delete from 表名 where 条件;

               例子：delete from student where id=1;

删除表中所有数据 delete from 表名;

清空表(id自增会从1重新开始) truncate 表名;

 

查询

查询所有字段 select * from 表名;

查询指定字段 select 字段1,字段2,字段3 from 表名;

DISTINCT

用来去重，作用于所有列，只有当所有列都相同时才算相同。 用法 ：SELECT DISTINCT 字段名 FROM 表名;

例子 ：select distinct * from student;

LIMIT

分页查询，限制返回的行数。可以有两个参数，第一个参数为起始行，从 0 开始；第二个参数为返回的总行数。

select * from student limit 5;    返回前5行

select * from student limit 5,5;    从第五行开始返回，一共返回五行

排序

ASC：升序（默认）   DESC：降序

例子：select * from student order by age ASC;

           select * from student order by age DESC;

过滤

使用SQL语句过滤不必要的数据，而不是传输数据到客户端再进行过滤。

下面是where可用的操作符

操作符

说明

=

等于

<

小于

>

大于

<>!=

不等于

<=!>

小于等于

>=!<

大于等于

BETWEEN

在两个值之间

IS NULL

为空值

IS NOT NULL

不为空值

 

 

 

 

 

 

 

 

 

 

 

注：NULL与0，空字符串不同

  • AND 和OR 用于连接多个过滤条件，优先处理AND，当表达式涉及多个AND和OR，用()来决定优先级，使优先级关系更清晰。

        用法：SELECT 字段1,字段2...FROM 表名 WHERE 条件表达式1 AND 条件表达式2;

                   SELECT 字段1,字段2...FROM 表名 WHERE 条件表达式1 OR 条件表达式2;

  • IN操作符用于匹配一组值，后面可以接SELECT子句，从而匹配子查询得到的一组值。

        用法：SELECT id,name FROM student where id IN (2,4,5);

                   SELECT id,name FROM student where id IN (SELECT id from student where age=18);

  • NOT操作符用于否定一个条件

        用法：SELECT id FROM 表名 WHERE 字段名 IS NOT NULL;

  • BETWEEN和AND关键词

        用法：SELECT NAME FROM student1 WHERE id BETWEEN 2 AND 4;

 

通配符

通配符也可用在过滤语句中，但只能用于文本字段。

  • % 匹配大于等于0个任意字符
  • _  匹配1个任意字符
  • [ ] 匹配集合内的字符，例如[ab]将匹配字符a或b。用^可以否定，就是不匹配集合内的字符。

 

计算字段

在数据库服务器上完成数据的转换和格式化的工作往往比客户端上快。

  • 计算

       用法：SELECT 字段1*字段2 AS 别名 FROM 表名;   //把两个字段乘在一起用AS取个别名

  • 连接字段   CONCAT()连接两个字段 ，TRIM()可以去除首位空格

       用法：SELECT CONCAT(TRIM(字段一),'(',TRIM(字段二),')') AS 别名 FROM nnewtable;

 

函数

DBMS的函数各不相同，不可移植，以下主要为MYSQL函数。

  • AVG()函数  是求某列平均值的函数，例如适用于求平均分。

                          经常与GROUP BY关键字一起使用。

                          例子：SELECT AVG(n) AS avgg FROM student1 GROUP BY d;

                          AVG()会忽略NULL行，可以使用DISTINCT汇总不同的值。

                          例子：SELECT AVG(DISTINCT n) AS avgg FROM student1;

  • MAX(),MIN()函数 是求某列的最大值，最小值的函数

                          经常与GROUP BY关键字一起使用。

                          例子：SELECT name,MAX(n) FROM student1 WHERE age=18;
                                     SELECT name,MIN(n) FROM student1;

  • SUM()函数 是求和函数，求某列值的和

                           例子：SELECT SUM(n) AS sum_n FROM student1;

  • COUNT()函数 聚合函数，返回某列的行数

                           例子：SELECT COUNT(*) AS sum_n FROM student1;

 

分组查询

 GROUP BY分组查询，把具有相同数据值的行放在同一组。

  • 单独使用（没有意义）
  • 与GROUP_CONCAT()函数一起使用。

       例子：SELECT age,GROUP_CONCAT(NAME) FROM student1 GROUP BY age;  //会把age相同的所有行的name放在一列输出

  • 与COUNT()一起使用

       例子：SELECT age,COUNT(*) FROM student1 GROUP BY age;  //会统计每个年龄的人数作为一列输出

  • 与COUNT(), ORDER BY一起使用

       例子：SELECT age,COUNT(*) AS num FROM student1 GROUP BY age ORDER BY num DESC;

  • 与HAVING一起使用，限制输出的结果 

       例子：SELECT age,COUNT(*) FROM student1 GROUP BY age HAVING COUNT(*)>1; //该年龄人总数大于1时才会输出

  • 与WITH ROLLUP一起使用

       例子：SELECT age,COUNT(*) AS summ FROM student1 GROUP BY age WITH ROLLUP; //会加入统计总数行，在分组基础上再进                   行统计。

 分组规定：

  •  GROUP BY子句出现在WHERE子句后，ORDER BY子句前。
  •  NULL的行会单独分为一组。
  •  大多数SQL不支持GROUP BY 列具有可变长度的数据类型。

子查询

 子查询只能返回一个字段的数据，可以将子查询的结果作为WHERE语句的过滤条件。

  • 带IN/NOT IN 关键字的子查询

        例子：SELECT *FROM student1 WHERE age IN(SELECT age FROM student1 WHERE id=2);

  • 带比较运算符的子查询

        例子：SELECT *FROM student1 WHERE age <(SELECT age FROM student1 WHERE id=2);

  • 带EXISTS/NOT EXISTS关键字的子查询

        使用EXISTS/EXISTS返回的值是TRUE/FALSE，可以理解为存在，当子条件可以查到数据时就返回TRUE，当子条件查不到数据，返         回FALSE。

        例子：SELECT *FROM student1 WHERE EXISTS (SELECT age FROM student1 WHERE id=2);

                   SELECT *FROM student1 WHERE NOT EXISTS (SELECT age FROM student1 WHERE id=2);

  • 带ANY关键字的子查询

        ANY关键字表示满足其中一种条件。

        例子：SELECT *FROM student1 WHERE age>ANY(SELECT age FROM student1 WHERE id=2 OR id=4);

  • 带ALL关键字的子查询

        ALL关键字表示满足所有条件

  • 子查询作为一个字段

        例如检索出客户的订单数量，子查询语句会对第一个查询检索出的每个客户执行一次

        SELECT cust_name, (SELECT COUNT(*) FROM Orders WHERE Orders.cust_id = Customers.cust_id) AS orders_num FROM               Customers ORDER BY cust_name;

连接查询

连接可以用于连接多个表，使用JOIN关键字，并且条件语句使用ON而不是WHERE。

连接查询可以替换子查询，并且比子查询的效率快。

可以用AS给列名，计算字段和表名取别名，给表名取别名可以简化SQL语句以及连接相同表。

  • 内连接

       内连接又称等值连接

       使用INNER JOIN关键字：SELECT a.value,b.value FROM tablea AS a INNER JOIN tableb AS b ON a.key=b.key;

       不使用INNER JOIN关键字：SELECT a.value,b.value FROM tablea AS a , tableb AS b WHERE a.key=b.key;

  • 自连接

        一个表，自己连接自己。

        SELECT FROM tablea AS a ,tableb AS b WHERE a.key1=b.key1 AND b.key2=1;

  • 自然连接

        自然连接就是把具有同名列的自动连接起来。

        SELECT a.value , b.value FROM tablea AS a NATURAL JOIN tableb AS b;

  • 外连接

        外连接保留了没有关联的行，分为左外连接，右外连接，全外连接。

        左外连接（保留左表没有关联的行,LEFT OUTER JOIN）

            SELECT ,B.num FROM A LEFT OUTER JOIN B ON =;

                                                     A表：

id

name

1

a

2

b

3

c

                                                     B表：

id

num

1

1

1

2

3

3

3

4

                                                     结果：

id

name

num

1

a

1

1

a

2

2

b

null

3

c

3

3

c

4

        右外连接（保留右表没有关联的行, RIGHT OUTER JOIN）

        全外连接（左右表都保留, FULL OUTER JOIN）

 

组合查询

 使用UNION来组合两个查询，如果第一个查询返回M行，第二个查询返回N行，那么组合查询结果一般为M+N行。

 每个查询必须包含相同的列、表达式和聚集函数。

 默认去除相同的行，如果想保留相同行，用UNION ALL。

 只能包含一个ORDER BY 子句，并位于语句最后。

SELECT id FROM t_book UNION SELECT id FROM t_booktype;
     SELECT id FROM t_book UNION ALL SELECT id FROM t_booktype;


 

视图

 视图是一种虚拟的表，其中本身不包含数据，除了不能进行索引操作，其他操作和对普通表操作一样。

 使用视图的好处：

  • 简化复杂的SQL操作，比如复杂的连接。
  • 只使用实际表的一部分数据。
  • 可以只给用户访问视图的权限，保证数据安全性。

 在单表创建视图

CREATE VIEW v1 AS SELECT * FROM student;
    CREATE VIEW myview AS SELECT Concat(col1, col2) AS concat_col, col3*col4 AS compute_col FROM mytable WHERE col5 = val;

 在多表创建视图

CREATE VIEW v4 AS SELECT bookName,bookTypeName FROM t_book,t_booktype WHERE t_book.bookTypeId=t_booktype.id;

 查看视图

     SELECT * FROM 视图名；

 删除视图

      DROP VIEW IF EXISTS 视图名；

 

存储过程

 存储过程是一组为了完成特定功能的SQL语句集，存储在数据库中一次编译后再调用时无须二次编译。用户通过指定存储过程的名字和给  出参数来调用执行。

 存储过程的好处（在处理比较复杂的业务时比较实用）：

  • 响应时间短，可以提高运行效率。
  • 从安全上看使用了存储过程的系统更稳定。
  • 可以重复使用，减少数据库开发人员的工作量
  • 更强的适应性，可以在不改动接口的情况下对数据库操作进行改动，这些改动不会对应用程序有影响。

 命令行中创建存储过程需要自定义分隔符，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结  束符，造成语法错误。

 包含 in、out 和 inout 三种参数。

 给变量赋值都需要用 select into 语句。

eg.
 DELIMITER //
  CREATE PROCEDURE 过程名(IN 参数名 参数类型)   IN代表往里传参
  BEGIN
    正常书写SQL语句，例如DELETE，SELECT...语句
  END //eg.
DELIMITER //
 CREATE PROCEDURE delete_employee(IN ssnn VARCHAR(20))
 BEGIN
  DELETE FROM employee WHERE ssn=ssnn;
 END //

 

游标

 在存储过程中使用游标可以对一个结果集进行移动遍历。游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览修改。

 使用游标的四个步骤：

  1. 声明游标，这个过程没有实际检索出数据。
  2. 打开游标
  3. 取出数据
  4. 关闭游标
DELIMITER //
 CREATE PROCEDURE 过程名(IN ssnn VARCHAR(20))
 BEGIN 
  DECLARE n VARCHAR(20);                      —— 用DECLARE定义变量
  DECLARE dn VARCHAR(20);
  DECLARE done INT DEFAULT FALSE;     ——done用来记载表是否结束，默认初始值是FALSE
  DECLARE cur CURSOR FOR SELECT namea,dname FROM employee e,department t WHERE e.ssn=ssnn AND t.dnumber=e.dno;                                        ——用DECLARE和CURSOR FOR 关键字定义游标(cur是游标名)
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;——当表结束时把done的值变为TRUE
  OPEN cur;                          ——打开游标
  WHILE(NOT done)DO        ——用while循环取出数据
    FETCH cur INTO n,dn;     ——把搜索到的数据放入变量
   END WHILE;
  CLOSE cur;                        ——关闭游标
  SELECT n,dn;                    ——输出结果
 END //SET @ssnn='230101197712013128';
 CALL c1(@ssnn);  调用，或者CALL c1('230101197712013128');

除了while循环，还可以用loop,repeat 具体可查看

以上是当检索结果只有一条时。

当检索记录是多条时就要用到临时表

 

临时表

在存储过程中可以使用临时表。

语法：

  • 创建 create temporary table 表名（列信息）；
  • 删除 drop table 表名；
  • 清空 truncate table 表名；

注意：

  1. 在mysql中临时表一但建立，销毁的条件是session中断，为了避免反复调用时创建过程中出现表已经存在的错误，将建表语句改为CREATE TEMPORARY TABLE IF NOT EXISTS 表名(列信息);
  2. 临时表只在用户退出连接时清空数据，为防止数据累积，若有需求需要在临时表使用之后清空临时表。

例子：

DELIMITER //
 CREATE PROCEDURE c3()
 BEGIN 
  DECLARE n VARCHAR(10);
  DECLARE dn VARCHAR(20);
  DECLARE sl FLOAT;
  DECLARE done INT DEFAULT FALSE;
  DECLARE cur CURSOR FOR SELECT namea,dname,salary FROM employee a,department b WHERE a.salary IN (SELECT MAX(salary) FROM employee) AND a.dno=b.dnumber;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
  CREATE TEMPORARY TABLE IF NOT EXISTS ct3(n VARCHAR(10),dn VARCHAR(20),sl FLOAT); ——创建临时表
  TRUNCATE TABLE ct3;  ——清空临时表
  OPEN cur;
  FETCH cur INTO n,dn,sl;
  WHILE(NOT done)DO
    INSERT INTO ct3 VALUES(n,dn,sl); ——将数据逐条加入表中
    FETCH cur INTO n,dn,sl;
   END WHILE;
  CLOSE cur;
  
  SELECT * FROM ct3;  ——输出结果
 END //

触发器

触发器会在某个表执行语句时自动执行，触发器必须指定是在语句执行前还是执行后自动执行。

BEFORE 用于数据验证和净化，AFTER用于审计跟踪

创建触发器例子：

题目：在表department上创建一个触发器deaprt_update，当更改部门号时同步更改employee表中对应的部门号

DELIMITER //
 CREATE TRIGGER deaprt_update 
 AFTER UPDATE ON department 
 FOR EACH ROW 
  BEGIN
  UPDATE employee,department SET employee.dno=new.dnumber WHERE employee.dno=old.dnumber;
  END//

其中new.dnumber中的new代表UPDATE之后的数据，old.dnumber中的old代表UPDATE之前的数据。

删除触发器：

DROP TRIGGER deaprt_update;

权限管理

MySQL的账户信息保存在 mysql 这个数据库里。

  USE mysql;

  SELECT user FROM user;

  • 创建账户，新创建的用户没有任何权限

       例如创建个用户，名字为Tom，密码为Tom   create user 'Tom'@'localhost' identified by 'Tom';

  • 修改账户名

       RENAME USER 'myuser' TO 'newuser';

  • 删除用户

       DROP USER 'myuser';

  • 查看权限

       SHOW GRANTS FOR 'myuser';

  • 授予权限

       例如将可以在db_class库employee表上select,update,delete的权限授予Tom,

       grant select,update,delete,insert on db_class.employee to 'Tom'@'localhost' with grant option;

       其中with grant option代表Tom可以将这些权限传递给下一个人。