Press "Enter" to skip to content

Tag: 数据库

存储过程与函数

存储过程和函数实在数据库中年定义一些SQL语句的集合,然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且,存储过程和函数实在MySQL服务器中存储和执行的,可以减少客户端和服务器端的数据传输。

创建存储过程和函数

创建存储过程和函数是指将经常使用的一组SQL语句组合在一起,并将这些SQL语句当做一个整体存储在MySQL服务器中。

创建示例表

idnamecompanypriceproduce_timevalidity_timeaddress
1AA饼干AA饼干厂2.520083北京
2CC牛奶CC牛奶厂3.520091河北
3EE果冻EE果冻厂1.520072北京
4FF咖啡FF咖啡厂2020025天津
5GG奶糖GG奶糖厂1420033广东
#创建表
mysql> create table food
(id int(10) not null unique primary key auto_increment,
name varchar(20) not null, 
company varchar(30) not null, 
price float, 
produce_time YEAR, 
validity_time int(4), 
address varchar(50)) 
default charset=utf8;
#插入数据
mysql> insert into food values
(1,'AA饼干','AA饼干厂',2.5,'2008',3,'北京'),
(2,'CC牛奶','CC牛奶厂',3.5,'2009',1,'河北'),  
(3,'EE果冻','EE果冻厂',1.5,'2007',2,'北京'),  
(4,'FF咖啡','FF咖啡厂',20,'2002',5,'天津'),  
(5,'GG奶糖','GG奶糖厂',14,'2003',3,'广东');
#创建存储过程
mysql> delimiter &&
mysql> create procedure food_price_count(in price_info1 float,in price_info2 float,out  count int)
    -> reads sql data
    -> begin
     -> declare temp float;
    -> declare match_price cursor for select price from food;
    -> declare exit handler for not found close match_price;
    -> set @sum=0;
    -> select count(*) into count from food
    -> where price>price_info1 and price_info2;
    -> open match_price;
    -> repeat
    -> fetch match_price into temp;
    -> if temp>price_info1 and price_info2
    -> then set @sum=@sum+temp;
    -> end if;
    -> until 0 end repeat;
    -> close match_price;
    -> end&&
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call food_price_count(2,18,@count);
Query OK, 0 rows affected (0.00 sec)
mysql> select @count,@sum;
+--------+------+
| @count | @sum |
+--------+------+
|      4 |   40 |
+--------+------+
1 row in set (0.00 sec)
#查看存储过程状态
mysql> show create procedure food_price_count\G
#删除存储过程
mysql> drop procedure food_price_count;

MySQL函数

MySQL数据库中提供了丰富的函数。通过这些函数可以简化用户的操作。

数学函数

数学函数是MySQL中常用的一类函数,主要用欧冠与处理数字,包括整型、浮点数等。数学好书包括绝对值函数、正弦函数、余弦函数和获取随机数的函数等。

字符串函数

字符串函数主要用于处理表中的字符串。包括求字符串长度、合并字符串、在字符串中插入子串和大小写字母切换等函数。

时间和日期函数

日期和时间函数主要用于处理表中的日期和时间数据。日期和时间函数包括获取当日日期的函数、获取当前时间的函数、计算日期的函数、计算时间的函数等。

条件判断函数

条件判断函数用来在SQL语句中进行条件判断。根据是否满足判断条件,SQL语句执行不同的分支。

系统信息函数

系统信息函数用来查询MySQL数据库的系统信息,例如数据库版本、当前用户等。

mysql> select database(),schema(),version(),session_user();
+------------+----------+-------------------------+----------------+
| database() | schema() | version()               | session_user() |
+------------+----------+-------------------------+----------------+
| sujx       | sujx     | 5.7.19-0ubuntu0.16.04.1 | sujx@localhost |
+------------+----------+-------------------------+----------------+
1 row in set (0.00 sec)

加密函数

因为数据库总有些敏感信息不希望被其他人看到,就应该通过加密的方式来使得这些数据变成看似乱码的数据。例如用户密码。

其他函数

MySQL查询习题

建立连个数据表,department表和employee表。


| d_id | d_name | function | d_address |
| ------------ | ------------ | ------------ | ------------ |
| 1001 | 人事部 | 人事管理 | 北京 |
| 1002 | 科研部 | 研发产品 | 成都 |
| 1003 | 生产部 | 产品生产 | 天津 |
| 1004 | 销售部 | 产品销售 | 上海 |


| id | name | sex | age | d_id | salary | address |
| :------------: |:------------: |: ------------:| :------------ | :------------: | :------------:| :------------ 😐
| 9001 | Aric | 男 | 25 | 1002 | 4000 | 北京市海淀区 |
| 9002 | Jim | 男 | 26 | 1001 | 2500 | 北京市昌平区 |
| 9003 | Tom | 男 | 20 | 1003 | 1500 | 湖南省永州市 |
| 9004 | Eric | 男 | 30 | 1001 | 3500 | 北京市顺义区 |
| 9005 | Lily | 女 | 21 | 1002 | 3000 | 北京市平谷县 |
| 9006 | Lucy | 女 | 27 | 1004 | 8000 | 上海市静安区 |
| 9007 | Jack | 男 | 23 | 1004 | 4000 | 天津市南开区 |


CREATE TABLE `department` (
  `d_id` int(10) NOT NULL,
  `d_name` varchar(20) NOT NULL,
  `function` varchar(20) DEFAULT NULL,
  `d_address` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`d_id`),
  UNIQUE KEY `d_id` (`d_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `employee` (
  `id` int(10) NOT NULL,
  `name` varchar(20) NOT NULL,
  `sex` varchar(4) DEFAULT NULL,
  `age` int(5) DEFAULT NULL,
  `d_id` varchar(20) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

然后在department表和employee表查询记录。
查询的要求如下:
1. 计算所有员工的出生年份,并显示结果中的字段别名为birth_year;

mysql> select name,2017-age as birty_year from employee ;
+------+------------+
| name | birty_year |
+------+------------+
| Aric |       1992 |
| Jim  |       1991 |
| Tom  |       1997 |
| Eric |       1987 |
| Lily |       1996 |
| Lucy |       1990 |
| Jack |       1994 |
+------+------------+
7 rows in set (0.00 sec)
  1. 用limit关键字来查询工资最低的员工的信息;
mysql> select * from employee order by salary limit 0,1;
+------+------+------+------+------+--------+--------------------+
| id   | name | sex  | age  | d_id | salary | address            |
+------+------+------+------+------+--------+--------------------+
| 9003 | Tom  | 男   |   20 | 1003 |   1500 | 湖南省永州市       |
+------+------+------+------+------+--------+--------------------+
  1. 计算男性与女性员工的平均工资;
mysql> select sex,avg(salary) from employee group by sex;                                                       +------+-------------+
| sex  | avg(salary) |
+------+-------------+
| 女   |        5500 |
| 男   |        3100 |
+------+-------------+
2 rows in set (0.01 sec)
  1. 查询在上海工作的员工姓名、性别、年龄和部门名称;
select name,sex,age,department.d_name,d_address from employee,department where employee.d_id=(select d_id from department where d_address='上海') and employee.d_id=department.d_id;
+------+------+------+-----------+-----------+
| name | sex  | age  | d_name    | d_address |
+------+------+------+-----------+-----------+
| Lucy | 女   |   27 | 销售部    | 上海      |
| Jack | 男   |   23 | 销售部    | 上海      |
+------+------+------+-----------+-----------+
2 rows in set (0.00 sec)
  1. 用右连接的方式查询department表和employee表;
mysql> select name,sex,age,department.d_name,d_address,function from employee right join department on employee.d_id=department.d_id;
+------+------+------+-----------+-----------+--------------+
| name | sex  | age  | d_name    | d_address | function     |
+------+------+------+-----------+-----------+--------------+
| Aric | 男   |   25 | 科研部    | 成都      | 研发产品     |
| Jim  | 男   |   26 | 人事部    | 北京      | 人事管理     |
| Tom  | 男   |   20 | 生产部    | 天津      | 产品生产     |
| Eric | 男   |   30 | 人事部    | 北京      | 人事管理     |
| Lily | 女   |   21 | 科研部    | 成都      | 研发产品     |
| Lucy | 女   |   27 | 销售部    | 上海      | 产品销售     |
| Jack | 男   |   23 | 销售部    | 上海      | 产品销售     |
+------+------+------+-----------+-----------+--------------+
7 rows in set (0.00 sec)
  1. 查询名字以字母T为开头的员工姓名、性别、年龄、部门和工作地点;
mysql> select name,sex,age,department.d_name,d_address from employee,department where name like "^T" and employee.d_id=department.d_id;
+------+------+------+-----------+-----------+
| name | sex  | age  | d_name    | d_address |
+------+------+------+-----------+-----------+
| Tom  | 男   |   20 | 生产部    | 天津      |
+------+------+------+-----------+-----------+
1 row in set (0.00 sec)
  1. 查询年龄小于25或者大于30岁的员工信息;
mysql> select * from employee where age>30 or age<25;
+------+------+------+------+------+--------+--------------------+
| id   | name | sex  | age  | d_id | salary | address            |
+------+------+------+------+------+--------+--------------------+
| 9003 | Tom  | 男   |   20 | 1003 |   1500 | 湖南省永州市       |
| 9005 | Lily | 女   |   21 | 1002 |   3000 | 北京市平谷县       |
| 9007 | Jack | 男   |   23 | 1004 |   4000 | 天津市南开区       |
+------+------+------+------+------+--------+--------------------+
  1. 查询家住永州市的员工的信息。
mysql> select * from employee where address like "%永州%";
+------+------+------+------+------+--------+--------------------+
| id   | name | sex  | age  | d_id | salary | address            |
+------+------+------+------+------+--------+--------------------+
| 9003 | Tom  | 男   |   20 | 1003 |   1500 | 湖南省永州市       |
+------+------+------+------+------+--------+--------------------+
1 row in set (0.00 sec)

MySQL用户管理

MySQL的用户主要包括普通用户和root用户。root用户权限包括创建用户、删除用户和修改普通用户密码等管理权限。而普通用户只拥有创建该用户时赋予它的权限。

权限表

安装MySQL时会自动安装一个名为mysql的数据库。数据库下面存储的都是权限表。用户登录以后,MySQL数据库会根据这些权限表的内容为每个用户赋予相应的权限。这些权限表中最重要是user表、db表和host表。

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

user表

  1. 用户列
    user表的用户列包括host|user|Password,分别表示主机名、用户名和密码。用户登录首先要判断着三个字段。修改用户密码实际就是修改user表的password字段。
  2. 权限列
    user表的权限列包括Select_priv,Insert_Priv等以priv结尾的字段。这些字段决定了用户的权限,其中包括查询、修改等普通权限,也包括饿了关闭服务的权限、超级权限和加载用户等高级管理权限。
    普通权限用于操作数据库。高级管理权限用于对数据库进行管理。
    这些字段值只有Y和N,Y表示该权限可以用到所有数据库上,N表示不能用到所有数据库。默认为N。
  3. 安全列
    user表的安全列只有4个字段,分别是ssl_type、ssl_cipher、X509_issur和X509_subject。ssl用于加密,x509用来标识用户。
  4. 资源控制列
    user表的资源控制列是max_questions、max_updates、max_connections、max_user_connections。max_questions和max_updates分别规定每小时可以允许执行多少次查询和更新;max_connections规定每小时可以建立多少链接;max_user_connections规定单个用户可以同时具有的连接数。默认值为0,表示没有限制。

db表和host表

db表和host表也是MySQL中重要的权限表。db表中存储了某个用户对一个数据库的权限。

  1. 用户列
    db表有三个字段:host、db和user。表示主机名、数据库名和用户名。
    host表有两个字段:host和db。
    host表示db表的扩展。
  2. 权限列
    db表和host表的权限列几乎一样,只是db表中多了一个create_routine_priv字段和alter_routine_priv字段。它决定用户是否具有创建和修改存储的权限。
    user表中的权限是针对所有数据库。如果user表中的select_priv字段取值Y,那么该用户可以查询所有数据库中的表。由此可知,用户先根据user表的内容获取权限,然后再根据db表的内容获取权限。

tables_priv表和columns_priv表

在MySQL中权限分配是按照user表、db表、tables_priv表和columns_priv表的顺序进行分配的。

tables_priv表对单个表权限进行限制;columns_priv表对单个数据列进行权限限制。tables_priv表包含8个字段,host、db、user、table_name、table_priv、column_priv、timestamp\、grantor.columns_priv表包括7个字段,是host、db、user、table_name、Column_name和Column_priv和timestamp。

procs_priv表

procs_priv表对存储过程和存储函数进行权限设置。

procs_priv表包含8个字段,分别是Host、DB、User、Routine_name、Routine_type、Routine_priv、timestamp、grantor。routine_name字段表示存储过程或函数的名称。Routine_type表示存储的类型。该字段有两个值,function(函数)和procdeure(存储过程)。proc_priv字段表示拥有的权限。权限分为三类,分别为execute、alter routine、grant。timestamp存储更行时间、grantor字段存储谁设置的权限。

账户管理

账户管理是MySQL用户管理的最基本的内容。账户管理包括登录和退出MySQL服务器、创建用户、删除用户、密码管理和权限管理等内容。通过账户管理,可以保证MySQL数据库的安全性。

登录和退出MySQL服务器

mysql -h hostname|IP -P prot -u username -p Database -e "SQL语句"
\\在shell中执行sql命令,完成后自动退出
sujx@TC8304:~$ mysql -h 192.168.31.25 -P 3306 -u sujx -p sujx -e "show tables;"
Enter password: 
+----------------+
| Tables_in_sujx |
+----------------+
| department     |
| employee       |
| product        |
| t1             |
+----------------+
\\使用指定用户登录制定数据库
sujx@TC8304:~$ mysql -h 192.168.31.25 -P 3306 -u sujx -p sujx 
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.19-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show tables;
+----------------+
| Tables_in_sujx |
+----------------+
| department     |
| employee       |
| product        |
| t1             |
+----------------+
4 rows in set (0.00 sec)

新建普通用户

  1. 使用create user来创建用户;
mysql> create user 'test1'@'localhost' identified by 'test1';
  1. 使用insert语句来新建用户;
mysql> insert into mysql.user(host,user,authentication_string,ssl_cipher,x509_issuer,x509_subject) values('localhost','test1','test1','','','');
Query OK, 1 row affected (0.00 sec)

tips:从5.7版本开始mysql.user表中密码字段不再是password,而是authentication_string
3. 使用grant来新建普通用户;

mysql> grant select on sujx.* to 'test2'@'localhost' identified by 'test2';

删除普通用户

  1. 使用drop user来删除用户;
mysql> drop user 'test1'@'localhost';
Query OK, 0 rows affected (0.00 sec)
  1. 使用delete来删除
    可以使用delete直接从mysql.user表中删除用户信息。

注意,要使用 flushprivileges 来刷新权限。

root修改密码

  1. 使用mysqladmin命令来修改root用户密码
sujx@TC8304:~$ mysqladmin -u root -p password "*******"
Enter password: **********
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
  1. 修改mysql.user表
update mysql.user set authentication_string='******' where user='root';
  1. 使用set语句来修改root用户密码
mysql> set password=password("******");
Query OK, 0 rows affected, 1 warning (0.00 sec)

修改普通用户密码

  1. 使用set语句
  2. 直接修改mysql.user表
  3. 用grant语句来修改

root用户密码丢失

  1. 使用--skip-grant-tables选项启动MySQL;
sujx@TC8304:~$ sudo service mysql stop
sujx@TC8304:~$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf 
\\在[mysqld]字段下增加:skip-grant-tables
sujx@TC8304:~$ sudo service mysql start
sujx@TC8304:~$ mysql
  1. 无密码登陆root用户,必须使用update修改密码;
update user set authentication_string=password('******') where user='root' and host='localhost';
  1. 加载权限表
  2. 去除配置文件中过的skip_grant_tables项

权限管理

  1. 授权
mysql> grant all privileges on sujx.* to 'sujx'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'sujx';
+------------------------------------------------+
| Grants for sujx@%                              |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'sujx'@'%'               |
| GRANT ALL PRIVILEGES ON `sujx`.* TO 'sujx'@'%' |
+------------------------------------------------+
2 rows in set (0.00 sec)
  1. 收回
mysql> revoke all privileges, grant option from 'sujx';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'sujx';
+----------------------------------+
| Grants for sujx@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO 'sujx'@'%' |
+----------------------------------+
1 row in set (0.00 sec)
  1. 查看
mysql> show grants for 'sujx';
+------------------------------------------------+
| Grants for sujx@%                              |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'sujx'@'%'               |
| GRANT ALL PRIVILEGES ON `sujx`.* TO 'sujx'@'%' |
+------------------------------------------------+
2 rows in set (0.00 sec)